设计一款可扩展和基于windows系统的一键处理表格小工具思路

原创总结/朱季谦

设计一款可扩展和基于windows系统的一键处理表格小工具思路

日常开发当中，业务人员经常会遇到一些重复性整理表格的事情，这时候，就可以通过一些方式进行自动化程序处理，提高工作（摸鱼）效率。

例如，业务人员有这样需要，日常需要手工整理以下原始xlsx表格数据，这些数据格式都是固定死，他们需要去除【手机号】这一列，然后在第一行增加一个表头标题【审计结果表】，同时需要将【日期】格式统一整理成yyyy-mm-dd格式的，最后需要在日期列前面增加一列【是否合格】，统一设置值为1。

整理后的表格如下：

注意，真实需求会比以上需求更加复杂，这里只是以一个比较简单的需求展开演示，来设计一个可一键傻瓜式自动化整理日常表格的工具。

工具的开发环境如下：

Java，Bat，需要依赖处理表格的poi的maven依赖。

一、创建一个maven工程，pom.xml依赖如下

<?xml version="1.0" encoding="UTF-8"?> <project xmlns="http://maven.apache.org/POM/4.0.0"          xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"          xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">     <modelVersion>4.0.0</modelVersion>      <groupId>org.example</groupId>     <artifactId>auto-put-file</artifactId>     <version>1.0-SNAPSHOT</version>      <properties>         <maven-clean-plugin.version>3.1.0</maven-clean-plugin.version>         <maven-compiler-plugin.version>3.8.1</maven-compiler-plugin.version>         <maven-dependency-plugin.version>3.1.2</maven-dependency-plugin.version>         <maven-deploy-plugin.version>2.8.2</maven-deploy-plugin.version>         <maven-install-plugin.version>2.5.2</maven-install-plugin.version>         <maven-jar-plugin.version>3.1.2</maven-jar-plugin.version>     </properties>     <dependencies>          <dependency>             <groupId>org.projectlombok</groupId>             <artifactId>lombok</artifactId>             <version>1.18.2</version>         </dependency>          <dependency>             <groupId>org.apache.commons</groupId>             <artifactId>commons-lang3</artifactId>             <version>3.2.1</version>         </dependency>          <dependency>             <groupId>org.apache.poi</groupId>             <artifactId>poi</artifactId>             <version>4.1.2</version>         </dependency>          <dependency>             <groupId>org.apache.poi</groupId>             <artifactId>poi-ooxml-schemas</artifactId>             <version>4.1.2</version>         </dependency>          <dependency>             <groupId>org.apache.poi</groupId>             <artifactId>poi-scratchpad</artifactId>             <version>4.1.2</version>         </dependency>          <dependency>             <groupId>org.apache.poi</groupId>             <artifactId>poi-ooxml</artifactId>             <version>4.1.2</version>         </dependency>          <dependency>             <groupId>fr.opensagres.xdocreport</groupId>             <artifactId>xdocreport</artifactId>             <version>2.0.2</version>         </dependency>          <dependency>             <groupId>org.apache.poi</groupId>             <artifactId>ooxml-schemas</artifactId>             <version>1.4</version>         </dependency>     </dependencies>       <build>             <plugins>                 <plugin>                     <artifactId>maven-clean-plugin</artifactId>                     <version>${maven-clean-plugin.version}</version>                 </plugin>                  <plugin>                     <artifactId>maven-deploy-plugin</artifactId>                     <version>${maven-deploy-plugin.version}</version>                 </plugin>                   <plugin>                     <artifactId>maven-install-plugin</artifactId>                     <version>${maven-install-plugin.version}</version>                 </plugin>                  <plugin>                     <artifactId>maven-jar-plugin</artifactId>                     <version>${maven-jar-plugin.version}</version>                 </plugin>                  <plugin>                     <groupId>org.apache.maven.plugins</groupId>                     <artifactId>maven-compiler-plugin</artifactId>                     <version>${maven-compiler-plugin.version}</version>                     <configuration>                         <encoding>UTF-8</encoding>                         <source>1.8</source>                         <target>1.8</target>                     </configuration>                 </plugin>                 <plugin>                     <groupId>org.apache.maven.plugins</groupId>                     <artifactId>maven-assembly-plugin</artifactId>                     <version>3.1.1</version>                     <configuration>                         <archive>                             <manifest>                                 <mainClass>com.put.AutoExcel</mainClass>                             </manifest>                         </archive>                         <descriptorRefs>                             <descriptorRef>jar-with-dependencies</descriptorRef>                         </descriptorRefs>                     </configuration>                     <executions>                         <execution>                             <id>make-assembly</id>                             <phase>package</phase>                             <goals>                                 <goal>single</goal>                             </goals>                         </execution>                     </executions>                 </plugin>             </plugins>      </build> </project> 

