excel多个对象导出工具类_多个导出放在一个工具类-优快云博客

本文链接：https://blog.youkuaiyun.com/weixin_47074036/article/details/123135181

废话不多说、直接上代码：

 <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter</artifactId>
            <version>2.5.2</version> <!-- 请检查最新版本 -->
        </dependency>
        <!-- Spring Boot Web Starter, includes Tomcat and Spring MVC -->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
            <version>2.7.3</version> <!-- 请检查最新版本 -->
        </dependency>
        <dependency>
            <groupId>org.apache.httpcomponents</groupId>
            <artifactId>httpclient</artifactId>
            <version>4.5.13</version> <!-- 检查最新版本 -->
        </dependency>
        <dependency>
            <groupId>org.json</groupId>
            <artifactId>json</artifactId>
            <version>20211205</version> <!-- 请根据需要选择最新版本 -->
        </dependency>

        <!-- Apache POI for handling Excel files -->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>5.2.2</version> <!-- 请检查最新版本 -->
        </dependency>
        <!-- Apache Commons Lang -->
        <dependency>
            <groupId>org.apache.commons</groupId>
            <artifactId>commons-lang3</artifactId>
            <version>3.12.0</version> <!-- 请检查最新版本 -->
        </dependency>
        <dependency>
            <groupId>org.apache.pdfbox</groupId>
            <artifactId>pdfbox</artifactId>
            <version>2.0.24</version>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>1.18.24</version> <!-- 请检查最新版本 -->
            <scope>provided</scope>
        </dependency>
        <!-- FastJSON -->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>fastjson</artifactId>
            <version>1.2.78</version> <!-- 请检查最新版本 -->
        </dependency>

        <!-- OkHttp -->
        <dependency>
            <groupId>com.squareup.okhttp3</groupId>
            <artifactId>okhttp</artifactId>
            <version>4.9.1</version> <!-- 请检查最新版本 -->
        </dependency>

        <!-- Spring Framework -->
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-context</artifactId>
            <version>5.3.10</version> <!-- 请检查最新版本 -->
        </dependency>

        <!-- Spring Web -->
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-web</artifactId>
            <version>5.3.10</version> <!-- 请检查最新版本 -->
        </dependency>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-webmvc</artifactId>
            <version>5.3.10</version> <!-- 请检查最新版本 -->
        </dependency>

        <!-- Apache Commons FileUpload for MultipartFile -->
        <dependency>
            <groupId>commons-fileupload</groupId>
            <artifactId>commons-fileupload</artifactId>
            <version>1.4</version>
        </dependency>
        <dependency>
            <groupId>commons-io</groupId>
            <artifactId>commons-io</artifactId>
            <version>2.8.0</version>
        </dependency>


        <dependency>
            <groupId>com.aliyun</groupId>
            <artifactId>ocr_api20210707</artifactId>
            <version>3.1.1</version>
        </dependency>
        <dependency>
            <groupId>com.aliyun</groupId>
            <artifactId>alibabacloud-ocr_api20210707</artifactId>
            <version>3.0.2</version>
        </dependency>
    </dependencies>

package com.ling.field;
import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;

// 定义注解ExcelField
@Retention(RetentionPolicy.RUNTIME) // 保留到运行时，可以通过反射获取
@Target(ElementType.FIELD) // 用于字段
public @interface ExcelField {
    // 定义注解的属性
    String name() default ""; // Excel中的列名
    boolean url() default false;
    String dateformat() default "";
    // 可以根据需要添加更多属性
    int width() default 20; // 列宽
    boolean isRequired() default false; // 是否必填
}

List<Product> collect1= new ArrayList<>();//导出数据1
List<Product> collect2= new ArrayList<>();//导出数据2
String name="导出excel名称";
List<String> strList=new ArrayList<>();//sheet名称
strList.add("sheet1");
strList.add("sheet2");
ExportListUtils.exportToWeb(request,response,name,strList,collect1,collect2);

package com.ling.utils;
import com.ling.field.ExcelField;
import org.apache.poi.common.usermodel.HyperlinkType;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.streaming.SXSSFCell;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFCell;
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 ExportListUtils {

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

	/**
	 * fileName：  导出的的excel名称
	 * sheetList: 每个sheet名称
	 * sheetDataListArr：excelSheet对象list
	 * @author fmzhang2
	 * @date 2022/2/23
	 **/
	public static <T> void exportToWeb(HttpServletRequest request, HttpServletResponse response, String fileName,
									   List<String> sheetList,List<T>... sheetDataListArr) throws Exception {
		XSSFWorkbook wb = exportSheet(sheetList, sheetDataListArr);
		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();
	}


	public static XSSFWorkbook exportSheet(List<String> sheetList,List<?>... sheetDataListArr) throws Exception {
		if (sheetDataListArr == null || sheetDataListArr.length == 0) {
			throw new RuntimeException(">>>>>>>>>>> xxl-excel error, data array can not be empty.");
		}

		// book （HSSFWorkbook=2003/xls、XSSFWorkbook=2007/xlsx）
		// 得到Excel工作簿对象
		XSSFWorkbook workbook = new XSSFWorkbook();
		int i=0;
		for (List<?> dataList : sheetDataListArr) {
			String name = sheetList.get(i);
			ExportListUtils.getXSSFWorkbook(workbook,dataList, name);
			i+=1;
		}
		return workbook;
	}


	/**
	 * workbook创建
	 * @param wb workboot
	 * @param list 导出数据
	 * @param name sheet名称
	 * @return
	 * @param <T>
	 * @throws Exception
	 */
    public static <T> XSSFWorkbook getXSSFWorkbook(XSSFWorkbook wb,List<T> list, String name) throws Exception {
        if (list == null) {
            return null;
        }
		int sheetNum = (list.size() - 1) / maxRowNums + 1;//sheet数
        XSSFSheet[] sheets = new XSSFSheet[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);
            }
            XSSFRow rowTitle = sheets[i].createRow(0);
            //TODO 冻结第一行表头
            sheets[i].createFreezePane(0, 1, 0, 1);
            // 自动调整列宽
            sheets[i].getDefaultColumnWidth();
            for (int j = 0; j < titles.size(); j++) {//遍历title，为excel首行设置标题
                XSSFCell 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;
                XSSFRow 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 = formatValue(field, fieldValue);
//                        SXSSFCell cell = row.createCell(i);
//                        link(wb, fieldValueString, cell);
                    } else {
						setValueToCell1(field, l, row, i);
                    }
                }
            }
        }
        return wb;
    }

	public static String formatValue(Field field, Object value) {
		if (value == null) {
			return "null";
		}

		// 根据字段类型进行格式化
		Class<?> fieldType = field.getType();
		if (fieldType == String.class) {
			return (String) value;
		} else if (fieldType == int.class || fieldType == Integer.class) {
			return String.valueOf(value);
		} else if (fieldType == double.class || fieldType == Double.class) {
			return String.format("%.2f", value);
		} else if (fieldType == boolean.class || fieldType == Boolean.class) {
			return String.valueOf(value);
		}
		// 可以根据需要添加更多类型的处理
		return value.toString(); // 默认处理
	}

    /**
     * 链接字体变蓝可以点击跳转
     * @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(XSSFWorkbook 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 setValueToCell1(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));
				}
			}
		}
	}
}