Share via

insert values to individual cells in an Excel sheet using c#.net.

  • Article

Question

Monday, November 17, 2008 12:02 PM

HI All,
         This is my need, using COM I need to insert values to individual cells.

The problems like creating an Excel application , Adding a Work book, Adding a Worksheet are all done. i have problem with inserting values to individual cells. Can anyone suggest a solution for this.

Thank you.

All replies (2)

Wednesday, November 19, 2008 7:28 AM âś…Answered

Hi,

Please try this ,it works here:

            Excel.Application oXL;  
            Excel._Workbook oWB;  
            Excel._Worksheet oSheet;  
            Excel.Range oRng;  
 
            try  
            {  
                //Start Excel and get Application object.  
                oXL = new Excel.Application();  
                oXL.Visible = true;  
 
                //Get a new workbook.  
                oWB = (Excel._Workbook)(oXL.Workbooks.Add(Missing.Value));  
                oSheet = (Excel._Worksheet)oWB.ActiveSheet;  
 
               //change it to your xlsx file
                string workbookPath = @"C:\Documents and Settings\v-harzhu\Desktop\csharplinq\excel update\excel update\bin\Debug\Book1.xlsx";  
            Excel.Workbook excelWorkbook = oXL.Workbooks.Open(workbookPath,  
        0, false, 5, "", "", false, Excel.XlPlatform.xlWindows, "",  
        true, false, 0, true, false, false);  
 
               Excel.Sheets excelSheets = excelWorkbook.Sheets;  
                string currentSheet = "Sheet1";  
                Excel.Worksheet excelWorksheet =  
                    (Excel.Worksheet)excelSheets.get_Item("sheet2");  
 
 
 
                //Add table headers going cell by cell.  
                oSheet.Cells[1, 1] = "First Name";  
                oSheet.Cells[1, 2] = "Last Name";  
                oSheet.Cells[1, 3] = "Full Name";  
                oSheet.Cells[1, 4] = "Salary";  
 
                //Format A1:D1 as bold, vertical alignment = center.  
                oSheet.get_Range("A1", "D1").Font.Bold = true;  
                oSheet.get_Range("A1", "D1").VerticalAlignment =  
                    Excel.XlVAlign.xlVAlignCenter;  
 
                // Create an array to multiple values at once.  
                string[,] saNames = new string[5, 2];  
                string[,] ss = new string[5, 2];  
 
                saNames[0, 0] = "John";  
                saNames[0, 1] = "Smith";  
                saNames[1, 0] = "Tom";  
                saNames[1, 1] = "Brown";  
                saNames[2, 0] = "Sue";  
                saNames[2, 1] = "Thomas";  
                saNames[3, 0] = "Jane";  
                saNames[3, 1] = "Jones";  
                saNames[4, 0] = "Adam";  
                saNames[4, 1] = "Johnson";  
                  
                //Fill A2:B6 with an array of values (First and Last Names).  
                oSheet.get_Range("A2", "B6").Value2 = saNames;  
 
                 object o=oXL.get_Range(oXL.Cells[1,1],oXL.Cells[5,2]).Value2;  
                 
                //Fill C2:C6 with a relative formula (=A2 & " " & B2).  
                oRng = oSheet.get_Range("C2", "C6");  
                oRng.Formula = "=A2 & \" \" & B2";  
 
                //Fill D2:D6 with a formula(=RAND()*100000) and apply format.  
                oRng = oSheet.get_Range("D2", "D6");  
                oRng.Formula = "=RAND()*100000";  
                oRng.NumberFormat = "$0.00";  
 
                //AutoFit columns A:D.  
                oRng = oSheet.get_Range("A1", "D1");  
                oRng.EntireColumn.AutoFit();  
 
                //Manipulate a variable number of columns for Quarterly Sales Data.  
                DisplayQuarterlySales(oSheet);  
 
                //Make sure Excel is visible and give the user control  
                //of Microsoft Excel's lifetime.  
                oXL.Visible = true;  
                oXL.UserControl = true;  
            }  
            catch (Exception theException)  
            {  
                String errorMessage;  
                errorMessage = "Error: ";  
                errorMessage = String.Concat(errorMessage, theException.Message);  
                errorMessage = String.Concat(errorMessage, " Line: ");  
                errorMessage = String.Concat(errorMessage, theException.Source);  
 
                MessageBox.Show(errorMessage, "Error");  
            } 

If you still can not insert data ,please post your code, maybe some members have idea on it.

Best regards,
Harry

Wednesday, November 19, 2008 12:56 PM

I need only the part that is used to insert value in a single cell...Anyway thank you for your timely help.