모든 Excel 시트를 C#의 DataSet으로 가져오는 방법

모든 Excel 시트를 C#의 DataSet으로 가져오는 방법

인터넷으로 찾아봤는데 이런 질문은 찾을 수가 없었어요.모든 사람들이 엑셀 파일에서 개별 시트를 가져올 방법을 찾고 있었지만, 제가 원하는 것은 파일에 있는 모든 시트를 다음으로 가져오는 것입니다.DataTable에 들어 있는DataSet이름도 모르고요

저는 엑셀로 해본 적이 별로 없습니다.인터넷에서 찾은 샘플 및 부분적으로 작동하는 코드이며 지정된 시트 이름만 구문 분석합니다.

public static DataSet Parse(string fileName, string workSheetName)
{
    string connectionString = string.Format("provider=Microsoft.Jet.OLEDB.4.0; data source={0};Extended Properties=Excel 8.0;", fileName);
    string query = string.Format("SELECT * FROM [{0}$]", workSheetName);

    DataSet data = new DataSet();
    using (OleDbConnection con = new OleDbConnection(connectionString))
    {
        con.Open();
        OleDbDataAdapter adapter = new OleDbDataAdapter(query, con);
        adapter.Fill(data);
    }

    return data;
}

위의 코드에서는 쿼리가 가져올 위치를 알 수 있도록 workSheetName을(를) 전달해야 합니다.제 경우에는 이름이 무엇이든 모든 시트를 통과하여 개인에게 가져오기를 원합니다.DataTable의DataSet.

그래서 본질적으로, 마지막은DataSet각각의DataTable가져온 파일의 각 시트에 대한 행을 유지합니다.

이것은 제가 생각해낸 코드이고 완벽하게 작동하지만 다른 사람이 이미 답변을 추가한 것을 보았습니다.

static DataSet Parse(string fileName)
{
    string connectionString = string.Format("provider=Microsoft.Jet.OLEDB.4.0; data source={0};Extended Properties=Excel 8.0;", fileName);


    DataSet data = new DataSet();

    foreach(var sheetName in GetExcelSheetNames(connectionString))
    {
        using (OleDbConnection con = new OleDbConnection(connectionString))
        {    
            var dataTable = new DataTable();
            string query = string.Format("SELECT * FROM [{0}]", sheetName);
            con.Open();
            OleDbDataAdapter adapter = new OleDbDataAdapter(query, con);
            adapter.Fill(dataTable);
            data.Tables.Add(dataTable);
        }
    }

    return data;
}

static string[] GetExcelSheetNames(string connectionString)
{
        OleDbConnection con = null;
        DataTable dt = null;
        con= new OleDbConnection(connectionString);
        con.Open();
        dt = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

        if (dt == null)
        {
            return null;
        }

        String[] excelSheetNames = new String[dt.Rows.Count];
        int i = 0;

        foreach (DataRow row in dt.Rows)
        {
            excelSheetNames[i] = row["TABLE_NAME"].ToString();
            i++;
        }

        return excelSheetNames;
}

심심했기 때문에:

 static void Main(string[] args)
 {
            string filename = @"c:\temp\myfile.xlsx";    
            System.Data.OleDb.OleDbConnection myConnection = new System.Data.OleDb.OleDbConnection( 
                        "Provider=Microsoft.ACE.OLEDB.12.0; " +
                         "data source='" + filename + "';" +
                            "Extended Properties=\"Excel 12.0;HDR=YES;IMEX=1\" ");
            myConnection.Open();
            DataTable mySheets = myConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });                
            DataSet ds = new DataSet();
            DataTable dt;

            for (int i = 0; i <= mySheets.Rows.Count; i++)
            {
                   dt =   makeDataTableFromSheetName(filename, mySheets.Rows[i]["TABLE_NAME"].ToString());
                   ds.Tables.Add(dt);
            }
 }

private static DataTable makeDataTableFromSheetName(string filename, string sheetName)
{      
    System.Data.OleDb.OleDbConnection myConnection = new System.Data.OleDb.OleDbConnection(
    "Provider=Microsoft.ACE.OLEDB.12.0; " +
    "data source='" + filename + "';" +
    "Extended Properties=\"Excel 12.0;HDR=YES;IMEX=1\" ");

    DataTable dtImport = new DataTable();
    System.Data.OleDb.OleDbDataAdapter myImportCommand = new System.Data.OleDb.OleDbDataAdapter("select * from [" + sheetName + "$]", myConnection);
    myImportCommand.Fill(dtImport);
    return dtImport;
}

Avitus에서 제안한 함수는 맞으나 logica 오류가 있으므로 다음에서 다시 작성해야 합니다.

