这里写自定义目录标题
- 利用POI进行excel的导入导出
- 引入的jar包
- excel导入
- 主方法：
- 将excel里面的图片转成数据
- xls格式
- xlsx格式
- 将图片数据转成字节流的方式传输到FTP服务器
- 将数据进行传输（FTP方面）
- 这里再另外提供一种方法直接下载到本地
- byteToFile方法的代码
- 提取excel表里的其他数据
- excel导出
- 主方法
- 将数据导出成workBook
- 从FTP服务器获取图片的二进制流
- excel资源
- excel表格下载地址
利用POI进行excel的导入导出
项目背景：需求是商品的导入和导出，excel里面有图片，需要讲图片上传到FTP服务器；并且，导出的时候的还需要讲图片导出到excel表格里面。
引入的jar包
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>${poi-version}</version>
<scope>system</scope>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>${poi-version}</version>
<scope>system</scope>
</dependency><poi-version>3.17</poi-version>excel导入
这里直接引入我项目里面写的代码，测试代码后面也会贴出来。
主方法：
public Map<String, Object> importExcel(MultipartFile file1) {
//获取文件的名称
String originalFilename = "G:\\whalecloud\\电信项目" + File.separator + "商品导入导出模板(5).xlsx";
// String originalFilename = "G:\\whalecloud\\电信项目" + File.separator + "工作簿1.xls";
// String originalFilename = file.getOriginalFilename();
// String originalFilename = file.getName();
if (!originalFilename.endsWith(".xls") && !originalFilename.endsWith(".xlsx")) {
return ResultTopVo.returnFailMp("文件不是excel类型");
}
try {
//获取excel的workbook
Workbook wookbook = null;
Sheet sheet = null;
//获取一个绝对地址的流
InputStream fis = new FileInputStream(originalFilename);
// InputStream fis = file.getInputStream();
Map<String, PictureData> maplist = null;
if (originalFilename.endsWith(".xls")) {
//2003版本的excel，用.xls结尾
wookbook = WorkbookFactory.create(fis);
sheet = wookbook.getSheetAt(0);
maplist = PoiExcelUtil.getXlsPictures((HSSFSheet) sheet);
} else {
//2007版本的excel，用.xlsx结尾
// wookbook = WorkbookFactory.create(fis);
wookbook = new XSSFWorkbook(fis);
sheet = wookbook.getSheetAt(0);
maplist = PoiExcelUtil.getXlsxPictures((XSSFSheet) sheet);
}
Map<String, Object> ftpParams = new HashMap<>();
//从数据库获取配置信息
String param = systemAttrSpecService.getAttrValueStr("PICTURE_FTP_PARAMS");
ftpParams = JsonUtil.jsonStrToMap(param);
//文件传输到FTP服务器
Map<String, Object> picPathParams = PoiExcelUtil.transferImgToFtp(maplist, ftpParams);
//解析sheet页的其他内容的值，并把返回的图片服务地址进行填充
List<ProductExcelDto> productExcelDtos = resolveExcelToEntity(sheet, picPathParams);
if (productExcelDtos.size() > 0) {
// 这里可以利用excel的表格，多建立一个key的索引
Map<String, Object> resultMap = transferEntityToSql(productExcelDtos);
return ResultTopVo.returnMp(resultMap);
} else {
return ResultTopVo.returnFailMp("导入报错,请检查excel格式");
}
} catch (Exception e) {
e.printStackTrace();
return ResultTopVo.returnFailMp("导入报错");
}
}将excel里面的图片转成数据
xls格式
/**
* 获取图片和位置 (xls)
*
* @param sheet
* @return
* @throws IOException
*/
public static Map<String, PictureData> getXlsPictures(HSSFSheet sheet) throws IOException {
Map<String, PictureData> map = new HashMap<String, PictureData>();
List<HSSFShape> list = sheet.getDrawingPatriarch().getChildren();
for (HSSFShape shape : list) {
if (shape instanceof HSSFPicture) {
HSSFPicture picture = (HSSFPicture) shape;
HSSFClientAnchor cAnchor = (HSSFClientAnchor) picture.getAnchor();
PictureData pdata = picture.getPictureData();
// 行号-列号
String key = cAnchor.getRow1() + "-" + cAnchor.getCol1();
map.put(key, pdata);
}
}
return map;
}xlsx格式
/**
* 获取图片和位置 (xlsx)
*
* @param sheet
* @return
* @throws IOException
*/
public static Map<String, PictureData> getXlsxPictures(XSSFSheet sheet) throws IOException {
Map<String, PictureData> map = new HashMap<String, PictureData>();
for (XSSFShape shape : sheet.getDrawingPatriarch().getShapes()) {
XSSFClientAnchor anchor = (XSSFClientAnchor) shape.getAnchor();
if (shape instanceof XSSFPicture) {
XSSFPicture pic = (XSSFPicture) shape;
//获取行编号
int row = anchor.getRow2();
//获取列编号
int col = anchor.getCol2();
map.put(row+"-"+col, pic.getPictureData());
}
}
return map;
}将图片数据转成字节流的方式传输到FTP服务器
/**
* 传输图片到FTP服务器
* @param sheetList 图片内容
* @param ftpParams FTP服务器参数
*/
public static Map<String, Object> transferImgToFtp(Map<String, PictureData> sheetList, Map<String, Object> ftpParams) {
Object key[] = sheetList.keySet().toArray();
String filePath = "";
//返回参数
Map<String, Object> resultMap = new HashMap<>();
for (int i = 0; i < sheetList.size(); i++) {
// 获取图片流
PictureData pic = sheetList.get(key[i]);
// 获取图片索引
String picName = key[i].toString();
// 获取图片格式
String ext = pic.suggestFileExtension();
//图片类型
String imgType = String.valueOf(pic.getPictureType());
String originalFileName = "pic-"+picName;
byte[] data = pic.getData();
//传输图片到FTP服务器
String uploadFilePath = null;
try {
uploadFilePath = FtpUploadUtil.uploadFile(data,Long.parseLong("000001"),ext, imgType, originalFileName, ftpParams);
} catch (Exception e) {
e.printStackTrace();
}
resultMap.put(picName, uploadFilePath);
}
return resultMap;
}将数据进行传输（FTP方面）
/**
* 上传文件到服务器
*
* @param content 传输的文件
* @param ext 文件类型（png,jpg等等）
* @param imgType 文件类型
* @param originalFileName 文件名称
* @param ftpParams FTP服务器的配置信息
* @return
* @throws Exception
*/
public static String uploadFile(byte[] content, Long offerId, String ext, String imgType,String originalFileName, Map<String, Object> ftpParams) throws Exception {
FTPClient ftp = new FTPClient();
ByteArrayInputStream fileIO = null;
String hostName = String.valueOf(ftpParams.get("hostName"));
int port = Integer.parseInt(String.valueOf(ftpParams.get("port")));
String userName = String.valueOf(ftpParams.get("userName"));
String passWord = String.valueOf(ftpParams.get("passWord"));
String path = String.valueOf(ftpParams.get("path"));
String header = String.valueOf(ftpParams.get("header"));
try {
//处理文件
Long timeStamp = System.currentTimeMillis();
int rand = (int) (Math.random() * 900) + 100;
StringBuffer imgName = new StringBuffer();
imgName.append(imgType).append(String.valueOf(offerId)).append(String.valueOf(timeStamp)).
append(String.valueOf(rand)).append(originalFileName).append(".").append(ext);
// 连接FTP服务器
ftp.connect(hostName, port);
// 下面三行代码必须要，而且不能改变编码格式，否则不能正确下载中文文件
ftp.setControlEncoding("GBK");
FTPClientConfig conf = new FTPClientConfig(FTPClientConfig.SYST_UNIX);
conf.setServerLanguageCode("zh");
// 登录ftp
ftp.login(userName, passWord);
if (!FTPReply.isPositiveCompletion(ftp.getReplyCode())) {
ftp.disconnect();
System.out.println("连接服务器失败");
}
System.out.println("登陆服务器成功,IP:" + hostName);
String realPath = path;
// 转移到指定FTP服务器目录
boolean changeWD = ftp.changeWorkingDirectory(realPath);
if (!changeWD) {
if (!CreateDirecroty(realPath, ftp)) {
throw new Exception("创建远程文件夹失败!");
}
}
// 得到目录的相应文件列表
FTPFile[] fs = ftp.listFiles();
String fileName = imgName.toString();
fileName = FtpUploadUtil.changeName(fileName, fs);
fileName = new String(fileName.getBytes("GBK"), "ISO-8859-1");
realPath = new String(realPath.getBytes("GBK"), "ISO-8859-1");
// 转到指定上传目录
//ftp.changeWorkingDirectory(realPath);
System.out.println("指定文件路径：" + realPath);
// 将上传文件存储到指定目录
ftp.setFileType(FTP.BINARY_FILE_TYPE);
// 如果缺省该句 传输txt正常 但图片和其他格式的文件传输出现乱码
fileIO = new ByteArrayInputStream(content);
ftp.storeFile(fileName, fileIO);
System.out.println("文件名称：" + fileName);
// 退出ftp
ftp.logout();
System.out.println("上传成功。。。。。。");
return header + fileName;
} catch (Exception e) {
e.printStackTrace();
throw e;
} finally {
// 关闭ftp连接
closeFTP(ftp);
fileIO.close();
}
}这里再另外提供一种方法直接下载到本地
/**
* 图片导出到本地
*
* @param sheetList
* @throws Exception
*/
public static void printImg(Map<String, PictureData> sheetList) throws Exception {
Object key[] = sheetList.keySet().toArray();
String filePath = "";
for (int i = 0; i < sheetList.size(); i++) {
// 获取图片流
PictureData pic = sheetList.get(key[i]);
// 获取图片索引
String picName = key[i].toString();
// 获取图片格式
String ext = pic.suggestFileExtension();
byte[] data = pic.getData();
//图片保存路径
filePath = "D:\\img\\";
String fileName = "pic"+picName+"."+ext;
byteToFile(data, filePath, fileName);
}
}byteToFile方法的代码
/**
* 根据byte数组，生成文件
* @param filePath 文件存放路径
* @param fileName 文件名称
*/
public static void byteToFile(byte[] bytes,String filePath,String fileName){
BufferedOutputStream bos=null;
FileOutputStream fos=null;
File file=null;
try{
File dir=new File(filePath);
if(!dir.exists() && !dir.isDirectory()){//判断文件目录是否存在
dir.mkdirs();
}
file=new File(filePath+fileName);
fos=new FileOutputStream(file);
bos=new BufferedOutputStream(fos);
bos.write(bytes);
}
catch(Exception e){
e.printStackTrace();
}
finally{
try{
if(bos != null){
bos.close();
}
if(fos != null){
fos.close();
}
}
catch(Exception e){
e.printStackTrace();
}
}
}提取excel表里的其他数据
/**
* 将excel的数据解析成实体类
*
* @param sheet
* @param picPathParams
* @return
*/
public List<ProductExcelDto> resolveExcelToEntity(Sheet sheet, Map<String, Object> picPathParams) {
List<ProductExcelDto> excelDtos = new ArrayList<>();
ProductExcelDto dto = null;
//获取数据的总行数
int totalRowNum = sheet.getLastRowNum();
for (int i = 3; i <= totalRowNum; i++) {
dto = new ProductExcelDto();
//获取弟i行的数据
Row row = sheet.getRow(i);
Cell cell = null;
//商品编码
cell = row.getCell(1);
if (cell != null) {
cell.setCellType(CellType.STRING);
dto.setOfferCode(cell.getStringCellValue());
}
//商品备注
cell = row.getCell(2);
if (cell != null) {
cell.setCellType(CellType.STRING);
dto.setOfferName(cell.getStringCellValue());
}
//关键字
cell = row.getCell(3);
if (cell != null) {
cell.setCellType(CellType.STRING);
dto.setKeyword(cell.getStringCellValue());
}
//是否显示红点
cell = row.getCell(4);
if (cell != null) {
cell.setCellType(CellType.STRING);
String isReddot = systemAttrSpecService.getAttrValueByValueName("IS_REDDOT", cell.getStringCellValue()).getAttrValueCode();
dto.setIsReddot(isReddot);
}
//省份
cell = row.getCell(5);
if (cell != null) {
cell.setCellType(CellType.STRING);
Map<String, Object> provinceParam = new HashMap<>();
provinceParam.put("tsName", cell.getStringCellValue());
Territory territory = terrritoryMapper.queryEntityByNameOrBm(provinceParam);
dto.setProvince(territory.getTsBm());
}
//城市
cell = row.getCell(6);
if (cell != null) {
cell.setCellType(CellType.STRING);
dto.setCities(cell.getStringCellValue());
}
//商品金额
cell = row.getCell(7);
if (cell != null) {
cell.setCellType(CellType.STRING);
dto.setOfferPrice(cell.getStringCellValue());
}
//折扣比率
cell = row.getCell(8);
if (cell != null) {
cell.setCellType(CellType.STRING);
dto.setOfferDiscount(cell.getStringCellValue());
}
//角标类型
cell = row.getCell(9);
if (cell != null) {
cell.setCellType(CellType.STRING);
dto.setAngleType(cell.getStringCellValue());
}
//平台
cell = row.getCell(10);
if (cell != null) {
cell.setCellType(CellType.STRING);
String offerShop = systemAttrSpecService.getAttrValueByValueName("OFFER_SHOP", cell.getStringCellValue()).getAttrValueCode();
dto.setOfferShop(offerShop);
}
//最大版本
cell = row.getCell(11);
if (cell != null) {
cell.setCellType(CellType.STRING);
dto.setMaxVersion(cell.getStringCellValue());
}
//最小版本
cell = row.getCell(12);
if (cell != null) {
cell.setCellType(CellType.STRING);
dto.setMinVersion(cell.getStringCellValue());
}
//跳转类型
cell = row.getCell(13);
if (cell != null) {
cell.setCellType(CellType.STRING);
dto.setSkipType(cell.getStringCellValue());
}
//跳转地址
cell = row.getCell(14);
if (cell != null) {
cell.setCellType(CellType.STRING);
dto.setSkipAddress(cell.getStringCellValue());
}
//生效时间
cell = row.getCell(15);
if (cell != null) {
cell.setCellType(CellType.STRING);
dto.setEffDate(cell.getStringCellValue());
}
//失效时间
cell = row.getCell(16);
if (cell != null) {
cell.setCellType(CellType.STRING);
dto.setExpDate(cell.getStringCellValue());
}
//推荐位
cell = row.getCell(17);
if (cell != null) {
cell.setCellType(CellType.STRING);
dto.setRecommendId(cell.getStringCellValue());
}
//标题
cell = row.getCell(18);
if (cell != null) {
cell.setCellType(CellType.STRING);
dto.setOfferTitle(cell.getStringCellValue());
}
//图标地址
cell = row.getCell(19);
dto.setIconArea(i + "-" + "19");
//副标题
cell = row.getCell(20);
if (cell != null) {
cell.setCellType(CellType.STRING);
dto.setOfferSubTitle(cell.getStringCellValue());
}
//图片地址
cell = row.getCell(21);
dto.setPictureArea(i + "-" + "21");
excelDtos.add(dto);
}
//对图片地址进行赋值操作
for (ProductExcelDto excelDto : excelDtos) {
//根据图片所在位置作为主键
String iconArea = String.valueOf(picPathParams.get(excelDto.getIconArea()));
excelDto.setIconAddress(iconArea);
String pictureArea = String.valueOf(picPathParams.get(excelDto.getPictureArea()));
excelDto.setPictureAddress(pictureArea);
}
return excelDtos;
}这里将数据解析入库的方法就不提供了。
excel导出
主方法
public Map<String, Object> exportExcel(List<Map<String, Object>> param) {
//获取需要导出的数据
List<ProductExcelDto> productExcelDtos = getExportData(param);
if (productExcelDtos.size() < 0) {
return ResultTopVo.returnFailMp("参数不全");
}
//导出数据成excel
HSSFWorkbook workbook = exportToXls(productExcelDtos);
//输出Excel文件
OutputStream output = null;
try {
// output = response.getOutputStream();
// response.reset();
// response.setHeader("Content-disposition", "attachment; 商品明细.xls");
// response.setContentType("application/x-xls");
output = new FileOutputStream("d:\\商品明细.xls");
workbook.write(output);
output.close();
} catch (Exception e) {
e.printStackTrace();
return ResultTopVo.returnFailMp("导出失败");
}
return ResultTopVo.success();
}获取数据的方法就不提供了。可以根据具体的业务自己进行写代码。
将数据导出成workBook
/**
* 导出数据到excel
*
* @param
*/
public HSSFWorkbook exportToXls(List<ProductExcelDto> list) {
// 声明一个工作薄
HSSFWorkbook workBook = new HSSFWorkbook();
// 生成一个表格
HSSFSheet sheet = workBook.createSheet();
// sheet.createFreezePane(0, 1, 0, 1);
workBook.setSheetName(0, "商品信息");
CellStyle style = workBook.createCellStyle();
Font font = workBook.getFontAt((short) 0);
font.setCharSet(HSSFFont.DEFAULT_CHARSET);
//更改默认字体大小
font.setFontHeightInPoints((short) 12);
font.setFontName("宋体");
style.setFont(font);
//默认宽高
sheet.setDefaultColumnWidth((short) 20);
sheet.setDefaultRowHeight((short) 2000);
// 创建表格标题行 第一行
HSSFRow titleRow0 = sheet.createRow(0);
titleRow0.setHeight((short) 1000);
for (int i = 0; i < 24; i++) {
HSSFCell cell = titleRow0.createCell(i);
if (i == 0) {
cell.setCellValue("主商品信息");
} else if (i == 3) {
cell.setCellValue("商品详情");
} else {
cell.setCellValue("推荐位");
}
}
//设置表格合并
CellRangeAddress region = new CellRangeAddress(0, 0, 0, 2);
CellRangeAddress region1 = new CellRangeAddress(0, 0, 3, 18);
CellRangeAddress region2 = new CellRangeAddress(0, 0, 19, 23);
sheet.addMergedRegion(region);
sheet.addMergedRegion(region1);
sheet.addMergedRegion(region2);
//第二行
HSSFRow titleRow = sheet.createRow(1);
titleRow.setHeight((short) 1000);
titleRow.createCell(0).setCellValue("主商品编码");
titleRow.createCell(1).setCellValue("主商品类型");
titleRow.createCell(2).setCellValue("主商品名称");
titleRow.createCell(3).setCellValue("商品编码");
titleRow.createCell(4).setCellValue("商品备注");
titleRow.createCell(5).setCellValue("关键字");
titleRow.createCell(6).setCellValue("是否显示红点");
titleRow.createCell(7).setCellValue("省份");
titleRow.createCell(8).setCellValue("城市");
titleRow.createCell(9).setCellValue("商品金额（元）");
titleRow.createCell(10).setCellValue("折扣比率");
titleRow.createCell(11).setCellValue("角标类型");
titleRow.createCell(12).setCellValue("平台");
titleRow.createCell(13).setCellValue("最大版本");
titleRow.createCell(14).setCellValue("最小版本");
titleRow.createCell(15).setCellValue("跳转类型");
titleRow.createCell(16).setCellValue("跳转地址");
titleRow.createCell(17).setCellValue("生效时间");
titleRow.createCell(18).setCellValue("失效时间");
titleRow.createCell(19).setCellValue("推荐位");
titleRow.createCell(20).setCellValue("标题");
titleRow.createCell(21).setCellValue("图标");
titleRow.createCell(22).setCellValue("副标题");
titleRow.createCell(23).setCellValue("图片");
BufferedImage bufferImg = null;//图片
for (int i = 0; i < list.size(); i++) {
ProductExcelDto checkWorkVo = list.get(i);
HSSFRow titleRowNext = sheet.createRow(i + 2);
//主商品信息
titleRowNext.createCell(0).setCellValue(String.valueOf(checkWorkVo.getMainOfferCode() == null ? "": checkWorkVo.getMainOfferCode()));
titleRowNext.createCell(1).setCellValue(String.valueOf(checkWorkVo.getMainOfferType() == null ? "": checkWorkVo.getMainOfferType()));
titleRowNext.createCell(2).setCellValue(String.valueOf(checkWorkVo.getMainOfferName() == null ? "": checkWorkVo.getMainOfferName()));
//商品详情信息
titleRowNext.createCell(3).setCellValue(String.valueOf(checkWorkVo.getOfferCode() == null ? "": checkWorkVo.getOfferCode()));
titleRowNext.createCell(4).setCellValue(String.valueOf(checkWorkVo.getOfferName() == null ? "": checkWorkVo.getOfferName()));
titleRowNext.createCell(5).setCellValue(String.valueOf(checkWorkVo.getKeyword() == null ? "": checkWorkVo.getKeyword()));
titleRowNext.createCell(6).setCellValue(String.valueOf(checkWorkVo.getIsReddot() == null ? "": checkWorkVo.getIsReddot()));
titleRowNext.createCell(7).setCellValue(String.valueOf(checkWorkVo.getProvince() == null ? "": checkWorkVo.getProvince()));
titleRowNext.createCell(8).setCellValue(String.valueOf(checkWorkVo.getCities() == null ? "": checkWorkVo.getCities()));
titleRowNext.createCell(9).setCellValue(String.valueOf(checkWorkVo.getOfferPrice() == null ? "": checkWorkVo.getOfferPrice()));
titleRowNext.createCell(10).setCellValue(String.valueOf(checkWorkVo.getOfferDiscount() == null ? "": checkWorkVo.getOfferDiscount()));
titleRowNext.createCell(11).setCellValue(String.valueOf(checkWorkVo.getAngleType() == null ? "": checkWorkVo.getAngleType()));
titleRowNext.createCell(12).setCellValue(String.valueOf(checkWorkVo.getOfferShop() == null ? "": checkWorkVo.getOfferShop()));
titleRowNext.createCell(13).setCellValue(String.valueOf(checkWorkVo.getMaxVersion() == null ? "": checkWorkVo.getMaxVersion()));
titleRowNext.createCell(14).setCellValue(String.valueOf(checkWorkVo.getMinVersion() == null ? "": checkWorkVo.getMinVersion()));
titleRowNext.createCell(15).setCellValue(String.valueOf(checkWorkVo.getSkipType() == null ? "": checkWorkVo.getSkipType()));
titleRowNext.createCell(16).setCellValue(String.valueOf(checkWorkVo.getSkipAddress() == null ? "": checkWorkVo.getSkipAddress()));
titleRowNext.createCell(17).setCellValue(String.valueOf(checkWorkVo.getEffDate() == null ? "": checkWorkVo.getEffDate()));
titleRowNext.createCell(18).setCellValue(String.valueOf(checkWorkVo.getExpDate() == null ? "": checkWorkVo.getExpDate()));
//推荐位信息
titleRowNext.createCell(19).setCellValue(String.valueOf(checkWorkVo.getRecommendId() == null ? "": checkWorkVo.getRecommendId()));
titleRowNext.createCell(20).setCellValue(String.valueOf(checkWorkVo.getOfferTitle() == null ? "": checkWorkVo.getOfferTitle()));
//图片FTP服务器地址
Map<String, String> ftpParams = new HashMap<>();
//从数据库获取配置信息
String param = systemAttrSpecService.getAttrValueStr("PICTURE_FTP_PARAMS");
ftpParams = JsonUtil.jsonStrToMap(param);
// 利用HSSFPatriarch将图片写入EXCEL
HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
//图标赋值
if (!StringUtil.isEmpty(checkWorkVo.getIconAddress()) & !checkWorkVo.getIconAddress().equals("null")) {
//获取图片路径
byte[] iconBytes = FtpUploadUtil.getFileBytes(ftpParams, checkWorkVo.getIconAddress());
/**
* 该构造函数有8个参数
* 前四个参数是控制图片在单元格的位置，dx1 dy1 表示据当前表格单元的左右距离，dx2, dy2表示图片的高度和宽度
* 后四个参数，前连个表示图片左上角所在的cellNum和 rowNum，后天个参数对应的表示图片右下角所在的cellNum和 rowNum，
* excel中的cellNum和rowNum的index都是从0开始的
*
*/
HSSFClientAnchor anchor = new HSSFClientAnchor(50, 50, 200, 150,
(short) 21, i + 2, (short) 21, i+2);
// 插入图片
patriarch.createPicture(anchor, workBook.addPicture(iconBytes, HSSFWorkbook.PICTURE_TYPE_PNG));
}
titleRowNext.createCell(22).setCellValue(String.valueOf(checkWorkVo.getOfferSubTitle() == null ? "": checkWorkVo.getOfferSubTitle()));
//图片赋值
if (!StringUtil.isEmpty(checkWorkVo.getPictureAddress()) & !checkWorkVo.getPictureAddress().equals("null")) {
byte[] pictureBytes = FtpUploadUtil.getFileBytes(ftpParams, checkWorkVo.getPictureAddress());
HSSFClientAnchor pictureAnchor = new HSSFClientAnchor(50, 50, 200, 150,
(short) 21, i + 2, (short) 21, i+2);
// 插入图片
patriarch.createPicture(pictureAnchor, workBook.addPicture(pictureBytes, HSSFWorkbook.PICTURE_TYPE_PNG));
}
}
return workBook;
}从FTP服务器获取图片的二进制流
/**
* 下载ftp服务器文件方法
*
* @param ftpParams FTPClient的参数
* @param fileName 原文件（路径＋文件名）
* @return
* @throws IOException
*/
public static byte[] getFileBytes(Map<String, String> ftpParams, String fileName) {
String hostName = String.valueOf(ftpParams.get("hostName"));
String port = ftpParams.get("port");
String userName = String.valueOf(ftpParams.get("userName"));
String passWord = String.valueOf(ftpParams.get("passWord"));
String path = String.valueOf(ftpParams.get("path"));
byte[] data = null;
FTPClient ftpClient = null;
InputStream inputStream = null;
//跳转到指定目录
try {
//获取连接
ftpClient = ftpConnection(hostName, port, userName, passWord);
//这里先列出服务器的当前的文件夹，相当于pwd命令
String realPath = ftpClient.printWorkingDirectory().toString().concat(path);
ftpClient.changeWorkingDirectory(realPath);
String fileOriginalName = fileName.substring(fileName.lastIndexOf("/")+1);
String fileFtpName = new String(fileOriginalName.getBytes("GBK"), "iso-8859-1");
ftpClient.setFileType(FTPClient.BINARY_FILE_TYPE);
ftpClient.enterLocalPassiveMode();
InputStream fileStream = ftpClient.retrieveFileStream(fileFtpName);
data = IOUtils.toByteArray(fileStream);
} catch (IOException e) {
e.printStackTrace();
} finally {
if (ftpClient.isConnected()) {
try {
ftpClient.getReply();
ftpClient.disconnect();
} catch (IOException e) {
e.printStackTrace();
}
}
if (null != inputStream) {
try {
inputStream.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
return data;
}至此，这里就结束了，记录一下，这两天的编写过程。
这里列出excel的格式。
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
