Java Poi 在Excel中输出特殊符号的实现方法

Java Poi 在Excel中输出特殊符号的实现方法

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

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

相应的代码输出：

cell.setCellValue("\u221A");

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

org.apache.poi

poi

http:// 4.1.2

org.apache.poi

poi-ooxml

4.1.2

org.apache.poi

poi-ooxml-schemas

4.1.2

package com.king.tools.util;

import java.util.HashMap;

import java.util.Map;

/**

* @author

* @date 2020-6-22 17:03

* 导出的Exchttp://el中，百分比

*/

public class ExcelPercentField {

public final static Map 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 {

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> fields, Collection 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> fields,String[] header,Collection 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> fields,String[] header,Collection 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> fields, Collection 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> fields, String[] header, Collection 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> fields, String[] header, Collection 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> fields, String[] header, Collection 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> fields,String[] header,Collection 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> fields, String[] header, Colhttp://lection 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 entityFields = fields.get("entity");

// 获取子表属性字段

List 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

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

if(i >0){

rowNum++;

row = sheet.createRow(rowNum);

}

for(int j = 0;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

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);

RegioIJyxnFlrSnUtil.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

* @return

*/

private 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

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);

}

}

}

效果图如下：

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

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。