1.文件操作
1.1递归目录，查找所有文件
1 #region 递归目录路径 2 private List<string> GetDirs(string path) 3 { 4 string[] dirs = Directory.GetDirectories(path); 5 List<string> result = new List<string>(); 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 #endregion 20 21 //调用 22 List<string> strdirs = GetDirs(path); 23 List<string> strfiles = new List<string>(); 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 List<string> GetFileSet(string par_path) 3 { 4 List<string> result = new List<string>(); 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<string> 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<student_modele> failtable = new List<student_modele>(); //不及格名单 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] + "统计： "; 151 float per = 1 - failcount / res_table.Rows.Count; 152 this.Total_kryptonTextBox.Text += "合格率："+per.ToString("p") + " "; 153 if (failcount > 0) 154 { 155 this.Total_kryptonTextBox.Text += "不合格名单： "; 156 for (int n = 0; n < failtable.Count; n++) 157 { 158 this.Total_kryptonTextBox.Text += failtable[n].Num + " "; 159 } 160 } 161 this.Total_kryptonTextBox.Text += "-------------------------------------------------------------------------- "; 162 //导出Excel 163 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+" "); 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 Num 13 { 14 get { return num; } 15 set { this.num=value;} 16 } 17 18 public string Name 19 { 20 get { return name; } 21 set { this.name = value; } 22 } 23 24 public string PeopleNum 25 { 26 get { return peoplenum; } 27 set {this.peoplenum=value;} 28 } 29 30 public string Type 31 { 32 get { return type; } 33 set { this.type = value; } 34 } 35 36 public string TypeNum 37 { 38 get { return typenum; } 39 set { this.typenum = value; } 40 } 41 42 public string SeniorityNum 43 { 44 get { return senioritynum; } 45 set { this.senioritynum = value; } 46 } 47 48 public float Score 49 { 50 get { return score; } 51 set { this.score = value; } 52 } 53 } 54 #endregion