导入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

View Code

读取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

View Code

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

View Code

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

View Code

验证身份证号，得到出生日期，性别

 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

View Code

转载请注明出入来源：https://www.cnblogs.com/famhuai/p/8257279.html