添加链接
link管理
链接快照平台
  • 输入网页链接,自动生成快照
  • 标签化管理网页链接

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!