大数据容量 导出Excel分表,引用组件ExcelLibrary.dll
//attachName:保存文件名
//tab:表DataTable
//sheetName:Excel sheet名
//RowOfSheet:Excel每页sheet 大小
public static void DataTable2Excel(string attachName, DataTable tab,string sheetName,int RowOfSheet)
{
string file = "d:\\"+attachName+".xls";
Workbook workbook = new Workbook();
int page = tab.Rows.Count / RowOfSheet + 1;
for (int currPage = 0; currPage < page; currPage++)
{
Worksheet worksheet = new Worksheet( Convert.ToString(sheetName) + ":" + (currPage+1));
//构建表头
for (int col = 0; col < tab.Columns.Count; col++)
{
worksheet.Cells[0, col] = new Cell(tab.Columns[col].ColumnName);
}
for (int rowIndex = 0; rowIndex < RowOfSheet - 1; rowIndex++)
{
for (int colIndex = 0; colIndex < tab.Columns.Count; colIndex++)
{
if (currPage * RowOfSheet + rowIndex < tab.Rows.Count)
worksheet.Cells[rowIndex + 1, colIndex] = new Cell(tab.Rows[currPage * RowOfSheet + rowIndex][colIndex].ToString());
}
}
//设置默认宽度
if (worksheet.Cells.LastRowIndex > 1)
{
for (int n = 0; n < tab.Columns.Count; n++)
{
if (worksheet.Cells[1, n].Value.ToString().Length >= 50)
worksheet.Cells.ColumnWidth[(ushort)n] = 10000;
else if (worksheet.Cells[1, n].Value.ToString().Length >= 10)
worksheet.Cells.ColumnWidth[(ushort)n] = (ushort)(worksheet.Cells[1, n].Value.ToString().Length * 300);
else
worksheet.Cells.ColumnWidth[(ushort)n] = 3000;
}
}
else
worksheet.Cells.ColumnWidth[0, (ushort)(tab.Columns.Count - 1)] = 3000;
workbook.Worksheets.Add(worksheet);
}
workbook.Save(file);
}