Click here to Skip to main content
15,567,415 members
Please Sign up or sign in to vote.
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
Updated 15-Nov-17 20:33pm
v2

1 solution

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[^]
 
Share this answer
 
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.

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900