推荐设备MORE

电商版小程序—微信红包

电商版小程序—微信红包

行业知识

C# NPOI Excel 跨工作中薄Workbook拷贝工作中表Sheet,

日期:2021-02-19
我要分享

C# NPOI Excel 跨工作中薄Workbook拷贝工作中表Sheet,做c

跨工作中薄拷贝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; //工作中表顶部题目行范畴
}

 

dengb.TechArticleC# NPOI Excel 跨工作中薄Workbook拷贝工作中表Sheet,做c 跨工作中薄拷贝Sheet,其实不是单纯性的将Sheet的数据信息拷贝到新Sheet中,必须将数据信息、公式计算等包含数...