Java使用POI导出excel —— 实战

注意：本文只做参考，具体需要根据需求设置客户想要的excel样式

• 涉及知识点：
• 合并单元格
• 设置字体样式
• 设置单元格宽度

导出样式：

前台

function exportExcel(){
    var searchForm = document.getElementById("searchForm");
    searchForm.action="/user/exportExcel";
    searchForm.submit();
}

后台

@RequestMapping("exportExcel")
    public void exportExcel(UserVO vo, HttpServletResponse response, HttpServletRequest request){
        List<User> userList = userService.queryUserListNoPage(vo);
        // 1.创建一个工作薄
        XSSFWorkbook workbook = new XSSFWorkbook();
        // 2.创建sheet页
        XSSFSheet sheet = workbook.createSheet();

        // 给列设置默认宽度
        sheet.setDefaultColumnWidth(30);
        // 合并单元格
        CellRangeAddress range = new CellRangeAddress(0,0,0,4);
        sheet.addMergedRegion(range);
        CellRangeAddress range2 = new CellRangeAddress(1,1,0,4);
        sheet.addMergedRegion(range2);

        // 3.创建行
        int row = 0;
        XSSFRow row1 = sheet.createRow(row);

        // 4.创建单元格
        XSSFCell cell = row1.createCell(0);
        // 5.给单元格赋值
        cell.setCellValue("用户数据");
        // 设置第一行样式
        XSSFCellStyle titleStyle = createTitleStyle(workbook);
        cell.setCellStyle(titleStyle);

        // 创建第二行
        XSSFRow row2 = sheet.createRow(++row);
        XSSFCell cell2 = row2.createCell(0);
        cell2.setCellValue("总数："+userList.size()+"条，导出时间："+new Date().toLocaleString());
        // 设置第二行样式
        XSSFCellStyle subTitleStyle = createSubTitleStyle(workbook);
        cell2.setCellStyle(subTitleStyle);

        // 创建第三行
        XSSFRow row3 = sheet.createRow(++row);
        String[] titles = {"用户编号","用户名称","用户性别","所属部门","入职时间"};
        for (int i = 0; i <titles.length ; i++) {
            XSSFCell row3_cell = row3.createCell(i);
            row3_cell.setCellValue(titles[i]);
            XSSFCellStyle tableTitle = createTableTitle(workbook);
            row3_cell.setCellStyle(tableTitle);
        }

        XSSFCellStyle baseStyle = createBaseStyle(workbook);
        // 创建数据行
        for (int i = 0; i < userList.size(); i++) {
            User user = userList.get(i);
            XSSFRow dataRow = sheet.createRow(++row);

            XSSFCell dataRowCell1 = dataRow.createCell(0);
            dataRowCell1.setCellValue(i+1);
            dataRowCell1.setCellStyle(baseStyle);

            XSSFCell dataRowCell2 = dataRow.createCell(1);
            dataRowCell2.setCellValue(user.getName());
            dataRowCell2.setCellStyle(baseStyle);

            XSSFCell dataRowCell3 = dataRow.createCell(2);
            dataRowCell3.setCellValue(user.getSex()==null?"":user.getSex()==0?"男":"女");
            dataRowCell3.setCellStyle(baseStyle);

            XSSFCell dataRowCell4 = dataRow.createCell(3);
            dataRowCell4.setCellValue(user.getDeptname());
            dataRowCell4.setCellStyle(baseStyle);

            XSSFCell dataRowCell5 = dataRow.createCell(4);
            dataRowCell5.setCellValue(user.getHiredate().toLocaleString());
            dataRowCell5.setCellStyle(baseStyle);
        }


        // 导出Excel
        ExcelUtil.excelDownload(workbook,request,response,"用户列表.xlsx");
    }

    /**
     * 设置技术样式
     * 水平居中和垂直居中
     * @param workbook
     * @return
     */
    public XSSFCellStyle createBaseStyle(XSSFWorkbook workbook){
        XSSFCellStyle style = workbook.createCellStyle();
        // 水平居中
        style.setAlignment(HorizontalAlignment.CENTER);
        // 垂直居中
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        return style;
    }

    /**
     * 设置标题样式
     */
    public XSSFCellStyle createTitleStyle(XSSFWorkbook workbook){
        XSSFCellStyle style = createBaseStyle(workbook);
        // 给字体设置样式
        XSSFFont font = workbook.createFont();
        // 加粗
        font.setBold(true);
        // 大小
        font.setFontHeightInPoints((short) 30);
        // 字体颜色
        font.setColor(HSSFColor.BLUE.index);
        // 字体样式
        font.setFontName("华文彩云");
        style.setFont(font);
        return style;
    }

    /**
     * 设置小标题样式
     */
    public XSSFCellStyle createSubTitleStyle(XSSFWorkbook workbook){
        XSSFCellStyle style = createBaseStyle(workbook);
        // 给字体设置样式
        XSSFFont font = workbook.createFont();
        // 加粗
        font.setBold(true);
        // 大小
        font.setFontHeightInPoints((short) 20);
        // 字体颜色
        font.setColor(HSSFColor.RED.index);
        // 字体样式
        font.setFontName("微软雅黑");
        style.setFont(font);
        return style;
    }

    /**
     * 设置表格标题样式
     */
    public XSSFCellStyle createTableTitle(XSSFWorkbook workbook){
        XSSFCellStyle style = createBaseStyle(workbook);
        // 给字体设置样式
        XSSFFont font = workbook.createFont();
        // 加粗
        font.setBold(true);
        // 大小
        font.setFontHeightInPoints((short) 15);
        // 字体颜色
//        font.setColor(HSSFColor.RED.index);
        // 字体样式
        font.setFontName("宋体");
        style.setFont(font);
        return style;
    }

工具类

import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.OutputStream;
import java.io.UnsupportedEncodingException;
import java.net.URLEncoder;

public class ExcelUtil {

    public static void excelDownload(XSSFWorkbook wirthExcelWB, HttpServletRequest request,HttpServletResponse response, String fileName) {
        OutputStream out = null;
        try {
        	
        	//解决下载文件名中文乱码问题
        	if(request.getHeader("User-agent").toLowerCase().indexOf("firefox")!=-1){
        		fileName = new String(fileName.getBytes("utf-8"),"iso-8859-1");
        	}else{
        		fileName = URLEncoder.encode(fileName,"utf-8");
        	}
        	
            out = response.getOutputStream();
            // 让浏览器识别是什么类型的文件
            response.reset(); // 重点突出
            response.setCharacterEncoding("UTF-8"); // 重点突出
            response.setContentType("application/x-msdownload");// 不同类型的文件对应不同的MIME类型
                                                                // // 重点突出
            // inline在浏览器中直接显示，不提示用户下载
            // attachment弹出对话框，提示用户进行下载保存本地
            // 默认为inline方式
            response.setHeader("Content-Disposition", "attachment;filename=" + fileName);
            wirthExcelWB.write(out);
        } catch (UnsupportedEncodingException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            if (null != out) {
                try {
                    out.close();
                    out = null;
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
    }
}