java操作excel

最新推荐文章于 2023-06-13 11:03:52 发布
最新推荐文章于 2023-06-13 11:03:52 发布
阅读量501 收藏
点赞数
分类专栏： java 文章标签： java
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weiqiang915/article/details/107980617
版权

百度 此博客标题，能找到很多结果，但是内容千篇一律。

这里用的是spire.xlsx以及poi。

需求：向excel模板中插入动态数据以及向存在动态数据的excel文件中插入图片。

用到的技术：Excel4J（感兴趣的可以去开源中国查查）；POI（阿帕奇公司的，不用我多说了吧）

1.加依赖

其中spire.xls.free是手动添加到本地maven库的，点此下载。

手动添加jar包本地maven库的教程可以点这查看。

<!-- spire操作excel -->
<dependency>
	<groupId>spire</groupId>
	<artifactId>xls.free</artifactId>
	<version>2.2.0</version>
</dependency>
<!-- Excel4依赖的poi包 -->
<dependency>
	<groupId>org.apache.poi</groupId>
	<artifactId>poi</artifactId>
	<version>4.1.2</version>
</dependency>
<dependency>
	<groupId>org.apache.poi</groupId>
	<artifactId>poi-ooxml</artifactId>
	<version>4.1.2</version>
</dependency>
<!-- poi在封装jar包，操作excel -->
<dependency>
	<groupId>com.github.crab2died</groupId>
	<artifactId>Excel4J</artifactId>
	<version>3.0.0</version>
</dependency>

2.把下面的工具类复制到项目中

package com.jdkj.charge.common.utils;

import com.github.crab2died.converter.DefaultConvertible;
import com.github.crab2died.exceptions.Excel4JException;
import com.github.crab2died.exceptions.Excel4jReadException;
import com.github.crab2died.handler.ExcelHeader;
import com.github.crab2died.handler.SheetTemplate;
import com.github.crab2died.handler.SheetTemplateHandler;
import com.github.crab2died.sheet.wrapper.MapSheetWrapper;
import com.github.crab2died.sheet.wrapper.NoTemplateSheetWrapper;
import com.github.crab2died.sheet.wrapper.NormalSheetWrapper;
import com.github.crab2died.sheet.wrapper.SimpleSheetWrapper;
import com.github.crab2died.utils.Utils;
import org.apache.commons.csv.CSVFormat;
import org.apache.commons.csv.CSVParser;
import org.apache.commons.csv.CSVPrinter;
import org.apache.commons.csv.CSVRecord;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.*;
import java.lang.reflect.Array;
import java.nio.charset.StandardCharsets;
import java.util.ArrayList;
import java.util.Collection;
import java.util.List;
import java.util.Map;

public final class ExcelUtils {
	private static volatile ExcelUtils excelUtils;

	private ExcelUtils() {
	}

	public static ExcelUtils getInstance() {
		if (null == excelUtils) {
			synchronized (ExcelUtils.class) {
				if (null == excelUtils) {
					excelUtils = new ExcelUtils();
				}
			}
		}
		return excelUtils;
	}

	public <T> List<T> readExcel2Objects(String excelPath, Class<T> clazz, int offsetLine, int limitLine,
			int sheetIndex) throws Excel4JException, IOException {

		try (Workbook workbook = WorkbookFactory.create(new FileInputStream(new File(excelPath)))) {
			return readExcel2ObjectsHandler(workbook, clazz, offsetLine, limitLine, sheetIndex);
		}
	}

	/**
	 * 读取Excel操作基于注解映射成绑定的java对象
	 *
	 * @param is         待导出Excel的数据流
	 * @param clazz      待绑定的类(绑定属性注解{@link com.github.crab2died.annotation.ExcelField})
	 * @param offsetLine Excel表头行(默认是0)
	 * @param limitLine  最大读取行数(默认表尾)
	 * @param sheetIndex Sheet索引(默认0)
	 * @param <T>        绑定的数据类
	 * @return 返回转换为设置绑定的java对象集合
	 * @throws Excel4JException 异常
	 * @throws IOException      异常
	 * @author Crab2Died
	 */
	public <T> List<T> readExcel2Objects(InputStream is, Class<T> clazz, int offsetLine, int limitLine, int sheetIndex)
			throws Excel4JException, IOException {

		try (Workbook workbook = WorkbookFactory.create(is)) {
			return readExcel2ObjectsHandler(workbook, clazz, offsetLine, limitLine, sheetIndex);
		}
	}

	public <T> List<T> readExcel2Objects(String excelPath, Class<T> clazz, int offsetLine, int sheetIndex)
			throws Excel4JException, IOException {
		return readExcel2Objects(excelPath, clazz, offsetLine, Integer.MAX_VALUE, sheetIndex);
	}

	public <T> List<T> readExcel2Objects(String excelPath, Class<T> clazz, int sheetIndex)
			throws Excel4JException, IOException {
		return readExcel2Objects(excelPath, clazz, 0, Integer.MAX_VALUE, sheetIndex);
	}

	public <T> List<T> readExcel2Objects(String excelPath, Class<T> clazz) throws Excel4JException, IOException {
		return readExcel2Objects(excelPath, clazz, 0, Integer.MAX_VALUE, 0);
	}

