百度 此博客标题,能找到很多结果,但是内容千篇一律。
这里用的是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);
应该可以了,有问题请留言。