5.2 网络数据Excel存储

网络爬虫在采集少量数据时，可以使用Excel进行存储。 Java中主要有两款操作Excel的工具JXL(主要处理xls格式的Excel)和POI(可处理xls、xlsx、word等格式的文件)。本小节利用POI封装了Excel读取和写入方法，供读者参考。
使用Excel存储数据之前，需要在Maven工程的pom.xml文件中添加Excel的dependency。

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

如程序5-2所示，将采集到的URL存入excel表格。

//程序5-2
public class ExcelStorage {
    public static void main(String[] args) throws IOException {
        //需要写入到excel的内容
        //这里选择使用map数据结构来存储excel所对应的一行数据
        List<Map<String,String>> excelMapList = new LinkedList<>();
        //通过Jsoup创建和url的连接
        Connection connection = Jsoup.connect("https://searchcustomerexperience.techtarget.com/info/news");
        //获取网页的Document对象
        Document document = connection.get();
        Elements aList = document.getElementsByTag("a");
        if(aList.size()>0){
            for(int i = 0;i<aList.size();i++){
                Element a = aList.get(i);
                if(a != null){
                    String url = a.attr("href");
                    if(url.contains("http")) {
                        Map<String,String> map = new HashMap<>();
                        map.put("f1",url);
                        excelMapList.add(map);
                    }
                }
            }
        }
        //设置excel表头
        List<String> headList = new ArrayList<>();
        headList.add("url");
        //创建含有表头的excel
        XSSFWorkbook workbook = ExcelUtils.getWorkbook("Sheet1", headList);
        //将excelMapList内容写入Excel
        String excelPath = "/Users/steven/Documents/代码/project/spider/src/main/java/com/topicBet/url.xlsx";
        ExcelUtils.writeExcelContent(workbook, "Sheet1", excelPath, excelMapList);
    }
}

@Slf4j
public class ExcelUtils {
    /**
     * 读取excel文件内容,文件需要含有表头，如果没有表头请以f1,f2,f3......填充
     *
     * @param filePath  文件路径
     * @param sheetAt 表格
     * @return List<Map<String,String>>
     */
    public static List<Map<String, String>> readExcelContent(String filePath, Integer sheetAt) {
        List<Map<String, String>> mapList = new ArrayList<>();
        FileInputStream fileInputStream = null;
        try {
            fileInputStream = new FileInputStream(filePath);
            // 延迟解析比率
            ZipSecureFile.setMinInflateRatio(-1.0d);
            XSSFWorkbook workbook = new XSSFWorkbook(fileInputStream);
            XSSFSheet sheet = workbook.getSheetAt(sheetAt);
            int rows = sheet.getPhysicalNumberOfRows();
            for (int i = 1; i <= rows; i++) {
                //此行不为空行
                if (sheet.getRow(i) != null) {
                    Map<String, String> map = new HashMap<>();
                    int columns = sheet.getRow(i).getPhysicalNumberOfCells();
                    for (int j = 0; j < columns; j++) {
                        map.put("f" + (j + 1), getString(sheet.getRow(i).getCell(j)).trim());
                    }
                    mapList.add(map);
                }
            }
        } catch (Exception e) {
            log.error("读取文件出现异常");
        }
        return mapList;
    }

    /**
     * 把单元格的内容转为字符串
     *
     * @param xssfCell 单元格
     * @return String
     */
    public static String getString(XSSFCell xssfCell) {
        if (xssfCell == null) {
            return "";
        }
        if (xssfCell.getCellTypeEnum() == CellType.NUMERIC) {
            DecimalFormat df = new DecimalFormat("0");
            return df.format(xssfCell.getNumericCellValue());
        } else if (xssfCell.getCellTypeEnum() == CellType.BOOLEAN) {
            return String.valueOf(xssfCell.getBooleanCellValue());
        } else {
            return xssfCell.getStringCellValue();
        }
    }

    /**
     * 创建含有表头的excel
     *
     * @param sheetName 表格名称
     * @param headList  表头字符串列表
     * @return XSSFWorkbook
     */
    public static XSSFWorkbook getWorkbook(String sheetName, List<String> headList) {
        XSSFWorkbook workbook = new XSSFWorkbook();
        XSSFSheet sheet = workbook.createSheet(sheetName);
        XSSFRow titleRow = sheet.createRow(0);
        for (int i = 0; i < headList.size(); i++) {
            XSSFCell cell = titleRow.createCell(i);
            cell.setCellValue(headList.get(i));
        }
        return workbook;
    }

    /**
     * 将需要输出的内容填充到excel并保存到本地
     *
     * @param workbook     表格
     * @param sheetName    表格名称
     * @param path         输出到本地的路径
     * @param excelMapList 需要输出的内容
     */
    public static void writeExcelContent(XSSFWorkbook workbook, String sheetName, String path, List<Map<String, String>> excelMapList) {
        // 延迟解析比率
        ZipSecureFile.setMinInflateRatio(-1.0d);
        XSSFSheet sheet = workbook.getSheet(sheetName);
        for (int i = 1; i <= excelMapList.size(); i++) {
            XSSFRow row = sheet.createRow(i);
            Map<String, String> map = excelMapList.get(i - 1);
            for (int j = 0; j < map.size(); j++) {
                XSSFCell cell = row.createCell(j);
                cell.setCellValue(map.get("f" + (j + 1)));
            }
        }
        //用输出流写到excel
        FileOutputStream outputStream = null;
        try {
            outputStream = new FileOutputStream(path);
            workbook.write(outputStream);
            outputStream.flush();
            outputStream.close();
        } catch (Exception e) {
            log.error("写入excel发生异常");
        }
    }
}