请教各位，winform中C#读取Excel数据

Question

在做这部分的时候，用以下语句来读取Excel的数据
"Provider=Microsoft.Jet.Oledb.4.0;Data Source=D:\\test\\aa.xls;Extended Properties='Excel 8.0;HDR=Yes;IMEX=1';"
现在Excel文件中有94条数据，执行程序调试跟踪发现每次读出来的数据总会出现其中有几行是空，有时又不是空，请问这个问题该怎么解决呢？都快弄了一天了,我是用C#来做的

Answer 1

using System;   
using System.Collections.Generic;   
using System.Text;   
using System.Data;   
using System.Data.OleDb;   
using System.Data.SqlClient;   
using System.IO;   
using Microsoft.Office.Interop.Excel;   
namespace TestAccess   
{   
    class Program   
    {   
        static void Main(string[] args)   
        {   
  
            string strConnection = "Provider=Microsoft.ACE.OLEDB.12.0;";   
            strConnection += @"Data Source=C:\Documents and Settings\v-changl\My Documents\couse.xlsx;";   
            strConnection += "Extended Properties=\"Excel 12.0 Xml;HDR=YES\";";   
            OleDbConnection objConnection = new OleDbConnection(strConnection);   
            objConnection.Open();   
            OleDbDataAdapter myCommandd = new OleDbDataAdapter("select * from [Sheet1$]", objConnection);   
            DataSet ds = new DataSet();   
            myCommandd.Fill(ds, "[Sheet1$]");   
            System.Data.DataTable dt = ds.Tables["[Sheet1$]"];   
            Console.WriteLine(dt.Columns[0].ToString());   
            Console.WriteLine(dt.Columns[1].ToString());   
            DataRow drDisplay = dt.Rows[0];   
            int[] num = new int[dt.Columns.Count];   
            for (int j = 0; ; )   
            {   
                for (int i = 0; i < dt.Columns.Count; i++)   
                {   
  
                    if (drDisplay[i] is DBNull) ;   
                    else  
                        num[i] += Convert.ToInt32(drDisplay[i]);   
  
                }   
                if (++j >= dt.Rows.Count) break;   
                drDisplay = dt.Rows[j];   
            }   
            objConnection.Close();   
            object MissingValue = Type.Missing;   
            Microsoft.Office.Interop.Excel.Application app = new Application();   
            Microsoft.Office.Interop.Excel.Workbook wbook = app.Workbooks.Open(@"C:\Documents and Settings\v-changl\My Documents\couse.xlsx", MissingValue,   
MissingValue, MissingValue, MissingValue,   
MissingValue, MissingValue, MissingValue,   
MissingValue, MissingValue, MissingValue,   
MissingValue, MissingValue, MissingValue,   
MissingValue);   
            Microsoft.Office.Interop.Excel.Worksheet wsheet = wbook.ActiveSheet as Microsoft.Office.Interop.Excel.Worksheet;   
            for (int i = 0; i < dt.Columns.Count; i++)   
            {   
                //注意下面是i+1,,excel小标默认从1开始   
                wsheet.Cells[dt.Rows.Count + 2, i + 1] = num[i].ToString();   
            }   
  
            wbook.Save();   
            wbook.Close(true, null, null);   
            System.Runtime.InteropServices.Marshal.ReleaseComObject(wsheet);   
            System.Runtime.InteropServices.Marshal.ReleaseComObject(wbook);   
            System.Runtime.InteropServices.Marshal.ReleaseComObject(app);   
            wsheet = null;   
            wbook = null;   
            app = null;   
            GC.Collect();   
        }   
    }   
}  

Answer 2

"Provider=Microsoft.Jet.Oledb.4.0;Data Source=D:\\test\\aa.xls;Extended Properties='Excel 8.0;HDR=Yes;IMEX=1';" 
最后两个参数去掉试试!

Answer 3

1#的是office2007的代码，
2#HDR=Yes;IMEX=1'不行我才加，结果加上去结果一样，
3#网上说加这两个参数可以解决HDR=Yes;IMEX=1，结果还是一样谢谢你们了

Answer 4

我的程序也是经常出现这个的问题，现在我都改成将excel另存为txt,然后上传，实在没没办法才这样做，引用了FilesHelper.dll

Answer 5

给你个例子 这是我以前已经测试好的
public void OpenExcel()
        {
            Application app = new Application();//Open Excel Application.
            Workbooks wbs = app.Workbooks;
            _Workbook wb = wbs.Add(@"D:\work\30yr_pmmsmnth.xls");//Open an existing workbook
            Sheets shs = wb.Sheets;
            _Worksheet wsh = (_Worksheet)shs.get_Item(1);//Select the first Sheet page            int a = 8;
            int b = 20;
            for (int i = 0; i < 6; i++)
            {
                if (i == 0)
                {
                    for (; a < b; a++)
                    {
                        Range rr = wsh.get_Range("A" + a, "U" + a);
                        Object[,] saRet = (System.Object[,])rr.get_Value(System.Reflection.Missing.Value);
                        for (int n = 0; n < 7; n++)
                        {
                            string indexs = "";
                            if (n == 0)
                            {
                                indexs = "B5";
                            }
                            else if (n == 1)
                            {
                                indexs = "E5";
                            }
                            else if (n == 2)
                            {
                                indexs = "H5";
                            }
                            else if (n == 3)
                            {
                                indexs = "K5";
                            }
                            else if (n == 4)
                            {
                                indexs = "N5";
                            }
                            else if (n == 5)
                            {
                                indexs = "Q5";
                            }
                            else if (n == 6)
                            {
                                indexs = "T5";
                            }
                            Range range0 = wsh.get_Range(indexs, System.Reflection.Missing.Value);
                            Pmmsmnthyr pp = new Pmmsmnthyr();
                            pp.WeekDay = saRet[1, 1].ToString() + "/" + range0.Value2.ToString();
                            pp.Rate = saRet[1, 2 + n * 3].ToString();
                            pp.Pts = saRet[1, 3 + n * 3].ToString();
                        }
                    }
                }
}

Answer 6

 给你关键代码，这段代码我使用了好几年了
fileName = this.txtexcel.Text.Trim();
            if (System.IO.File.Exists(fileName) == false)
            {
                MessageBox.Show("請確認Excel檔的位置,路徑請設置為英文或數字,並盡可能短,謝謝!");
                return;
            }
GetWindowThreadProcessId(t,out k);
System.Diagnostics.Process pe=System.Diagnostics.Process.GetProcessById(k); 
object missing=Type.Missing;
Excel.Workbook xBook =m_objExcel.Workbooks.Open(fileName,missing,missing,missing,missing,missing,missing,missing,missing,missing,missing,missing,missing,missing,missing);
//Excel.Sheets sheets = xBook.Worksheets;
string cell="";
for(int hh=1;hh<=xBook.Worksheets.Count;hh++)
{
                                ......
                        }

Answer 7

问题解决了，用了NPOI，感觉还不错