/// <summary> /// Closes the writer and saves the written data to the stream. Automatically called /// when disposed. /// </summary> public void Close() { if (_book != null) { // Set the column widths if we are doing auto sizing PerformColumnResize(); // Now save the Excel file to the output stream _book.SaveAs(_stream); // Clean up and dispose of everything _book?.Dispose(); _defaultFont?.Dispose(); _sheet = null; _book = null; } }
private void BT_IMPORT_RESULT(object sender, RoutedEventArgs e) { gcollect.Clear(); List <string> duplicate = new List <string>(); string folderName = System.Environment.CurrentDirectory + @"\資料匯總"; try { foreach (var finame in System.IO.Directory.GetFileSystemEntries(folderName)) { if (System.IO.Path.GetExtension(finame) != ".xlsx") { continue; } using (var wb = new XLWorkbook()) { var ws = wb.Worksheet(1); if (!DateTime.TryParse(ws.Cell(1, 5).GetString(), CultureInfo.InvariantCulture, DateTimeStyles.None, out DateTime time)) { continue; } if (time.Year != DateTime.Now.AddMonths(-1).Year || time.Month != DateTime.Now.AddMonths(-1).Month) { continue; } for (int i = 2; i < 500; i++) { if (string.IsNullOrEmpty(ws.Cell(i, 1).GetString())) { break; } if (string.IsNullOrEmpty(ws.Cell(i, 3).GetString())) { continue; } MElements data = new MElements { Element = ws.Cell(i, 3).GetString().Trim(), ElementData = ws.Cell(i, 5).GetString().Trim(), Eledate = time }; if (gcollect.Count > 0) { bool dup = false; foreach (var x in gcollect) { if (data.Element == x.Element) { duplicate.Add(x.Element); dup = true; break; } } if (!dup) { gcollect.Add(data); } } else { gcollect.Add(data); } if (gduplicate.ContainsKey(data.Element)) { var glists = gduplicate.Where(o => o.Key == data.Element).FirstOrDefault().Value; foreach (var x in glists) { if (gcollect.Find(o => o.Element == x.ToString()) == null && gdata.Find(o => o.MeasureID == x.ToString()) != null) { MElements dupdata = new MElements { Element = x.ToString(), ElementData = data.ElementData, Eledate = data.Eledate }; gcollect.Add(dupdata); } } } } wb.Dispose(); } } } catch (Exception ex) { MessageBox.Show(ex.Message); } MessageBox.Show("匯入成功 : " + gcollect.Count()); if (duplicate.Count > 0) { TxtBox1.Text += Environment.NewLine + "重複資料清單 : " + string.Join(",", duplicate) + Environment.NewLine; } if (gcollect.Count > 0) { TxtBox1.Text += Environment.NewLine + string.Format("指標收回數量 : {0}/{1} ({2}%)", gcollect.Count, gdata.Count, gcollect.Count * 100 / gdata.Count) + Environment.NewLine; gcollect.Sort((x, y) => { return(x.Element.CompareTo(y.Element)); }); string fpath = Environment.CurrentDirectory + @"\要素備份"; if (!Directory.Exists(fpath)) { Directory.CreateDirectory(fpath); } string fname = @"\指標收集存檔(月份)" + DateTime.Now.AddMonths(-1).ToString("yyyy-MM") + ".xlsx"; string fname2 = @"\指標收集存檔總檔.xlsx"; try { using (var wb = new XLWorkbook()) { var ws = wb.Worksheets.Add("工作表1"); ws.Style.Font.FontSize = 12; ws.Style.Font.FontName = "微軟正黑體"; ws.Columns(1, 2).Width = 15; ws.Cell(1, 1).Value = "指標要素"; ws.Cell(1, 2).Value = DateTime.Now.AddMonths(-1).ToString("yyyy/MM"); ws.Cell(1, 2).Style.DateFormat.Format = "yyyy/MM"; ws.Cell(1, 2).Style.Fill.BackgroundColor = XLColor.LightBlue; ws.Cell(1, 2).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center; ws.Column(2).AdjustToContents(); for (int i = 0; i < gcollect.Count; i++) { ws.Cell(i + 2, 1).Value = gcollect[i].Element; ws.Cell(i + 2, 2).Value = gcollect[i].ElementData; } wb.SaveAs(fpath + fname); } } catch (Exception ex) { MessageBox.Show(ex.ToString()); } if (!System.IO.File.Exists(fpath + fname)) { return; } if (!System.IO.File.Exists(fpath + fname2)) { using (var wb = new XLWorkbook(fpath + fname)) { wb.SaveAs(fpath + fname2); } } else { try { using (var wb = new XLWorkbook(fpath + fname2)) { var ws = wb.Worksheet(1); if (!DateTime.TryParse(ws.Cell(1, 2).GetString(), CultureInfo.InvariantCulture, DateTimeStyles.None, out DateTime dts)) { wb.Dispose(); return; } if ((dts.Month == DateTime.Now.Month && dts.Year == DateTime.Now.Year) || dts >= DateTime.Now) { return; } if (dts.Month != DateTime.Now.AddMonths(-1).Month&& dts.Year != DateTime.Now.AddMonths(-1).Year) { ws.Column(1).InsertColumnsAfter(1); ws.Cell(1, 2).Value = DateTime.Now.AddMonths(-1).ToString("yyyy/MM"); } foreach (var x in gcollect) { int wsrows = ws.LastRowUsed().RowNumber(); var same = ws.RangeUsed().Rows(r => r.Cell(1).GetString() == x.Element).FirstOrDefault(); if (same != null) { same.Cell(2).Value = x.ElementData; } else { ws.Cell(wsrows + 1, 1).Value = x.Element; ws.Cell(wsrows + 1, 2).Value = x.ElementData; } /* * bool oldele = false; * for (int j = 0; j < 500; j++) * { * if (string.IsNullOrEmpty(ws.Cell(j + 2, 1).GetString())) * break; * if (ws.Cell(j + 2, 1).GetString() == x.Element) * { * ws.Cell(j + 2, 2).Value = x.ElementData; * oldele = true; * break; * } * } * if (!oldele) * { * ws.Cell(wsrows + 1, 1).Value = x.Element; * ws.Cell(wsrows + 1, 2).Value = x.ElementData; * } */ } wb.SaveAs(fpath + fname2); } } catch (Exception ex) { MessageBox.Show(ex.ToString()); } } } }
public void Dispose() { _wb?.Dispose(); }
/// ----------------------------------------------------------------------------- /// <summary> /// DataTableをもとにxlsxファイルを作成しPDF化</summary> /// <param name="dtSetCd_B_Input"> /// 得意先元帳確認の印刷データテーブル</param> /// ----------------------------------------------------------------------------- public string dbToPdf(DataTable dtSetCd_B_Input, List <string> lstPrintData) { string strWorkPath = System.Configuration.ConfigurationManager.AppSettings["workpath"]; string strDateTime = DateTime.Now.ToString("yyyyMMddHHmmss"); string strNow = DateTime.Now.ToString("yyyy/MM/dd HH:mm:ss"); //合計値の確保用 decimal decUriKin = 0; //売上金額 decimal decNyukin = 0; //入金金額 //得意先コードの表示とページ変えのチェック用 string strTokuiCd = ""; //得意先名の表示用 string strTokuiName = ""; try { CreatePdf pdf = new CreatePdf(); // ワークブックのデフォルトフォント、フォントサイズの指定 XLWorkbook.DefaultStyle.Font.FontName = "MS ゴシック"; XLWorkbook.DefaultStyle.Font.FontSize = 7.5; // excelのインスタンス生成 XLWorkbook workbook = new XLWorkbook(XLEventTracking.Disabled); IXLWorksheet worksheet = workbook.Worksheets.Add("Header"); IXLWorksheet headersheet = worksheet; // ヘッダーシート IXLWorksheet currentsheet = worksheet; // 処理中シート // Linqで必要なデータをselect var outDataAll = dtSetCd_B_Input.AsEnumerable() .Select(dat => new { TokuiCd = dat["仕入先コード"], //[0]ヘッダー表示のみ使用 TokuiName = dat["仕入先名"], //[1]ヘッダー表示のみ使用 TokuiYMD = dat["年月日"], //[2] Tokuikbn = dat["取引区分名"], //[3] TokuiShohinName = dat["商品名"], //[4] TokuiSu = dat["数量"], //[5] TokuiTanka = dat["仕入単価"], //[6] TokuiUrikin = dat["仕入金額"], //[7] TokuiNyukin = dat["支払金額"], //[8] TokuiSashiZan = dat["差引残高"], //[9] TokuiBiko = dat["備考"], //[10] }).ToList(); // リストをデータテーブルに変換 DataTable dtChkList = pdf.ConvertToDataTable(outDataAll); int maxRowCnt = dtChkList.Rows.Count + 1; int maxColCnt = dtChkList.Columns.Count; int pageCnt = 0; // ページ(シート枚数)カウント int rowCnt = 1; // datatable処理行カウント int xlsRowCnt = 5; // Excel出力行カウント(開始は出力行) int maxPage = 0; // 最大ページ数 // 最大ページ数の行数カウント用 int rowCntMaxPage = 1; int xlsRowCntMaxPage = 5; // 最大ページ数の取得 foreach (DataRow drTokuiCheak in dtChkList.Rows) { // 2ページ目以降 if (rowCntMaxPage > 1) { // 次の得意先が違う場合にシート作成 if (strTokuiCd != drTokuiCheak[0].ToString()) { strTokuiCd = drTokuiCheak[0].ToString(); //合計分の行を追加 maxPage++; xlsRowCntMaxPage = 5; } } // 1ページ目のシート作成 if (rowCntMaxPage == 1) { maxPage++; strTokuiCd = drTokuiCheak[0].ToString(); } //44行に達した場合 if (xlsRowCntMaxPage == 44) { maxPage++; xlsRowCntMaxPage = 4; } //maxPage++; rowCntMaxPage++; xlsRowCntMaxPage++; } // ClosedXMLで1行ずつExcelに出力 foreach (DataRow drTokuiCheak in dtChkList.Rows) { //2ページ目以降 if (rowCnt > 1) { //次の得意先が違う場合にシート作成 if (strTokuiCd != drTokuiCheak[0].ToString()) { //マージ currentsheet.Range("A" + xlsRowCnt, "E" + xlsRowCnt).Merge(); currentsheet.Range("H" + xlsRowCnt, "I" + xlsRowCnt).Merge(); currentsheet.Row(xlsRowCnt).Height = 10; currentsheet.Cell(xlsRowCnt, 1).Value = "■■■ 合 計 ■■■"; currentsheet.Cell(xlsRowCnt, 1).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center; currentsheet.Cell(xlsRowCnt, 6).Value = decUriKin.ToString(); //売上金額 currentsheet.Cell(xlsRowCnt, 7).Value = decNyukin.ToString(); //入金金額 //最終行、各項目のカンマ処理と文字寄せ for (int intCnt = 6; intCnt < 8; intCnt++) { currentsheet.Cell(xlsRowCnt, intCnt).Style.NumberFormat.Format = "#,0"; currentsheet.Cell(xlsRowCnt, intCnt).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Right; } // 1行分のセルの周囲に罫線を引く currentsheet.Range(xlsRowCnt, 1, xlsRowCnt, 16).Style .Border.SetTopBorder(XLBorderStyleValues.Thin) .Border.SetBottomBorder(XLBorderStyleValues.Thin) .Border.SetLeftBorder(XLBorderStyleValues.Thin) .Border.SetRightBorder(XLBorderStyleValues.Thin); //初期化 decUriKin = 0; decNyukin = 0; //得意先コードの確保 strTokuiCd = drTokuiCheak[0].ToString(); strTokuiName = drTokuiCheak[1].ToString(); pageCnt++; // ヘッダー出力(表ヘッダー上) headersheet.Cell("A3").Value = strTokuiCd.Trim() + " " + strTokuiName.Trim(); //取引先名と取引先コード xlsRowCnt = 5; //ヘッダーシートのコピー、ヘッダー部の指定 pdf.sheetCopy(ref workbook, ref headersheet, ref currentsheet, pageCnt, maxPage, strNow); } } //1ページ目のシート作成 if (rowCnt == 1) { //得意先コードの確保 strTokuiCd = drTokuiCheak[0].ToString(); strTokuiName = drTokuiCheak[1].ToString(); pageCnt++; // タイトル出力(中央揃え、セル結合) IXLCell titleCell = headersheet.Cell("A1"); titleCell.Value = "仕 入 先 元 帳"; titleCell.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center; titleCell.Style.Font.FontSize = 16; headersheet.Range("A1", "I1").Merge(); // ヘッダー出力(表ヘッダー) headersheet.Cell("A4").Value = "年月日"; headersheet.Cell("B4").Value = "区分"; headersheet.Cell("C4").Value = "商 品 名"; headersheet.Cell("D4").Value = "数 量"; headersheet.Cell("E4").Value = "単 価"; headersheet.Cell("F4").Value = "仕入金額"; headersheet.Cell("G4").Value = "支払金額"; headersheet.Cell("H4").Value = "差引残高"; headersheet.Cell("I4").Value = "備考"; //行高さの指定 headersheet.Row(4).Height = 10; // 列幅の指定 headersheet.Column(1).Width = 10; //年月日 headersheet.Column(2).Width = 9; //区分 headersheet.Column(3).Width = 80; //商品名 headersheet.Column(4).Width = 11.5; //数量 headersheet.Column(5).Width = 11.5; //単価 headersheet.Column(6).Width = 11.5; //売上金額 headersheet.Column(7).Width = 11.5; //入金金額 headersheet.Column(8).Width = 11.5; //差引残高 headersheet.Column(9).Width = 30; //備考 //ヘッダー文字位置の指定 headersheet.Column(1).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center; //年月日 headersheet.Column(2).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center; //区分 headersheet.Column(3).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center; //商品名 headersheet.Column(4).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center; //数量 headersheet.Column(5).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center; //単価 headersheet.Column(6).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center; //売上金額 headersheet.Column(7).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center; //入金金額 headersheet.Column(8).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center; //差引残高 headersheet.Column(9).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center; //備考 // セルの周囲に罫線を引く headersheet.Range("A4", "I4").Style .Border.SetTopBorder(XLBorderStyleValues.Thin) .Border.SetBottomBorder(XLBorderStyleValues.Thin) .Border.SetLeftBorder(XLBorderStyleValues.Thin) .Border.SetRightBorder(XLBorderStyleValues.Thin); //背景を灰色にする headersheet.Range("A4", "I4").Style.Fill.BackgroundColor = XLColor.LightGray; // 印刷体裁(A4横、印刷範囲) headersheet.PageSetup.PaperSize = XLPaperSize.A4Paper; headersheet.PageSetup.PageOrientation = XLPageOrientation.Landscape; // ヘッダー部の指定(番号) headersheet.PageSetup.Header.Left.AddText("(№34)"); //マージ currentsheet.Range("A3", "C3").Merge(); currentsheet.Range("D3", "E3").Merge(); currentsheet.Range("F3", "G3").Merge(); // ヘッダー出力(表ヘッダー上) headersheet.Cell("A3").Value = strTokuiCd + " " + strTokuiName; //取引先名と取引先コード headersheet.Cell("F3").Value = "(外税 請求単位)"; //(外税 請求単位)の記入 headersheet.Cell("I3").Value = "対象期間:" + lstPrintData[2] + " ~ " + lstPrintData[3]; //対象期間 // ヘッダー列 headersheet.Range("A3", "C3").Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Left; headersheet.Range("D3", "F3").Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Left; headersheet.Range("I3").Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Right; //ヘッダーシートのコピー、ヘッダー部の指定 pdf.sheetCopy(ref workbook, ref headersheet, ref currentsheet, pageCnt, maxPage, strNow); } // 1セルずつデータ出力 for (int colCnt = 1; colCnt <= maxColCnt; colCnt++) { string str = drTokuiCheak[colCnt - 1].ToString(); //行の高さ指定 currentsheet.Row(xlsRowCnt).Height = 10; //数量、単価の場合 if (colCnt == 6 || colCnt == 7) { //空白以外の場合 if (str != "") { //小数点以下第二位まで表示 currentsheet.Cell(xlsRowCnt, colCnt - 2).Style.NumberFormat.Format = "#,0.00"; } currentsheet.Cell(xlsRowCnt, colCnt - 2).Value = str; currentsheet.Cell(xlsRowCnt, colCnt - 2).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Right; //0の場合 if (currentsheet.Cell(xlsRowCnt, colCnt - 2).Value.ToString() == "0") { //空にする currentsheet.Cell(xlsRowCnt, colCnt - 2).Value = ""; } } //売上金額、入金金額、割引残高の場合 else if (colCnt >= 8 && colCnt <= 10) { //空でない場合 if (str != "") { //余計な小数点以下数値を取り除く str = Math.Floor(decimal.Parse(str)).ToString(); //空白以外の場合 if (str != "0") { //カンマつけ currentsheet.Cell(xlsRowCnt, colCnt - 2).Style.NumberFormat.Format = "#,0"; } //0の場合は空白 else if (str == "0") { str = ""; } } currentsheet.Cell(xlsRowCnt, colCnt - 2).Value = str; currentsheet.Cell(xlsRowCnt, colCnt - 2).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Right; //0の場合 if (currentsheet.Cell(xlsRowCnt, colCnt - 2).ToString() == "0") { //空にする currentsheet.Cell(xlsRowCnt, colCnt - 2).Value = ""; } } //得意先コード、得意先名はスルー else if (colCnt == 1 || colCnt == 2) { //スルー } //備考 else if (colCnt == 11) { currentsheet.Cell(xlsRowCnt, colCnt - 2).Value = str; currentsheet.Cell(xlsRowCnt, colCnt - 2).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Left; } //年月日、区分、商品名の場合 else { currentsheet.Cell(xlsRowCnt, colCnt - 2).Value = str; currentsheet.Cell(xlsRowCnt, colCnt - 2).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Left; } } // 1行分のセルの周囲に罫線を引く currentsheet.Range(xlsRowCnt, 1, xlsRowCnt, 9).Style .Border.SetTopBorder(XLBorderStyleValues.Thin) .Border.SetBottomBorder(XLBorderStyleValues.Thin) .Border.SetLeftBorder(XLBorderStyleValues.Thin) .Border.SetRightBorder(XLBorderStyleValues.Thin); // 44行毎(ヘッダーを除いた行数)にシート作成 if (xlsRowCnt == 44) { pageCnt++; xlsRowCnt = 4; // ヘッダーシートのコピー、ヘッダー部の指定 pdf.sheetCopy(ref workbook, ref headersheet, ref currentsheet, pageCnt, maxPage, strNow); } rowCnt++; xlsRowCnt++; //各合計値を入れる decUriKin = decUriKin + decimal.Parse(drTokuiCheak[7].ToString()); decNyukin = decNyukin + decimal.Parse(drTokuiCheak[8].ToString()); //最終行の場合 if (rowCnt > dtChkList.Rows.Count) { //マージ currentsheet.Range("A" + xlsRowCnt, "E" + xlsRowCnt).Merge(); currentsheet.Row(xlsRowCnt).Height = 10; currentsheet.Cell(xlsRowCnt, 1).Value = "◆◆◆ 合 計 ◆◆◆"; currentsheet.Cell(xlsRowCnt, 1).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center; currentsheet.Cell(xlsRowCnt, 6).Value = decUriKin.ToString(); //売上金額 currentsheet.Cell(xlsRowCnt, 7).Value = decNyukin.ToString(); //入金金額 //最終行、各項目のカンマ処理と文字寄せ for (int intCnt = 6; intCnt < 8; intCnt++) { currentsheet.Cell(xlsRowCnt, intCnt).Style.NumberFormat.Format = "#,0"; currentsheet.Cell(xlsRowCnt, intCnt).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Right; } // 1行分のセルの周囲に罫線を引く currentsheet.Range(xlsRowCnt, 1, xlsRowCnt, 16).Style .Border.SetTopBorder(XLBorderStyleValues.Thin) .Border.SetBottomBorder(XLBorderStyleValues.Thin) .Border.SetLeftBorder(XLBorderStyleValues.Thin) .Border.SetRightBorder(XLBorderStyleValues.Thin); //初期化 decUriKin = 0; decNyukin = 0; } } // ヘッダーシート削除 headersheet.Delete(); // workbookを保存 string strOutXlsFile = strWorkPath + strDateTime + ".xlsx"; workbook.SaveAs(strOutXlsFile); // workbookを解放 workbook.Dispose(); // PDF化の処理 return(pdf.createPdf(strOutXlsFile, strDateTime, 1)); } catch (Exception ex) { throw (ex); } finally { // Workフォルダの全ファイルを取得 string[] files = System.IO.Directory.GetFiles(strWorkPath, "*", System.IO.SearchOption.AllDirectories); // Workフォルダ内のファイル削除 foreach (string filepath in files) { //File.Delete(filepath); } } }
/// ----------------------------------------------------------------------------- /// <summary> /// DataTableをもとにxlsxファイルを作成しPDF化</summary> /// <param name="dtSyohingunUriageSiire"> /// 仕入推移表のデータテーブル</param> /// ----------------------------------------------------------------------------- public string dbToPdf(DataTable dtSyohingunUriageSiire) { string strWorkPath = System.Configuration.ConfigurationManager.AppSettings["workpath"]; string strDateTime = DateTime.Now.ToString("yyyyMMddHHmmss"); try { string strHeader = ""; string strNow = DateTime.Now.ToString("yyyy/MM/dd HH:mm:ss"); string strSpace = " "; string strComputerName = System.Windows.Forms.SystemInformation.ComputerName; // excelのインスタンス生成 XLWorkbook workbook = new XLWorkbook(XLEventTracking.Disabled); // ワークブックのデフォルトフォント、フォントサイズの指定 XLWorkbook.DefaultStyle.Font.FontName = "MS 明朝"; XLWorkbook.DefaultStyle.Font.FontSize = 9; IXLWorksheet worksheet = workbook.Worksheets.Add("Header"); IXLWorksheet headersheet = worksheet; // ヘッダーシート IXLWorksheet currentsheet = worksheet; // 処理中シート //Linqで必要なデータをselect var outDataAll = dtSyohingunUriageSiire.AsEnumerable() .Select(dat => new { daibunruiCd = dat["大分類コード"], daibunruiName = dat["大分類名"], chubunruiName = dat["中分類名"], kamikiUriagegaku = (decimal)dat["上期売上額"], kamikiSiiregaku = (decimal)dat["上期仕入額"], simokiUriagegaku = (decimal)dat["下期売上額"], simokiSiiregaku = (decimal)dat["下期仕入額"], goukeiUriagegaku = (decimal)dat["合計売上額"], goukeiSiiregaku = (decimal)dat["合計仕入額"], }).ToList(); // linqで売上額(上期、下期、合計)、仕入額(上期、下期、合計)を算出 decimal[] decKingaku = new decimal[13]; decKingaku[0] = outDataAll.Select(gokei => gokei.kamikiUriagegaku).Sum(); decKingaku[1] = outDataAll.Select(gokei => gokei.kamikiSiiregaku).Sum(); decKingaku[2] = outDataAll.Select(gokei => gokei.simokiUriagegaku).Sum(); decKingaku[3] = outDataAll.Select(gokei => gokei.simokiSiiregaku).Sum(); decKingaku[4] = outDataAll.Select(gokei => gokei.goukeiUriagegaku).Sum(); decKingaku[5] = outDataAll.Select(gokei => gokei.goukeiSiiregaku).Sum(); // 大分類計(小計) var daibunruiGoukei = from tbl in dtSyohingunUriageSiire.AsEnumerable() group tbl by tbl.Field <string>("大分類コード") into g select new { section = g.Key, count = g.Count(), kamikiUriagegaku = g.Sum(p => p.Field <decimal>("上期売上額")), kamikiSiiregaku = g.Sum(p => p.Field <decimal>("上期仕入額")), simokiUriagegaku = g.Sum(p => p.Field <decimal>("下期売上額")), simokiSiiregaku = g.Sum(p => p.Field <decimal>("下期仕入額")), goukeiUriagegaku = g.Sum(p => p.Field <decimal>("合計売上額")), goukeiSiiregaku = g.Sum(p => p.Field <decimal>("合計仕入額")), }; // 大分類計(小計)の売上額(上期、下期、合計)、仕入額(上期、下期、合計)を算出 decimal[,] decKingakuDaibunrui = new decimal[daibunruiGoukei.Count(), 13]; for (int cnt = 0; cnt < daibunruiGoukei.Count(); cnt++) { decKingakuDaibunrui[cnt, 0] = daibunruiGoukei.ElementAt(cnt).kamikiUriagegaku; decKingakuDaibunrui[cnt, 1] = daibunruiGoukei.ElementAt(cnt).kamikiSiiregaku; decKingakuDaibunrui[cnt, 2] = daibunruiGoukei.ElementAt(cnt).simokiUriagegaku; decKingakuDaibunrui[cnt, 3] = daibunruiGoukei.ElementAt(cnt).simokiSiiregaku; decKingakuDaibunrui[cnt, 4] = daibunruiGoukei.ElementAt(cnt).goukeiUriagegaku; decKingakuDaibunrui[cnt, 5] = daibunruiGoukei.ElementAt(cnt).goukeiSiiregaku; } // リストをデータテーブルに変換 DataTable dtChkList = this.ConvertToDataTable(outDataAll); int maxRowCnt = dtChkList.Rows.Count; int maxColCnt = dtChkList.Columns.Count; int pageCnt = 0; // ページ(シート枚数)カウント int rowCnt = 1; // datatable処理行カウント int xlsRowCnt = 4; // Excel出力行カウント(開始は出力行) int maxPage = 0; // 最大ページ数 // ページ数計算 maxRowCnt += daibunruiGoukei.Count() + 1; double page = 1.0 * maxRowCnt / 35; double decimalpart = page % 1; if (decimalpart != 0) { // 小数点以下が0でない場合、+1 maxPage = (int)Math.Floor(page) + 1; } else { maxPage = (int)page; } int daibunruiCnt = 0; int daibunruiRowCnt = 0; string strSiireCode = ""; // ClosedXMLで1行ずつExcelに出力 foreach (DataRow drSyohingunUriageSiire in dtChkList.Rows) { // 1ページ目のシート作成 if (rowCnt == 1) { pageCnt++; // タイトル出力(中央揃え、セル結合) IXLCell titleCell = headersheet.Cell("A1"); titleCell.Value = "商品群別売上仕入管理表"; titleCell.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center; titleCell.Style.Font.FontSize = 14; headersheet.Range("A1", "H1").Merge(); // ヘッダー出力(3行目のセル) headersheet.Cell("A3").Value = "大分類名"; headersheet.Cell("B3").Value = "中分類名"; headersheet.Cell("C3").Value = "上期売上高"; headersheet.Cell("D3").Value = "上期仕入高"; headersheet.Cell("E3").Value = "下期売上高"; headersheet.Cell("F3").Value = "下期仕入高"; headersheet.Cell("G3").Value = "合計売上高"; headersheet.Cell("H3").Value = "合計仕入高"; // ヘッダー列 headersheet.Range("A3", "H3").Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center; // セルの周囲に罫線を引く headersheet.Range("A3", "H3").Style .Border.SetTopBorder(XLBorderStyleValues.Thin) .Border.SetBottomBorder(XLBorderStyleValues.Thin) .Border.SetLeftBorder(XLBorderStyleValues.Thin) .Border.SetRightBorder(XLBorderStyleValues.Thin); // 列幅の指定 headersheet.Column(1).Width = 20; headersheet.Column(2).Width = 20; headersheet.Column(3).Width = 20; headersheet.Column(4).Width = 20; headersheet.Column(5).Width = 20; headersheet.Column(6).Width = 20; headersheet.Column(7).Width = 20; headersheet.Column(8).Width = 20; // 印刷体裁(B4横、印刷範囲) headersheet.PageSetup.PaperSize = XLPaperSize.B4Paper; headersheet.PageSetup.PageOrientation = XLPageOrientation.Landscape; // ヘッダー部の指定(番号) headersheet.PageSetup.Header.Left.AddText("(№65)"); // ヘッダーシートからコピー headersheet.CopyTo("Page1"); currentsheet = workbook.Worksheet(2); // ヘッダー部の指定(コンピュータ名、日付、ページ数を出力) strHeader = "( " + strComputerName + " )" + strSpace + strNow + strSpace + pageCnt.ToString() + " / " + maxPage.ToString(); currentsheet.PageSetup.Header.Right.AddText(strHeader); } // 大分類名出力 if (daibunruiRowCnt == 0) { currentsheet.Cell(xlsRowCnt, 1).Value = drSyohingunUriageSiire[1]; } // 1セルずつデータ出力 for (int colCnt = 2; colCnt < maxColCnt; colCnt++) { string str = drSyohingunUriageSiire[colCnt].ToString(); // 金額セルの処理 if (colCnt >= 3 && colCnt <= 8) { // 3桁毎に","を挿入する str = string.Format("{0:#,0}", decimal.Parse(str)); IXLCell kingakuCell = currentsheet.Cell(xlsRowCnt, colCnt); kingakuCell.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Right; kingakuCell.Value = str; } currentsheet.Cell(xlsRowCnt, colCnt).Value = str; } // 1行分のセルの周囲に罫線を引く currentsheet.Range(xlsRowCnt, 1, xlsRowCnt, 8).Style .Border.SetTopBorder(XLBorderStyleValues.Thin) .Border.SetBottomBorder(XLBorderStyleValues.Thin) .Border.SetLeftBorder(XLBorderStyleValues.Thin) .Border.SetRightBorder(XLBorderStyleValues.Thin); // 35行毎(ヘッダーを除いた行数)にシート作成 if (xlsRowCnt == 38) { pageCnt++; if (pageCnt <= maxPage) { xlsRowCnt = 3; // コンピュータ名、日付、ページ数を取得 strHeader = "( " + strComputerName + " )" + strSpace + strNow + strSpace + pageCnt.ToString() + " / " + maxPage.ToString(); // ヘッダーシートのコピー、ヘッダー部の指定 sheetCopy(ref workbook, ref headersheet, ref currentsheet, pageCnt, strHeader); } } // 大分類計(小計)を出力 daibunruiRowCnt++; if (daibunruiGoukei.ElementAt(daibunruiCnt).count == daibunruiRowCnt) { xlsRowCnt++; // セル結合、中央揃え IXLCell tantocell = currentsheet.Cell(xlsRowCnt, 1); currentsheet.Range(xlsRowCnt, 1, xlsRowCnt, 2).Merge(); tantocell.Value = "◆ 小 計 ◆"; tantocell.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Left; // 金額セルの処理(3桁毎に","を挿入する) for (int cnt = 0; cnt < 6; cnt++) { IXLCell kingakuCell = currentsheet.Cell(xlsRowCnt, cnt + 3); kingakuCell.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Right; kingakuCell.Value = string.Format("{0:#,0}", decKingakuDaibunrui[daibunruiCnt, cnt]); } // 1行分のセルの周囲に罫線を引く currentsheet.Range(xlsRowCnt, 1, xlsRowCnt, 8).Style .Border.SetTopBorder(XLBorderStyleValues.Thin) .Border.SetBottomBorder(XLBorderStyleValues.Thin) .Border.SetLeftBorder(XLBorderStyleValues.Thin) .Border.SetRightBorder(XLBorderStyleValues.Thin); daibunruiCnt++; daibunruiRowCnt = 0; } // 35行毎(ヘッダーを除いた行数)にシート作成 if (xlsRowCnt == 38) { pageCnt++; if (pageCnt <= maxPage) { xlsRowCnt = 3; // コンピュータ名、日付、ページ数を取得 strHeader = "( " + strComputerName + " )" + strSpace + strNow + strSpace + pageCnt.ToString() + " / " + maxPage.ToString(); // ヘッダーシートのコピー、ヘッダー部の指定 sheetCopy(ref workbook, ref headersheet, ref currentsheet, pageCnt, strHeader); } } rowCnt++; xlsRowCnt++; } // 最終行を出力した後、合計行を出力 if (dtChkList.Rows.Count > 0) { // セル結合、中央揃え IXLCell sumcell = currentsheet.Cell(xlsRowCnt, 1); currentsheet.Range(xlsRowCnt, 1, xlsRowCnt, 2).Merge(); sumcell.Value = "◆ 合 計 ◆"; sumcell.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Left; // 金額セルの処理(3桁毎に","を挿入する) for (int cnt = 0; cnt < 6; cnt++) { IXLCell kingakuCell = currentsheet.Cell(xlsRowCnt, cnt + 3); kingakuCell.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Right; kingakuCell.Value = string.Format("{0:#,0}", decKingaku[cnt]); } // 1行分のセルの周囲に罫線を引く currentsheet.Range(xlsRowCnt, 1, xlsRowCnt, 8).Style .Border.SetTopBorder(XLBorderStyleValues.Thin) .Border.SetBottomBorder(XLBorderStyleValues.Thin) .Border.SetLeftBorder(XLBorderStyleValues.Thin) .Border.SetRightBorder(XLBorderStyleValues.Thin); } // ヘッダーシート削除 headersheet.Delete(); // workbookを保存 string strOutXlsFile = strWorkPath + strDateTime + ".xlsx"; workbook.SaveAs(strOutXlsFile); // workbookを解放 workbook.Dispose(); // PDF化の処理 return(createPdf(strOutXlsFile, strDateTime)); } catch (Exception ex) { new CommonException(ex); throw ex; } finally { // Workフォルダの全ファイルを取得 string[] files = System.IO.Directory.GetFiles(strWorkPath, "*", System.IO.SearchOption.AllDirectories); // Workフォルダ内のファイル削除 foreach (string filepath in files) { //File.Delete(filepath); } } }
public ActionResult Download(ReportPOVendorViewModel model) { try { XLWorkbook xlWorkBook = new XLWorkbook(); var xlWorkSheet = xlWorkBook.Worksheets.Add("Report Vendor");// xlWorkSheet; //xlWorkBook = xlApp.Workbooks.Add(Type.Missing); //xlWorkSheet = (Worksheet)xlWorkBook.Worksheets.get_Item(1); xlWorkSheet.Cell(1, 1).Value = "OFFICER"; xlWorkSheet.Cell(1, 2).Value = "PLANT"; xlWorkSheet.Cell(1, 3).Value = "WARNA"; xlWorkSheet.Cell(1, 4).Value = "TYPEUNIT"; xlWorkSheet.Cell(1, 5).Value = "PONUMBER"; xlWorkSheet.Cell(1, 6).Value = "TGLPO"; xlWorkSheet.Cell(1, 7).Value = "POSTATUS"; xlWorkSheet.Cell(1, 8).Value = "NAMAPEMILIKVENDOR"; xlWorkSheet.Cell(1, 9).Value = "TGLPROMISEDELIVERYBPKB"; xlWorkSheet.Cell(1, 10).Value = "AGINGOVERDUEOUTSTANDINGUNITBPKB"; xlWorkSheet.Cell(1, 11).Value = "AGINGOUTSTANDINGUNITBPKB"; xlWorkSheet.Cell(1, 12).Value = "AGINGOVERDUEOUTSTANDINGUNIT"; xlWorkSheet.Cell(1, 13).Value = "AGINGOUTSTANDINGUNIT"; xlWorkSheet.Cell(1, 14).Value = "REMARKSDETAILPROBLEM"; xlWorkSheet.Cell(1, 15).Value = "DETAILPROBLEM"; xlWorkSheet.Cell(1, 16).Value = "STATUSBPKB"; xlWorkSheet.Cell(1, 17).Value = "POSISIBPKB"; xlWorkSheet.Cell(1, 18).Value = "KETBPKB"; xlWorkSheet.Cell(1, 19).Value = "ACTUALRECEIVEDBPKBCABANG"; xlWorkSheet.Cell(1, 20).Value = "NOBPKB"; xlWorkSheet.Cell(1, 21).Value = "NAMAPEMILIK"; xlWorkSheet.Cell(1, 22).Value = "INVOICESTATUS"; xlWorkSheet.Cell(1, 23).Value = "WARNA"; xlWorkSheet.Cell(1, 24).Value = "TGLPEMBAYARAN"; xlWorkSheet.Cell(1, 25).Value = "ESTIMATEDPAYMENTDATE"; xlWorkSheet.Cell(1, 26).Value = "ACTUALRECEIVEDINVOICE"; xlWorkSheet.Cell(1, 27).Value = "NOAP"; xlWorkSheet.Cell(1, 28).Value = "NOFAKTURPAJAK"; xlWorkSheet.Cell(1, 29).Value = "HARGAPPNUNITVENDOR"; xlWorkSheet.Cell(1, 30).Value = "HARGADPPVENDOR"; xlWorkSheet.Cell(1, 31).Value = "HARGABBNVENDOR"; xlWorkSheet.Cell(1, 32).Value = "HARGABBNUNIT"; xlWorkSheet.Cell(1, 33).Value = "HARGADPP"; xlWorkSheet.Cell(1, 34).Value = "NETPRICE"; xlWorkSheet.Cell(1, 35).Value = "DISC"; xlWorkSheet.Cell(1, 36).Value = "OTRPRICE"; xlWorkSheet.Cell(1, 37).Value = "INVOICENUMBER"; xlWorkSheet.Cell(1, 38).Value = "INVOICEDATE"; xlWorkSheet.Cell(1, 39).Value = "TGLSELESAIKAROSERI"; xlWorkSheet.Cell(1, 40).Value = "TGLMASUKKAROSERI"; xlWorkSheet.Cell(1, 41).Value = "KETKAROSERI"; xlWorkSheet.Cell(1, 42).Value = "TGLSTNKVENDOR"; xlWorkSheet.Cell(1, 43).Value = "NOENGINEVENDOR"; xlWorkSheet.Cell(1, 44).Value = "NOCHASISVENDOR"; xlWorkSheet.Cell(1, 45).Value = "NOPOLISIVENDOR"; xlWorkSheet.Cell(1, 46).Value = "TGLSTNK"; xlWorkSheet.Cell(1, 47).Value = "NOENGINE"; xlWorkSheet.Cell(1, 48).Value = "NOCHASIS"; xlWorkSheet.Cell(1, 49).Value = "NOPOLISI"; xlWorkSheet.Cell(1, 50).Value = "VARIANVENDOR"; xlWorkSheet.Cell(1, 51).Value = "MERKVENDOR"; xlWorkSheet.Cell(1, 52).Value = "VARIAN"; xlWorkSheet.Cell(1, 53).Value = "MERK"; xlWorkSheet.Cell(1, 54).Value = "YEAR"; xlWorkSheet.Cell(1, 55).Value = "STATUSBELI"; xlWorkSheet.Cell(1, 56).Value = "BBN"; IReportPOVendorService svc = new ReportPOVendorService(); var Data = svc.GetAll(); int Row = 2; if (Data.Count > 0) { for (int i = 0; i < Data.Count; i++) { xlWorkSheet.Cell(Row + i, 1).Value = Data[i].Officer; xlWorkSheet.Cell(Row + i, 2).Value = Data[i].Plant; xlWorkSheet.Cell(Row + i, 3).Value = Data[i].Warna; xlWorkSheet.Cell(Row + i, 4).Value = Data[i].TypeUnit; xlWorkSheet.Cell(Row + i, 5).Value = Data[i].PONumber; xlWorkSheet.Cell(Row + i, 6).Value = Data[i].TglPO; xlWorkSheet.Cell(Row + i, 7).Value = Data[i].POStatus; xlWorkSheet.Cell(Row + i, 8).Value = Data[i].NamaPemilikVendor; xlWorkSheet.Cell(Row + i, 9).Value = Data[i].TglPromiseDeliveryBPKB; xlWorkSheet.Cell(Row + i, 10).Value = Data[i].AgingOverdueOutstandingUnitBPKB; xlWorkSheet.Cell(Row + i, 11).Value = Data[i].AgingOutstandingUnitBPKB; xlWorkSheet.Cell(Row + i, 12).Value = Data[i].AgingOverdueOutstandongUnit; xlWorkSheet.Cell(Row + i, 13).Value = Data[i].AgingOverdueOutstandongUnit; xlWorkSheet.Cell(Row + i, 14).Value = Data[i].RemarksDetailProblem; xlWorkSheet.Cell(Row + i, 15).Value = Data[i].DetailProblem; xlWorkSheet.Cell(Row + i, 16).Value = Data[i].StatusBPKB; xlWorkSheet.Cell(Row + i, 17).Value = Data[i].PosisiBPKB; xlWorkSheet.Cell(Row + i, 18).Value = Data[i].KeteranganBPKB; xlWorkSheet.Cell(Row + i, 19).Value = Data[i].ActualReceivedBPKBCabang; xlWorkSheet.Cell(Row + i, 20).Value = Data[i].NoBPKB; xlWorkSheet.Cell(Row + i, 21).Value = Data[i].NamaPemilik; xlWorkSheet.Cell(Row + i, 22).Value = Data[i].InvoiceStatus; xlWorkSheet.Cell(Row + i, 23).Value = Data[i].Warna; xlWorkSheet.Cell(Row + i, 24).Value = Data[i].TglPembayaran; xlWorkSheet.Cell(Row + i, 25).Value = Data[i].EstimatedPaymentDate; xlWorkSheet.Cell(Row + i, 26).Value = Data[i].ActualReceivedInvoice; xlWorkSheet.Cell(Row + i, 27).Value = Data[i].NoAP; xlWorkSheet.Cell(Row + i, 28).Value = Data[i].NoFakturPajak; xlWorkSheet.Cell(Row + i, 29).Value = Data[i].HargaPPNUnitVendor; xlWorkSheet.Cell(Row + i, 30).Value = Data[i].HargaDPPVendor; xlWorkSheet.Cell(Row + i, 31).Value = Data[i].HargaBBNVendor; xlWorkSheet.Cell(Row + i, 32).Value = Data[i].HargaPPNUnit; xlWorkSheet.Cell(Row + i, 33).Value = Data[i].HargaDPP; xlWorkSheet.Cell(Row + i, 34).Value = Data[i].NetPrice; xlWorkSheet.Cell(Row + i, 35).Value = Data[i].Disc; xlWorkSheet.Cell(Row + i, 36).Value = Data[i].OTRPrice; xlWorkSheet.Cell(Row + i, 37).Value = Data[i].InvoiceNumber; xlWorkSheet.Cell(Row + i, 38).Value = Data[i].InvoiceDate; xlWorkSheet.Cell(Row + i, 39).Value = Data[i].TglSelesaiKaroseri; xlWorkSheet.Cell(Row + i, 40).Value = Data[i].TglMasukKaroseri; xlWorkSheet.Cell(Row + i, 41).Value = Data[i].KeteranganKaroseri; xlWorkSheet.Cell(Row + i, 42).Value = Data[i].TglSTNKVendor; xlWorkSheet.Cell(Row + i, 43).Value = Data[i].NoEngineVendor; xlWorkSheet.Cell(Row + i, 44).Value = Data[i].NoChasisVendor; xlWorkSheet.Cell(Row + i, 45).Value = Data[i].NoPolisiVendor; xlWorkSheet.Cell(Row + i, 46).Value = Data[i].TglSTNK; xlWorkSheet.Cell(Row + i, 47).Value = Data[i].NoEngine; xlWorkSheet.Cell(Row + i, 48).Value = Data[i].NoChasis; xlWorkSheet.Cell(Row + i, 49).Value = Data[i].NoPolisi; xlWorkSheet.Cell(Row + i, 50).Value = Data[i].VarianVendor; xlWorkSheet.Cell(Row + i, 51).Value = Data[i].MerkVendor; xlWorkSheet.Cell(Row + i, 52).Value = Data[i].Varian; xlWorkSheet.Cell(Row + i, 53).Value = Data[i].Merk; xlWorkSheet.Cell(Row + i, 54).Value = Data[i].YEAR; xlWorkSheet.Cell(Row + i, 55).Value = Data[i].StatusBeli; xlWorkSheet.Cell(Row + i, 56).Value = Data[i].BBN; } xlWorkSheet.Columns().AdjustToContents(); var path = Server.MapPath("..") + "\\Report-Vendor.xlsx"; xlWorkBook.SaveAs(path); xlWorkBook.Dispose(); return(File(path, "application/vnd.ms-excel", "Report-Vendor.xlsx")); } return(RedirectToAction("Index")); } catch (Exception) { throw; } }
public bool Import(string workSheetName, ref int rowCount) { var result = false; try { var ws = _workbook.Worksheet(workSheetName); // Look for the first row used var firstRowUsed = ws.FirstRowUsed(); // Narrow down the row so that it only includes the used part var supplierRow = firstRowUsed.RowUsed(); // First possible address of the company table: var firstPossibleAddress = ws.Row(supplierRow.RowNumber()).FirstCell().Address; // Last possible address of the company table: var lastPossibleAddress = ws.LastCellUsed().Address; // Get a range with the remainder of the worksheet data (the range used) var customerRange = ws.Range(firstPossibleAddress, lastPossibleAddress).RangeUsed(); // Treat the range as a table (to be able to use the column names) var customerTable = customerRange.AsTable(); var listOfCustomer = new List <Customer>(); listOfCustomer = customerTable.DataRange.Rows().Select(row => new Customer { nama_customer = row.Field("NAMA").GetString(), Provinsi = new Provinsi { nama_provinsi = row.Field("PROVINSI").GetString() }, Kabupaten = new Kabupaten { nama_kabupaten = row.Field("KABUPATEN/KOTA").GetString() }, Kecamatan = new Kecamatan { nama_kecamatan = row.Field("KECAMATAN").GetString() }, alamat = row.Field("ALAMAT").GetString(), kode_pos = row.Field("KODE POS").GetString(), kontak = row.Field("KONTAK").GetString(), telepon = row.Field("TELEPON").GetString(), diskon = row.Field("DISKON RESELLER").GetString().Length == 0 ? 0 : Convert.ToDouble(row.Field("DISKON RESELLER").GetString()), plafon_piutang = row.Field("PLAFON PIUTANG").GetString().Length == 0 ? 0 : Convert.ToDouble(row.Field("PLAFON PIUTANG").GetString()) }).ToList(); if (listOfCustomer.Count == 1 && listOfCustomer[0].nama_customer.Length == 0) { rowCount = 0; return(false); } rowCount = listOfCustomer.Count; using (IDapperContext context = new DapperContext()) { _unitOfWork = new UnitOfWork(context, _log); foreach (var customer in listOfCustomer) { if (customer.nama_customer.Length > 0) { if (customer.nama_customer.Length > 50) { customer.nama_customer = customer.nama_customer.Substring(0, 50); } if (customer.alamat.Length > 250) { customer.alamat = customer.alamat.Substring(0, 250); } if (customer.kontak.Length > 50) { customer.kontak = customer.kontak.Substring(0, 50); } if (customer.telepon.Length > 20) { customer.telepon = customer.telepon.Substring(0, 20); } var provinsi = _unitOfWork.WilayahRepository.GetProvinsi(customer.Provinsi.nama_provinsi); if (provinsi != null) { customer.provinsi_id = provinsi.provinsi_id; customer.Provinsi = new Provinsi { provinsi_id = provinsi.provinsi_id, nama_provinsi = provinsi.nama_provinsi }; } var kabupaten = _unitOfWork.WilayahRepository.GetKabupaten(customer.Kabupaten.nama_kabupaten); if (kabupaten != null) { customer.kabupaten_id = kabupaten.kabupaten_id; customer.Kabupaten = new Kabupaten { kabupaten_id = kabupaten.kabupaten_id, nama_kabupaten = kabupaten.nama_kabupaten }; } var kecamatan = _unitOfWork.WilayahRepository.GetKecamatan(customer.Kecamatan.nama_kecamatan); if (kecamatan != null) { customer.kecamatan_id = kecamatan.kecamatan_id; customer.Kecamatan = new Kecamatan { kecamatan_id = kecamatan.kecamatan_id, nama_kecamatan = kecamatan.nama_kecamatan }; } result = Convert.ToBoolean(_unitOfWork.CustomerRepository.Save(customer)); } } } result = true; } catch (Exception ex) { _log.Error("Error:", ex); } finally { _workbook.Dispose(); } return(result); }
private static void CriaExcel(ArrayList CepsValidos) { Console.WriteLine("Criando Excel"); var wb = new XLWorkbook(); var ws = wb.Worksheets.Add("planilha 1"); var nArray = CepsValidos.Count / 7; var nCount = 0; //titulo ws.Cell("B2").Value = "PROVA "; var range = ws.Range("B2:I2"); range.Merge().Style.Font.SetBold().Font.FontSize = 20; //Cabeçalho do Relatrio ws.Cell("B3").Value = "Logradouro/Nome"; ws.Cell("C3").Value = "Bairro"; ws.Cell("D3").Value = "Cidade"; ws.Cell("E3").Value = "UF"; ws.Cell("F3").Value = "CEP"; ws.Cell("G3").Value = "Complemento"; ws.Cell("H3").Value = "Processamento"; //CORPO RELATORIO var linha = 4; for (int i = 0; i < nArray; i++) { using (var wd = new AtendeClienteClient()) { // CepsValidos[CepsValidos.Count - 1].end; //criação de cada campo por Array var end = CepsValidos[i + nCount].ToString(); var bairro = CepsValidos[i + nCount + 1].ToString(); var cidade = CepsValidos[i + nCount + 2].ToString(); var uf = CepsValidos[i + nCount + 3].ToString(); var cep = CepsValidos[i + nCount + 4].ToString(); var complemento = CepsValidos[i + nCount + 5].ToString(); var processamento = CepsValidos[i + nCount + 6].ToString(); ws.Cell("B" + linha.ToString()).Value = (String.Format(end)); ws.Cell("C" + linha.ToString()).Value = (String.Format(bairro)); ws.Cell("D" + linha.ToString()).Value = (String.Format(cidade)); ws.Cell("E" + linha.ToString()).Value = (String.Format(uf)); ws.Cell("F" + linha.ToString()).Value = (String.Format(cep)); ws.Cell("G" + linha.ToString()).Value = (String.Format(complemento)); ws.Cell("H" + linha.ToString()).Value = (String.Format(processamento)); linha++; nCount += 6;//logica para incrementar o array dinamicamente } } linha--; wb.SaveAs(@"C:\Users\caio.telles\Desktop\importar_bairros\Resultado.xlsx"); //SALVA o resultado no seu disco local wb.Dispose(); //LIBERA A MEMORIA DO EXCEL Console.WriteLine("Excel Criado com Sucesso!"); Console.ReadKey(); }
/// ----------------------------------------------------------------------------- /// <summary> /// DataTableをもとにxlsxファイルを作成し、PDFファイルを作成 /// </summary> /// <param name="dtHachu">発注のデータテーブル</param> /// <returns>結合PDFファイル</returns> /// ----------------------------------------------------------------------------- public string dbToPdf(DataTable dtHachu) { string strWorkPath = System.Configuration.ConfigurationManager.AppSettings["workpath"]; string strFilePath = "./Template/A0120_ChumonShoPrint.xlsx"; string strDateTime = DateTime.Now.ToString("yyyyMMddHHmmss"); try { // excelのインスタンス生成 XLWorkbook workbook = new XLWorkbook(strFilePath, XLEventTracking.Disabled); IXLWorksheet templatesheet = workbook.Worksheet(1); // テンプレートシート IXLWorksheet currentsheet = null; // 処理中シート int pageCnt = 0; // ページ(シート枚数)カウント int xlsRowCnt = 11; // Excel出力行カウント(開始は出力行) Boolean blnSheetCreate = false; string strTorihikisakiCd = ""; string strHachusha = ""; // ClosedXMLで1行ずつExcelに出力 foreach (DataRow drHachu in dtHachu.Rows) { // 取引先コードが前行と違う場合、テンプレートシート作成 if (!strTorihikisakiCd.Equals(drHachu[1].ToString())) { // 取引先コードが空でない場合 if (!strTorihikisakiCd.Equals("")) { // 明細行が17行、18行の場合 if (xlsRowCnt == 28 || xlsRowCnt == 29) { // 不要な明細行、発注者行を削除 currentsheet.Rows(xlsRowCnt, 33).Delete(); pageCnt++; xlsRowCnt = 11; // テンプレートシートをコピーし、1つ前のシートから取引先名などをコピー templatesheet.CopyTo(templatesheet.Name); } // 不要な明細行を削除 currentsheet.Rows(xlsRowCnt, 28).Delete(); // 発注者 currentsheet.Cell(xlsRowCnt + 2, "K").Value = strHachusha; currentsheet.Range(xlsRowCnt + 2, 11, xlsRowCnt + 4, 11).Merge(); } strTorihikisakiCd = drHachu[1].ToString(); // 取引先コード strHachusha = drHachu[11].ToString(); // 発注者 pageCnt++; xlsRowCnt = 11; blnSheetCreate = true; // テンプレートシートからコピー templatesheet.CopyTo("Page" + pageCnt.ToString()); currentsheet = workbook.Worksheet(workbook.Worksheets.Count); } // 明細行が19行になった場合 if (xlsRowCnt == 29) { // 発注者行を削除 currentsheet.Rows(xlsRowCnt, 33).Delete(); pageCnt++; xlsRowCnt = 11; // テンプレートシートをコピーし、1つ前のシートから取引先名などをコピー templatesheet.CopyTo(templatesheet.Name); } // 最初の明細行の場合 if (blnSheetCreate) { blnSheetCreate = false; currentsheet.Cell("A4").Value = drHachu[2].ToString(); // 取引先名 currentsheet.Cell("B6").Value = drHachu[3].ToString(); // 電話番号 currentsheet.Cell("B7").Value = drHachu[4].ToString(); // FAX currentsheet.Cell("E3").Value = drHachu[0].ToString(); // 年月日 currentsheet.Cell("K3").Value = drHachu[13].ToString(); // 営業所名 } currentsheet.Cell(xlsRowCnt, "A").Value = drHachu[5].ToString(); // 商品名 currentsheet.Cell(xlsRowCnt, "E").Value = drHachu[6].ToString(); // 数量 currentsheet.Cell(xlsRowCnt, "F").Value = drHachu[7].ToString(); // 単価 currentsheet.Cell(xlsRowCnt, "G").Value = drHachu[8].ToString(); // 金額 currentsheet.Cell(xlsRowCnt, "I").Value = drHachu[9].ToString(); // 納期 currentsheet.Cell(xlsRowCnt, "J").Value = drHachu[10].ToString(); // 注文番号 currentsheet.Cell(xlsRowCnt, "K").Value = "'" + drHachu[12].ToString(); // 備考 xlsRowCnt++; } // 発注データがある場合 if (dtHachu.Rows.Count > 0) { // 明細行が17行、18行の場合 if (xlsRowCnt == 28 || xlsRowCnt == 29) { // 不要な明細行、発注者行を削除 currentsheet.Rows(xlsRowCnt, 33).Delete(); pageCnt++; xlsRowCnt = 11; // テンプレートシートをコピーし、1つ前のシートから取引先名などをコピー templatesheet.CopyTo(templatesheet.Name); //this.sheetCopy(ref workbook, ref currentsheet, templatesheet, pageCnt); } // 不要な明細行を削除 currentsheet.Rows(xlsRowCnt, 28).Delete(); // 発注者 currentsheet.Cell(xlsRowCnt + 2, "K").Value = strHachusha; currentsheet.Range(xlsRowCnt + 2, 11, xlsRowCnt + 4, 11).Merge(); } // テンプレートシート削除 templatesheet.Delete(); // workbookを保存 string strOutXlsFile = strWorkPath + strDateTime + ".xlsx"; workbook.SaveAs(strOutXlsFile); // workbookを解放 workbook.Dispose(); // CreatePdfのインスタンス生成 CreatePdf pdf = new CreatePdf(); // ロゴ貼り付け処理 int[] topRow = { 2 }; int[] leftColumn = { 8 }; pdf.logoPaste(strOutXlsFile, topRow, leftColumn, 200, 850, 57); // PDF化の処理 return(pdf.createPdf(strOutXlsFile, strDateTime)); } catch { throw; } finally { //// Workフォルダの作成日時ファイルを取得 //string[] files = Directory.GetFiles(strWorkPath, strDateTime + "*", SearchOption.AllDirectories); //// Workフォルダ内のファイル削除 //foreach (string filepath in files) //{ // File.Delete(filepath); //} } }
public bool Import(string workSheetName, ref int rowCount) { var result = false; try { var ws = _workbook.Worksheet(workSheetName); // Look for the first row used var firstRowUsed = ws.FirstRowUsed(); // Narrow down the row so that it only includes the used part var golonganRow = firstRowUsed.RowUsed(); // First possible address of the company table: var firstPossibleAddress = ws.Row(golonganRow.RowNumber()).FirstCell().Address; // Last possible address of the company table: var lastPossibleAddress = ws.LastCellUsed().Address; // Get a range with the remainder of the worksheet data (the range used) var golonganRange = ws.Range(firstPossibleAddress, lastPossibleAddress).RangeUsed(); // Treat the range as a table (to be able to use the column names) var golonganTable = golonganRange.AsTable(); var listOfGolongan = new List <Golongan>(); listOfGolongan = golonganTable.DataRange.Rows().Select(row => new Golongan { nama_golongan = row.Field("GOLONGAN").GetString(), diskon = row.Field("DISKON").GetString().Length == 0 ? 0 : Convert.ToDouble(row.Field("DISKON").GetString()) }).ToList(); if (listOfGolongan.Count == 1 && listOfGolongan[0].nama_golongan.Length == 0) { rowCount = 0; return(false); } rowCount = listOfGolongan.Count; using (IDapperContext context = new DapperContext()) { IUnitOfWork uow = new UnitOfWork(context, _log); foreach (var golongan in listOfGolongan) { if (golongan.nama_golongan.Length > 0) { if (golongan.nama_golongan.Length > 50) { golongan.nama_golongan = golongan.nama_golongan.Substring(0, 50); } var oldGolongan = uow.GolonganRepository.GetByName(golongan.nama_golongan, false) .FirstOrDefault(); if (oldGolongan == null) // data golongan belum ada { result = Convert.ToBoolean(uow.GolonganRepository.Save(golongan)); } } } } result = true; } catch (Exception ex) { _log.Error("Error:", ex); } finally { _workbook.Dispose(); } return(result); }
public bool Import(string workSheetName, ref int rowCount) { var result = false; try { var ws = _workbook.Worksheet(workSheetName); // Look for the first row used var firstRowUsed = ws.FirstRowUsed(); // Narrow down the row so that it only includes the used part var supplierRow = firstRowUsed.RowUsed(); // First possible address of the company table: var firstPossibleAddress = ws.Row(supplierRow.RowNumber()).FirstCell().Address; // Last possible address of the company table: var lastPossibleAddress = ws.LastCellUsed().Address; // Get a range with the remainder of the worksheet data (the range used) var supplierRange = ws.Range(firstPossibleAddress, lastPossibleAddress).RangeUsed(); // Treat the range as a table (to be able to use the column names) var supplierTable = supplierRange.AsTable(); var listOfDropshipper = new List <Dropshipper>(); listOfDropshipper = supplierTable.DataRange.Rows().Select(row => new Dropshipper { nama_dropshipper = row.Field("NAMA").GetString(), alamat = row.Field("ALAMAT").GetString(), telepon = row.Field("TELEPON").GetString() }).ToList(); if (listOfDropshipper.Count == 1 && listOfDropshipper[0].nama_dropshipper.Length == 0) { rowCount = 0; return(false); } rowCount = listOfDropshipper.Count; using (IDapperContext context = new DapperContext()) { _unitOfWork = new UnitOfWork(context, _log); foreach (var dropshipper in listOfDropshipper) { if (dropshipper.nama_dropshipper.Length > 0) { if (dropshipper.nama_dropshipper.Length > 50) { dropshipper.nama_dropshipper = dropshipper.nama_dropshipper.Substring(0, 50); } if (dropshipper.alamat.Length > 100) { dropshipper.alamat = dropshipper.alamat.Substring(0, 100); } if (dropshipper.telepon.Length > 20) { dropshipper.telepon = dropshipper.telepon.Substring(0, 20); } result = Convert.ToBoolean(_unitOfWork.DropshipperRepository.Save(dropshipper)); } } } result = true; } catch (Exception ex) { _log.Error("Error:", ex); } finally { _workbook.Dispose(); } return(result); }
/// ----------------------------------------------------------------------------- /// <summary> /// DataTableをもとにxlsxファイルを作成しPDF化</summary> /// <param name="dtSeikyuMeisai"> /// 請求明細書のデータテーブル</param> /// ----------------------------------------------------------------------------- public string dbToPdf(DataTable dtSetCd_B_Input, bool blNaga4) { string strWorkPath = System.Configuration.ConfigurationManager.AppSettings["workpath"]; string strFilePath = ""; string strDateTime = DateTime.Now.ToString("yyyyMMddHHmmss"); int intPaperSizeIndex = 0; if (blNaga4 == true) { strFilePath = "./Template/M0620_HushoAtenaInsatsu_Naga4.xlsx"; } else { strFilePath = "./Template/M0620_HushoAtenaInsatsu_Naga3.xlsx"; } try { // excelのインスタンス生成 XLWorkbook workbook = new XLWorkbook(strFilePath, XLEventTracking.Disabled); IXLWorksheet templatesheet1 = workbook.Worksheet(1); // テンプレートシート IXLWorksheet currentsheet = null; // 処理中シート int pageCnt = 0; // ページ(シート枚数)カウント // テンプレートシートからコピー templatesheet1.CopyTo("Page" + pageCnt.ToString()); currentsheet = workbook.Worksheet(workbook.Worksheets.Count); if (blNaga4 == true) { currentsheet.Cell("D2").Value = dtSetCd_B_Input.Rows[0]["郵便番号"]; // 郵便番号 currentsheet.Cell("L4").Value = dtSetCd_B_Input.Rows[0]["住所1"]; // 住所1 currentsheet.Cell("L6").Value = dtSetCd_B_Input.Rows[0]["住所2"]; // 住所2 currentsheet.Cell("R7").Value = dtSetCd_B_Input.Rows[0]["名称"]; // 名称 } else { currentsheet.Cell("F2").Value = dtSetCd_B_Input.Rows[0]["郵便番号"]; // 郵便番号 currentsheet.Cell("H4").Value = dtSetCd_B_Input.Rows[0]["住所1"]; // 住所1 currentsheet.Cell("H5").Value = dtSetCd_B_Input.Rows[0]["住所2"]; // 住所2 currentsheet.Cell("I8").Value = dtSetCd_B_Input.Rows[0]["名称"]; // 名称 } // テンプレートシート削除 templatesheet1.Delete(); string strOutXlsFile = strWorkPath + strDateTime + ".xlsx"; //横にする workbook.PageOptions.PageOrientation = XLPageOrientation.Landscape; // workbookを保存 workbook.SaveAs(strOutXlsFile); // workbookを解放 workbook.Dispose(); if (blNaga4 == true) { intPaperSizeIndex = 4; } else { intPaperSizeIndex = 3; } CreatePdf pdf = new CreatePdf(); // PDF化の処理 return(pdf.createPdf(strOutXlsFile, strDateTime, intPaperSizeIndex)); //return pdf.createPdf(strOutXlsFile, strDateTime); } catch { throw; } finally { // Workフォルダの全ファイルを取得 string[] files = System.IO.Directory.GetFiles(strWorkPath, "*", System.IO.SearchOption.AllDirectories); // Workフォルダ内のファイル削除 foreach (string filepath in files) { //File.Delete(filepath); } } }
private void BT_EXPORT_MEASURE(object sender, RoutedEventArgs e) { if (gmeasure.Count <= 0 || gbackups.Count <= 0) { return; } string fpath = Environment.CurrentDirectory + @"\要素備份"; if (!Directory.Exists(fpath)) { Directory.CreateDirectory(fpath); } string fname = @"\指標數據總資料" + DateTime.Now.AddMonths(-1).ToString("yyyy-MM", CultureInfo.InvariantCulture) + ".xlsx"; var sortbacks = gbackups.OrderBy(o => o.Key).ToDictionary(o => o.Key, p => p.Value.OrderByDescending(o => o.ElementData).ToList()); try { using (var wb = new XLWorkbook()) { var ws = wb.Worksheets.Add("工作表1"); ws.Style.Font.FontSize = 12; ws.Style.Font.FontName = "微軟正黑體"; var wscol = ws.Columns("A:C"); wscol.Width = 15; wscol.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center; wscol.Style.Alignment.Vertical = XLAlignmentVerticalValues.Center; wscol.Style.Alignment.WrapText = true; ws.Cells("A1:C1").Style.Fill.BackgroundColor = XLColor.LightBlue; ws.Cell(1, 1).Value = "指標群組"; ws.Cell(1, 2).Value = "指標代號"; ws.Cell(1, 3).Value = "指標名稱"; for (int i = 0; i < 12; i++) { ws.Cell(1, i + 4).Value = DateTime.Now.AddMonths(-i - 1).ToString("yyyy/MM"); ws.Cell(1, i + 4).Style.DateFormat.Format = "yyyy/MM"; ws.Cell(1, i + 4).Style.Fill.BackgroundColor = XLColor.LightBlue; ws.Cell(1, i + 4).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center; ws.Column(i + 4).AdjustToContents(); } int index = 2; foreach (var x in gmeasure) { ws.Cell(index, 1).Value = x.Group; ws.Cell(index, 2).Value = x.MeasureID; ws.Cell(index, 3).Value = x.MeasureName; ws.Range(index, 1, index + 2, 1).Merge(); ws.Range(index, 2, index + 2, 2).Merge(); ws.Range(index, 3, index + 2, 3).Merge(); int Destatus = 0; int Nustatus = 0; List <List <MElements> > DenosPlus = new List <List <MElements> >(); List <List <MElements> > NumePlus = new List <List <MElements> >(); var Numes = sortbacks.FirstOrDefault(o => o.Key == x.Numerator).Value; var Denos = sortbacks.FirstOrDefault(o => o.Key == x.Denominator).Value; if (x.Denominator.Contains("+")) { Destatus = 1; var elements = x.Denominator.Split('+').ToList(); if (elements.Count > 0) { foreach (var ele in elements) { var em = sortbacks.FirstOrDefault(o => o.Key == ele).Value; if (em != null) { DenosPlus.Add(em); } } } } else if (x.Denominator.Contains(".") && x.Denominator.Contains("-")) { Destatus = 2; var elements = x.Denominator.Split('-').ToList(); if (elements.Count > 0) { foreach (var ele in elements) { var em = sortbacks.FirstOrDefault(o => o.Key == ele).Value; if (em != null) { DenosPlus.Add(em); } } } } if (x.Numerator.Contains("+")) { Nustatus = 1; var elements = x.Numerator.Split('+').ToList(); if (elements.Count > 0) { foreach (var ele in elements) { var em = sortbacks.FirstOrDefault(o => o.Key == ele).Value; if (em != null) { NumePlus.Add(em); } } } } else if (x.Numerator.Contains(".") && x.Numerator.Contains("-")) { Nustatus = 2; var elements = x.Numerator.Split('-').ToList(); if (elements.Count > 0) { foreach (var ele in elements) { var em = sortbacks.FirstOrDefault(o => o.Key == ele).Value; if (em != null) { NumePlus.Add(em); } } } } for (int i = 0; i < 12; i++) { if (x.Numerator == "1") { ws.Cell(index + 1, i + 4).Value = 1; } else if (Numes != null) { var nume = Numes.FirstOrDefault(o => o.Eledate.Year == DateTime.Now.AddMonths(-i - 1).Year && o.Eledate.Month == DateTime.Now.AddMonths(-i - 1).Month); if (nume != null) { int numok; if (int.TryParse(nume.ElementData, out numok)) { ws.Cell(index + 1, i + 4).Value = numok; } } } else if (NumePlus.Count > 0) { try { int deno = 0; foreach (var ele in NumePlus) { var de = ele.FirstOrDefault(o => o.Eledate.Year == DateTime.Now.AddMonths(-i - 1).Year && o.Eledate.Month == DateTime.Now.AddMonths(-i - 1).Month); if (de == null) { break; } int num; if (!Int32.TryParse(de.ElementData, out num)) { break; } if (Nustatus == 1) { deno += num; } else if (Nustatus == 2) { if (deno == 0) { deno = num; } else { deno -= num; } } } if (deno > 0) { ws.Cell(index + 1, i + 4).Value = deno; } } catch (Exception ex) { MessageBox.Show(ex.ToString()); } } if (x.Denominator == "1") { ws.Cell(index + 2, i + 4).Value = "NA"; } else if (Denos != null) { var deno = Denos.FirstOrDefault(o => o.Eledate.Year == DateTime.Now.AddMonths(-i - 1).Year && o.Eledate.Month == DateTime.Now.AddMonths(-i - 1).Month); if (deno != null) { int numok; if (int.TryParse(deno.ElementData, out numok)) { ws.Cell(index + 2, i + 4).Value = numok; } } } else if (DenosPlus.Count > 0) { try { int deno = 0; foreach (var ele in DenosPlus) { var de = ele.FirstOrDefault(o => o.Eledate.Year == DateTime.Now.AddMonths(-i - 1).Year && o.Eledate.Month == DateTime.Now.AddMonths(-i - 1).Month); if (de == null) { break; } int num; if (!Int32.TryParse(de.ElementData, out num)) { break; } if (Destatus == 1) { deno += num; } else if (Destatus == 2) { if (deno == 0) { deno = num; } else { deno -= num; } } } if (deno > 0) { ws.Cell(index + 2, i + 4).Value = deno; } } catch (Exception ex) { MessageBox.Show(ex.ToString()); } } if (!string.IsNullOrEmpty(ws.Cell(index + 2, i + 4).GetString())) { double nu, de; if (double.TryParse(ws.Cell(index + 1, i + 4).GetString(), out nu) && double.TryParse(ws.Cell(index + 2, i + 4).GetString() == "NA" ? "1" : ws.Cell(index + 2, i + 4).GetString(), out de)) { if (nu == 0) { ws.Cell(index, i + 4).Value = nu; } else if (de != 0) { ws.Cell(index, i + 4).Value = nu / de; } } } } index += 3; } wb.SaveAs(fpath + fname); MessageBox.Show("指標匯出結束"); wb.Dispose(); } } catch (Exception ex) { MessageBox.Show(ex.ToString()); } }
private void BT_EXPORT_ELEMENT(object sender, RoutedEventArgs e) { if (gbackups.Count <= 0) { return; } if (gcollect.Count > 0) { foreach (var x in gcollect) { if (gbackups.ContainsKey(x.Element)) { var dataex = gbackups[x.Element].FirstOrDefault(o => o.Element == x.Element && o.Eledate == x.Eledate); if (dataex != null) { gbackups[x.Element].Remove(dataex); } gbackups[x.Element].Add(x); } } } var sortbacks = gbackups.OrderBy(o => o.Key).ToDictionary(o => o.Key, p => p.Value); string fpath = Environment.CurrentDirectory + @"\要素備份"; if (!Directory.Exists(fpath)) { Directory.CreateDirectory(fpath); } string fname = @"\指標收集總存檔" + DateTime.Now.AddMonths(-1).ToString("yyyy-MM", CultureInfo.InvariantCulture) + ".xlsx"; //string fname2 = @"\指標收集存檔總檔.xlsx"; try { using (var wb = new XLWorkbook()) { var ws = wb.Worksheets.Add("工作表1"); ws.Style.Font.FontSize = 12; ws.Style.Font.FontName = "微軟正黑體"; var wscol = ws.Columns("A"); wscol.Width = 15; wscol.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center; ws.Cell(1, 1).Style.Fill.BackgroundColor = XLColor.LightBlue; ws.Cell(1, 1).Value = "指標要素"; for (int i = 0; i < 6; i++) { ws.Cell(1, i + 2).Value = DateTime.Now.AddMonths(-1 - i).ToString("yyyy/MM"); ws.Cell(1, i + 2).Style.DateFormat.Format = "yyyy/MM"; ws.Cell(1, i + 2).Style.Fill.BackgroundColor = XLColor.LightBlue; ws.Cell(1, i + 2).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center; ws.Column(i + 2).AdjustToContents(); } int index = 0; foreach (var x in sortbacks) { ws.Cell(index + 2, 1).Value = x.Key; ws.Cell(index + 2, 1).Style.Fill.BackgroundColor = XLColor.LightCyan; foreach (var y in x.Value) { for (int i = 0; i < 6; i++) { int num; if (y.Eledate.Year == DateTime.Now.AddMonths(-1 - i).Year && y.Eledate.Month == DateTime.Now.AddMonths(-1 - i).Month&& int.TryParse(y.ElementData, out num)) { ws.Cell(index + 2, i + 2).Value = num; } } } index++; } wb.SaveAs(fpath + fname); MessageBox.Show("匯出要素備份成功"); wb.Dispose(); } } catch (Exception ex) { MessageBox.Show(ex.ToString()); } }
public void Dispose() { workbook.Dispose(); }
public void Dispose() { _workbook?.Dispose(); }
/// ----------------------------------------------------------------------------- /// <summary> /// DataTableをもとにxlsxファイルを作成 /// </summary> /// <param name="dtShohin"> /// 商品仕入単価履歴TMP2のデータテーブル /// </param> /// <param name="lstItem"> /// Excel出力用データ /// </param> /// <param name="strExcelFilePath"> /// Excel出力ファイルパス /// </param> /// ----------------------------------------------------------------------------- public bool dbToExcel(DataTable dtShohin, List <string> lstItem, string strExcelFilePath) { string strWorkPath = System.Configuration.ConfigurationManager.AppSettings["workpath"]; string strFilePath = "./Template/商品単価一覧.xlsx"; string strShu = ""; // テンプレートが存在すれば処理 if (System.IO.File.Exists(strFilePath)) { // excelのインスタンス生成 XLWorkbook workbook = new XLWorkbook(strFilePath, XLEventTracking.Disabled); try { IXLWorksheet currentsheet = workbook.Worksheet(1); // 処理中シート int xlsRowCnt = 14; // Excel出力行カウント(開始は出力行) currentsheet.Cell("F3").Value = lstItem[0].ToString(); // 大分類 currentsheet.Cell("H3").Value = lstItem[1].ToString(); // 中分類 currentsheet.Cell("K3").Value = lstItem[2].ToString(); // メーカー currentsheet.Cell("G5").Value = lstItem[3].ToString(); // 期間From currentsheet.Cell("I5").Value = lstItem[4].ToString(); // 期間To currentsheet.Cell("G6").Value = lstItem[5].ToString(); // 基準在庫日 // 全項目にチェックが入っている場合 if (bool.Parse(lstItem[6]) == true) { strShu += "全項目"; } else { // 期間内売上ありの場合 if (lstItem[7].Equals("0")) { strShu += " 期間内売上あり"; } // 期間内売上なしの場合 else if (lstItem[7].Equals("1")) { strShu += " 期間内売上なし"; } // 期間内仕入ありの場合 if (lstItem[8].Equals("0")) { strShu += " 期間内仕入あり"; } // 期間内仕入なしの場合 else if (lstItem[8].Equals("1")) { strShu += " 期間内仕入なし"; } } currentsheet.Cell("K5").Value = strShu; // 検索条件 currentsheet.Cell("H9").Value = lstItem[9].ToString(); // 在庫金額合計(商品マスタ評価単価) currentsheet.Cell("H10").Value = lstItem[10].ToString(); // 在庫金額合計(設定単価) currentsheet.Cell("H11").Value = lstItem[11].ToString(); // 在庫金額合計(直近仕入単価) // 出力に必要な分Excelに行を追加 for (int rowCnt = 1; rowCnt < dtShohin.Rows.Count; rowCnt++) { currentsheet.Range(14, 1, 14, 16).CopyTo(currentsheet.Range(14 + rowCnt, 1, 14 + rowCnt, 16)); } // ClosedXMLで1行ずつExcelに出力 foreach (DataRow drShohin in dtShohin.Rows) { for (int columnCnt = 1; columnCnt <= 16; columnCnt++) { currentsheet.Cell(xlsRowCnt, columnCnt).Value = drShohin[columnCnt].ToString(); } xlsRowCnt++; } // workbookを保存 workbook.SaveAs(strExcelFilePath); return(true); } catch { throw; } finally { // workbookを解放 workbook.Dispose(); } } else { return(false); } }
/// <summary> /// ''' Procedimiento que permite exportar un archivo a excel. /// ''' </summary> /// ''' <param name="Grid">El grid que contiene la información a exportar. Es necesario que el grid se llene con un DataTable.</param> /// ''' <param name="AbrirArchivo">Indica si se debe abrir el archivo al terminar el proceso de exportación.</param> /// ''' <param name="MostrarSaveDialog">Si se desea que el usuario seleccione la ruta donde desea guardar el echivo, esta varible lo controla.</param> /// ''' <param name="FileName">En caso de que se indique que no se desea que el usuario eliga la ruta y nomnre del archivo, aqui indicar la ruta y nombre, si se envia vacio, se almacenara en la misma ruta donde se encuentra el ejecutable y se le dara un nombre default.</param> /// ''' <remarks></remarks> public void ExportarDataGridViewExcel(ref DataGridView Grid, bool AbrirArchivo, bool MostrarSaveDialog = true, string FileName = "") { XLWorkbook workBook = new XLWorkbook(); try { // Creamos el datatable para exportar los datos // Dim dtInfo As DataTable = DirectCast(Grid.DataSource, DataTable) DataTable dtInfo = GetContentAsDataTable(Grid, true); if (dtInfo.TableName == "") { dtInfo.TableName = "Informacion"; } workBook.Worksheets.Add(dtInfo); // Si el parametro MostrarSaveDialog es true, mostramos una cuadro de dialogo al usuario para que seleccione // la ruta y nombre del archivo if (MostrarSaveDialog) { SaveFileDialog SaveDlg = new SaveFileDialog { InitialDirectory = @"c:\\", Filter = "Archivos de Microsoft Excel (xlsx) | *.xlsx", DefaultExt = ".xlsx", FileName = FileName }; if (SaveDlg.ShowDialog() == DialogResult.OK) { FileName = SaveDlg.FileName; } else { return; } } else { if (FileName == "") { FileName = Environment.CurrentDirectory + @"\ExportGrid.xlsx"; } // Si ya existe un archivo con ese nombre, lo eliminamos if (File.Exists(FileName)) { File.Delete(FileName); } } // lo guardamos en la ruta seleccionada workBook.SaveAs(FileName); // Si se creo correctamente el archivo, lo abrimos if (File.Exists(FileName)) { MessageBox.Show("Archivo gardado correctamente.", "Aviso", MessageBoxButtons.OK, MessageBoxIcon.Information); if (AbrirArchivo) { Process.Start(FileName); } } else { MessageBox.Show("El archivo no se pudo guardar.", "Aviso", MessageBoxButtons.OK, MessageBoxIcon.Warning); } workBook.Dispose(); } catch (Exception ex) { MessageBox.Show("Error al exportar a Excel: " + ex.Message, "Aviso", MessageBoxButtons.OK, MessageBoxIcon.Warning); workBook.Dispose(); } }
//Generate anf format rating Report into excel per Business Unit public static void GenerateRatingReportExcelFilePerBusinessUnit(DataTable dt, List <SurveyStatus> surveyForList, bool includeRaters, string businessUnit) { //Testing //Deleting Existing File using (XLWorkbook wb = new XLWorkbook()) { //Loop through team list and generate a report for each team member for (int r = 0; r < surveyForList.Count; r++) { dt.TableName = surveyForList[r].Survey_For; //Adding a datatable to a worksheet wb.Worksheets.Add(dt); wb.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center; wb.Style.Font.Bold = true; context.Current.Response.Clear(); context.Current.Response.Buffer = true; context.Current.Response.ClearContent(); context.Current.Response.ClearHeaders(); context.Current.Response.Buffer = true; surveyForList[r].Survey_For = surveyForList[r].Survey_For.Trim(); var validDisplayName = surveyForList[r].Survey_For.Replace(",", "_").Replace(";", "_").Replace(" ", "_"); context.Current.Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"; context.Current.Response.AddHeader("content-disposition", "attachment;filename=" + validDisplayName + "_Rating_Report" + ".xlsx"); using (MemoryStream MyMemoryStream = new MemoryStream()) { var worksheet = wb.Worksheet(surveyForList[r].Survey_For); var columnCounter = 1; for (int i = 2; i <= dt.Columns.Count; i++) { var value = worksheet.Cell(2, columnCounter).Value; worksheet.Cell(2, columnCounter).Style.Font.Bold = true; worksheet.Cell(2, columnCounter).Style.Font.FontSize = 15; worksheet.Cell(3, columnCounter).Style.Font.Bold = true; worksheet.Cell(3, columnCounter).Style.Font.FontSize = 15; break; } columnCounter = 1; //Formating first and second column heading for (int i = 3; i <= dt.Columns.Count; i++) { var value = worksheet.Cell(3, columnCounter).Value; worksheet.Cell(3, columnCounter).Style.Font.Bold = true; worksheet.Cell(3, columnCounter).Style.Font.FontSize = 15; columnCounter++; } //Formatting labels columnCounter = 1; for (int i = 4; i <= dt.Columns.Count; i++) { var value = worksheet.Cell(4, columnCounter).Value; worksheet.Cell(4, columnCounter).Style.Font.Bold = true; worksheet.Columns().Width = 15; //worksheet.Cell(4, columnCounter).Style.Font.FontSize = 8; worksheet.Cell(4, columnCounter).Style.Alignment.WrapText = true; //worksheet.Column(i).Width = 10; columnCounter++; } columnCounter = 1; //Formatting labels (Min, Max and Avg) for (int i = 5; i <= dt.Rows.Count; i++) { var value = worksheet.Cell(i, columnCounter).Value; worksheet.Cell(i, columnCounter).Style.Font.Bold = true; //worksheet.Cell(i, columnCounter).Style.Alignment.WrapText = true; } //Right aligning numbers columnCounter = 1; var startRow = 5; for (int j = 0; j < 3; j++) { columnCounter = 1; for (int i = 5; i <= dt.Columns.Count; i++) { string value = worksheet.Cell(startRow, columnCounter).Value.ToString(); decimal doublevalue; var doubleValueOutput = decimal.TryParse(value, out doublevalue); int integerValue; var interValueOutput = int.TryParse(value, out integerValue); if (doubleValueOutput) { worksheet.Cell(startRow, columnCounter).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Right; worksheet.Cell(startRow, columnCounter).Style.Font.Bold = true; } if (interValueOutput) { worksheet.Cell(startRow, columnCounter).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Right; worksheet.Cell(startRow, columnCounter).Style.Font.Bold = true; } columnCounter++; } startRow++; } //Right aligning rating values startRow = 11; columnCounter = 1; for (int j = 0; j < dt.Rows.Count; j++) { columnCounter = 1; for (int i = 0; i <= dt.Columns.Count; i++) { //Get cell value string value = worksheet.Cell(startRow, columnCounter).Value.ToString(); decimal doublevalue; var doubleValueOutput = decimal.TryParse(value, out doublevalue); int integerValue; var interValueOutput = int.TryParse(value, out integerValue); if (doubleValueOutput) { worksheet.Cell(startRow, columnCounter).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Right; worksheet.Cell(startRow, columnCounter).Style.Font.Bold = true; worksheet.Rows(startRow, startRow).Style.Fill.BackgroundColor = XLColor.Silver; } if (interValueOutput) { worksheet.Cell(startRow, columnCounter).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Right; worksheet.Cell(startRow, columnCounter).Style.Font.Bold = true; worksheet.Rows(startRow, startRow).Style.Fill.BackgroundColor = XLColor.Silver; } if (!interValueOutput && !doubleValueOutput) { if (value.Length > 30) { worksheet.Cell(startRow, columnCounter).Style.Alignment.WrapText = true; } } columnCounter++; } startRow++; } //Formatting Raters' columns startRow = 10; var employeeList = new Surveys().getSurveyTeamEmployeeList(); for (int j = 1; j <= dt.Rows.Count; j++) { columnCounter = 1; for (int i = 1; i <= dt.Columns.Count; i++) { //get cell value/text string rater = worksheet.Cell(startRow, columnCounter).Value.ToString(); decimal doublevalue; var doubleValueOutput = decimal.TryParse(rater, out doublevalue); int integerValue; var interValueOutput = int.TryParse(rater, out integerValue); //Check and exclude rating values if (!(interValueOutput == true || doubleValueOutput == true)) { if (!string.IsNullOrWhiteSpace(rater)) { //To validate and format a rater //First check if the rater is in fg user list var list = employeeList.Where(c => c.Employee == rater); if (!(list.Count() == 0)) { if (employeeList.Where(c => c.Employee == rater).First().Employee.Count() > 0) { worksheet.Cell(startRow, columnCounter).Style.Font.Bold = true; } } } } if (!interValueOutput && !doubleValueOutput) { if (rater.Length > 30) { worksheet.Cell(startRow, columnCounter).Style.Alignment.WrapText = true; } } columnCounter++; } startRow++; } worksheet.Rows(4, 4).Style.Fill.BackgroundColor = XLColor.Silver; worksheet.Rows(2, 2).Style.Fill.BackgroundColor = XLColor.Silver; worksheet.Rows(7, 7).Style.Fill.BackgroundColor = XLColor.Silver; worksheet.Rows(7, 7).Style.Font.Bold = true; if (includeRaters) { worksheet.Rows(9, 9).Style.Fill.BackgroundColor = XLColor.Silver; } //wb.SaveAs(MyMemoryStream); worksheet.Rows(1, 1).Style.Fill.BackgroundColor = XLColor.White; worksheet.Rows(1, 1).Style.Font.FontColor = XLColor.White; MyMemoryStream.WriteTo(context.Current.Response.OutputStream); var directoryPath = ConfigurationManager.AppSettings["BusinessUnitReportLocation"] + @"" + businessUnit; string time = DateTime.Now + DateTime.Now.Millisecond.ToString(); time = time.Replace(" ", "").Replace(":", "").Replace("AM", "").Replace("/", "_").Replace("PM", ""); if (!Directory.Exists(directoryPath)) { DirectoryInfo di = Directory.CreateDirectory(directoryPath); } var fileName = surveyForList[r].Survey_For; fileName = fileName.Trim(); fileName = fileName.Replace(" ", "_"); var filePath = ConfigurationManager.AppSettings["BusinessUnitReportLocation"] + businessUnit + @"\" + fileName + "_" + time + ".xlsx"; wb.SaveAs(filePath); wb.Dispose(); } } //context.Current.Response.Flush(); //context.Current.Response.End(); } }
static void Main(string[] args) { //Abrindo o arquivo do excel. var Ex = new XLWorkbook(@"C:/Produtividade 2019-2020.xlsx"); //Criando um for para percorrer todas as abas da planilha. for (int T = 1; T >= 1; T++) { // Planilha recebe todas abas que tiver existente na planilha var planilha = Ex.Worksheet(T); //Nome da aba da planilha. Console.WriteLine("Aba: " + planilha); Console.WriteLine(); //Acessando dados da linha 1 da planilha. var linha = 1; //Enquanto for verdadeiro, faca... while (true) { //nome recebe dados da coluna A da linha 1. var nome = planilha.Cell("A" + linha.ToString()).Value.ToString(); //Se encontrar linha vazia, pode encerrar o excel. if (string.IsNullOrEmpty(nome)) { break; } //Mostrando os dados. Console.Write(nome.PadRight(5)); //Mostrando dados da coluna B. Console.Write(" " + planilha.Cell("B" + linha.ToString()).Value.ToString().PadLeft(5)); //Mostrando dados da coluna B. Console.Write(" " + planilha.Cell("C" + linha.ToString()).Value.ToString().PadLeft(5)); //Mostrando dados da coluna B. Console.Write(" " + planilha.Cell("D" + linha.ToString()).Value.ToString().PadLeft(5)); //Mostrando dados da coluna B. Console.Write(" " + planilha.Cell("E" + linha.ToString()).Value.ToString().PadLeft(5)); //Mostrando dados da coluna B. Console.Write(" " + planilha.Cell("F" + linha.ToString()).Value.ToString().PadLeft(5)); //Mostrando dados da coluna B. Console.Write(" " + planilha.Cell("G" + linha.ToString()).Value.ToString().PadLeft(5)); //Mostrando dados da coluna B. Console.Write(" " + planilha.Cell("H" + linha.ToString()).Value.ToString().PadLeft(5)); //Mostrando dados da coluna B. Console.Write(" " + planilha.Cell("I" + linha.ToString()).Value.ToString().PadLeft(5)); //Mostrando dados da coluna B. Console.Write(" " + planilha.Cell("J" + linha.ToString()).Value.ToString().PadLeft(5)); //Mostrando dados da coluna B. Console.Write(" " + planilha.Cell("L" + linha.ToString()).Value.ToString().PadLeft(5)); //Mostrando dados da coluna B. Console.Write(" " + planilha.Cell("M" + linha.ToString()).Value.ToString().PadLeft(5)); //Mostrando dados da coluna B. Console.Write(" " + planilha.Cell("N" + linha.ToString()).Value.ToString().PadLeft(5)); //Mostrando dados da coluna B. Console.Write(" " + planilha.Cell("O" + linha.ToString()).Value.ToString().PadLeft(5)); //Mostrando dados da coluna B. Console.Write(" " + planilha.Cell("P" + linha.ToString()).Value.ToString().PadLeft(5)); //Mostrando dados da coluna B. Console.Write(" " + planilha.Cell("Q" + linha.ToString()).Value.ToString().PadLeft(5)); //Mostrando dados da coluna B. Console.Write(" " + planilha.Cell("R" + linha.ToString()).Value.ToString().PadLeft(5)); //Mostrando dados da coluna B. Console.Write(" " + planilha.Cell("S" + linha.ToString()).Value.ToString().PadLeft(5)); //Mostrando dados da coluna B. Console.Write(" " + planilha.Cell("T" + linha.ToString()).Value.ToString().PadLeft(5)); //Mostrando dados da coluna B. Console.Write(" " + planilha.Cell("U" + linha.ToString()).Value.ToString().PadLeft(5)); //Mostrando dados da coluna B. Console.Write(" " + planilha.Cell("V" + linha.ToString()).Value.ToString().PadLeft(5)); //Mostrando dados da coluna B. Console.Write(" " + planilha.Cell("X" + linha.ToString()).Value.ToString().PadLeft(5)); //Mostrando dados da coluna B. Console.Write(" " + planilha.Cell("Y" + linha.ToString()).Value.ToString().PadLeft(5)); //Mostrando dados da coluna B. Console.Write(" " + planilha.Cell("Z" + linha.ToString()).Value.ToString().PadLeft(5)); //Repetir para a linha 2,3,4 e etc... linha++; } planilha.Clear(); } Ex.Dispose(); Console.ReadKey(); }
/// ----------------------------------------------------------------------------- /// <summary> /// DataTableをもとにxlsxファイルを作成しPDF化</summary> /// <param name="dtNyukinCheckList"> /// 仕入推移表のデータテーブル</param> /// ----------------------------------------------------------------------------- public string dbToPdf(DataTable dtNyukinCheckList, List <string> lstItem) { string strWorkPath = System.Configuration.ConfigurationManager.AppSettings["workpath"]; string strDateTime = DateTime.Now.ToString("yyyyMMddHHmmss"); string strNow = DateTime.Now.ToString("yyyy/MM/dd HH:mm:ss"); try { CreatePdf pdf = new CreatePdf(); // ワークブックのデフォルトフォント、フォントサイズの指定 XLWorkbook.DefaultStyle.Font.FontName = "MS 明朝"; XLWorkbook.DefaultStyle.Font.FontSize = 9; // excelのインスタンス生成 XLWorkbook workbook = new XLWorkbook(XLEventTracking.Disabled); IXLWorksheet worksheet = workbook.Worksheets.Add("Header"); IXLWorksheet headersheet = worksheet; // ヘッダーシート IXLWorksheet currentsheet = worksheet; // 処理中シート //Linqで必要なデータをselect var outDataAll = dtNyukinCheckList.AsEnumerable() .Select(dat => new { tokuisakiCd = dat["得意先コード"], tokuisakiName = dat["取引先名"], nyukinYmd = dat["入金年月日"], denpyoNo = dat["伝票番号"], bunruiName = dat["取引区分名"], kingaku = (decimal)dat["入金額"], kijitsu = dat["手形期日"], bikou = dat["備考"] }).ToList(); // linqで税抜合計金額、消費税、税込合計金額の合計算出 decimal decKingaku = outDataAll.Select(gokei => gokei.kingaku).Sum(); // リストをデータテーブルに変換 DataTable dtChkList = pdf.ConvertToDataTable(outDataAll); int maxRowCnt = dtChkList.Rows.Count + 1; int maxColCnt = dtChkList.Columns.Count; int pageCnt = 0; // ページ(シート枚数)カウント int rowCnt = 1; // datatable処理行カウント int xlsRowCnt = 4; // Excel出力行カウント(開始は出力行) int maxPage = 0; // 最大ページ数 // ページ数計算 double page = 1.0 * maxRowCnt / 29; double decimalpart = page % 1; if (decimalpart != 0) { // 小数点以下が0でない場合、+1 maxPage = (int)Math.Floor(page) + 1; } else { maxPage = (int)page; } // ClosedXMLで1行ずつExcelに出力 foreach (DataRow drSiireCheck in dtChkList.Rows) { // 1ページ目のシート作成 if (rowCnt == 1) { pageCnt++; // タイトル出力(中央揃え、セル結合) IXLCell titleCell = headersheet.Cell("A1"); titleCell.Value = "入金チェックリスト"; titleCell.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center; titleCell.Style.Font.FontSize = 16; headersheet.Range("A1", "H1").Merge(); // 入力日、伝票年月日出力(A2のセル) IXLCell unitCell = headersheet.Cell("A2"); unitCell.Value = "入力日:" + string.Format("{0:yyyy年MM月dd日}", DateTime.Parse(lstItem[0])) + " ~ " + string.Format("{0:yyyy年MM月dd日}", DateTime.Parse(lstItem[1])) + " 伝票年月日:" + string.Format("{0:yyyy年MM月dd日}", DateTime.Parse(lstItem[2])) + " ~ " + string.Format("{0:yyyy年MM月dd日}", DateTime.Parse(lstItem[3])) + " 得意先コード:" + lstItem[5] + " ~ " + lstItem[6]; unitCell.Style.Font.FontSize = 10; // ヘッダー出力(3行目のセル) headersheet.Cell("A3").Value = "コード"; headersheet.Cell("B3").Value = "得意先名"; headersheet.Cell("C3").Value = "入金日"; headersheet.Cell("D3").Value = "伝票番号"; headersheet.Cell("E3").Value = "取引区分"; headersheet.Cell("F3").Value = "入金額"; headersheet.Cell("G3").Value = "手形期日"; headersheet.Cell("H3").Value = "備 考"; // ヘッダー列 headersheet.Range("A3", "H3").Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center; // セルの周囲に罫線を引く headersheet.Range("A3", "H3").Style .Border.SetTopBorder(XLBorderStyleValues.Thin) .Border.SetBottomBorder(XLBorderStyleValues.Thin) .Border.SetLeftBorder(XLBorderStyleValues.Thin) .Border.SetRightBorder(XLBorderStyleValues.Thin); // セルの背景色 headersheet.Range("A3", "H3").Style.Fill.BackgroundColor = XLColor.LightGray; // 列幅の指定 headersheet.Column(1).Width = 5; headersheet.Column(2).Width = 38; headersheet.Column(3).Width = 11; headersheet.Column(4).Width = 8; headersheet.Column(5).Width = 10; headersheet.Column(6).Width = 12; headersheet.Column(7).Width = 11; headersheet.Column(8).Width = 38; // 印刷体裁(A4横、印刷範囲) headersheet.PageSetup.PaperSize = XLPaperSize.A4Paper; headersheet.PageSetup.PageOrientation = XLPageOrientation.Landscape; // ヘッダー部の指定(番号) headersheet.PageSetup.Header.Left.AddText("(№5)"); // ヘッダーシートのコピー、ヘッダー部の指定 pdf.sheetCopy(ref workbook, ref headersheet, ref currentsheet, pageCnt, maxPage, strNow); } // 1セルずつデータ出力 for (int colCnt = 1; colCnt <= maxColCnt; colCnt++) { string str = drSiireCheck[colCnt - 1].ToString(); // 入金日、手形期日セルの場合 if (colCnt == 3 || colCnt == 7) { currentsheet.Cell(xlsRowCnt, colCnt).Style.DateFormat.SetFormat("yyyy/MM/dd"); currentsheet.Cell(xlsRowCnt, colCnt).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center; } // 伝票番号セルの処理 if (colCnt == 4) { currentsheet.Cell(xlsRowCnt, colCnt).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Right; } // 入金額セルの処理 if (colCnt == 6) { // 3桁毎に","を挿入する currentsheet.Cell(xlsRowCnt, colCnt).Style.NumberFormat.SetFormat("#,##0"); currentsheet.Cell(xlsRowCnt, colCnt).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Right; } // 備考セルの場合 if (colCnt == 8) { currentsheet.Cell(xlsRowCnt, colCnt).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Left; str = "'" + str; } currentsheet.Cell(xlsRowCnt, colCnt).Value = str; } // 1行分のセルの周囲に罫線を引く currentsheet.Range(xlsRowCnt, 1, xlsRowCnt, 8).Style .Border.SetTopBorder(XLBorderStyleValues.Thin) .Border.SetBottomBorder(XLBorderStyleValues.Thin) .Border.SetLeftBorder(XLBorderStyleValues.Thin) .Border.SetRightBorder(XLBorderStyleValues.Thin); // 29行毎(ヘッダーを除いた行数)にシート作成 if (xlsRowCnt == 32) { pageCnt++; if (pageCnt <= maxPage) { xlsRowCnt = 3; // ヘッダーシートのコピー、ヘッダー部の指定 pdf.sheetCopy(ref workbook, ref headersheet, ref currentsheet, pageCnt, maxPage, strNow); } } rowCnt++; xlsRowCnt++; } // 最終行を出力した後、合計行を出力 if (dtChkList.Rows.Count > 0) { // セル結合 currentsheet.Range(xlsRowCnt, 1, xlsRowCnt, 5).Merge(); currentsheet.Cell(xlsRowCnt, 1).Value = "◆◆◆ 合 計 ◆◆◆"; currentsheet.Cell(xlsRowCnt, 1).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center; // 入金額 currentsheet.Cell(xlsRowCnt, 6).Value = string.Format("{0:#,0}", decKingaku); currentsheet.Cell(xlsRowCnt, 6).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Right; // 1行分のセルの周囲に罫線を引く currentsheet.Range(xlsRowCnt, 1, xlsRowCnt, 8).Style .Border.SetTopBorder(XLBorderStyleValues.Thin) .Border.SetBottomBorder(XLBorderStyleValues.Thin) .Border.SetLeftBorder(XLBorderStyleValues.Thin) .Border.SetRightBorder(XLBorderStyleValues.Thin); } // ヘッダーシート削除 headersheet.Delete(); // workbookを保存 string strOutXlsFile = strWorkPath + strDateTime + ".xlsx"; workbook.SaveAs(strOutXlsFile); // workbookを解放 workbook.Dispose(); // PDF化の処理 return(pdf.createPdf(strOutXlsFile, strDateTime, 1)); } catch { throw; } finally { // Workフォルダの全ファイルを取得 string[] files = System.IO.Directory.GetFiles(strWorkPath, "*", System.IO.SearchOption.AllDirectories); // Workフォルダ内のファイル削除 foreach (string filepath in files) { //File.Delete(filepath); } } }
/// ----------------------------------------------------------------------------- /// <summary> /// DataTableをもとにxlsxファイルを作成しPDF化</summary> /// <param name="dtUriage"> /// 売上管理表のデータテーブル</param> /// ----------------------------------------------------------------------------- public string dbToPdf(DataTable dtUriage, List <string> lstItem, string pr, int num) { string strWorkPath = System.Configuration.ConfigurationManager.AppSettings["workpath"]; string strDateTime = DateTime.Now.ToString("yyyyMMddHHmmss"); string strNow = DateTime.Now.ToString("yyyy/MM/dd HH:mm:ss"); Microsoft.Office.Interop.Excel.Application objExcel = null; Microsoft.Office.Interop.Excel.Workbooks objWorkBooks = null; Microsoft.Office.Interop.Excel.Workbook objWorkBook = null; Microsoft.Office.Interop.Excel.Worksheet objWorkSheet = null; Microsoft.Office.Interop.Excel.Range objRange = null; try { CreatePdf pdf = new CreatePdf(); // ワークブックのデフォルトフォント、フォントサイズの指定 XLWorkbook.DefaultStyle.Font.FontName = "MS 明朝"; XLWorkbook.DefaultStyle.Font.FontSize = 9; // excelのインスタンス生成 XLWorkbook workbook = new XLWorkbook(XLEventTracking.Disabled); IXLWorksheet worksheet = workbook.Worksheets.Add("Header"); IXLWorksheet headersheet = worksheet; // ヘッダーシート IXLWorksheet currentsheet = worksheet; // 処理中シート // Linqで必要なデータをselect var outDataAll = dtUriage.AsEnumerable() .Select(dat => new { eigyoCd = dat["営業所コード"], eigyoName = dat["営業所名"], groupCd = dat["グループコード"], groupName = dat["グループ名"], tantoCd = dat["担当者コード"], tantoName = dat["担当者名"], uriage = (decimal)dat["売上額"], arari = (decimal)dat["粗利額"], arariRitsu = (decimal)dat["粗利率"], getsumatsuUriage = (decimal)dat["月末迄受注残売上"], getsumatsuArari = (decimal)dat["月末迄受注残粗利"], yokugetsuUriage = (decimal)dat["翌月以降受注残売上"], yokugetsuArari = (decimal)dat["翌月以降受注残粗利"], getsumatsuUrikake = (decimal)dat["月末売掛金残"], nyukin = (decimal)dat["当月入金額"], uriageMokuhyo = (decimal)dat["年間売上目標"], tassei = (decimal)dat["達成率"] }).ToList(); // linqで合計算出 decimal[] decKingaku = new decimal[11]; decKingaku[0] = outDataAll.Select(gokei => gokei.uriage).Sum(); decKingaku[1] = outDataAll.Select(gokei => gokei.arari).Sum(); decKingaku[2] = 0; decKingaku[3] = outDataAll.Select(gokei => gokei.getsumatsuUriage).Sum(); decKingaku[4] = outDataAll.Select(gokei => gokei.getsumatsuArari).Sum(); decKingaku[5] = outDataAll.Select(gokei => gokei.yokugetsuUriage).Sum(); decKingaku[6] = outDataAll.Select(gokei => gokei.yokugetsuArari).Sum(); decKingaku[7] = outDataAll.Select(gokei => gokei.getsumatsuUrikake).Sum(); decKingaku[8] = outDataAll.Select(gokei => gokei.nyukin).Sum(); decKingaku[9] = outDataAll.Select(gokei => gokei.uriageMokuhyo).Sum(); decKingaku[10] = 0; // グループ計 var groupGoukei = from tbl in dtUriage.AsEnumerable() group tbl by new { eigyoCd = tbl.Field <string>("営業所コード"), groupCd = tbl.Field <string>("グループコード") } into g select new { section = g.Key, count = g.Count(), uriage = g.Sum(p => p.Field <decimal>("売上額")), arari = g.Sum(p => p.Field <decimal>("粗利額")), getsumatsuUriage = g.Sum(p => p.Field <decimal>("月末迄受注残売上")), getsumatsuArari = g.Sum(p => p.Field <decimal>("月末迄受注残粗利")), yokugetsuUriage = g.Sum(p => p.Field <decimal>("翌月以降受注残売上")), yokugetsuArari = g.Sum(p => p.Field <decimal>("翌月以降受注残粗利")), getsumatsuUrikake = g.Sum(p => p.Field <decimal>("月末売掛金残")), nyukin = g.Sum(p => p.Field <decimal>("当月入金額")), uriageMokuhyo = g.Sum(p => p.Field <decimal>("年間売上目標")) }; // グループ計の合計算出 decimal[,] decKingakuGroup = new decimal[groupGoukei.Count(), 11]; for (int cnt = 0; cnt < groupGoukei.Count(); cnt++) { decKingakuGroup[cnt, 0] = groupGoukei.ElementAt(cnt).uriage; decKingakuGroup[cnt, 1] = groupGoukei.ElementAt(cnt).arari; decKingakuGroup[cnt, 2] = 0; decKingakuGroup[cnt, 3] = groupGoukei.ElementAt(cnt).getsumatsuUriage; decKingakuGroup[cnt, 4] = groupGoukei.ElementAt(cnt).getsumatsuArari; decKingakuGroup[cnt, 5] = groupGoukei.ElementAt(cnt).yokugetsuUriage; decKingakuGroup[cnt, 6] = groupGoukei.ElementAt(cnt).yokugetsuArari; decKingakuGroup[cnt, 7] = groupGoukei.ElementAt(cnt).getsumatsuUrikake; decKingakuGroup[cnt, 8] = groupGoukei.ElementAt(cnt).nyukin; decKingakuGroup[cnt, 9] = groupGoukei.ElementAt(cnt).uriageMokuhyo; decKingakuGroup[cnt, 10] = 0; } // 営業所計 var eigyoGoukei = from tbl in dtUriage.AsEnumerable() group tbl by tbl.Field <string>("営業所コード") into g select new { section = g.Key, count = g.Count(), uriage = g.Sum(p => p.Field <decimal>("売上額")), arari = g.Sum(p => p.Field <decimal>("粗利額")), getsumatsuUriage = g.Sum(p => p.Field <decimal>("月末迄受注残売上")), getsumatsuArari = g.Sum(p => p.Field <decimal>("月末迄受注残粗利")), yokugetsuUriage = g.Sum(p => p.Field <decimal>("翌月以降受注残売上")), yokugetsuArari = g.Sum(p => p.Field <decimal>("翌月以降受注残粗利")), getsumatsuUrikake = g.Sum(p => p.Field <decimal>("月末売掛金残")), nyukin = g.Sum(p => p.Field <decimal>("当月入金額")), uriageMokuhyo = g.Sum(p => p.Field <decimal>("年間売上目標")) }; // 営業所計の合計算出 decimal[,] decKingakuEigyo = new decimal[eigyoGoukei.Count(), 11]; for (int cnt = 0; cnt < eigyoGoukei.Count(); cnt++) { decKingakuEigyo[cnt, 0] = eigyoGoukei.ElementAt(cnt).uriage; decKingakuEigyo[cnt, 1] = eigyoGoukei.ElementAt(cnt).arari; decKingakuEigyo[cnt, 2] = 0; decKingakuEigyo[cnt, 3] = eigyoGoukei.ElementAt(cnt).getsumatsuUriage; decKingakuEigyo[cnt, 4] = eigyoGoukei.ElementAt(cnt).getsumatsuArari; decKingakuEigyo[cnt, 5] = eigyoGoukei.ElementAt(cnt).yokugetsuUriage; decKingakuEigyo[cnt, 6] = eigyoGoukei.ElementAt(cnt).yokugetsuArari; decKingakuEigyo[cnt, 7] = eigyoGoukei.ElementAt(cnt).getsumatsuUrikake; decKingakuEigyo[cnt, 8] = eigyoGoukei.ElementAt(cnt).nyukin; decKingakuEigyo[cnt, 9] = eigyoGoukei.ElementAt(cnt).uriageMokuhyo; decKingakuEigyo[cnt, 10] = 0; } // リストをデータテーブルに変換 DataTable dtChkList = pdf.ConvertToDataTable(outDataAll); int maxRowCnt = dtChkList.Rows.Count; int maxColCnt = dtChkList.Columns.Count; int pageCnt = 0; // ページ(シート枚数)カウント int rowCnt = 1; // datatable処理行カウント int xlsRowCnt = 5; // Excel出力行カウント(開始は出力行) int maxPage = 0; // 最大ページ数 // ページ数計算 maxRowCnt += groupGoukei.Count() * 2 + eigyoGoukei.Count() + 1; double page = 1.0 * maxRowCnt / 34; double decimalpart = page % 1; if (decimalpart != 0) { // 小数点以下が0でない場合、+1 maxPage = (int)Math.Floor(page) + 1; } else { maxPage = (int)page; } int groupCnt = 0; int groupRowCnt = 0; int eigyoCnt = 0; int eigyoRowCnt = 0; // ClosedXMLで1行ずつExcelに出力 foreach (DataRow drUriage in dtChkList.Rows) { // 1ページ目のシート作成 if (rowCnt == 1) { pageCnt++; // タイトル出力(中央揃え、セル結合) IXLCell titleCell = headersheet.Cell("A1"); titleCell.Value = "担当者別売上管理表"; titleCell.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center; titleCell.Style.Font.FontSize = 16; headersheet.Range("A1", "N1").Merge(); // 開始年月日、終了年月日出力(A3のセル) IXLCell unitCell = headersheet.Cell("A3"); unitCell.Value = "期間::" + string.Format("{0:yyyy年MM月dd日}", DateTime.Parse(lstItem[0])) + "~" + string.Format("{0:yyyy年MM月dd日}", DateTime.Parse(lstItem[1])); unitCell.Style.Font.FontSize = 10; // ヘッダー出力(3行目のセル) headersheet.Cell("G3").Value = "指定期間内受注残"; headersheet.Range("G3", "H3").Merge(); headersheet.Cell("I3").Value = "指定期間以降受注残"; headersheet.Range("I3", "J3").Merge(); // ヘッダー出力(4行目のセル) headersheet.Cell("A4").Value = "営業所名"; headersheet.Cell("B4").Value = "グループ名"; headersheet.Cell("C4").Value = "担当者名"; headersheet.Cell("D4").Value = "売上額"; headersheet.Cell("E4").Value = "粗利額"; headersheet.Cell("F4").Value = "粗利率"; headersheet.Cell("G4").Value = "金 額"; headersheet.Cell("H4").Value = "粗 利"; headersheet.Cell("I4").Value = "金 額"; headersheet.Cell("J4").Value = "粗 利"; headersheet.Cell("K4").Value = "月末売掛金残"; headersheet.Cell("L4").Value = "当月入金額"; headersheet.Cell("M4").Value = "月目標"; headersheet.Cell("N4").Value = "期間達成率"; // ヘッダー列 headersheet.Range("G3", "J3").Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center; headersheet.Range("A4", "N4").Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center; // セルの周囲に罫線を引く headersheet.Range("G3", "J3").Style .Border.SetTopBorder(XLBorderStyleValues.Thin) .Border.SetBottomBorder(XLBorderStyleValues.Thin) .Border.SetLeftBorder(XLBorderStyleValues.Thin) .Border.SetRightBorder(XLBorderStyleValues.Thin); headersheet.Range("A4", "N4").Style .Border.SetTopBorder(XLBorderStyleValues.Thin) .Border.SetBottomBorder(XLBorderStyleValues.Thin) .Border.SetLeftBorder(XLBorderStyleValues.Thin) .Border.SetRightBorder(XLBorderStyleValues.Thin); // セルの背景色 headersheet.Range("G3", "J3").Style.Fill.BackgroundColor = XLColor.LightGray; headersheet.Range("A4", "N4").Style.Fill.BackgroundColor = XLColor.LightGray; // 列幅の指定 //headersheet.Column(1).Width = 10; headersheet.Column(1).Width = 8.1; //headersheet.Column(2).Width = 10; headersheet.Column(2).Width = 10; //headersheet.Column(3).Width = 14; headersheet.Column(3).Width = 12.7; //headersheet.Column(4).Width = 12; headersheet.Column(4).Width = 12.7; //headersheet.Column(5).Width = 12; headersheet.Column(5).Width = 12.7; //headersheet.Column(6).Width = 8; headersheet.Column(6).Width = 7; //for (int cnt = 7; cnt <= 13; cnt++) //{ // headersheet.Column(cnt).Width = 12; //} headersheet.Column(7).Width = 12.7; headersheet.Column(8).Width = 12.7; headersheet.Column(9).Width = 12.7; headersheet.Column(10).Width = 12.7; headersheet.Column(11).Width = 12.7; headersheet.Column(12).Width = 12.7; headersheet.Column(13).Width = 12.7; headersheet.Column(14).Width = 10.1; // 印刷体裁(B4横、印刷範囲) //headersheet.PageSetup.PaperSize = XLPaperSize.B4Paper; headersheet.PageSetup.PaperSize = XLPaperSize.A4Paper; headersheet.PageSetup.PageOrientation = XLPageOrientation.Landscape; headersheet.PageSetup.Margins.Left = 0.2; headersheet.PageSetup.Margins.Right = 0.2; headersheet.PageSetup.Margins.Top = 0.4; headersheet.PageSetup.Margins.Bottom = 0.3; for (int ir = 3; ir < 39; ir++) { headersheet.Row(ir).Height = 13.2; } // ヘッダー部の指定(番号) headersheet.PageSetup.Header.Left.AddText("(№13)"); // ヘッダーシートのコピー、ヘッダー部の指定 pdf.sheetCopy(ref workbook, ref headersheet, ref currentsheet, pageCnt, maxPage, strNow); } // 営業所名出力 if (eigyoRowCnt == 0) { currentsheet.Cell(xlsRowCnt, 1).Value = drUriage[1]; } // グループ名出力 if (groupRowCnt == 0) { currentsheet.Cell(xlsRowCnt, 2).Value = drUriage[3]; // 1行分のセルの周囲に罫線を引く currentsheet.Range(xlsRowCnt, 1, xlsRowCnt, 14).Style .Border.SetTopBorder(XLBorderStyleValues.Thin) .Border.SetBottomBorder(XLBorderStyleValues.Thin) .Border.SetLeftBorder(XLBorderStyleValues.Thin) .Border.SetRightBorder(XLBorderStyleValues.Thin); xlsRowCnt++; } // 35行毎(ヘッダーを除いた行数)にシート作成 if (xlsRowCnt == 39) { pageCnt++; xlsRowCnt = 5; // ヘッダーシートのコピー、ヘッダー部の指定 pdf.sheetCopy(ref workbook, ref headersheet, ref currentsheet, pageCnt, maxPage, strNow); } // 1セルずつデータ出力 for (int colCnt = 5; colCnt < maxColCnt; colCnt++) { string str = drUriage[colCnt].ToString(); // 金額セルの処理 if (colCnt == 6 || colCnt == 7 || colCnt >= 9 && colCnt <= 15) { // 3桁毎に","を挿入する str = string.Format("{0:#,0}", decimal.Parse(str)); currentsheet.Cell(xlsRowCnt, colCnt - 2).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Right; } // 粗利率、期間達成率セルの処理 if (colCnt == 8 || colCnt == 16) { // 3桁毎に","を挿入する、小数点第2位まで currentsheet.Cell(xlsRowCnt, colCnt - 2).Style.NumberFormat.SetFormat("#,##0.00"); currentsheet.Cell(xlsRowCnt, colCnt - 2).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Right; } currentsheet.Cell(xlsRowCnt, colCnt - 2).Value = str; } // 1行分のセルの周囲に罫線を引く currentsheet.Range(xlsRowCnt, 1, xlsRowCnt, 14).Style .Border.SetTopBorder(XLBorderStyleValues.Thin) .Border.SetBottomBorder(XLBorderStyleValues.Thin) .Border.SetLeftBorder(XLBorderStyleValues.Thin) .Border.SetRightBorder(XLBorderStyleValues.Thin); // 35行毎(ヘッダーを除いた行数)にシート作成 if (xlsRowCnt == 38) { pageCnt++; xlsRowCnt = 4; // ヘッダーシートのコピー、ヘッダー部の指定 pdf.sheetCopy(ref workbook, ref headersheet, ref currentsheet, pageCnt, maxPage, strNow); } // グループ計を出力 groupRowCnt++; if (groupGoukei.ElementAt(groupCnt).count == groupRowCnt) { xlsRowCnt++; // セル結合 currentsheet.Range(xlsRowCnt, 2, xlsRowCnt, 3).Merge(); currentsheet.Cell(xlsRowCnt, 2).Value = " ◆グループ計◆"; for (int cnt = 0; cnt < 11; cnt++) { // 粗利率の場合 if (cnt == 2) { // 売上額が0でない場合 if (decKingakuGroup[groupCnt, 0] != 0) { decKingakuGroup[groupCnt, cnt] = decKingakuGroup[groupCnt, 1] / decKingakuGroup[groupCnt, 0] * 100; } else { decKingakuGroup[groupCnt, cnt] = 0; } currentsheet.Cell(xlsRowCnt, cnt + 4).Value = decKingakuGroup[groupCnt, cnt].ToString(); currentsheet.Cell(xlsRowCnt, cnt + 4).Style.NumberFormat.SetFormat("#,##0.00"); } // 期間達成率の場合 else if (cnt == 10) { // 年間売上目標が0でない場合 if (decKingakuGroup[groupCnt, 9] != 0) { decKingakuGroup[groupCnt, cnt] = decKingakuGroup[groupCnt, 0] / decKingakuGroup[groupCnt, 9] * 100; } else { decKingakuGroup[groupCnt, cnt] = 0; } currentsheet.Cell(xlsRowCnt, cnt + 4).Value = decKingakuGroup[groupCnt, cnt].ToString(); currentsheet.Cell(xlsRowCnt, cnt + 4).Style.NumberFormat.SetFormat("#,##0.00"); } else { currentsheet.Cell(xlsRowCnt, cnt + 4).Value = string.Format("{0:#,0}", decKingakuGroup[groupCnt, cnt]); } currentsheet.Cell(xlsRowCnt, cnt + 4).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Right; } // 1行分のセルの周囲に罫線を引く currentsheet.Range(xlsRowCnt, 1, xlsRowCnt, 14).Style .Border.SetTopBorder(XLBorderStyleValues.Thin) .Border.SetBottomBorder(XLBorderStyleValues.Thin) .Border.SetLeftBorder(XLBorderStyleValues.Thin) .Border.SetRightBorder(XLBorderStyleValues.Thin); groupCnt++; groupRowCnt = 0; } // 35行毎(ヘッダーを除いた行数)にシート作成 if (xlsRowCnt == 38) { pageCnt++; xlsRowCnt = 4; // ヘッダーシートのコピー、ヘッダー部の指定 pdf.sheetCopy(ref workbook, ref headersheet, ref currentsheet, pageCnt, maxPage, strNow); } // 営業所計を出力 eigyoRowCnt++; if (eigyoGoukei.ElementAt(eigyoCnt).count == eigyoRowCnt) { xlsRowCnt++; // セル結合 currentsheet.Range(xlsRowCnt, 2, xlsRowCnt, 3).Merge(); currentsheet.Cell(xlsRowCnt, 2).Value = " ◆営業所計◆"; for (int cnt = 0; cnt < 11; cnt++) { // 粗利率の場合 if (cnt == 2) { // 売上額が0でない場合 if (decKingakuEigyo[eigyoCnt, 0] != 0) { decKingakuEigyo[eigyoCnt, cnt] = decKingakuEigyo[eigyoCnt, 1] / decKingakuEigyo[eigyoCnt, 0] * 100; } else { decKingakuEigyo[eigyoCnt, cnt] = 0; } currentsheet.Cell(xlsRowCnt, cnt + 4).Value = decKingakuEigyo[eigyoCnt, cnt].ToString(); currentsheet.Cell(xlsRowCnt, cnt + 4).Style.NumberFormat.SetFormat("#,##0.00"); } // 期間達成率の場合 else if (cnt == 10) { // 年間売上目標が0でない場合 if (decKingakuEigyo[eigyoCnt, 9] != 0) { decKingakuEigyo[eigyoCnt, cnt] = decKingakuEigyo[eigyoCnt, 0] / decKingakuEigyo[eigyoCnt, 9] * 100; } else { decKingakuEigyo[eigyoCnt, cnt] = 0; } currentsheet.Cell(xlsRowCnt, cnt + 4).Value = decKingakuEigyo[eigyoCnt, cnt].ToString(); currentsheet.Cell(xlsRowCnt, cnt + 4).Style.NumberFormat.SetFormat("#,##0.00"); } else { currentsheet.Cell(xlsRowCnt, cnt + 4).Value = string.Format("{0:#,0}", decKingakuEigyo[eigyoCnt, cnt]); } currentsheet.Cell(xlsRowCnt, cnt + 4).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Right; } // 1行分のセルの周囲に罫線を引く currentsheet.Range(xlsRowCnt, 1, xlsRowCnt, 14).Style .Border.SetTopBorder(XLBorderStyleValues.Thin) .Border.SetBottomBorder(XLBorderStyleValues.Thin) .Border.SetLeftBorder(XLBorderStyleValues.Thin) .Border.SetRightBorder(XLBorderStyleValues.Thin); eigyoCnt++; eigyoRowCnt = 0; } // 35行毎(ヘッダーを除いた行数)にシート作成 if (xlsRowCnt == 38) { pageCnt++; xlsRowCnt = 4; // ヘッダーシートのコピー、ヘッダー部の指定 pdf.sheetCopy(ref workbook, ref headersheet, ref currentsheet, pageCnt, maxPage, strNow); } rowCnt++; xlsRowCnt++; } // 最終行を出力した後、合計行を出力 if (dtChkList.Rows.Count > 0) { // セル結合 currentsheet.Range(xlsRowCnt, 2, xlsRowCnt, 3).Merge(); currentsheet.Cell(xlsRowCnt, 2).Value = " ◆総合計◆"; for (int cnt = 0; cnt < 11; cnt++) { // 粗利率の場合 if (cnt == 2) { // 売上額が0でない場合 if (decKingaku[0] != 0) { decKingaku[cnt] = decKingaku[1] / decKingaku[0] * 100; } else { decKingaku[cnt] = 0; } currentsheet.Cell(xlsRowCnt, cnt + 4).Value = decKingaku[cnt].ToString(); currentsheet.Cell(xlsRowCnt, cnt + 4).Style.NumberFormat.SetFormat("#,##0.00"); } // 期間達成率の場合 else if (cnt == 10) { // 年間売上目標が0でない場合 if (decKingaku[9] != 0) { decKingaku[cnt] = decKingaku[0] / decKingaku[9] * 100; } else { decKingaku[cnt] = 0; } currentsheet.Cell(xlsRowCnt, cnt + 4).Value = decKingaku[cnt].ToString(); currentsheet.Cell(xlsRowCnt, cnt + 4).Style.NumberFormat.SetFormat("#,##0.00"); } else { currentsheet.Cell(xlsRowCnt, cnt + 4).Value = string.Format("{0:#,0}", decKingaku[cnt]); } currentsheet.Cell(xlsRowCnt, cnt + 4).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Right; } // 1行分のセルの周囲に罫線を引く currentsheet.Range(xlsRowCnt, 1, xlsRowCnt, 14).Style .Border.SetTopBorder(XLBorderStyleValues.Thin) .Border.SetBottomBorder(XLBorderStyleValues.Thin) .Border.SetLeftBorder(XLBorderStyleValues.Thin) .Border.SetRightBorder(XLBorderStyleValues.Thin); } // ヘッダーシート削除 headersheet.Delete(); // workbookを保存 string strOutXlsFile = strWorkPath + strDateTime + ".xlsx"; workbook.SaveAs(strOutXlsFile); // workbookを解放 workbook.Dispose(); // PDF化の処理 //return pdf.createPdf(strOutXlsFile, strDateTime, 0); objExcel = new Microsoft.Office.Interop.Excel.Application(); objExcel.Visible = false; if (pr != null) { objExcel.WindowState = Microsoft.Office.Interop.Excel.XlWindowState.xlMinimized; } else { objExcel.WindowState = Microsoft.Office.Interop.Excel.XlWindowState.xlMaximized; } objExcel.DisplayAlerts = false; objWorkBooks = objExcel.Workbooks; String strP = System.IO.Path.GetFullPath(strOutXlsFile); objWorkBook = objWorkBooks.Open(strP, //_xslFile, // FileName:ファイル名 Type.Missing, // UpdateLinks:ファイル内の外部参照の更新方法 Type.Missing, // ReadOnly:ReadOnlyにするかどうか Type.Missing, // Format: テキストファイルを開く場合に区切り文字を指定する Type.Missing, // Password:開く際にパスワードがある場合にパスワードを入力 Type.Missing, // WriteResPassword:書き込む際にパスワードがある場合にパスワードを入力 Type.Missing, // IgnoreReadOnlyRecommended:[読み取り専用を推奨する]チェックがオンの場合でも[読み取り専用を推奨する]メッセージを非表示 Type.Missing, // Origin:テキストファイルの場合、プラットフォームを指定 Type.Missing, // Delimiter:テキストファイルで且つ引数Formatが6の場合に区切り文字を指定 Type.Missing, // Editable:Excel4.0アドインの場合、アドインウィンドウを出すか指定 Type.Missing, // Notify:ファイルが読み取りor書き込みモードで開けない場合に通知リストに追加するか指定 Type.Missing, // Converter:ファイルを開くときに最初に使用するファイルコンバーターのインデックス番号を指定 Type.Missing, // AddToMru:最近使用したファイルの一覧にブックを追加するか指定 Type.Missing, // Local:Excel言語設定に合わせてファイルを保存するか指定 Type.Missing // CorruptLoad:使用できる定数は[xlNormalLoad][xlRepairFile][xlExtractData]。指定がない場合のは[xlNormalLoad]になりOMを通じて開始するときに回復は行われません。 ); if (pr != null) { for (int ip = 0; ip < num; ip++) { objWorkBook.PrintOut(Type.Missing, // From:印刷開始のページ番号 Type.Missing, // To:印刷終了のページ番号 1, // Copies:印刷部数 Type.Missing, // Preview:印刷プレビューをするか指定 pr, // ActivePrinter:プリンターの名称 Type.Missing, // PrintToFile:ファイル出力をするか指定 true, // Collate:部単位で印刷するか指定 Type.Missing // PrToFileName :出力先ファイルの名前を指定するかどうか ); } } else { objWorkBook.PrintOut(Type.Missing, // From:印刷開始のページ番号 Type.Missing, // To:印刷終了のページ番号 1, // Copies:印刷部数 true, // Preview:印刷プレビューをするか指定 pr, // ActivePrinter:プリンターの名称 Type.Missing, // PrintToFile:ファイル出力をするか指定 true, // Collate:部単位で印刷するか指定 Type.Missing // PrToFileName :出力先ファイルの名前を指定するかどうか ); objExcel.Visible = true; objWorkBook.Activate(); } return(""); } catch { throw; } finally { // EXCEL終了処理 if (objWorkSheet != null) { Marshal.ReleaseComObject(objWorkSheet); // オブジェクト参照を解放 objWorkSheet = null; // オブジェクト解放 } if (objWorkBook != null) { objWorkBook.Close(false, Type.Missing, Type.Missing); //ファイルを閉じる Marshal.ReleaseComObject(objWorkBook); // オブジェクト参照を解放 objWorkBook = null; // オブジェクト解放 } if (objWorkBooks != null) { Marshal.ReleaseComObject(objWorkBooks); // オブジェクト参照を解放 objWorkBooks = null; // オブジェクト解放 } if (objExcel != null) { objExcel.Quit(); // EXCELを閉じる Marshal.ReleaseComObject(objExcel); // オブジェクト参照を解放 objExcel = null; // オブジェクト解放 } System.GC.Collect(); // Workフォルダの全ファイルを取得 string[] files = System.IO.Directory.GetFiles(strWorkPath, "*", System.IO.SearchOption.AllDirectories); // Workフォルダ内のファイル削除 foreach (string filepath in files) { //File.Delete(filepath); } } }
/// ----------------------------------------------------------------------------- /// <summary> /// DataTableをもとにxlsxファイルを作成しPDF化</summary> /// <param name="dtSetCd_B_Input"> /// 担当者のデータテーブル</param> /// ----------------------------------------------------------------------------- public string dbToPdf(DataTable dtSetCd_B_Input) { string strWorkPath = System.Configuration.ConfigurationManager.AppSettings["workpath"]; string strDateTime = DateTime.Now.ToString("yyyyMMddHHmmss"); string strNow = DateTime.Now.ToString("yyyy/MM/dd HH:mm:ss"); try { CreatePdf pdf = new CreatePdf(); // ワークブックのデフォルトフォント、フォントサイズの指定 XLWorkbook.DefaultStyle.Font.FontName = "MS ゴシック"; XLWorkbook.DefaultStyle.Font.FontSize = 9; // excelのインスタンス生成 XLWorkbook workbook = new XLWorkbook(XLEventTracking.Disabled); IXLWorksheet worksheet = workbook.Worksheets.Add("Header"); IXLWorksheet headersheet = worksheet; // ヘッダーシート IXLWorksheet currentsheet = worksheet; // 処理中シート //Linqで必要なデータをselect var outDataAll = dtSetCd_B_Input.AsEnumerable() .Select(dat => new { tantoushaCd = dat["担当者コード"], tantoushaName = dat["担当者名"], loginID = dat["営業所名"], eigyoshoCd = dat["グループ名"], chubanmoji = dat["年間売上目標"], groupCd = dat["注番文字"], }).ToList(); //リストをデータテーブルに変換 DataTable dtChkList = pdf.ConvertToDataTable(outDataAll); int maxRowCnt = dtChkList.Rows.Count + 1; int maxColCnt = dtChkList.Columns.Count; int pageCnt = 0; // ページ(シート枚数)カウント int rowCnt = 1; // datatable処理行カウント int xlsRowCnt = 4; // Excel出力行カウント(開始は出力行) int maxPage = 0; // 最大ページ数 //ページ数計算 double page = 1.0 * maxRowCnt / 44; double decimalpart = page % 1; if (decimalpart != 0) { //小数点以下が0でない場合、+1 maxPage = (int)Math.Floor(page) + 1; } else { maxPage = (int)page; } //ClosedXMLで1行ずつExcelに出力 foreach (DataRow drSiireCheak in dtChkList.Rows) { //1ページ目のシート作成 if (rowCnt == 1) { pageCnt++; //タイトル出力(中央揃え、セル結合) IXLCell titleCell = headersheet.Cell("A1"); titleCell.Value = "担当者マスタリスト"; titleCell.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center; titleCell.Style.Font.FontSize = 16; headersheet.Range("A1", "C1").Merge(); //ヘッダー出力(表ヘッダー) headersheet.Cell("A3").Value = "コード"; headersheet.Cell("B3").Value = "担当者名"; headersheet.Cell("C3").Value = "営業所名"; headersheet.Cell("D3").Value = "グループ名"; headersheet.Cell("E3").Value = "年間目標金額"; headersheet.Cell("F3").Value = "注番文字"; //ヘッダー列 headersheet.Range("A3", "F3").Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center; // 列幅の指定 headersheet.Column(1).Width = 12; headersheet.Column(2).Width = 20; headersheet.Column(3).Width = 17; headersheet.Column(4).Width = 17; headersheet.Column(5).Width = 17; headersheet.Column(6).Width = 17; // セルの周囲に罫線を引く(細い方) headersheet.Range("A3", "C3").Style .Border.SetTopBorder(XLBorderStyleValues.Thin) .Border.SetBottomBorder(XLBorderStyleValues.Thin) .Border.SetLeftBorder(XLBorderStyleValues.Thin); //.Border.SetRightBorder(XLBorderStyleValues.Thin); // セルの周囲に罫線を引く(太い方) headersheet.Range("D3", "F3").Style .Border.SetTopBorder(XLBorderStyleValues.Medium) .Border.SetBottomBorder(XLBorderStyleValues.Medium) .Border.SetLeftBorder(XLBorderStyleValues.Medium) .Border.SetRightBorder(XLBorderStyleValues.Medium); // 印刷体裁(A4横、印刷範囲) headersheet.PageSetup.PaperSize = XLPaperSize.A4Paper; headersheet.PageSetup.PageOrientation = XLPageOrientation.Default; // ヘッダー部の指定(番号) headersheet.PageSetup.Header.Left.AddText("(№105)"); //ヘッダーシートのコピー、ヘッダー部の指定 pdf.sheetCopy(ref workbook, ref headersheet, ref currentsheet, pageCnt, maxPage, strNow); } // 1セルずつデータ出力 for (int colCnt = 1; colCnt <= maxColCnt; colCnt++) { string str = drSiireCheak[colCnt - 1].ToString(); currentsheet.Cell(xlsRowCnt, colCnt).Value = str; //コードのカラムの場合 if (colCnt == 1) { //二桁の0パディングをさせる currentsheet.Cell(xlsRowCnt, colCnt).Style.NumberFormat.SetFormat("0000"); //中心にする currentsheet.Cell(xlsRowCnt, colCnt).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center; } //年間目標金額のカラムの場合 if (colCnt == 5) { //二桁の0パディングをさせる currentsheet.Cell(xlsRowCnt, colCnt).Style.NumberFormat.SetFormat("#,0"); } string strKari = currentsheet.Cell(xlsRowCnt, colCnt).Value.ToString(); } // 1行分のセルの周囲に罫線を引く(細い方) currentsheet.Range(xlsRowCnt, 1, xlsRowCnt, 3).Style .Border.SetTopBorder(XLBorderStyleValues.Thin) .Border.SetBottomBorder(XLBorderStyleValues.Thin) .Border.SetLeftBorder(XLBorderStyleValues.Thin); // 1行分のセルの周囲に罫線を引く(太い方) currentsheet.Range(xlsRowCnt, 4, xlsRowCnt, 6).Style .Border.SetTopBorder(XLBorderStyleValues.Medium) .Border.SetBottomBorder(XLBorderStyleValues.Medium) .Border.SetLeftBorder(XLBorderStyleValues.Medium) .Border.SetRightBorder(XLBorderStyleValues.Medium); // 47行毎(ヘッダーを除いた行数)にシート作成 if (xlsRowCnt == 47) { pageCnt++; if (pageCnt <= maxPage) { xlsRowCnt = 3; // ヘッダーシートのコピー、ヘッダー部の指定 pdf.sheetCopy(ref workbook, ref headersheet, ref currentsheet, pageCnt, maxPage, strNow); } } rowCnt++; xlsRowCnt++; } // ヘッダーシート削除 headersheet.Delete(); // workbookを保存 string strOutXlsFile = strWorkPath + strDateTime + ".xlsx"; workbook.SaveAs(strOutXlsFile); // workbookを解放 workbook.Dispose(); // PDF化の処理 return(pdf.createPdf(strOutXlsFile, strDateTime, 1)); } catch (Exception ex) { throw; } finally { // Workフォルダの全ファイルを取得 string[] files = System.IO.Directory.GetFiles(strWorkPath, "*", System.IO.SearchOption.AllDirectories); // Workフォルダ内のファイル削除 foreach (string filepath in files) { //File.Delete(filepath); } } }
/// ----------------------------------------------------------------------------- /// <summary> /// DataTableをもとにxlsxファイルを作成しPDF化</summary> /// <param name="dtSeikyuMeisai"> /// 請求明細書のデータテーブル</param> /// ----------------------------------------------------------------------------- public string dbToPdf(DataTable dtSeikyuMeisai) { string strWorkPath = System.Configuration.ConfigurationManager.AppSettings["workpath"]; string strFilePath = "./Template/B0420_SeikyuMeisaishoPrint.xlsx"; string strDateTime = DateTime.Now.ToString("yyyyMMddHHmmss"); try { // excelのインスタンス生成 XLWorkbook workbook = new XLWorkbook(strFilePath, XLEventTracking.Disabled); IXLWorksheet templatesheet1 = workbook.Worksheet(1); // テンプレートシート IXLWorksheet templatesheet2 = workbook.Worksheet(2); // テンプレートシート(明細行のみ) IXLWorksheet currentsheet = null; // 処理中シート int pageCnt = 0; // ページ(シート枚数)カウント int xlsRowCnt = 21; // Excel出力行カウント(開始は出力行) Boolean blnSheetCreate = false; string strTokuisakiCd = ""; // ClosedXMLで1行ずつExcelに出力 foreach (DataRow drSeikyuMeisai in dtSeikyuMeisai.Rows) { // 得意先コードが前行と同じ場合、かつ、43行目になった場合、テンプレートシート(明細行のみ)作成 if (strTokuisakiCd.Equals(drSeikyuMeisai[0].ToString()) && xlsRowCnt == 43) { pageCnt++; xlsRowCnt = 15; blnSheetCreate = true; // テンプレートシート(明細行のみ)からコピー templatesheet2.CopyTo("Page" + pageCnt.ToString()); currentsheet = workbook.Worksheet(workbook.Worksheets.Count); } // 得意先コードが前行と違う場合、テンプレートシート作成 if (!strTokuisakiCd.Equals(drSeikyuMeisai[0].ToString())) { strTokuisakiCd = drSeikyuMeisai[0].ToString(); pageCnt++; xlsRowCnt = 21; blnSheetCreate = true; // テンプレートシートからコピー templatesheet1.CopyTo("Page" + pageCnt.ToString()); currentsheet = workbook.Worksheet(workbook.Worksheets.Count); currentsheet.Cell("A18").Value = drSeikyuMeisai[7].ToString(); // 前月御請求額 currentsheet.Cell("C18").Value = drSeikyuMeisai[8].ToString(); // 当月入金額 currentsheet.Cell("F18").Value = drSeikyuMeisai[9].ToString(); // 御支払残高 currentsheet.Cell("G18").Value = drSeikyuMeisai[10].ToString(); // 当月御買上額 currentsheet.Cell("H17").Value = drSeikyuMeisai[11].ToString(); // 内税 currentsheet.Cell("H18").Value = drSeikyuMeisai[12].ToString(); // 消費税額 currentsheet.Cell("I18").Value = drSeikyuMeisai[13].ToString(); // 当月御請求額 } // 最初の明細行の場合 if (blnSheetCreate) { blnSheetCreate = false; currentsheet.Cell("B4").Value = drSeikyuMeisai[2].ToString(); // 郵便番号 currentsheet.Cell("B6").Value = drSeikyuMeisai[3].ToString(); // 住所1 currentsheet.Cell("B8").Value = drSeikyuMeisai[4].ToString(); // 住所2 currentsheet.Cell("B10").Value = drSeikyuMeisai[1].ToString(); // 顧客名 currentsheet.Cell("H6").Value = drSeikyuMeisai[5].ToString(); // 請求年月日 } // currentsheet.Cell(xlsRowCnt, "A").Value = drSeikyuMeisai[14].ToString(); // 日付 currentsheet.Cell(xlsRowCnt, "B").Value = drSeikyuMeisai[15].ToString(); // 伝票No. currentsheet.Cell(xlsRowCnt, "D").Value = drSeikyuMeisai[18].ToString(); // 取区 currentsheet.Cell(xlsRowCnt, "E").Value = drSeikyuMeisai[19].ToString(); // 商品名 currentsheet.Cell(xlsRowCnt, "J").Value = drSeikyuMeisai[20].ToString(); // 数量 currentsheet.Cell(xlsRowCnt, "K").Value = drSeikyuMeisai[21].ToString(); // 単価 currentsheet.Cell(xlsRowCnt, "N").Value = drSeikyuMeisai[22].ToString(); // 金額 currentsheet.Cell(xlsRowCnt, "P").Value = drSeikyuMeisai[23].ToString(); // 入金金額 currentsheet.Cell(xlsRowCnt, "Q").Value = "'" + drSeikyuMeisai[24].ToString(); // 備考 xlsRowCnt++; } // テンプレートシート削除 templatesheet1.Delete(); templatesheet2.Delete(); // ページ数設定 for (pageCnt = 1; pageCnt <= workbook.Worksheets.Count; pageCnt++) { workbook.Worksheet(pageCnt).Cell("R2").Value = pageCnt.ToString(); // No. } // workbookを保存 string strOutXlsFile = strWorkPath + strDateTime + ".xlsx"; workbook.SaveAs(strOutXlsFile); // workbookを解放 workbook.Dispose(); // ロゴ貼り付け処理 CreatePdf pdf = new CreatePdf(); int[] topRow = { 5 }; int[] leftColumn = { 15 }; pdf.logoPaste(strOutXlsFile, topRow, leftColumn, 200, 850, 57); // PDF化の処理 return(pdf.createPdf(strOutXlsFile, strDateTime, 0)); } catch { throw; } finally { // Workフォルダの全ファイルを取得 string[] files = System.IO.Directory.GetFiles(strWorkPath, "*", System.IO.SearchOption.AllDirectories); // Workフォルダ内のファイル削除 foreach (string filepath in files) { //File.Delete(filepath); } } }
static void CreateTemplate(string sourceFileName, string destFileName) { try { var data = ReadCSV(sourceFileName); if (data == null || data.Count == 0) { return; } // Create New WorkBook and WorkSheet var workBook = new XLWorkbook(); var workSheet = workBook.Worksheets.Add("Sheet1"); // Count of population, variables var population = data.Count; var variablesNum = data[0].Count; // Set Size for (int i = 0; i < variablesNum; i++) { workSheet.Column(i + 2).Width = 17; } // Write Source Data and Calc Average and Variance { int OffsetX = 2; int OffsetY = 3; // Set Labels workSheet.Cell("A1").Value = "変量"; workSheet.Cell("B1").Value = "実測値"; for (var i = 1; i < variablesNum; i++) { workSheet.Cell(OffsetY - 2, i + OffsetX).Value = $"説明変量{i}"; } workSheet.Cell("A2").Value = "個体"; workSheet.Cell("B2").Value = "y"; for (var i = 1; i < variablesNum; i++) { workSheet.Cell(OffsetY - 1, i + OffsetX).Value = $"x{i}"; } // Write Source Data for (var i = 0; i < population; i++) { workSheet.Cell(i + OffsetY, 1).Value = $"{Get26Decimal(i)}"; for (var j = 0; j < variablesNum; j++) { workSheet.Cell(i + OffsetY, j + OffsetX).Value = data[i][j]; } } // Calc Average workSheet.Cell(population + OffsetY, 1).Value = "平均"; for (var i = 0; i < variablesNum; i++) { workSheet.Cell(population + OffsetY, i + OffsetX).FormulaA1 = $"=AVERAGE({(char)('B' + i)}${OffsetY}:{(char)('B' + i)}${population + OffsetY - 1})"; } // Calc Variance workSheet.Cell(population + OffsetY + 1, 1).Value = "分散"; for (var i = 0; i < variablesNum; i++) { workSheet.Cell(population + OffsetY + 1, i + OffsetX).FormulaA1 = $"=VAR({(char)('B' + i)}${OffsetY}:{(char)('B' + i)}${population + OffsetY - 1})"; } } // Write Normalize Data and Calc Average and Variance { int OffsetX = 2; int OffsetY = population + 7; // Set Labels workSheet.Cell(OffsetY - 1, OffsetX).Value = "y"; for (var i = 1; i < variablesNum; i++) { workSheet.Cell(OffsetY - 1, i + OffsetX).Value = $"x{i}"; } // Write Normalize Data for (var i = 0; i < population; i++) { workSheet.Cell(i + OffsetY, 1).Value = $"{Get26Decimal(i)}"; for (var j = 0; j < variablesNum; j++) { workSheet.Cell(i + OffsetY, j + OffsetX).FormulaA1 = $"=({(char)('B' + j)}{OffsetY - population - 4 + i}-{(char)('B' + j)}${OffsetY - 4})/SQRT({(char)('B' + j)}${OffsetY - 3})"; } } // Calc Average workSheet.Cell(population + OffsetY, 1).Value = "平均"; for (var i = 0; i < variablesNum; i++) { workSheet.Cell(population + OffsetY, i + OffsetX).FormulaA1 = $"=AVERAGE({(char)('B' + i)}${OffsetY}:{(char)('B' + i)}${population + OffsetY - 1})\n"; } // Calc Variance workSheet.Cell(population + OffsetY + 1, 1).Value = "分散"; for (var i = 0; i < variablesNum; i++) { workSheet.Cell(population + OffsetY + 1, i + OffsetX).FormulaA1 = $"=VAR({(char)('B' + i)}${OffsetY}:{(char)('B' + i)}${population + OffsetY - 1})\n"; } } // Write Calc Variance/Covariance Matrix { int OffsetX = 2; int OffsetY = population * 2 + 11; int NormX = OffsetX; int NormY = OffsetY - population - 4; // Set Labels workSheet.Cell(OffsetY - 1, OffsetX).Value = "y"; for (var i = 1; i < variablesNum; i++) { workSheet.Cell(OffsetY - 1, i + OffsetX).Value = $"x{i}"; } workSheet.Cell(OffsetY, OffsetX - 1).Value = "y"; for (var i = 1; i < variablesNum; i++) { workSheet.Cell(OffsetY + i, OffsetX - 1).Value = $"x{i}"; } // Write Variace/Covariance for (var i = 0; i < variablesNum; i++) { for (var j = 0; j < variablesNum; j++) { var arg1 = $"{(char)('B' + j)}{NormY}:{(char)('B' + j)}{NormY + population - 1}"; var arg2 = $"{(char)('B' + i)}{NormY}:{(char)('B' + i)}{NormY + population - 1}"; workSheet.Cell(OffsetY + i, OffsetX + j).FormulaA1 = $"=COVAR({arg1},{arg2})*{population}/({population}-1)"; } } } // Write Calc Matrix { int OffsetX = 1; int OffsetY = population * 2 + variablesNum + 13; int MatX = OffsetX; int MatY = OffsetY - variablesNum - 2; // Write Inverse var arg = $"{(char)('B' + 1)}{MatY + 1}:{(char)('B' + variablesNum - 1)}{MatY + variablesNum - 1}"; workSheet.Range(OffsetY, OffsetX, OffsetY + variablesNum - 2, OffsetX + variablesNum - 2).FormulaA1 = $"=MINVERSE({arg})"; // Write value for (int i = 1; i < variablesNum; i++) { workSheet.Cell(OffsetY + i - 1, OffsetX + variablesNum).FormulaA1 = $"B{MatY + i}"; } // Write Calc result var arg1 = $"A{OffsetY}:{(char)('A' + variablesNum - 2)}{OffsetY + variablesNum - 2}"; var arg2 = $"{(char)('B' + OffsetX + variablesNum - 2)}{OffsetY}:{(char)('B' + OffsetX + variablesNum - 2)}{OffsetY + variablesNum - 2}"; workSheet.Range(OffsetY, OffsetX + variablesNum + 2, OffsetY + variablesNum - 2, OffsetX + variablesNum + 2).FormulaA1 = $"=MMULT({arg1}, {arg2})"; } // Write Calc Predicate/Error { int OffsetX = 2; int OffsetY = population * 2 + variablesNum * 2 + 14; int MatX = OffsetX + variablesNum - 1; int MatY = OffsetY - variablesNum - 1; int NormY = population + 7; // Set Labels workSheet.Cell($"B{OffsetY - 1}").Value = "Y"; workSheet.Cell($"C{OffsetY - 1}").Value = "E"; workSheet.Cell($"D{OffsetY - 1}").Value = "Y - Ave(Y)"; for (var i = 0; i < population; i++) { workSheet.Cell(i + OffsetY, 1).Value = $"{Get26Decimal(i)}"; var y = "="; for (var j = 0; j < variablesNum - 1; j++) { y += (j == 0 ? "" : "+") + $"{(char)('B' + j + 1)}{NormY + i}*{(char)('B' + MatX)}{MatY + j}"; } workSheet.Cell(i + OffsetY, 2).FormulaA1 = y; workSheet.Cell(i + OffsetY, 3).FormulaA1 = $"B{NormY + i}-B{OffsetY + i}"; workSheet.Cell(i + OffsetY, 4).FormulaA1 = $"B{OffsetY + i}-B{OffsetY + population}"; } // Calc Average workSheet.Cell(population + OffsetY, 1).Value = "平均"; for (var i = 0; i < 2; i++) { workSheet.Cell(population + OffsetY, i + OffsetX).FormulaA1 = $"=AVERAGE({(char)('B' + i)}${OffsetY}:{(char)('B' + i)}${population + OffsetY - 1})\n"; } // Calc Variance workSheet.Cell(population + OffsetY + 1, 1).Value = "分散"; for (var i = 0; i < 2; i++) { workSheet.Cell(population + OffsetY + 1, i + OffsetX).FormulaA1 = $"=VAR({(char)('B' + i)}${OffsetY}:{(char)('B' + i)}${population + OffsetY - 1})\n"; } } // Write Analysis of Variance Table { int OffsetX = 1; int OffsetY = population * 3 + variablesNum * 2 + 18; int NormY = population + 7; int PredY = population * 2 + variablesNum * 2 + 14; // Set Labels workSheet.Cell($"A{OffsetY - 1}").Value = "平方和"; workSheet.Cell($"B{OffsetY - 1}").Value = "自由度"; workSheet.Cell($"C{OffsetY - 1}").Value = "不偏分散"; workSheet.Cell($"D{OffsetY - 1}").Value = "分散比"; workSheet.Cell(OffsetY + 0, OffsetX + 0).FormulaA1 = $"=SUMSQ(C{NormY}:C{NormY + population - 1})"; // Sr workSheet.Cell(OffsetY + 1, OffsetX + 0).FormulaA1 = $"=SUMXMY2(B{NormY}:B{NormY + population - 1}, B{PredY}:B{PredY + population - 1})"; // Se workSheet.Cell(OffsetY + 2, OffsetX + 0).FormulaA1 = $"=SUMSQ(B{NormY}:B{NormY + population - 1})"; // St workSheet.Cell(OffsetY + 0, OffsetX + 1).Value = variablesNum - 1; // p workSheet.Cell(OffsetY + 1, OffsetX + 1).Value = population - variablesNum; // n-p-1 workSheet.Cell(OffsetY + 2, OffsetX + 1).Value = population - 1; // n-1 workSheet.Cell(OffsetY + 0, OffsetX + 2).FormulaA1 = $"A{OffsetY}/B{OffsetY}"; workSheet.Cell(OffsetY + 1, OffsetX + 2).FormulaA1 = $"A{OffsetY+1}/B{OffsetY+1}"; workSheet.Cell(OffsetY + 0, OffsetX + 3).FormulaA1 = $"C{OffsetY}/C{OffsetY+1}"; } // Save file as destFileName and Close object workBook.SaveAs(destFileName); workBook.Dispose(); } catch (Exception ex) { Error.WriteLine(ex); Environment.Exit(1); } }
public void TearDown() { workbook.Dispose(); }
public void Dispose() { _workbook?.Dispose(); _stream?.Dispose(); }
/* * 操作类型 crashReport -- 异常报告 * 主题 dp2circulation * 发送者 xxx * 媒体类型 text * 内容 发生未捕获的界面线程异常: * Type: System.ArgumentOutOfRangeException * Message: 索引超出范围。必须为非负值并小于集合大小。 * 参数名: index * Stack: * 在 System.ThrowHelper.ThrowArgumentOutOfRangeException(ExceptionArgument argument, ExceptionResource resource) * 在 System.Collections.Generic.List`1.get_Item(Int32 index) * 在 DigitalPlatform.dp2.Statis.ClosedXmlUtil.ExportToExcel(Stop stop, List`1 items, String& strError) * 在 dp2Circulation.ItemSearchForm.menu_exportExcelFile_Click(Object sender, EventArgs e) * 在 System.Windows.Forms.MenuItem.OnClick(EventArgs e) * 在 System.Windows.Forms.MenuItem.MenuItemData.Execute() * 在 System.Windows.Forms.Command.Invoke() * 在 System.Windows.Forms.Control.WmCommand(Message& m) * 在 System.Windows.Forms.Control.WndProc(Message& m) * 在 System.Windows.Forms.ListView.WndProc(Message& m) * 在 DigitalPlatform.GUI.ListViewNF.WndProc(Message& m) * 在 System.Windows.Forms.NativeWindow.Callback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam) * * * dp2Circulation 版本: dp2Circulation, Version=3.7.7278.20124, Culture=neutral, PublicKeyToken=null * 操作系统:Microsoft Windows NT 6.2.9200.0 * 本机 MAC 地址: 94C691840CE9 * 操作时间 2020/1/2 14:44:36 (Thu, 02 Jan 2020 14:44:36 +0800) * 前端地址 xxx 经由 http://dp2003.com/dp2library * */ // return: // -1 出错 // 0 放弃或中断 // 1 成功 public static int ExportToExcel( Stop stop, List <SheetItem> sheets, out string strError) { strError = ""; if (sheets == null || sheets.Count == 0) { strError = "sheets == null || sheets.Count == 0"; return(-1); } // 询问文件名 SaveFileDialog dlg = new SaveFileDialog { Title = "请指定要输出的 Excel 文件名", CreatePrompt = false, OverwritePrompt = true, // dlg.FileName = this.ExportExcelFilename; // dlg.InitialDirectory = Environment.CurrentDirectory; Filter = "Excel 文件 (*.xlsx)|*.xlsx|All files (*.*)|*.*", RestoreDirectory = true }; // 如果在询问文件名对话框,点了取消,退不处理,返回0, if (dlg.ShowDialog() != DialogResult.OK) { return(0); } // 创建excel对象 XLWorkbook doc = null; try { doc = new XLWorkbook(XLEventTracking.Disabled); // 如果原来文件存在,则先删除 File.Delete(dlg.FileName); } catch (Exception ex) { strError = ExceptionUtil.GetAutoText(ex); return(-1); } // 循环导出每个sheet foreach (SheetItem oneSheet in sheets) { int nRet = ExportOneSheetToExcel(stop, oneSheet.sheetName, oneSheet.items, dlg.FileName, doc, out strError); if (nRet == -1) { strError = "导'" + oneSheet.sheetName + "'表出错:" + strError; return(-1); } } // 保存excel文件 doc.SaveAs(dlg.FileName); doc.Dispose(); // 自动打开excel文件 try { System.Diagnostics.Process.Start(dlg.FileName); } catch { } return(1); }
public void Dispose() { _template?.Dispose(); _wb?.Dispose(); }
private void BT_TO_EXPORT_CLINIC(object sender, RoutedEventArgs e) { if (gdata.Count <= 0) { MessageBox.Show("尚未匯入指標清單"); return; } if (!Directory.Exists(System.Environment.CurrentDirectory + @"\資料收集")) { Directory.CreateDirectory(System.Environment.CurrentDirectory + @"\資料收集"); } /// /// 匯出時濾掉名稱不同但相同數值的要素 /// var newda = gdata; newda.Sort((x, y) => { return(x.MeasureName.CompareTo(y.MeasureName)); }); var newdata = newda.GroupBy(o => o.Depart) .ToDictionary(o => o.Key, o => o.ToList()); var unitcounts = gdata.Where(o => !SameEle.Contains(o.MeasureID)) .GroupBy(o => o.Depart) .ToDictionary(o => o.Key, o => o.ToList().Count); TxtBox1.Text += Environment.NewLine + "指標收集單位數 : " + unitcounts.Count + Environment.NewLine + string.Join(",", unitcounts) + Environment.NewLine; try { foreach (var x in newdata) { using (var wb = new XLWorkbook()) { wb.Properties.Author = "TTMHH's QMC"; wb.Properties.Status = "Measurement"; wb.Properties.Title = "Measurement File"; wb.Properties.Comments = "Measurement File"; var ws = wb.Worksheets.Add("指標提報"); ws.Style.Font.FontSize = 12; ws.Style.Font.FontName = "微軟正黑體"; var wscol = ws.Columns("A:D"); wscol.Width = 15; wscol.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center; wscol.Style.Alignment.Vertical = XLAlignmentVerticalValues.Center; wscol.Style.Alignment.WrapText = true; ws.Column(1).Width = 15; ws.Column(2).Width = 10; ws.Column(3).Width = 25; ws.Column(4).Width = 45; ws.Column(5).Width = 10; ws.Cells("A1:D1").Style.Fill.BackgroundColor = XLColor.LightBlue; ws.Cell(1, 1).Value = "指標群組"; ws.Cell(1, 2).Value = "監測單位"; ws.Cell(1, 3).Value = "指標要素"; ws.Cell(1, 4).Value = "指標(要素)名稱"; for (int i = 0; i < 6; i++) { ws.Cell(1, i + 5).Value = DateTime.Now.AddMonths(-1 - i).ToString("yyyy/MM"); ws.Cell(1, i + 5).Style.DateFormat.Format = "yyyy/MM"; ws.Cell(1, i + 5).Style.Fill.BackgroundColor = XLColor.LightBlue; ws.Cell(1, i + 5).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center; ws.Column(i + 5).AdjustToContents(); } int index = 2; for (int i = 0; i < x.Value.Count; i++) { if (SameEle.Count > 0 && SameEle.Contains(x.Value[i].MeasureID)) { continue; } ws.Cell(index, 1).Value = x.Value[i].Group; ws.Cell(index, 2).Value = x.Value[i].Depart; ws.Cell(index, 3).Value = x.Value[i].MeasureID; ws.Cell(index, 4).Value = x.Value[i].MeasureName; ws.Cell(index, 5).Style.Protection.SetLocked(false); ws.Cell(index, 5).Style.Fill.BackgroundColor = XLColor.LightCyan; ws.Cell(index, 5).Style.Font.FontSize = 13; ws.Cell(index, 5).Style.Font.FontColor = XLColor.DarkMagenta; ws.Cell(index, 5).Style.Font.Bold = true; ws.Cell(index, 5).Style.Border.RightBorder = XLBorderStyleValues.Thin; ws.Cell(index, 5).Style.Border.RightBorderColor = XLColor.LightGray; ws.Cell(index, 5).Style.Border.LeftBorder = XLBorderStyleValues.Thin; ws.Cell(index, 5).Style.Border.LeftBorderColor = XLColor.LightGray; ws.Cell(index, 5).Style.Border.TopBorder = XLBorderStyleValues.Thin; ws.Cell(index, 5).Style.Border.TopBorderColor = XLColor.LightGray; ws.Cell(index, 5).Style.Border.BottomBorder = XLBorderStyleValues.Thin; ws.Cell(index, 5).Style.Border.BottomBorderColor = XLColor.LightGray; if (gbackups.Count > 0 && gbackups.ContainsKey(x.Value[i].MeasureID)) { for (int j = 2; j < 7; j++) { var data = gbackups[x.Value[i].MeasureID].Find(o => o.Eledate.Month == DateTime.Now.AddMonths(-j).Month && o.Eledate.Year == DateTime.Now.AddMonths(-j).Year); if (data == null) { continue; } ws.Cell(index, j + 4).Value = data.ElementData; } } index++; } ws.Protect() .SetInsertColumns(false) .SetDeleteColumns(false) .SetInsertRows(false) .SetDeleteRows(false) .SetFormatCells(true) .SetSelectLockedCells(false) .SetSelectUnlockedCells(true); string Refile = System.Environment.CurrentDirectory + @"\資料收集\" + x.Key + ".xlsx"; wb.SaveAs(Refile); wb.Dispose(); } } MessageBox.Show("轉出成功 : " + newdata.Count); } catch (Exception ex) { MessageBox.Show(ex.ToString()); } }