将网格视图导出到.xls
本文关键字：xls 网格 视图 | 更新日期: 2023-09-27 18:31:40
我正在开发一个具有网格视图控件的网页。我需要将数据转换为.xls格式。我能够创建.xls文件并传输数据，但问题是我需要在 excel 工作表的背景中显示网格单元格。现在，它只显示没有网格单元格的空白背景。否则，网格视图将正常传输。由于此问题，打印.xls文件是一个问题。具有更多列的表格不会压缩，而是打印在 2-3 页上。我的代码如下：
public static void ExportToXLS(string fileName, GridView gv,string companyName,string reportTitle , string period)
{
//For writing to XLS file
HttpContext.Current.Response.Clear();
HttpContext.Current.Response.AddHeader("content-disposition", string.Format("attachment; filename={0}", fileName));
HttpContext.Current.Response.ContentType = "application/ms-excel";
using (StringWriter sw = new StringWriter())
{
using (HtmlTextWriter htw = new HtmlTextWriter(sw))
{
Table tableReport = new Table();
tableReport.GridLines = gv.GridLines;
// add the header row to the table
if (gv.HeaderRow != null)
{
ReportList.PrepareControlForExport(gv.HeaderRow);
tableReport.Rows.Add(gv.HeaderRow);
}
// add each of the data rows to the table
foreach (GridViewRow row in gv.Rows)
{
ReportList.PrepareControlForExport(row);
tableReport.Rows.Add(row);
}
// add the footer row to the table
if (gv.FooterRow != null)
{
ReportList.PrepareControlForExport(gv.FooterRow);
tableReport.Rows.Add(gv.FooterRow);
}
//Takes value of company name
System.Web.UI.WebControls.Label labelCompany = new System.Web.UI.WebControls.Label();
labelCompany.Text = companyName;
labelCompany.Font.Bold = true;
//Takes value of report title
System.Web.UI.WebControls.Label labelReport = new System.Web.UI.WebControls.Label();
labelReport.Text = reportTitle;
labelReport.Font.Bold = true;
//Takes value of report period
System.Web.UI.WebControls.Label labelPeriod = new System.Web.UI.WebControls.Label();
labelPeriod.Text = period;
// render the htmlwriter into the response
htw.Write("<center>");
labelCompany.RenderControl(htw);
htw.Write("<br/>");
labelReport.RenderControl(htw);
htw.Write("<br/>");
labelPeriod.RenderControl(htw);
htw.Write("</center>");
htw.Write("<br/>");
tableReport.RenderControl(htw);
HttpContext.Current.Response.Write(sw.ToString());
HttpContext.Current.Response.End();
}
}
}
有什么建议吗？
参考：导出网格
试试这个：
protected void btnExportExcel_Click(object sender, EventArgs e)
{
Response.Clear();
Response.Buffer = true;
Response.AddHeader("content-disposition",
"attachment;filename=GridViewExport.xls");
Response.Charset = "";
Response.ContentType = "application/vnd.ms-excel";
StringWriter sw = new StringWriter();
HtmlTextWriter hw = new HtmlTextWriter(sw);
grdExport.AllowPaging = false;
oMailing.GetData(out ODs);
grdExport.DataSource = ODs;
grdExport.DataBind();
//Change the Header Row back to white color
grdExport.HeaderRow.Style.Add("background-color", "#FFFFFF");
//Apply style to Individual Cells
grdExport.HeaderRow.Cells[0].Style.Add("background-color", "green");
grdExport.HeaderRow.Cells[1].Style.Add("background-color", "green");
grdExport.HeaderRow.Cells[2].Style.Add("background-color", "green");
grdExport.HeaderRow.Cells[3].Style.Add("background-color", "green");
grdExport.HeaderRow.Cells[4].Style.Add("background-color", "green");
grdExport.RenderControl(hw);
//style to format numbers to string
string style = @"<style> .textmode { mso-number-format:'@; } </style>";
Response.Write(style);
Response.Output.Write(sw.ToString());
Response.Flush();
Response.End();
}
所有的答案都会得到你想要的 - 你只是缺少格式化部分。
归根结底，您真正创建的是 Excel 可以读取的 HTML 文件（带有 HTML 表格）。@BhaskarreddyMule中的 RESPONSE 标头是"强制"客户端将文件视为"xls"文件以及它是否有 Excel 运行并打开它的原因（但最重要的是它不是真正的"本机"Excel 文件。
现在这已经不碍事了，用HTML思考。像在 HTML 中一样设置列、行和文本内容的样式。这就是您控制格式的方式（即老式的"nowrap"以防止包装单元格内容、字体大小等）。
我已经有一段时间没有这样做了（当我需要这样做时，我已经转向 Excel XML 和 VB.Net XML 文字），所以我不确定您对RenderControl的控制级别有多大......或者，如果您必须走得更远，"老派"并从头开始构建HTML表字符串.....
首先，请参考： 如何将嵌套网格视图导出到带有子网格线上网格线的 excel/word，希望它能帮助您解决问题。
参考： 如何在控制台类型应用程序中导出网格视图以 excel
？您可以参考以下链接中的"导出到 Excel"控件。它是一个自定义控件。希望它能帮助你。
http://exporttoexcel.codeplex.com/
样式导出网格：
protected void btnExportExcel_Click(object sender, EventArgs e)
{
Response.Clear();
Response.Buffer = true;
Response.AddHeader("content-disposition",
"attachment;filename=GridViewExport.xls");
Response.Charset = "";
Response.ContentType = "application/vnd.ms-excel";
StringWriter sw = new StringWriter();
HtmlTextWriter hw = new HtmlTextWriter(sw);
grdExport.AllowPaging = false;
oMailing.GetData(out ODs);
grdExport.DataSource = ODs;
grdExport.DataBind();
//Change the Header Row back to white color
grdExport.HeaderRow.Style.Add("background-color", "#FFFFFF");
//Apply style to Individual Cells
grdExport.HeaderRow.Cells[0].Style.Add("background-color", "green");
grdExport.HeaderRow.Cells[1].Style.Add("background-color", "green");
grdExport.HeaderRow.Cells[2].Style.Add("background-color", "green");
grdExport.HeaderRow.Cells[3].Style.Add("background-color", "green");
grdExport.HeaderRow.Cells[4].Style.Add("background-color", "green");
for (int i = 0; i < grdExport.Rows.Count; i++)
{
GridViewRow row = grdExport.Rows;
//Change Color back to white
row.BackColor = System.Drawing.Color.White;
//Apply text style to each Row
row.Attributes.Add("class", "textmode");
//Apply style to Individual Cells of Alternating Row
if (i % 2 != 0)
{
row.Cells[0].Style.Add("background-color", "#C2D69B");
row.Cells[1].Style.Add("background-color", "#C2D69B");
row.Cells[2].Style.Add("background-color", "#C2D69B");
row.Cells[3].Style.Add("background-color", "#C2D69B");
row.Cells[4].Style.Add("background-color", "#C2D69B");
}
}
grdExport.RenderControl(hw);
//style to format numbers to string
string style = @"<style> .textmode { mso-number-format:'@; } </style>";
Response.Write(style);
Response.Output.Write(sw.ToString());
Response.Flush();
Response.End();
}
Response.Clear();
Response.Buffer = true;
Response.AddHeader("content-disposition",attachment;filename=GridViewExport.xls");
Response.Charset = "";
Response.ContentType = "application/vnd.ms-excel";
StringWriter sw = new StringWriter();
HtmlTextWriter hw = new HtmlTextWriter(sw);
我尝试导出到excel，似乎您首先需要创建一个使用部分Excel = using Microsoft.Office.Interop.Excel，当您单击按钮时，只需创建一个具有工作簿和工作表属性的excel类，然后使用Gridview.Row[].cell[].Text.的gridview属性，您可以将网格视图中的每个值动态存储到工作表中，然后将其写入FILE....