excel单个对象用poi导出，并设置每个sheet得最大行数防止报错

**
说明：
07版本excel最大为10万行数、
comExcelList得对象使用注解做的@ExcelSheet(name = “组件”)
**

String name="sheet";
ExportUtil.exportToWeb(request, response, fileName, comExcelList,name);

package com.zfm.util;
import com.zfm.config.ExcelField;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.lang.reflect.Type;
import java.net.URLEncoder;
import java.nio.charset.StandardCharsets;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**
 *
 * @author fmzhang2
 * @date 2022/2/23
 **/
public class ExportUtils {

	public static int maxRowNums=80000;//sheet最大行数


	/**
	 * fileName：  导出的的excel名称
	 * sheetDataListArr：	excelSheet对象list
	 * name ：shell页名称
	 * @author fmzhang2
	 * @date 2022/2/23
	 **/
	public static <T> void exportToWeb(HttpServletRequest request, HttpServletResponse response, String fileName,
									   List<T> sheetDataListArr, String name) throws Exception {

		XSSFWorkbook wb= ExportUtils.getXSSFWorkbook(sheetDataListArr,name);
		response.reset();
		response.setContentType("application/vnd.ms-excel;charset=GBK");
		if (!fileName.endsWith(".xlsx")) {
			fileName = fileName + ".xlsx";
		}

		String userAgent = request.getHeader("user-agent").toLowerCase();
		if (userAgent.contains("msie") || userAgent.contains("like gecko")) {
			// win10 ie edge 浏览器 和其他系统的ie
			fileName = URLEncoder.encode(fileName, "UTF-8");
		} else {
			// 其他
			fileName = new String(fileName.getBytes(StandardCharsets.UTF_8), StandardCharsets.ISO_8859_1);
		}
		response.setHeader("Content-Disposition", "attachment;filename=" + fileName);
		OutputStream os=response.getOutputStream();
		wb.write(os);
		os.flush();
		os.close();
	}

	    /**
     * 根据list解析注解生成HSSFWorkbook
     * @param list 导出的集合
     * @return
     * @throws Exception
     */
    public static <T> SXSSFWorkbook getXSSFWorkbook(List<T> list, String name) throws Exception {
        if (list == null) {
            return null;
        }

        SXSSFWorkbook wb = new SXSSFWorkbook();
        int sheetNum = (list.size() - 1) / maxRowNums + 1;//sheet数
        SXSSFSheet[] sheets = new SXSSFSheet[sheetNum];

        T t = list.get(0);
        Class clazz = t.getClass();
        List<String> titles = new ArrayList<String>();
        Field[] fields = clazz.getDeclaredFields();
        ArrayList<Field> effectFields = new ArrayList<Field>();
        for (Field field : fields) {
            ExcelField a = field.getAnnotation(ExcelField.class);
            if (a != null) {
                titles.add(a.name());//标题
                effectFields.add(field);//要导出的属性
            }
        }
        //CellStyle rowTopStyle = getRowTopStyle(wb);
        CellStyle style = getRowTopStyle(wb);
        for (int i = 0; i < sheets.length; i++) {
            // 创建Excel的工作sheet,对应到一个excel文档的tab
            //sheets[i] = wb.createSheet(name+(i+1));
            if (sheetNum > 1) {
                sheets[i] = wb.createSheet(name + (i + 1));
            } else {
                sheets[i] = wb.createSheet(name);
            }
            SXSSFRow rowTitle = sheets[i].createRow(0);
            //TODO 冻结第一行表头
            sheets[i].createFreezePane(0, 1, 0, 1);
            // 自动调整列宽
            sheets[i].trackAllColumnsForAutoSizing();
            for (int j = 0; j < titles.size(); j++) {//遍历title，为excel首行设置标题
                SXSSFCell cell = rowTitle.createCell(j);
                cell.setCellStyle(style);
                cell.setCellValue(titles.get(j));
                sheets[i].autoSizeColumn(j);
                //单元格列宽设置
                int colWidth = sheets[i].getColumnWidth(j) * 2;
                if (colWidth < 255 * 256) {
                    sheets[i].setColumnWidth(j, Math.max(colWidth, 3000));
                } else {
                    sheets[i].setColumnWidth(j, 5000);
                }
            }
        }
        if (list.size() > 0) {
            for (int k = 0; k < list.size(); k++) {
                T l = list.get(k);
                int index = k / maxRowNums;
                SXSSFRow row = sheets[index].createRow(k % maxRowNums + 1);
                for (int i = 0; i < effectFields.size(); i++) {
                    Field field = effectFields.get(i);
                    ExcelField a = field.getAnnotation(ExcelField.class);
                    boolean url = a.url();
                    if (url) {
                        //TODO 为链接地址
                        field.setAccessible(true);
                        Object fieldValue = field.get(l);
                        String fieldValueString = FieldReflectionUtil.formatValue(field, fieldValue);
                        SXSSFCell cell = row.createCell(i);
                        link(wb, fieldValueString, cell);
                    } else {
                        setValueToCell(field, l, row, i);
                    }
                }
            }
        }
        return wb;
    }

