POI 导出

package com.baoqilai.scp.service;

import java.io.File;
import java.io.FileOutputStream;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.junit.Test;

import com.baoqilai.base.service.export.CustomExportServiceImpl;
import com.baoqilai.base.service.export.HeadlessExportServiceImpl;
import com.baoqilai.base.service.export.TemplateExportServiceImpl;
import com.baoqilai.scp.service.export.ExcelExportDataStragy;
import com.baoqilai.scp.service.export.ExcelExportService;
import com.baoqilai.scp.service.export.ExcelExportStragyImpl;

public class ExcelExportTest {

    @Test
    public void testHeadless() {
        ExcelExportService excelExportService=new HeadlessExportServiceImpl();
        List<Map<String, Object>> data=new ArrayList<>();
        Map<String, Object> map=new HashMap<String, Object>();
        map.put("BigDecimal", 3.0300000000000002);
        map.put("Double", 1.33);
        map.put("Float", 1.1);
        map.put("Long", 10L);
        map.put("Integer", 5);
        map.put("int", 6);
        map.put("date", new Date());
        map.put("String", "李白");
        data.add(map);
        map=new HashMap<String, Object>();
        map.put("BigDecimal", 3.0300000000000002);
        map.put("Double", 1.33);
        map.put("Float", 1.1);
        map.put("Long", 10L);
        map.put("Integer", 5);
        map.put("int", 6);
        map.put("date", new Date());
        map.put("String", "鲁班");
        data.add(map);
        ExcelExportDataStragy dataStragy=new ExcelExportDataStragy();
        dataStragy.setStrategy(new ExcelExportStragyImpl());
        SXSSFWorkbook sx=excelExportService.exportByStragegy(data, dataStragy);
        File  f = new File("E:/ziyuan/test.xlsx");  
        try {
            FileOutputStream fos = new FileOutputStream(f);
            sx.write(fos);  
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    
    @Test
    public void testCustom() {
        ExcelExportService excelExportService=new CustomExportServiceImpl();
        List<Map<String, Object>> data=new ArrayList<>();
        Map<String, Object> map=new HashMap<String, Object>();
        map.put("BigDecimal", 3.0300000000000002);
        map.put("Double", 1.33);
        map.put("Float", 1.1);
        map.put("Long", 10L);
        map.put("Integer", 5);
        map.put("int", 6);
        map.put("date", new Date());
        map.put("String", "李白");
        data.add(map);
        map=new HashMap<String, Object>();
        map.put("BigDecimal", 3.0300000000000002);
        map.put("Double", 1.33);
        map.put("Float", 1.1);
        map.put("Long", 10L);
        map.put("Integer", 5);
        map.put("int", 6);
        map.put("date", new Date());
        map.put("String", "鲁班");
        data.add(map);
//        ExcelExportStragy stragegy =new ExcelExportStragyImpl();

        String[] title={"序号","BigDecimal类型","Double类型","Float类型","Long类型","Integer类型","int类型","date类型","String类型"};
        String[] result={"serialNum","BigDecimal","Double","Float","Long","Integer","int","date","String"};
        
        ExcelExportDataStragy dataStragy=new ExcelExportDataStragy();
        dataStragy.setStrategy(new ExcelExportStragyImpl());
        dataStragy.setTitle(title);
        dataStragy.setResult(result);
        SXSSFWorkbook sx=excelExportService.exportByStragegy(data, dataStragy);
        File  f = new File("E:/ziyuan/test3.xlsx");  
        try {
            FileOutputStream fos = new FileOutputStream(f);
            sx.write(fos);  
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    
    
    @Test
    public void testTemplate() {
        ExcelExportService excelExportService=new TemplateExportServiceImpl();
        List<Map<String, Object>> data=new ArrayList<>();
        Map<String, Object> map=new LinkedHashMap<String, Object>();
        map.put("BigDecimal", 3.0300000000000002);
        map.put("Double", 1.33);
        map.put("Float", 1.1);
        map.put("Long", 10L);
        map.put("Integer", 5);
        map.put("int", 6);
        map.put("date", new Date());
        map.put("String", "李白");
        data.add(map);
        map=new LinkedHashMap<String, Object>();
        map.put("BigDecimal", 3.0300000000000002);
        map.put("Double", 1.33);
        map.put("Float", 1.1);
        map.put("Long", 10L);
        map.put("Integer", 5);
        map.put("int", 6);
        map.put("date", new Date());
        map.put("String", "鲁班1");
        data.add(map);
        map=new LinkedHashMap<String, Object>();
        map.put("BigDecimal", 3.0300000000000002);
        map.put("Double", 1.33);
        map.put("Float", 1.1);
        map.put("Long", 10L);
        map.put("Integer", 5);
        map.put("int", 6);
        map.put("date", new Date());
        map.put("String", "鲁班2");
        data.add(map);
        map=new LinkedHashMap<String, Object>();
        map.put("BigDecimal", 3.0300000000000002);
        map.put("Double", 1.33);
        map.put("Float", 1.1);
        map.put("Long", 10L);
        map.put("Integer", 5);
        map.put("int", 6);
        map.put("date", new Date());
        map.put("String", "鲁班3");
        data.add(map);
//        ExcelExportStragy stragegy =new ExcelExportStragyImpl();
        String tempAddress="E:/ziyuan/test4.xlsx";
        String[] result={"serialNum","BigDecimal","Double","Float","Long","Integer","int","date","String"};
//        stragegy.setTempAddress(tempAddress);
//        stragegy.setResult(result);
        
        ExcelExportDataStragy dataStragy=new ExcelExportDataStragy();
        dataStragy.setStrategy(new ExcelExportStragyImpl());
        dataStragy.setTempAddress(tempAddress);
        dataStragy.setResult(result);
        SXSSFWorkbook sx=excelExportService.exportByStragegy(data, dataStragy);
        
//        SXSSFWorkbook sx=excelExportService.exportByStragegy(data, stragegy);
        File  f = new File("E:/ziyuan/test5.xlsx");  
        try {
            FileOutputStream fos = new FileOutputStream(f);
            sx.write(fos);  
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

}

package com.baoqilai.base.service.export;

import java.util.HashMap;
import java.util.Iterator;
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.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.stereotype.Service;

import com.baoqilai.scp.exception.BaseException;
import com.baoqilai.scp.service.export.ExcelExportDataStragy;
import com.baoqilai.scp.service.export.ExcelExportService;
/**
 * 模板导出
 * @author lly
 *
 */
@Service
public class TemplateExportServiceImpl implements ExcelExportService {


    @Override
    public SXSSFWorkbook export(List<Map<String, Object>> data) throws BaseException {
        
        return null;
    }


    @Override
    public SXSSFWorkbook exportByStragegy(List<Map<String, Object>> data, ExcelExportDataStragy excelExportStragy)
            throws BaseException {
        long stime = System.currentTimeMillis();
        try {
            
            XSSFWorkbook wb = excelExportStragy.getStrategy().getXSSFWorkbook(excelExportStragy.getTempAddress());
            
            int lastRowNum = wb.getSheetAt(0).getLastRowNum();
            Sheet sheet0 = wb.getSheetAt(0);
            Row baseRow0=sheet0.getRow(2);
            lastRowNum = wb.getSheetAt(0).getLastRowNum();

            Map<Integer, String> gsMap=new HashMap<>();
            
            for (Iterator<Cell> it = baseRow0.cellIterator(); it.hasNext();) {
                Cell baseCell = it.next();
                if (baseCell.getCellType() == Cell.CELL_TYPE_FORMULA) {
                    String cellFormula = baseCell.getCellFormula();
                    gsMap.put(baseCell.getColumnIndex(), cellFormula);
                }
            }
            sheet0.removeRow(baseRow0); //取到公式后进行删除
            
            SXSSFWorkbook workbook =new SXSSFWorkbook(wb,500);
            Sheet sheet = workbook.getSheetAt(0);
            
            final int startRow =lastRowNum;
            for (int i = startRow; i < data.size() + startRow; i++) {
                int rowNum = i - startRow;
                Row row = sheet.getRow(i);
                if (row == null) {
                    row = sheet.createRow(i);
                }
                Map<String, Object> dataMap = data.get(rowNum);

                String[] columNames = excelExportStragy.getResult();
                dataMap.put("serialNum", rowNum + 1);

                for (int j = 0; j < columNames.length; j++) {
                    Cell cell = row.getCell(j);
                    if (cell == null) {
                        cell = row.createCell(j);
                    }
                
                    Object val = dataMap.get(columNames[j]);
                    excelExportStragy.getStrategy().setCellValue(cell, val);
                    if(gsMap!=null&&gsMap.get(cell.getColumnIndex())!=null){
                        String cellFormula =gsMap.get(cell.getColumnIndex());
                        String s = cellFormula.replaceAll("(\\w)\\d", "$1" + (i + 1));
                        cell.setCellFormula(s);
                        cell.setCellType(Cell.CELL_TYPE_FORMULA);
                    }
                }
                dataMap.clear();
                // 清空内存中缓存的行数
                if (i % 500 == 0) {
                    ((SXSSFSheet) sheet).flushRows();
                }
            }
            // 数据清理
            data.clear();
            data = null;
            workbook.setForceFormulaRecalculation(true);//计算公式
            long etime = System.currentTimeMillis();
            System.out.println("处理写入模板数据用时：" + (etime - stime) / 1000);
            return workbook;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }

    

}

package com.baoqilai.base.service.export;

import java.io.IOException;
import java.util.List;
import java.util.Map;
import java.util.Set;

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.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.springframework.stereotype.Service;

import com.baoqilai.scp.exception.BaseException;
import com.baoqilai.scp.service.export.ExcelExportDataStragy;
import com.baoqilai.scp.service.export.ExcelExportService;
/**
 * 无头导出
 * @author lly
 *
 */
@Service
public class HeadlessExportServiceImpl implements ExcelExportService {

    @Override
    public SXSSFWorkbook export(List<Map<String, Object>> data) throws BaseException {
        
        return null;
    }

    @Override
    public SXSSFWorkbook exportByStragegy(List<Map<String, Object>> data, ExcelExportDataStragy excelExportStragy)
            throws BaseException {
        long stime = System.currentTimeMillis();
        SXSSFWorkbook wb = excelExportStragy.getStrategy().getSXSSFWorkbook(); // 设置最大行数，如果不想做限制可以设置为-1
        // 创建第一个sheet（页），并命名
        Sheet sheet = wb.createSheet("1");
        // 创建第一行
        Row row = sheet.createRow((short) 0);

        // 创建列（每行里的单元格）
        Cell cell = null ;
        Map<String, Object> row1 = data.get(0);
        Set<String> keys1 = row1.keySet();
        int rowNum2 = 0;
        for (String key: keys1) {
            cell = row.createCell(rowNum2);
            cell.setCellValue(key);
            rowNum2 ++;
        }
        
        for (short i = 0; i < data.size(); i++) {
            Map<String, Object> map = data.get(i);
            // 创建一行，在页sheet上
            row = sheet.createRow((short) i + 1);
            Set<String> keys = map.keySet();
            int rowNum = 0;
            for (String key: keys) {
                cell = row.createCell(rowNum);
                excelExportStragy.getStrategy().setCellValue(cell, map.get(key));
                rowNum ++;
            }
            map.clear();
            // 清空内存中缓存的行数
            if (i % 500 == 0) {
                try {
                    ((SXSSFSheet) sheet).flushRows();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
        data.clear();
        data = null;
        long etime = System.currentTimeMillis();
        System.out.println("处理写入自定义数据用时：" + (etime - stime) / 1000);
        return wb;
    }

}

package com.baoqilai.base.service.export;

import java.io.IOException;
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.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.springframework.stereotype.Service;

import com.baoqilai.scp.exception.BaseException;
import com.baoqilai.scp.service.export.ExcelExportDataStragy;
import com.baoqilai.scp.service.export.ExcelExportService;
/**
 * 自定义头导出
 * @author lly
 *
 */
@Service
public class CustomExportServiceImpl implements ExcelExportService{


    @Override
    public SXSSFWorkbook export(List<Map<String, Object>> data) throws BaseException {
        
        return null;
    }

    @Override
    public SXSSFWorkbook exportByStragegy(List<Map<String, Object>> data, ExcelExportDataStragy excelExportStragy)
            throws BaseException {
        long stime = System.currentTimeMillis();
        SXSSFWorkbook wb =excelExportStragy.getStrategy().getSXSSFWorkbook(); // 设置最大行数，如果不想做限制可以设置为-1
        // 创建第一个sheet（页），并命名
        Sheet sheet = wb.createSheet("1");
        // 创建第一行
        Row row = sheet.createRow((short) 0);
        // 生成表头
        int i = 0;
        String[] title=excelExportStragy.getTitle();
        String[] result=excelExportStragy.getResult();
        Cell cell = row.createCell(i);
        for (; i < title.length; i++) {
            cell = row.createCell(i);
            cell.setCellValue(title[i]);
        }
        // 写入
        for (i = 0; i < data.size(); i++) {
            Map<String, Object> map = data.get(i);
            
            map.put("serialNum", i + 1);
            row = sheet.createRow(i + 1);
            int j = 0;
            for (; j < result.length; j++) {
                String col = result[j];
                cell = row.createCell(j);
                excelExportStragy.getStrategy().setCellValue(cell, map.get(col));
            }
            map.clear();
            // 清空内存中缓存的行数
            if (i % 500 == 0) {
                try {
                    ((SXSSFSheet) sheet).flushRows();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
        data.clear();
        data = null;
        long etime = System.currentTimeMillis();
        System.out.println("处理写入自定义数据用时：" + (etime - stime) / 1000);
        return wb;
    }



}

package com.baoqilai.scp.service.export;

public class ExcelExportDataStragy {

    //持有一个具体的策略对象
    private ExcelExportStragy strategy;
    private String[] title;
    private String[] result;
    private String tempAddress;
    
    public ExcelExportDataStragy() {
        super();
    }
    public ExcelExportStragy getStrategy() {
        return strategy;
    }
    public void setStrategy(ExcelExportStragy strategy) {
        this.strategy = strategy;
    }
    public String[] getTitle() {
        return title;
    }
    public void setTitle(String[] title) {
        this.title = title;
    }
    public String[] getResult() {
        return result;
    }
    public void setResult(String[] result) {
        this.result = result;
    }
    public String getTempAddress() {
        return tempAddress;
    }
    public void setTempAddress(String tempAddress) {
        this.tempAddress = tempAddress;
    }
    
    
    
}

package com.baoqilai.scp.service.export;

import java.io.File;
import java.io.FileInputStream;
import java.util.HashMap;
import java.util.Iterator;
import java.util.Map;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.DataFormat;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class ExcelExportStragyImpl implements ExcelExportStragy {

    
    @Override
    public Row getHeaders() {
        SXSSFWorkbook wb = getSXSSFWorkbook(); // 设置最大行数，如果不想做限制可以设置为-1
        // 创建第一个sheet（页），并命名
        Sheet sheet = wb.createSheet("1");
        Row row = sheet.createRow((short) 0);
        return row;
    }

    @Override
    public Row getNextRow() {
        
        return null;
    }

    @Override
    public CellStyle getCellStyle() {
        SXSSFWorkbook wb = getSXSSFWorkbook();
        CellStyle contextstyle = wb.createCellStyle();
        DataFormat df = wb.createDataFormat();
        contextstyle.setDataFormat(df.getFormat("#,##0.00"));
        return contextstyle;
    }

    @Override
    public void setCellValue(Cell cell, Object val) {
        if (val == null) {
            cell.setCellValue("");
        } else if (val instanceof java.math.BigDecimal) {
            cell.setCellType(Cell.CELL_TYPE_NUMERIC);
            cell.setCellValue(((java.math.BigDecimal) val).doubleValue());
            cell.setCellStyle(getCellStyle());
        } else if (val instanceof Double) {
            cell.setCellType(Cell.CELL_TYPE_NUMERIC);
            cell.setCellValue((((Double) val).doubleValue()));
            cell.setCellStyle(getCellStyle());
        } else if (val instanceof Float) {
            cell.setCellType(Cell.CELL_TYPE_NUMERIC);
            cell.setCellValue((((Float) val).floatValue()));
            cell.setCellStyle(getCellStyle());
        } else if (val instanceof Long) {
            cell.setCellType(Cell.CELL_TYPE_NUMERIC);
            cell.setCellValue(((Long) val).intValue());
        } else if (val instanceof Integer) {
            cell.setCellType(Cell.CELL_TYPE_NUMERIC);
            cell.setCellValue(((Integer) val).intValue());
        } else {
            cell.setCellType(Cell.CELL_TYPE_STRING);
            cell.setCellValue(val.toString());
        }
    }

    @Override
    public SXSSFWorkbook getSXSSFWorkbook() {
        SXSSFWorkbook wb = new SXSSFWorkbook(500); // 设置最大行数，如果不想做限制可以设置为-1
        wb.setCompressTempFiles(false);
        return wb;
    }
    @Override
    public XSSFWorkbook getXSSFWorkbook(String tempAddress){
        try {
            File fi = new File(tempAddress);
            FileInputStream is = new FileInputStream(fi);
            XSSFWorkbook wb = new XSSFWorkbook(is);
            return wb;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }

}