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
本文转自曾祥展博客园博客，原文链接：http://www.cnblogs.com/zengxiangzhan/archive/2009/09/23/1572316.html，如需转载请自行联系原作者