	public <T> List<T> readExcel2Objects(InputStream is, Class<T> clazz, int sheetIndex)
			throws Excel4JException, IOException {
		return readExcel2Objects(is, clazz, 0, Integer.MAX_VALUE, sheetIndex);
	}

	public <T> List<T> readExcel2Objects(InputStream is, Class<T> clazz) throws Excel4JException, IOException {
		return readExcel2Objects(is, clazz, 0, Integer.MAX_VALUE, 0);
	}

	private <T> List<T> readExcel2ObjectsHandler(Workbook workbook, Class<T> clazz, int offsetLine, int limitLine,
			int sheetIndex) throws Excel4JException {

		Sheet sheet = workbook.getSheetAt(sheetIndex);
		Row row = sheet.getRow(offsetLine);
		List<T> list = new ArrayList<>();
		Map<Integer, ExcelHeader> maps = Utils.getHeaderMap(row, clazz);
		if (maps == null || maps.size() <= 0)
			throw new Excel4jReadException(
					"The Excel format to read is not correct, and check to see if the appropriate rows are set");
		long maxLine = sheet.getLastRowNum() > ((long) offsetLine + limitLine) ? ((long) offsetLine + limitLine)
				: sheet.getLastRowNum();

		for (int i = offsetLine + 1; i <= maxLine; i++) {
			row = sheet.getRow(i);
			if (null == row)
				continue;
			T obj;
			try {
				obj = clazz.newInstance();
			} catch (InstantiationException | IllegalAccessException e) {
				throw new Excel4JException(e);
			}
			for (Cell cell : row) {
				int ci = cell.getColumnIndex();
				ExcelHeader header = maps.get(ci);
				if (null == header)
					continue;
				String val = Utils.getCellValue(cell);
				Object value;
				String filed = header.getFiled();
				// 读取转换器
				if (null != header.getReadConverter()
						&& header.getReadConverter().getClass() != DefaultConvertible.class) {
					value = header.getReadConverter().execRead(val);
				} else {
					// 默认转换
					value = Utils.str2TargetClass(val, header.getFiledClazz());
				}
				Utils.copyProperty(obj, filed, value);
			}
			list.add(obj);
		}
		return list;
	}

	public List<List<String>> readExcel2List(String excelPath, int offsetLine, int limitLine, int sheetIndex)
			throws IOException {

		try (Workbook workbook = WorkbookFactory.create(new FileInputStream(new File(excelPath)))) {
			return readExcel2ObjectsHandler(workbook, offsetLine, limitLine, sheetIndex);
		}
	}

	public List<List<String>> readExcel2List(InputStream is, int offsetLine, int limitLine, int sheetIndex)
			throws IOException {

		try (Workbook workbook = WorkbookFactory.create(is)) {
			return readExcel2ObjectsHandler(workbook, offsetLine, limitLine, sheetIndex);
		}
	}

	public List<List<String>> readExcel2List(String excelPath, int offsetLine) throws IOException {

		try (Workbook workbook = WorkbookFactory.create(new FileInputStream(new File(excelPath)))) {
			return readExcel2ObjectsHandler(workbook, offsetLine, Integer.MAX_VALUE, 0);
		}
	}

	public List<List<String>> readExcel2List(InputStream is, int offsetLine) throws IOException {

		try (Workbook workbook = WorkbookFactory.create(is)) {
			return readExcel2ObjectsHandler(workbook, offsetLine, Integer.MAX_VALUE, 0);
		}
	}

	public List<List<String>> readExcel2List(String excelPath) throws IOException {

		try (Workbook workbook = WorkbookFactory.create(new FileInputStream(new File(excelPath)))) {
			return readExcel2ObjectsHandler(workbook, 0, Integer.MAX_VALUE, 0);
		}
	}

	public List<List<String>> readExcel2List(InputStream is) throws IOException {

		try (Workbook workbook = WorkbookFactory.create(is)) {
			return readExcel2ObjectsHandler(workbook, 0, Integer.MAX_VALUE, 0);
		}
	}

	private List<List<String>> readExcel2ObjectsHandler(Workbook workbook, int offsetLine, int limitLine,
			int sheetIndex) {

		List<List<String>> list = new ArrayList<>();
		Sheet sheet = workbook.getSheetAt(sheetIndex);
		long maxLine = sheet.getLastRowNum() > ((long) offsetLine + limitLine) ? ((long) offsetLine + limitLine)
				: sheet.getLastRowNum();
		for (int i = offsetLine; i <= maxLine; i++) {
			List<String> rows = new ArrayList<>();
			Row row = sheet.getRow(i);
			if (null == row)
				continue;
			for (Cell cell : row) {
				String val = Utils.getCellValue(cell);
				rows.add(val);
			}
			list.add(rows);
		}
		return list;
	}

	public void exportObjects2Excel(String templatePath, int sheetIndex, List<?> data, Map<String, String> extendMap,
			Class clazz, boolean isWriteHeader, String targetPath) throws Excel4JException {

		try (SheetTemplate sheetTemplate = exportExcelByModuleHandler(templatePath, sheetIndex, data, extendMap, clazz,
				isWriteHeader)) {
			sheetTemplate.write2File(targetPath);
		} catch (IOException e) {
			throw new Excel4JException(e);
		}
	}

