只需要阅读excel表格C#并导入mySQL中的3列
本文关键字:导入 mySQL 中的 3列 表格 excel | 更新日期: 2023-09-27 18:20:02
我正试图从excel工作表中读取3列,并将它们导入mySQL数据库。我是C#的新手,需要尽可能多的详细帮助。我的using块中的MySQL_DB_Management是一个朋友的类,主要用于创建我的连接字符串。到目前为止,我的代码如下:
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);
}
对于我下一步需要做的任何帮助,我们将不胜感激。
尝试查看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];