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);
}