GridView 导出到 Word/Excel/PDF/CSV
using System.Drawing;
using System.IO;
using System.Text;
protected void Button3_Click(object sender, EventArgs e)
{
Response.Clear();
Response.Buffer = false;
Response.Charset = "GB2312";
Response.AppendHeader("Content-Disposition", "attachment;filename=pkmv_de.xls");
Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
Response.ContentType = "application/ms-excel";
Response.Write("<meta http-equiv=Content-Type content=\"text/html; charset=GB2312\">");
this.EnableViewState = false;
System.IO.StringWriter oStringWriter = new System.IO.StringWriter();
HtmlTextWriter oHtmlTextWriter = new HtmlTextWriter(oStringWriter);
GridView2.RenderControl(oHtmlTextWriter);
Response.Write(oStringWriter.ToString());
Response.End();
}
public override void VerifyRenderingInServerForm(Control control)
{
}
或者
/// 定义导出 Excel Word 的函数
private void Export(string FileType, string FileName)
{
Response.Charset = "GB2312";
Response.ContentEncoding = System.Text.Encoding.UTF8;
Response.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(FileName, Encoding.UTF8).ToString());
Response.ContentType = FileType;
this.EnableViewState = false;
StringWriter tw = new StringWriter();
HtmlTextWriter hw = new HtmlTextWriter(tw);
GridView1.RenderControl(hw);
Response.Write(tw.ToString());
Response.End();
}
/// 此方法必重写，否则会出错
public override void VerifyRenderingInServerForm(Control control)
{
}
protected void Button1_Click(object sender, EventArgs e) // Excel
{
Export("application/ms-excel", "Employee information.xls");
}
protected void Button2_Click(object sender, EventArgs e) //Word
{
//Export("application/ms-excel", "Employee.doc");
Export("application/ms-word", "员工信息.doc");//都可以
}
点击 下载 按钮
{
//用戶端的物件
System.Net.WebClient wc = new System.Net.WebClient();
byte[] file = null;
try
{
//用戶端下載檔案到byte陣列
file = wc.DownloadData(fileUrlPath);
}
catch (Exception ex)
{
HttpContext.Current.Response.Write("ASP.net禁止下載此敏感檔案(通常為：.cs、.vb、微軟資料庫mdb、mdf和config組態檔等)。<br/>檔案路徑：" + fileUrlPath + "<br/>錯誤訊息：" + ex.ToString());
return;
}
HttpContext.Current.Response.Clear();
string fileName = System.IO.Path.GetFileName(fileUrlPath);
//跳出視窗，讓用戶端選擇要儲存的地方 //使用Server.UrlEncode()編碼中文字才不會下載時，檔名為亂碼
HttpContext.Current.Response.AddHeader("content-disposition", "attachment;filename=" + HttpContext.Current.Server.UrlEncode(fileName));
//設定MIME類型為二進位檔案
HttpContext.Current.Response.ContentType = "application/octet-stream";
try
{
//檔案有各式各樣，所以用BinaryWrite
HttpContext.Current.Response.BinaryWrite(file);
}
catch (Exception ex)
{
HttpContext.Current.Response.Write("檔案輸出有誤，您可以在瀏覽器的URL網址貼上以下路徑嘗試看看。<br/>檔案路徑：" + fileUrlPath + "<br/>錯誤訊息：" + ex.ToString());
return;
}
//這是專門寫文字的
//HttpContext.Current.Response.Write();
HttpContext.Current.Response.End();
}
using System.Collections.Generic;
using System.Web;
/***Copy Start***/
//引用Microsoft Excel相關參考
using Microsoft.Office.Interop;
using Microsoft.Office.Interop.Excel;
//移機時記得Bin底下的Microsoft.Office.Interop.Excel.dll和office.dll等，Excel相關dll也要Copy過去
/***Copy End***/
public class ExcelImport :System.Web.UI.Page
{
/***Copy Start***/
//畫面上要先擺一個FileUpload控制項
/*** Excel Interop reference ***/
Microsoft.Office.Interop.Excel.Application xlApp = null;
Workbook wb = null;
Worksheet ws = null;
Range aRange = null;
//*******************************/
//要上傳Excel檔的Server端 檔案總管目錄
string upload_excel_Dir = @"D:\web\myWeb\";
#region 匯入EXCEL
//按鈕Click事件
protected void lbtOK_Click(object sender, EventArgs e)
{
string excel_filePath = "";
try
{
excel_filePath = SaveFileAndReturnPath();//先上傳EXCEL檔案給Server
if (this.xlApp == null)
{
this.xlApp = new Microsoft.Office.Interop.Excel.Application();
}
//打開Server上的Excel檔案
this.xlApp.Workbooks.Open(excel_filePath, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
this.wb = xlApp.Workbooks[1];//第一個Workbook
this.wb.Save();
//從第一個Worksheet讀資料
SaveOrInsertSheet(excel_filePath, (Worksheet)xlApp.Worksheets[1]);
ClientScript.RegisterClientScriptBlock(typeof(Page), "匯入完成", "alert('匯入完成');", true);
}
catch (Exception ex)
{
throw ex;
}
finally
{
xlApp.Workbooks.Close();
xlApp.Quit();
try
{
//刪除 Windows工作管理員中的Excel.exe 處理緒.
System.Runtime.InteropServices.Marshal.ReleaseComObject(this.xlApp);
System.Runtime.InteropServices.Marshal.ReleaseComObject(this.ws);
System.Runtime.InteropServices.Marshal.ReleaseComObject(this.aRange);
}
catch { }
this.xlApp = null;
this.wb = null;
this.ws = null;
this.aRange = null;
//是否刪除Server上的Excel檔
bool isDeleteFileFromServer = true;
if (isDeleteFileFromServer)
{
System.IO.File.Delete(excel_filePath);
}
GC.Collect();
}
}
#endregion
#region 儲存EXCEL檔案給Server
private string SaveFileAndReturnPath()
{
string return_file_path = "";//上傳的Excel檔在Server上的位置
if (FileUpload1.FileName != "")
{
return_file_path = System.IO.Path.Combine(this.upload_excel_Dir, Guid.NewGuid().ToString() + ".xls");
FileUpload1.SaveAs(return_file_path);
}
return return_file_path;
}
#endregion
#region 把Excel資料Insert into Table
private void SaveOrInsertSheet(string excel_filename,Worksheet ws)
{
//要開始讀取的起始列(微軟Worksheet是從1開始算)
int rowIndex = 1;
//取得一列的範圍
this.aRange = ws.get_Range("A" + rowIndex.ToString(), "C" + rowIndex.ToString());
//判斷Row範圍裡第1格有值的話，迴圈就往下跑
while (((object[,])this.aRange.Value2)[1, 1] != null)
{
//範圍裡第1格的值
string cell1 = ((object[,])this.aRange.Value2)[1, 1] != null ? ((object[,])this.aRange.Value2)[1, 1].ToString() : "";
//範圍裡第2格的值
string cell2 = ((object[,])this.aRange.Value2)[1, 2] != null ? ((object[,])this.aRange.Value2)[1, 2].ToString() : "";
//範圍裡第3格的值
string cell3 = ((object[,])this.aRange.Value2)[1, 3] != null ? ((object[,])this.aRange.Value2)[1, 3].ToString() : "";
//抓出來後Insert into Table...(略
//往下抓一列Excel範圍
rowIndex++;
this.aRange = ws.get_Range("A" + rowIndex.ToString(), "C" + rowIndex.ToString());
}
}
#endregion
/***Copy End***/
}
打开word
using System.IO;
using System.Text;
protected void open()
{
string fileName = "temp.doc";
if (fileName != null)
{
string filePath = @"images/";
FileStream MyFileStream = new FileStream(filePath + fileName, FileMode.Open);
long FileSize = MyFileStream.Length;
byte[] Buffer = new byte[(int)FileSize];
MyFileStream.Read(Buffer, 0, (int)FileSize);
MyFileStream.Close();
Response.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(fileName, Encoding.UTF8).ToString());
Response.ContentType = "application/ms-word";
Response.BinaryWrite(Buffer);
Response.End();
}
else
{
Response.Write("文件不存在！");
}
}
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 = @"<style> .textmode { } </style>";
Response.Write(style);
Response.Output.Write(sw.ToString());
Response.Flush();
Response.End();
}
using iTextSharp.text;
using iTextSharp.text.pdf;
using iTextSharp.text.html;
using iTextSharp.text.html.simpleparser;
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();
}
public override void VerifyRenderingInServerForm(Control control)
{
/* Verifies that the control is rendered */
}
Excel 与 Access 互导入 with ASP.NET
string Access = Server.MapPath("App_Data/contacts.mdb");
string Excel = Server.MapPath("App_Data/Book1.xls");
string connect = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Excel +";Extended Properties=Excel 8.0;";
using (OleDbConnection conn = new OleDbConnection(connect))
{
using (OleDbCommand cmd = new OleDbCommand())
{
cmd.Connection = conn;
cmd.CommandText = "INSERT INTO [MS Access;Database=" + Access + "].[Persons] SELECT * FROM [Sheet1$]";
conn.Open();
cmd.ExecuteNonQuery();
}
}
string Access = Server.MapPath("App_Data/contacts.mdb");
string Excel = Server.MapPath("App_Data/Book1.xls");
string connect = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Excel +";Extended Properties=Excel 8.0;";
using (OleDbConnection conn = new OleDbConnection(connect))
{
using (OleDbCommand cmd = new OleDbCommand())
{
cmd.Connection = conn;
cmd.CommandText = "SELECT * INTO [MS Access;Database=" + Access + "].[New Table] FROM [Sheet1$]";
conn.Open();
cmd.ExecuteNonQuery();
}
}
import data from excel sheet to griedview directly.
string Access = Server.MapPath("App_Data/contacts.mdb");
string Excel = Server.MapPath("App_Data/Book1.xls");
string connect = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Excel + ";Extended Properties=Excel 8.0;";
using (OleDbConnection conn = new OleDbConnection(connect))
{
using (OleDbCommand cmd = new OleDbCommand())
{
cmd.Connection = conn;
cmd.CommandText = "SELECT * FROM [Sheet1$]";
conn.Open();
OleDbDataReader dr = cmd.ExecuteReader();
GridView1.DataSource = dr;
GridView1.DataBind();
conn.Close();
}
}
Reading Text files into Access with ASP.NET
/*
FirstName, SecondName
Joe,Bloggs
Fred,Bassett
Archie,Falls
Doris,Knight
Gladys,Day
*/
string connect = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|Contacts.mdb";
OleDbConnection conn = new OleDbConnection(connect);
string path = Server.MapPath("App_Data");
string query = "INSERT INTO Persons (FirstName, SecondName) SELECT FirstName, SecondName FROM
[Text;DATABASE=" + path + ";].[test.txt]";
OleDbCommand cmd = new OleDbCommand(query, conn);
conn.Open();
cmd.ExecuteNonQuery();
conn.Close();
Export GridView with Images from database to Word, Excel and PDF Formats
Read and Import Excel Sheet into ASP.Net GridView Control
