Trying to write data to Excel

[itmasterw]

Hi,

I used to be able tpo do this but must of forgot something. I am trying to write to Excel sheet and while I have the right references, but something particuarly with the ranges I am doing wrong. Can someone please tell me what I am doing wrong.

here is my code:

private void toolStripButton4_Click(object sender, EventArgs e) 
{ 
Excel.Application exl; 

Excel._Workbook wb; 

Excel._Worksheet ws; 

Excel.Range rng; 


string commandString; 

int a; 
int b; 
int c; 

Single TotalV; 
DialogResult dlgRes; 

OleDbConnection cn = new OleDbConnection(SalesPorf.Util.ConectStr()); 


commandString = "SELECT [Order Details].OrderID, [Order Details].ProductID, [Order Details].UnitPrice, [Order Details].Quantity, [Order Details].Discount FROM [Order Details] Where [Order Details].OrderID= '" + Util.OrderHold + "' "; 
OleDbDataAdapter da = new OleDbDataAdapter(commandString, cn); 

DataSet ds = new DataSet(); 

da.Fill(ds, "UserTable"); 

//*********This section loads the cells*********** 

//sets up the the Ecel application, workbook, and worksheet 

//Start Excel and get Application object. 
exl = new Excel.Application(); 
exl.Visible = true; 

//Get a new workbook. 
 
wb = excel.Workbooks.Add(); 
ws = wb.ActiveSheet(); 


//This loads the headings 
rng = ws.get_Range("C1"); 
rng = ws.Cells('"C1"); 
rng.FormulaR1C1 = "Order Details Report"; 
rng.Cells.Interior.ColorIndex = 6; //6 = the collor Yellow; 
//rng.Cells.Font.Bold; 
rng.ColumnWidth = 17.71; 

rng = ws.get_Range("A3"); 
rng.Value2 = "Order Totals"; 
rng = ws.get_Range("A4"); 
rng.Value2 = "Product ID"; 
rng = ws.get_Range("B4"); 
rng.Value2 = "Quantity"; 
rng = ws.get_Range("C4"); 
rng.Value2 = "Unit Price"; 

//Cell counters 
a = 6; 
b = 6; 
c = 6; 

//Go through the dataset and populate the cells 
for (int i = 0; ds.Tables("OrdersDT").Rows.Count - 1; i++) 
{ 
rng = ws.get_Range("A") & cstr(a); 
rng.Value2 = ds.Tables("OrdersDT").Rows(i)("ProductID").ToString(); 
rng = ws.ws.get_Range("B" & CStr(b)); 
rng.Value = ds.Tables("OrdersDT").Rows(i)("Quantity").ToString(); 
rng = ws.Range("C" & CStr(c)); 
rng.Value = ds.Tables("OrdersDT").Rows(i)("UnitPrice").ToString(); 
a = a + 1; 
b = b + 1; 
TotalVl = TotalVl + rng.Value2; 
c = c + 1; 
} 

c = c + 2; 

rng = ws.Range("B" & CStr(b + 1)); 
rng.Value = "Total"; 
rng = ws.Range("C" & CStr(c - 1)); 
rng.Value = Math.Round(TotalVl, 2); //Rounds the value to two decimale places 

rng = ws.Range("A1"); 
rng.ColumnWidth = 11.43; 

rng = ws.Range("A3"); 
rng.Cells.Font.Bold = True; 
rng.Cells.Font.Underline = True; 

excel.Visible = True; 
wb.Activate(); 

dlgRes = MessageBox.Show("Do you which to save the report", "Save Report", MessageBoxButtons.YesNoCancel, MessageBoxIcon.Question); 

If dlgRes = DialogResult.Yes; 
{ 
//excel.SaveWorkspace() 
excel.ActiveWorkbook.SaveAs(); //saves the the Excel report 
} 

excel.Quit(); 
excel = Nothing; 
}

[kvprajapati]

>Trying to write data to Excel

The easiest way is OleDB.

System.Data.OleDb.OleDbConnection cn = new System.Data.OleDb.OleDbConnection(
                @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\file.xls;Extended Properties=Excel 8.0;");

cn.Open();

System.Data.OleDb.OleDbCommand cmd = new System.Data.OleDb.OleDbCommand();

cmd.Connection = cn;
cmd.CommandText = "Insert into Sheet1 (A1,B1) values (10,'Foo')";
	
cmd.ExecuteNonQuery();
cn.Close();

[itmasterw]

Thank you I will try this