如何使用C#读取Excel文件的数据?

内容来源于 Stack Overflow,并遵循CC BY-SA 3.0许可协议进行翻译与使用

  • 回答 (2)
  • 关注 (0)
  • 查看 (272)

如何使用C#读取Excel文件?我打开一个Excel文件来读取并复制到剪贴板上搜索电子邮件格式,但是我不知道如何去做。

FileInfo finfo;
Excel.ApplicationClass ExcelObj = new Excel.ApplicationClass();
ExcelObj.Visible = false;

Excel.Workbook theWorkbook;
Excel.Worksheet worksheet;

if (listView1.Items.Count > 0)
{
    foreach (ListViewItem s in listView1.Items)
    {
        finfo = new FileInfo(s.Text);
        if (finfo.Extension == ".xls" || finfo.Extension == ".xlsx" || finfo.Extension == ".xlt" || finfo.Extension == ".xlsm" || finfo.Extension == ".csv")
        {
            theWorkbook = ExcelObj.Workbooks.Open(s.Text, 0, true, 5, "", "", true, Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, false, false);

            for (int count = 1; count <= theWorkbook.Sheets.Count; count++)
            {
                worksheet = (Excel.Worksheet)theWorkbook.Worksheets.get_Item(count);
                worksheet.Activate();
                worksheet.Visible = false;
                worksheet.UsedRange.Cells.Select();
            }
        }
    }
}
提问于
用户回答回答于

你可以用Microsoft.Office.Interop.Excel程序集处理EXCEL文件。

  1. 右键单击项目并转到Add reference...。添加Microsoft.Office.Interop.Excel程序集。
  2. 包括using Microsoft.Office.Interop.Excel;利用装配

下面是示例代码:

    using Microsoft.Office.Interop.Excel;

    //create the Application object we can use in the member functions.
    Microsoft.Office.Interop.Excel.Application _excelApp = new Microsoft.Office.Interop.Excel.Application();
    _excelApp.Visible = true;

    string fileName = "C:\\sampleExcelFile.xlsx";

    //open the workbook
    Workbook workbook = _excelApp.Workbooks.Open(fileName,
        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);

    //select the first sheet        
    Worksheet worksheet = (Worksheet)workbook.Worksheets[1];

    //find the used range in worksheet
    Range excelRange = worksheet.UsedRange;

    //get an object array of all of the cells in the worksheet (their values)
    object[,] valueArray = (object[,])excelRange.get_Value(
                XlRangeValueDataType.xlRangeValueDefault);

    //access the cells
    for (int row = 1;  row <= worksheet.UsedRange.Rows.Count; ++row)
    {
        for (int col = 1; col <= worksheet.UsedRange.Columns.Count; ++col)
        {
            //access each cell
            Debug.Print(valueArray[row, col].ToString());
        }
    }

    //clean up stuffs
    workbook.Close(false, Type.Missing, Type.Missing);
    Marshal.ReleaseComObject(workbook);

    _excelApp.Quit();
    Marshal.FinalReleaseComObject(_excelApp);
用户回答回答于

ExcelVSTO编程中最难理解的概念之一是,不像数组那样引用单元格,Worksheet[0][0]不会给你单元格A1,它会错误地出现在你身上。即使在打开Excel时键入A1,实际上也是将数据输入范围A1。因此,可以将单元格称为Range。下面是一个例子:

Excel.Worksheet sheet = workbook.Sheets["Sheet1"] as Excel.Worksheet; 
Excel.Range range = sheet.get_Range("A1", Missing.Value)

现在可以按字面顺序键入:

range.Text // this will give you the text the user sees
range.Value2 // this will give you the actual value stored by Excel (without rounding)

如果你想做这样的事:

Excel.Range range = sheet.get_Range("A1:A5", Missing.Value)

if (range1 != null)
     foreach (Excel.Range r in range1)
     {
         string user = r.Text
         string value = r.Value2

     }

也许有更好的方法,但这对我有用。

你需要使用的原因Value2而不是Value是因为Value属性是参数化的,C#还不支持它们。

注:

  • 如果不设置Visibility属性Excel不显示,这可能会使用户感到不安,但是如果想要直接提取数据,那可能就足够了。
  • 你可以用OleDb,这样也行。

以下是一个完整的示例:

using System;
using System.IO;
using System.Reflection;
using NUnit.Framework;
using ExcelTools = Ms.Office;
using Excel = Microsoft.Office.Interop.Excel;

namespace Tests
{
    [TestFixture]
    public class ExcelSingle
    {
        [Test]
        public void ProcessWorkbook()
        {
            string file = @"C:\Users\Chris\Desktop\TestSheet.xls";
            Console.WriteLine(file);

            Excel.Application excel = null;
            Excel.Workbook wkb = null;

            try
            {
                excel = new Excel.Application();

                wkb = ExcelTools.OfficeUtil.OpenBook(excel, file);

                Excel.Worksheet sheet = wkb.Sheets["Data"] as Excel.Worksheet;

                Excel.Range range = null;

                if (sheet != null)
                    range = sheet.get_Range("A1", Missing.Value);

                string A1 = String.Empty;

                if( range != null )
                    A1 = range.Text.ToString();

                Console.WriteLine("A1 value: {0}", A1);

            }
            catch(Exception ex)
            {
                //if you need to handle stuff
                Console.WriteLine(ex.Message);
            }
            finally
            {
                if (wkb != null)
                    ExcelTools.OfficeUtil.ReleaseRCM(wkb);

                if (excel != null)
                    ExcelTools.OfficeUtil.ReleaseRCM(excel);
            }
        }
    }
}

明天我会发布ExcelTools的函数,我也没有那个代码。

编辑:正如承诺的,下面是您可能需要的ExcelTools函数。

public static Excel.Workbook OpenBook(Excel.Application excelInstance, string fileName, bool readOnly, bool editable,
        bool updateLinks) {
        Excel.Workbook book = excelInstance.Workbooks.Open(
            fileName, updateLinks, readOnly,
            Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
            Type.Missing, editable, Type.Missing, Type.Missing, Type.Missing,
            Type.Missing, Type.Missing);
        return book;
    }

public static void ReleaseRCM(object o) {
        try {
            System.Runtime.InteropServices.Marshal.ReleaseComObject(o);
        } catch {
        } finally {
            o = null;
        }
    }

坦率地说,如果你使用VB.NET,这个东西就容易多了。它是用C#写的,因为它不是我写的。NET很好地选择参数,C#没有,因此类型错误。

下面是一个例子

range = sheet.Cells.Find("Value to Find",
                                                 Type.Missing,
                                                 Type.Missing,
                                                 Type.Missing,
                                                 Type.Missing,
                                                 Excel.XlSearchDirection.xlNext,
                                                 Type.Missing,
                                                 Type.Missing, Type.Missing);

range.Text; //give you the value found

下面是另一个

 range = sheet.Cells.Find("Value to find", Type.Missing, Type.Missing,Excel.XlLookAt.xlWhole,Excel.XlSearchOrder.xlByColumns,Excel.XlSearchDirection.xlNext,false, false, Type.Missing);

它有助于理解参数。

扫码关注云+社区