如何自动调整已合并单元格的列？

问题描述：

在C＃中，对于Excel电子表格，我该如何自动调整已合并单元格的列？我试着用下面的代码来做这件事，但它不会自动填充列。如何自动调整已合并单元格的列？

这是到目前为止我的代码：

Worksheet xlSheet 

xlSheet.Cells[2, 1] = "Autobiographies and Titles, Autobiographies and Titles, Autobiographies 
and Titles, Autobiographies and Titles, Autobiographies and Titles, Autobiographies and Titles, 
Autobiographies and Titles, Autobiographies and Titles"; 

Range hRangeSubsystemName = xlSheet.get_Range("A2", "G2"); 
hRangeSubsystemName.MergeCells = true; 
hRangeSubsystemName.EntireColumn.AutoFit();

请避免在代码中包含您的问题标题。 – 2012-01-14 01:02:40

合并单元格后，Excel仍将它们视为单独的列吗？如果没有，那么你的代码没有意义。更合适的可能是'xlSheet.get_Range（“A2”）。AutoFit（）'或类似的。请注意，我并不是Excel interop的专家，但这只是基于我对你正在做什么的解释的猜测。 – 2012-01-14 03:11:50

答

您无法在Excel合并单元格列使用自动调整。

查看MS支持文章：

http://support.microsoft.com/kb/212010

我明白了。谢谢。 – 2012-01-14 12:47:59

答

也许你可以将此转换为C＃，但我发现一个VB宏here，将模拟的工作表上的任何单元格合并的自动调整。从MrExcel.com

Sub AutoFitMergedCellRowHeight() 
Dim CurrentRowHeight As Single, MergedCellRgWidth As Single 
Dim CurrCell As Range 
Dim ActiveCellWidth As Single, PossNewRowHeight As Single 
Dim StartCell As Range, c As Range, MergeRng As Range, Cell As Range 
Dim a() As String, isect As Range, i 


'Take a note of current active cell 
Set StartCell = ActiveCell 

'Create an array of merged cell addresses that have wrapped text 
For Each c In ActiveSheet.UsedRange 
If c.MergeCells Then 
    With c.MergeArea 
    If .Rows.Count = 1 And .WrapText = True Then 
     If MergeRng Is Nothing Then 
      Set MergeRng = c.MergeArea 
      ReDim a(0) 
      a(0) = c.MergeArea.Address 
     Else 
     Set isect = Intersect(c, MergeRng) 
      If isect Is Nothing Then 
       Set MergeRng = Union(MergeRng, c.MergeArea) 
       ReDim Preserve a(UBound(a) + 1) 
       a(UBound(a)) = c.MergeArea.Address 
      End If 
     End If 
    End If 
    End With 
End If 
Next c 


Application.ScreenUpdating = False 

'Loop thru merged cells 
For i = 0 To UBound(a) 
Range(a(i)).Select 
     With ActiveCell.MergeArea 
      If .Rows.Count = 1 And .WrapText = True Then 
       'Application.ScreenUpdating = False 
       CurrentRowHeight = .RowHeight 
       ActiveCellWidth = ActiveCell.ColumnWidth 
       For Each CurrCell In Selection 
        MergedCellRgWidth = CurrCell.ColumnWidth + MergedCellRgWidth 
       Next 
       .MergeCells = False 
       .Cells(1).ColumnWidth = MergedCellRgWidth 
       .EntireRow.AutoFit 
       PossNewRowHeight = .RowHeight 
       .Cells(1).ColumnWidth = ActiveCellWidth 
       .MergeCells = True 
       .RowHeight = IIf(CurrentRowHeight > PossNewRowHeight, _ 
        CurrentRowHeight, PossNewRowHeight) 
      End If 
     End With 
MergedCellRgWidth = 0 
Next i 

StartCell.Select 
Application.ScreenUpdating = True 

'Clean up 
Set CurrCell = Nothing 
Set StartCell = Nothing 
Set c = Nothing 
Set MergeRng = Nothing 
Set Cell = Nothing 

End Sub

答

来源学分招架我写了一个函数Gembox要做到这一点，你可能会发现有用

private int AutoSizeMergedCells(CellRange myMergedCells, string text) 
    { 
     var file = new ExcelFile(); 
     file.Worksheets.Add("AutoSize"); 
     var ws = file.Worksheets[0]; 

     ws.Cells[0, 0].Column.Width = myMergedCells.Sum(x => x.Column.Width); 
     ws.Cells[0, 0].Value = text; 
     ws.Cells[0, 0].Style.WrapText = true; 
     ws.Cells[0, 0].Row.AutoFit(); 
     var result = ws.Cells[0, 0].Row.Height; 
     file = null; 
     return result; 
    }

答

请尽量将

 private double AutoSizeMergedCells(string text)   
    { 
     Excel.Worksheet ws = xlWorkBook.Sheets[1]; 
     `enter code here`ws.Cells[14, 10].ColumnWidth = 9.29+7.43+10.71+11.29;(size width range) 
     ws.Cells[14, 10].Value = text; 
      ws.Cells[14, 10].Style.WrapText = true; 
        ws.Cells[14, 10].Rows.AutoFit(); 
        var result = ws.Cells[14, 10].RowHeight; 
      ws.Cells[14, 10].Value = ""; 
        return result; 
}

如何自动调整已合并单元格的列？

相关推荐