使用java对excel表格进行操作

在对表格进行操作的时候，有时候需要对时间进行比对，这个时候可以把时间转化为时间戳进行比对。

package com.example.demo;

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.junit.jupiter.api.Test;
import org.springframework.boot.test.context.SpringBootTest;

import java.io.*;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

@SpringBootTest
class DemoApplicationTests {

    final String FILE_NAME = "布吉河81202003"; //文件名
    final int INTERVAL = 5; //时间间隔
    final String TYPE = "bujihe//";

    @Test
    void contextLoads() {
        // 此处为我创建Excel路径：E:/zhanhj/studysrc/jxl下
        File file = new File("D://Homework//" + TYPE + FILE_NAME + ".xlsx");
        readExcel(INTERVAL, file);
    }

    public long toTimeStamp(String s) {
        SimpleDateFormat simpleDateFormat = new SimpleDateFormat("MM/dd/yyyy HH:mm:ss");
        try {
            Date date = simpleDateFormat.parse(s);
            long ts = date.getTime();
            return ts;
        } catch (ParseException e) {
            e.printStackTrace();
        }
        return 0;
    }

    // 去读Excel的方法readExcel，该方法的入口参数为一个File对象
    public void readExcel(int INTERVAL, File file) {
        try {
            // 创建输入流，读取Excel
            InputStream is = new FileInputStream(file.getAbsolutePath());
            // jxl提供的Workbook类
            Workbook wb = new XSSFWorkbook(is);
            Sheet sheet = wb.getSheetAt(0);
            int rownum = sheet.getPhysicalNumberOfRows();
            long time = toTimeStamp(String.valueOf(sheet.getRow(0).getCell(0))) - toTimeStamp(String.valueOf(sheet.getRow(1).getCell(0)));
            FileWriter fileWriter = new FileWriter(new File("D://Homework//" + TYPE + FILE_NAME + "-2.txt"));
            fileWriter.write("时间间隔应为：" + (time/1000/60) + "分钟，以下为异常数据：\n");
            for (int i = 1; i < rownum; i++) {
                Row row = sheet.getRow(i);
                Row lastRow = null;
                lastRow = sheet.getRow(i - 1);
                if (row.getCell(0) == null || String.valueOf(row.getCell(0)).equals("")) {
                    break;
                }
                long curTmp = toTimeStamp(String.valueOf(row.getCell(0)));
                long lastTmp = 0;
                if (lastRow != null) {
                    lastTmp = toTimeStamp(String.valueOf(lastRow.getCell(0)));
                }
                if ((curTmp + time) != lastTmp) {
                    //fileWriter.write( String.valueOf(row.getCell(0))+ "  与上一个时间段相差了" + ((lastTmp - curTmp) / 1000 / 60) + "分钟   " + "第" + (i + 1) + "行有问题" +"\n");
                    fileWriter.write( String.valueOf(row.getCell(0))+ "  与上一个时间段相差了" + ((lastTmp - curTmp) / 1000 / 60) + "分钟" + "\n");
                }
            }
            fileWriter.close();
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

}