NPOI批量读取excel文件内容写入excel模板.rar

using NPOI.HSSF.UserModel; using NPOI.SS.UserModel; using NPOI.XSSF.UserModel; using System; using System.Collections.Generic; using System.ComponentModel; using System.Data; using System.Diagnostics; using System.Drawing; using System.IO; using System.Linq; using System.Text; using System.Threading.Tasks; using System.Windows.Forms; using ExcelApp = Microsoft.Office.Interop.Excel; namespace EmployeePerformanceForm { public partial class Form1 : Form { public Form1() { InitializeComponent(); } /// <summary> /// 选择绩效模板 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> private void button1_Click(object sender, EventArgs e) { OpenFileDialog ofd = new OpenFileDialog(); ofd.Title = "请选择绩效表模板"; ofd.Filter = "Excel 97-2003 工作簿(*.xls)|*.xls|Excel 工作簿(*.xlsx)|*.xlsx"; ofd.FilterIndex = 2; if (ofd.ShowDialog()==DialogResult.OK) { string filename = ofd.FileName; textBox1.Text = filename; } } /// <summary> /// 选择各项目的绩效表 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> private void button2_Click(object sender, EventArgs e) { OpenFileDialog ofd = new OpenFileDialog(); ofd.Title = "请选择各项目负责人提交的绩效表<可以多选>"; ofd.Filter = "Excel 97-2003 工作簿(*.xls)|*.xls|Excel 工作簿(*.xlsx)|*.xlsx"; ofd.FilterIndex = 2; ofd.Multiselect = true; if (ofd.ShowDialog() == DialogResult.OK) { string[] filename_arr = ofd.FileNames; foreach (string item in filename_arr) { listBox1.Items.Add(item); } } } /// <summary> /// 生成绩效汇总表 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> private void button3_Click(object sender, EventArgs e) { #region//初始化 string mb_path = textBox1.Text; string year_month = textBox3.Text; List<string> files = new List<string>(); foreach (string item in listBox1.Items) { files.Add(item); } #endregion if (files.Count > 0 && mb_path!="") { IWorkbook mb_workbook = null; //FileStream filestream_mb = new FileStream(mb_path, FileMode.Open, FileAccess.Read); using (FileStream fs = new FileStream(mb_path, FileMode.Open, FileAccess.Read)) { if (mb_path.IndexOf(".xlsx") > 0) // 2007版本 { mb_workbook = new XSSFWorkbook(fs); //xlsx数据读入workbook } else if (mb_path.IndexOf(".xls") > 0) // 2003版本 { mb_workbook = new HSSFWorkbook(fs); //xls数据读入workbook } fs.Close(); } ISheet mb_sheet = mb_workbook.GetSheetAt(0); IRow rowm = mb_sheet.GetRow(1); ICell cellm = rowm.GetCell(6); cellm.SetCellValue(year_month); foreach (string file in files) { //文件名 string filename = Path.GetFileNameWithoutExtension(file); IWorkbook workbook = null; //读取文件流 FileStream filestream = new FileStream(file, FileMode.Open, FileAccess.Read); if (file.IndexOf(".xlsx") > 0) { workbook = new XSSFWorkbook(filestream); } else if (file.IndexOf(".xls") > 0) { workbook = new HSSFWorkbook(filestream); } ISheet sheet = workbook.GetSheetAt(0); //获取行数 int rownum = sheet.LastRowNum; int i = 3; Dictionary<string, PersonDATA> dic_jxb = new Dictionary<string, PersonDATA>(); while (i < rownum+1) { int xh = 0; IRow row = sheet.GetRow(i); int.TryParse(row.GetCell(0).ToString(), out xh); if (xh != 0) { try { PersonDATA data = new PersonDATA(); string name = row.GetCell(1).ToString(); string xmmc = row.GetCell(2).ToString(); string gznr = row.GetCell(3).ToString(); string gzlx = row.GetCell(4).ToString(); if (row.GetCell(5).ToString() != "") { double wyts = double.Parse(row.GetCell(5).ToString());//double data.wyts = wyts; } if (row.GetCell(6).ToString() != "") { double dwcb = double.Parse(row.GetCell(6).ToString());//double data.dwcb = dwcb; } string jldw = row.GetCell(7).ToString();//string if (row.GetCell(8).ToString() != "") { double rwl = double.Parse(row.GetCell(8).ToString());//double data.rwl = rwl; } if (row.GetCell(9).ToString() != "") { double jl = double.Parse(row.GetCell(9).ToString());//double data.jl = jl; } if (row.GetCell(10).NumericCellValue !=0) { data.gt = row.GetCell(10).NumericCellValue; } if (row.GetCell(12)!=null) { string bz = row.GetCell(12).ToString(); data.bz = bz; } data.name = name; data.xmmc = xmmc; data.gznr = gznr; data.gzlx = gzlx; data.jldw = jldw; if (name != "") { if (data.dwcb != 0 && data.rwl != 0) { dic_jxb.Add(name, data); } } } catch { } } else { break; } i++; }