注意下， com.put.AutoExcel这一行需要填写你的main对应路径，如果路径不对的话，打成jar后，是无法通过java -jar xxx.jar运行的。

我在项目里依赖的4.1.2版本的org.apache.poi依赖包，最开始使用的是4.1.0版本的，但发现里面有一个很大的bug，就是使用XSSFSheet中处理指定行数据下移的方法sheet.shiftRows(0,sheet.getLastRowNum(),1,true,false)时，会发现指定下移位置之后的数据，都被删除完了，导致下移后的表格都成了一片空白。后来，我改成了4.1.2版本，才没有这个问题，但是，注意了，4.1.2版本依旧存在一个bug，那便是将第二列数据通过sheet.shiftColumns(1,sheet.getRow(0).getLastCellNum(),-1)左移覆盖掉第一列时，会出现以下异常：

Exception in thread "Thread-0" java.lang.IndexOutOfBoundsException 	at org.apache.xmlbeans.impl.store.Xobj.removeElement(Xobj.java:2206) 	at org.apache.xmlbeans.impl.store.Xobj.remove_element(Xobj.java:2236) 	at org.openxmlformats.schemas.spreadsheetml.x2006.main.impl.CTRowImpl.removeC(Unknown Source) 	at org.apache.poi.xssf.usermodel.XSSFRow.fixupCTCells(XSSFRow.java:612) 	at org.apache.poi.xssf.usermodel.XSSFRow.onDocumentWrite(XSSFRow.java:582) 	at org.apache.poi.xssf.usermodel.XSSFSheet.write(XSSFSheet.java:3640) 	at org.apache.poi.xssf.usermodel.XSSFSheet.commit(XSSFSheet.java:3585) 	at org.apache.poi.ooxml.POIXMLDocumentPart.onSave(POIXMLDocumentPart.java:490) 	at org.apache.poi.ooxml.POIXMLDocumentPart.onSave(POIXMLDocumentPart.java:495) 	at org.apache.poi.ooxml.POIXMLDocument.write(POIXMLDocument.java:236) 	at com.put.service.impl.ConToImageServiceImpl.export(ConToImageServiceImpl.java:64) 	at com.put.AutoExcel.lambda$main$0(AutoExcel.java:26) 	at java.lang.Thread.run(Thread.java:745) 

查看一些资料得知，即使到了5.x版本，该bug一直没有完全修复，只能通过先删除第一列后，再将第二列之后的数据往左移一列的方式，曲线解决这个反向移动问题。

二、基于Java SPI设计一套可便于后期扩展的接口实现

我在工程里使用到了Java SPI的服务发现机制，便于后期如果有需要进行工具处理新功能扩展，只需要增加一个实现类，放到com.put.service.DisposeService文件目录里，这样，后期就不需要改动原有工具的代码了。

1、先创建一个接口DisposeService：

package com.put.service;  /**  * TODO  *  * @author zhujiqian  * @date 2023/3/16 09:38  **/ public interface DisposeService {     public void export(String sourceFile, String sourceFileName); } 

这里的sourceFile是包括文件后缀的字符串名，例如：“测试表格文件.xlsx”，用来读取文件内容。

sourceFileName是没有的文件后缀的字符串名，例如：“测试表格文件”，用来创建用于存放已经整理的文件的目录。

