从GridView中导出Excel的公共方法(使用DataSet来传递值)

从GridView中导出Excel的公共方法（使用DataSet来传递值）

一：前台（“按钮Export的id：btnExport”）

二：按钮Export的事件

protected void btnExport_Click(object sender, EventArgs e)

{

if (ViewState["downloadDS"] == null)

{

string str = "Please make sure there is query data before click export button!";

ScriptManager.RegisterStartupScript(Page, this.GetType(), "Close",

"", false);

return;

}

string filename = "EmpDetails_Reprot.xls";

Page.Response.Clear();

PublicFun pf = new PublicFun();

bool success = pf.ExcelExport(Page.Request, Page.Response, (DataSet)ViewState["downloadDS"], filename); //传递DataSet值

if (!success)

{

Response.Write("Download Failure ！");

}

Page.Response.End();

}

三：后台代码

1：在PublicFun类中

public bool ExcelExport(HttpRequest _Request, HttpResponse _Response, DataSet ds, string filename)

{

bool result = false;

try

{

//先处理下载的文件名filename

//判断filename是否为空

if (string.IsNullOrEmpty(filename))

{

filename = "Temp.xls";

}

else

{

if (filename.Length > 4)

{

//获取上传文件路径中的文件及后缀（ToUpper:字符串转换大写ToLower：转换小写）

if (filename.Substring(https://www.360docs.net/doc/2b18734927.html,stIndexOf('.')).ToUpper() !=

".XLS")

{

filename = filename.Trim() + ".xls";

}

}

else

{

filename = filename.Trim() + ".xls";

}

}

ExportExcel exp = new ExportExcel();

//删除已有的filename文件名

if (exp.DeleteFile(ExportPath + filename) == true)

{

//编辑Excel文件的内容，如果编辑Excel成功，就可以下载

if (exp.ExcelExport(ds, ExportPath, filename) == true)

{

//下载Excel文件

if (exp.DownloadFile(_Request, _Response, filename, ExportPath

+ filename, 1024000) == true)

{

result = true;

}

exp.DeleteFile(ExportPath + filename);

}

}

}

catch (Exception ex)

{

throw ex;

}

return result;

}

2:在ExportExcel类中

using System;

using System.Collections.Generic;

using System.Linq;

using System.Web;

using System.IO;

using System.Data;

using System.Threading;

using CSharpJExcel.Jxl;

using CSharpJExcel.Jxl.Write;

using CSharpJExcel.Jxl.Format;

using System.Globalization;

//删除已有的filename文件名

public bool DeleteFile(string path)

{

bool result = true;

if (System.IO.File.Exists(path))

{

try

{

System.IO.File.Delete(path);

result = true;

}

catch

{

result = false;

}

}

return result;

}

//编辑Excel文件的内容

public bool ExcelExport(DataSet rts, string path, string filename)

{

bool result = true;

try

{

string file = path + filename;

//Filestream只可以进行以字节为单位原始数据流进行读写操作(实例化一个文件流，如果

FileMode是Create的话, 那么当实例产生的时候就会同时创建一个Bytes为0的File。)

FileStream sf = new FileStream(file, FileMode.Create);

//StreamWriter可以进行以字符为单位的数据读写操作

StreamWriter sw = new StreamWriter(sf);

//读取excel的时候一开始设置好读取的编码方式就能解决中文乱码(关键代码，解决中文乱码).

WorkbookSettings ws = new WorkbookSettings();

ws.setLocale(new CultureInfo("en"));

//创建工作薄,

WritableWorkbook workbook = Workbook.createWorkbook(sf, ws);

//是workbook设置一定颜色setColourRGB(Colour c,int r,int g,int b)

workbook.setColourRGB(Colour.LIME, 0xff, 0, 0);

//定义单元格格式(用于表格标题)

WritableCellFormat cf = new

WritableCellFormat(WritableWorkbook.ARIAL_10_PT);

// 是否换行

cf.setWrap(true);

// 线条

cf.setBorder(CSharpJExcel.Jxl.Format.Border.ALL, BorderLineStyle.THIN);

// 水平对齐

cf.setAlignment(Alignment.CENTRE);

int indsheet = 1;

foreach (DataTable dt in rts.Tables)

{

//DataTable 对象的集合中每个表的TableName。

string sheetname = dt.TableName;

//判断字符串sheetname是否为空

if (string.IsNullOrEmpty(sheetname))

{

//Replace()功能将一个字符串中指定个数的字符串替换为另一个字符串

sheetname = filename.Replace(".xls", "") + indsheet.ToString();

}

//导出表数据到Excel的sheet

writeSheet(workbook, sheetname, indsheet, dt);

indsheet++;

}

workbook.write();

workbook.close();

//重新写入流

sw.Write(sf);

//是清空缓存区的垃圾

sw.Flush();

sw.Close();

sf.Close();

}

catch

{

result = false;

}

return result;

}

