江湖救急、就从今天开始吧

Aug 16, 2017, 阴晴不定，听说下午下了点小雨

REQUEST

今天小米找我看一个excel的问题，3000多行的数据，每一行的第1列是一个网页的链接，第2列是出现在这个网页中的一个img元素节点，表现形式和可能情况大概如下：

str1	str2
www.aaa.com/1.html	<img src="x1.jpg" alt="y1">
www.aaa.com/1.html	<img alt="y2" src="x2.png" >
www.aaa.com/2.html	<img alt="y3" data-src-pc="x3.gif" data-src-mobile="x4.gif" >
www.aaa.com/3.html	<img src="./x5.jpg" >
www.aaa.com/3.html	<img alt="" data-media-s1="./x6.jpg" data-media-s2="//image/x7.jpg" data-media-s3="../x8.jpg" data-media-s4="http://www.aaa.com/x9.jpg">

 

她的需求是将第2列中出现的所有属性（alt, src, data-src-pc,etc.） 引号内对应的值分别提取到之后的第3列，第4列...

由于这个姐姐的excel不是很好，找我来帮忙，此时下午2点多一刻

SOLUTION

1. 首先很容易就想到重新复制一列第2列用双引号”来切割这列，毕竟所有的属性值都被填写在双引号内，然而切割之后发现由于相同的属性的有无不一致，出现顺序也不一致，导致切割之后的数据还需要重新分析，直接哭死...

