Excel导入数据库

前台：

<asp:FileUpload ID="fuGlossaryXls" runat="server" style="float:left;height:30px; " EnableViewState="False" />
<asp:Button style="float:left;" ID="btnImport" runat="server" class="btn btn-default btn-sm" Text="导入" OnClick="btnImport_Click" EnableViewState="true"></asp:Button>

后台：

protected void btnImport_Click(object sender, EventArgs e)
{
if (fuGlossaryXls.HasFile == false)//HasFile用来检查FileUpload是否有指定文件
{
Response.Write("<script>alert('请您选择Excel文件')</script> ");
return;//当无文件时,返回
}
string IsXls = System.IO.Path.GetExtension(fuGlossaryXls.FileName).ToString().ToLower();//System.IO.Path.GetExtension获得文件的扩展名
string[] strExtension = { ".xls", ".xlsx" ,".Xlsx",".Xls"};
if (!strExtension.Contains(IsXls))
{
Response.Write("<script>alert('只可以选择Excel文件')</script>");
return;//当选择的不是Excel文件时,返回
}
string filename = DateTime.Now.ToString("yyyyMMdd_hhmmss") + fuGlossaryXls.FileName; //获取Execle文件名 DateTime日期函数
string savePath = Server.MapPath(("upfiles\\") + filename);//Server.MapPath 获得虚拟服务器相对路径
if (File.Exists(savePath)) //是否存在
{
File.Delete(savePath);
}
fuGlossaryXls.SaveAs(savePath); //SaveAs 将上传的文件内容保存在服务器上
DataSet ds = ExcelSqlConnection(savePath, filename, IsXls); //调用自定义方法
//循环判断sheet
for (int i = 0; i < ds.Tables.Count;i++ ) {
DataRow[] dr = ds.Tables[i].Select();//定义一个DataRow数组
int rowsnum = ds.Tables[0].Rows.Count;//表内是否有数据
if (ds.Tables[i].TableName == "各业务当月预计可结算金额$") {
for (int a = 0; a < dr.Length; a++)
{
try
{

string OperativeSegments = dr[a]["业务板块"].ToString().Trim();
string Channel = dr[a]["产品/渠道"].ToString().Trim();
string StatisticsCycle = dr[a]["统计周期"].ToString().Trim();
string DayPredict = dr[a]["今日预计"].ToString().Trim();
string NetIncrease = dr[a]["净增"].ToString().Trim();
DateTime g_Date = Convert.ToDateTime(System.DateTime.Now.ToString());

string sql = "INSERT INTO [BESettlementAmount] values(";
sql += mk.SQLQC(OperativeSegments) + mk.SQLQC(Channel) + mk.SQLQC(StatisticsCycle) + mk.SQLQC(DayPredict);
sql += mk.SQLQC(NetIncrease) + mk.SQLQ(g_Date) + ")";
sda.ExecSqlHandel(sql);
}
catch (Exception ex)
{
Response.Write("<script>alert('各业务当月预计可结算金额!'" + ex.Message + ");</script>");
return;
}
binddataDayreport();
}
}
else if (ds.Tables[i].TableName == "流量掌厅$")
{
for (int a = 0; a < dr.Length; a++)
{
try
{

string Channel = dr[a]["渠道"].ToString().Trim();
string CurrentMonthTarget = dr[a]["本月目标"].ToString().Trim();
string CurrentMonthOrder = dr[a]["本月订购额"].ToString().Trim();
string LastMonthOrder = dr[a]["上月同期订购额"].ToString().Trim();
string CompareTimeSeries = dr[a]["完成时序"].ToString().Trim();
string Ratio = dr[a]["环比"].ToString().Trim();
//string Type = dr[a]["类别"].ToString().Trim();

//DateTime g_Date = Convert.ToDateTime(System.DateTime.Now.ToString("d"));
DateTime g_Date = Convert.ToDateTime(System.DateTime.Now.ToString());

string sql = "INSERT INTO [FlowBusinessOffice](Channel,CurrentMonthTarget,CurrentMonthOrder,LastMonthOrder,CompareTimeSeries,Ratio,Date) values(";
sql += mk.SQLQC(Channel) + mk.SQLQC(CurrentMonthTarget) + mk.SQLQC(CurrentMonthOrder) + mk.SQLQC(LastMonthOrder);
sql += mk.SQLQC(CompareTimeSeries) + mk.SQLQC(Ratio) + mk.SQLQ(g_Date) + ")";
sda.ExecSqlHandel(sql);
}
catch (Exception ex)
{
Response.Write("<script>alert('流量掌厅!'" + ex.Message + ");</script>");

}
binddatadlzhangting();
}
}
else if (ds.Tables[i].TableName == "电子黄页$")
{
for (int a = 0; a < dr.Length;a++ )
{
try
{

string getarea = dr[a]["地区"].ToString().Trim();
string getdaynewcrease = dr[a]["当日新增"].ToString().Trim();
string getdaycancel = dr[a]["当日取消"].ToString().Trim();
string getdayinter = dr[a]["当日在网"].ToString().Trim();
string getARPU = dr[a]["ARPU"].ToString().Trim();
string getES = dr[a]["当月预计可结算金额"].ToString().Trim();
string getcc = dr[a]["当月新增完成情况"].ToString().Trim();
//DateTime g_Date = Convert.ToDateTime(System.DateTime.Now.ToString("d"));
DateTime g_Date = Convert.ToDateTime(System.DateTime.Now.ToString());

string sql = "INSERT INTO [ElactricYellowPage] values(";
sql += mk.SQLQC(getarea) + mk.SQLQC(getdaynewcrease) + mk.SQLQC(getdaycancel) + mk.SQLQC(getdaycancel);
sql += mk.SQLQC(getARPU) + mk.SQLQC(getES) + mk.SQLQC(getcc) + mk.SQLQ(g_Date) + ")";
sda.ExecSqlHandel(sql);
}
catch (Exception ex) {
Response.Write("<script>alert('电子黄页!'"+ex.Message+");</script>");
return;
}
BindDataElectricYellowPage();
}
}
else if (ds.Tables[i].TableName == "短信渠道$") {
for (int a = 0; a < dr.Length; a++)
{
try
{

string Area = dr[a]["地区"].ToString().Trim();
string SendCount = dr[a]["发送数"].ToString().Trim();
string OrderCount = dr[a]["订购数"].ToString().Trim();
string OrderAmount = dr[a]["订购额"].ToString().Trim();
string ARPU = dr[a]["ARPU"].ToString().Trim();
string OrderRate = dr[a]["订购率"].ToString().Trim();
DateTime g_Date = Convert.ToDateTime(System.DateTime.Now.ToString());

string sql = "INSERT INTO [SMSChannel] values(";
sql += mk.SQLQC(Area) + mk.SQLQC(SendCount) + mk.SQLQC(OrderCount) + mk.SQLQC(OrderAmount);
sql += mk.SQLQC(ARPU) + mk.SQLQC(OrderRate) + mk.SQLQ(g_Date) + ")";
sda.ExecSqlHandel(sql);
}
catch (Exception ex)
{
Response.Write("<script>alert('短信渠道!'" + ex.Message + ");</script>");
return;
}
BindDataSMSChannel();
}
}
else if (ds.Tables[i].TableName == "客户端渠道$") {
for (int a = 0; a < dr.Length; a++)
{
try
{

string Channel = dr[a]["渠道"].ToString().Trim();
string ActiveQueryUser = dr[a]["主动查询用户"].ToString().Trim();
string TotalCount = dr[a]["总订单数"].ToString().Trim();
string SuccessOrderCount = dr[a]["成功订单数"].ToString().Trim();
string OrderAmount = dr[a]["订购额"].ToString().Trim();
string ARPU = dr[a]["ARPU"].ToString().Trim();
string OrderConversion = dr[a]["下单转化率"].ToString().Trim();
string SuccessConversion = dr[a]["成功转化率"].ToString().Trim();

DateTime g_Date = Convert.ToDateTime(System.DateTime.Now.ToString());

string sql = "INSERT INTO [ClientChannel] values(";
sql += mk.SQLQC(Channel) + mk.SQLQC(ActiveQueryUser) + mk.SQLQC(TotalCount) + mk.SQLQC(SuccessOrderCount);
sql += mk.SQLQC(OrderAmount) + mk.SQLQC(ARPU) + mk.SQLQC(OrderConversion) + mk.SQLQC(SuccessConversion) + mk.SQLQ(g_Date) + ")";
sda.ExecSqlHandel(sql);
}
catch (Exception ex)
{
Response.Write("<script>alert('客户端!'" + ex.Message + ");</script>");
return;
}
BindDataClientChannel();
}
}
else if (ds.Tables[i].TableName == "流量分销当月订购及与上月同期对比情况$") {
for (int a = 0; a < dr.Length; a++)
{
try
{

string CurrentMonthTotalAmout = dr[a]["当月总销售额"].ToString().Trim();
string LastMonthAmount = dr[a]["上月同期销售额"].ToString().Trim();
string Ratio = dr[a]["环比"].ToString().Trim();
string Target = dr[a]["指标"].ToString().Trim();
string FinishingRate = dr[a]["完成率"].ToString().Trim();


DateTime g_Date = Convert.ToDateTime(System.DateTime.Now.ToString());

string sql = "INSERT INTO [dbo].[2491FlowDistribution] values(";
sql += mk.SQLQC(CurrentMonthTotalAmout) + mk.SQLQC(LastMonthAmount) + mk.SQLQC(Ratio) + mk.SQLQC(Target);
sql += mk.SQLQC(FinishingRate) + mk.SQLQ(g_Date) + ")";
sda.ExecSqlHandel(sql);
}
catch (Exception ex)
{
Response.Write("<script>alert('流量分销当月订购及与上月同期对比情况!'" + ex.Message + ");</script>");
return;
}
BindData2491FlowDistribution();
}
}
else if (ds.Tables[i].TableName == "流量分销当月订购情况$") {
for (int a = 0; a < dr.Length; a++)
{
try
{

string SalesSummary = dr[a]["销售汇总"].ToString().Trim();
string CurrentMonth = dr[a]["当月"].ToString().Trim();
string LastMonth = dr[a]["上月同期"].ToString().Trim();
string NetIncreased = dr[a]["净增"].ToString().Trim();
string Ratio = dr[a]["环比"].ToString().Trim();
DateTime g_Date = Convert.ToDateTime(System.DateTime.Now.ToString());

string sql = "INSERT INTO [2491FDCurrentOrder] values(";
sql += mk.SQLQC(SalesSummary) + mk.SQLQC(CurrentMonth) + mk.SQLQC(LastMonth) + mk.SQLQC(NetIncreased);
sql += mk.SQLQC(Ratio) + mk.SQLQ(g_Date) + ")";
sda.ExecSqlHandel(sql);
}
catch (Exception ex)
{
Response.Write("<script>alert('流量分销当月订购情况!'" + ex.Message + ");</script>");
return;
}
BindDataFDCurrentOrder();
}
}


}
Response.Write("<script>alert('导入成功!');</script>");

}

 

