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;

    }

}