<table width="100%" bgcolor="#71B1F8" border="0" cellpadding="0" cellspacing="1"
style="border-style: double; border-width: 5px;">
<tr>
<td height="25" width="15%" background="../images/kuang_11.gif" bgcolor="#F7F7FF"
style="padding-left: 5px">
<strong>Excel文件:</strong>
</td>
<td background="../images/kuang_11.gif" width="75%" style="text-align: left" bgcolor="#F7F7FF">
<asp:FileUpload ID="fuInsertDistribute" runat="server" Width="400px" />
</td>
</tr>
<tr>
<td background="../images/kuang_11.gif" bgcolor="#F7F7FF" colspan="2" align="right">
<asp:ImageButton ID="ImageButton1" ImageUrl="~/images/SignBtn/ok.gif" runat="server"
OnClick="ImageButton1_Click" style="height: 23px" />
<%--<asp:Image ID="imgOk" runat="server" Style="cursor: hand; border: 0" ImageUrl="~/images/SignBtn/ok.gif"
οnclick="InExcel()" />--%>
</td>
</tr>
</table>
后台:
protected void ImageButton1_Click(object sender, ImageClickEventArgs e)
{
if (fuInsertDistribute.PostedFile.FileName == "")
{
return;
}
// /获取文件信息
string FileName = fuInsertDistribute.PostedFile.FileName;
string file_str = "文件名称:" + FileName + "<br>";
file_str = "文件类型:" + fuInsertDistribute.PostedFile.ContentType + "<br>";
file_str = "文件长度:" + fuInsertDistribute.PostedFile.ContentLength.ToString() + "KB<br>";
DirectoryInfo upDir = new DirectoryInfo(Server.MapPath("../JS"));
if (!upDir.Exists)
{
upDir.Create();
}
//上传文件到服务器
FileName = FileName.Substring(FileName.LastIndexOf("\\") + 1);// 取出文件名的路径(不包括文件的名称)
string endFileName = DateTime.Now.ToString("yyyyMMddHHmmss") + "_" + FileName;
string upload_file = Server.MapPath("../JS/") + endFileName;
//取出服务器虚拟路径,存储上传文件
fuInsertDistribute.PostedFile.SaveAs(upload_file);//开始上传文件
string docmentName = FileName;
string updateTime = DateTime.Now.ToString();
string msg = "";
DataTable dt = ReadExcelData(upload_file, out msg);
}
private static DataTable ReadExcelData(string fileFullName, out string ErrorMassage)
{
string excelConnStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1;\"";
string excelSqlStr = "select * from [{0}]";
OleDbConnection oleCn = new OleDbConnection();
try
{
string currConnStr = string.Format(excelConnStr, fileFullName);//连接字符串
oleCn.ConnectionString = currConnStr;
DataTable dt = new DataTable();
oleCn.Open();
string excelBookName = oleCn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, null).Rows[0][2].ToString();
if (excelBookName != string.Empty && excelBookName.Trim() != "")
{
string currSqlStr = string.Format(excelSqlStr, excelBookName);//sql字符串
OleDbCommand oleCmd = new OleDbCommand(currSqlStr, oleCn);
OleDbDataAdapter oleDda = new OleDbDataAdapter(oleCmd);
oleDda.Fill(dt);
}
oleCn.Close();
ErrorMassage = string.Empty;
return dt;
}
catch (Exception ex)
{
string errorMsg = "Excel文件格式不对,请尝试用excel打开该文件检查内容并另存一下,确保成为正确的excel文件,再重新上传";
ErrorMassage = errorMsg + " " + ex.Message;
oleCn.Close();
return null;
}
}