热门标签 | HotTags
当前位置:  开发笔记 > 编程语言 > 正文

导入Excel表中的数据

第一步:转换导入的文件privatevoidbtnSelectFile_Click(objectsender,EventArgse){OpenFileDialo

第一步:转换导入的文件

  private void btnSelectFile_Click(object sender, EventArgs e)
{
OpenFileDialog ofd
= new OpenFileDialog();
ofd.Title
= "Excel文件";
ofd.FileName
= "";
ofd.InitialDirectory
= Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments);
ofd.Filter
= "Excel文件(*.xls)|*.xls";
ofd.ValidateNames
= true;
ofd.CheckFileExists
= true;
ofd.CheckPathExists
= true;
if (openFileDialog.ShowDialog() == DialogResult.OK)
{
txtFileName.Text
= openFileDialog.FileName;
}

}

 第二步:执行导入

    private void btnImport_Click(object sender, EventArgs e)
{
if (cboSaleStock.SelectedIndex == -1)
{
MessageBox.Show(
"没有选择销售仓库");
return;
}
if (cboShop.SelectedIndex == -1)
{
MessageBox.Show(
"没有选择销售门店");
return;
}
if (string.IsNullOrEmpty(txtFileName.Text) || txtFileName.Text.Length == 0)
{
MessageBox.Show(
"没有选择Excel文件!无法进行数据导入");
return;
}
DataTable dt
= ExcelToDataTable(txtFileName.Text);
string sql = string.Empty;
for (int i = 0; i )
{
sql += string.Format(@" declare @sno{4} int,@dgrID{4} varchar(20)
set @dgrID{4}=(select top 1 dgoodsresultid from tbdGoodsResult where shopID='{0}' and stockid='{1}' and goodsid='{2}')
if @dgrID{4} is null begin
set @sno{4}=(select top 1 sno from tbpKeyManager where tablename='tbdGoodsResult')
if @sno{4} is null begin
insert into tbpKeyManager(tablename,sno,trdate) values('tbdGoodsResult',1,CONVERT(varchar(12),getdate(),112 ))
set @sno{4}=1
end else begin
set @sno{4}=@sno{4}+1
update tbpKeyManager set sno=sno+1 where tablename='tbdGoodsResult'
end
insert into tbdGoodsResult(dgoodsresultid,shopid,stockid,goodsid,pthismonthqty)
values(cast(@sno{4} as varchar(11)),'{0}','{1}','{2}','{3}')
end else begin
update tbdGoodsResult set pthismOnthqty={3} where dgoodsresultid=@dgrID{4}
end
", cboShop.SelectedValue, cboSaleStock.SelectedValue, dt.Rows[i][2], dt.Rows[i][1], i);
}
string msg = "";
if (ws.ExecuteNonQueryWithTrans(sql, ref msg) > 0)
{
MessageBox.Show(
"导入数据成功");
}
if (msg.Length > 0)
{
MessageBox.Show(
"导入数据失败:\r\n" + msg);
}
}

第三步:Excel数据导入

      private DataTable ExcelToDataTable(string filePath)
{
//根据路径打开一个Excel文件并将数据填充到DataSet中
string strCOnn= "Provider=Microsoft.Jet.OLEDB.4.0;Data Source = " + filePath + ";Extended Properties ='Excel 8.0;HDR=NO;IMEX=1'";//导入时包含Excel中的第一行数据,并且将数字和字符混合的单元格视为文本进行导入
OleDbConnection cOnn= new OleDbConnection(strConn);
conn.Open();
string strExcel = "";
OleDbDataAdapter myCommand
= null;
DataSet ds
= null;
strExcel
= "select * from [sheet1$]";
myCommand
= new OleDbDataAdapter(strExcel, strConn);
ds
= new DataSet();
myCommand.Fill(ds,
"table1");
DataTable dt
= ds.Tables[0];
dt.Columns.Add(
"goodsid");
string joinSql = "select '' pluno";
foreach (DataRow dr in dt.Rows)
{
joinSql
+= string.Format(" union all select '{0}' pluno", dr[0]);
}
string sql = string.Format(@"select tbgoods.goodsid,tbgoods.pluno from tbgoods join ({0}) as t on t.pluno=tbgoods.pluno ", joinSql);
DataTable dtGoods
= cf.GetDataSet(sql).Tables[0];
foreach (DataRow dr in dt.Rows)
{
foreach (DataRow dr1 in dtGoods.Rows)
{
if (dr[0].ToString().ToUpper() == dr1["pluno"].ToString())
{
dr[
"goodsid"] = dr1["goodsid"].ToString();
}
}
}
return ds.Tables[0];
}

 


推荐阅读
author-avatar
可爱de小蜗牛
这个家伙很懒,什么也没留下!
PHP1.CN | 中国最专业的PHP中文社区 | DevBox开发工具箱 | json解析格式化 |PHP资讯 | PHP教程 | 数据库技术 | 服务器技术 | 前端开发技术 | PHP框架 | 开发工具 | 在线工具
Copyright © 1998 - 2020 PHP1.CN. All Rights Reserved | 京公网安备 11010802041100号 | 京ICP备19059560号-4 | PHP1.CN 第一PHP社区 版权所有