竟数据导出EXCEL,并实现再EXCEL中,并实现再EXCEL中的分页显示 - 黑马程序员技术交流社区

protected void btnExcel_Click(object sender, EventArgs e)
{
//导出数据到EXCEL
string modeFile = Server.MapPath("PrintProvider.xls");
string tempFile = Server.MapPath("ProviderTemp.xls");//取Excel报表模板
File.Copy(tempFile, modeFile, true);
Excel.Application xExcel = new Excel.ApplicationClass();//创建excel对象
Excel.Workbook xBook = xExcel.Workbooks.Open(modeFile, 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);
Excel.Worksheet xSheet = (Excel.Worksheet)xBook.Worksheets[1];
try
{
string sqlStr;
sqlStr = "select * from PM_notice";
DataTable dt = Class1.GetDsFromSql(sqlStr).Tables[0];
//查询结果数据
int count = dt.Rows.Count;
int page = 0;
if (count % 15 == 0)
{
page = count / 15;
}
else
{
page = count / 15 + 1;
}
if (page == 0) page = 1;
int sheet = 1;
int tempSheet = sheet;
int p;
for (p = 1; p < page; p++)
{
xSheet = (Excel.Worksheet)xBook.Worksheets[tempSheet];
xSheet.Copy(Type.Missing, xBook.Worksheets[tempSheet]);
tempSheet++;
}
xBook.Save();
int index = 0;
for (p = 0; p < page; p++)
{
xSheet = (Excel.Worksheet)xBook.Worksheets[sheet++];
int row = 3;//jjjjjjjjjjjjj
for (int i = p * 15; i < (p + 1) * 15 && i < count; i++)
{
index++;
xSheet.Cells[row, 1] = index.ToString();//序号列
xSheet.Cells[row, 2] = dt.Rows[i]["title"].ToString();
xSheet.Cells[row, 3] = dt.Rows[i]["content"].ToString();
xSheet.Cells[row, 4] = dt.Rows[i]["uploader"].ToString();
xSheet.Cells[row, 5] = dt.Rows[i]["uploadTime"].ToString();
xSheet.Cells[row, 6] = dt.Rows[i]["deadline"].ToString();
xSheet.Cells[row, 7] = dt.Rows[i]["remark"].ToString();
row++;
}
}
}
catch (Exception ee)
{
this.lblProviderInfo.Text = "报表生成过程出现错误，请稍后重试！";
}
finally
{
try
{
xBook.Save();
xBook.Close(false, null, null);
xExcel.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(xBook);
System.Runtime.InteropServices.Marshal.ReleaseComObject(xExcel);
System.Runtime.InteropServices.Marshal.ReleaseComObject(xSheet);
xBook = null;
xExcel = null;
xSheet = null;
GC.Collect();
string path = Server.MapPath("PrintProvider.xls");
System.IO.FileInfo file = new System.IO.FileInfo(path);
Response.Clear();
Response.Charset = "GB2312";
Response.ContentEncoding = System.Text.Encoding.UTF8;
// 添加头信息，为"文件下载/另存为"对话框指定默认文件名
Response.AddHeader("Content-Disposition", "attachment; filename=" + Server.UrlEncode(file.Name));
// 添加头信息，指定文件大小，让浏览器能够显示下载进度
Response.AddHeader("Content-Length", file.Length.ToString());
// 指定返回的是一个不能被客户端读取的流，必须被下载
Response.ContentType = "application/ms-excel";
// 把文件流发送到客户端
Response.WriteFile(file.FullName);
// 停止页面的执行
Response.End();
}
catch (Exception eee)
{
//this.lblProviderInfo.Text = eee.ToString();
}
}
}

复制代码