Recent

Author Topic: Export Stringgrid to an xlslx file.  (Read 997 times)

seghele0

  • Jr. Member
  • **
  • Posts: 50
Export Stringgrid to an xlslx file.
« on: September 18, 2021, 03:31:21 pm »
Using: Windows10 + Lazarus.
I hope to find a programmer willing to provide me the code to export the contents of a Stringgrid to an xlsx file.
A simple 'form' with one stringgrid and one button to activate the export.
This is far too complex for my low level of programming.
Thanks already.

winni

  • Hero Member
  • *****
  • Posts: 2714
Re: Export Stringgrid to an xlslx file.
« Reply #1 on: September 18, 2021, 04:03:36 pm »
Hi!

Save the StringGrid to a CSV File:

Code: Pascal  [Select][+][-]
  1. StringGrid1.SaveToCSVFile('Filename.csv');

And then load the file into Excel.

Winni

seghele0

  • Jr. Member
  • **
  • Posts: 50
Re: Export Stringgrid to an xlslx file.
« Reply #2 on: September 18, 2021, 04:14:04 pm »
Thanks, but it's not for a CSV, but for an XLSX.
I know I can open a CSV with Excel, but would like an XLSX file right away.
 :-[

 

wp

  • Hero Member
  • *****
  • Posts: 8905
Re: Export Stringgrid to an xlslx file.
« Reply #3 on: September 18, 2021, 04:32:24 pm »
Use fpspreadsheet; you can install it via the Online-Package-Manager. There's lots of documentation in the wiki:

To get you started I am attaching a simple demo which exports a stringgrid to xlsx (or LibreOffice ods if you uncomment the corresponding line). The demo assumes that there are specific data types in each column and converts the strings of the StringGrid to corresponding types so that the xlsx file contains real numbers or dates rather than strings.
« Last Edit: September 18, 2021, 04:35:18 pm by wp »
Mainly Lazarus trunk / fpc 3.2.0 / all 32-bit on Win-10, but many more...

seghele0

  • Jr. Member
  • **
  • Posts: 50
Re: Export Stringgrid to an xlslx file.
« Reply #4 on: September 18, 2021, 05:27:02 pm »
Hero Member, thanks for the info.
Your code works fine, but the're only numbers in my application, positive and negative (-5;15;-5;-5)
What and where do I need to adjust?
Thanks.
 :)


wp

  • Hero Member
  • *****
  • Posts: 8905
Re: Export Stringgrid to an xlslx file.
« Reply #5 on: September 18, 2021, 05:47:30 pm »
When you only have Integers (no decimal separator) you can export them with the code shown in the demo for column 0.
Code: Pascal  [Select][+][-]
  1.   worksheet.WriteNumber(rowindex, colindex, StrToInt(StringGrid1.Cells[col, row]), nfGeneral);

For floating point values (with decimal separator) you use basically the same code, but replace StrToInt by StrToFloat which works fine when the StringGrid cells contain the decimal separators of your system. If not you should replace it by the function MyStrToFloat of the demo which accepts both point and comma as decimalseparator.

The last parameter, nfGeneral, means that the cells will be in the xlsx file as unformatted numbers. Replace it by nfFixed, 2 to format the numbers with two decimal places (or similar), e.g
Code: Pascal  [Select][+][-]
  1.   worksheet.WriteNumber(rowindex, colindex, StrToFloat(StringGrid1.Cells[col, row]), nfFixed, 2);
Mainly Lazarus trunk / fpc 3.2.0 / all 32-bit on Win-10, but many more...

seghele0

  • Jr. Member
  • **
  • Posts: 50
Re: Export Stringgrid to an xlslx file.
« Reply #6 on: September 19, 2021, 10:42:55 am »
Thanks ..... I'll be back in a few days with the result.
 ;)

seghele0

  • Jr. Member
  • **
  • Posts: 50
Re: Export Stringgrid to an xlslx file.
« Reply #7 on: September 20, 2021, 12:47:26 pm »
WP
May I thank you very much for sending your code (demo application) and the accompanying explanation.
Without your support I personally wouldn't have found out how to solve it.
Your support makes my program even more efficient.
Thank you, thank you.
 :)

 

TinyPortal © 2005-2018