Import Timesheets from Excel

{This script shows opening an excel file and creating a TimeSheet batch}

{from the cells contents. Note: the excel columns and rows are 0 based}

{so to read the first cell would be SSGetCellText(0,0) (Column,Row)}

{The download email attachment example could be included with this one}

{to first download the excel file from an email}

function GetExcelFile: string;

var

OpenDlg: TOpenDialog;

begin

OpenDlg := TOpenDialog.Create(Nil);

try

OpenDlg.Filter := 'Excel Files (*.xls)|*.xls|All Files (*.*)|*.*';

if OpenDlg.Execute then

Result := OpenDlg.filename

else

Result := '';

finally

OpenDlg.Free;

end;

function InsertHeader(ExcelFileName,EmployeeName,WeekEnding: String): Integer;

begin

Result := InsertTimesheetHeader('InProgress','For Week Ending: ' + WeekEnding,'Employee'

,EmployeeName,True,Date,'Waiting Approval',date,'','Imported from Excel (' + ExcelFileName + ')',Date);

end;

procedure CreateTimeSheetEntries;

var

ExcelFileName,EmployeeName: String;

x, RowCount, BatchNumber: Integer;

begin

ExcelFileName := GetExcelFile;

if trim(ExcelFileName) = '' then exit;

LoadSpreadSheet(ExcelFileName);

RowCount := SSGetContentRowCount;

BatchNumber := InsertHeader(ExcelFileName,SSGetCellText(1,0),SSGetCellText(1,1));

EmployeeName := SSGetCellText(1,0);

for x := 3 to RowCount -1 do

{Check we have a date}

if trim(SSGetCellText(0,x)) <> '' then

begin

InsertTimesheetLine(

BatchNumber

, strtodatetime(SSGetCellText(0,x))

,SSGetCellText(1,x)

,SSGetCellText(2,x)

,EmployeeName

,'STD'

,SSGetCellText(4,x)

,'LAB-SERVICE'

,strtofloat(SSGetCellText(3,x))

,'Chargeable'

,''

,SSGetCellText(5,x)

,False

,'');

end;

{Optionally update the spreadsheet so you know it has been imported}

SSSetCellText(2,0,'Batch No:');

SSSetCellText(2,1,'Imported On:');

SSSetCellText(3,0,inttostr(BatchNumber));

SSSetCellText(3,1,datetimetostr(Date));

SaveSpreadsheet(ExcelFileName);

end;

begin

CreateTimeSheetEntries;

MessageDlg('Timesheet Imported',mtinformation,mbok,0);

end.