XLS DB Read - formatting cells

[kayeng]

Hi,

I am in the process of evaluating XLSReadWriteII 5 and have run into the following issue:

Consider the code below:

Code: Select all

  TestDataset := TClientDataset.Create(nil);
  TestDataset.FieldDefs.Add('Name', ftString, 20);
  TestDataset.FieldDefs.Add('Address', ftString, 100);
  TestDataset.FieldDefs.Add('Balance', ftCurrency);
  TestDataset.FieldDefs.Add('DOB', ftDate);
  TestDataset.CreateDataSet;

  TestDataset.AppendRecord(['John Smith', '123 Fake Street', 244.50, IncMonth(Date, -200)]);
  TestDataset.AppendRecord(['Elva May Ace', '244 Elms Street', 2000, IncMonth(Date, -50)]);
  TestDataset.AppendRecord(['John Jenkinson', '55 Grace Avenue', 100, IncMonth(Date, -25)]);

  Sheet1 := XLS.Sheets[0];

  XLSDBRead := TXLSDBRead5.Create(nil);
  XLSDBRead.Dataset := TestDataset;
  XLSDBRead.IncludeFieldnames := True;
  XLSDBRead.Sheet := XLS.Sheets[0].Index;
  XLSDBRead.XLS := XLS;
  XLSDBRead.Read;

  for Index := 0 to TestDataset.FieldCount - 1 do begin
    case TestDataset.FieldDefList.FieldDefs[Index].DataType of
      ftDateTime, ftDate: begin
        Sheet1.Columns[Index].NumberFormat := 'DD-MMM-YYYY'; 
      end;
      ftCurrency: begin
        Sheet1.Columns[Index].NumberFormat := '[$£-809]#,##0.00;-[$£-809]#,##0.00'; 
        Sheet1.Columns[Index].HorizAlignment := chaRight;
      end;
    end;
  end;

  Sheet1.Name := 'Contributions to Pension';
  XLS.FileName := 'C:\TestFilename.xlsx';
  XLS.Write;

The XLSX gets written and everything is fine. However, I have specified the NumberFormat for the date, date time and currency type cells. These cells however, are not getting the formatting that I have specified. Does anyone know why this is?

Interestingly enough, if I don't use XLSDBRead to read a dataset and write it to a spreadsheet, but just do something like this:

Code: Select all

  XLS.Add;
  XLS.Sheets[1].AsDateTime[0, 1] := Date();
  XLS.Sheets[1].Cell[0, 1].NumberFormat := 'DD-MMM-YYYY';
  XLS.Sheets[1].AsFloat[0, 2] := 1234567.87;
  XLS.Sheets[1].Cell[0, 2].NumberFormat := '[$£-809]#,##0.00;-[$£-809]#,##0.00';
  XLS.Sheets[1].AutoWidthCols(0, 1);
  XLS.FileName := 'C:\TestFilename2.xlsx';
  XLS.Write;

the code works. I get the formatting that I want. Why doesn't the formatting work for XLSDBRead when it seems to work for standard cell input?

Thanks for your help.

Cheers,
Kaye.

[larsa]

Hello

Use CmdFormat. Example:

Code: Select all

    for Index := 0 to TestDataset.FieldCount - 1 do begin
      XLS.CmdFormat.BeginEdit(XLS[0]);
      case TestDataset.FieldDefList.FieldDefs[Index].DataType of
        ftDateTime, ftDate: begin
          XLS.CmdFormat.Number.Format := 'DD-MMM-YYYY';
        end;
        ftCurrency: begin
          XLS.CmdFormat.Number.Format := '[$£-809]#,##0.00;-[$£-809]#,##0.00';
          XLS.CmdFormat.Alignment.Horizontal := chaRight;
        end;
      end;
      XLS.CmdFormat.ApplyCols(Index,Index);
    end;