调用页面:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using JuMi.BLL.Business;
using System.Data;
using System.Data.OleDb;
using System.Windows.Forms;
using JuMi.Model.Business;
using JuMi.BLL.Business;
namespace JuMi.Web.Form.Manager.Business
{
public partial class WebForm1 : JuMi.Web.UI.BasePage
{
protected void Page_Load(object sender, EventArgs e)
{
}
/// <summary>
/// 导入广告商信息
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void btnAdverInfo_Click(object sender, EventArgs e)
{
DataTable dt = ExcelDemo.GetExcelToDatatable("F:\\Demo\\excelDemo.xlsx", "table");
int count = 0;
for (int i = 0; i < dt.Rows.Count; i++)
{
Model_Advertiser model = new Model_Advertiser();
model.Adver_CompanyName = dt.Rows[i][0].ToString();//名称
model.Adver_Distinguish = dt.Rows[i][1].ToString();//税号
model.Adver_RegisPhone = dt.Rows[i][2].ToString();//电话
model.Adver_BankNumber = dt.Rows[i][3].ToString();//银行账号
model.Adver_BankAddress = dt.Rows[i][4].ToString();//开户行地址
model.Adver_RegisAdress = dt.Rows[i][5].ToString();//注册地址
model.Adver_State = 0;
if (AdvertiserBLL.Add(model) > 0)
{
count++;
}
}
if (count >= dt.Rows.Count)
{
JscriptMsgSuccess("广告商信息导入成功!", "", "function(){api.reload();}");
}
else
{
JscriptMsgError("广告商信息导入失败!", "", "function(){api.reload();}");
}
}
}
}
实现方法页:
using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Data; using System.Data.OleDb; namespace JuMi.BLL.Business { public class ExcelDemo { /// <summary> /// Excel数据导入Datable /// </summary> /// <param name="fileUrl">文件路径</param> /// <param name="table"></param> /// <returns></returns> public static DataTable GetExcelToDatatable(string fileUrl, string table) { //支持office2007之前的 .xls //const string cmdText = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties='Excel 8.0;IMEX=1';";//链接字符串 //支持.xls和.xlsx,即包括office2010等版本的 HDR=Yes代表第一行是标题,不是数据; const string cmdText = "Provider=Microsoft.Ace.OleDb.12.0;Data Source={0};Extended Properties='Excel 12.0; HDR=Yes; IMEX=1'"; DataTable dt = null; //建立连接 OleDbConnection conn = new OleDbConnection(string.Format(cmdText, fileUrl)); try { //打开链接 if (conn.State == ConnectionState.Broken || conn.State == ConnectionState.Closed) { conn.Open(); } DataTable schemaTable = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null); //获取excel的第一个sheet名称 string sheetName1 = schemaTable.Rows[0]["TABLE_NAME"].ToString().Trim(); //查询sheet数据 string strSql1 = "select * from [" + sheetName1 + "]"; OleDbDataAdapter da = new OleDbDataAdapter(strSql1, conn); DataSet ds = new DataSet(); da.Fill(ds, table); dt = ds.Tables["" + table + ""]; return dt; } catch (Exception exc) { throw exc; } finally { conn.Close(); conn.Dispose(); } } } }