C# .NET - how to store 3 tables export into single excelsheet

Asked By Bhanuprakash T.R on 03-Sep-11 05:10 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
Reena Jain replied to Bhanuprakash T.R on 03-Sep-11 05:45 AM
Hi,

Now import the namespaces for the Excel library and InteropServices into your code.

using Microsoft.Office.Interop
using System.Runtime.InteropServices.Marshal
public void ExportToExcel(DataSet DS_MyDataset) {
    // The full path where the excel file will be stored
    string strFileName = AppDomain.CurrentDomain.BaseDirectory.Replace("/", "\\");
    strFileName = (strFileName + ("\\MyExcelFile" + System.DateTime.Now.Ticks.ToString()));
    ".xls";
    Excel.Application objExcel;
    Excel.Workbooks objBooks;
    Excel.Workbook objBook;
    Excel.Sheets objSheets;
    Excel.Worksheet objSheet;
    Excel.Range objRange;
    try {
      // Creating a new object of the Excel application object
      objExcel = new Excel.Application();
      // Hiding the Excel application
      objExcel.Visible = false;
      objExcel.DisplayAlerts = false;
      objBook = ((Excel.Workbook)(objExcel.Workbooks.Add()));
      objBook.SaveAs(strFileName, Excel.XlFileFormat.xlWorkbookNormal);
      // Getting the collection of workbooks in an object
      objBooks = objExcel.Workbooks;
      // Get the reference to the first sheet
      // in the workbook collection in a variable
      objSheet = ((Excel.Worksheet)(objBooks[1].objSheets.Item(1)));
      objSheet.Name = "First Sheet";
      objSheet.Range("A1", "Z1").Font.Bold = true;
      objRange = objSheet.Cells;
      // Calling the function to write the dataset data in the cells of the first sheet.
      WriteData(DS_MyDataset.Tables[0], objCells);
      // Setting the width of the specified range of cells
      // so as to absolutely fit the written data.
      objSheet.Range("A1", "Z1").EntireColumn.AutoFit();
      // Saving the worksheet.
      objSheet.SaveAs(strFileName);
      objBook = objBooks.Item[1];
      objSheets = objBook.Worksheets;
      objSheet = ((Excel.Worksheet)(objSheets.Item[2]));
      objSheet.Name = "Second Sheet";
      objSheet.Range("A1", "Z1").Font.ColorIndex = 3;
      objRange = objSheet.Cells;
      WriteData(DS_MyDataset.Tables[1], objCells);
      objSheet.Range("A1", "Z1").EntireColumn.AutoFit();
      objSheet.SaveAs(strFileName);
    }
    catch (Exception ex) {
      Response.Write(ex.Message);
    }
    finally {
      // Close the Excel application
      objExcel.Quit();
      // Release all the COM objects so as to free the memory
      ReleaseComObject(objRange);
      ReleaseComObject(objSheet);
      ReleaseComObject(objSheets);
      ReleaseComObject(objBook);
      ReleaseComObject(objBooks);
      ReleaseComObject(objExcel);
      // Set the all the objects for the Garbage collector to collect them.
      objExcel = null;
      objBooks = null;
      objBook = null;
      objSheets = null;
      objSheet = null;
      objRange = null;
      System.GC.Collect();
    }
  }
   
  private string WriteData(DataTable DT_DataTable, Excel.Range objCells) {
    int iRow;
    int iCol;
    // Traverse through the DataTable columns to write the
    // headers on the first row of the excel sheet.
    for (iCol = 0; (iCol
          <= (DT_DataTable.Columns.Count - 1)); iCol++) {
      objCells(1, (iCol + 1)) = DT_DataTable.Columns[iCol].ToString;
    }
    // Traverse through the rows and columns
    // of the datatable to write the data in the sheet.
    for (iRow = 0; (iRow
          <= (DT_DataTable.Rows.Count - 1)); iRow++) {
      for (iCol = 0; (iCol
            <= (DT_DataTable.Columns.Count - 1)); iCol++) {
        objCells((iRow + 2), (iCol + 1)) = DT_DataTable.Rows[iRow][iCol];
      }
    }
  }

http://www.codeproject.com/KB/aspnet/Export2Excel.aspx
dipa ahuja replied to Bhanuprakash T.R on 03-Sep-11 08:22 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 01:07 PM

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/
Radhika roy replied to Bhanuprakash T.R on 03-Sep-11 01:56 PM
First add data to the dataset and  Try this code -

private void createDataInExcel(DataSet ds)

{

    Application oXL;

    _Workbook oWB;

    _Worksheet oSheet;

    Range oRng;

    string strCurrentDir = Server.MapPath(".") + "\\reports\\";

    try

    {

       oXL = new Application();

       oXL.Visible = false;

       //Get a new workbook.

       oWB = (_Workbook)(oXL.Workbooks.Add( Missing.Value ));

       oSheet = (_Worksheet)oWB.ActiveSheet;

       //System.Data.DataTable dtGridData=ds.Tables[0];

       int iRow =2; 

       if(ds.Tables[0].Rows.Count>0)

       {

         //   for(int j=0;j<ds.Tables[0].Columns.Count;j++)

         //   {

         //    oSheet.Cells[1,j+1]=ds.Tables[0].Columns[j].ColumnName;

         // 

         for(int j=0;j<ds.Tables[0].Columns.Count;j++)

         {

             oSheet.Cells[1,j+1]=ds.Tables[0].Columns[j].ColumnName;

         }

         // For each row, print the values of each column.

         for(int rowNo=0;rowNo<ds.Tables[0].Rows.Count;rowNo++)

         {

           for(int colNo=0;colNo<ds.Tables[0].Columns.Count;colNo++)

           {

             oSheet.Cells[iRow,colNo+1]=ds.Tables[0].Rows[rowNo][colNo].ToString();

           }

         }

         iRow++;

        }

        oRng = oSheet.get_Range("A1", "IV1");

        oRng.EntireColumn.AutoFit();

        oXL.Visible = false;

        oXL.UserControl = false;

        string strFile ="report"+ DateTime.Now.Ticks.ToString() +".xls";//+

        oWB.SaveAs( strCurrentDir + 
     strFile,XlFileFormat.xlWorkbookNormal,null,null,false,false,XlSaveAsAccessMode.xlShared,false,false,null,null);

       // Need all following code to clean up and remove all references!!!

       oWB.Close(null,null,null);

       oXL.Workbooks.Close();

       oXL.Quit();

       Marshal.ReleaseComObject (oRng);

       Marshal.ReleaseComObject (oXL);

       Marshal.ReleaseComObject (oSheet);

       Marshal.ReleaseComObject (oWB);

       string  strMachineName = Request.ServerVariables["SERVER_NAME"];

       Response.Redirect("http://" + strMachineName +"/"+"ViewNorthWindSample/reports/"+strFile);

    }

    catch( Exception theException ) 

    {

        Response.Write(theException.Message);

      }

}

Hope this will help you.