2、创建一个实现类DisposeServiceImpl：

package com.put.service.impl;  import com.put.service.DisposeService; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.CellType; import org.apache.poi.ss.usermodel.Row; 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.FileInputStream; import java.io.FileNotFoundException; import java.io.FileOutputStream; import java.io.IOException;  import static com.put.utils.DateUtil.timeFormat;  /**  * TODO  *  * @author zhujiqian  * @date 2023/3/16 16:40  **/ public class DisposeServiceImpl implements DisposeService {     @Override     public void export(String sourceFile,String sourceFileName) {         System.out.println("开始整理"+sourceFileName+"文件");         try {             FileInputStream file = new FileInputStream(sourceFile);             XSSFWorkbook workbook = new XSSFWorkbook(file);             //处理第一个sheet,若需要处理多个sheet，可以自行扩展             XSSFSheet sheet = workbook.getSheetAt(0);             //移除表格第一列             removeCell(sheet,0);             //移除表格第一列后，接着将原先第二列的数据往左边移动，即变成了第一列             sheet.shiftColumns(1,sheet.getRow(0).getLastCellNum(),-1);             //表格最后一列往右移动一格             sheet.shiftColumns(sheet.getRow(0).getLastCellNum()-1,sheet.getRow(0).getLastCellNum(),1);             //在倒数第二列地方新增一个表头标题             sheet.getRow(0).createCell(sheet.getRow(0).getLastCellNum() - 2).setCellValue("是否合格");              for(int i = 1; i<= sheet.getLastRowNum(); i++){                 if(sheet.getRow(i) == null){                     continue;                 }              //单元格为空，则继续同一列的下一个单元格             if(sheet.getRow(i).getCell(sheet.getRow(i).getLastCellNum()-1) == null ){                 continue;             }             //调整最右边的申请时间数据             int cellNum = sheet.getRow(i).getLastCellNum();             XSSFCell cell = sheet.getRow(i).getCell(cellNum- 1);             cell.setCellType(CellType.STRING);             cell.setCellValue(timeFormat(cell.toString()));             //对倒数第二列标题为【是否合格】的列从第二行开始赋值为1             sheet.getRow(i).createCell(cellNum - 2).setCellValue(1);         }              //数据下移一行，空出第一行，设置新表头标题             sheet.shiftRows(0,sheet.getLastRowNum(),1,true,false);             XSSFRow rows = sheet.createRow(0);             rows.createCell(0).setCellValue("审计结果表");               FileOutputStream outFile =new FileOutputStream(System.getProperty("user.dir")+"//整理结果//"+sourceFileName+"//"+"处理完的表格.xlsx");              //写入到新文件里             workbook.write(outFile);             file.close();             outFile.flush();             outFile.close();             System.out.println("整理完成");         } catch (FileNotFoundException e) {             throw new RuntimeException(e);         } catch (IOException e) {             throw new RuntimeException(e);         }      }       public static void removeCell(XSSFSheet sheet, int index){          for (Row row : sheet) {              Cell cell = row.getCell(index);              if (cell != null) {                  row.removeCell(cell);              }          }     } } 

这个方法主要分成以下几个步骤：

1、读取源文件内容，创建一个可读取表格的XSSFWorkbook对象，并通过workbook.getSheetAt(0)获取第一个sheet表格；

FileInputStream file = new FileInputStream(sourceFile); XSSFWorkbook workbook = new XSSFWorkbook(file); //处理第一个sheet,若需要处理多个sheet，可以自行扩展 XSSFSheet sheet = workbook.getSheetAt(0); 

2、删除第一列数据，然后将第二列开始到最后一列的数据往左移动一列，即原本的第二列变成第一列，第三列变成第二列，依次移动；

//移除表格第一列 removeCell(sheet,0); //移除表格第一列后，接着将原先第二列的数据往左边移动，即变成了第一列 sheet.shiftColumns(1,sheet.getRow(0).getLastCellNum(),-1); 

removeCell(sheet,0)代码如下：

public static void removeCell(XSSFSheet sheet, int index){      for (Row row : sheet) {          Cell cell = row.getCell(index);          if (cell != null) {              row.removeCell(cell);          }      } } 

注意一点，前面有提到，直接使用sheet.shiftColumns(1,sheet.getRow(0).getLastCellNum(),-1)对第二列数据往左移动会报错，故而需要先删除第一列，再作迁移。

3、表格最后一列往右移动一格，然后在倒数第二列新增一个表头标题【是否合格】；

//表格最后一列往右移动一格 sheet.shiftColumns(sheet.getRow(0).getLastCellNum()-1,sheet.getRow(0).getLastCellNum(),1); //在倒数第二列地方新增一个表头标题 sheet.getRow(0).createCell(sheet.getRow(0).getLastCellNum() - 2).setCellValue("是否合格"); 

4、调整最右边的申请时间数据，统一改成“yyyy-mm-dd”格式，同时对倒数第二列标题为【是否合格】的列从第二行开始赋值为1；

for(int i = 1; i<= sheet.getLastRowNum(); i++){     if(sheet.getRow(i) == null){         continue;     }      //单元格为空，则继续同一列的下一个单元格     if(sheet.getRow(i).getCell(sheet.getRow(i).getLastCellNum()-1) == null ){         continue;     }     //调整最右边的申请时间数据     int cellNum = sheet.getRow(i).getLastCellNum();     XSSFCell cell = sheet.getRow(i).getCell(cellNum- 1);     cell.setCellType(CellType.STRING);     cell.setCellValue(timeFormat(cell.toString()));     //对倒数第二列标题为【是否合格】的列从第二行开始赋值为1     sheet.getRow(i).createCell(cellNum - 2).setCellValue(1); } 

5、所有数据下移一行，空出第一行设置新表头标题；

//数据下移一行，空出第一行，设置新表头标题 sheet.shiftRows(0,sheet.getLastRowNum(),1,true,false); XSSFRow rows = sheet.createRow(0); rows.createCell(0).setCellValue("审计结果表"); 

7、写入到指定目录的新文件，关闭读取；

FileOutputStream outFile =new FileOutputStream(System.getProperty("user.dir")+"//整理结果//"+sourceFileName+"//"+"处理完的表格.xlsx"); //写入到新文件里 workbook.write(outFile); file.close(); outFile.flush(); outFile.close(); System.out.println("整理完成"); 

其中，处理时间的方法代码如下，可支持对yyyy/mm/dd hh:mm:ss、yyyy/m/d h:mm:ss、yyyy/m/dd h:mm:ss、yyyymmdd、yyyy/mm/dd、yyyy/m/d、yyyy/m/dd、excel格式这些格式统一处理成“yyyy-mm-dd”：

public static String timeFormat(String date) {         if ("".equals(date) || date == null) return "时间为空";         if (date.length() < 5) return "时间格式错误";         if (date.charAt(4) == '-') return date;         String dateFormat = "";         switch (date.length()){             case 19:             case 10:                 dateFormat = date.substring(0, 4) + "-" + date.substring(5, 7) + "-" + date.substring(8, 10);                 break;             case 9:                 if (date.charAt(4) != '/' )break;             case 17:                 dateFormat = date.substring(0, 4) + "-" + date.charAt(5) + "-" + date.substring(7, 9);                 break;              case 8:                 if (date.charAt(4) != '/' ){                     dateFormat = date.substring(0, 4) + "-" + date.substring(4, 6) + "-" + date.substring(6);                     break;                 }             case 16:                 dateFormat = date.substring(0, 4) + "-" + date.charAt(5) + "-" + date.charAt(7);                 break;             case 5:                 return numberToDate(date);             default:                 return "时间格式错误";         }         return dateFormat;     }   public static String numberToDate(String number){         Calendar calendar = new GregorianCalendar(1900,0,-1);         Date date = DateUtils.addDays(calendar.getTime(),Integer.parseInt(number));         //对日期格式化操作         return new SimpleDateFormat("yyyy-MM-dd").format(date);     } 

因为是使用到Java SPI机制，故而需要在resource目录下创建一个META_INF.services目录，目录下创建一个与接口路径同名的文件：com.put.service.DisposeService。该文件里存放需要调用的DisposeService接口的实现类，然后就可以自动实现接口实现类的自动调用处理了。同理，后期若不需要调用某个实现类的方法了，只需要将该文件里的指定实现类路径去掉即可。

com.put.service.impl.DisposeServiceImpl 

三、最后，新增一个main启动

public class AutoExcel {        public static void main(String[] argv) {         start();     }       public static void start(){         //获取Java项目所在目录         File file = new File(System.getProperty("user.dir"));         for (File f : file.listFiles()){             if (!f.isDirectory() && f.getName().contains(".xlsx")){                 String sourceFile = f.getName();                 String sourceFileName = sourceFile.substring(0,sourceFile.lastIndexOf("."));                 if (StringUtils.isAnyBlank(sourceFile,sourceFileName))return;                 //创建文件目录                 createDirectory(sourceFileName);                 //基于SPI机制自动调用实现类来实现文件处理                 execute(sourceFile,sourceFileName);             }         }     }      public static void createDirectory(String sourceName){         File file = new File(System.getProperty("user.dir") + "//整理结果//" + sourceName);         if (!file.exists()){             file.mkdirs();         }     }      public static void execute(String sourceFile, String sourceName){         ServiceLoader<DisposeService> serviceLoader = ServiceLoader.load(DisposeService.class);         Iterator<DisposeService> serviceIterator = serviceLoader.iterator();         while (serviceIterator.hasNext()){             DisposeService service = serviceIterator.next();             if (service == null)return;             new Thread(()->{                 service.export(sourceFile,sourceName);             }).start();         }     } } 

基于以上代码来梳理说明一下流程：

1、获取Java项目所在的文件路径，然后获取该路径的.xlsx表格文件名，若是存在多个.xlsx表格文件，可以同时批量处理。

File file = new File(System.getProperty("user.dir")); for (File f : file.listFiles()){     if (!f.isDirectory() && f.getName().contains(".xlsx")){         String sourceFile = f.getName();         String sourceFileName = sourceFile.substring(0,sourceFile.lastIndexOf("."));         if (StringUtils.isAnyBlank(sourceFile,sourceFileName))return;         ......     } } 

2、创建处理好的表格统一存放目录，路径名为，当前目录//整理结果//文件名同名文件夹//

public static void createDirectory(String sourceName){     File file = new File(System.getProperty("user.dir") + "//整理结果//" + sourceName);     if (!file.exists()){         file.mkdirs();     } } 

例如，处理的文件名为“测试表格文件.xlsx”，那么创建的目录结构效果如下：

3、基于Java SPI机制，读取获取接口对应的com.put.service.DisposeService文件内容，然后反射得到文件里指定的实现类，循环执行各个实现类的方法：

public static void execute(String sourceFile, String sourceName){     ServiceLoader<DisposeService> serviceLoader = ServiceLoader.load(DisposeService.class);     Iterator<DisposeService> serviceIterator = serviceLoader.iterator();     while (serviceIterator.hasNext()){         DisposeService service = serviceIterator.next();         if (service == null)return;         new Thread(()->{             service.export(sourceFile,sourceName);         }).start();     } } 

该方法里我用了多线程并发处理，因为各个文件的处理无任何依赖，若是大批量处理时，串行执行实在太慢，但多线程处理同时也会存在一个问题是，若大批量表格文件中每个文件数据量都很大的话，电脑内存太小的话，可能会出现内存溢出问题。

三、maven项目打成一个jar，然后编写一个可在windows运行的bat脚本。

拷贝最后一个auto-put-file-1.0-SNAPSHOT-jar-with-dependencies.jar到随意一个目录里，然后编写一个名字为start.bat脚本：

@echo off java -jar auto-put-file-1.0-SNAPSHOT-jar-with-dependencies.jar 

将start.bat和auto-put-file-1.0-SNAPSHOT-jar-with-dependencies.jar放同一个目录里，然后将需要处理的Excel文件放到该目录下，点击start.bat即可运行。