Copying Worksheets between Workbooks

Apr 19, 2011 at 6:10 PM

Has anyone been actively working on copying worksheets between workbooks? For example, I have 2 existing workbooks. One workbook has a worksheet that I need to copy into the other workbook. So far, the process (even from the POI project) seems to be a manual activity. I found a thread on the POI forums that has some recent activity, but all the code is in Java. I've been manually converting the code but I don't want to duplicate anyone else's existing activity. The procedure that they are using is not exactly feature complete, but would probably be very useful to people that just need to do a basic copy of the worksheet between workbooks.

If anyone else is actively working on this or has a need for it, let me know. I'm planning to upload it as a patch anyway, but might get it uploaded more quickly if others have an interest.

Thanks.

Coordinator
Apr 22, 2011 at 10:29 PM

Noone is working on this feature. Please try to convert it from Java. :)

Apr 27, 2011 at 1:50 PM

Okay, I have some preliminary testing done and the sheets are coming out looking pretty good. I think there is some additional error checking that is needed but I should be able to post some source code to the system by the weekend. As I said, this will be some really basic copying, but the routine is currently copying all cells, including merges, the associated styles, formula(s), and page/print settings. I hope that this will meet the needs of most people until a more robust version enters the POI/NPOI source.

Coordinator
Apr 29, 2011 at 2:00 AM

Sounds cool :-)  When the code is ready, please sent it to tonyqus (at) gmail.com. I'll help review the code and check where is suitable to put it.

Apr 29, 2011 at 5:29 PM

