fpspreadsheet large file

[wp]

Code: [Select]

Now i get a file (6 MB), But Excel doesn't open it.Maybe we still have issues with "forbidden" xml characters, or something like that. Try to write to sfExcel8. Can Excel open this xls file?

[Hansvb]

the formats "sfExcel2, sfExcel5, sfExcel8" work fine
I found some chars tht are not accepted in the "sfOOXML export": ë, ö etc.

[wp]

Sorry, I cannot reproduce this: The file (containing äöü) created by the following program can be read sucessfully by my Excel2007. Similarly, the ods version of the file can be opened by LibreOffice. Please run the program below and try to open the created xlsx files with Excel. Does it work? If not tell us more on your system: Excel version, which language? Operating system?

Could you post a file which shows the issue, as rvk proposed above?

To the experts: My file contains the umlauts directly, not by their HTML codes ("ä", etc.). Is this an issue for xml? If yes, is there a function somewhere in FPC/Lazarus which does the conversion of all non-standard characters to HTML codes?

Code: [Select]

program myooxmlwrite;

{$mode delphi}{$H+}

uses
  Classes, SysUtils, fpspreadsheet, fpsallformats, fpsutils;

var
  MyWorkbook: TsWorkbook;
  MyWorksheet: TsWorksheet;
  MyDir: string;

begin
  // Create the spreadsheet
  MyWorkbook := TsWorkbook.Create;
  MyWorksheet := MyWorkbook.AddWorksheet('My Worksheet');

  MyWorksheet.WriteUTF8Text(5, 5, 'äöü');

  // Save the spreadsheet to a file
  MyDir := ExtractFilePath(ParamStr(0));
  MyWorkbook.WriteToFile(MyDir + 'test.xlsx', sfOOXML);
  MyWorkbook.WriteToFile(MyDir + 'test.ods', sfOpenDocument);

  MyWorkbook.Free;
end. 


[rvk]

To the experts: My file contains the umlauts directly, not by their HTML codes ("ä", etc.). Is this an issue for xml? If yes, is there a function somewhere in FPC/Lazarus which does the conversion of all non-standard characters to HTML codes?

Both the .xls and .ods are readable by LibreOffice. You're using encoding="utf-8" in the xml header of both files so you should be able to use those UTF-8 characters directly. Maybe an older version of Excel needs a BOM in front of the XML-file. Let's wait and see what version of Excel we're dealing with here. If it's an older version maybe we could see a file from it with those characters and see if a BOM is put in front by Excel itself.

(Can you check if Excel 2007 puts a BOM in front of sharedStrings.xml? Maybe it does but can also read it without it, and older version really need it. Just guessing)
(never mind... Excel 2007 does not work with BOM's. Special characters are also written to sharedStrings.xml exactly like fpspreadsheet does.)

[Hansvb]

The example from wp works fine on my pc. Excel opens test.xlsx.

My Excel version is: Excel 2003. (I tried my file with Excel 2007 and it wil not open. The file generated with the code of WP works fine in Excel 2003)
I use Win 7 64 bit
I retrieve the data from aan mdb file Access 2003. Perhaps the problem is access.
I use Lazarus 1.2.4 32 bit with fpc 2.6.4

the attachment is an Excel file with 1 record. It does not open on my pc I am not allowed to  upload a xlsx file

[wp]

You are confusing me: xlsx was introduced with Excel2007. How can you open the text.xlsx in Excel2003?

Did you forget the attachment? If the forum software inhibits you from uploading particular files just pack them into a zip.

[Mike.Cornflake]

You are confusing me: xlsx was introduced with Excel2007. How can you open the text.xlsx in Excel2003?

By using the Microsoft Addin provided for earlier versions of office. (ie "2007 Microsoft Office system Compatibility Pack for Excel")

[wp]

Ah, now I remember - long time gone! But maybe this compatibility pack could cause our problem?

[Hansvb]

the compatibility pack is not the problem (i think) because excel 2007 doesn't open the file either.

see the attachment. the excel file containts 1 line and does not open.

[rvk]

From the sharedStrings.xml:

Code: [Select]

<si><t>A5AF48A3-3600-492F-80E5-190F67FE3213</t></si>
<si><t>Groen</t></si>
<si><t>麯t></si>
<si><t>Groen</t></si>
<si><t>Groen</t></si>
<si><t>HOOFDWEGEN-STRUCTUUR</t></si>

Could you explain what should be on that line with 麯 ??
(Could this be a bug in the bufferstream?)

Hans... could you try generating this file without the boBufStream in options?

I do have another question for wp:
I see a lot of empty cells in the sheet1.xml which Excel 2007 doesn't really write to the .xlsx file.

Code: [Select]

<c r="AA2" s="0"><v></v></c>
Are these really necessary?

[wp]

My impression is that the character at position 4239 in sharedstrings.xml (#$EB)  is the only non-standard character. I guess that Access from which the data were imported passes data in some unexpected codepage which adds illegal characters to the utf-8 stream.

Try to find out which code page is used by Access in the original table and use the appropriate conversion "CPXXXXToUTF8" from lConvEncoding.pas.

In a first try, call AnsiToUTF8 in the procedure writing text cells:

Code: [Select]

worksheet.WriteUTF8Text(j + 1, i, AnsiToUTF8(DBGrid1.DataSource.DataSet.Fields[i].AsString));

@rvk: Virtual mode works directly during the writing process. Therefore, it is in the responsibility of the user to suppress unnecessary cells. Of course, it could be argued that the writer should not write truely empty cell, i.e. cells with cell content cctEmpty, or string cells with empty strings, and without any formatting. I'm not sure if this is the right way to go - personally, I dislike programs which "think" too much...

[rvk]

Therefore, it is in the responsibility of the user to suppress unnecessary cells.

Then how would i go about suppressing these cells?
In WriteCellDataHandler i can set AValue to null to set it as cctEmpty but in that case there is still a blank cell written by TsSpreadOOXMLWriter.WriteBlank.
And once in WriteCellDataHandler i can't cancel the writing of the cell anymore 
(And fpspreadsheet dictates the order of the rows and columns. I can't just skip one column.)

[wp]

Your answer teaches me that I never have considered this case seriously... Of course, you are absolutely right.

The new revision 3603 skips writing of cells if the OnWriteCellData event handler sets the cell value to NULL and if the cell does not carry a format (by assigning a spreadsheet cell to the styleCell parameter).

I did not touch the ods format though because here writing of empty cells does not occur by NOT writing them but by counting consecutive blanks and specifying this number in the preceding existing cell or at row start as "number-columns-repeated". Due to the transient existence of the cell values in virtual mode this becomes quite complicated.

The case of blank cells is now contained in the demo_virtualmode_write project.

[rvk]

The new revision 3603 skips writing of cells if the OnWriteCellData event handler sets the cell value to NULL and if the cell does not carry a format (by assigning a spreadsheet cell to the styleCell parameter).

Yeah... That works 

[Hansvb]

i updated fpsreadheet with svn update to 3603. First i made an excel with the same code as ysterday. Excel is stil not openening the file. Then i made an excel file after i
removed boBufStream

Code: [Select]


workbook.Options := [boVirtualMode];//, boBufStream];

The file is stil not opend by Excel.
see the attachemnt, the zip containts 2 files.  The xls file has the same line as the the xlsx file so you can see wat is in it. (The xls file works fine).