C# 通过 oledb 操作Excel
2024-04-20 08:41:53
public string GetConnectionString()
{
Dictionary<string, string> props = new Dictionary<string, string>(); // XLSX - Excel 2007, 2010, 2012, 2013
props["Provider"] = "Microsoft.ACE.OLEDB.12.0;";
props["Extended Properties"] = "Excel 12.0 XML";
props["Data Source"] = @"C:\tools\MyExcel.xlsx"; // XLS - Excel 2003 and Older
//props["Provider"] = "Microsoft.Jet.OLEDB.4.0";
//props["Extended Properties"] = "Excel 8.0";
//props["Data Source"] = "C:\\MyExcel.xls"; var sb = new StringBuilder(); foreach (KeyValuePair<string, string> prop in props)
{
sb.Append(prop.Key);
sb.Append('=');
sb.Append(prop.Value);
sb.Append(';');
} return sb.ToString();
} public 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();
}
} public DataSet ReadExcelFile()
{
DataSet ds = new DataSet(); string connectionString = GetConnectionString(); using (OleDbConnection conn = new OleDbConnection(connectionString))
{
conn.Open();
OleDbCommand cmd = new OleDbCommand();
cmd.Connection = conn; // Get all Sheets in Excel File
DataTable dtSheet = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null); // Loop through all Sheets to get data
foreach (DataRow dr in dtSheet.Rows)
{
string sheetName = dr["TABLE_NAME"].ToString(); if (!sheetName.EndsWith("$"))
continue; // Get all rows from the Sheet
cmd.CommandText = "SELECT * FROM [" + sheetName + "]"; DataTable dt = new DataTable();
dt.TableName = sheetName; OleDbDataAdapter da = new OleDbDataAdapter(cmd);
da.Fill(dt); ds.Tables.Add(dt);
} cmd = null;
conn.Close();
} return ds;
}
最新文章
- Python常用方法
- Tomcat 利用server.xml进行其他盘符的其他项目映射的部署以及JSP引用其他盘符的图片（虚拟目录及虚拟路径）
- iOS 实现app文件共享
- [转]如何拷贝一个 SQL Server 的表
- asp.net中Ajax控件的用途（二）
- oracle中的nvl(), nvl2()函数
- 反编译ILSpy 无法显式调用运算符或访问器 错误处理方法 转
- 常用SQL语句优化技巧
- Marvelous Mazes
- python学习笔记--导入tab键自动补全功能的配置
- Dependency Walker使用说明
- Apache网页的缓存时间
- Oracle12c中多宿主容器数据库(CDBs)和可插拔数据库(PDBs)新特性之运行脚本
- v-cloak的用法和注意事项
- 安装并使用Jupyter Notebook
- ZooKeeper集群详细安装教程
- Vue.js 条件与循环
- jdbc:initialize-database标签的研究
- UDP传输原理及数据分片——学习笔记
- pg定时任务创建、查询与删除