var
DataFileName: String;
fmt: TsCellFormat;
nfp: TsNumFormatParams;
workbook: TsWorkbook;
worksheet: TsWorksheet;
fieldType: TFieldType;
fieldsize: Integer;
fieldname: String;
datacell: PCell;
titleCell: PCell;
r, c: Cardinal;
maxc, maxr: Integer;
begin
Screen.Cursor := crHourglass;
try
DataFileName := 'laborminta.xlsx';
workbook := TsWorkbook.Create;
try
workbook.ReadFromFile(DataFileName, sfOOXML);
worksheet := workbook.GetWorksheetByIndex(0);
//maxc := worksheet.GetlastColIndex; //exce;AA-ig
//maxr := worksheet.GetLastRowIndex; // excel AA ig
maxc := worksheet.GetLastColIndex(true);
maxr := worksheet.GetLastRowIndex;
if not FileExists(BufDataset1.FileName) then begin
// Create a table
for c:=0 to maxc do begin
titlecell := worksheet.GetCell(0, c);
datacell := worksheet.GetCell(1, c);
fieldsize := 0;
fmt := worksheet.ReadCellFormat(dataCell);
nfp := workbook.GetNumberFormat(fmt.NumberFormatIndex);
case datacell^.ContentType of
cctNumber:
begin
if IsCurrencyFormat(nfp) then
fieldType := ftCurrency
else
fieldType := ftFloat;
end;
cctDateTime :
if IsTimeFormat(nfp) then
fieldType := ftTime
else if IsDateFormat(nfp) then
fieldType := ftDate
else
fieldType := ftDateTime;
else
fieldType := ftString;
fieldSize := 20;
end;
fieldname := titlecell^.UTF8StringValue;
ZQuery1.FieldDefs.Add(fieldName, fieldType, fieldsize);
end;
ZQuery1.Open;
ZQuery1.DisableControls;
for r := 1 to maxr do begin
//BufDataset1.Append;
ZQuery1.Insert;
for c := 0 to maxc do begin
datacell := worksheet.GetCell(r, c);
case ZQuery1.Fields[c].DataType of
ftCurrency,
ftFloat,
ftInteger : ZQuery1.Fields[c].AsFloat := datacell^.NumberValue;
ftDate,
ftDateTime,
ftTime : ZQuery1.Fields[c].AsDateTime := datacell^.NumberValue;
ftString : ZQuery1.Fields[c].AsString := datacell^.UTF8StringValue;
end;
end;
ZQuery1.Post;
ZQuery1.close;
ZQuery1.open;
end;
ZQuery1.EnableControls;
end;
finally
workbook.Free;
end;
finally
Screen.Cursor := crDefault;
end;