How do I get table name before table in excel sheet using epplus library.

Question

0.00/5 (No votes)

See more:

I am exporting the datatable to excel using epplus library in c# but i am not able to attach the table name or any other information as text as above the table in excel file.
Actually my code exporting 6 stored procedures to single excel sheet and i want that every table has its unique name as on the top of every table.
Thanks for helping.

What I have tried:

ExcelPackage package = new ExcelPackage();
           var worksheet = package.Workbook.Worksheets.Add("ExcelSheet");
           worksheet.View.ShowGridLines = false;
           int z = 1;
           for (int x = 0; x < ds.Tables.Count; x++)
           {
               var names = worksheet.Tables.Add(ds.Tables.Count, ge);
               int r = z;
               int t = r + 1;
               for (int i = 1; i < ds.Tables[x].Columns.Count + 1; i++)
               {
                   worksheet.Cells[r, i].Value = ds.Tables[x].Columns[i - 1].ToString();
               }
               for (int i = 0; i < ds.Tables[x].Rows.Count; i++)
               {
                   for (int j = 0; j < ds.Tables[x].Columns.Count; j++)
                   {
                       worksheet.Cells[i + t, j + 1].Value = ds.Tables[x].Rows[i]
                       [j].ToString();
                   }
               }
               z += ds.Tables[x].Rows.Count + 4;

This is my epplus code.

Posted 15-Nov-17 18:54pm

Vikrant_saini

Updated 15-Nov-17 19:33pm

v2

Add a Solution

1 solution

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)

Karthik_Mahalingam · Accepted Answer · 2017-11-15T19:33:00

Solution 1

Slight modification to the Solution posted earlier
Declare the Title of the Table in the DataTable as

DataTable dt = new DataTable("Table Name 1");  // declare the Table Name when creating the DataTable
 DataTable dt1 = new DataTable("Table Name 2");// declare the Table Name when creating the DataTable
DataTable dt2 = new DataTable("Table Name 3");// declare the Table Name when creating the DataTable

and update in the Cell as below, refer the in-line comments

using (ExcelPackage package = new ExcelPackage())
            {
                ExcelWorksheet ws = package.Workbook.Worksheets.Add("Your Sheet Name");
                int rowNumber = 1;
                foreach (DataTable dtTemp in ds.Tables)
                {
                    ws.Cells["A" + rowNumber].Value = dtTemp.TableName;  // write it on top of the table 
                    ws.Cells["A" + ( rowNumber +1)].LoadFromDataTable(dtTemp, true);
                    rowNumber += dtTemp.Rows.Count + 2; // to create 2 empty rows
                }

                package.SaveAs(new FileInfo(@"D:\Projects\CPTemp\CPTemp\TextFile.xlsx"));
            }

for more info refer EPPLUS documentation[^]

Posted 15-Nov-17 19:33pm

Karthik_Mahalingam

Updated 15-Nov-17 19:34pm

v2

Comments

Vikrant_saini 16-Nov-17 1:53am

Hi, will u pls modify my code.
as i am only getting TableName Table Name 1 and in every first column of every table not on the top of table.

Karthik_Mahalingam 16-Nov-17 1:55am

you will have to give different names to each datatable, check the code where you are adding the datatable to dataset

Vikrant_saini 16-Nov-17 2:01am

Yes, i did. i give different table name to every datatable.
But only getting first datatable name in first column of every table.

Karthik_Mahalingam 16-Nov-17 2:02am

post your code.

Vikrant_saini 16-Nov-17 2:07am

using System.Data.SqlClient;
using System.IO;
using OfficeOpenXml;

