效果图:
相关引用:
- using System.Data;
- using System.Data.OleDb;
方法源码:
- /// <summary>
- /// 读取EXCEL列数据
- /// </summary>
- /// <param name="excel_path">要读取的Excel路径</param>
- /// <param name="select_field">要读取的列名</param>
- /// <param name="sheet_name">读取sheet页</param>
- /// <param name="range">读取区域</param>
- /// <param name="where_field">条件列名</param>
- /// <param name="where_condition">条件值列表</param>
- /// <returns></returns>
- public static DataTable LoadDataFromExcel(string excel_path, string select_field, string sheet_name, string range,
- string where_field, string[] where_condition)
- {
- //定义并根据Excel版本确定链接字符串
- var file = new System.IO.FileInfo(excel_path);
- var strConn = string.Empty;
- switch (file.Extension)
- {
- case ".xls":
- strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + excel_path + ";Extended Properties='Excel 8.0;HDR=Yes;IMEX=1;'";
- break;
- case ".xlsx":
- strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + excel_path + ";Extended Properties='Excel 12.0;HDR=Yes;IMEX=1;'";
- break;
- default:
- strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + excel_path + ";Extended Properties='Excel 12.0;HDR=Yes;IMEX=1;'";
- break;
- }
- OleDbConnection OleConn = new OleDbConnection(strConn);
- OleConn.Open();
- //确定要读取的列名
- var strSelectCol = string.IsNullOrWhiteSpace(select_field) ? "*" : select_field.Trim();
- //拼接query语句
- var sql = "SELECT " + strSelectCol + " FROM [" + sheet_name +"$"+ range + "]";
- for (int i = 0; i < where_condition.Length; i++)
- {
- sql += (i == 0 ? " WHERE " : " or ") + where_field + " like '%" + where_condition[i] + "%'";
- }
- //读取数据
- OleDbDataAdapter Oleda = new OleDbDataAdapter(sql, OleConn);
- DataSet Oleds = new DataSet();
- Oleda.Fill(Oleds, sheet_name);
- OleConn.Close();
- return Oleds.Tables[0];
- }
来源: