Excel文件 java 简单读写

maven 包

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

可以将 excel 文件简单理解为下面这样

一个json,内部是二维List。

{
    "sheet1": [
        ["A1","B1","C1","D1"],
        ["A2","B2","C2","D2"],
        ["A3","B3","C3","D3"]
    ],
    "sheet2": [
        ["A1","B1","C1","D1"],
        ["A2","B2","C2","D2"],
        ["A3","B3","C3","D3"]
    ]
}

有时候为了方便读取也会将sheet页获取成List HashMap ，不过为此需要提前确定表头。

简单粗暴的调用了一下。后面再出一个精致点的使用吧。

package com.asiainfo.crcrawl.knowledgepoints.excel;

import com.alibaba.fastjson.JSONObject;
import com.asiainfo.crcrawl.knowledgepoints.Hello;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.InputStream;
import java.util.HashMap;
import java.util.List;

@Slf4j
public class ExcelPoint {

    public static void main(String[] args) {
        ExcelPoint excelPoint = new ExcelPoint();
        excelPoint.read();
        excelPoint.write();
        System.out.println(" ExcelPoint Hello !! Knowledge points!!");
    }

    /*
    * 读
    * */
    public void read(){
        ExcelData excelData = new ExcelData();

        List<HashMap> tenderMapList = excelData.getTenderMapList(
                "D:\\Documents\\WorkTasks\\中移铁通标讯项目\\2021年6月7日-铁通-商情推送相关\\test-标讯一万条.xlsx",
//                "test");
//                 "标讯信息");
                "2021年9月2日");

        log.info(JSONObject.toJSONString(tenderMapList.get(0)));
        log.info(JSONObject.toJSONString(tenderMapList.get(tenderMapList.size())));

    }

    /*
    * 写
    * */
    public void write(){

        // 第一步，创建一个webbook，对应一个Excel文件
        HSSFWorkbook hwb = new HSSFWorkbook();

        // 第二步，在webbook中添加一个sheet,对应Excel文件中的sheet
        HSSFSheet sheet = hwb.createSheet("sheet1");
        // 第三步，在sheet中添加表头第0行
        HSSFRow row = sheet.createRow( 0);

        HSSFCell cell = row.createCell( 0);  //第0列
        cell.setCellValue("标题");

        cell = row.createCell(1);
        cell.setCellValue("发布时间");

        cell = row.createCell(2);
        cell.setCellValue("招标编号");

        cell = row.createCell( 3);
        cell.setCellValue("中标金额");

        cell = row.createCell( 4);
        cell.setCellValue("招标单位");

        cell = row.createCell( 5);
        cell.setCellValue("中标单位");

        cell = row.createCell( 6);
        cell.setCellValue("url");

        cell = row.createCell( 7);
        cell.setCellValue("正文");



        // 第六步，将文件存到指定位置
        try
        {
            FileOutputStream fout = new FileOutputStream(System.getProperty("user.dir")+"cr_file"+ File.separator+"excelPoint_file.xls");
            hwb.write(fout);
            fout.close();
        }
        catch (Exception e)
        {
            e.printStackTrace();
        }

    }

}

从别处盗来的读取类，挺方便的。上面的代码有用到。

package com.asiainfo.crcrawl.knowledgepoints.excel;
//package test.java.utils;

import lombok.extern.slf4j.Slf4j;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.FileInputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;

/**
 * Author: 灵枢
 * Date: 2018/12/05
 * Time: 17:21
 * Description:读取Excel数据
 */
@Slf4j
public class ExcelData {
//    private XSSFSheet sheet;
//
//    /**
//     * 构造函数，初始化excel数据
//     * @param filePath  excel路径
//     * @param sheetName sheet表名
//     */
//    public ExcelData(String filePath, String sheetName){
//        FileInputStream fileInputStream = null;
//        try {
//            fileInputStream = new FileInputStream(filePath);
//            XSSFWorkbook sheets = new XSSFWorkbook(fileInputStream);
//            //获取sheet
//            sheet = sheets.getSheet(sheetName);
//        } catch (Exception e) {
//            e.printStackTrace();
//        }
//    }

    public ExcelData() {
    }

    public   List<HashMap>  getTenderMapList(String filePath, String sheetName){

        FileInputStream fileInputStream = null;

//        String filePath = "D:\\Documents\\WorkTasks\\中移铁通标讯项目\\2021年6月7日-铁通-商情推送相关\\test-标讯一万条.xlsx";

        List<HashMap> tList = new ArrayList<>();
        try {
            fileInputStream = new FileInputStream(filePath);
            XSSFWorkbook sheets = new XSSFWorkbook(fileInputStream);
            //获取sheet
            XSSFSheet sheet = sheets.getSheet(sheetName);

            XSSFRow row1 = sheet.getRow(1);
            int rowNum = sheet.getLastRowNum();
//            log.info("rowNum : "+rowNum);

            String cell = row1.getCell(1).toString();
            int cellNum = row1.getLastCellNum();
//            log.info("cellNum : "+cellNum);

//            log.info(cell);


            for(int ri =2;ri<=rowNum;ri++){
                XSSFRow rowi = sheet.getRow(ri);
                HashMap tMap = new HashMap();
                for(int ci = 0;ci<cellNum;ci++){
                    tMap.put(row1.getCell(ci).toString(),rowi.getCell(ci).toString());
                }

//                log.info((String) tMap.get("标题"));
                tList.add(tMap);
            }

            return tList;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return tList;
    }
//
//    /**
//     * 根据行和列的索引获取单元格的数据
//     * @param row
//     * @param column
//     * @return
//     */
//    public String getExcelDateByIndex(int row,int column){
//        XSSFRow row1 = sheet.getRow(row);
//        String cell = row1.getCell(column).toString();
//        return cell;
//    }
//
//    /**
//     * 根据某一列值为“******”的这一行，来获取该行第x列的值
//     * @param caseName
//     * @param currentColumn 当前单元格列的索引
//     * @param targetColumn 目标单元格列的索引
//     * @return
//     */
//    public String getCellByCaseName(String caseName,int currentColumn,int targetColumn){
//        String operateSteps="";
//        //获取行数
//        int rows = sheet.getPhysicalNumberOfRows();
//        for(int i=0;i<rows;i++){
//            XSSFRow row = sheet.getRow(i);
//            String cell = row.getCell(currentColumn).toString();
//            if(cell.equals(caseName)){
//                operateSteps = row.getCell(targetColumn).toString();
//                break;
//            }
//        }
//        return operateSteps;
//    }
//
//    //打印excel数据
//    public void readExcelData(){
//        //获取行数
//        int rows = sheet.getPhysicalNumberOfRows();
//        for(int i=0;i<rows;i++){
//            //获取列数
//            XSSFRow row = sheet.getRow(i);
//            int columns = row.getPhysicalNumberOfCells();
//            for(int j=0;j<columns;j++){
//                String cell = row.getCell(j).toString();
//                System.out.println(cell);
//            }
//        }
//    }
//
//    //测试方法
//    public static void main(String[] args){
//        ExcelData sheet1 = new ExcelData("resource/FirstTests.xlsx", "username");
//        //获取第二行第4列
//        String cell2 = sheet1.getExcelDateByIndex(1, 3);
//        //根据第3列值为“customer23”的这一行，来获取该行第2列的值
//        String cell3 = sheet1.getCellByCaseName("customer23", 2,1);
//        System.out.println(cell2);
//        System.out.println(cell3);
//    }
}