NPOI 之导入导出

2018-06-18 00:30:07来源:未知 阅读 ()

新老客户大回馈,云服务器低至5折


转自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, string[] childTitles = null, string[] dateTimes = null)
 395         {
 396             if (string.IsNullOrEmpty(filePath))
 397             {
 398                 filePath = GetSaveFilePath();
 399             }
 400             if (string.IsNullOrEmpty(filePath)) throw new ArgumentNullException("excel文件路径为空字符串或null");
 401             #region 检测标题、子标题、时间
 402             if (titles == null)
 403             {
 404                 titles = new string[sourceDs.Tables.Count];
 405                 for (int i = 0; i < titles.Length; i++)
 406                 {
 407                     titles[i] = sourceDs.Tables[i].TableName;
 408                 }
 409             }
 410             if (dateTimes == null)
 411             {
 412                 dateTimes = new string[sourceDs.Tables.Count];
 413                 for (int i = 0; i < dateTimes.Length; i++)
 414                 {
 415                     titles[i] = DateTime.Now.ToString("yyyy-MM-dd");
 416                 }
 417             }
 418             if (titles != null && (titles.Length < sourceDs.Tables.Count || titles.Length > sourceDs.Tables.Count))
 419             {
 420                 throw new Exception(string.Format("工作表行首Title参数个数应该为{0}", sourceDs.Tables.Count));
 421             }
 422             if (childTitles != null && (childTitles.Length < sourceDs.Tables.Count || childTitles.Length > sourceDs.Tables.Count))
 423             {
 424                 throw new Exception(string.Format("工作表行首Title参数个数应该为{0}", sourceDs.Tables.Count));
 425             }
 426             if (dateTimes != null && (dateTimes.Length < sourceDs.Tables.Count || dateTimes.Length > sourceDs.Tables.Count))
 427             {
 428                 throw new Exception(string.Format("工作表行首Title参数个数应该为{0}", sourceDs.Tables.Count));
 429             }
 430             #endregion
 431 
 432 
 433             bool isCompatible = IsCompatible(filePath);
 434             IWorkbook workbook = CreateWorkbook(isCompatible);
 435             //表头行样式
 436             ICellStyle headerCellStyle = GetCellStyle(workbook, cellStyle ?? new CellStyleModel { ColorIndex = 22 });
 437             //数据行样式
 438             
 439             ICellStyle dataCellStyle = GetCellStyle(workbook, cellStyle ?? new CellStyleModel { HorizontalAlignment = NPOI.SS.UserModel.HorizontalAlignment.Left, ColorIndex = 42 });
 440             #region sheet处理
 441             for (int i = 0; i < sourceDs.Tables.Count; i++)
 442             {
 443                 DataTable table = sourceDs.Tables[i];
 444                 string sheetName = string.IsNullOrEmpty(table.TableName) ? "sheet" + i.ToString() : table.TableName;
 445                 ISheet sheet = workbook.CreateSheet(sheetName);
 446                 IRow titleRow1 = sheet.CreateRow(0);
 447                 IRow titleRow2 = sheet.CreateRow(1);
 448                 IRow headerRow = sheet.CreateRow(2);
 449 
 450                 #region 处理首行
 451                 foreach (DataColumn column in table.Columns)
 452                 {
 453                     ICell cell = headerRow.CreateCell(column.Ordinal);
 454                     ICell titleCell1 = titleRow1.CreateCell(column.Ordinal);
 455                     ICell titleCell2 = titleRow2.CreateCell(column.Ordinal);
 456                     cell.SetCellValue(column.ColumnName);
 457                     cell.CellStyle = headerCellStyle;
 458                 }
 459                 //标题行样式
 460                 int cellMaxIndex = titleRow1.LastCellNum - 1;
 461                 SetCellRangeAddress(sheet, 0, 0, 0, cellMaxIndex);
 462                 SetCellRangeAddress(sheet, 1, 1, 0, (int)Math.Floor(cellMaxIndex / 2.0));
 463                 SetCellRangeAddress(sheet, 1, 1, (int)Math.Floor(cellMaxIndex / 2.0) + 1, cellMaxIndex);
 464                 titleRow1.Cells[0].SetCellValue(titles[i]);
 465                 if (childTitles != null)
 466                 {
 467                     titleRow2.Cells[0].SetCellValue(childTitles[i]);
 468                 }
 469                 titleRow2.Cells[(int)Math.Floor(cellMaxIndex / 2.0) + 1].SetCellValue(dateTimes[i]);
 470                 titleRow1.Cells.ForEach(c => c.CellStyle = GetCellStyle(workbook,
 471                     cellStyle??new CellStyleModel { BorderStyleB=false,ColorIndex=32767,IsSetFont=true}));
 472 
 473                 titleRow2.Cells.ForEach(c => c.CellStyle = GetCellStyle(workbook,
 474                     cellStyle??new CellStyleModel { BorderStyleT=false,ColorIndex=32767}));
 475                 titleRow2.Cells[0].CellStyle = GetCellStyle(workbook,
 476                     cellStyle??new CellStyleModel { BorderStyleT=false,BorderStyleR=false,ColorIndex=32767});
 477                 titleRow2.Cells[titleRow2.LastCellNum - 1].CellStyle = GetCellStyle(workbook,
 478                     cellStyle??new CellStyleModel { BorderStyleT=false,BorderStyleL=false,ColorIndex=32767});
 479                 titleRow1.HeightInPoints = 40;
 480                 titleRow2.HeightInPoints = 20;
 481                 #endregion
 482 
 483                 #region 处理数据行,首行除外
 484                 int rowIndex = 3;
 485                 foreach (DataRow row in table.Rows)
 486                 {
 487                     IRow dataRow = sheet.CreateRow(rowIndex);
 488                     foreach (DataColumn column in table.Columns)
 489                     {
 490                         ICell dataCell = dataRow.CreateCell(column.Ordinal);
 491                         dataCell.SetCellValue((row[column] ?? "").ToString());
 492                         dataCell.CellStyle = dataCellStyle;
 493                     }
 494                     rowIndex++;
 495                 }
 496                 #endregion
 497                 //设置列宽
 498                 for (int k = 0; k < table.Columns.Count; k++)
 499                 {
 500                     sheet.SetColumnWidth(k, (table.Columns[k].Caption.Length < 20 ? 20 : table.Columns[k].Caption.Length) * 256);
 501                 }
 502             }
 503             #endregion
 504             #region 保存
 505             using (FileStream fs = new FileStream(filePath, FileMode.OpenOrCreate, FileAccess.ReadWrite))
 506             {
 507                 workbook.Write(fs);
 508             }
 509             workbook = null;
 510             #endregion
 511             return filePath;
 512         }
 513         /// <summary>
 514         /// DataTable导出Excel
 515         /// </summary>
 516         /// <param name="sourceTable">DataTable源</param>
 517         /// <param name="sheetName">工作表名称</param>
 518         /// <param name="filePath">文件保存路径</param>
 519         /// <param name="title">首行标题</param>
 520         /// <param name="childTitle">子标题</param>
 521         /// <param name="dateTime">子标题时间</param>
 522         /// <param name="cellStyle">样式类</param>
 523         /// <returns>返回生成的Excel保存路径</returns>
 524         public static string ExportToExcel(DataTable sourceTable, string sheetName = "sheet1", string filePath = null, string title = "", string childTitle = "", string dateTime = "")
 525         {
 526             if (sourceTable.Rows.Count <= 0) throw new ArgumentException("DataTable源不存在有效的数据!");
 527             if (string.IsNullOrEmpty(filePath))
 528             {
 529                 filePath = GetSaveFilePath();
 530             }
 531             if (string.IsNullOrEmpty(filePath)) throw new ArgumentNullException("excel文件路径为空字符串或null!");
 532             bool isCompatible = IsCompatible(filePath);
 533             IWorkbook workbook = CreateWorkbook(isCompatible);
 534             //表头行样式
 535             ICellStyle headerCellStyle = GetCellStyle(workbook, colorIndex: 22);
 536             //数据行样式
 537             ICellStyle dataCellStyle = GetCellStyle(workbook, horizontalAlignment: NPOI.SS.UserModel.HorizontalAlignment.Left, colorIndex: 42);
 538             ISheet sheet = workbook.CreateSheet(sheetName);
 539             IRow titleRow1 = sheet.CreateRow(0);
 540             IRow titleRow2 = sheet.CreateRow(1);
 541             IRow headerRow = sheet.CreateRow(2);
 542 
 543             #region 处理首行
 544             foreach (DataColumn column in sourceTable.Columns)
 545             {
 546                 ICell cell = headerRow.CreateCell(column.Ordinal);
 547                 ICell titleCell1 = titleRow1.CreateCell(column.Ordinal);
 548                 ICell titleCell2 = titleRow2.CreateCell(column.Ordinal);
 549                 cell.SetCellValue(column.ColumnName);
 550                 cell.CellStyle = headerCellStyle;
 551             }
 552 
 553             //标题行样式
 554             int cellMaxIndex = titleRow1.LastCellNum - 1;
 555             SetCellRangeAddress(sheet, 0, 0, 0, titleRow1.LastCellNum - 1);
 556             SetCellRangeAddress(sheet, 1, 1, 0, (int)Math.Floor(cellMaxIndex / 2.0));
 557             SetCellRangeAddress(sheet, 1, 1, (int)Math.Floor(cellMaxIndex / 2.0) + 1, cellMaxIndex);
 558 
 559             titleRow1.Cells[0].SetCellValue(string.IsNullOrEmpty(title) ? sourceTable.TableName : title);
 560             titleRow2.Cells[0].SetCellValue(childTitle);
 561             titleRow2.Cells[(int)Math.Floor(cellMaxIndex / 2.0) + 1].SetCellValue(string.IsNullOrEmpty(dateTime) ? DateTime.Now.ToString("yyyy-MM-dd") : dateTime);
 562 
 563             titleRow1.Cells.ForEach(c => c.CellStyle = GetCellStyle(workbook, borderStyleB: false, colorIndex: 32767, isSetFont: true));
 564 
 565             //titleRow2.Cells.ForEach(c => c.CellStyle = GetCellStyle(workbook, borderStyleT: false, colorIndex: 32767));
 566             titleRow2.Cells[0].CellStyle = GetCellStyle(workbook, borderStyleT: false, borderStyleR: false, colorIndex: 32767);
 567             titleRow2.Cells[titleRow2.LastCellNum - 1].CellStyle = GetCellStyle(workbook, borderStyleT: false, borderStyleL: false, colorIndex: 32767);
 568 
 569             titleRow1.HeightInPoints = 40;
 570             titleRow2.HeightInPoints = 20;
 571             #endregion
 572 
 573             #region 处理数据行,首行除外
 574             int rowIndex = 3;
 575             foreach (DataRow row in sourceTable.Rows)
 576             {
 577                 IRow dataRow = sheet.CreateRow(rowIndex);
 578                 foreach (DataColumn column in sourceTable.Columns)
 579                 {
 580                     ICell dataCell = dataRow.CreateCell(column.Ordinal);
 581                     dataCell.SetCellValue((row[column] ?? "").ToString());
 582                     dataCell.CellStyle = dataCellStyle;
 583                 }
 584                 rowIndex++;
 585             }
 586             //设置列宽
 587             for (int k = 0; k < sourceTable.Columns.Count; k++)
 588             {
 589                 sheet.SetColumnWidth(k, (sourceTable.Columns[k].Caption.Length < 20 ? 20 : sourceTable.Columns[k].Caption.Length) * 256);
 590             }
 591             #endregion
 592             #region 保存
 593             using (FileStream fs = new FileStream(filePath, FileMode.OpenOrCreate, FileAccess.ReadWrite))
 594             {
 595                 workbook.Write(fs);
 596             }
 597             sheet = null;
 598             headerRow = null;
 599             workbook = null;
 600             #endregion
 601             return filePath;
 602         }
 603         /// <summary>
 604         /// DataTable导出Excel
 605         /// </summary>
 606         /// <param name="sourceTable">DataTable源</param>
 607         /// <param name="sheetName">工作表名称</param>
 608         /// <param name="filePath">文件保存路径</param>
 609         /// <param name="title">首行标题</param>
 610         /// <param name="childTitle">子标题</param>
 611         /// <param name="dateTime">子标题时间</param>
 612         /// <param name="cellStyle">样式类</param>
 613         /// <returns></returns>
 614         public static string ExportToExcel(DataTable sourceTable, CellStyleModel cellStyle, string sheetName = "sheet1", string filePath = null, string title = "", string childTitle = "", string dateTime = "")
 615         {
 616             if (sourceTable.Rows.Count <= 0) throw new ArgumentException("DataTable源不存在有效的数据!");
 617             if (string.IsNullOrEmpty(filePath))
 618             {
 619                 filePath = GetSaveFilePath();
 620             }
 621             if (string.IsNullOrEmpty(filePath)) throw new ArgumentNullException("excel文件路径为空字符串或null!");
 622             bool isCompatible = IsCompatible(filePath);
 623             IWorkbook workbook = CreateWorkbook(isCompatible);
 624             //表头行样式
 625             ICellStyle headerCellStyle = GetCellStyle(workbook, cellStyle??new CellStyleModel { ColorIndex=22});
 626             //数据行样式
 627             ICellStyle dataCellStyle = GetCellStyle(workbook, cellStyle??new CellStyleModel { HorizontalAlignment=NPOI.SS.UserModel.HorizontalAlignment.Left,ColorIndex=42});
 628             ISheet sheet = workbook.CreateSheet(sheetName);
 629             IRow titleRow1 = sheet.CreateRow(0);
 630             IRow titleRow2 = sheet.CreateRow(1);
 631             IRow headerRow = sheet.CreateRow(2);
 632 
 633             #region 处理首行
 634             foreach (DataColumn column in sourceTable.Columns)
 635             {
 636                 ICell cell = headerRow.CreateCell(column.Ordinal);
 637                 ICell titleCell1 = titleRow1.CreateCell(column.Ordinal);
 638                 ICell titleCell2 = titleRow2.CreateCell(column.Ordinal);
 639                 cell.SetCellValue(column.ColumnName);
 640                 cell.CellStyle = headerCellStyle;
 641             }
 642 
 643             //标题行样式
 644             int cellMaxIndex = titleRow1.LastCellNum - 1;
 645             SetCellRangeAddress(sheet, 0, 0, 0, titleRow1.LastCellNum - 1);
 646             SetCellRangeAddress(sheet, 1, 1, 0, (int)Math.Floor(cellMaxIndex / 2.0));
 647             SetCellRangeAddress(sheet, 1, 1, (int)Math.Floor(cellMaxIndex / 2.0) + 1, cellMaxIndex);
 648 
 649             titleRow1.Cells[0].SetCellValue(string.IsNullOrEmpty(title) ? sourceTable.TableName : title);
 650             titleRow2.Cells[0].SetCellValue(childTitle);
 651             titleRow2.Cells[(int)Math.Floor(cellMaxIndex / 2.0) + 1].SetCellValue(string.IsNullOrEmpty(dateTime) ? DateTime.Now.ToString("yyyy-MM-dd") : dateTime);
 652 
 653             titleRow1.Cells.ForEach(c => c.CellStyle = GetCellStyle(workbook, 
 654                 cellStyle??new CellStyleModel { BorderStyleB=false,ColorIndex=32767,IsSetFont=true}));
 655             //titleRow2.Cells.ForEach(c => c.CellStyle = GetCellStyle(workbook, borderStyleT: false, colorIndex: 32767));
 656             titleRow2.Cells[0].CellStyle = GetCellStyle(workbook, 
 657                 cellStyle??new CellStyleModel { BorderStyleT=false,BorderStyleR=false,ColorIndex=32767});
 658             titleRow2.Cells[titleRow2.LastCellNum - 1].CellStyle = GetCellStyle(workbook, 
 659                 cellStyle??new CellStyleModel { BorderStyleT=false,BorderStyleL=false,ColorIndex=32767});
 660             titleRow1.HeightInPoints = 40;
 661             titleRow2.HeightInPoints = 20;
 662             #endregion
 663 
 664             #region 处理数据行,首行除外
 665             int rowIndex = 3;
 666             foreach (DataRow row in sourceTable.Rows)
 667             {
 668                 IRow dataRow = sheet.CreateRow(rowIndex);
 669                 foreach (DataColumn column in sourceTable.Columns)
 670                 {
 671                     ICell dataCell = dataRow.CreateCell(column.Ordinal);
 672                     dataCell.SetCellValue((row[column] ?? "").ToString());
 673                     dataCell.CellStyle = dataCellStyle;
 674                 }
 675                 rowIndex++;
 676             }
 677             //设置列宽
 678             for (int k = 0; k < sourceTable.Columns.Count; k++)
 679             {
 680                 sheet.SetColumnWidth(k, (sourceTable.Columns[k].Caption.Length < 20 ? 20 : sourceTable.Columns[k].Caption.Length) * 256);
 681             }
 682             #endregion
 683             #region 保存
 684             using (FileStream fs = new FileStream(filePath, FileMode.OpenOrCreate, FileAccess.ReadWrite))
 685             {
 686                 workbook.Write(fs);
 687             }
 688             sheet = null;
 689             headerRow = null;
 690             workbook = null;
 691             #endregion
 692             return filePath;
 693         }
 694         /// <summary>
 695         /// List导出Excel
 696         /// </summary>
 697         /// <typeparam name="T">List元素类型</typeparam>
 698         /// <param name="data">List数据源</param>
 699         /// <param name="headerNameList">首行数据映射源</param>
 700         /// <param name="sheetName">工作表名称</param>
 701         /// <param name="filePath">导出的文件地址</param>
 702         /// <param name="title">首行标题</param>
 703         /// <param name="childTitle">子标题</param>
 704         /// <param name="dateTime">子标题时间</param>
 705         /// <returns></returns>
 706         public static string ExportToExcel<T>(List<T> data, IList<KeyValuePair<string, string>> headerNameList, string sheetName = "sheet1", string filePath = null, string title = "", string childTitle = "", string dateTime = "") where T : class
 707         {
 708             if (data.Count <= 0) throw new ArgumentException("List数据源不存在有效的数据");
 709             if (string.IsNullOrEmpty(filePath))
 710             {
 711                 filePath = GetSaveFilePath();
 712             }
 713             if (string.IsNullOrEmpty(filePath)) throw new ArgumentNullException("excel文件路径为空字符串或null");
 714             bool isCompatible = IsCompatible(filePath);
 715             IWorkbook workbook = CreateWorkbook(isCompatible);
 716             //表头行样式
 717             ICellStyle headerCellStyle = GetCellStyle(workbook, colorIndex: 22);
 718             //数据行样式
 719             ICellStyle dataCellStyle = GetCellStyle(workbook, horizontalAlignment: NPOI.SS.UserModel.HorizontalAlignment.Left, colorIndex: 42);
 720             ISheet sheet = workbook.CreateSheet(sheetName);
 721             IRow titleRow1 = sheet.CreateRow(0);
 722             IRow titleRow2 = sheet.CreateRow(1);
 723             IRow headerRow = sheet.CreateRow(2);
 724 
 725             #region 处理首行
 726             for (int i = 0; i < headerNameList.Count; i++)
 727             {
 728                 ICell cell = headerRow.CreateCell(i);
 729                 ICell titleCell1 = titleRow1.CreateCell(i);
 730                 ICell titleCell2 = titleRow2.CreateCell(i);
 731                 cell.SetCellValue(headerNameList[i].Value);
 732                 cell.CellStyle = headerCellStyle;
 733             }
 734             //标题行样式
 735             int cellMaxIndex = titleRow1.LastCellNum - 1;
 736             SetCellRangeAddress(sheet, 0, 0, 0, cellMaxIndex);
 737             SetCellRangeAddress(sheet, 1, 1, 0, (int)Math.Floor(cellMaxIndex / 2.0));
 738             SetCellRangeAddress(sheet, 1, 1, (int)Math.Floor(cellMaxIndex / 2.0) + 1, cellMaxIndex);
 739             //Console.WriteLine("合并后列数是{0}",titleRow1.PhysicalNumberOfCells);
 740             titleRow1.Cells[0].SetCellValue(string.IsNullOrEmpty(title) ? sheetName : title);
 741             titleRow2.Cells[0].SetCellValue(childTitle);
 742             titleRow2.Cells[(int)Math.Floor(cellMaxIndex / 2.0) + 1].SetCellValue(string.IsNullOrEmpty(dateTime) ? DateTime.Now.ToString("yyyy-MM-dd") : dateTime);
 743             titleRow1.Cells.ForEach(c => c.CellStyle = GetCellStyle(workbook, borderStyleB: false, colorIndex: 32767, isSetFont: true));
 744 
 745             //titleRow2.Cells.ForEach(c => c.CellStyle = GetCellStyle(workbook, borderStyleT: false, colorIndex: 32767));
 746             titleRow2.Cells[0].CellStyle = GetCellStyle(workbook, borderStyleT: false, borderStyleR: false, colorIndex: 32767);
 747             titleRow2.Cells[titleRow2.LastCellNum - 1].CellStyle = GetCellStyle(workbook, borderStyleT: false, borderStyleL: false, colorIndex: 32767);
 748             titleRow1.HeightInPoints = 40;
 749             titleRow2.HeightInPoints = 20;
 750             #endregion
 751             #region 处理数据行
 752             Type t = typeof(T);
 753             int rowIndex = 3;
 754             foreach (T item in data)
 755             {
 756                 IRow dataRow = sheet.CreateRow(rowIndex);
 757                 for (int j = 0; j < headerNameList.Count; j++)
 758                 {
 759                     object pValue = t.GetProperty(headerNameList[j].Key).GetValue(item, null);
 760                     ICell dataCell = dataRow.CreateCell(j);
 761                     dataCell.SetCellValue((pValue ?? "").ToString());
 762                     dataCell.CellStyle = dataCellStyle;
 763                 }
 764                 rowIndex++;
 765             }
 766             //设置列宽
 767             for (int k = 0; k < headerRow.Cells.Count; k++)
 768             {
 769                 sheet.SetColumnWidth(k, (headerRow.Cells[k].StringCellValue.Length < 20 ? 20 : headerRow.Cells[k].StringCellValue.Length) * 256);
 770             }
 771             #endregion
 772             FileStream fs = new FileStream(filePath, FileMode.OpenOrCreate, FileAccess.ReadWrite);
 773             workbook.Write(fs);
 774             fs.Dispose();
 775             sheet = null;
 776             headerRow = null;
 777             workbook = null;
 778             return filePath;
 779         }
 780         /// <summary>
 781         /// List导出Excel
 782         /// </summary>
 783         /// <typeparam name="T">List元素类型</typeparam>
 784         /// <param name="data">List数据源</param>
 785         /// <param name="headerNameList">首行数据映射源</param>
 786         /// <param name="sheetName">工作表名称</param>
 787         /// <param name="filePath">导出的文件地址</param>
 788         /// <param name="title">首行标题</param>
 789         /// <param name="childTitle">子标题</param>
 790         /// <param name="dateTime">子标题时间</param>
 791         /// <param name="cellStyle">样式类</param>
 792         /// <returns></returns>
 793         public static string ExportToExcel<T>(List<T> data, IList<KeyValuePair<string, string>> headerNameList, CellStyleModel cellStyle, string sheetName = "sheet1", string filePath = null, string title = "", string childTitle = "", string dateTime = "") where T : class
 794         {
 795             if (data.Count <= 0) throw new ArgumentException("List数据源不存在有效的数据");
 796             if (string.IsNullOrEmpty(filePath))
 797             {
 798                 filePath = GetSaveFilePath();
 799             }
 800             if (string.IsNullOrEmpty(filePath)) throw new ArgumentNullException("excel文件路径为空字符串或null");
 801             bool isCompatible = IsCompatible(filePath);
 802             IWorkbook workbook = CreateWorkbook(isCompatible);
 803             //表头行样式
 804             ICellStyle headerCellStyle = GetCellStyle(workbook, cellStyle??new CellStyleModel { ColorIndex=22});
 805             //数据行样式
 806             ICellStyle dataCellStyle = GetCellStyle(workbook, 
 807                 cellStyle??new CellStyleModel { HorizontalAlignment= NPOI.SS.UserModel.HorizontalAlignment.Left,ColorIndex=42});
 808             ISheet sheet = workbook.CreateSheet(sheetName);
 809             IRow titleRow1 = sheet.CreateRow(0);
 810             IRow titleRow2 = sheet.CreateRow(1);
 811             IRow headerRow = sheet.CreateRow(2);
 812 
 813             #region 处理首行
 814             for (int i = 0; i < headerNameList.Count; i++)
 815             {
 816                 ICell cell = headerRow.CreateCell(i);
 817                 ICell titleCell1 = titleRow1.CreateCell(i);
 818                 ICell titleCell2 = titleRow2.CreateCell(i);
 819                 cell.SetCellValue(headerNameList[i].Value);
 820                 cell.CellStyle = headerCellStyle;
 821             }
 822             //标题行样式
 823             int cellMaxIndex = titleRow1.LastCellNum - 1;
 824             SetCellRangeAddress(sheet, 0, 0, 0, cellMaxIndex);
 825             SetCellRangeAddress(sheet, 1, 1, 0, (int)Math.Floor(cellMaxIndex / 2.0));
 826             SetCellRangeAddress(sheet, 1, 1, (int)Math.Floor(cellMaxIndex / 2.0) + 1, cellMaxIndex);
 827             //Console.WriteLine("合并后列数是{0}",titleRow1.PhysicalNumberOfCells);
 828             titleRow1.Cells[0].SetCellValue(string.IsNullOrEmpty(title) ? sheetName : title);
 829             titleRow2.Cells[0].SetCellValue(childTitle);
 830             titleRow2.Cells[(int)Math.Floor(cellMaxIndex / 2.0) + 1].SetCellValue(string.IsNullOrEmpty(dateTime) ? DateTime.Now.ToString("yyyy-MM-dd") : dateTime);
 831             titleRow1.Cells.ForEach(c => c.CellStyle = GetCellStyle(workbook, 
 832                 cellStyle??new CellStyleModel { BorderStyleB=false,ColorIndex=32767,IsSetFont=true}));
 833             //titleRow2.Cells.ForEach(c => c.CellStyle = GetCellStyle(workbook, borderStyleT: false, colorIndex: 32767));
 834             titleRow2.Cells[0].CellStyle = GetCellStyle(workbook, 
 835                 cellStyle??new CellStyleModel { BorderStyleT=false,BorderStyleR=false,ColorIndex=32767});
 836             titleRow2.Cells[titleRow2.LastCellNum - 1].CellStyle = GetCellStyle(workbook, 
 837                 cellStyle??new CellStyleModel { BorderStyleT=false,BorderStyleL=false,ColorIndex=32767});
 838             titleRow1.HeightInPoints = 40;
 839             titleRow2.HeightInPoints = 20;
 840             #endregion
 841             #region 处理数据行
 842             Type t = typeof(T);
 843             int rowIndex = 3;
 844             foreach (T item in data)
 845             {
 846                 IRow dataRow = sheet.CreateRow(rowIndex);
 847                 for (int j = 0; j < headerNameList.Count; j++)
 848                 {
 849                     object pValue = t.GetProperty(headerNameList[j].Key).GetValue(item, null);
 850                     ICell dataCell = dataRow.CreateCell(j);
 851                     dataCell.SetCellValue((pValue ?? "").ToString());
 852                     dataCell.CellStyle = dataCellStyle;
 853                 }
 854                 rowIndex++;
 855             }
 856             //设置列宽
 857             for (int k = 0; k < headerRow.Cells.Count; k++)
 858             {
 859                 sheet.SetColumnWidth(k, (headerRow.Cells[k].StringCellValue.Length < 20 ? 20 : headerRow.Cells[k].StringCellValue.Length) * 256);
 860             }
 861             #endregion
 862             FileStream fs = new FileStream(filePath, FileMode.OpenOrCreate, FileAccess.ReadWrite);
 863             workbook.Write(fs);
 864             fs.Dispose();
 865             sheet = null;
 866             headerRow = null;
 867             workbook = null;
 868             return filePath;
 869         }
 870         /// <summary>
 871         /// DataGridView导出Excel
 872         /// </summary>
 873         /// <param name="grid">DataGridView数据源</param>
 874         /// <param name="sheetName">工作表名称</param>
 875         /// <param name="filePath">文件路径</param>
 876         /// <param name="title">首行标题</param>
 877         /// <param name="childTitle">子标题</param>
 878         /// <param name="dateTime">子标题时间</param>
 879         /// <returns></returns>
 880         public static string ExportToExcel(DataGridView grid, string sheetName = "sheet1", string filePath = null, string title = "", string childTitle = "", string dateTime = "")
 881         {
 882             if (grid.Rows.Count <= 0) throw new ArgumentException("DataGridView数据源不存在游侠的数据!");
 883             if (string.IsNullOrEmpty(filePath))
 884             {
 885                 filePath = GetSaveFilePath();
 886             }
 887             if (string.IsNullOrEmpty(filePath)) throw new ArgumentNullException("excel文件路径为空字符串或null!");
 888             bool isCompatible = IsCompatible(filePath);
 889             IWorkbook workbook = CreateWorkbook(isCompatible);
 890             //表头行样式
 891             ICellStyle headerCellStyle = GetCellStyle(workbook, colorIndex: 22);
 892             //数据行样式
 893             ICellStyle dataCellStyle = GetCellStyle(workbook, horizontalAlignment: NPOI.SS.UserModel.HorizontalAlignment.Left, colorIndex: 42);
 894             ISheet sheet = workbook.CreateSheet(sheetName);
 895             IRow titleRow1 = sheet.CreateRow(0);
 896             IRow titleRow2 = sheet.CreateRow(1);
 897             IRow headerRow = sheet.CreateRow(2);
 898 
 899             for (int i = 0; i < grid.Columns.Count; i++)
 900             {
 901                 ICell cell = headerRow.CreateCell(i);
 902                 ICell titleCell1 = titleRow1.CreateCell(i);
 903                 ICell titleCell2 = titleRow2.CreateCell(i);
 904                 cell.SetCellValue(grid.Columns[i].HeaderText);
 905                 cell.CellStyle = headerCellStyle;
 906             }
 907             //标题行样式
 908             int cellMaxIndex = titleRow1.LastCellNum - 1;
 909             SetCellRangeAddress(sheet, 0, 0, 0, titleRow1.LastCellNum - 1);
 910             SetCellRangeAddress(sheet, 1, 1, 0, (int)Math.Floor(cellMaxIndex / 2.0));
 911             SetCellRangeAddress(sheet, 1, 1, (int)Math.Floor(cellMaxIndex / 2.0) + 1, cellMaxIndex);
 912             titleRow1.Cells[0].SetCellValue(string.IsNullOrEmpty(title) ? sheetName : title);
 913             titleRow2.Cells[0].SetCellValue(childTitle);
 914             titleRow2.Cells[(int)Math.Floor(cellMaxIndex / 2.0) + 1].SetCellValue(string.IsNullOrEmpty(dateTime) ? DateTime.Now.ToString("yyyy-MM-dd") : dateTime);
 915             titleRow1.Cells.ForEach(c => c.CellStyle = GetCellStyle(workbook, borderStyleB: false, colorIndex: 32767, isSetFont: true));
 916 
 917             //titleRow2.Cells.ForEach(c => c.CellStyle = GetCellStyle(workbook, borderStyleT: false, colorIndex: 32767));
 918             titleRow2.Cells[0].CellStyle = GetCellStyle(workbook, borderStyleT: false, borderStyleR: false, colorIndex: 32767);
 919             titleRow2.Cells[titleRow2.LastCellNum - 1].CellStyle = GetCellStyle(workbook, borderStyleT: false, borderStyleL: false, colorIndex: 32767);
 920             titleRow1.HeightInPoints = 40;
 921             titleRow2.HeightInPoints = 20;
 922 
 923             int rowIndex = 3;
 924             foreach (DataGridViewRow row in grid.Rows)
 925             {
 926                 IRow dataRow = sheet.CreateRow(rowIndex);
 927                 for (int j = 0; j < grid.Columns.Count; j++)
 928                 {
 929                     ICell dataCell = dataRow.CreateCell(j);
 930                     dataCell.SetCellValue((row.Cells[j].Value ?? "").ToString());
 931                     dataCell.CellStyle = dataCellStyle;
 932                 }
 933                 rowIndex++;
 934             }
 935 
 936             //设置列宽
 937             for (int k = 0; k < grid.Columns.Count; k++)
 938             {
 939                 sheet.SetColumnWidth(k, (grid.Columns[k].HeaderText.Length < 20 ? 20 : grid.Columns[k].HeaderText.Length) * 256);
 940             }
 941             FileStream fs = new FileStream(filePath, FileMode.OpenOrCreate, FileAccess.ReadWrite);
 942             workbook.Write(fs);
 943             fs.Dispose();
 944             sheet = null;
 945             headerRow = null;
 946             workbook = null;
 947             return filePath;
 948         }
 949         /// <summary>
 950         /// DataGridView导出Excel
 951         /// </summary>
 952         /// <param name="grid">DataGridView数据源</param>
 953         /// <param name="sheetName">工作表名称</param>
 954         /// <param name="filePath">文件路径</param>
 955         /// <param name="title">首行标题</param>
 956         /// <param name="childTitle">子标题</param>
 957         /// <param name="dateTime">子标题时间</param>
 958         /// <param name="cellStyle">样式类</param>
 959         /// <returns></returns>
 960         public static string ExportToExcel(DataGridView grid, CellStyleModel cellStyle, string sheetName = "sheet1", string filePath = null, string title = "", string childTitle = "", string dateTime = "")
 961         {
 962             if (grid.Rows.Count <= 0) throw new ArgumentException("DataGridView数据源不存在游侠的数据!");
 963             if (string.IsNullOrEmpty(filePath))
 964             {
 965                 filePath = GetSaveFilePath();
 966             }
 967             if (string.IsNullOrEmpty(filePath)) throw new ArgumentNullException("excel文件路径为空字符串或null!");
 968             bool isCompatible = IsCompatible(filePath);
 969             IWorkbook workbook = CreateWorkbook(isCompatible);
 970             //表头行样式
 971             ICellStyle headerCellStyle = GetCellStyle(workbook, cellStyle??new CellStyleModel { ColorIndex=22});
 972             //数据行样式
 973             ICellStyle dataCellStyle = GetCellStyle(workbook,cellStyle??new CellStyleModel { HorizontalAlignment=NPOI.SS.UserModel.HorizontalAlignment.Left,ColorIndex=42});
 974             ISheet sheet = workbook.CreateSheet(sheetName);
 975             IRow titleRow1 = sheet.CreateRow(0);
 976             IRow titleRow2 = sheet.CreateRow(1);
 977             IRow headerRow = sheet.CreateRow(2);
 978 
 979             for (int i = 0; i < grid.Columns.Count; i++)
 980             {
 981                 ICell cell = headerRow.CreateCell(i);
 982                 ICell titleCell1 = titleRow1.CreateCell(i);
 983                 ICell titleCell2 = titleRow2.CreateCell(i);
 984                 cell.SetCellValue(grid.Columns[i].HeaderText);
 985                 cell.CellStyle = headerCellStyle;
 986             }
 987             //标题行样式
 988             int cellMaxIndex = titleRow1.LastCellNum - 1;
 989             SetCellRangeAddress(sheet, 0, 0, 0, titleRow1.LastCellNum - 1);
 990             SetCellRangeAddress(sheet, 1, 1, 0, (int)Math.Floor(cellMaxIndex / 2.0));
 991             SetCellRangeAddress(sheet, 1, 1, (int)Math.Floor(cellMaxIndex / 2.0) + 1, cellMaxIndex);
 992             titleRow1.Cells[0].SetCellValue(string.IsNullOrEmpty(title) ? sheetName : title);
 993             titleRow2.Cells[0].SetCellValue(childTitle);
 994             titleRow2.Cells[(int)Math.Floor(cellMaxIndex / 2.0) + 1].SetCellValue(string.IsNullOrEmpty(dateTime) ? DateTime.Now.ToString("yyyy-MM-dd") : dateTime);
 995             titleRow1.Cells.ForEach(c => c.CellStyle = GetCellStyle(workbook, 
 996                 cellStyle??new CellStyleModel { BorderStyleB=false,ColorIndex=32767,IsSetFont=true}));
 997             //titleRow2.Cells.ForEach(c => c.CellStyle = GetCellStyle(workbook, borderStyleT: false, colorIndex: 32767));
 998             titleRow2.Cells[0].CellStyle = GetCellStyle(workbook,
 999                 cellStyle??new CellStyleModel { BorderStyleT=false,BorderStyleR=false,ColorIndex=32767});
1000             titleRow2.Cells[titleRow2.LastCellNum - 1].CellStyle = GetCellStyle(workbook, 
1001                 cellStyle??new CellStyleModel { BorderStyleT=false,BorderStyleL=false,ColorIndex=32767});
1002             titleRow1.HeightInPoints = 40;
1003             titleRow2.HeightInPoints = 20;
1004             int rowIndex = 3;
1005             foreach (DataGridViewRow row in grid.Rows)
1006             {
1007                 IRow dataRow = sheet.CreateRow(rowIndex);
1008                 for (int j = 0; j < grid.Columns.Count; j++)
1009                 {
1010                     ICell dataCell = dataRow.CreateCell(j);
1011                     dataCell.SetCellValue((row.Cells[j].Value ?? "").ToString());
1012                     dataCell.CellStyle = dataCellStyle;
1013                 }
1014                 rowIndex++;
1015             }
1016 
1017             //设置列宽
1018             for (int k = 0; k < grid.Columns.Count; k++)
1019             {
1020                 sheet.SetColumnWidth(k, (grid.Columns[k].HeaderText.Length < 20 ? 20 : grid.Columns[k].HeaderText.Length) * 256);
1021             }
1022             FileStream fs = new FileStream(filePath, FileMode.OpenOrCreate, FileAccess.ReadWrite);
1023             workbook.Write(fs);
1024             fs.Dispose();
1025             sheet = null;
1026             headerRow = null;
1027             workbook = null;
1028             return filePath;
1029         }
1030         #endregion
1031         #region 公共导入方法
1032         /// <summary>
1033         /// 由Excel导入DataTable
1034         /// </summary>
1035         /// <param name="excelFileStream">Excel文件流</param>
1036         /// <param name="sheetName">工作表名称</param>
1037         /// <param name="headerRowIndex">表头行索引</param>
1038         /// <param name="isCompatible">是否兼容模式</param>
1039         /// <returns>返回DataTable</returns>
1040         public static DataTable ImportFromExcel(Stream excelFileStream, string sheetName, int headerRowIndex, bool isCompatible)
1041         {
1042             IWorkbook workbook = CreateWorkbook(isCompatible, excelFileStream);
1043             ISheet sheet = null;
1044             int sheetIndex = -1;
1045             if (int.TryParse(sheetName, out sheetIndex))
1046             {
1047                 sheet = workbook.GetSheetAt(sheetIndex);
1048             }
1049             else
1050             {
1051                 sheet = workbook.GetSheet(sheetName);
1052             }
1053             DataTable table = GetDataTableFromSheet(sheet, headerRowIndex);
1054             excelFileStream.Close();
1055             workbook = null;
1056             sheet = null;
1057             return table;
1058         }
1059         /// <summary>
1060         /// 由Excel导入DataTable
1061         /// </summary>
1062         /// <param name="excelFilePath">Excel文件路径</param>
1063         /// <param name="sheetName">工作表名称</param>
1064         /// <param name="headerRowIndex">表头行索引</param>
1065         /// <returns></returns>
1066         public static DataTable ImportFromExcel(string excelFilePath, string sheetName, int headerRowIndex)
1067         {
1068             if (string.IsNullOrEmpty(excelFilePath))
1069             {
1070                 excelFilePath = GetOpenFilePath();
1071             }
1072             if (string.IsNullOrEmpty(excelFilePath)) throw new ArgumentNullException("excel文件路径为空字符串或null");
1073             using (FileStream fs = File.OpenRead(excelFilePath))
1074             {
1075                 bool isCompatible = IsCompatible(excelFilePath);
1076                 return ImportFromExcel(fs, sheetName, headerRowIndex, isCompatible);
1077             }
1078         }
1079         /// <summary>
1080         /// Excel导入DataSet,如果有多个工作表,则导入多个DataTable
1081         /// </summary>
1082         /// <param name="excelFileStream">Excel文件流</param>
1083         /// <param name="headRowIndex">表头行索引</param>
1084         /// <param name="isCompatible">是否兼容模式</param>
1085         /// <returns>返回dataSet</returns>
1086         public static DataSet ImportFromExcel(Stream excelFileStream, int headerRowIndex, bool isCompatible)
1087         {
1088             DataSet ds = new DataSet();
1089             IWorkbook workbook = CreateWorkbook(isCompatible, excelFileStream);
1090             for (int i = 0; i < workbook.NumberOfSheets; i++)
1091             {
1092                 ISheet sheet = workbook.GetSheetAt(i);
1093                 DataTable table = GetDataTableFromSheet(sheet, headerRowIndex);
1094                 ds.Tables.Add(table);
1095             }
1096             excelFileStream.Close();
1097             workbook = null;
1098             return ds;
1099         }
1100         /// <summary>
1101         /// Excel导入DataSet,如果有多个工作表,则导入多个DataTable
1102         /// </summary>
1103         /// <param name="excelFilePath">Excel文件路径</param>
1104         /// <param name="headerRowIndex">表头行索引</param>
1105         /// <returns>返回dataSet</returns>
1106         public static DataSet ImportFromExcel(string excelFilePath, int headerRowIndex)
1107         {
1108             if (string.IsNullOrEmpty(excelFilePath))
1109             {
1110                 excelFilePath = GetOpenFilePath();
1111             }
1112             if (string.IsNullOrEmpty(excelFilePath)) throw new ArgumentNullException("excel文件路径为空字符串或null");
1113             using (FileStream fs = File.OpenRead(excelFilePath))
1114             {
1115                 bool isCompatible = IsCompatible(excelFilePath);
1116                 return ImportFromExcel(fs, headerRowIndex, isCompatible);
1117             }
1118         }
1119         /// <summary>
1120         /// Excel导入List
1121         /// </summary>
1122         /// <typeparam name="T">实体类</typeparam>
1123         /// <param name="excelFilePath">Excel文件全路径</param>
1124         /// <param name="headerRowIndex">行索引</param>
1125         /// <returns></returns>
1126         public static IList<T> ImportFromExcel<T>(string excelFilePath, int headerRowIndex = 0) where T : class, new()
1127         {
1128             IList<T> list = new List<T>();
1129             if (string.IsNullOrEmpty(excelFilePath))
1130             {
1131                 excelFilePath = GetOpenFilePath();
1132             }
1133             if (string.IsNullOrEmpty(excelFilePath)) throw new ArgumentNullException("excel文件路径为空字符串或null");
1134             try
1135             {
1136                 using (FileStream fs = File.OpenRead(excelFilePath))
1137                 {
1138                     bool isComPatible = IsCompatible(excelFilePath);
1139                     IWorkbook workbook = CreateWorkbook(isComPatible, fs);
1140                     ISheet sheet = workbook.GetSheetAt(0);
1141                     IRow headerRow = sheet.GetRow(0);
1142                     int headerCellNum = headerRow.LastCellNum;
1143                     for (int i = headerRow.FirstCellNum; i < headerCellNum; i++)
1144                     {
1145                         if (headerRow.GetCell(i) == null || headerRow.GetCell(i).StringCellValue.Trim() == "")
1146                         {
1147                             //如果标题行遇到空列,则不再向后继续读取
1148                             headerCellNum = i + 1;//返回真实列数
1149                             break;
1150                         }
1151                     }
1152                     //T t = default(T);
1153                     T t = new T();
1154                     PropertyInfo[] properties = typeof(T).GetProperties();
1155                     if (properties.Count() < headerCellNum)
1156                     {
1157                         throw new Exception("对象属性与工作表字段数量不符!");
1158                     }
1159                     for (int i = headerRowIndex + 1; i < sheet.LastRowNum + 1; i++)
1160                     {
1161                         IRow row = sheet.GetRow(i);
1162                         if (row != null && !string.IsNullOrEmpty(row.Cells[0].StringCellValue))
1163                         {
1164                             t = Activator.CreateInstance<T>();
1165                             for (int j = row.FirstCellNum; j < headerCellNum; j++)
1166                             {
1167                                 //如果碰到空cell,那么创建cell后赋空字符串
1168                                 var value = ValueType(properties[j].PropertyType, row.GetCellEx(j).ToString());
1169                                 properties[j].SetValue(t, value, null);
1170                             }
1171                             list.Add(t);
1172 
1173                         }
1174                     }
1175                 }
1176             }
1177             catch (Exception ex)
1178             {
1179                 list = null;
1180                 throw new Exception(ex.Message);
1181             }
1182             return list;
1183         }
1184         /// <summary>
1185         /// Excel导入List
1186         /// </summary>
1187         /// <typeparam name="T">实体类型</typeparam>
1188         /// <param name="excelFilePath">excel文件路径</param>
1189         /// <param name="fields">实体属性的字符串表示形式的数组,顺序与excel字段保持一致</param>
1190         /// <param name="headerRowIndex">头部索引行</param>
1191         /// <returns></returns>
1192         public static IList<T> ImportFromExcel<T>(ISheet sheet, string[] fields, int headerRowIndex = 0) where T : class, new()
1193         {
1194             if (fields == null) throw new ArgumentNullException("fields", "参数不能为null!");
1195             IList<T> list = new List<T>();
1196             try
1197             {
1198                 IRow headerRow = sheet.GetRow(0);
1199                 int headerCellNum = headerRow.LastCellNum;
1200                 for (int i = headerRow.FirstCellNum; i < headerCellNum; i++)
1201                 {
1202                     if (headerRow.GetCell(i) == null || headerRow.GetCell(i).StringCellValue.Trim() == "")
1203                     {
1204                         //如果标题行遇到空列,则不再向后继续读取
1205                         headerCellNum = i + 1;//返回真实列数
1206                         break;
1207                     }
1208                 }
1209                 if (fields.Length != headerCellNum)
1210                 {
1211                     throw new Exception("指定的对象属性数量超过工作表字段数量!");
1212                 }
1213                 //T t = default(T);
1214                 T t = new T();
1215                 for (int i = headerRowIndex + 1; i < sheet.LastRowNum + 1; i++)
1216                 {
1217                     IRow row = sheet.GetRow(i);
1218                     if (row != null && !string.IsNullOrEmpty(row.Cells[0].StringCellValue))
1219                     {
1220                         t = Activator.CreateInstance<T>();
1221                         for (int j = 0; j < fields.Length; j++)
1222                         {
1223                             var property = typeof(T).GetProperty(fields[j]);
1224                             //如果碰到空cell,那么创建cell后赋空字符串
1225                             var value = ValueType(property.PropertyType, row.GetCellEx(j).ToString());
1226                             property.SetValue(t, value, null);
1227                         }
1228                         list.Add(t);
1229                     }
1230                 }
1231             }
1232             catch (Exception ex)
1233             {
1234                 list = null;
1235                 throw new Exception(ex.Message);
1236             }
1237             return list;
1238         }
1239         /// <summary>
1240         /// Excel导入List
1241         /// </summary>
1242         /// <typeparam name="T">实体类型</typeparam>
1243         /// <param name="excelFilePath">excel文件全路径</param>
1244         /// <param name="fields">实体属性的字符串表示形式的数组,顺序与excel字段保持一致</param>
1245         /// <param name="sheetName">工作表名</param>
1246         /// <param name="headerRowIndex">首行索引,默认0</param>
1247         /// <returns></returns>
1248         public static IList<T> ImportFromExcel<T>(string excelFilePath, string[] fields, string sheetName, int headerRowIndex = 0) where T : class, new()
1249         {
1250             if (string.IsNullOrEmpty(excelFilePath))
1251             {
1252                 excelFilePath = GetOpenFilePath();
1253             }
1254             if (string.IsNullOrEmpty(excelFilePath)) throw new ArgumentNullException("excel文件路径为空字符串或null");
1255             FileStream fs = null;
1256             IWorkbook workbook = null;
1257             IList<T> list = null;
1258             try
1259             {
1260                 using (fs = File.OpenRead(excelFilePath))
1261                 {
1262                     bool isComPatible = IsCompatible(excelFilePath);
1263                     workbook = CreateWorkbook(isComPatible, fs);
1264                     list = ImportFromExcel<T>(workbook.GetSheet(sheetName), fields, headerRowIndex);
1265                 }
1266             }
1267             catch (Exception ex)
1268             {
1269                 workbook = null;
1270                 if (fs != null) fs.Dispose();
1271                 list = null;
1272                 throw new Exception(ex.Message);
1273             }
1274             return list;
1275         }
1276         /// <summary>
1277         /// Excel导入List
1278         /// </summary>
1279         /// <typeparam name="T">实体类型</typeparam>
1280         /// <param name="excelFilePath">excel文件全路径</param>
1281         /// <param name="fields">实体属性的字符串表示形式的数组,顺序与excel字段保持一致</param>
1282         /// <param name="sheetIndex">第几个工作表,默认值为1</param>
1283         /// <param name="headerRowIndex">首行索引,默认0</param>
1284         /// <returns></returns>
1285         public static IList<T> ImportFromExcel<T>(string excelFilePath, string[] fields, int sheetIndex = 1, int headerRowIndex = 0) where T : class, new()
1286         {
1287             if (string.IsNullOrEmpty(excelFilePath))
1288             {
1289                 excelFilePath = GetOpenFilePath();
1290             }
1291             if (string.IsNullOrEmpty(excelFilePath)) throw new ArgumentNullException("excel文件路径为空字符串或null");
1292             FileStream fs = null;
1293             IWorkbook workbook = null;
1294             IList<T> list = null;
1295             try
1296             {
1297                 using (fs = File.OpenRead(excelFilePath))
1298                 {
1299                     bool isComPatible = IsCompatible(excelFilePath);
1300                     workbook = CreateWorkbook(isComPatible, fs);
1301                     list = ImportFromExcel<T>(workbook.GetSheetAt(sheetIndex - 1), fields, headerRowIndex);
1302                 }
1303             }
1304             catch (Exception ex)
1305             {
1306                 workbook = null;
1307                 if (fs != null) fs.Dispose();
1308                 list = null;
1309                 throw new Exception(ex.Message);
1310             }
1311             return list;
1312         }
1313         #endregion
1314         #region 公共转换方法
1315         /// <summary>
1316         /// 将Excel的列索引转换为列名,列索引从0开始,列名从A开始。如第0列为A,第1列为B...
1317         /// </summary>
1318         /// <param name="index">列索引</param>
1319         /// <returns>列名,如第0列为A,第1列为B...</returns>
1320         public static string ConvertColumnIndexToColumnName(int index)
1321         {
1322             index++;
1323             int letterCount = 26;
1324             char[] array = new char[100];
1325             int i = 0;
1326             while (index > 0)
1327             {
1328                 int mod = index % letterCount;
1329                 if (mod == 0) mod = letterCount;
1330                 array[i++] = (char)(mod - 1 + 'A');
1331                 index = (index - 1) / 26;
1332             }
1333             StringBuilder sb = new StringBuilder(i);
1334             for (int j = i - 1; j >= 0; j--)
1335             {
1336                 sb.Append(array[j]);
1337             }
1338             return sb.ToString();
1339         }
1340         /// <summary>
1341         /// 转化日期
1342         /// </summary>
1343         /// <param name="date">日期</param>
1344         /// <returns></returns>
1345         public static DateTime ConvertDate(object date)
1346         {
1347             string dateString = (date ?? "").ToString();
1348             DateTime dt = new DateTime();
1349             if (DateTime.TryParse(dateString, out dt))
1350             {
1351                 return dt;
1352             }
1353             try
1354             {
1355                 string spString = "";
1356                 if (dateString.Contains("-"))
1357                 {
1358                     spString = "-";
1359                 }
1360                 else if (dateString.Contains("/"))
1361                 {
1362                     spString = "/";
1363                 }
1364                 string[] time = dateString.Split(spString.ToCharArray());
1365                 int year = Convert.ToInt32(time[2]);
1366                 int month = Convert.ToInt32(time[0]);
1367                 int day = Convert.ToInt32(time[1]);
1368                 string years = Convert.ToString(year);
1369                 string months = Convert.ToString(month);
1370                 string days = Convert.ToString(day);
1371                 if (months.Length == 4)
1372                 {
1373                     dt = Convert.ToDateTime(date);
1374                 }
1375                 else
1376                 {
1377                     string rq = "";
1378                     if (years.Length == 1)
1379                     {
1380                         years = "0" + years;
1381                     }
1382                     if (months.Length == 1)
1383                     {
1384                         months = "0" + months;
1385                     }
1386                     if (days.Length == 1)
1387                     {
1388                         days = "0" + days;
1389                     }
1390                     rq = "20" + years + "-" + months + "-" + days;
1391                     dt = Convert.ToDateTime(rq);
1392                 }
1393             }
1394             catch
1395             {
1396                 throw new Exception("日期格式不正确,转换日期失败!");
1397             }
1398             return dt;
1399         }
1400         /// <summary>
1401         /// 转化数字
1402         /// </summary>
1403         /// <param name="d">数字字符串</param>
1404         /// <returns></returns>
1405         public static decimal ConvertDecimal(object d)
1406         {
1407             string dString = (d ?? "").ToString();
1408             decimal result = 0m;
1409             if (decimal.TryParse(dString, out result))
1410             {
1411                 return result;
1412             }
1413             else
1414             {
1415                 throw new Exception("数字格式不正确,转换数字失败!");
1416             }
1417         }
1418         /// <summary>
1419         /// 转换布尔
1420         /// </summary>
1421         /// <param name="b">布尔值字符串</param>
1422         /// <returns></returns>
1423         public static bool ConvertBoolean(object b)
1424         {
1425             string bString = (b ?? "").ToString();
1426             bool result = false;
1427             if (bool.TryParse(bString, out result))
1428             {
1429                 return result;
1430             }
1431             else if (bString == "0" || bString == "1")
1432             {
1433                 return (bString == "0");
1434             }
1435             else
1436             {
1437                 throw new Exception("布尔格式不正确,转换布尔类型失败!");
1438             }
1439         }
1440         /// <summary>
1441         /// 实体类属性类型与Excel字段类型的转换
1442         /// </summary>
1443         /// <param name="t"></param>
1444         /// <param name="value"></param>
1445         /// <returns></returns>
1446         public static object ValueType(Type t, string value)
1447         {
1448             object o = null;
1449             string strType = t.Name;
1450             if (strType == "Nullable`1")
1451             {
1452                 strType = t.GetGenericArguments()[0].Name;
1453             }
1454             switch (strType)
1455             {
1456                 case "Decimal":
1457                     o = decimal.Parse(value);
1458                     break;
1459                 case "Int32":
1460                     o = int.Parse(value);
1461                     break;
1462                 case "Float":
1463                     o = float.Parse(value);
1464                     break;
1465                 case "DateTime":
1466                     o = DateTime.Parse(value);
1467                     break;
1468                 case "Char":
1469                     o = char.Parse(value);
1470                     break;
1471                 case "Boolean":
1472                     if (new string[] { "0","1"}.Contains(value))
1473                     {
1474                         o = Convert.ToBoolean(int.Parse(value));
1475                     }
1476                     else
1477                     {
1478                         o = bool.Parse(value);
1479                     }                   
1480                     break;
1481                 default:
1482                     o = value;
1483                     break;
1484             }
1485             return o;
1486         }
1487         #endregion
1488 
1489     }
1490 }

 

