欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

Java导出完整版模板(export)

程序员文章站 2022-03-15 12:58:30
...

1.在pom引入jra包

<!--导入导出excel-->
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>3.15</version>
    <exclusions>
        <exclusion>
            <groupId>stax</groupId>
            <artifactId>stax-api</artifactId>
        </exclusion>
    </exclusions>
</dependency>

2.导出模板(util文件)

import lombok.extern.slf4j.Slf4j;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.xssf.streaming.SXSSFCell;
import org.apache.poi.xssf.streaming.SXSSFRow;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.LinkedHashMap;
import java.util.List;

@Slf4j
public class ExportExcelStandard {
    private String sheetName; // 导出表格的表名
    private String[] rowName;// 导出表格的列名
    private List<LinkedHashMap<String, Object>> dataList = new ArrayList<>(); // 对象数组的List集合
    private HttpServletResponse response;
    private HttpServletRequest request;
    private String fileName;
    private String filePath;

    public ExportExcelStandard(String sheetName, String[] rowName, List<LinkedHashMap<String, Object>> dataList, HttpServletRequest request, HttpServletResponse response) {
        this.rowName = rowName;
        this.dataList = dataList;
        this.response = response;
        this.request = request;
        this.sheetName = sheetName;
    }

    public ExportExcelStandard(String sheetName, String fileName, String[] rowName, List<LinkedHashMap<String, Object>> dataList, HttpServletRequest request, HttpServletResponse response) {
        this.rowName = rowName;
        this.dataList = dataList;
        this.fileName = fileName;
        this.response = response;
        this.request = request;
        this.sheetName = sheetName;
    }

    public ExportExcelStandard(String sheetName, String[] rowName, List<LinkedHashMap<String, Object>> dataList, String fileName, String filePath) {
        this.rowName = rowName;
        this.dataList = dataList;
        this.sheetName = sheetName;
        this.fileName = fileName;
        this.filePath = filePath;
    }

    public ExportExcelStandard() {
    }
    
