1.文件操作
1.1递归目录，查找所有文件
1 #region 递归目录路径 2 private ListGetDirs(string path) 3 { 4 string[] dirs = Directory.GetDirectories(path); 5 List result = new List (); 6 foreach(string dir in dirs) 7 { 8 result.Add(dir); 9 }10 if (dirs.Length > 0)11 {12 foreach (string dir in dirs)13 {14 result.AddRange(GetDirs(dir));15 }16 }17 return result;18 }19 #endregion20 21 //调用22 List strdirs = GetDirs(path);23 List strfiles = new List ();24 foreach (string dir in strdirs)25 {26 foreach (string file in Directory.GetFiles(dir,"*汇总*.xls"))27 {28 strfiles.Add(file);29 }30 }
1.2 获取文件目录与文件名：
1 #region 获取文件路径设置2 private ListGetFileSet(string par_path)3 {4 List result = new List ();5 result.Add(Path.GetDirectoryName(par_path));6 result.Add(Path.GetFileNameWithoutExtension(par_path));7 return result;8 }9 #endregion
2.导入导出Excel与数据统计：
1 using System; 2 using System.Data; 3 using System.Text; 4 using System.Windows.Forms; 5 using System.IO; 6 using NPOI.HSSF.UserModel; 7 using NPOI.SS.UserModel; 8 using System.Collections.Generic; 9 10 #region 处理数据 11 private void BI(string mespath, string scorepath) 12 { 13 try 14 { 15 List fnames = GetFileSet(scorepath); 16 17 DataTable msg_table = new DataTable(); 18 DataTable score_table = new DataTable(); 19 //数据处理 20 //读取考生信息表 21 using (FileStream fs = File.OpenRead(mespath)) 22 { 23 HSSFWorkbook msg_xls = new HSSFWorkbook(fs); 24 for (int i = 0; i < msg_xls.NumberOfSheets; i++) 25 { 26 ISheet sheet = msg_xls.GetSheetAt(i); 27 for (int j = 0; j <= sheet.LastRowNum; j++) 28 { 29 IRow row = sheet.GetRow(j); 30 if (j > 0) 31 { 32 if (row != null) 33 { 34 DataRow t_row = msg_table.NewRow(); 35 for (int k = 0; k <= row.LastCellNum; k++) 36 { 37 ICell cell = row.GetCell(k); 38 if (cell != null) 39 { 40 t_row[k] = cell.ToString().Trim(); 41 } 42 } 43 msg_table.Rows.Add(t_row); 44 } 45 } 46 else 47 { 48 if (row != null) 49 { 50 //表定义 51 for (int k = 0; k <= row.LastCellNum; k++) 52 { 53 ICell cell = row.GetCell(k); 54 if (cell != null) 55 { 56 msg_table.Columns.Add(new DataColumn(cell.StringCellValue, typeof(string))); 57 } 58 } 59 } 60 } 61 } 62 } 63 } 64 //读取考生成绩表 65 using (FileStream fs = File.OpenRead(scorepath)) 66 { 67 HSSFWorkbook msg_xls = new HSSFWorkbook(fs); 68 for (int i = 0; i < msg_xls.NumberOfSheets; i++) 69 { 70 ISheet sheet = msg_xls.GetSheetAt(i); 71 for (int j = 0; j <= sheet.LastRowNum; j++) 72 { 73 IRow row = sheet.GetRow(j); 74 if (j > 0) 75 { 76 if (row != null) 77 { 78 DataRow t_row = score_table.NewRow(); 79 for (int k = 0; k <= row.LastCellNum; k++) 80 { 81 ICell cell = row.GetCell(k); 82 if (cell != null) 83 { 84 t_row[k] = cell.ToString().Trim(); 85 } 86 } 87 score_table.Rows.Add(t_row); 88 } 89 } 90 else 91 { 92 if (row != null) 93 { 94 //表定义 95 for (int k = 0; k <= row.LastCellNum; k++) 96 { 97 ICell cell = row.GetCell(k); 98 if (cell != null) 99 {100 score_table.Columns.Add(new DataColumn(cell.StringCellValue.Trim(), typeof(string)));101 }102 }103 }104 }105 }106 }107 }108 DataTable res_table = new DataTable();109 res_table.Columns.Add(new DataColumn("考号", typeof(string)));110 res_table.Columns.Add(new DataColumn("姓名", typeof(string)));111 res_table.Columns.Add(new DataColumn("身份证号码", typeof(string)));112 res_table.Columns.Add(new DataColumn("执业类别", typeof(string)));113 res_table.Columns.Add(new DataColumn("执业证书编号", typeof(string)));114 res_table.Columns.Add(new DataColumn("资格证编号", typeof(string)));115 res_table.Columns.Add(new DataColumn("总分", typeof(float)));116 117 118 //进行数据统计119 float failcount = 0; //不及格人数120 List failtable = new List (); //不及格名单121 student_modele failstudent = null;122 //进行数据处理123 DataRow res_row = null;124 for (int n = 0; n < score_table.Rows.Count; n++)125 {126 res_row = res_table.NewRow();127 res_row["考号"] = score_table.Columns.Contains("考号") ? score_table.Rows[n]["考号"] : "";128 res_row["姓名"] = score_table.Columns.Contains("姓名") ? score_table.Rows[n]["姓名"] : "";129 res_row["身份证号码"] = score_table.Columns.Contains("身份证号码") ? score_table.Rows[n]["身份证号码"] : "";130 res_row["执业类别"] = score_table.Columns.Contains("执业类别") ? score_table.Rows[n]["执业类别"] : "";131 res_row["执业证书编号"] = score_table.Columns.Contains("执业证书编号") ? score_table.Rows[n]["执业证书编号"] : "";132 res_row["资格证编号"] = score_table.Columns.Contains("资格证编号") ? score_table.Rows[n]["资格证编号"] : "";133 res_row["总分"] = score_table.Columns.Contains("总分") ? score_table.Rows[n]["总分"] : 0;134 135 if ((float)res_row["总分"] < 60)136 {137 failcount++;138 failstudent = new student_modele();139 failstudent.Num = res_row["考号"].ToString();140 failstudent.Name = res_row["姓名"].ToString();141 failstudent.PeopleNum = res_row["身份证号码"].ToString();142 failstudent.Type = res_row["执业类别"].ToString();143 failstudent.TypeNum = res_row["执业证书编号"].ToString();144 failstudent.SeniorityNum = res_row["资格证编号"].ToString();145 failstudent.Score=(float) res_row["总分"];146 failtable.Add(failstudent);147 }148 res_table.Rows.Add(res_row);149 }150 this.Total_kryptonTextBox.Text += fnames[1] + "统计：\r\n";151 float per = 1 - failcount / res_table.Rows.Count;152 this.Total_kryptonTextBox.Text += "合格率："+per.ToString("p") + "\r\n";153 if (failcount > 0)154 {155 this.Total_kryptonTextBox.Text += "不合格名单：\r\n";156 for (int n = 0; n < failtable.Count; n++)157 {158 this.Total_kryptonTextBox.Text += failtable[n].Num + "\r\n";159 }160 }161 this.Total_kryptonTextBox.Text += "--------------------------------------------------------------------------\r\n";162 //导出Excel163 using (FileStream fs = new FileStream(fnames[0] + "\\" + fnames[1] + "_汇总.xls", FileMode.Create))164 {165 HSSFWorkbook result_xls = new HSSFWorkbook();166 ISheet sheet = result_xls.CreateSheet(fnames[1]);167 //创建行168 IRow row = sheet.CreateRow(0);169 for (int n = 0; n < res_table.Columns.Count; n++)170 {171 //单元格设置172 ICell cell = row.CreateCell(n);173 cell.SetCellValue(res_table.Columns[n].ColumnName);174 }175 //设置表头样式176 var titlestyle = result_xls.CreateCellStyle();177 titlestyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;178 //设置excel列宽179 sheet.SetColumnWidth(0, 15 * 256);180 sheet.GetRow(0).GetCell(0).CellStyle = titlestyle;181 182 sheet.SetColumnWidth(1, 10 * 256);183 sheet.GetRow(0).GetCell(1).CellStyle = titlestyle;184 185 sheet.SetColumnWidth(2, 15 * 256);186 sheet.GetRow(0).GetCell(2).CellStyle = titlestyle;187 188 sheet.SetColumnWidth(3, 15 * 256);189 sheet.GetRow(0).GetCell(3).CellStyle = titlestyle;190 191 sheet.SetColumnWidth(4, 15 * 256);192 sheet.GetRow(0).GetCell(4).CellStyle = titlestyle;193 194 sheet.SetColumnWidth(5, 15 * 256);195 sheet.GetRow(0).GetCell(5).CellStyle = titlestyle;196 197 sheet.SetColumnWidth(6, 8 * 256);198 sheet.GetRow(0).GetCell(6).CellStyle = titlestyle;199 200 var style1 = result_xls.CreateCellStyle();201 style1.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Right;202 203 DataRow t_row = null;204 for (int n = 0; n < res_table.Rows.Count; n++)205 {206 t_row = res_table.Rows[n];207 row = sheet.CreateRow(n + 1);208 for (int p = 0; p < t_row.ItemArray.Length; p++)209 {210 ICell cell = row.CreateCell(p);211 cell.SetCellValue(t_row.ItemArray[p].ToString());212 switch (p)213 {214 case 0:215 cell.CellStyle = style1;216 break;217 case 1:218 cell.CellStyle = titlestyle;219 break;220 case 2:221 cell.CellStyle = style1;222 break;223 case 3:224 cell.CellStyle = titlestyle;225 break;226 case 4:227 cell.CellStyle = style1;228 break;229 case 5:230 cell.CellStyle = style1;231 break;232 case 6:233 cell.CellStyle = titlestyle;234 break;235 }236 }237 }238 result_xls.Write(fs);239 }240 }241 catch 242 {243 errorlogs.Append("发生错误：成绩文件为："+scorepath+"\r\n");244 }245 }246 #endregion 3.数据模型：
1 #region 统计数据模型 2 class student_modele 3 { 4 private string num = string.Empty; //考号 5 private string name = string.Empty; //姓名 6 private string peoplenum = string.Empty; //身份证号 7 private string type = string.Empty; //职业类别 8 private string typenum = string.Empty; //职业证书编号 9 private string senioritynum = string.Empty; //资格证编号10 private float score = 0; //总分11 12 public string Num13 {14 get { return num; }15 set { this.num=value;}16 }17 18 public string Name19 {20 get { return name; }21 set { this.name = value; }22 }23 24 public string PeopleNum25 {26 get { return peoplenum; }27 set { this.peoplenum=value;}28 }29 30 public string Type31 {32 get { return type; }33 set { this.type = value; }34 }35 36 public string TypeNum37 {38 get { return typenum; }39 set { this.typenum = value; }40 }41 42 public string SeniorityNum43 {44 get { return senioritynum; }45 set { this.senioritynum = value; }46 }47 48 public float Score49 {50 get { return score; }51 set { this.score = value; }52 }53 }54 #endregion