用Python向excel填补数据

需求说明： 根据excel中已有的数据，填充空白的单元格

问题举例： 在一个excel中，同一个人的编号只保存了一次，多条记录的情况下存在编号空缺情况

需求： 需要对同一个人的多条记录的空缺编号进行填补

目标：要达到的填补效果如下图（本需求为向上填充）：

1. 引入相关的库

import xlrd
from xlutils.copy import copy

2. 定义函数

def paddingData(excel_path, n):
    ''' 向excel中填充数据
        args:
            excel_path - excel的路径
            n - 第 n 次调用本函数的标记
        return:

    '''
    print("excel:", excel_path)
    print("正在打开excel文件...")
    excel_file = xlrd.open_workbook(excel_path)  # 打开Excel文件

    table = excel_file.sheets()[0]         # 通过索引打开第一个Sheet
    print('行数:%d' % table.nrows)  # 打印行数
    print('列数:%d' % table.ncols)  # 打印列数

    print("转换为可操作对象...")
    workbook = copy(excel_file)  # 将xlrd对象拷贝转化为xlwt可操作的对象

    print("获取已存在的Sheet...")
    worksheet = workbook.get_sheet(0)  # 获取一个已存在的sheet

    print("开始向数组中填补数据...")
    all_content = []
    for i in range(table.nrows - 1, 0, -1):
        row_content = []
        # 获取单元格内容
        if (table.cell(i, 0).value != ""):
            cell_value = table.cell(i, 0).value
        else:
            cell_value = table.cell(i + 1, 0).value

        row_content.append(cell_value)
        all_content.append(row_content)

    print("经过第",  n, "轮填补后的数组为：")
    print(type(all_content))
    print(all_content)

    print("开始将数组写入excel表中...")
    for x in range(table.nrows - 1, 0, -1):
        worksheet.write(table.nrows - x, 0, all_content[x - 1][0])
    print("写入excel已完成！")

    print("正在保存excel...")
    workbook.save(excel_path)
    print("保存成功！")

3. 调用函数，进行测试

# 调用函数，进行测试
excel_path = r"D:\MyFileFolder\Desktop\e.xls"    # Excel文件路径
paddingData(excel_path, 1)
paddingData(excel_path, 2)

4. 运行效果，最终效果

excel: D:\MyFileFolder\Desktop\e.xls
正在打开excel文件...
行数:8
列数:3
转换为可操作对象...
获取已存在的Sheet...
开始向数组中填补数据...
经过第 1 轮填补后的数组为：
<class 'list'>
[[332.0, '白居易', 'A33'], [331.0, '白居易', 'A33'], [223.0, '杜甫', 'A22'], [222.0, '杜甫', 'A22'], [221.0, '杜甫', ''], [112.0, '李白', 'A11'], [111.0, '李白', 'A11']]
开始将数组写入excel表中...
写入excel已完成！
正在保存excel...
保存成功！
excel: D:\MyFileFolder\Desktop\e.xls
正在打开excel文件...
行数:8
列数:3
转换为可操作对象...
获取已存在的Sheet...
开始向数组中填补数据...
经过第 2 轮填补后的数组为：
<class 'list'>
[[332.0, '白居易', 'A33'], [331.0, '白居易', 'A33'], [223.0, '杜甫', 'A22'], [222.0, '杜甫', 'A22'], [221.0, '杜甫', 'A22'], [112.0, '李白', 'A11'], [111.0, '李白', 'A11']]
开始将数组写入excel表中...
写入excel已完成！
正在保存excel...
保存成功！

Process finished with exit code 0

与需求效果完全符合！

POI进行Excel的合并单元格数据处理

近日接到一个要处理合并Excel单元格的上料表的需求，就到网上找了一些模板，发现有的技术大牛还是挺厉害的，对他们致以敬意。

合并单元格工具类

在这个类中将传入的文件转化成流对象，再对其进行具体的数据处理

public class ExcelUtilsMerge {


    public static Map<String, Object> readFileContentToEntity(InputStream inputStream) {
        Map<String, Object> excelMap = new HashMap<>(16);
        List<List<ExcelEntity>> result = new ArrayList<>();
        Workbook wb = null;
        try {
            wb = WorkbookFactory.create(inputStream);
        } catch (IOException e) {
        }
        if (wb == null) {
            return excelMap;
        }
        // 获取excel中存在的sheet个数
        int sheetsNum = wb.getNumberOfSheets();
        if (sheetsNum <= 0) {
            return excelMap;
        }
        for (int i = 0; i < sheetsNum; i++) {
            Sheet sheet = wb.getSheetAt(i);
            // 读取excel数据
            if (sheet.getPhysicalNumberOfRows() <= 0) {
                continue;
            }

            int coloumNum = sheet.getRow(0).getPhysicalNumberOfCells();
            result.addAll(dealWithExcelSheet(sheet));
            // 列总数
            excelMap.put("totalColumnNumber", coloumNum);
            // 数据集
            excelMap.put("data", result);
        }
        return excelMap;
    }

