作者:可爱de小蜗牛 | 来源:互联网 | 2023-09-06 11:58
第一步:转换导入的文件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];
}