[ASP.NET] NPOI 複製一列+移動多列 改良版

之前有發現外國寫的複製列方法,發現很多方法是沒效果的,因此自己改寫程式,可有效的複製並覆蓋一列資料。

之前有發現外國寫的複製列方法,發現很多方法是沒效果的,因此自己改寫程式,可有效的複製並覆蓋一列資料。


        /// 
        /// 複製一整列
        /// 
        private void CopyRow(ref HSSFSheet worksheet, int sourceRowNum, int destinationRowNum
                , bool IsCoverRow = false, bool IsRemoveSrcRow = false
            , bool copyRowHeight = true, bool resetOriginalRowHeight = true)
        {
            HSSFRow newRow = worksheet.GetRow(destinationRowNum) as HSSFRow;
            HSSFRow sourceRow = worksheet.GetRow(sourceRowNum) as HSSFRow;
            HSSFCell oldCell, newCell;
            int i;

            if (newRow == null)
                newRow = worksheet.CreateRow(destinationRowNum) as HSSFRow;
            else
            {
                if (!IsCoverRow)
                    ShiftRows(ref worksheet, destinationRowNum, worksheet.LastRowNum, 1);
            }

            // Loop through source columns to add to new row
            for (i = 0; i < sourceRow.LastCellNum; i++)
            {
                // Grab a copy of the old/new cell
                oldCell = sourceRow.GetCell(i) as HSSFCell;
                newCell = newRow.GetCell(i) as HSSFCell;

                if (newCell == null)
                    newCell = newRow.CreateCell(i) as HSSFCell;

                // If the old cell is null jump to next cell
                if (oldCell == null)
                {
                    newCell = null;
                    continue;
                }

                // Copy style from old cell and apply to new cell
                newCell.CellStyle = oldCell.CellStyle;

                // If there is a cell comment, copy
                if (newCell.CellComment != null) newCell.CellComment = oldCell.CellComment;

                // If there is a cell hyperlink, copy
                if (oldCell.Hyperlink != null) newCell.Hyperlink = oldCell.Hyperlink;

                // Set the cell data value
                switch (oldCell.CellType)
                {
                    case CellType.BLANK:
                        newCell.SetCellValue(oldCell.StringCellValue);
                        break;
                    case CellType.BOOLEAN:
                        newCell.SetCellValue(oldCell.BooleanCellValue);
                        break;
                    case CellType.ERROR:
                        newCell.SetCellErrorValue(oldCell.ErrorCellValue);
                        break;
                    case CellType.FORMULA:
                        newCell.CellFormula = oldCell.CellFormula;
                        break;
                    case CellType.NUMERIC:
                        newCell.SetCellValue(oldCell.NumericCellValue);
                        break;
                    case CellType.STRING:
                        newCell.SetCellValue(oldCell.RichStringCellValue);
                        break;
                    case CellType.Unknown:
                        newCell.SetCellValue(oldCell.StringCellValue);
                        break;
                }
            }

            // If there are are any merged regions in the source row, copy to new row
            CellRangeAddress cellRangeAddress = null, newCellRangeAddress = null;
            for (i = 0; i < worksheet.NumMergedRegions; i++)
            {
                cellRangeAddress = worksheet.GetMergedRegion(i);
                if (cellRangeAddress.FirstRow == sourceRow.RowNum)
                {
                    newCellRangeAddress = new CellRangeAddress(newRow.RowNum,
                                                                                (newRow.RowNum +
                                                                                 (cellRangeAddress.LastRow -
                                                                                  cellRangeAddress.FirstRow)),
                                                                                cellRangeAddress.FirstColumn,
                                                                                cellRangeAddress.LastColumn);
                    worksheet.AddMergedRegion(newCellRangeAddress);
                }
            }

            //複製行高到新列
            if (copyRowHeight)
                newRow.Height = sourceRow.Height;
            //重製原始列行高
            if (resetOriginalRowHeight)
                sourceRow.Height = worksheet.DefaultRowHeight;
            //清掉原列
            if (IsRemoveSrcRow == true)
                worksheet.RemoveRow(sourceRow);
        }

原本的資料列移動也可以靠複製列功能達到更好的移動效果。


        /// 
        /// 客製ShiftRows,以保留原本格式
        /// 
        private void ShiftRows(ref HSSFSheet sheet, int startrow, int endrow, int n
            , bool copyRowHeight = true, bool resetOriginalRowHeight = true)
        {
            if (n == 0) return;

            int i;

            if (n > 0)
            {
                for (i = endrow; i >= startrow; i--)
                    if (sheet.GetRow(i) != null)
                        CopyRow(ref sheet, i, i + n, true, true, copyRowHeight, resetOriginalRowHeight);
            }
            else
            {
                for (i = startrow; i <= endrow; i++)
                    if (sheet.GetRow(i) != null)
                        CopyRow(ref sheet, i, i + n, true, true, copyRowHeight, resetOriginalRowHeight);
            }
        }