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

Question

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""
                    }]";

one of the problems with working with excel is that the cells are not zero based indexes, youre better off using Closed XML found here https://closedxml.codeplex.com/ — Simon Price, Nov 24 '16 at 07:33
Can you please post a sample JSON data and the expected Excel conversion? — Souvik Ghosh, Nov 24 '16 at 07:41
Could you please add the full callstack of the exception? It's not clear where exactly the error occurs — Dirk Vollmar, Nov 24 '16 at 07:53
@SimonPrice you misunderstand cell indexes *and* what ClosedXml does - which is *exactly* the same thing as EPPlus. Both use indexes, both use addresses. You just pick one access method. In the end, both work with Excel sheets so they can't use arbitrary access methods — Panagiotis Kanavos, Nov 24 '16 at 08:12
@Kgn-web `LoadFromCollection` works with *collections*, not arbitrary strings. Did you forget to deserialize the string perhaps? Should the question be closed as a typo? — Panagiotis Kanavos, Nov 24 '16 at 08:14
@SimonPrice no, I just know Excel and I *did* offer a solution. Deserialize the string into a collection first. Vladislav offered a complete answer. There is nothing wrong with `Cells[1,1]`. No reason to switch to ClosedXML, or use the address syntax (A1) — Panagiotis Kanavos, Nov 24 '16 at 10:38

Vladislav Khapin · Accepted Answer · 2016-11-24T11:08:34.100

~~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:

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";

This isn't a matter of internals. `LoadFromCollection` expects a *collection*. It doesn't parse Json, XML or other arbitrary formats. — Panagiotis Kanavos, Nov 24 '16 at 08:15
@VladislavKhapin Exactly! It expects an IEnumerable collection as the input parameter. — Souvik Ghosh, Nov 24 '16 at 08:58
@VladislavKhapin, If am doing so, the `ActionDate` is getting value in the format as **42272.67503** in the downloaded excel — Kgn-web, Nov 24 '16 at 10:31
@Kgn-web That *is* how dates are stored in Excel. They are displayed as dates only because Excel applies a date format over the value — Panagiotis Kanavos, Nov 24 '16 at 10:37
@PanagiotisKanavos, in chrome I am getting an exception `Error code: ERR_RESPONSE_HEADERS_MULTIPLE_CONTENT_DISPOSITION` — Kgn-web, Nov 24 '16 at 10:48
@VladislavKhapin. Great really appreciate your efforts!! Thanks — Kgn-web, Nov 24 '16 at 11:14
@VladislavKhapin. just 1 more thing to ask. Whats **T** in the `2015-09-25T16:06:25.083` — Kgn-web, Nov 24 '16 at 11:16
@Kgn-web its a [delimeter](https://en.wikipedia.org/wiki/ISO_8601) like T — Vladislav Khapin, Nov 24 '16 at 11:18

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

1 Answers1