◷ Reading Time: 4 minutes
toExcel
Allows updating existing Excel document. It returns an Excel document to modify.
file |toExcel ()
- file (String): File name of the document including the path (Madatory)
Example: ('1.xlsx')|toExcel ()
Result: Returns an Excel document.
Example: (pathCombine(pathCurrent(),'1.xlsx'))|toExcel ()
Result: Returns an Excel document in the current path.
excelRowNew
Inserts a new row at a position with new values.
document |excelRowNew (sheetName, rowIndex, columnIndex, columns, values)
- sheetName (String): Name of the sheet to read (Madatory)
- rowIndex (Integer): Zero-based row index to start inserting new rows (Madatory)
- columnIndex (Integer): Zero-based column index to start inserting cell values (Madatory)
- columns (Array): Array of string providing the column names in their order (Madatory)
- values (Array of key-value objects ): Values to be inserted (Madatory)
Example: '1.xlsx' |toExcel()|excelRowNew ('Sheet1', 7, 7, ['Age','Name'], [{'Age':23, 'Name': 'Alex'},{'Age':34, 'Name': 'John'}])
Result: Adds a new row to the Excel document with the given columns. But this will only add a column and won't save the changes.
Example: '1.xlsx' |toExcel()|excelRowNew ('Sheet1', 7, 7, ['Age','Name'], [{'Age':23, 'Name': 'Alex'},{'Age':34, 'Name': 'John'}])|excelSave (pathCombine(pathCurrent(),"1.xlsx"))
Result: Adds a new row to the Excel document with the given columns. This will also save the changes to the same file.
Example: '1.xlsx' |toExcel()|excelRowNew ('Sheet1', 7, 7, ['Age','Name'], [{'Age':23, 'Name': 'Alex'},{'Age':34, 'Name': 'John'}])|excelSave (pathCombine(pathCurrent(),"1_updated.xlsx"))
Result: Adds a new row to the Excel document with the given columns. This will also save the changes to a new file called 1_updated.xlsx.
excelRowUpdate
Updates a row at a position with new values.
document |excelRowUpdate (sheetName, rowIndex, columnIndex, columns, values)
- sheetName (String): Name of the sheet to read (Madatory)
- rowIndex (Integer): Zero-based row index to start inserting new rows (Madatory)
- columnIndex (Integer): Zero-based column index to start inserting cell values (Madatory)
- columns (Array): Array of string providing the column names in their order (Madatory)
- values (Array of key-value objects ): Values to be inserted (Madatory)
Example: '1.xlsx' |toExcel()|excelRowNew ('Sheet1', 7, 7, ['Age','Name'], [{'Age':23, 'Name': 'Alex'},{'Age':34, 'Name': 'John'}])
Result: Adds a new row to the Excel document with the given columns. But this will only add a column and won't save the changes.
Example: '1.xlsx' |toExcel()|excelRowUpdate ('Sheet1', 0, 2, ['Age','Name'], [{'Age':55, 'Name': 'Alex'},{'Age':44, 'Name': 'John'}])|excelSave (pathCombine(pathCurrent(),"1.xlsx"))
Result: Updated a row with the given values. This will also save the changes to the same file.
Example: '1.xlsx' |toExcel()|excelRowUpdate ('Sheet1', 0, 2, ['Age','Name'], [{'Age':55, 'Name': 'Alex'},{'Age':44, 'Name': 'John'}])|excelSave (pathCombine(pathCurrent(),"1_updated.xlsx"))
Result: Updated a row with the given values. This will also save the changes to a new file called 1_updated.xlsx.
excelSave
Save the excel document.
document |excelSave (path)
Example: ("1.xlsx")|toExcel()|excelSave (pathCombine(pathCurrent(),"1_edited.xlsx"))
Result: Save 1.xlsx as 1_edited.xlsx"
Examples
The sample project (Excel Example.zip) is attached at the end of the page.
Update Excel.xml
The example will read the file input_1.xlsx, add a new column at the end, and update the Excel file.
file |excelRowUpdate ('Sheet1', 0, 3, ['New Column'], list)
Create Excel.xml
The example will create a new Excel file, input_2.xlsx.
file |excelRowNew ('Sheet1', 0, 0, ['Code', 'State', 'Country'], list)
