javaWeb导入excel表格数据,表格包含图片,保存到数据库中
导入excel表格数据
做个记录吧,方便查找
导入后缀是xls的表格的方法
private List<FoodBean> readXls(String path,HttpServletRequest request) throws IOException, InvalidFormatException {
HSSFWorkbook hssfWorkbook = null;
try {
InputStream is = new FileInputStream(path);
hssfWorkbook = new HSSFWorkbook(is);
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
BigDecimal bd2 = null;
FoodBean foodBean = null;
Sheet sheet = null;
List<FoodBean> list = new ArrayList<FoodBean>();
File file = new File(path);
// 创建流
InputStream input = new FileInputStream(file);
HSSFWorkbook wb = (HSSFWorkbook) WorkbookFactory.create(input);
String name=null;
PictureData sheetIndexPicMap=null;
if (hssfWorkbook != null) {
for (int numSheet = 0; numSheet < hssfWorkbook.getNumberOfSheets(); numSheet++) {
HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(numSheet);
if (hssfSheet == null) {
continue;
}
for (int rowNum = 1; rowNum <= hssfSheet.getLastRowNum(); rowNum++) {
HSSFRow hssfRow = hssfSheet.getRow(rowNum);
sheet=wb.getSheetAt(numSheet);
// map等待存储excel图片
sheetIndexPicMap = getSheetPictrues03(rowNum, (HSSFSheet)sheet);
if (hssfRow != null) {
foodBean = new FoodBean();
//默认图片
if(sheetIndexPicMap==null){
name="/upload/xxx/more.png";
}else{
name= printImg(sheetIndexPicMap,request).get(0);
}
HSSFCell foodname = hssfRow.getCell(1);
HSSFCell price = hssfRow.getCell(2);
foodBean.setImgurl(name);
foodBean.setFoodname(getValue(foodname));
bd2 = new BigDecimal(price.toString());
foodBean.setPrice(bd2.toPlainString());
list.add(foodBean);
}
}
}
}
return list;
}
//xls表格图片信息
public static PictureData getSheetPictrues03(int sheetNum,
HSSFSheet sheet) {
Map<Integer, PictureData> result = new HashMap<Integer, PictureData>();
if(sheet.getDrawingPatriarch()!=null){
List<HSSFShape> list = sheet.getDrawingPatriarch().getChildren();
for (HSSFShape shape : list) {
if (shape instanceof HSSFPicture) {
HSSFPicture picture = (HSSFPicture) shape;
HSSFClientAnchor cAnchor = (HSSFClientAnchor) picture.getAnchor();
HSSFPictureData pdata = picture.getPictureData();
Integer key = cAnchor.getRow1(); // 行号
result.put(key, pdata);
}
}
}
return result.get(sheetNum);
}
导入xlsx后缀的表格方法
private List<FoodBean> readXlsx(String path,HttpServletRequest request) throws IOException, InvalidFormatException {
XSSFWorkbook xssfWorkbook = null;
try {
InputStream is = new FileInputStream(path);
xssfWorkbook = new XSSFWorkbook(is);
} catch (IOException e) {
e.printStackTrace();
}
BigDecimal bd2 = null;
FoodBean foodBean = null;
List<FoodBean> list = new ArrayList<FoodBean>();
Sheet sheet = null;
String name=null;
PictureData sheetIndexPicMap=null;
File file = new File(path);
// 创建流
InputStream input = new FileInputStream(file);
XSSFWorkbook wb= (XSSFWorkbook)WorkbookFactory.create(input);
if (xssfWorkbook != null) {
// Read the Sheet
for (int numSheet = 0; numSheet < xssfWorkbook.getNumberOfSheets(); numSheet++) {
XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(numSheet);
if (xssfSheet == null) {
continue;
}
for (int rowNum = 1; rowNum <= xssfSheet.getLastRowNum(); rowNum++) {
XSSFRow xssfRow = xssfSheet.getRow(rowNum);
sheet=wb.getSheetAt(numSheet);
sheetIndexPicMap = getSheetPictrues07(rowNum, (XSSFSheet) sheet);
if (xssfRow != null) {
foodBean = new FoodBean();
if(sheetIndexPicMap==null){
name="/upload/xxx/more.png.png";
}else{
name= printImg(sheetIndexPicMap,request).get(0);
}
XSSFCell foodname = xssfRow.getCell(1);
XSSFCell price = xssfRow.getCell(2);
foodBean.setFoodname(getValue(foodname));
bd2 = new BigDecimal(price.toString());
foodBean.setPrice(bd2.toPlainString());
foodBean.setImgurl(name);
list.add(foodBean);
}
}
}
}
return list;
}
//xlsx版本表格的图片信息
public static PictureData getSheetPictrues07(int sheetNum,
XSSFSheet sheet) {
Map<Integer, PictureData> sheetIndexPicMap = new HashMap<Integer, PictureData>();
for (POIXMLDocumentPart dr : sheet.getRelations()) {
if (dr instanceof XSSFDrawing) {
XSSFDrawing drawing = (XSSFDrawing) dr;
List<XSSFShape> shapes = drawing.getShapes();
for (XSSFShape shape : shapes) {
XSSFPicture pic = (XSSFPicture) shape;
XSSFClientAnchor anchor = pic.getPreferredSize();
CTMarker ctMarker = anchor.getFrom();
Integer picIndex =ctMarker.getRow() ;
sheetIndexPicMap.put(picIndex, pic.getPictureData());
}
}
}
return sheetIndexPicMap.get(sheetNum);
}
图片上传
//图片上传
public static List<String> printImg(PictureData picData,HttpServletRequest request) throws IOException {
List<String> name=new ArrayList<String>();
// 获取图片流
PictureData pic = picData;
// 设置图片名字
String picName = picData.getData().toString().substring(1);
// 获取图片格式
String ext = pic.suggestFileExtension();
byte[] data = pic.getData();
String path = request.getSession().getServletContext().getRealPath("upload") + "\\foodImg" + "\\" + picName+"."+ext;
System.out.println("dddddd+"+path);
System.out.println("picNamepicName+"+picName);
name.add("\\upload"+"\\xxx" + "\\" + picName+"."+ext);
FileOutputStream out = new FileOutputStream(path);
out.write(data);
out.close();
return name;
}