### Bookstore

 Computer Math and Games in Pascal (preview) Lazarus Handbook

### Author Topic: Setting a column to date formats  (Read 619 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: 549
##### Re: Setting a column to date formats
« Reply #1 on: April 09, 2021, 09:50:26 am »

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: 8398
##### 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;
12.     procedure WriteDateTime(ACell: PCell; AValue: TDateTime;

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: 8398
##### 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
5. var
6.   book: TsWorkbook;
7.   sheet: TsWorksheet;
8.   fmt: TsCellFormat;
9.   fmtIndex: Integer;
10. begin
11.   book := TsWorkbook.Create;
12.   try
14.
15.     // Create a format record for yellow background and specific date format
16.     InitFormatRecord(fmt);
18.     fmt.SetBackgroundColor(scYellow);
19.     fmt.UsedformattingFields := fmt.UsedFormattingFields + [uffNumberFormat, uffBackground];
20.     // Store format record in workbook
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.

#### 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:

#### wp

• Hero Member
• Posts: 8398
##### 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