C# と VB.NET の質問掲示板

ASP.NET、C++/CLI、Java 何でもどうぞ

C# と VB.NET の入門サイト

ClosedXmlを使用してEXCEL出力

[トピック内 11 記事 (1 - 11 表示)]  << 0 >>

■93321 / inTopicNo.1)  ClosedXmlを使用してEXCEL出力
  
□投稿者/ kk (1回)-(2019/12/04(Wed) 19:21:19)

分類:[C#] 

visual studio 2019
ClosedXml

初心者です。C#とClosedXmlを使用してExcelファイル出力をしています。
年のところを「4月 5月 6月」とだけ表示したいですが、下記のように出力されてうまく表示されません。
また、金額を出力するところがどうソースを書けばよいか分からず悩んでおります。
ちなみにピボットテーブルを使用すればいいと思いますが、使用しない方法を試しています。
どうすればいいでしょうか? よろしくお願いいたします。

public class Pastry
    {
        public Pastry(string code, string name, int amount, string date, string month)
        {
            Code = code;
            Date = date;
            Name = name;
            NumberOfOrders = amount;
            Month = month;
        }

        public string Code { get; set; }
        public string Name { get; set; }
        public int NumberOfOrders { get; set; }
        public string Date { get; set; }
        public string Month { get; set; }
    }

pastries = new List<Pastry>
       {
         new Pastry("1","Croissant", 150, "2019/04/01", "4月"),
         new Pastry("1","Croissant", 200, "2019/04/02", "4月"),
         new Pastry("1","Croissant", 250, "2019/05/01", "5月"),
         new Pastry("1","Croissant", 134, "2019/06/01", "6月"),
         new Pastry("2","Doughnut", 250, "2019/04/02", "4月"),
         new Pastry("2","Doughnut", 225, "2019/05/02", "5月"),
         new Pastry("2","Doughnut", 210, "2019/06/02", "6月"),
         new Pastry("3","Bearclaw", 134, "2019/04/03", "4月"),
         new Pastry("3","Bearclaw", 184, "2019/05/03", "5月"),
         new Pastry("3","Bearclaw", 124, "2019/06/03", "6月"),
         new Pastry("4","Danish", 394, "2019/04/04", "4月"),
         new Pastry("4","Danish", 190, "2019/05/04", "5月"),
         new Pastry("4","Danish", 221, "2019/06/04", "6月"),
         new Pastry("5","Scone", 135, "2019/04/05", "4月"),
         new Pastry("5","Scone", 122, "2019/05/05", "5月"),
         new Pastry("5","Scone", 243, "2019/06/05", "6月")
        };

var workbook = new XLWorkbook();

        var aggregateSheet = workbook.Worksheets.Add("集計");

        aggregateSheet.SetShowZeros(false);
    aggregateSheet.SetAutoFilter();
        aggregateSheet.SheetView.FreezeRows(2);

         //ヘッダ出力       
        aggregateSheet.Cell("A2").Value = "名前";
        aggregateSheet.Cell("B2").Value = "コード";

        //データ出力
        int rowIndex = 3;
        int collumnIndex = 3;

        //データの書出し
        foreach (var row in pastries)
        {
            aggregateSheet.Cell(rowIndex, "A").Value = row.Name;
            aggregateSheet.Cell(rowIndex, "B").Value = row.Code;
            aggregateSheet.Cell(2, collumnIndex).Value = row.Month;
            aggregateSheet.Cell(2, collumnIndex).Style.NumberFormat.SetFormat("MM月");

            rowIndex++;
            collumnIndex++;
         }


※Excelファイル
        ※月の表示を4月 5月 6月と表示
名前	 コード	 04月	04月	05月	06月	04月	05月	06月	04月	05月	06月	04月
Croissant     1																
Croissant     1	 ※ここに金額を出力したい。															
Croissant     1																
Croissant     1																
Doughnut      2																
Doughnut      2																
Doughnut      2																
Bearclaw      3																
Bearclaw      3																
Bearclaw      3																
Danish	      4																
Danish	      4																
Danish	      4																
Scone	      5																
Scone	      5																
Scone	      5	


引用返信 編集キー/
■93326 / inTopicNo.2)  Re[1]: ClosedXmlを使用してEXCEL出力
□投稿者/ Hongliang (931回)-(2019/12/05(Thu) 11:06:38)
> 年のところを「4月 5月 6月」とだけ表示したいですが、下記のように出力されてうまく表示されません。
先行ゼロを表示しない書式は「M月」です。「MM月」だと先行ゼロが付くようになります。

