Recent

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

seghele0

  • Full Member
  • ***
  • Posts: 173
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: 3197
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

  • Full Member
  • ***
  • Posts: 173
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: 11782
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 »

seghele0

  • Full Member
  • ***
  • Posts: 173
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: 11782
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);

seghele0

  • Full Member
  • ***
  • Posts: 173
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

  • Full Member
  • ***
  • Posts: 173
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.
 :)

majid.ebru

  • Sr. Member
  • ****
  • Posts: 491
Re: Export Stringgrid to an xlslx file.
« Reply #8 on: December 06, 2023, 07:14:01 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.

Hi
i know this topic is old but i have a question
is it possible to export stringgrid with style to excel ?
like this
« Last Edit: December 06, 2023, 07:23:15 pm by majid.ebru »

wp

  • Hero Member
  • *****
  • Posts: 11782
Re: Export Stringgrid to an xlslx file.
« Reply #9 on: December 07, 2023, 01:33:42 am »
What do you mean with "style"? The alternating row colors? The font? Gray background of the grids fixed cells? No Excel row and column headers (A, B, C, 1, 2, 3, ...)?

majid.ebru

  • Sr. Member
  • ****
  • Posts: 491
Re: Export Stringgrid to an xlslx file.
« Reply #10 on: December 07, 2023, 04:02:17 am »
All cases

majid.ebru

  • Sr. Member
  • ****
  • Posts: 491
Re: Export Stringgrid to an xlslx file.
« Reply #11 on: December 12, 2023, 08:37:10 am »
Hi

please help or guide me

wp

  • Hero Member
  • *****
  • Posts: 11782
Re: Export Stringgrid to an xlslx file.
« Reply #12 on: December 12, 2023, 11:38:16 am »
All cases
See attachment

please help or guide me
I don't know from which data this screenshot was created and what you are doing. My crystal ball is in the repair shop...

 

TinyPortal © 2005-2018