37
Hi Friends,
Can someone help me on writing a C# code to convert the data from an excel file into byte array and then perform some validation check to the various columns in the excel like checking the date format, length of data, not null etc
Thanks
JB
*This post is locked for comments
Hi Temmy,
I tried wiriting a c# code like below for converting into byte array
private static void GetExcelData()
{
try
{
var fileBytes = File.ReadAllBytes(@"filepath");
using (var stream = new MemoryStream(fileBytes))
using (var document = SpreadsheetDocument.Open(stream, true))
{
var workbookpart = document.WorkbookPart;
WorksheetPart worksheetPart = GetWorksheetFromSheetName(workbookpart, "Sheet1");
Worksheet sheet = worksheetPart.Worksheet;
var cells = sheet.Descendants<Cell>();
var rows = sheet.Descendants<Row>();
var sharedStringPart = workbookpart.SharedStringTablePart;
var values = sharedStringPart.SharedStringTable.Elements<SharedStringItem>().ToArray();
var count = 1;
var rowindex = "";
foreach (Row row in rows)
{
if (row.RowIndex == 1)
continue;
if (row.RowIndex == 2)
count++;
rowindex = "A" + count;
} } } }
I am stuck here on how to proceed to check whether a particular cell is empty or a whole A coulmn is empty. Can you guide me how to write this.
You can check this url: stackoverflow.com/.../7920473
Thanks Temmy for your response, I am very new to WepApi, could you provide me some sample code for performing this.
Thanks
JB
The safest and the easiest way would be creating WebApi to handle converting the file into byte array and perform some validation. You can using third party libs like ClosedXML. In there you can convert excel file become c# POCO and do some validation. If the validation pass, then you can return byte array as result then your plugin can just use the value that already clean.
Why WebApi? Because it will help you to ease your development (no need doing merge) and will be more easy in the future if you want to upgrade to online version.
Thanks Braddev for your response. Converting the excel data into byte array was working with your code but how can i do validations on the value from the excel. For example, I have date of birth column in which I need to check the length of the value is 6 and if its 5 then add a '0' at the front and if it less than 5 or more than 6 just throw some error. How can I implement this.
Hello,
This is the code i use:
static public byte[] FileToByteArray(string path)
{
FileStream fs = new FileStream(path, FileMode.Open);
byte[] binary = StreamtoByteArray(fs);
fs.Close();
return binary;
}
Hi JB,
You need to use File.ReadAllBytes command to do this.
Here is the Microsoft documentation docs.microsoft.com/.../system.io.file.readallbytes
In practice you will end up with something like this...
-----
using System;
using System.IO;
class Program
{
static void Main()
{
byte[] array = File.ReadAllBytes("YOUREXCELFILELOCATIONHERE");
Console.WriteLine(array.Length);
}
}
----
Hope this helps...
#1
Mohamed Amine Mahmoudi
83
Super User 2025 Season 1
#2
Community Member
52
#3
dkrishna
6
Share
Report
All responses (
Answers (