	public void exportObjects2Excel(String templatePath, int sheetIndex, List<?> data, Map<String, String> extendMap,
			Class clazz, boolean isWriteHeader, OutputStream os) throws Excel4JException {

		try (SheetTemplate sheetTemplate = exportExcelByModuleHandler(templatePath, sheetIndex, data, extendMap, clazz,
				isWriteHeader)) {
			sheetTemplate.write2Stream(os);
		} catch (IOException e) {
			throw new Excel4JException(e);
		}
	}

	public void exportObjects2Excel(String templatePath, List<?> data, Map<String, String> extendMap, Class clazz,
			boolean isWriteHeader, String targetPath) throws Excel4JException {

		exportObjects2Excel(templatePath, 0, data, extendMap, clazz, isWriteHeader, targetPath);
	}

	public void exportObjects2Excel(String templatePath, List<?> data, Map<String, String> extendMap, Class clazz,
			boolean isWriteHeader, OutputStream os) throws Excel4JException {

		exportObjects2Excel(templatePath, 0, data, extendMap, clazz, isWriteHeader, os);
	}

	public void exportObjects2Excel(String templatePath, List<?> data, Map<String, String> extendMap, Class clazz,
			String targetPath) throws Excel4JException {

		exportObjects2Excel(templatePath, 0, data, extendMap, clazz, true, targetPath);
	}

	public void exportObjects2Excel(String templatePath, List<?> data, Map<String, String> extendMap, Class clazz,
			OutputStream os) throws Excel4JException {

		exportObjects2Excel(templatePath, 0, data, extendMap, clazz, true, os);
	}

	public void exportObjects2Excel(String templatePath, List<?> data, Class clazz, String targetPath)
			throws Excel4JException {

		exportObjects2Excel(templatePath, 0, data, null, clazz, true, targetPath);
	}

	public void exportObjects2Excel(String templatePath, List<?> data, Class clazz, OutputStream os)
			throws Excel4JException {

		exportObjects2Excel(templatePath, 0, data, null, clazz, true, os);
	}

	private SheetTemplate exportExcelByModuleHandler(String templatePath, int sheetIndex, List<?> data,
			Map<String, String> extendMap, Class clazz, boolean isWriteHeader) throws Excel4JException {

		SheetTemplate template = SheetTemplateHandler.sheetTemplateBuilder(templatePath);
		generateSheet(sheetIndex, data, extendMap, clazz, isWriteHeader, template);
		return template;
	}

	public void normalSheet2Excel(List<NormalSheetWrapper> sheetWrappers, String templatePath, String targetPath)
			throws Excel4JException {

		try (SheetTemplate sheetTemplate = exportExcelByModuleHandler(templatePath, sheetWrappers)) {
			sheetTemplate.write2File(targetPath);
		} catch (IOException e) {
			throw new Excel4JException(e);
		}
	}

	public void normalSheet2Excel(List<NormalSheetWrapper> sheetWrappers, String templatePath, OutputStream os)
			throws Excel4JException {

		try (SheetTemplate sheetTemplate = exportExcelByModuleHandler(templatePath, sheetWrappers)) {
			sheetTemplate.write2Stream(os);
		} catch (IOException e) {
			throw new Excel4JException(e);
		}
	}

	private SheetTemplate exportExcelByModuleHandler(String templatePath, List<NormalSheetWrapper> sheets)
			throws Excel4JException {

		SheetTemplate template = SheetTemplateHandler.sheetTemplateBuilder(templatePath);
		for (NormalSheetWrapper sheet : sheets) {
			generateSheet(sheet.getSheetIndex(), sheet.getData(), sheet.getExtendMap(), sheet.getClazz(),
					sheet.isWriteHeader(), template);
		}
		return template;
	}

	private void generateSheet(int sheetIndex, List<?> data, Map<String, String> extendMap, Class clazz,
			boolean isWriteHeader, SheetTemplate template) throws Excel4JException {

		SheetTemplateHandler.loadTemplate(template, sheetIndex);
		SheetTemplateHandler.extendData(template, extendMap);
		List<ExcelHeader> headers = Utils.getHeaderList(clazz);
		if (isWriteHeader) {
			// 写标题
			SheetTemplateHandler.createNewRow(template);
			for (ExcelHeader header : headers) {
				SheetTemplateHandler.createCell(template, header.getTitle(), null);
			}
		}

		for (Object object : data) {
			SheetTemplateHandler.createNewRow(template);
			SheetTemplateHandler.insertSerial(template, null);
			for (ExcelHeader header : headers) {
				SheetTemplateHandler.createCell(template,
						Utils.getProperty(object, header.getFiled(), header.getWriteConverter()), null);
			}
		}
	}

	public void exportMap2Excel(String templatePath, int sheetIndex, Map<String, List<?>> data,
			Map<String, String> extendMap, Class clazz, boolean isWriteHeader, String targetPath)
			throws Excel4JException {

		try (SheetTemplate sheetTemplate = exportExcelByMapHandler(templatePath, sheetIndex, data, extendMap, clazz,
				isWriteHeader)) {
			sheetTemplate.write2File(targetPath);
		} catch (IOException e) {
			throw new Excel4JException(e);
		}
	}

