|
Richard Deeming ,
You sir are my hero!!! Thank you so much
|
|
|
|
|
Never use "SelectedIndex" unless you know its value first.
The default is "-1"; which means "not selected"; and always results in an invalid index.
(And SelectedIndex will "bounce around" prior to a form / window being fully loaded).
Just because a "list control" has items, does not mean that SelectedIndex is "in range".
That also goes for "SelectedItem", "CurrentItem", "Position", etc. when dealing with "lists" and navigation.
"(I) am amazed to see myself here rather than there ... now rather than then".
― Blaise Pascal
|
|
|
|
|
Hello,
I am new to C# and has been assigned a task to modify a code.
One of our Reporting Tool is fetching the data from our Historian server and generating the report in excel format. But time taken for the generation of each report is around 15-20 mins depending upon the size of data. But this time should be less.
So, the developer of the Tool told us to try by our own exporting the data to .csv format. I tried it and changed the format to .csv but the time did not reduced.
I am attaching the part of our code:
private void export_to_excel(string template)
{
try
{
Microsoft.Office.Interop.Excel.Application excelApp = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbook excelWorkBook = excelApp.Workbooks.Open(path + "Report.xltx");
Microsoft.Office.Interop.Excel.Worksheet excelWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)excelWorkBook.Sheets["Report"];
string excelfile = IniReadValue("TemplateDetails", "ExpPath", SlTemplate.Text) + template +"_" + Fdt.Text.Replace('/', '_').Replace('-', '_').Replace(' ', '_').Replace(':', '_') + ".xlsx";
object misValue = System.Reflection.Missing.Value;
Microsoft.Office.Interop.Excel.Range range;
int j = 0, k = 0, i = 0, l = 0,m=0;
for (k = 0; k < dt.Rows.Count; k++)
{
i = 0;
for (j = 0; j < dt.Columns.Count; j++)
{
if (stat == false)
{
excelWorkSheet.Cells[9 + k, j + 2] = dt.Rows[k].ItemArray[j].ToString();
}
else
{
if(j==0)
{
excelWorkSheet.Cells[9 + k, i + 2] = dt.Rows[k].ItemArray[j].ToString();
}
else if(dt.Columns[j].ColumnName.Contains("C"))
{
excelWorkSheet.Cells[9 + k, i + 3] = dt.Rows[k].ItemArray[j].ToString();
if (dt.Columns.Contains("C" + i.ToString()))
{
if (dt.Rows[k]["S" + i.ToString()].ToString().ToUpper() != "GOOD" & (dt.Rows[k]["S" + i.ToString()].ToString() != ""))
{
range = excelWorkSheet.get_Range(Number2String(9 + k) + (k + 9).ToString(), Number2String(i + 3) + (k + 9).ToString());
range.Cells.Font.Color = Microsoft.Office.Interop.Excel.XlRgbColor.rgbRed;
}
}
i++;
}
}
}
}
excelApp.DisplayAlerts = false;
for (m = 4; m < ((j + 3)/2); m++)
{
if (m < j + 1)
{
if (excelWorkSheet.Cells[9, m].Value.ToString() == excelWorkSheet.Cells[9, m + 1].Value.ToString())
{
l++;
}
else
{
if (l > 0)
{
range = excelWorkSheet.get_Range(Number2String(m - l) + "9", Number2String(m) + "9");
range.Merge();
range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
}
l = 0;
}
}
else
{
if (l > 0)
{
range = excelWorkSheet.get_Range(Number2String(m - l) + "9", Number2String(m) + "9");
range.Merge();
}
l = 0;
}
}
excelWorkSheet.Cells[5, 5] = IniReadValue("TemplateDetails", "Substation", SlTemplate.Text);
excelWorkSheet.Cells[6, 5] = IniReadValue("TemplateDetails", "Region", SlTemplate.Text);
excelWorkSheet.Cells[7, 5] = Fdt.Text;
string alph = "A";
if(stat==false)
{
alph = Number2String(j + 1);
}
else
{
alph = Number2String(Convert.ToInt16(Math.Abs(Convert.ToDouble((j + 4) / 2))));
}
range=excelWorkSheet.get_Range("B9", alph + (dt.Rows.Count + 8).ToString());
range.Borders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
range = excelWorkSheet.get_Range("C12", alph + (dt.Rows.Count + 3).ToString());
range.NumberFormat = "General";
excelWorkSheet.Cells[12 + k, 1] = "Remarks:";
range = excelWorkSheet.get_Range("A"+(12 + k).ToString() ,"E"+ (14 + k).ToString());
range.Merge();
for (j = 0; j < pntid.Count; j++)
{
excelWorkSheet.Cells[49+j, 2] = DGSet[j];
excelWorkSheet.Cells[49+j, 3] = dgval[j];
excelWorkSheet.Cells[49+j, 2].Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Yellow);
excelWorkSheet.Cells[49+j, 3].Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Yellow);
}
range = excelWorkSheet.get_Range("B49", "C"+ (49+j-1).ToString());
range.Borders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
range.VerticalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
range.Cells.WrapText = true;
range = excelWorkSheet.get_Range("B49");
range.EntireColumn.AutoFit();
excelWorkSheet.PageSetup.Orientation = Microsoft.Office.Interop.Excel.XlPageOrientation.xlPortrait;
excelWorkSheet.PageSetup.PaperSize = Microsoft.Office.Interop.Excel.XlPaperSize.xlPaperA3;
Microsoft.Office.Interop.Excel.Worksheet excelWorkSheet1 = (Microsoft.Office.Interop.Excel.Worksheet)excelWorkBook.Sheets["Chart"];
if (graph.Checked == true)
{
Microsoft.Office.Interop.Excel.ChartObject chartObject11 = (Microsoft.Office.Interop.Excel.ChartObject)excelWorkSheet1.ChartObjects(1);
chartObject11.Activate();
Microsoft.Office.Interop.Excel.Chart chartPage = chartObject11.Chart;
range = excelWorkSheet.get_Range("B9", alph + (dt.Rows.Count + 3).ToString());
excelApp.ActiveChart.SetSourceData(range, Microsoft.Office.Interop.Excel.XlRowCol.xlColumns);
excelWorkSheet1.PageSetup.Orientation = Microsoft.Office.Interop.Excel.XlPageOrientation.xlPortrait;
excelWorkSheet1.PageSetup.PaperSize = Microsoft.Office.Interop.Excel.XlPaperSize.xlPaperA3;
}
else
{
excelWorkSheet1.Visible = Microsoft.Office.Interop.Excel.XlSheetVisibility.xlSheetHidden;
}
excelWorkBook.SaveAs(excelfile, Microsoft.Office.Interop.Excel.XlFileFormat.xlOpenXMLWorkbook, misValue, misValue, misValue, misValue, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
excelWorkBook.Close();
excelApp.Quit();
releaseObject(excelWorkSheet);
releaseObject(excelWorkBook);
releaseObject(excelApp);
foreach (Process clsProcess in Process.GetProcesses())
{
if (clsProcess.ProcessName.Equals("EXCEL"))
clsProcess.Kill();
}
}
catch (Exception err)
{
fail = fail+1;
ErrorLog(DateTime.Now.ToString() + "----- Export to excel ---" + err.ToString()); ;
}
}
Can anyone please help me getting out of the situation with or without using interop.excel.
Thanks in Advance,
Rohit
|
|
|
|
|
If you do it in the way as showed inside your code you could save it as XLS-File.
Your time comes from using Excel per "remote"-access.
When you want to export your data to a CSV-File you use the Standard-Features from .Net to create a Text-File with the Extension 'CSV'. Different Cells/Entries are seperated with a Comma or Semicolon. This kind of writing data will be very much faster ...
|
|
|
|
|
Thanks Ralf for the reply,
Can you please add the codes (of course if possible) or give some hint to dump to .txt.
Or you can suggest some similar threat.
Thanks,
Rohit
|
|
|
|
|
I suggest you ask Google for this - 'C# write CSV' for example.
Now you will get a lot of Results - you should look by yourself which matches best to your requirement (because I think you must also learn how to use the commands) and how to create the Method you really need ...
|
|
|
|
|
Ok Thanks,
I will try searching for the similar threads.
|
|
|
|
|
Trying to export this as CSV is unlikely to work, since you create so much information that needs to be in specific Excel format, which you cannot express in .csv. You need to look at the actual code that is extracting and converting individual items from the input data. It may be that you could get that in a simpler format, as some of your expressions seem over complex.
|
|
|
|
|
A lot of that is going to be his client's expectations. It may be that it is a data load and .csv would work better anyway. I definitely agree he will lose all formatting placing in a .csv.
modified 27-Oct-17 11:08am.
|
|
|
|
|
Looking at his code I do not see how he could even begin to think about creating .csv file(s). The whole thing is so tightly connected to Excel it would need a complete redesign. And that would still not produce the results they want.
|
|
|
|
|
Excel interop is notoriously slow. Try using a library which generates the file directly instead - eg:
If you still want to try CSV, and abandon the features which aren't supported in that format, there are various libraries available to help - for example, CsvHelper[^].
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
I just want to second Richard. I've used OpenXML a few times and have always been happy with it.
There's enough shake-and-bake examples on the net to make getting started with it fairly easy, as well.
"There are three kinds of lies: lies, damned lies and statistics."
- Benjamin Disraeli
|
|
|
|
|
The only problem with OpenXML is a somewhat steep learning curve for someone admittedly new to C#. I agree it is a great solution to the problem, though.
I have used Microsoft.VisualBasic.IO to import .csv files, and it has the ability to export to .csv as well. Importing was extremely quick. To use it you will have to do the following:
Add a reference in code to FileIO:
using Microsoft.VisualBasic.FileIO;
You will need to add a reference to the assembly. The steps are (Framework 4.5, VS Pro 2013):
Open the Project menu
Select Add Reference
Open Framework
Checkmark Microsoft.VisualBasic
You can then use a FileSystem class method called OpenTextFileWriter or WriteAllText to put text into a .csv.
|
|
|
|
|
It's a deep hole that is getting deeper.
"Magic numbers", and combined with the fact that "you are new to C#", this is a sad way to start.
Should've used VBA, or exported to CSV via "Save as...".
Even the original Excel choice may have been wrong.
One needs help from someone with some common sense and experience.
"(I) am amazed to see myself here rather than there ... now rather than then".
― Blaise Pascal
|
|
|
|
|
Gerry Schmitz wrote: some common sense and experience.
Yes, you might find some in QA. 
|
|
|
|
|
I have a Windows forms program that crashes every time I turn off the monitor, minimize the form, or perform an update on the form. I seem to be able to Update() individual objects on the form without any issue, but repainting the entire form causes the following error:
Quote: System.ArgumentException was unhandled
HResult=-2147024809
Message=Parameter is not valid.
Source=System.Drawing
StackTrace:
at System.Drawing.Image.get_RawFormat()
at System.Drawing.Graphics.DrawImage(Image image, Int32 x, Int32 y, Int32 width, Int32 height)
at System.Drawing.Graphics.DrawImage(Image image, Rectangle rect)
at System.Windows.Forms.PictureBox.OnPaint(PaintEventArgs pe)
at System.Windows.Forms.Control.PaintWithErrorHandling(PaintEventArgs e, Int16 layer)
at System.Windows.Forms.Control.WmPaint(Message& m)
at System.Windows.Forms.Control.WndProc(Message& m)
at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
at System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
InnerException:
As you can see, none of the trace refers to my code, but to System.Drawing dll. Here is the code I used to attempt to isolate the problem and produce this message:
try
{
if (WindowState != FormWindowState.Minimized)
this.Invalidate(true);
}
catch (System.ArgumentException ax)
{
World.Debugging.RTMessage("FindNextCritter()", "System.ArgumentException", "Exception: " + ax);
}
catch (Exception ex)
{
World.Debugging.RTMessage("FindNextCritter()", "this.Invalidate()", "Exception: " + ex);
}
try
{
if (WindowState != FormWindowState.Minimized)
this.Update();
}
catch (System.ArgumentException ax)
{
World.Debugging.RTMessage("FindNextCritter()", "System.ArgumentException", "Exception: " + ax);
}
catch (Exception ex)
{
World.Debugging.RTMessage("FindNextCritter()", "this.Update()", "Exception: " + ex);
}
... more stuff
}
The debugger pointed to the line that says "this.Update()", but neither of the two catch blocks on the associated Try managed to catch the exception. Remember, I also get the same result when I turn off the monitor or drag the form off the screen, but then the unhandled exception points to my Application.DoEvents() statement, which is also wrapped in a try block that fails to catch the exception. The rather wordy test code above is an attempt to isolate the issue.
I am using Microsoft Visual C# 2010 Express; you know, the free SDK downloaded from Microsoft. I suspect that I have done something stupid to the form, but I don't think I can upload that to show it. It contains 8 bitmap images that the program successfully updates from bitmap objects built internally, one doughnut graph defined on the form, two buttons, four radio buttons in a groupbox, several labels and textboxes, a checkbox, and another groupbox enclosing two of the bitmaps, the doughnut graph, and a label. Some of the labels have \n in them and stretch across multiple lines.
Yes, I know this is a big form. It's currently running on a TV screen with a form size of 1831,977. It was just more convenient and informative to have everything up there at once.
Thanks in advance for your help. I have googled around and found people with similar issues, but with no relevant solution. I am not an experience C# coder (old school CICS/COBOL here!) and I do not have local peers to ask.
|
|
|
|
|
Well, if you're using Application.DoEvents, you're doing something very wrong. Even more so with repainting individual controls or images. DoEvents doesn't go back to your apps normal message pump to process any pending messages, like WM_PAINT. It processes them in place, in it's own loop until the message queue is empty, essentially putting your app "on hold".
DON'T USE THIS, EVER. It just causes problems, like what you're dealing with.
|
|
|
|
|
That seems like sensible advice, thanks. I have to turn the program inside out to use another tactic,so it will take a while to see if that fixes it; now reading about BackgroundWorker...
|
|
|
|
|
Member 12748783 wrote: As you can see, none of the trace refers to my code, but to System.Drawing dll.
It is referring to one of your PictureBoxes. Remove them and the error goes away.
Bastard Programmer from Hell 
If you can't read my code, try converting it here[^]
|
|
|
|
|
LOL. I suppose that would work. 
|
|
|
|
|
Would that exception-handler be part of the OnPaint-method?
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
|
|
|
|
|
Dave is absolutely right: if you are using DoEvents there is a problem - and since your stack trace is talking about the fault happening in Paint event handlers, then if you are using DoEvents in that, then there is a very, very good chance that you are blowing the stack to pieces. DoEvents processes messages in the loop and dispatches them - so it allows the system to handle Paint events while it does something long and complex. If you start handling new Paint events within the Paint handler, there is a good chance that the Paint code caused a Paint event, and the DoEvents message loop processing is just stacking up calls - effectively recursing the Paint handler and using all the stack space.
Paint handlers should be as short and snappy as possible - any long processing at all in there will slow the whole app UI experience!
Bad command or file name. Bad, bad command! Sit! Stay! Staaaay...
AntiTwitter: @DalekDave is now a follower!
|
|
|
|
|
The vote seems to be unanimous. I'll recode it to avoid DoEvents and try to squish all the graphics updates together and see if that helps.
This is a long running (weeks, months) simulation, and updating a form is mostly an afterthought. The prior version was a console application and I thought, hey, wouldn't it be nice to be able to see what it's doing while it's running, rather than just analyzing the logs and watching the video afterwards? And my C# in Easy Steps book says DoEvents is the easiest way to do that.
Anyway, thanks, all, for the advice. Might as well close this one, because it will be a different program after the changes, lol.
|
|
|
|
|
You're welcome!
Member 12748783 wrote: my C# in Easy Steps book says DoEvents is the easiest way to do that.
"Easy" doesn't always mean "best"!
The "easiest" way to start driving is to steal a car and put your foot down - doesn't mean it's the best way to get to work every day!
Bad command or file name. Bad, bad command! Sit! Stay! Staaaay...
AntiTwitter: @DalekDave is now a follower!
|
|
|
|
|
Well poop.
For debugging purposes, I made minimal changes to see if y'all's suggestions helped to isolate the issue. I commented out the DoEvents, commented out the individual Update()s for text and picturebox elements, and relied on one this.Update(), as quoted in the original post. The exact same thing happens. It crashes on this.Update after this.Invalidate with the same message and the same stack trace. Stepping through the program on Debug I can see that it crashes the SECOND time it hits this.Update(). That is consistent. It will refresh the form once, but chokes the second time.
I also ran it for a while retaining the multiple individual Update()s and with the this.Update() commented out along with the DoEvents line. Other than locking the form and failing to redraw some text (as one might expect), it hummed along perfectly. I could not minimize the form, but turning the monitor off/on had no ill effects. Since this is a long running simulation and the only important controls on the form are START and STOP buttons, that was an improvement. (There are plenty of other ways to stop it!)
So I still have the same issue without DoEvents. Any thoughts?
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
