使用apach-poi导出excel

导包

<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>4.1.0</version>
</dependency>

 public static void main(String[] args) {

        //1、创建一个excel文件
        HSSFWorkbook hssfWorkbook = new HSSFWorkbook();
        //2、创建工作簿
        HSSFSheet sheet = hssfWorkbook.createSheet("按布草");

        HSSFCellStyle style = hssfWorkbook.createCellStyle();
        //设置水平对齐的样式为居中对齐;
        style.setAlignment(HorizontalAlignment.CENTER);
        //设置垂直对齐的样式为居中对齐;
        style.setVerticalAlignment(VerticalAlignment.CENTER);

        //3、创建标题行
        HSSFRow bigTitelRow = sheet.createRow(0);
        bigTitelRow.createCell(0).setCellValue("布草洗涤费统计表");
        bigTitelRow.setRowStyle(style);
        //CellRangeAddress cra = new CellRangeAddress(起始行,结束行 , 起始列, 结束列);

        //4、创建二级标题行
        HSSFRow secondTitel = sheet.createRow(1);

        //总金额

        List<LinenModel> linenModelList = getLinenModelList();
        if (null != linenModelList && linenModelList.size() > 0) {
            String total = "0";
            for (int i = 0; i < linenModelList.size(); i++) {
                HSSFRow row = sheet.createRow(i + 2);
                row.setRowStyle(style);
                if (i == 0) {
                    getCell(hssfWorkbook, secondTitel, 0, "品名／日期");
                    getCell(hssfWorkbook, secondTitel, 1, "单价");
                }
                List<LinenModelSon> linenModelSons = linenModelList.get(i).getLinenModelSons();
                int sum = linenModelSons.stream().mapToInt(LinenModelSon::getQuantity).sum();
                BigDecimal multiply = new BigDecimal(sum).multiply(linenModelList.get(i).getUnPrice());

                //计算总金额
                total = multiply.add(new BigDecimal(total)).toString();

                for (int j = 0; j < linenModelSons.size(); j++) {

                    getCell(hssfWorkbook, secondTitel, j + 2, linenModelSons.get(j).getDate());
                    getCell(hssfWorkbook, row, j + 2, linenModelSons.get(j).getQuantity());

                    if (j == linenModelSons.size() - 1) {
                        getCell(hssfWorkbook, secondTitel, linenModelSons.size() + 1, "数量");
                        getCell(hssfWorkbook, secondTitel, linenModelSons.size() + 2, "金额");

                        getCell(hssfWorkbook, row, linenModelSons.size() + 1, sum);
                        getCell(hssfWorkbook, row, linenModelSons.size() + 2, multiply.toString());
                    }
                }
                getCell(hssfWorkbook, row, 0, linenModelList.get(i).getLinenName());
                getCell(hssfWorkbook, row, 1, linenModelList.get(i).getUnPrice());
            }
            List<LinenModelSon> linenModelSons = linenModelList.get(0).getLinenModelSons();
            CellRangeAddress cra = new CellRangeAddress(0, 0, 0, linenModelSons.size()+4);
            sheet.addMergedRegion(cra);
            //总计行
            HSSFRow totalRow = sheet.createRow(linenModelList.size()+2);
            getCell(hssfWorkbook, totalRow, 0, "总计");
            getCell(hssfWorkbook, totalRow, linenModelSons.size()+2, total);

            CellRangeAddress cra2 = new CellRangeAddress(linenModelList.size()+2, linenModelList.size()+2, 0, linenModelSons.size()+1);

            sheet.addMergedRegion(cra2);
        }



        File savefile = new File("E:/excel/");
        if (!savefile.exists()) {
            savefile.mkdirs();
        }
        FileOutputStream fos = new FileOutputStream("E:/excel/横向遍历/poi测试.xls");
        hssfWorkbook.write(fos);
        fos.close();
    }


    private List<LinenModel> getLinenModelList() {
        List<LinenModelSon> sons = new ArrayList<>();
        for (int i = 0; i < 30; i++) {
            LinenModelSon linenModel = new LinenModelSon(i + "", i + 10);
            sons.add(linenModel);
        }
        List<LinenModel> result = new ArrayList<>();
        for (int i = 0; i < 5; i++) {
            LinenModel linenModel = new LinenModel("布草" + i, new BigDecimal("0.01"), sons);
            result.add(linenModel);
        }
        return result;
    }

    private HSSFCell getCell(HSSFWorkbook wb, HSSFRow row, int num, Object object){
        HSSFCellStyle style = wb.createCellStyle();
        //设置水平对齐的样式为居中对齐;
        style.setAlignment(HorizontalAlignment.CENTER);
        //设置垂直对齐的样式为居中对齐;
        style.setVerticalAlignment(VerticalAlignment.CENTER);

        //边框实线
        //下边框
        style.setBorderBottom(BorderStyle.THIN);
        //左边框
        style.setBorderLeft(BorderStyle.THIN);
        //右边框
        style.setBorderRight(BorderStyle.THIN);
        //上边框
        style.setBorderTop(BorderStyle.THIN);

        HSSFCell cell = row.createCell(num);
        cell.setCellStyle(style);
        cell.setCellValue(object.toString());
        return cell;
    }

 

public class LinenModelSon {
    private String date;

    private int quantity;

    public LinenModelSon(String date, int quantity) {
        this.date = date;
        this.quantity = quantity;
    }

    public String getDate() {
        return date;
    }

    public void setDate(String date) {
        this.date = date;
    }

    public int getQuantity() {
        return quantity;
    }

    public void setQuantity(int quantity) {
        this.quantity = quantity;
    }
}

 

public class LinenModel {

    private String linenName;

    private BigDecimal unPrice;

    private List<LinenModelSon> linenModelSons;


    public LinenModel(String linenName, BigDecimal unPrice, List<LinenModelSon> linenModelSons) {
        this.linenName = linenName;
        this.unPrice = unPrice;
        this.linenModelSons = linenModelSons;
    }

    public String getLinenName() {
        return linenName;
    }

    public void setLinenName(String linenName) {
        this.linenName = linenName;
    }

    public BigDecimal getUnPrice() {
        return unPrice;
    }

    public void setUnPrice(BigDecimal unPrice) {
        this.unPrice = unPrice;
    }

    public List<LinenModelSon> getLinenModelSons() {
        return linenModelSons;
    }

    public void setLinenModelSons(List<LinenModelSon> linenModelSons) {
        this.linenModelSons = linenModelSons;
    }


}