poi报表工具类的使用方法，报表导出，批量导出

杵意

已于 2023-02-13 11:29:37 修改

阅读量457

点赞数

分类专栏：问题记录2 文章标签： java 开发语言 apache poi

于 2022-11-25 09:34:25 首次发布

王锡豪

本文链接：https://blog.csdn.net/wang121213145/article/details/127904344

版权

问题记录2 专栏收录该内容

45 篇文章 2 订阅

订阅专栏

文章目录

- 1、使用ExcelUtils工具类进行操作
- 2、原始poi

本次使用的是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();