> また、金額を出力するところがどうソースを書けばよいか分からず悩んでおります。
金額というのはPastryクラスで言うところのNumberOfOrdersでいいのでしょうか?
出力は1行につき1か所だけで、対角線的に表示する形でいいのでしょうか?

名前	 コード	 4月	4月	5月	4月	5月
Croissant     1	 150	
Croissant     1		200
Croissant     1			250
Doughnut      2				250
Doughnut      2					225

引用返信 編集キー/
■93327 / inTopicNo.3)  Re[2]: ClosedXmlを使用してEXCEL出力
□投稿者/ kk (2回)-(2019/12/05(Thu) 12:03:36)
No93326 (Hongliang さん) に返信
> > 年のところを「4月 5月 6月」とだけ表示したいですが、下記のように出力されてうまく表示されません。
> 先行ゼロを表示しない書式は「M月」です。「MM月」だと先行ゼロが付くようになります。
> 
>>また、金額を出力するところがどうソースを書けばよいか分からず悩んでおります。
> 金額というのはPastryクラスで言うところのNumberOfOrdersでいいのでしょうか?
> 出力は1行につき1か所だけで、対角線的に表示する形でいいのでしょうか?
> 
> 名前	 コード	 4月	4月	5月	4月	5月
> Croissant     1	 150	
> Croissant     1		200
> Croissant     1			250
> Doughnut      2				250
> Doughnut      2					225

説明不足ですみません。
下記の表が完成イメージです。罫線出力も考えています。

名前	  コード4月 5月 6月 合計
Croissant     1	 150         150												
Croissant     1	 200         200										
Croissant     1	   250     250												
Croissant     1	         134 134												
Doughnut      2	 250	    250												
Doughnut      2	     225     225												
Doughnut      2	     210 210												
Bearclaw      3	 134         134												
Bearclaw      3	   184     184												
Bearclaw      3	         124 124													
Danish	      4	 394         394												
Danish	      4	     190     190												
Danish	      4	         221 221													
Scone	      5	 135         135												
Scone	      5	 122 122												
Scone	      5	         243 243
合計      1263 971 932 3166

引用返信 編集キー/
■93328 / inTopicNo.4)  Re[3]: ClosedXmlを使用してEXCEL出力
□投稿者/ 魔界の仮面弁士 (2509回)-(2019/12/05(Thu) 12:12:26)
No93327 (kk さん) に返信
> 下記の表が完成イメージです。罫線出力も考えています。

年の異なる同月があった場合に、どのように出力させるのかは気になりますが、
列データを確定するため、Excel への出力前に LINQ を使って
Month の一覧を取得しておくと楽だと思います。

それと、行方向の出力順は、List<Pastry> に格納されている順でしょうか?
それとも、Name, Code, Date 順でしょうか?
引用返信 編集キー/
■93329 / inTopicNo.5)  Re[4]: ClosedXmlを使用してEXCEL出力
□投稿者/ kk (3回)-(2019/12/05(Thu) 12:31:57)
No93328 (魔界の仮面弁士 さん) に返信
> ■No93327 (kk さん) に返信
>>下記の表が完成イメージです。罫線出力も考えています。
>
> 年の異なる同月があった場合に、どのように出力させるのかは気になりますが、
> 列データを確定するため、Excel への出力前に LINQ を使って
> Month の一覧を取得しておくと楽だと思います。
>
> それと、行方向の出力順は、List<Pastry> に格納されている順でしょうか?
> それとも、Name, Code, Date 順でしょうか?

年の異なる同月は考えていませんでした。
LINQを使ってMonthの一覧を取得で年の異なる同月の出力は可能でしょうか?

Name,Code,Dateの順番です。
引用返信 編集キー/
■93330 / inTopicNo.6)  Re[5]: ClosedXmlを使用してEXCEL出力
□投稿者/ 魔界の仮面弁士 (2510回)-(2019/12/05(Thu) 14:09:27)
No93329 (kk さん) に返信
> 年の異なる同月は考えていませんでした。
どちらの意味でしょう?

