|
|
|
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");
cellRange.Formula = "=RAND()*100000";
cellRange.NumberFormat = "£#,##0.00";
There are a number of websites run by Excel experts that offer help which Google can find for you.
|
|
|
|
|
I use the EPPlus lib. Gives me full control
|
|
|
|
|
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;
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;
xlWorkSheet.Cells[1, 1] = "Dados do cliente:";
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.
|
|
|
|
|
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?
|
|
|
|
|
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;
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;
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.
|
|
|
|
|
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;
xlWorkSheet.Visible = XlSheetVisibility.xlSheetVisible;
|
|
|
|
|
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?
Thanks a lot.
modified 14-Aug-21 20:02pm.
|
|
|
|
|
You are welcome. I also learned a few things myself.
|
|
|
|
|
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.
|
|
|
|
|
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++)
|
|
|
|
|
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.
|
|
|
|
|
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
|
|
|
|
|
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];
for (int i = 0; i < dt.Rows.Count; i++
DoSomething(i); 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.
|
|
|
|
|
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.
|
|
|
|
|
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
|
|
|
|
|
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!
|
|
|
|
|
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()
|
|
|
|
|
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 ...
"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!
|
|
|
|
|
Thanks. I used Tag property and also Switch statement to solve this issue.
|
|
|
|
|
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.
"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!
|
|
|
|
|
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.
|
|
|
|
|
@OriginalGriff if I'm bothering you and others in this forum, please tell me. I will go if you don't like me and my questions. But, I say again, I need your help.
modified 13-Aug-21 13:42pm.
|
|
|
|
|
Quote: I'm trying hard to learn and have had some successes yet. I'm working on a reporting project. Sometimes I need help to solve my problems during the developing process. I use YouTube, StackOverFlow, Codeproject, Google, etc. and finally find my answer and learn the points. Microsoft documentation is great for learning but there are moments you need to get help from others about how and with what tools you can solve your problem. And this website has helped me a lot. Now, At the same time, I'm learning ASP.NET using YouTube tutorials. I can achieve my goals. One day, I will be a good programmer. I promise.
I didn't reply immediately, because this is difficult - and probably going to be more so for you than I. So I'll apologise in advance if I cause any offence, and say that you should view this as constructive criticism rather than getting upset if you can.
You aren't learning.
That's blunt, but it's pretty much true. What you are learning is not the right stuff - you are making the same mistakes, ignoring the same things, following the wrong path; this shows in your questions both in QA and here. What you are do is picking up solutions to specific problems, most of which exist only because you have created them because you don't know any better.
And that's because you are trying to learn the wrong way.
Let's ignore computers and think about becoming a surgeon. Nobody starts off on day one being gowned up, masked, up, gloved up, given a scalpel and an unconscious patient then told "fix his hernia" with a mobile number to call if you get stuck while your teacher is off to the golf course.
They have to learn from books, be shown what goes on inside the body, practice on corpses, and probably a whole load of other gloopy things we'd rather not think about while eating.
That takes time: lots of it. And one of the first things they are allowed to do on an actual body - and have to practice a lot - is "opening and closing": making the initial incision, and sewing it back up again. In short, the basics. Getting a feel for what they are supposed to do (and weeding out the ones who will vomit at eteh first sign of blood)
And software is the same: you need to have the basics down pat or you don't really know what you are doing.
And you don't. Not even close. Your question on strings recently for example: How to split an string and remove extra spaces - C# Discussion Boards[^]
If you had a good grasp of the basics of strings you would never have asked that - you would know that Trim existed (even if you couldn't remember it's name) and looked for it instead of spending nearly an hour waiting for someone else to help you!
Your whole approach to learning is wrong.
Youtube - generally useless, because most of the tutorials you will find are produced by people who want "likes and subscribes" for the money they bring, while having as little idea how to do things as the people they are "teaching". There are good ones out there I'm sure - but they are well hidden in a huge mountain of dross!
SO, CP, Google - good resources for problem help, but not teaching basics!
MS documentation is OK, and comprehensive - but unless you know what you are looking for it's pure luck if you can find it!
SO, CP, MS are all very handy - but what you need to do is learn how to do it properly. And that means (in order of descending quality):
1) A course. Having a human tutor means he can rephrase and reexplain things if you don't understand. You also get homework which grades you progress and let's him and more importantly you know when you are going wrong. Local colleges may have classes.
2) A book. Addison Wesley, Wrox, Microsoft Press all do excellent ones, and provide tests for you to try and implement to both reinforce and check learning.
3) Guess and hope. Throw some code down, hope it works. When it doesn't try to fix it and then give up and ask someone else to fix it for you.
4) Youtube tutorials. Learn to make the same mistakes as other people on code samples that don't even compile half the time! Make money for idiots and spend a lot of time doing it!
At the moment, you are using a combination of 3 & 4 - and it shows in your questions and the "structure" of the code and design behind them.
Please, do yourself a favour, and think about this for a while before you explode and reply.
This is intended as constructive criticism, not to upset you - and I'm sorry in advance if it did.
"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!
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
