使用java导出Excel表格

使用Jakarta POI实现导出Excel表格
导包就不讲了，下面直接看代码：

package com.hxlh.common.service;

import org.apache.commons.collections4.map.HashedMap;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.util.CellRangeAddress;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

/**
 * Created by jnn on 2018/03/21.
 */
@Controller
public class ExportExcel {

    @RequestMapping("/test")
    public String CreatExcel(HttpServletResponse response) throws IOException {

        //模拟考勤数据，放到list中
        List<Map<String, String>> mapList = new ArrayList<Map<String, String>>();
        Map map1 = new HashedMap();
        map1.put("name","张飞");
        map1.put("date","2018-03-21");
        map1.put("goTime","08:56");
        map1.put("outTime","17:56");
        map1.put("department","IT部");

        Map map2 = new HashedMap();
        map2.put("name","关羽");
        map2.put("date","2018-03-21");
        map2.put("goTime","08:00");
        map2.put("outTime","17:30");
        map2.put("department","IT部");

        Map map3 = new HashedMap();
        map3.put("name","刘备");
        map3.put("date","2018-03-21");
        map3.put("goTime","08:30");
        map3.put("outTime","19:00");
        map3.put("department","IT部");

        mapList.add(map1);
        mapList.add(map2);
        mapList.add(map3);

        String fileName = "考勤文件";//Excel文件名字
        String sheetName = "sheet名称";//Excel中sheet名称
        String titleName = "2018年3月考勤";//表格的标题名称
        int[] columnWidth = {30,30,30,30,30};//每列的宽度
        String[] columnName = {"姓名","打卡日期","上班时间","下班时间","部门"};//列标题
        String[] columnKey = {"name","date","goTime","outTime","department"};//对于Map中的key值

        ExportExcel(mapList,fileName,sheetName,titleName,columnWidth,columnName,columnKey,response);

        return null;
    }


    //导出Excel方法
    public static void ExportExcel(List<Map<String, String>> dataList, 
                                    String fileName, String sheetName, 
                                    String titleName, int[] columnWidth, 
                                    HttpServletResponse response) throws IOException {

        //创建HSSFWorkbook对象（Excel文件）
        HSSFWorkbook wb = new HSSFWorkbook();
        //创建sheet表
        HSSFSheet sheet = wb.createSheet(sheetName);
        //设置每列的宽度
        for (int i = 0; i < columnWidth.length; i++) {
            sheet.setColumnWidth(i,columnWidth[i] * 256);
        }

        //创建三种单元格样式，分别用于表格标题、列标题、数据区域
        //第一种样式，用于表格标题
        HSSFCellStyle style1 = wb.createCellStyle();//创建单元格样式
        style1.setAlignment(HSSFCellStyle.ALIGN_CENTER);//设置对齐方式,横向居中
        style1.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//设置对齐方式，纵向居中
        //style1.setBottomBorderColor(HSSFColor.RED.index);//
        style1.setBorderBottom(HSSFCellStyle.BORDER_THIN);//下边框
        style1.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
        style1.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
        style1.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
        HSSFFont font1 = wb.createFont();//创建字体格式
        font1.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);//粗体
        font1.setFontName("黑体");//字体
        font1.setFontHeightInPoints((short) 15);//字号
        style1.setFont(font1);//字体格式应用到单元格样式

        //第二种样式，用于表格标题
        HSSFCellStyle style2 = wb.createCellStyle();
        style2.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        style2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        style2.setBottomBorderColor(HSSFColor.BLACK.index);
        style2.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        style2.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        style2.setBorderRight(HSSFCellStyle.BORDER_THIN);
        style2.setBorderTop(HSSFCellStyle.BORDER_THIN);
        HSSFFont font2 = wb.createFont();
        font2.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        font2.setFontName("黑体");
        font2.setFontHeightInPoints((short) 13);
        style2.setFont(font2);

        //第三种样式，用于表格标题
        HSSFCellStyle style3 = wb.createCellStyle();
        style3.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        style3.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        style3.setBottomBorderColor(HSSFColor.BLACK.index);
        style3.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        style3.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        style3.setBorderRight(HSSFCellStyle.BORDER_THIN);
        style3.setBorderTop(HSSFCellStyle.BORDER_THIN);
        HSSFFont font3 = wb.createFont();
        font3.setFontName("楷体");
        font3.setFontHeightInPoints((short) 12);
        style3.setFont(font3);

        //创建第一行，表格标题，如果不循环，则边框显示不全
        HSSFRow rowTitle = sheet.createRow(0);
        for (int i = 0; i < columnName.length; i++ ) {
            HSSFCell cellTitle = rowTitle.createCell(i);
            cellTitle.setCellValue(titleName);
            cellTitle.setCellStyle(style1);
        }
        //合并单元格：起始行，截至行，起始列， 截至列
        sheet.addMergedRegion(new CellRangeAddress(0,0,0,columnName.length -1));

        //创建表头
        HSSFRow rowHeader = sheet.createRow(1);
        for (int i = 0; i < columnName.length; i++) {
            HSSFCell cellHeader = rowHeader.createCell(i);
            cellHeader.setCellValue(columnName[i]);
            cellHeader.setCellStyle(style2);
        }

        //创建数据区域
        for (int i = 2; i < dataList.size() + 2; i++) {
            HSSFRow row = sheet.createRow(i);
            for (int j = 0; j < columnKey.length; j++ ) {
                HSSFCell cell = row.createCell(j);
                cell.setCellValue(dataList.get(i-2).get(columnKey[j]));//用Map中Key取数据
                cell.setCellStyle(style3);
            }
        }

        //向浏览器输出Excel文件
        String fileName2 = fileName + ".xls";
        response.setContentType("application/ms-excel;charset=UTF-8");
        response.setHeader("Content-disposition", "attachment; filename=" + new String(fileName2.getBytes(),"ISO-8859-1"));
        OutputStream out = response.getOutputStream();
        wb.write(out);
        out.close();
    }
}

在浏览器中下载文件

导出后Excel样式