public static System.Data.DataSet ExcelSqlConnection(string filepath, string tableName,string IsXls)
{
string strCon = "";
if (IsXls == ".xlsx" || IsXls == ".Xlsx")
{
strCon = "Provider=Microsoft.Ace.OleDb.12.0;" + "data source=" + filepath + ";Extended Properties='Excel 12.0; HDR=YES; IMEX=1'";
}
else {
strCon = "provider=Microsoft.Jet.OLEDB.4.0;data source=" + filepath + ";Extended Properties='Excel 8.0;HDR=Yes;IMEX=1'";
}

//string strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filepath + ";Extended Properties='Excel 8.0;HDR=YES;IMEX=1;'";
OleDbConnection odcon = new OleDbConnection(strCon);

ArrayList sheetNamelist = new ArrayList();
DataSet dsExcel = new DataSet();
try
{
odcon.Open();
System.Data.DataTable sTable = odcon.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, null);
//Sheets Name
//创建ArrayList对象 存放所有sheetname

for (int i = 0; i < sTable.Rows.Count; i++)
{
tableName = sTable.Rows[i][2].ToString().Trim();
sheetNamelist.Add(tableName);

}
}
catch (Exception ex)
{
throw new Exception(ex.Message.ToString(), ex);
}
finally
{
odcon.Close();
}

string strSQL = string.Empty;
for (int i = 0; i < sheetNamelist.Count; i++)
{
strSQL = "select * from [" + sheetNamelist[i].ToString() + "]";
OleDbDataAdapter da = new OleDbDataAdapter(strSQL, odcon);
DataTable dtExcel = new DataTable(sheetNamelist[i].ToString());
da.Fill(dtExcel);
dsExcel.Tables.Add(dtExcel);
}
return dsExcel;
}