如何将大型Excel文件导入数据表? [英] How to import large excel file to datatable?

问题描述

我有一个500列的Excel文件.我正在使用OLEDB将其导入到DataTable中.但是，数据表仅包含前255列.我不知道为什么其余的列没有被导入.

I have an Excel file with 500 columns. I am using OLEDB to import it into a DataTable. But the DataTable contains only the first 255 columns. I can't figure out why the rest of the columns aren't imported.

我的代码是

public DataTable ToDataTable()
{
    DataSet dsEmpMaster = new DataSet();
    DataTable dtEmpMaster = null;
    string strConnectionString;
    string strSql;
    string FileNameWithPath = String.Format("{0}\\{1}", System.IO.Path.GetDirectoryName(System.Diagnostics.Process.GetCurrentProcess().MainModule.FileName), _FileName);
    try
    {
        dtEmpMaster = new DataTable();

        strConnectionString = String.Format(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=Excel 12.0;", FileNameWithPath);
        strSql = String.Format("SELECT * FROM [{0}$]", _SheetName);
        OleDbDataAdapter adpMasterData = new OleDbDataAdapter(strSql, strConnectionString);
        adpMasterData.Fill(dsEmpMaster);
        dtEmpMaster = dsEmpMaster.Tables[0];
        adpMasterData.Dispose();
    }
    catch (Exception ex)
    {
        _ExcelError = ex.Message;
    }

推荐答案

OLEDB无法支持256列以上，请参见

OLEDB cannot support >256 columns, see this for a workaround.

        var file_path = @"D:\doc\excel.xlsx";
        Microsoft.Office.Interop.Excel.Application xlApp =
            new Microsoft.Office.Interop.Excel.ApplicationClass();
        // Disabling messageboxes to prevent losing control on Excel app
        xlApp.DisplayAlerts = false;
        Microsoft.Office.Interop.Excel.Workbook xlWorkBook =
            xlApp.Workbooks.Open(file_path, 0, false, 5, "", "", true,
                Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
        xlWorkBook.DoNotPromptForConvert = true;
        for (var k = 1; k <= xlWorkBook.Worksheets.Count; k++)
        {
            Microsoft.Office.Interop.Excel.Worksheet xlWorkSheet =
                (Microsoft.Office.Interop.Excel.Worksheet) this.xlWorkBook.Worksheets.get_Item(k);
            int cCnt = xlWorkSheet.Cells.Count;
            // processing all cells used, but can call named ranges
            Microsoft.Office.Interop.Excel.Range range = xlWorkSheet.UsedRange;
            for (var rCnt = 1; rCnt <= range.Rows.Count; rCnt++)
            {
                for (cCnt = 1; cCnt <= range.Columns.Count; cCnt++)
                {
                    Microsoft.Office.Interop.Excel.Range rng =
                        (Microsoft.Office.Interop.Excel.Range) range.Cells[rCnt, cCnt];
                    // do something with range
                }
            }
        }

但是，正如该线程中所提到的，Interop可能对于您的需求而言太慢了，我个人还没有使用它，但是可能值得参考 ExcelDataReader

But as mentioned in that thread Interop may be too slow for your needs, I personally havent used it but it might be worth looking at something like ExcelDataReader

正在修复链接中代码的格式

edit: was fixing the formatting of the code from the link

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