.NET操作Excel

jopen 11年前

如果你新建一个项目的话,首先要添加Microsoft.Office.Core 与Microsoft.Office.Interop.Exce这两个应用,然后就能很方便的操作了,示例代码(只实现了简单的读写):

 

private Excel._Application excelApp;    private Workbook wbclass;        excelApp = new Excel.Application();        object objOpt = System.Reflection.Missing.Value;                    wbclass = (Workbook)excelApp.Workbooks.Open("E:\Book6.xlsx", objOpt, false, objOpt, objOpt, objOpt, true, objOpt, objOpt, true, objOpt, objOpt, objOpt, objOpt, objOpt);

上面声明,引用,并把要操作的 excel 的路径传给他

 

得到所有的表名:

 

 

 List<string> list = new List<string>();              Excel.Sheets sheets = wbclass.Worksheets;              string sheetNams = string.Empty;              foreach (Excel.Worksheet sheet in sheets)              {                  list.Add(sheet.Name);              }

 

 获取某个表中的数据,这里获取的是sheet 表中的:

 
public Excel.Worksheet GetWorksheetByName(string name)            {                Excel.Worksheet sheet = null;                Excel.Sheets sheets = wbclass.Worksheets;                foreach (Excel.Worksheet s in sheets)                {                    if (s.Name == name)                    {                        sheet = s;                        break;                    }                }                return sheet;            }                public System.Data.DataTable GetDateTable(string name)            {                System.Data.DataTable dt = new System.Data.DataTable();                    var worksheet = GetWorksheetByName(name);      //调用上面的方法,利用表名得到这张表                    string cellContent;                    int iRowCount = worksheet.UsedRange.Rows.Count;                int iColCount = worksheet.UsedRange.Columns.Count;                Excel.Range range;                for (int iRow = 1; iRow <= iRowCount; iRow++)                {                    DataRow dr = dt.NewRow();                        for (int iCol = 1; iCol <= iColCount; iCol++)                    {                        range = (Excel.Range)worksheet.Cells[iRow, iCol];                            cellContent = (range.Value2 == null) ? "" : range.Text.ToString();                            if (iRow == 1)                        {                            dt.Columns.Add(cellContent);                        }                        else                        {                            dr[iCol - 1] = cellContent;                        }                    }                        if (iRow != 1)                        dt.Rows.Add(dr);                }                    return dt;                }  


上面得到的只是 

 
System.Data.DataTable   

如何把数据取出来,请看下面:

 

    var dataTable = GetDateTable("Sheet1");   //调用上面的方法                                   foreach (DataRow row in dataTable.Rows)                   {                            string a = (string)row[2];                       string b = (string)row[4];          }  
</div>
</div>