Here is the code that I have to date. While the CopySheet works properly to copy a sheet within the same workbook, the routine to copy a sheet to a different workbook is resulting in a corrupt sheet with cells in incorrect places. Everything appears correct if I reference various cells as a spot check, but the final saved workbook has numerous invalid cells. I'll be continuing to work on this but I'm open to suggestions. This source code is intended to be inserted into the HSSFSheet class.

        // Below code imported from:
        // http://www.coderanch.com/t/420958/open-source/Copying-sheet-excel-file-another
        // thanks to: Pierre Guilbert, 2011-04-14
        //
        // Original code comments:
        /**
         *
         * @author jk
         * getted from http://jxls.cvs.sourceforge.net/jxls/jxls/src/java/org/jxls/util/Util.java?revision=1.8&view=markup
         * by Leonid Vysochyn 
         * and modified (adding styles copying)
         * modified by Philipp L�pmeier (replacing deprecated classes and methods, using generic types)
         */
        //
        // extensive code changes required to reconfigure for C# from Java syntax
        // changed case to standard .NET case

        // CopySheet is working 100% (as far as currently defined, at least)
        // Needs comments very badly
        public HSSFSheet CopySheet()
        {
            return CopySheet(string.Concat(SheetName, " - Copy"), true);
        }

        public HSSFSheet CopySheet(String Name)
        {
            return CopySheet(Name, true);
        }

        public HSSFSheet CopySheet(Boolean CopyStyle)
        {
            return CopySheet(string.Concat(SheetName, " - Copy"), CopyStyle);
        }

        public HSSFSheet CopySheet(String Name, Boolean CopyStyle)
        {
            int maxColumnNum = 0;
            HSSFSheet NewSheet = (HSSFSheet)Workbook.CreateSheet(Name);
            NewSheet.Sheet.Records.Clear();
            for (int i = 0; i < Sheet.Records.Count; i++)
            {
                NewSheet.Sheet.Records.Add(Sheet.Records[i]);
            }
            IDictionary<Int32, HSSFCellStyle> styleMap = (CopyStyle) ? new Dictionary<Int32, HSSFCellStyle>() : null;
            for (int i = FirstRowNum; i <= LastRowNum; i++)
            {
                HSSFRow srcRow = (HSSFRow)GetRow(i);
                HSSFRow destRow = (HSSFRow)NewSheet.CreateRow(i);
                if (srcRow != null)
                {
                    CopyRow(this, NewSheet, srcRow, destRow, styleMap);
                    if (srcRow.LastCellNum > maxColumnNum)
                    {
                        maxColumnNum = srcRow.LastCellNum;
                    }
                }
            }
            for (int i = 0; i <= maxColumnNum; i++)
            {
                NewSheet.SetColumnWidth(i, GetColumnWidth(i));
            }
            NewSheet.ForceFormulaRecalculation = true;
            NewSheet.PrintSetup.Landscape = PrintSetup.Landscape;
            NewSheet.PrintSetup.HResolution = PrintSetup.HResolution;
            NewSheet.PrintSetup.VResolution = PrintSetup.VResolution;
            NewSheet.SetMargin(MarginType.LeftMargin, GetMargin(MarginType.LeftMargin));
            NewSheet.SetMargin(MarginType.RightMargin, GetMargin(MarginType.RightMargin));
            NewSheet.SetMargin(MarginType.TopMargin, GetMargin(MarginType.TopMargin));
            NewSheet.SetMargin(MarginType.BottomMargin, GetMargin(MarginType.BottomMargin));
            NewSheet.PrintSetup.HeaderMargin = PrintSetup.HeaderMargin;
            NewSheet.PrintSetup.FooterMargin = PrintSetup.FooterMargin;
            NewSheet.Header.Left = Header.Left;
            NewSheet.Header.Center = Header.Center;
            NewSheet.Header.Right = Header.Right;
            NewSheet.Footer.Left = Footer.Left;
            NewSheet.Footer.Center = Footer.Center;
            NewSheet.Footer.Right = Footer.Right;
            NewSheet.PrintSetup.Scale = PrintSetup.Scale;
            NewSheet.PrintSetup.FitHeight = PrintSetup.FitHeight;
            NewSheet.PrintSetup.FitWidth = PrintSetup.FitWidth;
            return NewSheet;
        }

        public void CopyTo(HSSFWorkbook Dest, String Name, Boolean CopyStyle, Boolean KeepFormulas)
        {
            int maxColumnNum = 0;
            HSSFSheet NewSheet = (HSSFSheet)Dest.CreateSheet(Name);
            NewSheet.Sheet.Records.Clear();
            for (int i = 0; i < Sheet.Records.Count; i++)
            {
                NewSheet.Sheet.Records.Add(Sheet.Records[i]);
            }
            IDictionary<Int32, HSSFCellStyle> styleMap = (CopyStyle) ? new Dictionary<Int32, HSSFCellStyle>() : null;
            for (int i = FirstRowNum; i <= LastRowNum; i++)
            {
                HSSFRow srcRow = (HSSFRow)GetRow(i);
                HSSFRow destRow = (HSSFRow)NewSheet.CreateRow(i);
                if (srcRow != null)
                {
                    CopyRow(this, NewSheet, srcRow, destRow, styleMap, KeepFormulas);
                    if (srcRow.LastCellNum > maxColumnNum)
                    {
                        maxColumnNum = srcRow.LastCellNum;
                    }
                }
            }
            for (int i = 0; i < maxColumnNum; i++)
            {
                NewSheet.SetColumnWidth(i, GetColumnWidth(i));
            }
            NewSheet.ForceFormulaRecalculation = true;
            NewSheet.PrintSetup.Landscape = PrintSetup.Landscape;
            NewSheet.PrintSetup.HResolution = PrintSetup.HResolution;
            NewSheet.PrintSetup.VResolution = PrintSetup.VResolution;
            NewSheet.SetMargin(MarginType.LeftMargin, GetMargin(MarginType.LeftMargin));
            NewSheet.SetMargin(MarginType.RightMargin, GetMargin(MarginType.RightMargin));
            NewSheet.SetMargin(MarginType.TopMargin, GetMargin(MarginType.TopMargin));
            NewSheet.SetMargin(MarginType.BottomMargin, GetMargin(MarginType.BottomMargin));
            NewSheet.PrintSetup.HeaderMargin = PrintSetup.HeaderMargin;
            NewSheet.PrintSetup.FooterMargin = PrintSetup.FooterMargin;
            NewSheet.Header.Left = Header.Left;
            NewSheet.Header.Center = Header.Center;
            NewSheet.Header.Right = Header.Right;
            NewSheet.Footer.Left = Footer.Left;
            NewSheet.Footer.Center = Footer.Center;
            NewSheet.Footer.Right = Footer.Right;
            NewSheet.PrintSetup.Scale = PrintSetup.Scale;
            NewSheet.PrintSetup.FitHeight = PrintSetup.FitHeight;
            NewSheet.PrintSetup.FitWidth = PrintSetup.FitWidth;
        }

        private static void CopyRow(HSSFSheet srcSheet, HSSFSheet destSheet, HSSFRow srcRow, HSSFRow destRow, IDictionary<Int32, HSSFCellStyle> styleMap)
        {
            CopyRow(srcSheet, destSheet, srcRow, destRow, styleMap, true);
        }

        private static void CopyRow(HSSFSheet srcSheet, HSSFSheet destSheet, HSSFRow srcRow, HSSFRow destRow, IDictionary<Int32, HSSFCellStyle> styleMap, Boolean KeepFormulas)
        {
            System.Collections.Generic.List<SS.Util.CellRangeAddress> mergedRegions = destSheet.Sheet.MergedRecords.MergedRegions;
            destRow.Height = srcRow.Height;
            for (int j = srcRow.FirstCellNum; j <= srcRow.LastCellNum; j++)
            {
                HSSFCell oldCell = (HSSFCell)srcRow.GetCell(j);
                HSSFCell newCell = (HSSFCell)destRow.GetCell(j);
                if (srcSheet.Workbook == destSheet.Workbook)
                {
                    newCell = (HSSFCell)destRow.GetCell(j);
                }
                if (oldCell != null)
                {
                    if (newCell == null)
                    {
                        newCell = (HSSFCell)destRow.CreateCell(j);
                    }
                    CopyCell(oldCell, newCell, styleMap, KeepFormulas);
                    CellRangeAddress mergedRegion = GetMergedRegion(srcSheet, srcRow.RowNum, (short)oldCell.ColumnIndex);
                    if (mergedRegion != null)
                    {
                        //System.out.println(mergedRegion.toString());
                        CellRangeAddress newMergedRegion =
                                new CellRangeAddress(
                                mergedRegion.FirstRow,
                                mergedRegion.LastRow,
                                mergedRegion.FirstColumn,
                                mergedRegion.LastColumn);

                        if (IsNewMergedRegion(newMergedRegion, mergedRegions))
                        {
                            mergedRegions.Add(newMergedRegion);
                        }
                    }
                }
            }
        }

        private static void CopyCell(HSSFCell oldCell, HSSFCell newCell, IDictionary<Int32, HSSFCellStyle> styleMap)
        {
            CopyCell(oldCell, newCell, styleMap, true);
        }

        // change Java Map<> to C# IDictionary<>
        private static void CopyCell(HSSFCell oldCell, HSSFCell newCell, IDictionary<Int32, HSSFCellStyle> styleMap, Boolean KeepFormulas)
        {
            if (styleMap != null)
            {
                if (oldCell.CellStyle != null)
                {
                    if (oldCell.Sheet.Workbook == newCell.Sheet.Workbook)
                    {
                        newCell.CellStyle = oldCell.CellStyle;
                    }
                    else
                    {
                        int StyleHashCode = oldCell.CellStyle.GetHashCode();
                        if (styleMap.ContainsKey(StyleHashCode))
                        {
                            HSSFCellStyle NewCellStyle = styleMap[StyleHashCode];
                        }
                        else
                        {
                            HSSFCellStyle NewCellStyle = (HSSFCellStyle)newCell.Sheet.Workbook.CreateCellStyle();
                            NewCellStyle.CloneStyleFrom(oldCell.CellStyle);
                            //newCell.CellStyle = NewCellStyle;
                            styleMap.Add(StyleHashCode, NewCellStyle);
                        }
                    }
                }
                else
                {
                    newCell.CellStyle = null;
                }
            }
            switch (oldCell.CellType)
            {
                case NPOI.SS.UserModel.CellType.STRING:
                    newCell.SetCellValue(oldCell.StringCellValue);
                    break;
                case NPOI.SS.UserModel.CellType.NUMERIC:
                    newCell.SetCellValue(oldCell.NumericCellValue);
                    break;
                case NPOI.SS.UserModel.CellType.BLANK:
                    newCell.SetCellType(NPOI.SS.UserModel.CellType.BLANK);
                    break;
                case NPOI.SS.UserModel.CellType.BOOLEAN:
                    newCell.SetCellValue(oldCell.BooleanCellValue);
                    break;
                case NPOI.SS.UserModel.CellType.ERROR:
                    newCell.SetCellValue(oldCell.ErrorCellValue);
                    break;
                case NPOI.SS.UserModel.CellType.FORMULA:
                    if (KeepFormulas)
                    {
                        newCell.SetCellType(CellType.FORMULA);
                        newCell.CellFormula = oldCell.CellFormula;
                    }
                    else
                    {
                        try
                        {
                            newCell.SetCellType(CellType.NUMERIC);
                            newCell.SetCellValue(oldCell.NumericCellValue);
                        }
                        catch (Exception ex)
                        {
                            try
                            {
                                newCell.SetCellType(CellType.STRING);
                                newCell.SetCellValue(oldCell.StringCellValue);
                            }
                            catch (Exception exInner)
                            {
                            }
                        }
                    }
                    break;
                default:
                    break;
            }
        }

        public static CellRangeAddress GetMergedRegion(HSSFSheet sheet, int rowNum, short cellNum)
        {
            for (int i = 0; i < sheet.NumMergedRegions; i++)
            {
                CellRangeAddress merged = sheet.GetMergedRegion(i);
                if (rowNum >= merged.FirstRow && rowNum <= merged.LastRow)
                {
                    if (cellNum >= merged.FirstColumn && cellNum <= merged.LastColumn)
                    {
                        return merged;
                    }
                }
            }
            return null;
        }

        // modified syntax from Java to C#
        private static Boolean AreAllTrue(params Boolean[] values)
        {
            for (int i = 0; i < values.Length; ++i)
            {
                if (values[i] != true)
                {
                    return false;
                }
            }
            return true;
        }

        private static Boolean IsNewMergedRegion(CellRangeAddress newMergedRegion,
                System.Collections.Generic.List<CellRangeAddress> mergedRegions)
        {
            Boolean isNew = true;

            // we want to check if newMergedRegion is contained inside our collection
            foreach (CellRangeAddress add in mergedRegions)
            {
                Boolean r1 = (add.FirstRow == newMergedRegion.FirstRow);
                Boolean r2 = (add.LastRow == newMergedRegion.LastRow);
                Boolean c1 = (add.FirstColumn == newMergedRegion.FirstColumn);
                Boolean c2 = (add.LastColumn == newMergedRegion.LastColumn);
                if (AreAllTrue(r1, r2, c1, c2))
                {
                    isNew = false;
                }
            }
            return isNew;
        }

        // end of POI CopySheets

May 17, 2011 at 6:04 PM

Okay, this won't be happening for a while. Everything looks good when I sample the various rows and columns while debugging, but once the new file is actually saved, everything is scrambled. The cells are merged properly, but the contents of cells are displayed haphazardly throughout the cells and styling is incorrect. I'm sure there's a group of records that I'm failing to copy or something I'm not initializing, but I've run out of time to pursue this. Copying within the same workbook works pretty well, although I haven't tested with graphics and charts. Maybe this will give someone a base that they can continue to build from. I'll come back to it when I have time, but it's going to be a couple of months at least.

May 24, 2012 at 2:25 AM

I am having the same problem. Copying content in the same workbook works perfect but from different book, it will lead to a format error when open the file. Is this a limitation of Apache POI?