Recent

Author Topic: XLSM and fpspreadsheet  (Read 2695 times)

uli65

  • New Member
  • *
  • Posts: 12
XLSM and fpspreadsheet
« on: March 30, 2018, 04:10:03 pm »
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
Lazarus 2.0.6, FPC 3.0.4, Unidac 8.12

wp

  • Hero Member
  • *****
  • Posts: 7636
Re: XLSM and fpspreadsheet
« Reply #1 on: March 30, 2018, 04:14:40 pm »
Macros are not supported by fpspreadsheet.
Mainly Lazarus trunk / fpc 3.2.0 / all 32-bit on Win-10, but many more...

Blue_Painted

  • New Member
  • *
  • Posts: 16
Re: XLSM and fpspreadsheet
« Reply #2 on: December 24, 2019, 10:09:17 am »
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

  • Full Member
  • ***
  • Posts: 190
Re: XLSM and fpspreadsheet
« Reply #3 on: January 01, 2020, 11:08:15 am »
How 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

  • Hero Member
  • *****
  • Posts: 7636
Re: XLSM and fpspreadsheet
« Reply #4 on: January 01, 2020, 05:56:30 pm »
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:
Quote
XLSM 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][+][-]
  1. program read_xlsm;
  2.  
  3. uses
  4.   SysUtils,
  5.   fpstypes, fpspreadsheet, fpsutils, fpsreaderwriter, xlsxooxml;
  6.  
  7. var
  8.   wb: TsWorkbook;
  9.   sh: TsWorksheet;
  10.   xlsmID: Integer;
  11.   cell: PCell;
  12.  
  13. begin
  14.   // Register the xlsm file format
  15.   xlsmID := RegisterSpreadFormat(
  16.     sfUser,  // indicator that a user-defined format is registered
  17.     TsSpreadOOXMLReader,  // class providing the file reader code
  18.     nil,                  // class providing the file writer code - we don't need it.
  19.     'Excel 2007+ Macro-Enabled XML',    // Item in file format combobox
  20.     'OOXML',                            // Short abbreviation of the format name
  21.     ['.xlsm']                           // Extension(s) of the format
  22.   );
  23.  
  24.   // Open a sample xlsm file
  25.   wb := TsWorkbook.Create;
  26.   try
  27.     wb.ReadFromFile('sample.xlsm', xlsmID);
  28.     sh := wb.GetFirstWorksheet;
  29.     // Display the cells
  30.     for cell in sh.Cells do
  31.       WriteLn(Format('%s: %s', [GetCellString(cell^.Row, cell^.Col), sh.ReadAsText(cell)]));
  32.   finally
  33.     wb.Free;
  34.   end;
  35.  
  36.   ReadLn;
  37. end.
Mainly Lazarus trunk / fpc 3.2.0 / all 32-bit on Win-10, but many more...

Blue_Painted

  • New Member
  • *
  • Posts: 16
Re: XLSM and fpspreadsheet
« Reply #5 on: January 05, 2020, 08:32:27 am »
How 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)

 

TinyPortal © 2005-2018