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

Question

提问by ankush

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.

如何使用 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();
            }
        }
    }
}

Answer 1

回答by Chathuranga Chandrasekara

Why don't you create OleDbConnection? There are a lot of available resources in the Internet. Here is an example

为什么不创建 OleDbConnection？互联网上有很多可用资源。这是一个例子

OleDbConnection con = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source="+filename+";Extended Properties=Excel 8.0");
con.Open();
try
{
     //Create Dataset and fill with imformation from the Excel Spreadsheet for easier reference
     DataSet myDataSet = new DataSet();
     OleDbDataAdapter myCommand = new OleDbDataAdapter(" SELECT * FROM ["+listname+"$]" , con);
     myCommand.Fill(myDataSet);
     con.Close();
     richTextBox1.AppendText("\nDataSet Filled");

     //Travers through each row in the dataset
     foreach (DataRow myDataRow in myDataSet.Tables[0].Rows)
     {
          //Stores info in Datarow into an array
          Object[] cells = myDataRow.ItemArray;
          //Traverse through each array and put into object cellContent as type Object
          //Using Object as for some reason the Dataset reads some blank value which
          //causes a hissy fit when trying to read. By using object I can convert to
          //String at a later point.
          foreach (object cellContent in cells)
          {
               //Convert object cellContect into String to read whilst replacing Line Breaks with a defined character
               string cellText = cellContent.ToString();
               cellText = cellText.Replace("\n", "|");
               //Read the string and put into Array of characters chars
               richTextBox1.AppendText("\n"+cellText);
          }
     }
     //Thread.Sleep(15000);
}
catch (Exception ex)
{
     MessageBox.Show(ex.ToString());
     //Thread.Sleep(15000);
}
finally
{
     con.Close();
}

Answer 2

回答by Chris

OK,

好的，

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 VSTO 编程的更难掌握的概念之一是，您不会像数组一样引用单元格，Worksheet[0][0]也不会给您单元格 A1，它会在您身上出错。即使您在 Excel 打开时在 A1 中键入，您实际上也是在将数据输入到范围 A1 中。因此，您将单元格称为命名范围。下面是一个例子：

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

You can now literally type:

你现在可以直接输入：

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.

可能有更好的方法，但这对我有用。

The reason you need to use Value2and not Valueis because the Valueproperty is a parametrized and C# doesn't support them yet.

您需要使用Value2而不是使用的原因Value是因为该Value属性是参数化的并且 C# 尚不支持它们。

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.

至于清理代码，我会在明天上班时发布，我没有代码，但它非常样板。您只需按照与创建对象相反的顺序关闭和释放对象。你不能使用Using()块，因为 Excel.Application 或 Excel.Workbook 没有实现IDisposable，如果你不清理，你将在内存中留下一个挂起的 Excel 对象。

Note:

笔记：

If you don't set the Visibilityproperty 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.

如果您不设置VisibilityExcel 不显示属性，这可能会让您的用户感到不安，但如果您只想删除数据，那可能就足够了
你可以用 OleDb，那也行。

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

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

明天我将发布 ExcelTools 中的函数，我也没有那个代码。

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

编辑：正如所承诺的，这里是您可能需要的 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;
        }
    }

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!

坦率地说，如果您使用 VB.NET，这些东西会容易得多。它在 C# 中，因为我没有编写它。VB.NET 可以很好地处理选项参数，而 C# 则不能，因此 Type.Missing。一旦你连续输入两次 Type.Missing，你就会尖叫着跑出房间！

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

当我开完会回来时，我会发布一个例子......干杯

Edit: Here is an example

编辑：这是一个例子

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

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

It helps to understand the parameters.

它有助于理解参数。

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.

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

Answer 3

回答by Hoghweed

First of all, it's important to know what you mean by "open an Excel file for reading and copy it to clipboard..."

首先，重要的是要了解“打开 Excel 文件进行阅读并将其复制到剪贴板...”的含义。

This is very important because there are many ways you could do that depending just on what you intend to do. Let me explain:

