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);
}
}
