使用组合框导出/导入到 Excel

Question

我仍在为我的场景而苦苦挣扎。我到处寻找解决方案，但找不到解决方案。我使用 C# 和 ASP.NET，需要将数据导出到 Excel 电子表格。

我有一个包含应用程序对象的应用程序列表。每个应用程序对象都有一个状态 ID。当我将这些应用程序导出到 Excel 时，我需要一个带有下拉/组合框的列。在此下拉/组合框中应包含数据库中所有状态的列表。然后我需要将状态选择为应用程序的状态 ID。

另一个问题也与上述场景有关。当用户下载 Excel 文档时，用户可以通过在下拉列表中选择不同的值来更改每个应用程序的状态。我需要上传此电子表格并循环遍历行并获取列的值以及下拉/组合框中所选项目的值。你会怎么做？这可能吗？

我可能不使用 COM 组件，所以我尝试使用 EPPlus 或 NPOI（不一定是这两个），但它们似乎不支持我的场景。我怎样才能实现我的场景？再次强调，它不必使用 EPPlus 和 NPOI。 将不胜感激：）

谢谢布伦丹

工作示例

Answer 1

您尝试过在 NPOI 中使用模板吗？当我想使用 NPOI 导出复杂的电子表格时，我创建一个 XLS 文档作为模板，并通过 C# 填充数据。

Answer 2

根本不需要宏。 C# 本身会为你做这件事。
这里我有一个演示，用于从 Excel（无论是 2003/2007 年）导入到 gridview 并将 gridview（编辑/更改）数据导出到新的 EXCEL 文件。尝试一下。 100% 有效

您需要做的是：在 Web 应用程序中创建一个名为“Files”的文件夹，并将 excel 文件保存在“Files”文件夹中。如果您的数据源（填充网格的位置）不是 Excel，则不需要上述文件夹。您可以在 btnImport_Click() 内编写自己的代码。
这是您的 .aspx 页面：

 <%@ Page Title="" Language="C#" MasterPageFile="~/Site.Master" AutoEventWireup="true" CodeBehind="ImportFromExcelToDatasetAndGridviewAndExport.aspx.cs" Inherits="ImportFromExcelToGridViewWebApp.ImportFromExcelToDatasetAndGridviewAndExport" %>
<asp:Content ID="Content1" ContentPlaceHolderID="HeadContent" runat="server">
</asp:Content>
<asp:Content ID="Content2" ContentPlaceHolderID="MainContent" runat="server">

    <div>
        <b>Please Select Excel File : </b>
        <asp:FileUpload ID="fileuploadExcel" runat="server" />  
        <asp:Button ID="btnImport" runat="server" Text="Import From Excel" OnClick="btnImport_Click" />
        <br />
        <asp:Label ID="lblMessage" runat="server" Visible="False" Font-Bold="True" ForeColor="#009933"></asp:Label><br />

        <asp:GridView ID="grvExcelData" runat="server"  AutoGenerateColumns="false"
            onrowdatabound="GridView2_RowDataBound">
            <HeaderStyle BackColor="#df5015" Font-Bold="true" ForeColor="White" />
            <Columns>
                 <asp:TemplateField ItemStyle-Width = "150px"  HeaderText = "UserName">
                    <ItemTemplate>
                        <asp:TextBox ID="txtUserName" runat="server"  
                                     CssClass="css2"  Text='<%# Eval("UserName")%>'></asp:TextBox>
                    </ItemTemplate>
                    <EditItemTemplate>
                        <asp:TextBox ID="txtUserName" runat="server" 
                                     CssClass="css2"  Text='<%# Eval("UserName")%>'>
                        </asp:TextBox>
                    </EditItemTemplate>  
                </asp:TemplateField>
                <asp:TemplateField ItemStyle-Width = "150px"  HeaderText = "Education">
                    <ItemTemplate>
                        <asp:TextBox ID="txtEducation" runat="server"  
                                     CssClass="css2"  Text='<%# Eval("Education")%>'></asp:TextBox>
                    </ItemTemplate>
                    <EditItemTemplate>
                        <asp:TextBox ID="txtEducation" runat="server" 
                                     CssClass="css2"  Text='<%# Eval("Education")%>'>
                        </asp:TextBox>
                    </EditItemTemplate>  
                </asp:TemplateField>
                <asp:TemplateField ItemStyle-Width = "150px"  HeaderText = "Location">
                    <ItemTemplate>
                        <asp:TextBox ID="txtLocation" runat="server"  
                                     CssClass="css2"  Text='<%# Eval("Location")%>'></asp:TextBox>
                    </ItemTemplate>
                    <EditItemTemplate>
                        <asp:TextBox ID="txtLocation" runat="server" 
                                     CssClass="css2"  Text='<%# Eval("Location")%>'>
                        </asp:TextBox>
                    </EditItemTemplate>  
                </asp:TemplateField>
                <asp:TemplateField HeaderText="Select">
                    <ItemTemplate>
                       <asp:DropDownList ID="DrdDatabase" Width="100px" runat="server">
                       </asp:DropDownList>
                    </ItemTemplate>
                    <EditItemTemplate>
                        <asp:DropDownList ID="DrdDatabase" Width="100px" runat="server">
                       </asp:DropDownList>
                    </EditItemTemplate> 
                </asp:TemplateField>
            </Columns>
        </asp:GridView>
        <asp:Button ID="btnExport" runat="server" Text="Export To Excel" OnClick="btnExport_Click" />
    </div>
