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

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

之前我有写过一篇 "NPOI操作EXCEL"

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

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

2. 源码:

npoi.cs

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

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("数据导出完成");
        }
    }
 
}