[开发工具]Java采用POI读取Excel时间日期数据并进行运算处理计算相邻两个数据的时间差

?要求:读取excel文件中的时间数据进行运算处理,查询相邻两行数据时间差不为5s的数据进行输出

1.需要导入相关依赖

        <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>
        <dependency>
            <groupId>joda-time</groupId>
            <artifactId>joda-time</artifactId>
            <version>2.10.14</version>
        </dependency>

2.引入文件流读取文档:

String PATH = "C:\\Users\\Administrator\\Desktop\\test.xls";

FileInputStream inputStream = new FileInputStream(PATH);

3.poi经典操作,创建工作簿,取表,遍历每一行每一列

        Workbook workbook = new HSSFWorkbook(inputStream);
        //取第一个表
        Sheet sheet = workbook.getSheetAt(0);
        //获取所有的行
        Row rowTitle = sheet.getRow(0);
        //引入一个列存储数据
        List<String> sl=new ArrayList<String>();
        //getPhysicalNumberOfRows用来得到行数,同理可以得到列数
        int rowCount = sheet.getPhysicalNumberOfRows();
        for (int rowNum = 0; rowNum < rowCount; rowNum++) {
            //获取行的数据
            Row rowData = sheet.getRow(rowNum);
            //看看有多少行
            //System.out.println(rowCount);
            if (rowData!=null){
                //读取列
                int cellCount = rowTitle.getPhysicalNumberOfCells();
                for (int cellNum = 0; cellNum < cellCount; cellNum++) {
                    //得到每一个表格的内容
                    Cell cell = rowData.getCell(cellNum);
                    //将表格内容转化为Date格式
                    Date dateCellValue = cell.getDateCellValue();
                    //转换为时分秒
                    String dateData = sdf.format(dateCellValue);
                    //将每个单元格的数据存储到list sl中
                    sl.add(dateData);
                }
            }
        }

4.解释上述代码段类型转换

读取excel每个表格内容之后利用cell.getCellTypeEnum()函数发现值为NUMERIC,(为啥excel表中的事件类型在这里是数字型的?) 发现不是想要的之后利用cell.getDateCellValue()函数得到Date类型函数,Date类型函数无法进行后续运算所以需要再将Date类型函数转换为字符串.

ps:直接输出cell内容的话为乱码 可以sout尝试一下

更换Date形式如下: 这里采用的是时:分:秒 可以加上年-月-日,看你的需求了

                    SimpleDateFormat sdf=new SimpleDateFormat("hh:mm:ss");
                    //得到每一个表格的内容
                    Cell cell = rowData.getCell(cellNum);
                    //将表格内容转化为Date格式
                    Date dateCellValue = cell.getDateCellValue();
                    //转换为时分秒
                    String dateData = sdf.format(dateCellValue);

5.存储好数据之后打印下sl看看,已经是时间形式了之后进行运算,继续转换将Date类型数据转换为字符串进行加减法运算.

