1 using System;  2 using System.Collections.Generic;  3 using System.Text;  4 using System.IO;  5 using System.Data;  6 using NPOI.SS.UserModel;  7 using NPOI.XSSF.UserModel;  8 using NPOI.HSSF.UserModel;  9  10 namespace NPOIExcel 11 { 12     public class ExcelEX 13     { 14  15         /// 
 16         /// 将DataTable数据导入到excel中 17         /// 
 18         /// 文件名 19         /// 要导入的数据 20         /// 要导入的excel的sheet的名称 21         /// 是否是追加模式 22         /// DataTable的列名是否要导入 23         /// 
     
      导入数据行数(包含列名那一行)
      24         public static int DataTableToExcel(string fileName, DataTable data, bool blnAppled = false, string sheetName = "sheet1", bool isColumnWritten = true) 25         { 26             int i = 0; 27             int j = 0; 28             int count = 0; 29             ISheet sheet = null; 30             IWorkbook workbook = null; 31             using (FileStream fs = new FileStream(fileName, FileMode.OpenOrCreate, FileAccess.ReadWrite)) 32             { 33                 if (!blnAppled) 34                 { 35                     if (fileName.IndexOf(".xlsx") > 0) // 2007版本 36                         workbook = new XSSFWorkbook(); 37                     else if (fileName.IndexOf(".xls") > 0) // 2003版本 38                         workbook = new HSSFWorkbook(); 39                 } 40                 else 41                 { 42                     if (fileName.IndexOf(".xlsx") > 0) // 2007版本 43                         workbook = new XSSFWorkbook(fs); 44                     else if (fileName.IndexOf(".xls") > 0) // 2003版本 45                         workbook = new HSSFWorkbook(fs); 46                 } 47  48                 try 49                 { 50                     if (!blnAppled && !string.IsNullOrEmpty(sheetName)) 51                     { 52                         if (workbook != null) 53                         { 54                             sheet = workbook.CreateSheet(sheetName); 55                         } 56                         else 57                         { 58                             return -1; 59                         } 60                     } 61                     else 62                     { 63                         sheet = workbook.GetSheetAt(0); 64                     } 65  66                     if (!blnAppled) 67                     { 68                         if (isColumnWritten == true) //写入DataTable的列名 69                         { 70                             IRow row = sheet.CreateRow(0); 71                             for (j = 0; j < data.Columns.Count; ++j) 72                             { 73                                 row.CreateCell(j).SetCellValue(data.Columns[j].ColumnName); 74                             } 75                             count = 1; 76                         } 77                         else 78                         { 79                             count = 0; 80                         } 81                     } 82  83                     count = sheet.LastRowNum + 1; 84  85                     for (i = 0; i < data.Rows.Count; ++i) 86                     { 87                         IRow row = sheet.CreateRow(count); 88                         for (j = 0; j < data.Columns.Count; ++j) 89                         { 90                             row.CreateCell(j).SetCellValue(data.Rows[i][j].ToString()); 91                         } 92                         ++count; 93                     } 94                     95                 } 96                 catch (Exception ex) 97                 { 98                     Console.WriteLine("Exception: " + ex.Message); 99                     return -1;100                 }101             }102 103             FileStream outFs = new FileStream(fileName, FileMode.Open);104             workbook.Write(outFs);105             outFs.Close();            106             return count;107         }108 109         /// 
110         /// 将excel中的数据导入到DataTable中111         /// 
112         /// excel工作薄sheet的名称113         /// 第一行是否是DataTable的列名114         /// 
     
      返回的DataTable
     115         public static DataTable ExcelToDataTable(string fileName, string sheetName = null, bool isFirstRowColumn = true)116         {117             ISheet sheet = null;118             DataTable data = new DataTable();119             int startRow = 0;120             IWorkbook workbook = null;121             try122             {123                 FileStream fs = new FileStream(fileName, FileMode.Open, FileAccess.Read);124                 if (fileName.IndexOf(".xlsx") > 0) // 2007版本125                     workbook = new XSSFWorkbook(fs);126                 else if (fileName.IndexOf(".xls") > 0) // 2003版本127                     workbook = new HSSFWorkbook(fs);128 129                 if (sheetName != null)130                 {131                     sheet = workbook.GetSheet(sheetName);132                     if (sheet == null) //如果没有找到指定的sheetName对应的sheet，则尝试获取第一个sheet133                     {134                         sheet = workbook.GetSheetAt(0);135                     }136                 }137                 else138                 {139                     sheet = workbook.GetSheetAt(0);140                 }141                 if (sheet != null)142                 {143                     IRow firstRow = sheet.GetRow(0);144                     int cellCount = firstRow.LastCellNum; //一行最后一个cell的编号 即总的列数145 146                     if (isFirstRowColumn)147                     {148                         for (int i = firstRow.FirstCellNum; i < cellCount; ++i)149                         {150                             ICell cell = firstRow.GetCell(i);151                             if (cell != null)152                             {153                                 string cellValue = cell.StringCellValue;154                                 if (cellValue != null)155                                 {156                                     DataColumn column = new DataColumn(cellValue);157                                     data.Columns.Add(column);158                                 }159                             }160                         }161                         startRow = sheet.FirstRowNum + 1;162                     }163                     else164                     {165                         startRow = sheet.FirstRowNum;166                     }167 168                     //最后一列的标号169                     int rowCount = sheet.LastRowNum;170                     for (int i = startRow; i <= rowCount; ++i)171                     {172                         IRow row = sheet.GetRow(i);173                         if (row == null) continue; //没有数据的行默认是null　　　　　　　174 175                         DataRow dataRow = data.NewRow();176                         for (int j = row.FirstCellNum; j < cellCount; ++j)177                         {178                             if (row.GetCell(j) != null) //同理，没有数据的单元格都默认是null179                                 dataRow[j] = row.GetCell(j).ToString();180                         }181                         data.Rows.Add(dataRow);182                     }183                 }184 185                 return data;186             }187             catch (Exception ex)188             {189                 Console.WriteLine("Exception: " + ex.Message);190                 return null;191             }192         }193 194 195     }196 }

1 using System; 2 using System.Collections.Generic; 3 using System.Linq; 4 using System.Text; 5 using System.Data; 6 using NPOIExcel; 7  8 namespace Test 9 {10     class Program11     {12         static void Main(string[] args)13         {14             DataTable dt = new DataTable();15             dt.Columns.Add("A", typeof(string));16             dt.Columns.Add("B", typeof(string));17             dt.Columns.Add("C", typeof(string));18             for (int i = 0; i < 50; i++)19             {20                 DataRow dr = dt.NewRow();21                 for (int j = 0; j < 3; j++)22                 {23                     dr[j] = "1_" + i.ToString() + "_" + j.ToString();24                 }25                 dt.Rows.Add(dr);26             }27             ExcelEX.DataTableToExcel("d:\\123.xlsx",dt);28             dt.Rows.Clear();29 30             for (int i = 0; i < 50; i++)31             {32                 DataRow dr = dt.NewRow();33                 for (int j = 0; j < 3; j++)34                 {35                     dr[j] = "2_" + i.ToString() + "_" + j.ToString();36                 }37                 dt.Rows.Add(dr);38             }39 40             ExcelEX.DataTableToExcel("d:\\123.xlsx", dt,true);41             Console.ReadKey();42         }43     }44 }