	public void exportMap2Excel(String templatePath, int sheetIndex, Map<String, List<?>> data,
			Map<String, String> extendMap, Class clazz, boolean isWriteHeader, OutputStream os)
			throws Excel4JException {

		try (SheetTemplate sheetTemplate = exportExcelByMapHandler(templatePath, sheetIndex, data, extendMap, clazz,
				isWriteHeader)) {
			sheetTemplate.write2Stream(os);
		} catch (IOException e) {
			throw new Excel4JException(e);
		}
	}

	public void exportMap2Excel(String templatePath, Map<String, List<?>> data, Map<String, String> extendMap,
			Class clazz, String targetPath) throws Excel4JException {

		try (SheetTemplate sheetTemplate = exportExcelByMapHandler(templatePath, 0, data, extendMap, clazz, true)) {
			sheetTemplate.write2File(targetPath);
		} catch (IOException e) {
			throw new Excel4JException(e);
		}
	}

	public void exportMap2Excel(String templatePath, Map<String, List<?>> data, Map<String, String> extendMap,
			Class clazz, OutputStream os) throws Excel4JException {

		try (SheetTemplate sheetTemplate = exportExcelByMapHandler(templatePath, 0, data, extendMap, clazz, true)) {
			sheetTemplate.write2Stream(os);
		} catch (IOException e) {
			throw new Excel4JException(e);
		}
	}

	public void exportMap2Excel(String templatePath, Map<String, List<?>> data, Class clazz, String targetPath)
			throws Excel4JException {

		try (SheetTemplate sheetTemplate = exportExcelByMapHandler(templatePath, 0, data, null, clazz, true)) {
			sheetTemplate.write2File(targetPath);
		} catch (IOException e) {
			throw new Excel4JException(e);
		}
	}

	public void exportMap2Excel(String templatePath, Map<String, List<?>> data, Class clazz, OutputStream os)
			throws Excel4JException {

		try (SheetTemplate sheetTemplate = exportExcelByMapHandler(templatePath, 0, data, null, clazz, true)) {
			sheetTemplate.write2Stream(os);
		} catch (IOException e) {
			throw new Excel4JException(e);
		}
	}

	private SheetTemplate exportExcelByMapHandler(String templatePath, int sheetIndex, Map<String, List<?>> data,
			Map<String, String> extendMap, Class clazz, boolean isWriteHeader) throws Excel4JException {
		SheetTemplate template = SheetTemplateHandler.sheetTemplateBuilder(templatePath);
		generateSheet(template, sheetIndex, data, extendMap, clazz, isWriteHeader);

		return template;
	}

	public void mapSheet2Excel(List<MapSheetWrapper> sheetWrappers, String templatePath, String targetPath)
			throws Excel4JException {

		try (SheetTemplate sheetTemplate = exportExcelByMapHandler(sheetWrappers, templatePath)) {
			sheetTemplate.write2File(targetPath);
		} catch (IOException e) {
			throw new Excel4JException(e);
		}
	}

	public void mapSheet2Excel(List<MapSheetWrapper> sheetWrappers, String templatePath, OutputStream os)
			throws Excel4JException {

		try (SheetTemplate sheetTemplate = exportExcelByMapHandler(sheetWrappers, templatePath)) {
			sheetTemplate.write2Stream(os);
		} catch (IOException e) {
			throw new Excel4JException(e);
		}
	}

	private SheetTemplate exportExcelByMapHandler(List<MapSheetWrapper> sheetWrappers, String templatePath)
			throws Excel4JException {
		SheetTemplate template = SheetTemplateHandler.sheetTemplateBuilder(templatePath);
		for (MapSheetWrapper sheet : sheetWrappers) {
			generateSheet(template, sheet.getSheetIndex(), sheet.getData(), sheet.getExtendMap(), sheet.getClazz(),
					sheet.isWriteHeader());
		}

		return template;
	}

	private void generateSheet(SheetTemplate template, int sheetIndex, Map<String, List<?>> data,
			Map<String, String> extendMap, Class clazz, boolean isWriteHeader) throws Excel4JException {

		SheetTemplateHandler.loadTemplate(template, sheetIndex);
		SheetTemplateHandler.extendData(template, extendMap);
		List<ExcelHeader> headers = Utils.getHeaderList(clazz);
		if (isWriteHeader) {
			// 写标题
			SheetTemplateHandler.createNewRow(template);
			for (ExcelHeader header : headers) {
				SheetTemplateHandler.createCell(template, header.getTitle(), null);
			}
		}
		for (Map.Entry<String, List<?>> entry : data.entrySet()) {
			for (Object object : entry.getValue()) {
				SheetTemplateHandler.createNewRow(template);
				SheetTemplateHandler.insertSerial(template, entry.getKey());
				for (ExcelHeader header : headers) {
					SheetTemplateHandler.createCell(template,
							Utils.getProperty(object, header.getFiled(), header.getWriteConverter()), entry.getKey());
				}
			}
		}
	}

	public void exportObjects2Excel(List<?> data, Class clazz, boolean isWriteHeader, String sheetName, boolean isXSSF,
			String targetPath) throws Excel4JException, IOException {

		try (FileOutputStream fos = new FileOutputStream(targetPath);
				Workbook workbook = exportExcelNoTemplateHandler(data, clazz, isWriteHeader, sheetName, isXSSF)) {
			workbook.write(fos);
		}
	}

