Modifying Data

The CData Cmdlets PowerShell Module for Microsoft Excel supports INSERT, UPDATE, and DELETE; more details are below.

Update and Delete

Updates and deletes require you to uniquely identify a single row by specifying the RowId column.

See Using Formulas to insert formulas.

Insert

You can insert new rows anywhere in the sheet, including inserting to named ranges.

Add a Row

You can add new rows to your spreadsheet using the INSERT statement. If the Header property is set to True, then you can insert based on the column headers. If the Header property is false, then you must use the cell names (A, B, C, etc.) to insert a row. When you insert a row, it is appended to the end of the sheet. This new row will be assigned a RowId based on where it is on the Excel sheet.

It is also possible to insert a row at the end of a range of cells. To do so, simply use the range in the table name while inserting a row.

INSERT INTO Sheet1#C1:D5 (A, B) VALUES ('Brian', '30')

The code above will add Brian and 30 to the end of the range; i.e., the cells C6 and D6.

Insert a Row

Sometimes you might want to insert a row in the middle of an Excel sheet. The cmdlet supports this using the INSERT command with the RowId, which identifies the row number. If the RowId is specified with the INSERT query, the cmdlet will add the row at the specified row number. All rows at and below the specified row number will be moved down one row, and their RowId will be incremented by one. For example:

INSERT INTO Sheet1 (RowId, A, B) VALUES (5, 'Brian', '30')

The code above will insert the values Brian and 30 to the cells A5 and B5. The existing data in row 5 and below will be moved down one row.

Add a Row to a Named Range

It is also possible to insert a row at the end of the named range. To do so, simply insert a row without the RowId to the table named after the range. The code below will append a new row to the end of the named range SALES. The definition of the named range will be expanded to include the row that was just inserted.