I ran into a problem today, testing my new NPOI DataTable web export function… While running some large queries that resulted in 65K+ rows of data, my function blew up. As you know, Excel 2003 and the BIFF format only support 65,536 rows! To make sure this never happens again, I’ve added a little block of code around my details loop to create an additional sheet every time you reach row 65,536.
code excerpt based on using a DataTable as your data source
int rowIndex = 1; // Starting Row (0 = Header)
int sheetIndex = 1; // Starting sheet is always set to "Sheet1"
const int maxRows = 65536; // Max rows p/sheet in Excel 2003
// Start loop of details to write to sheet
foreach (DataRow row in DataTableToExport.Rows)
// Check if max rows hit, if so start new sheet and copy headers from current sheet.
if(rowIndex % maxRows == 0)
// Auto size columns on current sheet
for (int h = 0; h < headerRow.LastCellNum; h++)
sheet.AutoSizeColumn(h);
// Increment sheet counter
sheetIndex++;
// Create new sheet
sheet = workbook.CreateSheet("Sheet" + sheetIndex);
// Create header on new sheet
HSSFRow additionalHeaderRow = sheet.CreateRow(0);
// Copy headers from first sheet
for (int h = 0; h < headerRow.LastCellNum; h++)
HSSFCell additionalHeaderColumn = additionalHeaderRow.CreateCell(h);
additionalHeaderColumn.CellStyle = headerRow.GetCell(h).CellStyle;
additionalHeaderColumn.SetCellValue(headerRow.GetCell(h).RichStringCellValue);
rowIndex = 1;
// Create new detail row in sheet
HSSFRow dataRow = sheet.CreateRow(rowIndex);
// Loop the columns from the DataRow and add using dataRow.CreateCell(#)....
In a nutshell, I create some counters before going into the detail row loop to track the Row and Sheet number. When I hit the max Row number number on a sheet, I create a new Sheet. To keep everything pretty, I copy the header row from the first sheet to the first row of the new sheet. The only output limitation now is the max sheets of 255.
This entry was posted on May 5, 2010, 5:28 pm and is filed under
C# Development
. You can follow any responses to this entry through
RSS 2.0
.
Both comments and pings are currently closed.
I used regular expressions to match the pattern of td, th , tr etc., in the html table. But if the styles are inline, it dint help. The inline styles were considered as text in the columns.
So I got the dataset from session as you had suggested.
Thanks for the help!
Hi Zach,
here you have provided example on how to use a datatable on export to excel.
How can we export a html table to excel using NPOI?
We are having a classic application where this is required. Can you give us some code sample?
You want to go from a HTML Table to Excel using NPOI? This is possible, but more work since you’ll have to itterate over the rows/columns to pull out all the values. Here is a link to StackOverflow where you can see somebody going from a GridView (aka: HTML Table) to a DataTable. You could go directly to NPOI vs. DataTable, just note that it’s a lot of work reading everything out a table since controls can be nested. Personally, I always try to cache my result set (DataTable, DataSet, Array, etc…) for a few minutes so I can use it for exporting, very quick and easy!