	public void exportObjects2Excel(List<?> data, Class clazz, boolean isWriteHeader, String sheetName, boolean isXSSF,
			OutputStream os) throws Excel4JException, IOException {

		try (Workbook workbook = exportExcelNoTemplateHandler(data, clazz, isWriteHeader, sheetName, isXSSF)) {
			workbook.write(os);
		}
	}

	public void exportObjects2Excel(List<?> data, Class clazz, boolean isWriteHeader, String targetPath)
			throws Excel4JException, IOException {

		try (FileOutputStream fos = new FileOutputStream(targetPath);
				Workbook workbook = exportExcelNoTemplateHandler(data, clazz, isWriteHeader, null, true)) {
			workbook.write(fos);
		}
	}

	public void exportObjects2Excel(List<?> data, Class clazz, boolean isWriteHeader, OutputStream os)
			throws Excel4JException, IOException {

		try (Workbook workbook = exportExcelNoTemplateHandler(data, clazz, isWriteHeader, null, true)) {
			workbook.write(os);
		}
	}

	public void exportObjects2Excel(List<?> data, Class clazz, OutputStream os) throws Excel4JException, IOException {

		try (Workbook workbook = exportExcelNoTemplateHandler(data, clazz, true, null, true)) {
			workbook.write(os);
		}
	}

	public void exportObjects2Excel(List<?> data, Class clazz, String targetPath) throws Excel4JException, IOException {

		try (FileOutputStream fos = new FileOutputStream(targetPath);
				Workbook workbook = exportExcelNoTemplateHandler(data, clazz, true, null, true)) {
			workbook.write(fos);
		}
	}

	private Workbook exportExcelNoTemplateHandler(List<?> data, Class clazz, boolean isWriteHeader, String sheetName,
			boolean isXSSF) throws Excel4JException {

		Workbook workbook;
		if (isXSSF) {
			workbook = new XSSFWorkbook();
		} else {
			workbook = new HSSFWorkbook();
		}

		generateSheet(workbook, data, clazz, isWriteHeader, sheetName);

		return workbook;
	}

	public void noTemplateSheet2Excel(List<NoTemplateSheetWrapper> sheets, String targetPath)
			throws Excel4JException, IOException {

		try (OutputStream fos = new FileOutputStream(targetPath);
				Workbook workbook = exportExcelNoTemplateHandler(sheets, true)) {
			workbook.write(fos);
		}
	}

	public void noTemplateSheet2Excel(List<NoTemplateSheetWrapper> sheets, boolean isXSSF, String targetPath)
			throws Excel4JException, IOException {

		try (OutputStream fos = new FileOutputStream(targetPath);
				Workbook workbook = exportExcelNoTemplateHandler(sheets, isXSSF)) {
			workbook.write(fos);
		}
	}

	public void noTemplateSheet2Excel(List<NoTemplateSheetWrapper> sheets, OutputStream os)
			throws Excel4JException, IOException {

		try (Workbook workbook = exportExcelNoTemplateHandler(sheets, true)) {
			workbook.write(os);
		}
	}

	public void noTemplateSheet2Excel(List<NoTemplateSheetWrapper> sheets, boolean isXSSF, OutputStream os)
			throws Excel4JException, IOException {

		try (Workbook workbook = exportExcelNoTemplateHandler(sheets, isXSSF)) {
			workbook.write(os);
		}
	}

	private Workbook exportExcelNoTemplateHandler(List<NoTemplateSheetWrapper> sheetWrappers, boolean isXSSF)
			throws Excel4JException {

		Workbook workbook;
		if (isXSSF) {
			workbook = new XSSFWorkbook();
		} else {
			workbook = new HSSFWorkbook();
		}
		for (NoTemplateSheetWrapper sheet : sheetWrappers) {
			generateSheet(workbook, sheet.getData(), sheet.getClazz(), sheet.isWriteHeader(), sheet.getSheetName());
		}

		return workbook;
	}

	private void generateSheet(Workbook workbook, List<?> data, Class clazz, boolean isWriteHeader, String sheetName)
			throws Excel4JException {

		Sheet sheet;
		if (null != sheetName && !"".equals(sheetName)) {
			sheet = workbook.createSheet(sheetName);
		} else {
			sheet = workbook.createSheet();
		}
		Row row = sheet.createRow(0);
		List<ExcelHeader> headers = Utils.getHeaderList(clazz);
		if (isWriteHeader) {
			// 写标题
			for (int i = 0; i < headers.size(); i++) {
				row.createCell(i).setCellValue(headers.get(i).getTitle());
			}
		}
		Object _data;
		for (int i = 0; i < data.size(); i++) {
			row = sheet.createRow(i + 1);
			_data = data.get(i);
			for (int j = 0; j < headers.size(); j++) {
				row.createCell(j).setCellValue(
						Utils.getProperty(_data, headers.get(j).getFiled(), headers.get(j).getWriteConverter()));
			}
		}

	}

	public void exportObjects2Excel(List<?> data, List<String> header, String sheetName, boolean isXSSF,
			String targetPath) throws IOException {

		try (OutputStream fos = new FileOutputStream(targetPath);
				Workbook workbook = exportExcelBySimpleHandler(data, header, sheetName, isXSSF)) {
			workbook.write(fos);
		}
	}

