ASP.Net动态读取Excel文件

版权声明:转载原创文章请以超链接形式请注明原文章出处,尊重作者,尊重原创!


恰饭广告




实现效果:

读取excel文件

注意:页面分别拖拽一个FileUpload、Button1、Label1、GridView控件,并新建一个UploadedExcel文件夹

Default.aspx.cs代码:

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using System.IO;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
namespace WebApplication2
{
    public partial class WebForm1 : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
              delete();
        }
        protected void Button1_Click(object sender, EventArgs e)
        {
            OleDbConnection conn = new OleDbConnection();
            OleDbCommand cmd = new OleDbCommand();
            OleDbDataAdapter da = new OleDbDataAdapter();
            DataSet ds = new DataSet();
            string query = null;
            string connString = "";
            string strFileName = DateTime.Now.ToString("ddMMyyyy_HHmmss");
            //string strFileName = Path.GetFileNameWithoutExtension(FileUpload1.PostedFile.FileName);
            string strFileType = Path.GetExtension(FileUpload1.FileName).ToString().ToLower();
            if (strFileType == ".xls" || strFileType == ".xlsx")
            {
                FileUpload1.SaveAs(Server.MapPath("~/UploadedExcel/" + strFileName + strFileType));
            }
            else
            {
                return;
            }
            string strNewPath = Server.MapPath("~/UploadedExcel/" + strFileName + strFileType);
            if (strFileType.Trim() == ".xls")
            {
                connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + strNewPath + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\"";
            }
            else if (strFileType.Trim() == ".xlsx")
            {
                connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + strNewPath + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\"";
            }
            query = "SELECT * FROM [Sheet1$]";
            conn = new OleDbConnection(connString);
            if (conn.State == ConnectionState.Closed)
            {
                conn.Open();
            }
            try
            {
                cmd = new OleDbCommand(query, conn);
                da = new OleDbDataAdapter(cmd);
                ds = new DataSet();
                da.Fill(ds);
                GridView1.DataSource = ds.Tables[0];
                GridView1.DataBind();
                Label1.Text = "读取成功";
            }
            catch (Exception ex)
            {
                Label1.Text = "读取失败";
                Response.Write(ex);
            }
            finally
            {
                da.Dispose();
                conn.Close();
                conn.Dispose();
            }
        }
        //定时任务
        private void delete()
        {
            DirectoryInfo di = new DirectoryInfo(Server.MapPath("/UploadedExcel/"));
            FileInfo[] fi = di.GetFiles("*." + "*");
            DateTime dtNow = DateTime.Now;
            foreach (FileInfo tmpfi in fi)
            {
                TimeSpan ts = dtNow.Subtract(tmpfi.LastWriteTime);
                if (ts.Milliseconds > 100)
                {
                    tmpfi.Attributes = FileAttributes.Normal;
                    tmpfi.Delete();
                }
            }
        }
    }
}

注意:FileUpload控件并不能直接获取到文件的绝对路径(IE6及以下除外),只能通过上传到服务器再进行数据加载,然后再删除

确保安装了Microsoft.ACE.OLEDB.12.0驱动:http://download.microsoft.com/download/7/0/3/703ffbcb-dc0c-4e19-b0da-1463960fdcdb/AccessDatabaseEngine.exe

原文链接:https://www.idaobin.com/archives/1207.html

让我恰个饭吧.ヘ( ̄ω ̄ヘ)

支付宝 ——————- 微信
图片加载中图片加载中



恰饭广告

发表评论

您的电子邮箱地址不会被公开。 必填项已用*标注

4 × 5 =