c# - 需要读取 excel 表 C# 并仅导入 mySQL 3 列

Question

我正在尝试从 Excel 工作表中读取 3 列并将它们导入 mySQL 数据库。我是 C# 的新手，需要尽可能详细的帮助。我的 using block 中的 MySQL_DB_Management 是一个 friend 的类，基本上用于创建我的连接字符串。到目前为止，我的代码如下:

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using Excel = Microsoft.Office.Interop.Excel;
using MySQL_DB_Management;
using System.Data.SqlClient;
using System.Data.OleDb;

namespace Class_Code_Discrepancy
{
  public partial class CC_Disc_Fixer : Form
  {
    MySqlDB mysql;
    SqlConnection sql;

    public CC_Disc_Fixer()
    {
        InitializeComponent();
        //create connection strings
        sql = new SqlConnection(@"server=1.1.1.1;" + @"Trusted_Connection=false;" + @"uid=RO_agent;" +
                                @"password=mypass;" + @"database=Depot;");
        mysql = new MySqlDB("1.1.1.1", "platform", "root", "password");
    }

private void Upload_Click(object sender, EventArgs e)
    {           
        string excelconn = @"Provider=Microsoft.Ace.OLEDB.12.0;" +
                                @"Data Source=c:\Notebook Classcode Master 20121024.xlsx;" +
                                @"Extended Properties=Excel 12.0;HDR=NO;FirstRowHasNames=NO";
        OleDbConnection xlconn = new OleDbConnection(excelconn);

        //DataSet xldataset = new DataSet();
        //OleDbDataAdapter xladaptor = new OleDbDataAdapter();
        xlconn.Open();
        OleDbCommand sel = new OleDbCommand();
        sel.Connection = xlconn;
        DataTable dtsheet = xlconn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
    }

对于我接下来需要做什么的任何帮助，我们将不胜感激。

Answer 1

尝试查看 ExcelDataReader库，有了它，您可以将 Excel 文件读入数据集中，就像他们页面上显示的那样。

FileStream stream = File.Open(filePath, FileMode.Open, FileAccess.Read);

//1. Reading from a binary Excel file ('97-2003 format; *.xls)
IExcelDataReader excelReader = ExcelReaderFactory.CreateBinaryReader(stream);
//...
//2. Reading from a OpenXml Excel file (2007 format; *.xlsx)
IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);
//...
//3. DataSet - The result of each spreadsheet will be created in the result.Tables
DataSet result = excelReader.AsDataSet();
//...
//4. DataSet - Create column names from first row
excelReader.IsFirstRowAsColumnNames = true;
DataSet result = excelReader.AsDataSet();

然后你可以用foreach循环遍历它

foreach (DataTable dt in result.Tables)
{
    foreach (DataRow dr in dt.Rows)
    {
        foreach (DataColumn dc in dt.Columns)
           {
               // do something
           }
    }
}

如果你只想访问前三列，你可以这样做

result.Tables[0].Rows[0][0];
result.Tables[0].Rows[0][1];
result.Tables[0].Rows[0][2];