原创总结/朱季谦
设计一款可扩展和基于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即可运行。