private void barIn_ItemClick(object sender, DevExpress.XtraBars.ItemClickEventArgs e) { if (this.openFileDialog1.ShowDialog() == DialogResult.OK) { ExcelClass.ExcelClass1 ec = new Book.UI.ExcelClass.ExcelClass1(); ec.Open(openFileDialog1.FileName); try { BL.V.BeginTransaction(); Model.BGHandbookRange bGHandbookRange = null; this.action = "insert"; Model.BGHandbookRangeDetail detail = null; for (int i = 1; i <= ec.wb.Worksheets.Count; i++) { bGHandbookRange = new Book.Model.BGHandbookRange(); bGHandbookRange.DetailProducts = new List <Model.BGHandbookRangeDetail>(); bGHandbookRange.DetailMaterials = new List <Model.BGHandbookRangeDetail>(); bGHandbookRange.BGHandbookRangeId = Guid.NewGuid().ToString(); bGHandbookRange.BGHandbookRangeDate = DateTime.Now; Microsoft.Office.Interop.Excel.Worksheet ws = (Microsoft.Office.Interop.Excel.Worksheet)ec.wb.Worksheets[i]; bGHandbookRange.CompanyNameAndId = ((Microsoft.Office.Interop.Excel.Range)ws.Cells[3, 1]).Text.ToString().Substring(((Microsoft.Office.Interop.Excel.Range)ws.Cells[3, 1]).Text.ToString().IndexOf(':') + 1); if (openFileDialog1.FileName.Contains("成品")) { //if (ws.Name != "草稿") //{ for (int j = 6; j < 10000; j++) { if (((Microsoft.Office.Interop.Excel.Range)ws.Cells[j, 1]).Text.ToString() == "经办关员意见:" || (((Microsoft.Office.Interop.Excel.Range)ws.Cells[j, 1]).Text.ToString() == "" && ((Microsoft.Office.Interop.Excel.Range)ws.Cells[j, 2]).Text.ToString() == "")) { break; } detail = new Book.Model.BGHandbookRangeDetail(); detail.BGHandbookRangeDetailId = Guid.NewGuid().ToString(); detail.BGHandbookRangeId = bGHandbookRange.BGHandbookRangeId; if (((Microsoft.Office.Interop.Excel.Range)ws.Cells[j, 1]).Text.ToString() != "") { detail.Id = ((Microsoft.Office.Interop.Excel.Range)ws.Cells[j, 1]).Text.ToString(); } if (((Microsoft.Office.Interop.Excel.Range)ws.Cells[j, 2]).Text.ToString() != "") { detail.ProductName = ((Microsoft.Office.Interop.Excel.Range)ws.Cells[j, 2]).Text.ToString(); } if (((Microsoft.Office.Interop.Excel.Range)ws.Cells[j, 3]).Text.ToString() != "") { detail.ProductSpecification = ((Microsoft.Office.Interop.Excel.Range)ws.Cells[j, 3]).Text.ToString(); } if (((Microsoft.Office.Interop.Excel.Range)ws.Cells[j, 4]).Text.ToString() != "") { detail.ProductUnit = ((Microsoft.Office.Interop.Excel.Range)ws.Cells[j, 4]).Text.ToString(); } if (((Microsoft.Office.Interop.Excel.Range)ws.Cells[j, 5]).Text.ToString() != "") { detail.CompanyProductId = ((Microsoft.Office.Interop.Excel.Range)ws.Cells[j, 5]).Text.ToString(); } if (((Microsoft.Office.Interop.Excel.Range)ws.Cells[j, 6]).Text.ToString() != "") { detail.CustomProductId = ((Microsoft.Office.Interop.Excel.Range)ws.Cells[j, 6]).Text.ToString(); } if (((Microsoft.Office.Interop.Excel.Range)ws.Cells[j, 7]).Text.ToString() != "") { detail.Note = ((Microsoft.Office.Interop.Excel.Range)ws.Cells[j, 7]).Text.ToString(); } detail.ProductType = "成品"; bGHandbookRange.DetailProducts.Add(detail); } //} //else //{ // for (int j = 6; j < 10000; j++) // { // if (((Microsoft.Office.Interop.Excel.Range)ws.Cells[j, 1]).Text.ToString() == "经办关员意见:" || (((Microsoft.Office.Interop.Excel.Range)ws.Cells[j, 1]).Text.ToString() == "" && ((Microsoft.Office.Interop.Excel.Range)ws.Cells[j, 2]).Text.ToString() == "")) // { // break; // } // detail = new Book.Model.BGHandbookRangeDetail(); // detail.BGHandbookRangeDetailId = Guid.NewGuid().ToString(); // detail.BGHandbookRangeId = bGHandbookRange.BGHandbookRangeId; // if (((Microsoft.Office.Interop.Excel.Range)ws.Cells[j, 1]).Text.ToString() != "") // detail.Id = ((Microsoft.Office.Interop.Excel.Range)ws.Cells[j, 1]).Text.ToString(); // if (((Microsoft.Office.Interop.Excel.Range)ws.Cells[j, 2]).Text.ToString() != "") // detail.CustomId = ((Microsoft.Office.Interop.Excel.Range)ws.Cells[j, 2]).Text.ToString(); // if (((Microsoft.Office.Interop.Excel.Range)ws.Cells[j, 3]).Text.ToString() != "") // detail.ProductName = ((Microsoft.Office.Interop.Excel.Range)ws.Cells[j, 3]).Text.ToString(); // if (((Microsoft.Office.Interop.Excel.Range)ws.Cells[j, 4]).Text.ToString() != "") // detail.ProductSpecification = ((Microsoft.Office.Interop.Excel.Range)ws.Cells[j, 4]).Text.ToString(); // if (((Microsoft.Office.Interop.Excel.Range)ws.Cells[j, 5]).Text.ToString() != "") // detail.ProductUnit = ((Microsoft.Office.Interop.Excel.Range)ws.Cells[j, 5]).Text.ToString(); // if (((Microsoft.Office.Interop.Excel.Range)ws.Cells[j, 6]).Text.ToString() != "") // detail.CompanyProductId = ((Microsoft.Office.Interop.Excel.Range)ws.Cells[j, 6]).Text.ToString(); // if (((Microsoft.Office.Interop.Excel.Range)ws.Cells[j, 7]).Text.ToString() != "") // detail.CustomProductId = ((Microsoft.Office.Interop.Excel.Range)ws.Cells[j, 7]).Text.ToString(); // if (((Microsoft.Office.Interop.Excel.Range)ws.Cells[j, 8]).Text.ToString() != "") // detail.Note = ((Microsoft.Office.Interop.Excel.Range)ws.Cells[j, 8]).Text.ToString(); // detail.ProductType = "成品"; // bGHandbookRange.DetailProducts.Add(detail); // } //} } else if (openFileDialog1.FileName.Contains("料件")) { if (ws.Name == "Sheet4") { continue; } else if (ws.Name == "申报要素") { continue; } else { for (int j = 6; j < 10000; j++) { if (((Microsoft.Office.Interop.Excel.Range)ws.Cells[j, 1]).Text.ToString() == "经办关员意见:" || (((Microsoft.Office.Interop.Excel.Range)ws.Cells[j, 1]).Text.ToString() == "" && ((Microsoft.Office.Interop.Excel.Range)ws.Cells[j, 2]).Text.ToString() == "")) { break; } detail = new Book.Model.BGHandbookRangeDetail(); detail.BGHandbookRangeDetailId = Guid.NewGuid().ToString(); detail.BGHandbookRangeId = bGHandbookRange.BGHandbookRangeId; if (((Microsoft.Office.Interop.Excel.Range)ws.Cells[j, 1]).Text.ToString() != "") { detail.Id = ((Microsoft.Office.Interop.Excel.Range)ws.Cells[j, 1]).Text.ToString(); } if (((Microsoft.Office.Interop.Excel.Range)ws.Cells[j, 2]).Text.ToString() != "") { detail.ProductName = ((Microsoft.Office.Interop.Excel.Range)ws.Cells[j, 2]).Text.ToString(); } if (((Microsoft.Office.Interop.Excel.Range)ws.Cells[j, 3]).Text.ToString() != "") { detail.ProductSpecification = ((Microsoft.Office.Interop.Excel.Range)ws.Cells[j, 3]).Text.ToString(); } if (((Microsoft.Office.Interop.Excel.Range)ws.Cells[j, 4]).Text.ToString() != "") { detail.ProductUnit = ((Microsoft.Office.Interop.Excel.Range)ws.Cells[j, 4]).Text.ToString(); } if (((Microsoft.Office.Interop.Excel.Range)ws.Cells[j, 5]).Text.ToString() != "") { detail.CompanyProductId = ((Microsoft.Office.Interop.Excel.Range)ws.Cells[j, 5]).Text.ToString(); } if (((Microsoft.Office.Interop.Excel.Range)ws.Cells[j, 6]).Text.ToString() != "") { detail.CustomProductId = ((Microsoft.Office.Interop.Excel.Range)ws.Cells[j, 6]).Text.ToString(); } if (((Microsoft.Office.Interop.Excel.Range)ws.Cells[j, 7]).Text.ToString() != "") { detail.Note = ((Microsoft.Office.Interop.Excel.Range)ws.Cells[j, 7]).Text.ToString(); } detail.ProductType = "料件"; bGHandbookRange.DetailMaterials.Add(detail); } } } bGHandbookRange.EmployeeId = ((Microsoft.Office.Interop.Excel.Range)ws.Cells[4, 1]).Text.ToString().Substring(((Microsoft.Office.Interop.Excel.Range)ws.Cells[4, 1]).Text.ToString().IndexOf(":") + 1, 4).Trim(); bGHandbookRange.Tel = ((Microsoft.Office.Interop.Excel.Range)ws.Cells[4, 1]).Text.ToString().Trim().Substring(((Microsoft.Office.Interop.Excel.Range)ws.Cells[4, 1]).Text.ToString().Trim().Length - 11); this._manager.Insert(bGHandbookRange); } BL.V.CommitTransaction(); //this.bindingSourceProduct.DataSource = bGHandbookRange.DetailProducts; //this.bindingSourceMaterial.DataSource = bGHandbookRange.DetailMaterials; //this.gridControl1.RefreshDataSource(); //this.gridControl2.RefreshDataSource(); this.MoveLast(); this.action = "view"; this.Refresh(); } catch { BL.V.RollbackTransaction(); ec.Close(); throw; } ec.Close(); } }
private void barOut_ItemClick(object sender, DevExpress.XtraBars.ItemClickEventArgs e) { ExcelClass.ExcelClass1 ex = new Book.UI.ExcelClass.ExcelClass1(); ex.Create(); Microsoft.Office.Interop.Excel.Worksheet sheet = (Microsoft.Office.Interop.Excel.Worksheet)ex.app.Application.Worksheets.get_Item(1); ex.UniteCells(sheet, 1, 1, 1, 7); ex.UniteCells(sheet, 2, 1, 2, 7); ex.UniteCells(sheet, 3, 1, 3, 7); ex.UniteCells(sheet, 4, 1, 4, 7); int a = this._BGHandbookRange.DetailProducts.Count; Range range; sheet.Cells[1, 1] = " 编码:厦[ ]加归类___号"; sheet.Cells[3, 1] = "企业名称及企业十位数代码:" + this._BGHandbookRange.CompanyNameAndId; sheet.Cells[4, 1] = "联系人及电话:" + this._BGHandbookRange.EmployeeId + this._BGHandbookRange.Tel; sheet.Cells[5, 1] = "序号"; sheet.Cells[5, 3] = "规格型号"; sheet.Cells[5, 4] = "计量单位"; sheet.Cells[5, 5] = "企业申报商品编码"; sheet.Cells[5, 6] = "海关归类商品编码"; sheet.Cells[5, 7] = "备注"; if (this.xtraTabControl1.SelectedTabPageIndex == 0) { sheet.Cells[2, 1] = "电子化手册出口成品归类申报表"; sheet.Cells[5, 2] = "成品名称"; for (int i = 0; i < a; i++) { sheet.Cells[i + 6, 1] = this._BGHandbookRange.DetailProducts[i].Id; sheet.Cells[i + 6, 2] = this._BGHandbookRange.DetailProducts[i].ProductName; sheet.Cells[i + 6, 3] = this._BGHandbookRange.DetailProducts[i].ProductSpecification; sheet.Cells[i + 6, 4] = this._BGHandbookRange.DetailProducts[i].ProductUnit; sheet.Cells[i + 6, 5] = this._BGHandbookRange.DetailProducts[i].CompanyProductId; sheet.Cells[i + 6, 6] = this._BGHandbookRange.DetailProducts[i].CustomProductId; sheet.Cells[i + 6, 7] = this._BGHandbookRange.DetailProducts[i].Note; } } else { sheet.Cells[2, 1] = "电子化手册进口料件归类申报表"; sheet.Cells[5, 2] = "料件名称"; a = this._BGHandbookRange.DetailMaterials.Count; for (int i = 0; i < a; i++) { sheet.Cells[i + 6, 1] = this._BGHandbookRange.DetailMaterials[i].Id; sheet.Cells[i + 6, 2] = this._BGHandbookRange.DetailMaterials[i].ProductName; sheet.Cells[i + 6, 3] = this._BGHandbookRange.DetailMaterials[i].ProductSpecification; sheet.Cells[i + 6, 4] = this._BGHandbookRange.DetailMaterials[i].ProductUnit; sheet.Cells[i + 6, 5] = this._BGHandbookRange.DetailMaterials[i].CompanyProductId; sheet.Cells[i + 6, 6] = this._BGHandbookRange.DetailMaterials[i].CustomProductId; sheet.Cells[i + 6, 7] = this._BGHandbookRange.DetailMaterials[i].Note; } } //宽度 ex.SetWidth(sheet, "A:A", 6); ex.SetWidth(sheet, "B:B", 12.25); ex.SetWidth(sheet, "C:C", 25); ex.SetWidth(sheet, "D:D", 9); ex.SetWidth(sheet, "E:E", 16); ex.SetWidth(sheet, "F:F", 16); ex.SetWidth(sheet, "G:G", 16); ex.UniteCells(sheet, a + 6, 1, a + 6, 2); ex.UniteCells(sheet, a + 8, 6, a + 8, 7); sheet.Cells[a + 6, 1] = "经办关员意见:" + this._BGHandbookRange.Opinion; sheet.Cells[a + 8, 6] = " 年 月 日"; sheet.Cells[a + 9, 1] = "备注栏可填写货物的形态、性质、成分、加工程度、结构原理、功能用途等技术参数"; sheet.Cells[a + 10, 1] = "注意事项:序号栏所填写序号应与备案资料库内商品序号相一致"; ((Range)sheet.Columns["A:G", System.Type.Missing]).HorizontalAlignment = XlHAlign.xlHAlignLeft; ((Range)sheet.Rows["1:1", System.Type.Missing]).HorizontalAlignment = XlHAlign.xlHAlignRight; ((Range)sheet.Rows["2:2", System.Type.Missing]).HorizontalAlignment = XlHAlign.xlHAlignCenter; ((Range)sheet.Rows["5:5", System.Type.Missing]).HorizontalAlignment = XlHAlign.xlHAlignCenter; ex.setBorder(sheet, 1, 5, 7, a + 8, 2); //ex.setBorder(); range = sheet.get_Range("A1", "G" + (a + 5).ToString()); range.WrapText = true; range.EntireRow.AutoFit(); ex.app.Visible = true; ex.release_xlsObj(); GC.Collect(); }
private void barIn_ItemClick(object sender, DevExpress.XtraBars.ItemClickEventArgs e) { if (this.openFileDialog1.ShowDialog() == DialogResult.OK) { ExcelClass.ExcelClass1 ec = new Book.UI.ExcelClass.ExcelClass1(); ec.Open(openFileDialog1.FileName); try { BL.V.BeginTransaction(); Model.BGHandbookRange bGHandbookRange = null; this.action = "insert"; Model.BGHandbookRangeDetail detail; for (int i = 1; i < ec.wb.Worksheets.Count; i++) { bGHandbookRange = new Book.Model.BGHandbookRange(); bGHandbookRange.Details = new List <Model.BGHandbookRangeDetail>(); bGHandbookRange.BGHandbookRangeId = Guid.NewGuid().ToString(); bGHandbookRange.BGHandbookRangeDate = DateTime.Now; Microsoft.Office.Interop.Excel.Worksheet ws = (Microsoft.Office.Interop.Excel.Worksheet)ec.wb.Worksheets[i]; bGHandbookRange.CompanyNameAndId = ((Microsoft.Office.Interop.Excel.Range)ws.Cells[3, 1]).Text.ToString().Substring(((Microsoft.Office.Interop.Excel.Range)ws.Cells[3, 1]).Text.ToString().IndexOf(':') + 1); if (openFileDialog1.FileName.Contains("成品")) { this.cobProductType.EditValue = "成品"; if (ws.Name != "草稿") { for (int j = 6; j < 200; j++) { if (((Microsoft.Office.Interop.Excel.Range)ws.Cells[j, 1]).Text.ToString() == "经办关员意见:" || (((Microsoft.Office.Interop.Excel.Range)ws.Cells[j, 1]).Text.ToString() == "" && ((Microsoft.Office.Interop.Excel.Range)ws.Cells[j, 2]).Text.ToString() == "")) { break; } detail = new Book.Model.BGHandbookRangeDetail(); detail.BGHandbookRangeDetailId = Guid.NewGuid().ToString(); if (((Microsoft.Office.Interop.Excel.Range)ws.Cells[j, 1]).Text.ToString() != "") { detail.Id = ((Microsoft.Office.Interop.Excel.Range)ws.Cells[j, 1]).Text.ToString(); } if (((Microsoft.Office.Interop.Excel.Range)ws.Cells[j, 2]).Text.ToString() != "") { detail.ProductName = ((Microsoft.Office.Interop.Excel.Range)ws.Cells[j, 2]).Text.ToString(); } if (((Microsoft.Office.Interop.Excel.Range)ws.Cells[j, 3]).Text.ToString() != "") { detail.ProductSpecification = ((Microsoft.Office.Interop.Excel.Range)ws.Cells[j, 3]).Text.ToString(); } if (((Microsoft.Office.Interop.Excel.Range)ws.Cells[j, 5]).Text.ToString() != "") { detail.ProductUnit = ((Microsoft.Office.Interop.Excel.Range)ws.Cells[j, 5]).Text.ToString(); } if (((Microsoft.Office.Interop.Excel.Range)ws.Cells[j, 6]).Text.ToString() != "") { detail.CompanyProductId = ((Microsoft.Office.Interop.Excel.Range)ws.Cells[j, 6]).Text.ToString(); } if (((Microsoft.Office.Interop.Excel.Range)ws.Cells[j, 7]).Text.ToString() != "") { detail.CustomProductId = ((Microsoft.Office.Interop.Excel.Range)ws.Cells[j, 7]).Text.ToString(); } if (((Microsoft.Office.Interop.Excel.Range)ws.Cells[j, 9]).Text.ToString() != "") { detail.Note = ((Microsoft.Office.Interop.Excel.Range)ws.Cells[j, 9]).Text.ToString(); } } } } else if (openFileDialog1.FileName.Contains("料件")) { this.cobProductType.EditValue = "料件"; } bGHandbookRange.Employee = (new BL.EmployeeManager()).GetByOperatorName(((Microsoft.Office.Interop.Excel.Range)ws.Cells[4, 1]).Text.ToString().Substring(((Microsoft.Office.Interop.Excel.Range)ws.Cells[4, 1]).Text.ToString().IndexOf(':') + 1, 4).Trim()); bGHandbookRange.EmployeeId = bGHandbookRange.Employee.EmployeeId; } } catch { BL.V.RollbackTransaction(); ec.Close(); throw; } ec.Close(); } }