// 下载Excel文件（_Request: Page.Request对象，_Response: Page.Response 对象，_fileName: 下载文件名，_fullPath: 带文件名下载路径，_speed 每秒允许下载的字节数）

public bool DownloadFile(HttpRequest _Request, HttpResponse _Response, string _fileName, string _fullPath, long _speed)

{

try

{

//保存在媒体滋盘上(在计算机创建目录)

FileStream myFile = new FileStream(_fullPath, FileMode.Open,

FileAccess.Read, FileShare.ReadWrite);

//以文件的全路对应的字符串和文件打开模式来初始化FileStream文件流实例

BinaryReader br = new BinaryReader(myFile);

try

{

//重要：续传必须。支持两个续传的响应头Accept-Ranges、ETag

（Accept-Ranges：响应

头，向客户端指明，此进程支持可恢复下载.实现后台智能传输服务（BITS），值为：bytes；

ETag：响应头，用于对客户端的初始（200）响应，以及来自客户端的恢复请求，必须为每个文

件提供一个唯一的ETag值（可由文件名和文件最后被修改的日期组成），这使客户端软件能够验

证它们已经下载的字节块是否仍然是最新的。）

_Response.AddHeader("Accept-Ranges", "bytes");

//关闭页面缓冲

_Response.Buffer = false;

long fileLength = myFile.Length;

long startBytes = 0;

double pack = 10240; //分快读取，每快10K bytes

//毫秒数：读取下一数据块的时间间隔

int sleep = (int)Math.Floor(1000 * pack / _speed) + 1;

//Request.Headers[]:这个方法防止刷新重复提交.(客户端报文头中通过Range报文头来标

识客户期望的下载位置，Range：续传的起始位置，即已经下载到客户端的字节数，值如：

bytes=1474560- )

if (_Request.Headers["Range"] != null)

{//---如果是续传请求，则获取续传的起始位置，即已经下载到客户端的字节数--- //重要：续传必须，表示局部范围响应。初始下载时默认为206

_Response.StatusCode = 206;

string[] range = _Request.Headers["Range"].Split(new char[] { '=', '-' }); //"bytes=1474560-"

//已经下载的字节数，即本次下载的开始位置

startBytes = Convert.ToInt64(range[1]);

}

// 添加头信息，指定文件大小，让浏览器能够显示下载进度

_Response.AddHeader("Content-Length", (fileLength –startBytes).ToString());

if (startBytes != 0)

{//---如果是续传请求，告诉客户端本次的开始字节数，总长度，以便客户端将续传数据追加到

startBytes位置后---

_Response.AddHeader("Content-Range", string.Format(" bytes {0}-

{1}/{2}", startBytes, fileLength - 1, fileLength));

}

//现成的长连接应用--Connection:keep-alive,在HTTp协议请求和响应中加入这条就能维

持长连接.再封装HTTP消息数据体的消息应用就显的非常简单易用

_Response.AddHeader("Connection", "Keep-Alive");

//下载文件不识别自动关闭窗口的方法，有时我们在开发中用到下载文件的方法中，往往用

ContentType:application/octet-stream和Content-

Disposition:attachment;filename=file.ext

_Response.ContentType = "application/octet-stream";

//添加头信息，为"文件下载/另存为"对话框指定默认文件名

_Response.AddHeader("Content-Disposition",

"attachment;filename=" +

HttpUtility.UrlEncode(_fileName, System.Text.Encoding.UTF8));

//向客户端发送数据块.(定位操作点，Begin是一个参考点)

br.BaseStream.Seek(startBytes, SeekOrigin.Begin);

//分块下载，剩余部分可分成的块数

int maxCount = (int)Math.Floor((fileLength - startBytes)/pack) + 1;

for (int i = 0; i < maxCount; i++)

{

//属性来获得浏览器是否连接

if (_Response.IsClientConnected)

{//---客户端中断连接，则暂停---

//HttpResponse.BinaryWrite() 方法将文件发送到客户端和ASP 响应缓冲

处于关闭时状态，您只能发送4 MB的数据到客户端，除非该页面的缓冲限制属性显

式设置。（默认情况下，在IIS6和7 bufferLimit在IIS属性ASPBufferLimit

属性的值是4,194,304字节。）

//BinaryReader.ReadBytes() 方法循环地调用Stream.Read 方法，直到达

到流的末尾，或者已经读取了请求的个字节为止。也就是说，如果没有到达流的末尾，该方法就一定会返回所请求的字节。

_Response.BinaryWrite(br.ReadBytes(int.Parse(pack.ToString( ))));

//将当前线程挂起指定的时间(使得当前线程休眠)

Thread.Sleep(sleep);

}

else

{

i = maxCount;

}

}

}

catch

{

return false;

}

finally

{

br.Close();

myFile.Close();

}

}

catch (Exception ex)

{

return false;

throw ex;

}

return true;

}

//设置单元格字体，日期单元格样式，数字单元格样式，文本单元格样式private WritableCellFormat setCellFormat(string dateType)

{

WritableCellFormat result = new WritableCellFormat();

//单元格字体

WritableFont CellFont = new WritableFont(WritableFont.ARIAL, 8, WritableFont.NO_BOLD, false);

if (dateType.ToUpper() == "DATETIME")

{

//设置日期单元格样式

DateFormat df = new DateFormat("DD/MM/YYYY hh:mm:ss");

WritableCellFormat DateStyle = new WritableCellFormat(CellFont, df);

//WritableCellFormat DateStyle = new WritableCellFormat(CellFont);

DateStyle.setBorder(Border.ALL, BorderLineStyle.THIN,

Colour.BLACK);

result = DateStyle;

}

else if (dateType.ToUpper() == "NUMBER")

{

//设置数字单元格样式

//NumberFormat nf = new NumberFormat("##0.###");

WritableCellFormat NumberStyle = new

WritableCellFormat(CellFont);

NumberStyle.setBorder(Border.ALL, BorderLineStyle.THIN, Colour.BLACK);

NumberStyle.setAlignment(Alignment.RIGHT);

result = NumberStyle;

}

else

{

//设置文本单元格样式

WritableCellFormat TextStyle = new WritableCellFormat(CellFont);

TextStyle.setBorder(Border.ALL, BorderLineStyle.THIN,

Colour.BLACK);

result = TextStyle;

}

return result;

}

//设置表头字体及样式，及数据

private bool writeSheet(WritableWorkbook workbook, string sheetName, int index, DataTable rts)

{

bool result = true;

try

{

WritableSheet ws = workbook.createSheet(sheetName, index);

ws.setRowView(0, 900);

//表头字体

WritableFont HeadFont = new WritableFont(WritableFont.ARIAL, 8, WritableFont.BOLD, false);

//设置表头样式

WritableCellFormat HeadStyle = new WritableCellFormat(HeadFont);

HeadStyle.setAlignment(Alignment.CENTRE);

HeadStyle.setVerticalAlignment(VerticalAlignment.CENTRE);

HeadStyle.setBorder(Border.ALL, BorderLineStyle.THIN,

Colour.BLACK);

//添加表头

int intcol = 0;

foreach (DataColumn colname in rts.Columns)

{

Label l = new Label(intcol, 0, colname.ColumnName, HeadStyle);

ws.addCell(l);

intcol++;

}

//数据导出

for (int col = 0; col < rts.Columns.Count; col++)

{

string datetype = GetDataType(rts.Columns[col]);

WritableCellFormat cf;

if (datetype.ToUpper() == "STRING")

{ cf = setCellFormat("String"); }

else if (datetype.ToUpper() == "DATETIME")

{ cf = setCellFormat("DATETIME"); }

else

{ cf = setCellFormat("NUMBER"); }

for (int row = 0; row < rts.Rows.Count; row++)

{

if (datetype.ToUpper() == "STRING" ||

string.IsNullOrEmpty(rts.Rows[row][col].ToString()))

{

Label cel=new Label(col,row+1,ts.Rows[row][col].ToString(),cf);

ws.addCell(cel);

}

else if (datetype.ToUpper() == "DATETIME")

{

ExcelDateTime cel = new ExcelDateTime(col, row + 1, Convert.ToDateTime(rts.Rows[row][col]), cf);

ws.addCell(cel);

}

else

{

if (datetype == "Byte")

{

Number cel = new Number(col, row + 1,

Convert.ToByte(rts.Rows[row][col]), cf);

ws.addCell(cel);

}

else if (datetype == "Int64")

{

Number cel = new Number(col, row + 1,

Convert.ToInt64(rts.Rows[row][col]), cf);

ws.addCell(cel);

}

else if (datetype == "Int16")

{

Number cel = new Number(col, row + 1, Convert.ToInt16(rts.Rows[row][col]), cf);

ws.addCell(cel);

}

else if (datetype == "Int32")

{

Number cel = new Number(col, row + 1, Convert.ToInt32(rts.Rows[row][col]), cf);

ws.addCell(cel);

}

else if (datetype == "Single")

{

Number cel = new Number(col, row + 1, Convert.ToSingle(rts.Rows[row][col]), cf);

ws.addCell(cel);

}

else if (datetype == "Double")

{

Number cel = new Number(col, row + 1, Convert.ToDouble(rts.Rows[row][col]), cf);

ws.addCell(cel);

}

else if (datetype == "Decimal")

{

Number cel = new Number(col, row + 1, Convert.ToDouble(rts.Rows[row][col]), cf);

ws.addCell(cel);

}

}

}

}

}

catch

{

result = false;

}

return result;

}

public bool ExcelExport(DataTable rts, string path, string filename, string sheetname, int indSheet)

{

bool result = true;

try

{

string file = path + filename;

if (string.IsNullOrEmpty(sheetname))

{

sheetname = filename.Replace(".xls", "");

}

FileStream sf = new FileStream(file, FileMode.Create);

StreamWriter sw = new StreamWriter(sf);

WorkbookSettings ws = new WorkbookSettings();

ws.setLocale(new CultureInfo("en"));

WritableWorkbook workbook = Workbook.createWorkbook(sf, ws);

workbook.setColourRGB(Colour.LIME, 0xff, 0, 0);

WritableCellFormat cf = new

WritableCellFormat(WritableWorkbook.ARIAL_10_PT);

cf.setWrap(true);

cf.setBorder(CSharpJExcel.Jxl.Format.Border.ALL, BorderLineStyle.THIN);

cf.setAlignment(Alignment.CENTRE);

writeSheet(workbook, sheetname, 1, rts);

workbook.write();

workbook.close();

sw.Write(sf);

sw.Flush();

sw.Close();

sf.Close();

}

catch

{

result = false;

}

return result;

}

private string GetDataType(DataColumn col)

{

string dtype = https://www.360docs.net/doc/2b18734927.html,;

string result = "";

switch (dtype)

{

case "Byte":result = "Byte"; break;

case "Int64":result = "Int64"; break;

case "Int16":result = "Int16"; break;

case "Int32":result = "Int32"; break;

case "Single":result = "Single"; break;

case "Double":result = "Double"; break;

case "Decimal":result = "Decimal"; break;

case "DateTime":result = "DateTime"; break;

case "String":result = "String"; break;

default: result = "String"; break;

}

return result; }