Inserting data from c# textbox to excel

Question

Hi im unsing Oledb to insert data to an excel sheet from multiple textboxes. The issue i have is that it is not going into excel as a number type but string, so a grpah can no be automated off the date. How would i insert values into excel as number type? ive tried converting the textboxes to int with no luck.

My code is as below.

 string szConn = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=L://Metrics_gen.xlsx;Extended Properties='Excel 8.0;HDR=YES;'";
            OleDbConnection conn = new OleDbConnection(szConn);
            int v1 = Convert.ToInt32(textBox1.Text);
            int v2 = Convert.ToInt32(textBox2.Text);
            int v3 = Convert.ToInt32(textBox3.Text);
            int v4 = Convert.ToInt32(textBox4.Text);
            int v5 = Convert.ToInt32(textBox5.Text);


            conn.Open();
            OleDbCommand cmd = new OleDbCommand("INSERT INTO [Sheet1$]([Total],[Closed],[Issues],[Cancelled],[Back out]) VALUES('" + v1 + "','" + v2 + "','" + v3 + "','" + v4 + "','" + v5 + "')", conn);
            cmd.ExecuteNonQuery();


            conn.Close();
            MessageBox.Show("complete");

@stefan Could you please check the answer I gave to your question, verify that it works and accept it if it does? Cheers :-) — Miltos Kokkonidis
– Miltos Kokkonidis, Commented Nov 30, 2012 at 3:16

Miltos Kokkonidis · Accepted Answer · 2012-11-28 00:19:46Z

The problem is that you are converting your integers into string literals when constructing the insert command. See below:

OleDbCommand cmd = new OleDbCommand("INSERT INTO [Sheet1$]([Total],[Closed],[Issues],[Cancelled],[Back out]) VALUES('" + v1 + "','" + v2 + "','" + v3 + "','" + v4 + "','" + v5 + "')", conn);

Change that line into:

OleDbCommand cmd = new OleDbCommand("INSERT INTO [Sheet1$]([Total],[Closed],[Issues],[Cancelled],[Back out]) VALUES(" + v1 + "," + v2 + "," + v3 + "," + v4 + "," + v5 + ")", conn);

This should solve all your problems :-)

Note: Constructing SQL by hand is error prone. Integers are the least of your worries. Imagine trying to inserting a string which happens to contain the single quotation mark character ('), a float when you have set your culture to one with comma as the decimals separator, a date and so on and so forth. Personally, I prefer using libraries such as EPPlus (XLSX) and ExcelLibrary (XLS). Both are LGPL licensed and give you greater flexibility than the method you are currently using. They do not require Excel to be installed on the machine which makes them ideal for server-side Excel report generation. Moreover they help avoid the various memory leak problems solutions using Microsoft Office Interop often come with.

haakon.io · Accepted Answer · 2012-11-28 00:35:16Z

This code snippet has helped me deal with Excel spreadsheets in C#, specifically entering rows of numbers and then generating sums of that data. If you are processing the rows in code after loading the data into excel, make sure you are ignoring the header rows, this should be obvious but just mentioning it here in case that is the issue you are having.

The following code requires the declaration- using Excel = Microsoft.Office.Interop.Excel;

      Excel.Application oXL;
       Excel.Workbook oWB;
        Excel.Worksheet oSheet;
        Excel.Range oRange;

             var filepath = "/somefilepath";


            // Start Excel and get Application object.  
            oXL = new Excel.Application();

            // Set some properties  
            oXL.Visible = true;
            oXL.DisplayAlerts = false;

            // Get a new workbook.  
            oWB = oXL.Workbooks.Add(Missing.Value);

            // Get the Active sheet  
            oSheet = (Excel.Worksheet)oWB.ActiveSheet;
            oSheet.Name = "Data";

            int rowCount = 1;
            string strExpr;
            string strSort;


            foreach (DataRow dr in dt.Rows)
            {
                rowCount += 1;
                for (int i = 1; i < dt.Columns.Count + 1; i++)
                {
                    // Add the header the first time through  
                    if (rowCount == 2)
                    {
                        oSheet.Cells[1, i] = dt.Columns[i - 1].ColumnName;
                    }
                    oSheet.Cells[rowCount, i] = dr[i - 1].ToString();
                }
            }
           //Freze the first row
            oSheet.Application.ActiveWindow.SplitRow = 1;
            oSheet.Application.ActiveWindow.FreezePanes = true;

            // Resize the columns  
            oRange = oSheet.get_Range(oSheet.Cells[1, 1],
                          oSheet.Cells[rowCount, dt.Columns.Count]);
            oRange.EntireColumn.AutoFit();

            // Save the sheet and close  
            oSheet = null;
            oRange = null;
            oWB.SaveAs(filepath, Excel.XlFileFormat.xlWorkbookNormal,
                Missing.Value, Missing.Value, Missing.Value, Missing.Value,
                Excel.XlSaveAsAccessMode.xlExclusive,
                Missing.Value, Missing.Value, Missing.Value,
                Missing.Value, Missing.Value);
            oWB.Close(Missing.Value, Missing.Value, Missing.Value);
            oWB = null;
            oXL.Quit();

OK, this does not answer the actual question, but it could be a useful comment for anyone coming to this page if you just gave a bit more information about what the pre-requisites are for this code to work i.e. tell the reader this is the Microsoft Office Interop way of doing things, that you need to add the Microsoft.Office.Interop.Excel assembly to the project references and mention the benefits and the drawbacks of this method.

Collectives™ on Stack Overflow

Inserting data from c# textbox to excel

2 Answers 2

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related