ASp.net 操作 Excel 分页导出数据

//=====================ExcelHelper（套用模板输出Excel，并对数据进行分页）==============

using System;

using System.IO;

using System.Data;

using System.Reflection;

using System.Diagnostics;

using cfg = System.Configuration;

using Excel;

namespace ExcelHelper

{

/// <summary>

/// 功能说明：套用模板输出Excel，并对数据进行分页

/// </summary>

public class ExcelHelper

{

protected string templetFile = null;

protected string outputFile = null;

protected object missing = Missing.Value;

/// <summary>

/// 构造函数，需指定模板文件和输出文件完整路径

/// </summary>

/// <param name="templetFilePath"> Excel模板文件路径 </param>

/// <param name="outputFilePath"> 输出Excel文件路径 </param>

public ExcelHelper(string templetFilePath, string outputFilePath)

{

if (templetFilePath == null)

throw new Exception(" Excel模板文件路径不能为空！ ");

if (outputFilePath == null)

throw new Exception(" 输出Excel文件路径不能为空！ ");

if (!File.Exists(templetFilePath))

throw new Exception(" 指定路径的Excel模板文件不存在！ ");

this.templetFile = templetFilePath;

this.outputFile = outputFilePath;

}

/**/

/// <summary>

/// 将DataTable数据写入Excel文件（套用模板并分页）

/// </summary>

/// <param name="dt"> DataTable </param>

/// <param name="rows"> 每个WorkSheet写入多少行数据 </param>

/// <param name="top"> Excel中行索引 </param>

/// <param name="left"> Excel中列索引 </param>

/// <param name="sheetPrefixName"> WorkSheet前缀名，比如：前缀名为“Sheet”，那么WorkSheet名称依次为“Sheet-1，Sheet-2” </param>

public void DataTableToExcel(System.Data.DataTable dt, int rows, int top, int left, string sheetPrefixName)

{

int rowCount = dt.Rows.Count; // 源DataTable行数

int colCount = dt.Columns.Count; // 源DataTable列数

int sheetCount = this.GetSheetCount(rowCount, rows); // WorkSheet个数

DateTime beforeTime;

DateTime afterTime;

if (sheetPrefixName == null || sheetPrefixName.Trim() == "")

sheetPrefixName = " Sheet ";

// 创建一个Application对象并使其可见

beforeTime = DateTime.Now;

Excel.Application app = new Excel.ApplicationClass();

app.Visible = true;

afterTime = DateTime.Now;

// 打开模板文件，得到WorkBook对象 （网上都是13个参数，现在变成14个参数了）

Excel.Workbook workBook = app.Workbooks.Open(templetFile, missing, missing, missing, missing, missing,

missing, missing, missing, missing, missing, missing, missing, missing, missing);

// 得到WorkSheet对象

Excel.Worksheet workSheet = (Excel.Worksheet)workBook.Sheets.get_Item(1);

// 复制sheetCount-1个WorkSheet对象

for (int i = 1; i < sheetCount; i++)

{

((Excel.Worksheet)workBook.Worksheets.get_Item(i)).Copy(missing, workBook.Worksheets);

}

for (int i = 1; i <= sheetCount; i++)

{

int startRow = (i - 1) * rows; // 记录起始行索引

int endRow = i * rows; // 记录结束行索引

// 若是最后一个WorkSheet，那么记录结束行索引为源DataTable行数

if (i == sheetCount)

endRow = rowCount;

// 获取要写入数据的WorkSheet对象，并重命名

Excel.Worksheet sheet = (Excel.Worksheet)workBook.Worksheets.get_Item(i);

sheet.Name = sheetPrefixName + " - " + i.ToString();

// 将dt中的数据写入WorkSheet

for (int j = 0; j < endRow - startRow; j++)

{

for (int k = 0; k < colCount; k++)

{

sheet.Cells[top + j, left + k] = dt.Rows[startRow + j][k].ToString();

}

}

//// ====================写文本框数据（有错误注释掉了）=============

//Excel.TextBox txtAuthor = (Excel.TextBox)sheet.TextBoxes(" txtAuthor ");

//Excel.TextBox txtDate = (Excel.TextBox)sheet.TextBoxes(" txtDate ");

//Excel.TextBox txtVersion = (Excel.TextBox)sheet.TextBoxes(" txtVersion ");

//txtAuthor.Text = " KLY.NET的Blog ";

//txtDate.Text = DateTime.Now.ToShortDateString();

//txtVersion.Text = " 1.0.0.0 ";

sheet.Cells[rows + 1, left + 1] = "总计：10000";

sheet.Cells[rows + 1, left + 5] = "编码：10000";

}

// 输出Excel文件并退出

try

{

//（网上是11个参数，现在是12个参数）

workBook.SaveAs(outputFile, missing, missing, missing, missing, missing, Excel.XlSaveAsAccessMode.xlExclusive, missing, missing, missing, missing, missing);

workBook.Close(null, null, null);

app.Workbooks.Close();

app.Application.Quit();

app.Quit();

System.Runtime.InteropServices.Marshal.ReleaseComObject(workSheet);

System.Runtime.InteropServices.Marshal.ReleaseComObject(workBook);

System.Runtime.InteropServices.Marshal.ReleaseComObject(app);

workSheet = null;

workBook = null;

app = null;

GC.Collect();

}

catch (Exception e)

{

throw e;

}

finally

{

Process[] myProcesses;

DateTime startTime;

myProcesses = Process.GetProcessesByName(" Excel ");

// 得不到Excel进程ID，暂时只能判断进程启动时间

foreach (Process myProcess in myProcesses)

{

startTime = myProcess.StartTime;

if (startTime > beforeTime && startTime < afterTime)

{

myProcess.Kill();

}

}

}

}

/**/

/// <summary>

/// 获取WorkSheet数量

/// </summary>

/// <param name="rowCount"> 记录总行数 </param>

/// <param name="rows"> 每WorkSheet行数 </param>

private int GetSheetCount(int rowCount, int rows)

{

int n = rowCount % rows; // 余数

if (n == 0)

return rowCount / rows;

else

return Convert.ToInt32(rowCount / rows) + 1;

}

}

}

使用示例：

public partial class _Default : System.Web.UI.Page

{

ExcelHelper.ExcelHelper excel =null;

protected void Page_Load(object sender, EventArgs e)

{

excel = new ExcelHelper.ExcelHelper(Server.MapPath("tempt/BJStock.xls"), Server.MapPath("ExcelFile/test.xls"));

}

protected void Button1_Click(object sender, EventArgs e)

{

excel.DataTableToExcel(DbHelperSQL.DbHelperSQL.GetDataTable("select * from excel"), 2, 1, 1, "Sheet");

Response.Redirect("ExcelFile/test.xls");

}

}