以下是我在項目開發中所做的關于Excel導出功能,不足之處還望大家指正,相互學習指正
protected
void
btn_Export_Click(
object
sender, EventArgs e)
{
string
FileID =
""
;
if
(Request.QueryString[
"fujian"
] !=
null
&& Request.QueryString[
"fujian"
].ToString() !=
""
)
{
FileID = Request.QueryString[
"fujian"
].ToString();
}
string
title = GetFileTile(FileID);
Workbook workbook =
new
Workbook();
Worksheet sheet = workbook.Worksheets[0];
Cells cells = sheet.Cells;
cells.SetColumnWidth(0, 20.00);
cells.SetColumnWidth(1, 30.00);
cells.SetColumnWidth(2, 30.00);
cells.Merge(0, 0, 1, 3);
cells[0, 0].PutValue(title +
"/文件已學人員名單"
);
cells[0, 1].PutValue(
""
);
cells[0, 2].PutValue(
""
);
cells[1, 0].PutValue(
"序號"
);
cells[1, 1].PutValue(
"姓名"
);
cells[1, 2].PutValue(
"時間"
);
string
sql =
"order by StudyTime"
;
ds = PublishBLL.GetCommentCount(FileID, sql);
if
(ds.Tables[0].Rows.Count > 0)
{
for
(
int
i = 1; i < ds.Tables[0].Rows.Count + 1; i++)
{
cells[i + 1, 0].PutValue(
""
+ i.ToString().PadLeft(3,
''0''
) +
""
);
cells[i + 1, 1].PutValue(
""
+ ds.Tables[0].Rows[i - 1][
"StudyName"
].ToString() +
""
);
cells[i + 1, 2].PutValue(
""
+ ds.Tables[0].Rows[i - 1][
"StudyTime"
].ToString() +
""
);
}
}
string
filename =
"統計"
+ DateTime.Now.ToString(
"yyyyMMddHHmmss"
) +
".xls"
;
string
path = ConfigurationManager.AppSettings[
"StudyFile"
] +
@"\"
+ filename + "";
workbook.Save(path);
#region 下載
System.IO.MemoryStream ms1 = workbook.SaveToStream();
byte
[] bt1 = ms1.ToArray();
string
fileName =
"統計"
+ DateTime.Now.ToString(
"yyyyMMddHHmmss"
) +
".xls"
;
Response.ContentType =
"application/vnd.ms-excel"
;
Response.AddHeader(
"Content-Disposition"
,
"attachment; filename="
+ HttpUtility.UrlEncode(fileName, System.Text.Encoding.UTF8));
Response.BinaryWrite(bt1);
Response.Flush();
Response.End();
#endregion
}