    /**
     * @Description: 读取sheet中的数据
     * @author: drj
     * @date: 2019/5/22 17:14
     */
    private static List<List<ExcelEntity>> dealWithExcelSheet(Sheet sheet) {
        List<List<ExcelEntity>> result = new ArrayList<>();
        // 遍历sheet行
        for (int i = 0; i < sheet.getPhysicalNumberOfRows(); i++) {
            List<ExcelEntity> record = new ArrayList<>();
            Row row = sheet.getRow(i);  //首先獲取Excel的行
            // 遍历行中的没有个单元格
            if (row == null || row.getPhysicalNumberOfCells() <= 0) {
                continue;
            }
            //循環列
            for (int j = 0; j < row.getPhysicalNumberOfCells(); j++) {
                Cell cell = row.getCell(j);  //獲取沒個單元的值
                if (cell == null) {
                    continue;
                }
                // 读取单元格数据格式（标记为字符串）
                cell.setCellType(CellType.STRING);
                String value = cell.getStringCellValue();
                // 处理有值的cell
                if (StringUtils.isEmpty(value)) {
                    continue;
                }
                try {
                    value = value.trim();
                    ExcelEntity entity = new ExcelEntity();  //初始化封裝好的返回Excel實體類
                    // 起始行数
                    entity.setStartRow(i);
                    // 结束行数
                    entity.setEndRow(getMergeRowNum(cell, sheet) + i - 1);  //加上当前行数，因为获取的当前行数加了1，所以要减1
                    // 起始列数
                    entity.setStartCol(j);
                    // 结束列数
                    entity.setEndCol(getMergeColumNum(cell, sheet) + j - 1); //加上当前列数，因为获取的当前列数加了1，所以要减1
                    // 单元格数据
                    entity.setValue(value); //将获取的值添加Value里
                    record.add(entity);
                } catch (NumberFormatException e) {
                }
            }
            result.add(record);
        }
        return result;
    }

    /**
     * @param cell  当前cell
     * @param sheet 当前sheet
     * @Description: 获取当前cell合并的行数
     * @author: drj
     * @date: 2019/5/22 18:00
     */
    public static int getMergeRowNum(Cell cell, Sheet sheet) {
        int mergeSize = 1;
        List<CellRangeAddress> mergedRegions = sheet.getMergedRegions();   //獲取的是合併的區域
        for (CellRangeAddress cellRangeAddress : mergedRegions) {   //對合併區域進行循環
            if (cellRangeAddress.isInRange(cell)) {   //判斷傳入單元格是否在合併區域中
                //获取合并的行数
                mergeSize = cellRangeAddress.getLastRow() - cellRangeAddress.getFirstRow() + 1;
                break;
            }
        }
        return mergeSize;
    }

    /**
     * @param cell  当前cell
     * @param sheet 当前sheet
     * @Description: 获取合并的列数
     * @author: drj
     * @date: 2019/5/22 17:59
     */
    public static int getMergeColumNum(Cell cell, Sheet sheet) {
        int mergeSize = 1;
        List<CellRangeAddress> mergedRegions = sheet.getMergedRegions();
        for (CellRangeAddress cellRangeAddress : mergedRegions) {
            if (cellRangeAddress.isInRange(cell)) {
                //获取合并的列数
                mergeSize = cellRangeAddress.getLastColumn() - cellRangeAddress.getFirstColumn() + 1;
                break;
            }
        }
        return mergeSize;
    }
}

返回的Excel对象实体类

这个类定义了要返回的结果集对象，封装了开始行结束行，开始列结束列，以及单元格的值.

public class ExcelEntity {

    /**
     * 开始行
     */
    private int startRow;
    /**
     * 结束行
     */
    private int endRow;
    /**
     * 开始列
     */
    private int startCol;
    /**
     * 结束列
     */
    private int endCol;
    /**
     * 单元格值
     */
    private String value;

    public int getStartRow() {
        return startRow;
    }

    public void setStartRow(int startRow) {
        this.startRow = startRow;
    }

    public int getEndRow() {
        return endRow;
    }

    public void setEndRow(int endRow) {
        this.endRow = endRow;
    }

    public int getStartCol() {
        return startCol;
    }

    public void setStartCol(int startCol) {
        this.startCol = startCol;
    }

    public int getEndCol() {
        return endCol;
    }

    public void setEndCol(int endCol) {
        this.endCol = endCol;
    }

    public String getValue() {
        return value;
    }

    public void setValue(String value) {
        this.value = value;
    }
}

测试类

返回的结果集的数据类型是List，然后每一行封装为一个类型为ExcelEntity的List.

        InputStream inputStream = file.getInputStream();
        Map<String, Object> result = ExcelUtilsMerge.readFileContentToEntity(inputStream);
        List<List<ExcelEntity>> data = (List<List<ExcelEntity>>) result.get("data");

网上的技术点琳琅满目，而在这样杂而乱的网络环境中，亦要吸取其精华，让它变为自己的东西，这才是真正的学习~

从上一篇文章我们已经可以获取某单元格的数据，现在我们只要执行：MsgBox VarType(curCell)就可以获取到类型，完整代码如下：

Sub Macro1()
'
' Macro1 Macro
' 宏由 cq 录制，时间: 2016/08/02
x = Sheets("Sheet1").[a5]
MsgBox VarType(curCell)
End Sub

我们会在弹窗看到一个数字，这个就是我们数据类型，下面这张表就是数据类型对应的值：（我们可以从excel帮助获得）