OleDb方式读取excel,和现实不符

开始时候按照严格的excel模板来执行,数据都是严格要求的,可是到了客户那里,什么花招都来了..也不使用指定的模板了...

导致一个问题:  数据列格式不一致  比如一列 前面几个全部是数字类型,中间穿插几个以字符形式保存的数字...   直接导致这些数据读取为DbNull

怎么解决...搜索一下..发现这个是oledb的bug...不管你设置成IMEX =1 还是=2 , 他都不会读取这些数据

最终解决方案... 使用interop,首先转换成csv...然后读取文本行 ,部分代码拷贝自网络

 

            string TempPath = AppDomain.CurrentDomain.BaseDirectory + "csvTemp\\";
            if (Directory.Exists(TempPath))
            {
                Directory.Delete(TempPath, true);
            }
            Directory.CreateDirectory(TempPath);
            string filename = TempPath + Guid.NewGuid() + ".csv";
            Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.ApplicationClass();
            try
            {
                app.Visible = false;
                Workbook wBook = app.Workbooks.Add(true);
               
                app.ScreenUpdating = false;
                Workbook wb = app.Workbooks.Open(filepath, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);

                wb.SaveAs(filename, Microsoft.Office.Interop.Excel.XlFileFormat.xlCSV, Type.Missing, Type.Missing,
                            Type.Missing,
                            Type.Missing,
                            Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing,
                            Type.Missing, Type.Missing, Type.Missing,
                            Type.Missing);
                            wb.Close(false, Type.Missing, false);

                         
                
       
            app.Workbooks.Close();
            app.Quit();
            app = null;
            GC.Collect();

            String line;
            String[] split = null;
            DataTable table = new DataTable(TableName);
 

            DataRow row = null;
            StreamReader sr = new StreamReader(filename, System.Text.Encoding.Default);
            //创建与数据源对应的数据列 
            line = sr.ReadLine();
            split = line.Split(',');
            foreach (String colname in split)
            {
                table.Columns.Add(colname, System.Type.GetType("System.String"));
            }
            //将数据填入数据表 
            int j = 0;
            while ((line = sr.ReadLine()) != null)
            {
                j = 0;
                row = table.NewRow();
                split = line.Split(',');
                foreach (String colname in split)
                {
                    row[j] = colname;
                    j++;
                }
                table.Rows.Add(row);
            }
            sr.Close();
            //显示数据 
            dgclass.DataSource = table;

            importNow.Enabled = true;

            }
            catch (Exception ex)
            {
                log.Error(ex, ex);
                MessageBox.Show(this, ex.Message + "\r\n您可能没有安装excel程序");
              
            }