最新要闻

广告

手机

iphone11大小尺寸是多少？苹果iPhone11和iPhone13的区别是什么？

iphone11大小尺寸是多少？苹果iPhone11和iPhone13的区别是什么？

 警方通报辅警执法直播中被撞飞：犯罪嫌疑人已投案

警方通报辅警执法直播中被撞飞：犯罪嫌疑人已投案

家电

  1. 首页
  2. 移动通信
  3. 内容

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

来源：博客园

原创总结/朱季谦


(资料图片)

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

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

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

整理后的表格如下：

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

工具的开发环境如下：

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

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

    4.0.0    org.example    auto-put-file    1.0-SNAPSHOT            3.1.0        3.8.1        3.1.2        2.8.2        2.5.2        3.1.2                            org.projectlombok            lombok            1.18.2                            org.apache.commons            commons-lang3            3.2.1                            org.apache.poi            poi            4.1.2                            org.apache.poi            poi-ooxml-schemas            4.1.2                            org.apache.poi            poi-scratchpad            4.1.2                            org.apache.poi            poi-ooxml            4.1.2                            fr.opensagres.xdocreport            xdocreport            2.0.2                            org.apache.poi            ooxml-schemas            1.4                                                                maven-clean-plugin                    ${maven-clean-plugin.version}                                                    maven-deploy-plugin                    ${maven-deploy-plugin.version}                                                    maven-install-plugin                    ${maven-install-plugin.version}                                                    maven-jar-plugin                    ${maven-jar-plugin.version}                                                    org.apache.maven.plugins                    maven-compiler-plugin                    ${maven-compiler-plugin.version}                                            UTF-8                        1.8                        1.8                                                                        org.apache.maven.plugins                    maven-assembly-plugin                    3.1.1                                                                                                        com.put.AutoExcel                                                                                                        jar-with-dependencies                                                                                                                    make-assembly                            package                                                            single

注意下， 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.IndexOutOfBoundsExceptionat 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 serviceLoader = ServiceLoader.load(DisposeService.class);        Iterator 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 serviceLoader = ServiceLoader.load(DisposeService.class);    Iterator 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 offjava -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即可运行。

关键词：