1.11M Members

merge cells with same values in microsoft office interop excel

 
0
 

i created a report which exports to excel. it exports just fine. what i want to do now is merge the consecutive cells in a column which have the same values. how do i do this? please help me.

this is code that generates the excel body:

Protected Sub generateExcelBody(ByVal xcelworksheet As Microsoft.Office.Interop.Excel.Worksheet, ByVal recarray As Array, ByVal numofrecords As Integer)
    Dim chartrange As Microsoft.Office.Interop.Excel.Range
    chartrange = Nothing
    chartrange = xcelworksheet.Range("B5", "F5")
    chartrange.MergeCells = True
    chartrange.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft
    chartrange.VerticalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter

    chartrange = Nothing
    chartrange = xcelworksheet.Range("A8", System.Reflection.Missing.Value)
    chartrange.FormulaR1C1 = "Record Series : " & hiddenrs.Value
    chartrange = Nothing
    chartrange = xcelworksheet.Range("A9", System.Reflection.Missing.Value)
    chartrange.FormulaR1C1 = "Department : " & hiddendept.Value
    chartrange = Nothing
    chartrange = xcelworksheet.Range("A10", System.Reflection.Missing.Value)
    chartrange.FormulaR1C1 = "Number of Records : " & numofrecords
    chartrange = Nothing
    chartrange = xcelworksheet.Range("A14", "F14")
    chartrange.Resize(numofrecords, 6).Value2 = recarray
    chartrange.Resize(numofrecords, 6).Borders(Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeLeft).Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlMedium
    chartrange.Resize(numofrecords, 6).Borders(Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeRight).Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlMedium
    chartrange.Resize(numofrecords, 6).Borders(Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideHorizontal).Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin
    chartrange.Resize(numofrecords, 6).Borders(Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideVertical).Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin
    chartrange.Resize(numofrecords, 6).Borders(Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeBottom).Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlMedium
    chartrange.Resize(numofrecords, 6).WrapText = True
    chartrange.Resize(numofrecords, 6).EntireRow.AutoFit()
    chartrange.Resize(numofrecords, 6).Font.Size = 10
    chartrange.Resize(numofrecords, 6).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter
    chartrange.Resize(numofrecords, 6).VerticalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter
End Sub
You
Post:
Start New Discussion
View similar articles that have also been tagged: