c# - 在 Excel 互操作对象的合并行中自动调整文本。C＃

Question

我有一种在 excel 中写入数据的方法，但是有些部分有很多文本，并且该方法将文本包装到列中，但我不能在行中执行，因为它们已合并。所以我得到这样的东西：

我需要像这样得到它：

有没有办法通过代码做到这一点？使用互操作对象。谢谢你的帮助。

标签： c#excelexcel-interop

Answer 1

我不知道这是否正确，但我想出了以下解决方案。思路如下：

1. 记住行数
2. 计算合并区域中所有行的总高度
3. 根据总高度计算每行的百分比
4. 取消合并单元格
5. 自动调整行
6. 记住第一行的高度（即数据行）——一个新的高度
7. main：将百分比（在第 3 阶段）应用到新高度
8. 合并单元格（借助第 1 阶段的行数）

如您所见，此方法是通用的——即它适用于合并区域中的任何行数，并且它将根据新高度尊重每行的先前比率。您可以下载带有代码的示例工作簿。

VBA

Sub Test()
    Call AutoFitMergedCells(Range("D11"))
End Sub

Sub AutoFitMergedCells(cell As Range)

    Dim dOldHeight#, dNewHeight#, dPercent#, arow, addr, rows_count
    Dim dicCells As New Dictionary, dicHeights As New Dictionary

    '// turn off flickering
    Application.ScreenUpdating = False

    With cell

        '// remember rows count for merging cells back
        rows_count = .MergeArea.Count

        '// every dictionary entry holds following info:
        '// 1) original height of all rows in merged cells
        '// 2) percentage of row's height to height of all rows in merged area
        For Each arow In .MergeArea.Rows
            With arow.Cells(1)
                Set dicHeights = New Dictionary
                dicHeights("height") = .RowHeight
                dicHeights("percent") = 0
                dicCells.Add Key:=.Address(0, 0), Item:=dicHeights
            End With
        Next

        '// total height of all rows
        For Each addr In dicCells.Keys()
            dOldHeight = dOldHeight + dicCells(addr)("height")
        Next

        '// update the percentage of every row
        For Each addr In dicCells.Keys()
            dicCells(addr)("percent") = dicCells(addr)("height") / dOldHeight
        Next

        .UnMerge
        .EntireRow.AutoFit
        '// remember new height
        dNewHeight = .RowHeight

        '// this applies percentage of previous row's height to new height
        For Each addr In dicCells.Keys()
            Range(addr).EntireRow.RowHeight = dicCells(addr)("percent") * dNewHeight
        Next

        '// merge back
        .Resize(rows_count).Merge

    End With

    Application.ScreenUpdating = True

End Sub

更新

C＃

using System.Diagnostics;
using Excel = Microsoft.Office.Interop.Excel;

private void ProcessMergedCells()
{

    var xlApp = new Excel.Application { Visible = false, ScreenUpdating = false };
    // get Excel process in order to kill it after the work is done
    var xlHandle = new IntPtr(xlApp.Hwnd);
    var xlProc = Process
                 .GetProcesses()
                 .First(p => p.MainWindowHandle == xlHandle);
    var book = xlApp.Workbooks.Open(@"C:\AutoFitMergedCells.xlsm");
    var sheet = book.Sheets[1] as Excel.Worksheet;

    // obtain merged cells any way you like
    // here I just populate array with arbitrary cells
    var merged_ranges = new Excel.Range[]
    {
        sheet.Range["D11"],
        sheet.Range["D13"]
    };

    // process merged cells
    foreach(var merged_range in merged_ranges)
    {
        AutoFitMergedCells(merged_range);
    }

    // quit with saving
    book.Close(SaveChanges: true);
    xlApp.Quit();

    // clean up
    GC.Collect();
    GC.WaitForFullGCComplete();

    // kill Excel for sure
    xlProc.Kill();

}

private void AutoFitMergedCells(Excel.Range merged_range)
{

    double dOldHeight = 0d, dNewHeight = 0d;
    var dicCells = new Dictionary<string, Dictionary<string, double>>();

    // remember rows count for merging cells back
    int rows_count = merged_range.MergeArea.Count;

    // every dictionary entry holds following info:
    // 1) original height of all rows in merged cells
    // 2) percentage of row's height to height of all rows in merged area
    foreach (Excel.Range the_row in merged_range.MergeArea.Rows)
    {
        // we need only top-left cell
        var first_cell = the_row.Cells[1];
        var dicHeights = new Dictionary<string, double>
        {
            ["height"] = first_cell.RowHeight,
            ["percent"] = 0d
        };
        dicCells[first_cell.Address[0, 0]] = dicHeights;
    }

    // total height of all rows
    foreach (string addr in dicCells.Keys)
        dOldHeight += dicCells[addr]["height"];

    // update the percentage of every row
    foreach (string addr in dicCells.Keys)
        dicCells[addr]["percent"] = dicCells[addr]["height"] / dOldHeight;

    // unmerge range and autofit
    merged_range.UnMerge();
    merged_range.EntireRow.AutoFit();

    // remember new height
    dNewHeight = merged_range.RowHeight;

    // this applies percentage of previous row's height to new height
    var sheet = merged_range.Parent;
    foreach (string addr in dicCells.Keys)
        sheet.Range[addr].EntireRow.RowHeight = dicCells[addr]["percent"] * dNewHeight;

    // merge back
    merged_range.Resize[rows_count].Merge();

}