扩展IRow的GetCell方法
 1 using NPOI.SS.UserModel;
 2 using System;
 3 using System.Collections.Generic;
 4 using System.Linq;
 5 using System.Text;
 6 
 7 namespace RaysUtil.Office.Excel
 8 {
 9     public static  class NpoiEx
10     {
11         /// <summary>
12         /// 如果当前单元格为空,创建新的字符串类型的单元格
13         /// </summary>
14         /// <param name="row"></param>
15         /// <param name="index"></param>
16         /// <returns></returns>
17         public static ICell GetCellEx(this IRow row,int index)
18         {
19             return row.FirstOrDefault(n => n.ColumnIndex == index) ?? row.CreateCell(index,CellType.String);
20         }
21     }
22 }

 

 

样式类

 1 using NPOI.HSSF.Util;
 2 using NPOI.SS.UserModel;
 3 using System;
 4 using System.Collections.Generic;
 5 using System.Linq;
 6 using System.Text;
 7 
 8 namespace RaysUtil.Office.Excel
 9 {
10     //==============================================================
11     //  作者:*
12     //  时间:2018/1/6 21:06:18
13     //  文件名:CellStyleModel
14     //  版本:V1.0.1  
15     //  说明: 
16     //  修改者:**
17     //  修改说明: 分离出样式,单独写一个类
18     //==============================================================
19     public  class CellStyleModel
20     {
21         /// <summary>
22         /// 是否存在下边框,默认true
23         /// </summary>
24         public bool BorderStyleB { get; set; } = true;
25         /// <summary>
26         /// 是否存在左边框,默认true
27         /// </summary>
28         public bool BorderStyleL { get; set; } = true;
29         /// <summary>
30         /// 是否存在右边框,默认true
31         /// </summary>
32         public bool BorderStyleR { get; set; } = true;
33         /// <summary>
34         /// 是否存在上边框,默认true
35         /// </summary>
36         public bool BorderStyleT { get; set; } = true;
37         /// <summary>
38         /// 有边框的样式,默认薄边框
39         /// </summary>
40         public BorderStyle BorderStyle { get; set; } = BorderStyle.Thin;
41         /// <summary>
42         /// 背景色
43         /// </summary>
44         public short ColorIndex { get; set; } = HSSFColor.LightGreen.Index;
45         /// <summary>
46         /// 是否横向对齐,默认True
47         /// </summary>
48         public bool IsAlignment { get; set; } = true;
49         /// <summary>
50         /// 横向对齐,默认横向居中
51         /// </summary>
52         public HorizontalAlignment HorizontalAlignment { get; set; } = HorizontalAlignment.Center;
53         /// <summary>
54         /// 垂直对齐,默认垂直居中
55         /// </summary>
56         public VerticalAlignment VerticalAlignment { get; set; } = VerticalAlignment.Center;
57         /// <summary>
58         /// 是否设置字体信息,默认False
59         /// </summary>
60         public bool IsSetFont { get; set; } = false;
61         /// <summary>
62         /// 字体信息,默认null
63         /// </summary>
64         public IFont Font { get; set; } = null;
65         /// <summary>
66         /// 字体大小,默认30
67         /// </summary>
68         public short FontSize { get; set; } = 30;
69     }
70 }

 

 

标签:

版权申明:本站文章部分自网络,如有侵权,请联系:west999com@outlook.com
特别注意:本站所有转载文章言论不代表本站观点,本站所提供的摄影照片,插画,设计作品,如需使用,请与原作者联系,版权归原作者所有

上一篇:NEST - 编写查询

下一篇:echart 数据视图 样式重写