掌握這些技巧,讓Excel批次資料清洗變得簡單高效!

葡萄城技術團隊發表於2023-10-02

什麼是資料清洗

資料清洗是指在資料處理過程中對原始資料進行篩選、轉換和修正,以確保資料的準確性、一致性和完整性的過程。它是資料預處理的一部分,旨在處理和糾正可能存在的錯誤、缺失值、異常值和不一致性等資料質量問題。

為什麼要資料清洗

Excel在資料採集場景中非常常用。作為一款電子表格軟體,它提供了豐富的功能和易用的介面,使其成為大部分人首選的資料採集工具之一。

而在資料採集的過程中,因為採集渠道多樣,資料格式也多種多樣,從而會出現部分資料的丟失和不準確的情況,因此為了處理掉這些 “垃圾”資料,需要對資料進行清洗。

哪些資料需要進行清洗

通常在這幾種情況下需要進行資料清洗。

1.缺失資料處理:資料在採集或遷移的過程中,出現資料的遺漏。

2.錯誤資料判斷:資料在採集或遷移的過程中與原資料不一致。

3.重複資料處理:一條資料重複出現多次。

4.資料格式轉換:資料在採集或遷移的過程中出現了亂碼。

資料清洗都需要做些什麼

下面讓我們看一下資料清洗都會涉及的處理步驟:

  1. 分析需求:透過對資料原本的格式,特徵進行分析,規劃資料清洗的業務規則及需求。
  2. 開啟檔案:把Excel檔案開啟,通常這一步需要依賴Excel元件庫,比如使用POI,GcExcel,EasyExcel等。
  3. 讀取資料:透過Excel庫中的API,讀取需要操作的資料,這裡比較一下三個產品的特點:

GcExcel提供了IRange(區域)的概念,可以透過API快速的讀取有資料的區域。POI和EasyExcel(POJO註解)則需要遍歷每一個單元格。

根據業務需求,可以選擇使用API,也可以選擇遍歷所有單元格。

  1. 資料清洗:根據需求,結合Excel庫的API,進行資料清洗。如:用預設值填寫缺失資料的單元格,刪除整個空行,刪除重複資料,把不符合範圍的資料刪除掉,或者把日期數字的格式統一起來,等等。
  2. 資料持續化:把處理好的資料回存至Excel檔案,或者儲存在資料庫中或者CSV檔案中。

如何使用GcExcel實現資料清洗

GcExcel有IRange的API,可以讓資料清洗時程式碼寫的更簡單,因此下面我們選擇用GcExcel的程式碼為例解決上面提到的幾個場景。

基於IRange,GcExcel提供一些快速查詢的API,如下(在檔案中查詢特殊單元格):

Workbook workbook = new Workbook();
 workbook.open("data.xlsx");
 IWorksheet sheet = workbook.getActiveSheet();

 //尋找sheet中,使用到的所有單元格
 IRange usedRange = sheet.getUsedRange();

 //尋找所有的公式單元格
 IRange allFormulas = sheet.getCells().specialCells(SpecialCellType.Formulas);
 //尋找所有的常量單元格
 IRange allConstants = sheet.getCells().specialCells(SpecialCellType.Constants);

雖然GcExcel提供了API,但資料清洗時,也可能有需求需要遍歷,下面是GcExcel遍歷單元格的程式碼,後面我們就有可能會用到。

public void FetchCellBasedOnRange(IRange area) {
   for (int column = 0; column < area.getColumns().getCount(); column++) {
     for (int row = 0; row < area.getRows().getCount(); row++) {
       IRange cell = area.get(row, column);
       //獲取單元格的值
       Object val = cell.getValue();
     }
   }
 }

場景一:缺失資料處理

假如有一個Excel的資料,現在藍色的格子是空的,我們需要對不同列下的藍色格子做不同的處理,例如姓名的空格子替換為匿名,年齡替換成-1,身份證號填寫N/A,住址填寫為未知。

程式碼如下:

public void replaceBlankCell() {
   Workbook workbook = new Workbook();
   workbook.open("resources/BlankCells.xlsx");
   IWorksheet sheet = workbook.getActiveSheet();
   IRange blankRanges = sheet.getCells().specialCells(SpecialCellType.Blanks);
   for (IRange area : blankRanges.getAreas()) {
     for (int column = 0; column < area.getColumns().getCount(); column++) {
       for (int row = 0; row < area.getRows().getCount(); row++) {
         IRange cell = area.get(row, column);
         Object defaultVal = getDefaultVal(cell.getColumn());
         cell.setValue(defaultVal);
       }
     }
   }

   workbook.save("Result.xlsx");
 }

 private Object getDefaultVal(int column) {
   switch (column) {
     case 1:
       return "匿名";
     case 2:
       return -1;
     case 3:
       return "N/A";
     case 4:
       return "未知";
   }
   return null;
 }

要注意的是,sheet.getCells().specialCells(SpecialCellType.Blanks);返回的區域是多個,因此我們需要遍歷透過遍歷areas來對每一個區域進行遍歷。

cell.getColumn()可以獲取到當前格子對應到sheet上的第幾列,因此獲取預設值時使用該方法。

場景二:錯誤資料判斷

錯誤資料的判斷,與缺失資料處理相似,透過制定一些規則找出錯誤的值,對於錯誤值可以透過修改背景顏色進行高亮處理,用來提示,進行人工修改。

