【用java写的一个excel签到表】

画页面

import cn.hutool.core.date.DateUtil;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import javax.swing.*;
import java.awt.*;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.util.ArrayList;
import java.util.Date;


public class AwtSwing {
    JFrame frame = new JFrame("日常打卡");
    static JButton signIn = new JButton("签到");
    static JButton signOut = new JButton("签退");
    static String fileName  = "C:\\Users\\Public\\Data\\PunchIn.xlsx";
    JLabel label = new JLabel("本月平均签到时长:");
    JLabel duration = new JLabel("10");
    public static void main(String[] args) {
        AwtSwing as = new AwtSwing();
        as.show();
        signIn.addActionListener(new ActionListener() {
            @Override
            public void actionPerformed(ActionEvent e) {
                //获取签到表，签到
                Date date = DateUtil.date(System.currentTimeMillis());
                DataTime dataTime = new DataTime();
                dataTime.setData(DateUtil.today());
                dataTime.setMorTime(DateUtil.formatTime(date));
                dataTime.setAftTime(DateUtil.formatTime(date));
                String sheetName = DateUtil.format(date, "yyyy-MM");
                File file = new File(fileName);
                if (!file.exists()){
                    ArrayList<DataTime> objects = new ArrayList<>();
                    objects.add(dataTime);
                    EasyExcelUtil.init(fileName,sheetName,objects);
                }
                try (FileInputStream fileInputStream = new FileInputStream(file);
                     XSSFWorkbook xssfWorkbook = new XSSFWorkbook(fileInputStream);) {
                   if (xssfWorkbook.getSheet(sheetName)==null){
                        WriteData.writeData(sheetName,fileName);
                   }else {
                       if (!EasyExcelUtil.readDate(fileName,sheetName)){
                           System.out.println("不能重复签到");
                       }else {
                           WriteData.writeData(dataTime,sheetName,fileName);
                       }
                   }
                } catch (FileNotFoundException fileNotFoundException) {
                    fileNotFoundException.printStackTrace();
                } catch (IOException ioException) {
                    ioException.printStackTrace();
                }
            }
        });

        signOut.addActionListener(new ActionListener() {
            @Override
            public void actionPerformed(ActionEvent e) {
                //获取签到表签退
                Date date = DateUtil.date(System.currentTimeMillis());
                String sheetName = DateUtil.format(date, "yyyy-MM");
                try {
                    WriteData.writeData(sheetName,fileName,DateUtil.today(),DateUtil.formatTime(date));
                } catch (IOException ioException) {
                    ioException.printStackTrace();
                }
            }
        });
    }

    public AwtSwing() {
        frame.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
        frame.setSize(300,250);
        frame.setLocationRelativeTo(null);
        frame.setBackground(Color.gray);
        signIn.setBounds(40, 75, 80, 30);
        signOut.setBounds(160, 75, 80, 30);
        label.setBounds(40,115,100,40);
        duration.setBounds(145,115,20,40);
        frame.add(duration);
        frame.add(label);
        frame.add(signIn);
        frame.add(signOut);
    }

    public void show() {
        frame.setLayout(null);
        frame.setVisible(true);
    }
}

数据实体：

import com.alibaba.excel.annotation.ExcelProperty;
import lombok.Data;
import lombok.EqualsAndHashCode;
import lombok.ToString;

import java.io.Serializable;

@Data
@ToString
@EqualsAndHashCode
public class DataTime implements Serializable {
    @ExcelProperty(value = "日期",index = 0)
    private String data;
    @ExcelProperty(value = "上午签到时间",index = 1)
    private String morTime;
    @ExcelProperty(value = "下午签到时间",index = 2)
    private String aftTime;
    @ExcelProperty(value = "本日平均工作时间",index = 3)
    private String aveWorkTimeD;
    @ExcelProperty(value = "本月平均工作时间",index = 4)
    private String aveWorkTimeM;
}

EasyExcelUtiL工具类：

import cn.hutool.core.date.DateUtil;
import com.alibaba.excel.EasyExcel;
import java.util.List;


public class EasyExcelUtil {

