All worksheet save to csv [ANSWERED]
lazarus / 49-FPSpreadsheet

----------------------------------------------------------------------
totya · 2020-07-22 11:29

Hi master!

I can save excel file as csv, via Workbook.WriteToFile(CSVFileName,
True);

But I have an excel file with about 30 worksheet, and I want to save
all of them to the csv.
Can I set worksheet (index/name) anyhow to save?

----------------------------------------------------------------------
totya · 2020-07-22 13:35

I wrote own method:

Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i unit
unit_tsworkbook_helper; {$mode objfpc}{$H+} interface uses  Classes,
SysUtils,  fpstypes, fpspreadsheet, fpsutils,
{%H-}fpsAllFormats; type  TsWorkBookHelper = class helper for
TsWorkBook  public    procedure
myWriteWorkbookAllWorksheetToCSVFile(const ADestDirectory: string;   
 AOverwrite: boolean);  end; implementation procedure
TsWorkBookHelper.myWriteWorkbookAllWorksheetToCSVFile(  const
ADestDirectory: string; AOverwrite: boolean);const  CSVExtension =
'.csv';var  i: integer;  WB: TSWorkBook;  WS: TSWorkSheet;begin  for i
:= 0 to GetWorksheetCount - 1 do  begin    WS :=
GetWorksheetByIndex(i);     WB := nil;    WB := TSWorkBook.Create;   
try      WB.CopyWorksheetFrom(WS, False);      
WB.WriteToFile(IncludeTrailingPathDelimiter(ADestDirectory) +       
WS.Name + CSVExtension, AOverwrite);    finally      if Assigned(WB)
then        WB.Free;    end;  end;end; end.

----------------------------------------------------------------------
wp · 2020-07-22 14:21

No need to copy the worksheets to an temporary woorkbook. In unit
fpsCSV, there is a global record CSVParams in which you can determine
a variety of parameters for writing (and reading), among them the
index of the page to be written:

Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i type 
TsCSVLineEnding = (leSystem, leCRLF, leCR, leLF);   TsCSVParams =
record   // W = writing, R = reading, RW = reading/writing   
SheetIndex: Integer;             // W: Index of the sheet to be
written    LineEnding: TsCSVLineEnding;     // W: Specification for
line ending to be written    Delimiter: Char;                 // RW:
Column delimiter    QuoteChar: Char;                 // RW: Character
for quoting texts    Encoding: String;                // RW: Encoding
of file (code page, such as "utf8", "cp1252" etc)   
DetectContentType: Boolean;      // R: try to convert strings to
content types    NumberFormat: String;            // W: if empty write
numbers like in sheet, otherwise use this format   
AutoDetectNumberFormat: Boolean; // R: automatically detects
decimal/thousand separator used in numbers    TrueText: String;      
        // RW: String for boolean TRUE    FalseText: String;         
    // RW: String for boolean FALSE    FormatSettings:
TFormatSettings; // RW: add'l parameters for conversion  end; var 
CSVParams: TsCSVParams = (    SheetIndex: 0;    LineEnding: leSystem; 
  Delimiter: ';';    QuoteChar: '"';    Encoding: '';    // '' = auto-
detect when reading, UTF8 when writing    DetectContentType: true;   
NumberFormat: '';    AutoDetectNumberFormat: true;    TrueText:
'TRUE';    FalseText: 'FALSE';  {%H-});   
Here is a little demo with creates a workbook with two sheets, one
with a table of the sin(x) values, the other one with the cos(x)
values. Each of the sheets is written to a separate file with a
filename given by a common prefix plus the sheet name. Additionally
there is also a variant which writes all sheets to the same file,
separated by some dividing string.

Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i program
Project1; uses  Classes, fpspreadsheet, fpstypes, fpscsv,
xlsxOOXML; var  book: TsWorkbook;  sheet: TsWorksheet;  i: Integer; 
r: Integer;  x: Double;  stream: TFileStream;  divider: String;begin 
book := TsWorkbook.Create;  try    // Prepare dummy data, tables of
sin(x) and cos(x), each one on its own sheet.    sheet :=
book.AddWorksheet('sin_x');    sheet.WriteText(0, 0, 'x');   
sheet.WriteText(0, 1, 'sin(x)');    for r := 1 to 30 do    begin     
x := (r-1) / 29 * 2*pi;      sheet.WriteNumber(r, 0, x, nfFixed, 6); 
   sheet.WriteNumber(r, 1, sin(x), nfFixed, 6);    end;     sheet :=
book.AddWorksheet('cos_x');    sheet.WriteText(0, 0, 'x');   
sheet.WriteText(0, 1, 'cos(x)');    for r := 1 to 30 do    begin     
x := (r-1) / 29 * 2*pi;      sheet.WriteNumber(r, 0, x, nfFixed, 6); 
   sheet.WriteNumber(r, 1, cos(x), nfFixed, 6);    end;     // Save to
xlsx for verification    book.WriteToFile('data.xlsx', sfOOXML,
true);     // Save to CSV, one file per page    CSVParams.Delimiter :=
#9;    for i := 0 to book.GetWorksheetCount-1 do    begin     
CSVParams.SheetIndex := i;      book.WriteToFile('data_' +
book.GetWorksheetByIndex(i).Name + '.csv', sfCSV, true);    end;    
// Save to CSV, one file containing all pages    divider := LineEnding
+ '===============================' + LineEnding + LineEnding;   
stream := TFileStream.Create('data_all.csv', fmCreate);    try     
for i := 0 to book.GetWorksheetCount-1 do      begin       
CSVParams.SheetIndex := i;        book.WriteToStream(stream, sfCSV); 
     // write dividing line between sheets        if i <>
book.GetWorksheetCount-1 then          stream.Write(divider[1],
Length(divider));      end;    finally      stream.Free;    end;  
finally    book.Free;  end;end.

----------------------------------------------------------------------
totya · 2020-07-22 18:40

Thanks master! Otherwise the new method is slightly slower:

Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i procedure
TsWorkBookHelper.myWriteWorkbookAllWorksheetToCSVFile(  const
ADestDirectory: string; AOverwrite: boolean);const  CSVExtension =
'.csv';var  i: integer;  OldIndex: integer;begin  OldIndex :=
CSVParams.SheetIndex;  try    for i := 0 to GetWorksheetCount - 1 do 
 begin      CSVParams.SheetIndex := i;     
WriteToFile(IncludeTrailingPathDelimiter(ADestDirectory) +       
GetWorksheetByIndex(i).Name + CSVExtension, sfCSV, AOverwrite);   
end;  finally    CSVParams.SheetIndex := OldIndex;  end;end;