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
}
