Re: How to merge cells in Excel using C# - C# Discussion Boards

16-Aug-21 13:15

Hello.
I use C# in Visual Studio and need to work with Excels spreadsheets. I know how to create and save the spreadsheets, but I need to format the plan, like to define the cell color, a collumn width and so on. Can anyone tell me how to access the commands in Excel through C#?
Thanks.

Richard Deeming16-Aug-21 21:36

Richard Deeming

16-Aug-21 21:36

How to access Office interop objects - C# Programming Guide | Microsoft Docs[^]
How to automate Microsoft Excel from Microsoft Visual C#.NET - Office | Microsoft Docs[^]
Microsoft.Office.Interop.Excel Namespace | Microsoft Docs[^]

NB: You will need Office installed on the computer where your application runs, and you cannot use Office Interop from an unattended application like ASP.NET or a Windows service:

Considerations for server-side Automation of Office[^]
Microsoft does not currently recommend, and does not support, Automation of Microsoft Office applications from any unattended, non-interactive client application or component (including ASP, ASP.NET, DCOM, and NT Services), because Office may exhibit unstable behavior and/or deadlock when Office is run in this environment.

"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer

Ismael Oliveira 202124-Aug-21 12:46

24-Aug-21 12:46

Thanks Richard.

Richard MacCutchan16-Aug-21 21:42

16-Aug-21 21:42

You can use the Range[^] class to refer to rows, columns and cells. You can add formatting, formulas etc through these objects. I have a sample application that contains the following sequence to control a range of cells:

cellRange = worksheet.get_Range("D2", "D6"); // select some cells
cellRange.Formula = "=RAND()*100000";        // add a formula to each cell
cellRange.NumberFormat = "£#,##0.00";        // format the values as pounds sterling

There are a number of websites run by Excel experts that offer help which Google can find for you.

How to merge cells in Excel using C#

SpiveyC#7-Sep-21 1:44

SpiveyC#

7-Sep-21 1:44

I use the EPPlus lib. Gives me full control

Ismael Oliveira 202112-Aug-21 12:43

12-Aug-21 12:43

Hi.
I use C# in Visual Studio and use Forms. I need to generate an Excel file with merged columns. I tried the following code:

private void button1_Click(object sender, EventArgs e)
{
    string Arquivo = @"D:\Diversos\Antares\Relatório.xlsx";
    Excel.Application xlApp;
    Excel.Workbook xlWorkBook;
    Excel.Worksheet xlWorkSheet;
    object misValue = System.Reflection.Missing.Value;

    //Cria uma planilha temporária na memória do computador
    xlApp = new Excel.Application();
    xlWorkBook = xlApp.Workbooks.Add(misValue);
    xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
    xlWorkSheet.Range[ws.Cells[1, 1], ws.Cells[1, 4]].Merge();
    xlWorkSheet.Visible = XlSheetVisibility.xlSheetVisible;

    //incluindo dados
    xlWorkSheet.Cells[1, 1] = "Dados do cliente:";
    //            xlWorkSheet.Cells[1, 2] = textBox1.Text;
    //            xlWorkSheet.Cells[1, 3] = textBox2.Text;
    //            xlWorkSheet.Cells[1, 4] = textBox3.Text;

    //Salva o arquivo de acordo com a documentação do Excel.
    xlWorkBook.SaveAs(Arquivo, Excel.XlFileFormat.xlWorkbookDefault, misValue, misValue, misValue, misValue,
    Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
    xlWorkBook.Close(true, misValue, misValue);
    xlApp.Quit();

}

Of course, it's just a try and much code is missing, but the essencial is here. When I run this program I receive the message: "System.Runtime.InteropServices.COMException: 'Não há suporte para esta interface" The last piece of the message means "There's no support for this interface", at the line of the merge.
Can anyone help me solve this problem? If you have other suggestion on how to inteact with Excel, I'd appreciate it.
Thanks.

modified 12-Aug-21 18:54pm.

Richard MacCutchan12-Aug-21 21:35

12-Aug-21 21:35

I have just tried this and it works correctly. However I notice a possible error in the following line of code:

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

where is ws defined?

Ismael Oliveira 202113-Aug-21 13:45

13-Aug-21 13:45

Hi, Richard.
You are right. There was a problem in my definition. I correct it and the problem with merge disappeared. The spreadsheet is created correctly, but I could not make it appear. I changed the routine to the formload event. See the code:

private void Form1_Load(object sender, EventArgs e)
{
    string Arquivo = @"D:\Diversos\Antares\Relatório.xlsx";
    Excel.Application xlApp;
    Excel.Workbook xlWorkBook;
    Excel.Worksheet xlWorkSheet;
    object misValue = System.Reflection.Missing.Value;

    //Cria uma planilha temporária na memória do computador
    xlApp = new Excel.Application();
    xlWorkBook = xlApp.Workbooks.Add(misValue);
    xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
    xlWorkSheet.Range[xlWorkSheet.Cells[1, 1], xlWorkSheet.Cells[1, 4]].Merge();
    xlWorkSheet.Visible = XlSheetVisibility.xlSheetVisible;

    //incluindo dados
    xlWorkSheet.Cells[1, 1] = "Dados do cliente:";
}

The problem now is that I can't make the spreadsheet visible. The line:

xlWorkSheet.Visible = XlSheetVisibility.xlSheetVisible;

appears not to work. Do you know what I must do to correct it?
Thanks.

Richard MacCutchan13-Aug-21 21:44

13-Aug-21 21:44

I have just tried that and it works fine. But I think you may first need to make the application visible, so the sequence should be:

xlApp.Visible = true;  // make the application visible
xlWorkSheet.Visible = XlSheetVisibility.xlSheetVisible; // select the current worksheet

Ismael Oliveira 202114-Aug-21 12:46

14-Aug-21 12:46

Richard.
Great hint. It worked fine. One more thing: suppose I want to save this file in a button. When I click the button, C# must verifiy if it is running. How do I do this?

Smile | :)

