本代码实现思路是：页面显示和导出分开，导出的数据和用于页面显示的是同一查询数据方式，所以也是同样的数据，只是在导出数据时从数据库重新捞了一次数据。
此导出数据方式会先将数据保存到Excel中，然后将创建的Excel文件保存到服务器指定位置，然后下载到客户端，下载完后立即删除掉刚在服务器上创建的Excel文件。
// 导出按钮事件
protected void btOutputData_Click(object sender, EventArgs e)
{
ExportExcel(GetPrintingData(ViewState["ssearchCondition"].ToString()), Convert.ToInt32(ConfigurationManager.AppSettings["OutputData PageSize"].ToString()));
}
#region 导出数据
/// <summary>
/// 获取需要导出的数据
/// </summary>
/// <param name="strWhere">筛选数据的条件</param>
/// <returns></returns>
public System.Data.DataTable GetPrintingData(string strWhere)
{
System.Data.DataTable dt = GetListByPage(strWhere, 0, GetRecordCount(ViewState["ssearchCondition"].ToString()));
System.Data.DataTable newTable = new System.Data.DataTable();
newTable.Columns.AddRange(new DataColumn[]{
new DataColumn("rowN"),
new DataColumn("Description"),
new DataColumn("area"),
new DataColumn("projectName"),
new DataColumn("stake"),
new DataColumn("itemProjectName"),
new DataColumn("newCol2"),
new DataColumn("newCol3"),
new DataColumn("newCol4"),
new DataColumn("newCol5"),
new DataColumn("newCol6"),
new DataColumn("newCol7"),
new DataColumn("acceptConclution_zdb"),
new DataColumn("newCol8"),
new DataColumn("acceptConclution_zjb"),
new DataColumn("joinAccept_yz"),
new DataColumn("joinAccept_zjb"),
new DataColumn("joinAccept_zdb"),
new DataColumn("joinAccept_xmb")});
int num = 1;
for (int i = 0; i < dt.Rows.Count; i++)
{
DataRow newRow = newTable.NewRow();
newRow["rowN"] = num++;// dt.Rows[i]["rowN"];
newRow["Description"] = dt.Rows[i]["Description"];
newRow["area"] = dt.Rows[i]["area"];
newRow["projectName"] = dt.Rows[i]["projectName"];
newRow["stake"] = dt.Rows[i]["stake"];
newRow["itemProjectName"] = dt.Rows[i]["itemProjectName"];
newRow["newCol2"] = dt.Rows[i]["newCol2"];
newRow["newCol3"] = dt.Rows[i]["newCol3"];
newRow["newCol4"] = dt.Rows[i]["newCol4"];
newRow["newCol5"] = dt.Rows[i]["newCol5"];
newRow["newCol6"] = dt.Rows[i]["newCol6"];
newRow["newCol7"] = dt.Rows[i]["newCol7"];
newRow["acceptConclution_zdb"] = dt.Rows[i]["acceptConclution_zdb"];
newRow["newCol8"] = dt.Rows[i]["newCol8"];
newRow["acceptConclution_zjb"] = dt.Rows[i]["acceptConclution_zjb"];
newRow["joinAccept_yz"] = dt.Rows[i]["joinAccept_yz"];
newRow["joinAccept_zjb"] = dt.Rows[i]["joinAccept_zjb"];
newRow["joinAccept_zdb"] = dt.Rows[i]["joinAccept_zdb"];
newRow["joinAccept_xmb"] = dt.Rows[i]["joinAccept_xmb"];
newTable.Rows.Add(newRow);
}
return newTable;
}
/// <summary>
/// 把table中的数据导出到excel中去
/// </summary>
/// <param name="dt">要导入的table</param>
/// <param name="maxcount">模板excel中，一个sheet要显示的行数,如果数据多一个sheet中的最大值，会自动生成新的sheet</param>
private void ExportExcel(System.Data.DataTable dt, int maxcount)
{
Random ran = new Random();
string fileautoname = Server.MapPath("~/") + DateTime.Now.ToString("yyyyMMddhhmmss") + ran.Next(100, 999) + ".xls";//给新文件命名
string filepath = Server.MapPath("/") + "UpLoadFile\\Template\\OutputDataBlock.xls"; //模板文件路径
object missing = Type.Missing;
Microsoft.Office.Interop.Excel.Application application = new Application();
Workbook workbook = application.Workbooks.Open(filepath, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing);
Worksheet worksheet;
worksheet = (Worksheet)workbook.Sheets.get_Item(1);
//DataView dv = dt.DefaultView;
//dv.Sort = "id asc"; //table 中的数据按id升序排列
//System.Data.DataTable table = dv.ToTable();
System.Data.DataTable table = dt;
int sheetcount = GetSheetCount(table.Rows.Count, maxcount);
for (int count = 1; count < sheetcount; count++)
{
((Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets.get_Item(count)).Copy(missing, workbook.Worksheets[count]);
}
List<object[,]> list = new List<object[,]>();
object[,] ret;
for (int count = 0; count < sheetcount; count++)
{
if (count == sheetcount - 1)
{
ret = new object[table.Rows.Count - count * maxcount, table.Columns.Count - 2];
for (int i = 0; i < table.Rows.Count - count * maxcount; i++)
{
for (int j = 0; j < table.Columns.Count - 2; j++)
{
ret[i, j] = table.Rows[count * maxcount + i][j];
}
}
list.Add(ret);
}
else
{
ret = new object[maxcount, table.Columns.Count - 2];
for (int i = 0; i < maxcount; i++)
{
for (int j = 0; j < table.Columns.Count - 2; j++)
{
ret[i, j] = table.Rows[i + count * maxcount][j];
}
}
list.Add(ret);
}
}
object[,] obj;
for (int p = 0; p < list.Count; p++)
{
worksheet = (Worksheet)workbook.Sheets.get_Item(p + 1);
obj = list[p];
string cn = "L" + (obj.GetLength(0) + 2).ToString(); //设置填充区域
worksheet.get_Range("A3", cn).FormulaR1C1 = obj;
}
workbook.SaveAs(fileautoname, missing, missing, missing, missing, missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, missing, missing, missing, missing, missing);
workbook.Close(missing, missing, missing);
application.Quit();
workbook = null;
#region
System.IO.FileInfo file = new System.IO.FileInfo(fileautoname);
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.Flush();//这个语句必须有，否则就不回弹出保存的对话框，搞了N久
//删除长生的临时Excel文件
string filePath = fileautoname;//获取文件路径
if (filePath != null && System.IO.File.Exists(filepath))
{
//判断文件是否存在，如果存在，就删除之
System.IO.File.Delete(filePath);
}
// 停止页面的执行
Response.End();
#endregion
}
/// <summary>
/// 获取WorkSheet数量
/// </summary>
/// <param name="rowCount">记录总行数</param>
/// <param name="rows">每WorkSheet行数</param>
/// <returns></returns>
private int GetSheetCount(int rowCount, int rows)
{
int n = rowCount % rows; //余数
if (n == 0)
return rowCount / rows;
else
return Convert.ToInt32(rowCount / rows) + 1;
}
#endregion
