Recent

Author Topic: Setting a column to date formats  (Read 707 times)

cov

  • Full Member
  • ***
  • Posts: 241
Setting a column to date formats
« on: April 09, 2021, 08:47:44 am »
Forgive me if I've missed something obvious but all my searching hasn't turned up an answer.

How do I set a column of cells in FPSpreadsheet to a particular date format?

If I write
Code: Pascal  [Select][+][-]
  1. Archive.WriteNumber(row,1,Now);
the current date is written to the spreadsheet, but, when the spreadsheet is opened, the value is 44295,3617921759. I want it to be formatted as today's date.

Many thanks.

~ Dave

Zvoni

  • Hero Member
  • *****
  • Posts: 550
Re: Setting a column to date formats
« Reply #1 on: April 09, 2021, 09:50:26 am »
WriteNumber is an overloaded Function

https://wiki.freepascal.org/FPSpreadsheet

Code: Pascal  [Select][+][-]
  1. function WriteNumber(ARow, ACol: Cardinal; ANumber: double): PCell; overload;
  2.     procedure WriteNumber(ACell: PCell; ANumber: Double); overload;
  3.     function WriteNumber(ARow, ACol: Cardinal; ANumber: double;
  4.       ANumFormat: TsNumberFormat; ADecimals: Byte = 2): PCell; overload;     {--> Now, what could be ANumFormat?}
  5.     procedure WriteNumber(ACell: PCell; ANumber: Double;                            
  6.       ANumFormat: TsNumberFormat; ADecimals: Byte = 2); overload;                {--> Now, what could be ANumFormat?}
  7.     function WriteNumber(ARow, ACol: Cardinal; ANumber: double;
  8.       ANumFormat: TsNumberFormat; ANumFormatString: String): PCell; overload;
  9.     procedure WriteNumber(ACell: PCell; ANumber: Double;
  10.       ANumFormat: TsNumberFormat; ANumFormatString: String); overload;
  11.  
One System to rule them all, One IDE to find them,
One Code to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------
People call me crazy, because i'm jumping out of perfectly fine aircraft

wp

  • Hero Member
  • *****
  • Posts: 8410
Re: Setting a column to date formats
« Reply #2 on: April 09, 2021, 02:15:02 pm »
If I write
Code: Pascal  [Select][+][-]
  1. Archive.WriteNumber(row,1,Now);
the current date is written to the spreadsheet, but, when the spreadsheet is opened, the value is 44295,3617921759. I want it to be formatted as today's date.
Unlike Excel, fpspreadsheet treats numbers and dates as different quantities, and there are different commands to write them to the worksheet. In order to write a date/time you must use one of the WriteDateTime overloads.
Code: Pascal  [Select][+][-]
  1. type
  2.   TsWorksheet = class(TsBasicWorksheet)
  3. ...
  4.     function WriteDateTime(ARow, ACol: Cardinal; AValue: TDateTime): PCell; overload;
  5.     procedure WriteDateTime(ACell: PCell; AValue: TDateTime); overload;
  6.     function WriteDateTime(ARow, ACol: Cardinal; AValue: TDateTime;
  7.       ANumFormat: TsNumberFormat; ANumFormatStr: String = ''): PCell; overload;
  8.     procedure WriteDateTime(ACell: PCell; AValue: TDateTime;
  9.       ANumFormat: TsNumberFormat; ANumFormatStr: String = ''); overload;
  10.     function WriteDateTime(ARow, ACol: Cardinal; AValue: TDateTime;
  11.       ANumFormatStr: String): PCell; overload;
  12.     procedure WriteDateTime(ACell: PCell; AValue: TDateTime;
  13.       ANumFormatStr: String); overload;

The NumFormat used here is one of the date/time nfXXXX constants (nfShortDateTime, nfShortDate, nfLongDate, nfShortTime, nfLongTime, nfShortTimeAM, nfLongTimeAM, nfDayMonth, nfMonthYear, nfTimeInterval), and the ANumFormatStr is a formatstring as used by FormatDateTime, such as 'dd/mm/yyyy',
Mainly Lazarus trunk / fpc 3.2.0 / all 32-bit on Win-10, but many more...

cov

  • Full Member
  • ***
  • Posts: 241
Re: Setting a column to date formats
« Reply #3 on: April 09, 2021, 10:37:37 pm »
Ok, thanks.

So I can't specify the column as formatted for date.

That's fine, formatting the cell should do the trick.

Thanks very much.

~Dave

wp

  • Hero Member
  • *****
  • Posts: 8410
Re: Setting a column to date formats
« Reply #4 on: April 09, 2021, 11:05:33 pm »
No you can, I just did not catch that you were talking of column formats.

The following demo creates a format record with a yellow background color and a date format of 'yyyy-mm-dd'. This format record is used for default formatting the column 1 of a worksheet. When you later add a date (or even a number) to this column the values are automatically formatted as 'yyyy-mm-dd'.

Code: Pascal  [Select][+][-]
  1. program Project1;
  2.  
  3. uses
  4.   fpspreadsheet, fpsutils, fpstypes, xlsxooxml;
  5. var
  6.   book: TsWorkbook;
  7.   sheet: TsWorksheet;
  8.   fmt: TsCellFormat;
  9.   fmtIndex: Integer;
  10. begin
  11.   book := TsWorkbook.Create;
  12.   try
  13.     sheet := book.AddWorksheet('Test');
  14.  
  15.     // Create a format record for yellow background and specific date format
  16.     InitFormatRecord(fmt);
  17.     fmt.NumberFormatIndex := book.AddNumberFormat('yyyy-mm-dd');
  18.     fmt.SetBackgroundColor(scYellow);
  19.     fmt.UsedformattingFields := fmt.UsedFormattingFields + [uffNumberFormat, uffBackground];
  20.     // Store format record in workbook
  21.     fmtIndex := book.AddCellFormat(fmt);
  22.     // Assign format record to entire column 1 of the worksheet
  23.     sheet.WriteColFormatIndex(1, fmtIndex);
  24.  
  25.     // Add numbers and dates to the pre-formatted column
  26.     sheet.WriteNumber(0, 1, 40000);
  27.     sheet.WriteDateTime(1, 1, 40000);
  28.  
  29.     // Save workbook to file
  30.     book.WriteToFile('test.xlsx', true);
  31.   finally
  32.     book.Free;
  33.   end;
  34. end.
Mainly Lazarus trunk / fpc 3.2.0 / all 32-bit on Win-10, but many more...

cov

  • Full Member
  • ***
  • Posts: 241
Re: Setting a column to date formats
« Reply #5 on: April 10, 2021, 02:40:23 pm »
Thanks.

Exactly what I'm after.  8)

cov

  • Full Member
  • ***
  • Posts: 241
Re: Setting a column to date formats
« Reply #6 on: April 10, 2021, 09:47:52 pm »
Where is InitFormatRecord defined?

I get:

unit1.pas(90,5) Error: Identifier not found "InitFormatRecord"

wp

  • Hero Member
  • *****
  • Posts: 8410
Re: Setting a column to date formats
« Reply #7 on: April 10, 2021, 09:56:44 pm »
fpsUtils. Most of these utility functions are there.
Mainly Lazarus trunk / fpc 3.2.0 / all 32-bit on Win-10, but many more...

cov

  • Full Member
  • ***
  • Posts: 241
Re: Setting a column to date formats
« Reply #8 on: April 11, 2021, 07:56:44 am »
Thanks.

I had put it on the wrong unit.

~Dave

 

TinyPortal © 2005-2018