Pasing parameters to Spreadsheet

Azhavee grajeda · September 20th, 2006, 10:49 AM

I have a gridview and I want to be able to pass the values from the gridview to an existing excel document. I want to be able to use the Excel object model. So far I have managed to open my existing document but I havent figure out how to pass the values. ANy suggestions? Thank you

Code:

protected void BtnAnalysis_Click(object sender, EventArgs e)

Code:

    {
        Microsoft.Office.Interop.Excel.Application m_objExcel;
        Microsoft.Office.Interop.Excel._Workbook m_objBook;
        Microsoft.Office.Interop.Excel.Workbooks m_objBooks;
        Microsoft.Office.Interop.Excel.Sheets m_objSheets;
        Microsoft.Office.Interop.Excel._Worksheet m_objSheet;
        Microsoft.Office.Interop.Excel.Worksheets ExcelWorkSheets;
        Microsoft.Office.Interop.Excel.Font m_objFont;

        m_objOpt = System.Reflection.Missing.Value;
        m_objExcel = new Microsoft.Office.Interop.Excel.Application();
        m_objBooks = (Microsoft.Office.Interop.Excel.Workbooks)m_objExcel.Workbooks;
        m_objBook = (Microsoft.Office.Interop.Excel.Workbook)(m_objBooks.Add(m_objOpt));
        m_objExcel.Visible = true;



        string workbookPath = "c:/BillingAnalysis.xls";
        m_objBook = m_objExcel.Workbooks.Open(workbookPath,
            0, false, 5, "", "", false, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "",
            true, false, 0, true, false, false);



        }




}

mmcdonal · September 22nd, 2006, 07:31 AM

I don't think this is VBA. :D

Try "Integrating Excel and Access" by OReilly. I think you need that level of depth.

mmcdonal

Bob Bedell · September 22nd, 2006, 11:15 PM

Post your comment to the C# forum:

http://p2p.wrox.com/c-22/

If your list control is using a DataSet as its record source, you could write the values from the relevant DataTable to Excel using nested foreach loops, something like:

Code:

DataTable table = dataSet.Tables[0];
int ColumnIndex=0; 
foreach(Datacolumn col in table.Columns) {   
  ColumnIndex++;
  excelObject.Cells[1, ColumnIndex]=col.ColumnName;
} 
int rowIndex=0; 
foreach(DataRow row in table.Row) {         
  rowIndex++;       
  ColumnIndex=0;         
  foreach(DataColumn col in table.Columns) {  
    ColumnIndex++;                
    excelObject.Cells[rowIndex+1, ColumnIndex]=row.Cells[col.ColumnName].Text;         
  }
}

HTH,

Bob