Java POI操作

1. 单元格显示格式化（数值加百分号）
2.  设置字体
3. 添加批注

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.*;
import org.apache.poi.xssf.usermodel.extensions.XSSFCellBorder;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.OutputStream;
import java.util.Arrays;
import java.util.List;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

public class ExcelUtil {

    protected static Logger logger = LoggerFactory.getLogger(ExcelUtil.class);

    private static final String CHARSET = "UTF-8";

    /**
     * 生成文件
     * @param response      相应体
     * @param request       请求体
     * @param extfilename   Excel文件名
     */
    private static void fileName(HttpServletResponse response, HttpServletRequest request, String extfilename){
        try {
            String agent = request.getHeader("USER-AGENT").toLowerCase();
            if(agent.contains("msie")){//根据浏览器类型处理文件名称
                extfilename = java.net.URLEncoder.encode(getValidFileName(extfilename), CHARSET);
            } else{
                if(agent.contains("gecko")&&agent.contains("rv:11.0")) {
                    extfilename = java.net.URLEncoder.encode(getValidFileName(extfilename), CHARSET);
                }else {
                    extfilename = new String(getValidFileName(extfilename).getBytes(CHARSET), "ISO8859-1");
                }
            }
            response.setContentType("application/msexcel");
            response.addHeader("Content-Disposition", "attachment;filename=" + extfilename + ".xlsx");
        }catch (Exception e) {
            logger.error(e.getMessage());
        }
    }
    /**
     * 检验获取合法的文件名
     * @param fileName  原文件名称
     * @return 合法的经处理的文件名称
     */
    private static String getValidFileName(String fileName){
        Pattern pattern = Pattern.compile("[\\s\\\\/:\\*\\?\\\"<>\\|]");
        Matcher matcher = pattern.matcher(fileName);
        fileName= matcher.replaceAll(""); // 将匹配到的非法字符以空替换
        if(fileName.contains("("))
            fileName = fileName.replace("(","（");
        if(fileName.contains(")"))
            fileName = fileName.replace(")","）");
        return fileName;
    }

    public static void exportExcel(HttpServletResponse response, HttpServletRequest request,
                                   List<String> colNames, List<String> valueNames, String fileName, List<Map<String, Object>> data) {
        try(OutputStream os = response.getOutputStream();
            XSSFWorkbook wb = new XSSFWorkbook()) {
            ExcelUtil.fileName(response,request,fileName);
            //1. 制作Excel表头
            Sheet sheet = wb.createSheet();
            XSSFDataFormat format = wb.createDataFormat();//自定义数据格式
            format.putFormat((short)1,"0.00%");

            Font ztFont = wb.createFont();
            ztFont.setFontName("黑体");
            XSSFCellStyle titleStyle = wb.createCellStyle();
            titleStyle.setAlignment(HorizontalAlignment.CENTER);
            titleStyle.setVerticalAlignment(VerticalAlignment.CENTER);
            titleStyle.setFont(ztFont);
            titleStyle.setBorderRight(BorderStyle.THIN);
            titleStyle.setBorderLeft(BorderStyle.THIN);
            titleStyle.setBorderTop(BorderStyle.THIN);
            titleStyle.setBorderBottom(BorderStyle.THIN);
            titleStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
            titleStyle.setFillForegroundColor(new XSSFColor(new java.awt.Color(255, 192, 0)));

            XSSFCellStyle cellStyle = wb.createCellStyle();
            cellStyle.setLocked(false);
            cellStyle.setAlignment(HorizontalAlignment.LEFT);
            cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
            cellStyle.setBorderRight(BorderStyle.THIN);
            cellStyle.setBorderLeft(BorderStyle.THIN);
            cellStyle.setBorderTop(BorderStyle.THIN);
            cellStyle.setBorderBottom(BorderStyle.THIN);
            cellStyle.setBorderColor(XSSFCellBorder.BorderSide.TOP,new XSSFColor(new java.awt.Color(0,0,0)));

            XSSFCellStyle centStyle = wb.createCellStyle();
            centStyle.setLocked(false);
            centStyle.setAlignment(HorizontalAlignment.LEFT);
            centStyle.setVerticalAlignment(VerticalAlignment.CENTER);
            centStyle.setBorderRight(BorderStyle.THIN);
            centStyle.setBorderLeft(BorderStyle.THIN);
            centStyle.setBorderTop(BorderStyle.THIN);
            centStyle.setBorderBottom(BorderStyle.THIN);
            centStyle.setDataFormat(1);
            centStyle.setBorderColor(XSSFCellBorder.BorderSide.TOP,new XSSFColor(new java.awt.Color(0,0,0)));

            Row row = sheet.createRow((short) 0);
            for (int i = 0; i < colNames.size(); i++) {
                Cell cell=row.createCell(i);
                cell.setCellValue(colNames.get(i));
                cell.setCellStyle(titleStyle);
            }
            Cell cell = row.getCell(colNames.size()-1);
            sheet.createDrawingPatriarch();
            // 创建绘图对象
            XSSFDrawing p = (XSSFDrawing) sheet.createDrawingPatriarch();
            // 前四个参数是坐标点,后四个参数是编辑和显示批注时的大小.
            XSSFComment comment = p.createCellComment(new XSSFClientAnchor(0, 0, 0,0, (short) 4, 3, (short) 6, 6));
            // 输入批注信息
            comment.setString(new XSSFRichTextString("相同行业的模拟权重相加等于100%"));
            comment.setVisible(true);
            // 添加作者,选中B5单元格,看状态栏
//        comment.setAuthor("toad");
            // 将批注添加到单元格对象中
            cell.setCellComment(comment);

            setData(data,sheet,cellStyle,centStyle,colNames,valueNames);
            for (int i = 0; i < colNames.size(); i++) {
                sheet.autoSizeColumn(i,true);
            }
            wb.write(os);
        }catch (Exception e) {
            logger.error("Method=>{},Exception=>{}","StockMicroConsumer",e.getMessage());
        }
    }

    /**
     * 设置单元格的数据值
     * @param localData execl表格的数据
     * @param sheet     sheet的实体
     * @param cellStyle 单元格样式
     * @param centStyle
     * @param colNames  单元格的头名称
     * @param valueNames    key
     */
    private static void setData(List<Map<String, Object>> localData, Sheet sheet, XSSFCellStyle cellStyle, XSSFCellStyle centStyle, List<String> colNames, List<String> valueNames){
        for (int i = 0; i < localData.size(); i++) {
            Row row = sheet.createRow((short) (i+1));
            Map<String,Object> map = localData.get(i);
            for (int j = 0; j < colNames.size(); j++) {
                Cell cell=row.createCell(j);
                if ("weight".equals(valueNames.get(j))){
                    double value = new Double(map.get(valueNames.get(j)).toString());
                    cell.setCellValue(value);
                    cell.setCellStyle(centStyle);
                }else {
                    cell.setCellValue(map.get(valueNames.get(j)).toString());
                    cell.setCellStyle(cellStyle);
                }
            }
        }
    }
}