跨工作薄复制Sheet，并不是单纯的将Sheet的数据复制到新Sheet中，需要将数据、公式等包括数据格式(DataFormat)，单元格的风格(CellStyle)等等都复制到新Sheet中。
NPOI目前的版本为2.5.1，其Excel处理已经可以较好的支持XSSF(2007及以上)与HSSF(2003及以下)各自的工作薄间的Sheet拷贝，但XSSF工作薄与HSSF工作薄间的Sheet拷贝仍未实现。而2.4.1版的HSSF的Sheet拷贝也并未完善，虽然大部分功能已实现，颜色上有异常，2.5.1版已正常。为了处理各种情况下的Sheet的拷贝，网上有比较多的示例，比较成功的示例虽然有所不同但处理方式大同小异，有些处理考虑也有不到的地方。可参考：NPOI中如何复制Sheet，poi操作excel，复制sheet，复制行，复制单元格。由于某些原因，此处只考虑NPOI 2.4.1的版本下的处理，某些不同的版本可能会有些不同。
处理思路：Sheet的复制主要考虑的就是单元格Cell，分Cell的数据与风格。数据处理网上有许多成功示例，请自寻不赘述。风格处理包括：单元格的各种情况，其中重点是字体与颜色的处理(私设：这也是目前NPOI未解决得好的原因吧 :-p )。本人参考了许多示例，Sheet复制已经能够基本解决，但一直未能处理好颜色情况(包括字体颜色)，注意：此处指HSSF与XSSF间的相互或自我复制的不同情况。以下出现的代码示例中，有关颜色部分被屏蔽，呵呵，虽未成功但一直努力中……如有哪位能够解决并提供代码的话，那么基本上NPOI的Sheet复制基本上就成功了。当然，仍然有其它的方面需要解决(如：Chart、Picture……)，但对于一般的代码应用，估计差不多了吧。
不多说了……翠花，上酸菜——
public static class NPOIExt{ /// <summary> /// 跨工作薄Workbook复制工作表Sheet /// </summary> /// <param name="sSheet">源工作表Sheet</param> /// <param name="dWb">目标工作薄Workbook</param> /// <param name="dSheetName">目标工作表Sheet名</param> /// <param name="clonePrintSetup">是否复制打印设置</param> public static ISheet CrossCloneSheet(this ISheet sSheet, IWorkbook dWb, string dSheetName, bool clonePrintSetup) { ISheet dSheet; dSheetName = string.IsNullOrEmpty(dSheetName) ? sSheet.SheetName : dSheetName; dSheetName = (dWb.GetSheet(dSheetName) == null) ? dSheetName : dSheetName + "_拷贝"; dSheet = dWb.GetSheet(dSheetName) ?? dWb.CreateSheet(dSheetName); CopySheet(sSheet, dSheet); if (clonePrintSetup) ClonePrintSetup(sSheet, dSheet); dWb.SetActiveSheet(dWb.GetSheetIndex(dSheet)); //当前Sheet作为下次打开默认Sheet return dSheet; } /// <summary> /// 跨工作薄Workbook复制工作表Sheet /// </summary> /// <param name="sSheet">源工作表Sheet</param> /// <param name="dWb">目标工作薄Workbook</param> /// <param name="dSheetName">目标工作表Sheet名</param> public static ISheet CrossCloneSheet(this ISheet sSheet, IWorkbook dWb, string dSheetName) { bool clonePrintSetup = true; return CrossCloneSheet(sSheet, dWb, dSheetName, clonePrintSetup); } /// <summary> /// 跨工作薄Workbook复制工作表Sheet /// </summary> /// <param name="sSheet">源工作表Sheet</param> /// <param name="dWb">目标工作薄Workbook</param> public static ISheet CrossCloneSheet(this ISheet sSheet, IWorkbook dWb) { string dSheetName = sSheet.SheetName; bool clonePrintSetup = true; return CrossCloneSheet(sSheet, dWb, dSheetName, clonePrintSetup); } private static IFont FindFont(this IWorkbook dWb, IFont font, List<IFont> dFonts) { //IFont dFont = dWb.FindFont(font.Boldweight, font.Color, (short)font.FontHeight, font.FontName, font.IsItalic, font.IsStrikeout, font.TypeOffset, font.Underline); IFont dFont = null; foreach (IFont currFont in dFonts) { //if (currFont.Charset != font.Charset) continue; //else //if (currFont.Color != font.Color) continue; //else if (currFont.FontName != font.FontName) continue; else if (currFont.FontHeight != font.FontHeight) continue; else if (currFont.IsBold != font.IsBold) continue; else if (currFont.IsItalic != font.IsItalic) continue; else if (currFont.IsStrikeout != font.IsStrikeout) continue; else if (currFont.Underline != font.Underline) continue; else if (currFont.TypeOffset != font.TypeOffset) continue; else { dFont = currFont; break; } } return dFont; } private static ICellStyle FindStyle(this IWorkbook dWb, IWorkbook sWb, ICellStyle style, List<ICellStyle> dCellStyles, List<IFont> dFonts) { ICellStyle dStyle = null; foreach (ICellStyle currStyle in dCellStyles) { if (currStyle.Alignment != style.Alignment) continue; else if (currStyle.VerticalAlignment != style.VerticalAlignment) continue; else if (currStyle.BorderTop != style.BorderTop) continue; else if (currStyle.BorderBottom != style.BorderBottom) continue; else if (currStyle.BorderLeft != style.BorderLeft) continue; else if (currStyle.BorderRight != style.BorderRight) continue; else if (currStyle.TopBorderColor != style.TopBorderColor) continue; else if (currStyle.BottomBorderColor != style.BottomBorderColor) continue; else if (currStyle.LeftBorderColor != style.LeftBorderColor) continue; else if (currStyle.RightBorderColor != style.RightBorderColor) continue; //else if (currStyle.BorderDiagonal != style.BorderDiagonal) continue; //else if (currStyle.BorderDiagonalColor != style.BorderDiagonalColor) continue; //else if (currStyle.BorderDiagonalLineStyle != style.BorderDiagonalLineStyle) continue; //else if (currStyle.FillBackgroundColor != style.FillBackgroundColor) continue; //else if (currStyle.FillBackgroundColorColor != style.FillBackgroundColorColor) continue; //else if (currStyle.FillForegroundColor != style.FillForegroundColor) continue; //else if (currStyle.FillForegroundColorColor != style.FillForegroundColorColor) continue; //else if (currStyle.FillPattern != style.FillPattern) continue; else if (currStyle.Indention != style.Indention) continue; else if (currStyle.IsHidden != style.IsHidden) continue; else if (currStyle.IsLocked != style.IsLocked) continue; else if (currStyle.Rotation != style.Rotation) continue; else if (currStyle.ShrinkToFit != style.ShrinkToFit) continue; else if (currStyle.WrapText != style.WrapText) continue; else if (!currStyle.GetDataFormatString().Equals(style.GetDataFormatString())) continue; else { IFont sFont = sWb.GetFontAt(style.FontIndex); IFont dFont = dWb.FindFont(sFont, dFonts); if (dFont == null) continue; else { currStyle.SetFont(dFont); dStyle = currStyle; break; } } } return dStyle; } private static IFont CopyFont(this IFont dFont, IFont sFont, List<IFont> dFonts) { //dFont.Charset = sFont.Charset; //dFont.Color = sFont.Color; dFont.FontHeight = sFont.FontHeight; dFont.FontName = sFont.FontName; dFont.IsBold = sFont.IsBold; dFont.IsItalic = sFont.IsItalic; dFont.IsStrikeout = sFont.IsStrikeout; dFont.Underline = sFont.Underline; dFont.TypeOffset = sFont.TypeOffset; dFonts.Add(dFont); return dFont; } private static ICellStyle CopyStyle(this ICellStyle dCellStyle, ICellStyle sCellStyle, IWorkbook dWb, IWorkbook sWb, List<ICellStyle> dCellStyles, List<IFont> dFonts) { ICellStyle currCellStyle = dCellStyle; currCellStyle.Alignment = sCellStyle.Alignment; currCellStyle.VerticalAlignment = sCellStyle.VerticalAlignment; currCellStyle.BorderTop = sCellStyle.BorderTop; currCellStyle.BorderBottom = sCellStyle.BorderBottom; currCellStyle.BorderLeft = sCellStyle.BorderLeft; currCellStyle.BorderRight = sCellStyle.BorderRight; currCellStyle.TopBorderColor = sCellStyle.TopBorderColor; currCellStyle.LeftBorderColor = sCellStyle.LeftBorderColor; currCellStyle.RightBorderColor = sCellStyle.RightBorderColor; currCellStyle.BottomBorderColor = sCellStyle.BottomBorderColor; //dCellStyle.BorderDiagonal = sCellStyle.BorderDiagonal; //dCellStyle.BorderDiagonalColor = sCellStyle.BorderDiagonalColor; //dCellStyle.BorderDiagonalLineStyle = sCellStyle.BorderDiagonalLineStyle; //dCellStyle.FillBackgroundColor = sCellStyle.FillBackgroundColor; //dCellStyle.FillForegroundColor = sCellStyle.FillForegroundColor; //dCellStyle.FillPattern = sCellStyle.FillPattern; currCellStyle.Indention = sCellStyle.Indention; currCellStyle.IsHidden = sCellStyle.IsHidden; currCellStyle.IsLocked = sCellStyle.IsLocked; currCellStyle.Rotation = sCellStyle.Rotation; currCellStyle.ShrinkToFit = sCellStyle.ShrinkToFit; currCellStyle.WrapText = sCellStyle.WrapText; currCellStyle.DataFormat = dWb.CreateDataFormat().GetFormat(sWb.CreateDataFormat().GetFormat(sCellStyle.DataFormat)); IFont sFont = sCellStyle.GetFont(sWb); IFont dFont = dWb.FindFont(sFont, dFonts) ?? dWb.CreateFont().CopyFont(sFont, dFonts); currCellStyle.SetFont(dFont); dCellStyles.Add(currCellStyle); return currCellStyle; } private static void CopySheet(ISheet sSheet, ISheet dSheet) { var maxColumnNum = 0; List<ICellStyle> dCellStyles = new List<ICellStyle>(); List<IFont> dFonts = new List<IFont>(); MergerRegion(sSheet, dSheet); for (int i = sSheet.FirstRowNum; i <= sSheet.LastRowNum; i++) { IRow sRow = sSheet.GetRow(i); IRow dRow = dSheet.CreateRow(i); if (sRow != null) { CopyRow(sRow, dRow, dCellStyles, dFonts); if (sRow.LastCellNum > maxColumnNum) maxColumnNum = sRow.LastCellNum; } } for (int i = 0; i <= maxColumnNum; i++) dSheet.SetColumnWidth(i, sSheet.GetColumnWidth(i)); } private static void CopyRow(IRow sRow, IRow dRow, List<ICellStyle> dCellStyles, List<IFont> dFonts) { dRow.Height = sRow.Height; ISheet sSheet = sRow.Sheet; ISheet dSheet = dRow.Sheet; for (int j = sRow.FirstCellNum; j <= sRow.LastCellNum; j++) { NPOI.SS.UserModel.ICell sCell = sRow.GetCell(j); NPOI.SS.UserModel.ICell dCell = dRow.GetCell(j); if (sCell != null) { if (dCell == null) dCell = dRow.CreateCell(j); CopyCell(sCell, dCell, dCellStyles, dFonts); } } } private static void CopyCell(NPOI.SS.UserModel.ICell sCell, NPOI.SS.UserModel.ICell dCell, List<ICellStyle> dCellStyles, List<IFont> dFonts) { ICellStyle currCellStyle = dCell.Sheet.Workbook.FindStyle(sCell.Sheet.Workbook, sCell.CellStyle, dCellStyles, dFonts); if (currCellStyle == null) currCellStyle = dCell.Sheet.Workbook.CreateCellStyle().CopyStyle(sCell.CellStyle, dCell.Sheet.Workbook, sCell.Sheet.Workbook, dCellStyles, dFonts); dCell.CellStyle = currCellStyle; switch (sCell.CellType) { case CellType.String: dCell.SetCellValue(sCell.StringCellValue); break; case CellType.Numeric: dCell.SetCellValue(sCell.NumericCellValue); break; case CellType.Blank: dCell.SetCellType(CellType.Blank); break; case CellType.Boolean: dCell.SetCellValue(sCell.BooleanCellValue); break; case CellType.Error: dCell.SetCellValue(sCell.ErrorCellValue); break; case CellType.Formula: dCell.SetCellFormula(sCell.CellFormula); break; default: break; } } private static void MergerRegion(ISheet sSheet, ISheet dSheet) { int sheetMergerCount = sSheet.NumMergedRegions; for (int i = 0; i < sheetMergerCount; i++) dSheet.AddMergedRegion(sSheet.GetMergedRegion(i)); } private static void ClonePrintSetup(ISheet sSheet, ISheet dSheet) { //工作表Sheet页面打印设置 dSheet.PrintSetup.Copies = 1; //打印份数 dSheet.PrintSetup.PaperSize = sSheet.PrintSetup.PaperSize; //纸张大小 dSheet.PrintSetup.Landscape = sSheet.PrintSetup.Landscape; //纸张方向：默认纵向false(横向true) dSheet.PrintSetup.Scale = sSheet.PrintSetup.Scale; //缩放方式比例 dSheet.PrintSetup.FitHeight = sSheet.PrintSetup.FitHeight; //调整方式页高 dSheet.PrintSetup.FitWidth = sSheet.PrintSetup.FitWidth; //调整方式页宽 dSheet.PrintSetup.FooterMargin = sSheet.PrintSetup.FooterMargin; dSheet.PrintSetup.HeaderMargin = sSheet.PrintSetup.HeaderMargin; //页边距 dSheet.SetMargin(MarginType.TopMargin, sSheet.GetMargin(MarginType.TopMargin)); dSheet.SetMargin(MarginType.BottomMargin, sSheet.GetMargin(MarginType.BottomMargin)); dSheet.SetMargin(MarginType.LeftMargin, sSheet.GetMargin(MarginType.LeftMargin)); dSheet.SetMargin(MarginType.RightMargin, sSheet.GetMargin(MarginType.RightMargin)); dSheet.SetMargin(MarginType.HeaderMargin, sSheet.GetMargin(MarginType.HeaderMargin)); dSheet.SetMargin(MarginType.FooterMargin, sSheet.GetMargin(MarginType.FooterMargin)); //页眉页脚 dSheet.Header.Left = sSheet.Header.Left; dSheet.Header.Center = sSheet.Header.Center; dSheet.Header.Right = sSheet.Header.Right; dSheet.Footer.Left = sSheet.Footer.Left; dSheet.Footer.Center = sSheet.Footer.Center; dSheet.Footer.Right = sSheet.Footer.Right; //工作表Sheet参数设置 dSheet.IsPrintGridlines = sSheet.IsPrintGridlines; //true: 打印整表网格线。不单独设置CellStyle时外框实线内框虚线。 false: 自己设置网格线 dSheet.FitToPage = sSheet.FitToPage; //自适应页面 dSheet.HorizontallyCenter = sSheet.HorizontallyCenter; //打印页面为水平居中 dSheet.VerticallyCenter = sSheet.VerticallyCenter; //打印页面为垂直居中 dSheet.RepeatingRows = sSheet.RepeatingRows; //工作表顶端标题行范围 }}C# NPOI Excel 跨工作薄Workbook复制工作表Sheet执御、 crowd、 jpgoodbuy、 卖家注意！美国快递企业老板跑路，数百万货物不翼而飞！、 2018年速卖通双11发货、退货、入仓规则说明！、 欧洲遇史上最热夏天，中国电风扇卖爆了！、 组团去香港迪士尼海洋公园、 组团去新加坡马来西亚、 组团去新加坡马来西亚、
没有评论:
发表评论