2021-09-05poi 使用心得
1、版本
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.17</version>
</dependency>2、根据需求处理相关的
1、需求：做成下面的样子
2、思路：
1、查找数据
2、对数据按照黄色的时间进行分组
3、创建第一行头
4、创建第二行的行头，并且填入数据，–还可以处理以下每个单元格的大小
5、按照分组数据，循环填入数据，当然黄色部分是需要合并的
6、告诉前端下载
7、使用postman自测下载
8、调整样式
3、数据查询和接口
getEventByFilter(pageReq)；是封装的方法，直接阔以忽略
@RestController
@RequestMapping("/event")
@Slf4j
public class EventController {
public HSSFWorkbook hssfWorkbook ;
public HSSFSheet sheet;
@RequestMapping("/print")
public void print(@RequestBody PageReq<EventEntity> pageReq, HttpServletResponse response) {
// 获取数据
pageReq.setSize(-1);
IPage<EventEntity> eventByFilter = getEventByFilter(pageReq);
List<EventEntity> records = eventByFilter.getRecords();
if(!CollectionUtils.isEmpty(records)){
//LocalDateTime--需要转换为String
// String localDateTimeToString = LocalDateTime.now().format(DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss"));
//records= records.stream().sorted(Comparator.comparing(EventEntity::getCreateTime)).collect(Collectors.toList());
// 按照创建 的时间的年月日进行分组 // 将数据转换为想要的数据结构
Map<String, List<EventEntity>> entryMap = records.stream().collect(Collectors.groupingBy(tem -> tem.getReportTime().format(DateTimeFormatter.ofPattern("yyyy-MM-dd"))));
// map可能不是有顺序的所以需要排序--可能需要排序--内部的list也需要排序
printMy(pageReq,entryMap,records.size(),response);
}
}4、打印方法
printMy(pageReq,entryMap,records.size(),response);
public void printMy(PageReq<EventEntity> pageReq,Map<String, List<EventEntity>> entryMap,int totalSize,HttpServletResponse response){
// 创建HSSFWorkbook对象
hssfWorkbook = new HSSFWorkbook();
// 全局的样式
HSSFCellStyle cellStyle = null;
// 创建一个 sheet
sheet = hssfWorkbook.createSheet("值班记录");
// 一行信息
HSSFRow row = null;
//一个单元格
HSSFCell hssfCell = null;
// 以下是sheep的操作
// 第一行--开始时间到结束时间
int lineNumber =0;
String begintime= (pageReq.getBeginTime()!=null?pageReq.getBeginTime().format(DateTimeFormatter.ofPattern("yyyy-MM-dd")):"");
String endtime= (pageReq.getEndTime()!=null?pageReq.getEndTime().format(DateTimeFormatter.ofPattern("yyyy-MM-dd")):"");
String tableHeaderFirst= begintime+"~"+endtime+"值班记录";
// 第二行
String[] tableHeaderTwo = {"日期", "序号", "上报人员", "上报时间", "上报渠道", "上报部门", "上报问题","所属系统","响应时间","响应人员","处理人","协助人","回复时间","需求及现象","原因及解决方法","处理分类","备注","事件进度"};
// 循环得到的map数据，然后根据日期来一个个循环处理
// 第一行标题字体13号，颜色还是黑色，背景色还是普通
cellStyle = getStyle(hssfWorkbook,HorizontalAlignment.CENTER,HSSFColor.BLACK.index,(short)13,IndexedColors.AUTOMATIC.getIndex());
lineNumber = writeJustOneLine(sheet,row,hssfCell,cellStyle,tableHeaderTwo,lineNumber,tableHeaderFirst);
// 第二行标题
cellStyle = getStyle(hssfWorkbook,HorizontalAlignment.CENTER,HSSFColor.BLACK.index,(short)9,IndexedColors.SEA_GREEN.getIndex());
lineNumber = writeJustOneLine(sheet,row,hssfCell,cellStyle,tableHeaderTwo,lineNumber,null);
setLineLength(tableHeaderTwo);
// 处理有规律的数据---按照key进行排序
//Set<Map.Entry<String, List<EventEntity>>> entries = entryMap.entrySet();
List<Map.Entry<String,List<EventEntity>>> lstEntry=new ArrayList(entryMap.entrySet());
Collections.sort(lstEntry,((o1, o2) -> {
return o1.getKey().compareTo(o2.getKey());
}));
for (Map.Entry<String, List<EventEntity>> entry : lstEntry){
// 得到最初的日期
String key = entry.getKey();
// 日期是一行
cellStyle = getStyle(hssfWorkbook,HorizontalAlignment.LEFT,HSSFColor.BLACK.index,(short)9,IndexedColors.LIGHT_ORANGE.getIndex());
lineNumber = writeJustOneLine(sheet,row,hssfCell,cellStyle,tableHeaderTwo,lineNumber,key);
// 得到日期下的数据
List<EventEntity> eventEntities = entry.getValue();
eventEntities= eventEntities.stream().sorted(Comparator.comparing(EventEntity::getReportTime)).collect(Collectors.toList());
// 循环将数据写入
cellStyle = getStyle(hssfWorkbook,HorizontalAlignment.CENTER,HSSFColor.BLACK.index,(short)9,IndexedColors.AUTOMATIC.getIndex());
lineNumber = writeEventEntityInfo(sheet,row,hssfCell,cellStyle,tableHeaderTwo,lineNumber,eventEntities);
}
// 最后一个合计
row = sheet.createRow(lineNumber);
hssfCell = row.createCell(0);
hssfCell.setCellValue("合计");
hssfCell = row.createCell(1);
hssfCell.setCellValue(totalSize);
// sheet处理整体样式
// 告诉前端进行下载
try {
String fileName = tableHeaderFirst;
OutputStream outputStream = response.getOutputStream();
hssfWorkbook.write(generateResponseExcel(fileName, response));
outputStream.flush();
outputStream.close();
}catch (Exception e){
e.printStackTrace();
}
}在第二行，也就是标题写了之后我们可以先规定每个cell的大小，也就是有多少个字体
public void setLineLength(String[] tableHeaderTwo){
try {
for (int i = 0; i < tableHeaderTwo.length; i++) {
sheet.autoSizeColumn(i);
if(i== 6 | i==13 | i ==14 | i==16 ){
sheet.setColumnWidth(i, 512*13);
}
else if(i ==0){
sheet.setColumnWidth(i, 512*4);
}
else {
sheet.setColumnWidth(i, sheet.getColumnWidth(i)*13/10);
}
}
}
catch (Exception e){
e.printStackTrace();
}
}整体样式方法
public HSSFCellStyle getStyle(HSSFWorkbook hssfWorkbook,HorizontalAlignment horizontalAlignment,short fontColor ,short fontSize,short backGroundColor){
//将字体对象放入XSSFCellStyle对象中
HSSFCellStyle cellStyle = hssfWorkbook.createCellStyle();
// 字体是左对齐还是居中---黄色的日期是居中的
if(horizontalAlignment == null){
// 水平居中
cellStyle.setAlignment(HorizontalAlignment.CENTER);
}else {
cellStyle.setAlignment(horizontalAlignment);
}
//IndexedColors.RED.getIndex()
// 背景颜色
cellStyle.setFillForegroundColor(backGroundColor);
cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
// 垂直居中
// cellStyle.setFillBackgroundColor(IndexedColors.RED.getIndex());
cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
// 换行
cellStyle.setWrapText(true);
// 下面是四个边框
cellStyle.setBorderBottom(BorderStyle.THIN);
cellStyle.setBorderLeft(BorderStyle.THIN);
cellStyle.setBorderTop(BorderStyle.THIN);
cellStyle.setBorderRight(BorderStyle.THIN);
// 全局样式
HSSFFont font = null;
//创建一个字体对象
font = hssfWorkbook.createFont();
//给字体对象设置颜色属性
// HSSFColor.RED.index
font.setColor(fontColor);
font.setFontHeightInPoints(fontSize);
cellStyle.setFont(font);
return cellStyle;
}对于最前面的2个头，或者是下面的第一行黄色的都是可以由一个方法做的
public int writeJustOneLine( HSSFSheet sheet,HSSFRow row, HSSFCell hssfCell,HSSFCellStyle cellStyle,String[] tableHeaderTwo,int lineNumber,String tableHeaderFirst){
//循环赋值并且合并单元格
row = sheet.createRow(lineNumber);
for (int i = 0; i < tableHeaderTwo.length; i++) {
hssfCell = row.createCell(i);
//只填一个表格
if(StringUtils.isNotEmpty(tableHeaderFirst)){
hssfCell.setCellValue(tableHeaderFirst);
}else {
hssfCell.setCellValue(tableHeaderTwo[i]);
}
hssfCell.setCellStyle(cellStyle);
}
// 合并第一行大标题
if(StringUtils.isNotEmpty(tableHeaderFirst)) {
sheet.addMergedRegion(new CellRangeAddress(lineNumber , lineNumber, 0, tableHeaderTwo.length-1));
}
// 行数加1
lineNumber++;
return lineNumber;
}填入真实数据–每次写一行，每行又每一次写一个单元格；
public int writeEventEntityInfo(HSSFSheet sheet,HSSFRow row, HSSFCell hssfCell,HSSFCellStyle cellStyle,String[] tableHeaderTwo,int lineNumber,List<EventEntity> eventEntities){
// 循环tableHeaderTwo，然后每循环都需要创建一行然后，创建多个单元格，并且填入数据；
// ctrl+alt+J,
int k = 0;
for (EventEntity eventEntity : eventEntities) {
row = sheet.createRow(lineNumber);
k++;
for (int i = 0; i < 18; i++) {
hssfCell = row.createCell(i);
hssfCell.setCellStyle(cellStyle);
switch (i){
case 0:
//时间不需要
hssfCell.setCellValue("");
break;
case 1:
// 序号
hssfCell.setCellValue(k);
break;
case 2:
// 上报人员R
hssfCell.setCellValue(eventEntity.getReportPersonName());
break;
case 3:
// 上报时间需要转换
hssfCell.setCellValue(getStringTime(eventEntity.getReportTime()));
break;
case 4:
// 上报渠道
hssfCell.setCellValue(eventEntity.getReportWay());
break;
case 5:
// 上报部门
hssfCell.setCellValue(eventEntity.getDepartment());
break;
case 6:
// 上报问题
// String shortString = getShortString(eventEntity.getReportProblem(), 30);
hssfCell.setCellValue(eventEntity.getReportProblem());
break;
case 7:
// 所属系统
hssfCell.setCellValue(eventEntity.getSystemCode());
break;
case 8:
// 响应时间
hssfCell.setCellValue(getStringTime(eventEntity.getResponseTime()));
break;
case 9:
// 响应人员
hssfCell.setCellValue(eventEntity.getReportPersonName());
break;
case 10:
// 处理人
hssfCell.setCellValue(eventEntity.getHandlePersonName());
break;
case 11:
// 协助人
hssfCell.setCellValue(eventEntity.getAssistPersonName());
break;
case 12:
// 回复时间--解决时间
hssfCell.setCellValue(getStringTime(eventEntity.getActualFinishTime()));
break;
case 13:
// 需求以及现象\
// String eventReasonAnalysis = getShortString(eventEntity.getEventReasonAnalysis(), 30);//
hssfCell.setCellValue(eventEntity.getEventReasonAnalysis());
break;
case 14:
// 事件的描述
hssfCell.setCellValue(eventEntity.getProblemDescription());
break;
case 15:
// 事件类型
hssfCell.setCellValue(eventEntity.getEventType());
break;
case 16:
// 备注说明
hssfCell.setCellValue(eventEntity.getEventSupplement());
break;
case 17:
// 事件进度
hssfCell.setCellValue(EventStatusEnum.getCodeNameByCode(eventEntity.getEventStatus()));
break;
default:
break;
}
}
lineNumber++;
}
return lineNumber;
}告诉response需要下载
private ServletOutputStream generateResponseExcel(String excelName, HttpServletResponse response) throws IOException {
excelName = excelName == null || "".equals(excelName) ? "excel" : URLEncoder.encode(excelName, "UTF-8");
response.setContentType("application/vnd.ms-excel;charset=utf-8");
response.setHeader("Content-Disposition", "attachment; filename=" + excelName + ".xlsx");
return response.getOutputStream();
}
`5、启动程序，使用postman访问，选择保存为文件即可
6、查看和修改；表格的整体很容易出来但是也需要不停的调试
3、背景颜色对照略
4、背景颜色填充形式略
5、前端由于post请求导致无法直接下载问题解决
在这里插入代码片
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>message push test</title>
<script type="text/javascript" src="https://cdn.bootcss.com/jquery/3.3.1/jquery.min.js"></script>
<script type="text/javascript" src="https://cdn.bootcss.com/jquery.form/4.2.2/jquery.form.min.js"></script>
<script type="text/javascript" src="https://cdn.bootcss.com/sockjs-client/1.1.4/sockjs.min.js"></script>
<script type="text/javascript" src="https://cdn.bootcss.com/stomp.js/2.3.3/stomp.min.js"></script>
<script type="text/javascript">
function connect() {
var url = "http://173.100.1.67:7077/smart-ops-app/event/print" ;
//var url = "http://localhost:8971/event/print" ;
var xhr = new XMLHttpRequest();
xhr.open('POST', url, true);
// 返回类型blob
xhr.responseType = "blob";
xhr.setRequestHeader("Content-Type", "application/json");
xhr.onload=function(){
const blob = new Blob([this.response], { type: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet' });
if ('download' in document.createElement('a')) {
const link = document.createElement('a');
link.download = '值班记录.xlsx';
link.style.display = 'none';
link.href = URL.createObjectURL(blob);
document.body.appendChild(link);
link.click();
URL.revokeObjectURL(link.href);
document.body.removeChild(link);
} else {
navigator.msSaveBlob(blob);
}
}
xhr.setRequestHeader("Authorization", "Bearer eyJhbGciOiJIUzI1NiJ9.eyJzdWIiOiJkM2NmZmQ0YzdmMjA0ZTNiOTU1MjUxMzFlM2MyM2EyNSIsInVzZXJJZCI6ImQzY2ZmZDRjN2YyMDRlM2I5NTUyNTEzMWUzYzIzYTI1IiwidXNlckxvZ2luTmFtZSI6IuadjumbhCIsInVzZXJOYW1lIjoiQkE5RDdGQUEyRDFEN0NBQSIsImRlcHRJZCI6IjgxY2M1NWE0NTFhMzRmYTdhZmFlYjY3MzlkYzcxYzAwIiwiZGVwdENvZGUiOiLmioDmnK_kuK3lv4MiLCJkZXB0TmFtZSI6IuaKgOacr-S4reW_gyIsInJvbGVDb2RlcyI6InByb2JsZW1IYW5kbGVyIiwiaWF0IjoxNjMwODkzODE5LCJleHAiOjE2MzA5ODAyMTl9.DVUF6ZChGHj_Wlbrw6iMcuS-O_sVkz5tQ6IAl5hkV9s");
// 发送ajax请求
xhr.send(JSON.stringify({"current":1,"size":20,"filter":{"statusList":["B","C","D"],"mastAboutMe":false}}));
}
</script>
</head>
<body class="easyui-layout">
<div>
<div>
<button id="connect" onclick="connect();">Connect</button>
</div>
</div>
</body>
</html>
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