    /**
     * 链接字体变蓝可以点击跳转
     * @param wb
     * @param value
     * @param cellX
     * @author fmzhang2
     * @date 2022/7/28
     **/
    public static void link(SXSSFWorkbook wb, String value, SXSSFCell cellX) {
        CreationHelper createHelper = wb.getCreationHelper();
        Hyperlink hyperlink = createHelper.createHyperlink(HyperlinkType.FILE);
        hyperlink.setAddress(value);
        cellX.setHyperlink(hyperlink);
        CellStyle style1 = wb.createCellStyle();
        //声明一个字体对象
        Font font = null;
        //创建一个字体对象
        font = wb.createFont();
        //给字体对象设置颜色属性
        font.setColor((short) 30);
        //将字体对象放入XSSFCellStyle对象中
        style1.setFont(font);
        cellX.setCellStyle(style1);
        cellX.setCellValue(value);
    }


    /**
     * @description 标题列的单元格样式
     */
    public static CellStyle getRowTopStyle(SXSSFWorkbook workbook) {
        //设置样式;
        CellStyle style = workbook.createCellStyle();
        style.setAlignment(HorizontalAlignment.CENTER);
        // 顶边栏
        style.setBorderTop(BorderStyle.THIN);
        style.setTopBorderColor(IndexedColors.GREY_40_PERCENT.getIndex());
        // 右边栏
        style.setBorderRight(BorderStyle.THIN);
        style.setRightBorderColor(IndexedColors.GREY_40_PERCENT.getIndex());
        // 底边栏
        style.setBorderBottom(BorderStyle.THIN);
        style.setBottomBorderColor(IndexedColors.GREY_40_PERCENT.getIndex());
        // 左边栏
        style.setBorderLeft(BorderStyle.THIN);
        style.setLeftBorderColor(IndexedColors.GREY_40_PERCENT.getIndex());
        style.setAlignment(HorizontalAlignment.CENTER);
        // 设置填充方案
        style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        // 背景色
        style.setFillForegroundColor(IndexedColors.PALE_BLUE.index);
        return style;
    }
	
	/**
	 * 填充内容到单元格
	 * @param field 属性
	 * @param l 实体
	 * @param row 行
	 * @param cellIndex 列下标
	 * @throws Exception
	 */
	private static <T> void setValueToCell(Field field, T l, XSSFRow row, int cellIndex) throws Exception
	{
		Class clazz=l.getClass();
		ExcelField a=field.getAnnotation(ExcelField.class);
		if(a==null)//如果没有RowTitle注解，跳过该字段
		{
			return;
		}
		Type fieldType=field.getGenericType();
		String fieldName=field.getName();
		fieldName=fieldName.substring(0, 1).toUpperCase()+fieldName.replaceFirst("\\w","");//将首字母转换为大写
		Method method=clazz.getMethod("get"+fieldName);
		if(fieldType.toString().equals("class java.lang.Integer")|| fieldType.toString().equals("int"))
		{
			Integer value=(Integer)method.invoke(l);
			if(value!=null)
			{
				row.createCell(cellIndex).setCellValue(value);
			}
		}else if(fieldType.toString().equals("class java.lang.String"))
		{
			String value=(String)method.invoke(l);
			if(value!=null)
			{
				row.createCell(cellIndex).setCellValue(value);
			}
		}else if(fieldType.toString().equals("class java.lang.Long")||fieldType.toString().equals("long"))
		{
			Long value=(Long)method.invoke(l);
			if(value!=null)
			{
				row.createCell(cellIndex).setCellValue(value);
			}
		}else if(fieldType.toString().equals("class java.lang.Float")||fieldType.toString().equals("float"))
		{
			Float value=(Float)method.invoke(l);
			if(value!=null)
			{
				row.createCell(cellIndex).setCellValue(value);
			}
		}else if(fieldType.toString().equals("class java.lang.Double")||fieldType.toString().equals("double"))
		{
			Double value=(Double)method.invoke(l);
			if(value==null)
			{
				return;
			}
			if(!"".equals(a.dateformat()))
			{
				 DecimalFormat df = new DecimalFormat(a.dateformat());
				 row.createCell(cellIndex).setCellValue(df.format(value));
			}else{
				row.createCell(cellIndex).setCellValue(value);
			}
		}else if(fieldType.toString().equals("class java.util.Date")||
				fieldType.toString().equals("class java.sql.Timestamp"))
		{	if("".equals(a.dateformat()))
			{
				Date value=(Date)method.invoke(l);
				if(value!=null)
				{
					row.createCell(cellIndex).setCellValue(value);
				}
			}else
			{
				String format=a.dateformat();
				SimpleDateFormat sdf=new SimpleDateFormat(format);
				Date value=(Date)method.invoke(l);
				if(value!=null)
				{
					row.createCell(cellIndex).setCellValue(sdf.format(value));
				}
			}
		}
	}
}