java使用poi导出大量数据

使用POI导出数据库大量数据，使用的主要jar包：poi-3.8-20120326.jar、poi-ooxml-3.8-20120326.jar

 

样例代码：

import java.io.FileOutputStream;
import java.util.List;
import java.util.Map;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;


public class LotExport {

	public static void main(String[] args) {
		try {
			exportLot();
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	/**
	 * 
	 * 大批量导出
	 * @throws Exception
	 */
	public static void exportLot() throws Exception  {
		
		String sql = "SELECT COUNT(*) FROM T_QR_B";
		int count = DBUtil.count(sql);
		int limit = 60000;
		
		int pageNumber = (count%limit==0) ? (count/limit) : (count/limit+1);
		
		// 创建Excel的工作书册 Workbook,对应到一个excel文档
		Workbook wb = new SXSSFWorkbook(1000); // keep 100 rows in memory, exceeding rows will be flushed to disk   
		
		for(int p=0; p<pageNumber; p++) {//分页查询导出数据
			
			sql = "SELECT TOP " + limit + " * FROM (SELECT * FROM T_QR_B) T1"
				+" WHERE ROW_ID NOT IN (SELECT TOP " + p*limit + " ROW_ID FROM (SELECT * FROM T_QR_B) T2 ORDER BY ROW_ID)"
				+" ORDER BY ROW_ID";
			List list = DBUtil.execQuery(sql);
			
			// 创建Excel的工作sheet,对应到一个excel文档的tab
			Sheet sheet = wb.createSheet("sheet" + p);

			Row row = null;
			Cell cell = null;
			
			//设置标题
			row = sheet.createRow(0);
			
			cell = row.createCell(0);
			cell.setCellValue("序号");
			cell = row.createCell(1);
			cell.setCellValue("QR");
			cell = row.createCell(2);
			cell.setCellValue("OWNER_ID");
			cell = row.createCell(3);
			cell.setCellValue("");
			cell = row.createCell(4);
			cell.setCellValue("SKU_ID");
			cell = row.createCell(5);
			cell.setCellValue("OWNER_PO_NO");
			cell = row.createCell(6);
			cell.setCellValue("QTY");
			cell = row.createCell(7);
			cell.setCellValue("EXTERNAL_LOT");
			cell = row.createCell(8);
			cell.setCellValue("WORK_NO");
			cell = row.createCell(9);
			cell.setCellValue("PRODUCE_DATE");
			cell = row.createCell(10);
			cell.setCellValue("LASER1");
			cell = row.createCell(11);
			cell.setCellValue("LASER2");
			cell = row.createCell(12);
			cell.setCellValue("CHECK_CODE");
			cell = row.createCell(13);
			cell.setCellValue("CARTON_NUMBER");
			cell = row.createCell(14);
			cell.setCellValue("PALLET_NUMBER");
			cell = row.createCell(15);
			cell.setCellValue("PACKING_LINE");
			cell = row.createCell(16);
			cell.setCellValue("IS_ACTIVE");
			cell = row.createCell(17);
			cell.setCellValue("INVENTORY_STATUS");
			cell = row.createCell(18);
			cell.setCellValue("SCAN_DATE");
			cell = row.createCell(19);
			cell.setCellValue("REMARK");
			cell = row.createCell(20);
			cell.setCellValue("CREATED_BY");
			cell = row.createCell(21);
			cell.setCellValue("CREATED_DATE");
			cell = row.createCell(22);
			cell.setCellValue("UPDATED_BY");
			cell = row.createCell(23);
			cell.setCellValue("UPDATED_DATE");
			
			for(int i=0; i<list.size(); i++) {
				Map map = (Map) list.get(i);
				//System.out.println("p:"+p + ",  i:" +i);
				row = sheet.createRow(i + 1);
				
				cell = row.createCell(0);
				cell.setCellValue((null==map.get("ROW_ID")) ? "" : map.get("ROW_ID").toString());
				cell = row.createCell(1);
				cell.setCellValue((null==map.get("QR")) ? "" : map.get("QR").toString());
				cell = row.createCell(2);
				cell.setCellValue((null==map.get("OWNER_ID")) ? "" : map.get("OWNER_ID").toString());
				cell = row.createCell(3);
				cell.setCellValue((null==map.get("SKU_ID")) ? "" : map.get("SKU_ID").toString());
				cell = row.createCell(4);
				cell.setCellValue((null==map.get("BATCH_NO")) ? "" : map.get("BATCH_NO").toString());
				cell = row.createCell(5);
				cell.setCellValue((null==map.get("OWNER_PO_NO")) ? "" : map.get("OWNER_PO_NO").toString());
				cell = row.createCell(6);
				cell.setCellValue((null==map.get("QTY")) ? "" : map.get("QTY").toString());
				cell = row.createCell(7);
				cell.setCellValue((null==map.get("EXTERNAL_LOT")) ? "" : map.get("EXTERNAL_LOT").toString());
				cell = row.createCell(8);
				cell.setCellValue((null==map.get("WORK_NO")) ? "" : map.get("WORK_NO").toString());
				cell = row.createCell(9);
				cell.setCellValue((null==map.get("PRODUCE_DATE")) ? "" : map.get("PRODUCE_DATE").toString());
				cell = row.createCell(10);
				cell.setCellValue((null==map.get("LASER1")) ? "" : map.get("LASER1").toString());
				cell = row.createCell(11);
				cell.setCellValue((null==map.get("LASER2")) ? "" : map.get("LASER2").toString());
				cell = row.createCell(12);
				cell.setCellValue((null==map.get("CHECK_CODE")) ? "" : map.get("CHECK_CODE").toString());
				cell = row.createCell(13);
				cell.setCellValue((null==map.get("CARTON_NUMBER")) ? "" : map.get("CARTON_NUMBER").toString());
				cell = row.createCell(14);
				cell.setCellValue((null==map.get("PALLET_NUMBER")) ? "" : map.get("PALLET_NUMBER").toString());
				cell = row.createCell(15);
				cell.setCellValue((null==map.get("PACKING_LINE")) ? "" : map.get("PACKING_LINE").toString());
				cell = row.createCell(16);
				cell.setCellValue((null==map.get("IS_ACTIVE")) ? "" : map.get("IS_ACTIVE").toString());
				cell = row.createCell(17);
				cell.setCellValue((null==map.get("INVENTORY_STATUS")) ? "" : map.get("INVENTORY_STATUS").toString());
				cell = row.createCell(18);
				cell.setCellValue((null==map.get("SCAN_DATE")) ? "" : map.get("SCAN_DATE").toString());
				cell = row.createCell(19);
				cell.setCellValue((null==map.get("REMARK")) ? "" : map.get("REMARK").toString());
				cell = row.createCell(20);
				cell.setCellValue((null==map.get("CREATED_BY")) ? "" : map.get("CREATED_BY").toString());
				cell = row.createCell(21);
				cell.setCellValue((null==map.get("CREATED_DATE")) ? "" : map.get("CREATED_DATE").toString());
				cell = row.createCell(22);
				cell.setCellValue((null==map.get("UPDATED_BY")) ? "" : map.get("UPDATED_BY").toString());
				cell = row.createCell(23);
				cell.setCellValue((null==map.get("UPDATED_DATE")) ? "" : map.get("UPDATED_DATE").toString());
			}
			System.out.println("第" + p + "页导出成功");
		}

		FileOutputStream os = new FileOutputStream("D:\\report\\workbook.xlsx");
		wb.write(os);
		os.close();
		
		System.out.println("导出成功");
	}
}