相关Excel工作表 [英] Related to Excel sheets

问题描述

如何下​​载(打开)excel工作表模板(不加入我们的项目)，其中包含动态包含的字段，例如sno，name，age，然后用户填充相同的excel工作表并上传到具有相似字段的数据库中. ?

how to download (open)excel sheet Template (by not incuding in our project) with fields included dynamically eg sno , name , age, then user populate the same excel sheet and upload into database in atable of similar fields.???

推荐答案

尝试一下

首先从服务器下载Excel文件首先将要添加的任何字段添加为标头字段，然后将其保存到您的项目文件夹中，然后使用下面的代码下载时间即可.

context.Response.Redirect("Excel文件夹的服务器路径/generated.xlsx");


然后上传excel文件，我以下面的示例为例

在aspx页面中，添加以下代码

Try This

Download Excel File From Server Initially Make one excel with what ever field you want add as a header field then save it into your project folder then download time use below code

context.Response.Redirect("server path for excel folder/generated.xlsx");


Then upload excel file i give below example make it as your way

in aspx page add below code

<table style="padding: 5px; font-size: 11px;">
                                                                            <tbody>
                                                                                <tr>
                                                                                    <td>
                                                                                        <strong>Please Select Excel file ...</strong>
                                                                                    </td>
                                                                                </tr>
                                                                                <tr>
                                                                                    <td>
                                                                                        <div style="padding: 8px 5px; border-bottom: 1px solid #ccc;">
                                                                                            <asp:FileUpload ID="txtFilePath" runat="server"></asp:FileUpload>&nbsp;&nbsp;<asp:Button

                                                                                                ID="btnUpload" runat="server" Text="Upload" OnClick="btnUpload_Click" />
                                                                                            <br />
                                                                                            <asp:Label ID="lblMessage" runat="server" Visible="False" Font-Bold="True" ForeColor="#009933"></asp:Label>
                                                                                        </div>
                                                                                    </td>
                                                                                </tr>
                                                                                <tr>
                                                                                    <td>
                                                                                        &nbsp;</td>
                                                                                </tr>
                                                                                <tr>
                                                                                    <td>
                                                                                        <asp:GridView ID="grvExcelData" runat="server">
                                                                                            
                                                                                        </asp:GridView>
                                                                                    </td>
                                                                                </tr>
                                                                            </tbody>
                                                                        </table>

然后，aspx.cs页面按钮上传事件包括以下代码

在页面顶部添加头文件

Then aspx.cs page button upload event include this below code

add header file in top of page

using System.IO;
using System.Data.OleDb;

protected void btnUpload_Click(object sender, EventArgs e)
    {
        if ((txtFilePath.HasFile))
        {
            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("MM-dd-yyyy_HHmmss");
            string strFileType = System.IO.Path.GetExtension(txtFilePath.FileName).ToString().ToLower();

            //Check file type
            if (strFileType == ".xls" || strFileType == ".xlsx")
            {
                txtFilePath.SaveAs(Server.MapPath("Documents folder \\path for your folder\\" + strFileName + strFileType));
            }
            else
            {
                lblMessage.Text = "Only excel files allowed";
                lblMessage.ForeColor = System.Drawing.Color.Red;
                lblMessage.Visible = true;
                return;
            }

            string strNewPath = Server.MapPath("Documents folder \\folder path\\" + strFileName + strFileType);

            //Connection String to Excel Workbook
            if (strFileType.Trim() == ".xls")
            {
                connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + strNewPath + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1\"";
            }
            else if (strFileType.Trim() == ".xlsx")
            {

                connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + strNewPath + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=1\"";
            }


            query = "SELECT * FROM [sheet1

" ; // 创建连接对象 conn = 新 OleDbConnection(connString); // 打开连接 如果(conn.State == ConnectionState.Closed)conn.Open(); // 创建命令对象 cmd = 新 OleDbCommand(query，conn); da = 新 OleDbDataAdapter(cmd); ds = 新 DataSet(); da.Fill(ds); grvExcelData.DataSource = ds.Tables [ 0 ]; grvExcelData.DataBind(); con.Open(); OleDbDataReader odr = cmd.ExecuteReader(); while (odr.Read()) { // 在这里，我们从xsl工作表数据中检索 类别= odr.GetValue( 0 ).ToString(); Brand = odr.GetValue( 1 ).ToString(); 类型= odr.GetValue( 2 ).ToString(); 说明= odr.GetValue( 3 ).ToString(); // 在此，我们将数据插入数据库中 如果(类别<！span class ="code-string">" & ;& Brandid！= " ) { SqlCommand cmdinsert = 新 SqlCommand(" + Categoryid + '，' " +布兰迪(Brandid)+ '，' +类型+ '，'" +说明+ + DateTime.现在+ " ，骗局)； cmdinsert.ExecuteNonQuery(); } } } 其他 { lblMessage.Text = " ; lblMessage.ForeColor = System.Drawing.Color.Red; lblMessage.Visible = true ; } }

"; //Create the connection object conn = new OleDbConnection(connString); //Open connection if (conn.State == ConnectionState.Closed) conn.Open(); //Create the command object cmd = new OleDbCommand(query, conn); da = new OleDbDataAdapter(cmd); ds = new DataSet(); da.Fill(ds); grvExcelData.DataSource = ds.Tables[0]; grvExcelData.DataBind(); con.Open(); OleDbDataReader odr = cmd.ExecuteReader(); while (odr.Read()) { //Here we are retrive from xsl sheet data Category = odr.GetValue(0).ToString(); Brand = odr.GetValue(1).ToString(); Type = odr.GetValue(2).ToString(); Description = odr.GetValue(3).ToString(); //Here using this method we are inserting the data into the database if (Categoryid != "" && Brandid != "") { SqlCommand cmdinsert = new SqlCommand("insert into TypeMaster(BrandId,CategoryId,TypeName,TypeDescription,CreatorName,CreateDate)values('" + Categoryid + "','" + Brandid + "','" + Type + "','" + Description + "','VMichael','" + DateTime.Now + "')", con); cmdinsert.ExecuteNonQuery(); } } } else { lblMessage.Text = "Please select an excel file first"; lblMessage.ForeColor = System.Drawing.Color.Red; lblMessage.Visible = true; } }

希望这会帮助！！！任何疑问，请发送您的疑问让我知道

Hope This will hepl!!! any query Please send your Doubts Let me known

这篇关于相关Excel工作表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！