Export to excel in c# showing error and not downloading the excel

Question

My code, where the collection of values retrived from DB and that values stored one by one in datatable.And loading that dataTable to worksheet of Excel

var dataTable = new DataTable();
var sb = new StringBuilder();
var resultvalues=methodtogetvalues() ;


if (resultvalues!= null && resultvalues.Count > 0)
{
    var icount = 1;

    foreach (var values in resultvalues)
    {
        if (icount == 1)
        {
            sb.Append(values.Id);
            icount += 1;
        }

        dataTable.Columns.Add("ID");
        dataTable.Rows.Add(values.Id);
    }
}

var firstName = context.Request.Params["FirstName"];
var lastName = context.Request.Params["LastName"];
var fileName = firstName+lastName+"_ProgramStatusHistory_"+DateTime.Now;

var tempText = Convert.ToString(sb);
var workBook = new ExpertXls.ExcelLib.ExcelWorkbook(ExpertXls.ExcelLib.ExcelWorkbookFormat.Xlsx_2007);
var accessedRangeStyle = workBook.Styles.AddStyle("ΑccessedRangeStyle");
accessedRangeStyle.Font.Size = 10;
accessedRangeStyle.Font.Bold = true;
accessedRangeStyle.Alignment.VerticalAlignment = ExpertXls.ExcelLib.ExcelCellVerticalAlignmentType.Center;
accessedRangeStyle.Alignment.HorizontalAlignment = ExpertXls.ExcelLib.ExcelCellHorizontalAlignmentType.Left;
workBook.Worksheets.AddWorksheet();

var workSheet = workBook.Worksheets[0];
workSheet.LoadDataTable(dataTable, 1, 1, true);
workSheet.AutofitColumns();
workBook.Worksheets.RemoveWorksheet("Sheet2");
workBook.Worksheets.RemoveWorksheet("Sheet3");
workBook.Worksheets.RemoveWorksheet("Sheet4");
workBook.LicenseKey = "gqmworCworOysrWitKyyorGzrLOwrLu7u7s=";

context.Response.Clear();
context.Response.Buffer = true;
context.Response.Charset = "";
context.Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
context.Response.AddHeader("content-disposition", "attachment;filename=" + fileName);

using (var MyMemoryStream = new System.IO.MemoryStream())
{
    workBook.Save(MyMemoryStream);
    MyMemoryStream.WriteTo(context.Response.OutputStream);
    context.Response.Flush();
    context.Response.End();
}

This code simply shows an alert box with the error message "Error".

I don't understand whats wrong. Can anyone redirect me with correct way.

`this code simply showing alert box with the error message "Error".` When you debug through it, what is the line of code executing when `Error` appears? — mjwills, Dec 19 '17 at 08:17
context.Response.End(); after this line it moves to catch segment and shows the alert box — user7415073, Dec 19 '17 at 08:21
Suggest stepping through with the debugger and seeing which line goes wrong, work out what's wrong with the input parameters or object state at that point in time, and then if you're unsure how to resolve it then post that question. — LordWilmore, Dec 19 '17 at 08:27
Possibly look at adding logging so you are able to debug exceptions that occur on QA and Production such as [elmah](https://elmah.github.io) — lloyd, Dec 19 '17 at 08:46
Possible duplicate of [Unable to evaluate expression because the code is optimized or a native frame is on top of the call stack](https://stackoverflow.com/questions/2041482/unable-to-evaluate-expression-because-the-code-is-optimized-or-a-native-frame-is) — mjwills, Dec 19 '17 at 10:46

score 0 · Answer 1 · answered Dec 19 '17 at 09:21

0

I would move this line of code outside the using statement:

context.Response.End();

also show your catch block. It's unlikely to be giving just a message of 'Error' I would suggest placing a breakpoint in the catch block and looking inside the error object.

answered Dec 19 '17 at 09:21

bilpor

3,467
6
32
77

it simply showing the exception as " Unable to evaluate expression because the code is optimized or a native frame is on top of the call stack." – user7415073 Dec 19 '17 at 09:37

Export to excel in c# showing error and not downloading the excel

1 Answers1