有时候当我们的系统需要导入大量数据时可以以Excel表为媒介,或者用户有些老数据存储在Excel表中时,我们就需要有导入Excel的功能,要实现它,除了利用MS Office提供的Api外,微软还提供了一种更方便的方式,OleDbConnection,它类试SQLConnection用来连接SqlServer数据库,不过它的数据库类型,可以是Access、Oracle、Excel表,利用它,我们可以利用Sql 语句读取表格数据,非常方便,它的操作步骤,与用SQLConnection一样:
1.创建OleDbConnetion对象,并打开连接:
string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Extended Properties='Excel 8.0;HDR=False;IMEX=1'";//oledb连接字符串,Provider后表示OLE DB 提供程序的名称,即数据库操作引擎,这里的4.0版本智能读取excel2003版本的,要想读取excel2007版本,则可以到微软官网上下,最新的引擎,将4.0改为12.0,Data Source表示Excel表文件位置
OleDbConnection OleConn = new OleDbConnection(strConn);//用连接字符串实例化OleDbConnection对象
OleConn.Open();//打开OleDb连接
2.设置查询语句,执行查询:
string tableName = "[" + tbName + "$]";//设置表名,必须用[ $]括起来,否则会出错
String sql = string.Format("SELECT * FROM {0}", tableName);//可是更改Sheet名称,比如sheet2,等等
OleDbDataAdapter OleDaExcel = new OleDbDataAdapter(sql, OleConn);//以适配器的方式查询获取整个表格
DataSet OleDsExcle = new DataSet();//新建DataSet数据集对象
OleDaExcel.Fill(OleDsExcle, tbName);//用excel数据填充DataSet数据集
OleConn.Close();//关闭数据表
完整代码如下:
1 /// <summary> 2 /// 读取Excel表 3 /// </summary> 4 /// <param name="filePath"></param>文件路径 5 /// <param name="tbName"></param>表名称,如果不知道则默认为sheet1 6 /// <returns></returns>返回转化的DataTable类型 7 public static DataSet LoadDataFromExcel(string filePath,String tbName="sheet1") 8 { 9 try 10 { 11 string strConn; 12 strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Extended Properties='Excel 8.0;HDR=False;IMEX=1'"; 13 //oledb连接字符串 14 OleDbConnection OleConn = new OleDbConnection(strConn); 15 //新建OleDb连接对象 16 OleConn.Open();//打开OleDb连接 17 string tableName = "[" + tbName + "$]";//设置表名,必须用[ $]括起来,否则会出错 18 String sql = string.Format("SELECT * FROM {0}", tableName);//可是更改Sheet名称,比如sheet2,等等 19 OleDbDataAdapter OleDaExcel = new OleDbDataAdapter(sql, OleConn); 20 //以适配器的方式查询获取整个表格 21 DataSet OleDsExcle = new DataSet();//新建DataSet数据集对象 22 OleDaExcel.Fill(OleDsExcle, tbName);//用excel数据填充DataSet数据集 23 OleConn.Close();//关闭数据表 24 return OleDsExcle; 25 } 26 catch (Exception err) 27 { 28 MessageBox.Show("数据绑定Excel失败!失败原因:" + err.Message, "提示信息", MessageBoxButtons.OK, MessageBoxIcon.Information); 29 return null; 30 } 31 } 32 /// <summary> 33 /// 将DataTable数据转化为Excel表存储起来 34 /// </summary> 35 /// <param name="excelTable"></param>DataTable数据 36 /// <param name="filePath"></param>Excel文件存储位置 37 /// <returns></returns> 38 public static bool SaveDataTableToExcel(System.Data.DataTable excelTable, string filePath) 39 { 40 Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.ApplicationClass(); 41 try 42 { 43 app.Visible = false; 44 Workbook wBook = app.Workbooks.Add(true); 45 Worksheet wSheet = wBook.Worksheets[1] as Worksheet; 46 if (excelTable.Rows.Count > 0) 47 { 48 int row = 0; 49 row = excelTable.Rows.Count; 50 int col = excelTable.Columns.Count; 51 for (int i = 0; i < row; i++) 52 { 53 for (int j = 0; j < col; j++) 54 { 55 string str = excelTable.Rows[i][j].ToString(); 56 wSheet.Cells[i + 2, j + 1] = str; 57 } 58 } 59 } 60 int size = excelTable.Columns.Count; 61 for (int i = 0; i < size; i++) 62 { 63 wSheet.Cells[1, 1 + i] = excelTable.Columns[i].ColumnName; 64 } 65 //设置禁止弹出保存和覆盖的询问提示框 66 app.DisplayAlerts = false; 67 app.AlertBeforeOverwriting = false; 68 //保存工作簿 69 wBook.Save(); 70 //保存excel文件 71 app.Save(filePath); 72 app.SaveWorkspace(filePath); 73 app.Quit(); 74 app = null; 75 return true; 76 } 77 catch (Exception err) 78 { 79 MessageBox.Show("导出Excel出错!错误原因:" + err.Message, "提示信息", 80 MessageBoxButtons.OK, MessageBoxIcon.Information); 81 return false; 82 } 83 finally 84 { 85 } 86 } 87 }
效果如下: