/// <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 = "ＭＳ ゴシック";
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 = "ＭＳ 明朝";
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]["住所１"]; // 住所１
currentsheet.Cell("L6").Value = dtSetCd_B_Input.Rows[0]["住所２"]; // 住所２
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]["住所１"]; // 住所１
currentsheet.Cell("H5").Value = dtSetCd_B_Input.Rows[0]["住所２"]; // 住所２
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 = "ＭＳ 明朝";
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 = "ＭＳ 明朝";
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 = "ＭＳ ゴシック";
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(); // 住所１
currentsheet.Cell("B8").Value = drSeikyuMeisai[4].ToString(); // 住所２
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());
}
}
