Recursive error access violation

Question

I did a class on C# to open Excel with a DataTable:

var excelApp = new ExcelInterop.Application();
excelApp.Workbooks.Add();
ExcelInterop._Worksheet workSheet = excelApp.ActiveSheet;

for (int i = 0; i < dt.Columns.Count; i++)
{
    workSheet.Cells[1, (i + 1)] = dt.Columns[i].ColumnName;
}

ExcelRecursive(dt, workSheet, 0, 0);
excelApp.Visible = true;

The method recursive:

public void ExcelRecursive(DataTable dt, ExcelInterop._Worksheet ws, int i, int j)
{
    ws.Cells[(i + 2), (j + 1)] = dt.Rows[i][j];
    if (j + 1 < dt.Columns.Count)
        ExcelRecursive(dt, ws, i, j + 1);
    else if (i + 1 < dt.Rows.Count)
        ExcelRecursive(dt, ws, i + 1, 0);
}

DataTables with 70 rows it's work very well, but more then that the application stopped and on console show an error 'Access Violation':

The program '[3396] iisexpress.exe: Program Trace' has exited with code 0 (0x0).

The program '[3396] iisexpress.exe' has exited with code -1073741819 (0xc0000005) 'Access violation'.

I try this:

for (int i = 0; i < dt.Rows.Count; i++)
{
    // to do: format datetime values before printing
    for (int j = 0; j < dt.Columns.Count; j++)
    {
        workSheet.Cells[(i + 2), (j + 1)] = dt.Rows[i][j];
    }
}

And this:

public ExcelInterop._Worksheet ExcelRecursive(DataTable dt, ExcelInterop._Worksheet ws, int i, int j)
{
    ws.Cells[(i + 2), (j + 1)] = dt.Rows[i][j];

    if (j + 1 < dt.Columns.Count)
        return ExcelRecursive(dt, ws, i, j + 1);
    else if (i + 1 < dt.Rows.Count)
        return ExcelRecursive(dt, ws, i + 1, 0);
    else
        return ws;
}

But my code only works on little DataTables

As far as I can see, you have a recursive call for each cell in your worksheet. For example a sheet with 256 Columns and 70 Rows leads to a recursion depth around 18000 and each recursion step needs a portion of your stack... Maybe you can measure your recursion depth and adjust your code if thats the problem? — grek40
Do NOT use recursion if you don't know how to determine recursion depth. Find a different solution to the problem, using loops instead of recursion. — grek40

Rodolfo Luna Rodolfo Luna · Accepted Answer · 2016-01-15T13:02:22

I cannot use this recursive mode.

My solution was EPPlus.dll

using OfficeOpenXml;

this method:

DataTable dt = Conversao.ConvertTo(list);
if (dt == null || dt.Columns.Count == 0)
    throw new Exception("ExportToExcel: Null or empty input table!\n");

OfficeOpenXml.ExcelPackage excel = new ExcelPackage();

ExcelWorksheet worksheet = excel.Workbook.Worksheets.Add("Plan 1");
worksheet.Cells["A1"].LoadFromDataTable(dt, true);

for (var i = 0; i < dt.Columns.Count; i++)
{
    if (dt.Columns[i].DataType == System.Type.GetType("System.DateTime"))
    {
        worksheet.Column(i + 1).Style.Numberformat.Format = "dd/mm/yyyy hh:mm:ss";
    }
}

Response.Clear();
Response.AddHeader("content-disposition", "attachment;filename=rel.xlsx");

Response.ContentType = "application/vnd.ms-excel";
Response.ContentEncoding = System.Text.Encoding.Default;

Response.Cache.SetCacheability(HttpCacheability.NoCache);

System.IO.MemoryStream stream = new System.IO.MemoryStream();
excel.SaveAs(stream);

stream.WriteTo(Response.OutputStream);

Response.End();

Thanks grek40

Recursive error access violation

1 Answers