c#对excel，datatable数据库的操作

最新推荐文章于 2024-08-11 22:03:00 发布

mr_xqw

最新推荐文章于 2024-08-11 22:03:00 发布

阅读量648

点赞数 1

文章标签： c# 数据 excel 数据库

本文介绍了一种将Excel文件中的数据导入到数据库的方法，并提供了具体的C#代码实现，包括读取Excel、转换为DataTable以及将数据批量插入到Oracle数据库的过程。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

很多东西都是百度来自己修改的

将数据写入excel

string path = "E:\\zhuanyi\\zhuanyi.xls";
// File.Create(path);

string result = string.Empty;

//string path = "E:\\zhuanyi\\" + dtnow + ".xlsx";
// 实例化流对象，以特定的编码向流中写入字符。
StreamWriter sw = new StreamWriter(path, false, Encoding.GetEncoding("gb2312"));

StringBuilder sb = new StringBuilder();
for (int k = 0; k < lnewdt.Columns.Count; k++)
{
// 添加列名称
sb.Append(lnewdt.Columns[k].ColumnName.ToString() + "\t");
}
sb.Append(Environment.NewLine);
// 添加行数据
for (int i = 0; i < lnewdt.Rows.Count; i++)
{
DataRow row = lnewdt.Rows[i];
for (int j = 0; j < lnewdt.Columns.Count; j++)
{
// 根据列数追加行数据
sb.Append(row[j].ToString() + "\t");
}
sb.Append(Environment.NewLine);
}
sw.Write(sb.ToString());
sw.Flush();
sw.Close();
sw.Dispose();
KillSpecialExcel();

datatable插入数据库

public bool insertValueWithDt(DataTable dataTable)
{
string ip = txtNewip2.Text;
string id = txtNewid2.Text;
string password = txtnewPassword2.Text;
String ConnStr = "Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=" + ip + ")(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=orcl)));User Id=" + id + ";Password=" + password + ";Pooling=true;Min Pool Size=1;Max Pool Size=255;";
using (OracleConnection conn = new OracleConnection(ConnStr))
{
try
{
string sql = " select * from "+cbNewTablename2.Text +" where rownum < 1";
OracleCommand cmd = new OracleCommand(sql, conn);
OracleDataAdapter adapter = new OracleDataAdapter(cmd);
OracleCommandBuilder cb = new OracleCommandBuilder(adapter);
DataTable dsNew = new DataTable();
int count = adapter.Fill(dsNew);
for (int i = 0; i < dataTable.Rows.Count; i++)
{
DataRow dr = dsNew.NewRow();
for (int j = 0; j < dataTable.Columns.Count; j++)
{
dr[dsNew.Columns[j].ColumnName] = dataTable.Rows[i][j];
}
dsNew.Rows.Add(dr);
}
count = adapter.Update(dsNew);
adapter.UpdateBatchSize = 200;
//adapter.Update(dataTable);
return true;
}
catch (Exception e)
{

return false;
}
}
}

读取excel

public DataSet ExcelToDS(string Path)
{
string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + Path + ";" + "Extended Properties=Excel 8.0;";
OleDbConnection conn = new OleDbConnection(strConn);
conn.Open();
string strExcel = "";
OleDbDataAdapter myCommand = null;
DataSet ds = null;
strExcel = "select * from [zhuanyi$]";
myCommand = new OleDbDataAdapter(strExcel, strConn);
ds = new DataSet();
myCommand.Fill(ds, "table1");
return ds;
}

要设置成32位或者x86