Export two datatable into two sheets of same excel file

Question

1.00/5 (1 vote)

See more:

Hi,

I have to export two datatable values into two seperate sheets of same excel file.
How can i create multiple sheets and export to the same?

Thank you.

Posted 25-Aug-12 0:56am

Zukiari

Updated 24-Aug-17 3:20am

Add a Solution

Comments

AmitGajjar 25-Aug-12 7:17am

do you want to create multiple Excel sheet to export data ? but you just said that you have added two sheets. so what is your question ?

Zukiari 27-Aug-12 3:34am

When I'm clicking the button, I have to export data from two tables into two sheets of an excel file. I've to create multiple sheets and then export the data.

Thank you.

4 solutions

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)

Sreegth V · Answer 1 · 2012-08-25T02:18:00

Try creating multiple Worksheet objects of a Work book.

Dim xlsapp As New Microsoft.Office.Interop.Excel.Application()
Dim workbook As Microsoft.Office.Interop.Excel.Workbook = xlsapp.Workbooks.Open(dlgSaveFile.FileName, 0, False, 5, "", "", _
True, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, vbTab, False, False, 0, _
True, 1, 0)
Dim sheet1 As Microsoft.Office.Interop.Excel.Worksheet = workbook.Worksheets(1)
Dim sheet2 As Microsoft.Office.Interop.Excel.Worksheet = workbook.Worksheets(1)

Christian Graus · Answer 2 · 2012-08-25T01:41:00

Solution 1

How are you creating one sheet, now ? The microsoft tools for office let you work with excel sheets in code, there's also commercial libraries you can buy. They will support creating multiple sheets, which you can't do if you're creating excel files by exporting to csv.

It would help us if you posted code to show us how you create them now, I've had to guess.

Posted 25-Aug-12 1:41am

Christian Graus

Comments

Zukiari 27-Aug-12 3:24am

Hi,

As I've to adjust the column width, I'm using the following code which for one datatable.

private void ExporttoExcel(DataTable table)
{
HttpContext.Current.Response.Clear();
HttpContext.Current.Response.ClearContent();
HttpContext.Current.Response.ClearHeaders();
HttpContext.Current.Response.Buffer = true;
HttpContext.Current.Response.ContentType = "application/ms-excel";
HttpContext.Current.Response.Write(@"<!DOCTYPE HTML PUBLIC ""-//W3C//DTD HTML 4.0 Transitional//EN"">");
HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment;filename=Reports.xls");

HttpContext.Current.Response.Charset = "utf-8";
HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.GetEncoding("windows-1250");
//sets font
HttpContext.Current.Response.Write("");
HttpContext.Current.Response.Write("<BR><BR><BR>");
//sets the table border, cell spacing, border color, font of the text, background, foreground, font height
HttpContext.Current.Response.Write("<Table border='1' bgColor='#ffffff' " +
"borderColor='#000000' cellSpacing='0' cellPadding='0' " +
"style='font-size:10.0pt; font-family:Calibri; background:white;'>");
foreach (DataRow row in table.Rows)
{//write in new row
HttpContext.Current.Response.Write("<TR>");
HttpContext.Current.Response.Write("<Td>");
HttpContext.Current.Response.Write(row[0].ToString());
HttpContext.Current.Response.Write("</Td>");
HttpContext.Current.Response.Write("<Td style='width: 25px;'>");
HttpContext.Current.Response.Write(row[1].ToString());
HttpContext.Current.Response.Write("</Td>");
HttpContext.Current.Response.Write("<Td>");
HttpContext.Current.Response.Write(row[2].ToString());
HttpContext.Current.Response.Write("</Td>");
HttpContext.Current.Response.Write("<Td style='width: 25px;'>");
HttpContext.Current.Response.Write(row[3].ToString());
HttpContext.Current.Response.Write("</Td>");
HttpContext.Current.Response.Write("<Td>");
HttpContext.Current.Response.Write(row[4].ToString());
HttpContext.Current.Response.Write("</Td>");
HttpContext.Current.Response.Write("<Td>");
HttpContext.Current.Response.Write(row[5].ToString());
HttpContext.Current.Response.Write("</Td>");

HttpContext.Current.Response.Write("</TR>");
}
HttpContext.Current.Response.Write("</Table>");
HttpContext.Current.Response.Write("");
HttpContext.Current.Response.Flush();
HttpContext.Current.Response.End();
}

Just like that I've to export anothe data.
Thank you.

Sreegth V · Answer 3 · 2012-08-26T23:01:00

C#

Microsoft.Office.Interop.Excel.Application xlsapp = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbook workbook = xlsapp.Workbooks.Open(dlgSaveFile.FileName, 0, false, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, Constants.vbTab, false,
false, 0, true, 1, 0);
Microsoft.Office.Interop.Excel.Worksheet sheet1 = workbook.Worksheets(1);
Microsoft.Office.Interop.Excel.Worksheet sheet2 = workbook.Worksheets(1);

To name your excel sheet,use the name property of your Work sheet object.
eg:

C#

sheet1.Name="Example1";
sheet2.Name="Example2";

Happy coding..

Sreegth V · Answer 4 · 2012-08-27T21:43:00

Use the sheet objects to selects the range in desired sheets.
eg,

C#

 Microsoft.Office.Interop.Excel.Application xlsapp = null;
            Workbook xlsbook = null;
            Worksheet xlsdoc1 = null;
            Worksheet xlsdoc2 = null;
            xlsapp =new Microsoft.Office.Interop.Excel.Application();
            xlsbook = xlsapp.Workbooks.Add(Missing.Value);
            xlsdoc2 = xlsbook.Worksheets.Add(Missing.Value);
            xlsdoc1 = xlsbook.Worksheets.Add(Missing.Value);
            xlsdoc1.Name = "Job Summary";
            xlsdoc2.Name = "Job Details";
            Microsoft.Office.Interop.Excel.Range range;
            try
            {
                range = xlsdoc1.Range["A2", "K2"];
                range.Merge();
                range.Value2 = "Job Summary";
                range.Font.Name = "Bookman Old Style";
                range.Font.Size = 14;
                range.Font.ColorIndex = 5;
                range.Font.Bold = true;
                range.HorizontalAlignment = XlHAlign.xlHAlignCenter;
                range.BorderAround(XlLineStyle.xlContinuous, XlBorderWeight.xlThin, XlColorIndex.xlColorIndexNone, XlColorIndex.xlColorIndexNone);
//First Sheet
                {
                    range = xlsdoc1.Range["A4", "A4"];
                    range.Value2 = "Job Id:";
                    range = xlsdoc1.Range["B4", "B4"];
                    range.Value2 = "100005";
                    range.Font.Name = "Calibri";
                    range.Font.Bold = true;
                }
//Second Sheet
                {
                    range = xlsdoc2.Range["A4", "A4"];
                    range.Value2 = "Job Name:";
                    range = xlsdoc2.Range["B4", "B4"];
                    range.Value2 = "DFFFFDF";
                    range.Font.Name = "Calibri";
                    range.Font.Bold = true;
                }

Export two datatable into two sheets of same excel file

4 solutions

Solution 2

Solution 1

Solution 3

Solution 4

Add your solution here

Preview 0