背景：
每个月公司都会有发两个表，
一个是考勤表
另一个是工时表
需要我们对照考勤表把工时表完善
写了两次实在是感觉很浪费时间，于是就写了个代码代替人工
pom引用
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.14</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.14</version>
</dependency>
package com.justin.excel;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.io.*;
import java.text.SimpleDateFormat;
import java.util.HashMap;
import java.util.Map;
/**
* 描述:
* 读写Excel
*
* @author Justin.Sun
* @create 2018-07-30 17:34
*/
public class ExcelUtil {
private static final String EXCEL_XLS = "xls";
private static final String EXCEL_XLSX = "xlsx";
private static Map<Integer,Integer> time=new HashMap<Integer, Integer>();
private static Map<Integer,String> data=new HashMap<Integer, String>();
private static FileInputStream in;
private static Workbook workBook = null;
private static FileOutputStream out;
/**
* 读取考勤表并获取userName相关数据
* @param fileName 文件路径
* @param userName 名字
* @throws IOException
*/
public static void readExcel(String fileName,String userName) throws IOException {
Sheet sheet=getSheet(fileName);
// 第一行从0开始算
int rowNumber = sheet.getLastRowNum();
//逐行循环
for (int i = 1; i <= rowNumber; i++) {
Row row = sheet.getRow(i);
if (row==null){
System.out.println("获取到的行数"+i);
break;
}
Cell cell;
boolean arg=false;
//循环行内的列
for (int j=0;j<=row.getLastCellNum();j++){
cell=row.getCell(j);
if (cell==null){
break;
}
//获取数据的规则
if (i==2){
String value=cell.toString();
try{
int day=Integer.parseInt(value);
time.put(day,j);
}catch (Exception e){
continue;
}
}else if (i>2&&j==0&&userName.equals(cell.toString())){
arg=true;
for (int key:time.keySet()){
cell=row.getCell(time.get(key));
data.put(key,cell.toString().replace("\n","&").replaceAll(" ",""));
}
}
}
//得到了我想要的数据，可以结束循环了
if (arg){
System.out.println(data);
System.out.println("获取 "+userName+" 数据完成");
break;
}
}
close();
}
/**
* 写入数据到工时表
* @param fileName 文件路径
* @param userName 名字
* @throws IOException
*/
public static void writeExcel(String fileName,String userName) throws IOException {
SimpleDateFormat sdf=new SimpleDateFormat("d");
Sheet sheet=getSheet(fileName);
int rowNumber = sheet.getLastRowNum();
for (int i = 1; i <= rowNumber; i++) {
Row row = sheet.getRow(i);
if (row==null){
System.out.println("获取到的行数"+i);
break;
}
Cell cell;
//行循环
for (int j=0;j<=row.getLastCellNum();j++){
cell=row.getCell(j);
if (cell==null){
break;
}
//名字替换
if ("某某".equals(cell.toString())){
cell.setCellValue(userName);
}
//数据处理
if (i>3&&j==1){
try{
int day= Integer.parseInt(sdf.format(cell.getDateCellValue()));
String value=data.get(day);
int index=value.indexOf("&");
String startTime="";
String endTime="";
if (index>0){
startTime=value.substring(0,index);
endTime=value.substring(index+1,value.length());
}
cell=row.getCell(3);
cell.setCellValue(startTime);
cell=row.getCell(4);
cell.setCellValue(endTime);
if (startTime.equals("")&&endTime.equals("")){
cell=row.getCell(5);
cell.setCellValue("");
}
}catch (Exception e){
cell=row.getCell(5);
//找到引用函数的单元格
if (cell.getCellType()==Cell.CELL_TYPE_FORMULA){
//执行函数并得到返回值
FormulaEvaluator evaluator = workBook.getCreationHelper().createFormulaEvaluator();
CellValue cellValue=evaluator.evaluate(cell);
//替换新值到单元格
cell.setCellValue(cellValue.getNumberValue());
}
}
}
}
}
out=new FileOutputStream(fileName);
workBook.write(out);
close();
System.out.println("写入 "+userName+" 数据完成");
}
/**
* 创建连接并获取工作薄
* @param fileName 文件路径
* @return 工作薄对象
* @throws IOException
*/
private static Sheet getSheet(String fileName) throws IOException {
File file=new File(fileName);
in = new FileInputStream(file);
//Excel 2003
if(file.getName().endsWith(EXCEL_XLS)){
workBook = new HSSFWorkbook(in);
// Excel 2007/2010
}else if(file.getName().endsWith(EXCEL_XLSX)){
workBook = new XSSFWorkbook(in);
}
//默认获取第一个工作薄
return workBook.getSheetAt(0);
}
/**
* 关闭连接
* @throws IOException
*/
private static void close() throws IOException {
if (workBook!=null){
workBook.close();
}
if (in!=null){
in.close();
}
if (out!=null){
out.close();
}
}
}
public class Application {
private static String readFileName="E:/考勤表.xlsx";
private static String writeFileName="E:/空白工时表.xlsx";
private static String userName="Justin";
public static void main(String[] args) throws IOException {
//执行
ExcelUtil.readExcel( readFileName,userName);
ExcelUtil.writeExcel( writeFileName,userName);
}
}