winform Execl数据导入到数据库（SQL）

最新推荐文章于 2021-03-06 16:47:26 发布

Jia_ShengJie

最新推荐文章于 2021-03-06 16:47:26 发布

阅读量1.3k

点赞数

分类专栏： C# 文章标签： winform Excel 导入

本文链接：https://blog.csdn.net/u013816709/article/details/25424617

版权

C# 专栏收录该内容

80 篇文章 1 订阅

订阅专栏

首先，看一下我的窗体设计：

要插入的Excel表：

编码	名称	联系人	电话	省市	备注
100	100线	张三	12345678910	北京	测试
101	101线	张三	12345678910	上海	测试
102	102线	张三	12345678910	深圳	测试
103	103线	张三	12345678910	广州	测试
104	104线	张三	12345678910	福建	测试
105	105线	张三	12345678910	厦门	测试
106	106线	李四	12345678910	台湾	测试
107	107线	李四	12345678910	石家庄	测试
108	108线	李四	12345678910	合肥	测试
109	109线	李四	12345678910	东城	测试
110	110线	李四	12345678910	丰台	测试
111	111线	李四	12345678910	朝阳	测试
112	112线	王五	12345678910	海淀	测试
113	113线	王五	12345678910	昌平	测试
114	114线	王五	12345678910	大兴	测试
115	115线	王五	12345678910	西城	测试
116	116线	王五	12345678910	通州	测试
117	117线	赵六	12345678910	石景山	测试
118	118线	赵六	12345678910	门头沟	测试
119	119线	赵六	12345678910	房山	测试

后台代码：

private void btnFindFile_Click(object sender, EventArgs e)
{
#region 选择文件按钮事件
openFile.Multiselect = true;
openFile.FileName = "";
openFile.Filter = "(*.xlsx,*.xls)|*.xlss;*.xls";
openFile.ShowDialog();
openFile.Reset();
#endregion
}

private void openFile_FileOk(object sender, CancelEventArgs e)
{
#region 打开文件(确定事件)
try
{
if (dgvData.Rows.Count > 0)
{
dgvData.Rows.Clear();
}
strFileName = openFile.FileNames;
strFilePath = openFile.SafeFileNames;
Microsoft.Office.Interop.Excel.Application excel1 = new Microsoft.Office.Interop.Excel.Application();
strFilePaths.Clear();//清空上次要导入的文件
for (int j = 0; j < strFileName.Length; j++)
{
string FileName = strFileName[j];
int rows = this.dgvData.Rows.Count;
this.dgvData.Rows.Add(1);//添加一行，不然数据不显示
//获取工作表名
object Nothing = System.Reflection.Missing.Value;
Microsoft.Office.Interop.Excel.Workbook wbs = null;
//获取EXCEL中的第一张工作簿名称
wbs = excel1.Workbooks.Open(FileName, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing,Nothing, Nothing, Nothing, Nothing, Nothing);
Microsoft.Office.Interop.Excel.Worksheet SHT1 = (Microsoft.Office.Interop.Excel.Worksheet)wbs.Sheets[1];
string sheetname = SHT1.Name;
dgvData.Rows[j].Cells["XuHao"].Value=j+1;
dgvData.Rows[j].Cells["Lujing"].Value = FileName;
dgvData.Rows[j].Cells["Gongzuobiao"].Value = sheetname;
dgvData.Rows[j].Cells["Zhuangtai"].Value = "待导入";
}
Kill(excel1);
}
catch
{
return;
}
#endregion
}

