C# NOPI 项目实战(经典)(可下载项目源码)

1 -.首先说明下项目目的:

    之vb.net教程前我有写c#教程过一篇  "NPOI操作EXCEL" 

这篇python基础教程文章主要介java基础教程绍了如何安sql教程装NPOI，以及NPOI具体如何使用，并且用具体实例介绍了excel导入到datagridview以及 datagridview如何导出到excel并保存。如果不清楚这块的去我公众号去搜索这篇文章阅读。

      今天这篇文章主要是实现多个excel多张表格的合并，这个是我们数据处理时候经常使用的一个功能，其实最佳的途径是用excel自带的power query（excel2016自带，excel2016以下版本需要安装插件），用这个功能你甚至不需要写一句代码，就可以轻轻松松实现百万计甚至千万级数据操作，当然你也可以用excel里面的vba，这些都可以，但是这不是今天要将的内容，今天的内容就是要用C# 实现数据表格的合并。

2. 源码:

npoi.cs

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305 306 307 308 309 310 311 312 313 314 315 316 317 318 319 320 321 322 323 324 325 326 327 328 329 330 331 332 333 334 335 336 337 338

using System; using System.Collections.Generic; using NPOI.HSSF.UserModel; using NPOI.SS.UserModel; using NPOI.XSSF.UserModel; using System.Data; using System.IO; namespace npoi1 {     public class NPOIExcel     {         /// <summary>         /// 将excel导入到datatable         /// </summary>         /// <param name="filePath">excel路径</param>         /// <param name="isColumnName">第一行是否是列名</param>         /// <returns>返回datatable</returns>         public static DataTable ExcelToDataTable(string filePath, bool isColumnName)         {             DataTable dataTable = null;             FileStream fs = null;             DataColumn column = null;             DataRow dataRow = null;             IWorkbook workbook = null;             ISheet sheet = null;             IRow row = null;             ICell cell = null;             int startRow = 0;             try             {                 using (fs = File.OpenRead(filePath))                 {                     // 2007版本                     if (filePath.IndexOf(".xlsx") > 0)                         workbook = new XSSFWorkbook(fs);                     // 2003版本                     else if (filePath.IndexOf(".xls") > 0)                         workbook = new HSSFWorkbook(fs);                     if (workbook != null)                     {                         sheet = workbook.GetSheetAt(0);//读取第一个sheet，当然也可以循环读取每个sheet                         dataTable = new DataTable();                         if (sheet != null)                         {                             int rowCount = sheet.LastRowNum;//总行数                             if (rowCount > 0)                             {                                 IRow firstRow = sheet.GetRow(0);//第一行                                 int cellCount = firstRow.LastCellNum;//列数                                 //构建datatable的列                                 if (isColumnName)                                 {                                     startRow = 1;//如果第一行是列名，则从第二行开始读取                                     for (int i = firstRow.FirstCellNum; i < cellCount; ++i)                                     {                                         cell = firstRow.GetCell(i);                                         if (cell != null)                                         {                                             if (cell.StringCellValue != null)                                             {                                                 column = new DataColumn(cell.StringCellValue);                                                 dataTable.Columns.Add(column);                                             }                                         }                                     }                                 }                                 else                                 {                                     for (int i = firstRow.FirstCellNum; i < cellCount; ++i)                                     {                                         column = new DataColumn("column" + (i + 1));                                         dataTable.Columns.Add(column);                                     }                                 }                                 //填充行                                 for (int i = startRow; i <= rowCount; ++i)                                 {                                     row = sheet.GetRow(i);                                     if (row == null) continue;                                     dataRow = dataTable.NewRow();                                     for (int j = row.FirstCellNum; j < cellCount; ++j)                                     {                                         cell = row.GetCell(j);                                         if (cell == null)                                         {                                             dataRow[j] = "";                                         }                                         else                                         {                                             //CellType(Unknown = -1,Numeric = 0,String = 1,Formula = 2,Blank = 3,Boolean = 4,Error = 5,)                                             switch (cell.CellType)                                             {                                                 case CellType.Blank:                                                     dataRow[j] = "";                                                     break;                                                 case CellType.Numeric:                                                     short format = cell.CellStyle.DataFormat;                                                     //对时间格式（2015.12.5、2015/12/5、2015-12-5等）的处理                                                     if (format == 14 || format == 31 || format == 57 || format == 58)                                                         dataRow[j] = cell.DateCellValue;                                                     else                                                         dataRow[j] = cell.NumericCellValue;                                                     break;                                                 case CellType.String:                                                     dataRow[j] = cell.StringCellValue;                                                     break;                                             }                                         }                                     }                                     dataTable.Rows.Add(dataRow);                                 }                             }                         }                     }                 }                 return dataTable;             }             catch (Exception)             {                 if (fs != null)                 {                     fs.Close();                 }                 return null;             }         }         /// <summary>         /// 写入excel         /// </summary>         /// <param name="dt">datatable</param>         /// <param name="strFile">strFile</param>         /// <returns></returns>         public static bool DataTableToExcel(DataTable dt, string strFile)         {             bool result = false;             IWorkbook workbook = null;             FileStream fs = null;             IRow row = null;             ISheet sheet = null;             ICell cell = null;             try             {                 if (dt != null && dt.Rows.Count > 0)                 {                     workbook = new XSSFWorkbook();//HSSFWorkbook:是操作Excel2003以前（包括2003）的版本，扩展名是.xls  XSSFWorkbook:是操作Excel2007的版本，扩展名是.xlsx                     sheet = workbook.CreateSheet("Sheet0");//创建一个名称为Sheet0的表                     int rowCount = dt.Rows.Count;//行数                     int columnCount = dt.Columns.Count;//列数                     //设置列头                     row = sheet.CreateRow(0);//excel第一行设为列头                     for (int c = 0; c < columnCount; c++)                     {                         cell = row.CreateCell(c);                         cell.SetCellValue(dt.Columns[c].ColumnName);                     }                     //设置每行每列的单元格,                     for (int i = 0; i < rowCount; i++)                     {                         row = sheet.CreateRow(i + 1);                         for (int j = 0; j < columnCount; j++)                         {                             cell = row.CreateCell(j);//excel第二行开始写入数据                             cell.SetCellValue(dt.Rows[i][j].ToString());                         }                     }                     using (fs = File.OpenWrite(strFile))                     {                         workbook.Write(fs);//向打开的这个xls文件中写入数据                         result = true;                     }                 }                 return result;             }             catch (Exception ex)             {                 if (fs != null)                 {                     fs.Close();                 }                 Console.WriteLine(ex.StackTrace + ex.Message);                 return false;             }         }         /// <summary>         /// Excel导入成Datable         /// </summary>         /// <param name="file">导入路径(包含文件名与扩展名)</param>         /// <returns></returns>         public static DataTable ExcelToTable(string file ,int nSheet)         {             DataTable dt = new DataTable();             IWorkbook workbook;             string fileExt = Path.GetExtension(file).ToLower();             using (FileStream fs = new FileStream(file, FileMode.Open, FileAccess.Read))             {                 //XSSFWorkbook 适用XLSX格式，HSSFWorkbook 适用XLS格式                 if (fileExt == ".xlsx") { workbook = new XSSFWorkbook(fs); } else if (fileExt == ".xls") { workbook = new HSSFWorkbook(fs); } else { workbook = null; }                 if (workbook == null) { return null; }                 ISheet sheet = null;                 if (nSheet < workbook.NumberOfSheets) //为了不让获取超过表格数量最大索引导致报错                     sheet = workbook.GetSheetAt(nSheet);                 else                 { return null; }                 //表头                 IRow header = sheet.GetRow(sheet.FirstRowNum);                 List<int> columns = new List<int>();                 for (int i = 0; i < header.LastCellNum; i++)                 {                     object obj = GetValueType(header.GetCell(i));                     if (obj == null || obj.ToString() == string.Empty)                     {                         dt.Columns.Add(new DataColumn("Columns" + i.ToString()));                     }                     else                         dt.Columns.Add(new DataColumn(obj.ToString()));                     columns.Add(i);                 }                 //数据                 for (int i = sheet.FirstRowNum + 1; i <= sheet.LastRowNum; i++)                 {                     DataRow dr = dt.NewRow();                     bool hasValue = false;                     foreach (int j in columns)                     {                         dr[j] = GetValueType(sheet.GetRow(i).GetCell(j));                         if (dr[j] != null && dr[j].ToString() != string.Empty)                         {                             hasValue = true;                         }                     }                     if (hasValue)                     {                         dt.Rows.Add(dr);                     }                 }             }             return dt;         }         /// <summary>         /// Datable导出成Excel         /// </summary>         /// <param name="dt"></param>         /// <param name="file">导出路径(包括文件名与扩展名)</param>         public static void TableToExcel(DataTable[] dt, string file , int nSheet)         {             IWorkbook workbook;             string fileExt = Path.GetExtension(file).ToLower();             if (fileExt == ".xlsx") { workbook = new XSSFWorkbook(); } else if (fileExt == ".xls") { workbook = new HSSFWorkbook(); } else { workbook = null; }             if (workbook == null) { return; }             ISheet[] sheet = new ISheet[nSheet];             for (int k = 0; k < nSheet; k++)             {                 if (dt[k] != null)                 {                     sheet[k] = string.IsNullOrEmpty(dt[k].TableName) ? workbook.CreateSheet("Sheet" + (k + 1).ToString()) : workbook.CreateSheet(dt[k].TableName);                     //表头                     IRow row = sheet[k].CreateRow(0);                     for (int i = 0; i < dt[k].Columns.Count; i++)                     {                         ICell cell = row.CreateCell(i);                         cell.SetCellValue(dt[k].Columns[i].ColumnName);                     }                     //数据                     for (int i = 0; i < dt[k].Rows.Count; i++)                     {                         IRow row1 = sheet[k].CreateRow(i + 1);                         for (int j = 0; j < dt[k].Columns.Count; j++)                         {                             ICell cell = row1.CreateCell(j);                             cell.SetCellValue(dt[k].Rows[i][j].ToString());                         }                     }                 }                 else                    continue;             }                               //转为字节数组             MemoryStream stream = new MemoryStream();             workbook.Write(stream);             var buf = stream.ToArray();             //保存为Excel文件             using (FileStream fs = new FileStream(file, FileMode.Create, FileAccess.Write))             {                 fs.Write(buf, 0, buf.Length);                 fs.Flush();             }         }         /// <summary>         /// 获取单元格类型         /// </summary>         /// <param name="cell"></param>         /// <returns></returns>         private static object GetValueType(ICell cell)         {             if (cell == null)                 return null;             switch (cell.CellType)             {                 case CellType.Blank: //BLANK:                     return null;                 case CellType.Boolean: //BOOLEAN:                     return cell.BooleanCellValue;                 case CellType.Numeric: //NUMERIC:                     return cell.NumericCellValue;                 case CellType.String: //STRING:                     return cell.StringCellValue;                 case CellType.Error: //ERROR:                     return cell.ErrorCellValue;                 case CellType.Formula: //FORMULA:                 default:                     return "=" + cell.CellFormula;             }         }     } }

　　mainForm.cs

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87

using NPOI.SS.UserModel; using System; using System.Data; using System.Diagnostics; using System.IO; using System.Windows.Forms; namespace npoi1 {     public partial class mainForm : Form     {         public mainForm()         {             InitializeComponent();         }         private void Form1_Load(object sender, EventArgs e)         {         }         public static int sheetCount=5;//定义表的最大数量         DataTable[] lastTable = new DataTable[sheetCount];         private void button1_Click(object sender, EventArgs e)         {             Stopwatch sw = new Stopwatch();             sw.Start();             //dataGridView1.DataSource = NPOIExcel.ExcelToDataTable("电脑统计表.xlsx", true);//方式1             DataTable[] tempTable = new DataTable[sheetCount];             DirectoryInfo dir = new DirectoryInfo(@"C:\Users\TPS20\Desktop\excel\");             for (int i = 0; i < sheetCount; i++) //遍历一个excel的每个sheet             {                 bool flag = true;                 foreach (FileInfo dChild in dir.GetFiles("*.xlsx"))//遍历文件夹下的xlsx文件                 {                     tempTable[i] =  NPOIExcel.ExcelToTable(dChild.FullName, i);//方式2                     if (flag && lastTable[i] == null&& tempTable[i] != null) //第一次直接赋值，使得lastTable[i]获取表结构不为null                     {                         lastTable[i] = tempTable[i];                         flag = false;                     }                     if (tempTable[i]!=null)                     GetAllDataTable(tempTable[i] , i);//DataTable合并                 }                          }             NPOIExcel.TableToExcel(lastTable, @"C:\Users\TPS20\Desktop\excel\1\111.xlsx", sheetCount);//方式2             dataGridView1.DataSource = lastTable[0];//调试时候显示用的，可以去掉             dataGridView2.DataSource = lastTable[1];//调试时候显示用的，可以去掉             dataGridView3.DataSource = lastTable[2];//调试时候显示用的，可以去掉             sw.Stop();             label1.Text = sw.ElapsedMilliseconds.ToString("数据导入耗时：" + "0000"+"ms");             MessageBox.Show("数据导入完成");         }         public void GetAllDataTable(DataTable dt,int nSheet)         {             if(lastTable[nSheet]!=null)             lastTable[nSheet].Merge(dt, false, MissingSchemaAction.AddWithKey);         }         private void dataGridView1_RowsAdded(object sender, DataGridViewRowsAddedEventArgs e)         {             for (int i = 0; i < dataGridView1.Rows.Count; i++)                 this.dataGridView1.Rows[i].HeaderCell.Value = (i + 1).ToString();         }         private void button2_Click(object sender, EventArgs e)         {             //Stopwatch sw = new Stopwatch();             //sw.Start();             //DataTable dt = (dataGridView1.DataSource as DataTable);             NPOIExcel.DataTableToExcel(dt, "d:\\111.xlsx");//方式1             //NPOIExcel.TableToExcel(dt, "d:\\111.xlsx",0);//方式2             //sw.Stop();             //label2.Text = sw.ElapsedMilliseconds.ToString("数据导出耗时：" + "0000" + "ms");             //MessageBox.Show("数据导出完成");         }     } }

　　3. 运行效果

4.源码百度网盘下载地址：

链接：https://pan.baidu.com/s/1L0rv_CM0N4FTZ-fpwS-YXA 

提取码：cjj8