aspx页面：
<table style="margin:30%px auto;">
<tr>
<td>选择导入的文件：</td>
<td>
<INPUT id="FileExcel" style="WIDTH: 300px" type="file" size="42" name="FilePhoto" runat="server">
</td>
<td>
<asp:Button ID="BtnDR" runat="server" Text="导入" onclick="BtnDR_Click"/>
</td>
<td>
<asp:label id="LblMessage" runat="server" Font-Bold="True" ForeColor="Red"></asp:label>
</td>
</tr>
</table>
后台代码：
protected void BtnDR_Click(object sender, EventArgs e)
{
string filename = string.Empty;
ViewState["i"] = 0;
try
{
filename = UpLoadXls(FileExcel);//上传XLS文件
ImportXlsToData(filename);//将XLS文件的数据导入数据库
if (filename != string.Empty && System.IO.File.Exists(filename))
{
System.IO.File.Delete(filename);//删除上传的XLS文件
}
LblMessage.Text = "数据导入成功！";
}
catch (Exception ex)
{
LblMessage.Text = ex.Message;
}
}
/// <summary>
/// 上传Excel文件
/// </summary>
/// <param name="inputfile">上传的控件名</param>
/// <returns></returns>
private string UpLoadXls(System.Web.UI.HtmlControls.HtmlInputFile inputfile)
{
string orifilename = string.Empty;
string uploadfilepath = string.Empty;
string modifyfilename = string.Empty;
string fileExtend = "";//文件扩展名
int fileSize = 0;//文件大小
try
{
if (inputfile.Value != string.Empty)
{
//得到文件的大小
fileSize = inputfile.PostedFile.ContentLength;
if (fileSize == 0)
{
throw new Exception("导入的Excel文件大小为0，请检查是否正确！");
}
//得到扩展名
fileExtend = inputfile.Value.Substring(inputfile.Value.LastIndexOf(".") + 1);
if (fileExtend.ToLower() != "xls" && fileExtend.ToLower() != "xlsx")
{
throw new Exception("你选择的文件格式不正确，只能导入EXCEL文件！");
}
//路径
uploadfilepath = Server.MapPath("/ImportExcel");
//新文件名
modifyfilename = System.Guid.NewGuid().ToString();
modifyfilename += "." + inputfile.Value.Substring(inputfile.Value.LastIndexOf(".") + 1);
//判断是否有该目录
System.IO.DirectoryInfo dir = new System.IO.DirectoryInfo(uploadfilepath);
if (!dir.Exists)
{
dir.Create();
}
orifilename = uploadfilepath + "\\" + modifyfilename;
//如果存在,删除文件
if (File.Exists(orifilename))
{
File.Delete(orifilename);
}
//上传文件
inputfile.PostedFile.SaveAs(orifilename);
}
else
{
throw new Exception("请选择要导入的Excel文件!");
}
}
catch (Exception ex)
{
throw ex;
}
return orifilename;
}
//获取excel里的所有数据
private void ImportXlsToData(string fileName)
{
try
{
string jsBlock;
if (fileName == string.Empty)
{
throw new ArgumentNullException("Excel文件上传失败！");
}
string oleDBConnString = String.Empty;
oleDBConnString=GetConnectstring(fileName);
OleDbConnection oleDBConn = null;
OleDbDataAdapter oleAdMaster = null;
DataTable m_tableName = new DataTable();
DataSet ds = new DataSet();
oleDBConn = new OleDbConnection(oleDBConnString);
oleDBConn.Open();
m_tableName = oleDBConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
if (m_tableName != null && m_tableName.Rows.Count > 0)
{
m_tableName.TableName = m_tableName.Rows[0]["TABLE_NAME"].ToString();
}
string sqlMaster;
sqlMaster = " SELECT * FROM [" + m_tableName.TableName + "]";
oleAdMaster = new OleDbDataAdapter(sqlMaster, oleDBConn);
oleAdMaster.Fill(ds, "m_tableName");
oleAdMaster.Dispose();
oleDBConn.Close();
oleDBConn.Dispose();
AddDatasetToSQL(ds, 9);//将excel数据添加到数据库，9代表excel的列只有9列
}
catch (Exception ex)
{
throw ex;
}
}
//连接excel钥匙
public string GetConnectstring(string filepath)
{
string strConn;
try
{
strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filepath + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=1;\"";
}
catch
{
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filepath + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=0;\"";
}
return strConn;
}
//添加数据到excel
private bool AddDatasetToSQL(DataSet pds, int Cols)
{
int ic, ir;
ic = pds.Tables[0].Columns.Count;
if (pds.Tables[0].Columns[0].ToString() != "Item number")
{
throw new Exception("导入Excel格式错误！Excel表头第一列应该是Item number列");
}
if (pds.Tables[0].Columns[1].ToString() != "Reference")
{
throw new Exception("导入Excel格式错误！Excel表头第二列应该是Reference列");
}
if (pds.Tables[0].Columns[2].ToString() != "Physical cost amount")
{
throw new Exception("导入Excel格式错误！Excel表头第三列应该是有Physical cost amount列");
}
if (pds.Tables[0].Columns[3].ToString() != "Quantity")
{
throw new Exception("导入Excel格式错误！Excel表头第四列应该是有Quantity列");
}
if (pds.Tables[0].Columns[4].ToString() != "Account number")
{
throw new Exception("导入Excel格式错误！Excel表头第五列应该是有Account number列");
}
if (pds.Tables[0].Columns[5].ToString() != "Physical date")
{
throw new Exception("导入Excel格式错误！Excel表头第六个应该是有Physical date列");
}
if (pds.Tables[0].Columns[6].ToString() != "Financial cost amount")
{
throw new Exception("导入Excel格式错误！Excel表头第七列应该是有Financial cost amount列");
}
if (pds.Tables[0].Columns[7].ToString() != "Adjustment")
{
throw new Exception("导入Excel格式错误！Excel表头第八列应该是有Adjustment列");
}
if (pds.Tables[0].Columns[8].ToString() != "Warehouse")
{
throw new Exception("导入Excel格式错误！Excel表头第九列应该是有Warehouse列");
}
if (pds.Tables[0].Columns.Count < Cols)
{
throw new Exception("导入Excel格式错误！Excel只有" + ic.ToString() + "列");
}
ir = pds.Tables[0].Rows.Count;
if (pds != null && pds.Tables[0].Rows.Count > 0)
{
string sql = "select * from Stock where PhysicalDate=@PhysicalDate";
SqlParameter[] sqlpr = { new SqlParameter("@PhysicalDate", pds.Tables[0].Rows[0][5])};
DataTable dt = DbHelperSQL.DbHelperSQL.ExecuteQuery(sql, sqlpr);//根据excel里的字段年月进行查询
if (dt.Rows.Count > 0)
{
string sqldelete = "delete from Stock where PhysicalDate='"+pds.Tables[0].Rows[0][5]+"'";
//SqlParameter[] sqlps = { new SqlParameter("@PhysicalDate", pds.Tables[0].Rows[0][5]) };
DbHelperSQL.DbHelperSQL.AddStock(sqldelete.ToString());//如果有本月的记录，都删除掉
}
DbHelperSQL.DbHelperSQL.Excel_SQL(pds.Tables[0]);///这是导入进数据库的核心
}
else
{
throw new Exception("导入数据为空！");
}
return true;
}
dbhelpe里的代码：
public static void Excel_SQL(DataTable dt)
{
using (SqlConnection con = new SqlConnection(ConnectionStringBaseData))
{
con.Open();
using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(con))
{
sqlBulkCopy.BatchSize = 1000;//一次性插入1000行
sqlBulkCopy.SqlRowsCopied += new SqlRowsCopiedEventHandler(OnSqlRowsCopied);
sqlBulkCopy.DestinationTableName = "Stock"; //导入数据库中的表名
sqlBulkCopy.WriteToServer(dt);//微软自创方法
}
con.Close();
}
}
天下皆白，唯我独黑。
.--./.-. '----------.\'-' .--"--""-"-' '--'