首页 > Web开发 > 详细

.NET操作Excel

时间:2014-01-21 09:32:31      阅读:403      评论:0      收藏:0      [点我收藏+]

如果你新建一个项目的话,首先要添加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];
             }


.NET操作Excel

原文:http://blog.csdn.net/pzhtpf/article/details/18264507

(0)
(0)
   
举报
评论 一句话评论(0
关于我们 - 联系我们 - 留言反馈 - 联系我们:wmxa8@hotmail.com
© 2014 bubuko.com 版权所有
打开技术之扣,分享程序人生!