Excel POI设置自适应宽度和poi创建excel表格的代码DEMO,泛型对象T extends OrdeInfoVo赋值
Excel POI设置自适应宽度和poi创建excel表格的代码DEMO,泛型对象T extends OrdeInfoVo赋值
1.本地项目需要支持的jar
poi-3.17.jar poi-ooxml-3.17.jar poi-ooxml-schemas-3.17.jar commons-collections4-4.1.jar xmlbeans-2.6.0.jar
2.使用poi方式（XSSFSheet和SXSSFSheet两种方式）
//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表头及顺序。
//注解 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(); } } }
固定宽度
适应宽度
