如何使用C＃读取Excel文件的数据？ [英] How to read data of an Excel file using C#?

问题描述

如何使用C＃读取Excel文件？我打开一个Excel文件进​​行阅读并将其复制到剪贴板以搜索电子邮件格式，但我不知道该怎么做。

How to read an Excel file using C#? I open an Excel file for reading and copy it to clipboard to search email format, but I don't know how to do it.

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();
            }
        }
    }
}

推荐答案

OK，

掌握Excel VSTO编程的一个更难的概念就是不要像单元格那样引用单元格， Worksheet [0] [0] 不会给你单元格A1，它会错误出现在你身上。即使当Excel打开时键入A1，您实际上将数据输入到范围A1。因此，您将单元格称为命名范围。这里有一个例子：

One of the more difficult concepts to grasp about Excel VSTO programming is that you don't refer to cells like an array, Worksheet[0][0] won't give you cell A1, it will error out on you. Even when you type into A1 when Excel is open, you are actually entering data into Range A1. Therefore you refer to cells as Named Ranges. Here's an example:

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)

如果你想做这样的事情：

If you want to do something like this:

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

     }

可能会有更好的方法，但这已经有效对于我来说，

There might be a better way, but this has worked for me.

您需要使用 Value2 而不是价值是因为 Value 属性是一个参数化的，C＃不支持它们。

The reason you need to use Value2 and not Value is because the Value property is a parametrized and C# doesn't support them yet.

As对于清理代码，我会发布，当我去工作tomor行，我没有与我的代码，但它是非常样板。您只需按照与创建它们相反的顺序关闭并释放对象。您不能使用 Using（）块，因为Excel.Application或Excel.Workbook不实现 IDisposable ，如果您不清理，您将在内存中留下悬挂的Excel对象。

As for the cleanup code, i will post that when i get to work tomorrow, i don't have the code with me, but it's very boilerplate. You just close and release the objects in the reverse order you created them. You can't use a Using() block because the Excel.Application or Excel.Workbook doesn't implement IDisposable, and if you don't clean-up, you will be left with a hanging Excel objects in memory.

注意：

• 如果您未设置可见性属性，Excel不会显示，这可能会令您的用户感到不安，但如果你想把数据撕掉，那可能就够了


• 你可以OleDb，也可以工作。

• If you don't set the Visibility property Excel doesn't display, which can be disconcerting to your users, but if you want to just rip the data out, that is probably good enough
• You could OleDb, that will work too.

我希望能让你开始，如果你需要进一步的澄清，让我知道。我会发布一个完整的

I hope that gets you started, let me know if you need further clarification. I'll post a complete

这里是一个完整的示例：

here is a complete sample:

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的功能，我也没有这个代码。

I'll post the functions from ExcelTools tomorrow, I don't have that code with me either.

编辑：
如所承诺的，这里是ExcelTools的函数您可能需要。

As promised, here are the Functions from ExcelTools you might need.

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＃，因为我没有写。 VB.NET的选项参数很好，C＃没有，所以Type.Missing。一旦你连续两次输入Type.Missing，你就会从房间里尖叫起来！

To be frank, this stuff is much easier if you use VB.NET. It's in C# because I didn't write it. VB.NET does option parameters well, C# does not, hence the Type.Missing. Once you typed Type.Missing twice in a row, you run screaming from the room!

至于你的问题，你可以尝试一下：

As for you question, you can try to following:

http://msdn.microsoft.com/en-us/library/microsoft.office.interop.excel.range.find（VS.80）.aspx

当我从我的会议室回来时，我会举一个例子。欢呼声

I will post an example when I get back from my meeting... cheers

编辑：这是一个例子

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

这是另一个例子，灵感来自于这个 site ：

Here is another example inspired by this site:

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

它有助于了解参数。

PS我是喜欢学习COM自动化的那些奇怪的人之一。所有这些代码都是从我为工作而编写的工具中蒸出来的，这需要我每个星期一从实验室处理1000多个电子表格。

P.S. I'm one of those weird people who enjoys learning COM automation. All this code steamed from a tool I wrote for work which required me to process over 1000+ spreadsheets from the lab each Monday.

这篇关于如何使用C＃读取Excel文件的数据？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！