コード例 #1
0
        /// <summary>
        /// 复制行
        /// </summary>
        /// <param name="sheet">工作表</param>
        /// <param name="startRowIndex">起始行索引</param>
        /// <param name="endRowIndex">结束行索引</param>
        /// <returns></returns>
        public static int CopyRows(this ISheet sheet, int startRowIndex, int endRowIndex)
        {
            int span             = endRowIndex - startRowIndex + 1;
            int newStartRowIndex = startRowIndex + span;

            // 插入空行
            sheet.InsertRows(newStartRowIndex, span);
            // 复制行
            for (var i = startRowIndex; i <= endRowIndex; i++)
            {
                IRow sourceRow = sheet.GetRow(i);
                IRow targetRow = sheet.GetRow(i + span);

                targetRow.Height     = sourceRow.Height;
                targetRow.ZeroHeight = sourceRow.ZeroHeight;

                // 复制单元格
                foreach (ICell sourceCell in sourceRow.Cells)
                {
                    ICell targetCell = targetRow.GetCell(sourceCell.ColumnIndex);
                    if (null == targetCell)
                    {
                        targetCell = targetRow.CreateCell(sourceCell.ColumnIndex);
                    }

                    if (null != sourceCell.CellStyle)
                    {
                        targetCell.CellStyle = sourceCell.CellStyle;
                    }
                    if (null != sourceCell.CellComment)
                    {
                        targetCell.CellComment = sourceCell.CellComment;
                    }
                    if (null != sourceCell.Hyperlink)
                    {
                        targetCell.Hyperlink = sourceCell.Hyperlink;
                    }

                    var cfrs = sourceCell.GetConditionalFormattingRules();// 复制条件样式
                    if (null != cfrs && cfrs.Length > 0)
                    {
                        targetCell.AddConditionalFormattingRules(cfrs);
                    }
                    targetCell.SetCellType(sourceCell.CellType);

                    // 复制值
                    switch (sourceCell.CellType)
                    {
                    case CellType.Numeric:
                        targetCell.SetCellValue(sourceCell.NumericCellValue);
                        break;

                    case CellType.String:
                        targetCell.SetCellValue(sourceCell.StringCellValue);
                        break;

                    case CellType.Formula:
                        targetCell.SetCellValue(sourceCell.CellFormula);
                        break;

                    case CellType.Blank:
                        targetCell.SetCellValue(sourceCell.StringCellValue);
                        break;

                    case CellType.Boolean:
                        targetCell.SetCellValue(sourceCell.BooleanCellValue);
                        break;

                    case CellType.Error:
                        targetCell.SetCellValue(sourceCell.ErrorCellValue);
                        break;
                    }
                }
            }

            // 获取模板行内的合并区域
            var regions = sheet.GetMergedRegionInfos(startRowIndex, endRowIndex, null, null);

            // 复制合并区域
            foreach (var regionInfo in regions)
            {
                regionInfo.FirstRow += span;
                regionInfo.LastRow  += span;
                sheet.AddMergedRegion(regionInfo);
            }
            // 获取模板行内的图片
            var pictures = sheet.GetAllPictureInfos(startRowIndex, endRowIndex, null, null);

            // 复制图片
            foreach (var pictureInfo in pictures)
            {
                pictureInfo.FirstRow += span;
                pictureInfo.LastRow  += span;
                sheet.AddPicture(pictureInfo);
            }

            return(span);
        }
コード例 #2
0
        /// <summary>
        /// 复制源行到目标行(忽略合并单元格等信息)
        /// </summary>
        /// <param name="sourceRow"></param>
        /// <param name="targetRowIndex"></param>
        /// <returns></returns>
        public static IRow CopyTo(this IRow sourceRow, int targetRowIndex)
        {
            if (targetRowIndex == sourceRow.RowNum)
            {
                throw new ArgumentException("目标行标不能等于源行标。");
            }
            ISheet currentSheet = sourceRow.Sheet;
            IRow   targetRow    = currentSheet.GetRow(targetRowIndex);

            if (targetRow != null)
            {
                currentSheet.ShiftRows(targetRowIndex, currentSheet.LastRowNum, 1);
            }
            else
            {
                targetRow = currentSheet.CreateRow(targetRowIndex);
            }
            targetRow.Height     = sourceRow.Height;
            targetRow.ZeroHeight = sourceRow.ZeroHeight;

            #region  制单元格
            foreach (var sourceCell in sourceRow.Cells)
            {
                ICell targetCell = targetRow.GetCell(sourceCell.ColumnIndex);
                if (null == targetCell)
                {
                    targetCell = targetRow.CreateCell(sourceCell.ColumnIndex);
                }
                if (null != sourceCell.CellStyle)
                {
                    targetCell.CellStyle = sourceCell.CellStyle;
                }
                if (null != sourceCell.CellComment)
                {
                    targetCell.CellComment = sourceCell.CellComment;
                }
                if (null != sourceCell.Hyperlink)
                {
                    targetCell.Hyperlink = sourceCell.Hyperlink;
                }
                var cfrs = sourceCell.GetConditionalFormattingRules();  //复制条件样式
                if (null != cfrs && cfrs.Length > 0)
                {
                    targetCell.AddConditionalFormattingRules(cfrs);
                }
                targetCell.SetCellType(sourceCell.CellType);
                #region  制值
                switch (sourceCell.CellType)
                {
                case CellType.Numeric:
                    targetCell.SetCellValue(sourceCell.NumericCellValue);
                    break;

                case CellType.String:
                    targetCell.SetCellValue(sourceCell.RichStringCellValue);
                    break;

                case CellType.Formula:
                    targetCell.SetCellFormula(sourceCell.CellFormula);
                    break;

                case CellType.Blank:
                    targetCell.SetCellValue(sourceCell.StringCellValue);
                    break;

                case CellType.Boolean:
                    targetCell.SetCellValue(sourceCell.BooleanCellValue);
                    break;

                case CellType.Error:
                    targetCell.SetCellErrorValue(sourceCell.ErrorCellValue);
                    break;
                }
                #endregion
            }
            #endregion

            return(targetRow);
        }