var XLApp, Sheet, Data, vWorkBook: OleVariant;
vProcedureData:OleVariant;
i, iTitleLine, iSheetIndex, iRowCount, iColCount:Integer;
sSampleFile, sFileAlias, sFileName, sBeginCell, sEndCell, sFormulaBeginCell, sFormulaEndCell:String;
sRange, sSheetName:String;
begin
Result := '';
vProcedureData := UnitLib.fn_Get_Data_Array_From_Attach_Procedure;
iTitleLine := DM1.adoAutoMail.FieldByName('TitleLine').AsInteger;
sSampleFile := DM1.adoAutoMail.FieldByName('SampleFile').AsString;
sFileAlias := UnitLib.fn_Get_AutoMail_FieldValue('FileAlias');
iSheetIndex := DM1.adoAutoMail.FieldByName('SheetIndex').AsInteger;
//
XLApp := CreateOleObject('Excel.Application');
try
XLApp.Visible := False;
XLApp.DisplayAlerts := False; //關掉警告訊息
XLApp.WorkBooks.Open(sSampleFile);
vWorkBook := XLApp.Workbooks[1];
Sheet := vWorkBook.Worksheets[iSheetIndex];
//
sSheetName := Sheet.Name;
iRowCount := DM1.adoAttach_Procedure.RecordCount;
iColCount := DM1.adoAttach_Procedure.FieldCount;
sBeginCell := RefToCell(iTitleLine+1, 1);
sEndCell := RefToCell(iTitleLine+iRowCount+1, iColCount);
Sheet.Range[sBeginCell, sEndCell].Value := vProcedureData;
//SubTotal Row
for i := 0 to iColCount-1 do
begin
if fn_IsNumericField(DM1.adoAttach_Procedure.Fields[i]) then
begin
sBeginCell := RefToCell(iTitleLine+iRowCount+1, i+1);
sEndCell := RefToCell(iTitleLine+iRowCount+1, i+1);
sFormulaBeginCell := RefToCell(iTitleLine+1, i+1);
sFormulaEndCell := RefToCell(iTitleLine+iRowCount, i+1);
Sheet.Range[sBeginCell, sEndCell].Formula := '=SUM('+sFormulaBeginCell+':'+sFormulaEndCell+')';
end;
end;
//Save as Xlsx
sFileName := ChangeFileExt(sFileAlias, '.xlsx'); //xlOpenXMLWorkbook(51) Xlsx Format / xlHtml
vWorkBook.SaveAs(sFileName);
Result := sFileName;
//Save Sheet to Html
sFileName := ChangeFileExt(sFileAlias, '.html');
sBeginCell := RefToCell(1, 1);
sEndCell := RefToCell(iTitleLine+iRowCount+1, iColCount);
sRange := sBeginCell+':'+sEndCell;
vWorkBook.PublishObjects.Add(xlSourceRange, sFileName, sSheetName, sRange, xlHtmlStatic).Publish(True);
finally
if not VarIsEmpty(XLApp) then
begin
vWorkBook.Close;
XLApp.Quit;
XLAPP := Unassigned;
Sheet := Unassigned;
Application.ProcessMessages;
end;
end;
end;
