excel读取合并单元格的值，c#的NPOI，python3的xlrd

python3的xlrd

-- xls需要写上formatting_info=True, xlsx不用

file_name = "./a1.xls"
workbook = xlrd.open_workbook(file_name, formatting_info=True)
sheet = workbook.sheets()[0]
row = get合并单元格_整行(sheet, 行下标 = 3)

import xlrd


def get合并单元格(sheet, 行下标, 列下标):
    单元格值 = sheet.cell_value(行下标, 列下标)
    merged = sheet.merged_cells
    for (row_index_min, row_index_max, col_index_min, col_index_max) in merged:
        if row_index_min <= 行下标  and 行下标 < row_index_max:
            if col_index_min <= 列下标 and 列下标 < col_index_max:
                单元格值 = sheet.cell_value(row_index_min, col_index_min)
                break
    return 单元格值

def get合并单元格_整行(sheet, 行下标, 不强制判断合并单元格=True):
    强制判断合并单元格 = not 不强制判断合并单元格
    row = sheet.row_values(行下标)
    for 列下标, value in enumerate(row):
        if not value or 强制判断合并单元格:
            row[列下标] = get合并单元格(sheet, 行下标, 列下标)
    return row


def is合并单元格(sheet, 行下标, 列下标):
    merged = sheet.merged_cells
    for (row_index_min, row_index_max, col_index_min, col_index_max) in merged:
        if row_index_min <= 行下标 and 行下标 < row_index_max:
            if col_index_min <= 列下标 and 列下标 < col_index_max:
                return True
    return False

 

 

C#的NPOI

using NPOI.SS.UserModel;
using System.Drawing;

#region 读取合并单元格
/// <summary>
/// 获取合并单元格的值
/// </summary>
public static object getMergedCellValue(ISheet sheet, int rowIndex, int colIndex)
{
    //初始化坐标
    Point start = new Point(0, 0);
    Point end = new Point(0, 0);
    if (isMergeCell(sheet, rowIndex, colIndex, out start, out end))
    {
        rowIndex = start.X;
        colIndex = start.Y;
    }

    return getCellValue(sheet, rowIndex, colIndex);
}

/// <summary>
/// 判断是否是合并单元格
/// </summary>
/// <param name="sheet"></param>
/// <param name="rowIndex"></param>
/// <param name="colIndex"></param>
/// <param name="start">左上角的行列坐标</param>
/// <param name="end">右下角的行列坐标</param>
/// <returns></returns>
private static bool isMergeCell(ISheet sheet, int rowIndex, int colIndex, out Point start, out Point end)
{
    //定义返回结果
    bool result = false;
    start = new Point(0, 0);
    end = new Point(0, 0);

    //获取合并单元格数
    int regionsCount = sheet.NumMergedRegions;
    for (int i = 0; i < regionsCount; i++)
    {
        CellRangeAddress range = sheet.GetMergedRegion(i);
        if (rowIndex >= range.FirstRow && rowIndex <= range.LastRow && colIndex >= range.FirstColumn && colIndex <= range.LastColumn)
        {
            start = new Point(range.FirstRow, range.FirstColumn);
            end = new Point(range.LastRow, range.LastColumn);
            result = true;
            break;
        }
    }
    return result;
}

/// <summary>
/// 获取单元格的值
/// </summary>
/// <param name="sheet">sheet</param>
/// <param name="rowIndex">行下标</param>
/// <param name="colIndex">列下标</param>
/// <returns></returns>
public static string getCellValue(ISheet sheet, int rowIndex, int colIndex)
{
    string rst;

    ICell cell = sheet.GetRow(rowIndex).GetCell(colIndex);
    if (cell != null)
    {
        //Cell为非NUMERIC时，调用IsCellDateFormatted方法会报错，所以先要进行类型判断
        if (cell.CellType == CellType.Numeric && DateUtil.IsCellDateFormatted(cell))
            rst = cell.DateCellValue.ToString("yyyy-MM-dd hh:mm:ss");
        else
        {
            rst = cell.ToString();
        }
    }
    else
    {
        rst = "";
    }


    return rst;
}
#endregion