快速读取Excel中的数据

本文关键字：数据 Excel 读取 | 更新日期: 2023-09-27 18:06:27

我有下面的代码从Excel工作表读取数据，并将其转换为管道分隔的文本文件。它的工作原理。问题是它相当慢，因为我必须一次读取一个单元格，以便在管道中添加。

我想知道是否有更好的方法来做到这一点，即在一步中将数据读入内存/数组并在那里采取行动。

public string Process(string filename)
        {
            Excel.Application xlApp;
            Excel.Workbook xlWorkBook;
            Excel.Worksheet xlWorkSheet;
            Excel.Range range;
            string str = "";
            int rCnt = 0;
            int cCnt = 0;
            object misValue = System.Reflection.Missing.Value;
            xlApp = new Excel.Application();
            xlWorkBook = xlApp.Workbooks.Open(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); //Use the 1st worksheet
            StreamWriter sw = new StreamWriter(destpath);
            range = xlWorkSheet.UsedRange;
            for (rCnt = 1; rCnt <= range.Rows.Count; rCnt++)
            {
                if ((rCnt % 1000) == 0)
                {
                    txtProgress.Text = "Rows processed: "+ rCnt;
                }
                for (cCnt = 1; cCnt <= range.Columns.Count; cCnt++)
                {
                    str = str + ToStr((range.Cells[rCnt, cCnt] as Excel.Range).Value2) + "|";
                }
                sw.WriteLine(str);
                str = "";
            }
            xlWorkBook.Close(true, null, null);
            xlApp.Quit();
            sw.Close();
            releaseObject(xlWorkSheet);
            releaseObject(xlWorkBook);
            releaseObject(xlApp);
            MessageBox.Show("Complete","Status");
            return "Success";
        }
 public static string ToStr(object readField)
        {
            if ((readField != null))
            {
                if (readField.GetType() != typeof(System.DBNull))
                {
                    return Convert.ToString(readField);
                }
                else
                {
                    return "";
                }
            }
            else
            {
                return "";
            }
        }
 private void releaseObject(object obj)
        {
            try
            {
                System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
                obj = null;
            }
            catch (Exception ex)
            {
                obj = null;
                MessageBox.Show("Unable to release the Object " + ex.ToString());
            }
            finally
            {
                GC.Collect();
            }
        }

快速读取Excel中的数据

如果你计划只在excel文件内容上执行读取，我建议你使用ExcelDataReader库链接，它将工作表数据提取到数据集对象中。

    static void Main(string[] args)
    {
        IExcelDataReader reader = null;
        string FilePath = "PathToExcelFile";
        //Load file into a stream
        FileStream stream = File.Open(FilePath, FileMode.Open, FileAccess.Read);
        //Must check file extension to adjust the reader to the excel file type
        if (System.IO.Path.GetExtension(FilePath).Equals(".xls"))
        {
            reader = ExcelReaderFactory.CreateBinaryReader(stream);
        }
        else if (System.IO.Path.GetExtension(FilePath).Equals(".xlsx"))
        {
            reader = ExcelReaderFactory.CreateBinaryReader(stream);
        }
        if (reader != null)
        {
            //Fill DataSet
            System.Data.DataSet result = reader.AsDataSet();
            try
            {
                //Loop through rows for the desired worksheet
                //In this case I use the table index "0" to pick the first worksheet in the workbook
                foreach (DataRow row in result.Tables[0].Rows)
                {
                    string FirstColumn = row[0].ToString();
                }
            }
            catch
            {
            }
        }
    }

可以使用LinqToExcel的nuget包

https://www.nuget.org/packages/LinqToExcel/1.10.1

示例代码:

string path = @"Users.xlsx";
var excel = new ExcelQueryFactory(path);
return (from c in excel.Worksheet<User>()
    select c).OrderBy(a => a.Name).ToList();

相关文章：