「年をまたがるデータも今後ありえるので、考えておく必要がある」のか、
「そういうデータが含まれることはありえないので、考える必要が無い」のか。


> LINQを使ってMonthの一覧を取得で年の異なる同月の出力は可能でしょうか?
> Name,Code,Dateの順番です。

これでどうでしょう。年をまたがるデータは存在しない想定です。
月の並びを 1月〜12月ではなく、4月〜翌3月にしたいような場合は、OrderBy を調整してください。


int firstMonthColIndex = 3;  // 最初の月を何列目に配置するか
var months = pastries.Select(p => p.Month).Distinct()
                     .OrderBy(m => int.Parse(m.TrimEnd('月')))
                     .Select((month, index) => new { Key = month, Value = index + firstMonthColIndex })
                     .ToDictionary(k => k.Key, v => v.Value);
var q = pastries.OrderBy(p => p.Name).ThenBy(p => p.Code).ThenBy(p => p.Date);
foreach (var row in q)
{
 int colIndex = months[row.Month];
  aggregateSheet.Cell(rowIndex, 1).Value = row.Name;
  aggregateSheet.Cell(rowIndex, 2).Value = row.Code;
  aggregateSheet.Cell(rowIndex, colIndex).Value = row.NumberOfOrders;
}

引用返信 編集キー/
■93331 / inTopicNo.7)  Re[6]: ClosedXmlを使用してEXCEL出力
□投稿者/ kk (4回)-(2019/12/05(Thu) 14:28:54)
No93330 (魔界の仮面弁士 さん) に返信
> ■No93329 (kk さん) に返信
>>年の異なる同月は考えていませんでした。
> どちらの意味でしょう?
>
> 「年をまたがるデータも今後ありえるので、考えておく必要がある」のか、
> 「そういうデータが含まれることはありえないので、考える必要が無い」のか。
>
>
>>LINQを使ってMonthの一覧を取得で年の異なる同月の出力は可能でしょうか?
>>Name,Code,Dateの順番です。
>
> これでどうでしょう。年をまたがるデータは存在しない想定です。
> 月の並びを 1月〜12月ではなく、4月〜翌3月にしたいような場合は、OrderBy を調整してください。
>
>
> int firstMonthColIndex = 3; // 最初の月を何列目に配置するか
> var months = pastries.Select(p => p.Month).Distinct()
> .OrderBy(m => int.Parse(m.TrimEnd('月')))
> .Select((month, index) => new { Key = month, Value = index + firstMonthColIndex })
> .ToDictionary(k => k.Key, v => v.Value);
> var q = pastries.OrderBy(p => p.Name).ThenBy(p => p.Code).ThenBy(p => p.Date);
> foreach (var row in q)
> {
>  int colIndex = months[row.Month];
> aggregateSheet.Cell(rowIndex, 1).Value = row.Name;
> aggregateSheet.Cell(rowIndex, 2).Value = row.Code;
> aggregateSheet.Cell(rowIndex, colIndex).Value = row.NumberOfOrders;
> }

年をまたがるデータも今後ありえるので、考えておく必要がありました。
説明が下手ですみません。
引用返信 編集キー/
■93333 / inTopicNo.8)  Re[6]: ClosedXmlを使用してEXCEL出力
□投稿者/ kk (5回)-(2019/12/05(Thu) 15:53:19)
No93330 (魔界の仮面弁士 さん) に返信
> ■No93329 (kk さん) に返信
>>年の異なる同月は考えていませんでした。
> どちらの意味でしょう?
>
> 「年をまたがるデータも今後ありえるので、考えておく必要がある」のか、
> 「そういうデータが含まれることはありえないので、考える必要が無い」のか。
>
>
>>LINQを使ってMonthの一覧を取得で年の異なる同月の出力は可能でしょうか?
>>Name,Code,Dateの順番です。
>
> これでどうでしょう。年をまたがるデータは存在しない想定です。
> 月の並びを 1月〜12月ではなく、4月〜翌3月にしたいような場合は、OrderBy を調整してください。
>
>
> int firstMonthColIndex = 3; // 最初の月を何列目に配置するか
> var months = pastries.Select(p => p.Month).Distinct()
> .OrderBy(m => int.Parse(m.TrimEnd('月')))
> .Select((month, index) => new { Key = month, Value = index + firstMonthColIndex })
> .ToDictionary(k => k.Key, v => v.Value);
> var q = pastries.OrderBy(p => p.Name).ThenBy(p => p.Code).ThenBy(p => p.Date);
> foreach (var row in q)
> {
>  int colIndex = months[row.Month];
> aggregateSheet.Cell(rowIndex, 1).Value = row.Name;
> aggregateSheet.Cell(rowIndex, 2).Value = row.Code;
> aggregateSheet.Cell(rowIndex, colIndex).Value = row.NumberOfOrders;
> }

