private void EditForm_Load(object sender, EventArgs e)
{
IList <Model.CompanyLevel> levelList = this.companyLevelManager.Select();
foreach (Model.CompanyLevel level in levelList)
{
this.comboBoxEditCompanyLevel.Properties.Items.Add(level.CompanyLevelId + @"-" + level.CompanyLevelName);
}
this.bindingSourceCustom.DataSource = customerManager.Select();
//IList<Model.Setting> list = new BL.SettingManager().SelectTagOrderDefault("FP");
IList <Model.Company> list = new BL.CompanyManager().Select();
foreach (var item in list)
{
this.comboBoxEditFP.Properties.Items.Add(item.CompanyName);
}
IList <Model.CustomInspectionRule> list2 = new BL.CustomInspectionRuleManager().Select();
foreach (var item in list2)
{
this.cobCheckedStandard.Properties.Items.Add(item.CustomInspectionRuleName);
}
}
//导出Excel-新版
private void ExportExcelNew(List <Book.Model.AcInvoiceXOBill> headers, List <Book.Model.AcInvoiceXOBillDetail> details)
{
Type objClassType = null;
objClassType = Type.GetTypeFromProgID("Excel.Application");
if (objClassType == null)
{
MessageBox.Show("本機沒有安裝Excel", "提示！", MessageBoxButtons.OK);
return;
}
try
{
SaveFileDialog sfd = new SaveFileDialog();
sfd.AddExtension = true;
sfd.DefaultExt = ".xls";
sfd.Filter = "Excel|*.xls";
if (sfd.ShowDialog() == DialogResult.OK)
{
Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbook workbook = excel.Application.Workbooks.Add(true);
#region Invoice
Microsoft.Office.Interop.Excel.Worksheet sheet = (Microsoft.Office.Interop.Excel.Worksheet)excel.Worksheets[1];
sheet.Name = "發票";
sheet.Cells.ColumnWidth = 15;
//sheet.get_Range(sheet.Cells[1, 11], sheet.Cells[1, 11]).ColumnWidth = 15;
sheet.get_Range(sheet.Cells[1, 1], sheet.Cells[headers.Count + 1, 16]).EntireRow.AutoFit();
sheet.get_Range(sheet.Cells[1, 1], sheet.Cells[headers.Count + 1, 16]).NumberFormat = "@";
#region SetFormat
sheet.Cells[1, 1] = "發票號碼";
sheet.Cells[1, 2] = "發票日期";
sheet.Cells[1, 3] = "發票類別";
sheet.Cells[1, 4] = "賣方統一編號";
sheet.Cells[1, 5] = "賣方名稱";
sheet.Cells[1, 6] = "買方統一編號";
sheet.Cells[1, 7] = "買方名稱";
sheet.Cells[1, 8] = "課稅別";
sheet.Cells[1, 9] = "稅率";
sheet.Cells[1, 10] = "銷售額合計";
sheet.Cells[1, 11] = "營業稅";
sheet.Cells[1, 12] = "總計";
sheet.Cells[1, 13] = "賣方負責人姓名";
sheet.Cells[1, 14] = "賣方地址";
sheet.Cells[1, 15] = "賣方電話號碼";
sheet.Cells[1, 16] = "賣方電子郵件地址";
sheet.Cells[1, 17] = "賣方傳真號碼";
sheet.Cells[1, 18] = "賣方客戶編號";
sheet.Cells[1, 19] = "買方負責人姓名";
sheet.Cells[1, 20] = "買方地址";
sheet.Cells[1, 21] = "買方電話號碼";
sheet.Cells[1, 22] = "買方電子郵件地址";
sheet.Cells[1, 23] = "買方傳真號碼";
sheet.Cells[1, 24] = "買方客戶編號";
sheet.Cells[1, 25] = "開立者名稱";
sheet.Cells[1, 26] = "作廢者名稱";
sheet.Cells[1, 27] = "開立時間";
sheet.Cells[1, 28] = "開立確認時間";
sheet.Cells[1, 29] = "作廢時間";
sheet.Cells[1, 30] = "作廢確認時間";
sheet.Cells[1, 31] = "退回時間";
sheet.Cells[1, 32] = "退回確認時間";
sheet.Cells[1, 33] = "寄送日期";
sheet.Cells[1, 34] = "作廢原因";
sheet.Cells[1, 35] = "營業人角色註記";
sheet.Cells[1, 36] = "通關方式註記";
sheet.Cells[1, 37] = "買受人註記欄";
sheet.Cells[1, 38] = "相關號碼";
sheet.Cells[1, 39] = "扣抵金額";
sheet.Cells[1, 40] = "原幣金額";
sheet.Cells[1, 41] = "匯率";
sheet.Cells[1, 42] = "幣別";
sheet.Cells[1, 43] = "稅捐稽徵處名稱";
sheet.Cells[1, 44] = "核准日";
sheet.Cells[1, 45] = "核准文";
sheet.Cells[1, 46] = "核准號";
sheet.Cells[1, 47] = "彙開註記";
sheet.Cells[1, 48] = "寄送失敗原因";
sheet.Cells[1, 49] = "發票狀態";
sheet.Cells[1, 50] = "最後狀態";
sheet.Cells[1, 51] = "最後異動時間";
sheet.Cells[1, 52] = "專案作廢核准文號";
sheet.Cells[1, 53] = "捐贈註記";
sheet.Cells[1, 54] = "銷售類別";
sheet.Cells[1, 55] = "備註";
sheet.Cells[1, 56] = "附件";
sheet.Cells[1, 57] = "沖帳別";
sheet.Cells[1, 58] = "退回原因";
sheet.get_Range(sheet.Cells[1, 1], sheet.Cells[1, 58]).Interior.ColorIndex = 6; //黄色
#endregion
//賣方信息
Model.Company seller = new BL.CompanyManager().SelectIsDefaultCompany();
//買方信息
Model.Customer buyer = null;
object[,] headerRange = new object[headers.Count, 58];
int invoiceRow = 0;
foreach (var item in headers)
{
buyer = item.CustomerShouPiao == null ? new Model.Customer() : item.CustomerShouPiao;
headerRange[invoiceRow, 0] = item.Id; //發票號碼
headerRange[invoiceRow, 1] = item.AcInvoiceXOBillDate.HasValue ? item.AcInvoiceXOBillDate.Value.ToString("yyyy/MM/dd") : ""; //發票日期
if (!string.IsNullOrEmpty(item.InvoiceType))
{
headerRange[invoiceRow, 2] = EditForm.GetFPInvoiceType().Select("Key='" + item.InvoiceType + "'")[0]["Value"]; //發票類別
}
headerRange[invoiceRow, 3] = seller.NoNnite; //賣方統一編號
headerRange[invoiceRow, 4] = seller.CompanyName; //賣方名稱
headerRange[invoiceRow, 5] = buyer.CustomerNumber; //買方統一編號
headerRange[invoiceRow, 6] = buyer.CustomerFullName; //買方名稱
headerRange[invoiceRow, 7] = EditForm.GetTaxRateType(item.TaxRateType.HasValue ? item.TaxRateType.Value : 1); //課稅別
headerRange[invoiceRow, 8] = item.TaxRate.HasValue ? item.TaxRate.Value.ToString("f2") : ""; //稅率
headerRange[invoiceRow, 9] = item.HeJiMoney.HasValue ? item.HeJiMoney.Value.ToString("f2") : ""; //銷售額合計
headerRange[invoiceRow, 10] = item.TaxRateMoney.HasValue ? item.TaxRateMoney.Value.ToString("f2") : ""; //營業稅
headerRange[invoiceRow, 11] = item.ZongMoney.HasValue ? item.ZongMoney.Value.ToString("f2") : ""; //總計
headerRange[invoiceRow, 12] = seller.CompanyPrinciple; //賣方負責人姓名
headerRange[invoiceRow, 13] = seller.CompanyAddress1; //賣方地址
headerRange[invoiceRow, 14] = seller.CompanyPhone; //賣方電話號碼
headerRange[invoiceRow, 15] = seller.CompanyEMail; //賣方電子郵件地址
headerRange[invoiceRow, 16] = seller.CompanyFax; //賣方傳真號碼
headerRange[invoiceRow, 17] = ""; //賣方客戶編號
headerRange[invoiceRow, 18] = buyer.CustomerManager; //買方負責人姓名
headerRange[invoiceRow, 19] = buyer.CustomerAddress; //買方地址
headerRange[invoiceRow, 20] = buyer.CustomerPhone; //買方電話號碼
headerRange[invoiceRow, 21] = buyer.CustomerEMail; //買方電子郵件地址
headerRange[invoiceRow, 22] = buyer.CustomerFax; //買方傳真號碼
headerRange[invoiceRow, 23] = buyer.Id; //買方客戶編號
headerRange[invoiceRow, 24] = item.Employee.EmployeeName; //開立者名稱
headerRange[invoiceRow, 25] = ""; //作廢者名稱
headerRange[invoiceRow, 26] = item.InsertTime.Value.ToString("yyyy/MM/dd"); //開立時間
headerRange[invoiceRow, 27] = ""; //開立確認時間
headerRange[invoiceRow, 28] = ""; //作廢時間
headerRange[invoiceRow, 29] = ""; //作廢確認時間
headerRange[invoiceRow, 30] = ""; //退回時間
headerRange[invoiceRow, 31] = ""; //退回確認時間
headerRange[invoiceRow, 32] = ""; //寄送日期
headerRange[invoiceRow, 33] = ""; //作廢原因
headerRange[invoiceRow, 34] = ""; //營業人角色註記
if (!string.IsNullOrEmpty(item.ClearanceType))
{
headerRange[invoiceRow, 35] = EditForm.GetClearanceType().Select("Key='" + item.ClearanceType + "'")[0]["Value"]; //通關方式註記
}
headerRange[invoiceRow, 36] = ""; //買受人註記欄
headerRange[invoiceRow, 37] = item.RelatedNumbers; //相關號碼
headerRange[invoiceRow, 38] = ""; //扣抵金額
headerRange[invoiceRow, 39] = ""; //原幣金額
headerRange[invoiceRow, 40] = ""; //匯率
headerRange[invoiceRow, 41] = ""; //幣別
headerRange[invoiceRow, 42] = ""; //稅捐稽徵處名稱
headerRange[invoiceRow, 43] = ""; //核准日
headerRange[invoiceRow, 44] = ""; //核准文
headerRange[invoiceRow, 45] = ""; //核准號
headerRange[invoiceRow, 46] = item.HuikaiNote == true ? "*" : ""; //彙開註記
headerRange[invoiceRow, 47] = ""; //寄送失敗原因
headerRange[invoiceRow, 48] = ""; //發票狀態
headerRange[invoiceRow, 49] = ""; //最後狀態
headerRange[invoiceRow, 50] = item.UpdateTime.Value.ToString("yyyy/MM/dd"); //最後異動時間
headerRange[invoiceRow, 51] = ""; //專案作廢核准文號
headerRange[invoiceRow, 52] = ""; //捐贈註記
if (!string.IsNullOrEmpty(item.SalesType))
{
headerRange[invoiceRow, 53] = EditForm.GetSalesType().Select("Key='" + item.SalesType + "'")[0]["Value"]; //銷售類別
}
headerRange[invoiceRow, 54] = item.AcInvoiceXOBillDesc; //備註
headerRange[invoiceRow, 55] = ""; //附件
headerRange[invoiceRow, 56] = ""; //沖帳別
headerRange[invoiceRow, 57] = ""; //退回原因
invoiceRow++;
}
//先将数据存入二维数组，再统一插入Excel，比单独一个一个单元格插入快很多
if (headers.Count > 0)
{
sheet.get_Range(sheet.Cells[2, 1], sheet.Cells[1 + headers.Count, 58]).Value2 = headerRange;
}
#endregion
#region Invoice_Details
excel.Worksheets.Add(Missing.Value, sheet, Missing.Value, Missing.Value);
Microsoft.Office.Interop.Excel.Worksheet sheet2 = ((Microsoft.Office.Interop.Excel.Worksheet)excel.Worksheets[excel.Worksheets.Count]);
sheet2.Name = "明細";
sheet2.Cells.ColumnWidth = 12;
sheet2.get_Range(sheet2.Cells[1, 4], sheet2.Cells[1, 5]).ColumnWidth = 30;
sheet2.get_Range(sheet2.Cells[1, 1], sheet2.Cells[headers.Count + 1, 11]).EntireRow.AutoFit();
sheet2.get_Range(sheet2.Cells[1, 1], sheet2.Cells[headers.Count + 1, 11]).NumberFormatLocal = "@";
#region SetFormat
sheet2.Cells[1, 1] = "發票號碼";
sheet2.Cells[1, 2] = "發票日期";
sheet2.Cells[1, 3] = "序號";
sheet2.Cells[1, 4] = "品名編號";
sheet2.Cells[1, 5] = "發票品名";
sheet2.Cells[1, 6] = "相關號碼";
sheet2.Cells[1, 7] = "數量";
sheet2.Cells[1, 8] = "單位";
sheet2.Cells[1, 9] = "單價";
sheet2.Cells[1, 10] = "小計";
#endregion
object[,] detailRange = new object[details.Count, 10];
int detailRow = 0;
foreach (var item in details)
{
detailRange[detailRow, 0] = item.AcInvoiceXOBill.Id;
detailRange[detailRow, 1] = item.AcInvoiceXOBill.AcInvoiceXOBillDate.Value.ToString("yyy/MM/dd");
detailRange[detailRow, 2] = item.No;
detailRange[detailRow, 3] = item.Product.Id;
detailRange[detailRow, 4] = item.Product.ProductName;
detailRange[detailRow, 5] = item.AcInvoiceXOBill.RelatedNumbers;
detailRange[detailRow, 6] = item.InvoiceXODetaiInQuantity.HasValue ? item.InvoiceXODetaiInQuantity.Value.ToString("f2") : "";
detailRange[detailRow, 7] = item.InvoiceProductUnit;
detailRange[detailRow, 8] = item.InvoiceXODetailPrice.HasValue ? item.InvoiceXODetailPrice.Value.ToString("f2") : "";
detailRange[detailRow, 9] = item.InvoiceXODetailMoney.HasValue ? item.InvoiceXODetailMoney.Value.ToString("f2") : "";
detailRow++;
}
//先将数据存入二维数组，再统一插入Excel，比单独一个一个单元格插入快很多
if (details.Count > 0)
{
sheet2.get_Range(sheet2.Cells[2, 1], sheet2.Cells[1 + details.Count, 10]).Value2 = detailRange;
}
#endregion
excel.Visible = false;
workbook.SaveAs(sfd.FileName, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
excel.Quit();
MessageBox.Show("導出成功！", "提示");
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
