package com.test.test;
import org.apache.poi.ss.usermodel.;
import org.apache.poi.xssf.usermodel.;
import org.junit.Test;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.util.ResourceUtils;
import java.io.;
import java.util.;
@SpringBootApplication
public class TestApplication {
/*
如果是xls格式,使用HSSFWorkbook,HSSFSheet,HSSFRow来进行相关操作
如果是xlsx格式,使用XSSFWorkbook,XSSFSheet,XSSFRow来进行相关操作,目前只支持xlsx
*/
public static HashMap readExcelData(String filename, Integer row, Integer column,Integer sheet) throws IOException {
//用于存储Exce读取数据
HashMap<Integer,String> hashMap=new HashMap();
//读取excel数据
File file = ResourceUtils.getFile(filename);
InputStream inputStream = new FileInputStream(file);
XSSFWorkbook xssfWorkbook=new XSSFWorkbook(inputStream);
//获取sheet表格,及读取单元格内容
XSSFSheet xssfSheet=xssfWorkbook.getSheetAt(sheet);
//先将获取的单元格设置为String类型,下面使用getStringCellValue获取单元格内容
Integer cellIndex = 0;
while(cellIndex<=column){
//第一列为空时直接,赋值为空
if (xssfSheet.getRow(row)==null || xssfSheet.getRow(row).getCell(cellIndex)==null){
hashMap.put(cellIndex,"");
cellIndex++;
continue;
}
xssfSheet.getRow(row).getCell(cellIndex).setCellType(CellType.STRING);
String stringValue=xssfSheet.getRow(row).getCell(cellIndex).getStringCellValue();
hashMap.put(cellIndex,stringValue);
cellIndex++;
}
return hashMap;
}
/**
* 获取Excel中的图片
* @param xssfSheet
* @return
*/
public static Map<String, XSSFPictureData> getPictures(XSSFSheet xssfSheet){
Map<String,XSSFPictureData> map=new HashMap<>();
List<XSSFShape> list=xssfSheet.getDrawingPatriarch().getShapes();
for (XSSFShape shape:list){
XSSFPicture picture = (XSSFPicture) shape;
XSSFClientAnchor xssfClientAnchor=(XSSFClientAnchor) picture.getAnchor();
XSSFPictureData pdata = picture.getPictureData();
// 行号-列号
String key = xssfClientAnchor.getRow1() + "-" + xssfClientAnchor.getCol1();
map.put(key, pdata);
}
return map;
}
@Test
public void test() throws IOException{
//第一步找到获取流的方式
String filename="E:\\6.xlsx";
File file = ResourceUtils.getFile(filename);
InputStream inputStream = new FileInputStream(file);
//把流弄成excel
XSSFWorkbook xssfWorkbook=new XSSFWorkbook(inputStream);
//这个指定第一列是图片
Map<String, XSSFPictureData> map=getPictures(xssfWorkbook.getSheetAt(0));
//这个是你获取第几列的第几个图片,你需要根据数量计算
String mapKey="1-0";//指定行和列
//这个是获取图片
XSSFPictureData xssfPictureData= map.get(mapKey);
byte[] data =xssfPictureData.getData();
//这个可以当作我们在导入图片一样的保存到我们服务然后获取他的地址,
FileOutputStream out = new FileOutputStream("D:/test12.png");
out.write(data);
out.close();
}
}