(Expanding a little on the June 26, 2018 post above).
You can load an existing .XLSX file and modify it, saving the result as XLSX.
Here is a basic script that loads a spreadsheet (XLSX), adds text to a new row in the worksheet, and saves it, again as XLSX.
Code: Select all
/*
EPPlus - TEMPLATING - Part One
This very simple script shows how to add rows to
existing XLSX files and save the result.
Along with tmplinshi's invaluable help,
the script at https://stackoverflow.com/questions/9571581/epplus-how-to-use-a-template
was helpful as well(see the VB example by JamesCBaird).
The FileInfo() function is by tmplinshi.
EPPlus.dll needs to be in your script directory.
You'll also need "App 2.xlsx" (MUST NOT start blank. Needs at least one row to exist.)
Run the script several times to verify rows have been added.
*/
lib := Clr_LoadLibrary("mscorlib")
infile := FileInfo("App 2.xlsx")
outfile := infile
; modify the above if you want
; a different file to result.
asm := Clr_LoadLibrary(".\EPPlus.dll")
pck := Clr_CreateObject(asm, "OfficeOpenXml.ExcelPackage", infile)
ws := pck.Workbook.Worksheets.(1)
;grab the first worksheet
ws.Name := "AppTest"
;name or rename it.
nr := ws.Dimension.End.Row + 1
; 'nr' is be concatenated with the column
; letter to get the cell address.
ws.Cells.("A"nr).Value := "Hello World!"
ws.Cells.("A"nr).Style.Font.Size := 24
ws.Cells.("A"nr).Style.Font.Color.SetColor(0xFF0000)
ws.Cells.("A"nr).AutoFitColumns()
pck.SaveAs(outfile)
FileInfo(filename)
{
static lib := Clr_LoadLibrary("mscorlib")
return Clr_CreateObject(lib, "System.IO.FileInfo", filename)
}burque505
tmplinshi to the rescue!!!!!
