读取Excel单元格需要花费大量时间

本文关键字：时间 Excel 单元格 读取 | 更新日期: 2023-09-27 18:24:41

我有一个Excel文件，它有360行181列（65160个单元格）。大多数单元格都是空的，我只想保存非空单元格。

主要问题是，当我运行代码时，处理所有单元格大约需要六分钟，而这个过程应该针对3000个Excel文件完成。我在form_load事件中写了以下代码：

        Excel.Application xlapp;
        Excel.Workbook xlwb;
        Excel.Worksheet xlws;
        object misValue = System.Reflection.Missing.Value;
        xlapp = new Excel.Application();
        xlwb = xlapp.Workbooks.Open("test.xlsx", 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "'t", false, false, 0, true, 1, 0);
        xlws = (Excel.Worksheet)xlwb.Worksheets.get_Item(1);
        int i = 0;
        int j = 0;
        Int32 last_row = Convert.ToInt32(xlws.UsedRange.Rows.Count);
        Int32 last_column = Convert.ToInt32(xlws.UsedRange.Columns.Count);
        label1.Text = "";
        for ( j = 1; j <=last_row; j++)
        {
            for ( i = 1; i <= last_column; i++)
            {
                if (xlws.Cells[j,i].value != null)
                {
                    label1.Text = label1.Text + xlws.Cells[j, i].value.ToString();
                }
            }
            label1.Text += "'n";
        }
        xlwb.Close(true, misValue, misValue);
        xlapp.Quit();
        releaseObject(xlws);
        releaseObject(xlwb);
        releaseObject(xlapp);

这是我的releaseObject函数：

        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工作簿。有几种方法可以做到这一点。将工作簿视为数据源并使用OleDb进行查询效果良好。根据我的经验，它比使用Excel Automation要快得多。以下是一些来自现有项目的示例代码。它查询包含两个电子表格的工作簿，一个用于公制单位，另一个用于英制单位。希望它能帮助。。。

// get Excel file that should be imported
string strFilePath = "";
this.openFileDialog.Title = "Select Excel File For Importing";
this.openFileDialog.Filter = "Excel Files|*.xls;";
this.openFileDialog.CheckFileExists = true;
this.openFileDialog.Multiselect = false;
this.openFileDialog.ShowDialog();
strFilePath = this.openFileDialog.FileName;
this.txtExcelFileName.Text = this.openFileDialog.FileName;
string strConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + strFilePath + ";Extended Properties='"Excel 8.0;HDR=YES;IMEX=1;ImportMixedTypes=Text;'"";
System.Data.OleDb.OleDbDataAdapter dataAdapterExcel = new System.Data.OleDb.OleDbDataAdapter();
System.Data.OleDb.OleDbConnection oledbConnection = new System.Data.OleDb.OleDbConnection(strConnectionString);
DataTable tblEnglishTab = new DataTable("English");
DataTable tblMetricTab = new DataTable("Metric");
DataSet datasetExcelData = new DataSet();
oledbConnection.Open();
System.Data.OleDb.OleDbCommand cmdselect = new System.Data.OleDb.OleDbCommand();
try
{
    cmdselect.CommandText = "SELECT * FROM [English$A1:N10000]";
    cmdselect.Connection = oledbConnection;
    dataAdapterExcel.SelectCommand = cmdselect;
    dataAdapterExcel.Fill(tblEnglishTab);
    datasetExcelData.Tables.Add(tblEnglishTab);
}    
catch (Exception)
{
    MessageBox.Show("Please verify the Excel file type.'nUnable to locate the English worksheet in the specified file."Excel Import", MessageBoxButtons.OK, MessageBoxIcon.Error);
}    
try
{
    cmdselect.CommandText = "SELECT * FROM [Metric$A1:N10000]";
    cmdselect.Connection = oledbConnection;
    dataAdapterExcel.SelectCommand = cmdselect;
    dataAdapterExcel.Fill(tblMetricTab);
    datasetExcelData.Tables.Add(tblMetricTab);
}
catch (Exception)
{
    MessageBox.Show("Please verify the Excel file type.'nUnable to locate the Metric worksheet in the specified file.", "Excel Import", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
oledbConnection.Close();
dataAdapterExcel = null;

String对象是不可变的。每次使用其中一种方法在System.String类中，在内存中创建一个新的字符串对象，这需要为该新对象分配新的空间。

从excel填充数据时使用StringBuilder，然后将其放入label1.Text

以下是示例：

label1.Text = "";
StringBuilder excelContent = new StringBuilder();
for ( j = 1; j <=last_row; j++)
{
    for ( i = 1; i <= last_column; i++)
    {
        if (xlws.Cells[j,i].value != null)
        {
            excelContent.Append(xlws.Cells[j, i].value.ToString());
        }
    }
    excelContent.Append("'n");
}
label1.Text = excelContent.ToString();

你可以在这里阅读更多关于StringBuilder的信息。

相关文章：