ccPoiToolsExt icon

ccPoiToolsExt

POI Tools
ccPoiToolsExtAxon funcs

Registered StackHub users may elect to receive email notifications whenever a new package version is released or a comment is posted on the forum.

There are 2 watchers.

v1.2.0

getDateTimeFromExcelDouble
getDateTimeFromExcelDouble(excelSerialDate, tz: "UTC", checked: true)
Fantom signature:
@Axon static Obj? getDateTimeFromExcelDouble(Number excelSerialDate, Str tz := "UTC", Bool checked := true)
poiListSheets
poiListSheets(poi)

List all the sheet names found in the Excel document represented by the provided POIReader instance.

Examples:

poiReaderOpen("myFile.xlsx").poiListSheets

Side effects:

  • Returns the array of sheet names found
Fantom signature:
@Axon static Str[] poiListSheets(POIReader poi)
poiReadSheetByIndexAsStr
poiReadSheetByIndexAsStr(poi, sheetIndex)

Read the content of a specific sheet, identified by index, returned as a Str which can be further parsed by the calling Axon function.

Examples:

poiReaderOpen("myFile.xlsx").poiReadSheetByIndexAsStr(0)

Side effects:

  • Returns the contents of the sheet at a specific index as a Str, suitable for parsing by ioReadCsv
Fantom signature:
@Axon static Str poiReadSheetByIndexAsStr(POIReader poi, Number sheetIndex)
poiReadSheetByNameAsStr
poiReadSheetByNameAsStr(poi, sheetname)

Read the content of a specific sheet, identified by name, returned as a Str which can be further parsed by the calling Axon function.

Examples:

poiReaderOpen("myFile.xlsx").poiReadSheetByNameAsStr("Sheet1")

Side effects:

  • Returns the contents of the named sheet as a Str, suitable for parsing by ioReadCsv
Fantom signature:
@Axon static Str poiReadSheetByNameAsStr(POIReader poi, Str sheetname)
poiReaderClose
poiReaderClose(poi)

Close the provided POIReader instance.

Examples:

poiReaderOpen("myFile.xlsx").poiReaderClose

Side effects:

  • Closes completely the provided POIReader instance.
Fantom signature:
@Axon static Void poiReaderClose(POIReader poi)
poiReaderNew
poiReaderNew(opts: Etc.emptyDict())

Construct an empty new POIReader. Useful when writing data to a sheet and then saving to new file.

Examples:

poiReaderNew()
poiReaderNew({separator:",", formattedCells:true})

Side effects:

  • Returns an instance of POIReader which must be passed in on all subsequent calls that wish to use this reader
Fantom signature:
@Axon static POIReader poiReaderNew(Dict opts := Etc.emptyDict())
poiReaderOpen
poiReaderOpen(filename, opts: Etc.emptyDict())

Construct a POIReader from the provided file name When called from Axon, the filename must start with "io/", so assume that and prefix with project absolute path

Examples:

poiReaderOpen("myFile.xlsx")
poiReaderOpen("myFile.xlsx", {separator:","})

Side effects:

  • Returns an instance of POIReader which must be passed in on all subsequent calls that wish to use this reader
Fantom signature:
@Axon static POIReader poiReaderOpen(Str filename, Dict opts := Etc.emptyDict())
poiReaderOpenBuf
poiReaderOpenBuf(buf, opts: Etc.emptyDict())

Construct a POIReader for the provided binary byte buffer. The Buf instance should be the binary contents of any of the support Excel file versions.

Examples:

poiReaderOpen(smbReaderOpen("10.0.0.1").smbReaderConnect("shareName").smbReaderReadBinaryFile("dirName", "myFile.xlsx"))

Side effects:

  • Returns an instance of POIReader which must be passed in on all subsequent calls that wish to use this reader
Fantom signature:
@Axon static POIReader poiReaderOpenBuf(Buf buf, Dict opts := Etc.emptyDict())
poiSaveToFile
poiSaveToFile(poi, filename)
Fantom signature:
@Axon static POIReader poiSaveToFile(POIReader poi, Str filename)
poiWriteSheetData
poiWriteSheetData(poi, sheetName, data, opts: null)

Write the Grid of data to a specific sheet, identified by name.

Examples:

poiReaderOpen("test.xlsx")
  .poiWriteSheetData(
     "Sheet1",
     [{name:"n1", age:12},{name:"n2", age:10}].toGrid,
     {
       writeColHeaders:true, clearSheet:false, rowLocation:1, colLocation:1,
       dateFormat:"YYYY-MM-DD", dateTimeFormat:"YYYY-MM-DD hh:mm:ss"
     })
  .poiSaveToFile("test.xlsx")
  .poiReaderClose

If the sheet of the specified name doesn't exist, it will be created.

Options work as follows:

  • rowLocation is 1 based. Default is 0. Any value < 1 indicates append the data after existing rows.
  • colLocation is 1 based. Default is 1.
  • clearSheet when true will cause all rows after rowLocation to be cleared. When false, only the rows where the data lands will be replaced.
  • dateFormat defaults to "YYYY-MM-DD", so any cell in the Grid that is of type date, will be formatted correctly in Excel
  • dateTimeFormat defaults to "YYYY-MM-DD hh:mm:ss", so any cell in the Grid that is of type dateTime, will be formatted correctly in Excel

Side effects:

  • Updates the contents of the provided POIReader and returns it, ready for writing to file.
Fantom signature:
@Axon static POIReader poiWriteSheetData(POIReader poi, Str sheetName, Grid data, Dict? opts := null)
Published by Crowley Carbon

Packages by Crowley Carbon

Free packages