Generate excel Merged cells without 3rd party component?

Question

As title. I am developing a web site with other programmers. I am responsible for a feature to generate an Excel file to show some reports with fixed layouts in the sheets in the template excel file. Could I make it using OLE DB or any other way without 3rd party components such as Spire.XLS? Not because I hate them, but I must fit the manager's rule for some reason.

Like the picture here, could I merge cells like this? I want to merge some cells and write text into it. But I searched using keywords "C# oledb merge cells xlsx", results I get are about how to get value or how to merge by the components....

OLE DB is a database-centric technology: likely it knows nothing about merged cells, which are a layout/formatting thing. — Tim Williams, Aug 05 '20 at 04:14
So it is almost impossible to make that by Ole Db, isn't it? I changed my question a little. — Gary Lu, Aug 05 '20 at 04:44
It would help to add a little context to your question around exactly what your use-case is. Desktop? Server? How complex is therequired output? — Tim Williams, Aug 05 '20 at 05:03
I am wondering is there a way to do without Microsoft.Office.Interop.Excel... My boss doesn't like to use it because of some problems about licensing. I am sorry about saying it too late. — Gary Lu, Aug 05 '20 at 07:09
Automating Excel on the server is something Microsoft recommends against, since Excel is not designed for that type of use (plus as you note there are licensing issues). You're really fighting against yourselves by ruling out third-party tools. You could look at outputting an XML Excel format but that's going to be more work. — Tim Williams, Aug 05 '20 at 15:33

score 0 · Answer 1 · answered Aug 05 '20 at 06:47

First, you can install nuget-package Microsoft.Office.Interop.Excel in visual studio.

Second, here is a code example you can refer to.

            using Excel = Microsoft.Office.Interop.Excel;
            Excel.Application app = new Excel.Application();
            app.DisplayAlerts = false;
            Excel.Workbook workbook = app.Workbooks.Open("D:\\2.XLSX");
            Excel.Worksheet worksheet = workbook.ActiveSheet;
            Excel.Range range = worksheet.Range["A1","A3"];
            range.HorizontalAlignment= Excel.XlHAlign.xlHAlignCenter;
            range.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
            range.Value2 = "Name/ID";
            range.Select();
            range.Merge();
            workbook.Save();

The initial excel:

Modified excel:

score 0 · Answer 2 · answered Aug 05 '20 at 06:51

Search for "C# Excel Object Model" (using the Primary Interop Assemblies) + "how to export", eg https://www.c-sharpcorner.com/UploadFile/deveshomar/exporting-datatable-to-excel-in-C-Sharp-using-interop/

Once you have followed those steps and have it working, make a Macro in Excel that records the action of turning 3 cells into 1 merged cell. Use that as the basis to write the same thing in your C# code, eg VBA:

Range("A1:A3").Select
Range("A3").Activate
With Selection
    .HorizontalAlignment = xlGeneral
    .VerticalAlignment = xlBottom
    .WrapText = False
    .Orientation = 0
    .AddIndent = False
    .IndentLevel = 0
    .ShrinkToFit = False
    .ReadingOrder = xlContext
    .MergeCells = True
End With

C#:

workSheet.Range[workSheet.Cells[1, 1], workSheet.Cells[3, 1]].Merge();

There's a few alternatives to the Excel Object Model (using the PIA), such as Open or Closed XML or even HTML. Depending on the size of the datasets and if they need formatting (ie not plain text) then you might decide to choose the latter.

Generate excel Merged cells without 3rd party component?

2 Answers2