导入导出大量excel文件
转载：https://blog.csdn.net/Amazing_Pei/article/details/86674196#comments_11153310
C#批量读取Execl文件_ C#_NPOI_批量读取Execl数据导入导出数据》
程序截图：
1 public static DataTable MuchExcelToDataTable(List<string> filePath, bool isColumnName) 2 { 3 DataTable dataTable = null;//零时存取一个Execl文件的数据 4 DataTable lastdata = null;//最后返回的Datatable 5 FileStream fs = null; 6 DataColumn column = null; 7 DataRow dataRow = null; 8 IWorkbook workbook = null; 9 ISheet sheet = null; 10 IRow row = null; 11 ICell cell = null; 12 int startRow = 0; 13 bool isFisrtTime = true; 14 try 15 { 16 if (filePath.Count > 0) 17 { 18 foreach (var item in filePath) 19 { 20 using (fs = File.OpenRead(item)) 21 { 22 // 2007版本 23 if (item.IndexOf(".xlsx") > 0) 24 workbook = new XSSFWorkbook(fs); 25 // 2003版本 26 else if (item.IndexOf(".xls") > 0) 27 workbook = new HSSFWorkbook(fs); 28 if (workbook != null) 29 { 30 sheet = workbook.GetSheetAt(0);//读取第一个sheet，当然也可以循环读取每个sheet 31 //需要新建一个DataTable来将每一次读取的都合并存起来，然后这个dataTable只存每一个的 32 dataTable = new DataTable();//每次在这里重新实例化Datatable会导致数据被覆盖抹掉 33 if (sheet != null) 34 { 35 int rowCount = sheet.LastRowNum;//总行数 36 if (rowCount > 0) 37 { 38 IRow firstRow = sheet.GetRow(0);//第一行 39 int cellCount = firstRow.LastCellNum;//列数 40 41 //构建datatable的列 42 if (isColumnName) 43 { 44 startRow = 1;//如果第一行是列名，则从第二行开始读取 45 for (int i = firstRow.FirstCellNum; i < cellCount; ++i) 46 { 47 cell = firstRow.GetCell(i); 48 if (cell != null) 49 { 50 if (cell.StringCellValue != null) 51 { 52 column = new DataColumn(cell.StringCellValue); 53 dataTable.Columns.Add(column); 54 } 55 } 56 } 57 } 58 else 59 { 60 for (int i = firstRow.FirstCellNum; i < cellCount; ++i) 61 { 62 column = new DataColumn("column" + (i + 1)); 63 dataTable.Columns.Add(column); 64 } 65 } 66 67 //填充行 68 for (int i = startRow; i <= rowCount; ++i) 69 { 70 row = sheet.GetRow(i); 71 if (row == null) continue; 72 73 dataRow = dataTable.NewRow(); 74 for (int j = row.FirstCellNum; j < cellCount; ++j) 75 { 76 cell = row.GetCell(j); 77 if (cell == null) 78 { 79 dataRow[j] = ""; 80 } 81 else 82 { 83 //CellType(Unknown = -1,Numeric = 0,String = 1,Formula = 2,Blank = 3,Boolean = 4,Error = 5,) 84 switch (cell.CellType) 85 { 86 case CellType.Blank: 87 dataRow[j] = ""; 88 break; 89 case CellType.Numeric: 90 short format = cell.CellStyle.DataFormat; 91 //对时间格式（2015.12.5、2015/12/5、2015-12-5等）的处理 92 if (format == 14 || format == 31 || format == 57 || format == 58) 93 dataRow[j] = cell.DateCellValue; 94 else 95 dataRow[j] = cell.NumericCellValue; 96 break; 97 case CellType.String: 98 dataRow[j] = cell.StringCellValue; 99 break; 100 } 101 } 102 } 103 dataTable.Rows.Add(dataRow); 104 } 105 } 106 } 107 108 if (dataTable != null) 109 { 110 //第一次的情况下，需要将整个表复制给lastdata表中，同时保存表结构 111 if (isFisrtTime) 112 { 113 lastdata = dataTable.Copy(); 114 isFisrtTime = false; 115 } 116 //第二次则只需要将新表中的数据循环添加到表中即可 117 else 118 { 119 for (int i = 0; i < dataTable.Rows.Count; i++) 120 { 121 lastdata.Rows.Add(dataTable.Rows[i].ItemArray); 122 int count = lastdata.Rows.Count; 123 } 124 } 125 } 126 } 127 } 128 } 129 } 130 return lastdata; 131 } 132 catch (Exception) 133 { 134 if (fs != null) 135 { 136 fs.Close(); 137 } 138 return null; 139 } 140 } 141 } 142
下面贴上，按钮事件，添加一个Listbox控件
1 /// <summary> 2 /// 批量读取 3 /// </summary> 4 /// <param name="sender"></param> 5 /// <param name="e"></param> 6 private void btn_muchread_Click(object sender, EventArgs e) 7 { 8 if (listpath.Items.Count > 0) 9 { 10 List<string> lp = new List<string>(); 11 for (int i = 0; i <= listpath.Items.Count - 1; i++) 12 { 13 lp.Add(listpath.Items[i].ToString()); 14 } 15 stopwatch.Start(); 16 DataTable dt = MuchExeclToRead.MuchExcelToDataTable(lp, true) as DataTable; 17 dgv_ExeclData.DataSource = dt; 18 stopwatch.Stop(); 19 label6.Text = stopwatch.ElapsedMilliseconds + "ms"; 20 label4.Text = dt.Rows.Count.ToString(); 21 } 22 else 23 { 24 MessageBox.Show("未选择文件夹，请选择后重试"); 25 return; 26 } 27 }
程序处理结果：
一次读取661个文件，数据量330000条；
