经验首页 前端设计 程序设计 Java相关 移动开发 数据库/运维 软件/图像 大数据/云计算 其他经验
当前位置:技术经验 » 数据库/运维 » Windows » 查看文章
设计一款可扩展和基于windows系统的一键处理表格小工具思路
来源:cnblogs  作者:朱季谦  时间:2023/3/17 8:59:02  对本文有异议

原创总结/朱季谦

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

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

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

整理后的表格如下:
image

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

工具的开发环境如下:

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

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

  1. <?xml version="1.0" encoding="UTF-8"?>
  2. <project xmlns="http://maven.apache.org/POM/4.0.0"
  3. xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  4. xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
  5. <modelVersion>4.0.0</modelVersion>
  6. <groupId>org.example</groupId>
  7. <artifactId>auto-put-file</artifactId>
  8. <version>1.0-SNAPSHOT</version>
  9. <properties>
  10. <maven-clean-plugin.version>3.1.0</maven-clean-plugin.version>
  11. <maven-compiler-plugin.version>3.8.1</maven-compiler-plugin.version>
  12. <maven-dependency-plugin.version>3.1.2</maven-dependency-plugin.version>
  13. <maven-deploy-plugin.version>2.8.2</maven-deploy-plugin.version>
  14. <maven-install-plugin.version>2.5.2</maven-install-plugin.version>
  15. <maven-jar-plugin.version>3.1.2</maven-jar-plugin.version>
  16. </properties>
  17. <dependencies>
  18. <dependency>
  19. <groupId>org.projectlombok</groupId>
  20. <artifactId>lombok</artifactId>
  21. <version>1.18.2</version>
  22. </dependency>
  23. <dependency>
  24. <groupId>org.apache.commons</groupId>
  25. <artifactId>commons-lang3</artifactId>
  26. <version>3.2.1</version>
  27. </dependency>
  28. <dependency>
  29. <groupId>org.apache.poi</groupId>
  30. <artifactId>poi</artifactId>
  31. <version>4.1.2</version>
  32. </dependency>
  33. <dependency>
  34. <groupId>org.apache.poi</groupId>
  35. <artifactId>poi-ooxml-schemas</artifactId>
  36. <version>4.1.2</version>
  37. </dependency>
  38. <dependency>
  39. <groupId>org.apache.poi</groupId>
  40. <artifactId>poi-scratchpad</artifactId>
  41. <version>4.1.2</version>
  42. </dependency>
  43. <dependency>
  44. <groupId>org.apache.poi</groupId>
  45. <artifactId>poi-ooxml</artifactId>
  46. <version>4.1.2</version>
  47. </dependency>
  48. <dependency>
  49. <groupId>fr.opensagres.xdocreport</groupId>
  50. <artifactId>xdocreport</artifactId>
  51. <version>2.0.2</version>
  52. </dependency>
  53. <dependency>
  54. <groupId>org.apache.poi</groupId>
  55. <artifactId>ooxml-schemas</artifactId>
  56. <version>1.4</version>
  57. </dependency>
  58. </dependencies>
  59. <build>
  60. <plugins>
  61. <plugin>
  62. <artifactId>maven-clean-plugin</artifactId>
  63. <version>${maven-clean-plugin.version}</version>
  64. </plugin>
  65. <plugin>
  66. <artifactId>maven-deploy-plugin</artifactId>
  67. <version>${maven-deploy-plugin.version}</version>
  68. </plugin>
  69. <plugin>
  70. <artifactId>maven-install-plugin</artifactId>
  71. <version>${maven-install-plugin.version}</version>
  72. </plugin>
  73. <plugin>
  74. <artifactId>maven-jar-plugin</artifactId>
  75. <version>${maven-jar-plugin.version}</version>
  76. </plugin>
  77. <plugin>
  78. <groupId>org.apache.maven.plugins</groupId>
  79. <artifactId>maven-compiler-plugin</artifactId>
  80. <version>${maven-compiler-plugin.version}</version>
  81. <configuration>
  82. <encoding>UTF-8</encoding>
  83. <source>1.8</source>
  84. <target>1.8</target>
  85. </configuration>
  86. </plugin>
  87. <plugin>
  88. <groupId>org.apache.maven.plugins</groupId>
  89. <artifactId>maven-assembly-plugin</artifactId>
  90. <version>3.1.1</version>
  91. <configuration>
  92. <archive>
  93. <manifest>
  94. <mainClass>com.put.AutoExcel</mainClass>
  95. </manifest>
  96. </archive>
  97. <descriptorRefs>
  98. <descriptorRef>jar-with-dependencies</descriptorRef>
  99. </descriptorRefs>
  100. </configuration>
  101. <executions>
  102. <execution>
  103. <id>make-assembly</id>
  104. <phase>package</phase>
  105. <goals>
  106. <goal>single</goal>
  107. </goals>
  108. </execution>
  109. </executions>
  110. </plugin>
  111. </plugins>
  112. </build>
  113. </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)左移覆盖掉第一列时,会出现以下异常:

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

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

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