    /**
     * EasyExcel工具类 第一次写数据
     * @param absFilePath 绝对路径
     * @param sheetName 标签页名字
     */
    public static void init(String absFilePath, String sheetName,List<DataTime> dataTimes){
        EasyExcel.write(absFilePath, DataTime.class).sheet(sheetName).doWrite(dataTimes);
    }

    public static Boolean readDate(String fileName,String sheetName){
        // 这里 需要指定读用哪个class去读，然后读取第一个sheet 同步读取会自动finish
        List<DataTime> list = EasyExcel.read(fileName).head(DataTime.class).sheet(sheetName).doReadSync();
        for (DataTime data : list) {
            if (data.getData().equals(DateUtil.today())) {
                return false;
            }
        }
        return true;
    }

}

动态的修改数据

import cn.hutool.core.date.BetweenFormatter;
import cn.hutool.core.date.DateUtil;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;

public class WriteData {

    /**
     *
     * @param dataTime
     * @param sheetName
     * @param filename
     * @throws IOException
     */
    public static void writeData(DataTime dataTime,String sheetName,String filename) throws IOException {
        File file = new File(filename);
        XSSFWorkbook xssfWorkbook = null;
        FileOutputStream fileOutputStream = null;
        try {
            FileInputStream fileInputStream = new FileInputStream(file);
            xssfWorkbook = new XSSFWorkbook(fileInputStream);
            XSSFSheet sheet = xssfWorkbook.getSheet(sheetName);
            fileOutputStream = new FileOutputStream(filename);
            XSSFRow row = sheet.createRow(sheet.getLastRowNum() + 1);//新增一行
            XSSFCell cell1 = row.createCell(0);//行中的第“0+1”列
            XSSFCell cell2 = row.createCell(1);
            XSSFCell cell3 = row.createCell(2);
            cell1.setCellValue(dataTime.getData());//列中放的值
            cell2.setCellValue(dataTime.getMorTime());
            cell3.setCellValue(dataTime.getAftTime());
            fileOutputStream.flush();
            xssfWorkbook.write(fileOutputStream);
            fileOutputStream.close();
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
        }
    }

    /**
     * 新建一个sheet
     *
     * @param sheetName
     * @param filename
     * @throws IOException
     */
    public static void writeData(String sheetName,String filename) throws IOException {
        File file = new File(filename);
        FileOutputStream fileOutputStream = null;
        XSSFWorkbook excel=null;
        try {
            FileInputStream fileInputStream = new FileInputStream(file);
            excel = new XSSFWorkbook(fileInputStream);
            excel.createSheet(sheetName);
            fileOutputStream = new FileOutputStream(filename);
            fileOutputStream.flush();
            excel.write(fileOutputStream);
            fileOutputStream.close();
            excel.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    /**
     * 下午的签退
     * @param sheetName
     * @param filename
     * @param dataTody
     * @param after
     * @throws IOException
     */
    public static void writeData(String sheetName,String filename,String dataTody,String after) throws IOException {
        File file = new File(filename);
        FileOutputStream fileOutputStream = null;
        XSSFWorkbook excel=null;
        try {
            FileInputStream fileInputStream = new FileInputStream(file);
            excel = new XSSFWorkbook(fileInputStream);
            XSSFSheet sheet = excel.getSheet(sheetName);
            for (int i = 1; i <= sheet.getLastRowNum(); i++) {
                XSSFRow row = sheet.getRow(i);
                String value0Cell = row.getCell(0).toString();
                String value1Cell = row.getCell(1).toString();
                if (value0Cell.equals(dataTody)) {
                    sheet.getRow(i).getCell(2).setCellValue(after);
                    String between = DateUtil.formatBetween(DateUtil.parse(value0Cell + " " + value1Cell),
                            DateUtil.parse(value0Cell + " " + after), BetweenFormatter.Level.MINUTE);
                    sheet.getRow(i).getCell(3).setCellValue(between);
                }
            }
            fileOutputStream = new FileOutputStream(filename);
            fileOutputStream.flush();
            excel.write(fileOutputStream);
            fileOutputStream.close();
            excel.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }


}

最终效果：有待完善