java利用POI解析Excel及图片

水深无声 2022-02-03 20:09 739阅读 0赞

# 依赖jar #

<dependency>
                <groupId>org.apache.poi</groupId>
                <artifactId>poi</artifactId>
                <version>3.17</version>
            </dependency>
            <dependency>
                <groupId>org.apache.poi</groupId>
                <artifactId>poi-ooxml</artifactId>
                <version>3.17</version>
            </dependency>

# 代码： #

package com.betawoo.admin.test.base;
    import com.betawoo.admin.commons.utils.QiNiuUtils;
    import org.apache.poi.POIXMLDocumentPart;
    import org.apache.poi.hssf.usermodel.*;
    import org.apache.poi.ss.usermodel.*;
    import org.apache.poi.xssf.usermodel.*;
    import org.openxmlformats.schemas.drawingml.x2006.spreadsheetDrawing.CTMarker;
    import java.io.File;
    import java.io.FileInputStream;
    import java.io.FileOutputStream;
    import java.io.IOException;
    import java.util.HashMap;
    import java.util.List;
    import java.util.Map;
    /**
     * Created by hgg on 2019/5/7.
     */
    public class POIExcel {
        public static void getDataFromExcel(String filePath) throws IOException
        {
            //判断是否为excel类型文件
            if(!filePath.endsWith(".xls")&&!filePath.endsWith(".xlsx"))
            {
                System.out.println("文件不是excel类型");
            }
            FileInputStream fis =null;
            Workbook wookbook = null;
            Sheet sheet =null;
            try
            {
                //获取一个绝对地址的流
                fis = new FileInputStream(filePath);
                /* 读取网络文件（比如七牛等云存储）
                URL url = new URL(filePath);
                BufferedInputStream fis = new BufferedInputStream(url.openStream());*/
            }
            catch(Exception e)
            {
                e.printStackTrace();
            }
            try
            {
                //2003版本的excel，用.xls结尾
                wookbook = new HSSFWorkbook(fis);//得到工作簿
            }
            catch (Exception ex)
            {
                //ex.printStackTrace();
                try
                {
                    //2007版本的excel，用.xlsx结尾
                    fis = new FileInputStream(filePath);
                    wookbook = new XSSFWorkbook(fis);//得到工作簿
                } catch (IOException e)
                {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                }
            }
            Map<String, PictureData> maplist=null;
            sheet = wookbook.getSheetAt(0);
            // 判断用07还是03的方法获取图片
            if (filePath.endsWith(".xls")) {
                maplist = getPictures1((HSSFSheet) sheet);
            } else if(filePath.endsWith(".xlsx")){
                maplist = getPictures2((XSSFSheet) sheet);
            }
            try {
                printImg(maplist);
            } catch (Exception e) {
                e.printStackTrace();
            }finally {
                //释放map
                if (maplist != null){
                    maplist = null;
                }
            }
            //得到一个工作表
            //获得表头
            Row rowHead = sheet.getRow(0);
    		
            //获得数据的总行数
            int totalRowNum = sheet.getLastRowNum();
            //要获得属性
            String proName="";
            String space="";
            String size="";
            String brand="";
            String unit="";
            Integer num=null;
            Double unitPrice=null;
            Double total=null;
            String material="";
            String remark="";
            String pic="";
            //获得所有数据
            System.out.println("产品名称\t\t空间\t\t规格/尺寸\t\t品牌\t\t单位\t\t数量\t\t单价\t\t金额\t\t材质\t\t备注");
            for(int i = 1 ; i < totalRowNum ; i++)
            {
                //获得第i行对象
                Row row = sheet.getRow(i);
                
                //空间位置(为空则停止解析)
                Cell cell = row.getCell(0);
                if (cell == null){
                    break;
                }
                cell.setCellType(CellType.STRING);
                space =cell.getStringCellValue().toString();
                if (StringUtils.isBlank(space)){
                    break;
                }
    
                //产品名称
                cell = row.getCell(1);
                if (cell != null){
                    cell.setCellType(CellType.STRING);
                    proName =  cell.getStringCellValue();
                }
                //规格/尺寸
                cell = row.getCell(3);
                if (cell != null){
                    cell.setCellType(CellType.STRING);
                    size =cell.getStringCellValue()+"";
                }
                //品牌
                cell = row.getCell(4);
                if (cell != null){
                    cell.setCellType(CellType.STRING);
                    brand =cell.getStringCellValue()+"";
                }
                //单位
                cell = row.getCell(5);
                if (cell != null){
                    cell.setCellType(CellType.STRING);
                    unit =cell.getStringCellValue()+"";
                }
                //数量
                cell = row.getCell(6);
                if (cell != null){
                    num =(int)cell.getNumericCellValue();
                }
                //单价
                cell = row.getCell(7);
                if (cell != null){
                    unitPrice =cell.getNumericCellValue();
                }
                //金额
                cell = row.getCell(8);
                if (cell != null){
                    total =cell.getNumericCellValue();
                }
                //材质
                cell = row.getCell(9);
                if (cell != null){
                    cell.setCellType(CellType.STRING);
                    material =cell.getStringCellValue()+"";
                }
                //备注
                cell = row.getCell(10);
                if (cell != null){
                    cell.setCellType(CellType.STRING);
                    remark =cell.getStringCellValue()+"";
                }
                System.out.println(proName+"\t\t"+space+"\t\t"+size+"\t\t"+brand+"\t\t"+unit+"\t\t"+num+"\t\t"
                        +unitPrice+"\t\t"+total+"\t\t"+material+"\t\t"+remark);
            }
            for (Map.Entry<String, PictureData> entry : maplist.entrySet()) {
                System.out.println("Key = " + entry.getKey() + ", Value = " + entry.getValue());
            }
            //使用完成关闭
            wookbook.close();
            if (fis != null){
                fis.close();
            }
        }
        /**
         * 获取图片和位置 (xls)
         * @param sheet
         * @return
         * @throws IOException
         */
        public static Map<String, PictureData> getPictures1 (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)
         * @param sheet
         * @return
         * @throws IOException
         */
        public static Map<String, PictureData> getPictures2 (XSSFSheet sheet) throws IOException {
            Map<String, PictureData> map = new HashMap<String, PictureData>();
            List<POIXMLDocumentPart> list = sheet.getRelations();
            for (POIXMLDocumentPart part : list) {
                if (part instanceof XSSFDrawing) {
                    XSSFDrawing drawing = (XSSFDrawing) part;
                    List<XSSFShape> shapes = drawing.getShapes();
                    for (XSSFShape shape : shapes) {
                        XSSFPicture picture = (XSSFPicture) shape;
                        XSSFClientAnchor anchor = picture.getPreferredSize();
                        CTMarker marker = anchor.getFrom();
                        String key = marker.getRow() + "-" + marker.getCol();
                        map.put(key, picture.getPictureData());
                    }
                }
            }
            return map;
        }
        //图片写出
        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();
                //文件上传七牛
    //            QiNiuUtils.uploadOneObject(data,"111_"+picName + "." + ext);
                //图片保存路径
                filePath = "D:\\img\\pic" + picName + "." + ext;
                System.out.println(filePath);
                FileOutputStream out = new FileOutputStream(filePath);
                out.write(data);
                out.close();
            }
        }
        public static void main(String[] args) throws Exception {
            getDataFromExcel("D:"+ File.separator +"test.xlsx");
        }
    }

