EPPLUS エクセルファイル作成サンプル

EPPLUS Make ExcelFile Sample

using OfficeOpenXml;
using OfficeOpenXml.Style;

  /// <summary>
  /// Modelデータを元にExcel作成
  /// </summary>
  /// <param name="excelModel"></param>
  /// <returns>byte[]</returns>
  public byte[] MakeDetailsAsExcel(ContractExcelModel excelModel)
  {
      using (var package = new ExcelPackage())
      {
          var worksheet = package.Workbook.Worksheets.Add("Sheet1");
          worksheet.Column(1).Width = 3;
          var allRangeHeight = 500;

          // worksheet.Column(2)からworksheet.Column(32)まで同じ幅を設定
          for (int i = 2; i <= 32; i++)
          {
              worksheet.Column(i).Width = 3.9;
          }

          for (int i = 1; i <= allRangeHeight; i++)
          {
              worksheet.Row(i).Height = 11.25;
          }

          // 現在の行数。基本的に該当行の処理を終えると同時に改行(+1)する(currentRow++)。処理が複雑な場合は最後に行数を足す。
          int currentRow = 1;
          int headBlockRow = 1;

          var lightGrayColor = Color.FromArgb(242, 242, 242);
          var grayColor = Color.FromArgb(217, 217, 217);

          var allRange = worksheet.Cells[1, 1, allRangeHeight, 32];
          allRange.Style.Fill.PatternType = ExcelFillStyle.Solid;
          allRange.Style.Fill.BackgroundColor.SetColor(System.Drawing.ColorTranslator.FromHtml("#FFFFFF"));
          allRange.Style.Font.Name = "ＭＳ Ｐゴシック";
          allRange.Style.Font.Size = 9;
          allRange.Style.VerticalAlignment = ExcelVerticalAlignment.Center;

          var listData = new ExcelListDefaultModel();
          // データ設定後、現在の行数に＋１をする
          currentRow++;
          
          headBlockRow = currentRow;
          var infoRange = worksheet.Cells["B" + currentRow + ":L" + (currentRow + 5)];
          infoRange.Style.Border.BorderAround(ExcelBorderStyle.Thin);
          worksheet.Cells["B" + currentRow++].Value = excelModel.Name;
          currentRow++;
          
          // Modelの値をエクセルに打ち込む
          // 例
          // worksheet.Row(currentRow).Height = GetValueBlockHeight("value", 0, 3);
          // SetBlockTitleRange(worksheet, "title", currentRow++);
          // SetExcelRange(worksheet.Cells["B" + currentRow + ":AE" + currentRow], "value", Color.White, false, true);
          // SetListOnePartsRange(worksheet, "title", currentRow++);
          // SetListTwoPartsAutoHeightRange(worksheet, "title", "value", currentRow++);
          // SetListTwoPartsNotMergeRange(worksheet, "title", "value", currentRow++);
          
          
          #region 最後に出力範囲指定
          worksheet.PrinterSettings.PrintArea = worksheet.Cells["A1:AF" + currentRow];
          worksheet.View.PageBreakView = true;
          worksheet.View.ZoomScale = 100;
          worksheet.PrinterSettings.FitToPage = true;
          worksheet.PrinterSettings.PaperSize = ePaperSize.A4;
          worksheet.PrinterSettings.FitToHeight = 0;
          worksheet.PrinterSettings.FitToWidth = 1;
          worksheet.HeaderFooter.OddFooter.RightAlignedText = String.Format("{0} / {1}", ExcelHeaderFooter.PageNumber, ExcelHeaderFooter.NumberOfPages);
          #endregion

          return package.GetAsByteArray();
      }
  }

  /// <summary>
  /// 値からセルの高さを取得
  /// </summary>
  /// <param name="text"></param>
  /// <param name="defaultHeight">一行でも高さを取りたい場合、足す分の高さの値</param>
  /// <param name="blockType">1:デフォルト、2:小さいブロック、3:注意事項用の一番大きいブロック</param>
  /// <returns></returns>
  private static double GetValueBlockHeight(string text, double defaultHeight = 0, sbyte blockType = 1)
  {
      double height = 11.25;
      double rowHeight = 11.25;
      int textCount = 50;
      switch (blockType)
      {
          case 1:
              textCount = 50;
              break;
          case 2:
              textCount = 18;
              break;
          case 3:
              textCount = 65;
              height = 22.50;
              break;
      }

      if (string.IsNullOrEmpty(text))
      {
          return height;
      }
      else
      {
          text = text.Replace("\r\n", "\n");
      }     

      if (text.Length < textCount && text.IndexOf("\n") == -1)
      {
          return (height + defaultHeight);
      }
      else
      {
          string[] textList = text.Split('\n');
          int count = textList.Length + 1;
          foreach (var detail in textList)
          {
              if (detail.Length > textCount)
              {
                  count += detail.Length / textCount + 1;
              }
          }
          height = count * rowHeight;

          return (height + defaultHeight);
      }
  }

  /// <summary>
  /// ブロックのタイトル作成
  /// </summary>
  /// <param name="worksheet"></param>
  /// <param name="text"></param>
  /// <param name="row"></param>
  /// <param name="isSlimTitle">false=高さがあるタイトル, true=高さ非設定</param>
  public void SetBlockTitleRange(ExcelWorksheet worksheet, string text, int row, bool isSlimTitle = false)
  {
      if (!isSlimTitle)
      {
          worksheet.Row(row).Height = 30;
      }
      var titleRange = worksheet.Cells["B"+ row + ":AE" + row];
      titleRange.Merge = true;
      titleRange.Style.Font.Bold = true;
      titleRange.Style.Fill.BackgroundColor.SetColor(Color.FromArgb(89, 89, 89));
      titleRange.Style.Font.Color.SetColor(Color.White);
      titleRange.Style.Border.BorderAround(ExcelBorderStyle.Thin);
      titleRange.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
      worksheet.Cells["B" + row].Value = text;
  }

  /// <summary>
  /// 対象のExcelRangeを結合して枠線、背景色、値を設定する
  /// Rangeは行単位ではないので、行の高さは各自設定する。
  /// </summary>
  /// <param name="range"></param>
  /// <param name="text"></param>
  /// <param name="bgColor">背景色</param>
  /// <param name="isCenter">真ん中配置</param>
  /// <param name="isTop">上部配置</param>
  public void SetExcelRange(ExcelRange range, string text, Color bgColor, bool isCenter = false, bool isTop = false)
  {
    range.Style.Border.BorderAround(ExcelBorderStyle.Thin);
    range.Merge = true;
    range.Style.WrapText = true;
    range.Value = text;

    if(bgColor != Color.White)
    {
        range.Style.Fill.BackgroundColor.SetColor(bgColor);
    }
    if (isCenter)
    {
        range.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
    }
    if (isTop)
    {
        range.Style.VerticalAlignment = ExcelVerticalAlignment.Top;
    }
  }

  /// <summary>
  /// リストの灰色背景のヘッダ/1行の値表示用セル作成
  /// </summary>
  /// <param name="worksheet"></param>
  /// <param name="text"></param>
  /// <param name="row"></param>
  /// <param name="isHeader">ヘッダー判定</param>
  /// <param name="hasHeaderCharacter">ヘッダーの先頭に「■」をつけるか判定</param>
  public void SetListOnePartsRange(ExcelWorksheet worksheet, string text, int row, bool isHeader = true, bool hasHeaderCharacter = true)
  {
    var headerRange = worksheet.Cells["B" + row + ":AE" + row];
    headerRange.Style.Border.BorderAround(ExcelBorderStyle.Thin);

    var headerCharacter = string.Empty;
    if (hasHeaderCharacter)
    {
        headerCharacter = "■";
    }

    // ヘッダーの場合、背景色を設定し、表示文言の先頭に"■"を追加
    if (isHeader)
    {
        headerRange.Style.Fill.BackgroundColor.SetColor(Color.FromArgb(217, 217, 217));
        worksheet.Cells["B" + row].Value = headerCharacter + text;
    }else
    {
        worksheet.Cells["B" + row].Value = text;
    }
  }

  /// <summary>
  /// リストの1行(結合あり)のラベル/値のセル作成
  /// </summary>
  /// <param name="worksheet"></param>
  /// <param name="label"></param>
  /// <param name="value"></param>
  /// <param name="row"></param>
  /// <param name="defaultHeight"></param>
  /// <param name="isLabelBgColor">ラベルに色設定</param>
  public void SetListTwoPartsAutoHeightRange(ExcelWorksheet worksheet, string label, string value, int row, double defaultHeight = 0, bool isLabelBgColor = false)
  {
    // 値の長さから高さを設定
    worksheet.Row(row).Height = GetValueBlockHeight(value, defaultHeight);

    var listLabelRange = worksheet.Cells["B" + row + ":H" + row];
    listLabelRange.Style.Border.BorderAround(ExcelBorderStyle.Thin);
    listLabelRange.Merge = true;
    // 改行文字対応
    listLabelRange.Style.WrapText = true;

    if (isLabelBgColor)
    {
        listLabelRange.Style.Fill.BackgroundColor.SetColor(Color.FromArgb(242, 242, 242));
    }
    worksheet.Cells["B" + row].Value = label;

    var listValueRange = worksheet.Cells["I" + row + ":AE" + row];
    listValueRange.Style.Border.BorderAround(ExcelBorderStyle.Thin);
    listValueRange.Merge = true;
    // 改行文字対応
    listValueRange.Style.WrapText = true;
    listValueRange.Value = value;
  }

  /// <summary>
  /// リストの1行(結合なし)のラベル/値のセル作成
  /// </summary>
  /// <param name="worksheet"></param>
  /// <param name="label"></param>
  /// <param name="value"></param>
  /// <param name="row"></param>
  /// <param name="isViewValue">値の枠を表示するか否か</param>
  /// <param name="height">セルの高さ</param>
  /// <param name="isLongLabel">ラベルの長さが長い場合、ラベルと値の区切り時点を変える</param>
  /// <param name="isChild">ラベルに子要素を表示する場合</param>
  /// <param name="isRightLabel">右寄りラベル</param>
  public void SetListTwoPartsNotMergeRange(ExcelWorksheet worksheet, string label, string value, int row, bool isViewValue = true, double height = 0, bool isLongLabel = false, bool isChild = false, bool isRightLabel = false)
  {
    var labelStartColumn = "B";
    var labelEndColumn = "H";
    var valueStartColumn = "I";
    var valueEndColumn = "AE";

    // 高さが設定されている場合、高さを設定
    if (height > 0)
    {
        worksheet.Row(row).Height = height;
    }

    // ラベル名が長い場合
    if (isLongLabel)
    {
        labelEndColumn = "M";
        valueStartColumn = "N";
    }

    var listLabelRange = worksheet.Cells[labelStartColumn + row + ":" + labelEndColumn + row];  
    listLabelRange.Style.Border.BorderAround(ExcelBorderStyle.Thin);

    if (isChild)
    {
        worksheet.Cells["C" + row].Value = label;
    }
    else
    {
        if (isRightLabel)
        {
            worksheet.Cells[labelEndColumn + row].Value = label;
            worksheet.Cells[labelEndColumn + row].Style.HorizontalAlignment = ExcelHorizontalAlignment.Right;
        }
        else
        {
            worksheet.Cells[labelStartColumn + row].Value = label;
        }
    }

    // 「オプションサービス」用。isViewValueがfalseの場合は値枠は非表示
    if (isViewValue)
    {
        var listValueRange = worksheet.Cells[valueStartColumn + row + ":" + valueEndColumn + row];
        listValueRange.Style.Border.BorderAround(ExcelBorderStyle.Thin);
        worksheet.Cells[valueStartColumn + row].Value = value;
    }
  }