背景:一次性导出几十万条数据到excel,此时的导出效率会严重损失。
解决方法:用ibatis中的RowHandler,一次只处理一条数据,内存中只保持一条数据,导出时每5万条创建一个sheet
具体实现方法:
MyRowHandler处理类:
Java代码
- import java.util.Map;
- import org.apache.poi.hssf.usermodel.HSSFCell;
- 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 com.ibatis.sqlmap.client.event.RowHandler;
- public class MyRowHandler implements RowHandler {
- HSSFWorkbook wb;
- HSSFSheet sheet ;
- HSSFRow row;
- HSSFCell cell;
- int dataNum=0;
- int rowStart = 1;
- @Override
- public void handleRow(Object arg0){
- dataNum++;
- if(dataNum%50000==0){
- //每5万条记录分页
- setSheet();
- }
- Map map = (Map)arg0;
- //在这里处理逻辑,插入数据到excel
- System.out.println(map.get("key"));
- System.out.println(map.get("value"));
- }
- public HSSFWorkbook getWb() {
- return wb;
- }
- public void setWb(HSSFWorkbook wb) {
- this.wb = wb;
- setSheet();
- }
- public void setSheet(){
- this.sheet= wb.createSheet();
- rowStart = 1;
- //写表头
- writeHeader();
- }
- public void writeHeader(){
- String[] header = {"ID","类型"};
- row = sheet.createRow(0);
- int n=0;
- for (String head : header) {
- cell = row.createCell((short) n);
- HSSFRichTextString str = new HSSFRichTextString(head);
- cell.setCellValue(str);
- n++;
- }
- }
- }
action中执行:
Java代码
- public String execute(){
- String fileName="F:\\05-JAVA\\测试.xls";
- HSSFWorkbook wb = new HSSFWorkbook();
- MyRowHandler handler = new MyRowHandler();
- handler.setWb(wb);
- DaoFactory.getIbatisDao().getSqlMapClientTemplate().queryWithRowHandler("term.base", handler);
- wb = handler.getWb();
- OutputStream os = null;
- try {
- os = new FileOutputStream(fileName);
- wb.write(os);
- }catch(Exception e){
- }finally{
- try {
- os.close();
- } catch (IOException e) {
- e.printStackTrace();
- }
- }
- return "success";
- }