工具类篇-03-Java读取写入Excel表格内容(将内容转成任意的bean对象)

1. 需求

读取本地的Excel，将内容读取到内容；

2. 创建实体类

package com.greatmicro.ates.settle.core.domain.vo;
import lombok.Data;

/**
 * @author wql
 * @date 2021/10/18 13:37
 */
@Data
public class OrderExcel {

    /**
     * 业务类型
     */
    private String businessTypes;

    /**
     * 业务编号
     */
    private String businessNumber;

    /**
     * 委托编号
     */
    private String entrustTheNumber;

    /**
     * 收款单位
     */
    private String receivingUnit;

    /**
     * 航班
     */
    private String flight;

    /**
     * 提单号
     */
    private String blNo;

    /**
     * 离港日期
     */
    private String dateOfDeparture;
    
    /**
     * 起运港
     */
    private String startOfShipment;
    
    /**
     * 目的港
     */
    private String endOfShipment;
    
    /**
     * 费用项目
     */
    private String feeName;
    
    /**
     * 币别
     */
    private String currency;
    
    /**
     * 金额
     */
    private String amount;

}

3. 工具类

可以根据自己的需求更改

package com.greatmicro.ates.settle.core.util;

import com.greatmicro.ates.settle.core.domain.vo.OrderExcel;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;

import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.lang.reflect.Field;
import java.util.ArrayList;
import java.util.List;

public class ExcelUtil {

	/**
	 * 从第一行开始读取excel
	 *
	 * @param path   excel路径
	 * @param aimClass 实体类字节码对象
	 * @param <T>  实体类
	 * @return 封装excel的内容集合
	 */
	public static <T> List<T> parseFromExcel(String path, Class<T> aimClass) {
		return parseFromExcel(path, 0, aimClass);
	}



	/**
	 * 从第自定义行开始读取excel
	 *
	 * @param path   excel路径
	 * @param firstIndex   自定义开始有效行
	 * @param aimClass 实体类字节码对象
	 * @param <T>  实体类
	 * @return 封装excel的内容集合
	 */
	public static <T> List<T> parseFromExcel(String path, int firstIndex, Class<T> aimClass) {
		List<T> result = new ArrayList<T>();
		try {
			FileInputStream fis = new FileInputStream(path);
			Workbook workbook = WorkbookFactory.create(fis);
			//对excel文档的第一页,即sheet1进行操作
			Sheet sheet = workbook.getSheetAt(0);
			int lastRaw = sheet.getLastRowNum();
			for (int i = firstIndex; i < lastRaw; i++) {
				//第i行
				Row row = sheet.getRow(i);
				T parseObject = aimClass.newInstance();
				Field[] fields = aimClass.getDeclaredFields();
				for (int j = 0; j < fields.length; j++) {
					Field field = fields[j];
					field.setAccessible(true);
					Class<?> type = field.getType();
					//第j列
					Cell cell = row.getCell(j);
					if (cell == null) {
						continue;
					}
					//很重要的一行代码,如果不加,像12345这样的数字是不会给你转成String的,只会给你转成double,而且会导致cell.getStringCellValue()报错
					cell.setCellType(Cell.CELL_TYPE_STRING);
					String cellContent = cell.getStringCellValue();
					cellContent = "".equals(cellContent) ? "0" : cellContent;
					if (type.equals(String.class)) {
						field.set(parseObject, cellContent);
					} else if (type.equals(char.class) || type.equals(Character.class)) {
						field.set(parseObject, cellContent.charAt(0));
					} else if (type.equals(int.class) || type.equals(Integer.class)) {
						field.set(parseObject, Integer.parseInt(cellContent));
					} else if (type.equals(long.class) || type.equals(Long.class)) {
						field.set(parseObject, Long.parseLong(cellContent));
					} else if (type.equals(float.class) || type.equals(Float.class)) {
						field.set(parseObject, Float.parseFloat(cellContent));
					} else if (type.equals(double.class) || type.equals(Double.class)) {
						field.set(parseObject, Double.parseDouble(cellContent));
					} else if (type.equals(short.class) || type.equals(Short.class)) {
						field.set(parseObject, Short.parseShort(cellContent));
					} else if (type.equals(byte.class) || type.equals(Byte.class)) {
						field.set(parseObject, Byte.parseByte(cellContent));
					} else if (type.equals(boolean.class) || type.equals(Boolean.class)) {
						field.set(parseObject, Boolean.parseBoolean(cellContent));
					}
				}
				result.add(parseObject);
			}
			fis.close();
			return result;
		} catch (

				Exception e) {
			e.printStackTrace();
		}
		return result;
	}



	/**
	 * 带标题写入Excel
	 *
	 * @param beans  bean集合
	 * @param path  写入路径
	 * @param <T> 实体类类型
	 */
	public static <T> void writeExcelWithTitle(List<T> beans, String path) {
		writeExcel(beans,path,true);
	}


