Reputation:

Merging Cells in Excel using C#

I have a database which contains 5 tables. Each table contains 24 rows and each row contains 4 columns.

I want to display these records in Excel sheet. The heading of each table is the name of the table, but I am unable to merge the columns for heading.

Please help me.

Upvotes: 46

Answers (12)

Ivan Sambol

Reputation: 1

My simple solution would be with .Merge() method.

X - cell letter
Y - cell number

ws.Range["XY:XY"].Merge();

Upvotes: 0

Nguyen Van Thanh

Reputation: 825

using Excel = Microsoft.Office.Interop.Excel;
// Your code...
yourWorksheet.Range[yourWorksheet.Cells[rowBegin,colBegin], yourWorksheet.Cells[yourWorksheet.rowEnd, colEnd]].Merge();

Row and Col start at 1.

Upvotes: 0

Ly Thanh Ngo

Reputation: 466

Try it.

ws.Range("A1:F2").Merge();

Upvotes: 1

Subha8

Reputation: 9

Worksheet["YourRange"].Merge();

Upvotes: 0

Ana Carolina Manzan

Reputation: 192

You can use NPOI to do it.

Workbook wb = new HSSFWorkbook();
Sheet sheet = wb.createSheet("new sheet");

Row row = sheet.createRow((short) 1);
Cell cell = row.createCell((short) 1);
cell.setCellValue("This is a test of merging");

sheet.addMergedRegion(new CellRangeAddress(
        1, //first row (0-based)
        1, //last row  (0-based)
        1, //first column (0-based)
        2  //last column  (0-based)
));

// Write the output to a file
FileOutputStream fileOut = new FileOutputStream("workbook.xls");
wb.write(fileOut);
fileOut.close();

Upvotes: 2

Sobhan

Reputation: 816

You can use Microsoft.Office.Interop.Excel:

worksheet.Range[worksheet.Cells[rowNum, columnNum], worksheet.Cells[rowNum, columnNum]].Merge();

You can also use NPOI:

var cellsTomerge = new NPOI.SS.Util.CellRangeAddress(firstrow, lastrow, firstcol, lastcol);
_sheet.AddMergedRegion(cellsTomerge);

Upvotes: 3

Ahmad naser

Reputation: 129

Excel.Application xl = new Excel.ApplicationClass();

Excel.Workbook wb = xl.Workbooks.Add(Excel.XlWBATemplate.xlWBATWorkshe et);

Excel.Worksheet ws = (Excel.Worksheet)wb.ActiveSheet;

ws.Cells[1,1] = "Testing";

Excel.Range range = ws.get_Range(ws.Cells[1,1],ws.Cells[1,2]);

range.Merge(true);

range.Interior.ColorIndex =36;

xl.Visible =true;

Upvotes: 11

Shalin Jirawla

Reputation: 499

This solves the issue in the appropriate way

// Merge a row
            ws.Cell("B2").Value = "Merged Row(1) of Range (B2:D3)";
            ws.Range("B2:D3").Row(1).Merge();

Upvotes: 2

Vipswelt

Reputation: 345

take a list of string as like

List<string> colValListForValidation = new List<string>();

and match string before the task. it will help you bcz all merge cells will have same value

Upvotes: 0

Imamul Karim Tonmoy

Reputation: 584

oSheet.get_Range("A1", "AS1").Merge();

Upvotes: 12

C. Ross

Reputation: 31868

Using the Interop you get a range of cells and call the .Merge() method on that range.

eWSheet.Range[eWSheet.Cells[1, 1], eWSheet.Cells[4, 1]].Merge();

Upvotes: 80

Akshaya

Reputation:

Code Snippet

public partial class Form1 : Form
{
    public Form1()
    {
        InitializeComponent();
    }

    private Excel.Application excelApp = null;
    private void button1_Click(object sender, EventArgs e)
    {
        excelApp.get_Range("A1:A360,B1:E1", Type.Missing).Merge(Type.Missing);
    }

    private void Form1_Load(object sender, EventArgs e)
    {
        excelApp = Marshal.GetActiveObject("Excel.Application") as Excel.Application ;
    }
}

Thanks

Upvotes: 3

Merging Cells in Excel using C#

Answers (12)

Related Questions