pom
<dependency> <groupId>org.apache.poi</groupId> <artifactId>poi</artifactId> <version>3.17</version> </dependency> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-ooxml</artifactId> <version>3.17</version> </dependency> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-ooxml-schemas</artifactId> <version>3.17</version> </dependency> <dependency> <groupId>com.monitorjbl</groupId> <artifactId>xlsx-streamer</artifactId> <version>1.2.1</version> </dependency>
import java.beans.BeanInfo; import java.beans.IntrospectionException; import java.beans.Introspector; import java.beans.PropertyDescriptor; import java.io.BufferedInputStream; import java.io.BufferedOutputStream; import java.io.ByteArrayInputStream; import java.io.ByteArrayOutputStream; 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.InvocationTargetException; import java.lang.reflect.Method; import java.net.URLEncoder; import java.util.ArrayList; import java.util.HashMap; import java.util.List; import java.util.Map; import javax.servlet.http.HttpServletRequest; import javax.servlet.http.HttpServletResponse; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.CellStyle; import org.apache.poi.ss.usermodel.Font; 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.streaming.SXSSFWorkbook; import org.apache.poi.xssf.usermodel.XSSFRichTextString; import org.apache.poi.xssf.usermodel.XSSFWorkbook; import org.slf4j.Logger; import org.slf4j.LoggerFactory; import org.springframework.web.context.request.RequestContextHolder; import org.springframework.web.context.request.ServletRequestAttributes; import com.chinaway.g7s.common.G7sException; import com.chinaway.g7s.service.BaseService; import com.chinaway.intelli.intelli_core.common.ErrorEnum; import com.github.pagehelper.PageInfo; import com.monitorjbl.xlsx.StreamingReader; /** * excel导入导出工具类 * @author shihaiming * */ public class ExcelUtil { private static Logger log = LoggerFactory.getLogger(ExcelUtil.class); /** * @param filePath 文件保存绝对路径+文件名称 * @param params 分页查询数据的条件 * @param baseService 相关service * @param cls 相关实体类 * @param titles excel表头 * @param headFields 导出的实体类的字段名 * @param isDownload false时不弹出下载文件，为true时浏览器会弹出下载文件 * @throws Exception */ public static void exportExcel(String filePath, Map<String, Object> params, BaseService baseService, Class cls, String[] titles, String[] headFields, boolean isDownload) throws Exception { long startTime = System.currentTimeMillis(); // 开始时间 Sheet sheet = null; // 工作表对象 Row nRow = null; // 行对象 Cell nCell = null; // 列对象 int pageSize = 10000; //每次查询的数据量 params.put("pageSize", pageSize); params.put("pageNum", 1); List list = baseService.findByPage(params); PageInfo pageInfo = new PageInfo(list); List dataList = pageInfo.getList(); List<Map<String, Object>> dataMap = convertListBeanToListMap(dataList, cls); // 内存中只创建500个对象，写临时文件，当超过500条，就将内存中不用的对象释放。 Workbook wb = new SXSSFWorkbook(500); // 关键语句 Font font = wb.createFont(); font.setBold(true); CellStyle headCellStyle = wb.createCellStyle(); headCellStyle.setFont(font); int total = (int) pageInfo.getTotal(); //每个sheet保存多少行 int sheetNum = 1000000; int rowNo = 0; // 总行号 int pageRowNo = 0; // 页行号 // 根据行数求数据提取次数 int export_times = total % pageSize > 0 ? total / pageSize + 1 :total / pageSize; if(export_times > 0){ for (int i = 1; i <= export_times; i++) { for (int n = 0; n < dataMap.size(); n++) { //打印300000条后切换到下个工作表，可根据需要自行拓展，2百万，3百万...数据一样操作，只要不超过1048576就可以 if(rowNo%sheetNum==0){ sheet = wb.createSheet("第"+(rowNo/sheetNum+1)+"个工作簿");//建立新的sheet对象 sheet = wb.getSheetAt(rowNo/sheetNum); //动态指定当前的工作表 pageRowNo = 0; //每当新建了工作表就将当前工作表的行号重置为0 Row row = sheet.createRow(pageRowNo++); //新建行对象 Cell cell = row.createCell(0); for (int k = 0; k < titles.length; k++) { cell = row.createCell(k); cell.setCellStyle(headCellStyle); cell.setCellValue(new XSSFRichTextString(titles[k])); } } nRow = sheet.createRow(pageRowNo++); //新建行对象 // 打印每行, 列属性的个数 for(int j=0;j<headFields.length;j++){ nCell = nRow.createCell(j); nCell.setCellValue(dataMap.get(n).get(headFields[j])==null?"":dataMap.get(n).get(headFields[j]).toString()); } rowNo++; } dataList.clear(); params.put("pageNum", i+1); params.put("count", false);//导出时，只让第一次分页查询执行count，后面的查询不执行count，加快执行速度 list = baseService.findByPage(params); pageInfo = new PageInfo(list); dataList = pageInfo.getList(); dataMap = convertListBeanToListMap(dataList, cls); //list<Bean>转成list<Map>，方便通用各个模块 } } else { sheet = wb.createSheet("第"+(rowNo/sheetNum+1)+"个工作簿");//建立新的sheet对象 sheet = wb.getSheetAt(rowNo/sheetNum); //动态指定当前的工作表 pageRowNo = 0; //每当新建了工作表就将当前工作表的行号重置为0 Row row = sheet.createRow(pageRowNo++); //新建行对象 Cell cell = row.createCell(0); for (int k = 0; k < titles.length; k++) { cell = row.createCell(k); cell.setCellStyle(headCellStyle); cell.setCellValue(new XSSFRichTextString(titles[k])); } nRow = sheet.createRow(pageRowNo++); //新建行对象 } long finishedTime = System.currentTimeMillis(); // 处理完成时间 log.info("exportExcel finished execute time: {}s", (finishedTime - startTime) / 1000); FileOutputStream fOut = new FileOutputStream(filePath); if (fOut != null) { try { wb.write(fOut); fOut.flush(); // 刷新缓冲区 } catch (Exception e) { e.printStackTrace(); } fOut.close(); } if (wb != null){ try { wb.close(); } catch (Exception e) { e.printStackTrace(); } } long stopTime = System.currentTimeMillis(); // 写文件时间 log.info("exportExcel write xlsx file time: {}s", (stopTime - startTime) / 1000 ); //isDownload为true时，让浏览器弹出下载文件 if (isDownload) { HttpServletRequest request = ((ServletRequestAttributes)RequestContextHolder.getRequestAttributes()).getRequest(); HttpServletResponse response = ((ServletRequestAttributes)RequestContextHolder.getRequestAttributes()).getResponse(); try { File file = new File(filePath); String fileName = file.getName();// 获取日志文件名称 InputStream fis = new BufferedInputStream(new FileInputStream(filePath)); byte[] buffer = new byte[fis.available()]; fis.read(buffer); fis.close(); response.reset(); //然后转换编码格式为utf-8,保证不出现乱码,这个文件名称用于浏览器的下载框中自动显示的文件名 // 判断是否火狐浏览器 String agent = request.getHeader("User-Agent"); boolean isFirefox = (agent != null && agent.contains("Firefox")); if (isFirefox) { fileName = new String(fileName.getBytes("UTF-8"), "ISO-8859-1"); } else { fileName = URLEncoder.encode(fileName, "UTF8"); } response.addHeader("Content-Disposition", "attachment;filename=" + fileName); response.addHeader("Content-Length", "" + file.length()); OutputStream os = new BufferedOutputStream(response.getOutputStream()); response.setContentType("application/octet-stream"); os.write(buffer);// 输出文件 os.flush(); os.close(); } catch (Exception e) { log.error("exportExcel download出错: {}", e.getMessage()); } } } public static String exportExcelAndUpload(String fileName, Map<String, Object> params, BaseService baseService, Class cls, String[] titles, String[] headFields, boolean isDownload) throws Exception { String path = null; long startTime = System.currentTimeMillis(); // 开始时间 Sheet sheet = null; // 工作表对象 Row nRow = null; // 行对象 Cell nCell = null; // 列对象 int pageSize = 10000; //每次查询的数据量 params.put("pageSize", pageSize); params.put("pageNum", 1); List list = baseService.findByPage(params); PageInfo pageInfo = new PageInfo(list); List dataList = pageInfo.getList(); List<Map<String, Object>> dataMap = convertListBeanToListMap(dataList, cls); // 内存中只创建500个对象，写临时文件，当超过500条，就将内存中不用的对象释放。 Workbook wb = new SXSSFWorkbook(500); // 关键语句 Font font = wb.createFont(); font.setBold(true); CellStyle headCellStyle = wb.createCellStyle(); headCellStyle.setFont(font); int total = (int) pageInfo.getTotal(); //每个sheet保存多少行 int sheetNum = 1000000; int rowNo = 0; // 总行号 int pageRowNo = 0; // 页行号 // 根据行数求数据提取次数 int export_times = total % pageSize > 0 ? total / pageSize + 1 :total / pageSize; if(export_times > 0){ for (int i = 1; i <= export_times; i++) { for (int n = 0; n < dataMap.size(); n++) { //打印300000条后切换到下个工作表，可根据需要自行拓展，2百万，3百万...数据一样操作，只要不超过1048576就可以 if(rowNo%sheetNum==0){ sheet = wb.createSheet("第"+(rowNo/sheetNum+1)+"个工作簿");//建立新的sheet对象 sheet = wb.getSheetAt(rowNo/sheetNum); //动态指定当前的工作表 pageRowNo = 0; //每当新建了工作表就将当前工作表的行号重置为0 Row row = sheet.createRow(pageRowNo++); //新建行对象 Cell cell = row.createCell(0); for (int k = 0; k < titles.length; k++) { cell = row.createCell(k); cell.setCellStyle(headCellStyle); cell.setCellValue(new XSSFRichTextString(titles[k])); } } nRow = sheet.createRow(pageRowNo++); //新建行对象 // 打印每行, 列属性的个数 for(int j=0;j<headFields.length;j++){ nCell = nRow.createCell(j); nCell.setCellValue(dataMap.get(n).get(headFields[j])==null?"":dataMap.get(n).get(headFields[j]).toString()); } rowNo++; } dataList.clear(); params.put("pageNum", i+1); params.put("count", false);//导出时，只让第一次分页查询执行count，后面的查询不执行count，加快执行速度 list = baseService.findByPage(params); pageInfo = new PageInfo(list); dataList = pageInfo.getList(); dataMap = convertListBeanToListMap(dataList, cls); //list<Bean>转成list<Map>，方便通用各个模块 } } else { sheet = wb.createSheet("第"+(rowNo/sheetNum+1)+"个工作簿");//建立新的sheet对象 sheet = wb.getSheetAt(rowNo/sheetNum); //动态指定当前的工作表 pageRowNo = 0; //每当新建了工作表就将当前工作表的行号重置为0 Row row = sheet.createRow(pageRowNo++); //新建行对象 Cell cell = row.createCell(0); for (int k = 0; k < titles.length; k++) { cell = row.createCell(k); cell.setCellStyle(headCellStyle); cell.setCellValue(new XSSFRichTextString(titles[k])); } nRow = sheet.createRow(pageRowNo++); //新建行对象 } long finishedTime = System.currentTimeMillis(); // 处理完成时间 log.info("exportExcel finished execute time: {}s", (finishedTime - startTime) / 1000); long stopTime = System.currentTimeMillis(); // 写文件时间 log.info("exportExcel write xlsx file time: {}s", (stopTime - startTime) / 1000 ); ByteArrayOutputStream bos = null; InputStream is = null; try { bos = new ByteArrayOutputStream(); wb.write(bos); byte[] barray = bos.toByteArray(); is = new ByteArrayInputStream(barray); path = OSSUtil.upload(is, fileName, "xlsx"); } catch (Exception e) { e.printStackTrace(); } finally { if (wb != null){ try { wb.close(); } catch (Exception e) { e.printStackTrace(); } } if (bos != null) { try { bos.close(); } catch (Exception e) { e.printStackTrace(); } } if (is != null) { try { is.close(); } catch (Exception e) { e.printStackTrace(); } } } return path; } public static String exportExcelAndUploadOSS(String fileName, Map<String, Object> params, BaseService baseService, Class cls, String[] titles, String[] headFields, boolean isDownload) throws Exception { String path = null; long startTime = System.currentTimeMillis(); // 开始时间 Sheet sheet = null; // 工作表对象 Row nRow = null; // 行对象 Cell nCell = null; // 列对象 int pageSize = 10000; //每次查询的数据量 params.put("pageSize", pageSize); params.put("pageNum", 1); List list = baseService.findByPage(params); PageInfo pageInfo = new PageInfo(list); List dataList = pageInfo.getList(); List<Map<String, Object>> dataMap = convertListBeanToListMap(dataList, cls); // 内存中只创建500个对象，写临时文件，当超过500条，就将内存中不用的对象释放。 Workbook wb = new SXSSFWorkbook(500); // 关键语句 Font font = wb.createFont(); font.setBold(true); CellStyle headCellStyle = wb.createCellStyle(); headCellStyle.setFont(font); int total = (int) pageInfo.getTotal(); //每个sheet保存多少行 int sheetNum = 1000000; int rowNo = 0; // 总行号 int pageRowNo = 0; // 页行号 // 根据行数求数据提取次数 int export_times = total % pageSize > 0 ? total / pageSize + 1 :total / pageSize; if(export_times > 0){ for (int i = 1; i <= export_times; i++) { for (int n = 0; n < dataMap.size(); n++) { //打印300000条后切换到下个工作表，可根据需要自行拓展，2百万，3百万...数据一样操作，只要不超过1048576就可以 if(rowNo%sheetNum==0){ sheet = wb.createSheet("第"+(rowNo/sheetNum+1)+"个工作簿");//建立新的sheet对象 sheet = wb.getSheetAt(rowNo/sheetNum); //动态指定当前的工作表 pageRowNo = 0; //每当新建了工作表就将当前工作表的行号重置为0 Row row = sheet.createRow(pageRowNo++); //新建行对象 Cell cell = row.createCell(0); for (int k = 0; k < titles.length; k++) { cell = row.createCell(k); cell.setCellStyle(headCellStyle); cell.setCellValue(new XSSFRichTextString(titles[k])); } } nRow = sheet.createRow(pageRowNo++); //新建行对象 // 打印每行, 列属性的个数 for(int j=0;j<headFields.length;j++){ nCell = nRow.createCell(j); nCell.setCellValue(dataMap.get(n).get(headFields[j])==null?"":dataMap.get(n).get(headFields[j]).toString()); } rowNo++; } dataList.clear(); params.put("pageNum", i+1); params.put("count", false);//导出时，只让第一次分页查询执行count，后面的查询不执行count，加快执行速度 list = baseService.findByPage(params); pageInfo = new PageInfo(list); dataList = pageInfo.getList(); dataMap = convertListBeanToListMap(dataList, cls); //list<Bean>转成list<Map>，方便通用各个模块 } } else { sheet = wb.createSheet("第"+(rowNo/sheetNum+1)+"个工作簿");//建立新的sheet对象 sheet = wb.getSheetAt(rowNo/sheetNum); //动态指定当前的工作表 pageRowNo = 0; //每当新建了工作表就将当前工作表的行号重置为0 Row row = sheet.createRow(pageRowNo++); //新建行对象 Cell cell = row.createCell(0); for (int k = 0; k < titles.length; k++) { cell = row.createCell(k); cell.setCellStyle(headCellStyle); cell.setCellValue(new XSSFRichTextString(titles[k])); } nRow = sheet.createRow(pageRowNo++); //新建行对象 } long finishedTime = System.currentTimeMillis(); // 处理完成时间 log.info("exportExcel finished execute time: {}s", (finishedTime - startTime) / 1000); long stopTime = System.currentTimeMillis(); // 写文件时间 log.info("exportExcel write xlsx file time: {}s", (stopTime - startTime) / 1000 ); ByteArrayOutputStream bos = null; InputStream is = null; try { bos = new ByteArrayOutputStream(); wb.write(bos); byte[] barray = bos.toByteArray(); is = new ByteArrayInputStream(barray); path = OSSUtil.upload(is, fileName, "xlsx"); } catch (Exception e) { e.printStackTrace(); } finally { if (wb != null){ try { wb.close(); } catch (Exception e) { e.printStackTrace(); } } if (bos != null) { try { bos.close(); } catch (Exception e) { e.printStackTrace(); } } if (is != null) { try { is.close(); } catch (Exception e) { e.printStackTrace(); } } } return path; } public static String exportExcelAndUploadOSS(String fileName, Map<String, Object> params, BaseService baseService, String methodName, Class cls, String[] titles, String[] headFields, boolean isDownload) throws Exception { String path = null; long startTime = System.currentTimeMillis(); // 开始时间 Sheet sheet = null; // 工作表对象 Row nRow = null; // 行对象 Cell nCell = null; // 列对象 int pageSize = 10000; //每次查询的数据量 params.put("pageSize", pageSize); params.put("pageNum", 1); Class clz = baseService.getClass(); Object obj = clz.newInstance(); //获取方法 Method m = clz.getDeclaredMethod(methodName, Map.class); //调用方法 List list = (List) m.invoke(obj, params); // Method method2 = baseService.getClass().getMethod("methodName", Class.forName("java.util.Map")); // List list = (List) method2.invoke( params); // List list = baseService.findByPage(params); PageInfo pageInfo = new PageInfo(list); List dataList = pageInfo.getList(); List<Map<String, Object>> dataMap = convertListBeanToListMap(dataList, cls); // 内存中只创建500个对象，写临时文件，当超过500条，就将内存中不用的对象释放。 Workbook wb = new SXSSFWorkbook(500); // 关键语句 Font font = wb.createFont(); font.setBold(true); CellStyle headCellStyle = wb.createCellStyle(); headCellStyle.setFont(font); int total = (int) pageInfo.getTotal(); //每个sheet保存多少行 int sheetNum = 1000000; int rowNo = 0; // 总行号 int pageRowNo = 0; // 页行号 // 根据行数求数据提取次数 int export_times = total % pageSize > 0 ? total / pageSize + 1 :total / pageSize; if(export_times > 0){ for (int i = 1; i <= export_times; i++) { for (int n = 0; n < dataMap.size(); n++) { //打印300000条后切换到下个工作表，可根据需要自行拓展，2百万，3百万...数据一样操作，只要不超过1048576就可以 if(rowNo%sheetNum==0){ sheet = wb.createSheet("第"+(rowNo/sheetNum+1)+"个工作簿");//建立新的sheet对象 sheet = wb.getSheetAt(rowNo/sheetNum); //动态指定当前的工作表 pageRowNo = 0; //每当新建了工作表就将当前工作表的行号重置为0 Row row = sheet.createRow(pageRowNo++); //新建行对象 Cell cell = row.createCell(0); for (int k = 0; k < titles.length; k++) { cell = row.createCell(k); cell.setCellStyle(headCellStyle); cell.setCellValue(new XSSFRichTextString(titles[k])); } } nRow = sheet.createRow(pageRowNo++); //新建行对象 // 打印每行, 列属性的个数 for(int j=0;j<headFields.length;j++){ nCell = nRow.createCell(j); nCell.setCellValue(dataMap.get(n).get(headFields[j])==null?"":dataMap.get(n).get(headFields[j]).toString()); } rowNo++; } dataList.clear(); params.put("pageNum", i+1); params.put("count", false);//导出时，只让第一次分页查询执行count，后面的查询不执行count，加快执行速度 list = (List) m.invoke(obj, params); pageInfo = new PageInfo(list); dataList = pageInfo.getList(); dataMap = convertListBeanToListMap(dataList, cls); //list<Bean>转成list<Map>，方便通用各个模块 } } else { sheet = wb.createSheet("第"+(rowNo/sheetNum+1)+"个工作簿");//建立新的sheet对象 sheet = wb.getSheetAt(rowNo/sheetNum); //动态指定当前的工作表 pageRowNo = 0; //每当新建了工作表就将当前工作表的行号重置为0 Row row = sheet.createRow(pageRowNo++); //新建行对象 Cell cell = row.createCell(0); for (int k = 0; k < titles.length; k++) { cell = row.createCell(k); cell.setCellStyle(headCellStyle); cell.setCellValue(new XSSFRichTextString(titles[k])); } nRow = sheet.createRow(pageRowNo++); //新建行对象 } long finishedTime = System.currentTimeMillis(); // 处理完成时间 log.info("exportExcel finished execute time: {}s", (finishedTime - startTime) / 1000); long stopTime = System.currentTimeMillis(); // 写文件时间 log.info("exportExcel write xlsx file time: {}s", (stopTime - startTime) / 1000 ); ByteArrayOutputStream bos = null; InputStream is = null; try { bos = new ByteArrayOutputStream(); wb.write(bos); byte[] barray = bos.toByteArray(); is = new ByteArrayInputStream(barray); path = OSSUtil.upload(is, fileName, "xlsx"); } catch (Exception e) { e.printStackTrace(); } finally { if (wb != null){ try { wb.close(); } catch (Exception e) { e.printStackTrace(); } } if (bos != null) { try { bos.close(); } catch (Exception e) { e.printStackTrace(); } } if (is != null) { try { is.close(); } catch (Exception e) { e.printStackTrace(); } } } return path; } public static void main(String[] args) throws Exception { long beginTime = System.currentTimeMillis(); // List[] list = getDataFromExcel("G:"+ File.separator +"初始化业务员8 - 副本.xlsx", 0); // List[] list = getDataFromExcelByCache("G:"+ File.separator +"初始化业务员8.xlsx", 0); // List[] list = getDataFromExcelByCache("G:"+ File.separator +"初始化业务员8 - 副本.xlsx", 0); long endTime = System.currentTimeMillis(); log.info("Cast time : " + (endTime - beginTime)); // if (list != null && list.length > 0){ // for (int i = 0; i < list.length; i++) { // System.out.println(list[i]); // } // } } /** * 读取出filePath中的所有数据信息 * @param filePath excel文件的绝对路径 * @param sheetIndex 第几个sheet * @throws IOException * */ public static List[] getDataFromExcelByCache(InputStream fis, int sheetIndex) throws Exception { List[] data = null; Workbook wookbook = null; try { //获取一个绝对地址的流 // fis = new FileInputStream(filePath); wookbook = StreamingReader.builder() .rowCacheSize(100) //缓存到内存中的行数，默认是10 .bufferSize(4096) //读取资源时，缓存到内存的字节大小，默认是1024 .open(fis); //打开资源，必须，可以是InputStream或者是File，注意：只能打开XLSX格式的文件 Sheet sheet = wookbook.getSheetAt(sheetIndex); //获得数据的总行数 int totalRowNum = sheet.getLastRowNum(); log.info("====总行数"+totalRowNum); // if (totalRowNum > 200000) { // throw new G7sException(ErrorEnum.ERROR_DEFAULT.getErrorCode(), "Excel读取数量不能大于200000行"); // } data = new List[totalRowNum]; List<Object> rowData = null; int totalCell = 0; //遍历所有的行 for (Row row : sheet) { if(row.getRowNum() == 0) { totalCell = row.getLastCellNum(); } //遍历所有的列 if (row.getRowNum() > 0) { rowData = new ArrayList<Object>(); for (int i = 0; i < totalCell; i++) { if(row == null || row.getCell(i) == null) { rowData.add(""); } else { rowData.add(row.getCell(i).getStringCellValue()); } } data[row.getRowNum()-1] = rowData; } } } catch (Exception e) { log.error("excel读取报错：{}", e.getMessage()); } finally { if (fis != null) { try { fis.close(); } catch (Exception e) { e.printStackTrace(); } } if (wookbook != null) { try { wookbook.close(); } catch (Exception e) { e.printStackTrace(); } } } return data; } /** * 读取出filePath中的所有数据信息 * @param filePath excel文件的绝对路径 * @param sheetIndex 第几个sheet * @throws IOException * */ public static List[] getDataFromExcel(String filePath, int sheetIndex) throws IOException { List[] data = null; //判断是否为excel类型文件 if(!filePath.endsWith(".xls")&&!filePath.endsWith(".xlsx")) { throw new G7sException(ErrorEnum.ERROR_DEFAULT.getErrorCode(), "Excel格式无效"); } FileInputStream fis =null; Workbook wookbook = null; try { //获取一个绝对地址的流 fis = new FileInputStream(filePath); if(filePath.endsWith(".xls")) { //2003版本的excel，用.xls结尾 wookbook = new HSSFWorkbook(fis); } else if(filePath.endsWith(".xlsx")) { //2007版本的excel，用.xlsx结尾 wookbook = new XSSFWorkbook(fis); } //得到一个工作表 Sheet sheet = wookbook.getSheetAt(sheetIndex); //获得表头 Row rowHead = sheet.getRow(0); log.info("表头的数量：" + rowHead.getPhysicalNumberOfCells()); //获得数据的总行数 int totalRowNum = sheet.getLastRowNum(); log.info("总数量："+totalRowNum); // if (totalRowNum > 200000) { // throw new G7sException(ErrorEnum.ERROR_DEFAULT.getErrorCode(), "Excel读取数量不能大于200000行"); // } data = new List[totalRowNum]; List<Object> rowData = null; //获得所有数据 for(int i = 1 ; i <= totalRowNum ; i++) { //获得第i行对象 Row row = sheet.getRow(i); // //获得获得第i行第0列的 String类型对象 // Cell cell = row.getCell((short)0); // name = cell.getStringCellValue().toString(); // // //获得一个数字类型的数据 // cell = row.getCell((short)1); // latitude = cell.getStringCellValue(); rowData = new ArrayList<Object>(); for (int k = 0; k < row.getLastCellNum(); k++) { Cell cell = row.getCell((short)k); rowData.add(cell); } data[i-1] = rowData; } } catch (Exception e) { log.error("excel读取报错：{}", e.getMessage()); } finally { if (fis != null) { fis.close(); } if (wookbook != null) { wookbook.close(); } } return data; } /** * 将 List<JavaBean>对象转化为List<Map> * @param beanList * @return * @throws Exception */ public static <T> List<Map<String, Object>> convertListBeanToListMap(List<T> beanList, Class<T> T) throws Exception { List<Map<String, Object>> mapList = new ArrayList<>(); for (int i = 0, n = beanList.size(); i < n; i++) { Object bean = beanList.get(i); Map<String, Object> map = convertBeanToMap(bean); mapList.add(map); } return mapList; } /** * 将一个 JavaBean 对象转化为一个 Map * @param bean * @return * @throws IntrospectionException * @throws IllegalAccessException * @throws InvocationTargetException */ public static Map<String, Object> convertBeanToMap(Object bean) throws IntrospectionException, IllegalAccessException, InvocationTargetException { Class<? extends Object> type = bean.getClass(); Map<String, Object> returnMap = new HashMap<>(); BeanInfo beanInfo = Introspector.getBeanInfo(type); PropertyDescriptor[] propertyDescriptors = beanInfo.getPropertyDescriptors(); for (int i = 0; i < propertyDescriptors.length; i++) { PropertyDescriptor descriptor = propertyDescriptors[i]; String propertyName = descriptor.getName(); if (!"class".equals(propertyName)) { Method readMethod = descriptor.getReadMethod(); Object result = readMethod.invoke(bean, new Object[0]); if (result != null) { returnMap.put(propertyName, result); } else { returnMap.put(propertyName, null); } } } return returnMap; } public static void exportExcel(String filePath, List list, Class cls, String[] titles, String[] headFields, boolean isDownload) throws Exception { long startTime = System.currentTimeMillis(); // 开始时间 Sheet sheet = null; // 工作表对象 Row nRow = null; // 行对象 Cell nCell = null; // 列对象 // 内存中只创建500个对象，写临时文件，当超过500条，就将内存中不用的对象释放。 Workbook wb = new SXSSFWorkbook(500); // 关键语句 Font font = wb.createFont(); font.setBold(true); CellStyle headCellStyle = wb.createCellStyle(); headCellStyle.setFont(font); //每个sheet保存多少行 int sheetNum = 1000000; int rowNo = 0; // 总行号 int pageRowNo = 0; // 页行号 if(list != null && list.size() > 0) { PageInfo pageInfo = new PageInfo(list); List dataList = pageInfo.getList(); List<Map<String, Object>> dataMap = convertListBeanToListMap(dataList, cls); for (int n = 0; n < dataMap.size(); n++) { //打印300000条后切换到下个工作表，可根据需要自行拓展，2百万，3百万...数据一样操作，只要不超过1048576就可以 if(rowNo%sheetNum==0){ sheet = wb.createSheet("第"+(rowNo/sheetNum+1)+"个工作簿");//建立新的sheet对象 sheet = wb.getSheetAt(rowNo/sheetNum); //动态指定当前的工作表 pageRowNo = 0; //每当新建了工作表就将当前工作表的行号重置为0 Row row = sheet.createRow(pageRowNo++); //新建行对象 Cell cell = row.createCell(0); for (int k = 0; k < titles.length; k++) { cell = row.createCell(k); cell.setCellStyle(headCellStyle); cell.setCellValue(new XSSFRichTextString(titles[k])); } } nRow = sheet.createRow(pageRowNo++); //新建行对象 // 打印每行, 列属性的个数 for(int j=0;j<headFields.length;j++){ nCell = nRow.createCell(j); nCell.setCellValue(dataMap.get(n).get(headFields[j])==null?"":dataMap.get(n).get(headFields[j]).toString()); } rowNo++; } dataList.clear(); long finishedTime = System.currentTimeMillis(); // 处理完成时间 log.info("exportExcel finished execute time: {}s", (finishedTime - startTime) / 1000); } else { sheet = wb.createSheet("第1个工作簿");//建立新的sheet对象 sheet = wb.getSheetAt(0); //动态指定当前的工作表 Row row = sheet.createRow(pageRowNo++); //新建行对象 Cell cell = row.createCell(0); for (int k = 0; k < titles.length; k++) { cell = row.createCell(k); cell.setCellStyle(headCellStyle); cell.setCellValue(new XSSFRichTextString(titles[k])); } nRow = sheet.createRow(pageRowNo++); //新建行对象 } FileOutputStream fOut = new FileOutputStream(filePath); if (fOut != null) { try { wb.write(fOut); fOut.flush(); // 刷新缓冲区 } catch (Exception e) { e.printStackTrace(); } fOut.close(); } if (wb != null){ try { wb.close(); } catch (Exception e) { e.printStackTrace(); } } long stopTime = System.currentTimeMillis(); // 写文件时间 log.info("exportExcel write xlsx file time: {}s", (stopTime - startTime) / 1000 ); //isDownload为true时，让浏览器弹出下载文件 if (isDownload) { HttpServletRequest request = ((ServletRequestAttributes)RequestContextHolder.getRequestAttributes()).getRequest(); HttpServletResponse response = ((ServletRequestAttributes)RequestContextHolder.getRequestAttributes()).getResponse(); try { File file = new File(filePath); String fileName = file.getName();// 获取日志文件名称 InputStream fis = new BufferedInputStream(new FileInputStream(filePath)); byte[] buffer = new byte[fis.available()]; fis.read(buffer); fis.close(); response.reset(); //然后转换编码格式为utf-8,保证不出现乱码,这个文件名称用于浏览器的下载框中自动显示的文件名 // 判断是否火狐浏览器 String agent = request.getHeader("User-Agent"); boolean isFirefox = (agent != null && agent.contains("Firefox")); if (isFirefox) { fileName = new String(fileName.getBytes("UTF-8"), "ISO-8859-1"); } else { fileName = URLEncoder.encode(fileName, "UTF8"); } response.addHeader("Content-Disposition", "attachment;filename=" + fileName); response.addHeader("Content-Length", "" + file.length()); OutputStream os = new BufferedOutputStream(response.getOutputStream()); response.setContentType("application/octet-stream"); os.write(buffer);// 输出文件 os.flush(); os.close(); } catch (Exception e) { log.error("exportExcel download出错: {}", e.getMessage()); } } } public static String exportListExcelAndUpload(String fileName, List list, Class cls, String[] titles, String[] headFields, boolean isDownload) throws Exception { long startTime = System.currentTimeMillis(); // 开始时间 Sheet sheet = null; // 工作表对象 Row nRow = null; // 行对象 Cell nCell = null; // 列对象 String path = null; // 内存中只创建500个对象，写临时文件，当超过500条，就将内存中不用的对象释放。 Workbook wb = new SXSSFWorkbook(500); // 关键语句 Font font = wb.createFont(); font.setBold(true); CellStyle headCellStyle = wb.createCellStyle(); headCellStyle.setFont(font); //每个sheet保存多少行 int sheetNum = 1000000; int rowNo = 0; // 总行号 int pageRowNo = 0; // 页行号 if(list != null && list.size() > 0) { PageInfo pageInfo = new PageInfo(list); List dataList = pageInfo.getList(); List<Map<String, Object>> dataMap = convertListBeanToListMap(dataList, cls); for (int n = 0; n < dataMap.size(); n++) { //打印300000条后切换到下个工作表，可根据需要自行拓展，2百万，3百万...数据一样操作，只要不超过1048576就可以 if(rowNo%sheetNum==0){ sheet = wb.createSheet("第"+(rowNo/sheetNum+1)+"个工作簿");//建立新的sheet对象 sheet = wb.getSheetAt(rowNo/sheetNum); //动态指定当前的工作表 pageRowNo = 0; //每当新建了工作表就将当前工作表的行号重置为0 Row row = sheet.createRow(pageRowNo++); //新建行对象 Cell cell = row.createCell(0); for (int k = 0; k < titles.length; k++) { cell = row.createCell(k); cell.setCellStyle(headCellStyle); cell.setCellValue(new XSSFRichTextString(titles[k])); } } nRow = sheet.createRow(pageRowNo++); //新建行对象 // 打印每行, 列属性的个数 for(int j=0;j<headFields.length;j++){ nCell = nRow.createCell(j); nCell.setCellValue(dataMap.get(n).get(headFields[j])==null?"":dataMap.get(n).get(headFields[j]).toString()); } rowNo++; } dataList.clear(); long finishedTime = System.currentTimeMillis(); // 处理完成时间 log.info("exportExcel finished execute time: {}s", (finishedTime - startTime) / 1000); } else { sheet = wb.createSheet("第1个工作簿");//建立新的sheet对象 sheet = wb.getSheetAt(0); //动态指定当前的工作表 Row row = sheet.createRow(pageRowNo++); //新建行对象 Cell cell = row.createCell(0); for (int k = 0; k < titles.length; k++) { cell = row.createCell(k); cell.setCellStyle(headCellStyle); cell.setCellValue(new XSSFRichTextString(titles[k])); } nRow = sheet.createRow(pageRowNo++); //新建行对象 } long stopTime = System.currentTimeMillis(); // 写文件时间 log.info("exportListExcelAndUpload write xlsx file time: {}s", (stopTime - startTime) / 1000 ); ByteArrayOutputStream bos = null; InputStream is = null; try { bos = new ByteArrayOutputStream(); wb.write(bos); byte[] barray = bos.toByteArray(); is = new ByteArrayInputStream(barray); path = OSSUtil.upload(is, fileName, "xlsx"); } catch (Exception e) { e.printStackTrace(); } finally { if (wb != null){ try { wb.close(); } catch (Exception e) { e.printStackTrace(); } } if (bos != null) { try { bos.close(); } catch (Exception e) { e.printStackTrace(); } } if (is != null) { try { is.close(); } catch (Exception e) { e.printStackTrace(); } } } return path; } }