利用poi生成excel考勤表

最新推荐文章于 2020-06-11 14:33:51 发布
原创最新推荐文章于 2020-06-11 14:33:51 发布 · 691 阅读
5 ·
CC 4.0 BY-SA版权
文章标签：
#java
JAVA 专栏收录该内容
60 篇文章
订阅专栏
本文介绍如何使用Apache POI库自动生成包含特定日期范围内的考勤记录的Excel文件，包括设置样式、填充数据及自动计算日期等功能。
摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >
今天利用了Apache poi 编写了一个生成excel的考勤表，Apache poi链接地址为http://poi.apache.org/，代码如下：
package com.pom;

import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Sheet;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.Random;

/**
 * Created by admin on 2016/6/2.
 */
public class PomExcel {

    public void pomExcelOperation(){
        try {
            //获取当前日期
            Calendar calendar = Calendar.getInstance();
            //当前月份
            int month = calendar.get(Calendar.MONTH) + 1;
            //设置现在日期为本月1号
            calendar.set(Calendar.DATE,1);
            //设置月份为下一个月
            calendar.add(Calendar.MONTH,1);
            //减一天为上月最后一天
            calendar.add(Calendar.DATE,-1);
            DateFormat df = new SimpleDateFormat("yyyy-MM-dd");
            //获取本月有多少天
            int day = Integer.parseInt(df.format(calendar.getTime()).substring(8));
            String filePath = "e:/admin-"+calendar.get(Calendar.YEAR)+"."+month+"考勤.xls";
            HSSFWorkbook workbook = null;
            File file = new File(filePath);
            if(!file.exists()) {
                file = new File(filePath);
                file.createNewFile();
                workbook = new HSSFWorkbook();
                //sheet的名字
                String sheetName = "admin";
                HSSFSheet hssfSheet = workbook.createSheet(sheetName);
                //创建第一行
                HSSFRow hssfRow = hssfSheet.createRow(0);
                String [] firstRow = {"工号","姓名","出勤日期","上班时间","下班时间","说明"};
                HSSFCellStyle hssfCellStyle = workbook.createCellStyle();
                HSSFFont hssfFont = workbook.createFont();
                //字体大小
                hssfFont.setFontHeightInPoints((short)11);
                //加粗
                hssfFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
                hssfCellStyle.setFont(hssfFont);
                //左右居中
                hssfCellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
                //上下居中
                hssfCellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
                //hssfRow.setRowStyle(hssfCellStyle);
                //创建单元格
                HSSFCell hssfCell = null;
                for(int i = 0;i < firstRow.length;i++){
                    hssfCell = hssfRow.createCell(i);
                    hssfCell.setCellValue(firstRow[i]);
                    hssfCell.setCellStyle(hssfCellStyle);
                    //设置列宽，256表示每个字符大小
                    if(i == 0 || i == 2){
                        hssfSheet.setColumnWidth(i,15*256);
                    }else if(i == 1){
                        hssfSheet.setColumnWidth(i,10*256);
                    }
                }
                //从第二行开始创建
                for(int i = 1;i < day+1;i++){
                    String nowDate = calendar.get(Calendar.YEAR)+"-"+month+"-"+i;
                    hssfRow = hssfSheet.createRow(i);
                    HSSFCellStyle cellStyle = workbook.createCellStyle();
                    //左右居中
                    cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
                    //上下居中
                    cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
                    //创建单元格
                    for(int j = 0;j < firstRow.length;j++){
                        hssfCell = hssfRow.createCell(j);
                        hssfCell.setCellStyle(cellStyle);
                        if(j == 0){
                            hssfCell.setCellValue("0000060316");
                        }else if(j == 1){
                            hssfCell.setCellValue(sheetName);
                        }else if(j == 2){
                            HSSFDataFormat hssfDataFormat = workbook.createDataFormat();
                            //HSSFCellStyle cellStyle = workbook.createCellStyle();
                            //格式化时间
                            //cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy"));
                            //hssfCell.setCellStyle(cellStyle);
                            nowDate = calendar.get(Calendar.YEAR)+"-"+month+"-"+i;
                            hssfCell.setCellValue(df.format(df.parse(nowDate)));
                        }else if(j == 3 || j == 4){
                            //设置日期
                            calendar.setTime(df.parse(nowDate));
                            //获取随机时间
                            Random random = new Random();
                            //判断周6，周日
                            if(calendar.get(Calendar.DAY_OF_WEEK) != Calendar.SATURDAY &&
                                    calendar.get(Calendar.DAY_OF_WEEK) != Calendar.SUNDAY){
                                if(j == 3){
                                    String moTime = "8:";
                                    int randomNum = random.nextInt(29);
                                    if(randomNum < 10){
                                        if(randomNum == 0){
                                           randomNum = 7;
                                        }
                                        moTime = moTime + "0" + randomNum;
                                    }else {
                                        moTime = moTime + randomNum;
                                    }
                                    hssfCell.setCellValue(moTime);
                                }else {
                                    String afTime = "18:";
                                    int num = random.nextInt(40);
                                    if(num < 10){
                                        if(num == 0){
                                            num = 7;
                                        }
                                        afTime = afTime + "0" + num;
                                    }else {
                                        afTime = afTime + num;
                                    }
                                    hssfCell.setCellValue(afTime);
                                }
                            }
                        }else if(j == 5){
                            //设置日期
                            calendar.setTime(df.parse(nowDate));
                            //判断周6，周日
                            if(calendar.get(Calendar.DAY_OF_WEEK) == Calendar.SATURDAY  ||
                                    calendar.get(Calendar.DAY_OF_WEEK) == Calendar.SUNDAY){
                                hssfCell.setCellValue("周末");
                            }
                        }
                    }
                }
                FileOutputStream fileOutputStream = new FileOutputStream(filePath);
                workbook.write(fileOutputStream);
            }
        } catch (IOException e) {
            e.printStackTrace();
        } catch (ParseException e) {
            e.printStackTrace();
        }
    }

    public static void main(String [] args){
        PomExcel pomExcel = new PomExcel();
        pomExcel.pomExcelOperation();
    }
}