I'm working with Infragistics3.Documents.Excel.v11.1 (v11.1.20111.2050)
1.- How can I copy a worksheet from one workbook_orig to another workbook_dest??
2.- And if the worksheet_orig have formulas in some cells, I want copy only the value.How can I do that?
Thank you in advance
Copying a worksheet is currently not supported with the Excel library and I suspect if it was, it probably would copy the formulas as well, so even if it is was implemented, you would need to do this manually. Here is how you might do this:
Worksheet sourceWorksheet = worksheet_orig.Worksheets["Sheet1"];Workbook workbook_dest = new Workbook(WorkbookFormat.Excel2007);Worksheet destination = workbook_dest.Worksheets.Add(sourceWorksheet.Name);
foreach (WorksheetColumn sourceColumn in sourceWorksheet.Columns){ WorksheetColumn destinationColumn = destination.Columns[sourceColumn.Index]; destinationColumn.CellFormat.SetFormatting(CreateFormatCopy(workbook_dest, sourceColumn.CellFormat)); destinationColumn.Width = sourceColumn.Width; destinationColumn.Hidden = sourceColumn.Hidden;}
foreach (WorksheetRow sourceRow in sourceWorksheet.Rows){ WorksheetRow destinationRow = destination.Rows[sourceRow.Index]; destinationRow.CellFormat.SetFormatting(CreateFormatCopy(workbook_dest, sourceRow.CellFormat)); destinationRow.Height = sourceRow.Height; destinationRow.Hidden = sourceRow.Hidden;
foreach (WorksheetCell sourceCell in sourceRow.Cells) { WorksheetCell destinationCell = destinationRow.Cells[sourceCell.ColumnIndex]; destinationCell.CellFormat.SetFormatting(CreateFormatCopy(workbook_dest, sourceCell.CellFormat)); destinationCell.Value = sourceCell.Value; }}
static IWorksheetCellFormat CreateFormatCopy(Workbook workbook, IWorksheetCellFormat sourceCellFormat){ IWorksheetCellFormat copy = workbook.CreateNewWorksheetCellFormat(); copy.SetFormatting(sourceCellFormat); return copy;}
Mike's solution works like charm, as long as there are is no cell that contains two different formats. In that case the date in the date in the source cell is lost.
If one has merged cells, you might want to add this at the bottom in order to copy those as well:
foreach (var regionSource in sourceWorksheet.MergedCellsRegions) { destination.MergedCellsRegions.Add(regionSource.FirstRow, regionSource.FirstColumn, regionSource.LastRow, regionSource.LastColumn); }
Same goes for shapes / images:
foreach (WorksheetImage shapeSource in sourceWorksheet.Shapes) { WorksheetImage shapeDestination = new WorksheetImage(shapeSource.Image); shapeDestination.Outline = shapeSource.Outline; shapeDestination.TopLeftCornerCell = GetSameCellInOtherWorksheet(shapeSource.TopLeftCornerCell, destination); shapeDestination.TopLeftCornerPosition = shapeSource.TopLeftCornerPosition; shapeDestination.BottomRightCornerCell = GetSameCellInOtherWorksheet(shapeSource.BottomRightCornerCell, destination); shapeDestination.BottomRightCornerPosition = shapeSource.BottomRightCornerPosition; shapeDestination.PositioningMode = shapeSource.PositioningMode; destination.Shapes.Add(shapeDestination); } static WorksheetCell GetSameCellInOtherWorksheet(WorksheetCell sourceCell, Worksheet destinationWorksheet) { return destinationWorksheet.Rows[sourceCell.RowIndex].Cells[sourceCell.ColumnIndex]; }
Hi - this code used to work in older versions, however in the latest version it yields the error:
Parameter name: value
at Infragistics.Documents.Excel.WorksheetCellFormatData.set_Style(WorkbookStyle value) at Infragistics.Documents.Excel.WorksheetCellFormatData.SetFormatting(IWorksheetCellFormat source)
Is there a workaround for this? Unfortunately, I have come to rely heavily on this.