0

I am using OpenXML for exporting data to Excel (.xlsx) I am getting following error when I am trying to write Data having 29 columns and 62397 rows

enter image description here

However I have noiced that an xlsx file is created and all the rows have been written into it.

Please let me know if anything is missing.

The code is as follows

private static void ExporttoExcel(DataSet ds, SpreadsheetDocument sheet)
{
    try
    {           
        sheet.AddWorkbookPart();
        sheet.WorkbookPart.Workbook = new DocumentFormat.OpenXml.sheet.Workbook();

        sheet.WorkbookPart.Workbook.Append(new BookViews(new WorkbookView()));

        WorkbookStylesPart workbookStylesPart = sheet.WorkbookPart.AddNewPart<WorkbookStylesPart>("rIdStyles");
        Stylesheet stylesheet = new Stylesheet();
        workbookStylesPart.Stylesheet = stylesheet;


        int worksheetNumber = 1;
        foreach (DataTable dt in ds.Tables)
        {

            string workSheetID = "sheet" + worksheetNumber.ToString();
            string worksheetName = dt.TableName;

            WorksheetPart newWorksheetPart = sheet.WorkbookPart.AddNewPart<WorksheetPart>();
            newWorksheetPart.Worksheet = new DocumentFormat.OpenXml.sheet.Worksheet();


            newWorksheetPart.Worksheet.AppendChild(new DocumentFormat.OpenXml.sheet.SheetData());


            WriteDataTableToExcelWorksheet(dt, newWorksheetPart);
            newWorksheetPart.Worksheet.Save();

            if (worksheetNumber == 1)
                sheet.WorkbookPart.Workbook.AppendChild(new DocumentFormat.OpenXml.sheet.Sheets());

            sheet.WorkbookPart.Workbook.GetFirstChild<DocumentFormat.OpenXml.sheet.Sheets>().AppendChild(new DocumentFormat.OpenXml.sheet.Sheet()
            {
                Id = sheet.WorkbookPart.GetIdOfPart(newWorksheetPart),
                SheetId = (int)worksheetNumber,
                Name = dt.TableName
            });

            worksheetNumber++;
        }

        sheet.WorkbookPart.Workbook.Save();

    }
    catch (Exception)
    {
        throw;
    }
    finally
    {
        sheet.Dispose();
    }
}

private static void WriteDTToExcel(DataTable dt, WorksheetPart worksheet)
{
    var worksheet = worksheet.Worksheet;
    var sheetData = worksheet.GetFirstChild<SheetData>();

    string cellValue = "";

    int numberOfColumns = dt.Columns.Count;
    bool[] IsNumericColumn = new bool[numberOfColumns];

    string[] excelColumnNames = new string[numberOfColumns];
    for (int n = 0; n < numberOfColumns; n++)
        excelColumnNames[n] = GetExcelColumnName(n);


    int rowIndex = 1;

    var headerRow = new Row { RowIndex = rowIndex };  // add a row at the top of sheet
    sheetData.Append(headerRow);

    for (int colInx = 0; colInx < numberOfColumns; colInx++)
    {
        DataColumn col = dt.Columns[colInx];
        AppendTextCell(excelColumnNames[colInx] + "1", col.ColumnName, headerRow);
        IsNumericColumn[colInx] = (col.DataType.FullName == "System.Decimal") || (col.DataType.FullName == "System.Int32");
    }

    double cellNumericValue = 0;
    foreach (DataRow dr in dt.Rows)
    {

        ++rowIndex;
        var newExcelRow = new Row { RowIndex = rowIndex };  // add a row at the top of sheet
        sheetData.Append(newExcelRow);

        for (int colInx = 0; colInx < numberOfColumns; colInx++)
        {
            cellValue = dr.ItemArray[colInx].ToString();


            if (IsNumericColumn[colInx])
            {                   
                cellNumericValue = 0;
                if (double.TryParse(cellValue, out cellNumericValue))
                {
                    cellValue = cellNumericValue.ToString();
                    AppendNumericCell(excelColumnNames[colInx] + rowIndex.ToString(), cellValue, newExcelRow);
                }
            }
            else
            {                   
                AppendTextCell(excelColumnNames[colInx] + rowIndex.ToString(), cellValue, newExcelRow);
            }
        }
    }
}

private static void AppendTextCell(string cellReference, string value, Row excelRow)
{       
    Cell cell = new Cell() { CellReference = cellReference, DataType = CellValues.String };
    CellValue cellValue = new CellValue();
    cellValue.Text = value;
    cell.Append(cellValue);
    excelRow.Append(cell);
}

private static void AppendNumericCell(string cellReference, string value, Row row)
{       
    Cell cell = new Cell() { CellReference = cellReference };
    CellValue cellValue = new CellValue();
    cellValue.Text = value;
    cell.Append(cellValue);
    row.Append(cell);
}

private static string GetExcelColumnName(int columnIndex)
{

    if (columnIndex < 26)
        return ((char)('A' + columnIndex)).ToString();

    char firstChar = (char)('A' + (columnIndex / 26) - 1);
    char secondChar = (char)('A' + (columnIndex % 26));

    return string.Format("{0}{1}", firstChar, secondChar);
}

Exception is thrown while executing following line of ExporttoExcel method

spreadsheet.WorkbookPart.Workbook.Save();
11
  • Usually we get this exception when there is not enough space on the disk where you are storing the file. Have you checked about that? Commented Dec 7, 2016 at 9:08
  • I have plenty of space. Thank you. Commented Dec 7, 2016 at 9:38
  • 4
    My answer over here might help you. Commented Dec 7, 2016 at 9:49
  • 1
    @captainsac I would suggest that you switch to EPPlus. It's actively supported, open source,very popular with the community, doesn't require additional downloads and can be installed from Nuget directly. There are dozens of related questions in SO Commented Dec 7, 2016 at 11:00
  • 2
    The call stack you give indicates the error doesn't happen directly in your code, but is caused by ASP.NET view state serialization. You probably have put an object in a page, directly or indirectly (in a control, etc.) that ASP.NET tries to serialize that result in a too big string. If you don't use viewstate, you can try to desactivate it, at least to check how it's related : msdn.microsoft.com/en-us/library/… Commented Dec 10, 2016 at 9:53

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.