This project has moved and is read-only. For the latest updates, please go here.

Can't copy columns from one sheet to another

Jan 26, 2015 at 9:14 AM
Edited Jan 26, 2015 at 9:46 AM
Hello
Thank you for sharing this library :)
I'm using following code to copy all columns from one sheet to another and mirror them. (mirror means first column in source worksheet will be last column in destination sheet):
            using (var package = new ExcelPackage(new FileInfo(@"C:\Users\Jalal\Desktop\Report.xlsx")))
            {
                var worksheet = package.Workbook.Worksheets.FirstOrDefault();
                if (worksheet == null)
                    return;

                var fixedWorksheet = package.Workbook.Worksheets.Add("Page2");

                int startRow = worksheet.Dimension.Start.Row;
                int startColumn = worksheet.Dimension.Start.Column;
                int endRow = worksheet.Dimension.End.Row;
                int endColumn = worksheet.Dimension.End.Column;
                //MessageBox.Show(String.Format("Rows:{0}, Columns:{1}", rows, columns));

                int currentColumn = 1;
                while (currentColumn <= endColumn)
                {
                    var localAddress = ExcelRangeBase.GetAddress(startRow, currentColumn, endRow, currentColumn);
                    var sourceColumnAddress = ExcelRangeBase.GetFullAddress(worksheet.Name, localAddress);

                    var targetAddress = ExcelRangeBase.GetAddress(startRow, endColumn, endRow, endColumn);
                    var targetColumnAddress = ExcelRangeBase.GetFullAddress(fixedWorksheet.Name, targetAddress);

                    var sourceRange = worksheet.Cells[sourceColumnAddress];
                    var targetRange = worksheet.Cells[targetColumnAddress];
                    
                    sourceRange.Copy(targetRange); // InvalidOperationException: Can't delete merged cells. A range is partly merged with the deleted range. A1:I1



                    backgroundWorker1.ReportProgress((int)((currentColumn * 1d / endColumn * 1d) * 100d));
                    currentColumn++;
                }

                package.SaveAs(new FileInfo(@"C:\Users\Jalal\Desktop\ReportMirrored.xlsx"));
            }
        }
Exception details:
System.InvalidOperationException was unhandled by user code
HResult=-2146233079
Message=Can't delete merged cells. A range is partly merged with the deleted range. A1:I1
Source=EPPlus
StackTrace:
   at OfficeOpenXml.ExcelWorksheet.MergeCellsCollection.Delete(ExcelAddressBase Destination)
   at OfficeOpenXml.ExcelRangeBase.Copy(ExcelRangeBase Destination)
   at ExcelAlignmentFixer.Form1.MirrorColumns(DoWorkEventArgs e) in c:\Users\Jalal\Documents\Visual Studio 2013\Projects\ExcelAlignmentFixer\ExcelAlignmentFixer\Form1.cs:line 67
   at ExcelAlignmentFixer.Form1.backgroundWorker1_DoWork(Object sender, DoWorkEventArgs e) in c:\Users\Jalal\Documents\Visual Studio 2013\Projects\ExcelAlignmentFixer\ExcelAlignmentFixer\Form1.cs:line 81
   at System.ComponentModel.BackgroundWorker.OnDoWork(DoWorkEventArgs e)
   at System.ComponentModel.BackgroundWorker.WorkerThreadStart(Object argument)
InnerException: null

Sample excel file: http://ge.tt/5zawbH92/v/0
EPPlus version: 4.0.2
Thanks in advance