Java输出Excel文件

 最近需要将数据导出到Excel文件，因此记录一下操作过程

创建流程

1、实例化HSSFWorkbook
2、实例化HSSFSheet
3、创建HSSFRow
4、创建HSSFCell

示例代码

package com.example.utils;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.example.cimdata.CimdataApplication;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.io.FileUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;

@Slf4j
public class ExcelUtil {

    public static void write(List<JSONObject> list) {
        String[] title = {"编号", "名称", "数据对象", "数据大类", "浏览次数", "使用次数",
                "数据来源", "数据标签", "数据类型", "数据格式", "采集时间", "更新频率",
                "数据提供方", "数据维护方", "摘要", "发布日期", "更新日期", "接口地址"};
        // 创建excel工作簿
        HSSFWorkbook workbook = new HSSFWorkbook();
        // 创建工作表sheet
        HSSFSheet sheet = workbook.createSheet();
        // 创建第一行
        HSSFRow rowHeader = sheet.createRow(0);
        sheet.setDefaultColumnWidth(20);
        HSSFCell cell = null;
        // 插入第一行数据的表头
        for (int i = 0; i < title.length; i++) {
            cell = rowHeader.createCell(i);
            cell.setCellValue(title[i]);
            setHeaderStyle(workbook, cell);
        }
        int[] widths = {1500, 4000, 4000, 4000, 2300, 2300,
                4000, 3000, 3000, 3000, 3000, 2500,
                4000, 4000, 4000, 3000, 3000, 8000};
        for (int i = 0; i < widths.length; i++) {
            sheet.setColumnWidth(i, widths[i]);
        }
        // 写入数据
        int k = 1;
        for (JSONObject json : list) {
            HSSFRow row = sheet.createRow(k++);
            createCell(workbook, row, 0, json.getInteger("id"));
            createCell(workbook, row, 1, json.getString("name"));
            createCell(workbook, row, 2, json.getJSONObject("dataObj").getString("name"));
            createCell(workbook, row, 3, json.getString("dataCatalog"));
            createCell(workbook, row, 4, json.getInteger("scanCnt"));
            createCell(workbook, row, 5, json.getInteger("downloadCnt"));
            createCell(workbook, row, 6, json.getString("source"));
            createCell(workbook, row, 7, getLabels(json.getJSONArray("labels")));
            createCell(workbook, row, 8, json.getString("dataType"));
            createCell(workbook, row, 9, json.getString("dataFormat"));
            createCell(workbook, row, 10, json.getString("timeRange"));
            createCell(workbook, row, 11, json.getString("updateRate"));
            createCell(workbook, row, 12, json.getString("dataProvider"));
            createCell(workbook, row, 13, json.getString("dataMaintain"));
            createCell(workbook, row, 14, json.getString("summary"));
            createCell(workbook, row, 15, getDate(json, "publishDate"));
            createCell(workbook, row, 16, getDate(json, "lastUpdateDate"));
            createCell(workbook, row, 17, json.getJSONObject("gisApi").getString("apiUrl"));
        }
        // 创建excel文件
        String path = String.format("%sfiles/", PathUtil.getAppPath(CimdataApplication.class));
        File file = new File(path + "test.xls");
        try {
            // 将excel写入
            FileOutputStream stream = FileUtils.openOutputStream(file);
            workbook.write(stream);
            stream.close();
        } catch (IOException e) {
            log.error(e.toString());
        }
    }

    private static String getDate(JSONObject json, String name) {
        Date dt = json.getDate(name);
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        return sdf.format(dt);
    }

    private static String getLabels(JSONArray array) {
        if (array == null || array.size() == 0) {
            return "";
        }
        StringBuilder sb = new StringBuilder();
        for (Object obj : array) {
            JSONObject json = (JSONObject) obj;
            if (sb.length() > 0) {
                sb.append(",");
            }
            sb.append(json.getString("name"));
        }
        return sb.toString();
    }

    private static void createCell(HSSFWorkbook workbook, HSSFRow row, int index, Object value) {
        HSSFCell cell = row.createCell(index);
        if (value instanceof Integer) {
            cell.setCellValue((Integer) value);
        } else {
            cell.setCellValue((String) value);
        }
        if (index == 0 || index == 4 || index == 5 || index == 7 || index == 8 || index == 9 || index == 10 || index == 11 || index == 15 || index == 16) {
            setCellCenter(workbook, cell);
        } else {
            setCellLeft(workbook, cell);
        }
    }

    private static void setHeaderStyle(HSSFWorkbook workbook, HSSFCell cell) {
        CellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        setBorder(cellStyle);
        Font font = workbook.createFont();
        font.setBold(true);
        cellStyle.setFont(font);
        cell.setCellStyle(cellStyle);
    }

    private static void setCellLeft(HSSFWorkbook workbook, HSSFCell cell) {
        // 创建单元格样式并设置居中对齐
        CellStyle cellStyle = workbook.createCellStyle();
        setBorder(cellStyle);
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        // 将样式应用于单元格
        cell.setCellStyle(cellStyle);
    }

    private static void setCellCenter(HSSFWorkbook workbook, HSSFCell cell) {
        // 创建单元格样式并设置居中对齐
        CellStyle cellStyle = workbook.createCellStyle();
        setBorder(cellStyle);
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        // 将样式应用于单元格
        cell.setCellStyle(cellStyle);
    }

    private static void setBorder(CellStyle cellStyle) {
        // 设置边框
        cellStyle.setBorderTop(BorderStyle.THIN);
        cellStyle.setBorderBottom(BorderStyle.THIN);
        cellStyle.setBorderLeft(BorderStyle.THIN);
        cellStyle.setBorderRight(BorderStyle.THIN);
    }
}

设置列头样式

    private static void setHeaderStyle(HSSFWorkbook workbook, HSSFCell cell) {
        CellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        setBorder(cellStyle);
        Font font = workbook.createFont();
        font.setBold(true);
        cellStyle.setFont(font);
        cell.setCellStyle(cellStyle);
    }

设置单元格样式

    private static void setCellLeft(HSSFWorkbook workbook, HSSFCell cell) {
        // 创建单元格样式并设置居中对齐
        CellStyle cellStyle = workbook.createCellStyle();
        setBorder(cellStyle);
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        // 将样式应用于单元格
        cell.setCellStyle(cellStyle);
    }

    private static void setCellCenter(HSSFWorkbook workbook, HSSFCell cell) {
        // 创建单元格样式并设置居中对齐
        CellStyle cellStyle = workbook.createCellStyle();
        setBorder(cellStyle);
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        // 将样式应用于单元格
        cell.setCellStyle(cellStyle);
    }

    private static void setBorder(CellStyle cellStyle) {
        // 设置边框
        cellStyle.setBorderTop(BorderStyle.THIN);
        cellStyle.setBorderBottom(BorderStyle.THIN);
        cellStyle.setBorderLeft(BorderStyle.THIN);
        cellStyle.setBorderRight(BorderStyle.THIN);
    }

设置字体样式

Font font = workbook.createFont();
font.setBold(true);
cellStyle.setFont(font);