fpspreadsheet large file

[wp]

Again not attachment... Did you try the AnsiToUTF8?

[EDIT] Another question: How do you open the Access database from which you create the spreadsheet file?

[rvk]

The file is stil not opend by Excel.

Could you show us the exact code in your WriteCellDataHandler.

(Did you use AnsiToUTF8 like wp suggested?)

[Hansvb]

Here is the code:

Code: [Select]

procedure TMain_frm.Bt_test_export_to_xlsxClick(Sender: TObject);
var
  workbook: TsWorkbook;
  worksheet: TsWorksheet;
  t: TTime;
begin
  try
    workbook := TsWorkbook.Create;
    try
      worksheet := workbook.AddWorksheet('Sheet1');
//      worksheet.WriteFontStyle(0, 1, [fssBold]);

      workbook.Options := [boVirtualMode, boBufStream];

      workbook.VirtualColCount := DBGrid1.DataSource.DataSet.FieldCount;
      workbook.VirtualRowCount := 2; //DBGrid1.DataSource.DataSet.RecordCount;

      workbook.OnWriteCellData := @WriteCellDataHandler;

      t := Now;
      workbook.WriteToFile('test_virtual_01.xlsx', sfOOXML, true);
      //workbook.WriteToFile('test_virtual_02.xls', sfExcel2, true);

      t := Now - t;
    finally
      workbook.Free;
    end;
  finally
    //
  end;
end;   


Code: [Select]

procedure TMain_frm.WriteCellDataHandler(Sender: TObject; ARow, ACol: Cardinal; var AValue: variant; var AStyleCell: PCell);
begin
  if ARow = 0 then begin
    AValue :=  DBGrid1.DataSource.DataSet.Fields[ACol].FieldName;   //MyDatabase.Fields[ACol].FieldName;
    DBGrid1.DataSource.DataSet.First;
  end else begin
    AValue := DBGrid1.DataSource.DataSet.Fields[ACol].AsVariant;
    if ACol = DBGrid1.DataSource.DataSet.FieldCount-1 then DBGrid1.DataSource.DataSet.Next;
  end;
end;


The Access file is opened with a odbc connection.
paramaters:
Persist Security Info=False;
Extended Properties="DSN=MS Access Database;
DBQ=d:\Programma\Oefen\DBase\test.mdb;
DefaultDir=d:\Programma\Oefen\DBase;
DriverId=25;
FIL=MS Access;
MaxBufferSize=2048;
PageTimeout=5;
UID=admin;

[rvk]

So, yeah... you didn't add the AnsiToUTF8 like wp suggested 
You database connection is probably in a ISO-characterset.
You need to convert the characterset to UTF8.

