Inserting Data from Data Table / Data Set into Excel worksheet

Hello,

I wanted to know an easy way to insert data from a Data table into an excel work sheet which already has a couple of columns populated.

What I mean by this is say suppose I have an excel file and in WorkSheet 1 there are already two columns populated.

Now I would like to use this data table and which say has 7 columns and insert into worksheet 1 in such a way that I dont delete the first 2 columns.

So the finally the Worksheet1 will have  2 ( Already exisiting coulms ) + the 7 new columns from the data table.

Also I would like to insert the Data table column names into excel as its column names ..

Any help would be greatly appreciated.

Thanks,
Ashwin
Ashwin_shastryAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

dlmilleCommented:
This sounds fairly straight forward.  Are the data table columns just inserted adjacent to the 2 Excel columns, or is there a need to match-up keys in the Excel columns with a column or two in the data table?  Where does the data table reside (in another Excel workbook, or from a query, or what?)

Dave
Ashwin_shastryAuthor Commented:
Dave, thanks for the reply.. we dont need to match any of the columns in the already exisiting columns in Excel to the ones in Datatable. I jsut need to insert as is ...

Could you provide me a link which does this .. that would be a great ... or may be a  code snippet.

Thanks,
Ashwin
dlmilleCommented:
I can give you the code, but can you respond to the other question:  where does the data table reside?  How, from Excel will it be accessed?  Is it in another workbook, another sheet in the same workbook, in a database like access, or what?

Dave
Rowby Goren Makes an Impact on Screen and Online

Learn about longtime user Rowby Goren and his great contributions to the site. We explore his method for posing questions that are likely to yield a solution, and take a look at how his career transformed from a Hollywood writer to a website entrepreneur.

Ashwin_shastryAuthor Commented:
Dave sorry about the delay in replying .. I was able to insert the data table into the work sheet.

Now for specific cells in a work sheet I would like to change the background color of the cell.

How can I do that ?

I did this

expectedResultsWorkSheet.Cells[rowCount, i + 2].Style.Font.Color = Color.Blue;

And for whatever reason it changed the color of text in the entire workbook not jsut the work sheet I wanted in.

I actually wanted to change the background color of the cell ... not the color of the the text BTW.

Hope you can help me out.

Thanks,
Ashwin
dlmilleCommented:
>>I was able to insert the data table into the work sheet.

That's great

To change the background FILL of a cell, use:

expectedResultsWorksheet.Cells(rowCount,i + 2).Interior.Color= vbBlue

Are you doing this in a loop?  It appears from the object expectedResultsWorksheet.Cells(rowCount,i+2) that you are referencing only one cell, so it should only be changing one cell.

Can you check to see:

debug.print expectedResultsWorksheets.Cells(rowCount,i+2).Address

while you're processing, to demonstrate the address is only one cell range?

Are you writing VBA or C#.  the brackets would be invalid in VBA

Dave

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ashwin_shastryAuthor Commented:
Hi Dave,

Thanks for the prompt reply again!.  I was able to accomplish what I  wanted to achieve.  I have written a console app to do whatever i wanted to do in C#.

Now I wanted to know if I can attach the project here for you to just review it and see if there is a better way to do it ... do you think it would be possible for you to do a quick review. ?

Thanks,
Ashwin.

Ashwin_shastryAuthor Commented:
BTW its a pretty simple 1 file console app.
dlmilleCommented:
I'm happy to review, and I've just got Vis Studio and have C# and VSTO, etc., but I'm coming up that learning curve.  However, I can review the VBA as that's my best expertise area, and I have knowledge of VB.Net - so upload and let's take a look.

Dave
Ashwin_shastryAuthor Commented:
Thanks Dave, I will post the project shortly ...
Ashwin_shastryAuthor Commented:
Hi Dave ,

Sorry about the delay in getting back ... I have attached the code and the excel file which is both the input and the output...

If you let me know what you think about this and if there are any enhancements I could make that would be great.

Thanks,
Ashwin Program.cs
Results1.xlsx
dlmilleCommented:
I reviewed your code and could follow it.  I'm not a C# expert, but could follow what you were doing.  It seems pretty straightforward.  Unless you're having performance issues, I have no suggested changes I would recommend you make.

Dave
Ashwin_shastryAuthor Commented:
Thanks for looking at it Dave and thanks for your time as well.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
C#

From novice to tech pro — start learning today.