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");//都可以
    }




点击 下载 按钮

protected void ButtonDownloadFile(string fileUrlPath)
    {
        
//用戶端的物件
        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();
    }

 



1.上傳Excel檔。2. ASP.net讀Excel資料,然後Insert into Table。3.把上傳的Excel檔宰掉,避免硬碟空間不夠。

GridView 导出到 Word/Excel/PDF/CSV
using System;
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)[11!= null)
        {
            
//範圍裡第1格的值
            string cell1 = ((object[,])this.aRange.Value2)[11!= null ? ((object[,])this.aRange.Value2)[11].ToString() : "";

            
//範圍裡第2格的值
            string cell2 = ((object[,])this.aRange.Value2)[12!= null ? ((object[,])this.aRange.Value2)[12].ToString() : "";

            
//範圍裡第3格的值
            string cell3 = ((object[,])this.aRange.Value2)[13!= null ? ((object[,])this.aRange.Value2)[13].ToString() : "";
            
            
//抓出來後Insert into Table...(略
            
//往下抓一列Excel範圍
            rowIndex++;
            
this.aRange = ws.get_Range("A" + rowIndex.ToString(), "C" + rowIndex.ToString());
        }
    }
    
#endregion

    
/***Copy End***/
}
GridView 导出到 Word/Excel/PDF/CSV





打开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

http://www.aspsnippets.com/post/2009/04/22/Export-GridView-with-Images-from-database-to-Word-Excel-and-PDF-Formats.aspx

 

Read and Import Excel Sheet into ASP.Net GridView Control

http://www.aspsnippets.com/post/2009/06/04/Read-and-Import-Excel-Sheet-into-ASPNet-GridView-Control.aspx



    本文转自曾祥展博客园博客,原文链接:http://www.cnblogs.com/zengxiangzhan/archive/2009/09/23/1572316.html,如需转载请自行联系原作者

上一篇:PHP连接SQLServer2012两例


下一篇:pytest执行用例时,如何显示用例标题