private void btnLoad() { try { OpenFileDialog oFile = new OpenFileDialog(); oFile.Filter = "Excel文件2003|*.xls|Excel文件2007|*.xlsx"; if (oFile.ShowDialog() == DialogResult.OK) { string sFilePath = oFile.FileName; string sSQL = "select * from [Sheet1$]"; FrameBaseFunction.ClsExcel clsExcel = FrameBaseFunction.ClsExcel.Instance(); DataTable dtExcel = clsExcel.ExcelToDT(sFilePath, sSQL, true); for (int i = dtExcel.Rows.Count - 1; i >= 0; i--) { string sInvCode = dtExcel.Rows[i]["存货编码"].ToString().Trim(); if (sInvCode == "") { dtExcel.Rows.RemoveAt(i); } } gridControl1.DataSource = dtExcel; } } catch (Exception ee) { } }
public static ClsExcel Instance() { if (clsExcel == null) { lock (lockHelper) { if (clsExcel == null) { clsExcel = new ClsExcel(); } } } return(clsExcel); }
private void GetGrid() { try { OpenFileDialog oFile = new OpenFileDialog(); oFile.Filter = "Excel文件2003|*.xls|Excel文件2007|*.xlsx"; if (oFile.ShowDialog() == DialogResult.OK) { string sFilePath = oFile.FileName; string sSQL = "select 供应商编码,存货编码,含税单价,税率,表头备注,表体备注 from [Sheet1$]"; FrameBaseFunction.ClsExcel clsExcel = FrameBaseFunction.ClsExcel.Instance(); DataTable dtExcel = clsExcel.ExcelToDT(sFilePath, sSQL, true); if (dtExcel.Rows.Count <= 0) { MessageBox.Show("没有导入数据!"); return; } dtExcel.Columns.Add(new DataColumn("选择", System.Type.GetType("System.Boolean"))); for (int i = 0; i < dtExcel.Rows.Count; i++) { dtExcel.Rows[i]["存货编码"] = dtExcel.Rows[i]["存货编码"].ToString().Trim(); dtExcel.Rows[i]["供应商编码"] = dtExcel.Rows[i]["供应商编码"].ToString().Trim(); } gridControl1.DataSource = dtExcel; for (int i = gridView1.RowCount - 1; i >= 0; i--) { string sInvName = gridView1.GetRowCellDisplayText(i, gridColcInvName).ToString().Trim(); string sVenName = gridView1.GetRowCellDisplayText(i, gridColcVenName).ToString().Trim(); if (sInvName == "" && sVenName == "") { gridView1.DeleteRow(i); } } chkAll.Checked = true; chkAll_CheckedChanged(null, null); } } catch (Exception ee) { MessageBox.Show("获得列表失败! \n\n原因:\n " + ee.Message, "提示", MessageBoxButtons.OK, MessageBoxIcon.Error); } }
private void btnExcel_Click(object sender, EventArgs e) { try { OpenFileDialog oFile = new OpenFileDialog(); oFile.Filter = "Excel文件2003|*.xls|Excel文件2007|*.xlsx"; if (oFile.ShowDialog() == DialogResult.OK) { string sFilePath = oFile.FileName; string sSQL = "select * from [报价单$]"; FrameBaseFunction.ClsExcel clsExcel = FrameBaseFunction.ClsExcel.Instance(); DataTable dtExcel = clsExcel.ExcelToDT(sFilePath, sSQL, true); DataColumn dc = new DataColumn(); dc.ColumnName = "GUID物料"; dtExcel.Columns.Add(dc); for (int i = 0; i < dtExcel.Rows.Count; i++) { Guid g = Guid.NewGuid(); dtExcel.Rows[i]["GUID物料"] = g.ToString(); string sInvCode = dtExcel.Rows[i]["物料编码"].ToString().Trim(); if (sInvCode == "") { continue; } DataTable dtInv = (DataTable)ItemLookUpEdit_cInvName.DataSource; DataRow[] dr = dtInv.Select("cInvCode = '" + sInvCode + "'"); if (dr.Length > 0) { sInvCode = dr[0]["cInvCode"].ToString().Trim(); dtExcel.Rows[i]["物料编码"] = sInvCode; } } gridControl2.DataSource = dtExcel; } } catch (Exception ee) { MessageBox.Show(ee.Message); } }
/// <summary> /// True 标记;False 取消标记 /// </summary> /// <param name="bType"></param> private void btnImport(bool bType) { try { OpenFileDialog oFile = new OpenFileDialog(); oFile.Filter = "Excel文件2003|*.xls|Excel文件2007|*.xlsx"; if (oFile.ShowDialog() == DialogResult.OK) { string sFilePath = oFile.FileName; string sSQL = "select 单据ID,存货编码,需求日期 from [Sheet1$]"; FrameBaseFunction.ClsExcel clsExcel = FrameBaseFunction.ClsExcel.Instance(); DataTable dtExcel = clsExcel.ExcelToDT(sFilePath, sSQL, true); if (dtExcel.Rows.Count <= 0) { MessageBox.Show("没有导入数据!"); return; } DataTable dtImport = new DataTable(); DataColumn dc = new DataColumn(); dc.ColumnName = "行号"; dtImport.Columns.Add(dc); dc = new DataColumn(); dc.ColumnName = "单据ID"; dtImport.Columns.Add(dc); dc = new DataColumn(); dc.ColumnName = "存货编码"; dtImport.Columns.Add(dc); dc = new DataColumn(); dc.ColumnName = "需求日期"; dtImport.Columns.Add(dc); dc = new DataColumn(); dc.ColumnName = "状态"; dtImport.Columns.Add(dc); ArrayList aList = new ArrayList(); for (int i = 0; i < dtExcel.Rows.Count; i++) { sSQL = "select isnull(cbCloser,0) as cbCloser from @u8.PO_Podetails where [ID] =" + dtExcel.Rows[i]["单据ID"].ToString().Trim() + " and cInvCode = '" + dtExcel.Rows[i]["存货编码"].ToString().Trim() + "'"; DataTable dtTemp = clsSQLCommond.ExecQuery(sSQL); if (dtTemp.Rows.Count == 0) { DataRow dr = dtImport.NewRow(); dr["行号"] = i + 1; dr["单据ID"] = dtExcel.Rows[i]["单据ID"].ToString().Trim(); dr["存货编码"] = dtExcel.Rows[i]["存货编码"].ToString().Trim(); dr["需求日期"] = dtExcel.Rows[i]["需求日期"].ToString().Trim(); dr["状态"] = "数据错误"; dtImport.Rows.Add(dr); continue; } else { if (dtTemp.Rows[0]["cbCloser"].ToString().Trim() != "0") { DataRow dr = dtImport.NewRow(); dr["行号"] = i + 1; dr["单据ID"] = dtExcel.Rows[i]["单据ID"].ToString().Trim(); dr["存货编码"] = dtExcel.Rows[i]["存货编码"].ToString().Trim(); dr["需求日期"] = dtExcel.Rows[i]["需求日期"].ToString().Trim(); dr["状态"] = "订单已关闭"; dtImport.Rows.Add(dr); continue; } else { if (bType) { if (chkDate(dtExcel.Rows[i]["需求日期"].ToString().Trim()) && Convert.ToDateTime(dtExcel.Rows[i]["需求日期"]) >= Convert.ToDateTime(FrameBaseFunction.ClsBaseDataInfo.sLogDate)) { sSQL = "update @u8.PO_Podetails set cDefine31 = '" + dtExcel.Rows[i]["需求日期"].ToString().Trim() + "' where [ID] =" + dtExcel.Rows[i]["单据ID"].ToString().Trim() + " and cInvCode = '" + dtExcel.Rows[i]["存货编码"].ToString().Trim() + "'"; } else { DataRow dr = dtImport.NewRow(); dr["行号"] = i + 1; dr["单据ID"] = dtExcel.Rows[i]["单据ID"].ToString().Trim(); dr["存货编码"] = dtExcel.Rows[i]["存货编码"].ToString().Trim(); dr["需求日期"] = dtExcel.Rows[i]["需求日期"].ToString().Trim(); dr["状态"] = "需求日期错误,请检查!"; dtImport.Rows.Add(dr); continue; } } else { sSQL = "update @u8.PO_Podetails set cDefine31 = null where [ID] =" + dtExcel.Rows[i]["单据ID"].ToString().Trim() + " and cInvCode = '" + dtExcel.Rows[i]["存货编码"].ToString().Trim() + "'"; } aList.Add(sSQL); } } } if (dtImport.Rows.Count > 0) { FrmImport fImport = new FrmImport(dtImport); fImport.StartPosition = FormStartPosition.CenterParent; fImport.ShowDialog(); } if (aList.Count > 0) { clsSQLCommond.ExecSqlTran(aList); if (bType) { MessageBox.Show("导入共:" + aList.Count + "条数据成功!"); } else { MessageBox.Show("删除共:" + aList.Count + "条数据成功!"); } btnSEL(); } else { MessageBox.Show("无数据导入!"); } } } catch (Exception ee) { throw new Exception("导入Excel失败:" + ee.Message); } }