Click here to Skip to main content
14,880,436 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I am exporting data from a sql stored procedure to excel on seperate sheets in one workbook . the code works however my sheet names are called 'sheet1' , 'sheet2' etc. on the excel file tab

i would like to have my own sheet names on the excel file tab e.g. sheet1 be name 'BenCount', sheet2 'Wages'so that they make sense etc. my code:

What I have tried:

      SqlConnection connex = new SqlConnection();

      SqlCommand command = new SqlCommand("spTest", con) { CommandType = System.Data.CommandType.StoredProcedure };

      SqlDataAdapter sda = new SqlDataAdapter();

      command.Connection = con;
      sda.SelectCommand = command;
      command.CommandTimeout = 600;

      DataSet ds = new DataSet();

      sda = new SqlDataAdapter("spTest", con);
      sda.Fill(ds);

//Set Name of DataTables.
      ds.Tables[0].TableName = "Bencount";
      ds.Tables[1].TableName = "Wages";
      ds.Tables[3].TableName = "BeneficiariestobeExctracted";
      ds.Tables[4].TableName = "benificiarieswithnoAccNo";
      ds.Tables[5].TableName = "account number>11 characters/wages<100";
      ds.Tables[6].TableName = "beneficiary appear on more than project";
      ds.Tables[7].TableName = "Duplications by account number";
      ds.Tables[8].TableName = "Check Missing Beneficiaries";





          if (ds.Tables.Count > 0)
          {
              MemoryStream ms = new MemoryStream();
              int i = 1;
              using (ExcelPackage package = new ExcelPackage(ms))
              {
                  foreach (DataTable table in ds.Tables)
                  {
                      ExcelWorksheet worksheet = package.Workbook.Worksheets.Add(table.TableName);



                      worksheet.Cells["A2"].LoadFromDataTable(table, true);
                  }
                  Response.Clear();
                  package.SaveAs(Response.OutputStream);
                  Response.AddHeader("content-disposition", "attachchment; filename=Example.xls");

                  Response.Charset = "";
                  Response.ContentType = "application/vnd.xls";
                  Response.End();
              }
          }
  }
}
Posted
Updated 3-Apr-19 6:09am
v2

   
Comments
Member 14183767 15-Mar-19 11:18am
   
hi , thanks for ur response however i am using EFP PLUS and i have 9 sheets that i need to be renamed to a list of names that i chose, please help
Hi i used an if statement to rename the each sheet
C#
if (worksheet.Name == "sheet1")
                    { worksheet.Name = "Bencount"; }.

thanks for the advice :)
   

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