java poi excel 输出特殊符号 Java Poi 在Excel中输出特殊符号的实现方法
浮生－ 人气:1最近的工作围绕报表导出，并没有集成相应的报表插件，只是使用了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);
}
}
}
效果图如下：
但仍遇到一个问题，主子表结构导出，如果图片在主表，合并行之后，图片并不会居中，并且第一行会被撑开，有没有比较简单的方式进行处理(不想重新计算锚点，然后定高输出)？
加载全部内容