通常規則可以有兩種選擇:

  1. 使用Java直接編寫判斷邏輯。
  2. 使用資料校驗(Datavalidation)功能,或者條件格式(ConditionFormat)來進行處理。

假如我們有下面一份資料,其中聯絡電話中有兩條是錯誤的,位數不夠,貨物ID有兩條是錯誤的,貨物ID不能小於0,我們需要把他們找出來。

public void MarkErrorData(){
   Workbook workbook = new Workbook();
   workbook.open("resources/ErrorData.xlsx");
   IWorksheet sheet = workbook.getActiveSheet();

   IRange telRange = sheet.getRange("C2:D5");
   for (int r=0; r<telRange.getRows().getCount();r++){
     IRange cell = telRange.get(r,0);
     if(cell.getValue().toString().length() != 11){
       cell.getInterior().setColor(Color.GetOrangeRed());
     }
   }

   IFormatCondition condition =
       (IFormatCondition) sheet.getRange("D2:D5").getFormatConditions().
           add(FormatConditionType.CellValue, FormatConditionOperator.Less, 1, null);
   condition.getInterior().setColor(Color.GetOrangeRed());

   workbook.save("Result.xlsx");
 }


在程式碼中,我們對C2:C5進行遍歷,判斷字串長度,然後對長度不合法的資料進行顏色標記。

而對於貨物,設定了條件格式,可以讓Excel在開啟時,自行標記錯誤的資料。

場景三:重複資料處理

假如我們有一份資料,其中有一些行資料是完全重複的,我們需要刪除這些行,如圖所示。

public void RemoveDuplicateData() {
   Workbook workbook = new Workbook();
   workbook.open("resources/DuplicateRows.xlsx");
   IWorksheet sheet = workbook.getActiveSheet();
   IRange usedRange = sheet.getUsedRange();
   HashSet<String> set = new HashSet<>();
   Stack<IRange> deleteRows = new Stack<>();
   for (int r = 1; r < usedRange.getRows().getCount(); r++) {
     IRange row = usedRange.getRows().get(r);
     StringBuilder rowKey = new StringBuilder();
     for (int c = 0; c < row.getColumns().getCount(); c++) {
       rowKey.append(usedRange.get(r, c).getValue().toString());
     }
     if (set.contains(rowKey.toString())) {
       deleteRows.push(row);
     } else {
       set.add(rowKey.toString());
     }
   }

   while (!deleteRows.isEmpty()) {
     deleteRows.pop().delete();
   }

   workbook.save("Result.xlsx");
 }


可以看到,重複的行被移除掉了。程式碼中用到了雜湊set和棧,其中我們用雜湊set來查詢重複的行。

另外使用棧來記錄需要被刪除的行,這裡特地用了棧,而沒有使用佇列,陣列或者ArraryList的原因是,GcExcel在刪除一行時,會讓這行下面的資料上移,這樣我們之前記錄的行就會便宜,導致刪除錯誤的行。

簡而言之,我們需要從下向上刪除,來避免行位移導致刪錯的問題。

場景四:資料格式轉換

例如我們有一些日期資料,或者貨幣資料,在資料採集時資料格式不同,我們需要分別統一訂單日期,金額的格式。

程式碼如下:

public void unifyFormat() {
   Workbook workbook = new Workbook();
   workbook.open("resources/DifferentFormat.xlsx");
   IWorksheet sheet = workbook.getActiveSheet();
   IRange usedRange = sheet.getUsedRange();
   for (int row = 1; row < usedRange.getRows().getCount(); row++) {
     IRange dateCell = usedRange.get(row, 1);
     IRange priceCell = usedRange.get(row, 2);
     dateCell.setValue(parseDate(dateCell.getValue()));
     dateCell.setNumberFormat("yyyy年MM月dd日");
     priceCell.setValue(parsePrice(priceCell.getValue()));
     priceCell.setNumberFormat("¥0.00");
   }
   sheet.getRange("B1").setNumberFormat("");

   workbook.save("Result.xlsx");
 }

 private Double parsePrice(Object value) {
   if (value == null)
     return null;
   String val = value.toString();
   if (val.startsWith("$") || val.startsWith("¥")) {
     val = val.substring(1);
   }
   return Double.parseDouble(val);
 }

 private LocalDateTime parseDate(Object value) {
   if (value == null)
     return null;
   if (value instanceof LocalDateTime) {
     return (LocalDateTime) value;
   }
   DateTimeFormatter[] formatters = {
       DateTimeFormatter.ofPattern("yyyy/MM/dd"),
       DateTimeFormatter.ofPattern("MM-dd-yyyy"),
       DateTimeFormatter.ofPattern("yyyy年MM月dd日"),
       DateTimeFormatter.ofPattern("yyyy.MM.dd")
   };
   LocalDate datetime = null;

   for (DateTimeFormatter formatter : formatters) {
     try {
       datetime = LocalDate.parse(value.toString(), formatter);
       break;
     } catch (DateTimeParseException e) {
       e.printStackTrace();
     }
   }

   assert datetime != null;
   return datetime.atStartOfDay();
 }

需要注意的是在處理日期和金額時,由於value的型別不太一致,需要寫特定的方法來進行處理。


擴充套件連結:

Spring Boot框架下實現Excel服務端匯入匯出

專案實戰:線上報價採購系統(React +SpreadJS+Echarts)

React + Springboot + Quartz,從0實現Excel報表自動化

相關文章