image

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

1、先创建一个接口DisposeService:

  1. package com.put.service;
  2. /**
  3. * TODO
  4. *
  5. * @author zhujiqian
  6. * @date 2023/3/16 09:38
  7. **/
  8. public interface DisposeService {
  9. public void export(String sourceFile, String sourceFileName);
  10. }

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

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

2、创建一个实现类DisposeServiceImpl:

  1. package com.put.service.impl;
  2. import com.put.service.DisposeService;
  3. import org.apache.poi.ss.usermodel.Cell;
  4. import org.apache.poi.ss.usermodel.CellType;
  5. import org.apache.poi.ss.usermodel.Row;
  6. import org.apache.poi.xssf.usermodel.XSSFCell;
  7. import org.apache.poi.xssf.usermodel.XSSFRow;
  8. import org.apache.poi.xssf.usermodel.XSSFSheet;
  9. import org.apache.poi.xssf.usermodel.XSSFWorkbook;
  10. import java.io.FileInputStream;
  11. import java.io.FileNotFoundException;
  12. import java.io.FileOutputStream;
  13. import java.io.IOException;
  14. import static com.put.utils.DateUtil.timeFormat;
  15. /**
  16. * TODO
  17. *
  18. * @author zhujiqian
  19. * @date 2023/3/16 16:40
  20. **/
  21. public class DisposeServiceImpl implements DisposeService {
  22. @Override
  23. public void export(String sourceFile,String sourceFileName) {
  24. System.out.println("开始整理"+sourceFileName+"文件");
  25. try {
  26. FileInputStream file = new FileInputStream(sourceFile);
  27. XSSFWorkbook workbook = new XSSFWorkbook(file);
  28. //处理第一个sheet,若需要处理多个sheet,可以自行扩展
  29. XSSFSheet sheet = workbook.getSheetAt(0);
  30. //移除表格第一列
  31. removeCell(sheet,0);
  32. //移除表格第一列后,接着将原先第二列的数据往左边移动,即变成了第一列
  33. sheet.shiftColumns(1,sheet.getRow(0).getLastCellNum(),-1);
  34. //表格最后一列往右移动一格
  35. sheet.shiftColumns(sheet.getRow(0).getLastCellNum()-1,sheet.getRow(0).getLastCellNum(),1);
  36. //在倒数第二列地方新增一个表头标题
  37. sheet.getRow(0).createCell(sheet.getRow(0).getLastCellNum() - 2).setCellValue("是否合格");
  38. for(int i = 1; i<= sheet.getLastRowNum(); i++){
  39. if(sheet.getRow(i) == null){
  40. continue;
  41. }
  42. //单元格为空,则继续同一列的下一个单元格
  43. if(sheet.getRow(i).getCell(sheet.getRow(i).getLastCellNum()-1) == null ){
  44. continue;
  45. }
  46. //调整最右边的申请时间数据
  47. int cellNum = sheet.getRow(i).getLastCellNum();
  48. XSSFCell cell = sheet.getRow(i).getCell(cellNum- 1);
  49. cell.setCellType(CellType.STRING);
  50. cell.setCellValue(timeFormat(cell.toString()));
  51. //对倒数第二列标题为【是否合格】的列从第二行开始赋值为1
  52. sheet.getRow(i).createCell(cellNum - 2).setCellValue(1);
  53. }
  54. //数据下移一行,空出第一行,设置新表头标题
  55. sheet.shiftRows(0,sheet.getLastRowNum(),1,true,false);
  56. XSSFRow rows = sheet.createRow(0);
  57. rows.createCell(0).setCellValue("审计结果表");
  58. FileOutputStream outFile =new FileOutputStream(System.getProperty("user.dir")+"//整理结果//"+sourceFileName+"//"+"处理完的表格.xlsx");
  59. //写入到新文件里
  60. workbook.write(outFile);
  61. file.close();
  62. outFile.flush();
  63. outFile.close();
  64. System.out.println("整理完成");
  65. } catch (FileNotFoundException e) {
  66. throw new RuntimeException(e);
  67. } catch (IOException e) {
  68. throw new RuntimeException(e);
  69. }
  70. }
  71. public static void removeCell(XSSFSheet sheet, int index){
  72. for (Row row : sheet) {
  73. Cell cell = row.getCell(index);
  74. if (cell != null) {
  75. row.removeCell(cell);
  76. }
  77. }
  78. }
  79. }

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

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

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

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

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

