NPOI 复制Sheet（转载）

以下内容由chocolateboy兄所写，在此仅作为转帖

原文载于：http://blog.csdn.net/chocolateboy/article/details/9705425

 

NPOI已经发布到了2.0的版本，可喜的是该版本支持到了Excel2007，但缺憾的是没有复制SHEET表的功能，只有一个CloneSheet方法，确实没明白怎么使用。所以在网上搜索了一下，只找一个JAVA的版本，好像也是自己写的代码实现的。于是拿过来翻译了一下，复制SHEET后的格式基本没变，但单元格的宽度没有实现，ICell接口没有类似于Width的属性，只好作罢。

哪位如果知道CloneSheet方法的使用，恳请留言，免去不必要的操作和代码。

[csharp] view plain copy print ?

1. public class SheetClone
2. {
3. public staticvoid CopyCellStyle(IWorkbook wb, ICellStyle fromStyle, ICellStyle toStyle)
4. {
5. toStyle.Alignment = fromStyle.Alignment;
6. //边框和边框颜色
7. toStyle.BorderBottom = fromStyle.BorderBottom;
8. toStyle.BorderLeft = fromStyle.BorderLeft;
9. toStyle.BorderRight = fromStyle.BorderRight;
10. toStyle.BorderTop = fromStyle.BorderTop;
11. toStyle.TopBorderColor = fromStyle.TopBorderColor;
12. toStyle.BottomBorderColor = fromStyle.BottomBorderColor;
13. toStyle.RightBorderColor = fromStyle.RightBorderColor;
14. toStyle.LeftBorderColor = fromStyle.LeftBorderColor;
16. //背景和前景
17. toStyle.FillBackgroundColor = fromStyle.FillBackgroundColor;
18. toStyle.FillForegroundColor = fromStyle.FillForegroundColor;
20. toStyle.DataFormat = fromStyle.DataFormat;
21. toStyle.FillPattern = fromStyle.FillPattern;
22. //toStyle.Hidden=fromStyle.Hidden;
23. toStyle.IsHidden = fromStyle.IsHidden;
24. toStyle.Indention = fromStyle.Indention;//首行缩进
25. toStyle.IsLocked = fromStyle.IsLocked;
26. toStyle.Rotation = fromStyle.Rotation;//旋转
27. toStyle.VerticalAlignment = fromStyle.VerticalAlignment;
28. toStyle.WrapText = fromStyle.WrapText;
29. toStyle.SetFont(fromStyle.GetFont(wb));
30. }
32. /// <summary>
33. /// 复制表
34. /// </summary>
35. /// <param name="wb"></param>
36. /// <param name="fromSheet"></param>
37. /// <param name="toSheet"></param>
38. /// <param name="copyValueFlag"></param>
39. public staticvoid CopySheet(IWorkbook wb, ISheet fromSheet, ISheet toSheet,bool copyValueFlag)
40. {
41. //合并区域处理
42. MergerRegion(fromSheet, toSheet);
43. System.Collections.IEnumerator rows = fromSheet.GetRowEnumerator();
44. while (rows.MoveNext())
45. {
46. IRow row = null;
47. if (wb is HSSFWorkbook)
48. row = rows.Current as HSSFRow;
49. else
50. row = rows.Current as NPOI.XSSF.UserModel.XSSFRow;
51. IRow newRow = toSheet.CreateRow(row.RowNum);
52. CopyRow(wb, row, newRow, copyValueFlag);
53. }
54. }
56. /// <summary>
57. /// 复制行
58. /// </summary>
59. /// <param name="wb"></param>
60. /// <param name="fromRow"></param>
61. /// <param name="toRow"></param>
62. /// <param name="copyValueFlag"></param>
63. public staticvoid CopyRow(IWorkbook wb, IRow fromRow, IRow toRow,bool copyValueFlag)
64. {
65. System.Collections.IEnumerator cells = fromRow.GetEnumerator();//.GetRowEnumerator();
66. toRow.Height = fromRow.Height;
67. while (cells.MoveNext())
68. {
69. ICell cell = null;
70. //ICell cell = (wb is HSSFWorkbook) ? cells.Current as HSSFCell : cells.Current as NPOI.XSSF.UserModel.XSSFCell;
71. if (wb is HSSFWorkbook)
72. cell = cells.Current as HSSFCell;
73. else
74. cell = cells.Current as NPOI.XSSF.UserModel.XSSFCell;
75. ICell newCell = toRow.CreateCell(cell.ColumnIndex);
76. CopyCell(wb, cell, newCell, copyValueFlag);
77. }
78. }
81. /// <summary>
82. /// 复制原有sheet的合并单元格到新创建的sheet
83. /// </summary>
84. /// <param name="fromSheet"></param>
85. /// <param name="toSheet"></param>
86. public staticvoid MergerRegion(ISheet fromSheet, ISheet toSheet)
87. {
88. int sheetMergerCount = fromSheet.NumMergedRegions;
89. for (int i = 0; i < sheetMergerCount; i++)
90. {
91. //Region mergedRegionAt = fromSheet.GetMergedRegion(i); //.MergedRegionAt(i);
92. //CellRangeAddress[] cra = new CellRangeAddress[1];
93. //cra[0] = fromSheet.GetMergedRegion(i);
94. //Region[] rg = Region.ConvertCellRangesToRegions(cra);
95. toSheet.AddMergedRegion(fromSheet.GetMergedRegion(i));
96. }
97. }
99. /// <summary>
100. /// 复制单元格
101. /// </summary>
102. /// <param name="wb"></param>
103. /// <param name="srcCell"></param>
104. /// <param name="distCell"></param>
105. /// <param name="copyValueFlag"></param>
106. public staticvoid CopyCell(IWorkbook wb, ICell srcCell, ICell distCell,bool copyValueFlag)
107. {
108. ICellStyle newstyle = wb.CreateCellStyle();
109. CopyCellStyle(wb, srcCell.CellStyle, newstyle);
111. //样式
112. distCell.CellStyle = newstyle;
113. //评论
114. if (srcCell.CellComment != null)
115. {
116. distCell.CellComment = srcCell.CellComment;
117. }
118. // 不同数据类型处理
119. CellType srcCellType = srcCell.CellType;
120. distCell.SetCellType(srcCellType);
121. if (copyValueFlag)
122. {
123. if (srcCellType == CellType.NUMERIC)
124. {
126. if (HSSFDateUtil.IsCellDateFormatted(srcCell))
127. {
128. distCell.SetCellValue(srcCell.DateCellValue);
129. }
130. else
131. {
132. distCell.SetCellValue(srcCell.NumericCellValue);
133. }
134. }
135. else if (srcCellType == CellType.STRING)
136. {
137. distCell.SetCellValue(srcCell.RichStringCellValue);
138. }
139. else if (srcCellType == CellType.BLANK)
140. {
141. // nothing21
142. }
143. else if (srcCellType == CellType.BOOLEAN)
144. {
145. distCell.SetCellValue(srcCell.BooleanCellValue);
146. }
147. else if (srcCellType == CellType.ERROR)
148. {
149. distCell.SetCellErrorValue(srcCell.ErrorCellValue);
150. }
151. else if (srcCellType == CellType.FORMULA)
152. {
153. distCell.SetCellFormula(srcCell.CellFormula);
154. }
155. else
156. { // nothing29
157. }
158. }
159. }
160. }