private void OnExportCommand() { SaveFileDialog sDialog = new SaveFileDialog(); sDialog.Filter = "Excel Files(*.xls)|*.xls"; if (sDialog.ShowDialog() == true) { try { Workbook workbook = new Workbook(); Worksheet worksheet = new Worksheet("产品汇总"); //Title worksheet.Cells[0, 0] = new Cell("生产令号"); worksheet.Cells[0, 1] = new Cell("项目名称"); worksheet.Cells[0, 2] = new Cell("产品序号"); worksheet.Cells[0, 3] = new Cell("产品名称"); worksheet.Cells[0, 4] = new Cell("出厂编号"); Int16 RowCount = 1; foreach (ProductEntity productEntity in ProductEntityList) { int columnCount = 0; if (ProjectNameSearch != null && ProjectNameSearch.Length > 0) { if (!productEntity.ProjectName.Contains(ProjectNameSearch)) { continue; } } worksheet.Cells[RowCount, columnCount++] = new Cell(productEntity.ManufactureNumber); worksheet.Cells[RowCount, columnCount++] = new Cell(productEntity.ProjectName); worksheet.Cells[RowCount, columnCount++] = new Cell(productEntity.ProductID); worksheet.Cells[RowCount, columnCount++] = new Cell(productEntity.ProductName); worksheet.Cells[RowCount, columnCount++] = new Cell(productEntity.ProductOutputNumber); RowCount++; } workbook.Worksheets.Add(worksheet); Stream sFile = sDialog.OpenFile(); workbook.Save(sFile); ProductManager.Controls.Message.InfoMessage("导出成功"); } catch (Exception outputE) { string errorMessage = "导出文件失败:" + outputE.Message; ProductManager.Controls.Message.ErrorMessage(errorMessage); } } }
private bool ReadSheet(Worksheet aSheet) { foreach (KeyValuePair<int, Row> rowPair in aSheet.Cells.Rows) { if (rowPair.Key == 0) // 第一行 流水号检查 受理流水号 { Cell lCell = rowPair.Value.GetCell(0); string lValue = lCell.Value.ToString().Trim(); if (lValue != "税务机关代开普通发票申请表") { NotifyWindow lNotifyWindow = new NotifyWindow("读取错误", "打开的不是 '税务机关代开普通发票申请表' 或者 数据不在第一个工作表里。"); lNotifyWindow.Show(); return false; } } if (rowPair.Key == 1) // 第二行 流水号检查 受理流水号 { try { Cell lCell = rowPair.Value.GetCell(0); string lValue = lCell.Value.ToString(); StandBookEntity.StandBookID = lValue.Substring(lValue.IndexOf(':') + 1).Trim(); } catch(Exception e) { NotifyWindow lNotifyWindow = new NotifyWindow("读取错误","读取第二行 '受理流水号' 出错!请检查输入文件。"); lNotifyWindow.Show(); return false; } } if (rowPair.Key == 2) // 第三行 合同项目名称 合同总金额 本次拨款金额 { try { Cell lCellName = rowPair.Value.GetCell(0); Cell lCellTotolMoney = rowPair.Value.GetCell(2); Cell lCellThisMoney = rowPair.Value.GetCell(4); string lValueName = lCellName.Value.ToString(); StandBookEntity.ProjectName = lValueName.Substring(lValueName.IndexOf(':') + 1).Trim(); string lValueTotolMoney = lCellTotolMoney.Value.ToString(); lValueTotolMoney = lValueTotolMoney.Substring(lValueTotolMoney.IndexOf(':') + 1).Trim(); StandBookEntity.TotalMoney = Convert.ToDecimal(lValueTotolMoney); string lValueThisMoney = lCellThisMoney.Value.ToString(); lValueThisMoney = lValueThisMoney.Substring(lValueThisMoney.IndexOf(':') + 1).Trim(); StandBookEntity.ThisPartMoney = Convert.ToDecimal(lValueThisMoney); } catch(Exception e) { NotifyWindow lNotifyWindow = new NotifyWindow("读取错误","读取第三行 '合同项目名称 合同总金额 本次拨款金额' 出错!请检查输入文件。"); lNotifyWindow.Show(); return false; } } if (rowPair.Key == 3) // 第四行 综合税率 合同总应缴税额 { try { Cell lCellTotalRate = rowPair.Value.GetCell(1); string lValueTotalRate = lCellTotalRate.Value.ToString(); StandBookEntity.TotalTaxRate = Convert.ToDecimal(lValueTotalRate); } catch (Exception e) { NotifyWindow lNotifyWindow = new NotifyWindow("读取错误", "读取第四行 '综合税率 合同总应缴税额' 出错!请检查输入文件。"); lNotifyWindow.Show(); return false; } } if (rowPair.Key == 4) // 第五行 申请单位 { try { Cell lCellCpName = rowPair.Value.GetCell(1); string lValueCpName = lCellCpName.Value.ToString().Trim(); StandBookEntity.TaxPayerName = lValueCpName; } catch(Exception e) { NotifyWindow lNotifyWindow = new NotifyWindow("读取错误", "读取第五行 '申请单位' 出错!请检查输入文件。"); lNotifyWindow.Show(); return false; } } if (rowPair.Key == 7) // 第八行 经济性质 { try { Cell lCellEconomicNature = rowPair.Value.GetCell(1); string lValueEconomicNature = lCellEconomicNature.Value.ToString().Trim(); StandBookEntity.EconomicNature = lValueEconomicNature; Cell lCellPhoneNumber = rowPair.Value.GetCell(3); string lValuePhoneNumber = lCellPhoneNumber.Value.ToString().Trim(); StandBookEntity.PhoneNumber = lValuePhoneNumber; } catch (Exception e) { NotifyWindow lNotifyWindow = new NotifyWindow("读取错误", "读取第八行 '经济性质' '联系电话' 出错!请检查输入文件。"); lNotifyWindow.Show(); return false; } } if (rowPair.Key == 8) // 第九行 付款单位 { try { Cell lCellPlayCpName = rowPair.Value.GetCell(1); string lValueCpName = lCellPlayCpName.Value.ToString().Trim(); StandBookEntity.CapitalConstruction = lValueCpName; } catch(Exception e) { NotifyWindow lNotifyWindow = new NotifyWindow("读取错误", "读取第九行 '付款单位' 出错!请检查输入文件。"); lNotifyWindow.Show(); return false; } } if (rowPair.Key == 13) // 第十四行 有无外管证 { try { Cell lCellHasVerify = rowPair.Value.GetCell(0); string lValueHasVerify = lCellHasVerify.Value.ToString().Trim(); lValueHasVerify = lValueHasVerify.Substring(lValueHasVerify.IndexOf(':') + 1).Trim(); if (lValueHasVerify == "有") { StandBookEntity.HasOutVerify = true; } else if (lValueHasVerify == "无") { StandBookEntity.HasOutVerify = false; } else { NotifyWindow lNotifyWindow = new NotifyWindow("读取错误", "读取第十四行 '有无外管证' 出错!请在':'后输入'有'或'无'。"); lNotifyWindow.Show(); return false; } } catch(Exception e) { NotifyWindow lNotifyWindow = new NotifyWindow("读取错误","读取第十三行 '有无外管证' 出错!请检查输入文件。"); lNotifyWindow.Show(); return false; } } if (rowPair.Key == 20) // 第二十一行 法人代表 { try { Cell lCellJridicalPerson = rowPair.Value.GetCell(3); string lValueJridicalPerson = lCellJridicalPerson.Value.ToString().Trim(); lValueJridicalPerson = lValueJridicalPerson.Substring(lValueJridicalPerson.IndexOf(':') + 1).Trim(); StandBookEntity.TaxPayerPersonName = lValueJridicalPerson; if(lValueJridicalPerson == string.Empty) { NotifyWindow lNotifyWindow = new NotifyWindow("读取错误", "读取第二十一行 '法人代表' 出错!请在D列输入'法人姓名:XXX'。"); lNotifyWindow.Show(); return false; } } catch(Exception e) { NotifyWindow lNotifyWindow = new NotifyWindow("读取错误", "读取第二十行 '法人代表' 出错!请在D列输入'法人姓名:XXX'。"); lNotifyWindow.Show(); return false; } } if (rowPair.Key == 21) // 第二十二行 日期 { try { Cell lCellDataTime = rowPair.Value.GetCell(0); string lValueDataTime = lCellDataTime.Value.ToString().Trim(); lValueDataTime = lValueDataTime.Substring(lValueDataTime.IndexOf(':') + 1).Trim(); StandBookEntity.PayTime = Convert.ToDateTime(lValueDataTime); } catch(Exception e) { NotifyWindow lNotifyWindow = new NotifyWindow("读取错误","读取第二十二行 '日期' 出错!请在A列输入'日期:yyyy年mm月dd日'。"); lNotifyWindow.Show(); return false; } } if (rowPair.Key == 25 // 第二十六行 税 || rowPair.Key == 26 // 第二十七行 税 || rowPair.Key == 27 // 第二十八行 税 || rowPair.Key == 28 // 第二十九行 税 || rowPair.Key == 29 // 第三十行 税 || rowPair.Key == 30 // 第三十一行 税 ) { string lValueTaxType; string lValueTax; string lValueTaxItem = ""; string lValueTaxRate; int lKeyRow = rowPair.Key + 1; try { Cell lCellTaxType = rowPair.Value.GetCell(0); lValueTaxType = lCellTaxType.Value.ToString().Trim(); Cell lCellTaxItem = rowPair.Value.GetCell(1); if (lCellTaxItem.Value != null) { lValueTaxItem = lCellTaxItem.Value.ToString().Trim(); } Cell lCellTaxRate = rowPair.Value.GetCell(4); lValueTaxRate = lCellTaxRate.Value.ToString().Trim(); Cell lCellTax = rowPair.Value.GetCell(5); lValueTax = lCellTax.Value.ToString().Trim(); } catch(Exception e) { NotifyWindow lNotifyWindow = new NotifyWindow("读取错误", "读取第" + lKeyRow + "行 出错!"); lNotifyWindow.Show(); return false; } bool lHasCorrectTaxType = false; if (lValueTaxType != string.Empty) { if (lValueTaxType == "增值税") { lHasCorrectTaxType = true; try { decimal lTax = Convert.ToDecimal(lValueTax); decimal lTaxRate = Convert.ToDecimal(lValueTaxRate); StandBookEntity.HasAddValueTax = true; StandBookEntity.AddValueTax = lTax; StandBookEntity.AddValueTaxRate = lTaxRate; StandBookEntity.AddValueTaxItem = lValueTaxItem; } catch (Exception e) { NotifyWindow lNotifyWindow = new NotifyWindow("读取错误", "读取第" + lKeyRow + "行 '营业税' 出错!"); lNotifyWindow.Show(); return false; } } if (lValueTaxType == "营业税") { lHasCorrectTaxType = true; try { decimal lTax = Convert.ToDecimal(lValueTax); decimal lTaxRate = Convert.ToDecimal(lValueTaxRate); StandBookEntity.HasBusinessTax = true; StandBookEntity.BusinessTax = lTax; StandBookEntity.BusinessTaxRate = lTaxRate; StandBookEntity.BusinessTaxItem = lValueTaxItem; } catch(Exception e) { NotifyWindow lNotifyWindow = new NotifyWindow("读取错误", "读取第" + lKeyRow + "行 '营业税' 出错!"); lNotifyWindow.Show(); return false; } } if (lValueTaxType == "教育费附加") { lHasCorrectTaxType = true; try { decimal lTax = Convert.ToDecimal(lValueTax); decimal lTaxRate = Convert.ToDecimal(lValueTaxRate); StandBookEntity.HasEducationalSurtax = true; StandBookEntity.EducationalSurtaxTax = lTax; StandBookEntity.EducationalSurtaxTaxRate = lTaxRate; StandBookEntity.EducationalSurtaxTaxItem = lValueTaxItem; } catch(Exception e) { NotifyWindow lNotifyWindow = new NotifyWindow("读取错误", "读取第" + lKeyRow + "行 '教育费附加' 出错!"); lNotifyWindow.Show(); return false; } } if (lValueTaxType == "城建税") { lHasCorrectTaxType = true; try { decimal lTax = Convert.ToDecimal(lValueTax); decimal lTaxRate = Convert.ToDecimal(lValueTaxRate); StandBookEntity.HasUrbanTax = true; StandBookEntity.UrbanTax = lTax; StandBookEntity.UrbanTaxRate = lTaxRate; StandBookEntity.UrbanTaxItem = lValueTaxItem; } catch(Exception e) { NotifyWindow lNotifyWindow = new NotifyWindow("读取错误", "读取第" + lKeyRow + "行 '城建税' 出错!"); lNotifyWindow.Show(); return false; } } if (lValueTaxType == "地方教育附加") { lHasCorrectTaxType = true; try { decimal lTax = Convert.ToDecimal(lValueTax); decimal lTaxRate = Convert.ToDecimal(lValueTaxRate); StandBookEntity.HasLocalEducationalSurtax = true; StandBookEntity.LocalEducationalSurtaxTax = lTax; StandBookEntity.LocalEducationalSurtaxTaxRate = lTaxRate; StandBookEntity.LocalEducationalSurtaxTaxItem = lValueTaxItem; } catch(Exception e) { NotifyWindow lNotifyWindow = new NotifyWindow("读取错误", "读取第" + lKeyRow + "行 '城建税' 出错!"); lNotifyWindow.Show(); return false; } } if (lValueTaxType == "印花税") { lHasCorrectTaxType = true; try { decimal lTax = Convert.ToDecimal(lValueTax); decimal lTaxRate = Convert.ToDecimal(lValueTaxRate); StandBookEntity.HasStampTax = true; StandBookEntity.StampTax = lTax; StandBookEntity.StampTaxRate = lTaxRate; StandBookEntity.StampTaxItem = lValueTaxItem; } catch(Exception e) { NotifyWindow lNotifyWindow = new NotifyWindow("读取错误", "读取第" + lKeyRow + "行 '印花税' 出错!"); lNotifyWindow.Show(); return false; } } if (lValueTaxType == "企业所得税" || lValueTaxType == "个人所得税") { lHasCorrectTaxType = true; try { decimal lTax = Convert.ToDecimal(lValueTax); decimal lTaxRate = Convert.ToDecimal(lValueTaxRate); StandBookEntity.HasIncomeTax = true; StandBookEntity.IncomeTax = lTax; StandBookEntity.IncomeTaxRate = lTaxRate; StandBookEntity.IncomeTaxItem = lValueTaxItem; } catch(Exception e) { NotifyWindow lNotifyWindow = new NotifyWindow("读取错误", "读取第" + lKeyRow + "行 '企业所得税(个人所得税)' 出错!"); lNotifyWindow.Show(); return false; } } if (!lHasCorrectTaxType) { NotifyWindow lNotifyWindow = new NotifyWindow("读取错误", "读取第" + lKeyRow + "行 应納税种 不符合规范!"); lNotifyWindow.Show(); return false; } } } if (rowPair.Key == 32) // 第三十三行 备注 { try { Cell lCellNote = rowPair.Value.GetCell(1); if (lCellNote.Value != null) { string lValueCellNote = lCellNote.Value.ToString().Trim(); StandBookEntity.Note = lValueCellNote; } } catch (Exception e) { NotifyWindow lNotifyWindow = new NotifyWindow("读取错误", "读取第三十三行 '备注' 出错!"); lNotifyWindow.Show(); return false; } } if (rowPair.Key == 33) // 第三十四行 完税凭证字别 完税证号码 { try { //Cell lCellFinishID = rowPair.Value.GetCell(1); //string lValueFinishID = lCellFinishID.Value.ToString().Trim(); Cell lCellFinishNumber = rowPair.Value.GetCell(4); if (lCellFinishNumber.Value != null) { string lValueFinishNumber = lCellFinishNumber.Value.ToString().Trim(); StandBookEntity.TaxReceiptNumber = lValueFinishNumber; } } catch (Exception e) { NotifyWindow lNotifyWindow = new NotifyWindow("读取错误", "读取第三十四行 '完税凭证字别 完税证号码' 出错!"); lNotifyWindow.Show(); return false; } } if (rowPair.Key == 34) // 第三十五行 完税凭证字别 发票号码 { try { //Cell lCellFinishID = rowPair.Value.GetCell(1); //string lValueFinishID = lCellFinishID.Value.ToString().Trim(); Cell lCellInvoiceNumber = rowPair.Value.GetCell(4); if (lCellInvoiceNumber.Value != null) { string lValueInvoiceNumber = lCellInvoiceNumber.Value.ToString().Trim(); StandBookEntity.InvoiceNumber = lValueInvoiceNumber; } } catch (Exception e) { NotifyWindow lNotifyWindow = new NotifyWindow("读取错误", "读取第三十五行 '完税凭证字别 发票号码' 出错!"); lNotifyWindow.Show(); return false; } } } return true; }
private void ExportToExcelBtn_MouseLeftButtonDown(object sender, MouseButtonEventArgs e) { SaveFileDialog sDialog = new SaveFileDialog(); sDialog.Filter = "Excel Files(*.xls)|*.xls"; if (sDialog.ShowDialog() == true) { WriteableBitmap bitmap = new WriteableBitmap(1000, 500); bitmap.Render(MainChart, null); bitmap.Invalidate(); Workbook workbook = new Workbook(); Worksheet worksheet1 = new Worksheet("SheetWithImage"); Lite.ExcelLibrary.SpreadSheet.Picture pic = new Lite.ExcelLibrary.SpreadSheet.Picture(); System.Windows.Controls.Image image = new System.Windows.Controls.Image(); image.Source = bitmap; image.Name = "imgExport"; image.Width = 1000; image.Height = 500; image.Stretch = Stretch.Fill; pic.Image = new Lite.ExcelLibrary.SpreadSheet.Image(ImageTranslator.TranslateImageToBytes(image), 0xF01E); pic.TopLeftCorner = new CellAnchor(1, 1, 10, 10); pic.BottomRightCorner = new CellAnchor(24, 16, 10, 10); worksheet1.AddPicture(pic); workbook.Worksheets.Add(worksheet1); Stream sFile = sDialog.OpenFile(); workbook.Save(sFile); } }
private static Record EncodePictures(Dictionary<Pair<int, int>, Picture> pictures, SharedResource sharedResource, Worksheet worksheet) { MSODRAWING msoDrawing = new MSODRAWING(); MsofbtDgContainer dgContainer = new MsofbtDgContainer(); msoDrawing.EscherRecords.Add(dgContainer); MsofbtDg dg = new MsofbtDg(); dg.Instance = 1; dg.NumShapes = pictures.Count + 1; dg.LastShapeID = 1024 + pictures.Count; dgContainer.EscherRecords.Add(dg); MsofbtSpgrContainer spgrContainer = new MsofbtSpgrContainer(); dgContainer.EscherRecords.Add(spgrContainer); MsofbtSpContainer spContainer0 = new MsofbtSpContainer(); spContainer0.EscherRecords.Add(new MsofbtSpgr()); MsofbtSp shape0 = new MsofbtSp(); shape0.ShapeId = 1024; shape0.Flags = ShapeFlag.Group | ShapeFlag.Patriarch; shape0.Version = 2; spContainer0.EscherRecords.Add(shape0); spgrContainer.EscherRecords.Add(spContainer0); foreach (Picture pic in pictures.Values) { if (!sharedResource.Images.Contains(pic.Image)) { sharedResource.Images.Add(pic.Image); } MsofbtSpContainer spContainer = new MsofbtSpContainer(); MsofbtSp shape = new MsofbtSp(); shape.Version = 2; shape.ShapeType = ShapeType.PictureFrame; shape.ShapeId = 1024 + spgrContainer.EscherRecords.Count; shape.Flags = ShapeFlag.Haveanchor | ShapeFlag.Hasshapetype; spContainer.EscherRecords.Add(shape); MsofbtOPT opt = new MsofbtOPT(); opt.Add(PropertyIDs.LockAgainstGrouping, 33226880); opt.Add(PropertyIDs.FitTextToShape, 262148); opt.Add(PropertyIDs.BlipId, (uint)sharedResource.Images.IndexOf(pic.Image) + 1); spContainer.EscherRecords.Add(opt); MsofbtClientAnchor anchor = new MsofbtClientAnchor(); anchor.Row1 = pic.TopLeftCorner.RowIndex; anchor.Col1 = pic.TopLeftCorner.ColIndex; anchor.DX1 = pic.TopLeftCorner.DX; anchor.DY1 = pic.TopLeftCorner.DY; anchor.Row2 = pic.BottomRightCorner.RowIndex; anchor.Col2 = pic.BottomRightCorner.ColIndex; anchor.DX2 = pic.BottomRightCorner.DX; anchor.DY2 = pic.BottomRightCorner.DY; anchor.ExtraData = new byte[0]; spContainer.EscherRecords.Add(anchor); spContainer.EscherRecords.Add(new MsofbtClientData()); spgrContainer.EscherRecords.Add(spContainer); } return msoDrawing; }
void PlanListWindow_Closed(object sender, EventArgs e) { PlanListTraceWindow planListWindow = sender as PlanListTraceWindow; if (planListWindow.DialogResult == true) { SaveFileDialog sDialog = new SaveFileDialog(); sDialog.Filter = "Excel Files(*.xls)|*.xls"; if (sDialog.ShowDialog() == true) { try { string versionId = "文件编号:"; if (null != planListWindow.planExtraEntity && null != planListWindow.planExtraEntity.FileId) { versionId += planListWindow.planExtraEntity.FileId; } versionId += " 计划版本:"; if (null != planListWindow.planExtraEntity && null != planListWindow.planExtraEntity.VersionId) { versionId += planListWindow.planExtraEntity.VersionId; } string projectNameKey = null == planListWindow.planExtraEntity ? "所有项目" : null != SelectProjectEntity ? SelectProjectEntity.ProjectName : planListWindow.planListViewModelList[0].PlanList[0].ProjectName; projectNameKey += " "; string manufactureNumber = "生产令号:"; if (null != planListWindow.planExtraEntity && null != planListWindow.planExtraEntity.ManufactureNumber) { manufactureNumber += planListWindow.planExtraEntity.ManufactureNumber; } ColumnModel columnModel = new ColumnModel(); Dictionary<string, Worksheet> sheetDictionary = new Dictionary<string, Worksheet>(); Dictionary<string, int> rowCountDictionary = new Dictionary<string, int>(); Worksheet currentSheet = null; foreach(PlanListViewModel model in planListWindow.planListViewModelList) { foreach (PlanEntity planEntity in model.FilterPlanList) { if ("设计完成节点" == planEntity.SheetName) { planEntity.SheetName = "设计节点"; } else if ("采购完成节点" == planEntity.SheetName) { planEntity.SheetName = "采购节点"; } else if ("生产完成节点" == planEntity.SheetName) { planEntity.SheetName = "生产节点"; } if (!sheetDictionary.Keys.Contains(planEntity.SheetName)) { currentSheet = new Worksheet(planEntity.SheetName); string projectNameName = projectNameKey + planEntity.SheetName; Int16 rowHeaderCount = 0; currentSheet.Cells[rowHeaderCount++, 0] = new Cell(versionId); currentSheet.Cells[rowHeaderCount++, 0] = new Cell(projectNameName); currentSheet.Cells[rowHeaderCount++, 0] = new Cell(manufactureNumber); int columnHeaderCount = 0; if (null == planListWindow.planExtraEntity) { currentSheet.Cells[rowHeaderCount, columnHeaderCount++] = new Cell("项目名称"); currentSheet.Cells[rowHeaderCount, columnHeaderCount++] = new Cell("生产令号"); currentSheet.Cells[rowHeaderCount, columnHeaderCount++] = new Cell("版本号"); } int columnModelIndex = model.ColumnModelIndex; foreach (string itemColumn in columnModel.List[columnModelIndex]) { currentSheet.Cells[rowHeaderCount, columnHeaderCount++] = new Cell(itemColumn); } ++rowHeaderCount; sheetDictionary.Add(planEntity.SheetName, currentSheet); rowCountDictionary.Add(planEntity.SheetName, rowHeaderCount); } else { currentSheet = sheetDictionary[planEntity.SheetName]; } int rowCount = rowCountDictionary[planEntity.SheetName]; int columnCount = 0; if (null == planListWindow.planExtraEntity) { currentSheet.Cells[rowCount, columnCount++] = new Cell(planEntity.ProjectName); currentSheet.Cells[rowCount, columnCount++] = new Cell(planEntity.ManufactureNumber); currentSheet.Cells[rowCount, columnCount++] = new Cell(planEntity.VersionId); } string value = Convert.ToString(planEntity.SequenceId); currentSheet.Cells[rowCount, columnCount++] = new Cell(value); currentSheet.Cells[rowCount, columnCount++] = new Cell(planEntity.ComponentName); currentSheet.Cells[rowCount, columnCount++] = new Cell(planEntity.TaskDescription); value = Convert.ToString(planEntity.Weight); currentSheet.Cells[rowCount, columnCount++] = new Cell(value); if (planEntity.Score.HasValue) { value = Convert.ToString(planEntity.Score.Value); currentSheet.Cells[rowCount, columnCount] = new Cell(value); } ++columnCount; int currentColumnModelIndex = model.ColumnModelIndex; if (1 == currentColumnModelIndex) { if (planEntity.OrderDate.HasValue) { value = planEntity.OrderDate.Value.ToString(String.Format("yyyy-MM-dd")); currentSheet.Cells[rowCount, columnCount] = new Cell(value); } ++columnCount; } value = planEntity.TargetDate.ToString(String.Format("yyyy-MM-dd")); currentSheet.Cells[rowCount, columnCount++] = new Cell(value); if (planEntity.TargetDateAdjustment1.HasValue) { value = planEntity.TargetDateAdjustment1.Value.ToString(String.Format("yyyy-MM-dd")); currentSheet.Cells[rowCount, columnCount] = new Cell(value); } ++columnCount; if (planEntity.TargetDateAdjustment2.HasValue) { value = planEntity.TargetDateAdjustment2.Value.ToString(String.Format("yyyy-MM-dd")); currentSheet.Cells[rowCount, columnCount] = new Cell(value); } ++columnCount; if (planEntity.AccomplishDate.HasValue) { value = planEntity.AccomplishDate.Value.ToString(String.Format("yyyy-MM-dd")); currentSheet.Cells[rowCount, columnCount] = new Cell(value); } ++columnCount; if (null != planEntity.DepartmentName && string.Empty != planEntity.DepartmentName) { currentSheet.Cells[rowCount, columnCount] = new Cell(planEntity.DepartmentName); } ++columnCount; if (null != planEntity.Remark && string.Empty != planEntity.Remark) { currentSheet.Cells[rowCount, columnCount] = new Cell(planEntity.Remark); } ++columnCount; ++rowCountDictionary[planEntity.SheetName]; } } if(sheetDictionary.Count > 0) { Workbook workbook = new Workbook(); foreach (KeyValuePair<string, Worksheet> kv in sheetDictionary) { if (null != planListWindow.planExtraEntity) { PlanExtraEntity planExtraEntity = planListWindow.planExtraEntity; currentSheet = kv.Value; currentSheet.Cells[rowCountDictionary[kv.Key]++, 0] = new Cell("编制依据:" + planExtraEntity.CompilationBasis); currentSheet.Cells[rowCountDictionary[kv.Key]++, 0] = new Cell("第一次调整原因:" + planExtraEntity.ReasonAdjustment1); currentSheet.Cells[rowCountDictionary[kv.Key]++, 0] = new Cell("第二次调整原因:" + planExtraEntity.ReasonAdjustment2); currentSheet.Cells[rowCountDictionary[kv.Key], 0] = new Cell("编制:" + planExtraEntity.CompileUserName); currentSheet.Cells[rowCountDictionary[kv.Key], 3] = new Cell("审核:" + planExtraEntity.ExamineUserName); currentSheet.Cells[rowCountDictionary[kv.Key]++, 5] = new Cell("批准:" + planExtraEntity.ApproveUserName); currentSheet.Cells[rowCountDictionary[kv.Key], 0] = new Cell("日期:" + planExtraEntity.CompileDate); currentSheet.Cells[rowCountDictionary[kv.Key], 3] = new Cell("日期:" + planExtraEntity.ExamineDate); currentSheet.Cells[rowCountDictionary[kv.Key]++, 5] = new Cell("日期:" + planExtraEntity.ApproveDate); } workbook.Worksheets.Add(kv.Value); } Stream sFile = sDialog.OpenFile(); workbook.Save(sFile); Message.InfoMessage("导出成功"); } else { Message.ErrorMessage("导出失败:无数据!"); } } catch (Exception outputE) { string errorMessage = "导出文件失败:" + outputE.Message; Message.ErrorMessage(errorMessage); } } } if (planListWindow.DialogResult.HasValue) { ObservableCollection<string> unFinishedProject = new ObservableCollection<string>(); foreach (PlanListViewModel planListViewModel in planListWindow.planListViewModelList) { foreach (PlanEntity planEntity in planListViewModel.PlanList) { if (null == planEntity.AccomplishDate && !unFinishedProject.Contains(planEntity.ManufactureNumber)) { unFinishedProject.Add(planEntity.ManufactureNumber); } } } if (null != planListWindow.planExtraEntity) { if(null != SelectProjectEntity) { SelectProjectEntity.AccomplishMark = (0 == unFinishedProject.Count ? 1 : 0); SelectProjectEntity.DUpdate(); } } else { foreach (ProjectEntity projectItem in ProjectList) { if (!string.IsNullOrEmpty(projectItem.PlanVersionID)) { projectItem.AccomplishMark = unFinishedProject.Contains(projectItem.ManufactureNumber) ? 0 : 1; projectItem.DUpdate(); } } } SaveChanges(); } (OnViewSinglePlan as DelegateCommand).RaiseCanExecuteChanged(); (OnSetRemind as DelegateCommand).RaiseCanExecuteChanged(); (OnViewAllPlan as DelegateCommand).RaiseCanExecuteChanged(); (OnViewSingleHistory as DelegateCommand).RaiseCanExecuteChanged(); }
void PlanListWindow_Closed(object sender, EventArgs e) { PlanListEditWindow planListWindow = sender as PlanListEditWindow; if (planListWindow.DialogResult == true) { SaveFileDialog sDialog = new SaveFileDialog(); sDialog.Filter = "Excel Files(*.xls)|*.xls"; if (sDialog.ShowDialog() == true) { try { string versionId = "文件编号:"; if (null != planListWindow.planExtraEntity && null != planListWindow.planExtraEntity.FileId) { versionId += planListWindow.planExtraEntity.FileId; } versionId += " 计划版本:"; versionId += SelectProjectEntity.PlanVersionID; string projectNameKey = SelectProjectEntity.ProjectName + " "; string manufactureNumber = "生产令号:" + SelectProjectEntity.ManufactureNumber; Workbook workbook = new Workbook(); ColumnModel columnModel = new ColumnModel(); foreach (PlanListViewModel item in planListWindow.planListViewModelList) { string projectNameName = projectNameKey + item.Title; Worksheet worksheet = new Worksheet(item.Title); Int16 RowCount = 0; worksheet.Cells[RowCount++, 0] = new Cell(versionId); worksheet.Cells[RowCount++, 0] = new Cell(projectNameName); worksheet.Cells[RowCount++, 0] = new Cell(manufactureNumber); int columnCount = 0; foreach (string itemColumn in columnModel.List[item.ColumnModelIndex]) { worksheet.Cells[RowCount, columnCount++] = new Cell(itemColumn); } ++RowCount; foreach (PlanEntity planEntity in item.PlanList) { columnCount = 0; string value = Convert.ToString(planEntity.SequenceId); worksheet.Cells[RowCount, columnCount++] = new Cell(value); worksheet.Cells[RowCount, columnCount++] = new Cell(planEntity.ComponentName); worksheet.Cells[RowCount, columnCount++] = new Cell(planEntity.TaskDescription); value = Convert.ToString(planEntity.Weight); worksheet.Cells[RowCount, columnCount++] = new Cell(value); if (planEntity.Score.HasValue) { value = Convert.ToString(planEntity.Score.Value); worksheet.Cells[RowCount, columnCount] = new Cell(value); } ++columnCount; if (1 == item.ColumnModelIndex) { if (planEntity.OrderDate.HasValue) { value = Convert.ToString(planEntity.OrderDate.Value); worksheet.Cells[RowCount, columnCount] = new Cell(value); } ++columnCount; } value = Convert.ToString(planEntity.TargetDate); worksheet.Cells[RowCount, columnCount++] = new Cell(value); if (planEntity.TargetDateAdjustment1.HasValue) { value = Convert.ToString(planEntity.TargetDateAdjustment1.Value); worksheet.Cells[RowCount, columnCount] = new Cell(value); } ++columnCount; if (planEntity.TargetDateAdjustment2.HasValue) { value = Convert.ToString(planEntity.TargetDateAdjustment2.Value); worksheet.Cells[RowCount, columnCount] = new Cell(value); } ++columnCount; if (planEntity.AccomplishDate.HasValue) { value = Convert.ToString(planEntity.AccomplishDate.Value); worksheet.Cells[RowCount, columnCount] = new Cell(value); } ++columnCount; if (null != planEntity.DepartmentName && string.Empty != planEntity.DepartmentName) { worksheet.Cells[RowCount, columnCount] = new Cell(planEntity.DepartmentName); } ++columnCount; if (null != planEntity.Remark && string.Empty != planEntity.Remark) { worksheet.Cells[RowCount, columnCount] = new Cell(planEntity.Remark); } ++columnCount; ++RowCount; } workbook.Worksheets.Add(worksheet); } Stream sFile = sDialog.OpenFile(); workbook.Save(sFile); Message.InfoMessage("导出成功"); } catch (Exception outputE) { string errorMessage = "导出文件失败:" + outputE.Message; Message.ErrorMessage(errorMessage); } } } (OnExportPlan as DelegateCommand).RaiseCanExecuteChanged(); }
public static List<Record> Encode(Worksheet worksheet, SharedResource sharedResource) { List<Record> records = new List<Record>(); BOF bof = new BOF(); bof.BIFFversion = 0x0600; //0600H = BIFF8 bof.StreamType = StreamType.Worksheet; bof.BuildID = 3515; bof.BuildYear = 1996; bof.RequiredExcelVersion = 6; records.Add(bof); foreach (KeyValuePair<Pair<UInt16, UInt16>, UInt16> colWidth in worksheet.Cells.ColumnWidth) { COLINFO colInfo = new COLINFO(); colInfo.FirstColIndex = colWidth.Key.Left; colInfo.LastColIndex = colWidth.Key.Right; colInfo.Width = colWidth.Value; records.Add(colInfo); } DIMENSIONS dimensions = new DIMENSIONS(); if (worksheet.Cells.Rows.Count > 0) { dimensions.FirstRow = worksheet.Cells.FirstRowIndex; dimensions.FirstColumn = (Int16)worksheet.Cells.FirstColIndex; dimensions.LastRow = worksheet.Cells.LastRowIndex + 1; dimensions.LastColumn = (Int16)(worksheet.Cells.LastColIndex + 1); } records.Add(dimensions); // each Row Block contains 32 consecutive rows List<Record> rowblock = new List<Record>(32); List<Record> cellblock = new List<Record>(); for (int rowIndex = dimensions.FirstRow; rowIndex < dimensions.LastRow; rowIndex++) { if (worksheet.Cells.Rows.ContainsKey(rowIndex)) { Row sheetRow = worksheet.Cells.Rows[rowIndex]; ROW biffRow = new ROW(); biffRow.RowIndex = (UInt16)rowIndex; biffRow.FirstColIndex = (UInt16)sheetRow.FirstColIndex; biffRow.LastColIndex = (UInt16)(sheetRow.LastColIndex + 1); biffRow.RowHeight = sheetRow.Height; biffRow.Flags = 0x0F0100; // defaul value 0x0100 rowblock.Add(biffRow); for (int colIndex = sheetRow.FirstColIndex; colIndex <= sheetRow.LastColIndex; colIndex++) { Cell cell = sheetRow.GetCell(colIndex); if (cell != Cell.EmptyCell && cell.Value != null) { CellValue cellRecord = EncodeCell(cell, sharedResource); cellRecord.RowIndex = (UInt16)rowIndex; cellRecord.ColIndex = (UInt16)colIndex; cellRecord.XFIndex = (UInt16)sharedResource.GetXFIndex(cell.Format); cellblock.Add(cellRecord); } } if (rowblock.Count == 32) { records.AddRange(rowblock); records.AddRange(cellblock); rowblock.Clear(); cellblock.Clear(); } } } if (rowblock.Count > 0) { records.AddRange(rowblock); records.AddRange(cellblock); } if (worksheet.Pictures.Count > 0) { records.Add(EncodePictures(worksheet.Pictures, sharedResource, worksheet)); for (ushort id = 1; id <= worksheet.Pictures.Count; id++) { OBJ obj = new OBJ(); CommonObjectData objData = new CommonObjectData(); objData.ObjectID = id; objData.ObjectType = 8; objData.OptionFlags = 24593; obj.SubRecords.Add(objData); obj.SubRecords.Add(new End()); records.Add(obj); } } EOF eof = new EOF(); records.Add(eof); return records; }
private void OnExportCommand() { SaveFileDialog sDialog = new SaveFileDialog(); sDialog.Filter = "Excel Files(*.xls)|*.xls"; if (sDialog.ShowDialog() == true) { try { Workbook workbook = new Workbook(); Worksheet worksheet = new Worksheet("外购部件汇总"); //Title worksheet.Cells[0, 0] = new Cell("生产令号"); worksheet.Cells[0, 1] = new Cell("项目名称"); worksheet.Cells[0, 2] = new Cell("主要部件名称"); worksheet.Cells[0, 3] = new Cell("厂家"); worksheet.Cells[0, 4] = new Cell("到货时间"); Int16 RowCount = 1; foreach (ImportantPartEntity importantPartEntity in ImportantPartEntityList) { int columnCount = 0; if (ProjectNameSearch != null && ProjectNameSearch.Length > 0) { if (!importantPartEntity.ProjectName.Contains(ProjectNameSearch)) { continue; } } worksheet.Cells[RowCount, columnCount++] = new Cell(importantPartEntity.ManufactureNumber); worksheet.Cells[RowCount, columnCount++] = new Cell(importantPartEntity.ProjectName); worksheet.Cells[RowCount, columnCount++] = new Cell(importantPartEntity.ImportantPartName); worksheet.Cells[RowCount, columnCount++] = new Cell(importantPartEntity.ImportantPartManufacturers); worksheet.Cells[RowCount, columnCount++] = new Cell(importantPartEntity.AriveTime.HasValue?importantPartEntity.AriveTime.Value.ToShortDateString():""); RowCount++; } workbook.Worksheets.Add(worksheet); Stream sFile = sDialog.OpenFile(); workbook.Save(sFile); ProductManager.Controls.Message.InfoMessage("导出成功"); } catch (Exception outputE) { string errorMessage = "导出文件失败:" + outputE.Message; ProductManager.Controls.Message.ErrorMessage(errorMessage); } } }
private void OnExportCommand() { SaveFileDialog sDialog = new SaveFileDialog(); sDialog.Filter = "Excel Files(*.xls)|*.xls"; if (sDialog.ShowDialog() == true) { try { Workbook workbook = new Workbook(); Worksheet worksheet = new Worksheet("生产令号汇总"); //Title worksheet.Cells[0, 0] = new Cell("生产令号"); worksheet.Cells[0, 1] = new Cell("项目名称"); worksheet.Cells[0, 2] = new Cell("型号"); worksheet.Cells[0, 3] = new Cell("备注"); worksheet.Cells[0, 4] = new Cell("年份"); worksheet.Cells[0, 5] = new Cell("记录时间"); worksheet.Cells[0, 6] = new Cell("删除标记"); worksheet.Cells[0, 7] = new Cell("冻结标记"); worksheet.Cells[0, 8] = new Cell("发运完成时间"); worksheet.Cells[0, 9] = new Cell("项目合同号"); worksheet.Cells[0, 10] = new Cell("开票完成时间"); Int16 RowCount = 1; foreach (ProjectEntity projectEntity in ProjectEntityList) { int columnCount = 0; worksheet.Cells[RowCount, columnCount++] = new Cell(projectEntity.ManufactureNumber); worksheet.Cells[RowCount, columnCount++] = new Cell(projectEntity.ProjectName); worksheet.Cells[RowCount, columnCount++] = new Cell(projectEntity.ModelNumber); worksheet.Cells[RowCount, columnCount++] = new Cell(projectEntity.Remark); worksheet.Cells[RowCount, columnCount++] = new Cell(projectEntity.YearNumber.ToString()); worksheet.Cells[RowCount, columnCount++] = new Cell(projectEntity.RecordDateString); worksheet.Cells[RowCount, columnCount++] = new Cell(projectEntity.IsDeleteString); worksheet.Cells[RowCount, columnCount++] = new Cell(projectEntity.IsFreezeString); worksheet.Cells[RowCount, columnCount++] = new Cell(projectEntity.DeliveryTime.HasValue? projectEntity.DeliveryTime.Value.ToShortDateString():""); worksheet.Cells[RowCount, columnCount++] = new Cell(projectEntity.ContractNumber); worksheet.Cells[RowCount, columnCount++] = new Cell(projectEntity.InvoiceCompletionTime.HasValue? projectEntity.InvoiceCompletionTime.Value.ToShortDateString():""); RowCount++; } workbook.Worksheets.Add(worksheet); Stream sFile = sDialog.OpenFile(); workbook.Save(sFile); Message.InfoMessage("导出成功"); } catch (Exception outputE) { string errorMessage = "导出文件失败:" + outputE.Message; Message.ErrorMessage(errorMessage); } } }
//==================================================================================================================================== public static void YtgYtdExportToExcel(DataGrid theGrid) { // open file dialog to select an export file. SaveFileDialog sDialog = new SaveFileDialog(); sDialog.Filter = "Excel Files(*.xls)|*.xls"; if (sDialog.ShowDialog() == true) { // create an instance of excel workbook Workbook workbook = new Workbook(); // create a worksheet object Worksheet worksheet = new Worksheet("Data"); Int16 ColumnCount = 0; Int16 RowCount = 0; //Writing Column Names foreach (DataGridColumn dgcol in theGrid.Columns) { string header =""; if (ColumnCount == 0) { Setter setter = (Setter)dgcol.HeaderStyle.Setters[0]; ControlTemplate tem = (ControlTemplate)setter.Value; Grid contentGrid = (Grid)GetTemplateChildByName(theGrid, "ContentGrid"); StackPanel stack = (StackPanel)contentGrid.Children[0]; header = (stack.Children[0] as TextBlock).Text + "\n" + (stack.Children[1] as TextBlock).Text+ "\n" + (stack.Children[2] as TextBlock).Text; } if(dgcol.Header == null) dgcol.Header = header; worksheet.Cells[0, ColumnCount] = new Cell(dgcol.Header.ToString()); ColumnCount++; } //Extracting values from grid and writing to excell sheet // foreach (object data in theGrid.ItemsSource) { ColumnCount = 0; RowCount++; foreach (DataGridColumn col in theGrid.Columns) { string strValue = ""; Binding objBinding = null; if (col is DataGridBoundColumn) objBinding = (col as DataGridBoundColumn).Binding; if (col is DataGridTemplateColumn) { //This is a template column... let us see the underlying dependency object DependencyObject objDO = (col as DataGridTemplateColumn).CellTemplate.LoadContent(); FrameworkElement oFE = (FrameworkElement)objDO; FieldInfo oFI = oFE.GetType().GetField("TextProperty"); if (oFI != null) { if (oFI.GetValue(null) != null) { if (oFE.GetBindingExpression((DependencyProperty)oFI.GetValue(null)) != null) objBinding = oFE.GetBindingExpression((DependencyProperty)oFI.GetValue(null)).ParentBinding; } } } if (objBinding != null) { if (objBinding.Path.Path != "") { PropertyInfo pi = data.GetType().GetProperty(objBinding.Path.Path); if (pi != null) strValue = Convert.ToString(pi.GetValue(data, null)); } if (objBinding.Converter != null) { if (strValue != "") strValue = objBinding.Converter.Convert(strValue, typeof(string), objBinding.ConverterParameter, objBinding.ConverterCulture).ToString(); //else // strValue = objBinding.Converter.Convert(data, typeof(string), objBinding.ConverterParameter, objBinding.ConverterCulture).ToString(); } } // writing extracted value in excell cell Cell cell = new Cell(strValue); cell.Style = new CellStyle { BackColor = Colors.Orange, RichTextFormat = new Lite.ExcelLibrary.BinaryFileFormat.RichTextFormat(2)}; worksheet.Cells[RowCount, ColumnCount] = cell; ColumnCount++; } } //add worksheet to workbook workbook.Worksheets.Add(worksheet); // get the selected file's stream Stream sFile = sDialog.OpenFile(); workbook.Save(sFile); } }
private int ExcelSheetValidation(Worksheet aSheet, ref int aFirstValueRow, ref string aManufactureName, ref string aVersionId, ref string aFileId, ref string aRequirement, ref Dictionary<string, int> aMatchedColumnDictionary) { aFirstValueRow = -1; ColumnModel columnModel = new ColumnModel(); foreach (KeyValuePair<int, Row> rowPair in aSheet.Cells.Rows) { try { if (rowPair.Value.LastColIndex < columnModel.MinSize - 1) { Cell cell = rowPair.Value.GetCell(rowPair.Value.FirstColIndex); if (Cell.EmptyCell != cell) { string firstValue = cell.StringValue; string manufactureNumberKey = "生产令号:"; string versionIdKey1 = "计划版本:"; string versionIdKey2 = "计划版本:"; string versionIdKey = string.Empty; int pos = -1; if (-1 != (pos = firstValue.IndexOf(versionIdKey1))) { versionIdKey = versionIdKey1; } else if( -1 != (pos = firstValue.IndexOf(versionIdKey2))) { versionIdKey = versionIdKey2; } if (versionIdKey.Count() > 0) { int versionPos = pos + versionIdKey.Length; aVersionId = firstValue.Substring(versionPos); aVersionId = aVersionId.Trim(); string fileIdKey = "文件编号:"; int fileIdStartPos = firstValue.IndexOf(fileIdKey); if (-1 != fileIdStartPos) { fileIdStartPos += fileIdKey.Length; aFileId = firstValue.Substring(fileIdStartPos, versionPos - fileIdStartPos - versionIdKey.Count()); aFileId = aFileId.Trim(); } } else if (-1 != (pos = firstValue.IndexOf(manufactureNumberKey)) && pos < firstValue.Length) { pos += manufactureNumberKey.Length; const char space = ' '; while (space == firstValue[pos]) { ++pos; } int end = firstValue.IndexOf(space, pos); aManufactureName = firstValue.Substring(pos, end - pos); aManufactureName = aManufactureName.Trim(); aRequirement = firstValue.Substring(end); aRequirement = aRequirement.Trim(); } } continue; } } catch(Exception e) { //empty line continue; } foreach (ObservableCollection<string> item in columnModel.List) { int matchCount = 0; aMatchedColumnDictionary.Clear(); for (int column = rowPair.Value.FirstColIndex; column <= rowPair.Value.LastColIndex; ++column) { Cell cell = rowPair.Value.GetCell(column); if (Cell.EmptyCell != cell && item.Contains(cell.StringValue)) { aMatchedColumnDictionary.Add(cell.StringValue, column); ++matchCount; } } if (item.Count == matchCount) { aFirstValueRow = rowPair.Key + 1; return columnModel.List.IndexOf(item); } } } return -1; }
private void OnExportCommand() { SaveFileDialog sDialog = new SaveFileDialog(); sDialog.Filter = "Excel Files(*.xls)|*.xls"; if (sDialog.ShowDialog() == true) { try { Workbook workbook = new Workbook(); Worksheet worksheet = new Worksheet("负责人汇总"); //Title worksheet.Cells[0, 0] = new Cell("项目负责人"); worksheet.Cells[0, 1] = new Cell("生产令号"); worksheet.Cells[0, 2] = new Cell("项目名称"); worksheet.Cells[0, 3] = new Cell("项目备注"); worksheet.Cells[0, 4] = new Cell("记录时间"); worksheet.Cells[0, 5] = new Cell("发运完成时间"); Int16 RowCount = 1; foreach (ResponsiblePersonEntity responsiblePersonEntity in ResponsiblePersonEntityList) { int columnCount = 0; worksheet.Cells[RowCount, columnCount++] = new Cell(responsiblePersonEntity.ResponsiblePerson); worksheet.Cells[RowCount, columnCount++] = new Cell(responsiblePersonEntity.ManufactureNumber); worksheet.Cells[RowCount, columnCount++] = new Cell(responsiblePersonEntity.ProjectName); worksheet.Cells[RowCount, columnCount++] = new Cell(responsiblePersonEntity.ProjectNote); worksheet.Cells[RowCount, columnCount++] = new Cell(responsiblePersonEntity.RecoderDateTime.HasValue ? responsiblePersonEntity.RecoderDateTime.Value.ToShortDateString() : ""); worksheet.Cells[RowCount, columnCount++] = new Cell(responsiblePersonEntity.OutputDateTime.HasValue ? responsiblePersonEntity.OutputDateTime.Value.ToShortDateString() : ""); RowCount++; } workbook.Worksheets.Add(worksheet); Stream sFile = sDialog.OpenFile(); workbook.Save(sFile); Message.InfoMessage("导出成功"); } catch (Exception outputE) { string errorMessage = "导出文件失败:" + outputE.Message; Message.ErrorMessage(errorMessage); } } }