NPOI 之导入导出
程序员文章站
2022-04-04 13:20:27
转自https://www.cnblogs.com/zuowj/archive/2015/05/04/4475663.html转别人的，做了一点点改动 样式类 ......
转自https://www.cnblogs.com/zuowj/archive/2015/05/04/4475663.html
转别人的，做了一点点改动
1 using NPOI.HSSF.UserModel; 2 using NPOI.SS.UserModel; 3 using NPOI.XSSF.UserModel; 4 using System; 5 using System.Collections.Generic; 6 using System.Data; 7 using System.Linq; 8 using System.Text; 9 using System.IO; 10 using System.Windows.Forms; 11 using NPOI.SS.Util; 12 using NPOI.HSSF.Util; 13 using System.Reflection; 14 15 namespace RaysUtil.Office.Excel 16 { 17 //titleRow1.Cells[0].Hyperlink = new HSSFHyperlink(HyperlinkType.Url); 18 public static class ExcelHelper 19 { 20 /// <summary> 21 /// 获取保存文件路径 22 /// </summary> 23 /// <returns></returns> 24 private static string GetSaveFilePath() 25 { 26 SaveFileDialog saveFileDig = new SaveFileDialog(); 27 saveFileDig.Filter = "Excel Office97 - 2003(*.xls) | *.xls | Excel Office2007及以上(*.xlsx) | *.xlsx"; 28 saveFileDig.Title = "导出到"; 29 saveFileDig.OverwritePrompt = true; 30 saveFileDig.InitialDirectory = Environment.GetFolderPath(Environment.SpecialFolder.DesktopDirectory); 31 string filePath = null; 32 if (saveFileDig.ShowDialog() == DialogResult.OK) 33 { 34 filePath = saveFileDig.FileName; 35 } 36 return filePath; 37 } 38 /// <summary> 39 /// 获取要打开要导入的文件名称（含完整路径） 40 /// </summary> 41 /// <returns></returns> 42 private static string GetOpenFilePath() 43 { 44 OpenFileDialog openFileDialog = new OpenFileDialog(); 45 openFileDialog.Filter = "Excel Office97-2003(*.xls)|*.xls|Excel Office2007及以上(*.xlsx)|*.xlsx"; 46 openFileDialog.FilterIndex = 0; 47 openFileDialog.Title = "打开"; 48 openFileDialog.CheckFileExists = true; 49 openFileDialog.CheckPathExists = true; 50 openFileDialog.InitialDirectory = Environment.GetFolderPath(Environment.SpecialFolder.DesktopDirectory); 51 string filePath = null; 52 if (openFileDialog.ShowDialog() == DialogResult.OK) 53 { 54 filePath = openFileDialog.FileName; 55 } 56 return filePath; 57 } 58 /// <summary> 59 /// 是否兼容模式 60 /// </summary> 61 /// <param name="filePath">文件路径</param> 62 /// <returns></returns> 63 private static bool IsCompatible(string filePath) 64 { 65 //return filePath.EndsWith(".xls", StringComparison.OrdinalIgnoreCase); 66 if (Path.GetExtension(filePath).ToLower() == ".xls") 67 { 68 return true; 69 } 70 else if (Path.GetExtension(filePath).ToLower() == ".xlsx") 71 { 72 return false; 73 } 74 else 75 { 76 throw new Exception("文件扩展名非法或者文件路径不正确！！！"); 77 } 78 } 79 /// <summary> 80 /// 创建工作簿 81 /// </summary> 82 /// <param name="isCompatible">是否兼容模式</param> 83 /// <returns></returns> 84 private static IWorkbook CreateWorkbook(bool isCompatible) 85 { 86 if (isCompatible) 87 { 88 return new HSSFWorkbook(); 89 } 90 else 91 { 92 return new XSSFWorkbook(); 93 } 94 } 95 /// <summary> 96 /// 依据文件流创建工作簿 97 /// </summary> 98 /// <param name="isCompatible"></param> 99 /// <param name="stream"></param> 100 /// <returns></returns> 101 private static IWorkbook CreateWorkbook(bool isCompatible, dynamic stream) 102 { 103 if (isCompatible) 104 { 105 return new HSSFWorkbook(stream); 106 } 107 else 108 { 109 return new XSSFWorkbook(stream); 110 } 111 } 112 /// <summary> 113 /// 创建表格单元格样式 114 /// </summary> 115 /// <param name="workbook">当前工作簿</param> 116 /// <param name="borderStyleB">是否有下边框,默认True</param> 117 /// <param name="borderStyleL">是否有左边框,默认True</param> 118 /// <param name="borderStyleR">是否有右边框,默认True</param> 119 /// <param name="borderStyleT">是否有上边框,默认True</param> 120 /// <param name="borderStyle">有边框的样式,默认薄边框</param> 121 /// <param name="colorIndex">背景色</param> 122 /// <param name="isAlignment">是否横向对齐，默认True</param> 123 /// <param name="horizontalAlignment">横向对齐,默认横向居中</param> 124 /// <param name="verticalAlignment">垂直对齐，默认垂直居中</param> 125 /// <param name="isSetFont">是否设置字体信息,默认False</param> 126 /// <param name="font">字体信息,默认null</param> 127 /// <param name="fontSize">字体大小</param> 128 /// <returns></returns> 129 private static ICellStyle GetCellStyle(IWorkbook workbook, bool borderStyleB = true, bool borderStyleL = true, bool borderStyleR = true, bool borderStyleT = true, NPOI.SS.UserModel.BorderStyle borderStyle = NPOI.SS.UserModel.BorderStyle.Thin, short colorIndex = HSSFColor.LightGreen.Index, bool isAlignment = true, NPOI.SS.UserModel.HorizontalAlignment horizontalAlignment = NPOI.SS.UserModel.HorizontalAlignment.Center, VerticalAlignment verticalAlignment = VerticalAlignment.Center, bool isSetFont = false, IFont font = null, short fontSize = 30) 130 { 131 ICellStyle style = workbook.CreateCellStyle(); 132 style.FillPattern = FillPattern.SolidForeground; 133 style.FillForegroundColor = colorIndex; 134 //边框 135 style.BorderBottom = borderStyleB ? borderStyle : NPOI.SS.UserModel.BorderStyle.None; 136 style.BorderLeft = borderStyleL ? borderStyle : NPOI.SS.UserModel.BorderStyle.None; 137 style.BorderRight = borderStyleR ? borderStyle : NPOI.SS.UserModel.BorderStyle.None; 138 style.BorderTop = borderStyleT ? borderStyle : NPOI.SS.UserModel.BorderStyle.None; 139 //对齐 140 if (isAlignment) 141 { 142 style.Alignment = horizontalAlignment; 143 } 144 else 145 { 146 style.VerticalAlignment = verticalAlignment; 147 } 148 if (isSetFont) 149 { 150 if (font == null) 151 { 152 font = workbook.CreateFont(); 153 font.Boldweight = short.MaxValue; 154 font.FontHeightInPoints = fontSize; 155 } 156 style.SetFont(font); 157 } 158 return style; 159 } 160 /// <summary> 161 /// 创建表格单元格样式 162 /// </summary> 163 /// <param name="workbook">当前工作簿</param> 164 /// <param name="cellStyle">边框样式模板</param> 165 /// <returns></returns> 166 private static ICellStyle GetCellStyle(IWorkbook workbook, CellStyleModel cellStyle) 167 { 168 if (cellStyle==null) 169 { 170 cellStyle = new CellStyleModel(); 171 } 172 ICellStyle style = workbook.CreateCellStyle(); 173 style.FillPattern = FillPattern.SolidForeground; 174 style.FillForegroundColor = cellStyle.ColorIndex; 175 //边框 176 style.BorderBottom = cellStyle.BorderStyleB ? cellStyle.BorderStyle : NPOI.SS.UserModel.BorderStyle.None; 177 style.BorderLeft = cellStyle.BorderStyleL ? cellStyle.BorderStyle : NPOI.SS.UserModel.BorderStyle.None; 178 style.BorderRight = cellStyle.BorderStyleR ? cellStyle.BorderStyle : NPOI.SS.UserModel.BorderStyle.None; 179 style.BorderTop = cellStyle.BorderStyleT ? cellStyle.BorderStyle : NPOI.SS.UserModel.BorderStyle.None; 180 //对齐 181 if (cellStyle.IsAlignment) 182 { 183 style.Alignment = cellStyle.HorizontalAlignment; 184 } 185 else 186 { 187 style.VerticalAlignment = cellStyle.VerticalAlignment; 188 } 189 if (cellStyle.IsSetFont) 190 { 191 if (cellStyle.Font == null) 192 { 193 cellStyle.Font = workbook.CreateFont(); 194 cellStyle.Font.Boldweight = short.MaxValue; 195 cellStyle.Font.FontHeightInPoints = cellStyle.FontSize; 196 } 197 style.SetFont(cellStyle.Font); 198 } 199 return style; 200 } 201 /// <summary> 202 /// 合并单元格 203 /// </summary> 204 /// <param name="sheet">要合并单元格所在的sheet</param> 205 /// <param name="rowStart">开始行的索引</param> 206 /// <param name="rowEnd">结束行的索引</param> 207 /// <param name="colStart">开始列的索引</param> 208 /// <param name="colEnd">结束行的索引</param> 209 public static void SetCellRangeAddress(ISheet sheet, int rowStart, int rowEnd, int colStart, int colEnd) 210 { 211 CellRangeAddress cellRangeAddress = new CellRangeAddress(rowStart, rowEnd, colStart, colEnd); 212 sheet.AddMergedRegion(cellRangeAddress); 213 } 214 /// <summary> 215 /// 工作表生成DataTable 216 /// </summary> 217 /// <param name="sheet"></param> 218 /// <param name="headerRowIndex"></param> 219 /// <returns></returns> 220 private static DataTable GetDataTableFromSheet(ISheet sheet, int headerRowIndex) 221 { 222 DataTable table = new DataTable(); 223 #region 操作首行(标题行) 224 //获取首行 225 IRow headerRow = sheet.GetRow(headerRowIndex); 226 //PhysicalNumberOfCells获取不为空的列个数 227 //LastCellNum获取最后一个不为空的列个数 228 int cellCount = headerRow.LastCellNum; 229 for (int i = headerRow.FirstCellNum; i < cellCount; i++) 230 { 231 if (headerRow.GetCell(i) == null || headerRow.GetCell(i).StringCellValue.Trim() == "") 232 { 233 //如果标题行遇到空列，则不再向后继续读取 234 cellCount = i + 1;//返回真实列数 235 break; 236 } 237 DataColumn column = new DataColumn(headerRow.GetCell(i).StringCellValue); 238 table.Columns.Add(column); 239 } 240 #endregion 241 #region 遍历数据行，标题行除外 242 //遍历数据行，标题行除外 243 for (int i = (headerRowIndex + 1); i < sheet.LastRowNum; i++) 244 { 245 IRow row = sheet.GetRow(i); 246 if (row != null && !string.IsNullOrEmpty(row.Cells[0].StringCellValue)) 247 { 248 DataRow dataRow = table.NewRow(); 249 for (int j = row.FirstCellNum; j < cellCount; j++) 250 { 251 dataRow[i] = row.GetCellEx(j).ToString(); 252 } 253 table.Rows.Add(dataRow); 254 } 255 } 256 #endregion 257 return table; 258 } 259 #region 公共导出方法 260 /// <summary> 261 /// DataSet导出Excel 262 /// </summary> 263 /// <param name="sourceDs">DataSet源</param> 264 /// <param name="filePath">文件保存路径</param> 265 /// <param name="titles">首行标题数组</param> 266 /// <param name="childTitles">子标题数组</param> 267 /// <param name="dateTimes">子标题时间</param> 268 /// <param name="cellStyle">样式类</param> 269 /// <returns>返回生成的Excel保存路径</returns> 270 public static string ExportToExcel(DataSet sourceDs, string filePath, string[] titles = null, string[] childTitles = null, string[] dateTimes = null) 271 { 272 if (string.IsNullOrEmpty(filePath)) 273 { 274 filePath = GetSaveFilePath(); 275 } 276 if (string.IsNullOrEmpty(filePath)) throw new ArgumentNullException("excel文件路径为空字符串或null"); 277 #region 检测标题、子标题、时间 278 if (titles == null) 279 { 280 titles = new string[sourceDs.Tables.Count]; 281 for (int i = 0; i < titles.Length; i++) 282 { 283 titles[i] = sourceDs.Tables[i].TableName; 284 } 285 } 286 if (dateTimes == null) 287 { 288 dateTimes = new string[sourceDs.Tables.Count]; 289 for (int i = 0; i < dateTimes.Length; i++) 290 { 291 titles[i] = DateTime.Now.ToString("yyyy-MM-dd"); 292 } 293 } 294 if (titles != null && (titles.Length < sourceDs.Tables.Count || titles.Length > sourceDs.Tables.Count)) 295 { 296 throw new Exception(string.Format("工作表行首Title参数个数应该为{0}", sourceDs.Tables.Count)); 297 } 298 if (childTitles != null && (childTitles.Length < sourceDs.Tables.Count || childTitles.Length > sourceDs.Tables.Count)) 299 { 300 throw new Exception(string.Format("工作表行首Title参数个数应该为{0}", sourceDs.Tables.Count)); 301 } 302 if (dateTimes != null && (dateTimes.Length < sourceDs.Tables.Count || dateTimes.Length > sourceDs.Tables.Count)) 303 { 304 throw new Exception(string.Format("工作表行首Title参数个数应该为{0}", sourceDs.Tables.Count)); 305 } 306 #endregion 307 308 309 bool isCompatible = IsCompatible(filePath); 310 IWorkbook workbook = CreateWorkbook(isCompatible); 311 //表头行样式 312 ICellStyle headerCellStyle = GetCellStyle(workbook, colorIndex: 22); 313 //数据行样式 314 ICellStyle dataCellStyle = GetCellStyle(workbook, horizontalAlignment: NPOI.SS.UserModel.HorizontalAlignment.Left, colorIndex: 42); 315 #region sheet处理 316 for (int i = 0; i < sourceDs.Tables.Count; i++) 317 { 318 DataTable table = sourceDs.Tables[i]; 319 string sheetName = string.IsNullOrEmpty(table.TableName) ? "sheet" + i.ToString() : table.TableName; 320 ISheet sheet = workbook.CreateSheet(sheetName); 321 IRow titleRow1 = sheet.CreateRow(0); 322 IRow titleRow2 = sheet.CreateRow(1); 323 IRow headerRow = sheet.CreateRow(2); 324 325 #region 处理首行 326 foreach (DataColumn column in table.Columns) 327 { 328 ICell cell = headerRow.CreateCell(column.Ordinal); 329 ICell titleCell1 = titleRow1.CreateCell(column.Ordinal); 330 ICell titleCell2 = titleRow2.CreateCell(column.Ordinal); 331 cell.SetCellValue(column.ColumnName); 332 cell.CellStyle = headerCellStyle; 333 } 334 //标题行样式 335 int cellMaxIndex = titleRow1.LastCellNum - 1; 336 SetCellRangeAddress(sheet, 0, 0, 0, cellMaxIndex); 337 SetCellRangeAddress(sheet, 1, 1, 0, (int)Math.Floor(cellMaxIndex / 2.0)); 338 SetCellRangeAddress(sheet, 1, 1, (int)Math.Floor(cellMaxIndex / 2.0) + 1, cellMaxIndex); 339 titleRow1.Cells[0].SetCellValue(titles[i]); 340 if (childTitles != null) 341 { 342 titleRow2.Cells[0].SetCellValue(childTitles[i]); 343 } 344 titleRow2.Cells[(int)Math.Floor(cellMaxIndex / 2.0) + 1].SetCellValue(dateTimes[i]); 345 titleRow1.Cells.ForEach(c => c.CellStyle = GetCellStyle(workbook, borderStyleB: false, colorIndex: 32767, isSetFont: true)); 346 347 titleRow2.Cells.ForEach(c => c.CellStyle = GetCellStyle(workbook, borderStyleT: false, colorIndex: 32767)); 348 titleRow2.Cells[0].CellStyle = GetCellStyle(workbook, borderStyleT: false, borderStyleR: false, colorIndex: 32767); 349 titleRow2.Cells[titleRow2.LastCellNum - 1].CellStyle = GetCellStyle(workbook, borderStyleT: false, borderStyleL: false, colorIndex: 32767); 350 titleRow1.HeightInPoints = 40; 351 titleRow2.HeightInPoints = 20; 352 #endregion 353 354 #region 处理数据行，首行除外 355 int rowIndex = 3; 356 foreach (DataRow row in table.Rows) 357 { 358 IRow dataRow = sheet.CreateRow(rowIndex); 359 foreach (DataColumn column in table.Columns) 360 { 361 ICell dataCell = dataRow.CreateCell(column.Ordinal); 362 dataCell.SetCellValue((row[column] ?? "").ToString()); 363 dataCell.CellStyle = dataCellStyle; 364 } 365 rowIndex++; 366 } 367 #endregion 368 //设置列宽 369 for (int k = 0; k < table.Columns.Count; k++) 370 { 371 sheet.SetColumnWidth(k, (table.Columns[k].Caption.Length < 20 ? 20 : table.Columns[k].Caption.Length) * 256); 372 } 373 } 374 #endregion 375 #region 保存 376 using (FileStream fs = new FileStream(filePath, FileMode.OpenOrCreate, FileAccess.ReadWrite)) 377 { 378 workbook.Write(fs); 379 } 380 workbook = null; 381 #endregion 382 return filePath; 383 } 384 /// <summary> 385 /// DataSet导出Excel 386 /// </summary> 387 /// <param name="sourceDs">DataSet源</param> 388 /// <param name="filePath">文件保存路径</param> 389 /// <param name="titles">首行标题数组</param> 390 /// <param name="childTitles">子标题数组</param> 391 /// <param name="dateTimes">子标题时间</param> 392 /// <param name="cellStyle">样式类</param> 393 /// <returns></returns> 394 public static string ExportToExcel(DataSet sourceDs, string filePath, CellStyleModel cellStyle , string[] titles = null
赞 (0)
打赏
微信扫一扫
相关文章:
-
-
一位办公室主任要退休了，他感慨万分地说：做了二十多年的主任，服务了几任领导，练就了十多项业余技能。 我好奇的问：全什么技能？ 主任笑着说：喝酒，打牌，打麻将，网... [阅读全文]
-
The "IsFileSystemCaseSensitive" parameter is not supported by the &quo
今天从VS2015升级到VS2017, 一个.net Core 的project出现build failed,提示The "IsFileS... [阅读全文] -
Writing queries Version：5.x 英文原文地址： "Writing queries" 将数据索引到了 Elasticsearc... [阅读全文]
-
... ... [阅读全文]
-
本次主要实现列表的编辑及下拉列表的绑定 先看效果图： 主要用DropDownList绑定下拉列后端代码： 1：定义一个存下拉数据类 2：编辑的方法 ... [阅读全文] -
版权声明：本文内容由互联网用户贡献，该文观点仅代表作者本人。本站仅提供信息存储服务，不拥有所有权，不承担相关法律责任。 如发现本站有涉嫌抄袭侵权/违法违规的内容， 请发送邮件至 2386932994@qq.com 举报，一经查实将立刻删除。
发表评论