We are generating SQL Server reports and rendering them to Excel format. Each report creates a new Workbook. We need to be able to open workbook1 and workbook2 and then copy a sheet1 from workbook2 into workbook1 as the last sheet.
We can do this using the Excel object model, but would prefer to use the Infragistics.Excel so that Office does not have to be installed on a server.
I have looked over the Infragistics.Excel documentation and do not see any API support for this.
Any help would be appreciated.
Kent
Currently, the Infragistics.Excel assembly does not support copying a worksheet from one workbook to another. You can submit a feature request for this here: http://devcenter.infragistics.com/Protected/RequestFeature.aspx.
In the meantime, you will need to create a new worksheet in the other workbook and manually copy the contents from the original worksheet to the new worksheet.
Mike, thanks for the response. We started down the path you suggested of manually copying. The content was easy but the formatting, especially of merged cells got tricky and we ran out of time to get the quality of output we needed. The Infragistics.Excel library supported a cell style object, but would not allow you to set the style/format of a cell in one workbook with a instances of that style object from another.
We ended up staying with the MS Excel .net interop for now. Since we wanted this to run as a scheduled task, we had to set up an interactive login on a PC that the task was running under. Excel won't play nice in the background.
I will submit a feature request for this .
Thanks
farmcredit17 said:The Infragistics.Excel library supported a cell style object, but would not allow you to set the style/format of a cell in one workbook with a instances of that style object from another.
Maybe I misunderstood this, but are you saying you would like a way to copy over all style properties from one cell format instance to another. If so, there is a SetFormatting method on the IWorksheetCellFormat insterface which will do just that. But if I misunderstood this, what functionality were you looking for?
Is this feature now added to the Excel library? And if manual transfer of row data and formatting is required to be done, how to do that? Can you please help?
No, this feature is currently not implemented. You can submit a feature request for this and that will increase the likelihood that it will get implemented.
As for manually copying over data, I suggest using and foreach statement to iterate through the Worksheet.Rows collection. This will only return the rows which have been accessed, which means they probably have cell data and/or formatting. For each row iterated, copy over the row's height and formatting and then iterate the row's Cells collection, again using a foreach statement, because that will only return the cells with data and/or formatting. For each cell iterated, copy over the cell's formula if present. Otherwise, copy over the value. Then copy over the cell's CellFormat using the SetFormatting method listed above. After iterating the Rows collection, also iterate the Worksheet.Columns collection (again using a foreach statement). Copy over the formatting and width of each column.
Still not?
I'm also having a problem with auto updating the chart. Hope to find the answer here.
Hi thank you for your answer. Currently I am trying to auto update a chart when I insert a new column in a worksheet. How can I do it ?
Hi,
No, there's no such feature at this time.
Hi Mark, has the feature of copying a worksheet from a workbook to another workbook been implemented yet ?
Thank you in advance