的Microsoft.Office.Interop.Excel或EPPlus进行读一个巨大的（或没有）Excel文件 [英] Microsoft.Office.Interop.Excel or EPPlus for read a huge (or not) Excel file

问题描述

我写了一个code从Excel文件读取列。我用的Microsoft.Office.Interop.Excel在此，先读取整个范围，然后在那之后写的System.Array我做的System.Array中值某些操作，最后我把它转换到名单，因为我填一个列表框元素。这是code（仅适用于相关的部分）：

I wrote a code to read a column from a Excel file. I use Microsoft.Office.Interop.Excel on this, first read the entire Range and then write in System.Array after that I do some operations with the System.Array values and finally I convert it to List because I fill a ListBox element. This is the code (only relevant parts):

private List<string> bd = new List<string>();
private static System.Array objRowAValues;

private List<string> bl = new List<string>();
private static System.Array objRowBValues;

private List<string> cm = new List<string>();
private static System.Array objRowCValues;

private List<string> pl = new List<string>();
private List<string> bdCleanList;
private static Microsoft.Office.Interop.Excel.Application appExcel;

Excel.Application xlApp;
Excel.Workbook xlWorkBook;
Excel.Worksheet xlWorkSheet;
Excel.Range rngARowLast, rngBRowLast, rngCRowLast;

long lastACell, lastBCell, lastCCell, fullRow;

private void btnCargarExcel_Click(object sender, EventArgs e)
    {
        if (this.openFileDialog1.ShowDialog() == DialogResult.OK)
        {
            if (System.IO.File.Exists(openFileDialog1.FileName))
            {
                Stopwatch stopWatch = new Stopwatch();
                stopWatch.Start();
                Thread.Sleep(10000);

                filePath.Text = openFileDialog1.FileName.ToString();

                xlApp = new Microsoft.Office.Interop.Excel.Application();
                xlWorkBook = xlApp.Workbooks.Open(openFileDialog1.FileName, 0, true, 5, "", "", true,
                                                  Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false,
                                                  false, 0, true, 1, 0);
                xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);

                fullRow = xlWorkSheet.Rows.Count;
                lastACell = xlWorkSheet.Cells[fullRow, 1].End(Excel.XlDirection.xlUp).Row;
                rngARowLast = xlWorkSheet.get_Range("A1", "A" + lastACell);
                objRowAValues = (System.Array)rngARowLast.Cells.Value;

                foreach (object elem in objRowAValues)
                {
                    if (elem != "")
                    {
                        bd.Add(cleanString(elem.ToString(), 10));
                    }
                }

                nrosProcesados.Text = bd.Count().ToString();
                listBox1.DataSource = bd;

                xlWorkBook.Close(true, null, null);
                xlApp.Quit();

                releaseObject(xlWorkSheet);
                releaseObject(xlWorkBook);
                releaseObject(xlApp);

                stopWatch.Stop();

                TimeSpan ts = stopWatch.Elapsed;
                executiontime.Text =
                    String.Format("{0:00}:{1:00}:{2:00}.{3:00}", ts.Hours, ts.Minutes, ts.Seconds,
                                  ts.Milliseconds / 10).ToString();
            }
            else
            {
                MessageBox.Show("No se pudo abrir el fichero!");
                System.Runtime.InteropServices.Marshal.ReleaseComObject(appExcel);
                appExcel = null;
                System.Windows.Forms.Application.Exit();
            }
        }
    }

我测试用的Excel文件〜800个细胞，不到2分钟。然后，我测试样本EPPlus，比我的方法更快，所以我觉得在使用EPPlus代替的Microsoft.Office.Interop.Excel我也认为在使用OpenXML的SDK（但无法找到任何例子适合自己的目标，所以我离开现在）。在这个例子中，他们使用这个code从Excel文件读取：

I test with a Excel file with ~800 000 cells and take less than 2 minutes. Then I test samples from EPPlus and are faster than my approach so I think in use EPPlus instead of Microsoft.Office.Interop.Excel I think also in use OpenXML SDK (but can't find any example to suite my goals so I leave for now). In the example they use this code to read from a Excel file:

ExcelWorksheet sheet = package.Workbook.Worksheets[1];

var query1= (from cell in sheet.Cells["d:d"] where cell.Value is double && (double)cell.Value >= 9990 && (double)cell.Value <= 10000 select cell);

当然，他们使用LINQ在这里，但我的问题关于这个话题有：

of course they use LINQ here, but my questions regarding this topic are:

• 在没有你的使用？哪种方法
• 什么是对这个您的建议？
• 任何帮助，使用EPPlus或OpenXML的SDK？
编写相同

我是新手，在C＃中的世界从PHP的世界即将到来，这是我的第一个项目

I'm newbie in C# world coming from PHP world and this is my first project

推荐答案

没你使用哪种方法？ -EPPlus

Which approach did yours use? -EPPlus

你对这个建议？ -I've发现EPPLus是非常快。这也是一个简单的API来在我看来工作。由于多种原因，其中之一是缺乏COM互操作（包括速度和易用性）的。也有少的要求，尤其是部署到服务器环境时：没有安装Excel的垃圾

What are your recommendations on this? -I've found EPPLus to be hugely faster. It is also an easier API to work with in my opinion. For many reasons, one being the lack of COM interop(both for speed and ease of use). Also has less requirements, especially when deploying to a server environment: no installing Excel junk.

任何帮助，使用EPPlus或OpenXML的SDK写的一样吗？ -EPPlus API是相当straightfoward。做一个尝试，与你试过到目前为止发布更具体的问题。

Any help to write the same using EPPlus or OpenXML SDK? -EPPlus API is fairly straightfoward. Make an attempt and post more specific questions with what you've tried so far.

通过细胞的另一种方式循环：

Another way to loop through cells:

var firstColumnRows = sheet.Cells["A2:A"];

// Loop through rows in the first column, get values based on offset
foreach (var cell in firstColumnRows)
{
    var column1CellValue = cell.GetValue<string>();
    var neighborCellValue = cell.Offset(0, 1).GetValue<string>();
}

这篇关于的Microsoft.Office.Interop.Excel或EPPlus进行读一个巨大的（或没有）Excel文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！