How to convert an excel file into byte array and perform some validation checks on the data

(0) Share

Report

Posted on by CRM Reports

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

*This post is locked for comments

I have the same question (0)

All responses (7)

Answers (1)

braddev 62 on at

Like (1)

Report

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...

Was this reply helpful? Yes No
Suggested answer

Saad Kabarousse 734 on at

Like (0)

Report

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;

}

Was this reply helpful? Yes No
CRM Reports 37 on at

Like (0)

Report

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.

Was this reply helpful? Yes No
Verified answer

Temmy Wahyu Raharjo 2,916 on at

Like (1)

Report

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.

Was this reply helpful? Yes No
CRM Reports 37 on at

Like (0)

Report

Thanks Temmy for your response, I am very new to WepApi, could you provide me some sample code for performing this.

Thanks

JB

Was this reply helpful? Yes No
Suggested answer

Temmy Wahyu Raharjo 2,916 on at

Like (0)

Report

You can check this url: stackoverflow.com/.../7920473

Was this reply helpful? Yes No
CRM Reports 37 on at

Like (0)

Report

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.

Was this reply helpful? Yes No

Community site session details

How to convert an excel file into byte array and perform some validation checks on the data

Helpful resources

Quick Links

Responsible AI policies

Neeraj Kumar – Community Spotlight

Congratulations to the November Top 10 Community Leaders!

Leaderboard > 🔒一 Microsoft Dynamics CRM (Archived)

Featured topics

Product updates

Community site session details

How to convert an excel file into byte array and perform some validation checks on the data

Helpful resources

Quick Links

Responsible AI policies

Neeraj Kumar – Community Spotlight

Congratulations to the November Top 10 Community Leaders!

Subscribe to this forum!

Select categories

Leaderboard > 🔒一 Microsoft Dynamics CRM (Archived)

Featured topics

Product updates