|
mjackson11 wrote: grab a reference to an existing instance of Excel or to start a new one if it isn't running
I haven't done this for a while, but I think you can replace all of that code with:
using System;
using System.Runtime.InteropServices;
using Excel = Microsoft.Office.Interop.Excel;
public static class ExcelInteropService
{
public static Excel.Application FindOrStartExcel()
{
try
{
return (Excel.Application)Marshal.GetActiveObject("Excel.Application");
}
catch (COMException)
{
}
return new Excel.Application();
}
}
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
Unfortunately, the new Excel.Application call is buggy. The Excel instance that is launched will show the addins as being loaded but the addins cannot be used from a vba macro.
|
|
|
|
|
According to this SO question[^], you need to loop through the installed add-ins, set the Installed property to false , and then back to true .
Alternatively, this blog post[^] suggests that you should use the Application.AddIns.Add method to load the add-ins, and explains why they're not loaded when you use new Excel.Application() .
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
OMG, I spent two days on Google looking for this. Thank you thank you thank you. It was driving me nuts.
Mark Jackson
|
|
|
|
|
What do you actually want to achieve? To me, your approach looks line an "XY problem": you do not describe the problem proper, but your idea of a solution which failed somewhere, and now you ask us how to procede on your wrong way. Let's get a few steps back and find the correct way to cope with it.
|
|
|
|
|
Hi guys.
I'm working on a creating an application that will read from and write to Excel spreadsheets here at work (personally I'd prefer that we migrate to using databases but I ain't the boss... yet ). Here's the problem, when reading a particular value from a cell in the spreadsheet, it should be empty but it seems like when I get the range from the spreadsheet programmaticlly, it creates and assigns a value even to the empty cells in the range. Am I perhaps calling the range the wrong way? If so, how can I do this the right so that the values in the range (on the program match the values on the actual spreadsheet?
Here's the code of how I got the ranges from the spreadsheet (I commented as much as possible):
Excel.Workbook source;
Excel.Workbook file;
Excel.Workbook submit;
Excel.Range currentRange;
string fileIMDR = ConfigurationManager.AppSettings["IMDR"].ToString();
string controlSource = ConfigurationManager.AppSettings["Source"].ToString();
string submitted = ConfigurationManager.AppSettings["Submitted"].ToString();
string imdrNo = "";
application = new Excel.Application();
//Get the control source and get the next IMDRNumber
source = application.Workbooks.Open(controlSource, Type.Missing, Type.Missing, Type.Missing,Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
workSheet = source.Sheets["Sheet1"];
workSheet = source.ActiveSheet;
//Selecting the range
currentRange = workSheet.Range["A:B"];
|
|
|
|
|
Hi guys. I was reading from the wrong file - I was accessing the actual file that the company uses, not the one I was I am using for testing. Sorry.
|
|
|
|
|
Hi,
I have upgrade my code from 3.5 to 4.5 framework.
In 3.5 framework I am able to connect to web servcies from code but in 4.5 I am unable to connect to web services from code but I can access this service from browser.
I am getting below mentioned error:-
- InnerException {"No connection could be made because the target machine actively refused it "} System.Exception {System.Net.Sockets.SocketException}
+ [System.Net.Sockets.SocketException] {"No connection could be made because the target machine actively refused it "} System.Net.Sockets.SocketException
+ Data {System.Collections.ListDictionaryInternal} System.Collections.IDictionary {System.Collections.ListDictionaryInternal}
HelpLink null string
HResult -2147467259 int
+ InnerException null System.Exception
Message "No connection could be made because the target machine actively refused it " string
Source "System" string
StackTrace " at System.Net.Sockets.Socket.DoConnect(EndPoint endPointSnapshot, SocketAddress socketAddress)\r\n at System.Net.ServicePoint.ConnectSocketInternal(Boolean connectFailure, Socket s4, Socket s6, Socket& socket, IPAddress& address, ConnectSocketState state, IAsyncResult asyncResult, Exception& exception)" string
+ TargetSite {Void DoConnect(System.Net.EndPoint, System.Net.SocketAddress)} System.Reflection.MethodBase {System.Reflection.RuntimeMethodInfo}
|
|
|
|
|
Try DELETING the web service reference and then re-adding it again (in the upgraded project).
Changing the .NET version of an existing project with an existing web service reference does not automatically "upgrade" the web reference.
modified 28-Aug-14 11:40am.
|
|
|
|
|
hi to all
i want wrote a one line Code Similare This
myDataTable.Select("Guid IN ('8556c1fd-d4a1-4c1c-b2af-c1fa9403ce4e')")
but i face With this Problem
'myDataTable.Select("Guid IN ('8556c1fd-d4a1-4c1c-b2af-c1fa9403ce4e')")' threw an exception of type 'System.Data.EvaluateException' System.Data.DataRow[] {System.Data.EvaluateException}
this is a one line Code That is ok, but i dont want use of this Method
myDataTable.Select("Guid = '8556c1fd-d4a1-4c1c-b2af-c1fa9403ce4e'")
any way is Exist that Use Guid in Linq FilterExpresion With 'IN'????
thank for Any Help
|
|
|
|
|
Is the Guid in the database as a string representation or a byte [] ?
|
|
|
|
|
Guid save in DataBase AS UniqueIdentifier
|
|
|
|
|
That's most probably the culprit. This "UniqueIdentifier" is not a string and apparently your database engine cannot convert it implicitly. Try to follow Richard Deeming's advice. Alternatively you could use a parametrized query instead and try to pass the guid as a byte[] (blob) instead of a string.
Hope this helps.
|
|
|
|
|
Try converting the column to a string in the filter expression:
myDataTable.Select("Convert(Guid, 'System.String') IN ('8556c1fd-d4a1-4c1c-b2af-c1fa9403ce4e')")
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
thanks Mr Deeming,Your Answer Save My life 
|
|
|
|
|
I've implemented a progress bar to my application but it makes the file copy process painfully slow whereas without the progress bar the copy is very fast. I know without the progress bar it only takes a few seconds to copy a 10 megabyte file but with the progress bar it takes more than a minute. What am I doing wrong?
namespace Compression_Util
{
class DropBox
{
private object sender;
private EventArgs e;
private DragEventArgs de;
private Design zip;
private string hover;
private string[] files;
private string filetype;
public DropBox(object sender, DragEventArgs de, Design zip, string hover)
{
this.sender = sender;
this.de = de;
this.zip = zip;
this.hover = hover;
this.Hover();
}
public DropBox(object sender, EventArgs e, Design zip, string hover)
{
this.sender = sender;
this.e = e;
this.zip = zip;
this.hover = hover;
this.Hover();
}
private void InitializeProgressBar(int fileSize)
{
zip.DropProgress.Visible = true;
zip.DropProgress.Minimum = sizeof(Byte);
zip.DropProgress.Step = sizeof(Byte);
zip.DropProgress.Maximum = fileSize;
}
private void DeInitializeProgressBar()
{
zip.DropProgress.Invalidate();
zip.DropProgress.Visible = false;
}
private void IncrementProgressBar()
{
zip.DropProgress.PerformStep();
}
private void CreateFile(string read, string write)
{
try
{
FileStream fileStreamReader = new FileStream(read, FileMode.Open, FileAccess.Read);
FileStream fileStreamWriter = new FileStream(write, FileMode.Create, FileAccess.ReadWrite);
BinaryReader binaryReader = new BinaryReader(fileStreamReader);
BinaryWriter binaryWriter = new BinaryWriter(fileStreamWriter);
int position = 0;
int length = (int)binaryReader.BaseStream.Length;
InitializeProgressBar(length);
while (position < length)
{
Byte line = binaryReader.ReadByte();
binaryWriter.Write(line);
position += sizeof(Byte);
IncrementProgressBar();
}
DeInitializeProgressBar();
binaryWriter.Close();
binaryReader.Close();
fileStreamWriter.Close();
fileStreamReader.Close();
}
catch (FileNotFoundException FileEx)
{
MessageBox.Show(FileEx.Message);
}
catch (Exception Ex)
{
MessageBox.Show(Ex.Message);
}
}
private string CreateFileName(string file)
{
int i = 0;
string deleteText = "";
string newFileName = "";
while ((i = file.IndexOf('.', i)) != -1)
{
deleteText = file.Substring(i);
i++;
}
this.filetype = deleteText;
newFileName = file.Replace(deleteText, ".xxx");
return newFileName;
}
private void Hover()
{
switch (this.hover)
{
case "Enter":
{
zip.DragLabel.Visible = false;
zip.DropLabel.Visible = true;
zip.BackColor = System.Drawing.Color.DarkGray;
zip.DropBox.BackColor = System.Drawing.Color.Thistle;
zip.DropBox.BorderStyle = System.Windows.Forms.BorderStyle.FixedSingle;
if (de.Data.GetDataPresent(DataFormats.FileDrop, false) == true)
{
de.Effect = DragDropEffects.All;
}
break;
}
case "Leave":
{
zip.DragLabel.Visible = true;
zip.DropLabel.Visible = false;
zip.BackColor = System.Drawing.Color.WhiteSmoke;
zip.DropBox.BackColor = System.Drawing.Color.WhiteSmoke;
zip.DropBox.BorderStyle = System.Windows.Forms.BorderStyle.None;
break;
}
case "Drop":
{
zip.DragLabel.Visible = true;
zip.DropLabel.Visible = false;
zip.BackColor = System.Drawing.Color.WhiteSmoke;
zip.DropBox.BackColor = System.Drawing.Color.WhiteSmoke;
zip.DropBox.BorderStyle = System.Windows.Forms.BorderStyle.None;
this.Drop();
break;
}
}
}
private void Drop()
{
this.files = (string[])this.de.Data.GetData(DataFormats.FileDrop);
foreach (string file in files)
{
string newFileName = this.CreateFileName(file);
if (!File.Exists(newFileName))
{
this.CreateFile(file, newFileName);
}
else
{
MessageBoxButtons buttons = MessageBoxButtons.YesNo;
DialogResult result;
string message = "File already exists, Overwrite?";
string caption = "";
result = MessageBox.Show(zip, message, caption, buttons);
switch (result)
{
case DialogResult.Yes:
{
this.CreateFile(file, newFileName);
break;
}
case DialogResult.No:
{
MessageBox.Show("Operation has been cancelled!");
break;
}
default: break;
}
}
}
}
}
}
|
|
|
|
|
kiasta wrote: What am I doing wrong? You're performing UI operations during the copy. What you should instead do is use a BackgroundWorker[^] to perform the file copy and publish UI updates.
/ravi
|
|
|
|
|
Thanks for the reply! I will definitely look into that.
|
|
|
|
|
Not only are you copying the file in the UI, you are updating the progress bar every IO. I always calculate percent complete using the following formula: (number of bytes copied * 100) / size of file in bytes
All numbers used in the calculation are long but the result is converted to int. Update the progress bar only when the percent changes.
|
|
|
|
|
Hmm OK that makes some sense, but why would copying it inside the UI make any difference? Without the progress bar the files copy very fast, almost instantly. I don't really understand, I guess. Is the progress bar really that resource intensive?
|
|
|
|
|
As you said, the progress bar slows down the copy. The progress bar is CPU intensive while the file copy is most likely IO bound. The progress bar slows the copy when the two operations share the same thread. Copying the file in a separate thread will allow more efficient use of multiple processors; both operations run almost independently.
Also, try copying a file several gigabytes in size and watch your UI lock up.
I tend to reuse code a lot. Code a process right the first time and use it many times.
|
|
|
|
|
Ah OK I see now what you mean, thanks for the clarification!
|
|
|
|
|
 To add to what the others say, you are also doing this byte by byte!
Buffer it: allocate a buffer of say 1/2 meg and fill it. Write it, update progress. Fill it, write it, update progress.
But I do it in a background worker like this:
private void MoveFile(string src, string dst, BackgroundWorker worker = null, ProgressReport prMain = null)
{
if (src != dst)
{
int iSrc = src.IndexOf(':');
int iDst = dst.IndexOf(':');
FileInfo fiSrc = new FileInfo(src);
if (fiSrc.Length < blockSize || (iSrc > 0 && iDst > 0 && iSrc == iDst && src.Substring(0, iSrc) == dst.Substring(0, iDst)))
{
if (doCopyOnly)
{
File.Copy(src, dst);
}
else
{
File.Move(src, dst);
}
}
else
{
using (Stream sr = new FileStream(src, FileMode.Open))
{
using (Stream sw = new FileStream(dst, FileMode.Create))
{
long total = sr.Length;
long bytes = 0;
long cnt = total;
int progress = 0;
while (cnt > 0)
{
int n = sr.Read(transfer, 0, blockSize);
sw.Write(transfer, 0, n);
bytes += n;
cnt -= n;
int percent = (int)((bytes * 100) / total);
if (progress != percent)
{
progress = percent;
if (worker != null && prMain != null)
{
ProgressReport pr = new ProgressReport
{
FilesCount = prMain.FilesCount,
FileNumber = prMain.FileNumber,
Filename = prMain.Filename,
Action = prMain.Action,
FilePercentage = percent
};
worker.ReportProgress(-prMain.FileNumber, pr);
}
}
}
}
}
FileInfo fiDst = new FileInfo(dst);
fiDst.Attributes = fiSrc.Attributes;
fiDst.CreationTime = fiSrc.CreationTime;
fiDst.CreationTimeUtc = fiSrc.CreationTimeUtc;
fiDst.IsReadOnly = fiSrc.IsReadOnly;
fiDst.LastAccessTime = fiSrc.LastAccessTime;
fiDst.LastAccessTimeUtc = fiSrc.LastAccessTimeUtc;
fiDst.LastWriteTime = fiSrc.LastWriteTime;
fiDst.LastWriteTimeUtc = fiSrc.LastWriteTimeUtc;
if (!doCopyOnly)
{
File.Delete(src);
}
}
}
}
That's a general purpose method which does a lot more than you probably want to, but it moves a couple of gig files pretty quickly and reports progress.
You looking for sympathy?
You'll find it in the dictionary, between sympathomimetic and sympatric
(Page 1788, if it helps)
|
|
|
|
|
The main objective is to compress the files into a container, but I want to at least be able to copy files with some type of progress notification at the very least first before I start making an algorithm for compression. Thanks for the source I'll study it and try to make something work.
|
|
|
|
|
Sir first of all Great Job and work done from your side.
Sir I want to learn C# at the professional level. I started working on your code (ME). I want to change the code and want to extend it (extend just for learning nothing else). I want to copy the equations directly just by selecting it. Can you please guide me that how can I copy directly my written equations in your ME?
(In step one I want to enable the copy feature and then in step two I want to add a functionality in the export menu to add "Export to Word").
Help Please
Thanks
|
|
|
|