In ExcelWriter 6.7.1, a new InsertRows method was added to the Worksheet class. This method allows many rows to be inserted at one time with only one update required to the workbook. This improves performance dramatically over using the InsertRow method, and is the recommended practice for inserting multiple consecutive rows.
Method Signatures
void InsertRows(int rowNumber, int rowCount)
This is the basic signature. It allows you to specify a row at which to start inserting rows and the number of rows to be inserted.
New rows which are inserted use the default style, ignoring the styles of the rows and cells above and below.
void InsertRows(int rowNumber, int rowCount,
Style.RowInsertBehavior rowCellsCopyBehavior)
In addition to the basic signature, this signature allows you to specify the how the style of each cell in the new rows will be set based on neighboring cells' styles.
When setting the rowCellsCopyBehavior parameter to Style.RowInsertBehavior.Above or Style.RowInsertBehavior.Below, the style of an adjacent cell (above or below) will be copied to the new row if (1) the adjacent cell has content in it or (2) the style of the adjacent cell was set on the cell itself, and not on the entire row. The styles which were set on the entire adjacent row and have cells which do not contain content will be ignored, and the default style will be used.
void InsertRows(int rowNumber, int rowCount,
Style.RowInsertBehavior rowCellsCopyBehavior,
Style.RowInsertBehavior rowCopyBehavior)
In addition to specifying the basic parameters and the cell style behavior, this signature allows you to specify the row style behavior based on neighboring rows.
The rowCellsCopyBehavior parameter works the same way as in the previous signature, copying the styles from the cells above or below from cells which have content or have had their style set on the cells themselves. The rowCopyBehavior parameter, if set to Style.RowInsertBehavior.Above or Style.RowInsertBehavior.Below, will copy the row style from above or below. Cell styles always override row styles, so if you set both rowCellsCopyBehavior and rowCopyBehavior, the cell styles will always override the row styles for new rows that are inserted.
Example
The following example demonstrates how to use the Worksheet.InsertRows method to insert new rows in the middle of worksheet.
Input file
This example starts with an Excel file which looks like the following:
The light blue background is set on the rows, which makes it a row style. The dark blue and gray backgrounds, as well as the borders and font colors, are all set on the cells themselves, and are therefore cell styles.
Code
The following code will insert 5 new rows after the dark blue header row using the style of the first gray data row. The third signature of InsertRows should be used so that we can instruct ExcelWriter to use both the cell and row style of the 3rd row when inserting new rows.
|
[C#]
|
[VB.NET]
|
|
ExcelApplication xla = new ExcelApplication();
Workbook wb = xla.Open(Server.MapPath("InsertRows_in.xls"));
Worksheet sheet1 = wb.Worksheets[0];
//--- Insert 5 rows starting at the 3rd row.
//--- Copy cell and row styles from the current 3rd row.
sheet1.InsertRows(2, 5, Style.RowInsertBehavior.Below,
Style.RowInsertBehavior.Below);
xla.Save(wb, Response, "InsertRows_out.xls", false);
Dim xla As New ExcelApplication()
Dim wb As Workbook = xla.Open(Server.MapPath("InsertRows_in.xls"))
Dim sheet1 As Worksheet = wb.Worksheets(0)
'--- Insert 5 rows starting at the 3rd row.
'--- Copy cell and row styles from the current 3rd row.
sheet1.InsertRows(2, 5, Style.RowInsertBehavior.Below, _
Style.RowInsertBehavior.Below)
xla.Save(wb, Response, "InsertRows_out.xls", false)
|
Output
The output file looks like the following:
As can be seen, the cell styles (gray background and borders) were copied as well as the row style (the light blue background on the row itself).
|
