【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();
            }
        } 
    
    }

    源码打包下载:C#实现.xls文件的上传读取源码



    本博客所有文章如无特别注明均为原创。
    复制或转载请以超链接形式注明转自枫芸志,原文地址《【C#】上传并读取导入.xls文件
    标签:
    分享:

还没有人抢沙发呢~

无觅相关文章插件,快速提升流量