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
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();