public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{

string constring = "Data Source=DESKTOP-7MRDTE0\\SQLEXPRESS2014;Initial Catalog=;User ID=sa;Password=";
SqlConnection con1 = new SqlConnection();
con1 = null;
try
{
con1 = new SqlConnection(constring);
con1.Open();

SqlCommand cmd = new SqlCommand("[usp_GetCampaignSchedule]", con1);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@CampaignID", SqlDbType.Int).Value = 1102;
SqlDataAdapter da = new SqlDataAdapter(cmd);

SqlCommand cmd1 = new SqlCommand("[usp_GetEmailStatitics]", con1);
cmd1.CommandType = CommandType.StoredProcedure;
cmd1.Parameters.Add("@CampaignID", SqlDbType.Int).Value = 1102;
SqlDataAdapter da1 = new SqlDataAdapter(cmd1);

SqlCommand cmd2 = new SqlCommand("[usp_GetCampaignDetails]", con1);
cmd2.CommandType = CommandType.StoredProcedure;
cmd2.Parameters.Add("@CampaignID", SqlDbType.Int).Value = 1101;
SqlDataAdapter da2 = new SqlDataAdapter(cmd2);

SqlCommand cmd3 = new SqlCommand("[usp_GetForwardReport]", con1);
cmd3.CommandType = CommandType.StoredProcedure;
cmd3.Parameters.Add("@CampaignID", SqlDbType.Int).Value = 1101;
SqlDataAdapter da3 = new SqlDataAdapter(cmd3);

SqlCommand cmd4 = new SqlCommand("[usp_GetClickThrough]", con1);
cmd4.CommandType = CommandType.StoredProcedure;
cmd4.Parameters.Add("@CampaignID", SqlDbType.Int).Value = 1102;
SqlDataAdapter da4 = new SqlDataAdapter(cmd4);

SqlCommand cmd5 = new SqlCommand("[usp_GetBouncedReport]", con1);
cmd5.CommandType = CommandType.StoredProcedure;
cmd5.Parameters.Add("@Campaignid", SqlDbType.Int).Value = 1102;
SqlDataAdapter da5 = new SqlDataAdapter(cmd5);

DataTable dt = new DataTable("Table 1");
DataTable dt1 = new DataTable("Table 2");
DataTable dt2 = new DataTable("Table 3");
DataTable dt3 = new DataTable("Table 4");
DataTable dt4 = new DataTable("Table 5");
DataTable dt5 = new DataTable("Table 6");
DataSet ds = new DataSet();
da.Fill(dt);
da1.Fill(dt1);
da2.Fill(dt2);
da3.Fill(dt3);
da4.Fill(dt4);
da5.Fill(dt5);
ds.Tables.Add(dt);
ds.Tables.Add(dt1);
ds.Tables.Add(dt2);
ds.Tables.Add(dt3);
ds.Tables.Add(dt4);
ds.Tables.Add(dt5);

ExcelPackage package = new ExcelPackage();
var worksheet = package.Workbook.Worksheets.Add("ExcelSheet");
worksheet.View.ShowGridLines = false;
int z = 1;
for (int x = 0; x < ds.Tables.Count; x++)
{
int r = z;
int t = r + 1;

for (int i = 1; i < ds.Tables[x].Columns.Count + 1; i++)
{
worksheet.Cells[r, i].Value = ds.Tables[x].Columns[i - 1].ToString();
}
for (int i = 0; i < ds.Tables[x].Rows.Count; i++)
{
for (int j = 0; j < ds.Tables[x].Columns.Count; j++)
{
worksheet.Cells[i + t, j + 1].Value = ds.Tables[x].Rows[i]
[j].ToString();
}
}
z += ds.Tables[x].Rows.Count + 4;
worksheet.Cells.AutoFitColumns();
}
package.SaveAs(new FileInfo(@"C:\Users\Stored.xlsx"));
}
catch (Exception ex)
{
Console.

Karthik_Mahalingam 16-Nov-17 2:09am

full code pls

Karthik_Mahalingam 16-Nov-17 2:30am

try this


            DataTable dt = new DataTable("Table Name 1");  // declare the Table Name when creating the DataTable
            dt.Columns.Add("ID" );
            dt.Columns.Add(  "Name");
            dt.Rows.Add(1, "aa");
            dt.Rows.Add(2, "bb");

            DataTable dt1 = new DataTable("Table Name 2");// declare the Table Name when creating the DataTable
            dt1.Columns.Add("ID11");
            dt1.Columns.Add("Name11");
            dt1.Rows.Add(11, "aa1111");
            dt1.Rows.Add(22, "bb111");

            DataTable dt2 = new DataTable("Table Name 3");// declare the Table Name when creating the DataTable
            dt2.Columns.Add("ID22");
            dt2.Columns.Add("Name22");
            dt2.Rows.Add(221, "aa222");
            dt2.Rows.Add(233, "bb222");

            DataSet ds = new DataSet();
            ds.Tables.Add(dt);
            ds.Tables.Add(dt1);
            ds.Tables.Add(dt2);





            ExcelPackage package = new ExcelPackage();
            ExcelWorksheet worksheet = package.Workbook.Worksheets.Add("ExcelSheet");
            worksheet.View.ShowGridLines = false;
            int startIndexOfRow = 1;
            foreach (DataTable dtCurrent in ds.Tables)
            {
                // Adding Title 
                worksheet.Cells[startIndexOfRow, 1].Value = dtCurrent.TableName;
                // Adding Headers 
                startIndexOfRow++;
                for (int i = 0; i < dtCurrent.Columns.Count; i++)
                {
                    worksheet.Cells[startIndexOfRow, (i+1)].Value = dtCurrent.Columns[i].ColumnName;
                }
                // Adding rows

                for (int i = 0; i < dtCurrent.Rows.Count; i++)
                {
                    startIndexOfRow++;
                    for (int j = 0; j < dtCurrent.Columns.Count; j++)
                    {
                        worksheet.Cells[startIndexOfRow, j + 1].Value = dtCurrent.Rows[i][j].ToString();
                    }
                }
                startIndexOfRow += 3;  // 3 empty rows
                 
                package.SaveAs(new FileInfo(@"D:\Projects\CPTemp\CPTemp\TextFile.xlsx"));
            }
        }

Vikrant_saini 16-Nov-17 2:45am

It does not giving me right answer.
With this code, only single table is exported many times(=no of tables we are defining).
Thanks

Karthik_Mahalingam 16-Nov-17 2:49am

move this line outside the loop
package.SaveAs(new FileInfo(@"D:\Projects\CPTemp\CPTemp\TextFile.xlsx"));

Vikrant_saini 16-Nov-17 2:59am

No need to do this, its working nice.
I was doing a mistake before, in data adapter i was filling only table 1.
As da.fill(dt).
Thanks.

Karthik_Mahalingam 16-Nov-17 2:51am

using OfficeOpenXml;
using PdfSharp.Pdf;
using PdfSharp.Pdf.IO;
using PdfSharp.Pdf.Security;
using System.Data;
using System.IO;

namespace CP
{
    class POCProgram
    {
        static void Main(string[] args)
        {

            DataTable dt = new DataTable("Table Name 1");  // declare the Table Name when creating the DataTable
            dt.Columns.Add("ID");
            dt.Columns.Add("Name");
            dt.Rows.Add(1, "aa");
            dt.Rows.Add(2, "bb");

            DataTable dt1 = new DataTable("Table Name 2");// declare the Table Name when creating the DataTable
            dt1.Columns.Add("ID11");
            dt1.Columns.Add("Name11");
            dt1.Rows.Add(11, "aa1111");
            dt1.Rows.Add(22, "bb111");

            DataTable dt2 = new DataTable("Table Name 3");// declare the Table Name when creating the DataTable
            dt2.Columns.Add("ID22");
            dt2.Columns.Add("Name22");
            dt2.Rows.Add(221, "aa222");
            dt2.Rows.Add(233, "bb222");

            DataSet ds = new DataSet();
            ds.Tables.Add(dt);
            ds.Tables.Add(dt1);
            ds.Tables.Add(dt2);





            ExcelPackage package = new ExcelPackage();
            ExcelWorksheet worksheet = package.Workbook.Worksheets.Add("ExcelSheet");
            worksheet.View.ShowGridLines = false;
            int startIndexOfRow = 1;
            foreach (DataTable dtCurrent in ds.Tables)
            {
                // Adding Title 
                worksheet.Cells[startIndexOfRow, 1].Value = dtCurrent.TableName;
                // Adding Headers 
                startIndexOfRow++;
                for (int i = 0; i < dtCurrent.Columns.Count; i++)
                {
                    worksheet.Cells[startIndexOfRow, (i + 1)].Value = dtCurrent.Columns[i].ColumnName;
                }
                // Adding rows

                for (int i = 0; i < dtCurrent.Rows.Count; i++)
                {
                    startIndexOfRow++;
                    for (int j = 0; j < dtCurrent.Columns.Count; j++)
                    {
                        worksheet.Cells[startIndexOfRow, j + 1].Value = dtCurrent.Rows[i][j].ToString();
                    }
                }
                startIndexOfRow += 3;  // 3 empty rows

               
            }
            package.SaveAs(new FileInfo(@"D:\Projects\CPTemp\CPTemp\TextFile.xlsx"));
        }
    }
}

Vikrant_saini 16-Nov-17 2:56am

Thanks, its working fine.

Karthik_Mahalingam 16-Nov-17 3:06am

cool