C# Excel to Database(SQL)

c# sql excel datatable npoi

尚書大人 2021-07-06 16:59:07 ‧ 4318 瀏覽

分享至

我想請問一下，Excel如果要上傳到Database，需要先轉成 DataTable嗎?
我查了一些資料都是先 Excel →　DataTable →　Database(sql)

我對DataTable並不是很熟，這兩天開始學，請問有其他方法嗎?
如果有其他方法請讓我參考，麻煩了，謝謝

以下是我讀Excel to DataTable
可是為了確認，用Console.WriteLine讀出來是亂碼
套件是使用 NPOI

using System;
using System.Data;
using System.IO;
using System.Text;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;

namespace Excel_to_DataTable
{
class Program
{
    static void Main(string[] args)
    {
        DataTable dt = new DataTable();

        using(FileStream file = new FileStream(@"H:\ying\桌面\test\LIST.xlsx", FileMode.Open,FileAccess.Read))
        {
            IWorkbook workbook = null;

            
            workbook = new XSSFWorkbook(file);

            ISheet sheet = workbook.GetSheetAt(0);
            IRow row;
            row = sheet.GetRow(0);
            if (row != null)
            {
                for(int m = 0; m < row.LastCellNum; m++)
                {
                    string cellvalue = row.GetCell(m).ToString();

                    dt.Columns.Add(cellvalue);

                }
            }
            for(int i = 1; i <= sheet.LastRowNum; i++)
            {
                System.Data.DataRow dr = dt.NewRow();
                row = sheet.GetRow(i);
                if(row != null)
                {
                    for(int j=0; j < row.LastCellNum; j++)
                    {
                        string cellvalue = row.GetCell(j).ToString();

                        dr[j] = cellvalue;
                    }
                }
                dt.Rows.Add(dr);
            }
            file.Close();
            foreach(DataRow roww in dt.Rows)
            {
                foreach (DataColumn col in dt.Columns )
                {
                    Console.WriteLine(roww[col]);
                }
            }
            Console.ReadLine();
        }
    }
}

}

japhenchen iT邦超人 1 級 ‧ 2021-07-07 09:48:01 檢舉

我是不知道OleDB是否還能支援xlsx新版的excel檔匯入的功能，但我的做法，都是用Epplus(從Nuget安裝)讀取Excel我要的工作表及資料範圍，滙到SQLSERVER上相對應的資料TABLE及欄位裡，而不是直接把XLS檔直接丟給OleDB去處理匯入的工作......個人拙見

尚書大人 iT邦新手 3 級 ‧ 2021-07-07 13:31:37 檢舉

那如果用Epplus讀取EXCEL要怎麼匯到SQLserver呢? 我不會用對應的資料TABLE及欄位，Epplus已經會使用了

登入發表討論

直播研討會

{{ item.channelVendor }} {{ item.webinarstarted }} |

直播中

2 個回答

純真的人

iT邦大師 1 級 ‧ 2021-07-06 18:27:01

最佳解答

我寫的是VB...你參考看看..
這個FileName我上傳Server後(你自己改成你的檔案來源)，取得的檔案存放來源

        Dim conn As New System.Data.OleDb.OleDbConnection
        Dim da As New System.Data.DataSet
        Dim myAdapter As New System.Data.OleDb.OleDbDataAdapter
        Dim connString As String = ""
            
        If Right(FileName, 4) = ".xls" Then
            connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & FileName & ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=2"""
        ElseIf Right(FileName, 5) = ".xlsx" Then
            connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & FileName & ";Extended Properties=""Excel 12.0;HDR=Yes;IMEX=2"""
        End If
            
        conn = New System.Data.OleDb.OleDbConnection(connString)
        If conn.State = System.Data.ConnectionState.Closed Then
            conn.Open()
            Sql = "select * from [Course$]"
            myAdapter.SelectCommand = New System.Data.OleDb.OleDbCommand(Sql, conn)
            myAdapter.Fill(da)
            For i = 0 To da.Tables(0).Rows.Count - 1
                Dim Rs As System.Data.DataRow = da.Tables(0).Rows(i)
                    
                For j = 0 To da.Tables(0).Columns.Count - 1
                    Dim TH_Str As String = da.Tables(0).Columns(j).ColumnName
                    Dim Tmp_Str As String = Rs.Item(TH_Str) & ""
                        
                    Response.Write(TH_Str & "：" & Tmp_Str)
                    Response.Write("<br />")
                Next
                Response.Write("<hr />")
            Next
            da.Dispose()
            conn.Close()
            conn.Dispose()
        End If

補上VB轉C#的程式碼~

    var conn = new System.Data.OleDb.OleDbConnection();
    var da = new System.Data.DataSet();
    var myAdapter = new System.Data.OleDb.OleDbDataAdapter();
    string connString = "";
    if (Strings.Right(FileName, 4) == ".xls")
    {
        connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + FileName + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\"";
    }
    else if (Strings.Right(FileName, 5) == ".xlsx")
    {
        connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + FileName + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\"";
    }

    conn = new System.Data.OleDb.OleDbConnection(connString);
    if (conn.State == System.Data.ConnectionState.Closed)
    {
        conn.Open();
        Sql = "select * from [Course$]";
        myAdapter.SelectCommand = new System.Data.OleDb.OleDbCommand(Sql, conn);
        myAdapter.Fill(da);
        for (int i = 0, loopTo = da.Tables[0].Rows.Count - 1; i <= loopTo; i++)
        {
            var Rs = da.Tables[0].Rows[i];
            for (int j = 0, loopTo1 = da.Tables[0].Columns.Count - 1; j <= loopTo1; j++)
            {
                string TH_Str = da.Tables[0].Columns[j].ColumnName;
                string Tmp_Str = Conversions.ToString(Operators.ConcatenateObject(Rs[TH_Str], ""));
                Response.Write(TH_Str + "：" + Tmp_Str);
                Response.Write("<br />");
            }

            Response.Write("<hr />");
        }

        da.Dispose();
        conn.Close();
        conn.Dispose();
    }

回應
分享
檢舉

登入發表回應

暐翰

iT邦大師 1 級 ‧ 2021-07-07 13:19:12

可以使用 MiniExcel，讀取Excel資料到IEnumerable或是DataTable，再轉成SQL轉到資料庫

Github : https://github.com/shps951023/MiniExcel

例子:

1. SQLite & Dapper 讀取大數據新增到資料庫

Note : 請不要呼叫 call ToList/ToArray 等方法，這會將所有資料讀到記憶體內

using (var connection = new SQLiteConnection(connectionString))
{
    connection.Open();
    using (var transaction = connection.BeginTransaction())
    {
	   var rows = MiniExcel.Query(path);
	   foreach (var row in rows)
			 connection.Execute("insert into T (A,B) values (@A,@B)", new { row.A, row.B }, transaction: transaction);
	   transaction.Commit();
    }
}

讀取excel插入資料庫1千萬筆數據花費30秒，最大26MB記憶體