static void Main(string[] args) { var workbook = new Workbook(); workbook.Open("Template.xlsx"); //workbook.Worksheets[0].Range["B5"].Value = "Hello, DioDocs!"; //workbook.AddDataSource("CompanyName", "Hello, DioDocs!"); //var invoice = new Invoice {CompanyName = "Hello, DioDocs!"}; //workbook.AddDataSource("Invoice", invoice); var connectionStringBuilder = new SqlConnectionStringBuilder { DataSource = "localhost", UserID = "sa", Password = "******" }; using var connection = new SqlConnection(connectionStringBuilder.ToString()); connection.Open(); using var command = new SqlCommand( File.ReadAllText("SelectInvoices.sql"), connection); using var dataTable = new DataTable(); dataTable.Load(command.ExecuteReader()); workbook.AddDataSource("Invoice", dataTable); //Init template global settings workbook.Names.Add("TemplateOptions.InsertMode", "EntireRowColumn"); workbook.Names.Add("TemplateOptions.KeepLineSize", "true"); workbook.ProcessTemplate(); workbook.Save("Invoice.pdf", SaveFileFormat.Pdf); workbook.Save("Invoice.xlsx", SaveFileFormat.Xlsx); }
public FileResult ExportExcel3(string fileName = "") { Workbook workbook = new Workbook(); #region Init Data var datasource = new System.Data.DataTable(); datasource.Columns.Add(new DataColumn("ID", typeof(Int32))); datasource.Columns.Add(new DataColumn("Name", typeof(string))); datasource.Columns.Add(new DataColumn("Score", typeof(Int32))); datasource.Columns.Add(new DataColumn("Team", typeof(string))); datasource.Rows.Add(10, "Bob", 12, "Xi'An"); datasource.Rows.Add(11, "Tommy", 6, "Xi'An"); datasource.Rows.Add(12, "Jaguar", 15, "Xi'An"); datasource.Rows.Add(2, "Phillip", 9, "BeiJing"); datasource.Rows.Add(3, "Hunter", 10, "BeiJing"); datasource.Rows.Add(4, "Hellen", 8, "BeiJing"); datasource.Rows.Add(5, "Jim", 9, "BeiJing"); #endregion IWorksheet worksheet = workbook.Worksheets[0]; workbook.AddDataSource("ds", datasource); MemoryStream stream = new MemoryStream(); workbook.Save(stream); stream.Seek(0, SeekOrigin.Begin); byte[] bytes = new byte[stream.Length]; stream.Read(bytes, 0, bytes.Length); var contentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"; var donwloadFile = string.Format("attachment;filename={0}.xlsx;", string.IsNullOrEmpty(fileName) ? Guid.NewGuid().ToString() : WebUtility.UrlEncode(fileName)); return(File(bytes, contentType, donwloadFile)); }
static void Main(string[] args) { //Console.WriteLine("Hello DioDocs!"); // データソース(JSON) // var jsonString = File.ReadAllText("Data/testdata_multicustomer.json"); // var data = JsonSerializer.Deserialize<Data>(jsonString); // データソース(カスタムオブジェクト) var data = new Data { publisher = new Publisher { companyname = "ディオドック株式会社", postalcode = "981-2222", address1 = "M県S市紅葉区", address2 = "杜王町2-6-11", tel = "022-777-8210", bankname = "むかでや銀行", bankblanch = "杜王町支店", account = "123-456789", representative = "葡萄 城太郎" }, customer = new Customer[] { new Customer { companyname = "財団法人スピードワゴン", name = "虎猿出井 富似雄", postalcode = "981-9999", address1 = "M県S市広瀬区", address2 = "花京院3-1-4", tel = "022-987-2220", detail = new Detail[] { new Detail { sku = "0105231", name = "モッツァレッラチーズとトマトのサラダ", price = 1200, unit = 70, remark = "前菜" }, new Detail { sku = "0201071", name = "娼婦風スパゲティー", price = 2500, unit = 100, remark = "パスタ" }, new Detail { sku = "0301201", name = "小羊背肉のリンゴソースかけ", price = 5000, unit = 130, remark = "メイン" }, new Detail { sku = "0401011", name = "ごま蜜団子", price = 800, unit = 60, remark = "デザート" } } }, new Customer { companyname = "杜王グランドホテル", name = "江陽 太郎", postalcode = "982-1234", address1 = "M県S市秋保区", address2 = "上愛子6-4-7", tel = "022-876-1119", detail = new Detail[] { new Detail { sku = "0202061", name = "激辛麻婆焼きそば", price = 1000, unit = 50, remark = "麺" }, new Detail { sku = "0302081", name = "塩竈マグロ", price = 2300, unit = 80, remark = "魚" }, new Detail { sku = "0402301", name = "超極厚牛タン", price = 4800, unit = 110, remark = "肉" }, new Detail { sku = "0502021", name = "濃厚ずんだロール", price = 800, unit = 60, remark = "デザート" }, new Detail { sku = "0602271", name = "ハチミツパンケーキ", price = 1300, unit = 60, remark = "お持ち帰り" } } }, new Customer { companyname = "東方フルーツパーラー", name = "東方 花子", postalcode = "983-5678", address1 = "M県S市荒井区", address2 = "深沼9-7-1", tel = "022-765-0008", detail = new Detail[] { new Detail { sku = "0303071", name = "マスクメロン", price = 1100, unit = 60, remark = "フルーツ" }, new Detail { sku = "0403091", name = "シャインマスカット", price = 2400, unit = 90, remark = "フルーツ" }, new Detail { sku = "0503401", name = "ピオーネ", price = 4900, unit = 120, remark = "フルーツ" }, new Detail { sku = "0603031", name = "ラ・フランス", price = 700, unit = 50, remark = "フルーツ" }, new Detail { sku = "0703381", name = "富有柿", price = 1400, unit = 70, remark = "フルーツ" }, new Detail { sku = "0501161", name = "サンジェルマンのサンドイッチバッグ", price = 1500, unit = 80, remark = "お持ち帰り" } } } } }; // ライセンスキー // string key = "hogehoge"; // Workbook.SetLicenseKey(key); // 新しいワークブックを生成 var workbook = new Workbook(); // テンプレートを読み込む // workbook.Open("Templates/SimpleInvoiceJP_可変明細_単一シート.xlsx"); // workbook.Open("Templates/SimpleInvoiceJP_可変明細_複数シート.xlsx"); // workbook.Open("Templates/SimpleInvoiceJP_可変明細_複数シート_数式維持.xlsx"); // workbook.Open("Templates/SimpleInvoiceJP_固定明細_単一シート.xlsx"); // workbook.Open("Templates/SimpleInvoiceJP_固定明細_複数シート.xlsx"); // workbook.Open("Templates/SimpleInvoiceJP_固定明細_複数シート_数式維持.xlsx"); workbook.Open("Templates/SimpleInvoiceJP_固定明細_複数シート_数式維持_備考フォーム追加.xlsx"); // データソースを追加 workbook.AddDataSource("ds", data); // テンプレート処理を呼び出し workbook.ProcessTemplate(); // Excelファイルに保存 workbook.Save("result.xlsx"); // PDFファイルに保存 workbook.Save("result.pdf", SaveFileFormat.Pdf); }