Click here to Skip to main content
15,918,976 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Datagridview exception : System Exception : SomeText in Grid... is not a valid value for double . input string is not in a correct format

i use to import the excel file to datagridview and hide it in my program.

the marge cell in excel only show in first cell in datagridview. like if there was a

merge cell in cell 1 and 2 the string will just only appear in cell 1

so i design to loop and add that string in the second cell and keep doing this with all

the merge cell. i got the error with just! only one column it was like it is except only

integer and double value any one have any clue ? Here is my code

PS: Sorry if my English is bad : (
C#
private void comboBox1_SelectedIndexChanged(object sender, EventArgs e)
{
    try
    {
        dataGridView3.DataSource = null;
        string FileName = openFileDialog4.FileName; 
path.GetFileName
        if (FileName == "openFileDialog4" || FileName == null)
        {
            FileName = dragtamp;
        }
        OleDbConnection con = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + FileName + @";Extended Properties=""Excel 8.0;IMEX=1;HDR=NO;TypeGuessRows=0;ImportMixedTypes=Text""");
        con.Open();
        DataTable dts = new DataTable();
        dts = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
        if (dts == null)
        {
            //return null;
        }
        String[] excelSheets = new String[dts.Rows.Count];
        int ii = 0;
        foreach (DataRow row in dts.Rows)
        {
            excelSheets[ii] = row["TABLE_NAME"].ToString();
            ii++;
        }
        List<string> SheeTemp = new List<string>();
        SheeTemp.Clear();
        string[] sheetTemp = new string[5];
        for (int j = 0; j < excelSheets.Length; j++)
        {
            SheeTemp.Add(excelSheets[j]);
            // Query each excel sheet.
        }
        for (int l = 0; l < SheeTemp.Count; l++)
        {
            sheetTemp = SheeTemp[l].Split('$');
            if (sheetTemp[1] == "" || sheetTemp[1] == " ")
            {

            }
            else
            {
                SheeTemp.RemoveAt(l);
            }
        }

        string X = comboBox1.GetItemText(comboBox1.SelectedItem);
        string strSQL = "SELECT * FROM [" + X + "]";
        //string strSQL = "SELECT * FROM [T7AA(130725)$]";
        OleDbCommand com;
        com = new OleDbCommand(strSQL, con);
        OleDbDataReader odr;
        com.Parameters.Clear();
        odr = com.ExecuteReader();
        DataTable dt = new DataTable();
        ChangeIntJpnToIntEngTable(dt);
        if (odr.HasRows)
        {
            dt.Load(odr);
            //dataGridView1.Columns[3].Visible = false;
            //dataGridView1.Columns[2].Visible = false;
            //dataGridView1.Columns[1].Visible = false;
            //dataGridView1.Columns[0].Visible = false;

            dataGridView3.DataSource = dt;
            //dataGridView2.Rows.RemoveAt(0);
            List<int> deleteunwanteddata = new List<int>();
            int tempCounterfordelete = 0;
            for (int i = 0; i < dataGridView3.Rows.Count; i++)
            {
                tempCounterfordelete++;
                if (Convert.ToString(dataGridView3.Rows[i].Cells[1].Value) == "機種\nコード")
                {
                    i = dataGridView3.Rows.Count;
                }
                deleteunwanteddata.Add(tempCounterfordelete);

            }
            for (int i = 0; i <= deleteunwanteddata.Count - 2; i++)
            {
                dataGridView3.Rows.RemoveAt(0);
            }
            DataTable newdt = new DataTable();
            dataGridView4.Rows.Clear();
            dataGridView4.Columns.Clear();

            for (int i = 0; i < dataGridView3.Columns.Count; i++)
            {
                dataGridView4.Columns.Add(Convert.ToString(i),Convert.ToString(i));
                dataGridView4.Rows.Add();
                for (int iam = 0; iam < dataGridView3.Rows.Count; iam++)
                {
                    dataGridView4.Rows.Add();
                }
            }
            string TEMPGRID3 = "" ;

            for (int i = 0; i < dataGridView3.Rows.Count; i++)
            {
                for (int lll = 0; lll < dataGridView3.Columns.Count; lll++)
                { 
                    TEMPGRID3 = Convert.ToString(dataGridView3.Rows[i].Cells[lll].Value);
                     dataGridView4.Rows[i].Cells[lll].Value = TEMPGRID3;
                }
            }
            for (int i = 0; i < dataGridView3.Columns.Count - 1; i++)
            {

                if (Convert.ToString(dataGridView3.Rows[0].Cells[i].Value) == null || Convert.ToString(dataGridView3.Rows[0].Cells[i].Value) == "")
                {
// here is the line that i add a string to the other merge cell and the exception was //appear after this line below was excecuted
                    if (i > 0)
                    {
                        dataGridView3.Rows[0].Cells[i].Value = Convert.ToString(dataGridView3.Rows[0].Cells[i - 1].Value);
                    }
                }
            }
            //------------------------------------------------------------------------------------ใส่ HEADER ให้เหมือนกันแล้ววววววววววว
            for (int i = 7; i < dataGridView3.Rows.Count - 1; i++)
            {
                string chk;
                chk = Convert.ToString(dataGridView3.Rows[i].Cells[1].Value);
                if (chk == "0" || chk == "1" || chk == "2" || chk == "3" || chk == "4" || chk == "5" || chk == "6" || chk == "7" || chk == "8" || chk == "9")
                {
                    dataGridView3.Rows[i].Cells[1].Value = "0" + chk;
                }
            }
            for (int i = 7; i < dataGridView3.Rows.Count - 1; i++)
            {
                string chk;
                chk = Convert.ToString(dataGridView3.Rows[i].Cells[2].Value);
                if (chk == "0" || chk == "1" || chk == "2" || chk == "3" || chk == "4" || chk == "5" || chk == "6" || chk == "7" || chk == "8" || chk == "9")
                {
                    dataGridView3.Rows[i].Cells[2].Value = "0" + chk;
                }
            }
            bool splitstatus = false;
            int SplitCounter = 0;
            int temppointer;
            for (int i = 0; i < dataGridView3.Rows.Count; i++)
            {
                splitstatus = false;
                for (int k = 0; k < dataGridView3.Columns.Count; k++)
                {
                    Counter++;
                    if (Convert.ToString(dataGridView3.Rows[i].Cells[k].Value) == "" || Convert.ToString(dataGridView3.Rows[i].Cells[k].Value) == " " || Convert.ToString(dataGridView3.Rows[i].Cells[k].Value) == null)
                    {
                        splitstatus = true;
                    }
                    if (splitstatus == false)
                    {
                        temppointer = i;
                    }
                }
            }
        }
Posted
v2
Comments
Please debug and see. Somewhere you are converting text to double, which is not possible.
Real_Criffer 5-May-15 21:07pm    
i just loop entire excell to datagridview it just like

"The error cell" is already have a default value as double :/
Not clear. Please explain more.
Real_Criffer 6-May-15 4:39am    
the oledb check that the data in that column store only number So oledb set the format of that column to integer(double) as it say in error pop up.
the solution i found is to set format of entire excel to text. i just want to know is there any option ?

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900