You could try something like below.
(I check if the Field is a ftString or equivalent and if that's the case convert it to UTF8. I also check if the string is empty, in that case we can set it to NULL so the cell is really empty and not included in the .xlsx file itself. You need at least revision 3603 for that last one).

Code: [Select]

procedure TForm1.WriteCellDataHandler(Sender: TObject; ARow, ACol: Cardinal; var AValue: variant; var AStyleCell: PCell);
begin
  if ARow = 0 then begin
    AValue :=  DBGrid1.DataSource.DataSet.Fields[ACol].FieldName;   //MyDatabase.Fields[ACol].FieldName;
    DBGrid1.DataSource.DataSet.First;
  end else begin
    AValue := DBGrid1.DataSource.DataSet.Fields[ACol].AsVariant;

    // Add this
    if DBGrid1.DataSource.DataSet.Fields[ACol].DataType in [ftString, ftFixedChar, ftWideString, ftFixedWideChar] then
    begin
      AValue := AnsiToUTF8(DBGrid1.DataSource.DataSet.Fields[ACol].asString);
      if AValue = '' then AValue := null;
    end;
    // ^^^^^^^^^^^^^^^

    if ACol = DBGrid1.DataSource.DataSet.FieldCount-1 then DBGrid1.DataSource.DataSet.Next;
  end;
end;


[Hansvb]

AnsiToUTF8 does not make a difference.

iI made a new table with 1 record.  When the value of the records is "some text" then de excel file works. When the value of the records is  "ë" the excel file does not work AND the dbgrid does not show the value "e". Perhaps  it is not fpspreatsheet but some where else where it goes wrong.

 

[rvk]

Perhaps  it is not fpspreatsheet but some where else where it goes wrong.

It's definitively not fpspreadsheet but a problem with converting between the character sets.
As wp already stated the file contains a character #$EB, which is ë in your character set, but isn't in UTF-8. (You should only dump UTF-8 characters into fpspreadsheet but you are responsible for converting it to UTF-8)

First, to be sure, could you check if the code in the "if ... in [ftString...etc" is actually executed (you could use a breakpoint to see if it gets there).
Second, could you post the .xlsx-file again so we could check if the character #$EB is changed to something else.

Maybe you need another function, different from AnsiToUTF8.
Best would be if you could determine your character set of your database(-connection).

[wp]

I don't have ODBC available here, and I don't know the exact procedure.

But you could go to Windows' Control panel -> Administrative tools -> Data sources (or similar). In the appearing dialog open the ODBC driver for Access, Configure or Properties (or similar) - somewhere you should find a specification of the code page or character set used by this driver. If there is an option for UTF-8 select this one. (Not sure if this has an effect on the export or on the strings expected in the DB - make a backup copy of your mdb file before you change anything here).

If not try to find one for which a conversion procedure exists in unit lConvEncoding. Add lConvEncoding to the uses clause of your spreadsheet writing unit, left-click on this name with the CTRL key down - this opens this unit. Scroll down a bit and you see the conversion procedures which convert characters from a code page to UTF8. Select the one you found in the ODBC configuration. Or you can just test several variants: You seem to be from Europe, maybe CP1250ToUTF8() is what you need. Or ISO_8859_1ToUTF8(), or ISO_8859_15ToUTF8(), or CP437ToUTF8(), etc. Use these procedures in the code posted by rvk instead of AnsiToUTF8.

[Mike.Cornflake]

Wildly guess you're using SQLDB.   In your (again - guessing) TODBCOnnection, set CharSet := 'UTF8'

[Hansvb]

The accessdatabase was only to try fpspreadsheet. The real export to Excel is from an Oracle database. There i use zconnection en ztquery. the controlscodepage is set to: cCP_UTF8 and the client codepage is set to: UTF8

With the same result. The file wil not open in Excel.

[wp]

I am clueless...

Could you strip down the Access database file to a reasonable size and post it here?

[Hansvb]

WP, see the attachment. It is a new project with only the export fumnction and a acces database with one table and 3 records
in the dbgrid. I cant't see the second record in the dbgrid. And that is the records that gives the problem in the export.

[wp]

If I take the field value as a widestring, convert it to UTF8, and assign this to the variant, I get an Excel-readable file:

Code: [Select]

procedure TForm1.WriteCellDataHandler(Sender: TObject; ARow, ACol: Cardinal;
  var AValue: variant; var AStyleCell: PCell);
var
  s: String;
begin
  if ARow = 0 then begin
    AValue :=  DBGrid1.DataSource.DataSet.Fields[ACol].FieldName;
    DBGrid1.DataSource.DataSet.First;
  end else begin
    s := UTF8Encode(DBGrid1.DataSource.DataSet.Fields[ACol].AsWideString);
    if s = '' then
      AValue := NULL
    else
      AValue := s;
    if ACol = DBGrid1.DataSource.DataSet.FieldCount-1 then DBGrid1.DataSource.DataSet.Next;
  end;
end;

Please note that your VirtualRowCount does not take account of the header line that you are writing, and therefore, you are missing the last record in the Excel file:

Code: [Select]

  workbook.VirtualRowCount := DBGrid1.DataSource.DataSet.RecordCount + 1;

Regarding the DBGrid, I found this discussion in the forum (http://forum.lazarus.freepascal.org/index.php/topic,16993.msg93289.html#msg93289), and after doing this the second record was visible in the grid.

[Hansvb]

Thanks

I tried UTF8Encode and widestring but not together!
I still don't get it when to use utf8, UTF8Encode, widesting etc.

The export from Access works fine. Ik will try Oracle this week when i am at work.

[wp]

Code: [Select]

I still don't get it when to use utf8, UTF8Encode, widesting etc.You're in good company - strings have become the most difficult chapter of Pascal...

You may want to read this:
http://www.joelonsoftware.com/articles/Unicode.html - very good reading!
http://wiki.freepascal.org/Character_and_string_types

[Hansvb]

I will read it. Thanks.

I tested it on Oracle and i am able to make excel file wich will not open. It took me a while to search the (100.000+) records to find the record which causes the problem. The value "dc3 standaard" is the problem. I Oracle i see a smal square and then the text "standard" when i open the csv file in excel I see the same. (A square and then the text. When i open the csv on an other pc is see "!! standaard".

See the attachement. In the tabel is the export from the value which causes the problem. (Direct export from Oracle to Access with TOAD). In Access the value is also "!! standaard".

(I googled on dc3. this means CTRL+S)