0

I have a code to export my data grid to excell using BIFF4. The problem is it cant accept rows beyond 65536. I asuume it is because the row index is word and it is of 2 bytes. Is there any way i could write more than the available row count? any workaround is possible? any help will be appreciated.

Jeeva
  • 4,585
  • 2
  • 32
  • 56
  • 2
    no. you can't. other than using multiple worksheets, you cannot break a fundamental limit in excel. it'd be like asking how to represent `8 billion` in a 32bit var. If you upgrade to the OOXML format (.xlsx), you can use up to 1 million rows. – Marc B Feb 19 '13 at 04:00
  • Using an output format like CSV seems like an obvious workaround – barrowc Feb 19 '13 at 08:16
  • @MarcB: Assume if upgrade to BIFF5 or anything greater than that will i be able to export more rows? – Jeeva Feb 21 '13 at 06:28
  • No. the biff formats all have basically the same limits. 64k max for biff5/biff8. for 1million rows, you need the xml-based version from 2007+ (.xlsx). – Marc B Feb 21 '13 at 15:08

2 Answers2

2

If you have to use BIFF, the best thing to do is split the data into multiple sheets.

Aviva M.
  • 381
  • 1
  • 9
0

In Excel 97-2003, the row limit is 65535 per sheet. It should be the default BIFF row limit.

Tony Qu
  • 676
  • 8
  • 14