【C#】上传并读取导入.xls文件
通过上传Excel文件进行批量导入应该是信息系统里面常用的一个功能。
以下为用C#基于.net的实现代码（文末提供源码打包下载）：
页面代码：
<%@ Page Language="C#" AutoEventWireup="true CodeFile="Default.aspx.cs" Inherits="_Default" %> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "<a href="http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd</a>"> <html xmlns="<a href="http://www.w3.org/1999/xhtml">http://www.w3.org/1999/xhtml</a>"> <head runat="server"> <title>无标题页</title> </head> <body> <form id="form1" runat="server"> 请选择要导入的文件：<INPUT id="FileExcel" style="WIDTH: 300px" type="file" size="42" name="FilePhoto" runat="server" /> <asp:button id="btnImport" Text="导 入" CssClass="button" Runat="server" onclick="btnImport_Click"></asp:button><br /> <asp:label id="lblMessage" runat="server" Font-Bold="True" ForeColor="Red"></asp:label> </form> </body> </html>
后台代码：
using System;
using System.Configuration;
using System.Data;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.IO;
public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
}
/// <summary>
/// 上传Excel文件
/// </summary>
/// <param name="inputfile">上传的控件名</param>
/// <returns></returns>
private string UpLoadXls(System.Web.UI.HtmlControls.HtmlInputFile inputfile)
{
string orifilename = string.Empty;
string uploadfilepath = string.Empty;
string modifyfilename = string.Empty;
string fileExt = "" ;//文件扩展名
int fileSize = 0;//文件大小
try
{
if(inputfile.Value != string.Empty)
{
//得到文件的大小
fileSize = inputfile.PostedFile.ContentLength;
if(fileSize == 0 )
{
throw new Exception("导入的Excel文件大小为0，请检查是否正确！");
}
//得到扩展名
fileExt = inputfile.Value.Substring(inputfile.Value.LastIndexOf(".")+1);
if(fileExt.ToLower() != "xls")
{
throw new Exception("你选择的文件格式不正确，只能导入EXCEL文件！");
}
//路径
uploadfilepath = Server.MapPath("~/");
//新文件名
modifyfilename = System.Guid.NewGuid().ToString();
modifyfilename += "."+inputfile.Value.Substring(inputfile.Value.LastIndexOf(".")+1);
//判断是否有该目录
System.IO.DirectoryInfo dir = new System.IO.DirectoryInfo(uploadfilepath);
if (!dir.Exists)
{
dir.Create();
}
orifilename = uploadfilepath+modifyfilename;
//如果存在,删除文件
if(File.Exists(orifilename))
{
File.Delete(orifilename);
}
// 上传文件
inputfile.PostedFile.SaveAs(orifilename);
}
else
{
throw new Exception("请选择要导入的Excel文件!");
}
}
catch(Exception ex)
{
throw ex;
}
return orifilename;
}
protected void btnImport_Click(object sender, System.EventArgs e)
{
string filename = string.Empty;
try
{
filename = UpLoadXls(FileExcel);//上传XLS文件
string sql = String.Format("select * from {0}", Util_XLS.ConvertToSQLSheetName("Sheet1"));
DataSet ds = Util_XLS.SelectFromXLS(filename, sql);
if (ds.Tables.Count == 0)
{
lblMessage.Text = "上传的.xls文件中不存在Sheet1工作表，请打开确认";
DeleteFile(filename);
return;
}
DataTable dt = ds.Tables[0];
String[] cols = { "姓名", "院系", "服务岗位", "志愿者心语", "当选日期" };
foreach (String col in cols)
{
if (!dt.Columns.Contains(col))
{
lblMessage.Text = String.Format("上传的.xls文件Sheet1表中不存在“{0}”列", col);
DeleteFile(filename);
return;
}
}
if (dt.Rows.Count != 0)
Response.Write("以下为导入的数据，格式为：姓名,院系,服务岗位,志愿者心语,当选日期<br />");
foreach (DataRow dr in dt.Rows)
{
string name = dr["姓名"].ToString();
string department = dr["院系"].ToString();
string position = dr["服务岗位"].ToString();
string feeling = dr["志愿者心语"].ToString();
string date = dr["当选日期"].ToString();
if (name == "" || department == "" || position == "" || date == "")
continue;
else
Response.Write( String.Format("{0},{1},{2},{3},{4}<br />", name, department, position, feeling, date));
}
DeleteFile(filename);
lblMessage.Text = "数据导入成功！";
}
catch(Exception ex)
{
DeleteFile(filename);
lblMessage.Text=ex.Message;
}
}
/// <summary>
/// 删除文件
/// </summary>
/// <param name="filename">待删除的文件名</param>
private void DeleteFile(string filename)
{
if (filename != string.Empty && File.Exists(filename))
{
File.Delete(filename);
}
}
}
.xls文件处理辅助类代码：
using System;
using System.Data;
using System.Configuration;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.OleDb;
/// <summary>
/// XLS文件工具
/// </summary>
public class Util_XLS
{
/// <summary>
/// 执行查询
/// </summary>
/// <param name="ServerFileName">xls文件路径</param>
/// <param name="SelectSQL">查询SQL语句</param>
/// <returns>DataSet</returns>
public static DataSet SelectFromXLS(string ServerFileName, string SelectSQL)
{
string connStr = "Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source = '" + ServerFileName + "';Extended Properties=Excel 8.0";
OleDbConnection conn = new OleDbConnection(connStr);
OleDbDataAdapter da = null;
DataSet ds = new DataSet();
try
{
conn.Open();
da = new OleDbDataAdapter(SelectSQL, conn);
da.Fill(ds, "SelectResult");
}
catch (Exception e)
{
conn.Close();
throw e;
}
finally
{
conn.Close();
}
return ds;
}
/// <summary>
/// 获取工作表对应的SQL表名
/// </summary>
/// <param name="SheetName">工作表名</param>
/// <returns>SQL表名</returns>
public static string ConvertToSQLSheetName(string SheetName)
{
return "[" + SheetName + "$]";
}
/// <summary>
/// 执行无返回查询
/// </summary>
/// <param name="ServerFileName">xls文件路径</param>
/// <param name="QuerySQL">待执行的SQL语句</param>
public static void ExcuteNonQuery(string ServerFileName, string QuerySQL)
{
string connStr = "Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source = '" + ServerFileName + "';Extended Properties=Excel 8.0";
OleDbConnection conn = new OleDbConnection(connStr);
OleDbCommand cmd = new OleDbCommand(QuerySQL, conn);
try
{
conn.Open();
cmd.ExecuteNonQuery();
}
catch (Exception AnyError)
{
conn.Close();
throw AnyError;
}
finally
{
conn.Close();
}
}
}
源码打包下载：[download id="31" format="3"]
还没有人抢沙发呢~