C# 使用 Npoi 操作Excel文件,你会了吗?

开发 后端
NPOI是指构建在POI 3.x版本之上的一个程序,NPOI可以在没有安装Office的情况下对Word或Excel文档进行读写操作。

 [[437708]]

本文转载自微信公众号「后端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中

  1. /// <summary> 
  2.       /// 将DataTable数据导入到excel中 
  3.       /// </summary> 
  4.       /// <param name="data">要导入的数据</param> 
  5.       /// <param name="isColumnWritten">DataTable的列名是否要导入</param> 
  6.       /// <param name="sheetName">要导入的excel的sheet的名称</param> 
  7.       /// <returns>导入数据行数(包含列名那一行)</returns
  8.       public int DataTableToExcel(System.Data.DataTable data, string sheetName, bool isColumnWritten) 
  9.       { 
  10.           int i = 0; 
  11.           int j = 0; 
  12.           int count = 0; 
  13.           ISheet sheet = null
  14.  
  15.           try 
  16.           { 
  17.               fs = new FileStream(fileName, FileMode.OpenOrCreate, FileAccess.ReadWrite); 
  18.               if (fileName.IndexOf(".xls") > 0) // 2003版本 
  19.                   workbook = new HSSFWorkbook(); 
  20.  
  21.               if (workbook != null
  22.               { 
  23.                   sheet = workbook.CreateSheet(sheetName); 
  24.               } 
  25.               else 
  26.               { 
  27.                   return -1; 
  28.               } 
  29.  
  30.               if (isColumnWritten == true) //写入DataTable的列名 
  31.               { 
  32.                   IRow row = sheet.CreateRow(0); 
  33.                   for (j = 0; j < data.Columns.Count; ++j) 
  34.                   { 
  35.                       row.CreateCell(j).SetCellValue(data.Columns[j].ColumnName); 
  36.                   } 
  37.                   count = 1; 
  38.               } 
  39.               else 
  40.               { 
  41.                   count = 0; 
  42.               } 
  43.  
  44.               for (i = 0; i < data.Rows.Count; ++i) 
  45.               { 
  46.                   IRow row = sheet.CreateRow(count); 
  47.                   for (j = 0; j < data.Columns.Count; ++j) 
  48.                   { 
  49.                       row.CreateCell(j).SetCellValue(data.Rows[i][j].ToString()); 
  50.                   } 
  51.                   ++count
  52.               } 
  53.               workbook.Write(fs); //写入到excel 
  54.               return count
  55.           } 
  56.           catch (Exception ex) 
  57.           { 
  58.               Console.WriteLine("Exception: " + ex.Message); 
  59.               return -1; 
  60.           } 
  61.           finally 
  62.           { 
  63.               fs?.Close(); 
  64.           } 
  65.       } 

将excel中的数据导入到DataTable中

  1. /// <summary> 
  2.       /// 将excel中的数据导入到DataTable中 
  3.       /// </summary> 
  4.       /// <param name="sheetName">excel工作薄sheet的名称</param> 
  5.       /// <param name="isFirstRowColumn">第一行是否是DataTable的列名</param> 
  6.       /// <returns>返回的DataTable</returns
  7.       public System.Data.DataTable ExcelToDataTable(string sheetName, bool isFirstRowColumn) 
  8.       { 
  9.           ISheet sheet = null
  10.           var data = new System.Data.DataTable(); 
  11.           int startRow = 0; 
  12.           try 
  13.           { 
  14.               fs = new FileStream(fileName, FileMode.Open, FileAccess.Read); 
  15.               if (fileName.IndexOf(".xls") > 0) // 2003版本 
  16.                   workbook = new HSSFWorkbook(fs); 
  17.  
  18.               if (sheetName != null
  19.               { 
  20.                   sheet = workbook.GetSheet(sheetName); 
  21.                   if (sheet == null) //如果没有找到指定的sheetName对应的sheet,则尝试获取第一个sheet 
  22.                   { 
  23.                       sheet = workbook.GetSheetAt(0); 
  24.                   } 
  25.               } 
  26.               else 
  27.               { 
  28.                   sheet = workbook.GetSheetAt(0); 
  29.               } 
  30.               if (sheet != null
  31.               { 
  32.                   IRow firstRow = sheet.GetRow(0); 
  33.                   int cellCount = firstRow.LastCellNum; //一行最后一个cell的编号 即总的列数 
  34.                   for (int i = 0; i < cellCount; ++i) 
  35.                   { 
  36.                       var column = new System.Data.DataColumn("column" + i); 
  37.                       data.Columns.Add(column); 
  38.                   } 
  39.                   startRow = sheet.FirstRowNum; 
  40.                   //最后一列的标号 
  41.                   int rowCount = sheet.LastRowNum; 
  42.                   for (int i = startRow; i <= rowCount; ++i) 
  43.                   { 
  44.                       IRow row = sheet.GetRow(i); 
  45.                       if (row == nullcontinue; //没有数据的行默认是null        
  46.  
  47.                       var dataRow = data.NewRow(); 
  48.                       for (int j = row.FirstCellNum; j < cellCount; ++j) 
  49.                       { 
  50.                           if (row.GetCell(j) != null) //同理,没有数据的单元格都默认是null 
  51.                               dataRow[j] = row.GetCell(j).ToString(); 
  52.                       } 
  53.                       data.Rows.Add(dataRow); 
  54.                   } 
  55.               } 
  56.  
  57.               return data; 
  58.           } 
  59.           catch (Exception ex) 
  60.           { 
  61.               Console.WriteLine("Exception: " + ex.Message); 
  62.               return null
  63.           } 
  64.       } 

 

 

责任编辑:武晓燕 来源: 后端Q
相关推荐

2021-02-02 07:47:36

NPOI基础Excel

2021-02-04 07:22:07

NPOI操作Excel

2023-06-30 09:45:00

文件读写操作Java

2024-02-02 11:03:11

React数据Ref

2009-08-18 16:14:05

C# 操作Excel

2009-08-18 16:20:09

C# 操作Excel

2019-05-20 16:30:36

PythonMySQL存储

2022-10-21 13:14:41

lua插件neovim

2009-08-18 15:49:19

C# 操作Excel

2009-08-18 13:49:21

C# 操作Excel

2024-02-06 08:33:54

文件系统SSD

2018-04-26 16:35:44

PythonMySQL存储

2022-11-02 10:21:41

K8s pod运维

2024-02-27 08:39:19

RustJSON字符串

2023-06-15 08:00:23

2022-10-30 10:31:42

i2ccpuftrace

2022-11-21 16:57:20

2009-08-18 16:04:12

C# 操作Excel

2023-10-30 07:05:31

2023-12-27 07:31:45

json产品场景
点赞
收藏

51CTO技术栈公众号