|
Import Excel data with .NET
Hi
Before I start coding I need to be sure that nobody else has not done it yet and that I can use it. I need an import utility which import data from Excel to a database or some object in .NET. The data in Excel is not in structured columns but can exist everywhere in the workbook. For example if I am supposed to import a person and all his/her cars which exist in a workbook I want to be able to create an import protocol and specify that the first name exist in cell Sheet1!C4, the last name in Sheet1!F4, telephone in Sheet1!E10 and all cars in in column Sheet2!B4. The resulting table to a database could look something like this Agnes|Carlsson|123456|Subaru Agnes|Carlsson|123456|Volvo Agnes|Carlsson|123456|Ford Are their any Open Source projects, Frameworks and/or components which I can use or have a look at? Regards /Niklas |
Import Excel data with .NET
"Niklas" wrote in message
... Are their any Open Source projects, Frameworks and/or components which I can use or have a look at? Yes there are - loads of them - but, as Nicholas has already said, not required at all for what you need... |
Import Excel data with .NET
I have some examples of using Excel he http://www.sqlservercentral.com/columnists/sholliday/ But there aren't really any "frameworks" .. because excel data can be some random. You can try putting the excel data into a dataset... and work with it from there... in a business logic layer type object. "Niklas" wrote in message ... Hi Before I start coding I need to be sure that nobody else has not done it yet and that I can use it. I need an import utility which import data from Excel to a database or some object in .NET. The data in Excel is not in structured columns but can exist everywhere in the workbook. For example if I am supposed to import a person and all his/her cars which exist in a workbook I want to be able to create an import protocol and specify that the first name exist in cell Sheet1!C4, the last name in Sheet1!F4, telephone in Sheet1!E10 and all cars in in column Sheet2!B4. The resulting table to a database could look something like this Agnes|Carlsson|123456|Subaru Agnes|Carlsson|123456|Volvo Agnes|Carlsson|123456|Ford Are their any Open Source projects, Frameworks and/or components which I can use or have a look at? Regards /Niklas |
Import Excel data with .NET
Thank you. The programming stuff I know by heart, but I don't have the
resources to do a whole Export utility. We have several thousands Workbooks which are created by different users with different layout and now we want to export all this data to our other systems. This program/utility should be configurable so each user can export his/her own data. We are not the first company who wants to export Excel data, so I had a look at the Internet. What I found was a lot of utility which exports Excel tables to different databases. Our data is not in nice tables, but is scattered throughout the whole Workbook. The program should be able to create export protocol/templates which can be reused when doing an export on an already specified Workbook layout. I would prefer an utility/framework with source code which I can integrate with out own system. Regards /Niklas "Nicholas Paldino [.NET/C# MVP]" wrote: Niklas, There is really no need for such a thing really. You can use the Excel automation model to access the sheet directly and then get the values in those sheets/cells. Basically, you should set a reference to the Microsoft Office Primary Interop Assemblies in your project. Hope this helps. -- - Nicholas Paldino [.NET/C# MVP] - "Niklas" wrote in message ... Hi Before I start coding I need to be sure that nobody else has not done it yet and that I can use it. I need an import utility which import data from Excel to a database or some object in .NET. The data in Excel is not in structured columns but can exist everywhere in the workbook. For example if I am supposed to import a person and all his/her cars which exist in a workbook I want to be able to create an import protocol and specify that the first name exist in cell Sheet1!C4, the last name in Sheet1!F4, telephone in Sheet1!E10 and all cars in in column Sheet2!B4. The resulting table to a database could look something like this Agnes|Carlsson|123456|Subaru Agnes|Carlsson|123456|Volvo Agnes|Carlsson|123456|Ford Are their any Open Source projects, Frameworks and/or components which I can use or have a look at? Regards /Niklas |
Import Excel data with .NET
But if everyone uses their own layout/format, how do you expect to make it
easier than if everyone combine all their own data into a single table that you provide to them. If these data use Named ranges or some set you can locate, then code could combine them. Otherwise what rules can you apply to locate the required data in the correct order ? NickHK "Niklas" wrote in message ... Thank you. The programming stuff I know by heart, but I don't have the resources to do a whole Export utility. We have several thousands Workbooks which are created by different users with different layout and now we want to export all this data to our other systems. This program/utility should be configurable so each user can export his/her own data. We are not the first company who wants to export Excel data, so I had a look at the Internet. What I found was a lot of utility which exports Excel tables to different databases. Our data is not in nice tables, but is scattered throughout the whole Workbook. The program should be able to create export protocol/templates which can be reused when doing an export on an already specified Workbook layout. I would prefer an utility/framework with source code which I can integrate with out own system. Regards /Niklas "Nicholas Paldino [.NET/C# MVP]" wrote: Niklas, There is really no need for such a thing really. You can use the Excel automation model to access the sheet directly and then get the values in those sheets/cells. Basically, you should set a reference to the Microsoft Office Primary Interop Assemblies in your project. Hope this helps. -- - Nicholas Paldino [.NET/C# MVP] - "Niklas" wrote in message ... Hi Before I start coding I need to be sure that nobody else has not done it yet and that I can use it. I need an import utility which import data from Excel to a database or some object in .NET. The data in Excel is not in structured columns but can exist everywhere in the workbook. For example if I am supposed to import a person and all his/her cars which exist in a workbook I want to be able to create an import protocol and specify that the first name exist in cell Sheet1!C4, the last name in Sheet1!F4, telephone in Sheet1!E10 and all cars in in column Sheet2!B4. The resulting table to a database could look something like this Agnes|Carlsson|123456|Subaru Agnes|Carlsson|123456|Volvo Agnes|Carlsson|123456|Ford Are their any Open Source projects, Frameworks and/or components which I can use or have a look at? Regards /Niklas |
Import Excel data with .NET
That means a lot of manual copy&past which no one likes and it is resource
intensive. We are going to export a lot of old data and future data will also be exported in the same way. I would prefer that our super users do the data mapping in the export utility and then all users uses these mappings to export their data to our systems. Regards /Niklas "NickHK" wrote: But if everyone uses their own layout/format, how do you expect to make it easier than if everyone combine all their own data into a single table that you provide to them. If these data use Named ranges or some set you can locate, then code could combine them. Otherwise what rules can you apply to locate the required data in the correct order ? NickHK "Niklas" wrote in message ... Thank you. The programming stuff I know by heart, but I don't have the resources to do a whole Export utility. We have several thousands Workbooks which are created by different users with different layout and now we want to export all this data to our other systems. This program/utility should be configurable so each user can export his/her own data. We are not the first company who wants to export Excel data, so I had a look at the Internet. What I found was a lot of utility which exports Excel tables to different databases. Our data is not in nice tables, but is scattered throughout the whole Workbook. The program should be able to create export protocol/templates which can be reused when doing an export on an already specified Workbook layout. I would prefer an utility/framework with source code which I can integrate with out own system. Regards /Niklas "Nicholas Paldino [.NET/C# MVP]" wrote: Niklas, There is really no need for such a thing really. You can use the Excel automation model to access the sheet directly and then get the values in those sheets/cells. Basically, you should set a reference to the Microsoft Office Primary Interop Assemblies in your project. Hope this helps. -- - Nicholas Paldino [.NET/C# MVP] - "Niklas" wrote in message ... Hi Before I start coding I need to be sure that nobody else has not done it yet and that I can use it. I need an import utility which import data from Excel to a database or some object in .NET. The data in Excel is not in structured columns but can exist everywhere in the workbook. For example if I am supposed to import a person and all his/her cars which exist in a workbook I want to be able to create an import protocol and specify that the first name exist in cell Sheet1!C4, the last name in Sheet1!F4, telephone in Sheet1!E10 and all cars in in column Sheet2!B4. The resulting table to a database could look something like this Agnes|Carlsson|123456|Subaru Agnes|Carlsson|123456|Volvo Agnes|Carlsson|123456|Ford Are their any Open Source projects, Frameworks and/or components which I can use or have a look at? Regards /Niklas |
Import Excel data with .NET
Niklas,
You say you have "several thousands Workbooks which are created by different users with different layout". How many different layouts ? Can the user not record a macro of the actions for one sheet, the apply to all their own sheets ? NickHK "Niklas" wrote in message ... That means a lot of manual copy&past which no one likes and it is resource intensive. We are going to export a lot of old data and future data will also be exported in the same way. I would prefer that our super users do the data mapping in the export utility and then all users uses these mappings to export their data to our systems. Regards /Niklas "NickHK" wrote: But if everyone uses their own layout/format, how do you expect to make it easier than if everyone combine all their own data into a single table that you provide to them. If these data use Named ranges or some set you can locate, then code could combine them. Otherwise what rules can you apply to locate the required data in the correct order ? NickHK "Niklas" wrote in message ... Thank you. The programming stuff I know by heart, but I don't have the resources to do a whole Export utility. We have several thousands Workbooks which are created by different users with different layout and now we want to export all this data to our other systems. This program/utility should be configurable so each user can export his/her own data. We are not the first company who wants to export Excel data, so I had a look at the Internet. What I found was a lot of utility which exports Excel tables to different databases. Our data is not in nice tables, but is scattered throughout the whole Workbook. The program should be able to create export protocol/templates which can be reused when doing an export on an already specified Workbook layout. I would prefer an utility/framework with source code which I can integrate with out own system. Regards /Niklas "Nicholas Paldino [.NET/C# MVP]" wrote: Niklas, There is really no need for such a thing really. You can use the Excel automation model to access the sheet directly and then get the values in those sheets/cells. Basically, you should set a reference to the Microsoft Office Primary Interop Assemblies in your project. Hope this helps. -- - Nicholas Paldino [.NET/C# MVP] - "Niklas" wrote in message ... Hi Before I start coding I need to be sure that nobody else has not done it yet and that I can use it. I need an import utility which import data from Excel to a database or some object in .NET. The data in Excel is not in structured columns but can exist everywhere in the workbook. For example if I am supposed to import a person and all his/her cars which exist in a workbook I want to be able to create an import protocol and specify that the first name exist in cell Sheet1!C4, the last name in Sheet1!F4, telephone in Sheet1!E10 and all cars in in column Sheet2!B4. The resulting table to a database could look something like this Agnes|Carlsson|123456|Subaru Agnes|Carlsson|123456|Volvo Agnes|Carlsson|123456|Ford Are their any Open Source projects, Frameworks and/or components which I can use or have a look at? Regards /Niklas |
Import Excel data with .NET
I don't know how many layouts whcich exists, but a way to go is to let the
users record a macro, thank you. Regards /Niklas "NickHK" wrote: Niklas, You say you have "several thousands Workbooks which are created by different users with different layout". How many different layouts ? Can the user not record a macro of the actions for one sheet, the apply to all their own sheets ? NickHK "Niklas" wrote in message ... That means a lot of manual copy&past which no one likes and it is resource intensive. We are going to export a lot of old data and future data will also be exported in the same way. I would prefer that our super users do the data mapping in the export utility and then all users uses these mappings to export their data to our systems. Regards /Niklas "NickHK" wrote: But if everyone uses their own layout/format, how do you expect to make it easier than if everyone combine all their own data into a single table that you provide to them. If these data use Named ranges or some set you can locate, then code could combine them. Otherwise what rules can you apply to locate the required data in the correct order ? NickHK "Niklas" wrote in message ... Thank you. The programming stuff I know by heart, but I don't have the resources to do a whole Export utility. We have several thousands Workbooks which are created by different users with different layout and now we want to export all this data to our other systems. This program/utility should be configurable so each user can export his/her own data. We are not the first company who wants to export Excel data, so I had a look at the Internet. What I found was a lot of utility which exports Excel tables to different databases. Our data is not in nice tables, but is scattered throughout the whole Workbook. The program should be able to create export protocol/templates which can be reused when doing an export on an already specified Workbook layout. I would prefer an utility/framework with source code which I can integrate with out own system. Regards /Niklas "Nicholas Paldino [.NET/C# MVP]" wrote: Niklas, There is really no need for such a thing really. You can use the Excel automation model to access the sheet directly and then get the values in those sheets/cells. Basically, you should set a reference to the Microsoft Office Primary Interop Assemblies in your project. Hope this helps. -- - Nicholas Paldino [.NET/C# MVP] - "Niklas" wrote in message ... Hi Before I start coding I need to be sure that nobody else has not done it yet and that I can use it. I need an import utility which import data from Excel to a database or some object in .NET. The data in Excel is not in structured columns but can exist everywhere in the workbook. For example if I am supposed to import a person and all his/her cars which exist in a workbook I want to be able to create an import protocol and specify that the first name exist in cell Sheet1!C4, the last name in Sheet1!F4, telephone in Sheet1!E10 and all cars in in column Sheet2!B4. The resulting table to a database could look something like this Agnes|Carlsson|123456|Subaru Agnes|Carlsson|123456|Volvo Agnes|Carlsson|123456|Ford Are their any Open Source projects, Frameworks and/or components which I can use or have a look at? Regards /Niklas |
Import Excel data with .NET
Hi,
I am new in visual studio, and currently using ASP .net to do some of my design. I have a similar question, I need to be able to have my pages ask for an excel file, and automatically populate the data into a dataset. The excel file used as an input should have a pretty set layout, so all i need is a way to : 1. Refer to the excel file. (aspx page requests for the excel file to be inputted by the user) 2. Read each cell. 3. Populate my dataset in the aspx page. However, since i have no experience whatsoever in excel either, i am not exactly sure where to begin. "Basically, you should set a reference to the Microsoft Office Primary Interop Assemblies in your project" Currently this is all greek to me. It would be very helpful if you can perhaps point me to any documentations that i should be reading that is related to what i am trying to do. Also, a lot of my searches with keywords such as "importing excel to asp visual studio" comes more often than not with pages on exporting. Perhaps i should be looking with a more specific keyword? Thanks in advance, Phil "Nicholas Paldino [.NET/C# MVP]" wrote: Niklas, There is really no need for such a thing really. You can use the Excel automation model to access the sheet directly and then get the values in those sheets/cells. Basically, you should set a reference to the Microsoft Office Primary Interop Assemblies in your project. Hope this helps. -- - Nicholas Paldino [.NET/C# MVP] - "Niklas" wrote in message ... Hi Before I start coding I need to be sure that nobody else has not done it yet and that I can use it. I need an import utility which import data from Excel to a database or some object in .NET. The data in Excel is not in structured columns but can exist everywhere in the workbook. For example if I am supposed to import a person and all his/her cars which exist in a workbook I want to be able to create an import protocol and specify that the first name exist in cell Sheet1!C4, the last name in Sheet1!F4, telephone in Sheet1!E10 and all cars in in column Sheet2!B4. The resulting table to a database could look something like this Agnes|Carlsson|123456|Subaru Agnes|Carlsson|123456|Volvo Agnes|Carlsson|123456|Ford Are their any Open Source projects, Frameworks and/or components which I can use or have a look at? Regards /Niklas |
Import Excel data with .NET
http://www.sqlservercentral.com/colu...ataimports.asp
I think that will help. Its a KB I did a while back. "Phil" wrote in message ... Hi, I am new in visual studio, and currently using ASP .net to do some of my design. I have a similar question, I need to be able to have my pages ask for an excel file, and automatically populate the data into a dataset. The excel file used as an input should have a pretty set layout, so all i need is a way to : 1. Refer to the excel file. (aspx page requests for the excel file to be inputted by the user) 2. Read each cell. 3. Populate my dataset in the aspx page. However, since i have no experience whatsoever in excel either, i am not exactly sure where to begin. "Basically, you should set a reference to the Microsoft Office Primary Interop Assemblies in your project" Currently this is all greek to me. It would be very helpful if you can perhaps point me to any documentations that i should be reading that is related to what i am trying to do. Also, a lot of my searches with keywords such as "importing excel to asp visual studio" comes more often than not with pages on exporting. Perhaps i should be looking with a more specific keyword? Thanks in advance, Phil "Nicholas Paldino [.NET/C# MVP]" wrote: Niklas, There is really no need for such a thing really. You can use the Excel automation model to access the sheet directly and then get the values in those sheets/cells. Basically, you should set a reference to the Microsoft Office Primary Interop Assemblies in your project. Hope this helps. -- - Nicholas Paldino [.NET/C# MVP] - "Niklas" wrote in message ... Hi Before I start coding I need to be sure that nobody else has not done it yet and that I can use it. I need an import utility which import data from Excel to a database or some object in .NET. The data in Excel is not in structured columns but can exist everywhere in the workbook. For example if I am supposed to import a person and all his/her cars which exist in a workbook I want to be able to create an import protocol and specify that the first name exist in cell Sheet1!C4, the last name in Sheet1!F4, telephone in Sheet1!E10 and all cars in in column Sheet2!B4. The resulting table to a database could look something like this Agnes|Carlsson|123456|Subaru Agnes|Carlsson|123456|Volvo Agnes|Carlsson|123456|Ford Are their any Open Source projects, Frameworks and/or components which I can use or have a look at? Regards /Niklas |
| All times are GMT +1. The time now is 08:51 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com