ver 5.10.18a - migration from 4 / problem with excel 2003

[airsoft]

Hello,

I've downloaded the current 5.10.18a and have two problems:

1. is it correct such code migration?
1.1.
ver 4.00.65 - function Get_Styles (FS : TFontStyles) : TXFontStyles;
ver 5.10.18a - function Get_Styles (FS : TFontStyles) : TXc12FontStyles;
1.2.
ver 4.00.65 - with X.Sheets [0].Range [0, 0, W, H] do begin BorderTopColor := xcAutomatic; FormatOptions := [foWrapText]; end;
ver 5.10.18a - with X.Sheets [0].Range [0, 0, W, H] do BorderTopColor := cardinal (xcAutomatic); WrapText := true; end;

2. produced output files
ver 5.10.18a - when my code outputs XLS file - EXCEL 2003 says "Office File Validation detected a problem while trying to open this file. Opening may be dangerous." and when I press "Open" button the file is opened in EXCEL. Opening of XLSX file /XLS.Version := xvExcel2007/ triggers the plugin for downgrading XLSX files into XLS, but instead of font "Arial / 10pt" the EXCEL says "Calibri / 10pt"
ver 4.00.65 - has no one of the problems that I had mentioned

Regards

[larsa]

Hello

1.1 Yes.

1.2 Don't use Range. Use CmdFormat instead. See FormatCells sample for more details.

2. This is probably related to 1.2 Use CmdFormat.

[airsoft]

Thanks.

Seems to work correctly, the only problem that I cannot solve is how to set migrate this bold line

ver 4
with X.Sheets [0].Range [0, 0, W, H] do
begin
BorderTopColor := xcAutomatic;
BorderTopStyle := cbsThin;
BorderLeftColor := xcAutomatic;
BorderLeftStyle := cbsThin;
BorderRightColor := xcAutomatic;
BorderRightStyle := cbsThin;
BorderBottomColor := xcAutomatic;
BorderBottomStyle := cbsThin;
BorderInsideVertColor := xcAutomatic;
BorderInsideVertStyle := cbsThin;
BorderInsideHorizColor := xcAutomatic;
BorderInsideHorizStyle := cbsThin;
HorizAlignment := chaCenter;
VertAlignment := cvaCenter;
FontStyle := [xfsBold];
FormatOptions := [foWrapText];
end;

ver 5
with X.CmdFormat do
begin
Mode := xcfmReplace;
BeginEdit (X [0]);
Border.Color.IndexColor := xcAutomatic;
Border.Side [cbsTop] := true;
Border.Side [cbsLeft] := true;
Border.Side [cbsRight] := true;
Border.Side [cbsBottom] := true;
Border.Side [cbsInsideHoriz] := true;
Border.Side [cbsInsideVert] := true;
Border.Style := cbsThin;
Font.Style := [xfsBold];
Alignment.Horizontal := chaCenter;
Alignment.Vertical := cvaCenter;
Apply (0, 0, W, H);
end;

[airsoft]

Hi again,

I've found it. The analog code for ver5 vs ver4
ver 4
HorizAlignment := chaCenter;
VertAlignment := cvaCenter;
FormatOptions := [foWrapText];
ver 5
Alignment.Horizontal := chaCenter;
Alignment.Vertical := cvaCenter;
Alignment.WrapText := true;

since you don't have an example for it

[airsoft]

Hi again,

I still receive message from Excel 2003 SP3 "Office File Validation detected a problem while trying to open this file. Opening it may be dangerous."
and I wonder is this sequnce of code in that way

X.CmdFormat.Mode := xcfmReplace;
........
X.CmdFormat.Border.Style := cbsThin;
X.CmdFormat.Border.Side [cbsTop] := true;
X.CmdFormat.Border.Side [cbsLeft] := true;
X.CmdFormat.Border.Side [cbsRight] := true;
X.CmdFormat.Border.Side [cbsBottom] := true;
X.CmdFormat.Border.Side [cbsInsideHoriz] := true;
X.CmdFormat.Border.Side [cbsInsideVert] := true;
........

