导入Excel文件
选择文件
1 #region 2 //获取路径 3 string FilePath = ""; 4 //选择文件 5 OpenFileDialog openFileDialog1 = new OpenFileDialog(); 6 //获取本地启动路径 7 string str = System.Windows.Forms.Application.StartupPath; 8 openFileDialog1.InitialDirectory = str + "\\"; 9 openFileDialog1.Filter = "所有文件(*.*)|*.*"; 10 openFileDialog1.RestoreDirectory = false; 11 if (openFileDialog1.ShowDialog() == DialogResult.OK) 12 FilePath = openFileDialog1.FileName; 13 //获得文件的扩展名 14 string IsXls = System.IO.Path.GetExtension(FilePath).ToString().ToLower(); 15 if (!FilePath.Equals("")) 16 { 17 if (IsXls == ".xlsx" || IsXls == ".xls") 18 { 19 //调用方法 20 方法名(FilePath); 21 } 22 else 23 { 24 MessageBox.Show("只可以选择Excel格式文件,请重新选择文件!"); 25 return; 26 } 27 } 28 #endregion
读取excel到datatable
1 #region 读取excel到datatable 2 public static DataTable ExcelToDataTable(string FilePath) 3 { 4 DataTable dtExcel = new DataTable(); 5 try 6 { 7 if (RCExportExcel.isOfficeInstall("11.0") || RCExportExcel.isOfficeInstall("12.0") 8 || RCExportExcel.isOfficeInstall("14.0") || RCExportExcel.isOfficeInstall("15.0")) 9 { 10 #region 安装Office 11 dtExcel = OfficeExcel(FilePath); 12 #endregion 13 } 14 else 15 { 16 #region 未安装Office 17 dtExcel = NpoiExcel(FilePath); 18 #endregion 19 } 20 } 21 catch (Exception ex) 22 { 23 WriteLog(ex.Message + "，错误行号：" + ex.StackTrace); 24 MessageBox.Show("Excel文件被占用，请关闭Excel文件再进行导入操作!"); 25 } 26 return dtExcel; 27 } 28 #endregion
Office读取Excel
1 #region Office读取Excel 2 /// <param name="excelPath">excel地址</param> 3 /// <returns>成功返回datatable，失败返回null</returns> 4 public static DataTable OfficeExcel(string excelPath) 5 { 6 DataTable dtexcel = new DataTable(); 7 string strConn; 8 try 9 { 10 FileInfo fileInfo = new FileInfo(excelPath);//判断文件是否存在 11 if (fileInfo.Exists) 12 { 13 strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + excelPath + ";Extended Properties='Excel 14 12.0;HDR=Yes;IMEX=1;'"; 15 OleDbConnection conn = new OleDbConnection(strConn); 16 conn.Open(); 17 DataTable tbNames = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null); //得到所有工作簿名称 18 string tbName = tbNames.Rows[0]["TABLE_NAME"].ToString(); 19 OleDbDataAdapter oda = new OleDbDataAdapter("select * from [" + tbName + "]", conn); 20 DataSet ds = new DataSet(); //必须要使用DataSet对象 21 oda.Fill(ds, "tempExport"); 22 dtexcel = ds.Tables[0]; 23 conn.Close(); 24 } 25 } 26 catch (Exception e) 27 { 28 WriteLog(e.Message + "，错误行号：" + e.StackTrace); 29 MessageBox.Show("Excel文件被占用，请关闭Excel文件再进行导入操作!"); 30 return dtexcel; 31 } 32 finally 33 { 34 //释放资源 35 if (dtexcel != null) { dtexcel.Dispose(); } 36 } 37 return dtexcel; 38 } 39 #endregion
NPOI 读取Excel
1 #region NPOI 读取Excel 2 /// <param name="excelPath">excel地址</param> 3 /// <returns>成功返回datatable，失败返回null</returns> 4 public static DataTable NpoiExcel(string excelPath) 5 { 6 IWorkbook workbook = null;//全局workbook 7 ISheet sheet;//sheet 8 DataTable table = null; 9 try 10 { 11 FileInfo fileInfo = new FileInfo(excelPath);//判断文件是否存在 12 if (fileInfo.Exists) 13 { 14 FileStream fileStream = fileInfo.OpenRead();//打开文件，得到文件流 15 switch (fileInfo.Extension) 16 { 17 //xls是03，用HSSFWorkbook打开，.xlsx是07或者10用XSSFWorkbook打开 18 case ".xls": 19 workbook = new HSSFWorkbook(fileStream); break; 20 case ".xlsx": 21 workbook = new XSSFWorkbook(fileStream); break; 22 default: break; 23 } 24 fileStream.Close();//关闭文件流 25 } 26 if (workbook != null) 27 { 28 sheet = workbook.GetSheetAt(0);//读取到指定的sheet 29 table = new DataTable();//初始化一个table 30 31 IRow headerRow = sheet.GetRow(0);//获取第一行，一般为表头 32 int cellCount = headerRow.LastCellNum;//得到列数 33 34 for (int i = headerRow.FirstCellNum; i < cellCount; i++) 35 { 36 DataColumn column = new DataColumn(headerRow.GetCell(i).StringCellValue);//初始化table的列 37 table.Columns.Add(column); 38 } 39 //遍历读取cell 40 for (int i = (sheet.FirstRowNum + 1); i <= sheet.LastRowNum; i++) 41 { 42 NPOI.SS.UserModel.IRow row = sheet.GetRow(i);//得到一行 43 DataRow dataRow = table.NewRow();//新建一个行 44 45 for (int j = row.FirstCellNum; j < cellCount; j++) 46 { 47 ICell cell = row.GetCell(j);//得到cell 48 if (cell == null)//如果cell为null，则赋值为空 49 { 50 dataRow[j] = ""; 51 } 52 else 53 { 54 dataRow[j] = row.GetCell(j).ToString();//否则赋值 55 } 56 } 57 58 table.Rows.Add(dataRow);//把行 加入到table中 59 60 } 61 } 62 return table; 63 64 } 65 catch (Exception e) 66 { 67 WriteLog(e.Message + "，错误行号：" + e.StackTrace); 68 MessageBox.Show("Excel文件被占用，请关闭Excel文件再进行导入操作!"); 69 return table; 70 } 71 finally 72 { 73 //释放资源 74 if (table != null) { table.Dispose(); } 75 workbook = null; 76 sheet = null; 77 } 78 } 79 #endregion
验证身份证号，得到出生日期，性别
1 #region 验证身份证号，得到出生日期，性别 2 public static void AuthenticationIdcard(string idcard) 3 { 4 try 5 { 6 DateTime birthday = new DateTime(); 7 string sex = string.Empty; 8 bool id = true; 9 if (!Regex.IsMatch(idcard, @"^(^\d{15}$|^\d{18}$|^\d{17}(\d|X|x))$", RegexOptions.IgnoreCase)) 10 { 11 id = false; 12 } 13 if (id) 14 { 15 if (idcard.Length == 18) 16 { 17 birthday = DateTime.Parse(idcard.Substring(6, 8).Insert(6, "-").Insert(4, "-")); 18 sex = idcard.Substring(14, 3); 19 if (int.Parse(sex) % 2 == 0)//性别代码为偶数是女性奇数为男性 20 sex = "女"; 21 else 22 sex = "男"; 23 } 24 else if (idcard.Length == 15) 25 { 26 string a = "19"; 27 birthday = DateTime.Parse(a + idcard.Substring(6, 6).Insert(4, "-").Insert(2, "-")); 28 string tmp = idcard.Substring(idcard.Length - 3); 29 int sx = int.Parse(tmp); 30 int outNum; 31 Math.DivRem(sx, 2, out outNum); 32 if (outNum == 0) 33 sex = "女"; 34 else 35 sex = "男"; 36 DataRow row = dtRightIdcard.NewRow(); 37 row["身份证号"] = idcard; 38 row["出生日期"] = birthday; 39 row["性别"] = sex; 40 row["nationid"] = "01"; 41 dtRightIdcard.Rows.Add(row); 42 } 43 else if (idcard.Length == 12) 44 { 45 birthday = DateTime.Parse("1950-01-01"); 46 sex = ""; 47 DataRow row = dtRightIdcard.NewRow(); 48 row["身份证号"] = idcard; 49 row["出生日期"] = birthday; 50 row["性别"] = sex; 51 row["nationid"] = "01"; 52 dtRightIdcard.Rows.Add(row); 53 } 54 } 55 catch 56 { 57 58 throw; 59 } 60 } 61 #endregion
