poi简单操作excel

<!--引入poi依赖-->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-scratchpad</artifactId>
            <version>3.17</version>
        </dependency>
 
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.17</version>
        </dependency>

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

package com.java;
 
 
import com.java.bean.PumBean;
import com.java.mapper.PumMapper;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;
import java.io.FileInputStream;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.Iterator;
import java.util.List;
 
 
@RunWith(SpringRunner.class)
@SpringBootTest
@Slf4j
public class PinganApplicationTests{
 
    @Autowired
    private PumMapper pumMapper;
 
    @Test
    public void queryExcelContent() throws Exception{
 
        List<Object> objectList=new ArrayList<>();
        FileInputStream inputStream = new FileInputStream("E:\\工作簿.xlsx");
        XSSFWorkbook wb = new XSSFWorkbook(inputStream);
        //获取工作表对象
        XSSFSheet sheet = wb.getSheetAt(0);
        //得到行的迭代器
        Iterator<Row> iterator = sheet.iterator();
        int rowNum = 0;
        while (iterator.hasNext()) {
            Row row = iterator.next();
            //跳过标题行
//            if (rowNum == 0) {
//                rowNum++;
//                continue;
//            }
            //遍历,把每一行数据存到Object数组中
            Object[] obj = new Object[4];
            for (int i = 0; i < 3; i++) {
                // 获取到单元格内的数据，方法见下
                Cell cell=row.getCell(i);
                if (cell==null){
                    continue;
                }
                if (i==0){
                    cell.setCellType(CellType.STRING);
                    obj[i]=cell.getStringCellValue();
                    continue;
                }else if (i==1){
                    cell.setCellType(CellType.STRING);
                    obj[i]=cell.getStringCellValue();
                    continue;
                }else {
                    cell.setCellType(CellType.NUMERIC);
                    obj[i]=cell.getDateCellValue();
                    Date date=(Date) obj[i];
                    if (date==null){
                        continue;
                    }
                    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                    String format = sdf.format(date);
                }
            }
            //将object对象保存到集合中
            objectList.add(obj);
        }
 
        List<PumBean> pumBeanList=new ArrayList<>();
 
        objectList.stream().forEach(a->{
            Object[] obj =(Object[])a;
            PumBean pumBean=new PumBean();
            for (int i=0;i<obj.length;i++){
                if (obj[i]==null){
                    continue;
                }
                if (i==2){
                    Date date=(Date) obj[i];
                    if (date==null){
                        continue;
                    }
                    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                    String format = sdf.format(date);
                    pumBean.setMemoTime(format);
                    continue;
                }
 
                if (i==0){
                    pumBean.setId(Integer.parseInt(obj[i].toString()));
                }
                if (i==1){
                    String content = String.valueOf(obj[i]);
                    int length=content.length();
 
 
                    if (content.contains("今天的任务")){
                        System.out.println("length="+length);
                        System.out.println("content="+content);
                        content=content.substring(7,length-3);
                        System.out.println("【剪切后的关键字】:"+content);
                        pumBean.setMemoContent(content);
                    }
                }
            }
            pumBeanList.add(pumBean);
        });
 
        pumBeanList.forEach(a->{
            System.out.println(a);
        });
 
    }
 
 
}