/// <summary>
/// dgv设置行号
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void dataGridView1_RowsAdded(object sender, DataGridViewRowsAddedEventArgs e)
{
int digit = GlobalUtils.DecimalDigits;
for (int i = 0; i < dataGridView1.Rows.Count; i++)
{
DataGridViewRow row = dataGridView1.Rows[i];
row.HeaderCell.Value = (i + 1).ToString();
for (int j = 0; j != dataGridView1.ColumnCount; ++j)
{
var value = dataGridView1.Rows[i].Cells[j].Value;
if (dataGridView1.Rows[i].Cells[j].ValueType == typeof(decimal) && value != null)
{
if (digit == -1)
{
dataGridView1.Rows[i].Cells[j].Value = DecimalHandler.DecimalToString((decimal)value);
}
else
{
dataGridView1.Rows[i].Cells[j].Value = DecimalHandler.DecimalToString((decimal)value, digit);
}
}
if (dataGridView1.Columns[j].Name == "MarginRate")
{
dataGridView1.Rows[i].Cells[j].Value = DecimalHandler.DecimalToPercent(DgvDataSourceToList()[i].MarginRate);
}
}
}
}
private void UpdateJianChiLv(object sender, EventArgs e)
{
decimal amount = DecimalHandler.Parse(txtAmount.Text);
decimal amountPurchase = _purchaseInfoModel.Amount ?? 0;
txtJianChiLv.Text = DecimalHandler.DecimalToPercent(amount / amountPurchase);
}
private void UpdateMarginRate(object sender, EventArgs e)
{
try
{
txtMarginRate.Text = DecimalHandler.DecimalToPercent(
(DecimalHandler.Parse(txtTotalSale.Text) / _purchaseInfoModel.TotalMoney ?? 0) - 1
);
}
catch (Exception)
{
txtMarginRate.Text = "供应商总金额有误!";
}
}
private void FrmAddSaleInfo_Load(object sender, EventArgs e)
{
this.MaximizeBox = false;
this.MinimizeBox = false;
this.FormBorderStyle = FormBorderStyle.FixedDialog;
InitComboBoxs();
if (_purchaseInfoModel != null)
{
InitCtrlsByPurchaseInfoModel();
}
if (_is4Modify)
{
//把选中的加载到这里面
txtJianChiLv.Text = DecimalHandler.DecimalToPercent(_model.JianChiLv);
txtArrivalTime.Text = DateTimeFormator.DateTimeToString(_model.ArrivalTime);
txtSettleTime.Text = DateTimeFormator.DateTimeToString(_model.SettleTime);
txtSupplier.Text = _model.Supplier;
txtBrand.Text = _model.Brand;
txtTransportWay.Text = _model.TransportWay;
txtSize.Text = _model.Size;
txtTexture.Text = _model.Texture;
txtAmount.Text = DecimalHandler.DecimalToString(_model.Amount);
txtMargin.Text = DecimalHandler.DecimalToString(_model.Margin);
txtOnlinePrice.Text = DecimalHandler.DecimalToString(_model.OnlinePrice);
txtFluctuation1.Text = DecimalHandler.DecimalToString(_model.Fluctuation1);
txtFluctuation2.Text = DecimalHandler.DecimalToString(_model.Fluctuation2);
txtSalePrice.Text = DecimalHandler.DecimalToString(_model.SalePrice);
txtTotalSale.Text = DecimalHandler.DecimalToString(_model.TotalSale);
txtMarginRate.Text = DecimalHandler.DecimalToPercent(_model.MarginRate);
txtCorporation.Text = _model.Corporation;
txtProject.Text = _model.Project;
txtTieXi.Text = DecimalHandler.DecimalToString(_model.TieXi);
txtLiXi.Text = DecimalHandler.DecimalToString(_model.LiXi);
this.Text = "修改销售信息";
}
AddAutoUpdateEvents();
}
// <summary>
///
///
/// <param name="list"></param>
/// <returns></returns>
public static bool GetSaleInfoReport(List <Model.SaleInfo> list)
{
//1.创建工作簿对象
IWorkbook wkbook = new HSSFWorkbook();
//2.创建工作表对象
ISheet sheet = wkbook.CreateSheet("采购信息");
//2.1创建表头
IRow row = sheet.CreateRow(0);
//row.HeightInPoints = 20;
row.CreateCell(0).SetCellValue("公司");
row.CreateCell(1).SetCellValue("项目");
row.CreateCell(2).SetCellValue("检尺率");
row.CreateCell(3).SetCellValue("到货时间");
row.CreateCell(4).SetCellValue("结算时间");
row.CreateCell(5).SetCellValue("供应商");
row.CreateCell(6).SetCellValue("品牌");
row.CreateCell(7).SetCellValue("运输方式");
row.CreateCell(8).SetCellValue("规格");
row.CreateCell(9).SetCellValue("材质");
row.CreateCell(10).SetCellValue("送货重量");
row.CreateCell(11).SetCellValue("价差");
row.CreateCell(12).SetCellValue("网价");
row.CreateCell(13).SetCellValue("浮动1");
row.CreateCell(14).SetCellValue("浮动1");
row.CreateCell(15).SetCellValue("贴息");
row.CreateCell(16).SetCellValue("利息");
row.CreateCell(17).SetCellValue("销售单价");
row.CreateCell(18).SetCellValue("销总金额");
row.CreateCell(19).SetCellValue("利润率");
//row.CreateCell(19).SetCellValue("付宇汇通日期");
//row.CreateCell(20).SetCellValue("付款金额");
//row.CreateCell(19).SetCellValue("开票日期");
//row.CreateCell(20).SetCellValue("发票金额");
//2.2设置列宽度
sheet.SetColumnWidth(0, 10 * 256); //时间
sheet.SetColumnWidth(1, 10 * 256); //对账
sheet.SetColumnWidth(2, 10 * 256); //开票日期
sheet.SetColumnWidth(3, 10 * 256); //发票
sheet.SetColumnWidth(4, 10 * 256); //收款日期
sheet.SetColumnWidth(5, 10 * 256); //收款
sheet.SetColumnWidth(6, 10 * 256); //收款
sheet.SetColumnWidth(7, 10 * 256); //收款
sheet.SetColumnWidth(8, 10 * 256); //收款
sheet.SetColumnWidth(9, 10 * 256); //收款
sheet.SetColumnWidth(10, 10 * 256); //收款
sheet.SetColumnWidth(11, 10 * 256); //收款
sheet.SetColumnWidth(12, 10 * 256); //收款
sheet.SetColumnWidth(13, 10 * 256); //收款
sheet.SetColumnWidth(14, 10 * 256); //收款
sheet.SetColumnWidth(15, 10 * 256); //收款
sheet.SetColumnWidth(16, 10 * 256); //收款
sheet.SetColumnWidth(17, 10 * 256); //收款
sheet.SetColumnWidth(18, 10 * 256); //收款
sheet.SetColumnWidth(19, 10 * 256); //收款
int digit = GlobalUtils.DecimalDigits;
//3.插入行和单元格
for (int i = 0; i != list.Count; ++i)
{
//创建单元格
row = sheet.CreateRow(i + 1);
////设置行高
//row.HeightInPoints = 50;
//设置值
row.CreateCell(0).SetCellValue(list[i].Corporation);
row.CreateCell(1).SetCellValue(list[i].Project);
row.CreateCell(2).SetCellValue(DecimalHandler.DecimalToPercent(list[i].JianChiLv));
row.CreateCell(3).SetCellValue(DateTimeFormator.DateTimeToString(list[i].ArrivalTime));
row.CreateCell(4).SetCellValue(DateTimeFormator.DateTimeToString(list[i].SettleTime));
row.CreateCell(5).SetCellValue(list[i].Supplier);
row.CreateCell(6).SetCellValue(list[i].Brand);
row.CreateCell(7).SetCellValue(list[i].TransportWay);
row.CreateCell(8).SetCellValue(list[i].Size);
row.CreateCell(9).SetCellValue(list[i].Texture);
row.CreateCell(10).SetCellValue(DecimalHandler.DecimalToString(list[i].Amount, digit));
row.CreateCell(11).SetCellValue(DecimalHandler.DecimalToString(list[i].Margin, digit));
row.CreateCell(12).SetCellValue(DecimalHandler.DecimalToString(list[i].OnlinePrice, digit));
row.CreateCell(13).SetCellValue(DecimalHandler.DecimalToString(list[i].Fluctuation1, digit));
row.CreateCell(14).SetCellValue(DecimalHandler.DecimalToString(list[i].Fluctuation2, digit));
row.CreateCell(15).SetCellValue(DecimalHandler.DecimalToString(list[i].TieXi, digit));
row.CreateCell(16).SetCellValue(DecimalHandler.DecimalToString(list[i].LiXi, digit));
row.CreateCell(17).SetCellValue(DecimalHandler.DecimalToString(list[i].SalePrice, digit));
row.CreateCell(18).SetCellValue(DecimalHandler.DecimalToString(list[i].TotalSale, digit));
row.CreateCell(19).SetCellValue(DecimalHandler.DecimalToPercent(list[i].MarginRate));
}
////统计的一行
//row = sheet.CreateRow(list.Count + 1);
//row.CreateCell(0);
//row.CreateCell(1).SetCellValue("合计");
//row.CreateCell(2).SetCellValue(DecimalHandler.DecimalToString(total[0], 2));
//row.CreateCell(3).SetCellValue(DecimalHandler.DecimalToString(total[1], 2));
//row.CreateCell(4).SetCellValue(DecimalHandler.DecimalToString(total[2], 2));
//row.CreateCell(5).SetCellValue(DecimalHandler.DecimalToString(total[3], 2));
//row.CreateCell(6).SetCellValue(DecimalHandler.DecimalToString(total[4], 2));
//row.CreateCell(7).SetCellValue(DecimalHandler.DecimalToString(total[5], 2));
//row.CreateCell(8).SetCellValue(DecimalHandler.DecimalToString(total[6], 2));
//row.CreateCell(9).SetCellValue(DecimalHandler.DecimalToString(total[7], 2));
//row.CreateCell(10).SetCellValue(DecimalHandler.DecimalToString(total[8], 2));
//row.CreateCell(11).SetCellValue(DecimalHandler.DecimalToString(total[9], 2));
//4.1设置对齐风格和边框
ICellStyle style = wkbook.CreateCellStyle();
style.VerticalAlignment = VerticalAlignment.Center;
style.Alignment = HorizontalAlignment.Right;
style.BorderTop = BorderStyle.Thin;
style.BorderBottom = BorderStyle.Thin;
style.BorderLeft = BorderStyle.Thin;
style.BorderRight = BorderStyle.Thin;
short color = style.FillForegroundColor;
for (int i = 0; i <= sheet.LastRowNum; i++)
{
row = sheet.GetRow(i);
if (i == 0)
{
style.FillForegroundColor = HSSFColor.BlueGrey.Index;
style.FillPattern = FillPattern.SolidForeground;
}
else
{
style.FillForegroundColor = color;
style.FillPattern = FillPattern.NoFill;
}
for (int c = 0; c < row.LastCellNum; ++c)
{
row.GetCell(c).CellStyle = style;
}
}
////4.2合并单元格
//sheet.AddMergedRegion(new CellRangeAddress(1, sheet.LastRowNum, 12, 12));
//sheet.AddMergedRegion(new CellRangeAddress(1, sheet.LastRowNum, 13, 13));
//5.执行写入磁盘
string dstName = GlobalUtils.ShowSaveFileDlg("销售信息.xls", "office 2003 excel|*.xls");
return(SaveFile(dstName, wkbook));
}
// <summary>
///
///
/// <param name="list"></param>
/// <returns></returns>
public static bool GetSupplierCount(List <Model.SupplierBill> list)
{
//1.创建工作簿对象
IWorkbook wkbook = new HSSFWorkbook();
//2.创建工作表对象
ISheet sheet = wkbook.CreateSheet("供应商");
//2.1创建表头
IRow row = sheet.CreateRow(0);
row.CreateCell(0).SetCellValue("供应商");
row.CreateCell(1).SetCellValue("供应商购金额");
row.CreateCell(2).SetCellValue("重量");
row.CreateCell(3).SetCellValue("销售总额");
row.CreateCell(4).SetCellValue("利润率");
row.CreateCell(5).SetCellValue("运费");
//2.2设置列宽度
sheet.SetColumnWidth(0, 20 * 256); //时间
sheet.SetColumnWidth(1, 20 * 256); //对账
sheet.SetColumnWidth(2, 20 * 256); //开票日期
sheet.SetColumnWidth(3, 20 * 256); //发票
sheet.SetColumnWidth(4, 20 * 256); //收款日期
sheet.SetColumnWidth(5, 20 * 256); //收款
decimal[] total = new decimal[4];
decimal invoiceTotal = 0;
decimal receiptTotal = 0;
//3.插入行和单元格
for (int i = 0; i != list.Count; ++i)
{
//创建单元格
row = sheet.CreateRow(i + 1);
////设置行高
//row.HeightInPoints = 50;
//设置值
row.CreateCell(0).SetCellValue(list[i].Supplier);
row.CreateCell(1).SetCellValue(DecimalHandler.DecimalToString(list[i].TotalPurchase, 2));
total[0] += list[i].TotalPurchase;
row.CreateCell(2).SetCellValue(DecimalHandler.DecimalToString(list[i].PurchaseAmount, 1));
row.CreateCell(3).SetCellValue(DecimalHandler.DecimalToString(list[i].TotalSale, 2));
total[1] += list[i].PurchaseAmount;
total[2] += list[i].TotalSale;
total[3] += list[i].TransportCost;
row.CreateCell(4).SetCellValue(DecimalHandler.DecimalToPercent(list[i].MarginRate));
row.CreateCell(5).SetCellValue(DecimalHandler.DecimalToString(list[i].TransportCost, 2));
}
//统计的一行
row = sheet.CreateRow(list.Count + 1);
row.CreateCell(0).SetCellValue("合计");
row.CreateCell(1).SetCellValue(DecimalHandler.DecimalToString(total[0], 2));
row.CreateCell(2).SetCellValue(DecimalHandler.DecimalToString(total[1], 1));
row.CreateCell(3).SetCellValue(DecimalHandler.DecimalToString(total[2], 2));
row.CreateCell(4);
row.CreateCell(5).SetCellValue(DecimalHandler.DecimalToString(total[3], 2));
//统计的一行
row = sheet.CreateRow(list.Count + 2);
row.CreateCell(0).SetCellValue("利润");
row.CreateCell(1).SetCellValue(DecimalHandler.DecimalToString(total[2] - total[0], 2));
row.CreateCell(2);
row.CreateCell(3);
row.CreateCell(4).SetCellValue(DecimalHandler.DecimalToPercent((total[2] - total[0]) / total[0]));
//4.1设置对齐风格和边框
ICellStyle style = wkbook.CreateCellStyle();
style.VerticalAlignment = VerticalAlignment.Center;
style.Alignment = HorizontalAlignment.Right;
style.BorderTop = BorderStyle.Thin;
style.BorderBottom = BorderStyle.Thin;
style.BorderLeft = BorderStyle.Thin;
style.BorderRight = BorderStyle.Thin;
for (int i = 0; i <= sheet.LastRowNum; i++)
{
row = sheet.GetRow(i);
for (int c = 0; c < row.LastCellNum; ++c)
{
row.GetCell(c).CellStyle = style;
}
}
////4.2合并单元格
//sheet.AddMergedRegion(new CellRangeAddress(1, sheet.LastRowNum, 12, 12));
//sheet.AddMergedRegion(new CellRangeAddress(1, sheet.LastRowNum, 13, 13));
//5.执行写入磁盘
string dstName = GlobalUtils.ShowSaveFileDlg(list[0].Corporation + "_" + list[0].Project + ".xls", "office 2003 excel|*.xls");
return(SaveFile(dstName, wkbook));
}
