SXSSFWorkbook 导出大批量数据和图片到excel

// 将数据放入map类型的集合中List<Map<String, Object>> dataList = new ArrayList<Map<String, Object>>();String[] assetHeadTemp = { "商品名称", "销量", "库存", "周转率", "图片" };String[] assetNameTemp = { "maktx", "saleCount", "stockCount", "rotationRate", "matnr" };Workbook wb = new SXSSFWorkbook(100);Sheet sheet = wb.createSheet("Sheet1");Row row;Cell cell;// 图片字节数组byte[] imgByte = null;// excel样式CellStyle style = wb.createCellStyle();style.setAlignment(CellStyle.ALIGN_CENTER);// 水平style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);// 垂直// 输出表头row = sheet.createRow(0);for (int i = 0; i < assetHeadTemp.length; i++) {cell = row.createCell(i);cell.setCellValue(assetHeadTemp[i]);cell.setCellStyle(style);}// 输出内容int rowIndex = 1;for (Map<String, Object> map : dataList) {row = sheet.createRow(rowIndex++);// 设置行高row.setHeightInPoints((short) 70);int index = 0;File file = null;String imgPath;for (int i = 0; i < assetNameTemp.length; i++) {cell = row.createCell(i);// 设置列宽sheet.setColumnWidth(i, 256 * 25);// 输出图片到第5列if (i == 4) {imgByte = null;// 输出图片 imgPath = "E:\\图片测试\\materialThumbnail\\"+ map.get(assetNameTemp[4]).toString() + ".jpg";file = new File(imgPath);if (file.exists()) {// 图片转化为字节数组imgByte = IOUtils.toByteArray(new FileInputStream(imgPath));}if (imgByte != null) {// 图片存在即输出图片int addPicture = wb.addPicture(imgByte, wb.PICTURE_TYPE_JPEG);Drawing drawing = sheet.createDrawingPatriarch();CreationHelper helper = wb.getCreationHelper();ClientAnchor anchor = helper.createClientAnchor();anchor.setRow1(rowIndex - 1);anchor.setCol1(i);// 指定我想要的长宽double standardWidth = 100;double standardHeight = 100;// 计算单元格的长宽double cellWidth = sheet.getColumnWidthInPixels(cell.getColumnIndex());double cellHeight = cell.getRow().getHeightInPoints() / 72 * 96;// 计算需要的长宽比例的系数double a = standardWidth / cellWidth;double b = standardHeight / cellHeight;Picture picture = drawing.createPicture(anchor, addPicture);picture.resize(a, b);}} else {// 输出文字cell.setCellValue(map.get(assetNameTemp[index]) != null ? map.get(assetNameTemp[index]).toString() : "");cell.setCellStyle(style);index++;}}}

OutputStream out = sftpHandler.uploadFile(name);// 上传文件到服务器wb.write(out);

if (out != null) {try {out.close();} catch (IOException e) {}}if (wb != null) {wb.close();}