Excel 表如何导入数据库？
0
最佳答案
0
在WInform里面使用NPOI组件实现的，你可以参考一下：
private void btnLeadExcel_Click(object sender, EventArgs e) { //实现导入数据库中的Excel,读取数据 using (FileStream fileRead= File.OpenRead("客户资料new.xls")) { //创建一个Workbook的对象 using (Workbook wk = new HSSFWorkbook(fileRead)) { //获取第一个Sheet using (Sheet sheet = wk.GetSheetAt(0)) { //构建插入的SQL语句 string sql = @"insert into T_Customers(CC_CustomerName,CC_CellPhone,CC_Landline,CC_CarNum,CC_BracketNum,CC_BuyDate) values(@customername,@cellphone,@landline,@cartNum,@bracketNum,@buydate)"; //循环获取每一行数据，循环遍历每一行数据 for (int i = 1; i <= sheet.LastRowNum; i++) { SqlParameter[] ps = new SqlParameter[] { new SqlParameter("@customername",SqlDbType.NVarChar), new SqlParameter("@cellphone",SqlDbType.VarChar), new SqlParameter("@landline",SqlDbType.VarChar), new SqlParameter("@cartNum",SqlDbType.VarChar), new SqlParameter("@bracketNum",SqlDbType.VarChar), new SqlParameter("@buydate",SqlDbType.DateTime) }; //获取当前行的数据 Row row = sheet.GetRow(i); //循环遍历每一个单元格写入信息,c=1是为了去掉表格最上面的那个行数 for (int c = 0; c < row.LastCellNum; c++) { if (row.GetCell(c) == null) { ps[c].Value = "NULL"; } else { ps[c].Value = row.GetCell(c).ToString(); } } SqlHelper.ExecuteNonQuery(sql, ps); } MessageBox.Show("插入成功"); } } } }
这是NPOI使用的手册：http://www.cnblogs.com/Areas/archive/2012/06/26/2563139.html
收获园豆：5
其他回答(6)
0
public static DataSet ReadExcelFileToDataSet(String fileName, string sheetName) { FileInfo fileInfo = new FileInfo(fileName); String strConn; if (fileInfo.Extension.ToLower() == ".xlsx") { strConn = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileName + ";Extended Properties='Excel 12.0 Xml;HDR=YES'"; } else { throw new Exception("only .xlsx file can be read"); } OleDbConnection Conn = new OleDbConnection(strConn); DataSet ds = new DataSet(); try { string SQL = "select * from [" + sheetName + "$]"; Conn.Open(); OleDbDataAdapter da = new OleDbDataAdapter(SQL, strConn); da.Fill(ds); return ds; } catch (Exception e) { throw new Exception(e.Message); } finally { Conn.Close(); Conn.Dispose(); } }
0
NPOI 组件
/// <summary> /// 读取excel /// 默认第一行为标头 /// </summary> /// <param name="strFileName">excel文档路径</param> /// <returns></returns> public static DataTable Import(string strFileName) { DataTable dt = new DataTable(); HSSFWorkbook hssfworkbook; using(FileStream file = new FileStream(strFileName, FileMode.Open, FileAccess.Read)) { hssfworkbook = new HSSFWorkbook(file); } ISheet sheet = hssfworkbook.GetSheetAt(0); System.Collections.IEnumerator rows = sheet.GetRowEnumerator(); IRow headerRow = sheet.GetRow(0); int cellCount = headerRow.LastCellNum; for(int j = 0; j < cellCount; j++) { ICell cell = headerRow.GetCell(j); dt.Columns.Add(cell.ToString()); } for(int i = (sheet.FirstRowNum + 1); i <= sheet.LastRowNum; i++) { IRow row = sheet.GetRow(i); DataRow dataRow = dt.NewRow(); for(int j = row.FirstCellNum; j < cellCount; j++) { if(row.GetCell(j) != null) dataRow[j] = row.GetCell(j).ToString(); } dt.Rows.Add(dataRow); } return dt; }
0
以上提供的方法都是可行的
还有另外一种操作excel2007的epplus
0
0
现手上有一封装的QHelper,能够将Excel导入到数据库，或者将数据库的数据导出到Excel，用最少量的代码完成最强大的功能，完全不考虑其导入导出的实现详细过程，只调用相关方法就可以轻松实现，使用教程详见：http://pengzhiyong.com/blog/27.html
0
我也推荐NPOI，巨方便，功能也强大