Thanks a lot.

modified 14-Aug-21 20:02pm.

Richard MacCutchan14-Aug-21 21:09

Passing a variable from an string to a For loop

14-Aug-21 21:09

You are welcome. I also learned a few things myself.

Alex Dunlop11-Aug-21 23:55

Re: Passing a variable from an string to a For loop

11-Aug-21 23:55

I have a string variable as follows:

String str = "dt.Rows[i][3]";

I want to use that string variable in a For loop:

For (int i=0; dt.Rows.Count; i++)
{
    str;
}

How is it possible to pass i from the string to For loop?

modified 12-Aug-21 6:07am.

Pete O'Hanlon12-Aug-21 2:28

Pete O'Hanlon

12-Aug-21 2:28

I might be being really thick here but I can't see what you're wanting to do with that string in your loop. It must serve some purpose, so what is it that you are trying to do with it? BTW, I assume your for statement actually means to read:

for(int i = 0; i < dt.Rows.Count; i++)

Advanced TypeScript Programming Projects

Re: Passing a variable from an string to a For loop

harold aptroot12-Aug-21 2:42

harold aptroot

12-Aug-21 2:42

That's a really mysterious question, because that normally "Just Works(tm)". There isn't really any "passing", you just use the string in the for loop and it works. Unless there's something critical aspect of the code that prevents that which you haven't shown.

The string "dt.Rows[i][3]" looks suspiciously like it's supposed to have some deeper meaning, but it doesn't actually, it's just a string. You could store dt and 3 in some variables though.

modified 12-Aug-21 8:57am.

Re: Passing a variable from an string to a For loop

Richard Deeming12-Aug-21 2:48

Richard Deeming

12-Aug-21 2:48

Assuming you want to parse the string into executable code, you'll need to use something like DynamicLinq[^], Roslyn[^], or Mono.Cecil[^].

"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer

Re: Passing a variable from an string to a For loop

jsc4212-Aug-21 3:08

jsc42

12-Aug-21 3:08

Assuming the "dt.Rows[i][3]" is a sample of a pseudo-dynamic expression rather than something directly read from a database, you could create a Func<> or an Action<>. e.g.

