POI 生成 EXCEL

来源：互联网发布：rmit 知乎编辑：程序博客网时间：2024/05/16 23:45
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;    }}
0 0