导入到Excel中的数据表迅速 [英] Importing Excel into a DataTable Quickly

问题描述

我想读一个Excel文件到Data.DataTable的名单，虽然我目前的方法可能需要很长的时间。我essentually去工作表由工作表，细胞通过细胞，和它往往需要很长的时间。是否有这样做的更快的方法？这是我的code：

 名单，其中，数据表＆GT;名单=新的名单，其中，数据表＆GT;（）;

    //计数表
    对于（诠释计数= 1; COUNT＆LT; WB.Worksheets.Count ++计数）
    {
        //创建为每个工作表一个新的DataTable
        DATA.DataTable DT =新的DataTable（）;

        WS =（EXCEL.Worksheet）WB.Worksheets.get_Item（计数）;

        textBox1.Text = count.ToString（）;

        //获取工作范围
        范围= WS.UsedRange;


        //创建数据表中的新列
        为（CCNT = 1; CCNT＆其中; = Range.Columns.Count; CCNT ++）
        {
            textBox3.Text = cCnt.ToString（）;


                列=新的DataColumn（）;
                Column.DataType = System.Type.GetType（System.String）;
                Column.ColumnName = cCnt.ToString（）;
                DT.Columns.Add（列）;

            //为数据表创建一行
            对于（RCNT = 0; RCNT＆LT; = Range.Rows.Count; RCNT ++）
            {
                textBox2.Text = rCnt.ToString（）;

                尝试
                {
                    cellVal =（字符串）（Range.Cells [RCNT，CCNT]作为EXCEL.Range）.Value2;
                }
                赶上（Microsoft.CSharp.RuntimeBinder.RuntimeBinderException）
                {
                    ConvertVal =（双）（Range.Cells [RCNT，CCNT]作为EXCEL.Range）.Value2;
                    cellVal = ConvertVal.ToString（）;
                }

                //添加到DataTable
                如果（CCNT == 1）
                {

                    行= DT.NewRow（）;
                    行[cCnt.ToString（）] = cellVal;
                    DT.Rows.Add（行）;
                }
                其他
                {

                    行= DT.Rows [RCNT]
                    行[cCnt.ToString（）] = cellVal;

                }
            }
        }
        //添加DT到列表中。然后去在Excel工作簿下一张纸
        List.Add（DT）;
    }
 

解决方案

Caling .Value2 是一个昂贵的操作，因为它是一个COM-互操作调用。我反而读取整个范围到一个数组，然后通过数组循环：

 对象[，]数据= Range.Value2;

//创建数据表中的新列
为（CCNT = 1; CCNT＆其中; = Range.Columns.Count; CCNT ++）
{
    textBox3.Text = cCnt.ToString（）;

        列=新的DataColumn（）;
        Column.DataType = System.Type.GetType（System.String）;
        Column.ColumnName = cCnt.ToString（）;
        DT.Columns.Add（列）;

    //为数据表创建一行
    对于（RCNT = 0; RCNT＆LT; = Range.Rows.Count; RCNT ++）
    {
        textBox2.Text = rCnt.ToString（）;

        尝试
        {
            cellVal =（字符串）（数据[RCNT，CCNT]）;
        }
        赶上（Microsoft.CSharp.RuntimeBinder.RuntimeBinderException）
        {
            ConvertVal =（双）（数据[RCNT，CCNT]）;
            cellVal = ConvertVal.ToString（）;
        }

        //添加到DataTable
        如果（CCNT == 1）
        {

            行= DT.NewRow（）;
            行[cCnt.ToString（）] = cellVal;
            DT.Rows.Add（行）;
        }
        其他
        {

            行= DT.Rows [RCNT]
            行[cCnt.ToString（）] = cellVal;

        }
    }
}
 

I am trying to read an Excel file into a list of Data.DataTable, although with my current method it can take a very long time. I essentually go Worksheet by Worksheet, cell by cell, and it tends to take a very long time. Is there a quicker way of doing this? Here is my code:

    List<DataTable> List = new List<DataTable>();

    // Counting sheets
    for (int count = 1; count < WB.Worksheets.Count; ++count)
    {
        // Create a new DataTable for every Worksheet
        DATA.DataTable DT = new DataTable();

        WS = (EXCEL.Worksheet)WB.Worksheets.get_Item(count);

        textBox1.Text = count.ToString();

        // Get range of the worksheet
        Range = WS.UsedRange;


        // Create new Column in DataTable
        for (cCnt = 1; cCnt <= Range.Columns.Count; cCnt++)
        {
            textBox3.Text = cCnt.ToString();


                Column = new DataColumn();
                Column.DataType = System.Type.GetType("System.String");
                Column.ColumnName = cCnt.ToString();
                DT.Columns.Add(Column);

            // Create row for Data Table
            for (rCnt = 0; rCnt <= Range.Rows.Count; rCnt++)
            {
                textBox2.Text = rCnt.ToString();

                try
                {
                    cellVal = (string)(Range.Cells[rCnt, cCnt] as EXCEL.Range).Value2;
                }
                catch (Microsoft.CSharp.RuntimeBinder.RuntimeBinderException)
                {
                    ConvertVal = (double)(Range.Cells[rCnt, cCnt] as EXCEL.Range).Value2;
                    cellVal = ConvertVal.ToString();
                }

                // Add to the DataTable
                if (cCnt == 1)
                {

                    Row = DT.NewRow();
                    Row[cCnt.ToString()] = cellVal;
                    DT.Rows.Add(Row);
                }
                else
                {

                    Row = DT.Rows[rCnt];
                    Row[cCnt.ToString()] = cellVal;

                }
            }
        }
        // Add DT to the list. Then go to the next sheet in the Excel Workbook
        List.Add(DT);
    }

解决方案

Caling .Value2 is an expensive operation because it's a COM-interop call. I would instead read the entire range into an array and then loop through the array:

object[,] data = Range.Value2;

// Create new Column in DataTable
for (cCnt = 1; cCnt <= Range.Columns.Count; cCnt++)
{
    textBox3.Text = cCnt.ToString();

        Column = new DataColumn();
        Column.DataType = System.Type.GetType("System.String");
        Column.ColumnName = cCnt.ToString();
        DT.Columns.Add(Column);

    // Create row for Data Table
    for (rCnt = 0; rCnt <= Range.Rows.Count; rCnt++)
    {
        textBox2.Text = rCnt.ToString();

        try
        {
            cellVal = (string)(data[rCnt, cCnt]);
        }
        catch (Microsoft.CSharp.RuntimeBinder.RuntimeBinderException)
        {
            ConvertVal = (double)(data[rCnt, cCnt]);
            cellVal = ConvertVal.ToString();
        }

        // Add to the DataTable
        if (cCnt == 1)
        {

            Row = DT.NewRow();
            Row[cCnt.ToString()] = cellVal;
            DT.Rows.Add(Row);
        }
        else
        {

            Row = DT.Rows[rCnt];
            Row[cCnt.ToString()] = cellVal;

        }
    }
} 

这篇关于导入到Excel中的数据表迅速的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！