	public void exportObjects2Excel(List<?> data, List<String> header, String sheetName, boolean isXSSF,
			OutputStream os) throws IOException {

		try (Workbook workbook = exportExcelBySimpleHandler(data, header, sheetName, isXSSF)) {
			workbook.write(os);
		}
	}

	public void exportObjects2Excel(List<?> data, List<String> header, String targetPath) throws IOException {

		try (OutputStream fos = new FileOutputStream(targetPath);
				Workbook workbook = exportExcelBySimpleHandler(data, header, null, true)) {
			workbook.write(fos);
		}
	}

	public void exportObjects2Excel(List<?> data, List<String> header, OutputStream os) throws IOException {

		try (Workbook workbook = exportExcelBySimpleHandler(data, header, null, true)) {
			workbook.write(os);
		}
	}

	public void exportObjects2Excel(List<?> data, String targetPath) throws IOException {

		try (OutputStream fos = new FileOutputStream(targetPath);
				Workbook workbook = exportExcelBySimpleHandler(data, null, null, true)) {
			workbook.write(fos);
		}
	}

	public void exportObjects2Excel(List<?> data, OutputStream os) throws IOException {

		try (Workbook workbook = exportExcelBySimpleHandler(data, null, null, true)) {
			workbook.write(os);
		}
	}

	private Workbook exportExcelBySimpleHandler(List<?> data, List<String> header, String sheetName, boolean isXSSF) {

		Workbook workbook;
		if (isXSSF) {
			workbook = new XSSFWorkbook();
		} else {
			workbook = new HSSFWorkbook();
		}
		// 生成sheet
		this.generateSheet(workbook, data, header, sheetName);

		return workbook;
	}

	public void simpleSheet2Excel(List<SimpleSheetWrapper> sheets, String targetPath) throws IOException {

		try (OutputStream fos = new FileOutputStream(targetPath);
				Workbook workbook = exportExcelBySimpleHandler(sheets, true)) {
			workbook.write(fos);
		}
	}

	public void simpleSheet2Excel(List<SimpleSheetWrapper> sheets, boolean isXSSF, String targetPath)
			throws IOException {

		try (OutputStream fos = new FileOutputStream(targetPath);
				Workbook workbook = exportExcelBySimpleHandler(sheets, isXSSF)) {
			workbook.write(fos);
		}
	}

	public void simpleSheet2Excel(List<SimpleSheetWrapper> sheets, OutputStream os) throws IOException {

		try (Workbook workbook = exportExcelBySimpleHandler(sheets, true)) {
			workbook.write(os);
		}
	}

	public void simpleSheet2Excel(List<SimpleSheetWrapper> sheets, boolean isXSSF, OutputStream os) throws IOException {

		try (Workbook workbook = exportExcelBySimpleHandler(sheets, isXSSF)) {
			workbook.write(os);
		}
	}

	private Workbook exportExcelBySimpleHandler(List<SimpleSheetWrapper> sheets, boolean isXSSF) {

		Workbook workbook;
		if (isXSSF) {
			workbook = new XSSFWorkbook();
		} else {
			workbook = new HSSFWorkbook();
		}
		// 生成多sheet
		for (SimpleSheetWrapper sheet : sheets) {
			this.generateSheet(workbook, sheet.getData(), sheet.getHeader(), sheet.getSheetName());
		}

		return workbook;
	}

	private void generateSheet(Workbook workbook, List<?> data, List<String> header, String sheetName) {

		Sheet sheet;
		if (null != sheetName && !"".equals(sheetName)) {
			sheet = workbook.createSheet(sheetName);
		} else {
			sheet = workbook.createSheet();
		}

		int rowIndex = 0;
		if (null != header && header.size() > 0) {
			// 写标题
			Row row = sheet.createRow(rowIndex++);
			for (int i = 0; i < header.size(); i++) {
				row.createCell(i, CellType.STRING).setCellValue(header.get(i));
			}
		}
		for (Object object : data) {
			Row row = sheet.createRow(rowIndex++);
			if (object.getClass().isArray()) {
				for (int j = 0; j < Array.getLength(object); j++) {
					row.createCell(j, CellType.STRING).setCellValue(Array.get(object, j).toString());
				}
			} else if (object instanceof Collection) {
				Collection<?> items = (Collection<?>) object;
				int j = 0;
				for (Object item : items) {
					row.createCell(j++, CellType.STRING).setCellValue(item.toString());
				}
			} else {
				row.createCell(0, CellType.STRING).setCellValue(object.toString());
			}
		}
	}

	public <T> List<T> readCSV2Objects(String path, Class<T> clazz) {

		try (InputStream is = new FileInputStream(new File(path))) {
			return readCSVByMapHandler(is, clazz);
		} catch (IOException | Excel4JException e) {
			throw new Excel4jReadException("read [" + path + "] CSV Error: ", e);
		}
	}

	public <T> List<T> readCSV2Objects(InputStream is, Class<T> clazz) {

		try {
			return readCSVByMapHandler(is, clazz);
		} catch (Excel4JException | IOException e) {
			throw new Excel4jReadException("read CSV Error: ", e);
		}
	}

