• 本人综合的c#读取excel的例子

    [c#]
    post by bgaidu / 2009-8-24 15:50 Monday
    [code]<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>

    <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

    <html xmlns="http://www.w3.org/1999/xhtml">
    <head runat="server">
        <title>无标题页</title>
    </head>
    <body>
        <form id="form1" runat="server">
        <div>
        
            <asp:FileUpload ID="FileUpload1" runat="server" />
            <asp:Button ID="Button1" runat="server" onclick="Button1_Click" Text="Button" />
            <asp:TextBox ID="TextBox2" runat="server"></asp:TextBox>
            <asp:Button ID="Button2" runat="server" onclick="Button2_Click" Text="Button" />
        
        </div>
        <div>
            <asp:GridView ID="GridView1" runat="server">
            </asp:GridView>
            <br />
            <asp:Label ID="Label1" runat="server" Text="Label"></asp:Label>
        </div>
        </form>
    </body>
    </html>
    [/code]

    [code]using System;
    using System.Collections;
    using System.Configuration;
    using System.Data;
    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.IO;
    using System.Data.OleDb;
    using System.Data.SqlClient;

    public partial class _Default : System.Web.UI.Page
    {
        string strCon = ConfigurationManager.ConnectionStrings["rjbConnectionString"].ConnectionString;
        SqlConnection sqlcon;
        protected void Page_Load(object sender, EventArgs e)
        {

        }
        protected void Button1_Click(object sender, EventArgs e)
        {
            try
            {
                string filepath = FileUpload1.PostedFile.FileName.ToString();
                if (filepath == "")
                {
                    Response.Write("<script>alert('请选择上传EXCEL文件！');</script>");
                    return;
                }
                string ext = filepath.Substring(filepath.Length - 4).ToLower();
                if (ext != ".xls")
                {
                    Response.Write("<script>alert('非EXCEL文件！');</script>");
                    return;
                }

                string url = "UploadFiles/";
                string serverpath = Server.MapPath(url);
                DirectoryInfo dirInfo = new DirectoryInfo(serverpath);
                if (!dirInfo.Exists)
                {
                    Directory.CreateDirectory(serverpath);
                }

                string filename = FileUpload1.FileName;
                FileUpload1.SaveAs(serverpath + filename);
                string mystring = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source='" + serverpath + filename + "';Extended Properties=\"Excel 8.0;IMEX=1;\"";
                OleDbConnection conxls = new OleDbConnection(mystring);
                string sql = "SELECT * FROM [Sheet1$]";
                OleDbDataAdapter da = new OleDbDataAdapter(sql, conxls);
                DataSet ds = new DataSet();
                da.Fill(ds);

                if (ds.Tables[0].Rows.Count > 0)
                {
                    string error = string.Empty;
                    string strsql = "";

                    SqlConnection conn = new SqlConnection(strCon);
                    conn.Open();

                  


                    SqlCommand cmm = null;

                    for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                    {
                        strsql = "insert into t1 (t2,t3) "
                               + "values('" + ds.Tables[0].Rows[i].ItemArray[0].ToString().Trim() + "','" + ds.Tables[0].Rows[i].ItemArray[1].ToString().Trim() + "')";
                        try
                        {
                            cmm = new SqlCommand(strsql, conn);
                            cmm.ExecuteNonQuery();
                        }
                        catch
                        {
                            error += i.ToString() + ",";
                        }
                    }
          
                    conn.Close();
                    if (error == string.Empty)
                    {
                        Page.RegisterStartupScript("warnings", "<script>alert('数据导入成功');</script>");
                    }
                    else
                    {
                        error = error.Substring(0, error.Length - 1);
                        Page.RegisterStartupScript("warnings", "<script>alert('第" + error + "行数据导入失败，请检查数据源格式是否正确！');</script>");
                    }
                    bind();

                }
            }
            catch
            {
                Page.RegisterStartupScript("warnings", "<script>alert('文件类型不正确，或者文件正在被使用！');</script>");
            }
        }
        public void bind()
        {
            string sqlstr = "select  * from newspaper where newsID <> '' order by PostOrgan";
            sqlcon = new SqlConnection(strCon);
            SqlDataAdapter myda = new SqlDataAdapter(sqlstr, sqlcon);
            DataSet myds = new DataSet();
            sqlcon.Open();
            myda.Fill(myds, "tb_Member");
            GridView1.DataSource = myds;
            GridView1.DataKeyNames = new string[] { "id" };
            GridView1.DataBind();
            sqlcon.Close();
        }
        protected void Button2_Click(object sender, EventArgs e)
        {
            string filepath = FileUpload1.PostedFile.FileName.ToString();
            if (filepath == "")
            {
                Response.Write("<script>alert('请选择上传EXCEL文件！');</script>");
                return;
            }
            string ext = filepath.Substring(filepath.Length - 4).ToLower();
            if (ext != ".xls")
            {
                Response.Write("<script>alert('非EXCEL文件！');</script>");
                return;
            }
            string url = "UploadFiles/";
            string serverpath = Server.MapPath(url);
            
            DirectoryInfo dirInfo = new DirectoryInfo(serverpath);
            if (!dirInfo.Exists)
            {
                Directory.CreateDirectory(serverpath);
            }

            string filename =DateTime.Now.Year.ToString()+DateTime.Now.Month.ToString()+DateTime.Now.Day.ToString()+DateTime.Now.Hour.ToString()+DateTime.Now.Minute.ToString()+DateTime.Now.Second.ToString()+DateTime.Now.Millisecond.ToString()+FileUpload1.FileName;
            FileUpload1.SaveAs(serverpath + filename);

            string mypath = serverpath + filename;
             string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + mypath + ";" + "Extended Properties=Excel 8.0;";
                OleDbConnection conn = new OleDbConnection(strConn);

                conn.Open();

                //返回Excel的架构，包括各个sheet表的名称,类型，创建时间和修改时间等  
                DataTable dtSheetName = conn.GetOleDbSchemaTable (OleDbSchemaGuid.Tables, new object[] { null, null, null, "Table" });

                //包含excel中表名的字符串数组
                string[] strTableNames = new string[dtSheetName.Rows.Count];
                for (int k = 0; k < dtSheetName.Rows.Count; k++)
                {
                    strTableNames[k] = dtSheetName.Rows[k]["TABLE_NAME"].ToString();
                }

                OleDbDataAdapter myCommand = null;
                DataTable dt = new DataTable();

                //从指定的表明查询数据,可先把所有表明列出来供用户选择
                string strExcel = "select * from [" + strTableNames[0] + "]";
                myCommand = new OleDbDataAdapter(strExcel, strConn);
                dt = new DataTable();
                myCommand.Fill(dt);

             GridView1.DataSource = dt; //绑定到界面
             GridView1.DataBind();
             this.Label1.Text = dtSheetName.Rows.Count.ToString();
             KillExcelProcess();
        }
    }
    [/code]

    代码如下[file][/file]
    
    您对本文的评分:
    当前平均分: 0.0(0 次打分)
    « 最近写批量导入的页面 从网上总结了csv导入data table的代码 | C# 读取Excel中指定的Sheet名称或索引»

    引用地址:

    发表评论：