C#NPOIExcel跨⼯作薄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 2.4.1的版本下的处理，某些不同的版本可能会有些不同。
处理思路：Sheet的复制主要考虑的就是单元格Cell，分Cell的数据与风格。数据处理⽹上有许多成功⽰例，请⾃寻不赘述。风格处理包括：单元格的各种情况，其中重点是字体与颜⾊的处理(私设：这也是⽬前NPOI未解决得好的原因吧 :-p )。本⼈参考了许多⽰例，Sheet复制已经能够基本解决，但⼀直未能处理好颜⾊情况(包括字体颜⾊)，注意：此处指HSSF与XSSF间的相互或⾃我复制的不同情况。以下出现的代码⽰例中，有关颜⾊部分被屏蔽，呵呵，虽未成功但⼀直努⼒中……如有哪位能够解决并提供代码的话，那么基本上NPOI的Sheet复制基本上就成功了。当然，仍然有其它的⽅⾯需要解决(如：Chart、Picture……)，但对于⼀般的代码应⽤，估计差不多了吧。
不多说了……翠花，上酸菜——
public static class NPOIExt
{
///<summary>
///跨⼯作薄Workbook复制⼯作表Sheet
///</summary>
brands///<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>
category是什么意思
///跨⼯作薄Workbook复制⼯作表Sheet
///</summary>
///<param name="sSheet">源⼯作表Sheet</param>
///<param name="dWb">⽬标⼯作薄Workbook</param>
submarine///<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.TypeOfft, font.Underline);
IFont dFont = null;
foreach (IFont currFont in dFonts)
{
//if (currFont.Chart != font.Chart) continue;
//el
//if (currFont.Color != font.Color) continue;
//el
if (currFont.FontName != font.FontName) continue;
el if (currFont.FontHeight != font.FontHeight) continue;
el if (currFont.IsBold != font.IsBold) continue;
el if (currFont.IsItalic != font.IsItalic) continue;
el if (currFont.IsStrikeout != font.IsStrikeout) continue;
el if (currFont.Underline != font.Underline) continue;
el if (currFont.TypeOfft != font.TypeOfft) continue;
el { 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;
el if (currStyle.VerticalAlignment != style.VerticalAlignment) continue;
el if (currStyle.BorderTop != style.BorderTop) continue;
el if (currStyle.BorderBottom != style.BorderBottom) continue;
el if (currStyle.BorderLeft != style.BorderLeft) continue;
el if (currStyle.BorderRight != style.BorderRight) continue;
el if (currStyle.TopBorderColor != style.TopBorderColor) continue;
el if (currStyle.BottomBorderColor != style.BottomBorderColor) continue;
el if (currStyle.LeftBorderColor != style.LeftBorderColor) continue;
el if (currStyle.RightBorderColor != style.RightBorderColor) continue;
//el if (currStyle.BorderDiagonal != style.BorderDiagonal) continue;
//el if (currStyle.BorderDiagonalColor != style.BorderDiagonalColor) continue;
//el if (currStyle.BorderDiagonalLineStyle != style.BorderDiagonalLineStyle) continue;
//el if (currStyle.FillBackgroundColor != style.FillBackgroundColor) continue;
//el if (currStyle.FillBackgroundColorColor != style.FillBackgroundColorColor) continue;
//el if (currStyle.FillForegroundColor != style.FillForegroundColor) continue;
//el if (currStyle.FillForegroundColorColor != style.FillForegroundColorColor) continue;
//el if (currStyle.FillPattern != style.FillPattern) continue;
el if (currStyle.Indention != style.Indention) continue;
el if (currStyle.IsHidden != style.IsHidden) continue;
el if (currStyle.IsLocked != style.IsLocked) continue;
kitchenel if (currStyle.Rotation != style.Rotation) continue;
el if (currStyle.ShrinkToFit != style.ShrinkToFit) continue;
el if (currStyle.WrapText != style.WrapText) continue;
el if (!currStyle.GetDataFormatString().Equals(style.GetDataFormatString())) continue;
el
{
IFont sFont = sWb.GetFontAt(style.FontIndex);
IFont dFont = dWb.FindFont(sFont, dFonts);
if (dFont == null) continue;
el
{
currStyle.SetFont(dFont);
dStyle = currStyle;
break;
}
}
}
return dStyle;
}
private static IFont CopyFont(this IFont dFont, IFont sFont, List<IFont> dFonts)
radiology{
//dFont.Chart = sFont.Chart;
//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.TypeOfft = sFont.TypeOfft;
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;getudto
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);
takecareofif (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.UrModel.ICell sCell = sRow.GetCell(j);
NPOI.SS.UrModel.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.UrModel.ICell sCell, NPOI.SS.UrModel.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.Wor
kbook, sCell.Sheet.Workbook, dCellStyles, dFonts); dCell.CellStyle = currCellStyle;
switch (sCell.CellType)
{
ca CellType.String:
dCell.SetCellValue(sCell.StringCellValue);
break;
ca CellType.Numeric:
dCell.SetCellValue(sCell.NumericCellValue);
break;
ca CellType.Blank:
善待生命dCell.SetCellType(CellType.Blank);
break;
ca CellType.Boolean:
dCell.SetCellValue(sCell.BooleanCellValue);
break;
ca CellType.Error:
dCell.SetCellValue(sCell.ErrorCellValue);
break;
ca 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; //纸张⽅向：默认纵向fal(横向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时外框实线内框虚线。 fal: ⾃⼰设置⽹格线 dSheet.FitToPage = sSheet.FitToPage; //⾃适应页⾯
dSheet.HorizontallyCenter = sSheet.HorizontallyCenter; //打印页⾯为⽔平居中
dSheet.VerticallyCenter = sSheet.VerticallyCenter; //打印页⾯为垂直居中
dSheet.RepeatingRows = sSheet.RepeatingRows; //⼯作表顶端标题⾏范围
}
}
