private void DataGridView1_SelectionChanged(object sender, EventArgs e)
{
if (dataGridView1.SelectedRows.Count < 1)
{
return;
}
int digit = GlobalUtils.DecimalDigits;
decimal goujinzonge = 0, goujinzhongliang = 0, xiaoshouzonge = 0, xiaoshouzhongliang = 0, yunfei = 0;
for (int i = 0; i < dataGridView1.SelectedRows.Count; ++i)
{
var model = DgvDataSourceToList()[dataGridView1.SelectedRows[i].Index];
goujinzonge += DecimalHandler.Parse(model.TotalPurchase.ToString());
goujinzhongliang += DecimalHandler.Parse(model.PurchaseAmount.ToString());
xiaoshouzonge += DecimalHandler.Parse(model.TotalSale.ToString());
xiaoshouzhongliang += DecimalHandler.Parse(model.SaleAmount.ToString());
yunfei += DecimalHandler.Parse(model.TransportCost.ToString());
}
lbTotalCount.Text = string.Format("选中{5}项 合计: 购进额 {0} 购进量 {1} 销售总额 {2} 销售量 {3} 运费 {4}",
DecimalHandler.DecimalToString(goujinzonge, digit),
DecimalHandler.DecimalToString(goujinzhongliang, digit),
DecimalHandler.DecimalToString(xiaoshouzonge, digit),
DecimalHandler.DecimalToString(xiaoshouzhongliang, digit),
DecimalHandler.DecimalToString(yunfei, digit),
dataGridView1.SelectedRows.Count);
if (goujinzonge == 0)
{
//lbCount1.Text = string.Format("利润 {0} 利润率 {1}",
//DecimalHandler.DecimalToString(xiaoshouzonge - goujinzonge), -1);
}
}
private void UpdateTotalSale(object sender, EventArgs e)
{
txtTotalSale.Text = DecimalHandler.DecimalToString(
DecimalHandler.Parse(txtSalePrice.Text) *
DecimalHandler.Parse(txtAmount.Text)
);
}
/// <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);
}
}
}
}
}
private void FrmAddPurchaseBill_Load(object sender, EventArgs e)
{
this.MaximizeBox = false;
this.MinimizeBox = false;
this.FormBorderStyle = FormBorderStyle.FixedDialog;
InitComboBoxs();
if (_purchaseInfoModel != null)
{
InitCtrlsByPurchaseInfoModel();
}
if (_is4Modify)
{
//把选中的加载到这里面
txtCorporation.Text = _model.Corporation;
txtProject.Text = _model.Project;
txtSupplier.Text = _model.Supplier;
txtPayer.Text = _model.Payer;
txtAmount.Text = DecimalHandler.DecimalToString(_model.Amount);
this.Text = "修改销售收款";
}
}
private void DataGridView1_SelectionChanged(object sender, EventArgs e)
{
if (dataGridView1.SelectedRows.Count < 1)
{
return;
}
int digit = GlobalUtils.DecimalDigits;
decimal amountTotal = 0, duizhang = 0, invoicenum = 0, receiptnum = 0;
for (int i = 0; i < dataGridView1.SelectedRows.Count; ++i)
{
var model = DgvDataSourceToList()[dataGridView1.SelectedRows[i].Index];
amountTotal += DecimalHandler.Parse(model.Amount.ToString());
duizhang += DecimalHandler.Parse(model.DuiZhang.ToString());
invoicenum += DecimalHandler.Parse(model.InvoiceNum.ToString());
receiptnum += DecimalHandler.Parse(model.ReceiptNum.ToString());
}
lbTotalCount.Text = string.Format("选中{4}项 合计: 金额 {0} 对账 {1} 开票金额 {2} 收款金额 {3}",
DecimalHandler.DecimalToString(amountTotal, digit),
DecimalHandler.DecimalToString(duizhang, digit),
DecimalHandler.DecimalToString(invoicenum, digit),
DecimalHandler.DecimalToString(receiptnum, digit),
dataGridView1.SelectedRows.Count
);
}
private void FrmAddSaleBill_Load(object sender, EventArgs e)
{
this.MaximizeBox = false;
this.MinimizeBox = false;
this.FormBorderStyle = FormBorderStyle.FixedDialog;
InitComboBoxs();
if (_saleInfoModel != null)
{
InitCtrlsBySaleInfoModel();
}
if (_is4Modify)
{
//把选中的加载到这里面
txtCorporation.Text = _model.Corporation;
txtProject.Text = _model.Project;
txtSupplier.Text = _model.Supplier;
txtDuiZhang.Text = DecimalHandler.DecimalToString(_model.DuiZhang);
txtAmount.Text = DecimalHandler.DecimalToString(_model.Amount);
txtInvoiceNum.Text = DecimalHandler.DecimalToString(_model.InvoiceNum);
txtReceiptNum.Text = DecimalHandler.DecimalToString(_model.ReceiptNum);
txtInvoiceDate.Text = _model.InvoiceDate.ToString();
txtReceiptDate.Text = _model.ReceiptDate.ToString();
this.Text = "修改销售收款";
}
}
private void UpdatePrice(object sender, EventArgs e)
{
decimal quote = DecimalHandler.Parse(txtQuote.Text);
decimal fluctuation = DecimalHandler.Parse(txtFluctuation1.Text);
decimal transportCost = DecimalHandler.Parse(txtTransportCost.Text);
txtPrice.Text = DecimalHandler.DecimalToString(quote + fluctuation + transportCost);
}
private void UpdateSalePrice(object sender, EventArgs e)
{
txtSalePrice.Text = DecimalHandler.DecimalToString(
DecimalHandler.Parse(txtOnlinePrice.Text) +
DecimalHandler.Parse(txtFluctuation1.Text) +
DecimalHandler.Parse(txtFluctuation2.Text)
);
}
private void FrmAddPurchaseInfo_Load(object sender, EventArgs e)
{
this.MaximizeBox = false;
this.MinimizeBox = false;
this.FormBorderStyle = FormBorderStyle.FixedDialog;
InitComboBoxs();
if (_steelInfomodel != null)
{
InitCtrlsBySteelInfoModel();
}
if (_is4Modify)
{
//把选中的加载到这里面
txtCorporation.Text = _model.Corporation;
txtProject.Text = _model.Project;
txtDateline.Text = _model.Dateline.ToString();
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);
txtQuote.Text = DecimalHandler.DecimalToString(_model.Quote);
txtFluctuation1.Text = DecimalHandler.DecimalToString(_model.Fluctuation1);
txtTransportCost.Text = DecimalHandler.DecimalToString(_model.TransportCost);
txtPrice.Text = DecimalHandler.DecimalToString(_model.Price);
txtTotalMoney.Text = DecimalHandler.DecimalToString(_model.TotalMoney);
txtFuYuDate.Text = _model.FuYuDate.ToString();
txtMoney1.Text = DecimalHandler.DecimalToString(_model.Money1);
txtInvoiceDate.Text = _model.InvoiceDate.ToString();
txtMoney2.Text = DecimalHandler.DecimalToString(_model.Money2);
txtDiaoZhuang.Text = _model.DiaoZhuang.ToString();
txtLiXi.Text = DecimalHandler.DecimalToString(_model.LiXi);
txtChengDui.Text = DecimalHandler.DecimalToString(_model.ChengDui);
txtOtherCost.Text = DecimalHandler.DecimalToString(_model.OtherCost);
txtTieXi.Text = DecimalHandler.DecimalToString(_model.TieXi);
this.Text = "修改采购信息";
}
AddAutoUpdateEvents();
}
private void DataGridView1_SelectionChanged(object sender, EventArgs e)
{
if (dataGridView1.SelectedRows.Count < 1 || dataGridView1.Rows.Count < 1)
{
return;
}
int digit = GlobalUtils.DecimalDigits;
decimal[] total = new decimal[10];
for (int i = 0; i < dataGridView1.SelectedRows.Count; ++i)
{
var model = DgvDataSourceToList()[dataGridView1.SelectedRows[i].Index];
if (model == null)
{
return;
}
total[0] += DecimalHandler.Parse(model.TotalSale.ToString());
total[1] += DecimalHandler.Parse(model.InvoiceNum.ToString());
total[2] += DecimalHandler.Parse(model.FaHuoWeiKaiPiao.ToString());
total[3] += DecimalHandler.Parse(model.ReceiptNum.ToString());
total[4] += DecimalHandler.Parse(model.ZhangMianQianKuan.ToString());
total[5] += DecimalHandler.Parse(model.JinChangWeiYanShou.ToString());
total[6] += DecimalHandler.Parse(model.ZongQianKuan.ToString());
total[7] += DecimalHandler.Parse(model.WeiDuiZhang.ToString());
total[8] += DecimalHandler.Parse(model.DuiZhangWeiKaiPiao.ToString());
total[9] += DecimalHandler.Parse(model.DuiZhangNum.ToString());
}
lbTotalCount.Text = string.Format("选中{10}项 合计: 发货金额(销总金额) {0} 验收(发票) {1} 发货未开票 {2} " +
"已付款 {3} 账面欠款 {4} 进场未验收 {5} 总欠款 {6} 未对账 {7} 对账未开票 {8} 已对账{9}",
DecimalHandler.DecimalToString(total[0], digit),
DecimalHandler.DecimalToString(total[1], digit),
DecimalHandler.DecimalToString(total[2], digit),
DecimalHandler.DecimalToString(total[3], digit),
DecimalHandler.DecimalToString(total[4], digit),
DecimalHandler.DecimalToString(total[5], digit),
DecimalHandler.DecimalToString(total[6], digit),
DecimalHandler.DecimalToString(total[7], digit),
DecimalHandler.DecimalToString(total[8], digit),
DecimalHandler.DecimalToString(total[9], digit),
dataGridView1.SelectedRows.Count
);
}
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();
}
private void DataGridView1_SelectionChanged(object sender, EventArgs e)
{
if (dataGridView1.SelectedRows.Count < 1)
{
return;
}
int digit = GlobalUtils.DecimalDigits;
decimal amountTotal = 0;
for (int i = 0; i < dataGridView1.SelectedRows.Count; ++i)
{
var model = DgvDataSourceToList()[dataGridView1.SelectedRows[i].Index];
amountTotal += DecimalHandler.Parse(model.Amount.ToString());
}
lbTotalCount.Text = string.Format("选中{1}项 合计: 金额 {0}",
DecimalHandler.DecimalToString(amountTotal, digit),
dataGridView1.SelectedRows.Count
);
}
private void DataGridView1_SelectionChanged(object sender, EventArgs e)
{
if (dataGridView1.SelectedRows.Count < 1)
{
return;
}
int digit = GlobalUtils.DecimalDigits;
decimal songhuoliang = 0, zongjine = 0;
for (int i = 0; i < dataGridView1.SelectedRows.Count; ++i)
{
var model = DgvDataSourceToList()[dataGridView1.SelectedRows[i].Index];
songhuoliang += DecimalHandler.Parse(model.Amount.ToString());
zongjine += DecimalHandler.Parse(model.TotalSale.ToString());
}
lbTotalCount.Text = string.Format("选中{2}项 合计: 送货量 {0}(t) 销总金额 {1}",
DecimalHandler.DecimalToString(songhuoliang, digit),
DecimalHandler.DecimalToString(zongjine, digit),
dataGridView1.SelectedRows.Count);
}
/// <summary>
/// dgv设置行号
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void dataGridView1_RowsAdded(object sender, DataGridViewRowsAddedEventArgs e)
{
for (int i = 0; i < dataGridView1.Rows.Count; i++)
{
DataGridViewRow row = dataGridView1.Rows[i];
row.HeaderCell.Value = (i + 1).ToString();
//在这里控制单元格的显示
var invoiceNum = DgvDataSourceToList()[i].InvoiceNum;
if (invoiceNum != null)
{
dataGridView1["InvoiceNum", i].Value = DecimalHandler.DecimalToString(invoiceNum);
}
var receiptNum = DgvDataSourceToList()[i].ReceiptNum;
if (receiptNum != null)
{
dataGridView1["ReceiptNum", i].Value = DecimalHandler.DecimalToString(receiptNum);
}
}
}
/// <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);
}
if (dataGridView1.Columns[j].Name == "JianChiLv")
{
dataGridView1.Rows[i].Cells[j].Value = DecimalHandler.DecimalToPercent(DgvDataSourceToList()[i].JianChiLv);
}
}
}
}
/// <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);
}
}
}
//var price = DgvDataSourceToList()[i].Price;
//if (price != null)
// dataGridView1["Price", i].Value = DecimalHandler.DecimalToString(price.Value, 1);
//var fluctuation = DgvDataSourceToList()[i].Fluctuation;
//if (fluctuation != null)
// dataGridView1["Fluctuation", i].Value = DecimalHandler.DecimalToString(fluctuation.Value, 1);
}
}
private void DataGridView1_SelectionChanged(object sender, EventArgs e)
{
if (dataGridView1.SelectedRows.Count < 1)
{
return;
}
int digit = GlobalUtils.DecimalDigits;
decimal songhuoliang = 0, zongjine = 0, fukuanjine = 0, fapiaojine = 0;
for (int i = 0; i < dataGridView1.SelectedRows.Count; ++i)
{
var model = DgvDataSourceToList()[dataGridView1.SelectedRows[i].Index];
songhuoliang += DecimalHandler.Parse(model.Amount.ToString());
zongjine += DecimalHandler.Parse(model.TotalMoney.ToString());
fukuanjine += DecimalHandler.Parse(model.Money1.ToString());
fapiaojine += DecimalHandler.Parse(model.Money2.ToString());
}
//lbTotalCount.Text = string.Format("选中{4}项 合计: 送货量 {0}(t) 总金额 {1} 付款金额 {2} 发票金额 {3}",
// DecimalHandler.DecimalToString(songhuoliang, digit),
// DecimalHandler.DecimalToString(zongjine, digit),
// DecimalHandler.DecimalToString(fukuanjine, digit),
// DecimalHandler.DecimalToString(fapiaojine, digit),
// dataGridView1.SelectedRows.Count);
//lbTotalCount.Text += string.Format(" 欠款 {0} 欠票 {1}",
// DecimalHandler.DecimalToString(zongjine - fukuanjine, digit),
// DecimalHandler.DecimalToString(zongjine - fapiaojine, digit));
lbTotalCount.Text = string.Format("选中{3}项 合计: 送货量 {0}(t) 总金额 {1} 发票金额 {2}",
DecimalHandler.DecimalToString(songhuoliang, digit),
DecimalHandler.DecimalToString(zongjine, digit),
DecimalHandler.DecimalToString(fapiaojine, digit),
dataGridView1.SelectedRows.Count);
lbTotalCount.Text += string.Format(" 欠票 {0}",
DecimalHandler.DecimalToString(zongjine - fukuanjine, digit));
}
// <summary>
///
///
/// <param name="list"></param>
/// <returns></returns>
public static bool GetSailBillCount(List <Model.SaleBill> 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 duiZhangTotal = 0;
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(DateTimeFormator.DateTimeToString(list[i].EntryTime));
row.CreateCell(1).SetCellValue(DecimalHandler.DecimalToString(list[i].DuiZhang, 2));
duiZhangTotal += list[i].DuiZhang ?? 0;
row.CreateCell(2).SetCellValue(DateTimeFormator.DateTimeToString(list[i].InvoiceDate));
row.CreateCell(3).SetCellValue(DecimalHandler.DecimalToString(list[i].InvoiceNum, 2));
invoiceTotal += list[i].InvoiceNum ?? 0;
row.CreateCell(4).SetCellValue(DateTimeFormator.DateTimeToString(list[i].ReceiptDate));
row.CreateCell(5).SetCellValue(DecimalHandler.DecimalToString(list[i].ReceiptNum, 2));
receiptTotal += list[i].ReceiptNum ?? 0;
}
//统计的一行
row = sheet.CreateRow(list.Count + 1);
row.CreateCell(0).SetCellValue("合计");
row.CreateCell(1).SetCellValue(DecimalHandler.DecimalToString(duiZhangTotal, 2));
row.CreateCell(2);
row.CreateCell(3).SetCellValue(DecimalHandler.DecimalToString(invoiceTotal, 2));
row.CreateCell(4);
row.CreateCell(5).SetCellValue(DecimalHandler.DecimalToString(receiptTotal, 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;
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));
}
private void UpdateMargin(object sender, EventArgs e)
{
decimal onlinePrice = DecimalHandler.Parse(txtOnlinePrice.Text);
txtMargin.Text = DecimalHandler.DecimalToString(onlinePrice - _purchaseInfoModel.Quote ?? 0);
}
// <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));
}
// <summary>
///
///
/// <param name="list"></param>
/// <returns></returns>
public static bool GetProjectBillCount(List <Model.ProjectBill> 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("已对账");
//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); //收款
sheet.SetColumnWidth(6, 20 * 256); //收款
sheet.SetColumnWidth(7, 20 * 256); //收款
sheet.SetColumnWidth(8, 20 * 256); //收款
sheet.SetColumnWidth(9, 20 * 256); //收款
sheet.SetColumnWidth(10, 20 * 256); //收款
sheet.SetColumnWidth(11, 20 * 256); //收款
decimal[] total = new decimal[10] {
0, 0, 0, 0, 0, 0, 0, 0, 0, 0
};
//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.DecimalToString(list[i].TotalSale, 2));
row.CreateCell(3).SetCellValue(DecimalHandler.DecimalToString(list[i].InvoiceNum, 2));
row.CreateCell(4).SetCellValue(DecimalHandler.DecimalToString(list[i].FaHuoWeiKaiPiao, 2));
row.CreateCell(5).SetCellValue(DecimalHandler.DecimalToString(list[i].ReceiptNum, 2));
row.CreateCell(6).SetCellValue(DecimalHandler.DecimalToString(list[i].ZhangMianQianKuan, 2));
row.CreateCell(7).SetCellValue(DecimalHandler.DecimalToString(list[i].JinChangWeiYanShou, 2));
row.CreateCell(8).SetCellValue(DecimalHandler.DecimalToString(list[i].ZongQianKuan, 2));
row.CreateCell(9).SetCellValue(DecimalHandler.DecimalToString(list[i].WeiDuiZhang, 2));
row.CreateCell(10).SetCellValue(DecimalHandler.DecimalToString(list[i].DuiZhangWeiKaiPiao, 2));
row.CreateCell(11).SetCellValue(DecimalHandler.DecimalToString(list[i].DuiZhangNum, 2));
total[0] += list[i].TotalSale;
total[1] += list[i].InvoiceNum;
total[2] += list[i].FaHuoWeiKaiPiao;
total[3] += list[i].ReceiptNum;
total[4] += list[i].ZhangMianQianKuan;
total[5] += list[i].JinChangWeiYanShou;
total[6] += list[i].ZongQianKuan;
total[7] += list[i].WeiDuiZhang;
total[8] += list[i].DuiZhangWeiKaiPiao;
total[9] += list[i].DuiZhangNum;
}
//统计的一行
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(list[0].Corporation + "_" + list[0].Project + "_汇总.xls", "office 2003 excel|*.xls");
return(SaveFile(dstName, wkbook));
}
private void UpdateTotalMoney(object sender, EventArgs e)
{
decimal totalMoney = DecimalHandler.Parse(txtPrice.Text) * DecimalHandler.Parse(txtAmount.Text);
txtTotalMoney.Text = DecimalHandler.DecimalToString(totalMoney);
}
// <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 GetSaleBillReport(List <Model.SaleBill> 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("收款金额");
//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); //发票
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(list[i].Supplier);
row.CreateCell(3).SetCellValue(DecimalHandler.DecimalToString(list[i].Amount, digit));
row.CreateCell(4).SetCellValue(DecimalHandler.DecimalToString(list[i].DuiZhang, digit));
row.CreateCell(5).SetCellValue(DateTimeFormator.DateTimeToString(list[i].InvoiceDate));
row.CreateCell(6).SetCellValue(DecimalHandler.DecimalToString(list[i].InvoiceNum, digit));
row.CreateCell(7).SetCellValue(DateTimeFormator.DateTimeToString(list[i].ReceiptDate));
row.CreateCell(8).SetCellValue(DecimalHandler.DecimalToString(list[i].ReceiptNum, digit));
}
////统计的一行
//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));
}