C# .NET - how to store 3 tables in one excel sheet using c#.net

Asked By Bhanuprakash T.R on 03-Sep-11 05:26 AM
hello sir i try following code 3 tables are stored as diffenrent excel sheet .. i want coding for 3 tables export in single excel sheet .. inside the excel create 3 worksheets.. in the 3 worksheet stores 3 tables i try this following code
//button click
{
filename = textBox1.Text.ToString();
            SqlCeConnection con = new SqlCeConnection("DataSource=" + filename + "");
            SqlCeDataAdapter da = new SqlCeDataAdapter("select * from stockTake", con);
            SqlCeDataAdapter da1 = new SqlCeDataAdapter("select * from purchaseOrder", con);
            SqlCeDataAdapter da2 = new SqlCeDataAdapter("select * from newstock", con);
            DataTable dt = new DataTable();
            
            DataTable dt1 = new DataTable();
            DataTable dt2 = new DataTable();
            da.Fill(dt);
            da1.Fill(dt1);
           da2.Fill(dt2);
            Excel_FromDataTable(dt);
            purchaseOrderexcel(dt1);
            newstockTakeexcel(dt2);
}

 private static void Excel_FromDataTable(DataTable dt)
        {
            // Create an Excel object and add workbook...
            Microsoft.Office.Interop.Excel.ApplicationClass excel = new Microsoft.Office.Interop.Excel.ApplicationClass();
            Microsoft.Office.Interop.Excel.Workbook workbook = excel.Application.Workbooks.Add(true); // true for object template???

            // Add column headings...
            int iCol = 0;
            foreach (DataColumn c in dt.Columns)
            {
                iCol++;
                excel.Cells[1, iCol] = c.ColumnName;
            }
            // for each row of data...
            int iRow = 0;
            foreach (DataRow r in dt.Rows)
            {
                iRow++;

                // add each row's cell data...
                iCol = 0;
                foreach (DataColumn c in dt.Columns)
                {
                    iCol++;
                    excel.Cells[iRow + 1, iCol] = r[c.ColumnName];
                }
            }

            // Global missing reference for objects we are not defining...
            object missing = System.Reflection.Missing.Value;

            // If wanting to Save the workbook...
            workbook.SaveAs("stokTake.xls", Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
            workbook.SaveAs("stokTake.xls", Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
            excel.Visible = true;
            Microsoft.Office.Interop.Excel.Worksheet worksheet2 = (Microsoft.Office.Interop.Excel.Worksheet)excel.ActiveSheet;
            ((Microsoft.Office.Interop.Excel._Worksheet)worksheet2).Activate();
            // If wanting to make Excel visible and activate the worksheet...
            excel.Quit();
        }
i write two tables as same above function

output
3 tables are stored in 3 differnt excel sheet i want to store 3 tables into one excel sheet


Thanks 
Bhanu prakash
hello sir i try following code 3 tables are stored as diffenrent excel sheet .. i want coding for 3 tables export in single excel sheet .. inside the excel create 3 worksheets.. in the 3 worksheet stores 3 tables i try this following code
//button click
{
filename = textBox1.Text.ToString();
            SqlCeConnection con = new SqlCeConnection("DataSource=" + filename + "");
            SqlCeDataAdapter da = new SqlCeDataAdapter("select * from stockTake", con);
            SqlCeDataAdapter da1 = new SqlCeDataAdapter("select * from purchaseOrder", con);
            SqlCeDataAdapter da2 = new SqlCeDataAdapter("select * from newstock", con);
            DataTable dt = new DataTable();
            
            DataTable dt1 = new DataTable();
            DataTable dt2 = new DataTable();
            da.Fill(dt);
            da1.Fill(dt1);
           da2.Fill(dt2);
            Excel_FromDataTable(dt);
            purchaseOrderexcel(dt1);
            newstockTakeexcel(dt2);
}

 private static void Excel_FromDataTable(DataTable dt)
        {
            // Create an Excel object and add workbook...
            Microsoft.Office.Interop.Excel.ApplicationClass excel = new Microsoft.Office.Interop.Excel.ApplicationClass();
            Microsoft.Office.Interop.Excel.Workbook workbook = excel.Application.Workbooks.Add(true); // true for object template???

            // Add column headings...
            int iCol = 0;
            foreach (DataColumn c in dt.Columns)
            {
                iCol++;
                excel.Cells[1, iCol] = c.ColumnName;
            }
            // for each row of data...
            int iRow = 0;
            foreach (DataRow r in dt.Rows)
            {
                iRow++;

                // add each row's cell data...
                iCol = 0;
                foreach (DataColumn c in dt.Columns)
                {
                    iCol++;
                    excel.Cells[iRow + 1, iCol] = r[c.ColumnName];
                }
            }

            // Global missing reference for objects we are not defining...
            object missing = System.Reflection.Missing.Value;

            // If wanting to Save the workbook...
            workbook.SaveAs("stokTake.xls", Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
            workbook.SaveAs("stokTake.xls", Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
            excel.Visible = true;
            Microsoft.Office.Interop.Excel.Worksheet worksheet2 = (Microsoft.Office.Interop.Excel.Worksheet)excel.ActiveSheet;
            ((Microsoft.Office.Interop.Excel._Worksheet)worksheet2).Activate();
            // If wanting to make Excel visible and activate the worksheet...
            excel.Quit();
        }
i write two tables as same above function

output
3 tables are stored in 3 differnt excel sheet i want to store 3 tables into one excel sheet


Thanks 
Bhanu prakash
Reena Jain replied to Bhanuprakash T.R on 03-Sep-11 05:39 AM
Hi,

Instead of single Excel Sheet,you can write a Table values in each sheet,

http://csharp.net-informations.com/excel/csharp-excel-export.htm

increment the worksheet count you able to save multiple dataset values in the single excel file.


Hope this will help you
dipa ahuja replied to Bhanuprakash T.R on 03-Sep-11 08:07 AM
Try this :

void ExportToExcel(string sheet)
{
  DataTable dt1 = new DataTable();
  DataTable dt2 = new DataTable();
  DataTable dt3 = new DataTable();
 
 
  SqlDataAdapter da = new SqlDataAdapter();
  
  //code of dataAdapter
 
  da.Fill(dt1); //fill datatable 1 with first table
 
  dt1.Merge(dt2); // merge dt2 with second table
 
  dt1.Merge(dt3); //merge dt3 with third table
 
 
  // creating Excel Application
  Excel._Application app = new Microsoft.Office.Interop.Excel.Application();
  // creating new WorkBook within Excel application
  Excel._Workbook workbook = app.Workbooks.Add(Type.Missing);
  // creating new Excelsheet in workbook
  Excel._Worksheet worksheet = null;
  // see the excel sheet behind the program
  app.Visible = false;
  // get the reference of first sheet. By default its name is Sheet1.
  // store its reference to worksheet
  worksheet = workbook.Sheets["Sheet1"];
  worksheet = workbook.ActiveSheet;
  // changing the name of active sheet
  worksheet.Name = "Exported from gridview";
 
  // storing header part in Excel
  for (int i = 1; i < dt1.Columns.Count + 1; i++)
  {
    worksheet.Cells[1, i] = dt1.Columns[i - 1].Caption;
  }
  // storing Each row and column value to excel sheet
  for (int i = 0; i < dt1.Rows.Count - 1; i++)
  {
    for (int j = 0; j < dt1.Columns.Count; j++)
    {
      worksheet.Cells[i + 2, j + 1] = dt1.Rows[i][j].ToString();
    }
  }
  // save the application
  workbook.SaveAs("d:\\output.xls"Type.Missing, Type.Missing, Type.Missing,
   Type.Missing,
   Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive,
   Type.Missing, Type.Missing, Type.Missing, Type.Missing);
  // Exit from the application
  app.Quit();
  MessageBox.Show("Excel file created , you can find the file d:\\output.xls");
 
}
Jitendra Faye replied to Bhanuprakash T.R on 03-Sep-11 11:12 AM
Use this working code-

public static void ExportDataSetToExcel(DataSet ds, string filename)
   {
      HttpResponse response = HttpContext.Current.Response;
 
      // first let's clean up the response.object
      response.Clear();
      response.Charset = "";
 
      // set the response mime type for excel
      response.ContentType = "application/vnd.ms-excel";
      response.AddHeader("Content-Disposition", "attachment;filename=\"" + filename + "\"");
 
      // create a string writer
      using (StringWriter sw = new StringWriter())
      {
     using (HtmlTextWriter htw = new HtmlTextWriter(sw))
     {
        // instantiate a datagrid
        DataGrid dg = new DataGrid();
        dg.DataSource = ds.Tables[0];
        dg.DataBind();
        dg.RenderControl(htw);
        response.Write(sw.ToString());
        response.End();
     }
      }
    }


Pass Dataset to this function.


TRy this code and let me know.
Jitendra Faye replied to Bhanuprakash T.R on 03-Sep-11 11:15 AM
Use this working code-

public static void ExportDataSetToExcel(DataSet ds, string filename)
   {
      HttpResponse response = HttpContext.Current.Response;
 
      // first let's clean up the response.object
      response.Clear();
      response.Charset = "";
 
      // set the response mime type for excel
      response.ContentType = "application/vnd.ms-excel";
      response.AddHeader("Content-Disposition", "attachment;filename=\"" + filename + "\"");
 
      // create a string writer
      using (StringWriter sw = new StringWriter())
      {
     using (HtmlTextWriter htw = new HtmlTextWriter(sw))
     {
        // instantiate a datagrid
        DataGrid dg = new DataGrid();
        dg.DataSource = ds.Tables[0];
        dg.DataBind();
        dg.RenderControl(htw);
        response.Write(sw.ToString());
        response.End();
     }
      }
    }


Pass Dataset to this function.


TRy this code and let me know.
Radhika roy replied to Bhanuprakash T.R on 03-Sep-11 11:17 AM

Below is the complete function which will write an Excel file to your local disk. You have to pass it the DataSet to export and path to where file should be generated. 

public static void CreateWorkbook(DataSet ds, String path)
{
   XmlDataDocument xmlDataDoc = new XmlDataDocument(ds);
   XslTransform xt = new XslTransform();
   StreamReader reader =new StreamReader(typeof (WorkbookEngine).Assembly.GetManifestResourceStream(typeof (WorkbookEngine), “Excel.xsl”));
   XmlTextReader xRdr = new XmlTextReader(reader);
   xt.Load(xRdr, null, null);

   StringWriter sw = new StringWriter();
   xt.Transform(xmlDataDoc, null, sw, null);

   StreamWriter myWriter = new StreamWriter (path + “\\Report.xls“);
   myWriter.Write (sw.ToString());
   myWriter.Close ();
}

and thats it. Your DataSet has been exported to an Excel file which is saved at the path passed to this function.

For original article, please follow this-

http://itsrashid.wordpress.com/2007/05/14/export-dataset-to-excel-in-c/
Devil Scorpio replied to Bhanuprakash T.R on 03-Sep-11 11:24 AM
Hi Bhanuprakash,

Here is step by step procedure to store multiple tables in one excel sheet

1. Create the following class:

public class WorkbookEngine
{
public static void CreateWorkbook(DataSet ds, String path)
{
XmlDataDocument xmlDataDoc = new XmlDataDocument(ds);
XslTransform xt = new XslTransform();
StreamReader reader =new StreamReader(typeof (WorkbookEngine).Assembly.GetManifestResourceStream(typeof (WorkbookEngine), “Excel.xsl”));
XmlTextReader xRdr = new XmlTextReader(reader);
xt.Load(xRdr, null, null);
StringWriter sw = new StringWriter();
xt.Transform(xmlDataDoc, null, sw, null);
StreamWriter myWriter = new StreamWriter (path + “\\Report.xls”);
myWriter.Write (sw.ToString());
myWriter.Close ();
}
}


2. In your main program/class use the function as

WorkbookEngine.CreateWorkbook(ds, path);
‘ds’ will be your dataset to export and path to where file should be generated.


I hope it will help you.