/// <summary>
/// Initializes a new instance of the <see cref="CellReader"/> class.
/// </summary>
/// <param name="current">The current.</param>
public CellReader(IXLCell current)
{
ArgumentCheck.IsNotNull(current);
Address = current.Address.ToStringRelative(false);
Row = current.Address.RowNumber;
Column = current.Address.ColumnNumber;
Value = current.Value;
}
public new IXLRangeColumn CopyTo(IXLCell target)
{
base.CopyTo(target);
int lastRowNumber = target.Address.RowNumber + RowCount() - 1;
if (lastRowNumber > XLHelper.MaxRowNumber)
lastRowNumber = XLHelper.MaxRowNumber;
int lastColumnNumber = target.Address.ColumnNumber + ColumnCount() - 1;
if (lastColumnNumber > XLHelper.MaxColumnNumber)
lastColumnNumber = XLHelper.MaxColumnNumber;
return target.Worksheet.Range(
target.Address.RowNumber,
target.Address.ColumnNumber,
lastRowNumber,
lastColumnNumber)
.Column(1);
}
// ** implementation
object GetValue(IXLCell cell)
{
if (_evaluating)
{
throw new Exception("Circular Reference");
}
try
{
_evaluating = true;
var f = cell.FormulaA1;
if (XLHelper.IsNullOrWhiteSpace(f))
return cell.Value;
else
return new XLCalcEngine(cell.Worksheet).Evaluate(f);
}
finally
{
_evaluating = false;
}
}
/// -----------------------------------------------------------------------------
/// <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);
}
}
}
IXLRangeColumn IXLColumn.CopyTo(IXLCell target)
{
using (var asRange = AsRange())
using (var copy = asRange.CopyTo(target))
return copy.Column(1);
}
public IXLPivotTable AddNew(string name, IXLCell target, IXLRange source)
{
var pivotTable = new XLPivotTable { Name = name, TargetCell = target, SourceRange = source };
_pivotTables.Add(name, pivotTable);
return pivotTable;
}
public IXLRangeColumn Column(IXLCell start, IXLCell end)
{
return Column(start.Address.RowNumber, end.Address.RowNumber);
}
/// <summary>
/// Writes a list of values to the current row
/// </summary>
/// <param name="row"></param>
public void WriteRow(List<string> row)
{
IXLCell start = current;
foreach (string value in row)
{
current.Value = value;
current = current.CellRight();
}
current = start.CellBelow();
}
// 根据 Merged ListView 和指定的 Key 列创建报表
// parameters:
// nKeyColumn 用于 Key 的列 index 值。例如 MERGED_COLUMN_SELLER
// strKeyCaption Key 列的列标题。例如“渠道”
public static void BuildMergedReport(
IEnumerable <ListViewItem> items,
int nKeyColumn,
string strKeyCaption,
IXLWorksheet sheet)
{
List <KeyStatisLine> results = //this.listView_merged.Items
items
.Cast <ListViewItem>()
.GroupBy(p => ListViewUtil.GetItemText(p, nKeyColumn))
.Select(cl => new KeyStatisLine
{
Key = ListViewUtil.GetItemText(cl.First(), nKeyColumn),
OrderCopies = cl.Sum(o => Convert.ToInt32(ListViewUtil.GetItemText(o, MERGED_COLUMN_COPY))),
OrderPrice = ConcatPrice(cl, MERGED_COLUMN_PRICE),
}).ToList();
int line = 0;
// 栏目标题行
{
int column = 0;
// Seller
IXLCell start = PrintOrderForm.WriteExcelCell(
sheet,
TABLE_TOP_BLANK_LINES + line,
TABLE_LEFT_BLANK_COLUMS + column++,
strKeyCaption);
// OrderCopies
WriteExcelCell(
sheet,
TABLE_TOP_BLANK_LINES + line,
TABLE_LEFT_BLANK_COLUMS + column++,
"订购套数");
// OrderPrice
IXLCell end = WriteExcelCell(
sheet,
TABLE_TOP_BLANK_LINES + line,
TABLE_LEFT_BLANK_COLUMS + column++,
"订购价");
IXLRange range = sheet.Range(start, end);
range.Style.Font.Bold = true;
range.Style.Fill.BackgroundColor = XLColor.LightGray;
range.Style.Border.BottomBorder = XLBorderStyleValues.Thin;
line++;
}
// 内容行
foreach (var item in results)
{
int column = 0;
// Seller
WriteExcelCell(
sheet,
TABLE_TOP_BLANK_LINES + line,
TABLE_LEFT_BLANK_COLUMS + column++,
item.Key);
// OrderCopies
WriteExcelCell(
sheet,
TABLE_TOP_BLANK_LINES + line,
TABLE_LEFT_BLANK_COLUMS + column++,
item.OrderCopies);
// OrderPrice
WriteExcelCell(
sheet,
TABLE_TOP_BLANK_LINES + line,
TABLE_LEFT_BLANK_COLUMS + column++,
item.OrderPrice);
// text.Append($"i={i} Seller='{item.Seller}' OrderCopies={item.OrderCopies} OrderPrice={item.OrderPrice}\r\n");
line++;
}
}
public void GreaterThan(IXLCell cell)
{
dataValidation.Value = cell.Address.ToStringFixed();
dataValidation.Operator = XLOperator.GreaterThan;
}
public static int ExportOneSheetToExcel(
Stop stop,
string sheetName,
List <ListViewItem> items,
string excelFileName,
XLWorkbook doc,
out string strError)
{
strError = "";
if (items == null || items.Count == 0)
{
strError = "items == null || items.Count == 0";
return(-1);
}
// 传入items所对应的ListView对象
ListView list = items[0].ListView;
//创建一个sheet
IXLWorksheet sheet = null;
sheet = doc.Worksheets.Add(sheetName);//"表格");
// sheet.Style.Font.FontName = this.Font.Name;
// 关于显示进度
if (stop != null)
{
stop.SetProgressRange(0, items.Count);
}
// 一个数组，每个列的最大字符数
List <int> column_max_chars = new List <int>();
// 按listview的位置设置excel
List <XLAlignmentHorizontalValues> alignments = new List <XLAlignmentHorizontalValues>();
foreach (ColumnHeader header in list.Columns)
{
if (header.TextAlign == HorizontalAlignment.Center)
{
alignments.Add(XLAlignmentHorizontalValues.Center);
}
else if (header.TextAlign == HorizontalAlignment.Right)
{
alignments.Add(XLAlignmentHorizontalValues.Right);
}
else
{
alignments.Add(XLAlignmentHorizontalValues.Left);
}
// 先将每列最大字符数设置0
column_max_chars.Add(0);
}
// 确保excel列数量与listview数量一致
Debug.Assert(alignments.Count == list.Columns.Count, "");
string strFontName = list.Font.FontFamily.Name;
int nRowIndex = 1;
int nColIndex = 1;
// 设置第一行的列头信息
foreach (ColumnHeader header in list.Columns)
{
IXLCell cell = sheet.Cell(nRowIndex, nColIndex).SetValue(DomUtil.ReplaceControlCharsButCrLf(header.Text, '*'));
cell.Style.Alignment.WrapText = true;
cell.Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
cell.Style.Font.Bold = true;
cell.Style.Font.FontName = strFontName;
cell.Style.Alignment.Horizontal = alignments[nColIndex - 1];
nColIndex++;
}
nRowIndex++;
// 关于显示进度信息
//if (stop != null)
// stop.SetMessage("");
// 处理每一行
foreach (ListViewItem item in items)
{
Application.DoEvents();
// 关于显示进度信息
if (stop != null && stop.State != 0)
{
strError = "用户中断";
return(0);
}
// 输出每一行
nColIndex = 1; //从第一列开始
foreach (ListViewItem.ListViewSubItem subitem in item.SubItems)
{
// 统计最大字符数
// int nChars = column_max_chars[nColIndex - 1];
if (subitem.Text != null)
{
SetMaxChars(/*ref*/ column_max_chars, nColIndex - 1, subitem.Text.Length);
}
IXLCell cell = sheet.Cell(nRowIndex, nColIndex).SetValue(DomUtil.ReplaceControlCharsButCrLf(subitem.Text, '*'));
cell.Style.Alignment.WrapText = true;
cell.Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
cell.Style.Font.FontName = strFontName;
// 2020/1/6 增加保护代码
if (nColIndex - 1 < alignments.Count)
{
cell.Style.Alignment.Horizontal = alignments[nColIndex - 1];
}
else
{
cell.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Left;
}
nColIndex++;
}
// 关于显示进度信息
if (stop != null)
{
stop.SetProgressValue(nRowIndex - 1);
}
nRowIndex++;
}
// 关于显示进度
if (stop != null)
{
stop.SetMessage("正在调整列宽度 ...");
}
Application.DoEvents();
// 取出一个字符串的平均宽度
double char_width = GetAverageCharPixelWidth(list);
// 字符数太多的列不要做 width auto adjust
const int MAX_CHARS = 30; // 60
int i = 0;
foreach (IXLColumn column in sheet.Columns())
{
// int nChars = column_max_chars[i];
int nChars = GetMaxChars(column_max_chars, i);
if (nChars < MAX_CHARS)
{
column.AdjustToContents();
}
else
{
int nColumnWidth = 100;
// 2020/1/6 增加保护判断
if (i >= 0 && i < list.Columns.Count)
{
nColumnWidth = list.Columns[i].Width;
}
column.Width = (double)nColumnWidth / char_width; // Math.Min(MAX_CHARS, nChars);
}
i++;
}
// 保存excel文件
doc.SaveAs(excelFileName);
//doc.Dispose();
//// 自动打开excel文件
//try
//{
// System.Diagnostics.Process.Start(dlg.FileName);
//}
//catch
//{
//}
return(1);
}
// 根据 Origin ListView 和指定的 Key 列创建报表
// parameters:
// nKeyColumn 用于 Key 的列 index 值。例如 ORIGIN_COLUMN_SOURCE
// strKeyCaption Key 列的列标题。例如“经费来源”
public static void BuildOriginReport(
bool bSeries,
IEnumerable <ListViewItem> items,
string strKeyName,
string strKeyCaption,
IXLWorksheet sheet)
{
List <int> column_max_chars = new List <int>();
// 先变换为 LineInfo 数组
// LineInfo 可以放在固定面板区用 PropertyGrid 界面显示
List <PrintOrderForm.LineInfo> lines = new List <PrintOrderForm.LineInfo>();
int i = 0;
foreach (ListViewItem item in items)
{
var current = PrintOrderForm.LineInfo.Build(item, $"原始视图第 {(i + 1)} 行");
lines.Add(current.Adjust());
i++;
}
List <KeyStatisLine> results =
lines
.Where(o => bSeries || string.IsNullOrEmpty(o.State) == false)
.GroupBy(p => (string)GetPropertyValue(p, strKeyName))
.Select(cl => new KeyStatisLine
{
Key = (string)GetPropertyValue(cl.First(), strKeyName),
OrderIssueCount = cl.Where(a => a.Copy?.OldCopy?.Copy > 0)
.Sum(o => Convert.ToInt32(o.IssueCount)),
AcceptIssueCount = cl.Where(a => a.Copy?.NewCopy?.Copy > 0)
.Sum(o => Convert.ToInt32(o.IssueCount)),
OrderCopies = cl.Sum(o => Convert.ToInt32(o.Copy.OldCopy.Copy)),
OrderPrice = ConcatLinePrice(cl, "TotalPrice"),
AcceptCopies = cl.Sum(o => Convert.ToInt32(o.Copy.NewCopy.Copy)),
AcceptPrice = ConcatLinePrice(cl, "AcceptTotalPrice"),
OrderBiblioCount = cl.Where(a => a.Copy?.OldCopy?.Copy > 0)
.GroupBy(p => p.BiblioRecPath).LongCount(),
// cl.GroupBy(p => p.BiblioRecPath).LongCount(),
AcceptBiblioCount = cl.Where(a => a.Copy?.NewCopy?.Copy > 0)
.GroupBy(p => p.BiblioRecPath).LongCount(),
OrderFixedPrice = ConcatLinePrice(cl, "OrderTotalFixedPrice"), // 可能要乘以套数
OrderDiscount = cl.Where(a => a.Copy?.OldCopy?.Copy > 0)
.DefaultIfEmpty(new PrintOrderForm.LineInfo()).Average(o => o.OrderDiscount).ToString(),
AcceptFixedPrice = ConcatLinePrice(cl, "AcceptTotalFixedPrice"), // 可能要乘以套数
AcceptDiscount = cl.Where(a => a.Copy?.NewCopy?.Copy > 0)
.DefaultIfEmpty(new PrintOrderForm.LineInfo()).Average(o => o.AcceptDiscount).ToString(),
}).ToList();
int line = 0;
// 栏目标题行
string[] titles = new string[] {
strKeyCaption,
"订购种数", "*订购期数",
"订购套数", "订购价",
"订购码洋", "平均订购折扣",
"验收种数", "*验收期数",
"验收套数", "验收价",
"验收码洋", "平均验收折扣",
};
{
IXLCell start = null;
IXLCell end = null;
SetCellInfo info = new SetCellInfo
{
ColumnMaxChars = column_max_chars,
Line = line,
Column = 0,
Sheet = sheet,
};
foreach (string title in titles)
{
if (title.StartsWith("*") && bSeries == false)
{
continue;
}
string text = title.StartsWith("*") ? title.Substring(1) : title;
IXLCell cell = info.SetCellText(text);
#if NO
SetMaxChars(ref column_max_chars,
TABLE_LEFT_BLANK_COLUMS + column,
text.Length);
IXLCell cell = PrintOrderForm.WriteExcelCell(
sheet,
TABLE_TOP_BLANK_LINES + line,
TABLE_LEFT_BLANK_COLUMS + column++,
text);
#endif
if (start == null)
{
start = cell;
}
end = cell;
}
IXLRange range = sheet.Range(start, end);
range.Style.Font.Bold = true;
range.Style.Fill.BackgroundColor = XLColor.LightGray;
range.Style.Border.BottomBorder = XLBorderStyleValues.Thin;
line++;
}
// 内容行
foreach (var item in results)
{
SetCellInfo info = new SetCellInfo
{
ColumnMaxChars = column_max_chars,
Line = line,
Column = 0,
Sheet = sheet,
};
// Seller
info.SetCellText(item.Key);
// OrderBiblioCount
info.SetCellText(item.OrderBiblioCount);
if (bSeries)
{
// OrderIssueCount
info.SetCellText(item.OrderIssueCount);
}
// OrderCopies
info.SetCellText(item.OrderCopies);
// OrderPrice
info.SetCellText(item.OrderPrice);
// OrderFixedPrice
info.SetCellText(item.OrderFixedPrice);
// OrderDiscount
info.SetCellText(item.OrderDiscount);
// AcceptBiblioCount
info.SetCellText(item.AcceptBiblioCount);
if (bSeries)
{
// AcceptIssueCount
info.SetCellText(item.AcceptIssueCount);
}
// AcceptCopies
info.SetCellText(item.AcceptCopies);
// AcceptPrice
info.SetCellText(item.AcceptPrice);
// AcceptFixedPrice
info.SetCellText(item.AcceptFixedPrice);
// AcceptDiscount
info.SetCellText(item.AcceptDiscount);
// text.Append($"i={i} Seller='{item.Seller}' OrderCopies={item.OrderCopies} OrderPrice={item.OrderPrice}\r\n");
line++;
}
AdjectColumnWidth(sheet, column_max_chars);
}
/// <summary>
/// Set caption style to excel cell
/// </summary>
/// <param name="cell">Excel cell</param>
public void SetCaptionStyle(IXLCell cell)
{
cell.Style.Fill.PatternType = XLFillPatternValues.Solid;
cell.Style.Fill.BackgroundColor = XLColor.FromColor(Color.FromArgb(184, 204, 228));
cell.Style.Font.Bold = true;
}
public void NotBetween(IXLCell minValue, IXLCell maxValue)
{
dataValidation.MinValue = minValue.Address.ToStringFixed();
dataValidation.MaxValue = maxValue.Address.ToStringFixed();
dataValidation.Operator = XLOperator.NotBetween;
}
public void EqualOrLessThan(IXLCell cell)
{
dataValidation.Value = cell.Address.ToStringFixed();
dataValidation.Operator = XLOperator.EqualOrLessThan;
}
/// <summary>
/// Remove all sparklines in the specified cell
/// </summary>
/// <param name="cell">The cell to remove sparklines from</param>
public void Remove(IXLCell cell)
{
_sparklineGroups
.AsParallel()
.ForEach(g => g.Remove(cell));
}
IXLRange IXLRange.Range(IXLCell firstCell, IXLCell lastCell)
{
return(Range(firstCell, lastCell));
}
/// <summary>
/// Search for the first sparkline that is in the specified cell
/// </summary>
/// <param name="cell">The cell to find the sparkline for</param>
/// <returns>The sparkline in the cell or null if no sparklines are found</returns>
public IXLSparkline GetSparkline(IXLCell cell)
{
return(_sparklineGroups
.Select(g => g.GetSparkline(cell))
.FirstOrDefault(s => s != null));
}
/// <summary>
/// project出力
/// </summary>
/// <param name="context"></param>
/// <returns></returns>
public async Task OutputProject(IDialogContext context, IReadOnlyList <Project> projectList)
{
string filePath = @"D:\home\Excel\" + repository.Split('/')[1] + "(project).xlsx";
await context.PostAsync("project「" + repository.Split('/')[1] + "(project)」を出力しています...");
using (var book = new XLWorkbook(XLEventTracking.Disabled))
{
foreach (var project in projectList)
{
var proColumnList = await GitHubDialog.github.Repository.Project.Column.GetAll(project.Id);
// エクセルファイルに書き込み
var ws = book.AddWorksheet(project.Name);
int row = 2;
int col = 2;
//ラベル別の列幅とフォントサイズの指定
ws = GitHubDialog.convinient.ColumnWidthAndFontSize(ws, "task");
ws.Cell(row, col).Value = "No";
col++;
ws.Cell(row, col).Value = "タイプ";
col++;
ws.Cell(row, col).Value = "内容";
col++;
ws.Cell(row, col).Value = "詳細";
col++;
ws.Cell(row, col).Value = "完了判断";
col++;
ws.Cell(row, col).Value = "備考";
col++;
ws.Cell(row, col).Value = "担当者";
col++;
ws.Cell(row, col).Value = "着手日";
col++;
ws.Cell(row, col).Value = "完了日";
col++;
ws.Cell(row, col).Value = "実施状況";
IXLCell headerCell = ws.Cell(row, 2);
IXLRange ExcelRange = ws.Range(ws.Cell(row, 2), ws.Cell(row, col));
// ExcelRange.Interior.ColorIndex = 15; // カラーパレットの色を指定する場合
ExcelRange.Style.Fill.BackgroundColor = XLColor.Olivine; // オリーブ色
row++;
col = 2;
foreach (var column in proColumnList)
{
var proCardList = await GitHubDialog.github.Repository.Project.Card.GetAll(column.Id);
foreach (var card in proCardList)
{
if (!string.IsNullOrEmpty(card.Note))
{
continue;
}
//取得したカードのコンテンツURLの末尾の数字を取得
int issueNumber = int.Parse(card.ContentUrl.Substring(card.ContentUrl.LastIndexOf('/') + 1));
//コンテンツURLの末尾の数字を用いてissueを取得
Issue issue = await GitHubDialog.github.Issue.Get(repository.Split('/')[0], repository.Split('/')[1], issueNumber);
ws.Cell(row, col).Value = issue.Number;
col++;
ws.Cell(row, col).Value = string.Join("", issue.Labels.ToList().ConvertAll(x => x.Name));
col++;
ws.Cell(row, col).Value = issue.Title;
col++;
string[] temp = issue.Body.Split(new string[] { "##" }, StringSplitOptions.RemoveEmptyEntries);
int index = temp[0].IndexOf('\n');
ws.Cell(row, col).Value = temp[0].Substring(index + 1);
col++;
index = temp[1].IndexOf('\n');
ws.Cell(row, col).Value = temp[1].Substring(index + 1);
col++;
index = temp[2].IndexOf('\n');
ws.Cell(row, col).Value = temp[2].Substring(index + 1);
col++;
if (issue.Assignees.Count != 0)
{
ws.Cell(row, col).Value = issue.Assignees[0].Login;
}
col++;
ws.Cell(row, col).Value = issue.CreatedAt.DateTime.ToString();
col++;
if (issue.UpdatedAt.HasValue)
{
ws.Cell(row, col).Value = ((DateTimeOffset)issue.UpdatedAt).DateTime.ToString();
}
col++;
ws.Cell(row, col).Value = column.Name;
if (proCardList.Last().Equals(card))
{
IXLRange range = ws.Range(headerCell, ws.Cell(row, col));
range.Style
.Border.SetOutsideBorder(XLBorderStyleValues.Thin)
.Border.SetInsideBorder(XLBorderStyleValues.Hair)
.Border.SetLeftBorder(XLBorderStyleValues.Thin)
.Border.SetRightBorder(XLBorderStyleValues.Thin);
}
row++;
col = 2;
}
}
//検索用フィルターをつける
ws.RangeUsed().SetAutoFilter();
}
book.SaveAs(filePath);
}
string url = baseUrl + "files.upload?channels=" + GitHubDialog.channelName;
//
Encoding.UTF8.GetString(fileUpload(url, filePath));
await context.PostAsync("出力が完了しました");
//context.Wait(MessageReceivedAsync);
}
/// <summary>
/// Moves to the specified row and column
/// </summary>
/// <param name="row"></param>
/// <param name="column"></param>
public void MoveTo(int row, int column)
{
current = current.Worksheet.Cell(row, column);
}
/// <summary>
/// project出力
/// </summary>
/// <param name="context"></param>
/// <returns></returns>
public async Task OutputIssue(IDialogContext context, List <IReadOnlyList <Issue> > issueListForLabels, List <string> labelNameList)
{
//try
//{
//出力するExcelファイルの一時保存パス
string filePath = @"D:\home\Excel\" + repository.Split('/')[1] + "(issue).xlsx";
//出力する旨をbotで表示
await context.PostAsync("project「" + repository.Split('/')[1] + "(issue)」を出力しています...");
//ワークブックに書き込む
using (var book = new XLWorkbook(XLEventTracking.Disabled))
{
//ラベル名前リストのインデックス用変数
int i = 0;
//ラベル別issueリストの各issueリストに対する処理
foreach (var issueList in issueListForLabels)
{
//ワークシートを用意
IXLWorksheet ws = null;
//issueが一つでもあったらエクセルファイルに書き込み
if (issueList != null)
{
ws = book.AddWorksheet(labelNameList[i]);
}
//issueがひとつもなかったら次のループ処理に移る
else
{
continue;
}
//初期位置を2行目2列目に定める
int row = 2;
int col = 2;
//テンプレート大見出し格納リスト
List <string> HeadingList = new List <string>();
//ラベルのEntityを取得
TemplateEntity entity = entityOperation.RetrieveEntityResult(labelNameList[i], "label", string.Empty).Result as TemplateEntity;
if (entity != null)
{
//改行毎に区切る
string[] templateColumn = entity.Template.Split('\n');
foreach (var tempCol in templateColumn)
{
int index = tempCol.IndexOf("##");
if (index != -1)
{
HeadingList.Add(tempCol.Substring(index + 3));
}
}
}
//ラベル別の列幅とフォントサイズの指定
ws = GitHubDialog.convinient.ColumnWidthAndFontSize(ws, "task");
row++;
ws.Cell(row, col).Value = "No";
col++;
ws.Cell(row, col).Value = "タイプ";
col++;
ws.Cell(row, col).Value = "タイトル";
col++;
foreach (string header in HeadingList)
{
ws.Cell(row, col).Value = header;
col++;
}
ws.Cell(row, col).Value = "担当者";
col++;
ws.Cell(row, col).Value = "着手日";
col++;
ws.Cell(row, col).Value = "完了日";
//ヘッダーのセルを範囲指定
IXLCell headerCell = ws.Cell(row, 2);
IXLRange ExcelRange = ws.Range(ws.Cell(row, 2), ws.Cell(row, col));
//指定範囲の背景色をオリーブに変更
ExcelRange.Style.Fill.BackgroundColor = XLColor.Olivine;
//指定行を1行下げ、列を2列目に戻す
row++;
col = 2;
//issueリストの各issueに対する処理
foreach (var issue in issueList)
{
//issueの番号を指定セルに格納
ws.Cell(row, col).Value = issue.Number;
col++;
//issueのラベル名を指定セルに格納
ws.Cell(row, col).Value = string.Join("", issue.Labels.ToList().ConvertAll(x => x.Name));
col++;
//issueのタイトルを指定セルに格納
ws.Cell(row, col).Value = issue.Title;
col++;
//本文の見出し部分を除き、リストで格納
List <string> tempList = new List <string>();
tempList.AddRange(issue.Body.Split(new string[] { "##" }, StringSplitOptions.RemoveEmptyEntries));
//本文（見出し除く）を見出しごとにセルに格納
for (int c = 0; c < tempList.Count; c++)
{
int index = tempList[c].IndexOf('\n');
ws.Cell(row, col).Value = tempList[c].Substring(index + 1);
col++;
}
//issueにアサインしている人がひとりでもいたら
if (issue.Assignees.Count != 0)
{
//アサインしているアカウント名を指定セルに格納
ws.Cell(row, col).Value = issue.Assignees[0].Login;
}
col++;
//issueが作成された日付を指定セルに格納
ws.Cell(row, col).Value = issue.CreatedAt.DateTime.ToString();
col++;
//issueが更新されたことがあったら
if (issue.UpdatedAt.HasValue)
{
//issueの更新日付を指定セルに格納
ws.Cell(row, col).Value = ((DateTimeOffset)issue.UpdatedAt).DateTime.ToString();
}
//現在issueがcloseされていたら
if (issue.ClosedAt.HasValue)
{
//指定セルの行の背景色を灰色にする
IXLRange range = ws.Range(ws.Cell(row, 2), ws.Cell(row, col));
range.Style.Fill.BackgroundColor = XLColor.Gray; // 灰色
}
//リストの最後尾だったら
if (issueList.Last().Equals(issue))
{
//表形式にするため罫線をつける
IXLRange range = ws.Range(headerCell, ws.Cell(row, col));
range.Style
.Border.SetOutsideBorder(XLBorderStyleValues.Thin)
.Border.SetInsideBorder(XLBorderStyleValues.Hair)
.Border.SetLeftBorder(XLBorderStyleValues.Thin)
.Border.SetRightBorder(XLBorderStyleValues.Thin);
}
//次の行に行く
row++;
col = 2;
}
//検索用フィルターをつける
ws.RangeUsed().SetAutoFilter();
row++;
i++;
}
//引数のパスにExcelファイルを保存する
book.SaveAs(filePath);
}
//ファイル送信用slackAPIのURl
string url = baseUrl + "files.upload?channels=" + GitHubDialog.channelName;
//指定パスのファイルでファイル送信APIを送る
Encoding.UTF8.GetString(fileUpload(url, filePath));
//出力完了のメッセージを送信する
await context.PostAsync("出力が完了しました");
//context.Wait(MessageReceivedAsync);
//}
//catch (Exception e)
//{
// await context.PostAsync(e.Message);
// context.Wait(MessageReceivedAsync);
//}
}
private IXLCell CreateXLSXContentLines(ZustaendigAgOrtsTeilOrt ZustaendigAgOrtsTeilOrtInstance,
IXLWorksheet workSheet, IXLCell StartCell,
StatistikRootClass.StatistikDataSelectionTypes writeOutType,
bool StandardContent = true, bool ProcessOrte = true)
{
int ActuallRow = StartCell.WorksheetRow().RowNumber() + 1;
int FirstCellNumber = StartCell.WorksheetColumn().ColumnNumber();
IXLCell LastCell = null;
foreach (AGCounter agCounter in ZustaendigAgOrtsTeilOrtInstance.Children)
{
if (StandardContent)
{
workSheet.Cell(ActuallRow, FirstCellNumber).Value = agCounter.ArbeitsGruppeDaten.NameId;
workSheet.Cell(ActuallRow, FirstCellNumber + 1).Value
= agCounter.ArbeitsGruppeDaten.Beschreibung;
}
ActuallRow++;
foreach (OrtsTeilCounter ortsTeilCounter in agCounter.Children)
{
if (StandardContent)
{
workSheet.Cell(ActuallRow, FirstCellNumber + 1).Value
= ortsTeilCounter.OrtsTeilDaten.NameId;
}
if (ProcessOrte)
{
ActuallRow++;
if (StandardContent)
{
foreach (OrtsCounter ortsCounter in ortsTeilCounter.Children)
{
workSheet.Cell(ActuallRow, FirstCellNumber + 2).Value
= ortsCounter.OrtDaten.Bezeichnung;
LastCell = ShowStandardDataPerEntry(workSheet, ortsCounter.Counter,
ActuallRow, 6);
ActuallRow = LastCell.Address.RowNumber + 1;
}
if (ProcessOrte)
{
IXLRange OrtsTeilLine = workSheet.Range(ActuallRow, FirstCellNumber + 2, ActuallRow, FirstCellNumber + 3).Merge();
OrtsTeilLine.Style.Font.SetBold();
OrtsTeilLine.Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Right);
OrtsTeilLine.Value = ortsTeilCounter.OrtsTeilDaten.NameId + " - Summe";
LastCell = ShowStandardDataPerEntry(workSheet, ortsTeilCounter.Counter,
ActuallRow, 6);
ActuallRow = LastCell.Address.RowNumber;
}
}
else
{
foreach (OrtsCounter ortsCounter in ortsTeilCounter.Children)
{
LastCell = ShowDataPerEntry(workSheet, writeOutType, ortsCounter.Counter,
ActuallRow, StartCell.Address.ColumnNumber);
ActuallRow = LastCell.Address.RowNumber + 1;
}
if (ProcessOrte)
{
LastCell = ShowDataPerEntry(workSheet, writeOutType, ortsTeilCounter.Counter,
ActuallRow, StartCell.Address.ColumnNumber);
ActuallRow = LastCell.Address.RowNumber;
}
}
ActuallRow++;
}
else
{
if (StandardContent)
{
LastCell = ShowStandardDataPerEntry(workSheet, ortsTeilCounter.Counter,
ActuallRow, 6);
}
else
{
LastCell = ShowDataPerEntry(workSheet, writeOutType, ortsTeilCounter.Counter,
ActuallRow, StartCell.Address.ColumnNumber);
}
ActuallRow = LastCell.Address.RowNumber + 1;
}
}
}
return LastCell;
}
public void TestDelete()
{
// Deleting with moving cells up
XLWorkbook wb = InitWorkBookForDeleteRangeTest();
IXLWorksheet ws = wb.Worksheet("Test");
IXLRange range = ws.NamedRange("TestRange").Ranges.ElementAt(0);
var excelReport = Substitute.For <object>();
var templateProcessor = Substitute.For <ITemplateProcessor>();
var panel = new ExcelPanel(range, excelReport, templateProcessor);
panel.Delete();
IXLCell rangeStartCell = ws.Cells().SingleOrDefault(c => c.Value.ToString() == "RangeStart");
IXLCell rangeEndCell = ws.Cells().SingleOrDefault(c => c.Value.ToString() == "RangeEnd");
IXLCell belowCell1 = ws.Cells().Single(c => c.Value.ToString() == "BelowCell_1");
IXLCell belowCell2 = ws.Cells().Single(c => c.Value.ToString() == "BelowCell_2");
IXLCell rightCell1 = ws.Cells().Single(c => c.Value.ToString() == "RightCell_1");
IXLCell rightCell2 = ws.Cells().Single(c => c.Value.ToString() == "RightCell_2");
IXLCell aboveCell1 = ws.Cells().Single(c => c.Value.ToString() == "AboveCell_1");
IXLCell aboveCell2 = ws.Cells().Single(c => c.Value.ToString() == "AboveCell_2");
IXLCell leftCell1 = ws.Cells().Single(c => c.Value.ToString() == "LeftCell_1");
IXLCell leftCell2 = ws.Cells().Single(c => c.Value.ToString() == "LeftCell_2");
Assert.IsNull(rangeStartCell);
Assert.IsNull(rangeEndCell);
Assert.AreEqual(8, ws.CellsUsed(XLCellsUsedOptions.Contents).Count());
Assert.AreEqual(belowCell1, ws.Cell(6, 6));
Assert.AreEqual(belowCell2, ws.Cell(10, 8));
Assert.AreEqual(rightCell1, ws.Cell(7, 8));
Assert.AreEqual(rightCell2, ws.Cell(5, 8));
Assert.AreEqual(aboveCell1, ws.Cell(5, 6));
Assert.AreEqual(aboveCell2, ws.Cell(5, 4));
Assert.AreEqual(leftCell1, ws.Cell(7, 4));
Assert.AreEqual(leftCell2, ws.Cell(10, 4));
// Deleting with moving the row up
wb = InitWorkBookForDeleteRangeTest();
ws = wb.Worksheet("Test");
range = ws.NamedRange("TestRange").Ranges.ElementAt(0);
panel = new ExcelPanel(range, excelReport, templateProcessor)
{
ShiftType = ShiftType.Row
};
panel.Delete();
rangeStartCell = ws.Cells().SingleOrDefault(c => c.Value.ToString() == "RangeStart");
rangeEndCell = ws.Cells().SingleOrDefault(c => c.Value.ToString() == "RangeEnd");
belowCell1 = ws.Cells().Single(c => c.Value.ToString() == "BelowCell_1");
belowCell2 = ws.Cells().Single(c => c.Value.ToString() == "BelowCell_2");
rightCell1 = ws.Cells().SingleOrDefault(c => c.Value.ToString() == "RightCell_1");
rightCell2 = ws.Cells().Single(c => c.Value.ToString() == "RightCell_2");
aboveCell1 = ws.Cells().Single(c => c.Value.ToString() == "AboveCell_1");
aboveCell2 = ws.Cells().Single(c => c.Value.ToString() == "AboveCell_2");
leftCell1 = ws.Cells().SingleOrDefault(c => c.Value.ToString() == "LeftCell_1");
leftCell2 = ws.Cells().Single(c => c.Value.ToString() == "LeftCell_2");
Assert.IsNull(rangeStartCell);
Assert.IsNull(rangeEndCell);
Assert.IsNull(leftCell1);
Assert.IsNull(rightCell1);
Assert.AreEqual(6, ws.CellsUsed(XLCellsUsedOptions.Contents).Count());
Assert.AreEqual(belowCell1, ws.Cell(6, 6));
Assert.AreEqual(belowCell2, ws.Cell(6, 8));
Assert.AreEqual(rightCell2, ws.Cell(5, 8));
Assert.AreEqual(aboveCell1, ws.Cell(5, 6));
Assert.AreEqual(aboveCell2, ws.Cell(5, 4));
Assert.AreEqual(leftCell2, ws.Cell(6, 4));
// Deleting with moving cells left
wb = InitWorkBookForDeleteRangeTest();
ws = wb.Worksheet("Test");
range = ws.NamedRange("TestRange").Ranges.ElementAt(0);
panel = new ExcelPanel(range, excelReport, templateProcessor)
{
Type = PanelType.Horizontal
};
panel.Delete();
rangeStartCell = ws.Cells().SingleOrDefault(c => c.Value.ToString() == "RangeStart");
rangeEndCell = ws.Cells().SingleOrDefault(c => c.Value.ToString() == "RangeEnd");
belowCell1 = ws.Cells().Single(c => c.Value.ToString() == "BelowCell_1");
belowCell2 = ws.Cells().Single(c => c.Value.ToString() == "BelowCell_2");
rightCell1 = ws.Cells().Single(c => c.Value.ToString() == "RightCell_1");
rightCell2 = ws.Cells().Single(c => c.Value.ToString() == "RightCell_2");
aboveCell1 = ws.Cells().Single(c => c.Value.ToString() == "AboveCell_1");
aboveCell2 = ws.Cells().Single(c => c.Value.ToString() == "AboveCell_2");
leftCell1 = ws.Cells().Single(c => c.Value.ToString() == "LeftCell_1");
leftCell2 = ws.Cells().Single(c => c.Value.ToString() == "LeftCell_2");
Assert.IsNull(rangeStartCell);
Assert.IsNull(rangeEndCell);
Assert.AreEqual(8, ws.CellsUsed(XLCellsUsedOptions.Contents).Count());
Assert.AreEqual(belowCell1, ws.Cell(10, 6));
Assert.AreEqual(belowCell2, ws.Cell(10, 8));
Assert.AreEqual(rightCell1, ws.Cell(7, 5));
Assert.AreEqual(rightCell2, ws.Cell(5, 8));
Assert.AreEqual(aboveCell1, ws.Cell(5, 6));
Assert.AreEqual(aboveCell2, ws.Cell(5, 4));
Assert.AreEqual(leftCell1, ws.Cell(7, 4));
Assert.AreEqual(leftCell2, ws.Cell(10, 4));
// Deleting with moving the column left
wb = InitWorkBookForDeleteRangeTest();
ws = wb.Worksheet("Test");
range = ws.NamedRange("TestRange").Ranges.ElementAt(0);
panel = new ExcelPanel(range, excelReport, templateProcessor)
{
Type = PanelType.Horizontal, ShiftType = ShiftType.Row
};
panel.Delete();
rangeStartCell = ws.Cells().SingleOrDefault(c => c.Value.ToString() == "RangeStart");
rangeEndCell = ws.Cells().SingleOrDefault(c => c.Value.ToString() == "RangeEnd");
belowCell1 = ws.Cells().SingleOrDefault(c => c.Value.ToString() == "BelowCell_1");
belowCell2 = ws.Cells().Single(c => c.Value.ToString() == "BelowCell_2");
rightCell1 = ws.Cells().SingleOrDefault(c => c.Value.ToString() == "RightCell_1");
rightCell2 = ws.Cells().Single(c => c.Value.ToString() == "RightCell_2");
aboveCell1 = ws.Cells().SingleOrDefault(c => c.Value.ToString() == "AboveCell_1");
aboveCell2 = ws.Cells().Single(c => c.Value.ToString() == "AboveCell_2");
leftCell1 = ws.Cells().SingleOrDefault(c => c.Value.ToString() == "LeftCell_1");
leftCell2 = ws.Cells().Single(c => c.Value.ToString() == "LeftCell_2");
Assert.IsNull(rangeStartCell);
Assert.IsNull(rangeEndCell);
Assert.IsNull(aboveCell1);
Assert.IsNull(belowCell1);
Assert.AreEqual(6, ws.CellsUsed(XLCellsUsedOptions.Contents).Count());
Assert.AreEqual(belowCell2, ws.Cell(10, 5));
Assert.AreEqual(rightCell1, ws.Cell(7, 5));
Assert.AreEqual(rightCell2, ws.Cell(5, 5));
Assert.AreEqual(aboveCell2, ws.Cell(5, 4));
Assert.AreEqual(leftCell1, ws.Cell(7, 4));
Assert.AreEqual(leftCell2, ws.Cell(10, 4));
// Deleting without any shift
wb = InitWorkBookForDeleteRangeTest();
ws = wb.Worksheet("Test");
range = ws.NamedRange("TestRange").Ranges.ElementAt(0);
panel = new ExcelPanel(range, excelReport, templateProcessor)
{
ShiftType = ShiftType.NoShift
};
panel.Delete();
rangeStartCell = ws.Cells().SingleOrDefault(c => c.Value.ToString() == "RangeStart");
rangeEndCell = ws.Cells().SingleOrDefault(c => c.Value.ToString() == "RangeEnd");
belowCell1 = ws.Cells().Single(c => c.Value.ToString() == "BelowCell_1");
belowCell2 = ws.Cells().Single(c => c.Value.ToString() == "BelowCell_2");
rightCell1 = ws.Cells().Single(c => c.Value.ToString() == "RightCell_1");
rightCell2 = ws.Cells().Single(c => c.Value.ToString() == "RightCell_2");
aboveCell1 = ws.Cells().Single(c => c.Value.ToString() == "AboveCell_1");
aboveCell2 = ws.Cells().Single(c => c.Value.ToString() == "AboveCell_2");
leftCell1 = ws.Cells().Single(c => c.Value.ToString() == "LeftCell_1");
leftCell2 = ws.Cells().Single(c => c.Value.ToString() == "LeftCell_2");
Assert.IsNull(rangeStartCell);
Assert.IsNull(rangeEndCell);
Assert.AreEqual(XLBorderStyleValues.None, range.FirstCell().Style.Border.TopBorder);
Assert.AreEqual(XLBorderStyleValues.None, range.LastCell().Style.Border.BottomBorder);
Assert.AreEqual(8, ws.CellsUsed(XLCellsUsedOptions.Contents).Count());
Assert.AreEqual(belowCell1, ws.Cell(10, 6));
Assert.AreEqual(belowCell2, ws.Cell(10, 8));
Assert.AreEqual(rightCell1, ws.Cell(7, 8));
Assert.AreEqual(rightCell2, ws.Cell(5, 8));
Assert.AreEqual(aboveCell1, ws.Cell(5, 6));
Assert.AreEqual(aboveCell2, ws.Cell(5, 4));
Assert.AreEqual(leftCell1, ws.Cell(7, 4));
Assert.AreEqual(leftCell2, ws.Cell(10, 4));
//wb.SaveAs("test.xlsx");
}
public XLHyperlink(IXLCell cell)
{
SetValues(cell, String.Empty);
}
private void RenderCell(IXLWorksheet worksheet, IElement cellNode, int row, ref int col)
{
IXLCell cell = worksheet.Cell(row, col);
cell.Value = cellNode.TextContent.SafeTrim();
if (cellNode.NodeName == "th")
{
cell.Style.Font.Bold = true;
}
if (cellNode.Attributes != null && cellNode.Attributes.Any())
{
IAttr boldAttribute = cellNode.Attributes.SingleOrDefault(x => x.Name == "data-excel-bold");
if (boldAttribute != null)
{
if (bool.TryParse(boldAttribute.Value, out bool isBold))
{
cell.Style.Font.Bold = isBold;
}
}
IAttr hyperlinkAttribute = cellNode.Attributes.SingleOrDefault(x => x.Name == "data-excel-hyperlink");
if (hyperlinkAttribute != null)
{
if (Uri.TryCreate(hyperlinkAttribute.Value, UriKind.Absolute, out Uri uri))
{
cell.Hyperlink = new XLHyperlink(uri);
}
else
{
cell.Comment
.SetAuthor("TowerSoft.HtmlToExcel")
.AddSignature()
.AddText($"Unable to parse hyperlink: {hyperlinkAttribute.Value}");
}
}
IAttr commentAttribute = cellNode.Attributes.SingleOrDefault(x => x.Name == "data-excel-comment");
if (commentAttribute != null && !string.IsNullOrWhiteSpace(commentAttribute.Value))
{
string author = "System";
IAttr authorAttribute = cellNode.Attributes.SingleOrDefault(x => x.Name == "data-excel-comment-author");
if (authorAttribute != null && !string.IsNullOrWhiteSpace(authorAttribute.Value))
{
author = authorAttribute.Value;
cell.Comment.SetAuthor(author).AddSignature();
}
cell.Comment.AddText(commentAttribute.Value);
}
}
if (int.TryParse(cellNode.GetAttribute("colspan"), out int colspan))
{
if (colspan > 1)
{
worksheet.Range(worksheet.Cell(row, col), worksheet.Cell(row, col + colspan - 1)).Merge();
hasMergedCells = true;
col += colspan;
}
else
{
col++;
}
}
else
{
col++;
}
}
private void addRightBottomBorder(IXLCell cell, XLBorderStyleValues border = XLBorderStyleValues.Thick, XLColor color = null)
{
if (color == null)
{
color = XLColor.Black;
}
addBottomBorder(cell, border, color);
addRightBorder(cell, border, color);
}
private DataTable ConvertWorksheetToDataTable(IXLWorksheet worksheet)
{
var dataTable = new DataTable();
List <int> formattedColumnNumbers = new List <int>();
IXLRange range = worksheet.Range(worksheet.FirstCellUsed(), worksheet.LastCellUsed());
int columnCount = range.ColumnCount();
dataTable.Clear();
for (int i = 1; i <= columnCount; i++)
{
IXLCell cell = worksheet.Cell(1, i);
Type dataTableColumnType = GetDataType(range, cell.Address.ColumnNumber);
dataTable.Columns.Add(cell.Value.ToString(), dataTableColumnType);
}
int firstHeadRow = 0;
foreach (var row in range.Rows())
{
if (firstHeadRow != 0)
{
var array = new object[columnCount];
for (int y = 1; y <= columnCount; y++)
{
if (!row.Cell(y).HasFormula)
{
if (row.Cell(y).Value == null || string.IsNullOrEmpty(row.Cell(y).Value.ToString()))
{
array[y - 1] = null;
}
else if (formattedColumnNumbers.Contains(row.Cell(y).Address.ColumnNumber))
{
array[y - 1] = row.Cell(y).GetFormattedString();
}
else if (row.Cell(y).DataType == XLDataType.DateTime)
{
DateTime dateTime = row.Cell(y).GetDateTime();
array[y - 1] = dateTime.TimeOfDay.Ticks == 0
? dateTime.ToString("MM/dd/yyyy")
: dateTime.ToString();
}
else
{
row.Cell(y).SetDataType(XLDataType.Text);
array[y - 1] = row.Cell(y).Value;
}
}
}
dataTable.Rows.Add(array);
}
firstHeadRow++;
}
return(dataTable);
}
IXLRangeRow IXLRow.CopyTo(IXLCell target)
{
using (var asRange = AsRange())
using (var copy = asRange.CopyTo(target))
return copy.Row(1);
}
public async Task <bool> ExportToExcelFileAsync(DataToExport dataToExport, string filePath)
{
bool success = false;
await Task.Run(() =>
{
try
{
using var wb = new XLWorkbook();
IXLWorksheet ws = wb.AddWorksheet();
string canalType = dataToExport.CanalCharacteristics.canalType;
IList <Parameter> canalGeometryParameters = dataToExport.CanalCharacteristics.Item2;
ws.Cell("A2").SetValue("Тип канала");
ws.Cell("A3").SetValue(canalType);
for (int i = 0; i < canalGeometryParameters.Count; i++)
{
ws.Cell(2, i + 4).SetValue(canalGeometryParameters[i].Name);
ws.Cell(3, i + 4).SetValue(canalGeometryParameters[i].Value + $" {canalGeometryParameters[i].MeasureUnit}");
}
string materialType = dataToExport.MaterialCharacteristics.materialType;
IList <Parameter> materialPropertyParameters = dataToExport.MaterialCharacteristics.Item2;
ws.Cell("A5").SetValue("Тип материала");
ws.Cell("A6").SetValue(materialType);
for (int i = 0; i < materialPropertyParameters.Count; i++)
{
ws.Cell(5, i + 4).SetValue(materialPropertyParameters[i].Name);
ws.Cell(6, i + 4).SetValue(materialPropertyParameters[i].Value + $" {materialPropertyParameters[i].MeasureUnit}");
}
IXLColumn lastMergedColumn = ws.LastColumnUsed();
ws.Range(ws.Cell(1, 1), ws.LastColumnUsed().Cell(1)).Merge().SetValue("Входные параметры");
IXLCell xLCell = ws.LastColumnUsed().ColumnRight().ColumnRight().Cell(2);
IList <Parameter> variableParameters = dataToExport.VariableParameters;
for (int i = 0; i < variableParameters.Count; i++)
{
xLCell.SetValue(variableParameters[i].Name);
xLCell = xLCell.CellBelow().SetValue(variableParameters[i].Value + $" {variableParameters[i].MeasureUnit}");
xLCell = xLCell.CellRight().CellAbove();
}
ws.Range(lastMergedColumn.ColumnRight().ColumnRight().Cell(1),
ws.LastColumnUsed().Cell(1)).Merge().SetValue("Варьируемые параметры");
lastMergedColumn = ws.LastColumnUsed();
xLCell = ws.LastColumnUsed().ColumnRight().ColumnRight().Cell(2);
IList <Parameter> empiricalParametersOfMathModel = dataToExport.EmpiricalParametersOfMathModel;
for (int i = 0; i < empiricalParametersOfMathModel.Count; i++)
{
xLCell.SetValue(empiricalParametersOfMathModel[i].Name);
xLCell = xLCell.CellBelow().SetValue(empiricalParametersOfMathModel[i].Value + $" {empiricalParametersOfMathModel[i].MeasureUnit}");
xLCell = xLCell.CellRight().CellAbove();
}
ws.Range(lastMergedColumn.ColumnRight().ColumnRight().Cell(1),
ws.LastColumnUsed().Cell(1)).Merge().SetValue("Эмпирические коэффициенты математической модели");
IDictionary <string, IList <Parameter> > discreteOutputParameters = dataToExport.DiscreteOutputParameters;
xLCell = xLCell.CellBelow().CellBelow().CellBelow().CellBelow().CellBelow().CellBelow().CellBelow().CellBelow()
.WorksheetRow().Cell(1);
foreach (KeyValuePair <string, IList <Parameter> > keyValuePair in discreteOutputParameters)
{
IXLCell firstCell = xLCell;
foreach (Parameter parameter in keyValuePair.Value)
{
xLCell.SetValue(parameter.Name);
xLCell = xLCell.CellBelow().SetValue($"{parameter.Value} {parameter.MeasureUnit}");
xLCell = xLCell.CellAbove().CellRight();
}
if (keyValuePair.Value.Count <= 0)
{
continue;
}
if (keyValuePair.Value.Count == 1)
{
xLCell = xLCell.CellRight();
continue;
}
ws.Range(firstCell.CellAbove(), ws.LastRowUsed().LastCellUsed().CellAbove().CellAbove()).
Merge().SetValue(keyValuePair.Key);
xLCell = xLCell.CellRight();
}
xLCell = xLCell.CellLeft().CellLeft().CellAbove().CellAbove();
ws.Range(xLCell, xLCell.WorksheetRow().Cell(1)).Merge().SetValue("Результаты");
xLCell = ws.LastRowUsed().Cell(1).CellBelow().CellBelow();
ws.Range(xLCell, xLCell.CellRight().CellRight().CellRight()).Merge().SetValue("Таблица результатов");
IList <(Parameter coordinate, Parameter temperature, Parameter viscosity)> resultsTable = dataToExport.ContiniousResults;
xLCell.CellBelow().SetValue($"{resultsTable[0].coordinate.Name}, {resultsTable[0].coordinate.MeasureUnit}")
.CellRight().SetValue($"{resultsTable[0].temperature.Name}, {resultsTable[0].temperature.MeasureUnit}")
.CellRight().SetValue($"{resultsTable[0].viscosity.Name}, {resultsTable[0].viscosity.MeasureUnit}");
xLCell = xLCell.CellBelow().CellBelow();
NumberFormatInfo nfi = new NumberFormatInfo
{
NumberDecimalSeparator = "."
};
foreach (var(coordinate, temperature, viscosity) in resultsTable)
{
xLCell.SetValue(((double)coordinate.Value)
.ToString($"F{dataToExport.CoordinatePrecision}", nfi))
.SetDataType(XLDataType.Number)
.CellRight().SetValue(((double)temperature.Value).ToString($"F2", nfi))
.SetDataType(XLDataType.Number)
.CellRight().SetValue(((double)viscosity.Value).ToString("F2", nfi))
.SetDataType(XLDataType.Number);
xLCell = xLCell.CellBelow();
}
//ws.RowsUsed().AdjustToContents();
//ws.ColumnsUsed().AdjustToContents();
//using var stream = new MemoryStream();
//dataToExport.TemperaturePlot.Save(stream, ImageFormat.Png);
//ws.AddPicture(stream).MoveTo(ws.Cell("H8"))
// .WithSize(dataToExport.TemperaturePlot.Width, dataToExport.TemperaturePlot.Height);
//using var stream1 = new MemoryStream();
//dataToExport.ViscosityPlot.Save(stream1, ImageFormat.Png);
//ws.AddPicture(stream1).MoveTo(ws.Cell("O8"))
// .WithSize(dataToExport.ViscosityPlot.Width, dataToExport.ViscosityPlot.Height);
wb.SaveAs(filePath);
success = true;
}
catch { success = false; }
});
return(success);
}
/// -----------------------------------------------------------------------------
/// <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);
}
}
}
//This bit is needed because excel often thinks numbers are text
//ClosedXML seems to have trouble with some references to include this to get around exceptions
private string GetSingleCellTextValue(IXLCell cell)
{
return((string)cell.CachedValue);
}
private void SetFormat(IXLCell cell, object column, bool formatWithEuros)
{
_dataFormat.ApplyFormat(cell, column, formatWithEuros);
}
/// <summary>
/// Adds simplicity to adding cell styles
/// </summary>
private static void setOrAddCellStyle( IXLCell cell, bool bold = false, bool textWrapped = false, bool date = false )
{
if( bold )
cell.Style.Font.Bold = true;
if( textWrapped )
cell.Style.Alignment.WrapText = true;
if( date )
cell.Style.DateFormat.SetFormat( "mm/dd/yyyy" );
}
public IXLSparklineGroup Add(IXLCell location, IXLRange sourceData)
{
return(Add(new XLSparklineGroup(location, sourceData)));
}
/// <summary>
/// Performs application-defined tasks associated with freeing, releasing, or resetting unmanaged resources.
/// </summary>
public void Dispose()
{
current = null;
}
public List <SecondParamModel> ManupilateSecondPart(IXLWorksheet worksheet)
{
System.Collections.Generic.List <SecondParamModel> secondParamModels = new List <SecondParamModel>();
var lastRowNumber = worksheet.LastRowUsed().RowNumber();
var lastColumnNumber = worksheet.LastColumnUsed().ColumnNumber();
var secontRangeAdditional = worksheet.Range(_startRow, 1, lastRowNumber, 1);
var rowList = secontRangeAdditional.AsTable().DataRange.Rows().ToList();
var itemLastRow = rowList.Find(o =>
{
var value = o.FirstCell().Value;
return(value != null && value is string && (string)value == "Clock hours");
});
if (itemLastRow != null)
{
int lastItemRowNumber = itemLastRow.RowNumber() - 3;
var secondRange = worksheet.Range(_startRow, 1, lastItemRowNumber - 1, lastColumnNumber);
var secondRowList = secondRange.AsRange().Rows().ToList();
List <int> worksheetDateList = DateList(worksheet);
int i = 0;
foreach (IXLRangeRow row in secondRowList)
{
if (i % 2 == 0)
{
SecondParamModel paramModel = new SecondParamModel();
var cellList = row.Cells().ToList();
paramModel.ItemCode = cellList[0].Value.ToString();
paramModel.Category = cellList[1].Value.ToString();
paramModel.Style = cellList[2].Value.ToString();
paramModel.Item = cellList[3].Value.ToString();
//TODO : convert int string
//order
string oqty = cellList[4].Value.ToString();
if (oqty != "" && oqty != "TBA")
{
double d = Double.Parse(oqty);
d = Math.Round(d, 0);
paramModel.OrderQty = (d.ToString(CultureInfo.InvariantCulture).Split('.')[0]);
}
else
{
paramModel.OrderQty = (oqty);
}
//plan
string pqty = cellList[5].Value.ToString();
if (pqty != "" && pqty != "TBA")
{
double d = Double.Parse(pqty);
d = Math.Round(d, 0);
paramModel.PlanQty = (d.ToString(CultureInfo.InvariantCulture).Split('.')[0]);
}
else
{
paramModel.PlanQty = (pqty);
}
paramModel.SMV = cellList[6].Value.ToString();
paramModel.SMO = cellList[7].Value.ToString();
Dictionary <int, string> dateDictionary = new Dictionary <int, string>();
for (int j = 8; j < lastColumnNumber; j++)
{
IXLCell cell = cellList[j];
var dateValue = cell.Value.ToString();
if (dateValue != "")
{
double d = Double.Parse(dateValue);
d = Math.Round(d, 0);
dateDictionary.Add(worksheetDateList[j - 8], d.ToString(CultureInfo.InvariantCulture).Split('.')[0]);
}
else
{
dateDictionary.Add(worksheetDateList[j - 8], dateValue);
}
}
paramModel.DateList = dateDictionary;
secondParamModels.Add(paramModel);
}
i++;
}
}
return(secondParamModels);
}
public void NotEqualTo(IXLCell cell)
{
dataValidation.Value = cell.Address.ToStringFixed();
dataValidation.Operator = XLOperator.NotEqualTo;
}
/// <summary>
/// Initializes a new instance of the <see cref="WorksheetWriter"/> class.
/// </summary>
/// <param name="xlWorksheet">The xl worksheet.</param>
public WorksheetWriter(IXLWorksheet xlWorksheet)
{
ArgumentCheck.IsNotNull(xlWorksheet);
current = xlWorksheet.Cell(1,1);
}
public IXLPicture MoveTo(IXLCell cell)
{
return(MoveTo(cell, 0, 0));
}
/// <summary>
/// Moves to the specified cell using the address
/// </summary>
/// <param name="address"></param>
public void MoveTo(string address)
{
current = current.Worksheet.Cell(address);
}
public IXLPicture MoveTo(IXLCell cell, Int32 xOffset, Int32 yOffset)
{
return(MoveTo(cell, new Point(xOffset, yOffset)));
}
/// <summary>
/// Writes the specified value to the current cell and moves to the next column
/// </summary>
/// <param name="value">The value.</param>
public void Write(string value)
{
current.Value = value;
current = current.CellRight();
}
public IXLPicture MoveTo(IXLCell fromCell, IXLCell toCell)
{
return(MoveTo(fromCell, 0, 0, toCell, 0, 0));
}
IXLRange IXLWorksheet.Range(IXLCell firstCell, IXLCell lastCell)
{
return Range(firstCell, lastCell);
}
public IXLPicture MoveTo(IXLCell fromCell, Int32 fromCellXOffset, Int32 fromCellYOffset, IXLCell toCell, Int32 toCellXOffset, Int32 toCellYOffset)
{
return(MoveTo(fromCell, new Point(fromCellXOffset, fromCellYOffset), toCell, new Point(toCellXOffset, toCellYOffset)));
}
private int CreateXLSXHeadLines(IXLWorksheet workSheet, IXLCell StartTableCell,
StatistikRootClass.StatistikDataSelectionTypes writeOutType)
{
workSheet.Column("A").Width = 2;
int LineNumber = StartTableCell.WorksheetRow().RowNumber();
int StartColumnIndex = StartTableCell.WorksheetColumn().ColumnNumber();
// FullTable.Theme = XLTableTheme.TableStyleLight20;
CreateGroupHeader(workSheet, "AG's, OrtsTeile, Orte", LineNumber, StartColumnIndex, 6);
workSheet.Column(StartColumnIndex).Width = 3;
workSheet.Column(StartColumnIndex + 1).Width = 3;
workSheet.Column(StartColumnIndex + 2).Width = 3;
int OldStartColumnIndex = StartColumnIndex;
foreach (string LevelText in StandardCounter.AllHeadLines["LevelHeadLines"])
{
if ((LevelText == "Gesamt")
|| (LevelText == "AddProcesses")
|| (LevelText == "Beschreibung")
)
continue;
CreateVerticalNumberHeader(workSheet, LevelText, LineNumber + 1, StartColumnIndex++);
}
workSheet.Column(OldStartColumnIndex + 3).Width = 20;
workSheet.Column(StartColumnIndex).Width = 1;
workSheet.Cell(LineNumber, StartColumnIndex++).Style.Fill.SetBackgroundColor(XLColor.White);
CreateGroupHeader(workSheet, "WMU Punkte", LineNumber, StartColumnIndex, 3);
// StartColumnIndex = 8;
foreach (string LevelText in StandardCounter.AllHeadLines["WMUHeadLines"])
{
CreateVerticalNumberHeader(workSheet,
LevelText.Replace("W", "Weiblich").Replace("M", "Männlich").Replace("U", "Unbekannt"),
LineNumber + 1, StartColumnIndex++);
}
workSheet.Column(StartColumnIndex).Width = 1;
workSheet.Cell(LineNumber, StartColumnIndex++).Style.Fill.SetBackgroundColor(XLColor.White);
CreateGroupHeader(workSheet, "Projekt Phasen", LineNumber, StartColumnIndex, 5);
foreach (string LevelText in StandardCounter.AllHeadLines["ProjektPhasenHeadLines"])
{
CreateVerticalNumberHeader(workSheet, LevelText,
LineNumber + 1, StartColumnIndex++);
}
// StandardCounter.AllHeadLines
IXLCell StartCell = workSheet.Cell(LineNumber, StartColumnIndex);
int RunningColumn = StartCell.Address.ColumnNumber;
int RunningRow = StartCell.Address.RowNumber;
foreach (string writeOutSpecificHeadLine in StandardCounter.GetWriteOutSpecificHeadLines(writeOutType).Keys)
{
workSheet.Column(RunningColumn).Width = 1;
workSheet.Cell(RunningRow, RunningColumn++).Style.Fill.SetBackgroundColor(XLColor.White);
CreateGroupHeader(workSheet, writeOutSpecificHeadLine, LineNumber, RunningColumn,
StandardCounter.GetWriteOutSpecificHeadLines(writeOutType)[writeOutSpecificHeadLine].Count);
foreach (string ColumText in StandardCounter.GetWriteOutSpecificHeadLines(writeOutType)[writeOutSpecificHeadLine])
{
CreateVerticalNumberHeader(workSheet, ColumText, RunningRow + 1, RunningColumn++);
}
}
return 0;
}
/// <summary>
/// Return true if it have at least 1 DTOValidacionArchivo object instance. Q: Have any format error? YES(TRUE) or NO(FALSE)
/// </summary>
/// <param name="template">Template format object</param>
/// <param name="cell">IXL Cell objet</param>
/// <returns>True for any new instance DTOValidacionArchivo. If DTOValidacionArchivo is NULL then return false</returns>
private bool Validator_Cell(TemplateFormatCAC template, IXLCell cell)
{
bool flag = false;
try
{
DTOValidacionArchivo validation = null;
#region Validacion si es null
if (cell.IsEmpty() == true && template.Nullable == false)
{
validation = new DTOValidacionArchivo()
{
FechaCreacion = DateTime.Now.ToString(Configuration.GetValueConf(Constants.DateFormat)),
Descripcion = string.Format(Resource_DefaultMessage.ERROR_CELL_EMPTY_OR_NULL, cell.WorksheetColumn().ColumnLetter(), cell.WorksheetRow().RowNumber(), template.Name),
Valor = cell.GetString(),
Celda = $"{cell.WorksheetColumn().ColumnLetter()}{cell.WorksheetRow().RowNumber()}",
Fila = $"{cell.WorksheetRow().RowNumber()}",
Columna = $"{cell.WorksheetColumn().ColumnLetter()}"
};
Auditor.SaveLog(string.Format(Resource_DefaultMessage.CONTROL_VALUE, nameof(FileProcessBP.Validator_Cell), validation.Columna, validation.Valor, "ERROR_CELL_EMPTY_OR_NULL", validation.ToString(), cell.Worksheet.Name));
validator_result.Add(validation);
}
#endregion
#region Si no es null or empty
else if (cell.IsEmpty() == false)
{
bool hasFormula = cell.HasFormula;
if (hasFormula == true)
{
#region Si tiene formula
validation = new DTOValidacionArchivo()
{
FechaCreacion = DateTime.Now.ToString(Configuration.GetValueConf(Constants.DateFormat)),
Descripcion = string.Format(Resource_DefaultMessage.ERROR_CELL_HAVE_FORMULA, cell.WorksheetColumn().ColumnLetter(), cell.WorksheetRow().RowNumber(), template.Name),
Valor = cell.GetString(),
Celda = $"{cell.WorksheetColumn().ColumnLetter()}{cell.WorksheetRow().RowNumber()}",
Fila = $"{cell.WorksheetRow().RowNumber()}",
Columna = $"{cell.WorksheetColumn().ColumnLetter()}"
};
Auditor.SaveLog(string.Format(Resource_DefaultMessage.CONTROL_VALUE, nameof(FileProcessBP.Validator_Cell), validation.Columna, validation.Valor, "ERROR_CELL_HAVE_FORMULA", validation.ToString(), cell.Worksheet.Name));
validator_result.Add(validation);
#endregion Si tiene formula
}
else
{
#region Validacion del tipo de dato
var cell_datatype = cell.DataType.ToString();
if (template.Type.Contains(cell_datatype) == false)
{
validation = new DTOValidacionArchivo()
{
FechaCreacion = DateTime.Now.ToString(Configuration.GetValueConf(Constants.DateFormat)),
Descripcion = string.Format(Resource_DefaultMessage.ERROR_CELL_NOT_VALID_TYPE, cell.WorksheetColumn().ColumnLetter(), cell.WorksheetRow().RowNumber(), template.Type, cell_datatype, cell.Value, template.Name),
Valor = cell.GetString(),
Celda = $"{cell.WorksheetColumn().ColumnLetter()}{cell.WorksheetRow().RowNumber()}",
Fila = $"{cell.WorksheetRow().RowNumber()}",
Columna = $"{cell.WorksheetColumn().ColumnLetter()}"
};
Auditor.SaveLog(string.Format(Resource_DefaultMessage.CONTROL_VALUE, nameof(FileProcessBP.Validator_Cell), validation.Columna, validation.Valor, "ERROR_CELL_NOT_VALID_TYPE", validation.ToString(), cell.Worksheet.Name));
validator_result.Add(validation);
}
#endregion
#region Validacion si el valor está contenido en la lista predeterminado y si es texto
if (template.SelectList != null && template.SelectList.Count > 0)
{
bool isContained = false;
try
{
isContained = template.SelectList.Where(m => m.Value == cell.GetValue <string>()).Count() > 0 ? true : false;
}
catch (Exception)
{
isContained = false;
}
if (isContained == false)
{
validation = new DTOValidacionArchivo()
{
FechaCreacion = DateTime.Now.ToString(Configuration.GetValueConf(Constants.DateFormat)),
Descripcion = string.Format(Resource_DefaultMessage.ERROR_CELL_DO_NOT_LIST_VALUE, cell.WorksheetColumn().ColumnLetter(), cell.WorksheetRow().RowNumber(), cell.Value, template.Name),
Valor = cell.GetString(),
Celda = $"{cell.WorksheetColumn().ColumnLetter()}{cell.WorksheetRow().RowNumber()}",
Fila = $"{cell.WorksheetRow().RowNumber()}",
Columna = $"{cell.WorksheetColumn().ColumnLetter()}"
};
Auditor.SaveLog(string.Format(Resource_DefaultMessage.CONTROL_VALUE, nameof(FileProcessBP.Validator_Cell), validation.Columna, validation.Valor, "ERROR_CELL_DO_NOT_LIST_VALUE", validation.ToString(), cell.Worksheet.Name));
validator_result.Add(validation);
}
}
#endregion
#region Si es fecha y obtener el valor segun el tipo de dato
var cell_type = cell.Value.GetType();
if (typeof(DateTime).ToString().Contains(cell_type.ToString()) == true || template.Type.Contains(typeof(DateTime).Name))
{
bool regexResult = false;
try
{
DateTime cell_datetime;
if (cell.TryGetValue <DateTime>(out cell_datetime) == true)
{
string datetime_to_comparer = cell_datetime.ToString(template.Format);
string regex = @"^\d{4}-((0\d)|(1[012]))-(([012]\d)|3[01])$";
regexResult = Regex.Match(datetime_to_comparer, regex).Success;
}
else
{
regexResult = false;
}
}
catch (Exception)
{
regexResult = false;
}
if (regexResult == false)
{
validation = new DTOValidacionArchivo()
{
FechaCreacion = DateTime.Now.ToString(Configuration.GetValueConf(Constants.DateFormat)),
Descripcion = string.Format(Resource_DefaultMessage.ERROR_CELL_NOT_FORMAT, cell.WorksheetColumn().ColumnLetter(), cell.WorksheetRow().RowNumber(), template.Format, template.Name),
Valor = cell.GetString(),
Celda = $"{cell.WorksheetColumn().ColumnLetter()}{cell.WorksheetRow().RowNumber()}",
Fila = $"{cell.WorksheetRow().RowNumber()}",
Columna = $"{cell.WorksheetColumn().ColumnLetter()}"
};
Auditor.SaveLog(string.Format(Resource_DefaultMessage.CONTROL_VALUE, nameof(FileProcessBP.Validator_Cell), validation.Columna, validation.Valor, "ERROR_CELL_NOT_FORMAT", validation.ToString(), cell.Worksheet.Name));
validator_result.Add(validation);
}
}
#endregion
}
}
#endregion
flag = validation == null ? false : true;
}
catch (Exception ex)
{
validator_result.Add(ExceptionWriter(ex));
flag = true;
throw ex;
}
template = null;
cell = null;
return(flag);
}
private static void CreateSummaryTable(IXLCell scr)
{
XLWorkbook template = new XLWorkbook(Application.StartupPath + Helper.GetTemplateFileName());
var sheet = template.Worksheet("Template");
var summaryTableRange = sheet.Range(237, 8, 242, 17);
summaryTableRange.CopyTo(scr);
template.Dispose();
}
/// -----------------------------------------------------------------------------
/// <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);
}
}
}
public XLHyperlink(IXLCell cell, String tooltip)
{
SetValues(cell, tooltip);
}
/// <summary>
/// Get range coordinates relative to another range.
/// </summary>
/// <param name="cell">range</param>
/// <param name="baseAddr">Reference system. Coordinates are calculated relative to this range.</param>
public static IXLAddress Relative(this IXLCell cell, IXLAddress baseAddr)
{
return(baseAddr.Worksheet.Cell(
cell.Address.RowNumber - baseAddr.RowNumber + 1,
cell.Address.ColumnNumber - baseAddr.ColumnNumber + 1).Address);
}
IXLRange IXLRange.Range(IXLCell firstCell, IXLCell lastCell)
{
return Range(firstCell, lastCell);
}
public void ExportStockInfo(List <DetailedStockReportInfo> items, DateTime startDate, DateTime endDate, string path)
{
items.Sort((a, b) => (a.Item.Name + a.Item.Description).ToLower().CompareTo((b.Item.Name + b.Item.Description).ToLower()));
var startDateString = startDate.ToString(Utilities.DateTimeToFriendlyJustDateStringFormat());
var endDateString = endDate.ToString(Utilities.DateTimeToFriendlyJustDateStringFormat());
using (var workbook = new XLWorkbook())
{
var worksheet = workbook.Worksheets.Add("Stock Info");
worksheet.Cell("A1").Value = "SimpleInventory -- Stock Info Report for Sold Items";
worksheet.Cell("A1").Style.Font.Bold = true;
worksheet.Cell("A2").Value = startDateString + " - " + endDateString;
// table headers
worksheet.Cell("A4").SetValue("Name").Style.Font.SetBold(true);
worksheet.Cell("B4").SetValue("Description").Style.Font.SetBold(true);
worksheet.Cell("C4").SetValue("Beginning Stock (Computer)").Style.Font.SetBold(true);
worksheet.Cell("D4").SetValue("Ending Stock (Computer)").Style.Font.SetBold(true);
worksheet.Cell("E4").SetValue("Ending Stock (Manual Entry)").Style.Font.SetBold(true);
worksheet.Cell("F4").SetValue("Computer Difference").Style.Font.SetBold(true);
worksheet.Cell("G4").SetValue("Manual Difference").Style.Font.SetBold(true);
worksheet.Cell("H4").SetValue("Stock Difference").Style.Font.SetBold(true);
worksheet.Cell("I4").SetValue("Item Cost").Style.Font.SetBold(true);
worksheet.Cell("J4").SetValue("Cost Difference").Style.Font.SetBold(true);
// start exporting data
var currentCell = worksheet.Cell("A5");
var lastRow = currentCell.WorksheetRow();
IXLCell firstCellWithData = null;
// TODO: adjust formulas with string.Format() rather than string concat
foreach (DetailedStockReportInfo item in items)
{
lastRow = currentCell.WorksheetRow();
if (firstCellWithData == null)
{
firstCellWithData = currentCell;
}
currentCell.Value = item.Item.Name;
currentCell.CellRight(1).Value = item.Item.Description;
currentCell.CellRight(2).Value = item.StartStockWithPurchaseStockIncrease;
currentCell.CellRight(3).Value = item.EndStock; // computer
currentCell.CellRight(4).Value = ""; // manual entry
currentCell.CellRight(4).AddConditionalFormat()
.WhenEquals("\"\"")
.Fill.SetBackgroundColor(XLColor.Yellow); // if data not entered, highlight that work needs to happen!!
currentCell.CellRight(5).FormulaA1 = "=SUM(-" + currentCell.CellRight(2).Address.ToStringFixed() + ","
+ currentCell.CellRight(3).Address.ToStringFixed() + ")"; // computer diff
currentCell.CellRight(6).FormulaA1 = "=IF(" + currentCell.CellRight(3).Address.ToStringFixed() + "=\"\", \"-\", "
+ "SUM(-" + currentCell.CellRight(2).Address.ToStringFixed() + ","
+ currentCell.CellRight(4).Address.ToStringFixed() + "))"; // manual diff
currentCell.CellRight(5).AddConditionalFormat()
.WhenNotEquals("=" + currentCell.CellRight(6).Address.ToStringFixed())
.Fill.SetBackgroundColor(XLColor.LightPink);
currentCell.CellRight(6).AddConditionalFormat()
.WhenNotEquals("=" + currentCell.CellRight(5).Address.ToStringFixed())
.Fill.SetBackgroundColor(XLColor.LightPink);
currentCell.CellRight(7).SetFormulaA1("=ABS(SUM(" + currentCell.CellRight(5).Address.ToStringFixed() + ", -"
+ currentCell.CellRight(6).Address.ToStringFixed() + "))").AddConditionalFormat()
.WhenNotEquals("0")
.Fill.SetBackgroundColor(XLColor.LightPink); // stock difference
currentCell.CellRight(8).Value = item.Item.Cost; // item cost
currentCell.CellRight(9).SetFormulaA1("=" +
currentCell.CellRight(7).Address.ToStringFixed() + "*" +
currentCell.CellRight(8).Address.ToStringFixed()); // cost difference
if (currentCell.WorksheetRow().RowNumber() % 2 == 0)
{
currentCell.WorksheetRow().Style.Fill.BackgroundColor = XLColor.LightGray;
}
// if you add more data columns make sure to adjust print area!!!
// go to next row
currentCell = currentCell.CellBelow();
}
// add cost discrepency
if (items.Count > 0)
{
currentCell.CellRight(8).SetValue("Cost Discrepency").Style.Font.SetBold(true);
currentCell.CellRight(9).SetFormulaA1("=SUM(" + firstCellWithData.CellRight(9).Address.ToStringFixed()
+ ":" + currentCell.CellAbove(1).CellRight(9).Address.ToStringFixed() + ")").Style.Font.SetBold(true);
}
//// auto fit width
worksheet.Columns().AdjustToContents(4, 4, 10, 25);
// set print area
worksheet.PageSetup.PrintAreas.Clear();
var firstCellForPrinting = worksheet.Cell("A1");
var lastCellForPrinting = items.Count > 0 ? currentCell.CellRight(9) : worksheet.Cell("J4");
worksheet.PageSetup.PrintAreas.Add(firstCellForPrinting.Address.ToStringRelative() + ":" + lastCellForPrinting.Address.ToStringRelative());
worksheet.PageSetup.SetRowsToRepeatAtTop("4:4");
worksheet.PageSetup.PagesWide = 1;
worksheet.PageSetup.PageOrientation = XLPageOrientation.Landscape;
workbook.SaveAs(path);
Process.Start(path);
}
}
private void addBottomBorder(IXLCell cell, XLBorderStyleValues border = XLBorderStyleValues.Thick, XLColor color = null)
{
if (color == null)
{
color = XLColor.Black;
}
cell.Style.Border.BottomBorder = XLBorderStyleValues.Thick;
cell.Style.Border.BottomBorderColor = XLColor.Black;
}
public new void Processing()
{
XLWorkbook wb = new XLWorkbook(FilePath);
foreach (var page in wb.Worksheets)
{
if (page.Name.ToUpper().Trim() != "СТАТИСТИКА" && page.Name.ToUpper().Trim() != "СВОДНАЯ")
{
const int numColPoint = 4;
IXLCell CellDate = page.Cell(1, numColPoint + 1);
while (CellDate.GetString() == "" && CellDate.Address.ColumnNumber <= page.LastColumnUsed().ColumnNumber())
{
CellDate = CellDate.CellRight();
}
DateTime curDate;
//if (!DateTime.TryParse(CellDate.GetValue<string>(), out curDate))
//{
// CellDate = CellDate.CellAbove();
DateTime.TryParse(CellDate.GetValue <string>(), out curDate);
//}
Regex rComment = new Regex(@"КОРРЕКЦИИ");
int corrRow = 5;
Match Mcomment = rComment.Match(page.Cell(corrRow, 1).GetString().ToUpper());
while (!Mcomment.Success)
{
corrRow++;
Mcomment = rComment.Match(page.Cell(corrRow, 1).GetString().ToUpper());
}
List <Call> calls = new List <Call>();
while (!(CellDate.CellBelow().IsEmpty() && CellDate.CellBelow().CellRight().IsEmpty()))
{
if (CellDate.GetValue <string>() != "")
{
DateTime.TryParse(CellDate.GetValue <string>(), out curDate);
}
string phoneNumber = CellDate.CellBelow().GetValue <string>();
var phoneCell = CellDate.CellBelow();
if (phoneNumber != "")
{
TimeSpan duration;
string link = "";
if (phoneCell.HasHyperlink)
{
link = phoneCell.Hyperlink.ExternalAddress.AbsoluteUri;
}
IXLCell CellPoint = CellDate.CellBelow().CellBelow().CellBelow();
if (CellPoint.DataType == XLDataType.DateTime)
{
CellPoint.DataType = XLDataType.TimeSpan;
}
TimeSpan.TryParse(CellPoint.GetString(), out duration);
IXLCell CellNamePoint;
List <Point> points = new List <Point>();
Point curPoint;
int markOfPoint;
CellPoint = CellPoint.CellBelow();
string DealName = "";
string comment = page.Cell(corrRow, CellPoint.Address.ColumnNumber).GetString();
bool redComment = page.Cell(corrRow, CellPoint.Address.ColumnNumber).Style.Fill.BackgroundColor
== XLColor.Red ? true : false;
var Color = page.Cell(corrRow, CellPoint.Address.ColumnNumber).Style.Fill.BackgroundColor;
bool greenComment = page.Cell(corrRow, CellPoint.Address.ColumnNumber).Style.Fill.BackgroundColor
== XLColor.Lime ? true : false;
int maxMark;
page.Cell(corrRow - 3, CellPoint.Address.ColumnNumber).TryGetValue(out maxMark);
if (!CellPoint.TryGetValue <int>(out markOfPoint))
{
if (CellPoint.GetString() != "")
{
DealName = CellPoint.GetString();
}
}
else
{
CellNamePoint = page.Cell(CellPoint.Address.RowNumber, numColPoint);
bool error = CellPoint.Style.Fill.BackgroundColor == XLColor.Red;
curPoint = new Point(CellNamePoint.GetString(), markOfPoint, error);
curPoint.ColorForRNR = CellNamePoint.Style.Fill.BackgroundColor;
points.Add(curPoint);
}
CellPoint = CellPoint.CellBelow();
int weightPoint;
int numchl;
while (page.Cell(CellPoint.Address.RowNumber, 3).TryGetValue <int>(out numchl) || page.Cell(CellPoint.Address.RowNumber, 3).GetString() == "б\\н")
{
page.Cell(CellPoint.Address.RowNumber, 2).TryGetValue <int>(out weightPoint);
if (CellPoint.TryGetValue <int>(out markOfPoint))
{
CellNamePoint = page.Cell(CellPoint.Address.RowNumber, numColPoint);
bool error = CellPoint.Style.Fill.BackgroundColor == XLColor.Red;
if (error)
{
}
curPoint = new Point(CellNamePoint.GetString(), markOfPoint, error);
curPoint.ColorForRNR = CellNamePoint.Style.Fill.BackgroundColor;
points.Add(curPoint);
}
CellPoint = CellPoint.CellBelow();
}
bool outgoing = true;
if (Regex.Match(page.Name.ToUpper(), "ВХОДЯЩ").Success)
{
outgoing = false;
}
string Objections = "";
string howProcessObj = "";
string DealState = "";
string DateOfNext = "";
string doneObj = "";
if (curDate > new DateTime(2020, 5, 6))
{
Objections = page.Cell(corrRow + 2, CellPoint.Address.ColumnNumber).GetString();
howProcessObj = page.Cell(corrRow + 4, CellPoint.Address.ColumnNumber).GetString();
DealState = page.Cell(corrRow + 5, CellPoint.Address.ColumnNumber).GetString();
DateOfNext = page.Cell(corrRow + 6, CellPoint.Address.ColumnNumber).GetString();
DateTime ddateNext;
if (DateOfNext != "")
{
if (DateTime.TryParse(DateOfNext, out ddateNext))
{
DateOfNext = ddateNext.ToString("dd.MM.yyyy");
}
}
doneObj = page.Cell(corrRow + 3, CellPoint.Address.ColumnNumber).GetString();
}
if (points.Count > 0)
{
var curCall = new Call(phoneNumber, maxMark, duration, comment, DealName, points, redComment, curDate, outgoing, greenComment, Objections, howProcessObj, DealState, link, DateOfNext, doneObj);
calls.Add(curCall);
var testt = curCall.getAVGPersent();
if (testt > 1)
{
}
}
}
CellDate = CellDate.CellRight();
}
stages.Add(new Stage(page.Name, calls));
}
}
}
internal void SetValues(IXLCell cell, String tooltip)
{
Tooltip = tooltip;
_internalAddress = cell.Address.ToString();
IsExternal = false;
}
/// -----------------------------------------------------------------------------
/// <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);
}
}
}
public IXLRangeRow Row(IXLCell start, IXLCell end)
{
return Row(start.Address.ColumnNumber, end.Address.ColumnNumber);
}
public DataSheetCell(IXLCell cell)
{
_cell = cell ?? throw new ArgumentNullException(nameof(cell));
}