Merge mutliple excels into a single excel having one sheet

Question

1.00/5 (1 vote)

See more:

I have an application where i'm keeping multiple excels in a folder. Each excel will have only one sheet. I need to merge these together to single one.

For eg,

Excel 1 having sheet 1 with Header
Header 1 Header 2 Header 3

And data
1 2 3
11 22 33

Excel 2 woth sheet 1 having content
Header 1 Header 4 Header 5
33 44 55
66 77 88

I need a new excel with one sheet and data as follows

Header 1 header 2 Header 3 Header 4 Header 5
1 2 3
11 22 33
33 44 55
66 77 88

I tried one code from the below link

http://bembengarifin.wordpress.com/2010/11/02/combine-multiple-excel-files-into-one-with-c/[^]

It is giving single file with two sheets having data of excel1 in sheet1 and excel 2 in sheet 2

Posted 9-Sep-13 19:36pm

Member 10112611

Updated 9-Sep-13 19:47pm

Dholakiya Ankit

v2

Add a Solution

Comments

Sergey Alexandrovich Kryukov 10-Sep-13 1:43am

No, this is not urgent at all, I can guarantee that. :-)
Will you explain to us, what does it mean, "multiple excels"?
—SA

Member 10112611 10-Sep-13 1:44am

more than one excel file into one excel file

Dholakiya Ankit 10-Sep-13 1:47am

wrong multiple excells in to 1?

Member 10112611 10-Sep-13 1:49am

Please read my question..I emntioned my requirement in one example. with excel sheet1 and excel sheet 2
i need to get excel3 with the data in 1 and 2 sheet combined

Dholakiya Ankit 10-Sep-13 1:53am

so what's the issue you can select both excel in to diff datatable's then combine them into one datatable then write to excel or you can directly write using both datatables the first you have to do is do something..........

Member 10112611 10-Sep-13 1:57am

any sample code u have? please post for me

Member 10112611 10-Sep-13 1:54am

ok.thanks..i'll try

Dholakiya Ankit 10-Sep-13 2:02am

check my ans

Member 10070308 10-Sep-13 5:03am

i read data from excel to datatable , merged and created new databale..can u help me how to write that back to excel? I searched ..but all are writing to excel page using http respone. I want excel to be created in a given path.

Dholakiya Ankit 10-Sep-13 5:31am

check updated solution

Member 10112611 10-Sep-13 5:15am

can u help me on writing to excel page from datatable. Excel should not be open or downloaded. It should be saved in predefined path

Dholakiya Ankit 10-Sep-13 5:29am

yes u try i will check it

Member 10112611 10-Sep-13 2:07am

thanks ..let me try

3 solutions

Solution 2

I tried the below code to write from datatable to excel

string filename = "Reconciliation_Result.xls";
System.IO.StringWriter tw = new System.IO.StringWriter();
System.Web.UI.HtmlTextWriter hw = new System.Web.UI.HtmlTextWriter(tw);
DataGrid dgGrid = new DataGrid();
dgGrid.DataSource = dt;
dgGrid.DataBind();

//Get the HTML for the control.
dgGrid.RenderControl(hw);
//Write the HTML back to the browser.

Response.ContentType = "application/ms-excel";
Response.AppendHeader("Content-Disposition", "attachment; filename=" + filename + "");
Response.Charset = "utf-8";
Response.ContentEncoding = System.Text.Encoding.GetEncoding("windows-1250");
Response.Write("<Table border='1' bgColor='#ffffff' " +      "borderColor='#000000' cellSpacing='0' cellPadding='0' " +      "style='font-size:10.0pt; font-family:Calibri; background:white;'> <TR>");
this.EnableViewState = true;
Response.Write(tw.ToString());
Response.End();

it is working ..but the second column of the excel i need to format the second column as text ..can anyone help?

Posted 10-Sep-13 21:23pm

Member 10112611

Solution 3

Avoid default formatting when exporting data from datagrid to excel.[^]

Posted 16-Sep-13 22:53pm

Member 10112611

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Dholakiya Ankit · Accepted Answer · 2013-09-09T20:04:00

Read Excel Sheet Data into DataTable[^]

http://social.msdn.microsoft.com/Forums/vstudio/en-US/45141eb3-9010-455c-a095-e4f4f709ca08/function-to-import-excel-data-to-dataset-in-[^]

from this way you can select two diff excels into one and then write to excel or merge in to one dt then fill the excel
regards....:)

updated

https://www.google.co.in/search?sclient=psy-ab&q=wirte+dt+to+excel+c%23&btnG=[^]

Merge mutliple excels into a single excel having one sheet

3 solutions

Solution 1

Solution 2

Solution 3

Add your solution here

Preview 0

Existing Members

...or Join us