/// <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 = "MS 明朝"; XLWorkbook.DefaultStyle.Font.FontSize = 9; IXLWorksheet worksheet = workbook.Worksheets.Add("Header"); IXLWorksheet headersheet = worksheet; // ヘッダーシート IXLWorksheet currentsheet = worksheet; // 処理中シート //Linqで必要なデータをselect var outDataAll = dtSyohingunUriageSiire.AsEnumerable() .Select(dat => new { daibunruiCd = dat["大分類コード"], daibunruiName = dat["大分類名"], chubunruiName = dat["中分類名"], kamikiUriagegaku = (decimal)dat["上期売上額"], kamikiSiiregaku = (decimal)dat["上期仕入額"], simokiUriagegaku = (decimal)dat["下期売上額"], simokiSiiregaku = (decimal)dat["下期仕入額"], goukeiUriagegaku = (decimal)dat["合計売上額"], goukeiSiiregaku = (decimal)dat["合計仕入額"], }).ToList(); // linqで売上額(上期、下期、合計)、仕入額(上期、下期、合計)を算出 decimal[] decKingaku = new decimal[13]; decKingaku[0] = outDataAll.Select(gokei => gokei.kamikiUriagegaku).Sum(); decKingaku[1] = outDataAll.Select(gokei => gokei.kamikiSiiregaku).Sum(); decKingaku[2] = outDataAll.Select(gokei => gokei.simokiUriagegaku).Sum(); decKingaku[3] = outDataAll.Select(gokei => gokei.simokiSiiregaku).Sum(); decKingaku[4] = outDataAll.Select(gokei => gokei.goukeiUriagegaku).Sum(); decKingaku[5] = outDataAll.Select(gokei => gokei.goukeiSiiregaku).Sum(); // 大分類計(小計) var daibunruiGoukei = from tbl in dtSyohingunUriageSiire.AsEnumerable() group tbl by tbl.Field <string>("大分類コード") into g select new { section = g.Key, count = g.Count(), kamikiUriagegaku = g.Sum(p => p.Field <decimal>("上期売上額")), kamikiSiiregaku = g.Sum(p => p.Field <decimal>("上期仕入額")), simokiUriagegaku = g.Sum(p => p.Field <decimal>("下期売上額")), simokiSiiregaku = g.Sum(p => p.Field <decimal>("下期仕入額")), goukeiUriagegaku = g.Sum(p => p.Field <decimal>("合計売上額")), goukeiSiiregaku = g.Sum(p => p.Field <decimal>("合計仕入額")), }; // 大分類計(小計)の売上額(上期、下期、合計)、仕入額(上期、下期、合計)を算出 decimal[,] decKingakuDaibunrui = new decimal[daibunruiGoukei.Count(), 13]; for (int cnt = 0; cnt < daibunruiGoukei.Count(); cnt++) { decKingakuDaibunrui[cnt, 0] = daibunruiGoukei.ElementAt(cnt).kamikiUriagegaku; decKingakuDaibunrui[cnt, 1] = daibunruiGoukei.ElementAt(cnt).kamikiSiiregaku; decKingakuDaibunrui[cnt, 2] = daibunruiGoukei.ElementAt(cnt).simokiUriagegaku; decKingakuDaibunrui[cnt, 3] = daibunruiGoukei.ElementAt(cnt).simokiSiiregaku; decKingakuDaibunrui[cnt, 4] = daibunruiGoukei.ElementAt(cnt).goukeiUriagegaku; decKingakuDaibunrui[cnt, 5] = daibunruiGoukei.ElementAt(cnt).goukeiSiiregaku; } // リストをデータテーブルに変換 DataTable dtChkList = this.ConvertToDataTable(outDataAll); int maxRowCnt = dtChkList.Rows.Count; int maxColCnt = dtChkList.Columns.Count; int pageCnt = 0; // ページ(シート枚数)カウント int rowCnt = 1; // datatable処理行カウント int xlsRowCnt = 4; // Excel出力行カウント(開始は出力行) int maxPage = 0; // 最大ページ数 // ページ数計算 maxRowCnt += daibunruiGoukei.Count() + 1; double page = 1.0 * maxRowCnt / 35; double decimalpart = page % 1; if (decimalpart != 0) { // 小数点以下が0でない場合、+1 maxPage = (int)Math.Floor(page) + 1; } else { maxPage = (int)page; } int daibunruiCnt = 0; int daibunruiRowCnt = 0; string strSiireCode = ""; // ClosedXMLで1行ずつExcelに出力 foreach (DataRow drSyohingunUriageSiire in dtChkList.Rows) { // 1ページ目のシート作成 if (rowCnt == 1) { pageCnt++; // タイトル出力(中央揃え、セル結合) IXLCell titleCell = headersheet.Cell("A1"); titleCell.Value = "商品群別売上仕入管理表"; titleCell.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center; titleCell.Style.Font.FontSize = 14; headersheet.Range("A1", "H1").Merge(); // ヘッダー出力(3行目のセル) headersheet.Cell("A3").Value = "大分類名"; headersheet.Cell("B3").Value = "中分類名"; headersheet.Cell("C3").Value = "上期売上高"; headersheet.Cell("D3").Value = "上期仕入高"; headersheet.Cell("E3").Value = "下期売上高"; headersheet.Cell("F3").Value = "下期仕入高"; headersheet.Cell("G3").Value = "合計売上高"; headersheet.Cell("H3").Value = "合計仕入高"; // ヘッダー列 headersheet.Range("A3", "H3").Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center; // セルの周囲に罫線を引く headersheet.Range("A3", "H3").Style .Border.SetTopBorder(XLBorderStyleValues.Thin) .Border.SetBottomBorder(XLBorderStyleValues.Thin) .Border.SetLeftBorder(XLBorderStyleValues.Thin) .Border.SetRightBorder(XLBorderStyleValues.Thin); // 列幅の指定 headersheet.Column(1).Width = 20; headersheet.Column(2).Width = 20; headersheet.Column(3).Width = 20; headersheet.Column(4).Width = 20; headersheet.Column(5).Width = 20; headersheet.Column(6).Width = 20; headersheet.Column(7).Width = 20; headersheet.Column(8).Width = 20; // 印刷体裁(B4横、印刷範囲) headersheet.PageSetup.PaperSize = XLPaperSize.B4Paper; headersheet.PageSetup.PageOrientation = XLPageOrientation.Landscape; // ヘッダー部の指定(番号) headersheet.PageSetup.Header.Left.AddText("(№65)"); // ヘッダーシートからコピー headersheet.CopyTo("Page1"); currentsheet = workbook.Worksheet(2); // ヘッダー部の指定(コンピュータ名、日付、ページ数を出力) strHeader = "( " + strComputerName + " )" + strSpace + strNow + strSpace + pageCnt.ToString() + " / " + maxPage.ToString(); currentsheet.PageSetup.Header.Right.AddText(strHeader); } // 大分類名出力 if (daibunruiRowCnt == 0) { currentsheet.Cell(xlsRowCnt, 1).Value = drSyohingunUriageSiire[1]; } // 1セルずつデータ出力 for (int colCnt = 2; colCnt < maxColCnt; colCnt++) { string str = drSyohingunUriageSiire[colCnt].ToString(); // 金額セルの処理 if (colCnt >= 3 && colCnt <= 8) { // 3桁毎に","を挿入する str = string.Format("{0:#,0}", decimal.Parse(str)); IXLCell kingakuCell = currentsheet.Cell(xlsRowCnt, colCnt); kingakuCell.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Right; kingakuCell.Value = str; } currentsheet.Cell(xlsRowCnt, colCnt).Value = str; } // 1行分のセルの周囲に罫線を引く currentsheet.Range(xlsRowCnt, 1, xlsRowCnt, 8).Style .Border.SetTopBorder(XLBorderStyleValues.Thin) .Border.SetBottomBorder(XLBorderStyleValues.Thin) .Border.SetLeftBorder(XLBorderStyleValues.Thin) .Border.SetRightBorder(XLBorderStyleValues.Thin); // 35行毎(ヘッダーを除いた行数)にシート作成 if (xlsRowCnt == 38) { pageCnt++; if (pageCnt <= maxPage) { xlsRowCnt = 3; // コンピュータ名、日付、ページ数を取得 strHeader = "( " + strComputerName + " )" + strSpace + strNow + strSpace + pageCnt.ToString() + " / " + maxPage.ToString(); // ヘッダーシートのコピー、ヘッダー部の指定 sheetCopy(ref workbook, ref headersheet, ref currentsheet, pageCnt, strHeader); } } // 大分類計(小計)を出力 daibunruiRowCnt++; if (daibunruiGoukei.ElementAt(daibunruiCnt).count == daibunruiRowCnt) { xlsRowCnt++; // セル結合、中央揃え IXLCell tantocell = currentsheet.Cell(xlsRowCnt, 1); currentsheet.Range(xlsRowCnt, 1, xlsRowCnt, 2).Merge(); tantocell.Value = "◆ 小 計 ◆"; tantocell.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Left; // 金額セルの処理(3桁毎に","を挿入する) for (int cnt = 0; cnt < 6; cnt++) { IXLCell kingakuCell = currentsheet.Cell(xlsRowCnt, cnt + 3); kingakuCell.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Right; kingakuCell.Value = string.Format("{0:#,0}", decKingakuDaibunrui[daibunruiCnt, cnt]); } // 1行分のセルの周囲に罫線を引く currentsheet.Range(xlsRowCnt, 1, xlsRowCnt, 8).Style .Border.SetTopBorder(XLBorderStyleValues.Thin) .Border.SetBottomBorder(XLBorderStyleValues.Thin) .Border.SetLeftBorder(XLBorderStyleValues.Thin) .Border.SetRightBorder(XLBorderStyleValues.Thin); daibunruiCnt++; daibunruiRowCnt = 0; } // 35行毎(ヘッダーを除いた行数)にシート作成 if (xlsRowCnt == 38) { pageCnt++; if (pageCnt <= maxPage) { xlsRowCnt = 3; // コンピュータ名、日付、ページ数を取得 strHeader = "( " + strComputerName + " )" + strSpace + strNow + strSpace + pageCnt.ToString() + " / " + maxPage.ToString(); // ヘッダーシートのコピー、ヘッダー部の指定 sheetCopy(ref workbook, ref headersheet, ref currentsheet, pageCnt, strHeader); } } rowCnt++; xlsRowCnt++; } // 最終行を出力した後、合計行を出力 if (dtChkList.Rows.Count > 0) { // セル結合、中央揃え IXLCell sumcell = currentsheet.Cell(xlsRowCnt, 1); currentsheet.Range(xlsRowCnt, 1, xlsRowCnt, 2).Merge(); sumcell.Value = "◆ 合 計 ◆"; sumcell.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Left; // 金額セルの処理(3桁毎に","を挿入する) for (int cnt = 0; cnt < 6; cnt++) { IXLCell kingakuCell = currentsheet.Cell(xlsRowCnt, cnt + 3); kingakuCell.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Right; kingakuCell.Value = string.Format("{0:#,0}", decKingaku[cnt]); } // 1行分のセルの周囲に罫線を引く currentsheet.Range(xlsRowCnt, 1, xlsRowCnt, 8).Style .Border.SetTopBorder(XLBorderStyleValues.Thin) .Border.SetBottomBorder(XLBorderStyleValues.Thin) .Border.SetLeftBorder(XLBorderStyleValues.Thin) .Border.SetRightBorder(XLBorderStyleValues.Thin); } // ヘッダーシート削除 headersheet.Delete(); // workbookを保存 string strOutXlsFile = strWorkPath + strDateTime + ".xlsx"; workbook.SaveAs(strOutXlsFile); // workbookを解放 workbook.Dispose(); // PDF化の処理 return(createPdf(strOutXlsFile, strDateTime)); } catch (Exception ex) { new CommonException(ex); throw ex; } finally { // Workフォルダの全ファイルを取得 string[] files = System.IO.Directory.GetFiles(strWorkPath, "*", System.IO.SearchOption.AllDirectories); // Workフォルダ内のファイル削除 foreach (string filepath in files) { //File.Delete(filepath); } } }
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 = "MS ゴシック"; XLWorkbook.DefaultStyle.Font.FontSize = 9; // excelのインスタンス生成 XLWorkbook workbook = new XLWorkbook(XLEventTracking.Disabled); IXLWorksheet worksheet = workbook.Worksheets.Add("Header"); IXLWorksheet headersheet = worksheet; // ヘッダーシート IXLWorksheet currentsheet = worksheet; // 処理中シート //Linqで必要なデータをselect var outDataAll = dtSetCd_B_Input.AsEnumerable() .Select(dat => new { tantoushaCd = dat["担当者コード"], tantoushaName = dat["担当者名"], loginID = dat["営業所名"], eigyoshoCd = dat["グループ名"], chubanmoji = dat["年間売上目標"], groupCd = dat["注番文字"], }).ToList(); //リストをデータテーブルに変換 DataTable dtChkList = pdf.ConvertToDataTable(outDataAll); int maxRowCnt = dtChkList.Rows.Count + 1; int maxColCnt = dtChkList.Columns.Count; int pageCnt = 0; // ページ(シート枚数)カウント int rowCnt = 1; // datatable処理行カウント int xlsRowCnt = 4; // Excel出力行カウント(開始は出力行) int maxPage = 0; // 最大ページ数 //ページ数計算 double page = 1.0 * maxRowCnt / 44; double decimalpart = page % 1; if (decimalpart != 0) { //小数点以下が0でない場合、+1 maxPage = (int)Math.Floor(page) + 1; } else { maxPage = (int)page; } //ClosedXMLで1行ずつExcelに出力 foreach (DataRow drSiireCheak in dtChkList.Rows) { //1ページ目のシート作成 if (rowCnt == 1) { pageCnt++; //タイトル出力(中央揃え、セル結合) IXLCell titleCell = headersheet.Cell("A1"); titleCell.Value = "担当者マスタリスト"; titleCell.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center; titleCell.Style.Font.FontSize = 16; headersheet.Range("A1", "C1").Merge(); //ヘッダー出力(表ヘッダー) headersheet.Cell("A3").Value = "コード"; headersheet.Cell("B3").Value = "担当者名"; headersheet.Cell("C3").Value = "営業所名"; headersheet.Cell("D3").Value = "グループ名"; headersheet.Cell("E3").Value = "年間目標金額"; headersheet.Cell("F3").Value = "注番文字"; //ヘッダー列 headersheet.Range("A3", "F3").Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center; // 列幅の指定 headersheet.Column(1).Width = 12; headersheet.Column(2).Width = 20; headersheet.Column(3).Width = 17; headersheet.Column(4).Width = 17; headersheet.Column(5).Width = 17; headersheet.Column(6).Width = 17; // セルの周囲に罫線を引く(細い方) headersheet.Range("A3", "C3").Style .Border.SetTopBorder(XLBorderStyleValues.Thin) .Border.SetBottomBorder(XLBorderStyleValues.Thin) .Border.SetLeftBorder(XLBorderStyleValues.Thin); //.Border.SetRightBorder(XLBorderStyleValues.Thin); // セルの周囲に罫線を引く(太い方) headersheet.Range("D3", "F3").Style .Border.SetTopBorder(XLBorderStyleValues.Medium) .Border.SetBottomBorder(XLBorderStyleValues.Medium) .Border.SetLeftBorder(XLBorderStyleValues.Medium) .Border.SetRightBorder(XLBorderStyleValues.Medium); // 印刷体裁(A4横、印刷範囲) headersheet.PageSetup.PaperSize = XLPaperSize.A4Paper; headersheet.PageSetup.PageOrientation = XLPageOrientation.Default; // ヘッダー部の指定(番号) headersheet.PageSetup.Header.Left.AddText("(№105)"); //ヘッダーシートのコピー、ヘッダー部の指定 pdf.sheetCopy(ref workbook, ref headersheet, ref currentsheet, pageCnt, maxPage, strNow); } // 1セルずつデータ出力 for (int colCnt = 1; colCnt <= maxColCnt; colCnt++) { string str = drSiireCheak[colCnt - 1].ToString(); currentsheet.Cell(xlsRowCnt, colCnt).Value = str; //コードのカラムの場合 if (colCnt == 1) { //二桁の0パディングをさせる currentsheet.Cell(xlsRowCnt, colCnt).Style.NumberFormat.SetFormat("0000"); //中心にする currentsheet.Cell(xlsRowCnt, colCnt).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center; } //年間目標金額のカラムの場合 if (colCnt == 5) { //二桁の0パディングをさせる currentsheet.Cell(xlsRowCnt, colCnt).Style.NumberFormat.SetFormat("#,0"); } string strKari = currentsheet.Cell(xlsRowCnt, colCnt).Value.ToString(); } // 1行分のセルの周囲に罫線を引く(細い方) currentsheet.Range(xlsRowCnt, 1, xlsRowCnt, 3).Style .Border.SetTopBorder(XLBorderStyleValues.Thin) .Border.SetBottomBorder(XLBorderStyleValues.Thin) .Border.SetLeftBorder(XLBorderStyleValues.Thin); // 1行分のセルの周囲に罫線を引く(太い方) currentsheet.Range(xlsRowCnt, 4, xlsRowCnt, 6).Style .Border.SetTopBorder(XLBorderStyleValues.Medium) .Border.SetBottomBorder(XLBorderStyleValues.Medium) .Border.SetLeftBorder(XLBorderStyleValues.Medium) .Border.SetRightBorder(XLBorderStyleValues.Medium); // 47行毎(ヘッダーを除いた行数)にシート作成 if (xlsRowCnt == 47) { pageCnt++; if (pageCnt <= maxPage) { xlsRowCnt = 3; // ヘッダーシートのコピー、ヘッダー部の指定 pdf.sheetCopy(ref workbook, ref headersheet, ref currentsheet, pageCnt, maxPage, strNow); } } rowCnt++; xlsRowCnt++; } // ヘッダーシート削除 headersheet.Delete(); // workbookを保存 string strOutXlsFile = strWorkPath + strDateTime + ".xlsx"; workbook.SaveAs(strOutXlsFile); // workbookを解放 workbook.Dispose(); // PDF化の処理 return(pdf.createPdf(strOutXlsFile, strDateTime, 1)); } catch (Exception ex) { throw; } finally { // Workフォルダの全ファイルを取得 string[] files = System.IO.Directory.GetFiles(strWorkPath, "*", System.IO.SearchOption.AllDirectories); // Workフォルダ内のファイル削除 foreach (string filepath in files) { //File.Delete(filepath); } } }
//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 = "MS 明朝"; XLWorkbook.DefaultStyle.Font.FontSize = 9; // excelのインスタンス生成 XLWorkbook workbook = new XLWorkbook(XLEventTracking.Disabled); IXLWorksheet worksheet = workbook.Worksheets.Add("Header"); IXLWorksheet headersheet = worksheet; // ヘッダーシート IXLWorksheet currentsheet = worksheet; // 処理中シート //Linqで必要なデータをselect var outDataAll = dtNyukinCheckList.AsEnumerable() .Select(dat => new { tokuisakiCd = dat["得意先コード"], tokuisakiName = dat["取引先名"], nyukinYmd = dat["入金年月日"], denpyoNo = dat["伝票番号"], bunruiName = dat["取引区分名"], kingaku = (decimal)dat["入金額"], kijitsu = dat["手形期日"], bikou = dat["備考"] }).ToList(); // linqで税抜合計金額、消費税、税込合計金額の合計算出 decimal decKingaku = outDataAll.Select(gokei => gokei.kingaku).Sum(); // リストをデータテーブルに変換 DataTable dtChkList = pdf.ConvertToDataTable(outDataAll); int maxRowCnt = dtChkList.Rows.Count + 1; int maxColCnt = dtChkList.Columns.Count; int pageCnt = 0; // ページ(シート枚数)カウント int rowCnt = 1; // datatable処理行カウント int xlsRowCnt = 4; // Excel出力行カウント(開始は出力行) int maxPage = 0; // 最大ページ数 // ページ数計算 double page = 1.0 * maxRowCnt / 29; double decimalpart = page % 1; if (decimalpart != 0) { // 小数点以下が0でない場合、+1 maxPage = (int)Math.Floor(page) + 1; } else { maxPage = (int)page; } // ClosedXMLで1行ずつExcelに出力 foreach (DataRow drSiireCheck in dtChkList.Rows) { // 1ページ目のシート作成 if (rowCnt == 1) { pageCnt++; // タイトル出力(中央揃え、セル結合) IXLCell titleCell = headersheet.Cell("A1"); titleCell.Value = "入金チェックリスト"; titleCell.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center; titleCell.Style.Font.FontSize = 16; headersheet.Range("A1", "H1").Merge(); // 入力日、伝票年月日出力(A2のセル) IXLCell unitCell = headersheet.Cell("A2"); unitCell.Value = "入力日:" + string.Format("{0:yyyy年MM月dd日}", DateTime.Parse(lstItem[0])) + " ~ " + string.Format("{0:yyyy年MM月dd日}", DateTime.Parse(lstItem[1])) + " 伝票年月日:" + string.Format("{0:yyyy年MM月dd日}", DateTime.Parse(lstItem[2])) + " ~ " + string.Format("{0:yyyy年MM月dd日}", DateTime.Parse(lstItem[3])) + " 得意先コード:" + lstItem[5] + " ~ " + lstItem[6]; unitCell.Style.Font.FontSize = 10; // ヘッダー出力(3行目のセル) headersheet.Cell("A3").Value = "コード"; headersheet.Cell("B3").Value = "得意先名"; headersheet.Cell("C3").Value = "入金日"; headersheet.Cell("D3").Value = "伝票番号"; headersheet.Cell("E3").Value = "取引区分"; headersheet.Cell("F3").Value = "入金額"; headersheet.Cell("G3").Value = "手形期日"; headersheet.Cell("H3").Value = "備 考"; // ヘッダー列 headersheet.Range("A3", "H3").Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center; // セルの周囲に罫線を引く headersheet.Range("A3", "H3").Style .Border.SetTopBorder(XLBorderStyleValues.Thin) .Border.SetBottomBorder(XLBorderStyleValues.Thin) .Border.SetLeftBorder(XLBorderStyleValues.Thin) .Border.SetRightBorder(XLBorderStyleValues.Thin); // セルの背景色 headersheet.Range("A3", "H3").Style.Fill.BackgroundColor = XLColor.LightGray; // 列幅の指定 headersheet.Column(1).Width = 5; headersheet.Column(2).Width = 38; headersheet.Column(3).Width = 11; headersheet.Column(4).Width = 8; headersheet.Column(5).Width = 10; headersheet.Column(6).Width = 12; headersheet.Column(7).Width = 11; headersheet.Column(8).Width = 38; // 印刷体裁(A4横、印刷範囲) headersheet.PageSetup.PaperSize = XLPaperSize.A4Paper; headersheet.PageSetup.PageOrientation = XLPageOrientation.Landscape; // ヘッダー部の指定(番号) headersheet.PageSetup.Header.Left.AddText("(№5)"); // ヘッダーシートのコピー、ヘッダー部の指定 pdf.sheetCopy(ref workbook, ref headersheet, ref currentsheet, pageCnt, maxPage, strNow); } // 1セルずつデータ出力 for (int colCnt = 1; colCnt <= maxColCnt; colCnt++) { string str = drSiireCheck[colCnt - 1].ToString(); // 入金日、手形期日セルの場合 if (colCnt == 3 || colCnt == 7) { currentsheet.Cell(xlsRowCnt, colCnt).Style.DateFormat.SetFormat("yyyy/MM/dd"); currentsheet.Cell(xlsRowCnt, colCnt).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center; } // 伝票番号セルの処理 if (colCnt == 4) { currentsheet.Cell(xlsRowCnt, colCnt).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Right; } // 入金額セルの処理 if (colCnt == 6) { // 3桁毎に","を挿入する currentsheet.Cell(xlsRowCnt, colCnt).Style.NumberFormat.SetFormat("#,##0"); currentsheet.Cell(xlsRowCnt, colCnt).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Right; } // 備考セルの場合 if (colCnt == 8) { currentsheet.Cell(xlsRowCnt, colCnt).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Left; str = "'" + str; } currentsheet.Cell(xlsRowCnt, colCnt).Value = str; } // 1行分のセルの周囲に罫線を引く currentsheet.Range(xlsRowCnt, 1, xlsRowCnt, 8).Style .Border.SetTopBorder(XLBorderStyleValues.Thin) .Border.SetBottomBorder(XLBorderStyleValues.Thin) .Border.SetLeftBorder(XLBorderStyleValues.Thin) .Border.SetRightBorder(XLBorderStyleValues.Thin); // 29行毎(ヘッダーを除いた行数)にシート作成 if (xlsRowCnt == 32) { pageCnt++; if (pageCnt <= maxPage) { xlsRowCnt = 3; // ヘッダーシートのコピー、ヘッダー部の指定 pdf.sheetCopy(ref workbook, ref headersheet, ref currentsheet, pageCnt, maxPage, strNow); } } rowCnt++; xlsRowCnt++; } // 最終行を出力した後、合計行を出力 if (dtChkList.Rows.Count > 0) { // セル結合 currentsheet.Range(xlsRowCnt, 1, xlsRowCnt, 5).Merge(); currentsheet.Cell(xlsRowCnt, 1).Value = "◆◆◆ 合 計 ◆◆◆"; currentsheet.Cell(xlsRowCnt, 1).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center; // 入金額 currentsheet.Cell(xlsRowCnt, 6).Value = string.Format("{0:#,0}", decKingaku); currentsheet.Cell(xlsRowCnt, 6).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Right; // 1行分のセルの周囲に罫線を引く currentsheet.Range(xlsRowCnt, 1, xlsRowCnt, 8).Style .Border.SetTopBorder(XLBorderStyleValues.Thin) .Border.SetBottomBorder(XLBorderStyleValues.Thin) .Border.SetLeftBorder(XLBorderStyleValues.Thin) .Border.SetRightBorder(XLBorderStyleValues.Thin); } // ヘッダーシート削除 headersheet.Delete(); // workbookを保存 string strOutXlsFile = strWorkPath + strDateTime + ".xlsx"; workbook.SaveAs(strOutXlsFile); // workbookを解放 workbook.Dispose(); // PDF化の処理 return(pdf.createPdf(strOutXlsFile, strDateTime, 1)); } catch { throw; } finally { // Workフォルダの全ファイルを取得 string[] files = System.IO.Directory.GetFiles(strWorkPath, "*", System.IO.SearchOption.AllDirectories); // Workフォルダ内のファイル削除 foreach (string filepath in files) { //File.Delete(filepath); } } }
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 = "MS ゴシック"; XLWorkbook.DefaultStyle.Font.FontSize = 7.5; // excelのインスタンス生成 XLWorkbook workbook = new XLWorkbook(XLEventTracking.Disabled); IXLWorksheet worksheet = workbook.Worksheets.Add("Header"); IXLWorksheet headersheet = worksheet; // ヘッダーシート IXLWorksheet currentsheet = worksheet; // 処理中シート // Linqで必要なデータをselect var outDataAll = dtSetCd_B_Input.AsEnumerable() .Select(dat => new { TokuiCd = dat["仕入先コード"], //[0]ヘッダー表示のみ使用 TokuiName = dat["仕入先名"], //[1]ヘッダー表示のみ使用 TokuiYMD = dat["年月日"], //[2] Tokuikbn = dat["取引区分名"], //[3] TokuiShohinName = dat["商品名"], //[4] TokuiSu = dat["数量"], //[5] TokuiTanka = dat["仕入単価"], //[6] TokuiUrikin = dat["仕入金額"], //[7] TokuiNyukin = dat["支払金額"], //[8] TokuiSashiZan = dat["差引残高"], //[9] TokuiBiko = dat["備考"], //[10] }).ToList(); // リストをデータテーブルに変換 DataTable dtChkList = pdf.ConvertToDataTable(outDataAll); int maxRowCnt = dtChkList.Rows.Count + 1; int maxColCnt = dtChkList.Columns.Count; int pageCnt = 0; // ページ(シート枚数)カウント int rowCnt = 1; // datatable処理行カウント int xlsRowCnt = 5; // Excel出力行カウント(開始は出力行) int maxPage = 0; // 最大ページ数 // 最大ページ数の行数カウント用 int rowCntMaxPage = 1; int xlsRowCntMaxPage = 5; // 最大ページ数の取得 foreach (DataRow drTokuiCheak in dtChkList.Rows) { // 2ページ目以降 if (rowCntMaxPage > 1) { // 次の得意先が違う場合にシート作成 if (strTokuiCd != drTokuiCheak[0].ToString()) { strTokuiCd = drTokuiCheak[0].ToString(); //合計分の行を追加 maxPage++; xlsRowCntMaxPage = 5; } } // 1ページ目のシート作成 if (rowCntMaxPage == 1) { maxPage++; strTokuiCd = drTokuiCheak[0].ToString(); } //44行に達した場合 if (xlsRowCntMaxPage == 44) { maxPage++; xlsRowCntMaxPage = 4; } //maxPage++; rowCntMaxPage++; xlsRowCntMaxPage++; } // ClosedXMLで1行ずつExcelに出力 foreach (DataRow drTokuiCheak in dtChkList.Rows) { //2ページ目以降 if (rowCnt > 1) { //次の得意先が違う場合にシート作成 if (strTokuiCd != drTokuiCheak[0].ToString()) { //マージ currentsheet.Range("A" + xlsRowCnt, "E" + xlsRowCnt).Merge(); currentsheet.Range("H" + xlsRowCnt, "I" + xlsRowCnt).Merge(); currentsheet.Row(xlsRowCnt).Height = 10; currentsheet.Cell(xlsRowCnt, 1).Value = "■■■ 合 計 ■■■"; currentsheet.Cell(xlsRowCnt, 1).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center; currentsheet.Cell(xlsRowCnt, 6).Value = decUriKin.ToString(); //売上金額 currentsheet.Cell(xlsRowCnt, 7).Value = decNyukin.ToString(); //入金金額 //最終行、各項目のカンマ処理と文字寄せ for (int intCnt = 6; intCnt < 8; intCnt++) { currentsheet.Cell(xlsRowCnt, intCnt).Style.NumberFormat.Format = "#,0"; currentsheet.Cell(xlsRowCnt, intCnt).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Right; } // 1行分のセルの周囲に罫線を引く currentsheet.Range(xlsRowCnt, 1, xlsRowCnt, 16).Style .Border.SetTopBorder(XLBorderStyleValues.Thin) .Border.SetBottomBorder(XLBorderStyleValues.Thin) .Border.SetLeftBorder(XLBorderStyleValues.Thin) .Border.SetRightBorder(XLBorderStyleValues.Thin); //初期化 decUriKin = 0; decNyukin = 0; //得意先コードの確保 strTokuiCd = drTokuiCheak[0].ToString(); strTokuiName = drTokuiCheak[1].ToString(); pageCnt++; // ヘッダー出力(表ヘッダー上) headersheet.Cell("A3").Value = strTokuiCd.Trim() + " " + strTokuiName.Trim(); //取引先名と取引先コード xlsRowCnt = 5; //ヘッダーシートのコピー、ヘッダー部の指定 pdf.sheetCopy(ref workbook, ref headersheet, ref currentsheet, pageCnt, maxPage, strNow); } } //1ページ目のシート作成 if (rowCnt == 1) { //得意先コードの確保 strTokuiCd = drTokuiCheak[0].ToString(); strTokuiName = drTokuiCheak[1].ToString(); pageCnt++; // タイトル出力(中央揃え、セル結合) IXLCell titleCell = headersheet.Cell("A1"); titleCell.Value = "仕 入 先 元 帳"; titleCell.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center; titleCell.Style.Font.FontSize = 16; headersheet.Range("A1", "I1").Merge(); // ヘッダー出力(表ヘッダー) headersheet.Cell("A4").Value = "年月日"; headersheet.Cell("B4").Value = "区分"; headersheet.Cell("C4").Value = "商 品 名"; headersheet.Cell("D4").Value = "数 量"; headersheet.Cell("E4").Value = "単 価"; headersheet.Cell("F4").Value = "仕入金額"; headersheet.Cell("G4").Value = "支払金額"; headersheet.Cell("H4").Value = "差引残高"; headersheet.Cell("I4").Value = "備考"; //行高さの指定 headersheet.Row(4).Height = 10; // 列幅の指定 headersheet.Column(1).Width = 10; //年月日 headersheet.Column(2).Width = 9; //区分 headersheet.Column(3).Width = 80; //商品名 headersheet.Column(4).Width = 11.5; //数量 headersheet.Column(5).Width = 11.5; //単価 headersheet.Column(6).Width = 11.5; //売上金額 headersheet.Column(7).Width = 11.5; //入金金額 headersheet.Column(8).Width = 11.5; //差引残高 headersheet.Column(9).Width = 30; //備考 //ヘッダー文字位置の指定 headersheet.Column(1).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center; //年月日 headersheet.Column(2).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center; //区分 headersheet.Column(3).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center; //商品名 headersheet.Column(4).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center; //数量 headersheet.Column(5).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center; //単価 headersheet.Column(6).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center; //売上金額 headersheet.Column(7).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center; //入金金額 headersheet.Column(8).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center; //差引残高 headersheet.Column(9).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center; //備考 // セルの周囲に罫線を引く headersheet.Range("A4", "I4").Style .Border.SetTopBorder(XLBorderStyleValues.Thin) .Border.SetBottomBorder(XLBorderStyleValues.Thin) .Border.SetLeftBorder(XLBorderStyleValues.Thin) .Border.SetRightBorder(XLBorderStyleValues.Thin); //背景を灰色にする headersheet.Range("A4", "I4").Style.Fill.BackgroundColor = XLColor.LightGray; // 印刷体裁(A4横、印刷範囲) headersheet.PageSetup.PaperSize = XLPaperSize.A4Paper; headersheet.PageSetup.PageOrientation = XLPageOrientation.Landscape; // ヘッダー部の指定(番号) headersheet.PageSetup.Header.Left.AddText("(№34)"); //マージ currentsheet.Range("A3", "C3").Merge(); currentsheet.Range("D3", "E3").Merge(); currentsheet.Range("F3", "G3").Merge(); // ヘッダー出力(表ヘッダー上) headersheet.Cell("A3").Value = strTokuiCd + " " + strTokuiName; //取引先名と取引先コード headersheet.Cell("F3").Value = "(外税 請求単位)"; //(外税 請求単位)の記入 headersheet.Cell("I3").Value = "対象期間:" + lstPrintData[2] + " ~ " + lstPrintData[3]; //対象期間 // ヘッダー列 headersheet.Range("A3", "C3").Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Left; headersheet.Range("D3", "F3").Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Left; headersheet.Range("I3").Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Right; //ヘッダーシートのコピー、ヘッダー部の指定 pdf.sheetCopy(ref workbook, ref headersheet, ref currentsheet, pageCnt, maxPage, strNow); } // 1セルずつデータ出力 for (int colCnt = 1; colCnt <= maxColCnt; colCnt++) { string str = drTokuiCheak[colCnt - 1].ToString(); //行の高さ指定 currentsheet.Row(xlsRowCnt).Height = 10; //数量、単価の場合 if (colCnt == 6 || colCnt == 7) { //空白以外の場合 if (str != "") { //小数点以下第二位まで表示 currentsheet.Cell(xlsRowCnt, colCnt - 2).Style.NumberFormat.Format = "#,0.00"; } currentsheet.Cell(xlsRowCnt, colCnt - 2).Value = str; currentsheet.Cell(xlsRowCnt, colCnt - 2).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Right; //0の場合 if (currentsheet.Cell(xlsRowCnt, colCnt - 2).Value.ToString() == "0") { //空にする currentsheet.Cell(xlsRowCnt, colCnt - 2).Value = ""; } } //売上金額、入金金額、割引残高の場合 else if (colCnt >= 8 && colCnt <= 10) { //空でない場合 if (str != "") { //余計な小数点以下数値を取り除く str = Math.Floor(decimal.Parse(str)).ToString(); //空白以外の場合 if (str != "0") { //カンマつけ currentsheet.Cell(xlsRowCnt, colCnt - 2).Style.NumberFormat.Format = "#,0"; } //0の場合は空白 else if (str == "0") { str = ""; } } currentsheet.Cell(xlsRowCnt, colCnt - 2).Value = str; currentsheet.Cell(xlsRowCnt, colCnt - 2).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Right; //0の場合 if (currentsheet.Cell(xlsRowCnt, colCnt - 2).ToString() == "0") { //空にする currentsheet.Cell(xlsRowCnt, colCnt - 2).Value = ""; } } //得意先コード、得意先名はスルー else if (colCnt == 1 || colCnt == 2) { //スルー } //備考 else if (colCnt == 11) { currentsheet.Cell(xlsRowCnt, colCnt - 2).Value = str; currentsheet.Cell(xlsRowCnt, colCnt - 2).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Left; } //年月日、区分、商品名の場合 else { currentsheet.Cell(xlsRowCnt, colCnt - 2).Value = str; currentsheet.Cell(xlsRowCnt, colCnt - 2).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Left; } } // 1行分のセルの周囲に罫線を引く currentsheet.Range(xlsRowCnt, 1, xlsRowCnt, 9).Style .Border.SetTopBorder(XLBorderStyleValues.Thin) .Border.SetBottomBorder(XLBorderStyleValues.Thin) .Border.SetLeftBorder(XLBorderStyleValues.Thin) .Border.SetRightBorder(XLBorderStyleValues.Thin); // 44行毎(ヘッダーを除いた行数)にシート作成 if (xlsRowCnt == 44) { pageCnt++; xlsRowCnt = 4; // ヘッダーシートのコピー、ヘッダー部の指定 pdf.sheetCopy(ref workbook, ref headersheet, ref currentsheet, pageCnt, maxPage, strNow); } rowCnt++; xlsRowCnt++; //各合計値を入れる decUriKin = decUriKin + decimal.Parse(drTokuiCheak[7].ToString()); decNyukin = decNyukin + decimal.Parse(drTokuiCheak[8].ToString()); //最終行の場合 if (rowCnt > dtChkList.Rows.Count) { //マージ currentsheet.Range("A" + xlsRowCnt, "E" + xlsRowCnt).Merge(); currentsheet.Row(xlsRowCnt).Height = 10; currentsheet.Cell(xlsRowCnt, 1).Value = "◆◆◆ 合 計 ◆◆◆"; currentsheet.Cell(xlsRowCnt, 1).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center; currentsheet.Cell(xlsRowCnt, 6).Value = decUriKin.ToString(); //売上金額 currentsheet.Cell(xlsRowCnt, 7).Value = decNyukin.ToString(); //入金金額 //最終行、各項目のカンマ処理と文字寄せ for (int intCnt = 6; intCnt < 8; intCnt++) { currentsheet.Cell(xlsRowCnt, intCnt).Style.NumberFormat.Format = "#,0"; currentsheet.Cell(xlsRowCnt, intCnt).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Right; } // 1行分のセルの周囲に罫線を引く currentsheet.Range(xlsRowCnt, 1, xlsRowCnt, 16).Style .Border.SetTopBorder(XLBorderStyleValues.Thin) .Border.SetBottomBorder(XLBorderStyleValues.Thin) .Border.SetLeftBorder(XLBorderStyleValues.Thin) .Border.SetRightBorder(XLBorderStyleValues.Thin); //初期化 decUriKin = 0; decNyukin = 0; } } // ヘッダーシート削除 headersheet.Delete(); // workbookを保存 string strOutXlsFile = strWorkPath + strDateTime + ".xlsx"; workbook.SaveAs(strOutXlsFile); // workbookを解放 workbook.Dispose(); // PDF化の処理 return(pdf.createPdf(strOutXlsFile, strDateTime, 1)); } catch (Exception ex) { throw (ex); } finally { // Workフォルダの全ファイルを取得 string[] files = System.IO.Directory.GetFiles(strWorkPath, "*", System.IO.SearchOption.AllDirectories); // Workフォルダ内のファイル削除 foreach (string filepath in files) { //File.Delete(filepath); } } }
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)); }