Axolot Data

Hi,

I'm running on 5.10.25

I have created an Excel2007 xlsx file with XLSReadWriteII5 like so:
XLS := TXLSReadWriteII5.Create(nil);
try
// somestuff
for i := 0 to Pred(FileContents.Count) do
begin
for j := 0 to Pred(LineContents.Count) do
XLS[0].AsString[j,i] := LineContents[j];
end;
XLS.Calculate;
XLS[0].CalcDimensions;
XLS.Filename := Result;
XLS.Write;
finally
FreeAndNil(XLS);
end;

If I try to read the generated file like this:
SampleDataset := TADODataSet.Create(nil);
SampleDataset.ConnectionString := GetConnectionString(Definition.SampleFile);
SampleDataset.CommandType := cmdTableDirect;
SampleDataset.CommandText := Definition.SampleSheet + '$';
SampleDataset.Open;

if will fail with "External table is not in the expected format"

If I add the following code to the file generation code it works:
ExcelObject := CreateOleObject('Excel.Application');
try
ExcelObject.Visible := False;
ExcelObject.WorkBooks.Open(Result);
ExcelObject.DisplayAlerts := False;
ExcelObject.WorkBooks[1].SaveAS(Result);
ExcelObject.WorkBooks.Close;
finally
ExcelObject.Quit;
end;

I obviously do not want to start Excel to post process the file. What am I doing wrong, or is this an issue in the XLSReadWriteII5 component?


Regards,

Frank

Hello

If you create an excel file with just one numeric cell, will you still receive the same error?

Hi Lars,

That also will give an error.

Here is the code:
function TInterfaceDefinition.CSV_To_XLS(const AFileName: String; const ADelimiter: Char): String;
var
FileContents: TStringList;
i, j: Integer;
LineContents: TStringList;
XLS: TXLSReadWriteII5;
begin
Result := GetWindowsTempDir + GiveGuid(True)+'.xlsx';

if (FileExists(Result)) then
DelFile(Result);

XLS := TXLSReadWriteII5.Create(nil);
try
FileContents := TStringList.Create;
LineContents := TStringList.Create;
try
FileContents.LoadFromFile(AFileName);
for i := 0 to Pred(FileContents.Count) do
begin
LineContents.Clear;
LineContents.Delimiter := ADelimiter;
LineContents.StrictDelimiter := True;
LineContents.DelimitedText := FileContents;
for j := 0 to Pred(LineContents.Count) do
XLS[0].AsString[j,i] := LineContents[j];
end;
finally
FreeAndNil(LineContents);
FreeAndNil(FileContents);
end;
XLS.Calculate;
XLS[0].CalcDimensions;
XLS.Filename := Result;
XLS.Version := Xc12Utils5.xvExcel2007;
XLS.Write;
finally
FreeAndNil(XLS);
end;
end;

The CSV file looks like
NumValue
123

This will give the External table is not in the expected format error.

If I add the following at the end of the procedure it works
ExcelObject := CreateOleObject('Excel.Application'); // is variant
try
ExcelObject.Visible := False;
ExcelObject.WorkBooks.Open(Result);
ExcelObject.DisplayAlerts := False;
ExcelObject.WorkBooks[1].SaveAS(Result);
ExcelObject.WorkBooks.Close;
finally
ExcelObject.Quit;
end;

Hello

Can you read a file created by Excel?

Hi Lars,

Yes, that works.

Any news on this issue?

Hello

Sorry, I know nothing about ADO. What are the values of ConnectionString and CommandText?

Just a plain and simple ADO connection


var
SampleDataset: TADODataset;
begin
SampleDataset := TADODataSet.Create(nil);
SampleDataset.ConnectionString := Format('Provider=Microsoft.ACE.OLEDB.12.0;Data Source=%s;Extended Properties="Excel 12.0;HDR=No;IMEX=1;ImportMixedTypes=Text;FirstRowHasNames=0;MaxScanRows=0";Persist Security Info=False;', [Definition.SampleFile]);
SampleDataset.CommandType := cmdTableDirect;
SampleDataset.CommandText := 'Sheet1$';
SampleDataset.Open;

Hi,

any news on this issue?

Hello

Yes, this is fixed in update 5.20.00