</asp:Content>

这是您的隐藏代码。

    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Web;
    using System.Web.UI;
    using System.Web.UI.WebControls;
    using System.Data;
    using System.IO;
    using System.Data.OleDb;
    using System.Collections;

namespace ImportFromExcelToGridViewWebApp
{
    public partial class ImportFromExcelToDatasetAndGridviewAndExport : System.Web.UI.Page
    {
        private ArrayList ExcelData
        {
            get
            {
                object excel = Session["dropdownvalue"];
                if (excel == null) Session["dropdownvalue"] = new ArrayList();
                return (ArrayList)Session["dropdownvalue"];
            }
            set
            {
                Session["dropdownvalue"] = value;
            }
        }

        protected void GridView2_RowDataBound(object sender, GridViewRowEventArgs e)
        {
            if (e.Row.RowType == DataControlRowType.DataRow)
            {
                DropDownList ddl = (DropDownList)e.Row.FindControl("DrdDatabase");
                foreach (string colName in ExcelData)
                    ddl.Items.Add(new ListItem(colName));
            }
        }

        protected void btnImport_Click(object sender, EventArgs e)
        {
            ArrayList alist = new ArrayList();
            string connString = "";
            string strFileType = Path.GetExtension(fileuploadExcel.FileName).ToLower();
            string fileBasePath = Server.MapPath("~/Files/");
            string fileName = Path.GetFileName(this.fileuploadExcel.FileName);
            string fullFilePath = fileBasePath + fileName;

            //Connection String to Excel Workbook
            if (strFileType.Trim() == ".xls")
            {
                connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fullFilePath +
                              ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1\"";
            }
            else if (strFileType.Trim() == ".xlsx")
            {
                connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fullFilePath +
                             ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=1\"";
            }
            if (fileuploadExcel.HasFile)
            {
                string query = "SELECT [UserName],[Education],[Location] FROM [Sheet1$]";
                OleDbConnection conn = new OleDbConnection(connString);
                if (conn.State == ConnectionState.Closed)
                    conn.Open();
                OleDbCommand cmd = new OleDbCommand(query, conn);
                OleDbDataAdapter da = new OleDbDataAdapter(cmd);
                DataSet ds = new DataSet();
                da.Fill(ds);
                //Assigning dataset table data to GridView Control
                Session["griddata"] = ds.Tables[0];

                System.Data.DataTable dtt = (System.Data.DataTable)Session["griddata"];  //griddata is the gridview data from another page        
                var res = (from f in dtt.AsEnumerable()
                           select f.Field<string>("Location")
                    );


                foreach (string s in res.AsEnumerable())
                {
                    alist.Add(s);
                }

                Session["dropdownvalue"] = alist;

                grvExcelData.DataSource = Session["griddata"];
                grvExcelData.DataBind();

                da.Dispose();
                conn.Close();
                conn.Dispose();
            }
        }

        protected void btnExport_Click(object sender, EventArgs e)
        {
            System.Data.DataTable dtExcel = new DataTable();

            dtExcel.Columns.Add("UserName", typeof(string));
            dtExcel.Columns.Add("Education", typeof(string));
            dtExcel.Columns.Add("Location", typeof(string));
            dtExcel.Columns.Add("Select", typeof(string));
            foreach (GridViewRow row in grvExcelData.Rows)
            {
                string UserName = ((TextBox)row.FindControl("txtUserName")).Text;
                string Education = ((TextBox)row.FindControl("txtEducation")).Text;
                string Location = ((TextBox)row.FindControl("txtLocation")).Text;
                string Location2 = ((DropDownList)row.FindControl("DrdDatabase")).Text;
                DataRow dr = dtExcel.NewRow();
                dr["UserName"] = UserName;
                dr["Education"] = Education;
                dr["Location"] = Location;
                dr["Select"] = Location2;
                dtExcel.Rows.Add(dr);
            }

            CreateWorkbook(dtExcel, Server.MapPath("~/DownlodedFiles/Excel.xls"));
        }

        public static void CreateWorkbook(System.Data.DataTable dtExcelData, String path)
        {
            int rowindex = 0;
            int columnindex = 1;

            Microsoft.Office.Interop.Excel.Application app;
            Microsoft.Office.Interop.Excel.Worksheet wksheet;
            Microsoft.Office.Interop.Excel.Workbook wkbook;
            app = new Microsoft.Office.Interop.Excel.Application();
            app.Visible = false;
            wkbook = app.Workbooks.Add(true);
            wksheet = (Microsoft.Office.Interop.Excel.Worksheet)wkbook.ActiveSheet;
            try
            {
                for (int i = 0; i < dtExcelData.Columns.Count; i++)
                {
                    wksheet.Cells[1, i + 1] = dtExcelData.Columns[i].ColumnName;
                }

                foreach (DataRow row in dtExcelData.Rows)
                {
                    rowindex++;
                    columnindex = 0;
                    foreach (DataColumn col in dtExcelData.Columns)
                    {
                        columnindex++;
                        wksheet.Cells[rowindex + 1, columnindex] = row[col.ColumnName];
                    }
                }

                app.Visible = true;
            }
            catch (Exception ex1)
            {

            }
            app.UserControl = true;
        }
    }
}