Recent

Author Topic: [Answered by wp] Saved csf file codepage problem  (Read 3096 times)

totya

  • Hero Member
  • *****
  • Posts: 720
[Answered by wp] Saved csf file codepage problem
« on: March 18, 2018, 09:24:56 pm »
Hi!

When I save WorksheetGrid to csv file, the produced csv file codepage is wrong, because the exel (2007) opened incorrectly.
It seems to mee, the produced csv file codepage is "UTF8 without boom" but excel need as I see: "UTF8 with boom". Thank you!

Code: Pascal  [Select][+][-]
  1. unit Unit1;
  2.  
  3. {$mode objfpc}{$H+}
  4.  
  5. interface
  6.  
  7. uses
  8.   Classes, SysUtils, FileUtil, Forms, Controls, Graphics, Dialogs,
  9.   fpspreadsheetgrid;
  10.  
  11. type
  12.  
  13.   { TForm1 }
  14.  
  15.   TForm1 = class(TForm)
  16.     sWorksheetGrid1: TsWorksheetGrid;
  17.     procedure FormCreate(Sender: TObject);
  18.   private
  19.     { private declarations }
  20.   public
  21.     { public declarations }
  22.   end;
  23.  
  24. var
  25.   Form1: TForm1;
  26.  
  27. implementation
  28.  
  29. {$R *.lfm}
  30.  
  31. { TForm1 }
  32.  
  33. procedure TForm1.FormCreate(Sender: TObject);
  34. begin
  35.   with sWorksheetGrid1 do
  36.   begin
  37.     sWorksheetGrid1.Worksheet.WriteText(0,0,'öüóőúéáűíÖÜÓŐÚÉÁŰÍ');
  38.     sWorksheetGrid1.SaveToSpreadsheetFile('test.csv');
  39.   end;
  40. end;
  41.  
  42. end.
  43.  

Edit.: But it seems to me, save csv file with ANSI codepage is better, because if I open "; tagged" (csv file) utf8 (with boom) file in Excel, then I save again, the result: ";" changed to tabulator. LOL.
« Last Edit: March 19, 2018, 12:14:17 am by totya »

wp

  • Hero Member
  • *****
  • Posts: 11856
Re: Saved csf file codepage problem
« Reply #1 on: March 18, 2018, 10:36:21 pm »
Since csv has so many "undefined" options fpspreadsheet declares a global record CSVParams in unit fpscsv in which you can define specific parameters:
Code: Pascal  [Select][+][-]
  1. type
  2.   TsCSVParams = record   // W = writing, R = reading, RW = reading/writing
  3.     SheetIndex: Integer;             // W: Index of the sheet to be written
  4.     LineEnding: TsCSVLineEnding;     // W: Specification for line ending to be written
  5.     Delimiter: Char;                 // RW: Column delimiter
  6.     QuoteChar: Char;                 // RW: Character for quoting texts
  7.     Encoding: String;                // RW: Encoding of file (code page, such as "utf8", "cp1252" etc)
  8.     DetectContentType: Boolean;      // R: try to convert strings to content types
  9.     NumberFormat: String;            // W: if empty write numbers like in sheet, otherwise use this format
  10.     AutoDetectNumberFormat: Boolean; // R: automatically detects decimal/thousand separator used in numbers
  11.     TrueText: String;                // RW: String for boolean TRUE
  12.     FalseText: String;               // RW: String for boolean FALSE
  13.     FormatSettings: TFormatSettings; // RW: add'l parameters for conversion
  14.   end;  
  15.  
  16. var
  17.   CSVParams: TsCSVParams = (
  18.     SheetIndex: 0;
  19.     LineEnding: leSystem;
  20.     Delimiter: ';';
  21.     QuoteChar: '"';
  22.     Encoding: '';    // '' = auto-detect when reading, UTF8 when writing
  23.     DetectContentType: true;
  24.     NumberFormat: '';
  25.     AutoDetectNumberFormat: true;
  26.     TrueText: 'TRUE';
  27.     FalseText: 'FALSE';
  28.   );  

Therefore, you can fine-tune the encoding of the file you just have to adjust CSVParams.Encoding accordingly before writing. So, if Excel wants "UTF8 with BOM", set CSVParams.Encoding := 'utf8bom', or set CSVParams.Encoding := 'cp1252' to write an ANSI file in the central-European codepage.

See also: http://wiki.lazarus.freepascal.org/FPSpreadsheet#Reading_and_writing_of_CSV_files

totya

  • Hero Member
  • *****
  • Posts: 720
Re: Saved csf file codepage problem
« Reply #2 on: March 18, 2018, 11:04:02 pm »
Hi!

Very thank you for answer!

I can try only tomorrow, but I suggest, change the default csv codepage from utf8-without-boom to utf8-with-boom. Why? Because I try open this csv file open from LibreOffice (v5.2.2.2) too, and codepage-detect is bad again. With utf8 with boom, the libre office autodetect is OK, like as Excel 2007.

Thank you! :)

wp

  • Hero Member
  • *****
  • Posts: 11856
Re: Saved csf file codepage problem
« Reply #3 on: March 19, 2018, 12:11:53 am »
At first I thought: Good idea, and uploaded a new version with utf8bom as default encoding for CSV files. But then I changed my mind and reverted this: fpspreadsheet is a library spreadsheet files, so if you want to create data for Excel or Calc you would of course write in their native format, not in the crooked CSV. On the other hand, if you write CSV files it is most probably for other programs, and I estimate that most of them will not expect to have a BOM at the beginning of a text file. Delphi 7, for example, chokes if you load a UTF8-BOM source file.

Luckily the situation is very flexible. If your program is supposed to write a CSV for Excel/Calc just change CSVParams.Encoding to 'utf8bom'.

totya

  • Hero Member
  • *****
  • Posts: 720
Re: Saved csf file codepage problem
« Reply #4 on: March 19, 2018, 12:13:57 am »
Hi!

I see, you changed the default codepage, and later you changed back... but no problem, these lines:

Code: Pascal  [Select][+][-]
  1. uses ...fpscsv...
  2.  

and

Code: Pascal  [Select][+][-]
  1. CSVParams.Encoding:= 'cp1250';
  2.  

... solve my problem.

And yes, my need cp1250 codepage, and not cp1252. With cp1252 few chars missing, see demo app in the first message.

Thank you for all, master!

ps.: Why I need csv file? Because I can compare excel files easily...:)

 

TinyPortal © 2005-2018