C# NPOI Excel 跨工作薄Workbook复制工作表Sheet |
您所在的位置：网站首页 › 如何复制excel模板 › C# NPOI Excel 跨工作薄Workbook复制工作表Sheet |
|
跨工作薄复制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 { /// /// 跨工作薄Workbook复制工作表Sheet /// /// 源工作表Sheet /// 目标工作薄Workbook /// 目标工作表Sheet名 /// 是否复制打印设置 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; } /// /// 跨工作薄Workbook复制工作表Sheet /// /// 源工作表Sheet /// 目标工作薄Workbook /// 目标工作表Sheet名 public static ISheet CrossCloneSheet(this ISheet sSheet, IWorkbook dWb, string dSheetName) { bool clonePrintSetup = true; return CrossCloneSheet(sSheet, dWb, dSheetName, clonePrintSetup); } /// /// 跨工作薄Workbook复制工作表Sheet /// /// 源工作表Sheet /// 目标工作薄Workbook 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 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 dCellStyles, List 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 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 dCellStyles, List 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 dCellStyles = new List(); List dFonts = new List(); MergerRegion(sSheet, dSheet); for (int i = sSheet.FirstRowNum; i maxColumnNum) maxColumnNum = sRow.LastCellNum; } } for (int i = 0; i |
今日新闻 |
推荐新闻 |
| CopyRight 2018-2019 办公设备维修网 版权所有 豫ICP备15022753号-3 |