将Excel导入到数据库
using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.OleDb;
using Excel = Microsoft.Office.Interop.Excel;
namespace ExcelToDataSet
{
/// <summary>
/// 目前测试的com是office2003
/// 应用com组件：Microsoft Excel 11.0 Object Library
/// </summary>
public partial class ExcelToDataSet : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
}
protected void btnInputExcel_Click(object sender, EventArgs e)
{
string FileType = this.FileUpExcel.PostedFile.ContentType;
//if (FileType == "application/vnd.ms-excel")
if(FileType=="application/octet-stream")
{
this.GvExcel.DataSource = CreateDataSource();
this.GvExcel.DataBind();
}
else
{
this.lbMsg.Text = "<font color='red'>请输入Excel文件！</font>";
}
}
//创建数据源
private DataSet CreateDataSource()
{
//客户端路径
//string path = this.FileUpExcel.PostedFile.FileName;
string path = (string)Session["path"];
string strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + path + ";Extended Properties=Excel 8.0;";
OleDbConnection olecon = new OleDbConnection(strCon);
//OleDbDataAdapter myda = new OleDbDataAdapter("SELECT * FROM [Sheet1$]", strCon);
OleDbDataAdapter myda = new OleDbDataAdapter("SELECT * FROM [" + this.ddlExcelSheetName.SelectedValue + "$]", strCon);
DataSet myds = new DataSet();
myda.Fill(myds);
return myds;
}
//加载sheet表名到dropdown中
protected void AddSheetNameToDrop()
{
this.ddlExcelSheetName.Items.Clear();//清空DropDownList中的值
if (this.FileUpExcel.HasFile)
{
string pathName = this.FileUpExcel.PostedFile.FileName;
Session["path"] = pathName;
object MissingValue = Type.Missing;
Excel.Application ep = new Excel.ApplicationClass();
Excel.Workbook ew = ep.Workbooks.Open(pathName, MissingValue,
MissingValue, MissingValue, MissingValue,
MissingValue, MissingValue, MissingValue,
MissingValue, MissingValue, MissingValue,
MissingValue, MissingValue, MissingValue,
MissingValue);
// Excel.Worksheet ews;
int count = ew.Worksheets.Count;
//读出表的数量
//Response.Write(count.ToString());
//遍历出excel文件中的所有的表名
for (int i = 1; i <= count; i++)
{
// ews = (Excel.Worksheet)ew.Worksheets[i];
string sheetName = ((Excel.Worksheet)ew.Worksheets[i]).Name;
//绑定到ddl控件中
this.ddlExcelSheetName.Items.Add(sheetName);
}
//把excel进程干掉，恨死我拉搞了n长时间
ew.Close(false, Type.Missing, Type.Missing);
ep.Workbooks.Close();
ep.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(ep);
System.Runtime.InteropServices.Marshal.ReleaseComObject(ew);
ep = null;
ew = null;
GC.Collect();//强制对所有代码垃圾进行回收
}
}
protected void btnAddSheet_Click(object sender, EventArgs e)
{
AddSheetNameToDrop();
}
}
}