	private <T> List<T> readCSVByMapHandler(InputStream is, Class<T> clazz) throws IOException, Excel4JException {

		List<T> records = new ArrayList<>();

		List<ExcelHeader> headers = Utils.getHeaderList(clazz);
		if (null == headers || headers.size() <= 0) {
			throw new Excel4jReadException("[" + clazz + "] must configuration @ExcelFiled");
		}
		String[] csvHeaders = new String[headers.size()];
		for (int i = 0; i < headers.size(); i++) {
			csvHeaders[i] = headers.get(i).getTitle();
		}
		CSVFormat format = CSVFormat.EXCEL.withHeader(csvHeaders).withSkipHeaderRecord(true);
		try (Reader read = new InputStreamReader(is, StandardCharsets.UTF_8);
				CSVParser parser = new CSVParser(read, format)) {
			for (CSVRecord _parser : parser) {
				T obj;
				try {
					obj = clazz.newInstance();
				} catch (InstantiationException | IllegalAccessException e) {
					throw new Excel4jReadException(e);
				}
				for (ExcelHeader header : headers) {
					String value = _parser.get(header.getTitle());
					Object objectVal;
					String filed = header.getFiled();
					// 读取转换器
					if (null != header.getReadConverter()
							&& header.getReadConverter().getClass() != DefaultConvertible.class) {
						objectVal = header.getReadConverter().execRead(value);
					} else {
						// 默认转换
						objectVal = Utils.str2TargetClass(value, header.getFiledClazz());
					}
					Utils.copyProperty(obj, filed, objectVal);
				}
				records.add(obj);
			}
		}
		return records;
	}

	public void exportObjects2CSV(List<?> data, Class clazz, String path) throws Excel4JException {

		try {
			Writer writer = new FileWriter(path);
			exportCSVByMapHandler(data, clazz, true, writer);
		} catch (Excel4JException | IOException e) {
			throw new Excel4JException(e);
		}
	}

	public void exportObjects2CSV(List<?> data, Class clazz, OutputStream os) throws Excel4JException {

		try {
			Writer writer = new OutputStreamWriter(os);
			exportCSVByMapHandler(data, clazz, true, writer);
		} catch (Excel4JException | IOException e) {
			throw new Excel4JException(e);
		}
	}

	public void exportObjects2CSV(List<?> data, Class clazz, boolean isWriteHeader, String path)
			throws Excel4JException {

		try {
			Writer writer = new FileWriter(path);
			exportCSVByMapHandler(data, clazz, isWriteHeader, writer);
		} catch (Excel4JException | IOException e) {
			throw new Excel4JException(e);
		}
	}

	public void exportObjects2CSV(List<?> data, Class clazz, boolean isWriteHeader, OutputStream os)
			throws Excel4JException {

		try {
			Writer writer = new OutputStreamWriter(os);
			exportCSVByMapHandler(data, clazz, isWriteHeader, writer);
		} catch (Excel4JException | IOException e) {
			throw new Excel4JException(e);
		}
	}

	private static final byte[] UTF_8_DOM = { (byte) 0xEF, (byte) 0xBB, (byte) 0xBF };

	private void exportCSVByMapHandler(List<?> data, Class clazz, boolean isWriteHeader, Appendable appendable)
			throws Excel4JException, IOException {

		List<ExcelHeader> headers = Utils.getHeaderList(clazz);
		appendable.append(new String(UTF_8_DOM, StandardCharsets.UTF_8));

		try (CSVPrinter printer = new CSVPrinter(appendable, CSVFormat.EXCEL)) {

			if (isWriteHeader) {
				for (ExcelHeader header : headers) {
					printer.print(header.getTitle());
				}
				printer.println();
			}
			// 写数据
			for (Object _data : data) {
				for (ExcelHeader header : headers) {
					printer.print(Utils.getProperty(_data, header.getFiled(), header.getWriteConverter()));
				}
				printer.println();
			}
			printer.flush();
		}
	}

}

3.向Excel模板中插入数据

Excel模板及说明点此下载[1积分下载]，也可以去开源中国搜索项目下载

        //excel模板位置
        String tempPath = "D:\\IdeaSpace\\Excel4J\\src\\test\\resource\\normal_template.xlsx";
        List list = new ArrayList<>();
        list.add(new Student1("1010001", "盖伦", "六年级三班"));
        list.add(new Student1("1010002", "古尔丹", "一年级三班"));
        list.add(new Student1("1010003", "蒙多(被开除了)", "六年级一班"));
        list.add(new Student1("1010004", "萝卜特", "三年级二班"));
        list.add(new Student1("1010005", "奥拉基", "三年级二班"));
        list.add(new Student1("1010006", "得嘞", "四年级二班"));
        list.add(new Student1("1010007", "瓜娃子", "五年级一班"));
        list.add(new Student1("1010008", "战三", "二年级一班"));
        list.add(new Student1("1010009", "李四", "一年级一班"));
        Map data = new HashMap<>();
        data.put("title", "战争学院花名册");
        data.put("info", "学校统一花名册");
        // 基于模板导出Excel。方法最后一个参数是根据模板生成的excel文件位置
        ExcelUtils.getInstance().exportObjects2Excel(tempPath, 0, list, data, Student1.class, false, "D:/A.xlsx");

