处理excel中图片的流程：
（1）. 将上传的excel保存到临时文件中
（2）.将excel中的图片保存在服务器中，excel中原先是图片的地方，将以img标签《img src=’filePath’/>的形式替换原先的图片（图片的路径最好的绝对路径）
（3）.将会生成一个新的excel，再根据新的excel入库就可以了
步骤如下：
-1.从网盘中下载poi处理excel所需的jar包：http://pan.baidu.com/s/1nvtkT09
-2.新建一个处理图片的工具类
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.Comparator;
import java.util.List;
import java.util.Set;
import java.util.TreeSet;
import javax.servlet.http.HttpServletRequest;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.PictureData;
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.ss.usermodel.WorkbookFactory;
import org.bson.BasicBSONObject;
import org.springframework.web.context.request.RequestContextHolder;
import org.springframework.web.context.request.ServletRequestAttributes;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFClientAnchor;
import org.apache.poi.hssf.usermodel.HSSFPatriarch;
import org.apache.poi.hssf.usermodel.HSSFPicture;
import org.apache.poi.hssf.usermodel.HSSFPictureData;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFShape;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.PictureData;
public class ImgExcelTool extends ExcelCommon
{
Workbook wb = null;
String county = null;
//String timeNum = null;
List<PictureData> pictureDatas = new ArrayList<PictureData>();
List<HSSFPicture> picturePtrs = new ArrayList<HSSFPicture>();
String filePath = null;
String destImgDir = null;
String preUrl = null;
String file_name=null;
public ImgExcelTool(String _filePath,String _destImgDir,String _preUrl,String _file_name)
{
filePath = _filePath;
destImgDir = _destImgDir;
preUrl = _preUrl;
file_name = _file_name;
this.prepareImgs();
}
private void prepareImgs()
{
try
{
wb = WorkbookFactory.create(new File(filePath));
readedAllImg(0);
}
catch(Exception ex)
{
ex.printStackTrace();
}
}
private void readedAllImg(int sheetInd)
{
try
{
pictureDatas = (List<PictureData>) wb.getAllPictures();
HSSFSheet sheet = (HSSFSheet) wb.getSheetAt(sheetInd);
HSSFPatriarch patriarch = sheet.getDrawingPatriarch();
for (HSSFShape shape : patriarch.getChildren())
{
if (shape instanceof HSSFPicture)
{
picturePtrs.add((HSSFPicture) shape);
}
}
}
catch(Exception ex)
{
ex.printStackTrace();
}
}
public void readAllCells(int ind,HttpServletRequest request)
{
Sheet sheet = wb.getSheetAt(0);
int rowNum = sheet.getLastRowNum()+1;
Row row = null;
Cell cell = null;
ExcelCom excel=new ExcelCom();
String basePath=request.getSession().getServletContext().getRealPath("/");
try {
//excel临时文件
Workbook wb = WorkbookFactory.create(new File(basePath+"/excel/upload/e.xls"));
Sheet sheet1 = wb.getSheetAt(0);
for(int r = 0; r<rowNum;r ++)
{
row = sheet.getRow(r);
for(int c = 0;c<row.getPhysicalNumberOfCells();c++)
{
cell = row.getCell(c);
String v=getCellValue(cell);
excel.setCellValue(sheet1, r, c, v);
}
//处理好图片的excel保存路径，之后处理这个excel就好了
OutputStream os = new FileOutputStream(basePath+"/excel/upload/"+file_name);
wb.write(os);
}
} catch (InvalidFormatException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
}
public String getCellValue(Cell cell)
{
try
{
String str = super.getCellValue(cell);
int row = cell.getRowIndex();
int col = cell.getColumnIndex();
AnchorComparator<HSSFPicture> comparator= new AnchorComparator<HSSFPicture>();
TreeSet<HSSFPicture> set = new TreeSet<HSSFPicture>(comparator);
findPicturePtr(set,row,col);
for(HSSFPicture ptr : set)
{
//1.存储图片。
int ind = ptr.getPictureIndex()-1;
PictureData pd = pictureDatas.get(ind);
long commString=IDManager.getCommonID();
String filePath = commString+"."+pd.suggestFileExtension();
String src=destImgDir+filePath;
String url="";
FileOutputStream fOut = new FileOutputStream(src);
fOut.write(pd.getData());
str = str + "<img src='"+preUrl+filePath+"'/>";
}
return str;
}
catch (Exception e) {
e.printStackTrace();
}
return null;
}
private void findPicturePtr(Set set,int row,int col)
{
set.clear();
for(HSSFPicture ptr : picturePtrs)
{
HSSFClientAnchor anchor = (HSSFClientAnchor) ((HSSFShape)ptr).getAnchor();
if(anchor.getRow1() == row && anchor.getCol1() == col)
{
set.add(ptr);
}
}
}
class AnchorComparator<HSSFPicture> implements Comparator<HSSFPicture>
{
public int compare(HSSFPicture p1, HSSFPicture p2)
{
HSSFClientAnchor anchor1 = (HSSFClientAnchor) ((HSSFShape)p1).getAnchor();
HSSFClientAnchor anchor2 = (HSSFClientAnchor) ((HSSFShape)p2).getAnchor();
//先按行排
if(anchor1.getRow1() < anchor2.getRow1())
{
return -1;
}
else if(anchor1.getRow1() > anchor2.getRow1())
{
return 1;
}
//再按列排
else if(anchor1.getCol1()<anchor2.getCol1())
{
return -1;
}
else if(anchor1.getCol1()>anchor2.getCol1())
{
return -1;
}
//在同一行，同一列内，先按y轴排
else if(anchor1.getDy1() < anchor2.getDy1())
{
return -1;
}
else if(anchor1.getDy1() > anchor2.getDy1())
{
return 1;
}
//在同一行，同一列内，先按x轴排
else if(anchor1.getDx1() < anchor2.getDx1())
{
return -1;
}
else if(anchor1.getDx1() > anchor2.getDx1())
{
return 1;
}
return 0;
}
}
public static void main(String[] args)
{
new ImgExcelTool("C:/Users/1.xls","D:/images/","/pay/picture/image/","")
.readAllCells(0);
}
}-3.调用ImgExcelTool类
//生成excel的临时路径，图片的保存路径，图片的相对路径，文件名
new ImgExcelTool(savepath+"imageExcel/"+name,savepath+"picture/image/","picture/image/",name)
.readAllCells(0,request);-4.将刚处理好的excel入库就可以了