using System; using System.Data; using System.Configuration; using System.Collections; using System.Web; using System.Web.Security; using System.Web.UI; using System.Web.UI.WebControls; using System.Web.UI.WebControls.WebParts; using System.Web.UI.HtmlControls; using GThinking.N_MISPRO; using GThinking.N_MISPRO.Data; using GThinking.N_MODEL.Page; using GThinking.N_WORKFLOW; using GThinking.N_MODEL.Dialog; using GThinking.N_PUBLIC; using GThinking.N_MISPRO.Utility; using GThinking.N_CONTROL; using System.IO; using System.Data.OleDb; using System.Data.Common; using GThinking.N_MISPROAS; using System.Text.RegularExpressions; using System.Collections.Generic; namespace GThinking.N_XLJC { [TableMap(DB = "DBERP", DbTableName = "BJCPXX", Umid = 101421013, IsWorkFlow = false, UseWhr = false, IsAttach = false, QuerySelSql = "", EntitySelSql = "", InsertSql = "", UpdateSql = "", DeleteSql = "")] public class BJCPWHDR : EntityManager { } public partial class BJCPWHDRInfo : SingleCardBase { BJCPWHDR entity = new BJCPWHDR(); #region page类事件 protected void Page_Load(object sender, EventArgs e) { this.UMID = 101421013; this.UMName = "报价产品维护"; base.CurMaster = Master; base.CurEntity = entity; this.PageHeight = 180; this.PageWidth = 420; Master.EnableAttachBtn = false; Master.EnableCancelBtn = false; Master.EnableCopyBtn = false; Master.EnableDeleteBtn = false; Master.EnableEditBtn = false; Master.EnableInsertBtn = false; Master.EnablePrintBtn = false; Master.EnableRefreshBtn = false; Master.EnableSaveBtn = false; fupdPost.FileSaveType = gtAsyncFileUpload.DFileSaveType.SaveToPath; if (AppRuntime.BitType == AppBitType.Bit32) fupdPost.Filter = "XLS"; else // AppBitType.Bit64 fupdPost.Filter = "XLS,XLSX"; base.InitPage(); } #endregion #region 控件事件 protected void btnQD_Click(object sender, EventArgs e) { if (fupdPost.FilePath.Equals("")) { ShowMessage("请选择需要导入的文件！"); return; } TDatabase dberp = TContextDatabases.GetDatabase("DBERP"); TSQL sSQL = new TSQL(dberp); try { lblTitle.Text = "正在导入数据，请稍后..."; dberp.BeginTrans(); DataSet aDataExcel = new DataSet(); if (AppRuntime.BitType == AppBitType.Bit32) aDataExcel = ReadFromExcelFile(fupdPost.FilePath, 0, ""); else // AppBitType.Bit64 ReadWJ(fupdPost.FilePath, "", ref aDataExcel); ExcelColumnValid(aDataExcel); DateTime WHSJ = MISproUtils.GetServerTime();//获取服务器时间 N_PUBLIC2.XLUtils.InsBJCPXX(dberp, sSQL, aDataExcel, WHSJ); dberp.CommitTrans(); if (PageParam.GetRTrimString("txtNeedCallBackCID").Equals("")) { ShowMessage("导入成功！"); CloseWindow(); } else { DialogOperationScript dos = new DialogOperationScript(this); dos.SetOwnerControlValue(PageParam.GetRTrimString("txtNeedCallBackCID"), "Y"); dos.CloseSelf(); dos.Execute(); } } catch (Exception ex) { lblTitle.Text = ""; dberp.RollbackTrans(); ShowMessage(ex.Message); } } protected void btnQX_Click(object sender, EventArgs e) { CloseWindow(); } #endregion #region 自定义事件 //AppBitType.Bit32运行时，只能读取xls格式，导入Excel使用该方法，解决混合列读取数据丢失的问题 private DataSet ReadFromExcelFile(string filePath, int headerRowIndex, string SheetName) { string sMessage = ""; DataSet ds = TExcelData.ReadExcel_NPOI(filePath, SheetName, headerRowIndex, true, ref sMessage); if (!sMessage.Trim().Equals("")) { throw new Exception("文件读取失败！具体原因：" + sMessage); } return ds; } private void ExcelColumnValid(DataSet ds) { Dictionary ColumnNames = new Dictionary(); ColumnNames.Add("0", "型号"); ColumnNames.Add("1", "规格"); ColumnNames.Add("2", "标准"); ColumnNames.Add("3", "电压等级"); ColumnNames.Add("4", "外径"); ColumnNames.Add("5", "最小弯曲倍数"); ColumnNames.Add("6", "导体结构信息"); for (int i = 0; i < ds.Tables[0].Columns.Count; i++) { try { if (!ds.Tables[0].Columns[i].ColumnName.Replace("*", "").Trim().Equals(ColumnNames[i.ToString()].Trim())) { throw new Exception("导入文件的内容格式不正确，第" + (i + 1) + "列的标题应为【" + ColumnNames[i.ToString()].Trim() + "】，请按导出示例中要求的内容格式调整导入文件！"); } } catch (Exception e) { throw new Exception("导入文件的路线内容格式不正确，请按导出示例中要求的内容格式调整导入文件！错误信息：" + e.Message); } } for (int i = 0; i < ds.Tables[0].Rows.Count; i++) { DataRow Row = ds.Tables[0].Rows[i]; string sXH = sGetCellTest(Row, 0).Trim(); string sGG = sGetCellTest(Row, 1).Trim(); string sBZ = sGetCellTest(Row, 2).Trim(); string sDYDJ = sGetCellTest(Row, 3).Trim(); string sWJ = sGetCellTest(Row, 4).Trim(); string sZXWQBS = sGetCellTest(Row, 5).Trim(); string sDTJGXX = sGetCellTest(Row, 6).Trim(); if (sXH.Equals("")) throw new Exception("第" + (i + 1) + "行型号为空，导入失败！"); if (sGG.Equals("")) throw new Exception("第" + (i + 1) + "行规格为空，导入失败！"); if (sBZ.Equals("")) throw new Exception("第" + (i + 1) + "行标准为空，导入失败！"); if (sDYDJ.Equals("")) throw new Exception("第" + (i + 1) + "行电压等级为空，导入失败！"); if (!XLXHIDValid(sXH)) throw new Exception("第" + (i + 1) + "行型号不存在，导入失败！"); if (!XLBZIDValid(sBZ)) throw new Exception("第" + (i + 1) + "行标准不存在，导入失败！"); if (!DYDJIDValid(sDYDJ)) throw new Exception("第" + (i + 1) + "行电压等级不存在，导入失败！"); } } private void ReadWJ(string sPath, string sSheetName, ref DataSet aDataExcel) { string sMessage = ""; aDataExcel = TExcelData.ReadExcel(sPath.Trim(), sSheetName, "", false, ref sMessage); if (!sMessage.Trim().Equals("")) { throw new Exception("文件读取失败！具体原因：" + sMessage); } } private string sGetCellTest(DataRow Row, int ColumnIndex) { return Row[ColumnIndex].ToString().Trim().Replace("\r\n", "").Replace("\n", ""); } private bool XLXHIDValid(string sXLXHID) { if (CMISproDataBase.ExistsRecord("DBERP", "select 1 from XLXHB where XLXHB_XLXHID='" + sXLXHID + "'")) return true; return false; } private bool XLBZIDValid(string sXLBZID) { if (CMISproDataBase.ExistsRecord("DBERP", "select 1 from XLBZ where XLBZ_XLBZID='" + sXLBZID + "'")) return true; return false; } private bool DYDJIDValid(string sDYDJID) { if (CMISproDataBase.ExistsRecord("DBERP", "select 1 from XLDYDJB where XLDYDJB_DYDJID='" + sDYDJID + "'")) return true; return false; } #endregion } }