背景: 每个月公司都会有发两个表, 一个是考勤表 另一个是工时表 需要我们对照考勤表把工时表完善 写了两次实在是感觉很浪费时间,于是就写了个代码代替人工 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); } }