 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
.NET Forum / Languages / C# / October 2007Dragging data to Excel using DoDragDrop
I am currently using Control.DoDragDrop to drop a text string into Excel. I use the following code DataObject dd = new DataObject(); dd.SetData(DataFormats.Text,"\\\\ws-it-bstough\\ShareFolder\\Test.wav"); DoDragDrop(dd,DragDropEffects.Copy); The code works as expected but it will always populate into Excel as a string literal and not as a link to the audio file. If I type the path into a excel the link appears correctly. I am using a windows form in C# to initiate the drag request. Any Suggestions ? Bruce Stough www.cti-results.com > I am currently using Control.DoDragDrop to drop a text string into > Excel. I use the following code [quoted text clipped - 9 lines] > > Any Suggestions ? I'm not sure you're going to be able to do that using the Text data format. I've noticed in the past that Excel won't auto-recognize URLs, UNC paths, etc. except when parsing direct user-input. If you were using automation, there might be a way to get it to do the recognition explicitly, but AFAIK you can't hook that up to a simple D&D operation. The one thing that does come to mind is the possibility of creating a .lnk format object for the DataObject. That might be in a format Excel would recognize and paste in as an actual link. Of course, how exactly to do that doesn't come immediately to mind. I guess you'll have to look into that yourself. But it's a thought anyway. :) Pete Thanks Peter, I have tried to use a LinkLabel object as well but Excel seems to be rejecting all requests with the exception of Text, Bitmap or Metafile. I will keep at it, and judging from the next sales post it is indeed possible. :)  SignatureBruce Stough CTI-Results.Com > > I am currently using Control.DoDragDrop to drop a text string into > > Excel. I use the following code [quoted text clipped - 27 lines] > > Pete > Thanks Peter, > I have tried to use a LinkLabel object as well but Excel seems to be > rejecting all requests with the exception of Text, Bitmap or Metafile. I > will keep at it, and judging from the next sales post it is indeed possible. > :) Yes, I'd agree. It does seem possible. IMHO, it's unfortunate that Adrian didn't see fit to provide any suggestion as to how you might do it yourself, instead choosing simply to market his own product. However, simply knowing that it can be done is at least part of the battle. Also, he does provide important clues. In particular, from his description is sounds as though his code uses the native Excel data formats. So, if you can use a native Excel data format from your own code, you could probably package the link in a way that would preserve the link formatting when dropped into Excel. Now, whether this is a justifiable effort on your part depends on how closely tied to Excel you want to be. This would definitely be an Excel-specific thing, and might not be worth doing if your application is to support drag-and-drop to a wider variety of programs. But if you're writing something that is specifically to be used with Excel, it might be worth the effort. If you do find it worth the effort, the first thing I'd do is try to find some sort of Office clipboard format documentation. Microsoft's been told by the EU and other people to make their formats "more open" so maybe there's some documentation on it. I'd start with the Office web site, looking for some links to some kind of file/clipboard format SDK or documentation. If that didn't pan out, I'd try to reverse-engineer the format. To do broad support for Excel data formats might be impractical, but for the specific scenario it might not be too hard. Just do a drag-and-drop _from_ Excel into your own application, dragging a cell that has a formatted link like what you want to create, and look at the data format that comes in. Then it should be a matter of replicating that format. I doubt that Office is encrypting the clipboard data. Best-case scenario would be that it's formatted as some sort of XML or other string data, but even if it's a binary format I would guess you could figure out what the "magic" bytes are and what the variable portion describing the actual link are. You may need to try a variety of different links, to pick up on where the string is stored, whether there's any sort of character index or count, etc. But it could probably be done. :) Good luck! Pete Hi Peter, > Yes, I'd agree. It does seem possible. IMHO, it's unfortunate that > Adrian didn't see fit to provide any suggestion as to how you might > do it yourself, instead choosing simply to market his own product. I feel I need to clarify my position here. I am not trying to hide anything. Even when I know I might not convince you, let me say I am not even trying to market anything, I don't think newsgroups are a good place to do marketing, and in fact, the effect is normally the opposite. The situation is simpler, I just saw the post, and said, well, he could do this with our product, and it might help him to know it. I can let him know this is possible. I doubted a lot before posting, because I know mentioning your own product has an adverse effect on many people, (and I am not going to post under a fake account) but I thought this could honestly help. After all, if this was not the correct solution for him, he could just dismiss the message. Now really after reading both Bruce's and your answer, I think I maybe should have keep myself shut. The reason I did not post a "suggestion on how to do it yourself" is just because I don't know one. But I do know how to do it using our library, and that is what I said. The solution I mentioned is simple on the conceptual part, and I am glad to answer any question I can answer about it. It just puts a "biff8" object into the clipboard, or drags a biff8 object into Excel. Just in case, Biff8 is the native file format in Excel. And the code to do that is on the demo I mentioned, all source for the demo is included in the file you can freely download from the page. So I don't know what else I could have said. What Bruce whas doing was trying to put a text, link whatever object on the clipboard, but Excel was not getting this as a link. If you put a biff8 object into the clipboard (or drag one), Excel will understand links (and formulas, cell colors, merged cells, etc). I do not know any other way to put links inside Excel, even when it might exist. Now, to create this biff8 object to put into the clipboard, I use our library. You could do it yourself, but programming a complete biff8 generator and this is a *lot* of work. So if you ask me "is it possible", of course it is. Excel does it. (you can drag and drop a cell with a link from a worksheet to another). And you can do it the same, if you do the same Excel does. Now, doing "what Excel does" means creating a biff8 object and putting it into the clipboard, and for that the only solution I know is our library. > However, simply knowing that it can be done is at least part of the > battle. Also, he does provide important clues. In particular, from > his description is sounds as though his code uses the native Excel > data formats. So, if you can use a native Excel data format from > your own code, you could probably package the link in a way that > would preserve the link formatting when dropped into Excel. I am giving more than clues. As mentioned before, the full source for the app is on the page, if you care about it. > Now, whether this is a justifiable effort on your part depends on how > closely tied to Excel you want to be. This would definitely be an > Excel-specific thing, and might not be worth doing if your > application is to support drag-and-drop to a wider variety of > programs. But if you're writing something that is specifically to be > used with Excel, it might be worth the effort. You can copy more than one format to the clipboard. for example, text for apps that do not understand biff8, and biff8 for Excel. > If you do find it worth the effort, the first thing I'd do is try to > find some sort of Office clipboard format documentation. Microsoft's [quoted text clipped - 3 lines] > file/clipboard format SDK or documentation. If that didn't pan out, > I'd try to reverse-engineer the format. The clipboard format is basically the same as the file format. But you need tho create an xls object for this to work. > To do broad support for Excel data formats might be impractical, but > for the specific scenario it might not be too hard. Just do a > drag-and-drop from Excel into your own application, dragging a cell > that has a formatted link like what you want to create, and look at > the data format that comes in. Then it should be a matter of > replicating that format. Not to be negative here, but I don't think that might work. The biff8 format is quite complex, and it has offsets everywhere that need to be updated. (and the whole object is inside an OLE container, that has its own fat table and offsets to the data). > I doubt that Office is encrypting the clipboard data. Best-case > scenario would be that it's formatted as some sort of XML or other [quoted text clipped - 8 lines] > > Pete Or you could just use our library :) I just wanted to say it again, I mentioned a valid option, that is valid for a lot of people (and we are luckily selling very well). The fact that it is not a solution in your case (or bruce) does not mean I am not trying to actually help, instead of just trying to market my products. Hope my position is clear now. Best regards and best of luck with whatever solution you choose, Adrian. -- > [...] > I doubted a lot before posting, because I know mentioning your own [quoted text clipped - 3 lines] > dismiss the message. Now really after reading both Bruce's and your > answer, I think I maybe should have keep myself shut. Or perhaps you could have just been a little more clear about things. I don't think that there's any need to have an absolute prohibition on mentioning one's own products. There are indeed situations in which it's appropriate to mention that as a possible solution. But your post was missing some elements that IMHO would have been important in differentiating a post that's a really honest attempt to be helpful from a post intended simply to market one's own product. Note that I'm not saying your intent wasn't honest. I'm simply saying it wasn't immediately apparent from the post you made. > The reason I did not post a "suggestion on how to do it yourself" is > just because I don't know one. But I do know how to do it using our > library, and that is what I said. Well, one thing you might have mentioned is that you don't actually know how to do it yourself. Nothing in your post made it clear that in spite of being involved with a product that apparently supports this feature, you yourself had no involvement or understanding of the actual implementation of that feature. Obviously it would be unreasonable for anyone to be disappointed in you for not sharing information you yourself do not have. > The solution I mentioned is simple on the conceptual part, and I am > glad to answer any question I can answer about it. [quoted text clipped - 3 lines] > for the demo is included in the file you can freely download from the > page. So I don't know what else I could have said. Nothing in your post mentioned the availability of source code. Nor is it clear from your post (or even now) that you are offering the source code itself as a guide to a solution, as opposed to requiring some payment to use the code. IMHO, assuming Bruce doesn't just copy your code wholesale, but rather just implements his own solution based on techniques found in your code, that would be a fine approach for him to technique. I doubt you'd have legal standing to demand payment from him either in that scenario. But you didn't write anything in your post suggesting that as a solution. You didn't even mention that the source code was available. Just that you had a library he could license for that use. > [...] >> Now, whether this is a justifiable effort on your part depends on how [quoted text clipped - 6 lines] > You can copy more than one format to the clipboard. for example, text > for apps that do not understand biff8, and biff8 for Excel. Yes. Believe it or not, I do understand that. My point is that applications don't generally go around putting every possible format of data onto the clipboard. An application would generally only put Excel's format on the clipboard if there was a high expectation that the target application would be Excel. If Bruce is writing an application that just happens to paste file references as text to the clipboard to be used by a wide variety of applications, he may simply want to accept Excel's limitation as given and not bother working around it (and IMHO this is definitely a limitation in Excel...there's no fundamental reason Excel couldn't do the same "link recognition" when pasting data as it does in other scenarios). On the other hand, if his application is designed specifically to work with Excel, then yes...it likely would make a lot of sense to do whatever work is required to ensure a seamless interaction between the two applications. Pete Hi Peter, > > [...] > > I doubted a lot before posting, because I know mentioning your own [quoted text clipped - 5 lines] > > Or perhaps you could have just been a little more clear about things. You can always be more clear about things, and there will always be people who will understand other things. I think I was quite clear and posted a helpful answer (that might be the only helpful answer actually posted in this thread). But we will have to agree to disagree here. > I don't think that there's any need to have an absolute prohibition on > mentioning one's own products. There are indeed situations in which [quoted text clipped - 5 lines] > Note that I'm not saying your intent wasn't honest. I'm simply saying > it wasn't immediately apparent from the post you made. Again, I am not sure how I could have made it more clear. I disclosed the product was made by my company, told him a viable solution where there was not other real solution suggested, and that is all. I see nothing 'dishonest' about it, but I will again agree to disagree here. > > The reason I did not post a "suggestion on how to do it yourself" is > > just because I don't know one. But I do know how to do it using our [quoted text clipped - 8 lines] > Obviously it would be unreasonable for anyone to be disappointed in > you for not sharing information you yourself do not have. Not sure you undesrtood my post. I do have involvement with the library and the feature, but that doesn't mean I know how to do this "myself" if by myself you mean not using any library. So I offered no help on doing without the library. I offered help on what I know can be reasonably done, get this feature using a third party lib that will take all the gory details of creating a biff8 file. You tell me that I should have also posted help on how to do it without it, but for that I should know how (in a way that doesn't involve a couple of years to develop your own biff8 lib). > Nothing in your post mentioned the availability of source code. Nor > is it clear from your post (or even now) that you are offering the > source code itself as a guide to a solution, as opposed to requiring > some payment to use the code. Nothing in my post mentioned the source and there was no need to either. If you are interested you look at it and see if there is source or anything helpful, if not you just forget the post. I posted to help, but I do not have to do your homework. > IMHO, assuming Bruce doesn't just copy your code wholesale, but rather > just implements his own solution based on techniques found in your > code, that would be a fine approach for him to technique. I doubt > you'd have legal standing to demand payment from him either in that > scenario. Now really, I think we are going too much out of topic here. Why are you speaking about my "legal standing", and why would I want to charge him for using the code on a demo? The code for the demo is freely available and you are expected to use it in your own programs. That is the whole idea, and it would be silly to demand payment for that. He will not be able to do much with the code without a library to create biff8 files, but that is other story. But if he gets to do it (maybe he can do it automating Excel) that is really cool with me. And as I have said before I do not care about his payment. I posted here to help him, not to make a couple of bucks. Whe sell at a low price, so we need to sell a lot in order to recover our investment. An order more or less does not make any difference. > But you didn't write anything in your post suggesting that as a > solution. You didn't even mention that the source code was available. > Just that you had a library he could license for that use. And this *is* valid help for lots of people. I do not undertand why there should be source code (even when there is) or why it should be free in order to be a valid solution. > > You can copy more than one format to the clipboard. for example, > > text for apps that do not understand biff8, and biff8 for Excel. [quoted text clipped - 4 lines] > Excel's format on the clipboard if there was a high expectation that > the target application would be Excel. Well, my applications copy to the clipboard on lots of formats, and so does office, and most apps I use. For example, when you copy text in visual studio, the text is copied as RTF so you can paste formatted code to word. But again, agree to disagree. > If Bruce is writing an application that just happens to paste file > references as text to the clipboard to be used by a wide variety of [quoted text clipped - 8 lines] > whatever work is required to ensure a seamless interaction between the > two applications. On the first case, he can just disregard my post, it is not going to help him much. In the second, what I said can be a helpful thing, and be a better solution than saying "it isn't possible". But I think we have lost much time here discusing things are not going nowhere. So I am stoping here. Good luck to both of you (honestly) and hope you get the best solution for your problem. Regards, Adrian. Thanks Peter, Adrian I am sorry I misunderstood the original postings, but the followups have been very insiteful. I will take a look at the biff8 format and see what it has to offer. After taking Peter's suggestion I did notice that a memorywriter object is returned as the dataformats are pretty much all encompassing. I assume this is the standard return for a serializable object though. I will post again as soon as everything becomes clear :) Thanks again for everyones help. SignatureBruce Stough CTI-Results.Com > Hi Peter, > [quoted text clipped - 130 lines] > Regards, > Adrian. Hi Bruce, Nice to know you could get something constructive after all. Hope you can solve your problems, and if you have any doubt where you think I can help about your implementation, please feel free to contact me. Good luck :) Adrian. > Thanks Peter, Adrian > I am sorry I misunderstood the original postings, but the [quoted text clipped - 6 lines] > > Thanks again for everyones help. -- Hi Bruce, Just an idea I had this morning (I sometimes can't help having those ideas in the morning...) You might try putting an RTF object in the clipboard. It will not be as nice as a biff8 object, but Excel will probably understand the hyperlink, and creating an RTF file is much easier than creating a biff8 file. I have not tested it so I can not tell you if it works, but it surely is worth a try. Best regards, Adrian. > You can always be more clear about things, and there will always be > people who will understand other things. When two different people have clearly misunderstood your post, and no other person has positively stated that they understood your post, it would be unwise to assume you have no room for improvement. That said, I'm still not entirely clear on what you're offering. Your second post may have simply confused the issue, as it seems we both got the correct impression the first time around. > [...] >> Note that I'm not saying your intent wasn't honest. I'm simply saying [quoted text clipped - 4 lines] > there was not other real solution suggested, and that is all. I see > nothing 'dishonest' about it, but I will again agree to disagree here. The "honest" comes in with respect to your intent, not your disclosure. That is, are you really trying to help the person implement their own code that accomplishes the same? Or do you just want to help them make use of your own pre-packaged solution? In a forum intended to help others with their own programming questions, I don't find the latter to be of "honest intent". Your opinion may vary, but in any case it's an entirely different question than the question of "honest disclosure". You certainly were up-front about the fact that you were offering your product for sale. That's not what I was talking about though. > [...] > Not sure you undesrtood my post. It seems that I did. > I do have involvement with the library > and the feature, but that doesn't mean I know how to do this "myself" > if by myself you mean not using any library. That is what I meant, and you did not provide any indication in your first post that in spite of being associated with a product that implemented that functionality, you yourself had no knowledge of how to implement it. There's nothing wrong with you not knowing how to do it. There's not even anything wrong with you not stating so explicitly, per se. It's just that had you said so explicitly, it would have avoided some confusion regarding your potential ability to help and the difference between you refusing to help and you simply not being able to. > [...] >> IMHO, assuming Bruce doesn't just copy your code wholesale, but rather [quoted text clipped - 8 lines] > available and you are expected to use it in your own programs. That is > the whole idea, and it would be silly to demand payment for that. Well, here again is a simple misunderstanding. You wrote that "It just puts a "biff8" object into the clipboard, or drags a biff8 object into Excel. Just in case, Biff8 is the native file format in Excel. And the code to do that is on the demo I mentioned, all source for the demo is included in the file you can freely download from the page". While your meaning is now clear, one interpretation of what you wrote is that the code that's available is the code that "puts a 'biff8' object into the clipboard". That is, implements the specific functionality being requested by Bruce to solve his problem. Instead, the code turns out to simply be example code of how to use _your_ library to accomplish the same. With your further elaboration, the meaning is clear, but I assure you that was not at all clear from your previous post. Now that you (I hope) understand how I misunderstood what you wrote, you can understand the point of my reply. It's obviously not relevant, since it was based on a misunderstanding. A lot of your objection to my post seems to be regarding this point. I hope you see now that what I wrote was perfectly reasonable given the understanding I had, even if that understanding turned out to be incorrect after all. > [...] >>> You can copy more than one format to the clipboard. for example, [quoted text clipped - 6 lines] > > Well, my applications copy to the clipboard on lots of formats, Your applications are not necessarily indicative of what most applications require. In fact, the fact that you have seen a need to implement BIFF support strongly suggests to me that your applications are designed with a very specific need in mind, to operate well with Office. Most applications do not share this need. > and so does office, I would hope that it can be taken as granted that Office applications are all written with the expectation that they will be used with each other. > and most apps I use. For example, when you copy text in > visual studio, the text is copied as RTF so you can paste formatted > code to word. But again, agree to disagree. What does RTF have to do with BIFF? RTF is a well-documented, standard text-formatting format understood by a wide variety of applications. Any application that uses formatted text as a copyable piece of data is likely to export and import RTF, and for good reason. BIFF, on the other hand, is a very specific format for a very specific purpose, not widely supported, and not worth messing around with unless you have a specific need to interoperate with Excel. How does broad support for RTF in any way justify an application also supporting BIFF, if that application is not designed or intended to specifically work with Excel? > [...] > On the first case, he can just disregard my post, it is not going to > help him much. In the second, what I said can be a helpful thing, and > be a better solution than saying "it isn't possible". Yes, I agree and said as much in a previous post. It is very helpful to know that it can be done. However, this forum is primarily for helping other people implement their own code. It's clear now that you don't have the information required to go beyond the solution you offered, and there's nothing wrong with that. My point is simply that your original post did not make this at all clear, and IMHO you should not be surprised that it wasn't clear. If after all of this, you wish to remain surprised, that is of course your prerogative. It is just my opinion, after all. Pete Peter, You have expressed your opinion, I have expressed mine, and I don't think it makes sense to repeat it again and again. I think the root of all our misunderstandings is here: >However, this forum is primarily for helping other people implement >their own code. I believe this forum is primarily for helping other people solve their problems. In you light, what you said makes sense. In my pov, whay I said (and did) makes sense too. This is my last post here, since this is not going nowhere. Best regards, Adrian. > You have expressed your opinion, I have expressed mine, and I don't > think it makes sense to repeat it again and again. Yet, you keep doing so. Odd. > I think the root of all our misunderstandings is here: > [quoted text clipped - 3 lines] > I believe this forum is primarily for helping other people solve their > problems. And I believe that's far too general a description. We are writing code here. As I've said, I'm not actually promoting a no-exceptions rule to exclude any reference to third-party libraries. Sometimes those are just the right solution. But the preferred solutions should be in the form of real, usable code that uses the .NET Framework (and only the .NET Framework) to solve the problem. If a person has knowledge that would allow that form of solution to be posted, they should share that rather than just hawking their own wares. And just to try to make this even more clear, since this doesn't seem to be registering with you: at this point, it's clear you don't have that knowledge, and so of course I don't fault you for not posting it. It's just that your first post did not make this clear at all, thus my initial reaction. My primary complaint (if you can even call it that) at this point is with respect to the potential improvement with respect to communicating why you posted what you did instead of a full solution. Like I said, if you can't post the actual answer to the question, that's fine. But you should say so, to avoid any misunderstanding as to whether you are simply refusing to share your own knowledge in deference to selling a product. And to reiterate: it's simply the avoidance of the misunderstanding that I'm talking about. If you don't mind the misunderstanding, then there's absolutely no need for you to concern yourself with my comments. I do think it's a bit silly of you to say that your original post was perfectly clear; it seems to me that the mere fact that this thread has taken the direction it has is proof enough that there's room for improvement on the clarity front. But hey, that's your business. If you think I'm wrong about that, there's no need for you to be offended or argue about it. Just move on. If you do insist on arguing about it, I am happy to oblige. At least, to a point much farther than I suspect most people would. Even I have my limits. :) > In you light, what you said makes sense. In my pov, whay I said (and > did) makes sense too. > > This is my last post here, since this is not going nowhere. You said that before too. Pete Hi Bruce, > The code works as expected but it will always populate into Excel as > a string literal and not as a link to the audio file. If I type the > path into a excel the link appears correctly. I am using a windows > form in C# to initiate the drag request. > > Any Suggestions ? I am not sure if you can use third party comopnents, but if that is possible, we fully support dragging and dropping native xls data (including hyperlinks) to and from Excel. If you are insterested, you can take a look at www.tmssoftware.com/go/?flexcelnet download it, run the demo from the start menu, and select "API->Copy and Paste" demo from the left menu. In the demo you will be able to drag an hyperlink from Excel to the demo app, and then drag it again from the demo app to Excel. Of course your app you can create the hyperlink directly and let the user drag it to Excel. The xls reading/writing/reporting part of FlexCel is free for non commercial use, so it should be not a problem if your app is non commercial. If it is commercial, I believe the price is quite affordable anyway, and will pay itself very fast. Best regards, Adrian. The fix is in for those of you that would like to know. Excel ( Office in general ) utilizes html hyperlinks to reference UNC's. A UNC path does not work due to no small part because Excel autoformats the text when you press enter on the spreadsheet. In order to populate with DoDragDrop then format the reference using an Anchor tag as such : private void listView1_ItemDrag(object sender, ItemDragEventArgs e) { ListViewItem lvi = (ListViewItem)e.Item; String s = "<HTML><A href= \"" + <UNC Path> + "\">" + <HyperlinkText> + "<\\A><\\HTML>"; DoDragDrop(s, DragDropEffects.Copy); } Enjoy SignatureBruce Stough CTI-Results.Com > I am currently using Control.DoDragDrop to drop a text string into Excel. I > use the following code [quoted text clipped - 12 lines] > Bruce Stough > www.cti-results.com Free MagazinesGet these publications absolutely FREE for up to 12 months. There are no hidden fees and no obligation. Simply choose a title, complete the application form and submit it. Read more ...
|
Reply to this Message
|
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.