从Excel工作表上传数据 [英] uploading data from excel sheet
问题描述
我有一个Web应用程序,我要从excel上传数据以上传我使用的excel.
我有如下的aspx页面:
I have one web application where i m uploading data from excel to upload excel i have used.
I have aspx page like below:
<div class="div">
<div class="upload_icon">
<asp:ImageButton runat="server" ID="btnUpload" ImageUrl="images/control_img/down.jpg"
OnClick="btnUpload_Click" />
</div>
<div class="field">
<div class="browse_field_1">
<input type="file" id="File1" runat="server" class="browse_field" />
</div>
<%--<div class="file_2">
<img src="images/control_img/browse.png" /></div>--%>
</div>
</div>
I have made one utility class which upload excel is as follow:
public class Uplaoad
{
public Uplaoad()
{
//
// TODO: Add constructor logic here
//
}
public string UploadFile(HtmlInputFile objHtmlInputFile, string savePath)
{
string sFilename = string.Empty;
HttpPostedFile postedfile = objHtmlInputFile.PostedFile;
if (postedfile != null)
{
int postedfilelen = postedfile.ContentLength;
string[] arr = new string[2];
try
{
byte[] myData = new byte[postedfilelen + 1];
postedfile.InputStream.Read(myData, 0, postedfilelen);
sFilename = System.IO.Path.GetFileName(postedfile.FileName);
int file_append = 0;
while ((System.IO.File.Exists(savePath + sFilename)))
{
file_append = file_append + 1;
sFilename = System.IO.Path.GetFileNameWithoutExtension(postedfile.FileName) + file_append.ToString() + System.IO.Path.GetExtension(postedfile.FileName);
}
System.IO.FileStream newFile = new System.IO.FileStream(savePath + sFilename, System.IO.FileMode.Create);
newFile.Write(myData, 0, myData.Length);
newFile.Close();
arr[0] = "T";
arr[1] = sFilename;
return sFilename;
}
catch (Exception ex)
{
arr[1] = "File upload failed. Please contact system administrator.";
return sFilename;
}
}
return sFilename;
}
public string GetID(string ID)
{
string NewIDD = "";
if (ID.Length == 7)
NewIDD = ID;
else if (ID.Length == 6)
NewIDD = "0" + ID;
else if (ID.Length == 5)
NewIDD = "00" + ID;
else if (ID.Length == 4)
NewIDD = "000" + ID;
else if (ID.Length == 3)
NewIDD = "0000" + ID;
else if (ID.Length == 2)
NewIDD = "00000" + ID;
else if (ID.Length == 1)
NewIDD = "000000" + ID;
return NewIDD;
}
}
现在在图像按钮控件click事件处理程序中,我编写了如下类:
受保护的无效btnUpload_Click(对象发送者,ImageClickEventArgs e)
now in image button control click event handler i have written class as follow:
protected void btnUpload_Click(object sender, ImageClickEventArgs e)
{
string strconn = null;
//string LogoURL = "";
//ExcelProduct
Uplaoad file_upload = new Uplaoad();
string savePath = Server.MapPath("Temp/");
string filepath = file_upload.UploadFile(File1, savePath);
DataTable dt = new DataTable();
if (System.IO.Path.GetExtension(File1.PostedFile.FileName).ToLower() == ".xls")
strconn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + savePath.ToString() + filepath.ToString() + ";Extended Properties=\"Excel 8.0;IMEX=1\"";
else
strconn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + savePath.ToString() + filepath.ToString() + ";Extended Properties=\"Excel 12.0;HDR=YES;\"";
OleDbConnection olConn = new OleDbConnection(strconn);
if (olConn.State != ConnectionState.Open)
{
olConn.Open();
}
OleDbDataAdapter da = new OleDbDataAdapter("SELECT * FROM [Sheet1$]", olConn);
da.Fill(dt);
}
当我上传excel 2003时,它的工作正常.但是在上传excel 2007时却出现了错误,任何机构都可以在这里帮助我
when i am uploading excel 2003 its working fine . But it gives error while uploading excel 2007 can any body help me out here
推荐答案
,olConn); da.Fill(dt); }
", olConn); da.Fill(dt); }
当我上传excel 2003时,它的工作正常.但是在上传excel 2007时出现错误,任何机构都可以在这里帮助我
when i am uploading excel 2003 its working fine . But it gives error while uploading excel 2007 can any body help me out here
protected void Button1_Click(object sender, EventArgs e)
{
UploadFileAndBind();
}
public void UploadFileAndBind()
{
//this will check if there is any file in the fileupload control or not.
if (FileUpload1.HasFile)
{
//Fetch the name of the file.
string strFileName = FileUpload1.PostedFile.FileName;
//Fetch the Extension of the file.
string extFile = Path.GetExtension(strFileName ).ToString().ToLower();
if (extFile == ".xls" || extFile == ".xlsx")
{
FileUpload1.SaveAs(Server.MapPath("~/Files/" + strFileName));
BindGrid (Server.MapPath ("~/Files/" + strFileName),extFile );
}
else
{
Label1.Text = "File You Are Uploading is not an Excel File.";
Label1.Visible = true;
}
}
}
public void BindGrid(string filePath, string filetype)
{
//variable and object creation
string constr = null;
string qry = null;
//check if the file is of MS Excel 2003 or Of MS Excel 2007 and set the Connection on that basis.
//Connection string for the MS Excel 2003.
if (filetype.Trim() == ".xls")
{
constr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\"";
}
//connection string for MS Excel 2007.
else if (filetype.Trim() == ".xlsx")
{
constr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\"";
}
//query to fetch the Excel Sheet.
qry = "select * from [Sheet1
" ; OleDbConnection con =新的OleDbConnection(constr); 如果(con.State == ConnectionState.Closed) { con.Open(); } OleDbCommand cmd =新的OleDbCommand(qry,con); OleDbDataAdapter odp =新的OleDbDataAdapter(cmd); DataSet ds = new DataSet(); odp.Fill(ds); odp.Dispose(); con.Close(); con.Dispose(); }
"; OleDbConnection con = new OleDbConnection(constr); if (con.State == ConnectionState.Closed) { con.Open(); } OleDbCommand cmd = new OleDbCommand(qry,con ); OleDbDataAdapter odp = new OleDbDataAdapter(cmd ); DataSet ds = new DataSet(); odp.Fill(ds); odp.Dispose(); con.Close(); con.Dispose(); }
这篇关于从Excel工作表上传数据的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!