poi(3.17)生成excel - 2007

maven依赖

<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>fastjson</artifactId>
    <version>1.2.72</version>
</dependency>
<!-- poi相关 start-->
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>3.17</version>
</dependency>
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>3.17</version>
</dependency>
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml-schemas</artifactId>
    <version>3.17</version>
</dependency>
<!-- poi相关 end-->

代码实现

public class ExcelUtil {

    /**
     * 生成文件
     *
     * @param saveFilePath 输出文件路径
     * @param jsonArray    数据集合
     */
    public static void createFile(String saveFilePath, JSONArray jsonArray) throws IOException {
        OutputStream outputStream = null;
        Workbook workbook = null;
        try {
            workbook = new XSSFWorkbook();
            Sheet sheet = workbook.createSheet("Sheet1");
            CellStyle style = workbook.createCellStyle();
            CellStyle style1 = workbook.createCellStyle();

            Font font = workbook.createFont();
            //下边框(实线)
            style.setBorderBottom(BorderStyle.THIN);
            //左边框(实线)
            style.setBorderLeft(BorderStyle.THIN);
            //上边框(实线)
            style.setBorderTop(BorderStyle.THIN);
            //右边框(实线)
            style.setBorderRight(BorderStyle.THIN);
            //设置背景色
            style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
            style.setFillForegroundColor(IndexedColors.DARK_RED.getIndex());

            //设置字体大小  是否加粗  字体颜色
            font.setFontHeight((short) 240);
            font.setBold(true);
            font.setColor(IndexedColors.WHITE.getIndex());
            style.setFont(font);

            //设置内容垂直居中  水平居左
            style1.setVerticalAlignment(VerticalAlignment.CENTER);
            style1.setAlignment(HorizontalAlignment.LEFT);
            style.setVerticalAlignment(VerticalAlignment.CENTER);
            style.setAlignment(HorizontalAlignment.LEFT);

            for (int i = 0; i < jsonArray.size(); i++) {
                // 行号
                Row row = sheet.createRow(i);
                JSONObject jsonObject = jsonArray.getJSONObject(i);
                //列号
                for (int j = 0; j < jsonObject.size(); j++) {
                    Cell cell = row.createCell(j);
                    cell.setCellStyle(style1);
                    if (i == 0) {
                        sheet.setColumnWidth(j, 15 * 256);
                        cell.setCellStyle(style);
                        row.setHeightInPoints(40);
                    }
                    if (jsonObject.get(String.valueOf(j)) != null) {
                        cell.setCellValue(String.valueOf(jsonObject.get(String.valueOf(j))));
                    } else {
                        cell.setCellValue("");
                    }
                }
            }
            FileUtils.forceMkdirParent(new File(saveFilePath));
            outputStream = new FileOutputStream(saveFilePath);
            workbook.write(outputStream);
        } catch (Exception e) {
            e.printStackTrace();
            System.out.println("生成xlsx文件错误：" + e);
        } finally {
            if (outputStream != null) {
                outputStream.close();
            }
            if (workbook != null) {
                workbook.close();
            }
        }
    }
}

工具调用

String fileName = "输出文件绝对路径";
JSONArray jsonArray = new JSONArray();
···添加数据 
JSONObject jsonObject = new JSONObject(true);
jsonArray.add(jsonObject);
 
ExcelUtil.createFile(fileName,jsonArray);