使用POI导出Excel

1、pom.xml引入poi

<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>3.7</version>
</dependency>
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>3.7</version>
</dependency>

 

2、ExportExcelUtil.java

package com.aw.spin.common.util;

import com.aw.spin.common.entity.ExcelContentInfo;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.SheetUtil;
import org.springframework.util.StringUtils;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.List;
import java.util.Map;
import java.util.Set;

public class ExportExcelUtil {

    /**
     * 导出excel
     *
     * @param outputPath 输出路径
     * @param fileName   文件名
     * @param outData    导出的数据
     * @return
     * @throws IOException
     */
    public static String export(String outputPath, String fileName, Map<String, ExcelContentInfo> outData) throws IOException {
        HSSFWorkbook workbook = new HSSFWorkbook();
        Set<String> keys = outData.keySet();
        ExcelContentInfo excelContentInfo;
        // 根据map创建sheet
        for (String key : keys) {
            HSSFSheet sheet = workbook.createSheet(key);
            excelContentInfo = outData.get(key);
            setSheetContent(excelContentInfo.getTitle(), excelContentInfo.getData(), excelContentInfo.getRowName(), workbook, sheet);
        }
        isDirExists(outputPath);
        FileOutputStream out = new FileOutputStream(outputPath + fileName);
        workbook.write(out);
        out.close();
        return outputPath + fileName;
    }

    private static void isDirExists(String path) {
        File file = new File(path);
        if (!file.exists()) {
            file.mkdirs();
        }
    }

    /**
     * 设置sheet的内容
     * @param title 第一行的title，一合并了单元格
     * @param outDataList 数据
     * @param rowName 每列的名称
     * @param workbook
     * @param sheet
     */
    private static void setSheetContent(String title, List<Object[]> outDataList, String[] rowName, HSSFWorkbook workbook, HSSFSheet sheet) {
        int rownum = 0;
        if (!StringUtils.isEmpty(title)) {
            // 设置top，第一行
            HSSFRow topRow = sheet.createRow(rownum);
            rownum++;
            HSSFCell topCell = topRow.createCell(0);
            topRow.setHeight((short) (25 * 30));
            HSSFCellStyle topCellStyle = getColumnStyle(workbook, 18, "仿宋", IndexedColors.PALE_BLUE);
            //topCell.setCellValue(title.replaceAll("[^\u4e00-\u9fa5]", ""));
            topCell.setCellValue(title.trim());
            topCell.setCellStyle(topCellStyle);
            sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, (rowName.length - 1)));
        }

        // 设置列的标题， 第二行
        HSSFRow titleRow = sheet.createRow(rownum);
        rownum++;
        titleRow.setHeight((short) (25 * 20));
        HSSFCellStyle titleCellStyle = getColumnStyle(workbook, 16, "仿宋", IndexedColors.PALE_BLUE);
        int columnNum = rowName.length;
        for (int i = 0; i < columnNum; i++) {
            HSSFCell titleCell = titleRow.createCell(i);
            titleCell.setCellType(HSSFCell.CELL_TYPE_STRING);
            HSSFRichTextString text = new HSSFRichTextString(rowName[i]);
            titleCell.setCellValue(text);
            titleCell.setCellStyle(titleCellStyle);
        }
        // 数据
        HSSFCellStyle contentStyle = getColumnStyle(workbook, 14, "仿宋", null);
        for (int i = 0; i < outDataList.size(); i++) {
            Object[] objects = outDataList.get(i);
            HSSFRow contentRow = sheet.createRow(i + rownum);
            contentRow.setHeight((short) (25 * 20));
            for (int j = 0; j < objects.length; j++) {
                HSSFCell contentCell = contentRow.createCell(j);
                if (objects[j] != null && !"".equals(objects[j])) {
                    contentCell.setCellValue(objects[j].toString());
                }
                contentCell.setCellStyle(contentStyle);
            }
        }
        // 设置cloumn的width
        for (int colNum = 0; colNum < columnNum; colNum++) {
            /*int columnWidth = sheet.getColumnWidth(colNum);
            // 取所有行中cloumn的最大长度
            for (int rowNum = 0; rowNum < sheet.getLastRowNum(); rowNum++) {
                HSSFRow currentRow;
                if (sheet.getRow(rowNum) == null) {
                    currentRow = sheet.createRow(rowNum);
                } else {
                    currentRow = sheet.getRow(rowNum);
                }
                if (currentRow.getCell(colNum) != null) {
                    HSSFCell currentCell = currentRow.getCell(colNum);
                    if (currentCell.getCellType() == HSSFCell.CELL_TYPE_STRING) {
                        int length = currentCell.getStringCellValue().getBytes().length * 256;
                        if (columnWidth < length) {
                            columnWidth = length;
                        }
                    }
                }
            }*/
            //sheet.setColumnWidth(colNum, columnWidth + 12 * 256);
            double width = SheetUtil.getColumnWidth(sheet, colNum, false);
            width += 2;
            if (width != -1) {
                width *= (256 + 2);
                int maxColumnWidth = 255 * 256; // The maximum column width for an individual cell is 255 characters
                if (width > maxColumnWidth) {
                    width = maxColumnWidth;
                }
                sheet.setColumnWidth(colNum, (int) (width));
            }
            //sheet.autoSizeColumn(colNum, false);
        }
    }


    /**
     * 设置每个格子的样式
     * @param workbook
     * @param fontSize
     * @param fontName
     * @param backgroundColor
     * @return
     */
    public static HSSFCellStyle getColumnStyle(HSSFWorkbook workbook, int fontSize, String fontName, IndexedColors backgroundColor) {
        HSSFFont font = workbook.createFont();
        // 设置字体
        font.setFontHeightInPoints((short) fontSize);
        font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        font.setFontName(fontName);
        // 设置表格样式
        HSSFCellStyle style = workbook.createCellStyle();
        style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        style.setBottomBorderColor(HSSFColor.BLACK.index);
        style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        style.setLeftBorderColor(HSSFColor.BLACK.index);
        style.setBorderRight(HSSFCellStyle.BORDER_THIN);
        style.setRightBorderColor(HSSFColor.BLACK.index);
        style.setBorderTop(HSSFCellStyle.BORDER_THIN);
        style.setTopBorderColor(HSSFColor.BLACK.index);
        style.setFont(font);
        style.setWrapText(false);
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        if (backgroundColor != null) {
            style.setFillForegroundColor(backgroundColor.getIndex());
        }
        style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        return style;
    }

}

3、ExportContentInfo.java

package com.aw.spin.common.entity;

import java.util.List;

public class ExcelContentInfo {

    private String title;
    private String[] rowName;
    private List<Object[]> data;


    public String getTitle() {
        return title;
    }

    public void setTitle(String title) {
        this.title = title;
    }

    public String[] getRowName() {
        return rowName;
    }

    public void setRowName(String[] rowName) {
        this.rowName = rowName;
    }

    public List<Object[]> getData() {
        return data;
    }

    public void setData(List<Object[]> data) {
        this.data = data;
    }

    public ExcelContentInfo() {
    }

    public ExcelContentInfo(String title, String[] rowName, List<Object[]> data) {
        this.title = title;
        this.rowName = rowName;
        this.data = data;
    }
}