这非常重要，因为您可以通过多种方式做到这一点，具体取决于您打算做什么。让我解释：

If you want to read a set of data and copy that in the clipboard and you know the data format (e.g. column names), I suggest you use an OleDbConnectionto open the file, this way you can treat the xls file content as a Database Table, so you can read data with SQL instruction and treat the data as you want.
If you want to do operations on the data with the Excel object model then open it in the way you began.
Some time it's possible to treat an xls file as a kind of csv file, there are tools like File Helperswhich permit you to treat and open an xls file in a simple way by mapping a structure on an arbitrary object.

如果您想读取一组数据并将其复制到剪贴板中并且您知道数据格式（例如列名），我建议您使用OleDbConnection打开文件，这样您就可以将 xls 文件内容视为数据库表，因此您可以使用 SQL 指令读取数据并根据需要处理数据。
如果要使用 Excel 对象模型对数据进行操作，请按照开始的方式打开它。
有时可以将 xls 文件视为一种 csv 文件，有像File Helpers这样的工具允许您通过在任意对象上映射结构以简单的方式处理和打开 xls 文件。

Another important point is in which Excel version the file is.

另一个重点是文件所在的 Excel 版本。

I have, unfortunately I say, a strong experience working with Office automation in all ways, even if bounded in concepts like Application Automation, Data Management and Plugins, and generally I suggest only as the last resort, to using Excel automation or Office automation to read data; just if there aren't better ways to accomplish that task.

不幸的是，我在所有方面都拥有使用 Office 自动化的丰富经验，即使在应用程序自动化、数据管理和插件等概念方面也有限制，而且通常我只建议作为最后的手段，使用 Excel 自动化或 Office 自动化来读取数据；如果没有更好的方法来完成这项任务。

Working with automation could be heavy in performance, in terms of resource cost, could involve in other issues related for example to security and more, and last but not at least, working with COM interop it's not so "free".. So my suggestion is think and analyze the situation within your needs and then take the better way.

就资源成本而言，使用自动化可能会影响性能，可能涉及与安全等相关的其他问题，最后但并非最不重要的是，使用 COM 互操作并不是那么“免费”.. 所以我的建议是在您的需求范围内思考和分析情况，然后采取更好的方式。

Answer 4

回答by Green goblin

You can use Microsoft.Office.Interop.Excelassembly to process excel files.

您可以使用Microsoft.Office.Interop.Excel程序集来处理 excel 文件。

Right click on your project and go to Add reference. Add the Microsoft.Office.Interop.Excel assembly.
Include using Microsoft.Office.Interop.Excel;to make use of assembly.

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

Here is the sample code:

这是示例代码：

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

Answer 5

回答by Suganth G

Use OLEDB Connection to communicate with excel files. it gives better result

使用 OLEDB 连接与 excel 文件进行通信。它给出了更好的结果

using System.Data.OleDb;



                string physicalPath = "Your Excel file physical path";
                OleDbCommand cmd = new OleDbCommand();
                OleDbDataAdapter da = new OleDbDataAdapter();
                DataSet ds = new DataSet();
                String strNewPath = physicalPath;
                String connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + strNewPath + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\"";
                String query = "SELECT * FROM [Sheet1$]"; // You can use any different queries to get the data from the excel sheet
                OleDbConnection conn = new OleDbConnection(connString);
                if (conn.State == ConnectionState.Closed) conn.Open();
                try
                {
                    cmd = new OleDbCommand(query, conn);
                    da = new OleDbDataAdapter(cmd);
                    da.Fill(ds);

                }
                catch
                {
                    // Exception Msg 

                }
                finally
                {
                    da.Dispose();
                    conn.Close();
                }

The Output data will be stored in dataset, using the dataset object you can easily access the datas. Hope this may helpful

输出数据将存储在数据集中，使用数据集对象您可以轻松访问数据。希望这可能会有所帮助

Answer 6

回答by RandyMohan

Excel File Reader & Writer Without Excel On u'r System

