|
Hi,
I would like to ask what's the best way to create and manupulate Microsoft Excel files in C#?
Thanks,
Jassim
Technology News @ www.JassimRahma.com
|
|
|
|
|
|
Try one of these, particularly if you're writing a program which will run unattended:
They're all free and open-source, and none of them need Excel installed on the computer.
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
Hello! You can use simple code to do, without external tools as Microsoft suggest. To initiate the excel, follow the code
<pre lang="c#">
using sExcel = Microsoft.Office.Interop.Excel;
using Microsoft.Office.Interop.Excel;
sExcel.Application excel = new sExcel.Application();
excel.ScreenUpdating = false;
excel.Visible = false;
excel.DisplayAlerts = false;
excel.AutomationSecurity = Microsoft.Office.Core.MsoAutomationSecurity.msoAutomationSecurityLow;
Workbook wb = excel.Workbooks.Add();
Worksheet ws = excel.Worksheets[1];
ws.Name = "ws";
Lucas Rafael Leandro Silva
|
|
|
|
|
|
I'm trying to get my web application to reload a DbContext cache after initialization to introduce new entities (if available, as introduced via MEF) into the context model. Currently the context populates via an OnModelCreating overload.
public class MyContext : DbContext
{
protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
var models = DependencyResolver.Current.GetServices(typeof(IModel));
foreach (var model in models)
{
var entityMethod = typeof(DbModelBuilder).GetMethod("Entity");
entityMethod.MakeGenericMethod(model.GetType())
.Invoke(modelBuilder, new object[] { });
}
}
I use a custom initializer that will add entities as appropriate on initilization ( I'm working on an archive for entity removal, but that's another topic).
My issue is this: I would like to dynamically load modules, have the context pick-up the IModel objects immediately and make them available for use. Does anyone know of a hook that will allow me to re-initialize the context and clear the cache without a complete application restart?
Note: I don't want to disable the cache completely, as this will not be an overly common task and it does offer more benefit than this functionality is worth.
|
|
|
|
|
You picked a very odd place to put the cache initialization code. You also don't mention what kind of application this is, Windows Forms, WPF, or ASP.NET.
It would be better if you implemented your own caching scheme so you have more flexibility on when it initializes, what it loads and how and what you clear. Your code implies that you're relying on a context that is created once during application startup, is never disposed and is basically pre-loading the entire database into memory. That really doesn't scale well!
I did something similar in a web app I'm working on now. I created my own custom caching scheme. It's just a single interface and a couple of classes really, and based all of my controllers and service layers on base implementations of it.
Data items that are used frequently, but rarely ever change are cached only when they are initially requested and dumped and reloaded at various points in the code when required, depending on what edit operations are taking place. I can also either dump an entire table of cached data or just a single item and replace it.
|
|
|
|
|
The DbContext lives in an MVC application, but from my understanding that should be immaterial as it's specifically an EF implementation and not an MVC one. The context itself is wrapped by a separate UnitOfWork object that is injected into my controllers, and it explicitly disposes each context instance (per request). The UoW abstracts the data source so that I can use the same controller-base with multiple sorts of data sources (XML, Web services, LDAP, etc).
I like your thought of a custom cache control; I've been so wrapped up in the details of the Entity Framework that such a clear solution hadn't crossed my mind, sadly enough. Forests and trees.
I use that as a fallback if I cannot determine another implementation using the framework itself. Thank you for the perspective!
|
|
|
|
|
MVC...
OK. The problem with what you're doing is that all the EF data that you're pre-loading goes away on every request!
Upon a request, your Controller gets created, which is creating a UnitOfWork, which is creating a context instance, which is loading all that data. When the request is done, the Controller Disposes, which also Disposes the UnitOfWork, which is also Disposing the DbContext. All that data goes with it. It's not retained in memory. You're not really caching anything at all, and , frankly, you're probably using up more time loading all the tables than if you just loaded the data the controller needs.
EDIT:
I may be misunderstanding what you're trying to do. Are you trying to modify the model on-the-fly and having EF rebuild itself based on the new model?
modified 24-Jun-14 12:17pm.
|
|
|
|
|
According to the MSDN[^] documentation the cache is created on first instantiation and tied to the AppDomain. To an extent that is my issue, because I cannot reset that cache. I can inject new IModels in at runtime and they are not included in new instances of the DbContext until the application is restarted, at which point OnModelCreating is called again. This implies to me that the cache is functioning as intended. The population of the context model is happening just once, as is efficient, but I would like to bump it when an extension is added to re-build the context model again.
Fortunately the DbContext doesn't actually execute any SQL until an IQuryable has a terminal method run on it, such as .Single, .First, or .toList; I agree that it would be mind-numbingly inefficient if it did. Instead, after the initial context creation it automatically caches the specific implementation of DbContext so that it holds the ORM but not the table data. Future instances come from this cache instead of being generated at runtime. It's pretty slick, honestly, and does perform nicely. My only complaint is the completely crazy statements it generates for Table-Per-Type implementations (which model polymorphism much better than Table-Per-Hierarchy or Table-Per-Class) and, of course, this cache refresh obnoxiousness.
I think I understand the communication breakdown here. I'm not interested in the data, I want to call that per request in accordance with the UnitOfWork pattern. I want to find that DbContext model cache and refresh it when a new extension is loaded.
|
|
|
|
|
That makes things a bit clearer!
OK, what you're refering to as a "cache" isn't. EF has to generate new models and mapping views of the model (query trees!) for query and updating as well as validate those views to make sure they make sense. This is what you're trying to get EF to regenerate. It's not really a cache but is the data EF uses to maintain the model and database mappings.
EF does maintain caches, such as the first-level object cache, the query plan cache and metadata cache. If you change the model, these caches must also be dumped and rebuilt. And then there's are second-level caching that you provide.
The problem with this is that EF maps strong types to the database using the Conceptual model. Without recompiling the code, you'd have to create/modify new entity classes on-the-fly in .NET to map them to the model. But, even then, once the changes are made, how are you going to maintain those changes if the app restarts??
I think I know what you're trying to do, but I don't know of any way to do it, let alone support it in a production environment. You're trying to out-smart Entity Framework, and frankly, the people that wrote it. I found a post 4 years ago by Julie Lerman that says she doesn't know of a way to do it and she's a developer on the EF code! But, there's also this blog post[^] that describes what might be a possible solution.
The only thing I've found that might do what you want is an old project on CodePlex that hasn't been updated since 2009, EFModelAdapter[^] I have no idea if it works with anything other than EF4. Somehow, I doubt it will.
|
|
|
|
|
I've spent some time poking around the EF 6 code and it looks like all access to the Tuple where the DbCompiledModel is stored are internal. I could implement a cache for the DbCompiledModel, but the amount of work to get it all wired in for use seems a little oppressive.
I finally settled on a basic HttpRuntime.UnloadAppDomain in the module-loading function to force a Context (and site) rebuild when new extensions are added. The user-level experience should be effectively the same, only I will know that it's not as snazzy as originally intended.
I think that this might be a good off-time project, at least if I plan to make deadlines!
By the way, the the Conceptual model is populated via the code snippet in the original question, which uses a DI provider (in this case a custom MEF IDependencyResolver) to determine the pool of entities required for tracking. The intent was that a DirectoryCatalog.Refresh() would automatically provide new entities for use without an application restart. It works fine for Controllers, ViewModels, and, of course, the Model classes themselves. Adding the Models to the context has been the only sticking point.
Thanks again for the help, Mr. Kreskowiak.
|
|
|
|
|
Hello!
I have visio running on my computer trough Microsoft Virtualization software which streams visio from a remote server. I'm writing a c# program to command visio using the reference Microsoft.Office.Interop.Vsio, when trying to instantiate the visio application "Application app=new Application()" an exception is raised and roughfully telling that Visio is not installed.
Does someone, know how to address this issue?
Any contributions will be really appreciated!
|
|
|
|
|
As you stated Visio is NOT on your computer but run via VM from some serve. So obviously Visio not installed on your local machine, but exactly that's where you run your code!!!
As you want to command Visio that runs on a remote server, you have to run your code too on that remote server!!!
I'm not questioning your powers of observation; I'm merely remarking upon the paradox of asking a masked man who he is. (V)
|
|
|
|
|
First of all, thank your for your contribution!
In fact, i have Microsoft application virtualization app-v client installed locally on computer trough which Visio is streamed. So i can open and read manually visio as it was locally installed locally on my machine.
Now, other than running the code on the server side, is there anyway using my c# code to instantiate the viio application.
I think it's possible!!
Thanks
|
|
|
|
|
You think wrong! All you have of Visio on your computer is an image streamed from the server...
I'm not questioning your powers of observation; I'm merely remarking upon the paradox of asking a masked man who he is. (V)
|
|
|
|
|
So the only way is to develop an application which runs on the server side so that everyone having Microsoft application virtualization client can access and run its?
If i'm right how to built such an application?
Thank!
|
|
|
|
|
To be hones, for now all I can tell you is that you can't do what you are tried to do here.
To be able to advise you you must tell what are you want to do...
I'm not questioning your powers of observation; I'm merely remarking upon the paradox of asking a masked man who he is. (V)
|
|
|
|
|
Great!
The purpose of my work is to automate the drawing of my telecommunication network. In my disposal: a datase sotirng my network desciption. The idea is to read from this database and draw the network map insigth a visio sheet. Next, i will be working on how to dynamically update the map
I hope it's clear!
Thanks
|
|
|
|
|
It's clear...
However it does not change my previous observation. To do so you need the Visio interop (or Visio itslef ot VSTO) where you will run your application.
Running Vision in a VM does not do that for you...
If the users of you application used to run applications via VM it can be a very good option to add one more in such way. Write your application on a development machine with Visio, than install on the Hyper-V and publish it to the users...
I'm not questioning your powers of observation; I'm merely remarking upon the paradox of asking a masked man who he is. (V)
|
|
|
|
|
WRONG!
Visio is NOT installed on your machine. It's installed in a virtual machine image thats running on your machine. You have to treat that like its a remote machine, not locally installed. Your running a virtual machine on your system, not Visio!
|
|
|
|
|
When you asked exactly the same question[^] last week, you got the same answer, and decided that you would have to install Visio on each computer.
You must have had a particularly exciting weekend if you'd managed to forget that in seven days! 
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
Nah - he was just hoping that reality had changed for the new week...
Those who fail to learn history are doomed to repeat it. --- George Santayana (December 16, 1863 – September 26, 1952)
Those who fail to clear history are doomed to explain it. --- OriginalGriff (February 24, 1959 – ∞)
|
|
|
|
|
There has been a weekend in between, stranger things have happened.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I use sql loader sqlldr to read from csv files and insert into DB. Bad file which is often a text file contains reject records. My question is how to convert this text file into excel file or is there any option to output bad file as an excel file in the slqldr command. My records are in unicode format which is also a challenge for me. hope to get your advices. thks
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
