大数据容量 导出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);
        }

转载于:https://www.cnblogs.com/xmyy/articles/2780630.html