1. using System;
    2. using NPOI.HSSF.UserModel;
    3. using NPOI.XSSF.UserModel;
    4. using NPOI.SS.UserModel;
    5. using System.IO;
    6. using System.Collections.Generic;
    7. using NPOI.SS.Util;
    8. using System.Text;
    9. namespace ExamKing.Application.Utils.Excel
    10. {
    11. /// <summary>
    12. /// NPOIExcel 针对2.5.1.0出的NPOI操作Excel类的常用方法;
    13. /// </summary>
    14. public class NPOIExcel
    15. {
    16. /// <summary>
    17. /// 记录打开的Excel的路径
    18. /// </summary>
    19. private string npoiFileName;
    20. /// <summary>
    21. /// 工作簿,全局变量
    22. /// </summary>
    23. protected IWorkbook workbook;
    24. /// <summary>
    25. /// 获取工作表或是创建的
    26. /// </summary>
    27. private NPOI.SS.UserModel.ISheet sheet;
    28. /// <summary>
    29. /// 构造方法
    30. /// </summary>
    31. public NPOIExcel()
    32. {
    33. npoiFileName = "";
    34. workbook = new XSSFWorkbook();
    35. }
    36. /// <summary>
    37. /// 打开
    38. /// </summary>
    39. /// <param name="filename">excel文件路径</param>
    40. public void Open(string filename)
    41. {
    42. using (FileStream fileStream = new FileStream(filename, FileMode.Open, FileAccess.Read))
    43. {
    44. string ext = Path.GetExtension(filename).ToLower();
    45. if (ext == ".xlsx")
    46. workbook = new XSSFWorkbook(fileStream);
    47. else
    48. {
    49. workbook = new HSSFWorkbook(fileStream);
    50. }
    51. }
    52. npoiFileName = filename;
    53. }
    54. /// <summary>
    55. /// 创建一个Excel对象,该对象为可见的
    56. /// </summary>
    57. public void Create(string sheetname = "Sheet1")
    58. {
    59. sheet = workbook.CreateSheet(sheetname);
    60. }
    61. /// <summary>
    62. /// 获取一个工作表
    63. /// </summary>
    64. /// <param name="SheetName">工作表名称</param>
    65. /// <returns></returns>
    66. public ISheet GetSheet(string SheetName)
    67. {
    68. return (sheet = workbook.GetSheet(SheetName) ?? workbook.CreateSheet(SheetName));
    69. }
    70. /// <summary>
    71. /// 添加一个工作表
    72. /// </summary>
    73. /// <param name="SheetName">工作表名称</param>
    74. /// <returns></returns>
    75. public ISheet AddSheet(string SheetName)
    76. {
    77. ISheet s = workbook.CreateSheet(SheetName);
    78. return s;
    79. }
    80. /// <summary>
    81. /// 删除一个工作表
    82. /// </summary>
    83. /// <param name="SheetName">工作表名称</param>
    84. public void DelSheet(string SheetName)
    85. {
    86. int index = workbook.GetNameIndex(SheetName);
    87. workbook.RemoveSheetAt(index);
    88. }
    89. /// <summary>
    90. /// 重命名一个工作表
    91. /// </summary>
    92. /// <param name="OldSheetName">老工作表名称</param>
    93. /// <param name="NewSheetName">新工作表名称</param>
    94. /// <returns></returns>
    95. public ISheet ReNameSheet(string OldSheetName, string NewSheetName)
    96. {
    97. int index = workbook.GetNameIndex(OldSheetName);
    98. workbook.SetSheetName(index, NewSheetName);
    99. return workbook.GetSheetAt(index);
    100. }
    101. /// <summary>
    102. /// 设置单元格的值
    103. /// </summary>
    104. /// <param name="sheetName">工作表名称</param>
    105. /// <param name="row">行</param>
    106. /// <param name="col">列</param>
    107. /// <param name="value">设置的值</param>
    108. private void SetCellValue(ISheet sheetName, int row, int col, object value)
    109. {
    110. IRow _row = sheetName.GetRow(row) ?? sheetName.CreateRow(row);
    111. ICell cell = _row.GetCell(col) ?? _row.CreateCell(col);
    112. string valuetype = value.GetType().Name.ToLower();
    113. switch (valuetype)
    114. {
    115. case "string"://字符串类型
    116. case "system.string":
    117. case "datetime":
    118. case "system.datetime"://日期类型
    119. case "boolean"://布尔型
    120. case "system.boolean"://布尔型
    121. cell.SetCellType(CellType.String);
    122. cell.SetCellValue(value.ToString());
    123. break;
    124. case "byte":
    125. case "int":
    126. case "int16":
    127. case "int32":
    128. case "int64":
    129. case "system.int16"://整型
    130. case "system.int32":
    131. case "system.int64":
    132. case "system.byte":
    133. cell.SetCellType(CellType.Numeric);
    134. cell.SetCellValue(Convert.ToInt32(value));
    135. break;
    136. case "single":
    137. case "system.single":
    138. case "double":
    139. case "system.double":
    140. case "decimal":
    141. case "system.decimal":
    142. cell.SetCellType(CellType.Numeric);
    143. cell.SetCellValue(Convert.ToDouble(value));
    144. break;
    145. case "dbnull"://空值处理
    146. case "system.dbnull"://空值处理
    147. cell.SetCellValue("");
    148. break;
    149. default:
    150. cell.SetCellValue(value.ToString());
    151. break;
    152. }
    153. }
    154. /// <summary>
    155. /// 要设值的工作表的名称 X行Y列 value 值
    156. /// </summary>
    157. /// <param name="sheetName">工作表名</param>
    158. /// <param name="row">行</param>
    159. /// <param name="col">列</param>
    160. /// <param name="value">插入的值</param>
    161. public void SetCellValue(string sheetName, int row, int col, object value)
    162. {
    163. ISheet s = GetSheet(sheetName);
    164. SetCellValue(s, row, col, value);
    165. }
    166. /// <summary>
    167. /// 获取单元格值
    168. /// </summary>
    169. /// <param name="sheetName">工作表名</param>
    170. /// <param name="row">行</param>
    171. /// <param name="col">列</param>
    172. /// <returns></returns>
    173. public ICell GetCell(string sheetName, int row, int col)
    174. {
    175. return GetSheet(sheetName).GetRow(row).Cells[col];
    176. }
    177. /// <summary>
    178. /// 获取单元格值
    179. /// </summary>
    180. /// <param name="sheetName">工作表名</param>
    181. /// <param name="row">行</param>
    182. /// <param name="col">列</param>
    183. /// <returns></returns>
    184. private ICell GetCell(ISheet sheetName, int row, int col)
    185. {
    186. //return ws.GetRow(row).Cells[col];
    187. return sheetName.GetRow(row).GetCell(col);
    188. }
    189. /// <summary>
    190. /// 设置单元格的属性(字体,大小,颜色,对齐方式)(前提是该行列范围内有值,否则报错)
    191. /// </summary>
    192. /// <param name="sheetName"></param>
    193. /// <param name="startRow">起始行</param>
    194. /// <param name="startCol">起始列</param>
    195. /// <param name="endRow">结束行</param>
    196. /// <param name="endCol">结束列</param>
    197. /// <param name="fontName">字体</param>
    198. /// <param name="indexedColors">字体颜色</param>
    199. /// <param name="fontSize">字体大小</param>
    200. /// <param name="horizontalAlignment">水平排列</param>
    201. public void SetCellProperty(string sheetName, int startRow, int startCol, int endRow, int endCol, string fontName, IndexedColors indexedColors, int fontSize, HorizontalAlignment horizontalAlignment)
    202. {
    203. SetCellProperty(GetSheet(sheetName), startRow, startCol, endRow, endCol, fontName, indexedColors, fontSize, horizontalAlignment);
    204. }
    205. /// <summary>
    206. /// 设置一个单元格的属性(字体,大小,颜色,对齐方式)
    207. /// </summary>
    208. /// <param name="sheetName"></param>
    209. /// <param name="Startx"></param>
    210. /// <param name="Starty"></param>
    211. /// <param name="Endx"></param>
    212. /// <param name="Endy"></param>
    213. /// <param name="fontName"></param>
    214. /// <param name="indexedColors"></param>
    215. /// <param name="fontSize"></param>
    216. /// <param name="horizontalAlignment"></param>
    217. private void SetCellProperty(ISheet sheetName, int startRow, int startCol, int endRow, int endCol, string fontName, IndexedColors indexedColors, int fontSize, HorizontalAlignment horizontalAlignment)
    218. {
    219. ICellStyle style = workbook.CreateCellStyle();
    220. IFont font = workbook.CreateFont();
    221. font.Color = indexedColors.Index;
    222. font.FontName = fontName;
    223. font.FontHeightInPoints = fontSize;
    224. //font.Boldweight = Boldweight;//字体加粗
    225. style.Alignment = horizontalAlignment;
    226. //style.VerticalAlignment = VerticalAlignment.Center; //垂直居中
    227. style.SetFont(font);
    228. foreach (ICell cell in GetCellsOfRange(sheetName, startRow, startCol, endRow, endCol))
    229. {
    230. cell.CellStyle = style;
    231. }
    232. }
    233. /// <summary>
    234. /// 获取起止范围内的行列值
    235. /// </summary>
    236. /// <param name="sheetName"></param>
    237. /// <param name="startRow"></param>
    238. /// <param name="startCol"></param>
    239. /// <param name="endRow"></param>
    240. /// <param name="endCol"></param>
    241. /// <returns></returns>
    242. public IList<ICell> GetCellsOfRange(string sheetName, int startRow, int startCol, int endRow, int endCol)
    243. {
    244. return (GetCellsOfRange(GetSheet(sheetName), startRow, startCol, endRow, endCol));
    245. }
    246. /// <summary>
    247. /// 获取起止范围内的行列值
    248. /// </summary>
    249. /// <param name="sheetName"></param>
    250. /// <param name="startRow"></param>
    251. /// <param name="startCol"></param>
    252. /// <param name="endRow"></param>
    253. /// <param name="endCol"></param>
    254. /// <returns></returns>
    255. private IList<ICell> GetCellsOfRange(ISheet sheetName, int startRow, int startCol, int endRow, int endCol)
    256. {
    257. IList<ICell> allCell = new List<ICell>();
    258. for (int i = startRow; i <= endRow; i++)
    259. for (int j = startCol; j <= endCol; j++)
    260. {
    261. allCell.Add(GetCell(sheetName, i, j));
    262. }
    263. return allCell;
    264. }
    265. /// <summary>
    266. /// 合并单元格
    267. /// </summary>
    268. /// <param name="sheetName"></param>
    269. /// <param name="startRow"></param>
    270. /// <param name="endRow"></param>
    271. /// <param name="startCol"></param>
    272. /// <param name="endCol"></param>
    273. private void MergedCells(ISheet sheetName, int startRow, int endRow, int startCol, int endCol)
    274. {
    275. //CellRangeAddress四个参数为:起始行,结束行,起始列,结束列
    276. sheetName.AddMergedRegion(new CellRangeAddress(startRow, endRow, startCol, endCol));
    277. }
    278. /// <summary>
    279. /// 合并单元格
    280. /// </summary>
    281. /// <param name="sheetName"></param>
    282. /// <param name="startRow"></param>
    283. /// <param name="startCol"></param>
    284. /// <param name="endRow"></param>
    285. /// <param name="endCol"></param>
    286. public void MergedCells(string sheetName, int startRow, int startCol, int endRow, int endCol)
    287. {
    288. MergedCells(GetSheet(sheetName), startRow, endRow, startCol, endCol);
    289. }
    290. /// <summary>
    291. /// 文档另存为
    292. /// </summary>
    293. /// <param name="FileName"></param>
    294. /// <returns></returns>
    295. public bool SaveAs(string FileName)
    296. {
    297. npoiFileName = FileName;
    298. try
    299. {
    300. using (FileStream fs = new FileStream(FileName, FileMode.Create, FileAccess.Write))
    301. {
    302. workbook.Write(fs);
    303. }
    304. return true;
    305. }
    306. catch
    307. {
    308. return false;
    309. }
    310. }
    311. /// <summary>
    312. /// 关闭
    313. /// </summary>
    314. public void Close()
    315. {
    316. workbook.Close();
    317. }
    318. /// <summary>
    319. /// 自适应宽度
    320. /// </summary>
    321. /// <param name="sheetName">表名</param>
    322. /// <param name="startCol">起始列</param>
    323. /// <param name="endCol">结束列</param>
    324. public void AutoColumnWidth(string sheetName, int startCol, int endCol)
    325. {
    326. AutoColumnWidth(GetSheet(sheetName), startCol, endCol);
    327. }
    328. /// <summary>
    329. /// 自适应宽度
    330. /// </summary>
    331. /// <param name="sheet"></param>
    332. /// <param name="cols"></param>
    333. private void AutoColumnWidth(ISheet sheet, int startCol, int endCol)
    334. {
    335. for (int col = startCol; col <= endCol; col++)
    336. {
    337. sheet.AutoSizeColumn(col);//但是其实还是比实际文本要宽
    338. }
    339. }
    340. /// <summary>
    341. /// 设置起止范围的行高,单位为磅
    342. /// </summary>
    343. /// <param name="sheetName">工作表名称</param>
    344. /// <param name="startRow">起始行</param>
    345. /// <param name="endRow">结束行</param>
    346. /// <param name="heightValue">设置的高值</param>
    347. public void SetRowsHeight(string sheetName, int startRow, int endRow, int heightValue)
    348. {
    349. ISheet sheet = GetSheet(sheetName);
    350. for (int i = startRow; i <= endRow; i++)
    351. {
    352. //sheet.GetRow(i).Height = Height * 20;
    353. sheet.GetRow(i).HeightInPoints = heightValue;
    354. }
    355. }
    356. /// <summary>
    357. /// 设置起止列的宽度,单位为字符
    358. /// </summary>
    359. /// <param name="sheetName">工作表名称</param>
    360. /// <param name="startCol">起始列</param>
    361. /// <param name="endCol">结束列</param>
    362. /// <param name="widthValue">设置的宽度值</param>
    363. public void SetColumnsWidth(string sheetName, int startCol, int endCol, int widthValue)
    364. {
    365. ISheet sheet = GetSheet(sheetName);
    366. for (int j = startCol; j <= endCol; j++)
    367. {
    368. sheet.SetColumnWidth(3, widthValue * 256);
    369. }
    370. }
    371. }
    372. }