private void btnExportBill_Click(object sender, EventArgs e) { //装备单据数据 DataTable dt = PrepareBillData(); #region 导出数据 try { SpecialDirectories sp = new SpecialDirectories(); string fileName = FileDialogHelper.SaveExcel(string.Format("入库单({0})", DateTime.Now.ToString("yyyy-MM-dd")), sp.Desktop); if (string.IsNullOrEmpty(fileName)) { return; } string outError = ""; AsposeExcelTools.DataTableToExcel2(dt, fileName, out outError); if (!string.IsNullOrEmpty(outError)) { MessageDxUtil.ShowError(outError); LogTextHelper.Error(outError); } else { Process.Start(fileName); } } catch (Exception ex) { MessageDxUtil.ShowError(ex.Message); LogTextHelper.Error(ex); } #endregion }
private void ExportExelReport(string headerId, string year) { // TODO string sql = string.Format(@"Select [ItemType],[CostCenterOrDept], [One],[Two],[Three],[Four],[Five],[Six], [Seven],[Eight],[Nine],[Ten],[Eleven],[Twelve],[Total] from WM_ReportAnnualCostDetail WHERE Header_ID={0} ", headerId); WorkbookDesigner designer = new WorkbookDesigner(); if (ReportType == MonthlyReportType.全年费用汇总表) { #region MyRegion DataTable dtCostCenter = BLLFactory <WareHouse> .Instance.SqlTable(sql + string.Format(" AND ReportCode='001' AND Total <> 0 ")); dtCostCenter.TableName = "CostCenter"; DataTable dtNormalDept = BLLFactory <WareHouse> .Instance.SqlTable(sql + string.Format(" AND ReportCode='002' AND Total <> 0 ")); dtNormalDept.TableName = "NormalDept"; DataTable dtSpecialDept = BLLFactory <WareHouse> .Instance.SqlTable(sql + string.Format(" AND ReportCode='003' AND Total <> 0 ")); dtSpecialDept.TableName = "SpecialDept"; string path = System.IO.Path.Combine(Application.StartupPath, "Report3.xls"); designer.Workbook = new Workbook(path); designer.SetDataSource("Year", year); designer.SetDataSource(dtCostCenter); designer.SetDataSource(dtSpecialDept); designer.SetDataSource(dtNormalDept); designer.Process(); #endregion } #region 保存Excel文件 SpecialDirectories sp = new SpecialDirectories(); SaveFileDialog dialog = new SaveFileDialog(); dialog.Filter = "Excel(*.xls)|*.xls|All File(*.*)|*.*"; dialog.Title = "保存Excel"; dialog.InitialDirectory = sp.Desktop; dialog.RestoreDirectory = true; if (dialog.ShowDialog() == DialogResult.OK) { string fileToSave = dialog.FileName; if (string.IsNullOrEmpty(fileToSave)) { return; } if (File.Exists(fileToSave)) { File.Delete(fileToSave); } designer.Workbook.Save(fileToSave, SaveFormat.Excel97To2003); Process.Start(fileToSave); } #endregion }
private void SpecilaExport() { string TakeOutBill = Path.Combine(Application.StartupPath, "TakeOutBill.xls"); WorkbookDesigner designer = new WorkbookDesigner(); designer.Workbook = new Workbook(TakeOutBill); designer.SetDataSource("TakeOutDate", DateTime.Now.ToString("yyyy-MM-dd")); designer.SetDataSource("WareHouse", this.txtWareHouse.Text); designer.SetDataSource("Manager", this.txtCreator.Text); designer.SetDataSource("CostCenter", this.txtCostCenter.Text); //designer.SetDataSource("Dept", this.txtDept.Text); string columns = "Start|int,ItemNo,ItemName,Specification,Unit,Price|decimal,Count|int"; DataTable dt = DataTableHelper.CreateTable(columns); dt.TableName = "Detail"; DataRow row = null; for (int i = 0; i < this.lvwDetail.Items.Count; i++) { PurchaseDetailInfo info = this.lvwDetail.Items[i].Tag as PurchaseDetailInfo; if (info != null) { row = dt.NewRow(); row["Start"] = (i + 1); row["ItemNo"] = info.ItemNo; row["ItemName"] = info.ItemName; row["Specification"] = info.Specification; row["Unit"] = info.Unit; row["Price"] = info.Price; row["Count"] = Math.Abs(info.Quantity); dt.Rows.Add(row); } } designer.SetDataSource(dt); designer.Process(); SpecialDirectories sp = new SpecialDirectories(); string fileToSave = FileDialogHelper.SaveExcel(string.Format("出库单({0})", DateTime.Now.ToString("yyyy-MM-dd")), sp.Desktop); if (string.IsNullOrEmpty(fileToSave)) { return; } if (File.Exists(fileToSave)) { File.Delete(fileToSave); } designer.Workbook.Save(fileToSave, SaveFormat.Excel97To2003); Process.Start(fileToSave); }
private void btnExportDetail_Click(object sender, EventArgs e) { if (this.winGridView1.gridView1.RowCount == 0) { return; } #region 构造数据列表 //HandNo,ItemNo,ItemName,MapNo,Specification,Material,ItemBigType,ItemType,Unit,Price,Quantity,Amount,Source,StoragePos,UsagePos,d.WareHouse,d.Dept string columns = "货单号,项目编号,项目名称,图号,规格型号,材质,备件属类,备件类别,单位,单价|decimal,数量|int,金额|decimal,来源,库位,使用位置,库房,部门"; DataTable dtDetail = DataTableHelper.CreateTable(columns); DataGridViewRow row; for (int i = 0; i < winGridView1.gridView1.RowCount; i++) { string ID = this.winGridView1.gridView1.GetRowCellDisplayText(i, "ID"); if (!string.IsNullOrEmpty(ID)) { DataTable dt = BLLFactory <PurchaseDetail> .Instance.GetPurchaseDetailReportByID(Convert.ToInt32(ID)); dt.Rows.Add(dt.NewRow()); //复制到中文列的表中 for (int k = 0; k < dt.Rows.Count; k++) { DataRow r = dtDetail.NewRow(); for (int j = 0; j < dt.Columns.Count; j++) { r[j] = dt.Rows[k][j]; } dtDetail.Rows.Add(r); } } } #endregion #region 导出数据操作 SpecialDirectories sp = new SpecialDirectories(); string fileToSave = FileDialogHelper.SaveExcel(string.Format("入库单明细({0})", DateTime.Now.ToString("yyyy-MM-dd")), sp.Desktop); if (string.IsNullOrEmpty(fileToSave)) { return; } try { string fileName = fileToSave; string outError = ""; AsposeExcelTools.DataTableToExcel2(dtDetail, fileName, out outError); if (!string.IsNullOrEmpty(outError)) { MessageDxUtil.ShowError(outError); LogTextHelper.Error(outError); } else { Process.Start(fileName); } } catch (Exception ex) { MessageDxUtil.ShowError(ex.Message); LogTextHelper.Error(ex); } #endregion }
private void btnExportBill_Click(object sender, EventArgs e) { #region 准备数据 string columns = @"流水号,备注,供货商,操作员,库房名称,备件编号(pm码),备件名称,图号,规格型号,材质,备件属类,备件类别,单位,最新单价(元),入库数量,总价,入库日期,来源,库位,部门,使用位置"; DataTable dt = DataTableHelper.CreateTable(columns); DataRow row = null; for (int i = 0; i < this.lvwDetail.Items.Count; i++) { PurchaseDetailInfo info = this.lvwDetail.Items[i].Tag as PurchaseDetailInfo; if (info != null) { row = dt.NewRow(); row["流水号"] = this.txtHandNo.Text; row["备注"] = this.txtNote.Text; row["供货商"] = this.txtManufacture.Text; row["操作员"] = this.txtCreator.Text; row["入库日期"] = this.txtCreateDate.DateTime.ToString(); row["库房名称"] = info.WareHouse; row["备件编号(pm码)"] = info.ItemNo; row["备件名称"] = info.ItemName; row["图号"] = info.MapNo; row["规格型号"] = info.Specification; row["材质"] = info.Material; row["备件属类"] = info.ItemBigType; row["备件类别"] = info.ItemType; row["单位"] = info.Unit; row["最新单价(元)"] = info.Price.ToString("C2"); row["入库数量"] = info.Quantity.ToString(); row["总价"] = info.Amount.ToString("C2"); row["来源"] = info.Source; row["库位"] = info.StoragePos; row["部门"] = info.Dept; row["使用位置"] = info.UsagePos; dt.Rows.Add(row); } } #endregion #region 导出数据 try { SpecialDirectories sp = new SpecialDirectories(); string fileName = FileDialogHelper.SaveExcel(string.Format("入库单({0})", DateTime.Now.ToString("yyyy-MM-dd")), sp.Desktop); if (string.IsNullOrEmpty(fileName)) { return; } string outError = ""; AsposeExcelTools.DataTableToExcel2(dt, fileName, out outError); if (!string.IsNullOrEmpty(outError)) { MessageDxUtil.ShowError(outError); LogTextHelper.Error(outError); } else { Process.Start(fileName); } } catch (Exception ex) { MessageDxUtil.ShowError(ex.Message); LogTextHelper.Error(ex); } #endregion }
private void ExportExelReport(string headerId, string yearhMonth) { // TODO string sql = string.Format(@"Select [LastCount] as LC, [LastMoney] as LM, [CurrentInCount] as CIC, [CurrentInMoney] as CIM, [CurrentOutCount] as COC, [CurrentOutMoney] as COM, [CurrentCount] as CC, [CurrentMoney] as CM, ItemName from WM_ReportMonthlyDetail WHERE Header_ID={0} ", headerId); WorkbookDesigner designer = new WorkbookDesigner(); if (ReportType == MonthlyReportType.库房部门结存) { #region MyRegion DataTable dt = BLLFactory <WareHouse> .Instance.SqlTable(sql); dt.TableName = "BigType"; if (dt.Rows.Count == 0) { return; } string path = System.IO.Path.Combine(Application.StartupPath, "Report2-2.xls"); designer.Workbook = new Workbook(path); designer.SetDataSource(dt); designer.SetDataSource("YearMonth", yearhMonth); designer.Process(); #endregion } else if (ReportType == MonthlyReportType.库房结存) { #region MyRegion DataTable dt = BLLFactory <WareHouse> .Instance.SqlTable(sql); dt.TableName = "BigType"; if (dt.Rows.Count == 0) { return; } string path = System.IO.Path.Combine(Application.StartupPath, "Report2-3.xls"); designer.Workbook = new Workbook(path); designer.SetDataSource(dt); designer.SetDataSource("YearMonth", yearhMonth); designer.Process(); #endregion } else if (ReportType == MonthlyReportType.所有库房结存) { #region MyRegion DataTable dtBigType = BLLFactory <WareHouse> .Instance.SqlTable(sql + " AND ReportCode ='ItemBigType'"); dtBigType.TableName = "BigType"; if (dtBigType.Rows.Count == 0) { return; } DataTable dtItemType = BLLFactory <WareHouse> .Instance.SqlTable(sql + " AND ReportCode ='ItemType'"); dtItemType.TableName = "ItemType"; string path = System.IO.Path.Combine(Application.StartupPath, "Report2-1.xls"); designer.Workbook = new Workbook(path); designer.SetDataSource(dtBigType); designer.SetDataSource(dtItemType); designer.SetDataSource("YearMonth", yearhMonth); designer.Process(); #endregion } else if (ReportType == MonthlyReportType.车间成本月报表) { // TODO sql = string.Format(@"Select [DeptName], [ItemType], [TotalMoney] from WM_ReportMonthlyCostDetail WHERE Header_ID={0} ", headerId); DataTable dt = BLLFactory <WareHouse> .Instance.SqlTable(sql); if (dt.Rows.Count == 0) { return; } #region 获取备件类型和部门列表(不重复记录) List <string> itemTypeList = new List <string>(); List <string> partList = new List <string>(); foreach (DataRow row in dt.Rows) { string itemType = row["ItemType"].ToString(); if (!itemTypeList.Contains(itemType)) { itemTypeList.Add(itemType); } string part = row["DeptName"].ToString(); if (!partList.Contains(part)) { partList.Add(part); } } #endregion #region 构造部门及备件类型的二维表格数据 string columnString = "DeptName"; for (int i = 0; i < itemTypeList.Count; i++) { columnString += string.Format(",TotalMoney{0}|decimal", i); } DataTable dtBigType = DataTableHelper.CreateTable(columnString); dtBigType.TableName = "BigType"; foreach (string part in partList) { DataRow row = dtBigType.NewRow(); row["DeptName"] = part; for (int i = 0; i < itemTypeList.Count; i++) { string itemType = itemTypeList[i]; DataRow[] rowSelect = dt.Select(string.Format("DeptName='{0}' AND ItemType='{1}'", part, itemType)); if (rowSelect.Length > 0) { row["TotalMoney" + i.ToString()] = rowSelect[0]["TotalMoney"]; } } dtBigType.Rows.Add(row); } #endregion #region 动态构造栏目 string path = System.IO.Path.Combine(Application.StartupPath, "Report1.xls"); designer.Workbook = new Workbook(path); Aspose.Cells.Worksheet w = designer.Workbook.Worksheets[0]; //先设置标题项目:如大修件,日常备件等 int rowIndex = 2; //第三行为标题 Aspose.Cells.Style boldStyle = w.Cells[rowIndex, 0].GetStyle(); //继承开始栏目的样式 Aspose.Cells.Style style = w.Cells[rowIndex + 1, 1].GetStyle(); //继承数字栏目的样式 style.Number = 4; //金额数据对应格式是#,##0.00 for (int i = 0; i < itemTypeList.Count; i++) { //动态添加备件类型栏目 w.Cells[rowIndex, i + 1].PutValue(itemTypeList[i]); w.Cells[rowIndex, i + 1].SetStyle(boldStyle); //动态添加下面的对应的金额数据 w.Cells[rowIndex + 1, i + 1].PutValue("&=BigType.TotalMoney" + i.ToString()); w.Cells[rowIndex + 1, i + 1].SetStyle(style); } //添加合计行 w.Cells[rowIndex, itemTypeList.Count + 1].PutValue("合计"); w.Cells[rowIndex, itemTypeList.Count + 1].SetStyle(boldStyle); w.Cells[rowIndex + 1, itemTypeList.Count + 1].PutValue(string.Format("&=&=SUM(B{{r}}:{0}{{r}})", GetChar(itemTypeList.Count + 1))); w.Cells[rowIndex + 1, itemTypeList.Count + 1].SetStyle(style); #endregion designer.SetDataSource(dtBigType); designer.SetDataSource("YearMonth", yearhMonth); designer.Process(); } #region 保存Excel文件 SpecialDirectories sp = new SpecialDirectories(); SaveFileDialog dialog = new SaveFileDialog(); dialog.Filter = "Excel(*.xls)|*.xls|All File(*.*)|*.*"; dialog.Title = "保存Excel"; dialog.InitialDirectory = sp.Desktop; dialog.RestoreDirectory = true; if (dialog.ShowDialog() == DialogResult.OK) { string fileToSave = dialog.FileName; if (string.IsNullOrEmpty(fileToSave)) { return; } if (File.Exists(fileToSave)) { File.Delete(fileToSave); } designer.Workbook.Save(fileToSave, SaveFormat.Excel97To2003); Process.Start(fileToSave); } #endregion }
/// <summary> /// Class XtrmAddons Net Application Serializable Preferences constructor. /// </summary> public Preferences() { SpecialDirectories = new SpecialDirectories(); Storage = new StorageOptions(); }
private void btnExportBill_Click(object sender, EventArgs e) { #region 准备数据 string columns = @"流水号,备注,供货商,操作员,库房名称,备件编号(pm码),备件名称,图号,规格型号,材质,备件属类,备件类别,单位,最新单价(元),出库数量,总价,出库日期,来源,库位,部门,使用位置"; DataTable dt = DataTableHelper.CreateTable(columns); DataRow row = null; for (int i = 0; i < this.lvwDetail.gridView1.RowCount; i++) { PurchaseDetailInfo info = lvwDetail.gridView1.GetRow(i) as PurchaseDetailInfo; if (info != null) { row = dt.NewRow(); /*row["流水号"] = this.txtHandNo.Text; * row["备注"] = this.txtNote.Text; * row["供货商"] = this.txtPickingPeople.Text; * row["操作员"] = this.txtCreator.Text; * row["出库日期"] = this.txtCreateDate.DateTime.ToString(); * row["库房名称"] = info.WareHouse; * row["备件编号(pm码)"] = info.ItemNo; * row["备件名称"] = info.ItemName; * row["图号"] = info.MapNo; * row["规格型号"] = info.Specification; * row["材质"] = info.Material; * row["备件属类"] = info.ItemBigType; * row["备件类别"] = info.ItemType; * row["单位"] = info.Unit; * row["最新单价(元)"] = info.Price.ToString("C2"); * row["出库数量"] = info.Quantity.ToString(); * row["总价"] = info.Amount.ToString("C2"); * row["来源"] = info.Source; * row["库位"] = info.StoragePos; * row["部门"] = info.Dept; * row["使用位置"] = info.UsagePos;*/ dt.Rows.Add(row); } } #endregion #region 导出数据 try { SpecialDirectories sp = new SpecialDirectories(); string fileName = FileDialogHelper.SaveExcel(string.Format("出库单({0})", DateTimeHelper.GetServerDate()), sp.Desktop); if (string.IsNullOrEmpty(fileName)) { return; } string outError = ""; AsposeExcelTools.DataTableToExcel2(dt, fileName, out outError); if (!string.IsNullOrEmpty(outError)) { LogHelper.WriteLog(LogLevel.LOG_LEVEL_CRIT, outError, typeof(FrmPurchase)); MessageDxUtil.ShowError(outError); } else { Process.Start(fileName); } } catch (Exception ex) { LogHelper.WriteLog(LogLevel.LOG_LEVEL_CRIT, ex, typeof(FrmPurchase)); MessageDxUtil.ShowError(ex.Message); } #endregion }