最近的工作圍繞報(bào)表導(dǎo)出，并沒有集成相應(yīng)的報(bào)表插件，只是使用了Poi。其中有一個(gè)需求，Excel中導(dǎo)出特殊符號(hào)，如√、×等。在網(wǎng)上找尋了許久，沒有相關(guān)資料，故記錄分享一下。

思考良久，走了不少?gòu)澛罚詈笫?System.out.println() 啟發(fā)，實(shí)現(xiàn)方式真的超級(jí)簡(jiǎn)單。每一個(gè)特殊符號(hào)，都對(duì)應(yīng)一個(gè)Unicode編碼，我們只需要將特定的符號(hào)，轉(zhuǎn)變成Unicode編碼，進(jìn)行輸出即可。

相應(yīng)的代碼輸出：

cell.setCellValue('u221A');

另附自己編寫的Excel工具類，支持單表、主子表(可定制主表在前還是在后)、圖片、特殊符號(hào)等。

<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;/** * @author ππ * @date 2020-6-22 17:03 * 導(dǎo)出的Excel中，百分比 */public class ExcelPercentField { public final static Map<String,String> percentFiledMap = new HashMap<>(); static { // 根據(jù)實(shí)際情況進(jìn)行設(shè)置 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.*;/** * @author ππ * @date 2020-6-10 14:45 * excel 導(dǎo)出通用類 * 采用反射生成 * 目前僅支持導(dǎo)出slx，暫不支持導(dǎo)出xlsx格式 */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; } /** * 導(dǎo)出Excel到指定位置 * @param fields 字段集合 主表key為entity,子表key為children * @param dataset 數(shù)據(jù)集合 注意：如果為主子表，主表中，子表集合對(duì)應(yīng)的屬性名必須為children,反射使用的children進(jìn)行映射，可修改 * @param path 文件路徑 */ 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); } /** * 導(dǎo)出Excel到指定位置 * @param fields 字段集合 主表key為entity,子表key為children * @param header 表頭數(shù)組 * @param dataset 數(shù)據(jù)集合 注意：如果為主子表，主表中，子表集合對(duì)應(yīng)的屬性名必須為children,反射使用的children進(jìn)行映射，可修改 * @param path 文件路徑 * @param childBefore 子表在前 默認(rèn)false */ 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); } /** * 導(dǎo)出Excel到指定位置 * @param fields 字段集合 主表key為entity,子表key為children * @param header 表頭數(shù)組 * @param dataset 數(shù)據(jù)集合 注意：如果為主子表，主表中，子表集合對(duì)應(yīng)的屬性名必須為children,反射使用的children進(jìn)行映射，可修改 * @param pattern 日期格式 * @param path 文件路徑 * @param 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); } /** * 導(dǎo)出文件到本地 * @param fields 字段集合 主表key為entity,子表key為children * @param dataset 數(shù)據(jù)集合 注意：如果為主子表，主表中，子表集合對(duì)應(yīng)的屬性名必須為children,反射使用的children進(jìn)行映射，可修改 * @param response http */ 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); } /** * 導(dǎo)出文件到本地 * @param fields 字段集合 主表key為entity,子表key為children * @param header 表頭數(shù)組 * @param dataset 數(shù)據(jù)集合 注意：如果為主子表，主表中，子表集合對(duì)應(yīng)的屬性名必須為children,反射使用的children進(jìn)行映射，可修改 * @param response http */ 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); } /** * 導(dǎo)出文件到本地 * @param fields 字段集合 主表key為entity,子表key為children * @param header 表頭數(shù)組 * @param dataset 數(shù)據(jù)集合 注意：如果為主子表，主表中，子表集合對(duì)應(yīng)的屬性名必須為children,反射使用的children進(jìn)行映射，可修改 * @param pattern 日期格式 * @param response http */ 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); } /** * 頁(yè)面下載excel * @param title * @param fields * @param header * @param dataset * @param pattern * @param 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); } /** * 輸出到指定路徑 * @param title * @param fields * @param header * @param dataset * @param pattern * @param path * @param childBefore */ 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); } /** * 公共方法，創(chuàng)建excel 2003版 * @param title * @param fields * @param header * @param dataset * @param pattern * @param childBefore */ private void createExcel2003(String title, Map<String, List<String>> fields, String[] header, Collection<T> dataset, String pattern, boolean childBefore){ // 初始化構(gòu)建 initWorkBook(); // 生成樣式 HSSFCellStyle titleStyle = getTitleStyle(workbook); HSSFCellStyle headerStyle = getHeaderStyle(workbook); HSSFCellStyle normalStyle = getNormalStyle(workbook); HSSFCellStyle footerStyle = getFooterStyle(workbook); HSSFCellStyle percentStyle = createPercentStyle(workbook); // 創(chuàng)建表頭 createTableTitle(title,header.length-1,titleStyle); // 生成標(biāo)題行 createTableHead(header,headerStyle); // 迭代集合 Iterator it = dataset.iterator(); // 獲取主表屬性字段 List<String> entityFields = fields.get('entity'); // 獲取子表屬性字段 List<String> childFields = fields.get('children'); // 主表字段長(zhǎng)度 int entityColumnLength = entityFields.size(); int childColumnLength = 0; if(childFields !=null){ childColumnLength = childFields.size(); } // 合并行 int rowspan = 0; // 每個(gè)對(duì)象的子表數(shù)據(jù) Object children = null; HSSFRow row; HSSFCell cell; while (it.hasNext()){ rowNum ++; T t = (T) it.next(); row = sheet.createRow(rowNum); // 確定合并行數(shù) 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));// 創(chuàng)建單元格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); } /** * 初始化構(gòu)建工作簿 */ private void initWorkBook(){ // 創(chuàng)建一個(gè)工作簿 workbook = HSSFWorkbookFactory.createWorkbook(); // 創(chuàng)建一個(gè)sheet sheet = workbook.createSheet(); // 默認(rèn)表格列寬 sheet.setDefaultColumnWidth(18); patriarch = sheet.createDrawingPatriarch(); } /** * 創(chuàng)建Excel標(biāo)題 * @param title 標(biāo)題 * @param colspan 合并列 * @param headerStyle 樣式 */ 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 ++; } /** * 創(chuàng)建Excel表頭 * @param header * @param headerStyle */ 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); } } /** * 創(chuàng)建單元格 * @param cell * @param value * @param normalStyle */ 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'); } // 全部當(dāng)作字符串處理 }else{ cell.setCellType(CellType.STRING); cell.setCellValue(new HSSFRichTextString(String.valueOf(value))); } } /** * 創(chuàng)建標(biāo)題行 * @param workbook * @return */ 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; } /** * 創(chuàng)建尾部合計(jì)行 * @param workbook * @return */ 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; } /** * 創(chuàng)建表頭樣式 * @param workbook * @return */ 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; } /** * 百分比格式 * @param workbook * @return */ private HSSFCellStyle createPercentStyle(HSSFWorkbook workbook){ HSSFCellStyle style = getNormalStyle(workbook); style.setDataFormat(HSSFDataFormat.getBuiltinFormat('0.00%')); return style; } /** * 創(chuàng)建普通樣式 * @param workbook * @return */ private HSSFCellStyle getNormalStyle(HSSFWorkbook workbook){ // 創(chuàng)建字體 HSSFFont font = workbook.createFont(); font.setFontHeightInPoints((short)10); // 構(gòu)建樣式 HSSFCellStyle style = workbook.createCellStyle(); // 設(shè)置邊框 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); // 字體默認(rèn)換行 style.setWrapText(true); return style; } /** * 反射獲取值 * @param t * @param fieldName * @param <E> * @return */ 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; } /** * 輸出IO流 * @param workbook * @param path * @return */ 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();} } } } /** * 自適應(yīng)列寬 * @param sheet * @param size 列數(shù) */ 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;//當(dāng)前行未被使用過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); } }}

效果圖如下：

但仍遇到一個(gè)問題，主子表結(jié)構(gòu)導(dǎo)出，如果圖片在主表，合并行之后，圖片并不會(huì)居中，并且第一行會(huì)被撐開，有沒有比較簡(jiǎn)單的方式進(jìn)行處理(不想重新計(jì)算錨點(diǎn)，然后定高輸出)？

到此這篇關(guān)于Java Poi 在Excel中輸出特殊符號(hào)的文章就介紹到這了,更多相關(guān)java poi excel 輸出特殊符號(hào)內(nèi)容請(qǐng)搜索好吧啦網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持好吧啦網(wǎng)！