poi报表工具类的使用方法，报表导出，批量导出
本次使用的是ApachePOI技术，挺简单的就不多说了，做下记录。
·
本次使用的是ApachePOI技术，挺简单的就不多说了，做下记录
1、使用ExcelUtils工具类进行操作
工具类：
package com.youming.shuiku.system.utils.excel;
import cn.hutool.json.JSON;
import com.alibaba.fastjson.JSONObject;
import com.youming.shuiku.commons.exception.BusinessException;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang.StringUtils;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DataFormatter;
import org.apache.poi.ss.usermodel.Hyperlink;
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.usermodel.WorkbookFactory;
import java.io.InputStream;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
/**
* Excel工具类
*/
@Slf4j
public class ExcelUtils {
public static String DATETIME_FORMAT = "yyyy-MM-dd HH:mm:ss";
public static List<List<String>> getExcelContent(InputStream inputStream, int beginRow, ExcelFilesVaildate[] validates)throws Exception {
DataFormatter dataFormatter = new DataFormatter();
List<List<String>> fileContent = new ArrayList();
Workbook workbook = WorkbookFactory.create(inputStream);
Sheet sheet = workbook.getSheetAt(0);
int rows = sheet.getLastRowNum() + 1;
if (rows >= 1000 + beginRow) {
throw new BusinessException("excel文件的行数超过系统允许导入最大行数：" + 1000);
} else {
if (rows >= beginRow) {
List rowList = null;
Row row = null;
for (int i = beginRow - 1; i < rows; ++i) {
row = sheet.getRow(i);
rowList = new ArrayList();
fileContent.add(rowList);
if (row != null) {
int cells = row.getLastCellNum();
if (cells > 200) {
throw new BusinessException("文件列数超过200列，请检查文件！");
}
for (int j = 0; j < cells; ++j) {
Cell cell = row.getCell(j);
String cellValue = "";
Hyperlink hyperlink = cell.getHyperlink();
if(hyperlink != null){
HashMap<String,Object> map=new HashMap<>();
map.put("key",cell.getStringCellValue());
map.put("value",hyperlink.getAddress());
Object json = JSONObject.toJSON(map);
cellValue =json.toString();
}
if (cell != null && hyperlink == null) {
log.debug("Reading Excel File row:" + i + ", col:" + j + " cellType:" + cell.getCellType());
switch (cell.getCellType()) {
case 0:
if (org.apache.poi.ss.usermodel.DateUtil.isCellDateFormatted(cell)) {
cellValue = formatDateByFormat(cell.getDateCellValue(), DATETIME_FORMAT);
} else {
cellValue = dataFormatter.formatCellValue(cell);
}
break;
case 1:
cellValue = cell.getStringCellValue();
break;
case 2:
cellValue = String.valueOf(cell.getNumericCellValue());
break;
case 3:
cellValue = "";
break;
case 4:
cellValue = String.valueOf(cell.getBooleanCellValue());
break;
case 5:
cellValue = String.valueOf(cell.getErrorCellValue());
}
}
if (validates != null && validates.length > j) {
if (cellValue == null) {
throw new BusinessException("第" + (i + beginRow - 1) + "行,第" + (j + 1) + "列数据校验出错：" + validates[j].getErrorMsg());
}
Pattern p = Pattern.compile(validates[j].getPattern());
Matcher m = p.matcher(cellValue);
if (!m.matches()) {
throw new BusinessException("第" + (i + beginRow - 1) + "行,第" + (j + 1) + "列数据校验出错：" + validates[j].getErrorMsg());
}
}
rowList.add(cellValue);
}
}
}
}
return fileContent;
}
}
public static String formatDateByFormat(Date date, String format) {
String result = "";
if (date != null) {
try {
SimpleDateFormat sdf = new SimpleDateFormat(format);
result = sdf.format(date);
} catch (Exception var4) {
var4.printStackTrace();
}
}
return result;
}
public static String getExcelValue(List<String> list, int index) {
if (list == null || list.isEmpty() || list.size() < index + 1) {
return null;
}
return StringUtils.trim(list.get(index));
}
}
使用：
LambdaQueryWrapper<ArchiveDisplacementMeter> lambda = Wrappers.lambdaQuery();
lambda.orderByAsc(ArchiveDisplacementMeter::getNumber);
List<ArchiveDisplacementMeter> list = this.list(lambda);
try {
String[] showName = null;
String[] resourceFild = null;
if (list != null && list.size() > 0) {
for (ArchiveDisplacementMeter archiveDisplacementMeter: list) {
archiveDisplacementMeter.setMeasurePointExport("多点位移计"+archiveDisplacementMeter.getNumber());
double v = Double.valueOf(archiveDisplacementMeter.getElevation()) -
Double.valueOf(archiveDisplacementMeter.getHead());
archiveDisplacementMeter.setBeginEndElevation(String.valueOf(v));
archiveDisplacementMeter.setWorkState("正常");
}
}
showName = new String[]{"水工建筑物编号","测点编号","测点别名","考证信息日期","型式",
"桩号","轴距","起始高程","安装日期","测定日期","仪器出厂编号","工作状态","备注"};
resourceFild = new String[]{"getToponym","getMeasurePointExport","getMeasurePointName","getPreparerTime",
"getSensorType","getStakeMark","getAxisDistance","getBeginEndElevation","getPreparerTime","getPreparerTime",
"getFactoryNumber","getWorkState","getRemark"};
XSSFWorkbook workbook = ExportExcel.getWorkbookXlsx(list, showName, resourceFild, ArchiveDisplacementMeter.class,
null);
ByteArrayOutputStream os = new ByteArrayOutputStream();
workbook.write(os);
byte[] b = os.toByteArray();
MinioUploadDto minioUploadDto = minioService.upload("多点位移计安装埋设考证表.xlsx", b, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
return minioUploadDto.getUrl();
ExportExcel的工具类详情：
package com.youming.shuiku.system.utils.excel;
import org.apache.poi.hssf.usermodel.DVConstraint;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFDataValidation;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.DataFormat;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.InputStream;
import java.io.Serializable;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.time.LocalDateTime;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Date;
import java.util.List;
import java.util.Map;
//import org.apache.poi.hssf.usermodel.HSSFCellStyle;
@SuppressWarnings("all")
public class ExportExcel implements Serializable {
public static String getFileName() {
return com.youming.shuiku.system.utils.excel.DateUtil.toString(new Date(), "yyyyMMdd-HHmmss");
}
@SuppressWarnings("deprecation")
public static HSSFWorkbook getWorkbookXls(List<?> resultList, String[] showName, String[] resourceField,
Class<?> resultObj, Map<String, Map<String, String>> formatMap) throws SecurityException,
NoSuchMethodException, IllegalArgumentException, IllegalAccessException, InvocationTargetException {
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = workbook.createSheet("sheet1");
sheet.setDefaultColumnWidth((short) 20);
HSSFCellStyle centerStyle = workbook.createCellStyle();// 设置为水平居中
centerStyle.setAlignment(HorizontalAlignment.CENTER);
centerStyle.setVerticalAlignment(VerticalAlignment.CENTER);
centerStyle.setBorderBottom(BorderStyle.THIN); // 下边框
centerStyle.setBorderLeft(BorderStyle.THIN);// 左边框
centerStyle.setBorderTop(BorderStyle.THIN);// 上边框
centerStyle.setBorderRight(BorderStyle.THIN);// 右边框
Font font = workbook.createFont();
font.setBold(true); // 粗体
centerStyle.setFont(font);
HSSFCellStyle contentStyle = workbook.createCellStyle();// 设置为水平居中
contentStyle.setAlignment(HorizontalAlignment.CENTER);
contentStyle.setVerticalAlignment(VerticalAlignment.CENTER);
contentStyle.setBorderBottom(BorderStyle.THIN); // 下边框
contentStyle.setBorderLeft(BorderStyle.THIN);// 左边框
contentStyle.setBorderTop(BorderStyle.THIN);// 上边框
contentStyle.setBorderRight(BorderStyle.THIN);// 右边框
HSSFRow row;
HSSFCell cell;
createTitleXls(showName, sheet, centerStyle);
// 下面是输出各行的数据 下面是输出各行的数据 下面是输出各行的
for (int i = 0; i < resultList.size(); i++) {
Object result = resultList.get(i);
row = sheet.createRow(i + 1);
// 创建第 i+1 行
for (int j = 0; j < resourceField.length; j++) {
cell = row.createCell(j);// 创建第 j 列
Method method;
method = resultObj.getMethod(resourceField[j]);
// 这里用到了反射机制，通 这里用到了反射机制， 这里用到了反射机制 过方法名来取得对应方法返回的结果对象
Object obj = method.invoke(result);
if (obj != null) {
if (formatMap != null && formatMap.containsKey(resourceField)) {
cell.setCellValue(formatMap.get(resourceField).get(obj.toString()));
cell.setCellStyle(contentStyle);
}
else {
String type = method.getGenericReturnType().toString();
if ("class java.util.Date".equals(type)) {
cell.setCellValue(com.youming.shuiku.system.utils.excel.DateUtil.toString((Date) obj,
com.youming.shuiku.system.utils.excel.DateUtil.DEFAULT_DATETIME_FORMAT_SEC));
cell.setCellStyle(contentStyle);
}
else if ("class java.time.LocalDateTime".equals(type)) {
cell.setCellValue(com.youming.shuiku.system.utils.excel.DateUtils.formatLocalDateTime(
(LocalDateTime) obj,
com.youming.shuiku.system.utils.excel.DateUtils.DATETIME_FORMAT));
cell.setCellStyle(contentStyle);
}
else {
cell.setCellValue(obj.toString());
cell.setCellStyle(contentStyle);
}
}
}
else {
cell.setCellStyle(contentStyle);
}
}
}
return workbook;
}
@SuppressWarnings("deprecation")
public static XSSFWorkbook getWorkbookXlsx(List<?> resultList, String[] showName, String[] resourceField,
Class<?> resultObj, Map<String, Map<String, String>> formatMap) throws SecurityException,
NoSuchMethodException, IllegalArgumentException, IllegalAccessException, InvocationTargetException {
XSSFWorkbook workbook = new XSSFWorkbook();
XSSFSheet sheet = workbook.createSheet("sheet1");
sheet.setDefaultColumnWidth((short) 20);// 设置宽度
XSSFCellStyle centerStyle = workbook.createCellStyle();// 设置为水平居中
centerStyle.setAlignment(HorizontalAlignment.CENTER);
centerStyle.setVerticalAlignment(VerticalAlignment.CENTER);
centerStyle.setBorderBottom(BorderStyle.THIN); // 下边框
centerStyle.setBorderLeft(BorderStyle.THIN);// 左边框
centerStyle.setBorderTop(BorderStyle.THIN);// 上边框
centerStyle.setBorderRight(BorderStyle.THIN);// 右边框
Font font = workbook.createFont();
font.setBold(true); // 粗体
centerStyle.setFont(font);
XSSFCellStyle contentStyle = workbook.createCellStyle();// 设置为水平居中
contentStyle.setAlignment(HorizontalAlignment.CENTER);
contentStyle.setVerticalAlignment(VerticalAlignment.CENTER);
contentStyle.setBorderBottom(BorderStyle.THIN); // 下边框
contentStyle.setBorderLeft(BorderStyle.THIN);// 左边框
contentStyle.setBorderTop(BorderStyle.THIN);// 上边框
contentStyle.setBorderRight(BorderStyle.THIN);// 右边框
XSSFRow row;
XSSFCell cell;
createTitleXlsx(showName, sheet, centerStyle);
// 下面是输出各行的数据 下面是输出各行的数据 下面是输出各行的
for (int i = 0; i < resultList.size(); i++) {
Object result = resultList.get(i);
row = sheet.createRow(i + 1);
// 创建第 i+1 行
for (int j = 0; j < resourceField.length; j++) {
cell = row.createCell(j);// 创建第 j 列
Method method;
method = resultObj.getMethod(resourceField[j]);
// 这里用到了反射机制，通 这里用到了反射机制， 这里用到了反射机制 过方法名来取得对应方法返回的结果对象
Object obj = method.invoke(result);
if (obj != null) {
if (formatMap != null && formatMap.containsKey(resourceField)) {
cell.setCellValue(formatMap.get(resourceField).get(obj.toString()));
cell.setCellStyle(contentStyle);
}
else {
String type = method.getGenericReturnType().toString();
if ("class java.util.Date".equals(type)) {
cell.setCellValue(com.youming.shuiku.system.utils.excel.DateUtil.toString((Date) obj,
com.youming.shuiku.system.utils.excel.DateUtil.DEFAULT_DATETIME_FORMAT_SEC));
cell.setCellStyle(contentStyle);
}
else if ("class java.time.LocalDateTime".equals(type)) {
cell.setCellValue(com.youming.shuiku.system.utils.excel.DateUtils.formatLocalDateTime(
(LocalDateTime) obj,
com.youming.shuiku.system.utils.excel.DateUtils.DATETIME_FORMAT));
cell.setCellStyle(contentStyle);
}
else {
cell.setCellValue(obj.toString());
cell.setCellStyle(contentStyle);
}
}
}
else {
cell.setCellStyle(contentStyle);
}
}
}
return workbook;
}
/*
* @description: 当数据量过大 分为多个sheet表
* @author: wangxihao
* @date:2023/2/13 10:50
* @param: resultList
* @param: showName
* @param: resourceField
* @param: resultObj
* @param: formatMap
* @return: org.apache.poi.xssf.usermodel.XSSFWorkbook
**/
public static XSSFWorkbook getWorkbookXlsxMore(List<?> resultList, String[] showName, String[] resourceField,
Class<?> resultObj, Map<String, Map<String, String>> formatMap) throws SecurityException,
NoSuchMethodException, IllegalArgumentException, IllegalAccessException, InvocationTargetException {
XSSFWorkbook workbook = new XSSFWorkbook();
//万条为一sheet
if(resultList != null && resultList.size() >10000){
int num = 0;
if(resultList.size() % 10000 == 0){
num = resultList.size() % 10000;
}else {
num = resultList.size() / 10000 +1;
}
for (int k = 0; k < num; k++) {
List<?> objects = null;
if(k == num-1){
objects = resultList.subList(k, resultList.size());
}else {
objects = resultList.subList(k, k + 10000);
}
XSSFSheet sheet = workbook.createSheet("数据页"+k);
sheet.setDefaultColumnWidth((short) 20);// 设置宽度
XSSFCellStyle centerStyle = workbook.createCellStyle();// 设置为水平居中
centerStyle.setAlignment(HorizontalAlignment.CENTER);
centerStyle.setVerticalAlignment(VerticalAlignment.CENTER);
centerStyle.setBorderBottom(BorderStyle.THIN); // 下边框
centerStyle.setBorderLeft(BorderStyle.THIN);// 左边框
centerStyle.setBorderTop(BorderStyle.THIN);// 上边框
centerStyle.setBorderRight(BorderStyle.THIN);// 右边框
Font font = workbook.createFont();
font.setBold(true); // 粗体
centerStyle.setFont(font);
XSSFCellStyle contentStyle = workbook.createCellStyle();// 设置为水平居中
contentStyle.setAlignment(HorizontalAlignment.CENTER);
contentStyle.setVerticalAlignment(VerticalAlignment.CENTER);
contentStyle.setBorderBottom(BorderStyle.THIN); // 下边框
contentStyle.setBorderLeft(BorderStyle.THIN);// 左边框
contentStyle.setBorderTop(BorderStyle.THIN);// 上边框
contentStyle.setBorderRight(BorderStyle.THIN);// 右边框
XSSFRow row;
XSSFCell cell;
createTitleXlsx(showName, sheet, centerStyle);
// 下面是输出各行的数据 下面是输出各行的数据 下面是输出各行的
for (int i = 0; i < objects.size(); i++) {
Object result = objects.get(i);
row = sheet.createRow(i + 1);
// 创建第 i+1 行
for (int j = 0; j < resourceField.length; j++) {
cell = row.createCell(j);// 创建第 j 列
Method method;
method = resultObj.getMethod(resourceField[j]);
// 这里用到了反射机制，通 这里用到了反射机制， 这里用到了反射机制 过方法名来取得对应方法返回的结果对象
Object obj = method.invoke(result);
if (obj != null) {
if (formatMap != null && formatMap.containsKey(resourceField)) {
cell.setCellValue(formatMap.get(resourceField).get(obj.toString()));
cell.setCellStyle(contentStyle);
}
else {
String type = method.getGenericReturnType().toString();
if ("class java.util.Date".equals(type)) {
cell.setCellValue(com.youming.shuiku.system.utils.excel.DateUtil.toString((Date) obj,
com.youming.shuiku.system.utils.excel.DateUtil.DEFAULT_DATETIME_FORMAT_SEC));
cell.setCellStyle(contentStyle);
}
else if ("class java.time.LocalDateTime".equals(type)) {
cell.setCellValue(com.youming.shuiku.system.utils.excel.DateUtils.formatLocalDateTime(
(LocalDateTime) obj,
com.youming.shuiku.system.utils.excel.DateUtils.DATETIME_FORMAT));
cell.setCellStyle(contentStyle);
}
else {
cell.setCellValue(obj.toString());
cell.setCellStyle(contentStyle);
}
}
}
else {
cell.setCellStyle(contentStyle);
}
}
}
}
}
return workbook;
}
@SuppressWarnings("deprecation")
public static XSSFWorkbook getWorkbookXlsx(String[] showName, Map<String, Map<String, String>> formatMap)
throws SecurityException, NoSuchMethodException, IllegalArgumentException, IllegalAccessException,
InvocationTargetException {
XSSFWorkbook workbook = new XSSFWorkbook();
XSSFSheet sheet = workbook.createSheet("sheet1");
sheet.setDefaultColumnWidth((short) 20);
XSSFCellStyle centerStyle = workbook.createCellStyle();// 设置为水平居中
centerStyle.setAlignment(HorizontalAlignment.CENTER);
centerStyle.setVerticalAlignment(VerticalAlignment.CENTER);
centerStyle.setBorderBottom(BorderStyle.THIN); // 下边框
centerStyle.setBorderLeft(BorderStyle.THIN);// 左边框
centerStyle.setBorderTop(BorderStyle.THIN);// 上边框
centerStyle.setBorderRight(BorderStyle.THIN);// 右边框
Font font = workbook.createFont();
font.setBold(true); // 粗体
centerStyle.setFont(font);
XSSFCellStyle contentStyle = workbook.createCellStyle();// 设置为水平居中
contentStyle.setAlignment(HorizontalAlignment.CENTER);
contentStyle.setVerticalAlignment(VerticalAlignment.CENTER);
contentStyle.setBorderBottom(BorderStyle.THIN); // 下边框
contentStyle.setBorderLeft(BorderStyle.THIN);// 左边框
contentStyle.setBorderTop(BorderStyle.THIN);// 上边框
contentStyle.setBorderRight(BorderStyle.THIN);// 右边框
XSSFRow row;
XSSFCell cell;
createTitleXlsx(showName, sheet, centerStyle);
return workbook;
}
@SuppressWarnings("deprecation")
public static XSSFWorkbook getWorkbookXlsxContract(String[] showName, Map<String, Map<String, String>> formatMap)
throws SecurityException, NoSuchMethodException, IllegalArgumentException, IllegalAccessException,
InvocationTargetException {
XSSFWorkbook workbook = new XSSFWorkbook();
XSSFSheet sheet = workbook.createSheet("sheet1");
CellStyle textStyle = workbook.createCellStyle();
DataFormat format = workbook.createDataFormat();
textStyle.setDataFormat(format.getFormat("@"));
for (int i = 0; i < showName.length; i++) {
sheet.setDefaultColumnStyle(0, textStyle);
}
sheet.setDefaultColumnWidth((short) 20);
XSSFCellStyle centerStyle = workbook.createCellStyle();// 设置为水平居中
centerStyle.setAlignment(HorizontalAlignment.CENTER);
centerStyle.setVerticalAlignment(VerticalAlignment.CENTER);
centerStyle.setBorderBottom(BorderStyle.THIN); // 下边框
centerStyle.setBorderLeft(BorderStyle.THIN);// 左边框
centerStyle.setBorderTop(BorderStyle.THIN);// 上边框
centerStyle.setBorderRight(BorderStyle.THIN);// 右边框
Font font = workbook.createFont();
font.setBold(true); // 粗体
centerStyle.setFont(font);
XSSFCellStyle contentStyle = workbook.createCellStyle();// 设置为水平居中
contentStyle.setAlignment(HorizontalAlignment.CENTER);
contentStyle.setVerticalAlignment(VerticalAlignment.CENTER);
contentStyle.setBorderBottom(BorderStyle.THIN); // 下边框
contentStyle.setBorderLeft(BorderStyle.THIN);// 左边框
contentStyle.setBorderTop(BorderStyle.THIN);// 上边框
contentStyle.setBorderRight(BorderStyle.THIN);// 右边框
XSSFRow row;
XSSFCell cell;
createTitleXlsx(showName, sheet, centerStyle);
return workbook;
}
/**
* 设置某些列的值只能输入预制的数据,显示下拉框.
* @param sheet 要设置的sheet.
* @param textlist 下拉框显示的内容
* @param firstRow 开始行
* @param endRow 结束行
* @param firstCol 开始列
* @param endCol 结束列
* @return 设置好的sheet.
*/
public static XSSFSheet setXSSFValidation(XSSFSheet sheet, String[] textlist, int firstRow, int endRow,
int firstCol, int endCol) {
// 加载下拉列表内容
DVConstraint constraint = DVConstraint.createExplicitListConstraint(textlist);
// 设置数据有效性加载在哪个单元格上,四个参数分别是：起始行、终止行、起始列、终止列
CellRangeAddressList regions = new CellRangeAddressList(firstRow, endRow, firstCol, endCol);
// 数据有效性对象
HSSFDataValidation data_validation_list = new HSSFDataValidation(regions, constraint);
sheet.addValidationData(data_validation_list);
return sheet;
}
private static void createTitleXls(String[] showName, HSSFSheet sheet, HSSFCellStyle cellStyle) {
HSSFRow row = sheet.createRow(0); // 创建第 1 行，也就是输出表头 创建第
HSSFCell cell;
for (int i = 0; i < showName.length; i++) {
cell = row.createCell(i);
// 创建第 i 列 创建第
cell.setCellValue(new HSSFRichTextString(showName[i]));
cell.setCellStyle(cellStyle);
}
}
private static void createTitleXlsx(String[] showName, XSSFSheet sheet, XSSFCellStyle cellStyle) {
XSSFRow row = sheet.createRow(0); // 创建第 1 行，也就是输出表头 创建第
XSSFCell cell;
for (int i = 0; i < showName.length; i++) {
cell = row.createCell(i);
// 创建第 i 列 创建第
cell.setCellValue(new XSSFRichTextString(showName[i]));
cell.setCellStyle(cellStyle);
}
}
private static void createTitle2(String[] showName, HSSFSheet sheet, HSSFCellStyle centerStyle,
HSSFCellStyle style) {
HSSFRow row = sheet.createRow(3); // 创建第 1 行，也就是输出表头 创建第
HSSFCell cell;
for (int i = 0; i < showName.length; i++) {
cell = row.createCell(i);
// 创建第 i 列 创建第
cell.setCellValue(new HSSFRichTextString(showName[i]));
cell.setCellStyle(centerStyle); // 样式，居中
cell.setCellStyle(style); // 填充亮橘色
}
}
/**
* @param @param resultList
* @param @param showName
* @param @return 设定文件
* @return HSSFWorkbook 返回类型
* @throws
* @Title: createWorkbook
* @Description: 创建Workbook
*/
@SuppressWarnings("deprecation")
public static HSSFWorkbook createWorkbook(List<List<Cell>> resultList, String[] showName) {
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = workbook.createSheet("sheet1");
HSSFCellStyle centerStyle = workbook.createCellStyle();// 设置为水平居中
// centerStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
// centerStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
centerStyle.setAlignment(HorizontalAlignment.CENTER);
centerStyle.setVerticalAlignment(VerticalAlignment.CENTER);
createTitleXls(showName, sheet, centerStyle);
HSSFRow row = null;
HSSFCell cell = null;
if (resultList.size() > 0) {
int[][] arraSort = new int[resultList.get(0).size()][resultList.size()];
for (int i = 0; i < resultList.size(); i++) {
row = sheet.createRow(i + 1);
// sheet.setColumnWidth(i + 1, 15);
List<Cell> cellList = resultList.get(i);
for (int j = 0; j < cellList.size(); j++) {
cell = row.createCell(j);// 创建第 j 列
cell.setCellValue(cellList.get(j).getValue());
int b = cell.getStringCellValue().getBytes().length;
arraSort[j][i] = b;
if (cellList.get(j).getStyle() != null) {
cell.setCellStyle(cellList.get(j).getStyle());
}
}
}
// 列的最大列宽值（不包括标题）
int widthInfo[] = TwoMaxInfo(arraSort);
// 与标题在比较列宽
for (int i = 0; i < showName.length; i++) {
// sheet.autoSizeColumn(i);
// 算出列（包括标题的最大列宽）
int maxWidthInfo = showName[i].getBytes().length > widthInfo[i] ? showName[i].getBytes().length
: widthInfo[i];
sheet.setColumnWidth(i, maxWidthInfo > 255 ? 255 * 256 : maxWidthInfo * 256);
}
}
return workbook;
}
public static int[] TwoMaxInfo(int[][] arraSort) {
int[] arraySortInfo = null;
arraySortInfo = new int[arraSort.length];
int count = 0;
for (int[] is : arraSort) {
int[] arraInfo = is;
Arrays.sort(arraInfo);
arraySortInfo[count] = arraInfo[arraInfo.length - 1];
count++;
}
return arraySortInfo;
}
/**
* @param @param resultList
* @param @param showName
* @param @return 设定文件
* @return HSSFWorkbook 返回类型
* @throws
* @Title: createWorkbookAll
* @Description: 创建Workbook
*/
@SuppressWarnings("deprecation")
public static HSSFWorkbook createWorkbookAll(Map<String, List<List<Cell>>> vMap, String[] showName) {
HSSFWorkbook workbook = new HSSFWorkbook();
for (Map.Entry<String, List<List<Cell>>> entry : vMap.entrySet()) {
HSSFSheet sheet = workbook.createSheet(entry.getKey());
sheet.setDefaultColumnWidth((short) 15);
HSSFCellStyle centerStyle = workbook.createCellStyle();// 设置为水平居中
// centerStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
// centerStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
centerStyle.setAlignment(HorizontalAlignment.CENTER);
centerStyle.setVerticalAlignment(VerticalAlignment.CENTER);
createTitleXls(showName, sheet, centerStyle);
HSSFRow row;
HSSFCell cell;
for (int i = 0; i < entry.getValue().size(); i++) {
row = sheet.createRow(i + 1);
List<Cell> cellList = entry.getValue().get(i);
for (int j = 0; j < cellList.size(); j++) {
cell = row.createCell(j);// 创建第 j 列
cell.setCellValue(cellList.get(j).getValue());
if (cellList.get(j).getStyle() != null) {
cell.setCellStyle(cellList.get(j).getStyle());
}
}
}
for (int i = 0; i < showName.length; i++) {
sheet.autoSizeColumn(i);
}
}
return workbook;
}
public static InputStream workbook2InputStreamXls(HSSFWorkbook workbook, String fileName) throws Exception {
ByteArrayOutputStream baos = new ByteArrayOutputStream();
workbook.write(baos);
baos.flush();
byte[] aa = baos.toByteArray();
InputStream excelStream = new ByteArrayInputStream(aa, 0, aa.length);
baos.close();
return excelStream;
}
public static InputStream workbook2InputStreamXlsx(XSSFWorkbook workbook, String fileName) throws Exception {
ByteArrayOutputStream baos = new ByteArrayOutputStream();
workbook.write(baos);
baos.flush();
byte[] aa = baos.toByteArray();
InputStream excelStream = new ByteArrayInputStream(aa, 0, aa.length);
baos.close();
return excelStream;
}
/**
* @param @param resultList 导出的数据集合
* @param @param showName 导出的字段名称
* @param @param headerName Excel表头参数
* @param @param resourceField 实例类对象get方法名，通过反射获取值
* @param @param resultObj 实例类
* @param @param formatMap
* @param @return 返回workbook
* @param @throws SecurityException
* @param @throws NoSuchMethodException
* @param @throws IllegalArgumentException
* @param @throws IllegalAccessException
* @param @throws InvocationTargetException 设定文件
* @return HSSFWorkbook 返回类型
* @throws
* @Title: createWorkbookVariety
* @Description: 导出Excel报表
*/
public static HSSFWorkbook createWorkbookVariety(List<?> resultList, String[] showName,
ArrayList<String> headerName, String[] resourceField, Class<?> resultObj,
Map<String, Map<String, String>> formatMap) throws SecurityException, NoSuchMethodException,
IllegalArgumentException, IllegalAccessException, InvocationTargetException {
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = workbook.createSheet("sheet1");
sheet.setDefaultColumnWidth((short) 15);
HSSFCellStyle centerStyle = workbook.createCellStyle();// 设置为水平居中
// centerStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
// centerStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
centerStyle.setAlignment(HorizontalAlignment.CENTER);
centerStyle.setVerticalAlignment(VerticalAlignment.CENTER);
/**
* 设置表头的样式
*/
HSSFCellStyle titylStyle = workbook.createCellStyle();
createTitleVariety(showName, headerName, sheet, titylStyle);
HSSFRow row;
HSSFCell cell;
for (int i = 0; i < resultList.size(); i++) {
Object result = resultList.get(i);
if (headerName != null && headerName.size() > 0) {
row = sheet.createRow(i + 1 + headerName.size());
}
else {
row = sheet.createRow(i + 1);
}
// 创建第 i+1 行
for (int j = 0; j <= resourceField.length; j++) {
cell = row.createCell(j);// 创建第 j 列
cell.setCellStyle(centerStyle);
if (j == 0) {
// 为Excel表的第一列添加编号，表头为：序号；eg:1,2,3,4……
cell.setCellValue(i + 1);
}
else {
Method method;
method = resultObj.getMethod(resourceField[j - 1]);
// 这里用到了反射机制，通 这里用到了反射机制， 这里用到了反射机制 过方法名来取得对应方法返回的结果对象
Object obj = method.invoke(result);
if (obj != null) {
if (formatMap != null && formatMap.containsKey(resourceField)) {
cell.setCellValue(formatMap.get(resourceField).get(obj.toString()));
}
else {
String type = method.getGenericReturnType().toString();
if ("class java.util.Date".equals(type)) {
cell.setCellValue(com.youming.shuiku.system.utils.excel.DateUtil.toString((Date) obj,
com.youming.shuiku.system.utils.excel.DateUtil.DEFAULT_DATETIME_FORMAT_SEC));
}
else {
cell.setCellValue(obj.toString());
}
}
}
}
}
}
return workbook;
}
/**
* @param @param showName
* @param @param headerName
* @param @param sheet 设定文件
* @return void 返回类型
* @throws
* @Title: createTitleVariety
* @Description: 多行表头
*/
private static void createTitleVariety(String[] showName, ArrayList<String> headerName, HSSFSheet sheet,
HSSFCellStyle titylStyle) {
HSSFRow row;
HSSFCell cell;
// titylStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
// titylStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
titylStyle.setAlignment(HorizontalAlignment.CENTER);
titylStyle.setVerticalAlignment(VerticalAlignment.CENTER);
if (headerName != null && headerName.size() > 0) {
for (int i = 0; i < headerName.size(); i++) {
row = sheet.createRow((short) i);
if (i == 0) {
cell = row.createCell(i);
sheet.addMergedRegion(new CellRangeAddress(i, i, (short) 0, (short) showName.length));
cell.setCellStyle(titylStyle);
if (headerName.get(i) != null) {
cell.setCellValue(new HSSFRichTextString(headerName.get(i).toString()));
}
else {
cell.setCellValue(new HSSFRichTextString(""));
}
}
else {
cell = row.createCell(i - 1);
sheet.addMergedRegion(new CellRangeAddress(i, i, (short) 0, (short) showName.length));
if (headerName.get(i) != null) {
cell.setCellValue(new HSSFRichTextString(headerName.get(i).toString()));
}
else {
cell.setCellValue(new HSSFRichTextString(""));
}
}
}
}
// 设置Excel字段
if (headerName != null && headerName.size() > 0) {
row = sheet.createRow((short) headerName.size());
}
else {
row = sheet.createRow(0);
}
for (int n = 0; n <= showName.length; n++) {
if (n == 0) {
cell = row.createCell(n);
cell.setCellStyle(titylStyle);
cell.setCellValue(new HSSFRichTextString("序号"));
}
else {
cell = row.createCell(n);
cell.setCellStyle(titylStyle);
cell.setCellValue(new HSSFRichTextString(showName[n - 1]));
}
}
}
public static HSSFWorkbook createWorkbookVarietyParam(ArrayList<ArrayList<String>> resultList, String[] showName,
ArrayList<String> headerName) {
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = workbook.createSheet("sheet1");
sheet.setDefaultColumnWidth((short) 15);
HSSFCellStyle centerStyle = workbook.createCellStyle();// 设置为水平居中
// centerStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
// centerStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
centerStyle.setAlignment(HorizontalAlignment.CENTER);
centerStyle.setVerticalAlignment(VerticalAlignment.CENTER);
/**
* 设置表头的样式
*/
HSSFCellStyle titylStyle = workbook.createCellStyle();
createTitleVariety(showName, headerName, sheet, titylStyle);
HSSFRow row;
HSSFCell cell;
if (resultList != null && resultList.size() > 0) {
for (int i = 0; i < resultList.size(); i++) {
ArrayList<String> rowResultList = resultList.get(i);
if (headerName != null && headerName.size() > 0) {
row = sheet.createRow((short) (i + 1 + headerName.size()));
}
else {
row = sheet.createRow((short) (i + 1));
}
if (rowResultList != null && rowResultList.size() > 0) {
for (int n = 0; n <= rowResultList.size(); n++) {
cell = row.createCell(n);// 创建第 j 列
cell.setCellStyle(centerStyle);
if (n == 0) {
// 为Excel表的第一列添加编号，表头为：序号；eg:1,2,3,4……
cell.setCellValue(i + 1);
}
else if (rowResultList.get(n - 1) != null) {
cell.setCellValue(rowResultList.get(n - 1).toString());
}
else {
cell.setCellValue("");
}
}
}
}
}
return workbook;
}
/**
* @param @param resultList
* @param @param headList
* @param @param sumList
* @param @param showName
* @param @param resourceField
* @param @param resultObj
* @param @param formatMap
* @param @return
* @param @throws SecurityException
* @param @throws NoSuchMethodException
* @param @throws IllegalArgumentException
* @param @throws IllegalAccessException
* @param @throws InvocationTargetException 设定文件
* @return HSSFWorkbook 返回类型
* @throws
* @Title: getWorkbook2
* @Description: TODO(导出车辆运行过程分析Excel)
*/
@SuppressWarnings("deprecation")
public static HSSFWorkbook getWorkbook2(List<?> resultList, List<?> headList, List<?> sumList, String[] showName,
String[] resourceField, Class<?> resultObj, Map<String, Map<String, String>> formatMap)
throws SecurityException, NoSuchMethodException, IllegalArgumentException, IllegalAccessException,
InvocationTargetException {
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = workbook.createSheet("sheet1");
sheet.setDefaultColumnWidth((short) 20);
HSSFCellStyle centerStyle = workbook.createCellStyle();// 设置为水平居中
// centerStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
// centerStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
centerStyle.setAlignment(HorizontalAlignment.CENTER);
centerStyle.setVerticalAlignment(VerticalAlignment.CENTER);
centerStyle.setBorderBottom(BorderStyle.THIN); // 下边框
centerStyle.setBorderLeft(BorderStyle.THIN);// 左边框
centerStyle.setBorderTop(BorderStyle.THIN);// 上边框
centerStyle.setBorderRight(BorderStyle.THIN);// 右边框
HSSFDataFormat format = workbook.createDataFormat();
// 这样才能真正的控制单元格格式，@就是指文本型
centerStyle.setDataFormat(format.getFormat("@"));
HSSFCellStyle style = workbook.createCellStyle();
// style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
// style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
style.setAlignment(HorizontalAlignment.CENTER);
style.setVerticalAlignment(VerticalAlignment.CENTER);
style.setBorderBottom(BorderStyle.THIN); // 下边框
style.setBorderLeft(BorderStyle.THIN);// 左边框
style.setBorderTop(BorderStyle.THIN);// 上边框
style.setBorderRight(BorderStyle.THIN);// 右边框
style.setFillPattern(FillPatternType.SOLID_FOREGROUND);// 填充单元格
style.setFillForegroundColor(HSSFColor.HSSFColorPredefined.LIGHT_ORANGE.getIndex());// 填亮橘色
HSSFCellStyle greenStyle = workbook.createCellStyle();
// greenStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
// greenStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
greenStyle.setAlignment(HorizontalAlignment.CENTER);
greenStyle.setVerticalAlignment(VerticalAlignment.CENTER);
greenStyle.setBorderBottom(BorderStyle.THIN); // 下边框
greenStyle.setBorderLeft(BorderStyle.THIN);// 左边框
greenStyle.setBorderTop(BorderStyle.THIN);// 上边框
greenStyle.setBorderRight(BorderStyle.THIN);// 右边框
greenStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);// 填充单元格
// greenStyle.setFillForegroundColor(HSSFColor.BRIGHT_GREEN.index);//填亮绿色
greenStyle.setFillForegroundColor(HSSFColor.HSSFColorPredefined.SEA_GREEN.getIndex());// 填深绿色
Font greenfont = workbook.createFont();
greenfont.setBold(true); // 粗体
greenStyle.setFont(greenfont);
HSSFCellStyle overGreenStyle = workbook.createCellStyle();
// overGreenStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
// overGreenStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
overGreenStyle.setAlignment(HorizontalAlignment.CENTER);
overGreenStyle.setVerticalAlignment(VerticalAlignment.CENTER);
overGreenStyle.setBorderBottom(BorderStyle.THIN); // 下边框
overGreenStyle.setBorderLeft(BorderStyle.THIN);// 左边框
overGreenStyle.setBorderTop(BorderStyle.THIN);// 上边框
overGreenStyle.setBorderRight(BorderStyle.THIN);// 右边框
overGreenStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);// 填充单元格
overGreenStyle.setFillForegroundColor(HSSFColor.HSSFColorPredefined.SEA_GREEN.getIndex());// 填深绿色
HSSFCellStyle fontStyle = workbook.createCellStyle();// 字体样式
fontStyle.setAlignment(HorizontalAlignment.CENTER);
fontStyle.setVerticalAlignment(VerticalAlignment.CENTER);
fontStyle.setBorderBottom(BorderStyle.THIN); // 下边框
fontStyle.setBorderLeft(BorderStyle.THIN);// 左边框
fontStyle.setBorderTop(BorderStyle.THIN);// 上边框
fontStyle.setBorderRight(BorderStyle.THIN);// 右边框
fontStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);// 填充单元格
fontStyle.setFillForegroundColor(HSSFColor.HSSFColorPredefined.SEA_GREEN.getIndex());// 填深绿色
Font font = workbook.createFont();
font.setColor(HSSFColor.HSSFColorPredefined.WHITE.getIndex()); // 白字
fontStyle.setFont(font);
HSSFRow row;
HSSFCell cell;
// createTitle2(showName, sheet, centerStyle, style);
for (int j = 0; j < 3; j++) {
row = sheet.createRow(j);
for (int i = 0; i < showName.length; i++) {
cell = row.createCell(i);
if (j == 0) {
if (i == 0) {
cell.setCellValue(new HSSFRichTextString("查询时间"));
cell.setCellStyle(greenStyle);
}
else if (i == 1) {
// 参数1：行号 参数2：起始列号 参数3：行号 参数4：终止列号
CellRangeAddress region1 = new CellRangeAddress(j, j, (short) i, (short) (i + 3));
sheet.addMergedRegion(region1);
cell.setCellValue(new HSSFRichTextString(headList.get(0).toString()));
cell.setCellStyle(fontStyle);
}
}
else if (j == 1) {
if (i == 0) {
// 参数1：行号 参数2：起始列号 参数3：行号 参数4：终止列号
CellRangeAddress region1 = new CellRangeAddress(j, (j + 1), (short) 0, (short) 0);
sheet.addMergedRegion(region1);
cell.setCellValue(new HSSFRichTextString("车辆信息"));
cell.setCellStyle(greenStyle);
}
else if (i == 1) {
cell.setCellValue(new HSSFRichTextString("车牌号"));
}
else if (i == 2) {
cell.setCellValue(new HSSFRichTextString("所属公司"));
}
else if (i == 3) {
cell.setCellValue(new HSSFRichTextString("车辆类型"));
}
else if (i == 4) {
cell.setCellValue(new HSSFRichTextString("入网时间"));
}
else if (i == 5) {
cell.setCellValue(new HSSFRichTextString("车身颜色"));
}
else if (i == 6) {
cell.setCellValue(new HSSFRichTextString("型号"));
}
else if (i == 7) {
cell.setCellValue(new HSSFRichTextString("司机"));
}
else if (i == 8) {
cell.setCellValue(new HSSFRichTextString("手机号"));
}
if (i > 0 && i < 9) {
cell.setCellStyle(overGreenStyle);
}
}
else if (j == 2) {
if (i == 1) {
cell.setCellValue(new HSSFRichTextString(headList.get(1).toString()));
}
else if (i == 2) {
cell.setCellValue(new HSSFRichTextString(headList.get(2).toString()));
}
else if (i == 3) {
cell.setCellValue(new HSSFRichTextString(headList.get(3).toString()));
}
else if (i == 4) {
cell.setCellValue(new HSSFRichTextString(headList.get(4).toString()));
}
else if (i == 5) {
cell.setCellValue(new HSSFRichTextString(headList.get(5).toString()));
}
else if (i == 6) {
cell.setCellValue(new HSSFRichTextString(headList.get(6).toString()));
}
else if (i == 7) {
cell.setCellValue(new HSSFRichTextString(headList.get(7).toString()));
}
else if (i == 8) {
cell.setCellValue(new HSSFRichTextString(headList.get(8).toString()));
}
if (i > 0 && i < 9) {
cell.setCellStyle(fontStyle);
}
}
}
}
for (int k = 0; k < 3; k++) {
row = sheet.createRow(k + 3);
for (int i = 0; i < showName.length; i++) {
cell = row.createCell(i);
if (k == 0) {
if (i == 0) {
// 参数1：行号 参数2：起始列号 参数3：行号 参数4：终止列号
CellRangeAddress region1 = new CellRangeAddress(k + 3, k + 4, (short) (i), (short) (i + 1));
sheet.addMergedRegion(region1);
cell.setCellValue(new HSSFRichTextString("时间区间"));
}
else if (i == 2) {
// 参数1：行号 参数2：起始列号 参数3：行号 参数4：终止列号
CellRangeAddress region1 = new CellRangeAddress(k + 3, k + 4, (short) (i), (short) (i + 1));
sheet.addMergedRegion(region1);
cell.setCellValue(new HSSFRichTextString("时长"));
}
else if (i == 4) {
// 参数1：行号 参数2：起始列号 参数3：行号 参数4：终止列号
CellRangeAddress region1 = new CellRangeAddress(k + 3, k + 4, (short) (i), (short) (i));
sheet.addMergedRegion(region1);
cell.setCellValue(new HSSFRichTextString("位置信息"));
}
else if (i == 5) {
// 参数1：行号 参数2：起始列号 参数3：行号 参数4：终止列号
CellRangeAddress region1 = new CellRangeAddress(k + 3, k + 4, (short) (i), (short) (i));
sheet.addMergedRegion(region1);
cell.setCellValue(new HSSFRichTextString("车辆状态"));
}
else if (i == 6) {
// 参数1：行号 参数2：起始列号 参数3：行号 参数4：终止列号
CellRangeAddress region1 = new CellRangeAddress(k + 3, k + 5, (short) (i), (short) (i));
sheet.addMergedRegion(region1);
cell.setCellValue(new HSSFRichTextString("行驶里程（km）"));
}
else if (i == 7) {
// 参数1：行号 参数2：起始列号 参数3：行号 参数4：终止列号
CellRangeAddress region1 = new CellRangeAddress(k + 3, k + 5, (short) (i), (short) (i));
sheet.addMergedRegion(region1);
cell.setCellValue(new HSSFRichTextString("超速（次数）"));
}
else if (i == 8) {
// 参数1：行号 参数2：起始列号 参数3：行号 参数4：终止列号
CellRangeAddress region1 = new CellRangeAddress(k + 3, k + 3, (short) (i), (short) (i + 3));
sheet.addMergedRegion(region1);
cell.setCellValue(new HSSFRichTextString("疲劳驾驶"));
}
else if (i == 12) {
// 参数1：行号 参数2：起始列号 参数3：行号 参数4：终止列号
CellRangeAddress region1 = new CellRangeAddress(k + 3, k + 5, (short) (i), (short) (i));
sheet.addMergedRegion(region1);
cell.setCellValue(new HSSFRichTextString("急加速（次数）"));
}
else if (i == 13) {
// 参数1：行号 参数2：起始列号 参数3：行号 参数4：终止列号
CellRangeAddress region1 = new CellRangeAddress(k + 3, k + 5, (short) (i), (short) (i));
sheet.addMergedRegion(region1);
cell.setCellValue(new HSSFRichTextString("急减速（次数）"));
}
else if (i == 14) {
// 参数1：行号 参数2：起始列号 参数3：行号 参数4：终止列号
CellRangeAddress region1 = new CellRangeAddress(k + 3, k + 5, (short) (i), (short) (i));
sheet.addMergedRegion(region1);
cell.setCellValue(new HSSFRichTextString("急转弯（次数）"));
}
else if (i == 15) {
// 参数1：行号 参数2：起始列号 参数3：行号 参数4：终止列号
CellRangeAddress region1 = new CellRangeAddress(k + 3, k + 5, (short) (i), (short) (i));
sheet.addMergedRegion(region1);
cell.setCellValue(new HSSFRichTextString("平均速度（km/h）"));
}
else if (i == 16) {
// 参数1：行号 参数2：起始列号 参数3：行号 参数4：终止列号
CellRangeAddress region1 = new CellRangeAddress(k + 3, k + 5, (short) (i), (short) (i));
sheet.addMergedRegion(region1);
cell.setCellValue(new HSSFRichTextString("最高速度（km/h）"));
}
}
else if (k == 1) {
if (i == 8) {
// 参数1：行号 参数2：起始列号 参数3：行号 参数4：终止列号
CellRangeAddress region1 = new CellRangeAddress(k + 3, k + 4, (short) (i), (short) (i));
sheet.addMergedRegion(region1);
cell.setCellValue(new HSSFRichTextString("次数"));
}
else if (i == 9) {
// 参数1：行号 参数2：起始列号 参数3：行号 参数4：终止列号
CellRangeAddress region1 = new CellRangeAddress(k + 3, k + 3, (short) (i), (short) (i + 1));
sheet.addMergedRegion(region1);
cell.setCellValue(new HSSFRichTextString("时长"));
}
else if (i == 11) {
// 参数1：行号 参数2：起始列号 参数3：行号 参数4：终止列号
CellRangeAddress region1 = new CellRangeAddress(k + 3, k + 4, (short) (i), (short) (i));
sheet.addMergedRegion(region1);
cell.setCellValue(new HSSFRichTextString("里程（km）"));
}
}
else if (k == 2) {
if (i == 0) {
cell.setCellValue(new HSSFRichTextString("开始时间"));
}
else if (i == 1) {
cell.setCellValue(new HSSFRichTextString("结束时间"));
}
else if (i == 2) {
cell.setCellValue(new HSSFRichTextString("小时"));
}
else if (i == 3) {
cell.setCellValue(new HSSFRichTextString("分钟"));
}
else if (i == 4) {
cell.setCellValue(new HSSFRichTextString("详细地址"));
}
else if (i == 5) {
cell.setCellValue(new HSSFRichTextString("状态"));
}
else if (i == 9) {
cell.setCellValue(new HSSFRichTextString("小时"));
}
else if (i == 10) {
cell.setCellValue(new HSSFRichTextString("分钟"));
}
}
cell.setCellStyle(style); // 填充亮橘色
}
}
// 下面是输出各行的数据 下面是输出各行的数据 下面是输出各行的
for (int i = 0; i < resultList.size(); i++) {
Object result = resultList.get(i);
row = sheet.createRow(i + 6);
// 创建第 i+1 行
for (int j = 0; j < resourceField.length; j++) {
cell = row.createCell(j);// 创建第 j 列
Method method;
method = resultObj.getMethod(resourceField[j]);
// 这里用到了反射机制，通 这里用到了反射机制， 这里用到了反射机制 过方法名来取得对应方法返回的结果对象
Object obj = method.invoke(result);
if (obj != null) {
if (formatMap != null && formatMap.containsKey(resourceField)) {
cell.setCellValue(formatMap.get(resourceField).get(obj.toString()));
cell.setCellStyle(centerStyle); // 样式，居中
}
else {
String type = method.getGenericReturnType().toString();
if ("class java.util.Date".equals(type)) {
cell.setCellValue(com.youming.shuiku.system.utils.excel.DateUtil.toString((Date) obj,
com.youming.shuiku.system.utils.excel.DateUtil.DEFAULT_DATETIME_FORMAT_SEC));
}
else {
cell.setCellValue(obj.toString());
}
cell.setCellStyle(centerStyle); // 样式，居中
}
}
else {
cell.setCellStyle(centerStyle); // 样式，居中
}
}
}
// 参数1：起始行 参数2：终止行 参数3：起始列 参数4：终止列
// CellRangeAddress region1 = new CellRangeAddress(showName.length,
// showName.length, (short) 0, (short) 11);
row = sheet.createRow(resultList.size() + 6);
for (int i = 0; i < showName.length; i++) {
cell = row.createCell(i);
if (i == 0) {
// 参数1：行号 参数2：起始列号 参数3：行号 参数4：终止列号
CellRangeAddress region1 = new CellRangeAddress(resultList.size() + 6, resultList.size() + 6,
(short) (i), (short) (i + 1));
sheet.addMergedRegion(region1);
cell.setCellValue(new HSSFRichTextString("总计"));
cell.setCellStyle(greenStyle);
}
else if (i == 2) {
cell.setCellValue(new HSSFRichTextString(sumList.get(0).toString()));
cell.setCellStyle(fontStyle);
}
else if (i == 3) {
cell.setCellValue(new HSSFRichTextString(sumList.get(1).toString()));
cell.setCellStyle(fontStyle);
}
else if (i == 4) {// 折合时间（min）
cell.setCellValue(new HSSFRichTextString("折合时间（min）"));
cell.setCellStyle(greenStyle);
}
else if (i == 5) {
cell.setCellValue(new HSSFRichTextString(sumList.get(2).toString()));
cell.setCellStyle(fontStyle);
}
else if (i == 6) {
cell.setCellValue(new HSSFRichTextString(sumList.get(3).toString()));
cell.setCellStyle(fontStyle);
}
else if (i == 7) {
cell.setCellValue(new HSSFRichTextString(sumList.get(4).toString()));
cell.setCellStyle(fontStyle);
}
else if (i == 8) {
cell.setCellValue(new HSSFRichTextString(sumList.get(5).toString()));
cell.setCellStyle(fontStyle);
}
else if (i == 9) {
cell.setCellValue(new HSSFRichTextString(sumList.get(6).toString()));
cell.setCellStyle(fontStyle);
}
else if (i == 10) {
cell.setCellValue(new HSSFRichTextString(sumList.get(7).toString()));
cell.setCellStyle(fontStyle);
}
else if (i == 11) {
cell.setCellValue(new HSSFRichTextString(sumList.get(8).toString()));
cell.setCellStyle(fontStyle);
}
else if (i == 12) {
cell.setCellValue(new HSSFRichTextString(sumList.get(9).toString()));
cell.setCellStyle(fontStyle);
}
else if (i == 13) {
cell.setCellValue(new HSSFRichTextString(sumList.get(10).toString()));
cell.setCellStyle(fontStyle);
}
else if (i == 14) {
cell.setCellValue(new HSSFRichTextString(sumList.get(11).toString()));
cell.setCellStyle(fontStyle);
}
else if (i == 15) {
cell.setCellValue(new HSSFRichTextString(sumList.get(12).toString()));
cell.setCellStyle(fontStyle);
}
else if (i == 16) {
cell.setCellValue(new HSSFRichTextString(sumList.get(13).toString()));
cell.setCellStyle(fontStyle);
}
}
return workbook;
}
}
2、原始poi
int a = waterHeightReport.get(0).getPrv().size()+2;
XSSFWorkbook sheets = new XSSFWorkbook();
XSSFSheet water1 = sheets.createSheet("water1");
XSSFRow row = water1.createRow(0);
XSSFCell cell1 = row.createCell(0);
XSSFCellStyle cellStyle = sheets.createCellStyle();
cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);//水平居中
XSSFFont font = sheets.createFont(); //创建字体
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); //字体加粗
cellStyle.setFont(font);
cell1.setCellStyle(cellStyle);
cell1.setCellValue("水位报表");
CellRangeAddress callRangeAddress = new CellRangeAddress(0,0,0,a-1);//起始行,结束行,起始列,结束列
water1.addMergedRegion(callRangeAddress);//合并
XSSFRow row1 = water1.createRow(1);
XSSFCell cell2 = row1.createCell(0);
cell2.setCellValue("站点");
cell2.setCellStyle(cellStyle);
XSSFCell cell3 = row1.createCell(1);
cell3.setCellValue("实时水位");
cell3.setCellStyle(cellStyle);
for (int i = 0; i < waterHeightReport.get(0).getPrv().size() ; i++) {
XSSFCell cell = row1.createCell(i+2);
cell.setCellValue(waterHeightReport.get(0).getPrv().get(i).getPeriod()+"日(米)");
cell.setCellStyle(cellStyle);
}
for (int i = 0; i < waterHeightReport.size(); i++) {
XSSFRow rows = water1.createRow(i + 2);
rows.createCell(0).setCellValue(waterHeightReport.get(i).getStNm());
rows.createCell(1).setCellValue(waterHeightReport.get(i).getPrv().get(a-3).getWaterHeight());
for (int j = 0; j < waterHeightReport.get(i).getPrv().size(); j++) {
rows.createCell(j+2).setCellValue(waterHeightReport.get(i).getPrv().get(j).getWaterHeight());
}
}
ByteArrayOutputStream os = new ByteArrayOutputStream();
sheets.write(os);
byte[] b = os.toByteArray();
MinioUploadDto minioUploadDto = minioService.upload("水位报表.xlsx", b, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
return minioUploadDto.getUrl();
永洪科技，致力于打造全球领先的数据技术厂商，具备从数据应用方案咨询、BI、AIGC智能分析、数字孪生、数据资产、数据治理、数据实施的端到端大数据价值服务能力。
更多推荐
0
0- 0
已为社区贡献1条内容
所有评论(0)