C# 如何直接读取Excel表

来源：互联网发布：网络赚钱项目可信吗编辑：程序博客网时间：2024/05/21 10:36

using System;
using System.Collections.Generic;
using System.Text;
using Excel = Microsoft.Office.Interop.Excel;
using Microsoft.Office.Interop;
using System.Data;
using System.Data.OleDb;
using System.Reflection;
using System.Diagnostics;
using Microsoft.Office.Interop.Excel;

namespace ReadFirstSheetNameDemo
{
    public class Program
    {
            public static void Main(string[] args)
            {
                    //Get Xth SheetName of Excel File
                    //Console.WriteLine(GetFirstSheetNameFromExcelFileName(@"F:/PC/Documents/TianjinInstitute/天津自动站信息表address1new.XLS", 1));

                    //string fileName = @"F:/PC/Documents/TianjinInstitute/天津自动站信息表address1new.XLS";
                    string fileName = @"F:/PC/Documents/TianjinInstitute/自动站资料1.xls";
                    OpenExcel(fileName);

//ImportExcelToDataSet(fileName);

Console.ReadKey();
}

        /// <summary>
        /// 打开Excel文件
        /// 读取EXCEL的方法   (用范围区域读取数据)
        /// </summary>
        /// <param name="strFileName">Excel文件名</param>
        private static void OpenExcel(string strFileName)
        {
                object missing = System.Reflection.Missing.Value;
                Application excel = new Excel.Application();     //lauch excel application

                if (excel == null)
                {
                        Console.WriteLine("Can't access excel");
                }
                else
                {
                        excel.Visible = false;
                        excel.UserControl = true;

                        // 以只读的形式打开EXCEL文件
                        Workbook wb = excel.Application.Workbooks.Open(strFileName, missing, true, missing, missing, missing,
                            missing, missing, missing, true, missing, missing, missing, missing, missing);

                        //取得第一个工作薄
                        Worksheet ws = (Worksheet)wb.Worksheets.get_Item(1);

                        if (ws != null)
                        {
                                //取得总记录行数   (包括标题列)

                                //得到行数
                                int rowsint = ws.UsedRange.Cells.Rows.Count;

//得到列数
//int columnsint = mySheet.UsedRange.Cells.Columns.Count;

                                //取得数据范围区域 (不包括标题列)
                                //Range rng = ws.Cells.get_Range("B2:F2", "B" + rowsint);
                                Range rng = ws.Cells.get_Range("B2","F234");
                                //列数
                                int column = rng.Cells.Columns.Count;

object[,] arrayItem = (object[,])rng.Value2;

//将新值赋给一个数组
string[,] arry = new string[rowsint, column];

                                for (int i = 1; i <= rowsint - 1; i++)
                                {
                                        try
                                        {
                                                if (int.Parse(arrayItem[i, 1].ToString()) == 5)
                                                {

                                                }
                                        }
                                        catch
                                        {
                                                Console.WriteLine("{0,-8}{1,-20}{2,-15}{3,-15}{4,-8}", arrayItem[i, 1].ToString(), arrayItem[i, 2].ToString(),
                                                arrayItem[i, 3].ToString(), arrayItem[i, 4].ToString(), arrayItem[i, 5].ToString());
                                        }
                                }
                        }
                        //Console.WriteLine(arry[0, 0] + " / " + arry[0, 1] + "#" + arry[rowsint - 2, 0] + " / " + arry[rowsint - 2, 1]);
                }

                excel.Quit();
                excel = null;
                //Process[] procs = Process.GetProcessesByName("excel");
                //foreach (Process pro in procs)
                //{
                //        //没有更好的方法,只有杀掉进程
                //        pro.Kill();
                //}
                GC.Collect();
        }

        /// <summary>
        /// 获得Excel表的第N个表名
        /// </summary>
        /// <param name="filepath"></param>
        /// <param name="numberSheetID"></param>
        /// <returns></returns>
        public static string GetFirstSheetNameFromExcelFileName(string filepath, int numberSheetID)
        {
            if (!System.IO.File.Exists(filepath))
            {
                return "This file is on the sky!!!!!!!";
            }
            if (numberSheetID <= 1) { numberSheetID = 1; }
            try
            {
                Excel.Application obj = default(Excel.Application);
                Excel.Workbook objWB = default(Excel.Workbook);
                string strFirstSheetName = null;

                obj = (Excel.Application)Microsoft.VisualBasic.Interaction.CreateObject("Excel.Application", string.Empty);
                objWB = obj.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);

strFirstSheetName = ((Excel.Worksheet)objWB.Worksheets[1]).Name;

                objWB.Close(Type.Missing, Type.Missing, Type.Missing);
                objWB = null;
                obj.Quit();
                obj = null;
                return strFirstSheetName;
            }
            catch (Exception Err)
            {
                return Err.Message;
            }
        }
    }
}

可以参考微软的 "从 .NET 开发人员的角度理解 Excel 对象模型"