/// <summary> /// 根据分类查询配件库存 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> private void TreeVPartCategory_AfterSelect(object sender, TreeViewEventArgs e) { try { //显示查询进度 ProgressBarFrm ProgFrm = new ProgressBarFrm(); ProgFrm.MaxNum = 100; ProgFrm.ShowDialog(); CategoryName = e.Node.Text.ToString();//获取选中节点分类名称 if (CategoryName == "全部配件") { CategoryName = string.Empty; } string QueryWhere = QueryPartWhereCondition();//获取查询条件 if (CheckBGuarantyStock.Checked) { GetYuTongPartList(QueryWhere);//宇通三包库存查询 } else { GetPartList(QueryWhere);//配件库存查询 } } catch (Exception ex) { MessageBoxEx.Show(ex.Message, "异常提示", MessageBoxButtons.OK, MessageBoxIcon.Question); } }
/// <summary> /// 配件库存查询 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> private void btnSearch_Click(object sender, EventArgs e) { string QueryWhere = QueryPartWhereCondition();//获取查询条件 //显示查询进度 ProgressBarFrm ProgFrm = new ProgressBarFrm(); ProgFrm.MaxNum = 100; ProgFrm.ShowDialog(); if (CheckBGuarantyStock.Checked) { GetYuTongPartList(QueryWhere);//宇通三包库存查询 } else { GetPartList(QueryWhere);//配件库存查询 } }
private void btnSearch_Click(object sender, EventArgs e) { try { string QueryStr = string.Empty;//查询条件 QueryStr += txtBusinUnitCode.Caption.Trim().ToString(); QueryStr += txtBusinUnitName.Caption.Trim().ToString(); QueryStr += txtContact.Caption.Trim().ToString(); QueryStr += txtTelephone.Caption.Trim().ToString(); //显示查询进度 ProgressBarFrm ProgFrm = new ProgressBarFrm(); ProgFrm.MaxNum = 100; ProgFrm.ShowDialog(); BindSupData(); //获取供应商信息 BindCustData(); //获取客户信息 if (string.IsNullOrEmpty(QueryStr)) { //获取所有客户和供应商信息 GetSupMsg(); GetCustMsg(); } else { if (CustCounts == 0 && SuppCounts != 0) { GetSupMsg();//获取供应商信息 } else if (SuppCounts == 0 && CustCounts != 0) { GetCustMsg(); //获取客户信息 } else { //获取所有客户和供应商信息 GetSupMsg(); GetCustMsg(); } } } catch (Exception ex) { MessageBoxEx.Show(ex.Message, "异常提示", MessageBoxButtons.OK, MessageBoxIcon.Question); } }
/// <summary> /// 导入Excel与单据匹配字段的所有数据 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> private void btnImportExcelData_Click(object sender, EventArgs e) { try { if (CheckOutExcelField())//验证是否选择匹配字段 { for (int i = 0; i < dgMatchList.Rows.Count; i++) { DataGridViewComboBoxCell dgComBox = (DataGridViewComboBoxCell)dgMatchList.Rows[i].Cells["ExcelField"];//转换为combox单元格 //获取Excel匹配的字段名 MatchFieldHTable.Add(dgMatchList.Rows[i].Cells["BillField"].Value.ToString(), dgComBox.EditedFormattedValue.ToString()); } DialogResult = DialogResult.OK; //关闭当前对话框 ProgressBarFrm ProgFrm = new ProgressBarFrm(); ProgFrm.MaxNum = ExcelTable.Rows.Count; //获取最大导入记录行 ProgFrm.ShowDialog(); } } catch (Exception ex) { MessageBoxEx.Show(ex.Message, "异常提示", MessageBoxButtons.OK, MessageBoxIcon.Question); } }
/// <summary> /// 利用office组件读取Excel表格中配件信息 /// </summary> /// <returns></returns> //public static DataTable ImportExcelFile(string FilesPathName) //{ // try // { // string ExcelFilePath = string.Empty;//存储Excel文件路径 // string ExcelConnStr = string.Empty;//读取Excel表格字符串 // DataTable ExcelTable = null;//获取Excel表格中的数据 // string SheetName = string.Empty;//Excel中表名 // string ExcelSql = string.Empty;//查询Excel表格 // ExcelFilePath = FilesPathName;//获取选定文件的路径名 // if (Path.GetExtension(ExcelFilePath) != ".xlsx" && Path.GetExtension(ExcelFilePath) != ".xls") // { // MessageBoxEx.Show("请您输入Excel格式的文件", "提示", MessageBoxButtons.OK, MessageBoxIcon.Asterisk); // } // else if (ExcelFilePath == string.Empty) // { // MessageBoxEx.Show("请您选择要导入的配件信息文件", "提示", MessageBoxButtons.OK, MessageBoxIcon.Asterisk); // } // else // { // //HDR=YES代表Excel第一行是标题不作为数据使用,IMEX三种模式:0代表写入模式,1代表读取模式,2代表链接模式(支持同时读取和写入) // ExcelConnStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + ExcelFilePath + ";Extended Properties='Excel 12.0;HDR=YES;IMEX=1;'"; // OleDbConnection DbCon = new OleDbConnection(ExcelConnStr);//打开数据源连接 // DbCon.Open(); // ExcelTable = DbCon.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "table" });//获取Excel表格中的数据 // SheetName = ExcelTable.Rows[0]["table_name"].ToString();//获取Excel表名称 // ExcelSql = "select * from [" + SheetName + "]"; // OleDbDataAdapter DbAdapter = new OleDbDataAdapter(ExcelSql, DbCon);//执行数据查询 // DataSet ds = new DataSet(); // DbAdapter.Fill(ds, SheetName);//填充数据集 // DbCon.Close();//关闭数据源连接 // ExcelTable = ds.Tables[0]; // } // return ExcelTable; // } // catch (Exception ex) // { // MessageBoxEx.Show(ex.Message, "异常提示", MessageBoxButtons.OK, MessageBoxIcon.Question); // return null; // } //} /// <summary> /// 利用NPOI创建Excel文件 /// </summary> /// <param name="ExcelTable">excel表格名称</param> /// <param name="SheetName">sheet表名</param> public static void NPOIExportExcelFile(DataTable NPOIExcelTable, string SheetName) { try { string SaveExcelName = string.Empty;//保存的Excel文件名称 SaveFileDialog SFDialog = new SaveFileDialog(); SFDialog.DefaultExt = "xls"; SFDialog.Filter = "Excel文件(*.xls)|*.xls"; SFDialog.ShowDialog(); SFDialog.CheckPathExists = true; SFDialog.CheckFileExists = true; SaveExcelName = SFDialog.FileName;//获取保存的Excel文件名称 if (File.Exists(SaveExcelName)) { if (IsExcelOpen(SaveExcelName)) { File.Delete(SaveExcelName); //替换同名文件 } else { return; } } if (SaveExcelName.IndexOf(":") < 0) { return; //点击取消按钮返回 } HSSFWorkbook WkBk = new HSSFWorkbook(); //创建工作表流 MemoryStream ms = new MemoryStream(); //创建支持内存的存储区流 HSSFSheet sht = (HSSFSheet)WkBk.CreateSheet(SheetName); //创建Excel表格 sht.CreateFreezePane(0, 1, 0, 1); //设置冻结首行 HSSFRow HeaderRow = (HSSFRow)sht.CreateRow(0); //创建标题行 int TotalCount = NPOIExcelTable.Rows.Count; int rowRead = 0; //读取行数 ProgressBarFrm ProgBarFrm = new ProgressBarFrm(); ProgBarFrm.MaxNum = TotalCount; //获取最大记录行 ProgBarFrm.ShowDialog(); //显示进度条 for (int i = 0; i < NPOIExcelTable.Columns.Count; i++) { HeaderRow.CreateCell(i).SetCellValue(NPOIExcelTable.Columns[i].ColumnName.ToString()); //创建标题列 SetXlsHeaderStyle(HeaderRow, WkBk, i); //设置标题样式 sht.SetColumnWidth(i, 1000 * 5); //设置列宽 } //创建Excel数据行项 for (int j = 0; j < NPOIExcelTable.Rows.Count; j++) { HSSFRow DatasRow = (HSSFRow)sht.CreateRow(j + 1); //创建数据行 for (int k = 0; k < NPOIExcelTable.Columns.Count; k++) { //填充Excel表数据 DatasRow.CreateCell(k).SetCellValue(NPOIExcelTable.Rows[j][k].ToString()); } rowRead++; //自动增长行号 ProgressBarFrm.CurrentValue = rowRead; //传递当前值 Application.DoEvents(); //处理当前在消息队列中所有windows消息 } //写入内存流数据 WkBk.Write(ms); ms.Flush();//清空缓存 ms.Position = 0; sht = null; HeaderRow = null; WkBk = null; FileStream fs = new FileStream(SaveExcelName, FileMode.CreateNew, FileAccess.Write); //创建文件流 byte[] dataXls = ms.ToArray(); //把文件流转换为字节数组 fs.Write(dataXls, 0, dataXls.Length); fs.Flush(); //清除缓存 fs.Close(); //关闭文件流 dataXls = null; ms = null; fs = null; } catch (Exception ex) { MessageBoxEx.Show(ex.Message, "异常提示", MessageBoxButtons.OK, MessageBoxIcon.Question); } }