最近的工作围绕报表导出，并没有集成相应的报表插件，只是使用了Poi。其中有一个需求，Excel中导出特殊符号，如√、×等。在网上找寻了许久，没有相关资料，故记录分享一下。

思考良久，走了不少弯路，最后受 System.out.println() 启发，实现方式真的超级简单。每一个特殊符号，都对应一个Unicode编码，我们只需要将特定的符号，转变成Unicode编码，进行输出即可。

相应的代码输出：

cell.setCellValue("\u221A");

另附自己编写的Excel工具类，支持单表、主子表(可定制主表在前还是在后)、图片、特殊符号等。

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

package com.king.tools.util;
import java.util.HashMap;
import java.util.Map;



public class ExcelPercentField {
    public final static Map<String,String> percentFiledMap = new HashMap<>();
    static {
   		// 根据实际情况进行设置
        percentFiledMap.put("a","a");
        percentFiledMap.put("b","b");
        percentFiledMap.put("c","c");
    }
}

package com.king.tools.util;

import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.RegionUtil;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import javax.servlet.http.HttpServletResponse;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.lang.reflect.Method;
import java.util.*;



public class ExcelExport<T> {
    Logger logger = LoggerFactory.getLogger(ExcelExport.class);
    private HSSFWorkbook workbook;
    private HSSFSheet sheet;
    private int rowNum;
    private HSSFPatriarch patriarch ;
    private String fileName;
    private int version;

    public ExcelExport(){}
    public ExcelExport(String fileName, int version) {
        this.fileName = fileName;
        this.version = version;
    }

    
    public void exportExcel(String title, Map<String,List<String>> fields, Collection<T> dataset, String path,boolean childBefore){
        createExcelHSSF(title,fields,null,dataset,DateUtils.YYYY_MM_DD,path,childBefore);
    }

    
    public void exportExcel(String title,Map<String,List<String>> fields,String[] header,Collection<T> dataset,String path,boolean childBefore){
        createExcelHSSF(title,fields,header,dataset,DateUtils.YYYY_MM_DD,path,childBefore);
    }

    
    public void exportExcel(String title,Map<String,List<String>> fields,String[] header,Collection<T> dataset,String pattern,String path,boolean childBefore){
        createExcelHSSF(title,fields,header,dataset,pattern,path,childBefore);
    }

    
    public void exportExcel(String title,Map<String,List<String>> fields, Collection<T> dataset, HttpServletResponse response){
        createExcelHSSF(title,fields,null,dataset,DateUtils.YYYY_MM_DD,response);
    }

    
    public void exportExcel(String title, Map<String,List<String>> fields, String[] header, Collection<T> dataset, HttpServletResponse response){
        createExcelHSSF(title,fields,header,dataset,DateUtils.YYYY_MM_DD,response);
    }

    
    public void exportExcel(String title, Map<String,List<String>> fields, String[] header, Collection<T> dataset, String pattern, HttpServletResponse response){
        createExcelHSSF(title,fields,header,dataset,pattern,response);
    }
    