2. 在百度上搜到一个公式改了改“=MID(C2,FIND("src=",C2)+1,(FIND(""" ",C2)-FIND("src=",C2)-1))”，试了试，发现FIND只会从左往右搜，搜到了就会返回第一个搜到的值，导致MID的第三个参数总会出错，接着百度，看到有人说可以在公式中加入SUBSTITUTE来配合FIND从右往左找，试了试，这个方法需要太多调教，不是我们想要的...

3. 接着在谷歌上找公式，找到了一个叫Kutools的工具（免费试用25天），里边有一个功能叫Extract Text（https://www.extendoffice.com/product/kutools-for-excel/excel-extract-text-from-cell.html），看到这个例子的时候，我们就开始仰天长笑，但是试过之后，发现和FIND的懒惰匹配不同的是这个工具采用的是贪婪匹配，就是假如我在条件那项输入src="*"，并且src不以最后一个属性出现的话，那么从src之后的第一个双引号，一直到最后一个双引号之内的字符都会被当做结果找出来，不好！

 4. 再次深度思考了一下，决定拿出大招 - 正则表达式，写好正则 "<img\b[^>]*\bsrc="([^"]*)"[^>]*>"，在电脑上的正则测试器上试了试，瞬间美了，正则帮忙找出了所有的src，然而导出cvs的时候，发现有些特殊字符被替换成??，额，姐没空挨个看是否有特殊字符啊！！！另外3000多行只找到2400有src的，所以姐还得在做之前把第1列和第2列拼成一个字符串，为了再次vlookup一下！vlookup的时候，不知道excel哪根筋不对，明明一样字符串可以被找到，却返回#value，此时已经5点一刻，距离下班还有不到1个小时的时间。

5. 没人要求下班前做完，但我已经浪费了3个小时却没有结果，已经不是做完不做完的问题了。我默默打开Eclipse，用 Apache POI 读了个表，对取出的每行的第2列用Jsoup解析了一下，将解析之后的属性值依次对应放到第3列，第4列...保存了一下表，运行了一下这个小程序，10秒钟3000多行数据被整整齐齐地处理完了，此时5点55，这个姐姐因为刚生完孩子每天都可以提前一个小时下班，所以她已经回家了，我把处理好的excel发Outlook给她，这样明天早上她准时上班的时候就能直接用了，随后我关了电脑，欢快地回家了，明天又是迟到的一天。

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.nio.channels.FileChannel;

import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Cell;
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.jsoup.Jsoup;
import org.jsoup.nodes.Document;

public class extractSrcAlt {

    public static void main(String[] args) throws InvalidFormatException, IOException {
        
        File from = new File("./extractSrcAlt/MonitorResult.xlsx");
        File to = new File("./extractSrcAlt/MonitorResult_copy.xlsx");
        fileChannelCopy(from, to);
        
        InputStream inp = new FileInputStream(to);
        Workbook wb = WorkbookFactory.create(inp);
        Sheet sheet = wb.getSheet("data");
        
        String src ="";
        String alt = "";
        String datasrcpc ="";
        String datasrcmobile ="";
        String datamedias1 ="";
        String datamedias2 ="";
        String datamedias3 ="";
        String datamedias4 ="";
        
        for(int rowNum = 1; rowNum <= sheet.getLastRowNum(); rowNum++){
            
            Row row = sheet.getRow(rowNum);
            
            String linkCell = getValue(row.getCell(1));
            
            Document doc = Jsoup.parse(linkCell);
            try {
                
                src = doc.select("img").first().attr("src");
                System.out.println(src);
                
                alt = doc.select("img").first().attr("alt");
                System.out.println(alt);
                
                datasrcpc = doc.select("img").first().attr("data-src-pc");
                
                datasrcmobile = doc.select("img").first().attr("data-src-mobile");
                
                datamedias1 = doc.select("img").first().attr("data-media-s1");
                
                datamedias2 = doc.select("img").first().attr("data-media-s2");
                
                datamedias3 = doc.select("img").first().attr("data-media-s3");
                
                datamedias4 = doc.select("img").first().attr("data-media-s4");
                
            } catch (Exception e) {
                // TODO: handle exception
            }
            
            Cell srcCell = row.createCell(2);
            srcCell.setCellType(Cell.CELL_TYPE_STRING);
            srcCell.setCellValue(src);
            
            Cell altCell = row.createCell(3);
            altCell.setCellType(Cell.CELL_TYPE_STRING);
            altCell.setCellValue(alt);
            
            Cell datasrcpcCell = row.createCell(4);
            datasrcpcCell.setCellType(Cell.CELL_TYPE_STRING);
            datasrcpcCell.setCellValue(datasrcpc);
            
            Cell datasrcmobileCell = row.createCell(5);
            datasrcmobileCell.setCellType(Cell.CELL_TYPE_STRING);
            datasrcmobileCell.setCellValue(datasrcmobile);
            
            Cell datamedias1Cell = row.createCell(6);
            datamedias1Cell.setCellType(Cell.CELL_TYPE_STRING);
            datamedias1Cell.setCellValue(datamedias1);
            
            Cell datamedias2Cell = row.createCell(7);
            datamedias2Cell.setCellType(Cell.CELL_TYPE_STRING);
            datamedias2Cell.setCellValue(datamedias2);
            
            Cell datamedias3Cell = row.createCell(8);
            datamedias3Cell.setCellType(Cell.CELL_TYPE_STRING);
            datamedias3Cell.setCellValue(datamedias3);
            
            Cell datamedias4Cell = row.createCell(9);
            datamedias4Cell.setCellType(Cell.CELL_TYPE_STRING);
            datamedias4Cell.setCellValue(datamedias4);
            
        }
        
        int u = 0;
        while(u == 0){
            try {
                FileOutputStream fileOut = new FileOutputStream(to);
                wb.write(fileOut);
                fileOut.close();
                u = 1;
            } catch (Exception e) {
                u = 0;
            }
        }
    }
    
    
    private static String getValue(Cell cell){
        if(null == cell){
            //System.out.println("Null");
            return "";
        }else if(cell.getCellType() == Cell.CELL_TYPE_BLANK){
            //System.out.println("CELL_TYPE_BLANK");
            cell.setCellType(Cell.CELL_TYPE_STRING);
            return String.valueOf(cell.getStringCellValue());
        }else if(cell.getCellType() == Cell.CELL_TYPE_BOOLEAN){
            cell.setCellType(Cell.CELL_TYPE_STRING);
            return String.valueOf(cell.getStringCellValue());
        }else if(cell.getCellType()== Cell.CELL_TYPE_NUMERIC){
            cell.setCellType(Cell.CELL_TYPE_STRING);
            return String.valueOf(cell.getStringCellValue());
        }else{
            cell.setCellType(Cell.CELL_TYPE_STRING);
            return String.valueOf(cell.getStringCellValue());
        }
    }
    
    public static void fileChannelCopy(File from, File to) {

        FileInputStream fi = null;
        FileOutputStream fo = null;
        FileChannel in = null;
        FileChannel out = null;

        try {

            fi = new FileInputStream(from);
            fo = new FileOutputStream(to);
            in = fi.getChannel();//得到对应的文件通道
            out = fo.getChannel();//得到对应的文件通道

            in.transferTo(0, in.size(), out);//连接两个通道，并且从in通道读取，然后写入out通道

        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            try {
                fi.close();
                in.close();
                fo.close();
                out.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }
}