c#db导入导出excel

最新推荐文章于 2022-01-04 16:34:15 发布

qq_32444825

最新推荐文章于 2022-01-04 16:34:15 发布

阅读量437

点赞数

CC 4.0 BY-SA版权

分类专栏： Csharp

本文链接：https://blog.youkuaiyun.com/qq_32444825/article/details/96864747

这是一个C#工具类，用于将Excel数据导入到数据库和从数据库导出到Excel。它使用OleDb连接从Excel文件读取数据，并通过SqlBulkCopy将数据批量导入数据库。此外，它还提供了将数据库查询结果导出到Excel的功能。

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

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using System.Reflection;
using System.Windows.Controls;
using Excel;
using WpfDemo1.model;
using Application = Excel.Application;

namespace llj.utils
{
/// <summary>
/// 操作excel工具
/// </summary>
class ImportExcelDBUtil
{
//可以定义泛型，
static List<MediClass> medicine = new List<MediClass>();

/// <summary>
/// 读取excel导入到数据库
/// </summary>
/// <param name="excelFile">所导excel路径</param>
/// <param name="sheetName">excel表excel格Sheet1名称</param>
/// <param name="connectionString">数据库信息</param>
/// <param name="tableName">数据库表</param>
public static void TransferData(string excelFile, string sheetName, string connectionString,string tableName)
{
DataSet ds = new DataSet();
try
{
//获取全部数据
string strConn = "Provider = Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + excelFile + ";" + "Extended Properties = Excel 8.0;";
OleDbConnection conn = new OleDbConnection(strConn);
conn.Open();
string strExcel = "";
OleDbDataAdapter myCommand = null;
strExcel = string.Format("select * from [{0}$]", sheetName);
myCommand = new OleDbDataAdapter(strExcel, strConn);
myCommand.Fill(ds, sheetName);

//如果目标表不存在则创建,excel文件的第一行为列标题,从第二行开始全部都是数据记录
//string strSql = string.Format("if not exists(select * from sysobjects where name = '{0}') create table {0}(", sheetName); //以sheetName为表名
string strSql = string.Format("if not exists(select * from sysobjects where name = '{0}') create table {0}(", tableName);

/* 是这里循环三次吗？*/
foreach (System.Data.DataColumn c in ds.Tables[0].Columns)
{
strSql += string.Format("[{0}] varchar(255),", c.ColumnName);
//strSql += string.Format("[excelToDB] varchar(255),", c.ColumnName);
}

strSql = strSql.Trim(',') + ")";

using (System.Data.SqlClient.SqlConnection sqlconn = new System.Data.SqlClient.SqlConnection(connectionString))
{
sqlconn.Open();
System.Data.SqlClient.SqlCommand command = sqlconn.CreateCommand();
command.CommandText = strSql;
&n