一、使用的jar依赖版本
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.17</version>
</dependency>
注:jar包poi与poi-ooxml的区别:
poi:使用前缀为HSSF的类(HSSFWorkbook),只支持2007以前的excel(文件扩展名为xls)。
poi-ooxml:使用前缀为XSSH的类(XSSFWorkbook),支持2007以前和以后的excel(文件扩展名为xlsx)。
依赖和版本的差异可能会导致代码的差异。
以下代码只支持2007以前和以后的excel(文件扩展名为xlsx。
二、工具类
package test.poi;
import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFFont;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
public class ExcelUtil {
public static <T> ByteArrayOutputStream exportExcel(List<T> dataList, Class<T> dataEntityClass, String templateName, int titleRowNum) throws Exception {
if (dataList == null || dataList.size() == 0) {
throw new Exception("数据为空");
}
XSSFWorkbook workbook = null;
try {
InputStream in = ExcelUtil.class.getClassLoader().getResourceAsStream("templates/"+templateName);
workbook = new XSSFWorkbook(in);
XSSFSheet sheet = workbook.getSheetAt(0);
Field[] declaredFields = dataEntityClass.getDeclaredFields();
int declaredFieldsSize = declaredFields.length;
ByteArrayOutputStream baos = new ByteArrayOutputStream();
int dataListSize = dataList.size();
for (int i = 0; i < dataListSize; i++) {
T instance = dataList.get(i);
Row row = sheet.createRow(i + titleRowNum);
for(int j = 0; j < declaredFieldsSize; j++){
Field field = declaredFields[j];
field.setAccessible(true);
Object value = field.get(instance);
Cell cell = row.createCell(j);
XSSFCellStyle cellStyle = workbook.createCellStyle();
cellStyle.setWrapText(true);
cellStyle.setAlignment(HorizontalAlignment