/// <summary>
/// 将DataTable数据写入Excel文件（自动分页）
/// </summary>
/// <param name="dt">DataTable </param>
/// <param name="rows">每个WorkSheet写入多少行数据 </param>
/// <param name="top">表格数据起始行索引 </param>
/// <param name="left">表格数据起始列索引 </param>
public void DataTableToExcel(DataTable dt,int rows,int top,int left)
{
int rowCount = dt.Rows.Count;//DataTable行数
int colCount = dt.Columns.Count;//DataTable列数
sheetCount = this.GetSheetCount(rowCount,rows);//WorkSheet个数
//StringBuilder sb;

//复制sheetCount-1个WorkSheet对象
for(int i=1;i <sheetCount;i++)
{
workSheet = (Excel.Worksheet)workBook.Worksheets.get_Item(i);
workSheet.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对象，并重命名
workSheet = (Excel.Worksheet)workBook.Worksheets.get_Item(i);
workSheet.Name = sheetPrefixName + "-" + i.ToString();

//将dt中的数据写入WorkSheet
//for(int j=0;j <endRow-startRow;j++)
//{
//for(int k=0;k <colCount;k++)
//{
//workSheet.Cells[top + j,left + k] = dt.Rows[startRow + j][k].ToString();
//}
//}

//利用二维数组批量写入
int row = endRow-startRow;
string[,] ss = new string[row,colCount];

for(int j=0;j <row;j++)
{
for(int k=0;k <colCount;k++)
{
ss[j,k] = dt.Rows[startRow + j][k].ToString();
}
}

range = (Excel.Range)workSheet.Cells[top,left];
range = range.get_Resize(row,colCount);
range.Value = ss;

#region 利用Windwo粘贴板批量拷贝数据（在Web下面行不通）
/*sb = new StringBuilder();

for(int j=0;j <endRow-startRow;j++)
{
for(int k=0;k <colCount;k++)
{
sb.Append( dt.Rows[startRow + j][k].ToString() );
sb.Append("\t");
}

sb.Append("\n");
}

System.Windows.Forms.Clipboard.SetDataObject(sb.ToString());

range = (Excel.Range)workSheet.Cells[top,left];
workSheet.Paste(range,false);*/
#endregion

}
}