Action<int> DoSomething = (i) => dt.Rows[i][3];   // Create an action with 'i' as an argument and dt as an enclosing scope variable
for (int i = 0; i < dt.Rows.Count; i++
   DoSomething(i);  // Call the action passing the current value of i as a parameter

This could be genericised so each activation of the for loop has a different method assigned to the action; but each expression would have to be hard coded.
However, as is, both the above and your original do nothing. They get a value and then forget it. The only possible visible change they make would be to throw an exception if the [3] is out of range.

Re: Passing a variable from an string to a For loop

Dave Kreskowiak12-Aug-21 3:32

Dave Kreskowiak

12-Aug-21 3:32

No, that's not how compiled code works. You cannot reference a variable just by creating a string of the variable name.

Well, that's not entirely true, but it is in the sense you're trying to do it. To do it your way, you have to jump through a few flaming hoops and use a bit of parsing and Reflection to get it done. That's WAY more work on your part and makes for some very slow code.

In a word: DON'T.

Asking questions is a skill
CodeProject Forum Guidelines
Google: C# How to debug code
Seriously, go read these articles.

Dave Kreskowiak

Re: Passing a variable from an string to a For loop

Gerry Schmitz12-Aug-21 4:13

Gerry Schmitz

12-Aug-21 4:13

Amazing how much invalid syntax one can cram into a small space.

It was only in wine that he laid down no limit for himself, but he did not allow himself to be confused by it.
― Confucian Analects: Rules of Confucius about his food

Re: Passing a variable from an string to a For loop

OriginalGriff12-Aug-21 4:20

OriginalGriff

12-Aug-21 4:20

You can't: a string does not allow you to access variables, it's just a sequence of characters and compilation is well and truly over by the time it gets a value at all!

It might be possible to access some class level fields by using reflection, but even then, you couldn't just pass Reflection a string containing two variables and two array indexes and expect it to give you the value.

I don't know why you think you need this, but I'd suggest that you have over thought this code and are going down entirely the wrong route ...

"I have no idea what I did, but I'm taking full credit for it." - ThisOldTony
"Common sense is so rare these days, it should be classified as a super power" - Random T-shirt
AntiTwitter: @DalekDave is now a follower!

Re: Passing a variable from an string to a For loop

Alex Dunlop12-Aug-21 6:16

Re: Passing a variable from an string to a For loop

12-Aug-21 6:16

I'm using DevExpress in my project. I have written a query builder class that creates SQLite queries dynamically based on 3 comboBoxEdit controls with multi-selection properties, and 6 TextBox controls.
I get my data from SQLite file based on those 9 controls which the user select and then the filtered data are placed into a DataTable for further calculation. I have 4 CheckBox controls. These CheckBox controls determine the final calculations. First CheckBox is related to column 5 of DataTable, the second CheckBox is related to column 7 of DataTable, the third CheckBox is related to column 10 of DataTable, and the fourth CheckBox is related to column 14 of DataTable. I want to make a string expression from these selected checkboxes (using Join()) and use it inside a For Loop. I have written a code that can add checked items to a list.
My code is complicated and I cannot bring all of them here.
My problem is that I cannot make a dynamic expression inside For Loop based on those 4 CheckBoxes.
I need expresions like

dt.Rows[i][7].ToString()

OriginalGriff12-Aug-21 6:36

OriginalGriff

12-Aug-21 6:36

Instead, why not make a List of CheckBoxes, and put the associated column number into the CheckBox.Tag property?
Inside the loop, you retrieve the checkbox check it's Checked state, and if set, cast the Tag to an integer to access the column? Or better, put the column name in the Tag as string and use that instead as it's more obvious and maintainable?

Passing crap around as strings and then "decomposing" them when you need to use them is generally a bad idea ...

Re: Passing a variable from an string to a For loop

Alex Dunlop12-Aug-21 8:55

Re: Passing a variable from an string to a For loop

12-Aug-21 8:55

Thanks. I used Tag property and also Switch statement to solve this issue. Smile | :)

OriginalGriff12-Aug-21 22:49

OriginalGriff

12-Aug-21 22:49

Why is the switch necessary?

Off topic: You may like to know that you are having questions asked about you ... or perhaps not ... strange "lack of pattern" by one poster in the C# language forum: abuse ? or ?[^] - I don't know where that is going to end up, but Bill is not wrong. These questions do reveal a fundamental lack of understanding of the basics.

Re: Passing a variable from an string to a For loop

Alex Dunlop13-Aug-21 7:26

Re: Passing a variable from an string to a For loop

13-Aug-21 7:26

It is necessary because I need to join some strings based on selected CheckBoxes and use it as a Dictionary<> key. I want to use those selected checkboxes to find the summing of Column11 (in DataTable) values.

modified 13-Aug-21 13:59pm.

Alex Dunlop13-Aug-21 7:31