exporting data to Excel 1

[lfc77]

I want to export data to Excel using C#. My problem is that I found a few examples on the web, but none of them work. Could this be because I am using Excel 97? Do I need Excel 2000 or Excel XP to work with C#?

Thanks,

lfc77

 

[obislavu]

What is the error that you got ?
-obislavu-

 

[lfc77]

I've tried a copy of Excel 2000 and I still get the same error...maybe it's just bad code. Does anybody know where I can get an example of exporting data to Excel that works?


Thanks,

lfc77

 

[crazyboybert]

Hi lfc

Is this for a ASP.NET app? If so this has been discussed exhaustively on the ASP.NET forum here at TT. Here's a couple of recent threads but a keyword search fro 'excel' will trun up plenty of results...

thread855-756909

thread855-321761

Rob

Go placidly amidst the noise and haste, and remember what peace there may be in silence - Erhmann 1927

 

[lfc77]

This isn't for ASP.NET, it's for a Windows app. I'll have a look at the threads you've listed and see if there is anything in them that I can apply to what I am trying to do.

Thanks,

lfc77

 

[obislavu]

I put here a piece of code that I am using and is working well to export data to an excel file.
In this example when the user clicks on the Export menu item in a Form then :

- the .xls file to be exported is provided using a SaveFileDialog.
- the data is retrieved from a SQL database and stored in a DataSet object.
- the first row is the colum header and are retreievd from the column of the Tables[0]
- the Sheet is populated with the rows of the DataSet objects
- close Excel application
- use Status bar to display the export activity for the huge files

// Variables referenced in this example 
private System.Windows.Forms.SaveFileDialog m_SaveFileDialog;
private bool m_IsExporting=false;
private object m_MissingValue = System.Reflection.Missing.Value;
private System.Windows.Forms.StatusBar m_StatusBar;
private Excel.Application m_Excel;
private Excel._Workbook m_WorkBook;
private Excel._Worksheet m_WorkSheet;
private System.Data.SqlClient.SqlConnection m_SQLConnection;

// Called when the user click on the Export menu item in a Form
private void Export_Click(object sender, System.EventArgs e)
{
	
	if (m_IsExporting ) 
	{
		MessageBox.Show("Applicaiton is busy."); 
		return;
	}
    
	m_SaveFileDialog.Title="Export to file";
	m_SaveFileDialog.FileName = "..." + ".xls";
	if (m_SaveFileDialog.ShowDialog()!=System.Windows.Forms.DialogResult.OK) return;
	try 
	{
		m_IsExporting=true;
		SetContext(); // Enable/Disable the Export menu item
		GC.Collect(); 
		m_Excel = new Excel.Application();
		m_WorkBook= m_Excel.Workbooks.Add(m_MissingValue);
		Excel._Worksheet w = (Excel._Worksheet)m_WorkBook.Worksheets.get_Item(1);
		m_WorkSheet=(Excel._Worksheet) ((m_WorkBook.Worksheets.Count>0 ) ? m_WorkBook.Worksheets.get_Item(1) : m_WorkBook.Worksheets.Add(m_MissingValue,m_MissingValue,m_MissingValue,m_MissingValue)); 
		m_WorkSheet.Name =m_SearchForm.SelectedInventoryView.Name;
			
		// Populate With Data from a DataSet object obtained here
		DataSet vDataSet = GetDataSet("SELECT * FROM ..."); // This function retrieves the data to be exported 
		int r=0,c=0;
                // Populate first row with column names
		foreach(DataColumn vDataColumn in vDataSet.Tables[0].Columns)
		{
			m_WorkSheet.Cells[1,c+1]=vDataColumn.ColumnName;
			c++;
		}
		r++;
                // Add the data rows
		foreach(DataRow vDataRow in vDataSet.Tables[0].Rows)
 		{
			m_StatusBar.Text = "Exporting...  " + r.ToString() + "/" +  vDataSet.Tables[0].Rows.Count.ToString() ;
			for(c=0;c<vDataSet.Tables[0].Columns.Count;c++)
			{
				m_WorkSheet.Cells[r+1,c+1]=System.Convert.ToString(vDataRow[c]);
			}
			r++;
			System.Windows.Forms.Application.DoEvents();
                }
                m_WorkBook.SaveAs(m_SaveFileDialog.FileName,m_MissingValue, m_MissingValue,m_MissingValue, m_MissingValue, m_MissingValue, Excel.XlSaveAsAccessMode.xlNoChange, m_MissingValue, m_MissingValue, m_MissingValue, m_MissingValue, m_MissingValue);
		m_WorkBook.Close(null, null, null);
		m_Excel.Quit();
		if (m_Excel!=null) System.Runtime.InteropServices.Marshal.ReleaseComObject(m_Excel);
		if (m_WorkSheet!=null) System.Runtime.InteropServices.Marshal.ReleaseComObject(m_WorkSheet);
		if (m_WorkBook!=null) System.Runtime.InteropServices.Marshal.ReleaseComObject(m_WorkBook);
		m_WorkSheet=null;
		m_WorkBook=null;
		m_Excel=null;
		GC.Collect(); 
		m_StatusBar.Text = &quot;Export complete.&quot;;
          }
          catch(Exception vException)
	  {
		MessageBox.Show(&quot;An error ocurred in Excel application &quot; + vException).GetType() + vException.Message); 
          }
          finally
          {
           	if (m_Excel!=null) System.Runtime.InteropServices.Marshal.ReleaseComObject(m_Excel);
	   	if (m_WorkSheet!=null) System.Runtime.InteropServices.Marshal.ReleaseComObject(m_WorkSheet);
		if (m_WorkBook!=null) System.Runtime.InteropServices.Marshal.ReleaseComObject(m_WorkBook);
		m_WorkSheet=null;
		m_WorkBook=null;
		m_Excel=null;
		GC.Collect(); 
          }
          m_IsExporting=false;
	  SetContext(); // Enable/disable menu items

}

public System.Data.DataSet GetDataSet(string vSQLStatement)
{
	try
	{
		System.Data.SqlClient.SqlDataAdapter vSQLAdapter = new SqlDataAdapter(vSQLStatement,m_SQLConnection); 
		System.Data.DataSet vReturn = new System.Data.DataSet();   
		vSQLAdapter.Fill(vReturn); 
		return(vReturn);
	}
	catch(Exception vException)
	{
		return(new System.Data.DataSet());
	}
		
}
public void SetContext()
{
   // Enable /Disable menu itmes depending on m_IsExporting ...
}

-obislavu-

 

[lfc77]

Obislavu,

Very interesting piece of code! I'll definitely check this out and see what I can do with it!

Cheers,

lfc77