[09:55:16, 09:55:21, 09:55:26, 09:55:31, 09:55:36, 09:55:41, 09:55:46, 09:55:51, 09:55:56, 09:56:01, 09:56:06, 09:56:11, 09:56:16, 09:56:21, 09:56:26, 09:56:31, 09:56:36, 09:56:41, 09:56:46, 09:56:51, 09:56:56, 09:57:01, 09:57:06, 09:57:11, 09:57:16, 09:57:21, 09:57:26, 09:57:31, 09:57:36, 09:57:41, 09:57:46, 09:57:51, 09:57:56, 09:58:01, 09:58:06, 09:58:11, 09:58:16, 09:58:21, 09:58:26, 09:58:31, 09:58:36, 09:58:41, 09:58:46, 09:58:51, 09:58:56, 09:59:01, 09:59:06, 09:59:11, 09:59:16, 09:59:21, 09:59:26, 09:59:31, 09:59:36, 09:59:41, 09:59:46, 09:59:51, 09:59:56, 10:00:01, 10:00:06, 10:00:11, 10:00:16, 10:00:21, 10:00:26, 10:00:31, 10:00:36, 10:00:41, 10:00:46, 10:00:51, 10:00:56, 10:01:01, 10:01:06, 10:01:11, 10:01:16, 10:01:21, 10:01:26, 10:01:31, 10:01:36, 10:01:41, 10:01:46, 10:01:51, 10:01:56, 10:02:01, 10:02:06, 10:02:11, 10:02:16, 10:02:21, 10:02:26, 10:02:31, 10:02:36, 10:02:41, 10:02:46, 10:02:51, 10:02:56, 10:03:01, 10:03:06, 10:03:11, 10:03:16, 10:03:21, 10:03:26, 10:03:31, 10:03:36, 10:03:41, 10:03:46, 10:03:51, 10:03:56, 10:04:01, 10:04:06, 10:04:11, 10:04:16, 10:04:21, 10:04:26, 10:04:31, 10:04:36, 10:04:41, 10:04:46, 10:04:51, 10:04:56, 10:05:01, 10:05:06, 10:05:11, 10:05:16, 10:05:21, 10:05:26, 10:05:31, 10:05:36, 10:05:41, 10:05:46, 10:05:51, 10:05:56, 10:06:01, 10:06:06, 10:06:11, 10:06:16, 10:06:21, 10:06:26, 10:06:31, 10:06:36, 10:06:41, 10:06:46, 10:06:51, 10:06:56, 10:07:01, 10:07:06, 10:07:11, 10:07:16, 10:07:21, 10:07:26, 10:07:31, 10:07:36, 10:07:41, 10:07:46, 10:07:51, 10:07:56, 10:08:01, 10:08:06, 10:08:11, 10:08:16, 10:08:21, 10:08:26, 10:08:31, 10:08:36, 10:08:41, 10:08:46, 10:08:51, 10:08:56, 10:09:01, 10:09:06, 10:09:11, 10:09:16, 10:09:21, 10:09:26, 10:09:31, 10:09:36, 10:09:41, 10:09:46, 10:09:51, 10:09:56, 10:10:01, 10:10:06, 10:10:11, 10:10:16, 10:10:21, 10:10:26, 10:10:31, 10:10:36, 10:10:41, 10:10:46, 10:10:51, 10:10:56, 10:11:01, 10:11:06, 10:11:11, 10:11:16, 10:11:21, 10:11:26, 10:11:31, 10:11:36, 10:11:41, 10:11:46, 10:11:51, 10:11:56, 10:12:01, 10:12:06, 10:12:11, 10:12:16, 10:12:21, 10:12:26, 10:12:31, 10:12:36, 10:12:41, 10:12:46, 10:12:51, 10:12:56, 10:13:01, 10:13:06, 10:13:11, 10:13:16, 10:13:21, 10:13:26, 10:13:31, 10:13:36, 10:13:41, 10:13:46, 10:13:51, 10:13:56, 10:14:01, 10:14:06, 10:14:11, 10:14:16, 10:14:21, 10:14:26, 10:14:31, 10:14:36, 10:14:41, 10:14:46, 10:14:51, 10:14:56, 10:15:01, 10:15:06, 10:15:11, 10:15:16, 10:15:21, 10:15:26, 10:15:26, 10:15:31, 10:15:36, 10:15:41, 10:15:46, 10:15:51, 10:15:56, 10:16:01, 10:16:06, 10:16:11, 10:16:16, 10:16:21, 10:16:26, 10:16:31, 10:16:36, 10:16:41, 10:20:27, 10:20:32, 10:20:37, 10:20:42, 10:20:47, 10:20:52, 10:20:57, 10:21:02, 10:21:07, 10:21:12, 10:21:17, 10:21:22, 10:21:27, 10:21:32, 10:21:37, 10:21:42, 10:21:47, 10:21:52, 10:21:57, 10:22:02, 10:22:07, 10:22:12, 10:22:17, 10:22:22, 10:22:27, 10:22:32, 10:22:37, 10:22:42, 10:22:47, 10:22:52, 10:22:57, 10:23:02, 10:23:07, 10:23:12, 10:23:17, 10:23:22, 10:23:27, 10:23:32, 10:23:37, 10:23:42, 10:23:48, 10:23:53, 10:25:57, 10:26:02, 10:26:07, 10:26:12, 10:26:17, 10:26:22, 10:26:27, 10:26:32, 10:26:37, 10:26:42, 10:26:47, 10:26:52, 10:26:57, 10:27:02, 10:27:07, 10:27:12, 10:27:17, 10:27:22, 10:27:27, 10:27:32, 10:27:37, 10:27:42, 10:27:47, 10:27:52, 10:27:57, 10:28:02, 10:28:07, 10:28:12, 10:28:17, 10:28:22, 10:28:27, 10:28:32, 10:28:37, 10:28:42, 10:28:47, 10:28:52, 10:28:57, 10:29:02, 10:29:07, 10:29:12, 01:27:23, 01:27:28, 01:27:33, 01:27:38, 01:27:43, 01:27:48, 01:27:53]