	/**
	 * 仅把数据写入Excel
	 *
	 * @param beans  bean集合
	 * @param path  写入路径
	 * @param <T> 实体类类型
	 */
	public static <T> void writeExcel(List<T> beans, String path) {
		writeExcel(beans,path,false);
	}

	private static <T> void writeExcel(List<T> beans, String path, boolean writeTitle) {
		if(beans == null || beans.size() == 0) {
			return;
		}
		Workbook workbook = new HSSFWorkbook();
		FileOutputStream fos = null;
		int offset = writeTitle ? 1 : 0;
		try {
			Sheet sheet = workbook.createSheet();
			for (int i = 0; i < beans.size() + offset; ++i) {
				if(writeTitle && i == 0) {createTitle(beans, sheet);continue;}
				Row row = sheet.createRow(i);
				T bean = beans.get(i - offset);
				Field[] fields = bean.getClass().getDeclaredFields();
				for (int j = 0; j < fields.length; j++) {
					Field field = fields[j];
					field.setAccessible(true);
					Cell cell = row.createCell(j);
					//Date,Calender都可以 使用  +"" 操作转成字符串
					cell.setCellValue(field.get(bean)+"");
				}
			}
			fos = new FileOutputStream(path);
			workbook.write(fos);
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			try {
				fos.close();
				workbook.close();
			} catch (IOException e) {
				e.printStackTrace();
			}
		}
	}

	private static <T> void createTitle(List<T> beans,Sheet sheet){
		Row row = sheet.createRow(0);
		T bean = beans.get(0);
		Field[] fields = bean.getClass().getDeclaredFields();
		for (int i = 0; i < fields.length; i++) {
			Field field = fields[i];
			field.setAccessible(true);
			Cell cell = row.createCell(i);
			cell.setCellValue(field.getName());
		}
	}





	public static void main(String[] args) throws Exception {

		List<OrderExcel> orderExcels = parseFromExcel("E:\\非开发软件\\谷歌浏览器下载位置\\大掌柜-柏多世蓝代理应收0623.XLSX", 5,
				OrderExcel.class);
		System.out.println("orderExcels = " + orderExcels);

		//参数里的5表示有效行数从第5行开始
//		 List<StudentInfo> studentInfos = ExcelUtil.parseFromExcel("C:\\Users\\unive\\Desktop\\StudentInfo.xlsx", 5,
//		 StudentInfo.class);
//		 for (int i = 0; i < studentInfos.size(); i++) {
//		     System.err.println(studentInfos.get(i).toString());
//                 }
//         }
	}

}

4. 测试结果

5. 使用阿里的 EasyExcel

下面伪代码是将 List 写进 xlsx 内，个人觉得蛮好用的

  List<QueryLogExcelVO> queryLogExcelList = Lists.newArrayList();
            for (CheckLogIndexVO row : rows) {
                QueryLogExcelVO excelVO = new QueryLogExcelVO();
                excelVO.setSceneCode(row.getSceneCode());
                excelVO.setRequestId(row.getRequestId());
                excelVO.setCreateTime(DateUtil.longToStr(row.getCreateTime()));
                excelVO.setContent(row.getContent());
                excelVO.setLevelDesc(row.getLevelDesc());
                excelVO.setServerTypeName(getNameByCode(row.getServerType()));
                excelVO.setLabels(row.getLabels());
                excelVO.setUser(row.getUser());
                excelVO.setMobile(row.getMobile());
                excelVO.setYdResult(row.getYdResult());
                excelVO.setCustomResult(row.getCustomResult());
                queryLogExcelList.add(excelVO);
            }
            File file = new File(queryId + ".xlsx");
            EasyExcel.write(file, QueryLogExcelVO.class).sheet("请求日志").doWrite(queryLogExcelList);

excel实体类对象

@Data
@HeadRowHeight(30)  //表头行高
@ContentRowHeight(15)  //内容行高
@ColumnWidth(18)  //列宽
@ContentFontStyle(fontHeightInPoints = (short) 12) //字体大小
public class QueryLogExcelVO {

    @ExcelProperty(value = "场景",index = 0)
    private String sceneCode;

    @ExcelProperty(value ="请求流水号",index = 1)
    private String requestId;

    @ExcelProperty(value ="请求时间",index = 2)
    private String createTime;

    @ExcelProperty(value ="内容",index = 3)
    private String content;

    @ExcelProperty(value ="处置结论",index = 4)
    private String levelDesc;

    @ExcelProperty(value ="类别",index = 5)
    private String serverTypeName;

    @ExcelProperty(value ="拒绝原因",index = 6)
    private String labels;

    @ExcelProperty(value ="用户ID",index = 7)
    private String user;

    @ExcelProperty(value ="手机号",index = 8)
    private String mobile;

    @ExcelProperty(value ="三方-易盾结论",index = 9)
    private String ydResult;

    @ExcelProperty(value ="自研词库结论",index = 10)
    private String customResult;
}