DataTable dtImport = new DataTable();
using ( System.Data.OleDb.OleDbConnection myConnection = new System.Data.OleDb.OleDbConnection(
            "Provider=Microsoft.ACE.OLEDB.12.0; " +
             "data source='" + filename + "';" +
                "Extended Properties=\"Excel 12.0;HDR=YES;IMEX=1\" ")){


using ( System.Data.OleDb.OleDbDataAdapter myImportCommand = new System.Data.OleDb.OleDbDataAdapter("select * from [" + sheetName + "$]", myConnection))
myImportCommand.Fill(dtImport);
} return dtImport;

맞습니다. 그렇지 않으면 연결 및 데이터 어댑터를 수동으로 처리해야 합니다.

이 방법은 가장 빠르고 좋은 방법이 아닐 수도 있지만 다른 방법입니다(빈 셀 편집 추가 제거).

    public static DataSet ReadWorkbook(string excelFileName, bool useFirstRowAsColumnName = false)
    {
        var excel = new Microsoft.Office.Interop.Excel.Application();
        var workBook = excel.Workbooks.Open(excelFileName, 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);//MLHIDE
        try
        {
            System.Data.DataSet ds = new DataSet(excelFileName);
            foreach (var sheet0 in workBook.Worksheets)
            {
                var sheet = (Microsoft.Office.Interop.Excel.Worksheet)sheet0;
                try
                {
                    var dt = readSheet(sheet, useFirstRowAsColumnName);
                    if (dt != null)
                        ds.Tables.Add(dt);
                }
                finally
                {
                    releaseObject(sheet);
                }
            }
            return ds;
        }
        finally
        {
            workBook.Close(true, null, null);
            excel.Quit();

            releaseObject(workBook);
            releaseObject(excel);
        }
    }

    /// <summary>
    /// Returns null for empty sheets or if sheet is not found.
    /// </summary>
    public static DataTable ReadSheet(string excelFileName, string sheetName, bool useFirstRowAsColumnName = false)
    {
        var excel = new Microsoft.Office.Interop.Excel.Application();
        var workBook = excel.Workbooks.Open(excelFileName, 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);//MLHIDE
        try
        {
            foreach (var sheet0 in workBook.Worksheets)
            {
                var sheet = (Microsoft.Office.Interop.Excel.Worksheet)sheet0;
                try
                {
                    if (sheet.Name.Equals_Wildcard(sheetName))
                    {
                        var dt = readSheet(sheet, useFirstRowAsColumnName);
                        if (dt != null)
                            return dt;
                    }
                }
                finally
                {
                    releaseObject(sheet);
                }
            }
            return null;
        }
        finally
        {
            workBook.Close(true, null, null);
            excel.Quit();

            releaseObject(workBook);
            releaseObject(excel);
        }
    }

    /// <summary>
    /// Returns null for empty sheets
    /// </summary>
private static DataTable readSheet(Microsoft.Office.Interop.Excel.Worksheet sheet, bool useFirstRowAsColumnName = false)
        {
            using (Dece.Common.BeginChangeCurrentCultureBlock_EN_us())
            {
                var range = sheet.UsedRange;
                try
                {
                    object[,] values = (object[,])range.Value2;
                    int rowCount = values.GetLength(0);
                    int colCount = values.GetLength(1);
                    int rowCount0 = rowCount;
                    int colCount0 = colCount;
                    #region find row-col count
                    {
                        bool ok = false;
                        for (int row = rowCount; row > 0; row--)
                            if (!ok)
                                for (int col = colCount; col > 0; col--)
                                {
                                    var val = values[row, col];
                                    if ((val != null) && (!System.Convert.ToString(val).IsNullOrEmpty()))
                                    {
                                        rowCount = row;
                                        ok = true;
                                        break;
                                    }
                                }
                            else
                                break;
                    }
                    {
                        bool ok = false;
                        for (int col = colCount; col > 0; col--)
                            if (!ok)
                                for (int row = rowCount; row > 0; row--)
                                {
                                    var val = values[row, col];
                                    if ((val != null) && (!System.Convert.ToString(val).IsNullOrEmpty()))
                                    {
                                        colCount = col;
                                        ok = true;
                                        break;
                                    }
                                }
                            else
                                break;
                    }
                    #endregion
                    if ((rowCount > 0) && (colCount > 0))
                    {  
                        var dt = new DataTable(sheet.Name);
                        dt.BeginLoadData();
                        try
                        {
                            for (int col = 1; col <= colCount; col++)
                                dt.Columns.Add_RenameIfRequired(useFirstRowAsColumnName ? values[1, col].ToString_NullProof() : col.ToString());
                            var arr = new object[colCount];
                            for (int row = useFirstRowAsColumnName ? 1 : 0; row < rowCount; row++)
                            {
                                for (int col = 1; col <= colCount; col++)
                                    arr[col - 1] = values[row + 1, col];
                                dt.Rows.Add(arr);
                            }
                        }
                        finally
                        {
                            dt.EndLoadData();
                        }
                        return dt;                        
                    }
                    else
                        return null;
                }
                finally
                {
                    releaseObject(range);
                }
            }
        }

    private static void releaseObject(object obj)
    {
        try
        {
            System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
            obj = null;
        }
        catch (Exception ex)
        {
            obj = null;
            throw new Exception("Unable to release the Object " + ex.ToString(), ex);//MLHIDE
        }
        finally
        {
            GC.Collect();
        }
    }

