In this article, We learn how to convert base64 string format of excel file into Data Table form of data type using C#. I will show how to do it, and just follow the steps clearly.

For more updates please do Subscribe via Email:

I encounter this task and make me sleepless during night because I can’t see any reference in other blog resources. Basically the logic flows like from the Logic app pass value a string format of excel file into a function apps and make it converted into datatable type of data.

Sample data parameter

This is the output of getting content file from azure storage using azure logic app.

{
  "$content-type": "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
  "$content": "UEsDBBQABgAIAAAAIQAKhJ0TtAEAAFUGAAATAAg..."
}

Model

In C#, we need to create a model to handle when we convert the JSON to Object model. So this is the model.

  class ParamModel
    {
        [JsonProperty("$content-type")]
        public string contenttype { get; set; }

        [JsonProperty("$content")]
        public string content { get; set; }
    }

Declare a variable name requestBody as string type of data, And stored data from string JSON Sample data parameter.

Conversion of JSON to Model object

This is how to convert JSON string into Model object.

ParamModel stringob = JsonConvert.DeserializeObject(base64String);

Conversion of Base64String to Bytes

From string base64 data to byte conversion.

 byte[] bytes = Convert.FromBase64String(stringob.content);

Conversion of worksheet to datatable

Also, from worksheet to datatable of type of data.

System.Data.DataTable dataTable = worksheet.Cells.ExportDataTable(0, 0, worksheet.Cells.MaxDataRow + 1, worksheet.Cells.MaxDataColumn + 1, true);

Full source code of conversion from base64 string of excel to datatable using C#

this is the full source code flow from base64 to

 ParamModel stringob = JsonConvert.DeserializeObject<ParamModel>(base64String);
byte[] bytes = Convert.FromBase64String(stringob.content);
Stream file = new MemoryStream(bytes, 0, bytes.Length);

Aspose.Cells.LoadOptions loadOptions = new Aspose.Cells.LoadOptions(LoadFormat.Xlsx);
Aspose.Cells.Workbook workbook = new Aspose.Cells.Workbook(file, loadOptions);

Aspose.Cells.Worksheet worksheet = workbook.Worksheets[0];

System.Data.DataTable dataTable = worksheet.Cells.ExportDataTable(0, 0, worksheet.Cells.MaxDataRow + 1, worksheet.Cells.MaxDataColumn + 1, true);

Happy Learning..

Thank you for visiting my blog site. Hoping you learn more here. please feel free to comment and suggest if there is need to enhance and update. thank you.

Related Topics

Leave a Reply

Your email address will not be published. Required fields are marked *