Problem

The Excel .xls binary format has a limit of 65536 rows per worksheet. If you are importing data with ExcelWriter’s ExcelTemplate object (or our SSRS renderer, which uses the ExcelTemplate object) it is possible to get an error that Excel’s row limit was exceeded even though you know you have imported much fewer than 65K rows.

This problem is usually caused by cells or formulas that already exist in the template file. When ExcelWriter populates a spreadsheet with the ExcelTemplate object, it inserts a new row for every row of data and pushes all existing rows down. If the total number of pushed down rows plus inserted rows exceeds Excel’s limit, an exception will be thrown. Then, after the data has been imported, ExcelWriter goes through the entire workbook and updates any formulas that reference the data marker cells, stretching the formulas to include all the newly inserted rows. If the cell range in a formula is stretched beyond Excel’s row limit, again an exception will be thrown.

Solution

Cells

Sometimes people take a previously populated report and turn it into a template. They may have cleared the cell values, but the template may still contain many rows of cells that are blank or contain only formatting. These rows will be pushed down when the data is imported.

The solution is to clean up the template file by actually deleting all unnecessary rows and columns rather than just clearing cell values. Also, it’s generally a good idea to set background formatting using row or column headers rather than by selecting large areas of cells.

Formulas

Formulas can be found in many parts of a workbook, including cells, charts, conditional formatting, and named ranges. Make sure that all formulas reference the minimum number of rows necessary and bear in mind that ExcelWriter will stretch certain formulas when new rows are inserted.

Hidden named ranges

It is important to know that Excel sometimes creates hidden named ranges behind the scenes for internal purposes. You won’t be able to find them through the Excel UI, but if they reference data marker cells, ExcelWriter will update them. Common cases where Excel created hidden named ranges are when the user applies an AutoFilter or AdvancedFilter to a range of cells. This occurs whether the filter is applied from the Excel UI or from a VBA macro. For this reason, check your macros carefully to make sure they are not applying any filters to unecessarily large cell ranges that include data markers. If you have any unnecessarily large hidden named ranges in your macros, you can modify them by changing the VBA code and running the macro again. If you are having trouble modifying or removing hidden named ranges, you can write some VBA code to do it. The names always end in “`_FilterDatabase”

Tip: If you have Excel 2007 or above, here is a quick way to see if you have any hidden named ranges in an .xls file without writing any VBA:

1. In Excel, save a copy of the template file as .xlsx or .xlsm
2. Rename to .zip and unzip the file
3. In the subdirectory “xl”, open the file workbook..xml
4. Look in the Section
5. Look for defined names that end in “_FilterDatabase”. Here is an example of something you might see: <definedName name="xlnm.FilterDatabase” localSheetId=”3″ hidden=”1″> Data!$A$1:$BQ$65000
6. If you temporarily change hidden to 0, it will show up in Excel under the named item manager.

Note: We don’t recommend permanently modifying or removing these hidden named ranges in the XML directly as that could have unexpected results.

Follow

Related posts: