将数据集导出到 Excel 工作表
本文关键字:Excel 工作 数据集 | 更新日期: 2023-09-27 17:56:39
我是 C# 中 excel 自动化的新手,所以我对此感到困惑。我已经在数据集中导入了一个 excel,并根据我的要求在数据集中进行了一些更新。现在,我想将该数据集导出到该输入表,以便我可以看到数据表中反映的数据集中完成的更新。将数据集导出到 Excel 的最佳方法是什么。以下是我如何打开Excel工作表的代码:
string sConnection = null;
OleDbConnection oleExcelConnection = default(OleDbConnection);
sConnection = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:'input.xls;Extended Properties='"Excel 12.0;HDR=No;IMEX=1'"";
oleExcelConnection = new OleDbConnection(sConnection);
oleExcelConnection.Open();
string sqlquery = "Select * From [c:'input.xls]";
DataSet ds = new DataSet();
OleDbDataAdapter da = new OleDbDataAdapter(sqlquery, oleExcelConnection);
da.Fill(ds);
System.Data.DataTable dt = ds.Tables[0];
/* 10 to 12 linq queries on dt*/
-> now here I want to export the updated dt to input.xls
经过几个小时的研究,我找到了一种使用数据表编写 excel 的方法。虽然,我最初的要求是更新原始工作表,但我想我必须对从头开始创建新的输出工作表感到满意。解决方案如下:
//open file
StreamWriter wr = new StreamWriter(@"D:''Book1.xls");
// dt is the DataTable needed to be dumped in an excel sheet.
try
{
for (int i = 0; i < dt.Columns.Count; i++)
{
wr.Write(dt.Columns[i].ToString().ToUpper() + "'t");
}
wr.WriteLine();
//write rows to excel file
for (int i = 0; i < (dt.Rows.Count); i++)
{
for (int j = 0; j < dt.Columns.Count; j++)
{
if (dt.Rows[i][j] != null)
{
wr.Write(Convert.ToString(dt.Rows[i][j]) + "'t");
}
else
{
wr.Write("'t");
}
}
//go to next line
wr.WriteLine();
}
//close file
wr.Close();
}
catch (Exception ex)
{
throw ex;
}
http://www.codeproject.com/Tips/705470/Read-and-Write-Excel-Documents-Using-OLEDB
private void WriteExcelFile()
{
string connectionString = GetConnectionString();
using (OleDbConnection conn = new OleDbConnection(connectionString))
{
conn.Open();
OleDbCommand cmd = new OleDbCommand();
cmd.Connection = conn;
cmd.CommandText = "CREATE TABLE [table1] (id INT, name VARCHAR, datecol DATE );";
cmd.ExecuteNonQuery();
cmd.CommandText = "INSERT INTO [table1](id,name,datecol) VALUES(1,'AAAA','2014-01-01');";
cmd.ExecuteNonQuery();
cmd.CommandText = "INSERT INTO [table1](id,name,datecol) VALUES(2, 'BBBB','2014-01-03');";
cmd.ExecuteNonQuery();
cmd.CommandText = "INSERT INTO [table1](id,name,datecol) VALUES(3, 'CCCC','2014-01-03');";
cmd.ExecuteNonQuery();
cmd.CommandText = "UPDATE [table1] SET name = 'DDDD' WHERE id = 3;";
cmd.ExecuteNonQuery();
conn.Close();
}
}
谷歌是你的朋友=)