掌握这些技巧，让Excel批量数据清洗变得简单高效！

数据清洗是指在数据处理过程中对原始数据进行筛选、转换和修正，以确保数据的准确性、一致性和完整性的过程。它是数据预处理的一部分，旨在处理和纠正可能存在的错误、缺失值、异常值和不一致性等数据质量问题。本文将介绍需要进行数据清洗的场景、具体需要执行的步骤，并介绍如何通过GcExcel实现数据清洗。

发布于 2023/09/03 12:39

GcExcel

前言

什么是数据清洗

数据清洗是指在数据处理过程中对原始数据进行筛选、转换和修正，以确保数据的准确性、一致性和完整性的过程。它是数据预处理的一部分，旨在处理和纠正可能存在的错误、缺失值、异常值和不一致性等数据质量问题。

为什么要数据清洗

Excel在数据采集场景中非常常用。作为一款电子表格软件，它提供了丰富的功能和易用的界面，使其成为大部分人首选的数据采集工具之一。

而在数据采集的过程中，因为采集渠道多样，数据格式也多种多样，从而会出现部分数据的丢失和不准确的情况，因此为了处理掉这些 “垃圾”数据，需要对数据进行清洗。

哪些数据需要进行清洗

  1. 通常在这几种情况下需要进行数据清洗。

  2. 缺失数据处理：数据在采集或迁移的过程中，出现数据的遗漏。

  3. 错误数据判断：数据在采集或迁移的过程中与原数据不一致。

  4. 重复数据处理：一条数据重复出现多次。

  5. 数据格式转换：数据在采集或迁移的过程中出现了乱码。

数据清洗都需要做些什么

下面让我们看一下数据清洗都会涉及的处理步骤：

  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的类型不太一致，需要写特定的方法来进行处理。

GcExcel | 下载试用

GrapeCity Documents for Excel （简称：GcExcel）是一款基于 Java 平台的服务端高性能表格组件，可与纯前端表格控件 SpreadJS 前后端兼容，无需依赖 Office、POI 或第三方应用软件，在前端展示电子表格数据，在服务端批量创建、加载、编辑、打印、导入/导出 Excel 文档，为您开发的应用程序提供在线文档的前后端数据同步、在线填报与服务端批量导出与打印，以及类 Excel 报表模板设计与服务端高性能处理等一整套类 Excel 全栈解决方案。

热门博客分类
ComponentOne Enterprise SpreadJS ActiveReports 活字格 Wyn 商业智能 WijmoJS GcExcel ActiveReportsJS
相关产品

GcExcel

高效创建、编辑、解析、导出多种流行文档

免费试用 产品详情
推荐相关案例
云南柠能 - SpreadJS与GcExcel协同，实现BI-N平台一站式解决方案

云南柠能企业管理咨询有限公司是一家致力于打造业财融合、综合绩效管理平台的数字化产品研发及服务提供商。其中BI-N是一款一站式PaaS平台，主要包含数据平台、数据应用、数据分析、数据云产品四大部分，使用SpreadJS表格控件+Hadoop生态构建，提供数据分析、绩效管理等功能。SpreadJS在平台中能够还原Excel功能，支持多维数据分析和大数据场景下的表格渲染处理，配合GcExcel实现高性能公式计算，提高平台的访问效率，同时丰富的API接口也能够帮助用户灵活扩展平台的业务功能。

苏交科 - SpreadJS解锁TIC行业报表开发难题

通过使用SpreadJS+GcExcel助力TIC系统开发，加快了报表模板开发速度，提升了报表的计算效率，提高了系统的稳定性和用户体验。

上海佳软 - 优化多人协作，SpreadJS+GcExcel构建无缝协同系统

上海佳软推出了雷鸟365在线表格文档系统，这是一款有着Excel强大功能、允许多人实时协作编辑的产品。雷鸟365的出现让Excel联网成为可能，只需要导入本地文件，即可实现多人共享编辑文档，还可以设置访问修改权限。

首厚智能 - GcExcel助力LIMS系统，共塑卓越实验室之路

首厚智能以“互联网+智慧实验室解决方案”为核心，业务范围覆盖实验室智能化、数字化发展等多态融合的全新实验室服务，为科研企业提供应用系统开发、定制化系统开发、异构系统集成、信息系统运行维护、系统培训、软件及计算机硬件产品销售等服务。

某基金公司 - GcExcel助力基金数据监控，提升操作效率

某大型基金公司为提升投资管理水平，引入SpreadJS实现在线Excel文件管理、监管报送、数据解析和数据订阅等功能，提升工作效率和协作效果。SpreadJS带来类Excel的交互体验、数据处理能力和数据共享协作，提供了高效的投资管理解决方案。

查看更多精彩案例
关注微信
葡萄城社区二维码

关注“葡萄城社区”

活字格低代码二维码

关注“活字格低代码”

想了解更多信息，请联系我们, 随时掌握技术资源和产品动态

葡萄城热门产品

活字格
打通现有软件，快速定制面向未来的个性化应用
免费试用 产品详情
SpreadJS
可嵌入您系统的在线Excel的纯前端表格控件
免费试用 产品详情
Wyn商业智能
可与企业自有系统深度集成，增强数据分析的 BI 工具
免费试用 产品详情