because when I've tried like this

X.CmdFormat.Mode := xcfmReplace;
........
X.CmdFormat.Border.Side [cbsTop] := true;
X.CmdFormat.Border.Side [cbsLeft] := true;
X.CmdFormat.Border.Side [cbsRight] := true;
X.CmdFormat.Border.Side [cbsBottom] := true;
X.CmdFormat.Border.Side [cbsInsideHoriz] := true;
X.CmdFormat.Border.Side [cbsInsideVert] := true;
X.CmdFormat.Border.Style := cbsThin;
........

it does not work mean I don't see a border around each cell in Excel

Regards

[larsa]

Hello

I could not reproduce the error. Please provide a complete code sample that can be compiled. Also, which delphi do you use?

[airsoft]

Hi again,

no problem. Here is the whole code. You can make an empty project without forms

Code: Select all

program Project_Border_Test;

uses XLSReadWriteII5, Xc12Utils5, XLSCmdFormat5, Xc12DataStyleSheet5, SysUtils;

var X : TXLSReadWriteII5;
    R, C : integer;

begin
  X := TXLSReadWriteII5.Create (nil);
  try
    X.Clear;
    X.Version := xvExcel97;
    with X.CmdFormat do
    begin
      Mode := xcfmReplace;
      BeginEdit (X [0]);
      Border.Color.IndexColor      := xcAutomatic;
      Border.Style                 := cbsThin;
      Border.Side [cbsTop        ] := true;
      Border.Side [cbsLeft       ] := true;
      Border.Side [cbsRight      ] := true;
      Border.Side [cbsBottom     ] := true;
      Border.Side [cbsInsideHoriz] := true;
      Border.Side [cbsInsideVert ] := true;
      Font.Style                   := [xfsBold];
      Alignment.Horizontal         := chaCenter;
      Alignment.Vertical           := cvaCenter;
      Alignment.WrapText           := true;
      Apply (0, 0, 10, 6);
    end;
    for R := 0 to 6 do
      for C := 0 to 10 do
        X.Sheets [0].AsString [C, R] := Format ('Row %d, Col %d', [R, C]);
    X.Filename := 'test-it_is_ok.xls';
    X.Write;

    X.Clear;
    X.Version := xvExcel97;
    with X.CmdFormat do
    begin
      Mode := xcfmReplace;
      BeginEdit (X [0]);
      Border.Color.IndexColor      := xcAutomatic;
      Border.Side [cbsTop        ] := true;
      Border.Side [cbsLeft       ] := true;
      Border.Side [cbsRight      ] := true;
      Border.Side [cbsBottom     ] := true;
      Border.Side [cbsInsideHoriz] := true;
      Border.Side [cbsInsideVert ] := true;
      Border.Style                 := cbsThin;
      Font.Style                   := [xfsBold];
      Alignment.Horizontal         := chaCenter;
      Alignment.Vertical           := cvaCenter;
      Alignment.WrapText           := true;
      Apply (0, 0, 10, 6);
    end;
    for R := 0 to 6 do
      for C := 0 to 10 do
        X.Sheets [0].AsString [C, R] := Format ('Row %d, Col %d', [R, C]);
    X.Filename := 'test-not_ok.xls';
    X.Write;
  finally
    X.Free;
  end;
end.

the program creates two excel files, one has border around each cell in range [0, 0, 10, 6], the other has no border

My DELPHI is version 7.0

Regards

[larsa]

Hello

You must set the style before you assign it to the sides.

Correct:
Border.Style := cbsThin;
Border.Side [cbsTop] := true;

Wrong:
Border.Side [cbsTop] := true;
Border.Style := cbsThin;

When you call "Border.Side [cbsTop] := true", you assign the style (and color) to the side. If you not have set any style/color, there3 will not be any.