打开Excel，解析数据? [英] Open Excel, Parse Data?

问题描述

(替代标题:适用于Excel的ReadAllLines Analogue ::加载和处理Excel数据的最佳方法是什么?)

(Alternate Title: ReadAllLines Analogue for Excel?: What's the Best Way to Load and Manipulate Excel Data?)

我想快速打开一个Excel工作表并执行文本操作.我希望该操作像ReadAllLines( https: //msdn.microsoft.com/zh-CN/library/s2tte0y1(v=vs.110).aspx )，但适用于Excel.

I would like to quickly crack open an excel sheet and perform text manipulations. I want the operation to work like ReadAllLines (https://msdn.microsoft.com/en-us/library/s2tte0y1(v=vs.110).aspx) but for Excel.

我发现以下问题是正确的，但已有七年历史了. 从C#读取Excel文件(此外，这是一个历史性的重大问题，被冻结了.此外，我没有50分，因此如果打开它就无法发表评论.)我将Robin Robinson的答案剪切并粘贴到Visual Studio中，仅更改了路径:

I found the following question which is on point but seven years old. Reading Excel files from C# (Furthermore, it is an historically significant question that is frozen. Moreover, I do not have 50 points so I would not be able to comment were it open.) I cut and pasted Robin Robinson's answer into Visual Studio, changing only the path:

var fileName = string.Format("{0}\\fileNameHere", Directory.GetCurrentDirectory());
var connectionString = string.Format("Provider=Microsoft.Jet.OLEDB.4.0; data source={0}; Extended Properties=Excel 8.0;", fileName);

var adapter = new OleDbDataAdapter("SELECT * FROM [workSheetNameHere$]", connectionString);
var ds = new DataSet();

adapter.Fill(ds, "anyNameHere");

var data = ds.Tables["anyNameHere"].AsEnumerable();

var query = data.Where(x => x.Field<string>("id") != string.Empty).Select(x =>
            new 
            {
                id= x.Field<string>("id"),
            });

在外部表格格式不正确"的Fill方法上失败.

That fails on the Fill method with 'External table is not in the expected format.'

今天好的程序员是这样吗?也就是说，我应该调查Jet并查看是否有针对最新Excel的更新，还是有新的改进方法?

Are good programmers doing it this way today? i.e., should I investigate Jet and see if there are updates for the latest Excel, or is there a new and improved way?

推荐答案

这很快就可以轻松实现；我不能说我没有使用ReadAllLines和Regex进行文本操作. (删除了文本操作的详细信息.)

This is lightening fast; I can't tell I'm not doing text manipulations with ReadAllLines and Regex. (Removed the text manipulation details).

原始问题的注释中说明了我的工作方式.

How I got it to work is explained in the comments to the original question.

不确定为什么它比Interop.Excel快得多.编码效率低下?更有效的API?

Not sure why it's so much faster than Interop.Excel. Inefficient coding? More efficient API?

任何见识都受到赞赏！

        var path = string.Format(@"C:\Users\jlambert\Desktop\encryptedSSNs.xlsx");
        var connStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties=Excel 12.0;";


        var adapter = new OleDbDataAdapter("SELECT * FROM [sheetName$]", connStr);
        var ds = new DataSet();

        adapter.Fill(ds, "anyNameHere");

        var data = ds.Tables["anyNameHere"].AsEnumerable();

        var query = data.Where(x => x.Field<string>("MRN") != string.Empty).Select(x =>
            new 
            {
                mrn = x.Field<string>("MRN"),
                ssn = x.Field<string>("ssn"),
            });

        foreach (var q in query)
        {
            Console.WriteLine(q);    
        }
        Console.ReadLine();

这篇关于打开Excel，解析数据?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！