简单部署，跑一下应该就可以得到一个渲染好的excel文件。下面介绍一下如何向渲染好excel文件中插入图片。

        Workbook workbook = new Workbook();
        workbook.loadFromFile(渲染好的excel文件路径);
        Worksheet sheet = workbook.getWorksheets().get(0);
        ExcelPicture pic = sheet.getPictures().add(位置横坐标-1, 位置纵坐标-1, 图片路径);
        //图片宽高
        pic.setWidth(500);
        pic.setHeight(300);
        workbook.saveToFile("InsertImage.xlsx", ExcelVersion.Version2013);

应该可以了，有问题请留言。

hello_world_Q
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 1
    评论
专栏目录
Java操作Excel
05-25
Java操作Excel常用的两种组件，分别是Apache的POI和阿里巴巴的EasyExcel。本文主要概括了这两种方法的读和写操作，还附上了许多的实示例，大部分来自于官网。
Java 攻城狮面试题 05_SSM
李鲤驴的博客
10-25 5783
Spring Spring MVC 什么是Spring MVC？简单介绍下你对Spring MVC的理解。 Spring MVC是一个基于Java的实现了MVC设计模式的请求驱动类型的轻量级Web框架，通过把模型-视图-控制器分离，将web层进行职责解耦，把复杂的web应用分成逻辑清晰的几部分，简化开发，减少出错，方便组内开发人员之间的配合。 Spring MVC的优点？ 可以支持各种视图技术,而不仅仅局限于JSP； 与Spring框架集成（如IoC容器、AOP等）； 清晰的角色分配：前端控制器(disp
java 操作 Excel
shenxiuwen1989的专栏
10-14 860
java 操作 Excel   Java 
Spring 参数校验最佳实践及原理解析
大鹏的博客
08-24 3375
在参数校验框架出现前，业务逻辑代码中经常会充斥着参数校验的代码，将参数校验视为业务逻辑的一部分不失为是一种较为灵活的做法，然而对于一些通用的校验，如字符串不能为空、数值范围限制等，如果还放在业务逻辑中，则会导致业务代码出现大量的重复，为了避免这种问题，Java 社区提出了 JSR-303 规范，用于对 bean 进行校验。 Spring 框架横空出世后，它又提出了一套更为简单易用的校验接口，校验作为 Spring 的核心特性之一，能够和 Spring 其他组件有机地整合到一起。
使用Java操作excel的几种方法
Daniel
03-29 1万+
在平时的业务系统开发中，少不了需要用到导出、导入excel功能，今天我们就一起来总结一下！ 下面给大家介绍一下几种常用方法： 1、apache poi 2、easypoi 3、easyexcel
Java实现Excel导入导出操作详解
ahwangzc的博客
06-13 1万+
(2条消息) Java实现Excel导入和导出，看这一篇就够了(珍藏版)_zyqok的博客-CSDN博客
Java实现Excel导入和导出，看这一篇就够了(珍藏版)
热门推荐
小白一个
12-20 17万+
前言 最近抽了两天时间，把Java实现表格的相关操作进行了封装，本次封装是基于POI的二次开发，最终使用只需要调用一个工具类中的方法，就能满足业务中绝大部门的导入和导出需求。 环境准备 1. Maven 依赖 本次工具类的封装主要依赖于阿里巴巴的JSON包，以及表格处理的POI包，所以我们需要导入这两个库的依赖包，另外，我们还需要文件上传的相关包，毕竟我们在浏览器页面，做Excel导入时，是上传的Excel文件。 <!-- 文件上传 --> <dependency>
Java操作Excel详解
weixin_43790613的博客
04-23 1436
文章目录 依赖 <!--excel相关依赖--> <!--07版本的，行数不受限制--> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-ooxml</artifactId> <version>3.17</version> </dependency> <!--日期格式化工具--&gt
java操作excel文件
最新发布
08-30
这个是自己为了方便操作excel数据,写的一个工具类
Java操作excel
10-17
Java 实现excel文件的增改删查.
java操作Excel的jar
07-19
可用于SWT的表格导出.xls文件
java操作excel读写
11-20
java操作excel读写，可以根据自己的需要灵活修改。导入导出excel
java操作Excel
06-01
java操作Excel所必须的jar包 里面有读取和写入的demo
Java操作Excel神器，easyExcel 源码分析
我是香菜
12-21 5302
easyExcel简介 项目有个需求就是需要上传Excel，然后读取数据，进行一系列的操作，很久之前就看到EasyExcel，但是一直没用过，今天试着用一下。 Java领域解析、操作Excel比较有名的框架有Apache poi、jxl等。但他们都存在一个严重的问题就是非常的耗内存。如果你的系统并发量不大的话可能还行，但是一旦并发上来后一定会OOM或者JVM频繁的full gc。 easyExcel是阿里巴巴开源的一个excel处理框架，以使用简单、节省内存著称。 64M内存1分钟内读取75M(46
Java使用 spire 修改Excel指定位置的数据（不是修改所有相同数据的方法）
weixin_46470937的博客
04-13 1155
小白都想了解的Java使用Spire对Excel的操作
使用java进行excel的读写，两种excel的区别以及easyexcel的使用
JunWeiJodn的博客
10-22 2099
java对excel进行读取的操作 EasyExcel的操作 对excel读取的模板

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论 1
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值