easyexcel使用示例

说明

EasyExcel是一个基于Java的简单、省内存的读写Excel的开源项目。在尽可能节约内存的情况下支持读写百M的Excel。

github地址:https://github.com/alibaba/easyexcel

示例

1、写入，将日志文件解析成excel

创建模型对象，可以在模型对象的每个属性上指定每一列的样式（边框、宽度，对齐等）

import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.HorizontalAlignment;

import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.write.style.ColumnWidth;
import com.alibaba.excel.annotation.write.style.ContentRowHeight;
import com.alibaba.excel.annotation.write.style.ContentStyle;
import com.alibaba.excel.annotation.write.style.HeadStyle;

/**
 * @author wlddh
 *
 */
@ContentRowHeight(20)
@HeadStyle(borderBottom = BorderStyle.THIN, borderLeft = BorderStyle.THIN, borderRight = BorderStyle.THIN, borderTop = BorderStyle.THIN)
@ContentStyle(borderBottom = BorderStyle.THIN, borderLeft = BorderStyle.THIN, borderRight = BorderStyle.THIN, borderTop = BorderStyle.THIN)
public class LogModel {
	@ExcelProperty(index = 0, value = { "方法" })
	@ColumnWidth(50)
	private String mothod;

	@ExcelProperty(index = 1, value = { "耗时(ms)" })
	@ColumnWidth(10)
	private Long cost;

	@ExcelProperty(index = 2, value = { "级别" })
	@ColumnWidth(10)
	@ContentStyle(borderBottom = BorderStyle.THIN, borderLeft = BorderStyle.THIN, borderRight = BorderStyle.THIN, borderTop = BorderStyle.THIN, horizontalAlignment = HorizontalAlignment.CENTER)
	private String level;

	@ExcelProperty(index = 3, value = { "时间" })
	@ColumnWidth(20)
	private String date;
}

导出工具类

public class EasyExcelUtil {

	public static void write(String filePath, List<?> lstData, Class<?> clazz) throws IOException {
		OutputStream out = new FileOutputStream(filePath);
		write(out, lstData, clazz);
	}

	public static void write(OutputStream out, List<?> lstData, Class<?> clazz) {
		ExcelWriterSheetBuilder builder = EasyExcel.write(out, clazz).sheet("sheet1");
		builder.doWrite(lstData);
	}

	public static void write(String filePath, List<List<String>> lstData, List<List<String>> titles) {
		write(filePath, lstData, titles, null);
	}

	public static void write(String filePath, List<List<String>> lstData, List<List<String>> titles,
			Map<Integer, Integer> columnWidth) {
		ExcelWriterSheetBuilder builder = EasyExcel.write(filePath).head(titles).sheet("sheet1");
		builder.build().setColumnWidthMap(columnWidth);
		builder.doWrite(lstData);

	}

	public static void write(OutputStream out, List<List<String>> lstData, List<List<String>> titles) {
		ExcelWriterSheetBuilder builder = EasyExcel.write(out).head(titles).sheet("sheet1");
		builder.doWrite(lstData);
	}

	/** 返回默认类型数据 */
	public static List<List<String>> read(String filePath) {
		return read(filePath, null);
	}

	/** 返回指定类型数据 */
	public static <T> List<T> read(String filePath, Class<T> clazz) {
		List<T> rows = new ArrayList<>();
		EasyExcel.read(filePath, clazz, new ReadEventListener<T>(rows)).sheet().doRead();
		return rows;
	}

	public static List<List<String>> read(InputStream is) {
		return read(is, null);
	}

	public static <T> List<T> read(InputStream is, Class<T> clazz) {
		List<T> rows = new ArrayList<>();
		EasyExcel.read(is, clazz, new ReadEventListener<T>(rows)).sheet().doRead();
		return rows;
	}

}

日志解析工具类

public class Log2Excel {
	private static final String LOG_REG = "(\\d{4}-\\d{2}-\\d{2}\\s+\\d{2}\\:\\d{2}\\:\\d{2})(.+\\..+),(\\d+)\\(ms\\)\\[?(灾难|严重|一般|轻微)?\\]?";
	private final static Logger log = LoggerFactory.getLogger(Log2Excel.class);

	public static void parse(String logFilePath, String excelPath) {

		BufferedReader br = null;
		try {
			List<LogModel> lstData = new ArrayList<>();
			File file = new File(logFilePath);
			InputStreamReader read = new InputStreamReader(new FileInputStream(file), "gbk");
			br = new BufferedReader(read);
			String line;
			while ((line = br.readLine()) != null) {
				Pattern pattern = Pattern.compile(LOG_REG);
				Matcher m = pattern.matcher(line);
				if (m.matches()) {
					String method = m.group(2);
					method = StringUtils.substringAfterLast(method, " ");
					Long cost = Long.valueOf(m.group(3));
					String level = m.group(4);
					String date = m.group(1);
					lstData.add(new LogModel(method, cost, level, date));
				}
			}

			br.close();

			EasyExcelUtil.write(excelPath, lstData, LogModel.class);

		} catch (Exception e) {
			log.error("parse", e);
		} finally {
			try {
				if (br != null) {
					br.close();
				}
			} catch (IOException e) {

			}
		}

	}
}

测试代码

@Test
public void test() {
	Log2Excel.parse("D:/logs/pd_analysis.log.2020-07-24", "D:\\logs\\pd_analysis.xlsx");
}

2、读取

读取比较简单，可以根据模型读取，返回List对象；
也可以直接读取，返回List<List>

public class RedTest {

	@Test
	public void readWithReflectModel() {
		
		List<LogModel> list = EasyExcelUtil.read("D:/logs/pd_analysis.xlsx", LogModel.class);
		System.out.println(JsonUtil.obj2Json(list));

	}
	@Test
	public void read() {
		
		List<List<String>> list = EasyExcelUtil.read("D:/logs/pd_analysis.xlsx");
		System.out.println(JsonUtil.obj2Json(list));
		
	}
}

参考

• https://github.com/alibaba/easyexcel
• https://www.yuque.com/easyexcel/doc/easyexcel