    private void createExcelHSSF(String title, Map<String,List<String>> fields, String[] header, Collection<T> dataset, String pattern, HttpServletResponse response){
        response.reset(); // 清除buffer缓存
        // 指定下载的文件名
        response.setHeader("Content-Disposition", "attachment;filename=contacts" +(StringUtils.isBlank(fileName)?  DateUtils.dateTimeNow() : fileName) + ".xls");
        response.setContentType("application/vnd.ms-excel;charset=UTF-8");
        response.setHeader("Pragma", "no-cache");
        response.setHeader("Cache-Control", "no-cache");
        response.setDateHeader("Expires", 0);
        createExcel2003(title,fields,header,dataset,pattern, false);
        httpExcelHSSF(workbook,response);
    }

    
    private void createExcelHSSF(String title,Map<String,List<String>> fields,String[] header,Collection<T> dataset,String pattern,String path,boolean childBefore){
        createExcel2003(title,fields,header,dataset,pattern,childBefore);
        ioExcelHSSF(workbook,path);
    }

    
    private void createExcel2003(String title, Map<String, List<String>> fields, String[] header, Collection<T> dataset, String pattern, boolean childBefore){
        // 初始化构建
        initWorkBook();
        // 生成样式
        HSSFCellStyle titleStyle = getTitleStyle(workbook);
        HSSFCellStyle headerStyle = getHeaderStyle(workbook);
        HSSFCellStyle normalStyle = getNormalStyle(workbook);
        HSSFCellStyle footerStyle = getFooterStyle(workbook);
        HSSFCellStyle percentStyle = createPercentStyle(workbook);
        // 创建表头
        createTableTitle(title,header.length-1,titleStyle);
        // 生成标题行
        createTableHead(header,headerStyle);
        // 迭代集合
        Iterator it = dataset.iterator();
        // 获取主表属性字段
        List<String> entityFields = fields.get("entity");
        // 获取子表属性字段
        List<String> childFields = fields.get("children");
        // 主表字段长度
        int entityColumnLength = entityFields.size();
        int childColumnLength = 0;
        if(childFields !=null){
            childColumnLength = childFields.size();
        }
        // 合并行
        int rowspan = 0;
        // 每个对象的子表数据
        Object children = null;
        HSSFRow row;
        HSSFCell cell;
        while (it.hasNext()){
            rowNum ++;
            T t = (T) it.next();
            row = sheet.createRow(rowNum);
            // 确定合并行数
            if(childFields !=null && childFields.size() > 0){
                children = getValue(t,"children");
                if(children !=null && ((ArrayList)children).size()>0){
                    rowspan = ((ArrayList)children).size()-1;
                }
            }
            // 主表字段
            for(int i = 0; i <entityFields.size(); i++){
                Object value = getValue(t,entityFields.get(i));
                // 创建单元格
                if(childBefore){
                    if(ExcelPercentField.percentFiledMap.containsKey(entityFields.get(i))){
                        createTableCell(row.createCell(i+childColumnLength),value,percentStyle,pattern,rowspan);
                    }else{
                        createTableCell(row.createCell(i+childColumnLength),value,normalStyle,pattern,rowspan);
                    }
                }else{
                    if(ExcelPercentField.percentFiledMap.containsKey(entityFields.get(i))){
                        createTableCell(row.createCell(i),value,percentStyle,pattern,rowspan);
                    }else{
                        createTableCell(row.createCell(i),value,normalStyle,pattern,rowspan);
                    }
                }
            }
            // 子表字段
            if(childFields !=null && childFields.size() > 0){
                if(children !=null ){
                    List list = (ArrayList)children;
                    for(int i = 0;i <list.size(); i++){
                        if(i >0){
                            rowNum++;
                            row = sheet.createRow(rowNum);
                        }
                        for(int j = 0;j<childFields.size();j++){
                            Object value = getValue(list.get(i),childFields.get(j));
                            if(childBefore){
                                if(ExcelPercentField.percentFiledMap.containsKey(childFields.get(j))){
                                    createTableCell(row.createCell(j ),value,percentStyle,pattern,rowspan);
                                }else{
                                    createTableCell(row.createCell(j ),value,normalStyle,pattern,rowspan);
                                }
                            }else{
                                if(ExcelPercentField.percentFiledMap.containsKey(childFields.get(j))){
                                    createTableCell(row.createCell(j +entityColumnLength),value,percentStyle,pattern,rowspan);
                                }else{
                                    createTableCell(row.createCell(j +entityColumnLength),value,normalStyle,pattern,rowspan);
                                }

                            }
                        }
                    }
                }
            }
            // 如果需要合并行
            if(rowspan > 0){
               for(int i = 0;i<entityFields.size();i++){
                   CellRangeAddress cellRange = null;
                   if(childBefore){
                       cellRange= new CellRangeAddress(rowNum-rowspan,rowNum,i+childColumnLength,i+childColumnLength);
                   }else{
                       cellRange = new CellRangeAddress(rowNum-rowspan,rowNum,i,i);
                   }
                   sheet.addMergedRegion(cellRange);
                   //添加边框
                   RegionUtil.setBorderTop(BorderStyle.THIN, cellRange, sheet);
                   RegionUtil.setBorderBottom(BorderStyle.THIN, cellRange, sheet);
                   RegionUtil.setBorderLeft(BorderStyle.THIN, cellRange, sheet);
                   RegionUtil.setBorderRight(BorderStyle.THIN, cellRange, sheet);
                   RegionUtil.setTopBorderColor(HSSFColor.HSSFColorPredefined.GREEN.getIndex(),cellRange,sheet);
                   RegionUtil.setBottomBorderColor(HSSFColor.HSSFColorPredefined.GREEN.getIndex(),cellRange,sheet);
                   RegionUtil.setLeftBorderColor(HSSFColor.HSSFColorPredefined.GREEN.getIndex(),cellRange,sheet);
                   RegionUtil.setRightBorderColor(HSSFColor.HSSFColorPredefined.GREEN.getIndex(),cellRange,sheet);
               }
            }
        }
        sheet.autoSizeColumn(2);
        setSizeColumn(sheet,entityColumnLength+childColumnLength);
    }
    
