java实现百万级数据导出excel

package com.exportBigExcel;import java.io.FileOutputStream;import java.io.OutputStream;import java.sql.Connection;import java.sql.DatabaseMetaData;import java.sql.DriverManager;import java.sql.ResultSet;import java.sql.Statement;import java.util.ArrayList;import java.util.Date;import java.util.List;import org.apache.poi.hssf.usermodel.HSSFCell;import org.apache.poi.hssf.usermodel.HSSFCellStyle;import org.apache.poi.hssf.usermodel.HSSFFont;import org.apache.poi.hssf.usermodel.HSSFRichTextString;import org.apache.poi.hssf.usermodel.HSSFRow;import org.apache.poi.hssf.usermodel.HSSFSheet;import org.apache.poi.hssf.usermodel.HSSFWorkbook;import org.apache.poi.hssf.util.HSSFColor;public class ExportBigExcel {private static List<String> fileNames = new ArrayList<String>();//生成的所有表名及路径@SuppressWarnings("unchecked")public static void export() throws Exception{//声明需要导出数据库String dataBaseName = "reward";//获取数据库连接Class.forName("com.mysql.jdbc.Driver");Connection conn = DriverManager.getConnection("***", "***", "***");//声明statementDatabaseMetaData databaseMetaData = conn.getMetaData();//获取所有表ResultSet resultSet = databaseMetaData.getTables(dataBaseName, dataBaseName,null, new String[]{"TABLE"});//获取所有表名字List<String> tables = new ArrayList<String>();while(resultSet.next()){String table_name = resultSet.getString("TABLE_NAME");System.out.println("表名： " + table_name);tables.add(table_name);}Statement statement = conn.createStatement();for(String tableName : tables){//获取每张表的总记录数resultSet = statement.executeQuery("select count(*) from " + dataBaseName + "." + tableName);int count = 0;if(resultSet.next()){count = resultSet.getInt(1);//获取每张表的记录数}int rowCount = 60000;//每页最大数int pageCount = 0;//总页数，（转换成总的excel文件的个数）int sign = 0;if(count % rowCount == 0){pageCount = count / rowCount;}else{pageCount = count / rowCount + 1;}//分页获取数据，防止内存溢出for(int i = 0; i < pageCount; i++){/** * 获取所有列名 */ResultSet rs = statement.executeQuery("show full columns from " + tableName); List<String> headers = new ArrayList<String>();if (rs != null && rs.next()) {              while (rs.next()) {                     headers.add( rs.getString("Comment"));            } }/** * 添加行数据 */resultSet = statement.executeQuery("select * from " + dataBaseName + "." + tableName + " limit " + sign + "," + rowCount);sign += rowCount;List<List<String>> data = new ArrayList<List<String>>();while(resultSet.next()){@SuppressWarnings("rawtypes")List rowData = new ArrayList();for(int j = 0; j < headers.size(); j++){String val = resultSet.getString(j + 1);rowData.add(val);}data.add(rowData);}/** * 导出到excel */Thread thread = new ExportExcelUtil(i, tableName, headers, data);thread.start();}}}//返回输出的表名public static List<String> getFileNames(){return fileNames;}//添加输出的表名public static void callBackFileName(String fileName){fileNames.add(fileName);}}//到处到excel，采用多线程class ExportExcelUtil extends Thread{int number;//第多少张表String sheetTitle;//表名List<String> headers;//列名List<List<String>> result;//行数据public ExportExcelUtil(int number, String sheetTitle,List<String> headers, List<List<String>> result) {super();this.number = number;this.sheetTitle = sheetTitle;this.headers = headers;this.result = result;}@Overridepublic void run() {try {exportExcel(sheetTitle, number, headers, result);} catch (Exception e) {e.printStackTrace();};}/** * @Title: exportExcel * @Description: 导出Excel的方法 * @param workbook  * @param sheetNum (sheet的位置，0表示第一个表格中的第一个sheet) * @param sheetTitle  （sheet的名称） * @param headers    （表格的标题） * @param result   （表格的数据） * @param out  （输出流） * @throws Exception */public void exportExcel(String sheetTitle,int number,List<String> headers, List<List<String>> result) throws Exception {OutputStream out = new FileOutputStream("F:/java/works/reward" + sheetTitle + number + ".xls");System.out.println("F:/java/works/reward/" + sheetTitle + number + ".xls");// 生成一个表格//声明工作薄HSSFWorkbook workbook = new HSSFWorkbook();HSSFSheet sheet = workbook.createSheet();// 设置表格默认列宽度为20个字节sheet.setDefaultColumnWidth((short) 20);workbook.setSheetName(0,sheetTitle);// 生成一个样式HSSFCellStyle style = workbook.createCellStyle();// 设置这些样式style.setFillForegroundColor(HSSFColor.PALE_BLUE.index);style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);style.setBorderBottom(HSSFCellStyle.BORDER_THIN);style.setBorderLeft(HSSFCellStyle.BORDER_THIN);style.setBorderRight(HSSFCellStyle.BORDER_THIN);style.setBorderTop(HSSFCellStyle.BORDER_THIN);style.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 生成一个字体HSSFFont font = workbook.createFont();font.setColor(HSSFColor.BLACK.index);font.setFontHeightInPoints((short) 12);font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);// 把字体应用到当前的样式style.setFont(font);// 指定当单元格内容显示不下时自动换行style.setWrapText(true);// 产生表格标题行HSSFRow row = sheet.createRow(0);for (int i = 0; i < headers.size(); i++) {HSSFCell cell = row.createCell((short) i);cell.setCellStyle(style);HSSFRichTextString text = new HSSFRichTextString(headers.get(i));cell.setCellValue(text.toString());}// 遍历集合数据，产生数据行if (result != null) {int index = 1;for (List<String> m : result) {row = sheet.createRow(index);int cellIndex = 0;for (String str : m) {HSSFCell cell = row.createCell((short) cellIndex);cell.setCellValue(str);cellIndex++;}index++;}}workbook.write(out);out.close();ExportBigExcel.callBackFileName(sheetTitle + number + ".xls");//回调函数输出表名}}