导出gridview数据为xlsx格式
本文关键字：xlsx 格式 数据 gridview 导出 | 更新日期: 2023-09-27 18:16:37
我有一个大的gridview作为web应用程序的一部分，它结合了绑定字段和模板字段，并定义了dataformatstring，定义了如何在单元格上表示值。我很难将所有这些转换为数据表，以便我可以将它们写入excel表格。
将gridview直接转换为excel迫使我使用String Write和Html Write，它们只产生。xls格式。我们正在考虑将所有excel下载选项标准化为。xlsx格式。这是我到目前为止尝试的
dt = gvInvoicesEX.DataSource as DataTable;
DataSource属性由于某种原因返回null。我还尝试了正常的循环内循环技术，以遍历gridview中的每个单元格并将其复制到数据表中，但我只是得到一个充满空白单元格的数据表。
for (int i = 0; i < gvInvoicesEX.Rows.Count; i++)
{
DataRow dr = dt.NewRow();
for (int j = 1; j < gvInvoicesEX.Columns.Count; j++)
{
gvInvoicesEX.Rows[i].Cells[j].Attributes.Add("class", "text");
dr[j - 1] = gvInvoicesEX.Rows[i].Cells[j].Text;
}
dt.Rows.Add(dr);
}
using (var wb = new XLWorkbook())
{
DataTable dt = GetDataTableFromGridView();
string fileName = "BillingDetail" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xlsx";
wb.Worksheets.Add(dt);
Response.Clear();
Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
Response.AddHeader("content-disposition", "attachment;filename=" + fileName);
using (var ms = new System.IO.MemoryStream())
{
wb.SaveAs(ms);
ms.WriteTo(Response.OutputStream);
ms.Close();
}
Response.End();
}
上面有什么我可以改变的，使这一切顺利进行吗?
你应该直接处理数据源中的数据，而不是试图在gridview中处理数据并将其复制出来。Gridview使用反射和许多真正的高开销过程，因此通过直接从数据源迭代和格式化，您将跳过很多开销，并加快您的工作流程。
对于你用来加载数据到gridview的方法，无论是调用存储过程还是其他，你应该将数据加载到强类型对象列表中并迭代这些来生成excel表格。
我也强烈建议，除非你在你的表格中使用复杂的公式或类似的，只允许标准化下载作为.csv。Excel可以很容易地读取这种格式，而且它比使用.xlsx驱动程序更开放，更容易使用。
Gridview导出到XLSX:-
我使用名为EPPlus.dll的库进行导出。只需从链接http://epplus.codeplex.com/下载EPPlus.dll，并在项目中将命名空间命名为using OfficeOpenXml;。
<asp:GridView ID="grdDisplay" ShowHeaderWhenEmpty="True" runat="server" BackColor="White" BorderColor="#999999" BorderStyle="None" BorderWidth="1px" CellPadding="3" GridLines="Vertical"
OnRowCommand="grdDisplay_RowCommand" AutoGenerateColumns="false" AllowPaging="true" AllowSorting="true" DataKeyNames="ID" OnPageIndexChanging="grdDisplay_PageIndexChanging"
PageSize="10" OnSorting="Sorting" >
<AlternatingRowStyle BackColor="#DCDCDC" />
<FooterStyle BackColor="#DDE8F0" ForeColor="Black" />
<HeaderStyle BackColor="#DDE8F0" Font-Bold="True" ForeColor="Black"/>
<PagerSettings Mode="NextPrevious" NextPageText="Next" PreviousPageText="Previous" />
<PagerStyle BackColor="#DDE8F0" ForeColor="Black" HorizontalAlign="Center" />
<RowStyle BackColor="#EEEEEE" ForeColor="Black" />
<SelectedRowStyle BackColor="#008A8C" Font-Bold="True" ForeColor="White" />
<SortedAscendingCellStyle BackColor="#F1F1F1" />
<SortedAscendingHeaderStyle BackColor="#0000A9" />
<SortedDescendingCellStyle BackColor="#CAC9C9" />
<SortedDescendingHeaderStyle BackColor="#000065" />
<Columns>
<asp:TemplateField>
<ItemTemplate>
<asp:ImageButton ID="imgEdit" Width="25px" Height="25px" runat="server" CommandName="Edit" CommandArgument='<%#Eval("ID")%>' ImageUrl="../Images/edit.png" />
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField>
<ItemTemplate>
<asp:ImageButton ID="imgSearch" Width="25px" Height="25px" runat="server" CommandName="Search" CommandArgument='<%#Eval("ID")%>' ImageUrl="../Images/search.png" />
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField>
<ItemTemplate>
<asp:ImageButton ID="imgHistory" Width="25px" Height="25px" runat="server" CommandName="History" CommandArgument='<%#Eval("ID")%>' ImageUrl="../Images/history2.png" />
</ItemTemplate>
</asp:TemplateField>
<asp:BoundField DataField="ID" HeaderText="ID" ItemStyle-Wrap="false" SortExpression="ID">
<ItemStyle Wrap="False"></ItemStyle>
</asp:BoundField>
<asp:BoundField DataField="col2" HeaderText="col2" ItemStyle-Wrap="false" SortExpression="col2">
<ItemStyle Wrap="False"></ItemStyle>
</asp:BoundField>
<asp:BoundField DataField="col3" HeaderText="col3" ItemStyle-Wrap="false" SortExpression="col3">
<ItemStyle Wrap="False"></ItemStyle>
</asp:BoundField>
<asp:BoundField DataField="col4" HeaderText="col4" ItemStyle-Wrap="false" SortExpression="col4">
<ItemStyle Wrap="False"></ItemStyle>
</asp:BoundField>
<asp:BoundField DataField="col5" HeaderText="col5" ItemStyle-Wrap="false" SortExpression="col5">
<ItemStyle Wrap="False"></ItemStyle>
</asp:BoundField>
<asp:BoundField DataField="col6" HeaderText="col6" ItemStyle-Wrap="false" SortExpression="col6">
<ItemStyle Wrap="False"></ItemStyle>
</asp:BoundField>
<asp:BoundField DataField="col7" HeaderText="col7" ItemStyle-Wrap="false" SortExpression="col7">
<ItemStyle Wrap="False"></ItemStyle>
</asp:BoundField>
<asp:BoundField DataField="col8" HeaderText="col8" ItemStyle-Wrap="false" SortExpression="col8">
<ItemStyle Wrap="False"></ItemStyle>
</asp:BoundField>
<asp:BoundField DataField="col9" HeaderText="col9" ItemStyle-Wrap="false" SortExpression="col9">
<ItemStyle Wrap="False"></ItemStyle>
</asp:BoundField>
<asp:BoundField DataField="col10" HeaderText="col10" ItemStyle-Wrap="false" SortExpression="col10">
<ItemStyle Wrap="False"></ItemStyle>
</asp:BoundField>
<asp:BoundField DataField="col11" HeaderText="col11" ItemStyle-Wrap="false" SortExpression="col11">
<ItemStyle Wrap="False"></ItemStyle>
</asp:BoundField>
<asp:BoundField DataField="col12" HeaderText="col12" ItemStyle-Wrap="false" SortExpression="col12">
<ItemStyle Wrap="False"></ItemStyle>
</asp:BoundField>
<asp:BoundField DataField="col13" HeaderText="col13" ItemStyle-Wrap="false" SortExpression="col13">
<ItemStyle Wrap="False"></ItemStyle>
</asp:BoundField>
<asp:BoundField DataField="col14" HeaderText="col14" ItemStyle-Wrap="false" SortExpression="col14">
<ItemStyle Wrap="False"></ItemStyle>
</asp:BoundField>
<asp:BoundField DataField="col15" HeaderText="col15" ItemStyle-Wrap="false" SortExpression="col15">
<ItemStyle Wrap="False"></ItemStyle>
</asp:BoundField>
</Columns>
<EmptyDataTemplate>No Records Available..Please refine your search</EmptyDataTemplate>
</asp:GridView>
按钮出口:-
<asp:Button ID="btnExportToExcel" Text="Export To Excel" runat="server" AutoPostBack="true" OnClick="btnExportToExcel_Click" />
-
protected void btnExportToExcel_Click(object sender, EventArgs e)
{
try
{
string mySqlQuery = GenerateQuery();
List<Product> myGridData = GetGridData(mySqlQuery);
grdDisplay.DataSource = myGridData;
grdDisplay.DataBind();
ExcelPackage excel = new ExcelPackage();
var workSheet = excel.Workbook.Worksheets.Add("Product");
var totalCols = grdDisplay.Rows[0].Cells.Count;
var totalRows = myGridData.Count;
var headerRow = grdDisplay.HeaderRow;
///Ignoring the first three columns, Since first three columns are edit, search and history
for (var i = 3; i < totalCols; i++)
{
workSheet.Cells[1, i].Value = grdDisplay.Columns[i].HeaderText;
}
for (var j = 1; j <= totalRows; j++)
{
///Ignoring the first three columns, Since first three columns are edit, search and history
for (var i = 3; i < totalCols; i++)
{
var product = myGridData.ElementAt(j - 1);
string hdrTextVal = product.GetType().GetProperty(grdDisplay.Columns[i].HeaderText).Name;
if(hdrTextVal == "col5" || hdrTextVal == "col6")
{
workSheet.Cells[j + 1, i].Style.Numberformat.Format = "MM/DD/YYYY";
}
if (hdrTextVal == "col7" || hdrTextVal == "col8")
{
workSheet.Cells[j + 1, i].Style.Numberformat.Format = "hh:mm";
}
workSheet.Cells[j + 1, i].Value = product.GetType().GetProperty(grdDisplay.Columns[i].HeaderText).GetValue(product, null);
}
}
using (var memoryStream = new MemoryStream())
{
Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
Response.AddHeader("content-disposition", "attachment; filename=Product-Export.xlsx");
excel.SaveAs(memoryStream);
memoryStream.WriteTo(Response.OutputStream);
Response.Flush();
Response.End();
}
}
catch (ThreadAbortException exc)
{
}
catch (Exception ex)
{
}
}
: -
https://www.mikesdotnetting.com/article/278/a-better-way-to-export-gridviews-to-excel
我是这样做的，通过单击按钮将gridview导出为xslx格式。不需要再次从数据源获取。就像
一样遍历gridview protected void btnExport_Click(object sender, EventArgs e)
{
if (jvGrid.Rows.Count > 0)
{
Response.Clear();
Response.Buffer = true;
Response.AddHeader("content-disposition", "attachment;filename=Jv entries_" + cboQuater.SelectedValue +" qtr " + cboYear.SelectedValue + ".xlsx");
Response.Charset = "";
Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
StringWriter sw = new StringWriter();
jvGrid.HeaderRow.Style.Add("background-color", "#fff");
jvGrid.HeaderRow.Style.Add("color", "#000");
jvGrid.HeaderRow.Style.Add("font-weight", "bold");
for (int i = 0; i < jvGrid.Rows.Count; i++)
{
GridViewRow grow = jvGrid.Rows[i];
grow.BackColor = System.Drawing.Color.White;
grow.Attributes.Add("class", "textmode");
}
using (HtmlTextWriter hw = new HtmlTextWriter(sw))
{
jvGrid.RenderControl(hw);
Response.Output.Write(sw.ToString());
Response.Flush();
Response.End();
}
}
}
请在您的代码中包含System.IO命名空间