Hi Lars,
we create an Excel-File in our application and want to reimport it later on. Usually this Excel-File is being edited/saved in Excel before it is reimported. In this case everything works as expected. But if the users 'forgets' to save it, ther is some strange behaviour concerning cells written with '.AsFormula'. Refer to example-code below. Tested it with current version 4.0.0.49...
Do you have any suggestion, what i can do to get correct results even if the file is not being saved in Excel?
Best regards,
Carsten
Code:
procedure TForm1.btnCreateClick(Sender: TObject);
var
iFmt: Integer;
sFmla,
sVal: String;
begin
FExcel.Clear;
FExcel.Sheet[0].Columns.AddIfNone(0, 2);
FExcel.Sheet[0].Rows.AddIfNone(0);
FExcel.Formats.Add.NumberFormat := '@'; // Cell A1 is formatted "AsString"
iFmt := FExcel.Formats.Count - 1;
// Add the desired values: A1 -> inital value, A2 -> Formula
FExcel.Sheet[0].AsString [0,0] := '1';
FExcel.Sheet[0].Cell[0, 0].AssignFormat(iFmt);
FExcel.Sheet[0].AsFormula[1,0] := 'IF(A1="1";"4711";IF(A1="2";"4712";IF(A1="3";"4713";"N/A")))';
FExcel.Sheet[0].CalculateSheet; // See comment below
// Now save the file ------------------
FExcel.Filename := 'N:\Test.xls';
FExcel.Write;
// 'Later' reimport the file ------------------
FExcel.Clear;
FExcel.Read;
//FExcel.Sheet[0].CalculateSheet; // See comment below
If (FExcel.Sheet[0].Cell[1, 0] <> nil) then
begin
sVal := FExcel.Sheet[0].AsString [1, 0];
sFmla := FExcel.Sheet[0].AsFormula[1, 0];
// ------------------------------------------------------------------------
// The formula is OK and works perfectly when affected in MS Excel but...
// - If CalculateSheet() BEFORE Write() and file is NOT SAVED in Excel,
// the reimported value is to "TRUE"
// - If CalculateSheet() AFTER Read (), and file is NOT SAVED in Excel,
// the reimported value is to "#VALUE!"
// - If NO CalculateSheet() is executed, and file is NOT SAVED in Excel,
// the reimported value is "0"
// - If file is opened and saved in Excel, all imported values are OK!?
ShowMessage('Value: ' + sVal + ' / Formula: ' + sFmla);
end;
end;