ExcelWriter allows you to generate a spreadsheet from script alone - using the ExcelApplication object - or from a template spreadsheet and a script, using ExcelTemplate. You can also use ExcelApplication and ExcelTemplate together. This section shows you how to generate a template spreadsheet with ExcelApplication and pass the spreadsheet to ExcelTemplate which then populates the template's data markers.
ExcelApplication xla = new ExcelApplication(); Workbook wb = xla.Create(); Worksheet ws = wb.Worksheets[0]; for (int i = 0; i < colCount; i++) { //--- Form the current data marker. //--- Data markers are in this format: //--- %%=[dsrcName].[colName] string curMarker = String.Format("%%={0}.{1}", dsrcName, selectedCols[i].ToString()); ws[firstRow, firstCol + i].Value = curMarker; } |
%%=DataSource.EmployeeID(fieldname) |
ExcelTemplate xlt = new ExcelTemplate(); xlt.Open(xla, wb); |
private DataTable GetEmployeeDataTable() { string EmployeeSQL = "SELECT TOP 10 FirstName + ' ' + LastName As Name, " + "Title FROM Employee"; DataTable dt = new DataTable(); using(SqlConnection conn = new SqlConnection(connString)) new SqlDataAdapter(EmployeeSQL, conn).Fill(dt); return dt; } |
xlt.SetDataSource(dt, dsrcName); xlt.Process(); |
xlt.Save(Page.Response, "Report.xls", false); |
using SoftArtisans.OfficeWriter.ExcelWriter; class AppToTemplate : System.Web.UI.Page { protected void Page_Load(object sender, System.EventArgs e) { //--- Create ExcelApplication, a Workbook, and a Worksheet ExcelApplication xla = new ExcelApplication(); Workbook wb = xla.Create(); Worksheet ws = wb.Worksheets[0]; //--- Write ExcelTemplate data markers into the workbook ws.Cells[0, 0].Value = "%%=DSrcName.#1(fieldname)"; ws.Cells[0, 0].Style.Font.Bold = true; ws.Cells[1, 0].Value = "%%=DSrcName.#1"; ws.Cells[0, 1].Value = "%%=DSrcName.#2(fieldname)"; ws.Cells[0, 1].Style.Font.Bold = true; ws.Cells[1, 1].Value = "%%=DSrcName.#2"; //--- Open the Workbook as ExcelTemplate ExcelTemplate xlt = new ExcelTemplate(); xlt.Open(xla, wb); //--- Bind data using ExcelTemplate SetDataSource string[,] values = {{"New York", "NY"}, {"Miami", "FL"}, {"Boston", "MA"}}; string[] names = {"City", "State"}; xlt.SetDataSource(values, names, "DSrcName"); xlt.Process(); xlt.Save(Page.Response, "AppToTemplate.xls", false); } } |