# 注意事项及测试结果 #

Excel内容如下：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2hnZzkyMw_size_16_color_FFFFFF_t_70][]

**解析的文本内容，如下**

**![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2hnZzkyMw_size_16_color_FFFFFF_t_70 1][]**

**解析出的图片**

**![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2hnZzkyMw_size_16_color_FFFFFF_t_70 2][]**

**缺点或问题：**

1.  单元格图片不能越界（不然解析出来的图片和文本对应关系出错）；
2.  单元格多个图能取到最后一个图；

问题2已找到原因：getPictures1和getPictures2 方法中key导致 ，代码如下

String key = cAnchor.getRow1() + "-" + cAnchor.getCol1(); // 行号-列号                 
    
    map.put(key, pdata);

至此，试验完全OK~！[感谢原文一][Link 1]          [感谢原文二][Link 2]

扩展：如果是来自外网文件读取方式——[感谢思路][Link 3]

BufferedInputStream fis =null;
    URL url = new URL(filePath);
    fis = new BufferedInputStream(url.openStream());

**问题一**：[Cannot get a STRING value from a NUMERIC cell][]

原因：Excel把纯数字，当做了数值框，导致解析失败

**问题二：**报错：org..XSSFSimpleShape cannot be cast to ..XSSFPicture

解决办法：将【xlsx】另存为【xls】（因为低版本改成了高版本Excel，解析有问题）

**补充说明**：如果需要读取网络第三方或云存储中的文件（详见代码如下注释内容）

/\* 读取网络文件（比如七牛等云存储）  
URL url = new URL(filePath);

BufferedInputStream fis = new BufferedInputStream(url.openStream());*/

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2hnZzkyMw_size_16_color_FFFFFF_t_70]: /images/20220204/6cd7e19fb5bf4249bdcb692a80ed9ee8.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2hnZzkyMw_size_16_color_FFFFFF_t_70 1]: /images/20220204/2923e802a6414ab18fd34c9c8f54f6c3.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2hnZzkyMw_size_16_color_FFFFFF_t_70 2]: /images/20220204/f0ea780570414d47b69ee774a3045a1b.png
[Link 1]: https://blog.csdn.net/wy123123000/article/details/81330712
[Link 2]: https://blog.csdn.net/qw0907/article/details/54617706#commentBox
[Link 3]: https://dien.iteye.com/blog/2117531
[Cannot get a STRING value from a NUMERIC cell]: https://blog.csdn.net/wang704987562/article/details/81837750?utm_source=blogxgwz4