|
I'm aware of at least two possibilities for reading directly from xls files:
- Microsoft.Office.Interop.Excel
- Microsoft.ACE.OLEDB.12.0 OleDB provider
From what little information I've been able to find on the subject, it seems that the Microsoft.Office.Interop.Excel method requires that Excel be installed on the system where this will be run. Is this true?
Basically, I want a reliable (read: can be run on any system Windows 7 or higher with or without office installed) to read data from an XLS or XLSX spreadsheet. I don't need to do any writing to the format, I just need to read from it and dump the data into a SQLite or SQL Server Compact database table.
Are there any methods other than those two I mentioned, and which would be the highest recommended method of doing what I need?
Thanks.
|
|
|
|
|
agent154 wrote: Is this true?
Yes. So I avoid it.
agent154 wrote: Microsoft.ACE.OLEDB.12.0 OleDB provider
That's my preference.
agent154 wrote: read from it and dump the data into a SQLite or SQL Server Compact database table.
That's the sort of thing that SSIS does.
You'll never get very far if all you do is follow instructions.
|
|
|
|
|
Just an FYI since the other guy didn't mention it at all...
* Your first choice, of course requires that Excel be installed, but is a lot less work and is more platform independent.
* Your second choice does not... HOWEVER, and this is a BIG HOWEVER because if you don't know about this, you'll spend **weeks** trying to figure out what's going on... Microsoft ACE is the 64-bit driver. That's for a 64-bit OS and a 64-bit process **ONLY**. Won't run on a 32-bit OS or if your process is 32-bit. No big deal you're thinking, right? Not quite... if the machine happens to have 32-bit Office installed, you can't even install the 64-bit Ace driver, regardless of your OS because you aren't allowed to mix bitage. You have to install a different 32-bit driver and that limits your process to running as 32-bit because you can't use the 32-bit driver from a 64-bit process or vice versa.
This causes a lot of issues... you'd have to have a 32-bit build that uses the 32-bit driver if the machine has 32-bit Office installed and a 64-bit build that uses the 64-bit driver if the machine has 64-bit Office installed.
Most 64-bit machines I've seen have 32-bit Office installed... just sayin'... so you're limiting yourself to a 32-bit process.
|
|
|
|
|
AAAHHhhhh and they wonder why I dump on Office from a great height every opportunity I get. I hate having to deal with Office version incompatibilities, now you tell me the processor type may be an issue.
Ok so now I can see a benefit to the 32 bit OS policy I have been muttering about for some time.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
|
Both EPPlus[^] and ClosedXml[^] will allow you to read and write Excel 2007+ files (*.xlsx).
If you need support for the older format files (*.xls) as well, then NPOI[^] is pretty good.
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
agent154 wrote: requires that Excel be installed on the system where this will be run.
What is the Business case where that matters?
If this is a client machine and the spread sheet is just a read only data store that comes with the application why not use a different format?
If a client machine where a user can modify the file then they would need excel anyways.
If a server machine then the situation is similar to the above but there should be less concern about cost.
|
|
|
|
|
Could someone elaborate on how the runtime determines where to allocate a variable (stack/heap)?
class MyClass()
{
int x;
}
private void AssignmentCheck()
{
MyClass mc = new MyClass();
mc.x = 10;
int MyInt = 5;
}
space will be allocated on the heap for the new instance of MyClass and the reference to the heap location will be saved to the variable mc.
mc.x is on the heap and MyInt will be on the stack.
My question is how does the runtime know to allocate space on the heap for MyClass and space on the stack for myInt ?
Does the runtime do some kind of type checking prior to assignment ?
i.e.
ok, MyClass is a ref type, I'd better put it on the heap
MyInt derives from System.ValueType and it's a local variable, I'd better put it on the stack.
Obviously an implementation detail but curious to know..
thx in advance
|
|
|
|
|
|
taking_liberties wrote: ok, MyClass is a ref type, I'd better put it on the heap
MyInt derives from System.ValueType and it's a local variable, I'd better put it on the stack.
Obviously it can't do that, since not all instances of value types always go on the heap (for example, MyClass.x).
The MSIL tells it what to do. Your example code was unfortunately a poor example, because ints are magic and therefore don't show how code with value types compiles in general.
So I changed the code a bit.
.maxstack 2
.locals init (
[0] class Test.Program/MyClass mc,
[1] valuetype Test.Vec2 v
)
IL_0000: nop
IL_0001: newobj instance void Test.Program/MyClass::.ctor()
IL_0006: stloc.0
IL_0007: ldloca.s v
IL_0009: initobj Test.Vec2
IL_000f: ldloc.0
IL_0010: ldloc.1
IL_0011: stfld valuetype Test.Vec2 Test.Program/MyClass::x
IL_0016: ret
This should make some things clear. A reference to a MyClass and an instance of Vec2 both conceptually go on the stack (that is, they are locals). The instance of MyClass is "newobj"-ed, while the instance of Vec2 is "initobj"-ed. newobj allocates an object and pushed a reference to it on the evaluation stack - not "the stack", this is just the stack that MSIL conceptually uses because it's a stack language, that's really just to connect instructions implicitly to their operands. "initobj" takes an address and zeroes out the fields of the value type there in-place.
When the JIT compiler gets its hands on this, it could do anything that preserves the semantics, and what it actually does, depends. In debug mode (ie ran in a debugger and Suppress JIT Optimizations is ON (default)) it really likes The Stack (the real deal, nothing conceptual at this point) and all locals are always written back to the stack even when they are then immediately read back. In optimized mode, most temporaries only appear in registers, and locals are often completely enregistered as well (unless it doesn't fit or they have to be preserved across a call). It seems to have a tendency to put non-primitive value types on the stack though. (other JIT compilers may behave differently, YMMV)
|
|
|
|
|
Thx. I'm assuming then that there is some kind of type-checking going on as newobj is called for MyClass ?
|
|
|
|
|
It's pretty simple, sort of.
Reference types: the instance always goes on the heap. The reference itself may be stored on the stack, or on the heap as part of another reference type instance.
Value types: the instance goes on the stack, goes on the heap as part of a reference type instance, or may be "boxed" onto the heap in order to pass a reference to it to a method.
This may help: Using struct and class - what's that all about?[^]
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 – ∞)
|
|
|
|
|
It's something I don't like in .NET that much, the tendency to stick everything on the heap. Sure, heap allocations are super quick, but you just pay for it the other end in the GC.
There are times when stack allocations of 'reference types' would be damn handy. Makes me miss C++.
I've got this new C based language in my head, a cross between C++ and C#. It would have the rather beautiful imbedded metadata of an assembly so no header files and all that nonsense but would do away with woosey garbage collection. There would be a linking stage which could strip the metadata at will giving you a raw executable. The compiler would spit out native code for different processors along with some sort of CIL too, the linker 'targetting' the platform and what's exposed.
Best of all though, it would do away with usings at the top of you code. What the hell is the point of these? The fact you can right click to insert them tells you they're useless. Well, they would be there in fact but only to deal with the rare case of name clashes.
Regards,
Rob Philpott.
|
|
|
|
|
Rob Philpott wrote: There are times when stack allocations of 'reference types' would be damn handy.
Yes, but...that leads to it's own problems with dangling references:
MyClass MyMethod()
{
return new MyClassOnStack();
}Which caused so much fun and games in C and C++. You can't do that in C#, by design, because all references are heap based.
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 – ∞)
|
|
|
|
|
Hello ,
I have a web site that uses a FrameGrabber project which capture a frame out of video.
This FrameGrabber is using the DexterLib dll.
Until now , everything worked fine on our previous server that uses server 2003 OP,but lately we published this site on
a new server that Using server2012 op. Since then the DexterLib collapses on this row:
MediaDetClass mediaDet = new MediaDetClass();
with the following error:
Creating an instance of the COM component with CLSID {65BD0711-24D2-4FF7-9324-ED2E5D3ABAFA}
from the IClassFactory failed due to
the following error: 8007000e Not enough storage is available to complete this operation.
(Exception from HRESULT: 0x8007000E (E_OUTOFMEMORY)).
More Over ,In all of our developers machine, the code works fine with no errors.
I tried to debug the project in the visual studio on the new servers machine and seperate this row to another file ,
And the same thing happend , the code collapsed with the same error.
When I created a new seperated console application on this machine, using the framegrabber, everything worked fine.
Even more odd is that when I added a new web site to the current solution with a test page that uses the frame grabber,
again,everything worked fine.
I tried to rebuild the solution , and remove the DexterLib dll and add it again. Everything I tried until now , had failed.
Any help will be appreciated,
Kind Regards,
Tal Humy
|
|
|
|
|
this[^] one? I don't see any .NET assemblies in there?
Tal Humy wrote: Not enough storage is available to complete this operation.
Well, is it out of memory? If you are low on harddisk-space then this error would occur, as Windows can't grow its swapfile.
Bastard Programmer from Hell 
If you can't read my code, try converting it here[^]
|
|
|
|
|
That error wasn't thrown by a managed component. There are some strange and confusing situations when you get an "Out of Memory" error message but such an error did not happen. Actually, it was a different problem in the non-managed component.
Hence look for things which the functioning programs have in common, things which the crashing programs have in common, and the differences between the groups. E.g. bit-ness, user context (service, interactive, admin, ...) etc.
|
|
|
|
|
Hi, I have written an OData 4.0 WebAPI.
As anyone answering this question probably knows, $InLineCount=AllPages has now been replaced with $Count=True.
For backward compatibility purposes, I need to continue to support $inLineCount=AllPages as an option, I have tried for two days to override the validation of this, but I always get a message "The query parameter '$inlinecount' is not supported.".. I created a custom QueryOption validator, tried a CustomMessageHandler in the Configuration.MessageHandlers to no avail.
Would anyone know if/where the URL could possibly be overridden before getting to the OData code. The validation of the $inLineCount value, seems to occur to early for me to override. I would simply like to replace $inLineCount=AllPages with $count=true when the request arrives.
|
|
|
|
|
What the heck does this have to do with C#?
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 – ∞)
|
|
|
|
|
It hard to see what you are talking about without see your code...
I'm not questioning your powers of observation; I'm merely remarking upon the paradox of asking a masked man who he is. (V)
|
|
|
|
|
Sorry guys, it's a C# webAPI question.
I didn't see anything detailing specifically OData, so I thought it would fit in C#.
I don't think it's really a code specific question however, more of a question of Method. Here's my code below though. When an OData GET is called, this method fires, but even though I set a custom validator, the oData code is kicking out my request before it gets there, because "$inlineCount" is an unsupported method. My goal is to thow $inlineCount out of the REQUEST URL myself before it ever gets here. But I don't know when/where the request is getting blocked.
[EnableQuery(AllowedQueryOptions = AllowedQueryOptions.All, PageSize = 5)]
public IHttpActionResult Get(ODataQueryOptions<Task> queryOptions)
{
try
{
queryOptions.Validator = new CustomValidator();
return Ok(_db.Tasks.AsQueryable());
}
catch (Exception e)
{
_logger.LogError(e.ToString());
return BadRequest(e.ToString());
}
}
|
|
|
|
|
Hello!
I'm new here hope you guys can give me a suggestions/answers to my questions.
I have an aspx page which has two dropdownlist the first dropdownlist populates school level and the second dropdownlist populate available section depending on the selected school level. I had enabled already the EnableViewState to TRUE from the page and to my master page. Below is my code.
On my Page Load event I populate the school level, I have this condition
If(!IsPostback)
{
PopulateSchoolLevel();
}
on my first dropdownlist selectedindexchanged event I have this code
Datatable dt = New DataTable;
dt=_queries("StoredProcedure",ddlLevel.SelectedValue.Tostring());
if(dt.rows!=0)
{
ddlSection.Datasource=dt;
ddlSection.ValueTextField="Section";
ddlSection.DataValueField="id";
ddlSection.DataBind();
}
now every time i select a value in my second dropdownlist it always return to the first index or first value. how can i retain the selected value on my second dropdownlist? I'm having burned a lot of time for this screnario.. please help me.
Thanks
|
|
|
|
|
Sorry, not enough code to fully answer but try this. I am running from memory now so I might be off a bit. I had a similar problem but found that I was forcing the second dropdown back to the first object. IIRC I also a selectedindex change event on the second dropdown and that was where my problem was.
If this doesn't help drop in more of the code. 
Jack of all trades, master of none, though often times better than master of one.
|
|
|
|
|
every time you change the index you re-bind the data source.
a very simple solutions
session["current"] = ddlSection.selectedindex;
on page load
ddlSection.selectedIndex = convert.toint32(session["current"]);
|
|
|
|
|
Thanks for your replies.. i tried your suggestion by storing the selectedindex of my second dropdownlist into session. i put a breakpoint on Loading event to see if the selectedindex value was stored but unfortunately it was not stored,it always stored the first value of my second dropdownlist.. here are some of my codes below
private void _getSection()
{
ddlSection.Datasource=_queries("StoredProc",ddlLevel.SelectedIndexChanged);
ddlSection.DataTextField ="SectionName";
ddlSection.DataValueField="id";
ddlSection.DataBind();
}
private void _loadLevels()
{
//_queries is a class handles all queries to my sql database.
ddlLevel.Datasource=_queries("select * from levels");
ddlLevel.DataTextField ="LevelName";
ddlLevel.DataValueField ="id";
ddlLevel.DataBind();
}
on my pageload event i call the _loadLevels() function. I have this condition
if(!IsPostback)
{
_loadLevels();
}
if(Session["_sectionid"]!=null)
{
string _result = Session["_sectionid"].Tostring();
}
//---------------
protected void ddlLevel_SelectedIndexChanged(object sender, EventArgs e)
{
Session["_sectionid"]=ddlSection.Selectedindex;
_getSection(); //-- this will populate the section in my ddlSection which values are
ex. DataTextField DataValueField
St.Dominic 1
St.Agustine 2
}
Autopostback of my DDL's is set to True and EnableViewState from my masterpage is also set into True.
Every time i select the second or third item in my ddlSection it always give me the first index I can't understand???...Im sure that there is no event calling my _getSection function only in the ddlLevel. by the way Im using Masterpage but there is no code i have in my masterpage. Please let me know what's happening on this scenario?
modified 30-Jul-14 1:38am.
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
