POI 生成 EXCEL

最新推荐文章于 2024-10-22 10:02:28 发布
原创最新推荐文章于 2024-10-22 10:02:28 发布 · 490 阅读
0 ·
CC 4.0 BY-SA版权
文章标签：
#POI
POI 专栏收录该内容
1 篇文章
订阅专栏
import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

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.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;

public class ExcelExportHelper {

    public static void main(String[] args) {
        Map<String, List<String>> map = new HashMap<String, List<String>>();
        export(Constant.RESULT_EXCEL_FILE, map);
    }

    public static void export(String fileName, Map<String, List<String>> map) {
        HSSFWorkbook wb = new HSSFWorkbook();
        // 生成一个sheet1
        HSSFSheet sheet = wb.createSheet("sheet1");

        // 生成样式对象
        Map<String, CellStyle> styles = createStyles(wb);

        // 生成表头
        createHeader(sheet, styles);

        // 生成内容
        createContext(sheet, styles, map);

        ByteArrayOutputStream os = new ByteArrayOutputStream();
        try {
            wb.write(os);
        }
        catch (IOException e) {
            e.printStackTrace();
        }

        File file = new File(fileName);
        byte[] content = os.toByteArray();
        OutputStream fos = null;
        try {
            fos = new FileOutputStream(file);
            fos.write(content);
            os.close();
            fos.close();
        }
        catch (Exception e) {
            e.printStackTrace();
        }
    }

    private static void createHeader(HSSFSheet sheet,
            Map<String, CellStyle> styles) {

        // 为 sheet 生成第一行，用于放表头信息
        HSSFRow row = sheet.createRow(0);

        HSSFCell cell = row.createCell(0, 0);
        cell.setCellValue("序号");
        cell.setCellStyle(styles.get("header"));

        cell = row.createCell(1, 0);
        cell.setCellValue("interface");
        cell.setCellStyle(styles.get("header"));

        cell = row.createCell(2, 0);
        cell.setCellValue("action");
        cell.setCellStyle(styles.get("header"));
    }

    private static void createContext(HSSFSheet sheet,
            Map<String, CellStyle> styles, Map<String, List<String>> map) {
        int actionInUseCount = 0;
        for (Map.Entry<String, List<String>> entry : map.entrySet()) {
            if (entry.getValue().size() > 0) {
                // 合并单元格
                CellRangeAddress region = new CellRangeAddress(
                        actionInUseCount + 1,
                        actionInUseCount + entry.getValue().size(), 1, 1);
                sheet.addMergedRegion(region);
                for (String actionName : entry.getValue()) {
                    actionInUseCount++;
                    // 数据每增加一行，表格就再生成一行
                    HSSFRow row = sheet.createRow(actionInUseCount);
                    // 第一个单元格放序号
                    HSSFCell cell = row.createCell(0, 0);
                    cell.setCellValue(actionInUseCount);
                    cell.setCellStyle(styles.get("cell"));
                    // 第二个单元格放 InterfaceName
                    cell = row.createCell(1, 0);
                    cell.setCellValue(entry.getKey());
                    cell.setCellStyle(styles.get("cell"));
                    // 第三个单元格放 actionName
                    cell = row.createCell(2, 0);
                    cell.setCellValue(actionName);
                    cell.setCellStyle(styles.get("cell"));
                }
            }
        }
        // 列宽度自适应
        sheet.autoSizeColumn((short) 0);
        sheet.autoSizeColumn((short) 1);
        sheet.autoSizeColumn((short) 2);
    }

    // excel样式
    private static Map<String, CellStyle> createStyles(Workbook wb) {
        Map<String, CellStyle> styles = new HashMap<String, CellStyle>();
        CellStyle style;
        // 标题
        Font font = wb.createFont();
        // -- 字号大小
        font.setFontHeightInPoints((short) 18);
        // -- 字体宽度
        font.setBoldweight(Font.BOLDWEIGHT_BOLD);

        style = wb.createCellStyle();
        // -- 左右对齐
        style.setAlignment(CellStyle.ALIGN_CENTER);
        // -- 上下对齐
        style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        // -- 背景颜色
        style.setFillForegroundColor(IndexedColors.YELLOW.getIndex());
        // -- 填充方式
        style.setFillPattern(CellStyle.SOLID_FOREGROUND);
        // -- 设置边框
        style.setBorderTop(CellStyle.BORDER_THIN);
        style.setBorderRight(CellStyle.BORDER_THIN);
        style.setBorderBottom(CellStyle.BORDER_THIN);
        style.setBorderLeft(CellStyle.BORDER_THIN);
        // -- 是否换行
        style.setWrapText(true);
        style.setFont(font);
        styles.put("title", style);

        // 表头
        font = wb.createFont();
        font.setFontHeightInPoints((short) 12);
        font.setBoldweight(Font.BOLDWEIGHT_BOLD);

        style = wb.createCellStyle();
        style.setAlignment(CellStyle.ALIGN_CENTER);
        style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        style.setFillForegroundColor(IndexedColors.YELLOW.getIndex());
        style.setFillPattern(CellStyle.SOLID_FOREGROUND);
        style.setBorderTop(CellStyle.BORDER_THIN);
        style.setBorderRight(CellStyle.BORDER_THIN);
        style.setBorderBottom(CellStyle.BORDER_THIN);
        style.setBorderLeft(CellStyle.BORDER_THIN);
        style.setFont(font);
        styles.put("header", style);

        // 常规 cell
        font = wb.createFont();
        font.setFontName("宋体");
        font.setFontHeightInPoints((short) 10);

        style = wb.createCellStyle();
        style.setAlignment(CellStyle.ALIGN_LEFT);
        style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        style.setBorderTop(CellStyle.BORDER_THIN);
        style.setBorderRight(CellStyle.BORDER_THIN);
        style.setBorderBottom(CellStyle.BORDER_THIN);
        style.setBorderLeft(CellStyle.BORDER_THIN);
        style.setFont(font);
        styles.put("cell", style);

        // 数字格式化
        style = wb.createCellStyle();
        style.setAlignment(CellStyle.ALIGN_RIGHT);
        style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        style.setDataFormat(wb.createDataFormat().getFormat("0.00"));
        style.setBorderTop(CellStyle.BORDER_THIN);
        style.setBorderRight(CellStyle.BORDER_THIN);
        style.setBorderBottom(CellStyle.BORDER_THIN);
        style.setBorderLeft(CellStyle.BORDER_THIN);
        styles.put("double", style);

        // 时间格式化
        style = wb.createCellStyle();
        style.setAlignment(CellStyle.ALIGN_RIGHT);
        style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        style.setDataFormat(
                wb.createDataFormat().getFormat("yyyy-mm-dd hh:mm:ss"));
        style.setBorderTop(CellStyle.BORDER_THIN);
        style.setBorderRight(CellStyle.BORDER_THIN);
        style.setBorderBottom(CellStyle.BORDER_THIN);
        style.setBorderLeft(CellStyle.BORDER_THIN);
        style.setFont(font);
        styles.put("dateTime", style);

        return styles;
    }
}