Java使用POI结合VUE实现导出数据到Excel表格

• Java结合VUE实现导出数据到Excel表格,同时解决了单元格合并，导致单元格样式不生效问题
• 页面预览：
  1.前台展示页面
  
  2.导出的Excel
  
  3.Java后台中传入的参数
  
  
• 代码实现
  1.VUE代码：

<template>
  <el-table
    :data="tableData"
    border
    style="width: 100%">
    <el-table-column
      fixed
      prop="date"
      label="日期"
      width="150">
    </el-table-column>
    <el-table-column
      prop="name"
      label="姓名"
      width="120">
    </el-table-column>
    <el-table-column
      prop="province"
      label="省份"
      width="120">
    </el-table-column>
    <el-table-column
      prop="city"
      label="市区"
      width="120">
    </el-table-column>
    <el-table-column
      prop="address"
      label="地址"
      width="300">
    </el-table-column>
    <el-table-column
      prop="zip"
      label="邮编"
      width="120">
    </el-table-column>
    <el-table-column
      fixed="right"
      label="操作">
      <template slot-scope="scope">
        <el-button @click="handleClick(scope.row)" type="text" size="small">导出</el-button>
      </template>
    </el-table-column>
  </el-table>
</template>

<script>
  export default {
    methods: {
      handleClick(row) {
        var form = document.createElement("form");
        form.style.display = "none";
        form.action = 'http://localhost:8580'+'/export/exportExcel';
        form.method = "post";
        document.body.appendChild(form);

        for (var key in row) {
          var input = document.createElement("input");
          input.type = "hidden";
          input.name = key;
          input.value = row[key];
          form.appendChild(input);
        }

        form.submit();
        form.remove();

      }
    },

    data() {
      return {
        tableData: [{
          date: '2016-05-02',
          name: '王小虎',
          province: '上海',
          city: '普陀区',
          address: '上海市普陀区金沙江路 1518 弄',
          zip: 200333
        }, {
          date: '2016-05-04',
          name: '王小虎',
          province: '上海',
          city: '普陀区',
          address: '上海市普陀区金沙江路 1517 弄',
          zip: 200333
        }, {
          date: '2016-05-01',
          name: '王小虎',
          province: '上海',
          city: '普陀区',
          address: '上海市普陀区金沙江路 1519 弄',
          zip: 200333
        }, {
          date: '2016-05-03',
          name: '王小虎',
          province: '上海',
          city: '普陀区',
          address: '上海市普陀区金沙江路 1516 弄',
          zip: 200333
        }]
      }
    }
  }
</script>

2.Java代码

import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.RegionUtil;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.text.SimpleDateFormat;
import java.util.*;

@Controller
@RequestMapping("/export")
public class ExportAction {

    @RequestMapping(value = "/exportExcel", produces = "application/json;charset=utf-8")
    public void exportTaskResult(HttpServletRequest request, HttpServletResponse response) {
        try {
            Map properties = request.getParameterMap();

            HashMap<String, Object> mapData = new HashMap<>();

            Iterator entries = properties.entrySet().iterator();
            Map.Entry entry;
            String name = "";
            String value = "";
            while (entries.hasNext()) {
                entry = (Map.Entry) entries.next();
                name = (String) entry.getKey();
                Object valueObj = entry.getValue();
                if(null == valueObj){
                    value = "";
                }else if(valueObj instanceof String[]){
                    String[] values = (String[])valueObj;
                    for(int i=0;i<values.length;i++){
                        value = values[i] + ",";
                    }
                    value = value.substring(0, value.length()-1);
                }else{
                    value = valueObj.toString();
                }
                mapData.put(name, value);
            }

            exportExcel(request, response, mapData);
        }catch(Exception ex) {
            ex.printStackTrace();
        }
    }


