NPOI 之导入导出

转自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)

打赏 微信扫一扫

相关文章:

• 

  豆逼员工经理气乐了

  经理：你们要把每一个客户当作初恋情人，那么你们谈生意的成功率将会大增，懂吗？ 员工A：但初恋往往都是以失败告终的呀！ 员工B：我根本没谈过恋爱怎么办？ 员工C：... [阅读全文]
• 

  职场生活搞笑不断

  1、房产经纪人对一个寻找度假房的客户说：我可以给你一个非常好的报价。 房子非常靠近海岸，四层，退潮时甚至为五层。 2、上午九点开会,某领导先发言说：今天我只讲三... [阅读全文]
• 

  心理健康探索系列｜莫名地对手机产生依赖，可能是你的..

  心理健康探索系列｜莫名地对手机产生依赖，可能是你的..　　在诸多电子产品中，没有比手机更与人类的生活息息相关了。　　据有关机构的调查，2016... [阅读全文]
• 

  真好笑的同事啊

  同事：你会修电脑吗？ 我：会啊，怎么了？ 同事：过来帮我看看我电脑，启动不了不知道怎么回事？ 我：电源插上了吗？ 同事：插啦！ 我：那我没办法了！...查看全文 [阅读全文]
• 

  做了二十多年的主任,练就了十多项业余技能

  一位办公室主任要退休了，他感慨万分地说：做了二十多年的主任，服务了几任领导，练就了十多项业余技能。 我好奇的问：全什么技能？ 主任笑着说：喝酒，打牌，打麻将，网... [阅读全文]

版权声明：本文内容由互联网用户贡献，该文观点仅代表作者本人。本站仅提供信息存储服务，不拥有所有权，不承担相关法律责任。 如发现本站有涉嫌抄袭侵权/违法违规的内容， 请发送邮件至 [e-mail] 举报，一经查实将立刻删除。

发表评论

验证码：