//1 首先引入npoi操作的程序集
引用 NPOI.dll Ionic.Zip.dll
using NPOI.SS.UserModel; //包含对excel进行操作的方法
using NPOI.HSSF.UserModel; //包含excel每个sheet的属性
（1）Excel读取数据、Excel写入数据
（2）项目导入、项目导出
1 /// <summary> 2 /// 从Excel读 3 /// </summary> 4 /// <param name="sender"></param> 5 /// <param name="e"></param> 6 private void btnReadFromExcel_Click(object sender, EventArgs e) 7 { 8 //从流stream中读取 9 using(Stream stream=new FileStream("/Files/myexcel.xls",FileMode.Open,FileAccess.Read)) 10 { 11 //读取workbook 12 IWorkbook workbook=new HSSFWorkbook(stream); 13 ISheet sheet = workbook.GetSheetAt(0); 14 for (int i = 0; i < sheet.LastRowNum;i++ ) 15 { 16 IRow row = sheet.GetRow(i); 17 foreach(ICell cell in row.Cells) 18 { 19 string c = cell.StringCellValue; 20 MessageBox.Show(c); 21 } 22 } 23 } 24 MessageBox.Show("ok,读取成功"); 25 } 26 27 /// <summary> 28 /// 写入Excel 29 /// </summary> 30 /// <param name="sender"></param> 31 /// <param name="e"></param> 32 private void btnWriteToExcel_Click(object sender, EventArgs e) 33 { 34 //初始化workbook 35 IWorkbook workbook = new HSSFWorkbook(); 36 //创建sheet 37 ISheet sheet = workbook.CreateSheet("班级表"); 38 //创建row 39 IRow row = sheet.CreateRow(0); 40 //创建cell 41 ICell cell = row.CreateCell(0); 42 cell.SetCellType(CellType.STRING); 43 cell.SetCellValue("hello"); 44 //写入流 45 using(Stream stream=new FileStream("/Files/myexcel2.xls",FileMode.OpenOrCreate,FileAccess.Write)) 46 { 47 workbook.Write(stream); 48 } 49 MessageBox.Show("ok,写入成功"); 50 } 51 52 /// <summary> 53 /// 导出到Excel 54 /// </summary> 55 /// <param name="sender"></param> 56 /// <param name="e"></param> 57 private void btnExportToExcel_Click(object sender, EventArgs e) 58 { 59 //获得数据表 60 List<Object> list = myORM_BLL.SelectAllModel(typeof(T_CUSTOMER)); 61 //初始化workbook 62 IWorkbook workbook = new HSSFWorkbook(); 63 //创建sheet 64 ISheet sheet = workbook.CreateSheet("T_CUSTOMER"); 65 //遍历数据表 名称用反射获得,数据行直接获得 66 //创建头行headrow 67 IRow headrow = sheet.CreateRow(0); 68 Type type = typeof(T_CUSTOMER); 69 PropertyInfo[] props = type.GetProperties(); 70 for(int i=0;i<props.Length; i++) 71 { 72 string propName = props[i].Name; 73 ICell cell = headrow.CreateCell(i); 74 cell.SetCellType(CellType.STRING); 75 cell.SetCellValue(propName); 76 } 77 //创建数据行 78 //遍历集合 每个对象创建一个行 79 for (int j = 0; j < list.Count;j++ ) 80 { 81 IRow row = sheet.CreateRow(j + 1); 82 Object obj = list[j]; 83 Type tp = obj.GetType(); 84 PropertyInfo[] props2 = tp.GetProperties(); 85 //对象的每个属性 创建一个Cell 86 for (int k = 0; k < props2.Length;k++ ) 87 { 88 string propName2 = props2[k].Name; 89 object propValue2 = props2[k].GetValue(obj); 90 //对于每行创建cell，设置值 91 ICell cell = row.CreateCell(k); 92 cell.SetCellType(CellType.STRING); 93 cell.SetCellValue(propValue2.ToString()); 94 } 95 } 96 //写入stream 97 using(Stream stream=new FileStream("/Files/exportdata.xls",FileMode.OpenOrCreate,FileAccess.Write)) 98 { 99 workbook.Write(stream); 100 } 101 MessageBox.Show("导出成功"); 102 } 103 104 /// <summary> 105 /// 把C#中类型转Excel中类型 106 /// </summary> 107 /// <param name="cTyName">C#中类型 的名称</param> 108 /// <returns>Excel中类型</returns> 109 private CellType CelltypeToCType(string cTyName) 110 { 111 switch (cTyName) 112 { 113 case "Int16": 114 case "Int64": 115 case "Int32": return CellType.NUMERIC; 116 case "String": return CellType.STRING; 117 case "Data": 118 case "DateTime": return CellType.FORMULA; 119 default: throw new Exception("未知类型：" + cTyName); 120 } 121 } 122 123 124 /// <summary> 125 /// 从DB直接导出到Excel 126 /// </summary> 127 /// <param name="sender"></param> 128 /// <param name="e"></param> 129 private void btnExportToExcelFromDB_Click(object sender, EventArgs e) 130 { 131 //初始化workbook，创建sheet 132 IWorkbook workbook = new HSSFWorkbook(); 133 ISheet sheet = workbook.CreateSheet("T_CUSTOMER"); 134 //打开conn ,发出cmd，reader查询 135 string sql = "SELECT * FROM T_CUSTOMER"; 136 using(OracleConnection conn=OracleHelper.CreateConnection()) 137 using (OracleCommand cmd = new OracleCommand(sql, conn)) 138 using (OracleDataReader reader = cmd.ExecuteReader()) 139 { 140 //根据查询字段数fieldcount创建headrow 及遍历字段创建cell 141 IRow headrow = sheet.CreateRow(0); 142 for (int i = 0; i < reader.FieldCount; i++) 143 { 144 ICell cell = headrow.CreateCell(i); 145 cell.SetCellType(CellType.STRING); 146 cell.SetCellValue(reader.GetName(i)); 147 } 148 //循环reader查询，每一条查询，创建row 及遍历字段创建cell 149 int datarowIndex = 1; //数据行索引，从1开始 150 while(reader.Read()) 151 { 152 IRow row = sheet.CreateRow(datarowIndex); 153 for (int i = 0; i < reader.FieldCount;i++ ) 154 { 155 ICell cell = row.CreateCell(i); 156 cell.SetCellType(CellType.STRING); 157 cell.SetCellValue(reader.GetValue(i).ToString()); 158 } 159 datarowIndex++; 160 } 161 } 162 //关闭连接 163 //写入stream 164 //写入stream 165 using (Stream stream = new FileStream("/Files/exportdataFromDB.xls", FileMode.OpenOrCreate, FileAccess.Write)) 166 { 167 workbook.Write(stream); 168 } 169 MessageBox.Show("导出成功"); 170 } 171 172 /// <summary> 173 /// 从Excel导入 OracleBulkCopy大数据导入 174 /// </summary> 175 /// <param name="sender"></param> 176 /// <param name="e"></param> 177 private void btnImportFromExcel_Click(object sender, EventArgs e) 178 { 179 //创建datatable 180 DataTable dt = new DataTable(); 181 Type type = typeof(T_CUSTOMER); 182 PropertyInfo[] props = type.GetProperties(); 183 DataColumn[] dcArr = new DataColumn[props.Length]; 184 int j=0; 185 foreach(PropertyInfo prop in props) 186 { 187 string propName = prop.Name; 188 Type propTy = prop.PropertyType; //属性的类型 189 DataColumn dc = new DataColumn(); 190 dc.ColumnName = propName; 191 dcArr[j] = dc; //把表的列 存入数组 192 dc.DataType = propTy; //列中数据的类型 193 dt.Columns.Add(dc); 194 j++; 195 } 196 //读取Excel文件,获得stream 197 using (Stream stream = new FileStream("/Files/exportdata.xls", FileMode.Open, FileAccess.Read)) 198 { 199 //获得workbook 200 IWorkbook workbook = new HSSFWorkbook(stream); 201 //读取sheet 202 ISheet sheet = workbook.GetSheetAt(0); 203 //读取row 及行中的cell 放入一个datatable 204 for (int i = 1; i < sheet.LastRowNum;i++ ) 205 { 206 IRow row = sheet.GetRow(i); 207 DataRow dr = dt.NewRow(); 208 foreach(ICell cell in row.Cells) 209 { 210 if (dcArr[i-1].DataType.Name=="Int32") 211 { 212 dr[dcArr[i - 1]] = (Int32)cell.NumericCellValue; //还可能需 Convert.ToInt32 213 } 214 else if (dcArr[i - 1].DataType.Name == "String") 215 { 216 dr[dcArr[i - 1]] = cell.StringCellValue; 217 } 218 else if (dcArr[i - 1].DataType.Name == "DateTime?") 219 { 220 dr[dcArr[i - 1]] = (DateTime?)cell.DateCellValue; 221 } 222 else 223 { 224 throw new Exception("未知类型:" + cell.CellType); 225 } 226 } 227 dt.Rows.Add(dr); //把表的行加入表的行集合中,最终获得表 228 } 229 } 230 //OracleBulk 231 using (OracleBulkCopy bulkCopy = new OracleBulkCopy(OracleHelper.CreateConnection())) 232 { 233 bulkCopy.DestinationTableName = "T_CUSTOMER"; 234 foreach (DataColumn dc in dcArr) 235 { 236 string columnName = dc.ColumnName; 237 bulkCopy.ColumnMappings.Add(columnName, columnName); 238 } 239 bulkCopy.WriteToServer(dt); 240 } 241 //把数据表插入DB 242 } 243 244 245 /// <summary> 246 /// 从Execel直接导入DB 247 /// </summary> 248 /// <param name="sender"></param> 249 /// <param name="e"></param> 250 private void btnImportToDBFromExcel_Click(object sender, EventArgs e) 251 { 252 //读取excel 253 using (Stream stream = new FileStream("/Files/exportdataFromDB.xls", FileMode.Open, FileAccess.Read)) 254 { 255 IWorkbook workbook = new HSSFWorkbook(stream); 256 //获得sheet 257 ISheet sheet = workbook.GetSheetAt(0); 258 259 //行headrow 去除首行 获得含有':'列名数组 260 IRow headrow = sheet.GetRow(0); 261 string[] columnNameArr = new string[headrow.LastCellNum-1]; 262 for (int i = 1; i < headrow.LastCellNum;i++ ) 263 { 264 columnNameArr[i - 1] = ":" + headrow.Cells[i].StringCellValue; 265 } 266 StringBuilder sb=new StringBuilder(); 267 sb.Append("INSERT INTO T_CUSTOMER VALUES(SE_T_CUSTOMER.NEXTVAL,").Append(string.Join(",",columnNameArr)).AppendLine(")"); 268 //遍历行row 去除首行 获得参数数组 269 for (int j = 1; j < sheet.LastRowNum;j++ ) 270 { 271 IRow row = sheet.GetRow(j); 272 //除0行 的每一行都有一个参数数组，里面有各个cell值的参数 273 //声明一个参数数组，并对其中的灭一个参数赋值 274 OracleParameter[] paraArr = new OracleParameter[row.LastCellNum - 1]; { for (int i = 0; i < paraArr.Length;i++ ) { paraArr[i] = new OracleParameter(); } } 275 for (int k = 1; k < row.LastCellNum; k++) 276 { 277 ICell cell = row.Cells[k]; 278 paraArr[k - 1].ParameterName = columnNameArr[k - 1]; 279 paraArr[k - 1].Value = (object)cell.StringCellValue; //判断 不需要 280 } 281 //除0每一行 都执 /插入DB 282 OracleHelper.ExecuteNonQuery(sb.ToString(), paraArr); 283 } 284 } 285 }