C#

데이터 세트에 시트에 따라 이름이 지정된 테이블을 제공하는 깔끔하고 깔끔한 최소 버전(후행 달러 없이):

    private static OleDbConnection GetConnection(string filename, bool openIt)
    {
        // if your data has no header row, change HDR=NO
        var c = new OleDbConnection($"Provider=Microsoft.ACE.OLEDB.12.0;Data Source='{filename}';Extended Properties=\"Excel 12.0;HDR=YES;IMEX=1\" ");
        if (openIt)
            c.Open();
        return c;
    }

    private static DataSet GetExcelFileAsDataSet(OleDbConnection conn)
    {
        var sheets = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new[] { default, default, default, "TABLE" });
        var ds = new DataSet();
        foreach (DataRow r in sheets.Rows)
            ds.Tables.Add(GetExcelSheetAsDataTable(conn, r["TABLE_NAME"].ToString()));
        return ds;
    }

    private static DataTable GetExcelSheetAsDataTable(OleDbConnection conn, string sheetName)
    {
        using (var da = new OleDbDataAdapter($"select * from [{sheetName}]", conn))
        {
            var dt = new DataTable() { TableName = sheetName.TrimEnd('$') };
            da.Fill(dt);
            return dt;
        }
    }

다음과 같이 사용:

DataSet ds;

using(c = GetConnection(@"C:\path\to\your\xl.xlsx", true)
    ds = GetExcelFileAsDataSet(c);

또는 테이블 하나만 원하는데 원하는 정확한 시트 이름을 모두 알고 있다면(끝에 1달러가 있다는 것을 기억하십시오):

DataTable dt;

using(c = GetConnection(@"C:\path\to\your\xl.xlsx", true)
    dt = GetExcelSheetAsDataTable(c, "Sheet1$");

VB.NET

보너스! 참고: 문자열 보간, With 등을 이해할 수 있는 최신 버전의 VB가 필요합니다.

        Private Shared Function GetConnection(filename As String, openIt As Boolean) As OleDbConnection
        'if your data has no header row, change HDR=NO
        Dim c = New OleDbConnection($"Provider=Microsoft.ACE.OLEDB.12.0;Data Source='{filename}';Extended Properties=""Excel 12.0;HDR=YES;IMEX=1"" ")
        If openIt Then c.Open()

        Return c
    End Function

    Private Shared Function GetExcelFileAsDataSet(conn As OleDbConnection) As DataSet

        Dim sheets = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, {Nothing, Nothing, Nothing, "TABLE"})
        Dim ds = New DataSet()

        For Each r As DataRow In sheets.Rows
            ds.Tables.Add(GetExcelSheetAsDataTable(conn, r("TABLE_NAME").ToString()))
        Next

        Return ds

    End Function

    Private Shared Function GetExcelSheetAsDataTable(conn As OleDbConnection, sheetName As String) As DataTable
        Using da = New OleDbDataAdapter($"select * from [{sheetName}]", conn)
            Dim dt = New DataTable() With {.TableName = sheetName.TrimEnd("$"c)}
            da.Fill(dt)
            Return dt
        End Using
    End Function

다음과 같이 사용:

    Dim ds As DataSet

    Using c = GetConnection("C:\path\to\your\xl.xlsx", True)
        ds = GetExcelFileAsDataSet(c)
    End Using 'closes connection

또는 이름을 알고 있는 단일 시트(시트 이름은 달러로 종료됨)의 경우:

    Dim dt As DataTable

    Using c = GetConnection("C:\path\to\your\xl.xlsx", True)
        dt = GetExcelSheetAsDataTable(c, "Sheet1$")
    End Using 'closes connection

언급URL : https://stackoverflow.com/questions/18006318/how-to-import-all-the-excel-sheets-to-dataset-in-c-sharp