导出excel通用模板（程序定义模板导出）

转载原文：https://www.jianshu.com/p/5c7b359a159c

如下代码，本方法主要用于程序定义模板格式，并导出文件。该方法将定义和创建分离，达到了一定解耦合，降低了开发复杂度。但是依然是程序定义模板，无法根据需求个性化更改模板内容，即无法通过读取excel模板，根据模板定义的字段和顺序创建表格。

改良版，关于添加依赖之类的之前一篇文章里面有。
这篇是把之前的方法抽成通用模板。

一、添加一个实体类

package com.lencity.securitymanagementplatform.data.entity;

import java.util.List;

public class XlsData {

    public static final int DATA_TYPE_INTEGER = 0;
    public static final int DATA_TYPE_STRING = 1;
    private List<String> titles;//表头
    private List<Integer> types;//数据类型
    private List<List<Object>> values;存表数据
    
    public List<Integer> getTypes() {
        return types;
    }
    public void setTypes(List<Integer> types) {
        this.types = types;
    }
    public List<String> getTitles() {
        return titles;
    }
    public void setTitles(List<String> titles) {
        this.titles = titles;
    }
    public List<List<Object>> getValues() {
        return values;
    }
    public void setValues(List<List<Object>> values) {
        this.values = values;
    }   
}

二、创建一个service类

package com.lencity.securitymanagementplatform.service;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.OutputStream;
import java.io.UnsupportedEncodingException;
import java.net.URLEncoder;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.springframework.stereotype.Service;

import com.lencity.securitymanagementplatform.data.entity.XlsData;

@Service
public class XlsService {

        //写一个接口，哪个控制器需要加上导出excel功能就继承这个接口
    public static interface IXlsOutputProcessor {
        public XlsData processXlsData(Map<String, Object> condition);
    }

        //解析数据创建excel
    public HSSFWorkbook createExcelData(IXlsOutputProcessor processor, Map<String, Object> condition) {
        XlsData xlsData = processor.processXlsData(condition);
        HSSFWorkbook workbook = new HSSFWorkbook();
        HSSFSheet sheet = workbook.createSheet("统计表");// 创建一个excel表单
        HSSFRow titleRow = sheet.createRow(0);
        // 设置列宽，setColumnWidth的第二个参数要乘以256，这个参数的单位是1/256个字符宽度
        sheet.setColumnWidth(1, 15 * 256);
        sheet.setColumnWidth(3, 20 * 256);

        HSSFCellStyle style = workbook.createCellStyle();
        style.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy h:mm"));// 设置日期格式
        HSSFFont font = workbook.createFont();// 设置为居中加粗
        font.setBold(true);
        style.setAlignment(HorizontalAlignment.CENTER);
        style.setFont(font);
        List<String> titles = xlsData.getTitles();
        HSSFCell cell;
        /* 构造表头 */
        for (int i = 0; i < titles.size(); i++) {
            cell = titleRow.createCell(i);
            cell.setCellValue(titles.get(i));
            cell.setCellStyle(style);
        }
        int rowNum = 1;
        List<Integer> dataTypes = xlsData.getTypes();
        List<List<Object>> values = xlsData.getValues();
        for (int i = 0; i < values.size(); i++) {
            List<Object> value = values.get(i);
            HSSFRow row = sheet.createRow(rowNum);
            for (int j = 0; j < value.size(); j++) {
                switch (dataTypes.get(j)) {
                case XlsData.DATA_TYPE_INTEGER:
                    row.createCell(j).setCellValue((Integer) value.get(j));
                    break;
                case XlsData.DATA_TYPE_STRING:
                    row.createCell(j).setCellValue((String) value.get(j));
                    break;
                }
            }
            rowNum++;

        }
        return workbook;
    }


    // 浏览器导出excel
    public void buildExcelDocument(String filename, HSSFWorkbook workbook, HttpServletResponse response)
            throws Exception {
        response.reset();
        response.setContentType("application/vnd.ms-excel");
        response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(filename, "utf-8"));
        OutputStream outputStream = response.getOutputStream();
        workbook.write(outputStream);
        outputStream.flush();
        outputStream.close();
    }

    // 下载excel模板功能
    public void downloadTemplate(HttpServletResponse response,HttpServletRequest request) throws Exception {
        String fileName="导出模板.xls";
        response.reset();
        response.setContentType("application/vnd.ms-excel");
            response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "utf-8"));
            String filePath=request.getServletContext().getRealPath("/excel/")+fileName;
            FileInputStream input=new FileInputStream(filePath);
            OutputStream out=response.getOutputStream();
            byte[] b=new byte[2048];
            int len;
            while((len=input.read(b))!=-1) {
                out.write(b,0,len);
            }
             response.setHeader("Content-Length", String.valueOf(input.getChannel().size()));
                input.close();
    }
}

