1

I have a requirement on exporting Database records to excel file in my application. For this purpose i am using a light weight class from a blog External Link to Blog

It works fine.. But I am facing a problem.. If String length is more than Say 200, then it is not inserting data to the cell. I want to increase amount of data a cell can hold.. Does anybody have any idea, how it can be done normally using PHP

Thanks for help in advance

Codemator
  • 513
  • 1
  • 10
  • 19

1 Answers1

3

Excel has limits on the amount of data a cell can hold: for Excel BIFF 8 files, that limit is 32,767 characters, so (in theory) 200+ characters should not be an issue. However, for longer strings, this data is maintained in the BIFF file across several blocks with continuation records, For BIFF 5 files (Excel 95) the limit is 2084 bytes per block; in BIFF 8 files (Excel 97 and above) the limit is 8228 bytes. Records that are longer than these limits must be split up into CONTINUE blocks.

This relatively simplistic writer isn't written to handle splitting the record into multiple continuation records: it doesn't even use the BIFF 8 shared string table, or indicate what BIFF version it is writing (which means Excel will open it using lowest common denominator parameters). It simply tries to store the entire contents of the cell into a standard label block (which has a limit of 255 bytes). To fix this, you'd need to fix the library that you're using to handle splitting the string values with continuation blocks (via a shared string table); or switch to a library that does handle splitting the shared strings across multiple blocks already.

Mark Baker
  • 209,507
  • 32
  • 346
  • 385
  • Can any you give some examples. Or please suggest me some good PHP library which is light weight – Codemator Oct 02 '12 at 07:52
  • I'd recommend PHPExcel (http://www.phpexcel.net) because I've written much of it myself; though it's relatively heavyweight. If you want something lightweight, there's a whole list of options for reading and writing Excel files provided in the answer here http://stackoverflow.com/questions/3930975/alternative-for-php-excel but lightweight comes at a cost (doesn't always do everything you want, and may not handle certain data types as your original library demonstrates) – Mark Baker Oct 02 '12 at 07:55
  • Oh.. Its my privilege to have my questions answered by you.. I love your library.. Thanks for your time – Codemator Oct 02 '12 at 08:30
  • Thanks for posting this answer. It helps to understand issues I have encountered not related to php/Excel use. – Hamp Sep 09 '13 at 19:04
  • @MarkBaker: My application is in delphi and we have our own library could you please let me know how can we write strings which are larger in BIFF 6. – Jeeva Oct 18 '13 at 05:05
  • @Jeeva - The easiest way is to use SST (Shared String Table) records – Mark Baker Oct 18 '13 at 08:28
  • http://msdn.microsoft.com/en-us/library/cc313154(v=office.12).aspx or http://download.microsoft.com/download/2/4/8/24862317-78F0-4C4B-B355-C7B2C1D997DB/[MS-XLS].pdf – Mark Baker Oct 18 '13 at 09:56
  • @MarkBaker: I understand from your response that the limit is 200+ character. Infact we are exporting 260 char but that itself is a problem with BIFF6. Do you see anything wrong with that? We even mention the correct version. – Jeeva Oct 21 '13 at 07:20
  • A standard text block is limited to 255 characters. To hold more, you need to split the text the first part held in a standard block, then store the remainder in one or more extension blocks – Mark Baker Oct 21 '13 at 07:39