先上导出效果图
我的需求没有太多的东西,所以数据内容没有多少,各位可以根据下面往下看喔
注意:后端代码中哪里使用样式都有注释说明,多看看注释 可能写的不是很通顺
我的工具类代码 导入就行可以多个sheet使用有简单的讲解;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.RegionUtil;
public class ExcelUtil {
/**
* 说明一下我这个工具类里面的方法
* @param workbook 定义workbook无所谓
* @param sheet sheet页 让你将一堆数据根据某个条件分类 在一个表中有不同的sheet
* @param content sheet中的Title
*/
public static void setHeaderStyle(Workbook workbook, Sheet sheet ,String content) {
// 设置表头样式
Row headerRow = sheet.createRow(0);
headerRow.setHeightInPoints(40);
Cell cell = headerRow.createCell(0);
cell.setCellValue(content); //定义Title中输入的内容
//合并第一行的四列 (0, 0, 0, 4)这个合并几列 只需改动最后的数值就行,比如: (0, 0, 0, 5) 就是五列
CellRangeAddress cellRangeAddress = new CellRangeAddress(0, 0, 0, 4);
sheet.addMergedRegion(cellRangeAddress);
// 设置边框
RegionUtil.setBorderBottom(CellStyle.BORDER_THIN, cellRangeAddress, sheet,workbook);
RegionUtil.setBorderLeft(CellStyle.BORDER_THIN, cellRangeAddress, sheet,workbook);
RegionUtil.setBorderRight(CellStyle.BORDER_THIN, cellRangeAddress, sheet,workbook);
RegionUtil.setBorderTop(CellStyle.BORDER_THIN, cellRangeAddress, sheet,workbook);
// 内容铺满
CellStyle style = workbook.createCellStyle();
style.setWrapText(false);
style.setAlignment(CellStyle.ALIGN_CENTER);
style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
cell.setCellStyle(style);
//调整第一行的长度和宽度 380长 100宽
sheet.setColumnWidth(0,380 * 100);
//调节字体大小 粗细
Font font = workbook.createFont();
font.setFontHeightInPoints((short) 15);
font.setBold(true);
style.setFont(font);
//第二行表头 (这里的第二行对应上图中显示的数据 也就是数据库中查询的字段 )
Row secondRow = sheet.createRow(1);
secondRow.setHeightInPoints(30);
secondRow.createCell(0).setCellValue("机构名称");
secondRow.createCell(1).setCellValue("警号");
secondRow.createCell(2).setCellValue("用户名");
secondRow.createCell(3).setCellValue("评价时间");
secondRow.createCell(4).setCellValue("评价结果");
//设置第二行样式
CellStyle gs1 = workbook.createCellStyle();
Font fontg = workbook.createFont();
fontg.setFontHeightInPoints((short) 11);
gs1.setFont(fontg);
gs1.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
gs1.setAlignment(CellStyle.ALIGN_CENTER);
gs1.setBorderBottom(CellStyle.BORDER_THIN);
gs1.setBottomBorderColor(IndexedColors.BLACK.getIndex());
gs1.setBorderLeft(CellStyle.BORDER_THIN);
gs1.setLeftBorderColor(IndexedColors.BLACK.getIndex());
gs1.setBorderRight(CellStyle.BORDER_THIN);
gs1.setRightBorderColor(IndexedColors.BLACK.getIndex());
gs1.setBorderTop(CellStyle.BORDER_THIN);
gs1.setTopBorderColor(IndexedColors.BLACK.getIndex());
for (int i = 0; i < 5; i++) {
secondRow.getCell(i).setCellStyle(gs1);
}
}
//**** 这个是开始循环数据 样式
/**
*
* @param workbook
* @param sheet 以防存在多个sheet页
* @param row 从第几行开始循环样式
*/
public static void setRowStyle(Workbook workbook, Sheet sheet, Row row) {
// 设置行样式
CellStyle cellStyle = workbook.createCellStyle();
//设置字体大小
Font font = workbook.createFont();
font.setFontHeightInPoints((short) 11);
cellStyle.setFont(font);
//居中显示
cellStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
//设置边框 垂直居中显示
cellStyle.setAlignment(CellStyle.ALIGN_CENTER);
cellStyle.setBorderBottom(CellStyle.BORDER_THIN);
cellStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex());
cellStyle.setBorderLeft(CellStyle.BORDER_THIN);
cellStyle.setLeftBorderColor(IndexedColors.BLACK.getIndex());
cellStyle.setBorderRight(CellStyle.BORDER_THIN);
cellStyle.setRightBorderColor(IndexedColors.BLACK.getIndex());
cellStyle.setBorderTop(CellStyle.BORDER_THIN);
cellStyle.setTopBorderColor(IndexedColors.BLACK.getIndex());
// 查询有多少数据渲染样式
for (int i = 0; i < row.getLastCellNum(); i++) {
row.getCell(i).setCellStyle(cellStyle);
}
}
/**
*
* @param sheet
*/
public static void setColumnWidth(Sheet sheet) {
// 设置列宽
int totalWidth = sheet.getColumnWidth(0) + sheet.getColumnWidth(1) + sheet.getColumnWidth(2) + sheet.getColumnWidth(3) + sheet.getColumnWidth(4);
int averageWidth = totalWidth / 5;
// 设置每列的宽度为平均宽度
sheet.setColumnWidth(0, averageWidth);
sheet.setColumnWidth(1, averageWidth);
sheet.setColumnWidth(2, averageWidth);
sheet.setColumnWidth(3, averageWidth);
sheet.setColumnWidth(4, averageWidth);
}
}
我的后端代码 ; 注意看哪里使用样式工具类方便引入自己代码
可以Ctrl+F 搜索 ExcelUtil 查看
public static void main(String[] args){
//**********附上了我的部分代码以防导入后出现报错
// 创建一个 SimpleDateFormat 对象,用于将字符串转换为日期
SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss", Locale.CHINA);
format.setTimeZone(TimeZone.getTimeZone("Asia/Shanghai"));
SimpleDateFormat dateFormat = new SimpleDateFormat("yyyyMM");
Calendar calendar = Calendar.getInstance();
calendar.add(Calendar.MONTH, -1);
String lastMonth = dateFormat.format(calendar.getTime()); // 获取当前月-1 数据
//String lastMonth = dateFormat.format(new Date());
//表头时间
SimpleDateFormat dateFormatForCell = new SimpleDateFormat("yyyy年MM月");
Calendar calendar1 = Calendar.getInstance();
calendar1.add(Calendar.MONTH, -1);
String ny = dateFormatForCell.format(calendar1.getTime()); //获取当前月-1
//String ny = dateFormatForCell.format(new Date());
//郭杜车管所
List<UserInfo> list = getDeptData("1100000007");
//东所
List<UserInfo> list1 = getDeptData("1100000008");
// 创建一个新的工作簿
Workbook workbook = new HSSFWorkbook();
// 创建工作表
Sheet sheet = workbook.createSheet("郭杜车管所");
ExcelUtil.setHeaderStyle(workbook, sheet,"");
Sheet sheet1 = workbook.createSheet("东所");
ExcelUtil.setHeaderStyle(workbook, sheet1,"");
//到这里标题样式和表头内容以及表头样式已经定义完成了 那么有小伙伴问了 标题输入在哪里
ExcelUtil.setHeaderStyle(workbook,sheet, " 你的标题想写什么都在这个里面 ");
//*****************************这里是我查询数据的代码逻辑
Set<Integer> outputRows = new HashSet<>();
//这个是你循环数据开始的行数码
int rowNum = 2;
int count = 0 ; //小编代码逻辑
for (int i = 0; i < list.size() - 1; i++) {
UserInfo current = list.get(i);
UserInfo next = list.get(i + 1);
Date currentDate = format.parse(current.getEndTime());
Date nextDate = format.parse(next.getEndTime());
long diff = nextDate.getTime() - currentDate.getTime();
diff = Math.abs(diff);
if (diff > 30 * 1000 && diff <= 60 * 1000) {
if (!outputRows.contains(i)) {
count++ ;
Row currentRow = sheet.createRow(rowNum++);
currentRow.setHeightInPoints(30);
currentRow.createCell(0).setCellValue(current.getPlaceName());
currentRow.createCell(1).setCellValue(current.getUserNO());
currentRow.createCell(2).setCellValue(current.getUserName());
currentRow.createCell(3).setCellValue(current.getEndTime());
currentRow.createCell(4).setCellValue(current.getEvaluate());
// 主要是这里 将查出的数据进行样式渲染
ExcelUtil.setRowStyle(workbook, sheet, currentRow);
}
if (!outputRows.contains(i + 1)) {
count++ ;
Row nextRow = sheet.createRow(rowNum++);
nextRow.setHeightInPoints(30);
nextRow.createCell(0).setCellValue(next.getPlaceName());
nextRow.createCell(1).setCellValue(next.getUserNO());
nextRow.createCell(2).setCellValue(next.getUserName());
nextRow.createCell(3).setCellValue(next.getEndTime());
nextRow.createCell(4).setCellValue(next.getEvaluate());
// 主要是这里 将查出的数据进行样式渲染
ExcelUtil.setRowStyle(workbook, sheet, nextRow);
}
}
}
//************ 这行代码可以直接删除,这个因为要计算一些人数,所以写在了下面
//ExcelUtil.setHeaderStyle(workbook,sheet,ny+",郭杜车管所评价总数:"+list.size()+"次,参评业务人员:"+list.stream().map(UserInfo::getUserNO).distinct().count()+"人,预警数据"+count+"条(间隔时间大于30秒小于等于60秒的评价)");
// 设置每列的宽度为平均宽度
ExcelUtil.setColumnWidth(sheet2);
//东所
Set<Integer> outputRows1 = new HashSet<>();
int rowNum1 = 2;
int count1 = 0;
for (int i = 0; i < list1.size() - 1; i++) {
UserInfo current = list1.get(i);
UserInfo next = list1.get(i + 1);
Date currentDate = format.parse(current.getEndTime());
Date nextDate = format.parse(next.getEndTime());
long diff = nextDate.getTime() - currentDate.getTime();
diff = Math.abs(diff);
if (diff <= 30 * 1000) {
if (!outputRows1.contains(i)) {
count1++;
Row currentRow = sheet1.createRow(rowNum1++);
currentRow.setHeightInPoints(30);
currentRow.createCell(0).setCellValue(current.getPlaceName());
currentRow.createCell(1).setCellValue(current.getUserNO());
currentRow.createCell(2).setCellValue(current.getUserName());
currentRow.createCell(3).setCellValue(current.getEndTime());
currentRow.createCell(4).setCellValue(current.getEvaluate());
ExcelUtil.setRowStyle(workbook, sheet1, currentRow);
}
if (!outputRows1.contains(i + 1)) {
count1++;
Row nextRow = sheet1.createRow(rowNum1++);
nextRow.setHeightInPoints(30);
nextRow.createCell(0).setCellValue(next.getPlaceName());
nextRow.createCell(1).setCellValue(next.getUserNO());
nextRow.createCell(2).setCellValue(next.getUserName());
nextRow.createCell(3).setCellValue(next.getEndTime());
nextRow.createCell(4).setCellValue(next.getEvaluate());
ExcelUtil.setRowStyle(workbook, sheet1, nextRow);
}
}
}
ExcelUtil.setHeaderStyle(workbook, sheet1,ny+",东所评价总数:"+list1.size()+"次,参评业务人员:"+list1.stream().map(UserInfo::getUserNO).distinct().count()+"人,疑似异常数据"+count1+"条(时间间隔小于等于30秒的评价)");
// 设置每列的宽度为平均宽度
ExcelUtil.setColumnWidth(sheet2);
FileOutputStream outputStream = new FileOutputStream("E:\\"+lastMonth+"\\"+"data_"+lastMonth+"_60"+".xlsx");
workbook.write(outputStream);
System.out.println("预警下载完成");
}
导出后样式