三、控制器
假设我们要在用户页面加上导出表格的功能，那就在用户的控制器上继承接口

public class UserController implements IXlsOutputProcessor {

继承之后需要在控制器中重写接口方法，

 

导出的表格样式.png

关于封装数据，主要就是根据自己实际的项目需求，来构造数据了
// 封装数据
    @Override
    public XlsData processXlsData(Map<String, Object> condition) {
        List<String> titles = new ArrayList<>();//表头
        List<Integer> dataTypes = new ArrayList<>();//表数据类型
        List<List<Object>> values = new ArrayList<>();//表头对应的数据
        titles.add("姓名");
        dataTypes.add(XlsData.DATA_TYPE_STRING);
        titles.add("手机号码");
        dataTypes.add(XlsData.DATA_TYPE_STRING);
        titles.add("职位");
        dataTypes.add(XlsData.DATA_TYPE_STRING);
        titles.add("部门");
        dataTypes.add(XlsData.DATA_TYPE_STRING);
        List<User> users = userService.getUsersByCondition(condition);
        XlsData xlsData = new XlsData();
        xlsData.setTitles(titles);
        xlsData.setTypes(dataTypes);
        for (User user : users) {
            List<Object> tmpList = new ArrayList<>();
            tmpList.add(user.getName());
            tmpList.add(user.getMobile());
            tmpList.add(user.getPosition());
            tmpList.add(departmentService.getDepartmentNameByDepartmentCode(user.getDepartmentCode()));
            values.add(tmpList);
        }
        xlsData.setValues(values);
        return xlsData;
    }

    // 导出excel，前台js，点击   导出excel   关联的路径就是这个
    @PostMapping(value = "/downLoadXls")
    @ResponseBody
    public String downLoadXls(Map<String, Object> condition, HttpServletResponse response) throws Exception {
        String fileName = "导出excel.xls";
        HSSFWorkbook workbook = xlsService.createExcelData(this, condition);
        xlsService.buildExcelDocument(fileName, workbook, response);
        JSONObject jsonObject = new JSONObject();
        jsonObject.put("code", 1);
        return jsonObject.toString();
    }
    // 下载模板，前台js，点击  下载模板   关联的路径就是这个
    @PostMapping(value = "/downloadTemplate")
    @ResponseBody
    public String downloadTemplate(HttpServletResponse response,HttpServletRequest request) throws Exception {
        String fileName = "导出excel.xls";
        xlsService.downloadTemplate(response, request);
        JSONObject jsonObject = new JSONObject();
        jsonObject.put("code", 1);
        return jsonObject.toString();
    }

 

前台界面.png

 

模板的存放位置

前台按钮代码

<button type="button" class="btn btn-primary waves-effect" onclick="downloadTemplate()" id="downloadTemplate">
<i class="material-icons">vertical_align_bottom</i> 
<span>下载模板</span>
</button>

<button type="button" class="btn btn-primary waves-effect"onclick="exportExcel()">
<i class="material-icons">vertical_align_bottom</i> 
<span>导出表格</span>
</button>

js

form表单里面是页面的表单筛选条件，如果要导数据库所有的数据，可把form表单去掉。如果导出的数据是有筛选条件的，需要改一下form表单
function exportExcel() {
   var name = $("#name").val();
   var departmentCode = $("#departmentCode").find("option:selected").val();
    var form = $("<form>");   
    $(‘body‘).append(form);  
        form.attr(‘style‘,‘display:none‘);   
        form.attr(‘target‘,‘‘);
        form.attr(‘method‘,‘post‘);
        form.attr(‘action‘,contextPath+‘/user/downLoadXls‘);//下载文件的请求路径

        //对应查询条件的开始时间
        var input1 = $(‘<input>‘); 
        input1.attr(‘type‘,‘hidden‘); 
        input1.attr(‘name‘,"name"); 
        input1.attr(‘value‘,name);
        form.append(input1);  

        //对应查询条件的结束时间
        var input2 = $(‘<input>‘); 
        input2.attr(‘type‘,‘hidden‘); 
        input2.attr(‘name‘,‘departmentCode‘); 
        input2.attr(‘value‘,departmentCode);
        form.append(input2);
        form.submit();    
}

下载模板的js

function downloadTemplate() {
    var form = $("<form>");
    $(‘body‘).append(form);
    form.attr(‘style‘, ‘display:none‘);
    form.attr(‘target‘, ‘‘);
    form.attr(‘method‘, ‘post‘);
    form.attr(‘action‘, contextPath + ‘/user/downloadTemplate‘);// 下载文件的请求路径
    form.submit();
}

关于excel导出的总结

原文：https://www.cnblogs.com/yuluoxingkong/p/11572244.html