在你的系统上没有 Excel 的 Excel 文件阅读器和编写器

Download and add the dll for NPOIu'r project.

Using this code to read a excel file.

        using (FileStream file = new FileStream(filePath, FileMode.Open, FileAccess.Read, FileShare.ReadWrite))
        {
           XSSFWorkbook XSSFWorkbook = new XSSFWorkbook(file);
        }
        ISheet objxlWorkSheet = XSSFWorkbook.GetSheetAt(0);
        int intRowCount = 1;
        int intColumnCount = 0;
        for (; ; )
        {
            IRow Row = objxlWorkSheet.GetRow(intRowCount);
            if (Row != null)
            {
                ICell Cell = Row.GetCell(0);
                ICell objCell = objxlWorkSheet.GetRow(intRowCount).GetCell(intColumnCount); }}

下载并添加NPOI项目的 dll 。

使用此代码读取 excel 文件。

        using (FileStream file = new FileStream(filePath, FileMode.Open, FileAccess.Read, FileShare.ReadWrite))
        {
           XSSFWorkbook XSSFWorkbook = new XSSFWorkbook(file);
        }
        ISheet objxlWorkSheet = XSSFWorkbook.GetSheetAt(0);
        int intRowCount = 1;
        int intColumnCount = 0;
        for (; ; )
        {
            IRow Row = objxlWorkSheet.GetRow(intRowCount);
            if (Row != null)
            {
                ICell Cell = Row.GetCell(0);
                ICell objCell = objxlWorkSheet.GetRow(intRowCount).GetCell(intColumnCount); }}

Answer 7

回答by Kalpesh

The recommended way to read Excel files on server side app is Open XML.

在服务器端应用程序上读取 Excel 文件的推荐方法是 Open XML。

Sharing few links -

分享几个链接 -

https://msdn.microsoft.com/en-us/library/office/hh298534.aspx

https://msdn.microsoft.com/en-us/library/office/ff478410.aspx

https://msdn.microsoft.com/en-us/library/office/cc823095.aspx

Answer 8

回答by abcd

