<!--导入Excel文件-->
<table width="99%" border="0" align="center" cellpadding="0" id="searchtable" cellspacing="0" bgcolor="#CCCCCC">
<tr>
<td class="Blue" style=" padding-left:1px"><img src="../../../images/Main/arrow_1.jpg" width="12" height="18" align="absmiddle" /> 上传Excel文件
</td>
</tr>
<tr>
<td bgcolor="#FFFFFF">
<table width="100%" border="0" cellpadding="2" cellspacing="1" bgcolor="#CCCCCC"
class="table">
<tr class="table-item">
<td width="12%" height="20" bgcolor="#E7E7E7" align="center">
<a href="Bom上传模板.rar">模板下载</a>
</td>
<td width="48%" bgcolor="#FFFFFF">
<input id="fileExportExcel" style="width:100%" type="file" runat="server"/>
</td>
<td width="10%" bgcolor="#E7E7E7" align="center">
<asp:Button ID="btn_excel" Text="上传Excel" OnClick="btn_excel_Click" runat="server"/>
</td>
<td width="30%" bgcolor="#FFFFFF">
<asp:Label ID="lbl_result" ForeColor="#FF0000" runat="server" Text=""></asp:Label>
</td>
</tr>
</table>
</td>
</tr>
</table>
<!--导入结束-->
//上传excel
protected void btn_excel_Click(object sender, EventArgs e)
{
string strcompanycd = ((UserInfoUtil)SessionUtil.Session["UserInfo"]).CompanyCD;
string struid = ((UserInfoUtil)SessionUtil.Session["UserInfo"]).EmployeeNum;
try
{
#region
/*文件名*/
string FileName = string.Empty;
/*上传后完整的文件路径包含文件名*/
string FileNewUrl = string.Empty;
#region 上传验证
/*获取公司的上传路径*/
string FileUrl = ProductInfoBus.GetCompanyUpFilePath(strcompanycd);
/*验证该公司路径是否存在 不存在则创建*/
DirectoryInfo dir = new DirectoryInfo(FileUrl);
if (!dir.Exists)
{
try
{
dir.Create();
}
catch (Exception ex)
{
this.lbl_result.Text = ex.ToString();
return;
}
}
/*验证是否选择了文件*/
if (string.IsNullOrEmpty(fileExportExcel.PostedFile.FileName))
{
this.lbl_result.Text ="请选择需要导入的Excel文件";
return;
}
/*验证文件类型*/
string FileExtension = fileExportExcel.PostedFile.FileName.Split('.')[1].ToUpper();
if (FileExtension != "XLS" && FileExtension != "XLSX")
ErrorMsg += "文件错误,请上传正确的Excel文件\\n";
/*判断是否存在异常*/
if (!string.IsNullOrEmpty(ErrorMsg))
{
this.lbl_result.Text = ErrorMsg;
return;
}
/*上传文件*/
string strID = Guid.NewGuid().ToString();
FileName = strID + "." + FileExtension.ToLower();
FileNewUrl = FileUrl + "\\" + FileName;
try
{
fileExportExcel.PostedFile.SaveAs(FileNewUrl);
}
catch (Exception ex)
{
this.lbl_result.Text = ex.ToString();
}
#endregion
#endregion
DataSet ds = ToDataTable(FileNewUrl);
//先删除数据库中该专案的所有非标料号
string strSql = "delete officedba.ProductInfo_ExcelTemp where ProjectID='" + txtReportNo.Text.ToString().Trim() + "' and CompanyCD='" + strcompanycd + "'";
DataTable dtD = XBase.Business.Office.ProductionManager.BomBus.GetListBySql(strSql);
int i = 0;
int j = 0;
int ii = 0;
int jj = 0;
#region 非标件写入表单
foreach (DataRow dr in ds.Tables["零件清单$"].Rows)
{
if (dr[1].ToString().Trim() != string.Empty && dr[5].ToString().Trim() != string.Empty && dr[4].ToString().Trim() != string.Empty)
{
try
{
int.Parse(dr[0].ToString().Trim());
int.Parse(dr[5].ToString().Trim());
int.Parse(dr[6].ToString().Trim());
ProductInfo_ExcelTempModel.ID = int.Parse(dr[0].ToString().Trim());
ProductInfo_ExcelTempModel.DrawingNo = dr[1].ToString().Trim();
ProductInfo_ExcelTempModel.ProductName = dr[3].ToString().Trim();
ProductInfo_ExcelTempModel.Material = dr[4].ToString().Trim();
ProductInfo_ExcelTempModel.Special = "";
ProductInfo_ExcelTempModel.Types = "";
ProductInfo_ExcelTempModel.UnitID = "个";
ProductInfo_ExcelTempModel.IsHalf = dr[7].ToString().Trim();
ProductInfo_ExcelTempModel.QPA = dr[5].ToString().Trim();
ProductInfo_ExcelTempModel.Spare = "0";
ProductInfo_ExcelTempModel.Brand = "";
ProductInfo_ExcelTempModel.Size = dr[9].ToString().Trim();
ProductInfo_ExcelTempModel.remark = dr[8].ToString().Trim();
ProductInfo_ExcelTempModel.OpeType = dr[10].ToString().Trim();
ProductInfo_ExcelTempModel.CompanyCD = strcompanycd;
ProductInfo_ExcelTempModel.LB = "FBJ";
ProductInfo_ExcelTempModel.BH = "";
string ProjectID = txtReportNo.Text.ToString().Trim();
if (ProjectID == string.Empty)
{
DataTable dt = BomImportBus.GetFBJProjectId(ProductInfo_ExcelTempModel.DrawingNo, ProductInfo_ExcelTempModel.CompanyCD);
ProductInfo_ExcelTempModel.ProjectID = dt.Rows[0]["ProjectId"].ToString();
int result = BomImportBus.AddBomImport(ProductInfo_ExcelTempModel);
if (result == 1)
{
i = i + 1;
}
else
{
j = j + 1;
}
}
else
{
ProductInfo_ExcelTempModel.ProjectID = txtReportNo.Text.ToString().Trim();
int result = BomImportBus.AddBomImport(ProductInfo_ExcelTempModel);
if (result == 1)
{
i = i + 1;
}
else
{
j = j + 1;
}
}
}
catch (Exception ex)
{
continue;
}
}
}
#endregion
#region 标件写入表单
foreach (DataRow dr in ds.Tables["标准件清单$"].Rows)
{
if (dr[2].ToString().Trim() != string.Empty && dr[7].ToString().Trim() != string.Empty)
{
try
{
int.Parse(dr[0].ToString().Trim());
int.Parse(dr[7].ToString().Trim());
ProductInfo_ExcelTempModel.ID = int.Parse(dr[0].ToString().Trim());
ProductInfo_ExcelTempModel.DrawingNo = dr[1].ToString().Trim();
ProductInfo_ExcelTempModel.BH = dr[2].ToString().Trim();
ProductInfo_ExcelTempModel.ProductName = dr[3].ToString().Trim();
ProductInfo_ExcelTempModel.Material = "";
ProductInfo_ExcelTempModel.Special = dr[4].ToString().Trim();
ProductInfo_ExcelTempModel.Types = dr[5].ToString().Trim();
ProductInfo_ExcelTempModel.UnitID = dr[6].ToString().Trim();
ProductInfo_ExcelTempModel.IsHalf = "";
ProductInfo_ExcelTempModel.QPA = dr[7].ToString().Trim();
ProductInfo_ExcelTempModel.Spare = dr[8].ToString().Trim() == "" ? "0" : dr[8].ToString().Trim();
ProductInfo_ExcelTempModel.Brand = dr[10].ToString().Trim();
ProductInfo_ExcelTempModel.Size = "";
ProductInfo_ExcelTempModel.remark = dr[11].ToString().Trim();
ProductInfo_ExcelTempModel.OpeType = dr[12].ToString().Trim();
ProductInfo_ExcelTempModel.CompanyCD = strcompanycd;
ProductInfo_ExcelTempModel.LB = "BZJ";
string ProjectID=txtReportNo.Text.ToString().Trim();
DataTable dt = BomImportBus.GetBJProjectId(ProductInfo_ExcelTempModel.ProductName, ProductInfo_ExcelTempModel.Special, ProductInfo_ExcelTempModel.Brand, ProductInfo_ExcelTempModel.CompanyCD);
if (ProjectID == string.Empty)
{
ProductInfo_ExcelTempModel.ProjectID = dt.Rows[0]["ProjectId"].ToString();
int result = BomImportBus.AddBomImport(ProductInfo_ExcelTempModel);
if (result == 1)
{
ii = ii + 1;
}
else
{
jj = jj + 1;
}
}
else
{
ProductInfo_ExcelTempModel.ProjectID = txtReportNo.Text.ToString().Trim();
int result = BomImportBus.AddBomImport(ProductInfo_ExcelTempModel);
if (result == 1)
{
ii = ii + 1;
}
else
{
jj = jj + 1;
}
}
}
catch (Exception ex)
{
continue;
}
}
}
#endregion
//GetData(txtReportNo.Text.ToString().Trim());
//MessageBox("非标件成功上传" + i.ToString() +"笔,失败"+j.ToString()+"笔;标准件成功上传"+ii.ToString()+"笔,失败"+jj.ToString()+"笔", "msg1");
lbl_result.Text = "非标件成功读取" + i.ToString() + "笔,失败" + j.ToString() + "笔;标准件成功读取" + ii.ToString() + "笔,失败" + jj.ToString() + "笔";
btn_input.Visible = true;
//dtb = BomImportBus.GetBomImport(txtReportNo.Text.ToString().Trim(), strcompanycd);
dtb = BomImportBus.GetBomImport(ProductInfo_ExcelTempModel.ProjectID, strcompanycd);
this.setup1.Enabled = true;
this.setup2.Enabled = false;
this.setup6.Enabled = false;
this.tr_result.Visible = false;
}
catch (Exception ex)
{
lbl_result.Text = "读取Excel失败" + ex.ToString();
//MessageBox("上传失败"+ex.ToString(),"msg1");
//imgExportExcelUC.Visible = false;
btn_input.Visible = false;
return;
}
}
public static DataSet ToDataTable(string filePath)
{
string connStr = "";
string fileType = System.IO.Path.GetExtension(filePath);//获取文件的后缀名
if (string.IsNullOrEmpty(fileType)) return null;
if (fileType == ".xls")
connStr = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + filePath + ";" + ";Extended Properties=\"Excel 8.0;HDR=YES;IMEX=1\"";
else
connStr = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + filePath + ";" + ";Extended Properties=\"Excel 12.0;HDR=YES;IMEX=1\"";
string sql_F = "Select * FROM [{0}]";
OleDbConnection conn = null;
OleDbDataAdapter da = null;
DataTable dtSheetName = null;
DataSet ds = new DataSet();
try
{
// 初始化连接,并打开
conn = new OleDbConnection(connStr);
conn.Open();
// 获取数据源的表定义元数据
string SheetName = "";
dtSheetName = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
// 初始化适配器
da = new OleDbDataAdapter();
for (int i = 0; i < dtSheetName.Rows.Count; i++)
{
SheetName = (string)dtSheetName.Rows[i]["TABLE_NAME"];
if (SheetName.Contains("$") && !SheetName.Replace("'", "").EndsWith("$"))
{
continue;
}
//sql_F = "Select * FROM [{0}] ";
if (SheetName == "零件清单$")
{
sql_F = "Select * FROM [{0}] where F2 <> '' AND F6 <> '' ";
}
else if (SheetName == "标准件清单$")
{
sql_F = "Select * FROM [{0}] where F3 <> '' ";
}
else
{
sql_F = "Select * FROM [{0}] where F1 <> ''";
}
da.SelectCommand = new OleDbCommand(String.Format(sql_F, SheetName), conn);
DataSet dsItem = new DataSet();
da.Fill(dsItem, SheetName);
ds.Tables.Add(dsItem.Tables[0].Copy());
}
}
catch (Exception ex)
{
}
finally
{
// 关闭连接
if (conn.State == ConnectionState.Open)
{
conn.Close();
da.Dispose();
conn.Dispose();
}
}
return ds;
}