Excel POI设置自适应宽度和poi创建excel表格的代码DEMO,泛型对象T extends OrdeInfoVo赋值
1.本地项目需要支持的jar
代码语言：javascript复制 poi-3.17.jar
poi-ooxml-3.17.jar
poi-ooxml-schemas-3.17.jar
commons-collections4-4.1.jar
xmlbeans-2.6.0.jar2.使用poi方式（XSSFSheet和SXSSFSheet两种方式）
代码语言：javascript复制//vo对象
public class OrdeVo {
public String orderNo;
public String city;
public String orderTime;
public String amt;
public String getOrderNo() {
return orderNo;
}
public void setOrderNo(String orderNo) {
this.orderNo = orderNo;
}
public String getCity() {
return city;
}
public void setCity(String city) {
this.city = city;
}
public String getOrderTime() {
return orderTime;
}
public void setOrderTime(String orderTime) {
this.orderTime = orderTime;
}
public String getAmt() {
return amt;
}
public void setAmt(String amt) {
this.amt = amt;
}
}
//XSSFSheet方式
package com.example.core.mydemo.excel;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.time.LocalDateTime;
import java.util.ArrayList;
import java.util.List;
import java.util.Optional;
public class ExcelTest2XSSFSheet {
public static void main(String[] args) {
//准备数据
List<OrdeVo> list = new ArrayList<OrdeVo>();
for (int i = 0; i < 1000; i ) {
OrdeVo orderVo = new OrdeVo();
orderVo.setOrderNo("IDOrderNo" i);
orderVo.setOrderTime(LocalDateTime.now().toString());
orderVo.setCity("上海");
orderVo.setAmt("1000" i);
list.add(orderVo);
}
// 1. 创建workbook
XSSFWorkbook wb = new XSSFWorkbook ();
// 2. 创建sheet
XSSFSheet orderSheet = wb.createSheet("订单");
// //设置宽度，前置，不能放在最后面。
// setSizeColumn((SXSSFSheet) orderSheet,4);
//初始化样式
CellStyle style = initStyle(wb);
// 3. 创建行row:添加表头0行
Row row = orderSheet.createRow(0);
//4.创建单元格
Cell cell = row.createCell(0);
cell.setCellValue("订单号");
cell.setCellStyle(style);
cell = row.createCell(1);
cell.setCellValue("下单时间");
cell.setCellStyle(style);
cell = row.createCell(2);
cell.setCellValue("金额");
cell.setCellStyle(style);
cell = row.createCell(3);
cell.setCellValue("用车城市");
cell.setCellStyle(style);
int i = 1;
for(OrdeVo ordeVo : list){
row = orderSheet.createRow(i);
createCell(row,0,style).setCellValue(ordeVo.getOrderNo());
createCell(row,1,style).setCellValue(ordeVo.getOrderTime());
createCell(row,2,style).setCellValue(ordeVo.getAmt());
createCell(row,3,style).setCellValue(ordeVo.getCity());
i ;
}
//赋值之后再设置宽度
//设置宽度，前置，不能放在最后面。
setSizeColumn((XSSFSheet) orderSheet,4);
XSSFWorkbook sxssfWorkbook = wb;
//生成本地文件
largeDataExport2Local("D:/","testExcel4.xls",sxssfWorkbook);
System.out.println("创建完成");
}
/*
* 自动使用宽度 XSSFSheet
* 不能使用SXSSFSheet
* */
private static void setSizeColumn(XSSFSheet sheet, int size) {
for (int columnNum = 0; columnNum < size; columnNum ) {
int columnWidth = sheet.getColumnWidth(columnNum) / 256;
for (int rowNum = 0; rowNum <=sheet.getLastRowNum(); rowNum ) {
XSSFRow currentRow;
//当前行未被使用过
if (sheet.getRow(rowNum) == null) {
currentRow = sheet.createRow(rowNum);
} else {
currentRow = sheet.getRow(rowNum);
}
if (currentRow.getCell(columnNum) != null) {
XSSFCell currentCell = currentRow.getCell(columnNum);
if (currentCell.getCellType() == XSSFCell.CELL_TYPE_STRING) {
int length = currentCell.getStringCellValue().getBytes().length;
if (columnWidth < length) {
columnWidth = length;
}
}
}
}
sheet.setColumnWidth(columnNum, columnWidth * 256);
}
}
private static Cell createCell(Row row, int cloumn, CellStyle style) {
Cell cell = row.createCell(cloumn);
cell.setCellStyle(style);
return cell;
}
private static CellStyle initStyle(XSSFWorkbook sxssfWorkbook) {
CellStyle style = sxssfWorkbook.createCellStyle();
style.setAlignment(HorizontalAlignment.CENTER);
style.setVerticalAlignment(VerticalAlignment.CENTER);
style.setWrapText(true);
return style;
}
public static void largeDataExport2Local(String path, String fileName, XSSFWorkbook workBook) {
FileOutputStream fileOut = null;
try {
File f = new File(path);
if (!f.exists()) {
f.mkdirs();
}
File file = new File(path fileName);
fileOut = new FileOutputStream(file);
workBook.write(fileOut);
} catch (Exception e) {
e.printStackTrace();
} finally {
Optional.ofNullable(fileOut).ifPresent(f -> {
try {
f.close();
} catch (IOException e) {
e.printStackTrace();
}
});
}
}
}
//SXSSFSheet两种方式
package com.example.core.mydemo.excel;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.streaming.SXSSFCell;
import org.apache.poi.xssf.streaming.SXSSFRow;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.time.LocalDateTime;
import java.util.ArrayList;
import java.util.List;
import java.util.Optional;
public class ExcelTest2SXSSFSheet {
public static void main(String[] args) {
//准备数据
List<OrdeVo> list = new ArrayList<OrdeVo>();
for (int i = 0; i < 1000; i ) {
OrdeVo orderVo = new OrdeVo();
orderVo.setOrderNo("IDOrderNo" i);
orderVo.setOrderTime(LocalDateTime.now().toString());
orderVo.setCity("上海");
orderVo.setAmt("1000" i);
list.add(orderVo);
}
// 1. 创建workbook
SXSSFWorkbook wb = new SXSSFWorkbook ();
// 2. 创建sheet
SXSSFSheet orderSheet = wb.createSheet("订单");
// //设置宽度，前置，不能放在最后面。
// setSizeColumn((SXSSFSheet) orderSheet,4);
//初始化样式
CellStyle style = initStyle(wb);
// 3. 创建行row:添加表头0行
Row row = orderSheet.createRow(0);
//4.创建单元格
Cell cell = row.createCell(0);
cell.setCellValue("订单号");
cell.setCellStyle(style);
cell = row.createCell(1);
cell.setCellValue("下单时间");
cell.setCellStyle(style);
cell = row.createCell(2);
cell.setCellValue("金额");
cell.setCellStyle(style);
cell = row.createCell(3);
cell.setCellValue("用车城市");
cell.setCellStyle(style);
int i = 1;
for(OrdeVo ordeVo : list){
row = orderSheet.createRow(i);
createCell(row,0,style).setCellValue(ordeVo.getOrderNo());
createCell(row,1,style).setCellValue(ordeVo.getOrderTime());
createCell(row,2,style).setCellValue(ordeVo.getAmt());
createCell(row,3,style).setCellValue(ordeVo.getCity());
i ;
}
//赋值之后再设置宽度
//设置宽度，前置，不能放在最后面。
// 解决方法：
// 使用XSSFWorkbook替代SXSSFWorkbook，，然后直接用getRow获取行
setSizeColumn(orderSheet,1000);
SXSSFWorkbook sxssfWorkbook = wb;
//生成本地文件
largeDataExport2Local("D:/","testExcel40.xls",sxssfWorkbook);
System.out.println("创建完成");
}
/**
* 自适应列宽
* @param sheet
* @param columnLength 列数
*/
private static void setSizeColumn(SXSSFSheet sheet, int columnLength) {
for (int columnNum = 0; columnNum <= columnLength; columnNum ) {
int columnWidth = sheet.getColumnWidth(columnNum) / 256;
for (int rowNum = 0; rowNum < sheet.getLastRowNum(); rowNum ) {
SXSSFRow currentRow; // 当前行未被使用过
/**
* java解决poi导出excel使用SXSSF时“Attempting to write a row[?] in the range [0,?]that is already written to disk.”异常
* https://www.cnblogs.com/BobXie85/p/12362961.html
*
* 注释掉以下3行解决报错。
*/
// if (sheet.getRow(rowNum) == null) {
// currentRow = sheet.createRow(rowNum);
// } else {
currentRow = sheet.getRow(rowNum);
// }
if (currentRow != null && currentRow.getCell(columnNum) != null) {
SXSSFCell currentCell = currentRow.getCell(columnNum);
if (currentCell.getCellType() == HSSFCell.CELL_TYPE_STRING) {
int length = currentCell.getStringCellValue().getBytes().length;
if (columnWidth < length) {
columnWidth = length;
}
}
}
}
sheet.setColumnWidth(columnNum, columnWidth * 256);
}
}
/*
* 自动使用宽度 XSSFSheet
* 不能使用SXSSFSheet
* */
private static void setSizeColumn(XSSFSheet sheet, int size) {
for (int columnNum = 0; columnNum < size; columnNum ) {
int columnWidth = sheet.getColumnWidth(columnNum) / 256;
for (int rowNum = 0; rowNum <=sheet.getLastRowNum(); rowNum ) {
XSSFRow currentRow;
//当前行未被使用过
if (sheet.getRow(rowNum) == null) {
currentRow = sheet.createRow(rowNum);
} else {
currentRow = sheet.getRow(rowNum);
}
if (currentRow.getCell(columnNum) != null) {
XSSFCell currentCell = currentRow.getCell(columnNum);
if (currentCell.getCellType() == XSSFCell.CELL_TYPE_STRING) {
int length = currentCell.getStringCellValue().getBytes().length;
if (columnWidth < length) {
columnWidth = length;
}
}
}
}
sheet.setColumnWidth(columnNum, columnWidth * 256);
}
}
private static Cell createCell(Row row, int cloumn, CellStyle style) {
Cell cell = row.createCell(cloumn);
cell.setCellStyle(style);
return cell;
}
private static CellStyle initStyle(SXSSFWorkbook sxssfWorkbook) {
CellStyle style = sxssfWorkbook.createCellStyle();
style.setAlignment(HorizontalAlignment.CENTER);
style.setVerticalAlignment(VerticalAlignment.CENTER);
style.setWrapText(true);
return style;
}
public static void largeDataExport2Local(String path, String fileName, SXSSFWorkbook workBook) {
FileOutputStream fileOut = null;
try {
File f = new File(path);
if (!f.exists()) {
f.mkdirs();
}
File file = new File(path fileName);
fileOut = new FileOutputStream(file);
workBook.write(fileOut);
} catch (Exception e) {
e.printStackTrace();
} finally {
Optional.ofNullable(fileOut).ifPresent(f -> {
try {
f.close();
} catch (IOException e) {
e.printStackTrace();
}
});
}
}
}3.使用poi model注解工具类的方式，定义excel表头及顺序。
代码语言：javascript复制//注解
package com.example.core.mydemo.excel;
import java.lang.annotation.*;
@Target(ElementType.FIELD)
@Retention(RetentionPolicy.RUNTIME)
@Documented
public @interface ExcelElement {
/**
* 对应excel的第一行（标题行）- 中文列名
*
* @return
*/
String field();
/**
* 列
*
* @return
*/
int index();
}
//vo对象
package com.example.core.mydemo.excel;
public class OrdeInfoVo {
@ExcelElement(field = "订单号", index = 1)
public String orderNo;
@ExcelElement(field = "城市", index = 2)
public String city;
@ExcelElement(field = "下单时间", index = 3)
public String orderTime;
@ExcelElement(field = "订单金额", index = 4)
public String amt;
public String getOrderNo() {
return orderNo;
}
public void setOrderNo(String orderNo) {
this.orderNo = orderNo;
}
public String getCity() {
return city;
}
public void setCity(String city) {
this.city = city;
}
public String getOrderTime() {
return orderTime;
}
public void setOrderTime(String orderTime) {
this.orderTime = orderTime;
}
public String getAmt() {
return amt;
}
public void setAmt(String amt) {
this.amt = amt;
}
}
//工具类
package com.example.core.mydemo.excel;
import org.apache.commons.beanutils.BeanUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.streaming.SXSSFCell;
import org.apache.poi.xssf.streaming.SXSSFRow;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.lang.reflect.Array;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.util.*;
public class ExcelUtils<T extends OrdeInfoVo> {
// private HSSFWorkbook wb;
/**
* 大量数据导出
*/
private SXSSFWorkbook _wb;
/**
* SXSSFWorkbook使用的样式设置
*/
private CellStyle cellStyle;
private boolean fieldsNotSup = false;
private void initStyle(SXSSFWorkbook _wb) {
cellStyle = _wb.createCellStyle();
cellStyle.setAlignment(HorizontalAlignment.CENTER);
cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
cellStyle.setWrapText(true);
}
private void exportSheet(List<T> list, Sheet createSheet, int start)
throws IllegalAccessException, InvocationTargetException,
NoSuchMethodException {
T t;
Row row;
String[] beanFieldNames = null;
Map<String, String> excelFieldMap = null;
for (int i = 1 start; i <= list.size() start; i ) {
t = list.get(i - 1 - start);
if (t == null) {
continue;
}
if (beanFieldNames == null) {
excelFieldMap = getExcelFieldMap(t);
beanFieldNames = excelFieldMap.keySet().toArray(new String[0]);
}
if (i == 1) {
Row oneRow = createSheet.createRow(0);
exportRow(oneRow, beanFieldNames, excelFieldMap);
}
row = createSheet.createRow(i);
exportRow(t, row, beanFieldNames);
}
}
private void exportRow(T t, Row row, String[] beanFieldNames) throws IllegalAccessException, InvocationTargetException, NoSuchMethodException {
String beanFieldName;
String value;
Cell cell;
for (int j = 0; j < beanFieldNames.length; j ) {
beanFieldName = beanFieldNames[j];
value = BeanUtils.getSimpleProperty(t, beanFieldName);
cell = row.createCell(j);
cell.setCellValue(value);
cell.setCellStyle(cellStyle);
}
}
private void exportRow(Row row, String[] beanFieldNames, Map<String, String> excelFieldMap) {
Cell cell;
for (int i = 0; i < beanFieldNames.length; i ) {
cell = row.createCell(i);
cell.setCellValue(excelFieldMap.get(beanFieldNames[i]));
cell.setCellStyle(cellStyle);
}
}
/**
* 并集
*
* @param field1
* @param field2
* @param type
* @return
*/
public Field[] Union(Field[] field1, Field[] field2, Class<Field> type) {
Set<Field> set = new HashSet<Field>();
if (field2 != null) {
set.addAll(Arrays.asList(field2));
}
if (field1 != null) {
for (Field f1 : field1) {
if (!set.contains(f1)) {
set.add(f1);
}
}
}
return toArray(set, type);
}
private Field[] toArray(Collection<Field> c, Class<Field> type) throws NullPointerException {
if (type == null) {
throw new NullPointerException("Collection Class is null !");
}
Field[] array = (Field[]) Array.newInstance(type, c.size());
Iterator<Field> iterator = c.iterator();
int i = 0;
while (iterator.hasNext()) {
array[i ] = iterator.next();
}
return array;
}
private Field[] union(Field[] field1, Field[] field2) {
return Union(field1, field2, Field.class);
}
private Map<String, String> getExcelFieldMap(T t) {
Map<String, String> map = new HashMap<>(16);
Map<String, String> ret = new LinkedHashMap<>(16);
Map<Integer, String> order = new HashMap<>(16);
Field[] fields = t.getClass().getFields();
if (fieldsNotSup) {
fields = null;
}
Field[] thisFields = t.getClass().getDeclaredFields();
fields = union(fields, thisFields);
boolean annotationPresent;
for (Field f : fields) {
if (f == null) {
continue;
}
// 判断是否有该类型的注解
annotationPresent = f.isAnnotationPresent(ExcelElement.class);
if (annotationPresent) {
// 得到该类型的注解
ExcelElement test = f.getAnnotation(ExcelElement.class);
map.put(f.getName(), test.field());
order.put(test.index(), f.getName());
}
}
// 排序
Set<Integer> keySet = order.keySet();
Integer[] array = keySet.toArray(new Integer[0]);
Arrays.sort(array);
for (Integer integer : array) {
String fieldName = order.get(integer);
String excelFieldName = map.get(fieldName);
ret.put(fieldName, excelFieldName);
}
return ret;
}
public void largeDataCreateSheet(List<T> list, SXSSFWorkbook workBook, Integer index) throws Exception {
if (list == null) {
throw new Exception("export Excel error, data is null");
}
_wb = workBook;
initStyle(_wb);
// 创建Excel工作表（页签）
Sheet sheet = workBook.getSheet("sheet1");
if (sheet == null) {
sheet = workBook.createSheet("sheet1");
}
// excel内容赋值
try {
exportSheet(list, sheet, index == null ? 0 : index);
//自适应列宽
setSizeColumn((SXSSFSheet) sheet,100);
} catch (Exception e) {
e.printStackTrace();
throw new RuntimeException("生成excel出错", e);
}
}
/**
* 自适应列宽
* @param sheet
* @param columnLength 列数
*/
private static void setSizeColumn(SXSSFSheet sheet, int columnLength) {
for (int columnNum = 0; columnNum <= columnLength; columnNum ) {
int columnWidth = sheet.getColumnWidth(columnNum) / 256;
for (int rowNum = 0; rowNum < sheet.getLastRowNum(); rowNum ) {
SXSSFRow currentRow; // 当前行未被使用过
if (sheet.getRow(rowNum) == null) {
currentRow = sheet.createRow(rowNum);
} else {
currentRow = sheet.getRow(rowNum);
}
if (currentRow.getCell(columnNum) != null) {
SXSSFCell currentCell = currentRow.getCell(columnNum);
if (currentCell.getCellType() == HSSFCell.CELL_TYPE_STRING) {
int length = currentCell.getStringCellValue().getBytes().length;
if (columnWidth < length) {
columnWidth = length;
}
}
}
}
sheet.setColumnWidth(columnNum, columnWidth * 256);
}
}
public void largeDataExport2Local(String path, String fileName, SXSSFWorkbook workBook) {
FileOutputStream fileOut = null;
try {
File f = new File(path);
if (!f.exists()) {
f.mkdirs();
}
File file = new File(path fileName);
fileOut = new FileOutputStream(file);
workBook.write(fileOut);
} catch (Exception e) {
e.printStackTrace();
} finally {
Optional.ofNullable(fileOut).ifPresent(f -> {
try {
f.close();
} catch (IOException e) {
e.printStackTrace();
}
});
}
}
}
//测试类
package com.example.core.mydemo.excel;
import org.apache.poi.ss.formula.functions.T;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import java.time.LocalDateTime;
import java.util.ArrayList;
import java.util.List;
public class ExcelUtilsTest {
/**
* 超过此界限写入硬盘
*/
public static final int ROW_ACCESS_WINDOW_SIZE = 10000;
public static void main(String[] args) {
//准备数据
// List<T> list = new ArrayList<T>();
List<OrdeInfoVo> list2 = new ArrayList<OrdeInfoVo>();
for (int i = 0; i < 1000; i ) {
OrdeInfoVo orderVo = new OrdeInfoVo();
orderVo.setOrderNo("IDOrderNo" i);
orderVo.setOrderTime(LocalDateTime.now().toString());
orderVo.setCity("上海");
orderVo.setAmt("1000" i);
list2.add(orderVo);
}
ExcelUtils excelUtils = new ExcelUtils();
SXSSFWorkbook sxssfWorkbook = new SXSSFWorkbook(ROW_ACCESS_WINDOW_SIZE);
try {
// public class ExcelUtils<T extends OrdeInfoVo> 解决
excelUtils.largeDataCreateSheet(list2, sxssfWorkbook, 0);
System.out.println("封装完成");
} catch (Exception e) {
e.printStackTrace();
}
try {
excelUtils.largeDataExport2Local("D:/", "testExcel6.xls", sxssfWorkbook);
System.out.println("创建完成");
} catch (Exception e) {
e.printStackTrace();
}
}
}固定宽度
适应宽度
