在Excel c#中导出GridView数据时遇到的问题
本文关键字：数据 遇到 问题 GridView Excel | 更新日期: 2023-09-27 18:04:47
在excel中导出gridview数据时遇到问题。它是导出整个页面，而不是Gridview数据。
My Code如下:
Response.Clear();
Response.Buffer = true;
Response.ClearContent();
Response.ClearHeaders();
Response.Charset = "";
StringWriter strwritter = new StringWriter();
HtmlTextWriter htmlwritter = new HtmlTextWriter(strwritter);
Response.Cache.SetCacheability(HttpCacheability.NoCache);
Response.ContentType = "application/ms-excel";
Response.AddHeader("Content-Disposition", string.Format("attachment; filename={0}", "DSR"+DateTime.Now.ToString("dd-MM-yyyy")+".xls"));
GridView1.GridLines = GridLines.Both;
GridView1.HeaderStyle.Font.Bold = true;
GridView1.RenderControl(htmlwritter);
Response.Write(strwritter.ToString());
Response.End();
你可以试试这样做。简单明了。
Response.Clear();
Response.Buffer = true;
Response.ClearContent();
Response.ClearHeaders();
Response.Charset = "";
StringWriter strwritter = new StringWriter();
HtmlTextWriter htmlwritter = new HtmlTextWriter(strwritter);
Response.Cache.SetCacheability(HttpCacheability.NoCache);
StringWriter strwritter = new StringWriter();
HtmlTextWriter htmlwritter = new HtmlTextWriter(strwritter);
Response.Cache.SetCacheability(HttpCacheability.NoCache);
Response.ContentType = "application/ms-excel";
Response.AddHeader("Content-Disposition", string.Format("attachment; filename={0}", "DSR"+DateTime.Now.ToString("dd-MM-yyyy")+".xls"));
GridView1.RenderBeginTag(htmlwritter);
GridView1.HeaderRow.RenderControl(htmlwritter);
foreach (GridViewRow row in GridView1.Rows)
{
row.RenderControl(htmlwritter);
}
GridView1.FooterRow.RenderControl(htmlwritter);
GridView1.RenderEndTag(htmlwritter);
Response.Write(strwritter.ToString());
Response.End();
这个问题的一个简单解决方案是让您的c#代码将GridView的数据源写入Excel文件。
这是我写的c#库来做到这一点:导出到Excel
所有源代码都是免费提供的，您只需要在ASP中添加几行代码。Net代码:
// The following ASP.Net code gets run when I click on my "Export to Excel" button.
protected void btnExportToExcel_Click(object sender, EventArgs e)
{
// It doesn't get much easier than this...
CreateExcelFile.CreateExcelDocument(listOfEmployees, "Employees.xlsx", Response);
}
这样做的另一个优点是它将创建一个"真正的".xlsx文件(使用OpenDocument库)。缺点是这些Microsoft库的重量约为5Mb。
试试这个，会成功的。
private void Export_To_Excel()
{
Microsoft.Office.Interop.Excel._Application excel = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel._Workbook workbook = excel.Workbooks.Add(Type.Missing);
Microsoft.Office.Interop.Excel._Worksheet worksheet = null;
try
{
worksheet = workbook.ActiveSheet;
worksheet.Name = "ExportedFromDatGrid";
int cellRowIndex = 1;
int cellColumnIndex = 1;
////Loop through each row and read value from each column.
for (int i = 0; i < this.dGV.Rows.Count - 1; i++)
{
for (int j = 0; j < this.dGV.Columns.Count; j++)
{
//// Excel index starts from 1,1. As first Row would have the Column headers, adding a condition check.
if (cellRowIndex == 1)
{
worksheet.Cells[cellRowIndex, cellColumnIndex] = this.dGV.Columns[j].HeaderText;
worksheet.Cells[cellRowIndex, cellColumnIndex].Font.FontStyle = FontStyle.Bold;
}
else
{
worksheet.Cells[cellRowIndex, cellColumnIndex] = this.dGV.Rows[i].Cells[j].Value.ToString();
}
cellColumnIndex++;
}
cellColumnIndex = 1;
cellRowIndex++;
}
worksheet.Columns.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft;
worksheet.Columns.AutoFit();
////Getting the location and file name of the excel to save from user.
SaveFileDialog saveDialog = new SaveFileDialog();
saveDialog.Filter = "Excel files (*.xlsx)|*.xlsx";
saveDialog.FilterIndex = 2;
if (saveDialog.ShowDialog() == System.Windows.Forms.DialogResult.OK)
{
workbook.SaveAs(saveDialog.FileName);
MessageBox.Show("Export Successful", "Info", MessageBoxButtons.OK, MessageBoxIcon.Warning, MessageBoxDefaultButton.Button1);
}
}
catch (System.Exception ex)
{
MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Warning, MessageBoxDefaultButton.Button1);
}
finally
{
excel.Quit();
workbook = null;
excel = null;
}
}
以使用EPPlus为例。只要输入一个SQL查询(或存储过程名)和一个文件名到exportToExcel，你就会得到一个Excel文件。exportToExcel("SELECT * FROM yourTable", "myFileName");
using OfficeOpenXml;
using OfficeOpenXml.Style;
using System;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Globalization;
using System.Web;
//=== create an excel document =========================================
public static void exportToExcel(string sqlQuery, string fileName)
{
HttpResponse Response = HttpContext.Current.Response;
DataTable dt = loadExternalDataTable(sqlQuery);
using (ExcelPackage p = new ExcelPackage())
{
//create a new workbook
p.Workbook.Properties.Author = "VDWWD";
p.Workbook.Properties.Title = fileName;
p.Workbook.Properties.Created = DateTime.Now;
//create a new worksheet
p.Workbook.Worksheets.Add(fileName);
ExcelWorksheet ws = p.Workbook.Worksheets[1];
ws.Name = fileName;
ws.Cells.Style.Font.Size = 11;
ws.Cells.Style.Font.Name = "Calibri";
createExcelHeader(ws, dt);
createExcelData(ws, dt);
ws.Cells[ws.Dimension.Address].AutoFitColumns();
//make all columms just a bit wider, they would sometimes not fit
for (int col = 1; col <= ws.Dimension.End.Column; col++)
{
ws.Column(col).Width = ws.Column(col).Width + 1;
}
//send the file to the browser
byte[] bin = p.GetAsByteArray();
Response.ClearHeaders();
Response.Clear();
Response.Buffer = true;
Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
Response.AddHeader("content-length", bin.Length.ToString());
Response.AddHeader("content-disposition", "attachment; filename='"" + fileName + ".xlsx'"");
Response.OutputStream.Write(bin, 0, bin.Length);
Response.Flush();
Response.Close();
Response.End();
}
}
//=== create the excel sheet header row =========================================
private static void createExcelHeader(ExcelWorksheet ws, DataTable dt)
{
int colindex = 1;
//loop all the columns
foreach (DataColumn dc in dt.Columns)
{
var cell = ws.Cells[1, colindex];
//make the text bold
cell.Style.Font.Bold = true;
//make the background of the cell gray
var fill = cell.Style.Fill;
fill.PatternType = ExcelFillStyle.Solid;
fill.BackgroundColor.SetColor(ColorTranslator.FromHtml("#BFBFBF"));
//fill the cell with the text
cell.Value = dc.ColumnName.ToUpper();
colindex++;
}
}
//=== create the excel sheet data =========================================
private static void createExcelData(ExcelWorksheet ws, DataTable dt)
{
int colindex = 0;
int rowindex = 1;
//loop all the rows
foreach (DataRow dr in dt.Rows)
{
colindex = 1;
rowindex++;
//loop all the columns
foreach (DataColumn dc in dt.Columns)
{
var cell = ws.Cells[rowindex, colindex];
string datatype = dc.DataType.ToString();
//fill the cell with the data in the correct format, needs to be done here because the headder row makes every column a string otherwise
if (datatype == "System.Decimal" || datatype == "System.Double" || datatype == "System.Float")
{
if (!string.IsNullOrEmpty(dr[dc.ColumnName].ToString()))
cell.Value = Convert.ToDecimal(dr[dc.ColumnName]);
cell.Style.Numberformat.Format = "0.00";
}
else if (datatype == "System.Int16" || datatype == "System.Int32" || datatype == "System.Int64" || datatype == "System.Long")
{
if (!string.IsNullOrEmpty(dr[dc.ColumnName].ToString()))
cell.Value = Convert.ToInt64(dr[dc.ColumnName]);
}
else if (datatype == "System.Bool" || datatype == "System.Boolean")
{
if (!string.IsNullOrEmpty(dr[dc.ColumnName].ToString()))
cell.Value = Convert.ToBoolean(dr[dc.ColumnName]); ;
}
else if (datatype == "System.DateTime")
{
if (!string.IsNullOrEmpty(dr[dc.ColumnName].ToString()))
cell.Value = Convert.ToDateTime(dr[dc.ColumnName]);
cell.Style.Numberformat.Format = DateTimeFormatInfo.CurrentInfo.ShortDatePattern;
}
else
{
cell.Value = dr[dc.ColumnName];
}
colindex++;
}
}
}
//=== create a datatable from a query =========================================
public static DataTable loadExternalDataTable(string sqlQuery)
{
DataTable dt = new DataTable();
using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["MyConnectionString"].ToString()))
using (SqlDataAdapter adapter = new SqlDataAdapter(sqlQuery, connection))
{
try
{
adapter.Fill(dt);
}
catch
{
}
}
return dt;
}
这段代码可能会对你有所帮助
protected void btnExportExcel_Click(object sender, EventArgs e)
{
BindData();
GridView1.Visible = true;
string FileName = "Deal Report_(" + DateTime.Now.AddHours(5).ToString("yyyy-MM-dd") + ").xls";
Response.Clear();
Response.Buffer = true;
Response.AddHeader("content-disposition",
"attachment;filename=" + FileName);
Response.Charset = "";
Response.ContentType = "application/vnd.ms-excel";
StringWriter sw = new StringWriter();
HtmlTextWriter hw = new HtmlTextWriter(sw);
GridView1.HeaderRow.Style.Add("color", "#FFFFFF");
GridView1.HeaderRow.Style.Add("background-color", "#1F437D");
for (int i = 0; i < GridView1.Rows.Count; i++)
{
GridViewRow row = GridView1.Rows[i];
row.BackColor = System.Drawing.Color.White;
row.Attributes.Add("class", "textmode");
if (i % 2 != 0)
{
for (int j = 0; j < row.Cells.Count; j++)
{
//row.Cells[j].Style.Add("background-color", "#eff3f8");
}
}
}
GridView1.RenderControl(hw);
string style = @"<style> .textmode { mso-number-format:'@; } </style>";
Response.Write(style);
Response.Output.Write(sw.ToString());
Response.End();
GridView1.Visible = false;
}