    // 导出数据
    public void exportData() throws Exception {
        log.info("excel导出创建work");
        SXSSFWorkbook workbook = new SXSSFWorkbook();//声明一个工作薄 Excel 2007 OOXML (.xlsx)格式
        SXSSFSheet sheet = workbook.createSheet(sheetName); // 创建表格
        for (int i = 1; i < rowName.length; i++) { //根据列名设置每一列的宽度
            int length = rowName[i].toString().length();
            sheet.setColumnWidth(i, 2 * (length + 1) * 256);
        }
        // sheet样式定义
        CellStyle columnTopStyle = ExportExcelUtil.getColumnTopStyle(workbook, 14); // 头样式
        CellStyle columnStyle = ExportExcelUtil.getColumnStyle(workbook, 12); // 标题样式
        CellStyle style = ExportExcelUtil.getStyle(workbook, 11);  // 单元格样式
        CellStyle styleError = ExportExcelUtil.getStyleError(workbook, 11);  // 单元格样式
        // 产生第一行(列名)
        int columnNum = rowName.length;  // 表格列的长度
        SXSSFRow rowRowName = sheet.createRow(0);  // 在第二行创建行
        rowRowName.setHeightInPoints(21f);
        CellStyle cells = workbook.createCellStyle();
        cells.setBottomBorderColor(HSSFColor.BLACK.index);
        rowRowName.setRowStyle(cells);
        for (int i = 0; i < columnNum; i++) {
            SXSSFCell cellRowName = rowRowName.createCell(i);
            cellRowName.setCellType(SXSSFCell.CELL_TYPE_STRING); // 单元格类型
            XSSFRichTextString text = new XSSFRichTextString(rowName[i]);  // 得到列的值
            cellRowName.setCellValue(text); // 设置列的值
            cellRowName.setCellStyle(columnStyle); // 样式
        }
        // 产生其它行(将数据列表设置到对应的单元格中)注意:默认添加了第一列的序号,如果不要可以注释掉
        for (int i = 0; i < dataList.size(); i++) {
            LinkedHashMap<String, Object> linkedHashMapData = dataList.get(i);//遍历每个对象
            SXSSFRow row = sheet.createRow(i + 1);//创建所需的行数
            row.setHeightInPoints(17.25f);
            int j = 0;
            Iterator iter = linkedHashMapData.keySet().iterator();
            while (iter.hasNext()) {
                SXSSFCell cell = null;   //设置单元格的数据类型
                cell = row.createCell(j, SXSSFCell.CELL_TYPE_STRING);
                Object key = iter.next();
                Object val = linkedHashMapData.get(key);
                Boolean isNum = false;//val是否为数值型
                Boolean isInteger=false;//val是否为整数
                Boolean isPercent=false;//val是否为百分数
                if (val != null || "".equals(val)) {
                    //判断val是否为数值型
                    isNum = val.toString().matches("^(-?\\d+)(\\.\\d+)?$");
                    //判断val是否为整数(小数部分是否为0)
                    isInteger=val.toString().matches("^[-\\+]?[\\d]*$");
                    //判断val是否为百分数(是否包含“%”)
                    isPercent=val.toString().contains("%");
                }
                //如果单元格内容是数值类型,涉及到金钱(金额、本、利),则设置cell的类型为数值型,设置val的类型为数值类型
                if (isNum && !isPercent) {
                    HSSFDataFormat.getBuiltinFormat("0.00");
                    if (isInteger) {
                        style.setDataFormat(HSSFDataFormat.getBuiltinFormat("#,#0"));//数据格式只显示整数
                    }else{
                        style.setDataFormat(HSSFDataFormat.getBuiltinFormat("#,##0.00"));//保留两位小数点
                    }
                    // 设置单元格内容为double类型
                    cell.setCellValue(Double.parseDouble(val.toString()));
                } else if (null != val && !"".equals(val)){
                    // 设置单元格内容为字符型
                    cell.setCellValue(val.toString());
                }else{
                    cell.setCellValue("");
                }
                // 设置单元格格式
                cell.setCellStyle(style);
                j++;
            }
        }
        //  让列宽随着导出的列长自动适应,但是对中文支持不是很好  也可能在linux(无图形环境的操作系统)下报错,报错再说
        sheet.trackAllColumnsForAutoSizing();
        for (int i = 0; i < columnNum; i++) {
            sheet.autoSizeColumn(i);
            //sheet.setColumnWidth(i, sheet.getColumnWidth(i)*2);//适当再宽点
            int colWidth = sheet.getColumnWidth(i)*2;
            if(colWidth<255*256){
                sheet.setColumnWidth(i, colWidth < 3000 ? 3000 : colWidth);
            }else{
                sheet.setColumnWidth(i,6000 );
            }
        }
        log.info("excel导出生成完成");
        if (workbook != null) {
            // 输出到用户浏览器上
            if (null != filePath) {
                ExportExcelUtil.saveFile(filePath, fileName, workbook);
            } else {
                if (workbook != null) {
                    ExportExcelUtil.responWrite(response, request, workbook,sheetName);
                }
            }
        }
    }
}

3.service

public void exportOutsourcingAuditBills(String billMonth, String companyId,  HttpServletRequest request, HttpServletResponse response) {
    String[] rowName = {"列名1", "列名2", "列名3", "列名4", "列名5", "列名6", "列名7"};
    String SheetName = "表名";
    String billId = mapper.getBillId(billMonth, companyId);
    List<LinkedHashMap<String, Object>> taskStatusList = new ArrayList<>();
    if (null != billId) {
        taskStatusList = beOutbillDescMapper.getOutsourcingAuditBills(billId);// 得到要展示的值
    }
    ExportExcelStandard exportExcel = new ExportExcelStandard(SheetName, rowName, taskStatusList, request, response);// 加载导出文件a
    try {
        exportExcel.exportData();// 调导出的方法
    } catch (Exception e) {
        log.info(e + "");
    }
}

4.controller

@GetMapping("/export/exportFile/exportOutsourcingAuditBills")
public void exportOutsourcingAuditBills(String billMonth,String companyId,HttpServletRequest request, HttpServletResponse response){
    try {
        service.exportOutsourcingAuditBills(billMonth,companyId,request,response);
    } catch (Exception e) {
        log.error("/export/exportFile/exportOutsourcingAuditBills"+e);
    }
}

5.前端调用导出

window.location.href = getApi()+"report/export/exportFile/exportOutsourcingAuditBills?billMonth="+billMonth+"&companyId="+companyId
相关标签: 导出