这是我的代码
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
using System.Reflection;
using System.IO;
using System.Data.Sql;
using Microsoft.Office.Interop.Excel;
using System.Data.SQLite;
namespace Sheet
{
public partial class Display : Form
{
//调用App.config中的链接数据库字段
public static string str_con = System.Configuration.ConfigurationSettings.AppSettings["connectionstring"];
//原始语句
string strsql = "select Sku 产品编号, Upc 产品代码, SkuName 产品中文名称, SkuNameEn 产品英文名称, Type 产品类型, Category 产品种类, Batch 产品批次 from Sheet ";
public Display()
{
InitializeComponent();
}
private void Form1_Load(object sender, EventArgs e)
{
//创建链接通道
SqlConnection con = new SqlConnection(str_con);
//传入数据库的执行语句
SqlCommand com = new SqlCommand(strsql, con);
//DataSet 和 SQL Server之间的桥接器（数据适配器)
SqlDataAdapter da = new SqlDataAdapter();
da.SelectCommand = com;
//和数据库交互（增/删/改）少时用DataSet
DataSet ds = new DataSet();
//调用传入数据库的执行语句（通过适配器填充到DataSet（内存数据库））
da.Fill(ds, "Sheet");
//datagripview控件的绑定数据库数据
dataGridView1.DataSource = ds;
dataGridView1.DataMember = "Sheet";
}
public static bool DataCon(string sku,string upc,string skuname,string skunameen,string type,string category,string batch)
{
//数据库连接字符串
string sqlCon = "Data Source=Sofia;database=D:/SQLite;Pooling=true;FailIfMissing=false;";
using (SQLiteConnection con = new SQLiteConnection(sqlCon))
{
//判断数据库连接状态
if (con.State != ConnectionState.Open)
{
con.Close();
con.Open();
}
SQLiteCommand cmd = con.CreateCommand();
//数据操作字符串
string sqlText = "insert into test (Sku, Upc, SkuName, SkuNameEn, Type, Category, Batch)values('" + sku + "','" + upc + "','" + skuname + "','"+ skunameen +"','"+ type +"','"+ category +"',"+ batch +")";
cmd.CommandText = sqlText;
//返回受影响的行数大于0表示插入成功
if (cmd.ExecuteNonQuery() > 0)
{
return true;
}
else
{
return false;
}
}
}
/// <summary>
/// 导入Excel
/// </summary>
/// <param name="gridView">dataGridView控件名</param>
/// <param name="filename">路径名</param>
public static void ReadingExcel(DataGridView gridView, string fileName)
{
Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application();
Workbook book = app.Workbooks.Open(fileName, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
Worksheet sheet = book.Worksheets[1] as Worksheet;
//gridView.Rows.Clear();
//gridView.Columns.Clear();
//添加每一列的列标题
for (int i = 1; ; i++)
{
string value = (sheet.Cells[1, i] as Range).Text.ToString();//获得每列第一行数据。
if (value == string.Empty)//如果为空则代表已无数据
{
break;
}
gridView.Columns.Add("", value);
}
//导入每一行的数据
for (int i = 2; ; i++)
{
string value = (sheet.Cells[i, 1] as Range).Text.ToString();//获得每行第一列数据。
if (value == string.Empty)//如果为空则代表已无数据
{
break;
}
//获得EXCEL中每行上的每列数据
string sku = (sheet.Cells[i, 1] as Range).Text.ToString();
string upc = (sheet.Cells[i, 2] as Range).Text.ToString();
string skuname= (sheet.Cells[i, 3] as Range).Text.ToString();
string skunameen = (sheet.Cells[i, 4] as Range).Text.ToString();
string type = (sheet.Cells[i, 5] as Range).Text.ToString();
string category = (sheet.Cells[i, 6] as Range).Text.ToString();
string batch = (sheet.Cells[i, 7] as Range).Text.ToString();
DataCon(sku, upc, skuname, skunameen, type, category, batch);
gridView.Rows.Add(new object[] { sku, upc, skuname, skunameen, type, category, batch});
}
book.Close(Type.Missing, Type.Missing, Type.Missing);
app.Workbooks.Close();
app.Quit();
//DevComponents.DotNetBar.MessageBoxEx.Show("数据导入成功","系统提示",MessageBoxButtons.OK, MessageBoxIcon.Stop);
}
/// <summary>
/// 导入数据事件
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void 上传ToolStripMenuItem1_Click(object sender, EventArgs e)
{
//打开要导入的文件
OpenFileDialog open = new OpenFileDialog();
if (open.ShowDialog() == DialogResult.OK)
{
open.ShowDialog();
string fileName = open.FileName;
ReadingExcel(dataGridView1, fileName);
}
else
{
return;
}
}
}
}
这是我的配置文件
<?xml version="1.0"?>
<configuration>
<appSettings>
<add key="connectionstring" value="Data Source=Sofia;Initial Catalog=renvecf;Persist Security Info=True;MultipleActiveResultSets=true;User ID=sa;Password=a317066874"/>
</appSettings>
<runtime>
<assemblyBinding xmlns="urn:schemas-microsoft-com:asm.v1">
<dependentAssembly>
<assemblyIdentity name="Microsoft.Office.Interop.Excel" publicKeyToken="71E9BCE111E9429C" culture="neutral"/>
<bindingRedirect oldVersion="0.0.0.0-12.0.0.0" newVersion="12.0.0.0"/>
</dependentAssembly>
<dependentAssembly>
<assemblyIdentity name="System.Data.SQLite" publicKeyToken="DB937BC2D44FF139" culture="neutral"/>
<bindingRedirect oldVersion="0.0.0.0-1.0.74.0" newVersion="1.0.74.0"/>
</dependentAssembly>
<dependentAssembly>
<assemblyIdentity name="Microsoft.Vbe.Interop" publicKeyToken="71E9BCE111E9429C" culture="neutral"/>
<bindingRedirect oldVersion="0.0.0.0-12.0.0.0" newVersion="12.0.0.0"/>
</dependentAssembly>
<dependentAssembly>
<assemblyIdentity name="office" publicKeyToken="71E9BCE111E9429C" culture="neutral"/>
<bindingRedirect oldVersion="0.0.0.0-12.0.0.0" newVersion="12.0.0.0"/>
</dependentAssembly>
</assemblyBinding>
</runtime>
</configuration>
7 个解决方案
#1
有这个 dll的引用么？？首先
#2
能说详细点吗 怎么引用 代码 我copy的，
不运行的时候有这个，
把这句代码的注视去掉 会报上下文的错误！！
#4
我的引用都在这了，还要加啥？？
#5
请用x86编译。。
#6
从哪里怎么看出来的，说下，我发现问题了 我他报的错是。74的DLL 我引用的是。66的DLL。。。。
#7
SQLITE他有区分X86与64的如果你用66版本那应是x86的我也一直用这个，如果不行
那你把CONFIG下的这个节点删了。
<dependentAssembly>
<assemblyIdentity name="System.Data.SQLite" publicKeyToken="DB937BC2D44FF139" culture="neutral"/>
<bindingRedirect oldVersion="0.0.0.0-1.0.74.0" newVersion="1.0.74.0"/>
</dependentAssembly>
因为他这是指明你要用74版本的，而你却引用66的。。。你应是用NUGET安装的吧。
#1
有这个 dll的引用么？？首先
#2
能说详细点吗 怎么引用 代码 我copy的，
不运行的时候有这个，
把这句代码的注视去掉 会报上下文的错误！！
#3
#4
我的引用都在这了，还要加啥？？
#5
请用x86编译。。
#6
从哪里怎么看出来的，说下，我发现问题了 我他报的错是。74的DLL 我引用的是。66的DLL。。。。
#7
SQLITE他有区分X86与64的如果你用66版本那应是x86的我也一直用这个，如果不行
那你把CONFIG下的这个节点删了。
<dependentAssembly>
<assemblyIdentity name="System.Data.SQLite" publicKeyToken="DB937BC2D44FF139" culture="neutral"/>
<bindingRedirect oldVersion="0.0.0.0-1.0.74.0" newVersion="1.0.74.0"/>
</dependentAssembly>
因为他这是指明你要用74版本的，而你却引用66的。。。你应是用NUGET安装的吧。