Updated May 2019; see History
//XL, AddSheet {,srcbook=xxx.xls} {,dstbook=yyy.xls} {,sheetname=zzz}{,index=1} {,deletefirst}{,color=COLOR_xxx} {,protect} {,password=<password>}
AddSheet allows you to define the name of the sheet, and/or add it to an existing workbook, and/or change the name of the output workbook. Subsequent instances of the directive create new pages. By not including the file extension in the specification, the extension (XLXL or XLS) will be automatically selected based on the current output format. All four parameters are optional, but you need at least one to make any sense:
Parameter |
Description |
|---|---|
srcbook= |
Specifies the source workbook to which you are appending the sheet, e.g. srcbook=mybook.xls. Default is to create a new book. Must be a native filespec. |
dstbook= |
Specifies the destination workbook to save the result as. Can be same as srcbook. Default is the original CSV file with .xls extension, and optionally with a -# sequence number appended to the name – see AppendSeqno in [Post] section of the ini file. Must be a native filespec. |
sheetname= |
Specifies the name you want to assign to the sheet. Default is "sheet1". Note: must be unique within the workbook! |
index=# |
Causes the new sheet to be inserted at position #. Default is to append to the end. |
deletefirst |
If the deletefirst parameter is specified and the specified sheet already exists, it will first be deleted before being re-added. Otherwise, the default is to just overwrite the existing sheet with the new data. This could result in a mixture of new and old data if the CSV data contains fewer rows or columns than the existing sheet data. |
color= |
COLOR_xxx sets the tab color. |
protect |
Sets the default protection option for the sheet. |
password |
Set the password needed to unprotect the sheet. Implies protect. |
Subsequent AddSheet directives cause the current sheet to be finished and a new sheet started. This also includes triggering any automatic totals per the Initialization File settings, and re-initializing the number and formats of the columns per the data lines following the AddSheet directive. In other words, each sheet is effectively independent.
For example, the following creates a workbook with two sheets, the first named "English" with three columns, and the second named "Spanish" with two columns...
//XL,AddSheet,Sheetname=English
One,Two,Three
1,two,3
11,twenty-two,33
111,two-twenty-two,333
//XL,AddSheet,Sheetname=Spanish
Uno,Dos
Un perro,2
Unos hombres,22
Assuming the initialization file enables automatic totals, the two sheets of the resulting workbook would look something like this:
Note that the two sheets have completely different column structures.
History
2020 September, A-Shell 6.5.1691, CSV2XL.SBX edit 255: Add support for protect and password options.
2019 September, A-Shell 6.5.1668, CSV2XL.SBX edit 236: Extension may now be eliminated from the directive in order to allow the current output format to set the standard extension. Previously you had to specify either xls or xlsx which would then cause problems if the actual format didn't match.
2019 May, A-Shell 6.5.1661, CSV2XL.SBX edit 230: Add support for: //XL,AddSheet now supports an optional parameter COLOR=COLOR_xxx to set the tab color; and set wrap attribute for column headers.
2019 Feburary, A-Shell 6.5.1656, CSV2XL.SBX edit 222: Support multiple sheets within a single source file.
2018 January, A-Shell 6.4.1556: Add deletefirst parameter to AddSheet directive