    public void exportExcel(HttpServletRequest request, HttpServletResponse response, HashMap<String, Object> mapData) throws IOException {

        response.setContentType("application/vnd.ms-excel");

        SimpleDateFormat formatter= new SimpleDateFormat("yyyyMMddHHmmss");
        Date date = new Date(System.currentTimeMillis());
        String time = formatter.format(date);
        String prifix = "测试";

        // 文件名
        String fileName = prifix + time + ".xls";
        // 处理文件名包含特殊字符出现的乱码问题
        String userAgent = request.getHeader("User-Agent");
        if (StringUtils.isNotBlank(userAgent)) {
            userAgent = userAgent.toLowerCase();
            if (userAgent.contains("msie") || userAgent.contains("trident") || userAgent.contains("edge")) {
                if (fileName.length() > 150) {// 解决IE 6.0问题
                    fileName = new String(fileName.getBytes("UTF-8"), "ISO-8859-1");
                } else {
                    fileName = URLEncoder.encode(fileName, "UTF-8");
                }
            } else {
                fileName = new String(fileName.getBytes("UTF-8"), "ISO-8859-1");
            }
        }
        response.setHeader("Content-disposition", "attachment;filename=\"" + fileName + "\"");
        OutputStream stream = response.getOutputStream();
        // 创建excel文件对象
        HSSFWorkbook wb = new HSSFWorkbook();
        // 创建sheet
        Sheet sheet = wb.createSheet("sheet1");

        //正文字体
        Font contextFont = wb.createFont();
        contextFont.setFontName("微软雅黑");
        contextFont.setFontHeightInPoints((short) 12);
        contextFont.setBold(false);
        contextFont.setColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex());