6.采用迭代法进行相减,在这里利用parse解析String类型数据

        for (int i = 0; i < size-1; i++) {
            String before = sl.get(i);
            Date before1 = sdf.parse(before);
            String after = sl.get(i + 1);
            Date after1 = sdf.parse(after);
            long between = after1.getTime() - before1.getTime();
            long day = between / (24 * 60 * 60 * 1000);
            long hour = (between / (60 * 60 * 1000) - day * 24);
            long min = ((between / (60 * 1000)) - day * 24 * 60 - hour * 60);
            long s = (between / 1000 - day * 24 * 60 * 60 - hour * 60 * 60 - min * 60);
            if (s != 5){
                System.out.println(day + "天" + hour + "小时" + min + "分" + s + "秒");
            }
        }

7.结果:

0天0小时0分0秒
0天0小时3分46秒
0天0小时0分6秒
0天0小时2分4秒
0天-9小时-1分-49秒

PS:中间类型转换的时候总感觉有点不对劲,可能多了些没用的步骤,但是excel中时间格式的数据直接读取的话输出的并不是我想要的hh:mm:ss,反正来回转换是有点不对劲,不过不一会儿做完了就不追究了,希望大伙找找问题嗷看看是不是不需要这么来回整.

附一下完整代码:

package com.ma.testPoi;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.junit.Test;

import java.io.FileInputStream;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

public class Complete {
    String PATH = "C:\\Users\\Administrator\\Desktop\\test.xls";


    @Test
    public void testRead01() throws Exception{
        SimpleDateFormat sdf=new SimpleDateFormat("hh:mm:ss");
        //引入文件流
        FileInputStream inputStream = new FileInputStream(PATH);
        //创建工作簿
        Workbook workbook = new HSSFWorkbook(inputStream);
        //取第一个表
        Sheet sheet = workbook.getSheetAt(0);
        //获取所有的行
        Row rowTitle = sheet.getRow(0);
        //引入数组
        //引入一个列存储数据
        List<String> sl=new ArrayList<String>();
        int rowCount = sheet.getPhysicalNumberOfRows();
        for (int rowNum = 0; rowNum < rowCount; rowNum++) {
            //获取行的数据
            Row rowData = sheet.getRow(rowNum);
            //看看有多少行
//            System.out.println(rowCount);
            if (rowData!=null){
                //读取列
                int cellCount = rowTitle.getPhysicalNumberOfCells();
                for (int cellNum = 0; cellNum < cellCount; cellNum++) {
                    //得到每一个表格的内容
                    Cell cell = rowData.getCell(cellNum);
                    //将表格内容转化为Date格式
                    Date dateCellValue = cell.getDateCellValue();
                    //转换为时分秒
                    String dateData = sdf.format(dateCellValue);
                    sl.add(dateData);
                }
            }
        }
        int size = sl.size();
//        System.out.println(sl);
        for (int i = 0; i < size-1; i++) {
            String before = sl.get(i);
            Date before1 = sdf.parse(before);
            String after = sl.get(i + 1);
            Date after1 = sdf.parse(after);
            long between = after1.getTime() - before1.getTime();
            long day = between / (24 * 60 * 60 * 1000);
            long hour = (between / (60 * 60 * 1000) - day * 24);
            long min = ((between / (60 * 1000)) - day * 24 * 60 - hour * 60);
            long s = (between / 1000 - day * 24 * 60 * 60 - hour * 60 * 60 - min * 60);
            if (s != 5){
                System.out.println(day + "天" + hour + "小时" + min + "分" + s + "秒");
            }
        }
        inputStream.close();
    }



}