    private void initWorkBook(){
        // 创建一个工作簿
        workbook = HSSFWorkbookFactory.createWorkbook();
        // 创建一个sheet
        sheet = workbook.createSheet();
        // 默认表格列宽
        sheet.setDefaultColumnWidth(18);
        patriarch = sheet.createDrawingPatriarch();
    }
    
    private void createTableTitle(String title,int colspan, HSSFCellStyle headerStyle) {
        if(StringUtils.isBlank(title)){
            return;
        }
        HSSFRow row = sheet.createRow(rowNum);
        row.setHeightInPoints(30f);
        HSSFCell cell = row.createCell(0);
        sheet.addMergedRegion(new CellRangeAddress(rowNum,rowNum,0,colspan));
        cell.setCellStyle(headerStyle);
        cell.setCellValue(title);
        rowNum ++;
    }
    
    private void createTableHead(String[] header, HSSFCellStyle headerStyle) {
        if(header ==null || header.length <1){
            return;
        }
        HSSFRow row = sheet.createRow(rowNum);
        HSSFCell cell;
        for (int i = 0; i < header.length; i++){
            cell = row.createCell(i);
            cell.setCellStyle(headerStyle);
            cell.setCellValue(header[i]);
            cell.setCellType(CellType.STRING);
        }
    }

    
    private void createTableCell(HSSFCell cell, Object value, HSSFCellStyle normalStyle, String pattern, int rowspan) {
        cell.setCellStyle(normalStyle);
        if (value ==null){
            return;
        }
        if(value instanceof Number){
            cell.setCellType(CellType.NUMERIC);
            cell.setCellValue(Double.parseDouble(value.toString()));
        //日期
        } else if(value instanceof Date){
            cell.setCellType(CellType.STRING);
            cell.setCellValue(DateUtils.parseDateToStr(pattern,(Date)value));
        // 图片
        } else if(value instanceof byte[]){
            cell.getRow().setHeightInPoints(80);
            sheet.setColumnWidth(cell.getColumnIndex(),(short) (34.5 * 110));
            HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0,
                    1023, 255, (short) cell.getColumnIndex(), rowNum, (short) cell.getColumnIndex(), rowNum);
            anchor.setAnchorType(ClientAnchor.AnchorType.MOVE_DONT_RESIZE);
            patriarch.createPicture(anchor, workbook.addPicture(
                    (byte[])value, HSSFWorkbook.PICTURE_TYPE_JPEG));

        }else if(value instanceof Boolean){
            cell.setCellType(CellType.STRING);
            if((boolean)value){
                cell.setCellValue("\u221A");
            }
            // 全部当作字符串处理
        }else{
            cell.setCellType(CellType.STRING);
            cell.setCellValue(new HSSFRichTextString(String.valueOf(value)));
        }
    }

    
    private HSSFCellStyle getTitleStyle(HSSFWorkbook workbook) {
        HSSFCellStyle style = getNormalStyle(workbook);
        style.getFont(workbook).setFontHeightInPoints((short)12);
        style.setAlignment(HorizontalAlignment.CENTER);
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        return style;
    }

    
    private HSSFCellStyle getFooterStyle(HSSFWorkbook workbook) {
        HSSFCellStyle style = getNormalStyle(workbook);
        style.getFont(workbook).setFontHeightInPoints((short)12);
        style.setAlignment(HorizontalAlignment.CENTER);
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        style.setFillForegroundColor(IndexedColors.LIME.getIndex());
        style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        return style;
    }

    
    private HSSFCellStyle getHeaderStyle(HSSFWorkbook workbook) {
        HSSFCellStyle style = getNormalStyle(workbook);
        style.getFont(workbook).setFontHeightInPoints((short)11);
        style.setAlignment(HorizontalAlignment.CENTER);
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        style.setFillForegroundColor(IndexedColors.LIME.getIndex());
        style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        HSSFPalette palette = workbook.getCustomPalette();
        palette.setColorAtIndex(IndexedColors.LIME.getIndex(),(byte)198,(byte)224,(byte)180);
        return style;
    }

    
    private HSSFCellStyle createPercentStyle(HSSFWorkbook workbook){
        HSSFCellStyle style = getNormalStyle(workbook);
        style.setDataFormat(HSSFDataFormat.getBuiltinFormat("0.00%"));
        return style;
    }

    
    private HSSFCellStyle getNormalStyle(HSSFWorkbook workbook){
        // 创建字体
        HSSFFont font = workbook.createFont();
        font.setFontHeightInPoints((short)10);
        // 构建样式
        HSSFCellStyle style = workbook.createCellStyle();
        // 设置边框
        style.setBorderTop(BorderStyle.THIN);
        style.setBorderRight(BorderStyle.THIN);
        style.setBorderBottom(BorderStyle.THIN);
        style.setBorderLeft(BorderStyle.THIN);
        style.setTopBorderColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex());
        style.setRightBorderColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex());
        style.setBottomBorderColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex());
        style.setLeftBorderColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex());
        style.setAlignment(HorizontalAlignment.CENTER);
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        style.setFont(font);
        // 字体默认换行
        style.setWrapText(true);
        return style;
    }


    
    private <E> Object  getValue(E t,String fieldName){
        String methodName = "get"
                + fieldName.substring(0, 1).toUpperCase()
                + fieldName.substring(1);
        try {
            Method method = t.getClass().getMethod(methodName);
            method.setAccessible(true);
            Object value = method.invoke(t);
            return value;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }
    
    private void ioExcelHSSF(HSSFWorkbook workbook, String path){
        OutputStream ops =null;
        if(StringUtils.isBlank(fileName)){
            path = path + DateUtils.dateTimeNow() +".xls";
        } else {
            path = path + fileName + ".xls";
        }
        try {
            ops = new FileOutputStream(path);
            workbook.write(ops);
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }finally {
            if(ops != null){
                try {
                    ops.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
    }

    private void httpExcelHSSF(HSSFWorkbook workbook, HttpServletResponse response){
        OutputStream ops = null;
        try {
            ops = response.getOutputStream();
            response.flushBuffer();
            workbook.write(ops);
        } catch (IOException e) {
            e.printStackTrace();
            if(ops !=null){
                try {
                    ops.close();
                } catch (IOException ex) {
                    ex.printStackTrace();
                }
            }
        }
    }

    
    private void setSizeColumn(HSSFSheet sheet, int size) {
        for(int i =0;i<size;i++){
            int columnWidth = sheet.getColumnWidth(i) / 256;
            for (int rowNum = 1; rowNum <= sheet.getLastRowNum(); rowNum++) {
                HSSFRow currentRow;
                //当前行未被使用过
                if (sheet.getRow(rowNum) == null) {
                    currentRow = sheet.createRow(rowNum);
                } else {
                    currentRow = sheet.getRow(rowNum);
                }

                if (currentRow.getCell(i) != null) {
                    HSSFCell currentCell = currentRow.getCell(i);
// if(rowNum==sheet.getLastRowNum()){
// HSSFCellStyle style = currentCell.getCellStyle();
// style.setFillForegroundColor(IndexedColors.LIME.getIndex());
// style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
// currentCell.setCellStyle(style);
// }
                    if (currentCell.getCellType() == CellType.STRING) {
                        int length = currentCell.getStringCellValue().getBytes().length;
                        if (columnWidth < length) {
                            columnWidth = length;
                        }
                    }
                }
            }
            sheet.setColumnWidth(i, columnWidth * 256);
        }
    }
}

效果图如下：

但仍遇到一个问题，主子表结构导出，如果图片在主表，合并行之后，图片并不会居中，并且第一行会被撑开，有没有比较简单的方式进行处理(不想重新计算锚点，然后定高输出)？