Kgn-web Kgn-web - 9 days ago 9
ASP.NET (C#) Question

Index was outside the bounds of the array while exporting data to excel in C#

I have

ASP.Net
site. Following is my method to write stream to excel.

public void JsonToExcel(string jsonData, HttpResponseBase response)
{
try
{
ExcelPackage excel = new ExcelPackage();
var worksheet = excel.Workbook.Worksheets.Add("Sheet1");
//below line is throwing the error
worksheet.Cells[1, 1].LoadFromCollection(jsonData, true);
using (MemoryStream swObj = new MemoryStream())
{
string fileName = DateTime.Now.ToLongDateString() + ".xlsx";
response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
response.AddHeader("content-disposition", "attachment; filename=" + fileName + "");
excel.SaveAs(swObj);
swObj.WriteTo(response.OutputStream);
return;
}

}
catch (Exception ex)
{
//handle exception
return;
}
finally
{
response.Flush();
response.End();
}


At this line of the function
-
worksheet.Cells[1, 1].LoadFromCollection(jsonData, true);


I am getting the following exception


Index was outside the bounds of the array


I tried setting to
worksheet.Cells[0,0]


I am using EPPlus 4.1.0 package.

JSON Sample :-

string jsonData = @"jsonData = [
{
""DocumentName"": ""Test Document"",
""ActionDate"": ""2015-09-25T16:06:25.083"",
""ActionType"": ""View"",
""ActionPerformedBy"": ""Sreeja SJ""
},
{
""DocumentName"": ""Test Document"",
""ActionDate"": ""2015-09-25T16:12:02.497"",
""ActionType"": ""View"",
""ActionPerformedBy"": ""Sreeja SJ""
},
{
""DocumentName"": ""Test Document"",
""ActionDate"": ""2015-09-25T16:13:48.013"",
""ActionType"": ""View"",
""ActionPerformedBy"": ""Sreeja SJ""
}]";

Answer

I think that it's problem with JSON and EPPlus method LoadFromCollection internals LoadFromCollection method expects a collection, not an arbitrary string or something else, so when I deserialized it to a appropriate class with Newtonsoft.Json it seems to be OK.

using System;
using System.Collections.Generic;
using System.IO;
using Newtonsoft.Json;
using OfficeOpenXml;

namespace TestC
{
    public class PostData
    {
        public string DocumentName { get; set; }
        public DateTime ActionDate { get; set; }
        public string ActionType { get; set; }
        public string ActionPerformedBy { get; set; }
    }
    class Program
    {
        static void Main(string[] args)
        {
            using (ExcelPackage package = new ExcelPackage()) {
                var worksheet = package.Workbook.Worksheets.Add("Sheet1");
                string jsonData = @"[
                    {
                        ""DocumentName"": ""Test Document"",
                        ""ActionDate"": ""2015-09-25T16:06:25.083"",
                        ""ActionType"": ""View"",
                        ""ActionPerformedBy"": ""Sreeja SJ""
                    },
                    {
                        ""DocumentName"": ""Test Document"",
                        ""ActionDate"": ""2015-09-25T16:12:02.497"",
                        ""ActionType"": ""View"",
                        ""ActionPerformedBy"": ""Sreeja SJ""
                    },
                    {
                        ""DocumentName"": ""Test Document"",
                        ""ActionDate"": ""2015-09-25T16:13:48.013"",
                        ""ActionType"": ""View"",
                        ""ActionPerformedBy"": ""Sreeja SJ""
                    }]";

                List<PostData> dataForExcel = JsonConvert.DeserializeObject<List<PostData>>(jsonData);

                worksheet.Cells[1, 1].LoadFromCollection(dataForExcel, true);

                package.SaveAs(File.Create(@"C:\Users\User\Documents\sample.xlsx"));
            }
        }
    }
}

Results in:

Excel


For correct date output in Excel you should apply correct number format for cells in second column (ActionDate) starting from second row to the end:

worksheet.Cells[2, 2, worksheet.Dimension.End.Row, 2].Style.Numberformat.Format = "yyyy-mm-ddThh:mm:ss.000";
Comments