Table of Contents | ||||
---|---|---|---|---|
|
Excerpt | ||
---|---|---|
| ||
ExcelWriter interacts with existing Workbook content in two possible ways when importing data. |
Introduction
There are two ways to insert rows of data using ExcelWriter:
- ExcelTemplate's
...
- #ExcelTemplate.BindData method
...
- ExcelApplication's
...
- #Worksheet.ImportData method
...
ExcelTemplate.BindData
When data is imported into a template file that contains data markers with ExcelTemplate.BindData (API Reference), the following is true:
- ExcelWriter inserts a brand new row for each row of data in the data source.
- Content in the cells below the data markers will be pushed down as the new rows are added.
- Styles and formatting in the data marker row will be copied for each new row.
- Formulas that reference the data marker row will be updated to reflect the newly inserted rows.
For example, we start with a simple template file that contains data markers:
Here is the template after it has been populated with data:
Below is a break down of what was affected when the data was imported.
Content below the data markers
In this example, there is content below the data marker row (a Grand Total row and a blue footer row). This These will be pushed down as new rows are inserted.
Before
...
importing data
After :
...
the data is imported, these rows are moved from rows 4 & 5, to rows 7 & 8.
Formatting on the data marker row
There are several formatting elements on the data marker row. This formatting will be applied to each new row of data.
- Currency number formatting on B3
- Italic font on B3
- Red font on C3
Before importing data
After the data is imported, this formatting is applied to rows 3 through 6.
Formulas
- There are two formulas
- B4 contains a SUM formula that references a data marker cell. The lower boundary will expand to include new rows.
- C3 contains a formula that references a data marker cell. The row values will be updated for each new row.
Before/After 'SUM' formula:
Before/After formula:
- The italic and red font formatting will be applied for each row of data that is imported
Before:
After:
Worksheet.ImportData
How does ExcelWriter create rows when inserting data?
What happens to things in my template, when I insert data?
Import Data (application) vs. Bind Data (template)
When data is imported into a worksheet using ExcelApplication's Worksheet.ImportData (API Reference), the following is true:
- ExcelWriter does NOT insert new rows.
- Values and formulas can overwritten by the new values.
- Formulas referencing the cells with the new data are not updated.
- Formatting is not applied to the cells containing the new values.
For example, we take the same template, but remove the data markers:
Before
When the data is imported, the SUM formula that was in cell B4 is overwritten with one of the new values. Also, the formatting in cells A4, B3, B4, and C3 is only there because it was present in the original template. Also, the background color in row 5 was removed when the data was imported.