web项目导出excel表格

短命女 2022-05-25 05:00 215阅读 0赞

*  原博地址:https://blog.csdn.net/yuan487639/article/details/76212170
 *  该功能涉及到两个知识点，一是制作excel相关插件的使用，二是导出文件，spring-mvc该如何配置
 *  先说excel插件的使用，这次使用的是org.apache.poi包，版本是3.9，下面粘完整能运行的代码
 *  maven依赖

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

*  插件的实体类，也可以理解为参数，这里采用了build设计模式，好处是初始化了一些默认值，使用的时候代码写的方便。针对表格导出，我觉得三个参数足够了，标题、表头以及数据，所以，这三个参数构造方法我设计的必传，其它使用默认参数

package com.util;
    
    import java.util.List;
    
    public class ExcelParam {
        String name;
        int width;
        String font;
        String[] headers;
        /**
         * 导出数据的样式
         * 1:String left;
         * 2:String center
         * 3:String right
         * 4 int  right
         * 5:float ###,###.## right
         * 6:number: #.00% 百分比 right
         */
        int[] ds_format;
        /**
         * 每列表格的宽度,默认为256 * 14
         */
        int[] widths;
        List<String[]> data;
    
        private ExcelParam() {
    
        }
        //使用Builder模式，设置默认参数和必填参数
        public static class Builder {
            String name;
            int width = 256 * 14;
            String font = "微软雅黑";
            String[] headers;
            int[] ds_format;
            int[] widths;
            List<String[]> data;
    
            public Builder(String name) {
                this.name = name;
            }
    
            public Builder font(String font) {
                this.font = font;
                return this;
            }
    
            public Builder width(int width) {
                this.width = width;
                return this;
            }
    
            public Builder headers(String[] headers) {
                this.headers = headers;
                return this;
            }
    
            public Builder ds_format(int[] ds_format) {
                this.ds_format = ds_format;
                return this;
            }
    
            public Builder widths(int[] widths) {
                this.widths = widths;
                return this;
            }
    
            public Builder data(List<String[]> data) {
                this.data = data;
                return this;
            }
    
            public ExcelParam build() {
                ExcelParam excelParam = new ExcelParam();
                excelParam.name = this.name;
                excelParam.data = this.data;
                excelParam.widths = this.widths;
                excelParam.ds_format = this.ds_format;
                excelParam.headers = this.headers;
                excelParam.font = this.font;
                excelParam.width = this.width;
                return excelParam;
            }
        }
    }

*  插件本身

package com.ucredit.util;
    
    import org.apache.poi.hssf.usermodel.*;
    
    import javax.servlet.http.HttpServletResponse;
    import java.io.IOException;
    import java.io.OutputStream;
    import java.net.URLEncoder;
    
    public class ExcelUtil {
        private ExcelUtil() {
        }
    
        public static void export(ExcelParam excelParam, HttpServletResponse response) throws IOException {
            if (excelParam.widths == null) {
                excelParam.widths = new int[excelParam.headers.length];
                for (int i = 0; i < excelParam.headers.length; i++) {
                    excelParam.widths[i] = excelParam.width;
                }
            }
            if (excelParam.ds_format == null) {
                excelParam.ds_format = new int[excelParam.headers.length];
                for (int i = 0; i < excelParam.headers.length; i++) {
                    excelParam.ds_format[i] = 1;
                }
            }
            //创建一个工作薄
            HSSFWorkbook wb = new HSSFWorkbook();
            //创建一个sheet
            HSSFSheet sheet = wb.createSheet("excel");
            int rowCount = 0;
            if (excelParam.headers != null) {
                HSSFRow row = sheet.createRow(rowCount);
                //表头样式
                HSSFCellStyle style = wb.createCellStyle();
                HSSFFont font = wb.createFont();
                font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
                font.setFontHeightInPoints((short) 11);
                style.setFont(font);
                style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
    
                for (int i = 0; i < excelParam.headers.length; i++) {
                    sheet.setColumnWidth(i, excelParam.widths[i]);
                    HSSFCell cell = row.createCell(i);
                    cell.setCellValue(excelParam.headers[i]);
                    cell.setCellStyle(style);
                }
                rowCount++;
            }
            HSSFCellStyle style = wb.createCellStyle();
            style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
            //表格主体  解析list
            for (int i = 0; i < excelParam.data.size(); i++) {  //行数
                HSSFRow row = sheet.createRow(rowCount);
                for (int j = 0; j < excelParam.headers.length; j++) {  //列数
                    HSSFCell cell = row.createCell(j);
                    cell.setCellValue(excelParam.data.get(i)[j]);
                    cell.setCellStyle(style);
                }
                rowCount++;
            }
            //设置文件名
            String fileName = excelParam.name + ".xls";
            response.setContentType("application/vnd.ms-excel");
            response.setHeader("Content-disposition", "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8"));
            response.setHeader("Pragma", "No-cache");
            OutputStream outputStream = response.getOutputStream();
            wb.write(outputStream);
            outputStream.flush();
            outputStream.close();
        }
    }

*  其实从这个方法就能看出，文件最后导出，实际上是往response中的outputStream中写入文件，之后的工作是由浏览器来完成的
 *  controller层写法

@RequestMapping(value = "download")
        public void download(@RequestParam String username,
                             @RequestParam String operation_module,
                             @RequestParam String operation_type,
                             @RequestParam String start_time,
                             @RequestParam String end_time,
                             HttpServletResponse response) throws Exception {
            List<OperationLogEntity> list = operationLogDao.get(username, operation_module, operation_type, start_time, end_time);
            String[] heads = {"序号", "账号", "姓名", "操作类型", "操作模块", "处理员工号码", "处理结果", "登录IP", "时间", "所在城市"};
            List<String[]> data = new LinkedList<>();
            for (int i = 0; i < list.size(); i++) {
                OperationLogEntity entity = list.get(i);
                String[] temp = new String[10];
                temp[0] = String.valueOf(i + 1);
                temp[1] = entity.getUsername();
                temp[2] = entity.getName();
                temp[3] = entity.getOperation_type();
                temp[4] = entity.getOperation_module();
                temp[5] = entity.getProcess_number();
                temp[6] = entity.getProcess_result();
                temp[7] = entity.getIp();
                temp[8] = DateUtils.getDateBeforeOrAfterStrCN(entity.getOperation_time(), 0);
                temp[9] = entity.getCity();
                data.add(temp);
            }
            ExcelParam param = new ExcelParam.Builder("操作日志").headers(heads).data(data).build();
            ExcelUtil.export(param, response);
        }

*  实际使用

//这样写很简洁
    ExcelParam param = new ExcelParam.Builder("操作日志").headers(heads).data(data).build();