Java POI 导出Excel表

1.获取poi.jar包

官网：https://poi.apache.org/download.html

解压后放到项目的lib文件夹里

2.util创建

public class DownloadExcel {
    public static void setHeadCellNames(HSSFRow firstRow,HSSFCellStyle cellStyle, List<String> headCellNames){

        // 添加表头内容
        for (int i = 0; i < headCellNames.size(); i++) {
            HSSFCell headCell = firstRow.createCell(i);
            headCell.setCellValue(headCellNames.get(i));
            headCell.setCellStyle(cellStyle);
        }
    }

    public static void setCellValue(HSSFRow hssfRow,HSSFCellStyle cellStyle,List<String> cellValues){

        // 添加数据内容
        for (int i = 0; i < cellValues.size(); i++) {
            // 创建单元格，并设置值
            HSSFCell cell = hssfRow.createCell(i);
            cell.setCellValue(cellValues.get(i));
            cell.setCellStyle(cellStyle);
        }
    }

    public static <T> void createExcel(String fileName, List<String> headCellNames
            , List<T> dataList,HttpServletResponse response) throws InvocationTargetException {
        // 创建一个Excel文件
        HSSFWorkbook workbook = new HSSFWorkbook();
        // 创建一个工作表
        HSSFSheet sheet = workbook.createSheet();
        sheet.setDefaultRowHeightInPoints(1F);
        sheet.setDefaultColumnWidth(20);

        // 设置单元格格式
        HSSFCellStyle cellStyle = workbook.createCellStyle();
//        cellStyle.setAlignment(HorizontalAlignment.LEFT);
        // 单元格内容样式
        cellStyle.setLocked(true);
        cellStyle.setWrapText(true);
        // 字体
        HSSFFont font = workbook.createFont();
        font.setFontHeightInPoints((short) 13);
        cellStyle.setFont(font);
        // 添加表头行
        HSSFRow firstRow = sheet.createRow(0);
        // 添加表头内容
        setHeadCellNames(firstRow,cellStyle,headCellNames);

        for (int i=0;i< dataList.size();i++){
            T data = dataList.get(i);
            Method[] methods = data.getClass().getMethods();
            HSSFRow hssfRow = sheet.createRow( i + 1);
            List<String> cellValues = Lists.newArrayList();
            for (Method temp : methods)
                try {
                    if (temp.getName().contains("get")){
                        Object object = temp.invoke(data);
                        if (object==null){
                            cellValues.add("");
                        }else {
                            cellValues.add(object.toString());
                        }
                    }
                } catch (IllegalAccessException e) {
                    e.printStackTrace();
                }

            DownloadExcel.setCellValue(hssfRow,cellStyle,cellValues);
        }

        try {
            // 自定义输出文件名
            String typeName = new String(fileName.getBytes("UTF-8"), "UTF-8");
            response.setContentType("application/vnd.ms-excel");
            response.setHeader("Content-disposition", "attachment;filename="
                    + typeName);
            OutputStream outputStream = response.getOutputStream();
            workbook.write(outputStream);
            outputStream.flush();
            outputStream.close();
        } catch (IOException e) {
            e.printStackTrace();
        }

    }


}

3.Controller创建接口

@ResponseBody
    @RequestMapping("/recordExcel.do")
    public void recordExcel(HttpServletResponse response,HttpServletRequest request) {
        HSSFWorkbook workbook = new HSSFWorkbook();//声明一个Excel工作簿
        String sheetName = "库存清单";
        HSSFSheet sheet = workbook.createSheet(sheetName);//生成一个表格
        sheet.setDefaultRowHeightInPoints(100);//设置表格默认高度
        sheet.setDefaultColumnWidth(20);//设置表格默认宽度
        sheet.setColumnWidth(7, 50 * 256);

        // 字体
        HSSFFont font = workbook.createFont();
        font.setFontHeightInPoints((short) 13);

        // 单元格表头样式
        HSSFCellStyle headerStyle = workbook.createCellStyle();
        headerStyle.setFillForegroundColor(HSSFColor.SKY_BLUE.index);
        headerStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        headerStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        headerStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        headerStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
        headerStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
        //水平居中
        headerStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        //垂直居中
        headerStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);

        // 单元格内容样式
        HSSFCellStyle normalStyle = workbook.createCellStyle();
        normalStyle.setLocked(true);
        normalStyle.setWrapText(true);
        normalStyle.setFont(font);
        normalStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        normalStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        normalStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
        normalStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
        //水平居中
        normalStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        //垂直居中
        normalStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);


        HSSFRow firstRow = sheet.createRow(0);
        firstRow.setHeight((short) 600);//表头行高

        ArrayList<String> headCellNames = Lists.newArrayList("仓库","物品编号","物品名称","单位", "库存","金额", "库存状态");

        DownloadExcel.setHeadCellNames(firstRow, headerStyle, headCellNames);

        ArrayList<String> cellValues;

        int i = 0;
        for (Map<String, Object> map : GoodsStockList) {
            JSONObject jsonOne = new JSONObject();
            jsonOne.put("GoodsPackageTypeName", map.get("GoodsPackageTypeName") == null ? "" : map.get("GoodsPackageTypeName"));
            jsonOne.put("GoodPackageCode", map.get("GoodPackageCode") == null ? "" : map.get("GoodPackageCode"));
            jsonOne.put("GoodsPackageName", map.get("GoodsPackageName") == null ? "" : map.get("GoodsPackageName"));
            jsonOne.put("Unit", map.get("Unit") == null ? "" : map.get("Unit"));
            jsonOne.put("StockCount", map.get("StockCount") == null ? "" : map.get("StockCount"));
            jsonOne.put("TotalCash", map.get("TotalCash") == null ? "" : map.get("TotalCash"));
            jsonOne.put("StockState", map.get("StockState") == null ? "" : map.get("StockState"));

            cellValues = Lists.newArrayList(
                    jsonOne.get("GoodsPackageTypeName").toString(),
                    jsonOne.get("GoodPackageCode").toString(),
                    jsonOne.get("GoodsPackageName").toString(),
                    jsonOne.get("Unit").toString(),
                    jsonOne.get("StockCount").toString(),
                    jsonOne.get("TotalCash").toString(),
                    jsonOne.get("StockState").toString()
            );

            HSSFRow hssfRow = sheet.createRow(i + 1);
            hssfRow.setHeight((short) 600);//表内容行高
            i++;
            DownloadExcel.setCellValue(hssfRow, normalStyle, cellValues);
        }

        try {
            //设置分区中文名
            String filename = "库存清单";
            //设置响应的编码
            response.setContentType("application/x-download");//下面三行是关键代码，处理乱码问题
            response.setCharacterEncoding("utf-8");
            //设置浏览器响应头对应的Content-disposition
            response.setHeader("Content-disposition", "attachment;filename="+new String(filename.getBytes("gbk"), "iso8859-1")+".xls");

            OutputStream outputStream = response.getOutputStream();
            workbook.write(outputStream);
            outputStream.flush();
            outputStream.close();


        } catch (IOException e) {
            e.printStackTrace();
        }

    }

4.页面触发接口

<div class="stock-btn" onclick="recordExcel()">导出Excel</div>


    function recordExcel(){
        window.location.href='/goodsstock/recordExcel.do'
    }