Page 1 of 1
ver 5.10.18a - migration from 4 / problem with excel 2003
Posted: Wed May 01, 2013 10:22 am
by 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
Re: ver 5.10.18a - migration from 4 / problem with excel 2003
Posted: Wed May 01, 2013 1:26 pm
by 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.
Re: ver 5.10.18a - migration from 4 / problem with excel 2003
Posted: Wed May 01, 2013 4:40 pm
by 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;
Re: ver 5.10.18a - migration from 4 / problem with excel 2003
Posted: Wed May 01, 2013 6:55 pm
by 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
Re: ver 5.10.18a - migration from 4 / problem with excel 2003
Posted: Thu May 02, 2013 5:21 am
by 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
Re: ver 5.10.18a - migration from 4 / problem with excel 2003
Posted: Thu May 02, 2013 1:04 pm
by larsa
Hello
I could not reproduce the error. Please provide a complete code sample that can be compiled. Also, which delphi do you use?
Re: ver 5.10.18a - migration from 4 / problem with excel 2003
Posted: Tue May 07, 2013 7:26 pm
by 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
Re: ver 5.10.18a - migration from 4 / problem with excel 2003
Posted: Mon May 20, 2013 9:42 am
by 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.