public void excelRead(string sheetName)
        {
            Excel.Application appExl = new Excel.Application();
            Excel.Workbook workbook = null;
            try
            {
                string methodName = "";


                Excel.Worksheet NwSheet;
                Excel.Range ShtRange;

                //Opening Excel file(myData.xlsx)
                appExl = new Excel.Application();


                workbook = appExl.Workbooks.Open(sheetName, Missing.Value, ReadOnly: false);
                NwSheet = (Excel.Worksheet)workbook.Sheets.get_Item(1);
                ShtRange = NwSheet.UsedRange; //gives the used cells in sheet


                int rCnt1 = 0;
                int cCnt1 = 0;

                for (rCnt1 = 1; rCnt1 <= ShtRange.Rows.Count; rCnt1++)
                {
                    for (cCnt1 = 1; cCnt1 <= ShtRange.Columns.Count; cCnt1++)
                    {
                        if (Convert.ToString(NwSheet.Cells[rCnt1, cCnt1].Value2) == "Y")
                        {

                            methodName = NwSheet.Cells[rCnt1, cCnt1 - 2].Value2;
                            Type metdType = this.GetType();
                            MethodInfo mthInfo = metdType.GetMethod(methodName);

                            if (Convert.ToString(NwSheet.Cells[rCnt1, cCnt1 - 2].Value2) == "fn_AddNum" || Convert.ToString(NwSheet.Cells[rCnt1, cCnt1 - 2].Value2) == "fn_SubNum")
                            {
                                StaticVariable.intParam1 = Convert.ToInt32(NwSheet.Cells[rCnt1, cCnt1 + 3].Value2);
                                StaticVariable.intParam2 = Convert.ToInt32(NwSheet.Cells[rCnt1, cCnt1 + 4].Value2);
                                object[] mParam1 = new object[] { StaticVariable.intParam1, StaticVariable.intParam2 };
                                object result = mthInfo.Invoke(this, mParam1);
                                StaticVariable.intOutParam1 = Convert.ToInt32(result);
                                NwSheet.Cells[rCnt1, cCnt1 + 5].Value2 = Convert.ToString(StaticVariable.intOutParam1) != "" ? Convert.ToString(StaticVariable.intOutParam1) : String.Empty;
                            }

                            else
                            {
                                object[] mParam = new object[] { };
                                mthInfo.Invoke(this, mParam);

                                NwSheet.Cells[rCnt1, cCnt1 + 5].Value2 = StaticVariable.outParam1 != "" ? StaticVariable.outParam1 : String.Empty;
                                NwSheet.Cells[rCnt1, cCnt1 + 6].Value2 = StaticVariable.outParam2 != "" ? StaticVariable.outParam2 : String.Empty;
                            }
                            NwSheet.Cells[rCnt1, cCnt1 + 1].Value2 = StaticVariable.resultOut;
                            NwSheet.Cells[rCnt1, cCnt1 + 2].Value2 = StaticVariable.resultDescription;
                        }

                        else if (Convert.ToString(NwSheet.Cells[rCnt1, cCnt1].Value2) == "N")
                        {
                            MessageBox.Show("Result is No");
                        }
                        else if (Convert.ToString(NwSheet.Cells[rCnt1, cCnt1].Value2) == "EOF")
                        {
                            MessageBox.Show("End of File");
                        }

                    }
                }

                workbook.Save();
                workbook.Close(true, Missing.Value, Missing.Value);
                appExl.Quit();
                System.Runtime.InteropServices.Marshal.FinalReleaseComObject(ShtRange);
                System.Runtime.InteropServices.Marshal.FinalReleaseComObject(NwSheet);
                System.Runtime.InteropServices.Marshal.FinalReleaseComObject(workbook);
                System.Runtime.InteropServices.Marshal.FinalReleaseComObject(appExl);
            }
            catch (Exception)
            {
                workbook.Close(true, Missing.Value, Missing.Value);
            }
            finally
            {
                GC.Collect();
                GC.WaitForPendingFinalizers();
                System.Runtime.InteropServices.Marshal.CleanupUnusedObjectsInCurrentContext();
            }
        }

//code for reading excel data in datatable
public void testExcel(string sheetName)
        {
            try
            {
                MessageBox.Show(sheetName);

                foreach(Process p in Process.GetProcessesByName("EXCEL"))
                {
                    p.Kill();
                }
                //string fileName = "E:\inputSheet";
                Excel.Application oXL;
                Workbook oWB;
                Worksheet oSheet;
                Range oRng;


                //  creat a Application object
                oXL = new Excel.Application();




                //   get   WorkBook  object
                oWB = oXL.Workbooks.Open(sheetName);


                //   get   WorkSheet object
                oSheet = (Microsoft.Office.Interop.Excel.Worksheet)oWB.Sheets[1];
                System.Data.DataTable dt = new System.Data.DataTable();
                //DataSet ds = new DataSet();
                //ds.Tables.Add(dt);
                DataRow dr;


                StringBuilder sb = new StringBuilder();
                int jValue = oSheet.UsedRange.Cells.Columns.Count;
                int iValue = oSheet.UsedRange.Cells.Rows.Count;


                //  get data columns
                for (int j = 1; j <= jValue; j++)
                {
                    oRng = (Microsoft.Office.Interop.Excel.Range)oSheet.Cells[1, j];
                    string strValue = oRng.Text.ToString();
                    dt.Columns.Add(strValue, System.Type.GetType("System.String"));
                }


                //string colString = sb.ToString().Trim();
                //string[] colArray = colString.Split(':');


                //  get data in cell
                for (int i = 2; i <= iValue; i++)
                {
                    dr = dt.NewRow();
                    for (int j = 1; j <= jValue; j++)
                    {
                        oRng = (Microsoft.Office.Interop.Excel.Range)oSheet.Cells[i, j];
                        string strValue = oRng.Text.ToString();
                        dr[j - 1] = strValue;


                    }
                    dt.Rows.Add(dr);
                }
                if(StaticVariable.dtExcel != null)
                {
                    StaticVariable.dtExcel.Clear();
                    StaticVariable.dtExcel = dt.Copy();
                }
                else
                StaticVariable.dtExcel = dt.Copy();

                oWB.Close(true, Missing.Value, Missing.Value);
                oXL.Quit();
                MessageBox.Show(sheetName);

            }

            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            finally
            {

            }
        }