上記の方法を試したところ、「入力文字列の形式が正しくありません」 ハンドルされていない例外が発生します。
引用返信 編集キー/
■93339 / inTopicNo.9)  Re[7]: ClosedXmlを使用してEXCEL出力
□投稿者/ 魔界の仮面弁士 (2512回)-(2019/12/05(Thu) 18:19:19)
No93333 (kk さん) に返信
> 上記の方法を試したところ、「入力文字列の形式が正しくありません」 ハンドルされていない例外が発生します。

少なくとも No93321 の pastries に対してテストした限りでは問題が出なかったので、
恐らくは kk さんが伝え忘れた、何か追加の要件があるのでしょう。
(例えば、Month が null や "" になっているとか)
引用返信 編集キー/
■93340 / inTopicNo.10)  Re[8]: ClosedXmlを使用してEXCEL出力
□投稿者/ kk (6回)-(2019/12/05(Thu) 19:58:04)
No93339 (魔界の仮面弁士 さん) に返信
> ■No93333 (kk さん) に返信
>>上記の方法を試したところ、「入力文字列の形式が正しくありません」 ハンドルされていない例外が発生します。
>
> 少なくとも No93321 の pastries に対してテストした限りでは問題が出なかったので、
> 恐らくは kk さんが伝え忘れた、何か追加の要件があるのでしょう。
> (例えば、Month が null や "" になっているとか)

わかりました。もう一度確認してみます。
対応していただきありがとうございます。
引用返信 編集キー/
■93341 / inTopicNo.11)  Re[7]: ClosedXmlを使用してEXCEL出力
□投稿者/ 魔界の仮面弁士 (2513回)-(2019/12/06(Fri) 09:07:47)
2019/12/06(Fri) 09:09:51 編集(投稿者)

No93331 (kk さん) に返信
> 年をまたがるデータも今後ありえるので、考えておく必要がありました。

考えてみた結果、どのように修正することになりそうですか?

年をまたがるデータがあるのだとして、new List<Pastry> 内に
格納されるデータが変わってくるのか否か。

案1) Pastry の構造を変えて、Year と Month という 2 つのプロパティを持たせるようにする。
案2) Pastry のデータを変えて、Month が "2019/04" などのような年月を表す文字列を持つようにする。
案3) Pastry の内容は一切変えず、Date プロパティから月名を得るような処理にする。

ただ、そもそも Date 部の月が Month と同じであるのなら、Month メンバーは不要になる気がします。
といっても、頂いた情報からでは Date と Month に関連性があるのかどうかは不明なのですが。


また、複数年にわたる月がある場合、それを Excel に出力するに当たり、
どういう表現で出力するのかも再検討する必要がありそうです。

たとえば 平成31年4月と 令和2年5月のデータが、共に "5月" 表記の同じ列に出力するのか、
あるいは、列名表記は "5月" のまま、4月,5月,6月,…12月,1月,2月,3月,4月,5月,… などと
年が変わるごとに次の列を用意するようにするのか、もしくは列名表記を改めて、
"5月" ではなく、"2019/05" や "2020/05" な表記に改めるなどなど。



それともう一つ気になっているのが、月数の表記範囲。たとえば

// 4月と6月はあるが、5月は無い
pastries = new List<Pastry>
{
  new Pastry("1","Croissant", 150, "2019/04/01", "4月"),
  new Pastry("1","Croissant", 200, "2019/04/02", "4月"),
  new Pastry("1","Croissant", 134, "2019/06/01", "6月"),
};

なデータの場合、Excel に対して "5月" な空列を用意する必要があるのかどうかも気になります。
引用返信 編集キー/

このトピックをツリーで一括表示


トピック内ページ移動 / << 0 >>

このトピックに書きこむ