[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Inserting Data from Data Table / Data Set into Excel worksheet

Posted on 2011-10-05
12
Medium Priority
?
450 Views
Last Modified: 2012-05-12
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
0
Comment
Question by:Ashwin_shastry
  • 7
  • 5
12 Comments
 
LVL 42

Expert Comment

by:dlmille
ID: 36922520
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
0
 

Author Comment

by:Ashwin_shastry
ID: 36924331
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
0
 
LVL 42

Expert Comment

by:dlmille
ID: 36925394
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
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:Ashwin_shastry
ID: 36925965
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
0
 
LVL 42

Accepted Solution

by:
dlmille earned 2000 total points
ID: 36926337
>>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
0
 

Author Comment

by:Ashwin_shastry
ID: 36927156
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.

0
 

Author Comment

by:Ashwin_shastry
ID: 36927161
BTW its a pretty simple 1 file console app.
0
 
LVL 42

Expert Comment

by:dlmille
ID: 36927496
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
0
 

Author Comment

by:Ashwin_shastry
ID: 36931120
Thanks Dave, I will post the project shortly ...
0
 

Author Comment

by:Ashwin_shastry
ID: 36943089
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
0
 
LVL 42

Expert Comment

by:dlmille
ID: 36943717
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
0
 

Author Comment

by:Ashwin_shastry
ID: 36944190
Thanks for looking at it Dave and thanks for your time as well.
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
Hello there! As a developer I have modified and refactored the unit tests which was written by fellow developers in the past. On the course, I have gone through various misconceptions and technical challenges when it comes to implementation. I would…
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question