方法一：
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 ==