private void btnStartIn_Click(object sender, EventArgs e)
{
#region 开始导入按钮
try
{
int row = 0;
int DataNum = dgvData.Rows.Count;//将要导入的表的数量
if (DataNum == 0)
{
PublicClass.ShowMessage("请先选择文件");
return;
}
DataSet[] dss = new DataSet[DataNum];
Microsoft.Office.Interop.Excel.Application m_objExcel = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbooks workBooks = m_objExcel.Workbooks;
Object missing = 1;
//显示进度条
grbIn.Visible = true;
proJinDu.Visible = true;

string textName = "";//获取选择文件的名称
string sheetName = "";//获得选中工作表名称
// DataSet das;
for (int i = 0; i < DataNum;i++ )
{
textName = dgvData.Rows[i].Cells[1].Value.ToString(); //获得选中的文件的地址
sheetName = dgvData.Rows[i].Cells[2].Value.ToString(); //获得选中工作表名称
workBooks.Open(textName, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing);
Microsoft.Office.Interop.Excel.Sheets objSheets = m_objExcel.Worksheets;
Microsoft.Office.Interop.Excel._Worksheet objSheet = (Microsoft.Office.Interop.Excel._Worksheet)objSheets[1];

//读取excel数据
dss[i] = GetDataBySheetName07(sheetName, textName);
row += dss[i].Tables[0].Rows.Count;
}

#region 循环表
int numcout = 0;//记录上一张工作表导入成功的记录条数
for (int j = 0; j < DataNum; j++)
{
DataSet ds = new DataSet();
ds=dss[j];
int ii = 1;//导入工作表中的当前行
textName = dgvData.Rows[j].Cells[1].Value.ToString(); //获得选中的文件的地址
sheetName = dgvData.Rows[j].Cells[2].Value.ToString(); //获得选中工作表名称
//声明导入对象
workBooks.Open(textName, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing);
Microsoft.Office.Interop.Excel.Sheets objSheets = m_objExcel.Worksheets;
Microsoft.Office.Interop.Excel._Worksheet objSheet = (Microsoft.Office.Interop.Excel._Worksheet)objSheets[1];

//读取excel数据
ds = GetDataBySheetName07(sheetName, textName);
//row = ds.Tables[0].Rows.Count;
proJinDu.Minimum = 0;
proJinDu.Value = 0;
proJinDu.Maximum = row;
if (row == 0)
{
PublicClass.ShowMessage("当前选择的工作表没有数据信息!");
return;
}
#region 循环每张表的记录
for (int m = 0; m < ds.Tables[0].Rows.Count; m++)
{
try
{
ii++;

int_in++;//正在导入第XX条记录
string CarriersCode = ds.Tables[0].Rows[m][0].ToString().Trim();//承运商编码
string CarriersName = ds.Tables[0].Rows[m][1].ToString().Trim();//名称
string CarriersCity = ds.Tables[0].Rows[m][4].ToString().Trim();//省、市

//检查编码、名称是否为空
if (CarriersCode.Equals(""))
{
objSheet.Cells[ii, 7] = "编码不能为空!";
int_wrong++;
lblRecord.Text = "共" + row + "条记录,正在导入" + int_in + "条记录,错误" + int_wrong + "条！";
continue;
}
if (CarriersName.Equals(""))
{
objSheet.Cells[ii, 7] = "名称不能为空!";
int_wrong++;
lblRecord.Text = "共" + row + "条记录,正在导入" + int_in + "条记录,错误" + int_wrong + "条！";
continue;
}
if (CarriersCity.Equals(""))
{
objSheet.Cells[ii, 7] = "省市不能为空!";
int_wrong++;
lblRecord.Text = "共" + row + "条记录,正在导入" + int_in + "条记录,错误" + int_wrong + "条！";
continue;
}
//检查编码、名称是否存在
string ssql = "select * from T_Base_Carriers where Carriers_Code='" + CarriersCode + "' or Carriers_Name='" + CarriersName + "'";
DataSet ddss = DataAccess.GetDataSet(ssql, false, null);
if (ddss.Tables[0].Rows.Count > 0)
{
if (CarriersCode == ddss.Tables[0].Rows[0]["Carriers_Code"].ToString())
{

objSheet.Cells[ii, 7] = "编码已存在!";
int_wrong++;
lblRecord.Text = "共" + row + "条记录,正在导入" + int_in + "条记录,错误" + int_wrong + "条！";
continue;
}
if (CarriersName == ddss.Tables[0].Rows[0]["Carriers_Name"].ToString())
{
objSheet.Cells[ii, 7] = "名称已存在!";
int_wrong++;
lblRecord.Text = "共" + row + "条记录,正在导入" + int_in + "条记录,错误" + int_wrong + "条！";
continue;
}
}

object[] obj = new object[8];
obj[0] = doubleid;
obj[1] = CarriersCode;
obj[2] = CarriersName;
obj[3] = CarriersCity;
obj[4] = ds.Tables[0].Rows[m][2].ToString();
obj[5] = ds.Tables[0].Rows[m][3].ToString();
obj[6] = ds.Tables[0].Rows[m][5].ToString();
obj[7] = 1;
//obj[7] = Frm_Login.userID;
int hh = Convert.ToInt32(DataAccess.ExecuteNonQuery("P_Base_Carriers_Save", true, obj));
if (hh > 0)
{
int_right++;
}
else
{
int_wrong++;
}
}
catch
{
m_objExcel.Visible = true;
throw;
}

lblRecord.Text = "共" + row + "条记录,正在导入" + int_in + "条记录,错误" + int_wrong + "条！";
this.proJinDu.Value = int_in;
}
#endregion
lblRecord.Text = "共" + row + "条记录,导入成功" + (int_in - int_wrong) + "条记录,错误" + int_wrong + "条！";
this.proJinDu.Maximum = row - int_wrong;
this.proJinDu.Value = row - int_wrong;
numcout = int_right-numcout;
if (numcout < ds.Tables[0].Rows.Count)
{
dgvData.Rows[j].Cells[3].Value = "有错误记录";
}
else
{
dgvData.Rows[j].Cells[3].Value = "导入成功！";
}
}
#endregion
if (int_right < row)
{
//设置进度条不显示
grbIn.Visible = false;
proJinDu.Visible = false;
PublicClass.ShowMessage("共" + row + "条记录,导入成功" + int_right + "条记录,错误" + int_wrong + "条！");
m_objExcel.Visible = true;
System.Windows.Forms.Application.DoEvents();
return;
}
System.Windows.Forms.Application.DoEvents();
m_objExcel.Quit();
Kill(m_objExcel);
m_objExcel = null;
for (int s = 0; s < DataNum; s++)
{
dss[s].Dispose();
}

GC.Collect();
//设置进度条不显示
grbIn.Visible = false;
proJinDu.Visible = false;
PublicClass.ShowMessage("导入成功! 共" + row + "条记录,导入成功" + (int_in - int_wrong) + "条记录,错误" + int_wrong + "条！");
nowpage = 1;
DataBind();
PageState();
btnCancel.Focus();
}
catch (Exception ex)
{
PublicClass.ShowMessage(ex.Message);
}
#endregion
}