設計一款可延伸和基於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即可執行。