private void Export() { string fileName = AppSettings.InvoiceTemplate; XLWorkbook workbook = new XLWorkbook(fileName); IXLWorksheet ws = workbook.Worksheet(SelectedCustomer.InvoiceFormat); Output(ws, 7); if (SelectedCustomer.InvoiceFormat == 2) { Output(ws, 32); } //delete other sheets for (int i = workbook.Worksheets.Count; i >= 1; i--) { if (i != SelectedCustomer.InvoiceFormat) { workbook.Worksheet(i).Delete(); } } Microsoft.Win32.SaveFileDialog dlg = new Microsoft.Win32.SaveFileDialog(); dlg.FileName = string.Format("{0}-{1}", SelectedCustomer.Name, SelectedMonth.ToString("yyyyMM")); // Default file name dlg.DefaultExt = ".xlsx"; // Default file extension // Show save file dialog box Nullable <bool> result = dlg.ShowDialog(); // Process save file dialog box results if (result == true) { // Save document string filename = dlg.FileName; workbook.SaveAs(filename); } }
public void CreateMReport() { // 取得儲存檔案名稱, 並刪除舊有資料 string filepath = GlobalConfig.MReportFileName(Store, int.Parse(Year), SelectedMonth); if (File.Exists(filepath)) { try { File.Delete(filepath); } catch (Exception e) { System.Windows.MessageBox.Show($"刪除原有檔案失敗,請確認檔案是否開啟中.( { e.Message } )"); return; } } // 匯入所選年月發票資料 List <Invoice> monthInvoices = GlobalConfig.Connection.GetAllInvoice(Store, int.Parse(Year), SelectedMonth); // 產生月報資料 XLWorkbook wb = new XLWorkbook(); var ws = wb.AddWorksheet("Sheet1"); ws.Style.Font.FontName = "微軟正黑體"; ws.Style.Alignment.Vertical = XLAlignmentVerticalValues.Center; ws.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center; ws.Rows().AdjustToContents(); ws.Style.Font.SetFontSize(10); ws.Row(4).Height = 5; ws.Row(8).Height = 5; using (var row = ws.Range("B2:M2")) { row.Value = "營業人使用二聯式收銀機統一發票明細表"; row.Style.Font.SetFontSize(16); row.Merge(); } using (var row = ws.Range("B3:M3")) { row.SetValue <string>($"中華民國{ (int.Parse(Year) - 1911).ToString() }年{ SelectedMonth.ToString() }月"); row.Style.Font.SetFontSize(16); row.Merge(); } using (var block = ws.Range("B5:E7")) { using (var row = ws.Range("B5:D5")) { row.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Left; row.Style.Border.OutsideBorder = XLBorderStyleValues.Thin; row.Value = "統一編號"; row.Merge(); } using (var row = ws.Range("B6:D6")) { row.Style.Border.OutsideBorder = XLBorderStyleValues.Thin; row.Value = "營業人名稱"; row.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Left; row.Merge(); } using (var row = ws.Range("B7:D7")) { row.Style.Border.OutsideBorder = XLBorderStyleValues.Thin; row.Value = "稅籍編號"; row.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Left; row.Merge(); } using (var row = ws.Range("E5:E7")) { row.Style.Border.OutsideBorder = XLBorderStyleValues.Thin; } block.Style.Border.OutsideBorder = XLBorderStyleValues.Thin; block.Style.Border.InsideBorder = XLBorderStyleValues.Thin; // 統一編號 ws.Range("E5").SetValue(Store.UniformNumber); // 營業人名稱 ws.Range("E6").SetValue(Store.Name); ws.Range("E6").Style.Font.FontSize = 6; // 稅籍編號 ws.Range("E7").SetValue(Store.TaxId); } using (var row = ws.Range("F7:I7")) { row.Value = "所屬年月及發票字軌請營業人填註"; row.Merge(); } using (var block = ws.Range("J5:J7")) { using (var row = ws.Range("J6:J7")) { row.Merge(); // 收銀機編號 row.SetValue(Store.CashierNumber); row.Style.Font.FontSize = 9; } ws.Cell("J5").Value = "收銀機編號"; ws.Cell("J5").Style.Border.OutsideBorder = XLBorderStyleValues.Thin; block.Style.Border.OutsideBorder = XLBorderStyleValues.Thin; } using (var block = ws.Range("L5:M7")) { using (var col = ws.Range("L5:L7")) { col.Value = "發票字軌"; col.Style.Alignment.WrapText = true; col.Style.Border.RightBorder = XLBorderStyleValues.Thin; col.Merge(); } using (var col = ws.Range("M5:M7")) { col.SetValue(Track); col.Style.Font.Bold = true; col.Merge(); } block.Style.Border.OutsideBorder = XLBorderStyleValues.Thin; block.Style.Border.InsideBorder = XLBorderStyleValues.Thin; } using (var block = ws.Range("B9:C10")) { block.Value = "開立日期"; block.Style.Alignment.WrapText = true; block.Style.Border.OutsideBorder = XLBorderStyleValues.Thin; block.Merge(); } using (var block = ws.Range("D9:E10")) { block.Value = "開立發票起訖號碼"; block.Style.Alignment.WrapText = true; block.Style.Border.OutsideBorder = XLBorderStyleValues.Thin; block.Merge(); } using (var block = ws.Range("F9:G9")) { block.Value = "應 稅"; block.Style.Alignment.WrapText = true; block.Style.Border.OutsideBorder = XLBorderStyleValues.Thin; block.Merge(); } using (var block = ws.Range("F10:G10")) { block.Value = "發 票 總 金 額"; block.Style.Alignment.WrapText = true; block.Style.Border.OutsideBorder = XLBorderStyleValues.Thin; block.Merge(); } using (var block = ws.Range("H9:H10")) { block.Value = "免 稅銷 售 額"; block.Style.Alignment.WrapText = true; block.Style.Border.OutsideBorder = XLBorderStyleValues.Thin; block.Merge(); } using (var block = ws.Range("I9:M10")) { block.Value = "誤開作廢發票號碼"; block.Style.Alignment.WrapText = true; block.Style.Border.OutsideBorder = XLBorderStyleValues.Thin; block.Merge(); } int rowIndex = 11; for (var i = 1; i <= DateTime.DaysInMonth(int.Parse(Year), SelectedMonth); i++) { List <Invoice> dayInovices = monthInvoices.Where(x => x.CreateTime.Day == i).ToList(); using (var block = ws.Range(rowIndex, 2, rowIndex + 1, 3)) { block.Value = i; block.Style.Border.OutsideBorder = XLBorderStyleValues.Thin; block.Merge(); } using (var block = ws.Range(rowIndex, 4, rowIndex + 1, 5)) { List <int> invoiceNumber = new List <int>(); foreach (Invoice invoice in dayInovices) { invoiceNumber.Add(int.Parse(invoice.Number)); } var result = String.Join(",", invoiceNumber .Distinct() .OrderBy(x => x) .GroupAdjacentBy((x, y) => x + 1 == y) .Select(g => new int[] { g.First(), g.Last() }.Distinct()) .Select(g => String.Join("-", g))); string[] cols = result.Split(','); using (var row = ws.Range(rowIndex, 4, rowIndex, 5)) { // 開立發票起訖號碼 // if (invoice[i].Count() > 0) if (cols.Length > 0) { // row.SetValue<string>(invoice[i].First() + " - " + invoice[i].Last()); row.SetValue(cols[0]); } row.Style.Font.FontSize = 9; row.Merge(); } using (var row = ws.Range(rowIndex + 1, 4, rowIndex + 1, 5)) { // 開立發票起訖號碼 if (cols.Length > 1) { row.SetValue(cols[1]); } row.Style.Font.FontSize = 9; row.Merge(); } block.Style.Border.OutsideBorder = XLBorderStyleValues.Thin; } using (var block = ws.Range(rowIndex, 6, rowIndex + 1, 7)) { if (dayInovices.Count > 0) { // 發票總金額 block.SetValue(dayInovices.Where(x => !x.InValid).Select(x => x.Amount).Sum().ToString("###,##0")); } block.Style.Border.OutsideBorder = XLBorderStyleValues.Thin; block.Merge(); } using (var block = ws.Range(rowIndex, 8, rowIndex + 1, 8)) { block.Style.Border.OutsideBorder = XLBorderStyleValues.Thin; block.Merge(); } using (var block = ws.Range(rowIndex, 9, rowIndex + 1, 13)) { // 作廢發票號碼 if (dayInovices.Where(x => x.InValid == true).Count() > 0) { string invalid = String.Join(",", dayInovices.Where(x => x.InValid).Select(x => x.Number).ToList()); block.SetValue(invalid); } block.Style.Alignment.WrapText = true; block.Style.Border.OutsideBorder = XLBorderStyleValues.Thin; block.Merge(); } rowIndex += 2; } using (var block = ws.Range(rowIndex, 2, rowIndex + 10, 2)) { block.Value = "申報 單位"; block.Style.Alignment.WrapText = true; block.Style.Font.SetFontSize(5); block.Style.Border.OutsideBorder = XLBorderStyleValues.Thin; block.Merge(); } using (var block = ws.Range(rowIndex, 3, rowIndex + 10, 5)) { using (var row = ws.Range(rowIndex, 3, rowIndex + 8, 5)) { row.Merge(); } using (var row = ws.Range(rowIndex + 9, 3, rowIndex + 9, 5)) { row.Value = "(請蓋用統一發票專用章)"; row.Merge(); } using (var row = ws.Range(rowIndex + 10, 3, rowIndex + 10, 5)) { row.Value = "申報日期: 年 月 日"; row.Merge(); } block.Style.Border.OutsideBorder = XLBorderStyleValues.Thin; } using (var row = ws.Range(rowIndex, 6, rowIndex, 7)) { row.Value = "作 廢 份 數"; row.Style.Border.OutsideBorder = XLBorderStyleValues.Thin; row.Merge(); } using (var row = ws.Range(rowIndex, 8, rowIndex, 13)) { // 作廢份數 row.SetValue(monthInvoices.Where(x => x.InValid == true).Count().ToString("###,##0")); row.Style.Border.OutsideBorder = XLBorderStyleValues.Thin; row.Merge(); } using (var row = ws.Range(rowIndex + 1, 6, rowIndex + 1, 7)) { row.Value = "空 白 發 票 起 訖 號 碼"; row.Style.Border.OutsideBorder = XLBorderStyleValues.Thin; row.Merge(); } using (var row = ws.Range(rowIndex + 1, 8, rowIndex + 1, 13)) { row.Value = " 號 號共 份"; row.Style.Border.OutsideBorder = XLBorderStyleValues.Thin; row.Merge(); } using (var row = ws.Range(rowIndex + 2, 6, rowIndex + 3, 13)) { row.Value = "銷 售 額 及 稅 額 計 算"; row.Style.Border.OutsideBorder = XLBorderStyleValues.Thin; row.Merge(); } using (var block = ws.Range(rowIndex + 4, 6, rowIndex + 6, 6)) { block.Value = " 區分項目"; block.Style.Alignment.WrapText = true; block.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Left; block.Merge(); block.Style.Border.DiagonalDown = true; block.Style.Border.OutsideBorder = XLBorderStyleValues.Thin; block.Style.Border.DiagonalBorder = XLBorderStyleValues.Thin; } using (var block = ws.Range(rowIndex + 4, 7, rowIndex + 4, 11)) { block.Value = "應 稅"; block.Style.Border.OutsideBorder = XLBorderStyleValues.Thin; block.Merge(); } using (var block = ws.Range(rowIndex + 5, 7, rowIndex + 6, 7)) { ws.Cell(rowIndex + 5, 7).Value = "發票總金額"; ws.Cell(rowIndex + 6, 7).Value = "(1)"; ws.Cell(rowIndex + 6, 7).Style.Font.SetFontSize(6); block.Style.Border.OutsideBorder = XLBorderStyleValues.Thin; } using (var block = ws.Range(rowIndex + 5, 8, rowIndex + 6, 9)) { using (var row = ws.Range(rowIndex + 5, 8, rowIndex + 5, 9)) { row.Value = "銷售額"; row.Merge(); } using (var row = ws.Range(rowIndex + 6, 8, rowIndex + 6, 9)) { row.Value = "(2) = (1) x (100/105)"; row.Style.Font.SetFontSize(6); row.Merge(); } block.Style.Border.OutsideBorder = XLBorderStyleValues.Thin; } using (var block = ws.Range(rowIndex + 5, 10, rowIndex + 6, 11)) { using (var row = ws.Range(rowIndex + 5, 10, rowIndex + 5, 11)) { row.Value = "稅額"; row.Merge(); } using (var row = ws.Range(rowIndex + 6, 10, rowIndex + 6, 11)) { row.Value = "(3) = (2) x 5%"; row.Style.Font.SetFontSize(6); row.Merge(); } block.Style.Border.OutsideBorder = XLBorderStyleValues.Thin; } using (var block = ws.Range(rowIndex + 4, 12, rowIndex + 6, 13)) { block.Value = "免 稅銷 售 額"; block.Style.Alignment.WrapText = true; block.Style.Border.OutsideBorder = XLBorderStyleValues.Thin; block.Merge(); } using (var block = ws.Range(rowIndex + 7, 6, rowIndex + 8, 6)) { block.Value = "本 表合 計"; block.Style.Alignment.WrapText = true; block.Style.Border.OutsideBorder = XLBorderStyleValues.Thin; block.Merge(); } using (var block = ws.Range(rowIndex + 7, 7, rowIndex + 8, 7)) { // 本表合計 block.Style.Alignment.WrapText = true; block.Style.Border.OutsideBorder = XLBorderStyleValues.Thin; block.Merge(); } using (var block = ws.Range(rowIndex + 7, 8, rowIndex + 8, 9)) { block.Style.Alignment.WrapText = true; block.Style.Border.OutsideBorder = XLBorderStyleValues.Thin; block.Style.Border.DiagonalUp = true; block.Style.Border.DiagonalBorder = XLBorderStyleValues.Thin; block.Merge(); } using (var block = ws.Range(rowIndex + 7, 10, rowIndex + 8, 11)) { block.Style.Alignment.WrapText = true; block.Style.Border.OutsideBorder = XLBorderStyleValues.Thin; block.Style.Border.DiagonalUp = true; block.Style.Border.DiagonalBorder = XLBorderStyleValues.Thin; block.Merge(); } using (var block = ws.Range(rowIndex + 7, 12, rowIndex + 8, 13)) { block.Style.Alignment.WrapText = true; block.Style.Border.OutsideBorder = XLBorderStyleValues.Thin; block.Merge(); } using (var block = ws.Range(rowIndex + 9, 6, rowIndex + 10, 6)) { block.Value = "本期(月) 總 計"; block.Style.Alignment.WrapText = true; block.Style.Border.OutsideBorder = XLBorderStyleValues.Thin; block.Merge(); } // 本月發票總金額 int total = monthInvoices.Where(x => x.InValid == false).Select(x => x.Amount).Sum(); double taxFree = 0; using (var block = ws.Range(rowIndex + 9, 7, rowIndex + 10, 7)) { // 本期(月)總計 block.SetValue(total.ToString("###,##0")); block.Style.Alignment.WrapText = true; block.Style.Border.OutsideBorder = XLBorderStyleValues.Thin; block.Merge(); } using (var block = ws.Range(rowIndex + 9, 8, rowIndex + 10, 9)) { taxFree = total / 1.05; // 銷售額 block.SetValue(Math.Round(taxFree).ToString("###,##0")); block.Style.Alignment.WrapText = true; block.Style.Border.OutsideBorder = XLBorderStyleValues.Thin; block.Merge(); } using (var block = ws.Range(rowIndex + 9, 10, rowIndex + 10, 11)) { double tax = taxFree * 0.05; // 稅額 block.SetValue(Math.Round(tax).ToString("###,##0")); block.Style.Alignment.WrapText = true; block.Style.Border.OutsideBorder = XLBorderStyleValues.Thin; block.Merge(); } using (var block = ws.Range(rowIndex + 9, 12, rowIndex + 10, 13)) { block.Style.Alignment.WrapText = true; block.Style.Border.OutsideBorder = XLBorderStyleValues.Thin; block.Merge(); } ws.Column(1).Width = 1; ws.Column(2).Width = 2; ws.Column(3).Width = 2; ws.Column(4).Width = 6; ws.Column(5).Width = 12; ws.Column(6).Width = 8; ws.Column(7).Width = 12; ws.Column(8).Width = 10; ws.Column(9).Width = 2; ws.Column(10).Width = 10; ws.Column(11).Width = 2; ws.Column(12).Width = 4; ws.Column(13).Width = 4; wb.SaveAs(filepath); System.Windows.MessageBox.Show($"月報資料已產生至 { filepath } ."); }