// xlsx ファイルストリームの作成 FileStream outputStream = new FileStream("TemplateGenerateReport.xlsx", FileMode.Create); // 新規ワークブックの作成 var workbook = new GrapeCity.Documents.Excel.Workbook(); // 帳票テンプレートを読み込む var templateFile = this.GetResourceStream("xlsx\\Template_SalesDataGroup_GenerateReport.xlsx"); workbook.Open(templateFile); #region カスタムクラスの定義 //public class SalesData //{ // public List<SalesRecord> Sales; //} //public class SalesRecord //{ // public string Area; // public string City; // public string Category; // public string Name; // public double Revenue; //} #endregion var datasource = new SalesData { Sales = new List<SalesRecord>() }; #region データの初期化 var record1 = new SalesRecord { Area = "北米", City = "シカゴ", Category = "家電製品", Name = "Bose 785593-0050", Revenue = 92800 }; datasource.Sales.Add(record1); var record2 = new SalesRecord { Area = "北米", City = "ニューヨーク", Category = "家電製品", Name = "Bose 785593-0050", Revenue = 92800 }; datasource.Sales.Add(record2); var record3 = new SalesRecord { Area = "南米", City = "サンティアゴ", Category = "家電製品", Name = "Bose 785593-0050", Revenue = 19550 }; datasource.Sales.Add(record3); var record4 = new SalesRecord { Area = "北米", City = "シカゴ", Category = "家電製品", Name = "Canon EOS 1500D", Revenue = 98650 }; datasource.Sales.Add(record4); var record5 = new SalesRecord { Area = "北米", City = "ミネソタ", Category = "家電製品", Name = "Canon EOS 1500D", Revenue = 89110 }; datasource.Sales.Add(record5); var record6 = new SalesRecord { Area = "南米", City = "サンティアゴ", Category = "家電製品", Name = "Canon EOS 1500D", Revenue = 459000 }; datasource.Sales.Add(record6); var record7 = new SalesRecord { Area = "北米", City = "シカゴ", Category = "家電製品", Name = "Haier 394L 4Star", Revenue = 367050 }; datasource.Sales.Add(record7); var record8 = new SalesRecord { Area = "南米", City = "キト", Category = "家電製品", Name = "Haier 394L 4Star", Revenue = 729100 }; datasource.Sales.Add(record8); var record9 = new SalesRecord { Area = "南米", City = "サンティアゴ", Category = "家電製品", Name = "Haier 394L 4Star", Revenue = 578900 }; datasource.Sales.Add(record9); var record10 = new SalesRecord { Area = "北米", City = "フリーモント", Category = "家電製品", Name = "IFB 6.5 Kg FullyAuto", Revenue = 904930 }; datasource.Sales.Add(record10); var record11 = new SalesRecord { Area = "南米", City = "ブエノスアイレス", Category = "家電製品", Name = "IFB 6.5 Kg FullyAuto", Revenue = 673800 }; datasource.Sales.Add(record11); var record12 = new SalesRecord { Area = "南米", City = "メデジン", Category = "家電製品", Name = "IFB 6.5 Kg FullyAuto", Revenue = 82910 }; datasource.Sales.Add(record12); var record13 = new SalesRecord { Area = "北米", City = "シカゴ", Category = "家電製品", Name = "Mi LED 40inch", Revenue = 550010 }; datasource.Sales.Add(record13); var record14 = new SalesRecord { Area = "北米", City = "ミネソタ", Category = "家電製品", Name = "Mi LED 40inch", Revenue = 1784702 }; datasource.Sales.Add(record14); var record15 = new SalesRecord { Area = "南米", City = "サンティアゴ", Category = "家電製品", Name = "Mi LED 40inch", Revenue = 102905 }; datasource.Sales.Add(record15); var record16 = new SalesRecord { Area = "北米", City = "シカゴ", Category = "家電製品", Name = "Sennheiser HD 4.40-BT", Revenue = 178100 }; datasource.Sales.Add(record16); var record17 = new SalesRecord { Area = "南米", City = "キト", Category = "家電製品", Name = "Sennheiser HD 4.40-BT", Revenue = 234459 }; datasource.Sales.Add(record17); var record18 = new SalesRecord { Area = "北米", City = "ミネソタ", Category = "携帯電話", Name = "Iphone XR", Revenue = 1734621 }; datasource.Sales.Add(record18); var record19 = new SalesRecord { Area = "南米", City = "サンティアゴ", Category = "携帯電話", Name = "Iphone XR", Revenue = 109300 }; datasource.Sales.Add(record19); var record20 = new SalesRecord { Area = "北米", City = "シカゴ", Category = "携帯電話", Name = "OnePlus 7Pro", Revenue = 499100 }; datasource.Sales.Add(record20); var record21 = new SalesRecord { Area = "南米", City = "キト", Category = "携帯電話", Name = "OnePlus 7Pro", Revenue = 215000 }; datasource.Sales.Add(record21); var record22 = new SalesRecord { Area = "北米", City = "ミネソタ", Category = "携帯電話", Name = "Redmi 7", Revenue = 81650 }; datasource.Sales.Add(record22); var record23 = new SalesRecord { Area = "南米", City = "キト", Category = "携帯電話", Name = "Redmi 7", Revenue = 276390 }; datasource.Sales.Add(record23); var record24 = new SalesRecord { Area = "北米", City = "ミネソタ", Category = "携帯電話", Name = "Samsung S9", Revenue = 896250 }; datasource.Sales.Add(record24); var record25 = new SalesRecord { Area = "南米", City = "ブエノスアイレス", Category = "携帯電話", Name = "Samsung S9", Revenue = 896250 }; datasource.Sales.Add(record25); var record26 = new SalesRecord { Area = "南米", City = "キト", Category = "携帯電話", Name = "Samsung S9", Revenue = 716520 }; datasource.Sales.Add(record26); #endregion // グローバル設定を追加 workbook.Names.Add("TemplateOptions.KeepLineSize", "true"); // データソースを追加 workbook.AddDataSource("ds", datasource); // 帳票テンプレートにデータを連結して作成した帳票を、新しいワークブックのインスタンスとして返す IWorkbook report = workbook.GenerateReport(); // 帳票を xlsx としてストリームに保存 report.Save(outputStream); // xlsx ストリームを閉じる outputStream.Close();
' xlsx ファイルストリームの作成 Dim outputStream = File.Create("TemplateGenerateReport.xlsx") ' 新規ワークブックの作成 Dim workbook As New Workbook ' 帳票テンプレートを読み込む Dim templateFile = GetResourceStream("xlsx\Template_SalesDataGroup_GenerateReport.xlsx") workbook.Open(templateFile) #Region "カスタムクラスの定義" ' Public Class SalesData ' Public Sales As List(Of SalesRecord) ' End Class ' Public Class SalesRecord ' Public Area As String ' Public City As String ' Public Category As String ' Public Name As String ' Public Revenue As Double ' End Class #End Region #Region "データの初期化" Dim salesData As New List(Of SalesRecord) From { New SalesRecord With { .Area = "北米", .City = "シカゴ", .Category = "家電製品", .Name = "Bose 785593-0050", .Revenue = 92800 }, New SalesRecord With { .Area = "北米", .City = "ニューヨーク", .Category = "家電製品", .Name = "Bose 785593-0050", .Revenue = 92800 }, New SalesRecord With { .Area = "南米", .City = "サンティアゴ", .Category = "家電製品", .Name = "Bose 785593-0050", .Revenue = 19550 }, New SalesRecord With { .Area = "北米", .City = "シカゴ", .Category = "家電製品", .Name = "Canon EOS 1500D", .Revenue = 98650 }, New SalesRecord With { .Area = "北米", .City = "ミネソタ", .Category = "家電製品", .Name = "Canon EOS 1500D", .Revenue = 89110 }, New SalesRecord With { .Area = "南米", .City = "サンティアゴ", .Category = "家電製品", .Name = "Canon EOS 1500D", .Revenue = 459000 }, New SalesRecord With { .Area = "北米", .City = "シカゴ", .Category = "家電製品", .Name = "Haier 394L 4Star", .Revenue = 367050 }, New SalesRecord With { .Area = "南米", .City = "キト", .Category = "家電製品", .Name = "Haier 394L 4Star", .Revenue = 729100 }, New SalesRecord With { .Area = "南米", .City = "サンティアゴ", .Category = "家電製品", .Name = "Haier 394L 4Star", .Revenue = 578900 }, New SalesRecord With { .Area = "北米", .City = "フリーモント", .Category = "家電製品", .Name = "IFB 6.5 Kg FullyAuto", .Revenue = 904930 }, New SalesRecord With { .Area = "南米", .City = "ブエノスアイレス", .Category = "家電製品", .Name = "IFB 6.5 Kg FullyAuto", .Revenue = 673800 }, New SalesRecord With { .Area = "南米", .City = "メデジン", .Category = "家電製品", .Name = "IFB 6.5 Kg FullyAuto", .Revenue = 82910 }, New SalesRecord With { .Area = "北米", .City = "シカゴ", .Category = "家電製品", .Name = "Mi LED 40inch", .Revenue = 550010 }, New SalesRecord With { .Area = "北米", .City = "ミネソタ", .Category = "家電製品", .Name = "Mi LED 40inch", .Revenue = 1784702 }, New SalesRecord With { .Area = "南米", .City = "サンティアゴ", .Category = "家電製品", .Name = "Mi LED 40inch", .Revenue = 102905 }, New SalesRecord With { .Area = "北米", .City = "シカゴ", .Category = "家電製品", .Name = "Sennheiser HD 4.40-BT", .Revenue = 178100 }, New SalesRecord With { .Area = "南米", .City = "キト", .Category = "家電製品", .Name = "Sennheiser HD 4.40-BT", .Revenue = 234459 }, New SalesRecord With { .Area = "北米", .City = "ミネソタ", .Category = "携帯電話", .Name = "Iphone XR", .Revenue = 1734621 }, New SalesRecord With { .Area = "南米", .City = "サンティアゴ", .Category = "携帯電話", .Name = "Iphone XR", .Revenue = 109300 }, New SalesRecord With { .Area = "北米", .City = "シカゴ", .Category = "携帯電話", .Name = "OnePlus 7Pro", .Revenue = 499100 }, New SalesRecord With { .Area = "南米", .City = "キト", .Category = "携帯電話", .Name = "OnePlus 7Pro", .Revenue = 215000 }, New SalesRecord With { .Area = "北米", .City = "ミネソタ", .Category = "携帯電話", .Name = "Redmi 7", .Revenue = 81650 }, New SalesRecord With { .Area = "南米", .City = "キト", .Category = "携帯電話", .Name = "Redmi 7", .Revenue = 276390 }, New SalesRecord With { .Area = "北米", .City = "ミネソタ", .Category = "携帯電話", .Name = "Samsung S9", .Revenue = 896250 }, New SalesRecord With { .Area = "南米", .City = "ブエノスアイレス", .Category = "携帯電話", .Name = "Samsung S9", .Revenue = 896250 }, New SalesRecord With { .Area = "南米", .City = "キト", .Category = "携帯電話", .Name = "Samsung S9", .Revenue = 716520 } } #End Region Dim datasource = New SalesData With {.Sales = salesData} ' グローバル設定を追加 workbook.Names.Add("TemplateOptions.KeepLineSize", "true") ' データソースを追加 workbook.AddDataSource("ds", datasource) ' 帳票テンプレートにデータを連結して作成した帳票を、新しいワークブックのインスタンスとして返す Dim report As IWorkbook = workbook.GenerateReport() ' 帳票を xlsx としてストリームに保存 report.Save(outputStream) ' xlsx ストリームを閉じる outputStream.Close()