//code for class initialize
 public static void startTesting(TestContext context)
        {

            Playback.Initialize();
            ReadExcel myClassObj = new ReadExcel();
            string sheetName="";
            StreamReader sr = new StreamReader(@"E:\SaveSheetName.txt");
            sheetName = sr.ReadLine();
            sr.Close();
            myClassObj.excelRead(sheetName);
            myClassObj.testExcel(sheetName);
        }

//code for test initalize
public  void runValidatonTest()
        {

            DataTable dtFinal = StaticVariable.dtExcel.Copy();
            for (int i = 0; i < dtFinal.Rows.Count; i++)
            {
                if (TestContext.TestName == dtFinal.Rows[i][2].ToString() && dtFinal.Rows[i][3].ToString() == "Y" && dtFinal.Rows[i][4].ToString() == "TRUE")
                {
                    MessageBox.Show(TestContext.TestName);
                    MessageBox.Show(dtFinal.Rows[i][2].ToString());
                    StaticVariable.runValidateResult = "true";
                    break;
                }
            }
            //StaticVariable.dtExcel = dtFinal.Copy();
        }

Answer 9

回答by Vishal Kotak

try
        {
            DataTable sheet1 = new DataTable("Excel Sheet");
            OleDbConnectionStringBuilder csbuilder = new OleDbConnectionStringBuilder();
            csbuilder.Provider = "Microsoft.ACE.OLEDB.12.0";
            csbuilder.DataSource = fileLocation;
            csbuilder.Add("Extended Properties", "Excel 12.0 Xml;HDR=YES");
            string selectSql = @"SELECT * FROM [Sheet1$]";
            using (OleDbConnection connection = new OleDbConnection(csbuilder.ConnectionString))
            using (OleDbDataAdapter adapter = new OleDbDataAdapter(selectSql, connection))
            {
                connection.Open();
                adapter.Fill(sheet1);
            }
        }
        catch (Exception e)
        {
            Console.WriteLine(e.Message);
        }

This worked for me. Please try it and let me know for queries.

这对我有用。请尝试一下，让我知道查询。

Answer 10

回答by MetalxBeat

I'd recommend you to use Bytescout Spreadsheet.

我建议您使用 Bytescout 电子表格。

https://bytescout.com/products/developer/spreadsheetsdk/bytescoutspreadsheetsdk.html

I tried it with Monodevelop in Unity3D and it is pretty straight forward. Check this sample code to see how the library works:

我在 Unity3D 中使用 Monodevelop 进行了尝试，它非常简单。检查此示例代码以了解库的工作原理：

https://bytescout.com/products/developer/spreadsheetsdk/read-write-excel.html

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

提问by ankush

回答by Chathuranga Chandrasekara

回答by Chris

回答by Hoghweed

回答by Green goblin

回答by Suganth G

回答by RandyMohan

回答by Kalpesh

回答by abcd

回答by Vishal Kotak

回答by MetalxBeat

相关推荐

最近更新

标签

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

提问by ankush

回答by Chathuranga Chandrasekara

回答by Chris

回答by Hoghweed

回答by Green goblin

回答by Suganth G

回答by RandyMohan

回答by Kalpesh

回答by abcd

回答by Vishal Kotak

回答by MetalxBeat

相关推荐

C# 在 MonoDevelop 中读取控制台输入

使用 C# 对 MySQL 进行参数化查询

C# 如何获取程序集的命名空间？

“无法在 dll 中找到名为 [function] 的入口点”（c++ 到 c# 类型转换）

相关推荐

最近更新

标签