- 单元格显示格式化(数值加百分号)
- 设置字体
- 添加批注
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.*;
import org.apache.poi.xssf.usermodel.extensions.XSSFCellBorder;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.OutputStream;
import java.util.Arrays;
import java.util.List;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
public class ExcelUtil {
protected static Logger logger = LoggerFactory.getLogger(ExcelUtil.class);
private static final String CHARSET = "UTF-8";
/**
* 生成文件
* @param response 相应体
* @param request 请求体
* @param extfilename Excel文件名
*/
private static void fileName(HttpServletResponse response, HttpServletRequest request, String extfilename){
try {
String agent = request.getHeader("USER-AGENT").toLowerCase();
if(agent.contains("msie")){//根据浏览器类型处理文件名称
extfilename = java.net.URLEncoder.encode(getValidFileName(extfilename), CHARSET);
} else{
if(agent.contains("gecko")&&agent.contains("rv:11.0")) {
extfilename = java.net.URLEncoder.encode(getValidFileName(extfilename), CHARSET);
}else {
extfilename = new String(getValidFileName(extfilename).getBytes(CHARSET), "ISO8859-1");
}
}
response.setContentType("application/msexcel");
response.addHeader("Content-Disposition", "attachment;filename=" + extfilename + ".xlsx");
}catch (Exception e) {
logger.error(e.getMessage());
}
}
/**
* 检验获取合法的文件名
* @param fileName 原文件名称
* @return 合法的经处理的文件名称
*/
private static String getValidFileName(String fileName){
Pattern pattern = Pattern.compile("[\\s\\\\/:\\*\\?\\\"<>\\|]");
Matcher matcher = pattern.matcher(fileName);
fileName= matcher.replaceAll(""); // 将匹配到的非法字符以空替换
if(fileName.contains("("))
fileName = fileName.replace("(","(");
if(fileName.contains(")"))
fileName = fileName.replace(")",")");
return fileName;
}
public static void exportExcel(HttpServletResponse response, HttpServletRequest request,
List<String> colNames, List<String> valueNames, String fileName, List<Map<String, Object>> data) {
try(OutputStream os = response.getOutputStream();
XSSFWorkbook wb = new XSSFWorkbook()) {
ExcelUtil.fileName(response,request,fileName);
//1. 制作Excel表头
Sheet sheet = wb.createSheet();
XSSFDataFormat format = wb.createDataFormat();//自定义数据格式
format.putFormat((short)1,"0.00%");
Font ztFont = wb.createFont();
ztFont.setFontName("黑体");
XSSFCellStyle titleStyle = wb.createCellStyle();
titleStyle.setAlignment(HorizontalAlignment.CENTER);
titleStyle.setVerticalAlignment(VerticalAlignment.CENTER);
titleStyle.setFont(ztFont);
titleStyle.setBorderRight(BorderStyle.THIN);
titleStyle.setBorderLeft(BorderStyle.THIN);
titleStyle.setBorderTop(BorderStyle.THIN);
titleStyle.setBorderBottom(BorderStyle.THIN);
titleStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
titleStyle.setFillForegroundColor(new XSSFColor(new java.awt.Color(255, 192, 0)));
XSSFCellStyle cellStyle = wb.createCellStyle();
cellStyle.setLocked(false);
cellStyle.setAlignment(HorizontalAlignment.LEFT);
cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
cellStyle.setBorderRight(BorderStyle.THIN);
cellStyle.setBorderLeft(BorderStyle.THIN);
cellStyle.setBorderTop(BorderStyle.THIN);
cellStyle.setBorderBottom(BorderStyle.THIN);
cellStyle.setBorderColor(XSSFCellBorder.BorderSide.TOP,new XSSFColor(new java.awt.Color(0,0,0)));
XSSFCellStyle centStyle = wb.createCellStyle();
centStyle.setLocked(false);
centStyle.setAlignment(HorizontalAlignment.LEFT);
centStyle.setVerticalAlignment(VerticalAlignment.CENTER);
centStyle.setBorderRight(BorderStyle.THIN);
centStyle.setBorderLeft(BorderStyle.THIN);
centStyle.setBorderTop(BorderStyle.THIN);
centStyle.setBorderBottom(BorderStyle.THIN);
centStyle.setDataFormat(1);
centStyle.setBorderColor(XSSFCellBorder.BorderSide.TOP,new XSSFColor(new java.awt.Color(0,0,0)));
Row row = sheet.createRow((short) 0);
for (int i = 0; i < colNames.size(); i++) {
Cell cell=row.createCell(i);
cell.setCellValue(colNames.get(i));
cell.setCellStyle(titleStyle);
}
Cell cell = row.getCell(colNames.size()-1);
sheet.createDrawingPatriarch();
// 创建绘图对象
XSSFDrawing p = (XSSFDrawing) sheet.createDrawingPatriarch();
// 前四个参数是坐标点,后四个参数是编辑和显示批注时的大小.
XSSFComment comment = p.createCellComment(new XSSFClientAnchor(0, 0, 0,0, (short) 4, 3, (short) 6, 6));
// 输入批注信息
comment.setString(new XSSFRichTextString("相同行业的模拟权重相加等于100%"));
comment.setVisible(true);
// 添加作者,选中B5单元格,看状态栏
// comment.setAuthor("toad");
// 将批注添加到单元格对象中
cell.setCellComment(comment);
setData(data,sheet,cellStyle,centStyle,colNames,valueNames);
for (int i = 0; i < colNames.size(); i++) {
sheet.autoSizeColumn(i,true);
}
wb.write(os);
}catch (Exception e) {
logger.error("Method=>{},Exception=>{}","StockMicroConsumer",e.getMessage());
}
}
/**
* 设置单元格的数据值
* @param localData execl表格的数据
* @param sheet sheet的实体
* @param cellStyle 单元格样式
* @param centStyle
* @param colNames 单元格的头名称
* @param valueNames key
*/
private static void setData(List<Map<String, Object>> localData, Sheet sheet, XSSFCellStyle cellStyle, XSSFCellStyle centStyle, List<String> colNames, List<String> valueNames){
for (int i = 0; i < localData.size(); i++) {
Row row = sheet.createRow((short) (i+1));
Map<String,Object> map = localData.get(i);
for (int j = 0; j < colNames.size(); j++) {
Cell cell=row.createCell(j);
if ("weight".equals(valueNames.get(j))){
double value = new Double(map.get(valueNames.get(j)).toString());
cell.setCellValue(value);
cell.setCellStyle(centStyle);
}else {
cell.setCellValue(map.get(valueNames.get(j)).toString());
cell.setCellStyle(cellStyle);
}
}
}
}
}