Excel导入 支持2003和2007
excel导入到数据库，1.上传excel，2.读取excel数据，3.写数据到数据库（批量导入推荐使用SqlBulkCopy，或OracleBulkCopy）
1 using System; 2 using System.Collections.Generic; 3 using System.Data; 4 using System.IO; 5 using System.Linq; 6 using System.Text.RegularExpressions; 7 using System.Web; 8 using NPOI.HSSF.UserModel; 9 using NPOI.SS.UserModel; 10 using NPOI.XSSF.UserModel; 11 12 //支持excel 2007 或2003 导入 13 14 namespace FrameWork.Helper 15 { 16 public partial class ExcelHelper 17 { 18 /// <summary> 19 /// 上传文件到服务器并返回文件路径 20 /// </summary> 21 /// <param name="file">上载的文件对象HttpPostedFile</param> 22 /// <param name="savePath">相对存放路径【例："uploads/excel/"】</param> 23 /// <param name="maxContentLength">上载文件的最大允许大小（以字节为单位）</param> 24 /// <returns>返回上传成功的excel的路径</returns> 25 public static string SaveExcelFile(HttpPostedFileBase file, string savePath, int maxContentLength = 1024*1024*10) 26 { 27 if (file == null || file.ContentLength <= 0) 28 { 29 throw new Exception("文件不能为空"); 30 } 31 var filesize = file.ContentLength; //获取上传文件的大小单位为字节byte 32 if (filesize > maxContentLength) 33 { 34 throw new Exception("文件超过最大允许"); 35 } 36 var filename = Path.GetFileName(file.FileName); 37 if (!(IsExcel2003(filename) || IsExcel2007(filename))) 38 { 39 throw new Exception("文件格式不正确"); 40 } 41 var fileEx = Path.GetExtension(filename); //获取上传文件的扩展名 42 var noExFileName = Path.GetFileNameWithoutExtension(filename); //获取无扩展名的文件名 43 44 var newfileName = noExFileName + DateTime.Now.ToString("yyyyMMddhhmmss") + fileEx; 45 var path = AppDomain.CurrentDomain.BaseDirectory + savePath; 46 if (!Directory.Exists(path)) Directory.CreateDirectory(path); 47 savePath = Path.Combine(path, newfileName); 48 file.SaveAs(savePath); 49 return savePath; 50 } 51 52 53 /// <summary> 54 /// 读取excel到DataTable 55 /// 默认第一行为表头 56 /// </summary> 57 /// <param name="path">excel文档的绝对访问路径【例：C:\abc.xlsx】</param> 58 /// <param name="defaultColumn">默认字段和默认值</param> 59 /// <param name="transColumn"> 60 /// 需要转换的列头字典例：transColumn=new Dictionary<string, string />(){{"姓名","name"}}; 61 /// 程序读取到excel 第一行的列头姓名，就自动转换成name ，name 才是数据表中的列头 62 /// </param> 63 /// <returns></returns> 64 public static DataTable RenderDataTableFromExcel(string path, Dictionary<string, object> defaultColumn = null, 65 Dictionary<string, string> transColumn = null) 66 { 67 string msg; 68 if (!ValidateExcel(path, out msg)) 69 { 70 throw new Exception(msg); 71 } 72 var dt = new DataTable(); 73 if (IsExcel2003(path)) 74 { 75 ReadFileData2003(path, dt, defaultColumn, transColumn); 76 } 77 else 78 { 79 ReadFileData2007(path, dt, defaultColumn, transColumn); 80 } 81 return dt; 82 } 83 84 /// <summary> 85 /// 2003 86 /// </summary> 87 /// <param name="path"></param> 88 /// <param name="dt"></param> 89 /// <param name="defaultColumn">默认字段和默认值</param> 90 /// <param name="transColumn"> 91 /// 需要转换的列头字典例：transColumn=new Dictionary<string, string />(){{"姓名","name"}}; 92 /// 程序读取到excel 第一行的列头姓名，就自动转换成name ，name 才是数据表中的列头 93 /// </param> 94 private static void ReadFileData2003(string path, DataTable dt, Dictionary<string, object> defaultColumn = null, 95 Dictionary<string, string> transColumn = null) 96 { 97 HSSFWorkbook xssFworkbook; 98 using (var file = new FileStream(path, FileMode.Open, FileAccess.Read)) 99 { 100 xssFworkbook = new HSSFWorkbook(file); 101 } 102 var sheet = (HSSFSheet)xssFworkbook.GetSheetAt(0); 103 var rows = sheet.GetRowEnumerator(); 104 105 var headerRow = (HSSFRow)sheet.GetRow(0); 106 int cellCount = headerRow.LastCellNum; 107 108 for (var j = 0; j < cellCount; j++) 109 { 110 var cell = (HSSFCell)headerRow.GetCell(j); 111 var column = cell.ToString(); 112 113 if (transColumn != null && transColumn.ContainsKey(column)) 114 { 115 column = transColumn[column]; 116 } 117 dt.Columns.Add(column); 118 } 119 //加入默认列和值 120 if (defaultColumn != null && defaultColumn.Any()) 121 { 122 foreach (var key in defaultColumn.Keys) 123 { 124 dt.Columns.Add(key); 125 } 126 } 127 128 for (var i = (sheet.FirstRowNum + 1); i <= sheet.LastRowNum; i++) 129 { 130 var row = (HSSFRow)sheet.GetRow(i); 131 var dataRow = dt.NewRow(); 132 133 for (int j = row.FirstCellNum; j < cellCount; j++) 134 { 135 if (row.GetCell(j) != null) 136 //dataRow[j] = row.GetCell(j).ToString(); 137 dataRow[j] = GetICellValue(row.GetCell(j)); 138 } 139 //加入默认列和值 140 if (defaultColumn != null && defaultColumn.Any()) 141 { 142 for (var j = 0; j < defaultColumn.Keys.Count; j++) 143 { 144 var key = defaultColumn.Keys.ElementAt(j); 145 dataRow[cellCount + j] = defaultColumn[key]; 146 } 147 } 148 dt.Rows.Add(dataRow); 149 } 150 } 151 152 /// <summary> 153 /// 转换不同格式的单元格数据 154 /// </summary> 155 /// <param name="cell"></param> 156 /// <returns></returns> 157 private static object GetICellValue(ICell cell) 158 { 159 object value = null; 160 switch (cell.CellType) 161 { 162 case CellType.Blank: 163 break; 164 case CellType.Boolean: 165 value = cell.BooleanCellValue ? "1" : "0"; 166 break; 167 case CellType.Error: 168 value = cell.ErrorCellValue; 169 break; 170 case CellType.Formula: 171 //alue = "=" + cell.CellFormula; break; 172 // HSSFFormulaEvaluator e = new HSSFFormulaEvaluator(hssfworkbook); 173 // dr[iRow] = e.Evaluate(cell).StringValue; 174 try 175 { 176 value = cell.StringCellValue; 177 } 178 catch 179 { 180 try 181 { 182 value = cell.NumericCellValue; 183 } 184 catch 185 { 186 ; 187 } 188 } 189 break; 190 case CellType.Numeric: 191 //NPOI中数字和日期都是NUMERIC类型的，这里对其进行判断是否是日期类型 192 if (DateUtil.IsCellDateFormatted(cell)) //日期类型 193 { 194 value = cell.DateCellValue; 195 } 196 else //其他数字类型 197 { 198 value = cell.NumericCellValue; 199 } 200 201 break; 202 case CellType.String: 203 value = cell.StringCellValue; 204 break; 205 case CellType.Unknown: 206 break; 207 } 208 return value; 209 } 210 211 /// <summary> 212 /// 2007 213 /// </summary> 214 /// <param name="path"></param> 215 /// <param name="dt"></param> 216 /// <param name="defaultColumn">默认字段和默认值</param> 217 /// <param name="transColumn"> 218 /// 需要转换的列头字典例：transColumn=new Dictionary<string, string />(){{"姓名","name"}}; 219 /// 程序读取到excel 第一行的列头姓名，就自动转换成name ，name 才是数据表中的列头 220 /// 因为导入操作者的模板列头是中文的，不和数据表头一一对应 221 /// </param> 222 private static void ReadFileData2007(string path, DataTable dt, Dictionary<string, object> defaultColumn = null, 223 Dictionary<string, string> transColumn = null) 224 { 225 XSSFWorkbook xssFworkbook; 226 using (var file = new FileStream(path, FileMode.Open, FileAccess.Read)) 227 { 228 xssFworkbook = new XSSFWorkbook(file); 229 } 230 var sheet = (XSSFSheet)xssFworkbook.GetSheetAt(0); 231 var rows = sheet.GetRowEnumerator(); 232 233 var headerRow = (XSSFRow)sheet.GetRow(0); 234 int cellCount = headerRow.LastCellNum; 235 236 for (var j = 0; j < cellCount; j++) 237 { 238 var cell = (XSSFCell)headerRow.GetCell(j); 239 var column = cell.ToString(); 240 //如果上传模板列头和表列头不对应，需要转换列头 241 if (transColumn != null && transColumn.ContainsKey(column)) 242 { 243 column = transColumn[column]; 244 } 245 dt.Columns.Add(column); 246 } 247 //加入默认列和值 248 if (defaultColumn != null && defaultColumn.Any()) 249 { 250 foreach (var key in defaultColumn.Keys) 251 { 252 dt.Columns.Add(key); 253 } 254 } 255 256 for (var i = (sheet.FirstRowNum + 1); i <= sheet.LastRowNum; i++) 257 { 258 var row = (XSSFRow)sheet.GetRow(i); 259 var dataRow = dt.NewRow(); 260 261 for (int j = row.FirstCellNum; j < cellCount; j++) 262 { 263 if (row.GetCell(j) != null) 264 //dataRow[j] = row.GetCell(j).ToString(); 265 dataRow[j] = GetICellValue(row.GetCell(j)); 266 } 267 268 //加入默认列和值 269 if (defaultColumn != null && defaultColumn.Any()) 270 { 271 for (var j = 0; j < defaultColumn.Keys.Count; j++) 272 { 273 var key = defaultColumn.Keys.ElementAt(j); 274 dataRow[cellCount + j] = defaultColumn[key]; 275 } 276 } 277 dt.Rows.Add(dataRow); 278 } 279 } 280 281 /// <summary> 282 /// 验证是否是excel 283 /// </summary> 284 /// <param name="filePath">文件路径</param> 285 /// <param name="errorMess">异常信息</param> 286 /// <returns></returns> 287 public static bool ValidateExcel(string filePath, out string errorMess) 288 { 289 /** 检查文件名是否为空或者是否是Excel格式的文件 */ 290 if (filePath == null || !(IsExcel2003(filePath) || IsExcel2007(filePath))) 291 { 292 errorMess = "文件名不是excel格式"; 293 return false; 294 } 295 296 /** 检查文件是否存在 */ 297 var file = new FileInfo(filePath); 298 if (file.Exists) 299 { 300 errorMess = null; 301 return true; 302 } 303 errorMess = "文件不存在"; 304 return false; 305 } 306 307 /// <summary> 308 /// 是否Excel2003 309 /// </summary> 310 /// <param name="filePath"></param> 311 /// <returns></returns> 312 private static bool IsExcel2003(string filePath) 313 { 314 return IsMatch("^.+\\.(?i)(xls)$", filePath); 315 } 316 317 /// <summary> 318 /// 是否Excel2007 319 /// </summary> 320 /// <param name="filePath"></param> 321 /// <returns></returns> 322 private static bool IsExcel2007(string filePath) 323 { 324 return IsMatch("^.+\\.(?i)(xlsx)$", filePath); 325 } 326 327 /// <summary> 328 /// 正则匹配 329 /// </summary> 330 /// <param name="pattern"></param> 331 /// <param name="input"></param> 332 /// <returns></returns> 333 private static bool IsMatch(string pattern, string input) 334 { 335 var regex = new Regex(pattern); 336 return regex.IsMatch(input); 337 } 338 339 } 340 }
