一、asp.net DataGridView導出到Excel的三個方法
#region DataGridView資料顯示到Excel
/// <summary>
/// 打開Excel并将DataGridView控件中資料導出到Excel
/// </summary>
/// <param name="dgv">DataGridView對象 </param>
/// <param name="isShowExcle">是否顯示Excel界面 </param>
/// <remarks>
/// add com "Microsoft Excel 11.0 Object Library"
/// using Excel=Microsoft.Office.Interop.Excel;
/// </remarks>
/// <returns> </returns>
public bool DataGridviewShowToExcel(DataGridView dgv, bool isShowExcle)
{
if (dgv.Rows.Count == 0)
return false;
//建立Excel對象
Excel.Application excel = new Excel.Application();
excel.Application.Workbooks.Add(true);
excel.Visible = isShowExcle;
//生成字段名稱
for (int i = 0; i < dgv.ColumnCount; i++)
{
excel.Cells[1, i + 1] = dgv.Columns[i].HeaderText;
}
//填充資料
for (int i = 0; i < dgv.RowCount - 1; i++)
{
for (int j = 0; j < dgv.ColumnCount; j++)
{
if (dgv[j, i].ValueType == typeof(string))
{
excel.Cells[i + 2, j + 1] = "'" + dgv[j, i].Value.ToString();
}
else
{
excel.Cells[i + 2, j + 1] = dgv[j, i].Value.ToString();
}
}
}
return true;
}
#endregion
#region DateGridView導出到csv格式的Excel
/// <summary>
/// 常用方法,列之間加/t,一行一行輸出,此檔案其實是csv檔案,不過預設可以當成Excel打開。
/// </summary>
/// <remarks>
/// using System.IO;
/// </remarks>
/// <param name="dgv"></param>
private void DataGridViewToExcel(DataGridView dgv)
{
SaveFileDialog dlg = new SaveFileDialog();
dlg.Filter = "Execl files (*.xls)|*.xls";
dlg.FilterIndex = 0;
dlg.RestoreDirectory = true;
dlg.CreatePrompt = true;
dlg.Title = "儲存為Excel檔案";
if (dlg.ShowDialog() == DialogResult.OK)
{
Stream myStream;
myStream = dlg.OpenFile();
StreamWriter sw = new StreamWriter(myStream, System.Text.Encoding.GetEncoding(-0));
string columnTitle = "";
try
{
//寫入列标題
for (int i = 0; i < dgv.ColumnCount; i++)
{
if (i > 0)
{
columnTitle += "/t";
}
columnTitle += dgv.Columns[i].HeaderText;
}
sw.WriteLine(columnTitle);
//寫入列内容
for (int j = 0; j < dgv.Rows.Count; j++)
{
string columnValue = "";
for (int k = 0; k < dgv.Columns.Count; k++)
{
if (k > 0)
{
columnValue += "/t";
}
if (dgv.Rows[j].Cells[k].Value == null)
columnValue += "";
else
columnValue += dgv.Rows[j].Cells[k].Value.ToString().Trim();
}
sw.WriteLine(columnValue);
}
sw.Close();
myStream.Close();
}
catch (Exception e)
{
MessageBox.Show(e.ToString());
}
finally
{
sw.Close();
myStream.Close();
}
}
}
#endregion
#region DataGridView導出到Excel,有一定的判斷性
/// <summary>
///方法,導出DataGridView中的資料到Excel檔案
/// </summary>
/// <remarks>
/// add com "Microsoft Excel 11.0 Object Library"
/// using Excel=Microsoft.Office.Interop.Excel;
/// using System.Reflection;
/// </remarks>
/// <param name= "dgv"> DataGridView </param>
public static void DataGridViewToExcel(DataGridView dgv)
{
#region 驗證可操作性
//申明儲存對話框
SaveFileDialog dlg = new SaveFileDialog();
//默然檔案字尾
dlg.DefaultExt = "xls ";
//檔案字尾清單
dlg.Filter = "EXCEL檔案(*.XLS)|*.xls ";
//默然路徑是系統目前路徑
dlg.InitialDirectory = Directory.GetCurrentDirectory();
//打開儲存對話框
if (dlg.ShowDialog() == DialogResult.Cancel) return;
//傳回檔案路徑
string fileNameString = dlg.FileName;
//驗證strFileName是否為空或值無效
if (fileNameString.Trim() == " ")
{ return; }
//定義表格内資料的行數和列數
int rowscount = dgv.Rows.Count;
int colscount = dgv.Columns.Count;
//行數必須大于0
if (rowscount <= 0)
{
MessageBox.Show("沒有資料可供儲存 ", "提示 ", MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
}
//列數必須大于0
if (colscount <= 0)
{
MessageBox.Show("沒有資料可供儲存 ", "提示 ", MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
}
//行數不可以大于65536
if (rowscount > 65536)
{
MessageBox.Show("資料記錄數太多(最多不能超過65536條),不能儲存 ", "提示 ", MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
}
//列數不可以大于255
if (colscount > 255)
{
MessageBox.Show("資料記錄行數太多,不能儲存 ", "提示 ", MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
}
//驗證以fileNameString命名的檔案是否存在,如果存在删除它
FileInfo file = new FileInfo(fileNameString);
if (file.Exists)
{
try
{
file.Delete();
}
catch (Exception error)
{
MessageBox.Show(error.Message, "删除失敗 ", MessageBoxButtons.OK, MessageBoxIcon.Warning);
return;
}
}
#endregion
Excel.Application objExcel = null;
Excel.Workbook objWorkbook = null;
Excel.Worksheet objsheet = null;
try
{
//申明對象
objExcel = new Microsoft.Office.Interop.Excel.Application();
objWorkbook = objExcel.Workbooks.Add(Missing.Value);
objsheet = (Excel.Worksheet)objWorkbook.ActiveSheet;
//設定EXCEL不可見
objExcel.Visible = false;
//向Excel中寫入表格的表頭
int displayColumnsCount = 1;
for (int i = 0; i <= dgv.ColumnCount - 1; i++)
{
if (dgv.Columns[i].Visible == true)
{
objExcel.Cells[1, displayColumnsCount] = dgv.Columns[i].HeaderText.Trim();
displayColumnsCount++;
}
}
//設定進度條
//tempProgressBar.Refresh();
//tempProgressBar.Visible = true;
//tempProgressBar.Minimum=1;
//tempProgressBar.Maximum=dgv.RowCount;
//tempProgressBar.Step=1;
//向Excel中逐行逐列寫入表格中的資料
for (int row = 0; row <= dgv.RowCount - 1; row++)
{
//tempProgressBar.PerformStep();
displayColumnsCount = 1;
for (int col = 0; col < colscount; col++)
{
if (dgv.Columns[col].Visible == true)
{
try
{
objExcel.Cells[row + 2, displayColumnsCount] = dgv.Rows[row].Cells[col].Value.ToString().Trim();
displayColumnsCount++;
}
catch (Exception)
{
}
}
}
}
//隐藏進度條
//tempProgressBar.Visible = false;
//儲存檔案
objWorkbook.SaveAs(fileNameString, Missing.Value, Missing.Value, Missing.Value, Missing.Value,
Missing.Value, Excel.XlSaveAsAccessMode.xlShared, Missing.Value, Missing.Value, Missing.Value,
Missing.Value, Missing.Value);
}
catch (Exception error)
{
MessageBox.Show(error.Message, "警告 ", MessageBoxButtons.OK, MessageBoxIcon.Warning);
return;
}
finally
{
//關閉Excel應用
if (objWorkbook != null) objWorkbook.Close(Missing.Value, Missing.Value, Missing.Value);
if (objExcel.Workbooks != null) objExcel.Workbooks.Close();
if (objExcel != null) objExcel.Quit();
objsheet = null;
objWorkbook = null;
objExcel = null;
}
MessageBox.Show(fileNameString + "/n/n導出完畢! ", "提示 ", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
#endregion
二 、 Asp.net Gridview Export PDF,Word,Excel,Csv
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="CSharp.aspx.cs" Inherits="CSharp" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
<title>GridView Export</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:GridView ID="GridView1" runat="server"
AutoGenerateColumns = "false" Font-Names = "Arial"
Font-Size = "11pt" AlternatingRowStyle-BackColor = "#C2D69B"
HeaderStyle-BackColor = "green" AllowPaging ="true"
OnPageIndexChanging = "OnPaging" >
<Columns>
<asp:BoundField ItemStyle-Width = "150px" DataField = "CustomerID" HeaderText = "CustomerID" />
<asp:BoundField ItemStyle-Width = "150px" DataField = "City" HeaderText = "City"/>
<asp:BoundField ItemStyle-Width = "150px" DataField = "Country" HeaderText = "Country"/>
<asp:BoundField ItemStyle-Width = "150px" DataField = "PostalCode" HeaderText = "PostalCode"/>
</Columns>
</asp:GridView>
</div>
<br />
<asp:Button ID="btnExportWord" runat="server" Text="ExportToWord" OnClick="btnExportWord_Click" />
<asp:Button ID="btnExportExcel" runat="server" Text="ExportToExcel" OnClick="btnExportExcel_Click" />
<asp:Button ID="btnExportPDF" runat="server" Text="ExportToPDF" OnClick="btnExportPDF_Click" />
<asp:Button ID="Button1" runat="server" Text="ExportToCSV" OnClick="btnExportCSV_Click" />
</form>
</body>
</html>
背景編輯代碼時需引用itextsharp.dll元件
using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;
using System.IO;
using iTextSharp.text;
using iTextSharp.text.pdf;
using iTextSharp.text.html;
using iTextSharp.text.html.simpleparser;
using System.Text;
public partial class CSharp : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
string strQuery = "select CustomerID,City,Country,PostalCode from customers";
SqlCommand cmd = new SqlCommand(strQuery);
DataTable dt = GetData(cmd);
GridView1.DataSource = dt;
GridView1.DataBind();
}
private DataTable GetData(SqlCommand cmd)
{
DataTable dt = new DataTable();
String strConnString = System.Configuration.ConfigurationManager.ConnectionStrings["conString"].ConnectionString;
SqlConnection con = new SqlConnection(strConnString);
SqlDataAdapter sda = new SqlDataAdapter();
cmd.CommandType = CommandType.Text;
cmd.Connection = con;
try
{
con.Open();
sda.SelectCommand = cmd;
sda.Fill(dt);
return dt;
}
catch (Exception ex)
{
throw ex;
}
finally
{
con.Close();
sda.Dispose();
con.Dispose();
}
}
public override void VerifyRenderingInServerForm(Control control)
{
/* Verifies that the control is rendered */
}
protected void OnPaging(object sender, GridViewPageEventArgs e)
{
GridView1.PageIndex = e.NewPageIndex;
GridView1.DataBind();
}
protected void btnExportWord_Click(object sender, EventArgs e)
{
Response.Clear();
Response.Buffer = true;
Response.AddHeader("content-disposition", "attachment;filename=GridViewExport.doc");
Response.Charset = "";
Response.ContentType = "application/vnd.ms-word ";
StringWriter sw= new StringWriter();
HtmlTextWriter hw = new HtmlTextWriter(sw);
GridView1.AllowPaging = false;
GridView1.DataBind();
GridView1.RenderControl(hw);
Response.Output.Write(sw.ToString());
Response.Flush();
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);
GridView1.AllowPaging = false;
GridView1.DataBind();
//Change the Header Row back to white color
GridView1.HeaderRow.Style.Add("background-color", "#FFFFFF");
//Apply style to Individual Cells
GridView1.HeaderRow.Cells[0].Style.Add("background-color", "green");
GridView1.HeaderRow.Cells[1].Style.Add("background-color", "green");
GridView1.HeaderRow.Cells[2].Style.Add("background-color", "green");
GridView1.HeaderRow.Cells[3].Style.Add("background-color", "green");
for (int i = 0; i < GridView1.Rows.Count;i++ )
{
GridViewRow row = GridView1.Rows[i];
//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");
}
}
GridView1.RenderControl(hw);
//style to format numbers to string
string style = @"<mce:style><!--
.textmode { mso-number-format:/@; }
--></mce:style><style mce_bogus="1"> .textmode { mso-number-format:/@; } </style>";
Response.Write(style);
Response.Output.Write(sw.ToString());
Response.Flush();
Response.End();
}
protected void btnExportPDF_Click(object sender, EventArgs e)
{
Response.ContentType = "application/pdf";
Response.AddHeader("content-disposition", "attachment;filename=GridViewExport.pdf");
Response.Cache.SetCacheability(HttpCacheability.NoCache);
StringWriter sw = new StringWriter();
HtmlTextWriter hw = new HtmlTextWriter(sw);
GridView1.AllowPaging = false;
GridView1.DataBind();
GridView1.RenderControl(hw);
StringReader sr = new StringReader(sw.ToString());
Document pdfDoc = new Document(PageSize.A4, 10f,10f,10f,0f);
HTMLWorker htmlparser = new HTMLWorker(pdfDoc);
PdfWriter.GetInstance(pdfDoc, Response.OutputStream);
pdfDoc.Open();
htmlparser.Parse(sr);
pdfDoc.Close();
Response.Write(pdfDoc);
Response.End();
}
protected void btnExportCSV_Click(object sender, EventArgs e)
{
Response.Clear();
Response.Buffer = true;
Response.AddHeader("content-disposition", "attachment;filename=GridViewExport.csv");
Response.Charset = "";
Response.ContentType = "application/text";
GridView1.AllowPaging = false;
GridView1.DataBind();
StringBuilder sb = new StringBuilder();
for (int k = 0; k < GridView1.Columns.Count; k++)
{
//add separator
sb.Append(GridView1.Columns[k].HeaderText + ',');
}
//append new line
sb.Append("/r/n");
for (int i = 0; i < GridView1.Rows.Count; i++)
{
for (int k = 0; k < GridView1.Columns.Count; k++)
{
//add separator
sb.Append(GridView1.Rows[i].Cells[k].Text + ',');
}
//append new line
sb.Append("/r/n");
}
Response.Output.Write(sb.ToString());
Response.Flush();
Response.End();
}
}