读取Excel数据到Table表中
方法一：
try { List<DBUtility.CommandInfo> list = new List<DBUtility.CommandInfo>(); string strConn = "Provider=Microsoft.Ace.OleDb.12.0;" + "data source=" + path + ";Extended Properties='Excel 12.0; HDR=YES; IMEX=1'"; //此连接可以操作.xls与.xlsx文件 using (OleDbConnection conn = new OleDbConnection(strConn)) { conn.Open(); DataTable sheetsName = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "Table" }); //得到所有sheet的名字 string SheetName = sheetsName.Rows[0][2].ToString(); string strSQL = string.Format("SELECT * FROM [{0}]", SheetName); OleDbDataAdapter oda = new OleDbDataAdapter(strSQL, strConn); DataTable dt = new DataTable(); oda.Fill(dt); if (dt.Rows.Count > 0) { DateTime date = DateTime.Parse(System.DateTime.Now.ToString()); string year = date.ToString("yyyy"); string month = date.ToString("MM"); string proName = dt.Rows[0][1].ToString().Substring(5); strSQL = "insert into tb_targetcostlist (gcmc,Date,UserID) values('" + proName + "','" + date + "','" + Session["UserId"] + "');select @@identity;"; string proID = DBUtility.DbHelperSQL.GetSingle(strSQL).ToString(); for (int i = 1; i < dt.Rows.Count; i++) { DBUtility.CommandInfo item = new DBUtility.CommandInfo(); item.CommandText = "insert into tb_MonthlyCost (Num, ProClassification, ProName, Unit, Quantity, UnitPrice, TotalPrice, MonthPlanCost, MonthActuallyCost_GJ, MonthActuallyCost_JD, MonthProfitAndLoss, TotalPlanCost, TotalActuallyCost_GJ, TotalActuallyCost_JD, ProTotalCost, TotalProfitAndLoss, EvenCost, ContractPrice, ProfitAndLoss, Others, Month,Year,proID)"; item.CommandText += "values("; item.CommandText += "'" + dt.Rows[i][0].ToString() + "','" + dt.Rows[i][1].ToString() + "','" + dt.Rows[i][2].ToString() + "','" + dt.Rows[i][3].ToString() + "','" + dt.Rows[i][4].ToString() + "','" + dt.Rows[i][5].ToString() + "','" + dt.Rows[i][6].ToString() + "','" + dt.Rows[i][7].ToString() + "','" + dt.Rows[i][8] + "','" + dt.Rows[i][9].ToString() + "','" + dt.Rows[i][10].ToString() + "','" + dt.Rows[i][11].ToString() + "','" + dt.Rows[i][12].ToString() + "','" + dt.Rows[i][13].ToString() + "','" + dt.Rows[i][14].ToString() + "','" + dt.Rows[i][15].ToString() + "','" + dt.Rows[i][16].ToString() + "','" + dt.Rows[i][17].ToString() + "','" + dt.Rows[i][18].ToString() + "','" + dt.Rows[i][19].ToString() + "','" + month + "','" + year + "','" + proID + "'"; item.CommandText += ")"; list.Add(item); DBUtility.DbHelperSQL.ExecuteSqlTran(list); item.CommandText = ""; } } } } catch (Exception ex) { Page.RegisterStartupScript("", "<script>alert('" + ex.Message + "');</script>"); }
方法二：
此方法弊端：每次都会产生一个EXCEL.exe进程，下次再运行，要不这个进程关闭才行，非常不方便
private void ReadExcelToTable(string path) { Microsoft.Office.Interop.Excel.Application oXL; Microsoft.Office.Interop.Excel._Workbook oWB; Microsoft.Office.Interop.Excel._Worksheet oSheet; object missing = System.Type.Missing; //创建Excel实例 oXL = new Microsoft.Office.Interop.Excel.Application(); //打开已有的工作簿 oWB = oXL.Workbooks.Open(path, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing); //导入服务器的连接 String strcon = "Data Source=192.168.1.245;Initial Catalog=Component;User ID=sa;Password=yuxit2008"; using (SqlConnection objcon1 = new SqlConnection(strcon)) { objcon1.Open(); for (int i = 1; i <= oWB.Sheets.Count; i++) { oSheet = (Microsoft.Office.Interop.Excel.Worksheet)oWB.Sheets.get_Item(i); //tb_targetcostlist中插入标题，项目名称等 DateTime date = DateTime.Parse(System.DateTime.Now.ToString()); string year = date.ToString("yyyy"); string month = date.ToString("MM"); string proName = GetCellText(2, 2, oSheet).Substring(5); string title = GetCellText(1, 1, oSheet); string strSQL = "insert into tb_targetcostlist(gcmc,Title,Date,UserID) values('" + proName + "','" + title + "','" + date + "','" + Session["UserId"] + "');select @@identity;"; string proID = DBUtility.DbHelperSQL.GetSingle(strSQL).ToString(); // 从第二行开始遍历 行、列 数据 for (int j = 2; j <= oSheet.UsedRange.Rows.Count; j++) { string str = ""; for (int n = 1; n < oSheet.UsedRange.Columns.Count; n++) { str += "'" + GetCellText(j, n, oSheet) + "',"; } string strinsert = @"insert into tb_MonthlyCost( Num, ProClassification, ProName, Unit, Quantity, UnitPrice, TotalPrice, MonthPlanCost, MonthActuallyCost_GJ, MonthActuallyCost_JD, MonthProfitAndLoss, TotalPlanCost, TotalActuallyCost_GJ, TotalActuallyCost_JD, ProTotalCost, TotalProfitAndLoss, EvenCost, ContractPrice, ProfitAndLoss, Others,Year,Month,ProID)" + " values(" + str + "'" + year + "','" + month + "','" + proID + "')"; using (SqlCommand objcom = new SqlCommand(strinsert, objcon1)) { objcom.ExecuteNonQuery(); } } } } } ///<summary> ///获取单元格文本 ///</summary> ///<param name="row"></param> ///<param name="col"></param> ///<param name="oSheet"></param> ///<returns></returns> private string GetCellText(int row, int col, Microsoft.Office.Interop.Excel._Worksheet oSheet) { string result = ""; bool isFound = false; int rowEnd = 1; int colEnd = 1; Microsoft.Office.Interop.Excel.Range oRng = (Microsoft.Office.Interop.Excel.Range)oSheet.Cells[row, col]; if (oRng.Value2 != null) { result = oRng.Value2.ToString(); isFound = true; } else { if (!(bool)oRng.MergeCells) // 如果该单元格无值且不是合并的，则返回 null { result = null; isFound = true; } } if (!isFound) { // 倒序遍历该列所有行(从倒2行开始)，判断是否有合并单元格且有值，如果遇到则已求出， // 如果遇到非合并单元格，则行+1(倒回1行)，列同样倒序进行 for (int r = row - 1; r >= 1; r--) { oRng = (Microsoft.Office.Interop.Excel.Range)oSheet.Cells[r, col]; if ((bool)oRng.MergeCells) { try { if (oRng.Value2 != null) { result = oRng.Value2.ToString(); isFound = true; break; } } catch (Exception) { } } else { rowEnd = r + 1; break; } } if (!isFound) { // 倒序遍历该行所有列，判断是否有合并单元格且有值，如果遇到则已求出，如果遇到非合并单元格，则说明数据非法。。。 for (int c = col - 1; c >= 1; c--) { oRng = (Microsoft.Office.Interop.Excel.Range)oSheet.Cells[rowEnd, c]; if ((bool)oRng.MergeCells) { try { if (oRng.Value2 != null) { result = oRng.Value2.ToString(); isFound = true; break; } } catch (Exception) { } } else { colEnd = c + 1; break; } } } if (!isFound) { result = null; } } return result; }
方法三（重点）：
使用NPOI读取单元格。对于合并单元格：相同值，读取多次，存储到数据库表中
private void ReadExcelToTable(string path) { using (FileStream fs=File.Open(path,FileMode.Open)) { using (Workbook wk=new HSSFWorkbook(fs)) { for (int i = 0; i < wk.NumberOfSheets; i++) { using (Sheet sheet = wk.GetSheetAt(i)) { //tb_targetcostlist中插入标题，项目名称等 DateTime date = DateTime.Parse(System.DateTime.Now.ToString()); string year = date.ToString("yyyy"); string month = date.ToString("MM"); Cell cell_title = sheet.GetRow(0).GetCell(0); Cell cell_proName = sheet.GetRow(1).GetCell(1); Cell cell_monthly=sheet.GetRow(1).GetCell(9); Cell cell_total=sheet.GetRow(1).GetCell(13); string proName = getCellValue(cell_proName).Substring(5); string title = getCellValue(cell_title); string monthlyProduct=getCellValue(cell_monthly); string totalProduct=getCellValue(cell_total); string strSQL = "insert into tb_targetcostlist(ProName, Title, Date, UserID,MonthlyProduct,TotalProduct) values('" + proName + "','" + title + "','" + date + "','" + Session["UserId"] + "','" + monthlyProduct + "','" + totalProduct + "');select @@identity"; string proID = DBUtility.DbHelperSQL.GetSingle(strSQL).ToString(); //从第3行开始遍历 for (int j = 2; j <= sheet.LastRowNum; j++) { string value = ""; Row curRow = sheet.GetRow(j); if (curRow != null) { for (int m = 0; m < 19; m++) { Cell cell = curRow.GetCell(m); if (cell != null) { if (isMergedRegion(sheet, j, m)) { value += "'" + getMergedRegionValue(sheet, j, m) + "',"; } else { value += "'" + getCellValue(cell) + "',"; } } else { value += "'',"; } } string strinsert = @"insert into tb_MonthlyCost( Num, ProClassification, ItemName, Unit, Quantity, UnitPrice, TotalPrice, MonthPlanCost, MonthActuallyCost_GJ, MonthActuallyCost_JD, MonthProfitAndLoss, TotalPlanCost, TotalActuallyCost_GJ, TotalActuallyCost_JD, ProTotalCost, TotalProfitAndLoss, EvenCost, ContractPrice, ProfitAndLoss,Year,Month,ProID)" + " values(" + value + "'" + year + "','" + month + "','" + proID + "')"; DBUtility.DbHelperSQL.GetSingle(strinsert); } } } } } } } /// <summary> /// 获取合并单元格的值 /// </summary> /// <param name="sheet"></param> /// <param name="row"></param> /// <param name="column"></param> /// <returns></returns> public String getMergedRegionValue(Sheet sheet, int row, int column) { int sheetMergeCount = sheet.NumMergedRegions; for (int i = 0; i < sheetMergeCount; i++) { CellRangeAddress ca = sheet.GetMergedRegion(i); int firstColumn = ca.FirstColumn; int lastColumn = ca.LastColumn; int firstRow = ca.FirstRow; int lastRow = ca.LastRow; if (row >= firstRow && row <= lastRow) { if (column >= firstColumn && column <= lastColumn) { Row fRow = sheet.GetRow(firstRow); Cell fCell = fRow.GetCell(firstColumn); return getCellValue(fCell); } } } return null; } /// <summary> /// 判断指定的单元格是否是合并单元格 /// </summary> /// <param name="sheet"></param> /// <param name="row"></param> /// <param name="column"></param> /// <returns></returns> public bool isMergedRegion(Sheet sheet, int row, int column) { int sheetMergeCount = sheet.NumMergedRegions; for (int i = 0; i < sheetMergeCount; i++) { CellRangeAddress ca = sheet.GetMergedRegion(i); int firstColumn = ca.FirstColumn; int lastColumn = ca.LastColumn; int firstRow = ca.FirstRow; int lastRow = ca.LastRow; if (row >= firstRow && row <= lastRow) { if (column >= firstColumn && column <= lastColumn) { return true; } } } return false; } /// <summary> /// 获取单元格的值 /// </summary> /// <param name="cell"></param> /// <returns></returns> public String getCellValue(Cell cell) { if (cell == null) return ""; if (cell.CellType == CellType.STRING) { return cell.StringCellValue; } else if (cell.CellType == CellType.BOOLEAN) { return cell.BooleanCellValue.ToString(); } else if (cell.CellType == CellType.FORMULA) {
//此处注意，对于通过公式计算出来的单元格值，返回值为cell.NumericCellValue.ToString(); return cell.NumericCellValue.ToString(); } else if (cell.CellType == CellType.NUMERIC) { return cell.NumericCellValue.ToString(); } return ""; }