removeCell(sheet,0)代码如下:

  1. public static void removeCell(XSSFSheet sheet, int index){
  2. for (Row row : sheet) {
  3. Cell cell = row.getCell(index);
  4. if (cell != null) {
  5. row.removeCell(cell);
  6. }
  7. }
  8. }

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

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

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

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

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

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

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

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

  1. FileOutputStream outFile =new FileOutputStream(System.getProperty("user.dir")+"//整理结果//"+sourceFileName+"//"+"处理完的表格.xlsx");
  2. //写入到新文件里
  3. workbook.write(outFile);
  4. file.close();
  5. outFile.flush();
  6. outFile.close();
  7. 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”:

  1. public static String timeFormat(String date) {
  2. if ("".equals(date) || date == null) return "时间为空";
  3. if (date.length() < 5) return "时间格式错误";
  4. if (date.charAt(4) == '-') return date;
  5. String dateFormat = "";
  6. switch (date.length()){
  7. case 19:
  8. case 10:
  9. dateFormat = date.substring(0, 4) + "-" + date.substring(5, 7) + "-" + date.substring(8, 10);
  10. break;
  11. case 9:
  12. if (date.charAt(4) != '/' )break;
  13. case 17:
  14. dateFormat = date.substring(0, 4) + "-" + date.charAt(5) + "-" + date.substring(7, 9);
  15. break;
  16. case 8:
  17. if (date.charAt(4) != '/' ){
  18. dateFormat = date.substring(0, 4) + "-" + date.substring(4, 6) + "-" + date.substring(6);
  19. break;
  20. }
  21. case 16:
  22. dateFormat = date.substring(0, 4) + "-" + date.charAt(5) + "-" + date.charAt(7);
  23. break;
  24. case 5:
  25. return numberToDate(date);
  26. default:
  27. return "时间格式错误";
  28. }
  29. return dateFormat;
  30. }
  31. public static String numberToDate(String number){
  32. Calendar calendar = new GregorianCalendar(1900,0,-1);
  33. Date date = DateUtils.addDays(calendar.getTime(),Integer.parseInt(number));
  34. //对日期格式化操作
  35. return new SimpleDateFormat("yyyy-MM-dd").format(date);
  36. }

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

  1. com.put.service.impl.DisposeServiceImpl

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

  1. public class AutoExcel {
  2. public static void main(String[] argv) {
  3. start();
  4. }
  5. public static void start(){
  6. //获取Java项目所在目录
  7. File file = new File(System.getProperty("user.dir"));
  8. for (File f : file.listFiles()){
  9. if (!f.isDirectory() && f.getName().contains(".xlsx")){
  10. String sourceFile = f.getName();
  11. String sourceFileName = sourceFile.substring(0,sourceFile.lastIndexOf("."));
  12. if (StringUtils.isAnyBlank(sourceFile,sourceFileName))return;
  13. //创建文件目录
  14. createDirectory(sourceFileName);
  15. //基于SPI机制自动调用实现类来实现文件处理
  16. execute(sourceFile,sourceFileName);
  17. }
  18. }
  19. }
  20. public static void createDirectory(String sourceName){
  21. File file = new File(System.getProperty("user.dir") + "//整理结果//" + sourceName);
  22. if (!file.exists()){
  23. file.mkdirs();
  24. }
  25. }
  26. public static void execute(String sourceFile, String sourceName){
  27. ServiceLoader<DisposeService> serviceLoader = ServiceLoader.load(DisposeService.class);
  28. Iterator<DisposeService> serviceIterator = serviceLoader.iterator();
  29. while (serviceIterator.hasNext()){
  30. DisposeService service = serviceIterator.next();
  31. if (service == null)return;
  32. new Thread(()->{
  33. service.export(sourceFile,sourceName);
  34. }).start();
  35. }
  36. }
  37. }

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

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

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

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

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

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

image

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

  1. public static void execute(String sourceFile, String sourceName){
  2. ServiceLoader<DisposeService> serviceLoader = ServiceLoader.load(DisposeService.class);
  3. Iterator<DisposeService> serviceIterator = serviceLoader.iterator();
  4. while (serviceIterator.hasNext()){
  5. DisposeService service = serviceIterator.next();
  6. if (service == null)return;
  7. new Thread(()->{
  8. service.export(sourceFile,sourceName);
  9. }).start();
  10. }
  11. }

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

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

image

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

  1. @echo off
  2. 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即可运行。

原文链接:https://www.cnblogs.com/zhujiqian/p/17224186.html

 友情链接:直通硅谷  点职佳  北美留学生论坛

本站QQ群:前端 618073944 | Java 606181507 | Python 626812652 | C/C++ 612253063 | 微信 634508462 | 苹果 692586424 | C#/.net 182808419 | PHP 305140648 | 运维 608723728

W3xue 的所有内容仅供测试,对任何法律问题及风险不承担任何责任。通过使用本站内容随之而来的风险与本站无关。
关于我们  |  意见建议  |  捐助我们  |  报错有奖  |  广告合作、友情链接(目前9元/月)请联系QQ:27243702 沸活量
皖ICP备17017327号-2 皖公网安备34020702000426号