XLSM and fpspreadsheet
lazarus / 49-FPSpreadsheet

----------------------------------------------------------------------
uli65 · 2018-03-30 14:10

Hello,

I am looking for a solution to the following problem: I want to open
an XLSM file, modify a spreadsheet it contains, and then save it again
(using the fpspreadsheet component).
Opening the file and changing the data is not a problem, but saving
the file always causes the spreadsheet to be changed in the desired
form, but the macros disappear from the file and subsequently only
have an XLSX file format.
Is there an example that demonstrates the correct handling of files in
XLSM format?

Best regards
Uli65

----------------------------------------------------------------------
wp · 2018-03-30 14:14

Macros are not supported by fpspreadsheet.

----------------------------------------------------------------------
Blue_Painted · 2019-12-24 09:09

How do you open XLSM files?

This is my code snippet and I always get "... is not a valid
spreadsheet"


     WB:= TsWorkbook.Create;
     try
        WB.ReadFromFile(AFilename);

----------------------------------------------------------------------
kjteng · 2020-01-01 10:08

Quote from: Blue_Painted on December 24, 2019, 10:09:17 amHow do you
open XLSM files?


1. You need to add to the uses clause: fpsallformats
2. Open the file by either:-
      WorkBookSource.FileName := AFileName;
         OR
      Workbook.ReadFromFile(AFilename, sfOOXML);
 
NOTE: As mentioned by WP, macro is not supported in fpSpread. i.e.
macro in the original file would not be read into fpSpreadsheet.

----------------------------------------------------------------------
wp · 2020-01-01 16:56

I must say: I never use Excel macros - when an analysis becomes so
complicated that macros would be justified I don't use Excel at all.
Therefore, I never considered to implement xlsm files for
fpspreadsheet.

But this site makes me wonder: https://www.lifewire.com/xlsm-
file-2622538. They say here:
QuoteXLSM files are actually identical to Microsoft Excel Open XML
Format Spreadsheet (XLSX) files with the only difference being that
XLSM files will execute embedded macros that are programmed in the
Visual Basic for Applications (VBA) language.So, I'd only have to
allow the xlsxm extension to be accepted by fpspreadsheet and xlsm
files could be read - no other change needed... BUT: Macros will never
(?) be executed by fpspreadsheet. Therefore, xlsm files will only
provide the data part, not the code part which certainly is existent
here because otherwise the file would have been written with its
default extension. For this reason - because fpspreadsheet will not
handle xlsm files as expected - I think it is not a good idea to
officially support this file extension.

But fpspreadsheet can be extended to user-defined file formats. The
essential part is the file reader and  writer code -- but we already
have it, it is the standard ooxml unit containing the
TsSpreadXLSXReader and TsSpreadXLSXWriter (we don't need the latter
one). All what has to be done is to call "RegisterSpreadFormat()" (in
unit fpsReaderWriter) with the appropriate parameters. The format id
returned by the registration is the format parameter to be used in the
ReadFromFile() method of the workbook. The workbook then looks up the
format id in an internal list and finds the reader class this way.

Therefore, the following code for reading xlsm works:
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
read_xlsm; uses  SysUtils,  fpstypes, fpspreadsheet, fpsutils,
fpsreaderwriter, xlsxooxml; var  wb: TsWorkbook;  sh: TsWorksheet; 
xlsmID: Integer;  cell: PCell; begin  // Register the xlsm file
format  xlsmID := RegisterSpreadFormat(    sfUser,  // indicator that
a user-defined format is registered    TsSpreadOOXMLReader,  // class
providing the file reader code    nil,                  // class
providing the file writer code - we don't need it.    'Excel 2007+
Macro-Enabled XML',    // Item in file format combobox    'OOXML',   
                       // Short abbreviation of the format name   
['.xlsm']                           // Extension(s) of the format 
);   // Open a sample xlsm file  wb := TsWorkbook.Create;  try   
wb.ReadFromFile('sample.xlsm', xlsmID);     sh :=
wb.GetFirstWorksheet;    // Display the cells    for cell in sh.Cells
do      WriteLn(Format('%s: %s', [GetCellString(cell^.Row, cell^.Col),
sh.ReadAsText(cell)]));  finally    wb.Free;  end;   ReadLn;end.

----------------------------------------------------------------------
Blue_Painted · 2020-01-05 07:32

Quote from: kjteng on January 01, 2020, 11:08:15 amQuote from:
Blue_Painted on December 24, 2019, 10:09:17 amHow do you open XLSM
files?


1. You need to add to the uses clause: fpsallformats
2. Open the file by either:-
      WorkBookSource.FileName := AFileName;
         OR
      Workbook.ReadFromFile(AFilename, sfOOXML);
 
NOTE: As mentioned by WP, macro is not supported in fpSpread. i.e.
macro in the original file would not be read into fpSpreadsheet.

This was what I was missing, TVM!

(My first time using fpspreadsheet)