最近的工作圍繞報表導出，并沒有集成相應的報表插件，只是使用了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;/** * @author ππ * @date 2020-6-22 17:03 * 導出的Excel中，百分比 */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.*;/** * @author ππ * @date 2020-6-10 14:45 * excel 導出通用類 * 采用反射生成 * 目前僅支持導出slx，暫不支持導出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; } /** * 導出Excel到指定位置 * @param fields 字段集合 主表key為entity,子表key為children * @param dataset 數據集合 注意：如果為主子表，主表中，子表集合對應的屬性名必須為children,反射使用的children進行映射，可修改 * @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); } /** * 導出Excel到指定位置 * @param fields 字段集合 主表key為entity,子表key為children * @param header 表頭數組 * @param dataset 數據集合 注意：如果為主子表，主表中，子表集合對應的屬性名必須為children,反射使用的children進行映射，可修改 * @param path 文件路徑 * @param childBefore 子表在前 默認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); } /** * 導出Excel到指定位置 * @param fields 字段集合 主表key為entity,子表key為children * @param header 表頭數組 * @param dataset 數據集合 注意：如果為主子表，主表中，子表集合對應的屬性名必須為children,反射使用的children進行映射，可修改 * @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); } /** * 導出文件到本地 * @param fields 字段集合 主表key為entity,子表key為children * @param dataset 數據集合 注意：如果為主子表，主表中，子表集合對應的屬性名必須為children,反射使用的children進行映射，可修改 * @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); } /** * 導出文件到本地 * @param fields 字段集合 主表key為entity,子表key為children * @param header 表頭數組 * @param dataset 數據集合 注意：如果為主子表，主表中，子表集合對應的屬性名必須為children,反射使用的children進行映射，可修改 * @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); } /** * 導出文件到本地 * @param fields 字段集合 主表key為entity,子表key為children * @param header 表頭數組 * @param dataset 數據集合 注意：如果為主子表，主表中，子表集合對應的屬性名必須為children,反射使用的children進行映射，可修改 * @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); } /** * 頁面下載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); } /** * 公共方法，創建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){ // 初始化構建 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(); } /** * 創建Excel標題 * @param title 標題 * @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 ++; } /** * 創建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); } } /** * 創建單元格 * @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'); } // 全部當作字符串處理 }else{ cell.setCellType(CellType.STRING); cell.setCellValue(new HSSFRichTextString(String.valueOf(value))); } } /** * 創建標題行 * @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; } /** * 創建尾部合計行 * @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; } /** * 創建表頭樣式 * @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; } /** * 創建普通樣式 * @param workbook * @return */ 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; } /** * 反射獲取值 * @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();} } } } /** * 自適應列寬 * @param sheet * @param size 列數 */ 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); } }}

效果圖如下：

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

到此這篇關于Java Poi 在Excel中輸出特殊符號的文章就介紹到這了,更多相關java poi excel 輸出特殊符號內容請搜索好吧啦網以前的文章或繼續瀏覽下面的相關文章希望大家以后多多支持好吧啦網！