        //黑色字体
        Font blackFont = wb.createFont();
        blackFont.setFontName("微软雅黑");
        blackFont.setFontHeightInPoints((short) 12);
        blackFont.setBold(true);
        blackFont.setColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex());

        //单元格样式，左对齐
        CellStyle commonStyleLeft = wb.createCellStyle();
        commonStyleLeft.setFont(contextFont);
        commonStyleLeft.setAlignment(HorizontalAlignment.LEFT);// 左对齐
        commonStyleLeft.setVerticalAlignment(VerticalAlignment.CENTER);// 上下居中
        commonStyleLeft.setLocked(true);
        commonStyleLeft.setWrapText(false);// 自动换行
        commonStyleLeft.setBorderBottom(BorderStyle.THIN);//下边框
        commonStyleLeft.setBorderLeft(BorderStyle.THIN);//左边框
        commonStyleLeft.setBorderTop(BorderStyle.THIN);//上边框
        commonStyleLeft.setBorderRight(BorderStyle.THIN);//右边框

        //单元格样式，左右上下居中 边框
        CellStyle commonStyle = wb.createCellStyle();
        commonStyle.setFont(contextFont);
        commonStyle.setAlignment(HorizontalAlignment.CENTER);// 左右居中
        commonStyle.setVerticalAlignment(VerticalAlignment.CENTER);// 上下居中
        commonStyle.setLocked(true);
        commonStyle.setWrapText(false);// 自动换行
        commonStyle.setBorderBottom(BorderStyle.THIN);//下边框
        commonStyle.setBorderLeft(BorderStyle.THIN);//左边框
        commonStyle.setBorderTop(BorderStyle.THIN);//上边框
        commonStyle.setBorderRight(BorderStyle.THIN);//右边框

        CellStyle white = getCellStyle(wb, blackFont);;
        white.setFillForegroundColor(HSSFColor.HSSFColorPredefined.WHITE.getIndex());//设置单元格颜色

       int fixedHeight = 700;
       int fixedColumnWidth = 6000;

        String[] row_first = {"姓名", "", "性别", "", "年龄", "", "电话号码", "", "邮箱", ""};

        for (int i = 0; i < row_first.length; i++) {
            sheet.setColumnWidth(i, fixedColumnWidth);
        }

        //第一行
        Row r0 = sheet.createRow(0);
        r0.setHeight((short) fixedHeight);

        for (int i = 0; i < row_first.length; i++) {
            Cell tempCell = r0.createCell(i);
            tempCell.setCellValue(row_first[i]);
            tempCell.setCellStyle(white);
        }

        String[] row_2 = {"毕业院校", ""};

        for (int i = 0; i < row_2.length; i++) {
            sheet.setColumnWidth(i, fixedColumnWidth);
        }

        //第二行
        Row r1 = sheet.createRow(1);
        r1.setHeight((short) fixedHeight);

        for (int i = 0; i < row_2.length; i++) {
            Cell tempCell = r1.createCell(i);
            tempCell.setCellValue(row_2[i]);
            if (i == 1) {
                tempCell.setCellStyle(commonStyleLeft);
            } else {
                tempCell.setCellStyle(white);
            }
        }

        CellRangeAddress addresses = new CellRangeAddress(1, 1, 1, 9);
        sheet.addMergedRegion(addresses);

        setBorderStyle(BorderStyle.THIN, addresses, sheet);

        String[] row_3 = {"工作经历", ""};

        for (int i = 0; i < row_3.length; i++) {
            sheet.setColumnWidth(i, fixedColumnWidth);
        }

        //第三行
        Row r2 = sheet.createRow(2);
        r2.setHeight((short) fixedHeight);

        for (int i = 0; i < row_3.length; i++) {
            Cell tempCell = r2.createCell(i);
            tempCell.setCellValue(row_3[i]);
            tempCell.setCellStyle(white);
        }

        CellRangeAddress addresses1 = new CellRangeAddress(2, 2, 0, 9);
        sheet.addMergedRegion(addresses1);

        setBorderStyle(BorderStyle.THIN, addresses1, sheet);

        String[] row_4 = {"工作经历："};

        for (int i = 0; i < row_4.length; i++) {
            sheet.setColumnWidth(i, fixedColumnWidth);
        }
        //第四行
        Row r3 = sheet.createRow(3);
        r3.setHeight((short) fixedHeight);

        for (int i = 0; i < row_4.length; i++) {
            Cell tempCell = r3.createCell(i);
            tempCell.setCellValue(row_4[i]);
            tempCell.setCellStyle(white);
        }

        CellRangeAddress addresses2 = new CellRangeAddress(3, 3, 1, 9);
        sheet.addMergedRegion(addresses2);

        setBorderStyle(BorderStyle.THIN, addresses2, sheet);

        ServletOutputStream out =  response.getOutputStream();
        try {
            if (null != wb && null != stream) {
                wb.write(out);
                out.close();
            }
        } catch (Exception e) {
        }
    }

    private CellStyle getCellStyle (HSSFWorkbook wb, Font font) {
        CellStyle cellStyle = wb.createCellStyle();
        cellStyle.setFont(font);//字体样式
        cellStyle.setAlignment(HorizontalAlignment.CENTER);// 左右居中
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);// 上下居中
        cellStyle.setLocked(true);
        cellStyle.setWrapText(true);// 自动换行
        cellStyle.setBorderBottom(BorderStyle.THIN); //下边框
        cellStyle.setBorderLeft(BorderStyle.THIN);//左边框
        cellStyle.setBorderTop(BorderStyle.THIN);//上边框
        cellStyle.setBorderRight(BorderStyle.THIN);//右边框
        cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);  //填充单元格

        return cellStyle;
    }

    public void setBorderStyle(BorderStyle borderStyle, CellRangeAddress cellAddresses, Sheet sheet) {
        RegionUtil.setBorderBottom(borderStyle, cellAddresses, sheet);
        RegionUtil.setBorderLeft(borderStyle, cellAddresses, sheet);
        RegionUtil.setBorderTop(borderStyle, cellAddresses, sheet);
        RegionUtil.setBorderRight(borderStyle, cellAddresses, sheet);
    }

}

3.pom.xml

	 <dependency>
		  <groupId>org.apache.poi</groupId>
		  <artifactId>poi</artifactId>
		  <version>4.0.0</version>
	  </dependency>
	  <dependency>
		  <groupId>org.apache.poi</groupId>
		  <artifactId>poi-ooxml</artifactId>
		  <version>4.0.0</version>
	  </dependency>