C# 使用 Npoi 操作Excel文件,你会了吗?
本文转载自微信公众号「后端Q」,作者conan。转载本文请联系后端Q公众号。
什么是NPOI
What’s NPOI This project is the .NET version of POI Java project at http://poi.apache.org/. POI is an open source project which can help you read/write xls, doc, ppt files. It has a wide application. For example, you can use it to a. generate a Excel report without Microsoft Office suite installed on your server and more efficient than call Microsoft Excel ActiveX at background; b. extract text from Office documents to help you implement full-text indexing feature (most of time this feature is used to create search engines). c. extract images from Office documents d. generate Excel sheets that contains formulas
在没有安装Microsoft Office Excel的机子上也可以对Excel进行操作。另外一种方法是使用.NET自带的excel API,但是这种方法需要运行环境安装微软的excel才行。
C#使用NPOI操作excel
将DataTable数据导入到excel中
- /// <summary>
- /// 将DataTable数据导入到excel中
- /// </summary>
- /// <param name="data">要导入的数据</param>
- /// <param name="isColumnWritten">DataTable的列名是否要导入</param>
- /// <param name="sheetName">要导入的excel的sheet的名称</param>
- /// <returns>导入数据行数(包含列名那一行)</returns>
- public int DataTableToExcel(System.Data.DataTable data, string sheetName, bool isColumnWritten)
- {
- int i = 0;
- int j = 0;
- int count = 0;
- ISheet sheet = null;
- try
- {
- fs = new FileStream(fileName, FileMode.OpenOrCreate, FileAccess.ReadWrite);
- if (fileName.IndexOf(".xls") > 0) // 2003版本
- workbook = new HSSFWorkbook();
- if (workbook != null)
- {
- sheet = workbook.CreateSheet(sheetName);
- }
- else
- {
- return -1;
- }
- if (isColumnWritten == true) //写入DataTable的列名
- {
- IRow row = sheet.CreateRow(0);
- for (j = 0; j < data.Columns.Count; ++j)
- {
- row.CreateCell(j).SetCellValue(data.Columns[j].ColumnName);
- }
- count = 1;
- }
- else
- {
- count = 0;
- }
- for (i = 0; i < data.Rows.Count; ++i)
- {
- IRow row = sheet.CreateRow(count);
- for (j = 0; j < data.Columns.Count; ++j)
- {
- row.CreateCell(j).SetCellValue(data.Rows[i][j].ToString());
- }
- ++count;
- }
- workbook.Write(fs); //写入到excel
- return count;
- }
- catch (Exception ex)
- {
- Console.WriteLine("Exception: " + ex.Message);
- return -1;
- }
- finally
- {
- fs?.Close();
- }
- }
将excel中的数据导入到DataTable中
- /// <summary>
- /// 将excel中的数据导入到DataTable中
- /// </summary>
- /// <param name="sheetName">excel工作薄sheet的名称</param>
- /// <param name="isFirstRowColumn">第一行是否是DataTable的列名</param>
- /// <returns>返回的DataTable</returns>
- public System.Data.DataTable ExcelToDataTable(string sheetName, bool isFirstRowColumn)
- {
- ISheet sheet = null;
- var data = new System.Data.DataTable();
- int startRow = 0;
- try
- {
- fs = new FileStream(fileName, FileMode.Open, FileAccess.Read);
- if (fileName.IndexOf(".xls") > 0) // 2003版本
- workbook = new HSSFWorkbook(fs);
- if (sheetName != null)
- {
- sheet = workbook.GetSheet(sheetName);
- if (sheet == null) //如果没有找到指定的sheetName对应的sheet,则尝试获取第一个sheet
- {
- sheet = workbook.GetSheetAt(0);
- }
- }
- else
- {
- sheet = workbook.GetSheetAt(0);
- }
- if (sheet != null)
- {
- IRow firstRow = sheet.GetRow(0);
- int cellCount = firstRow.LastCellNum; //一行最后一个cell的编号 即总的列数
- for (int i = 0; i < cellCount; ++i)
- {
- var column = new System.Data.DataColumn("column" + i);
- data.Columns.Add(column);
- }
- startRow = sheet.FirstRowNum;
- //最后一列的标号
- int rowCount = sheet.LastRowNum;
- for (int i = startRow; i <= rowCount; ++i)
- {
- IRow row = sheet.GetRow(i);
- if (row == null) continue; //没有数据的行默认是null
- var dataRow = data.NewRow();
- for (int j = row.FirstCellNum; j < cellCount; ++j)
- {
- if (row.GetCell(j) != null) //同理,没有数据的单元格都默认是null
- dataRow[j] = row.GetCell(j).ToString();
- }
- data.Rows.Add(dataRow);
- }
- }
- return data;
- }
- catch (Exception ex)
- {
- Console.WriteLine("Exception: " + ex.Message);
- return null;
- }
- }
相关文章
- 为什么你的手机需要更强大的AI 处理器?
- 苹果iPhone 11用户在iOS 13.3.1 Beta 2中可以禁用超宽带功能了
- 微软 GitHub 发布 Android Beta 版,支持暗黑模式
- 苹果2020不行了?库克:看你们谁有我赚得多
- 2019年苹果表现如何?读完这10个故事就明白了
- 外媒The Verge:苹果走过辉煌十年,但库克做产品难超乔布斯
- 谷歌官方突然决定,苹果猝不及防,安卓系统将迎来“大换血”
- 苹果终于要给MacBook Pro加上面容识别了?
- iOS 13.2.2系统,到底值不值得更新?清楚这几点你就明白了
- 两个月八次版本更新,iOS 13 遇到了什么问题?
- 因 Bug 太多,苹果打算大改 iOS 14 的开发模式
- 7 款 Mac 工具,提高你的效率!
- 支付宝一大波新功能上线:彻底离不开了
- 为什么9102年了,我们还要清理iOS缓存?
- 关于手机“系统更新”,究竟有没有升级的必要?来看看优缺点
- 高通副总裁Reiner Klement:“5G+人工智能+云”将如何变革未来产业
- 微信还能这样玩?教你用微信远程控制电脑
- 这样的 iOS 14 概念设计,你喜欢吗
- 万字长文!超全面的B端产品设计指南
- 测试效率提升一倍!第二届NCTS中国云测试峰会开启AI测试新范式