现在pom.xml引入
<!-- poi --> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi</artifactId> </dependency> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-ooxml-schemas</artifactId> </dependency> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-ooxml</artifactId> </dependency>
import com.sgcloud.annotation.Export2Excel; import com.sgcloud.util.page.PageBean; import com.sgcloud.util.page.Query; import com.xiaoleilu.hutool.date.DatePattern; import com.xiaoleilu.hutool.date.DateUtil; import org.apache.commons.collections4.CollectionUtils; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.CellStyle; import org.apache.poi.ss.usermodel.CellType; import org.apache.poi.ss.usermodel.Font; import org.apache.poi.ss.usermodel.HorizontalAlignment; import org.apache.poi.ss.usermodel.Row; import org.apache.poi.ss.usermodel.Sheet; import org.apache.poi.ss.usermodel.Workbook; import org.apache.poi.xssf.usermodel.XSSFWorkbook; import org.slf4j.Logger; import org.slf4j.LoggerFactory; import java.io.BufferedInputStream; import java.io.File; import java.io.FileInputStream; import java.io.FileOutputStream; import java.io.IOException; import java.io.InputStream; import java.io.OutputStream; import java.lang.reflect.Field; import java.lang.reflect.InvocationTargetException; import java.lang.reflect.Method; import java.util.ArrayList; import java.util.Date; import java.util.List; /** * excel导入导出公告对象 * * @autho */ public class PoiExcelHelper { private Logger logger = LoggerFactory.getLogger(PoiExcelHelper.class); private Query query; private IPage iPage; private List<String> exportFieldList; private Integer maxPageNum = Integer.MAX_VALUE; public interface IPage<T> { PageBean<T> listPage(Query queryObj); } private PoiExcelHelper() { } public PoiExcelHelper(IPage iPage, Query query) { this.iPage = iPage; this.query = query; } public PoiExcelHelper(IPage iPage, Query query, Integer maxPageNum) { this.iPage = iPage; this.query = query; this.maxPageNum = maxPageNum; } public PoiExcelHelper(IPage iPage, Query query, List<String> exportFieldList) { this.iPage = iPage; this.query = query; this.exportFieldList = exportFieldList; } public List<List<Object>> readExcel(InputStream is) throws IOException { List<List<Object>> results = new ArrayList<>(); Workbook xssfWorkbook = new XSSFWorkbook(is); try { Sheet sheet = xssfWorkbook.getSheetAt(0); // 获取文件的第一个sheet for (Row row : sheet) { if (row.getRowNum() == 0) { continue; } List<Object> rows = new ArrayList<>(); for (Cell cell : row) { CellType cellType = cell.getCellTypeEnum(); switch (cellType) { case STRING: rows.add(cell.getStringCellValue()); break; case NUMERIC: rows.add(cell.getNumericCellValue()); break; default: break; } } results.add(rows); } } finally { logger.info("读取excel完毕!"); is.close(); xssfWorkbook.close(); } return results; } /** * 导出Excel * @param sheetName * @param os * @throws Exception */ public void writeExcel(String sheetName, OutputStream os) throws IOException { String filePath = this.writeExcel(sheetName); File file = new File(filePath); if (file.exists()) { BufferedInputStream bis = null; // 读取filename FileInputStream fis = new FileInputStream(file); bis = new BufferedInputStream(fis); int b = 0; byte[] buffer = new byte[1024]; while ((b = bis.read(buffer)) != -1) { os.write(buffer, 0, b); } } } public String writeExcel(String sheetName) throws IOException { if (iPage != null) { // 写到服务器上 String dirPath = System.getProperty("user.dir"); // ApplicationHome home = new ApplicationHome(getClass()); // File jarFile = home.getSource(); File tmpDic = new File(dirPath + "/tmp/"); if (!tmpDic.exists()) { tmpDic.mkdir(); } String path = tmpDic.getPath() + "/" + java.util.UUID.randomUUID() + ".xlsx"; File file = new File(path); OutputStream os = new FileOutputStream(file); // 第一步,创建一个HSSFWorkbook,对应一个Excel文件 Workbook wb = new XSSFWorkbook(); // 第二步,在workbook中添加一个sheet,对应Excel文件中的sheet Sheet sheet = wb.createSheet(sheetName); sheet.autoSizeColumn(0);//列宽度自适应 List<List<String>> rowsData = new ArrayList<>(); try { PageBean pageBean = iPage.listPage(this.query); List items = pageBean != null ? pageBean.getItems() : null; if(items != null) { this.loopWrite(items, wb, sheet, os, 0, rowsData); long totalPage = maxPageNum > pageBean.getTotalPage() ? pageBean.getTotalPage() : maxPageNum; for (int i = pageBean.getCurrentPage() + 1; i <= totalPage; i++) { this.query.setPageNum(i); pageBean = iPage.listPage(this.query); items = pageBean.getItems(); if(items != null) { os = new FileOutputStream(file); this.loopWrite(items, wb, sheet, os, (pageBean.getCurrentPage() - 1) * pageBean.getPageSize() + 1, rowsData); } } } return file.getAbsolutePath(); } catch (NoSuchMethodException e) { e.printStackTrace(); } catch (IllegalAccessException e) { e.printStackTrace(); } catch (InvocationTargetException e) { e.printStackTrace(); } finally { wb.close(); } } return null; } private void loopWrite(List items, Workbook wb, Sheet sheet, OutputStream os, int startRow, List<List<String>> rowsData) throws NoSuchMethodException, IllegalAccessException, InvocationTargetException, IOException { if (startRow == 0) { List<String> headers = new ArrayList<String>(); if (items.size() > 0) { Object obj = items.get(0); Field[] fields = obj.getClass().getDeclaredFields(); for (Field field : fields) { Export2Excel export2Excel = field.getAnnotation(Export2Excel.class); if(export2Excel != null && (CollectionUtils.isEmpty(this.exportFieldList) || this.exportFieldList.contains(field.getName()))){ headers.add(export2Excel.name()); } } } if(CollectionUtils.isEmpty(headers)){ throw new RuntimeException("没有导出的列!"); } // 第三步,在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制 Row row = sheet.createRow(startRow); // 第四步,创建单元格,并设置值表头 设置表头居中 CellStyle style = wb.createCellStyle(); style.setAlignment(HorizontalAlignment.CENTER); // 创建一个居中格式 Font font = wb.createFont(); font.setBold(true); style.setFont(font); Cell cell = null; // 创建标题 for (int i = 0; i < headers.size(); i++) { cell = row.createCell(i); cell.setCellValue(headers.get(i)); cell.setCellStyle(style); sheet.setColumnWidth(i, headers.get(i).length() * 600); } } // List<List<String>> rowsData = new ArrayList<>(); for (Object obj : items) { Class cls = obj.getClass(); Field[] fields = cls.getDeclaredFields(); List columnsData = new ArrayList(); for (Field field : fields) { Export2Excel export2Excel = field.getAnnotation(Export2Excel.class); if (export2Excel != null && (CollectionUtils.isEmpty(this.exportFieldList) || this.exportFieldList.contains(field.getName()))) { String fieldName = field.getName(); String getMethodName = "get" + fieldName.substring(0, 1).toUpperCase() + fieldName.substring(1); Method getMethod = cls.getMethod(getMethodName, new Class[]{}); Object value = getMethod.invoke(obj, new Object[]{}); String textValue = ""; if (value instanceof Integer) { int intValue = (Integer) value; textValue = intValue + ""; } else if (value instanceof Float) { float fValue = (Float) value; textValue = fValue + ""; } else if (value instanceof Double) { double dValue = (Double) value; textValue = dValue + ""; } else if (value instanceof Long) { long longValue = (Long) value; textValue = longValue + ""; } else if (value instanceof Date) { textValue = DateUtil.format((Date) value, DatePattern.NORM_DATETIME_PATTERN); } else if (value != null) { textValue = value.toString(); } Class enumClas = export2Excel.enumCls(); if (enumClas.isEnum()) { Object[] objects = enumClas.getEnumConstants(); Method getCode = enumClas.getMethod("getCode"); Method getName = enumClas.getMethod("getValue"); for (Object enumObj : objects) { String code = getCode.invoke(enumObj).toString(); if (code.equals(textValue)) { textValue = getName.invoke(enumObj).toString(); break; } } } columnsData.add(textValue); } } rowsData.add(columnsData); } // 第三步,在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制 // Row row = sheet.createRow(startRow); // 第四步,创建单元格,并设置值表头 设置表头居中 // CellStyle style = wb.createCellStyle(); // style.setAlignment(HorizontalAlignment.CENTER); // 创建一个居中格式 // Font font = wb.createFont(); // font.setBold(true); // style.setFont(font); // Cell cell = null; // 创建内容 for (int i = 0; i < rowsData.size(); i++) { int index = i + (startRow == 0 ? startRow + 1 : startRow); Row row = sheet.createRow(index); for (int j = 0; j < rowsData.get(i).size(); j++) { // 将内容按顺序赋给对应的列对象 row.createCell(j).setCellValue(rowsData.get(i).get(j)); } } rowsData.clear(); wb.write(os); os.flush(); os.close(); } public static void main(String[] args) { PoiExcelHelper helper = new PoiExcelHelper(); File file = new File("/Users/tangxingchu/guowang/test.xlsx"); if (file.exists()) { try { // List<List<Object>> results = helper.readExcel(new FileInputStream(file)); FileInputStream is = new FileInputStream(file); Workbook xssfWorkbook = new XSSFWorkbook(is); try { Sheet sheet = xssfWorkbook.getSheetAt(0); // 获取文件的第一个sheet for (Row row : sheet) { if (row.getRowNum() == 0) { continue; } List<Object> rows = new ArrayList<>(); for (Cell cell : row) { CellType cellType = cell.getCellTypeEnum(); switch (cellType) { case STRING: rows.add(cell.getStringCellValue()); break; case NUMERIC: rows.add(cell.getNumericCellValue()); break; default: break; } } } } finally { is.close(); xssfWorkbook.close(); } } catch (IOException e) { e.printStackTrace(); } } String[] titles = new String[]{"title1", "title2", "title3"}; String[] row = new String[]{"row1-c1", "row1-c2", "row1-c3"}; String[] row2 = new String[]{"row2-c1", "row2-c2", "row2-c3"}; String[][] values = new String[][]{row, row2}; // try { OutputStream os = new FileOutputStream(new File("/Users/tangxingchu/guowang/test2.xlsx")); helper.writeExcel("/Users/tangxingchu/guowang", "aaa", titles, values); os.close(); // } catch (IOException e) { // e.printStackTrace(); // } } public String writeReviewExcel(String sheetName) throws IOException { if (iPage != null) { // 写到服务器上 String dirPath = System.getProperty("user.dir"); // ApplicationHome home = new ApplicationHome(getClass()); // File jarFile = home.getSource(); File tmpDic = new File(dirPath + "/tmp/"); if (!tmpDic.exists()) { tmpDic.mkdir(); } String path = tmpDic.getPath() + "/" + java.util.UUID.randomUUID() + ".xlsx"; File file = new File(path); OutputStream os = new FileOutputStream(file); // 第一步,创建一个HSSFWorkbook,对应一个Excel文件 Workbook wb = new XSSFWorkbook(); // 第二步,在workbook中添加一个sheet,对应Excel文件中的sheet Sheet sheet = wb.createSheet(sheetName); sheet.autoSizeColumn(0);//列宽度自适应 List<List<String>> rowsData = new ArrayList<>(); try { PageBean pageBean = iPage.listPage(this.query); List items = pageBean != null ? pageBean.getItems() : null; if(items != null) { this.loopWrite(items, wb, sheet, os, 0, rowsData); long totalPage = maxPageNum > pageBean.getTotalPage() ? pageBean.getTotalPage() : maxPageNum; for (int i = pageBean.getCurrentPage() + 1; i <= totalPage; i++) { this.query.setPageNum(i); pageBean = iPage.listPage(this.query); items = pageBean.getItems(); if(items != null) { os = new FileOutputStream(file); this.loopWrite(items, wb, sheet, os, (pageBean.getCurrentPage() - 1) * pageBean.getPageSize() + 1, rowsData); } } } return file.getAbsolutePath(); } catch (NoSuchMethodException e) { e.printStackTrace(); } catch (IllegalAccessException e) { e.printStackTrace(); } catch (InvocationTargetException e) { e.printStackTrace(); } finally { wb.close(); } } return null; } }
调用:
try { exportFieldList=
Lists.newArrayList("班级", "姓名", "成绩");
// 设置默认查询条件 分页方法 参数对象 列名 PoiExcelHelper excelHelper = new PoiExcelHelper(this, query, exportFieldList); String filePath = excelHelper.writeExcel("Sheet 1"); File file = new File(filePath); if (file.exists()) { BufferedInputStream bis = null; // 读取filename FileInputStream fis = new FileInputStream(file); bis = new BufferedInputStream(fis); int b = 0; byte[] buffer = new byte[1024]; while ((b = bis.read(buffer)) != -1) { os.write(buffer, 0, b); } os.flush(); } } finally { os.close(); }