...
Code Block |
---|
using SoftArtisans.OfficeWriter.ExcelWriter; ... //Declare Template objects globaly ExcelTemplate XLTTo; ExcelTemplate XLTFrom; protected void RunReport() { //Get the populated files from BindTemplateData XLTTo = BindTemplateData("//templates//Part1_Financial_Template.xlsx"); XLTFrom = BindTemplateData("//templates//Part2_Financial_Template.xlsx"); //Join the two reports using CopySheet JoinReports(); } protected ExcelTemplate BindTemplateData(string filename) { ExcelTemplate XLT = new ExcelTemplate(); //Open the template file XLT.Open(Page.MapPath(filename)); //Create data binding properties for ExcelTemplate DataBindingProperties bindingProps = XLT.CreateDataBindingProperties(); //Get the datatables to be bound into the template. //This example uses CSV data, but the data can come from //anywhere DataTable dtAssets = GetCSVData("//data//Assets.csv"); DataTable dtLosses = GetCSVData("//data//Losses.csv"); DataTable dtOther = GetCSVData("//data//Other.csv"); //Bind the datatables XLT.BindData(dtAssets, "Assets", bindingProps); XLT.BindData(dtLosses, "Losses", bindingProps); XLT.BindData(dtOther, "Other", bindingProps); /*This section only applies to the first template. It will be ignored by the second template, since there are no markers to bind to. /*/ //Create the array of header values. This example only binds a single item string[] headerValues = { "2011" }; //Create the array of header names. string[] headerNames = { "FiscalYear" }; //Bind the header row data XLT.BindRowData(headerValues, headerNames, "Header", bindingProps); /*end*/ //Process to import the data to the file XLT.Process(); //Return the populated file return XLT; } protected void JoinReports() { //Instantiate ExcelApplication for the workbook being copied to ExcelApplication XLATo = new ExcelApplication(); //Open the populated template Workbook wbTo = XLATo.Open(XLTTo); //Open the first worksheet Worksheet wsTo = wbTo.Worksheets[0]; //Call AutoFit to set the height and width wsTo.PopulatedCells.AutoFitWidth(); wsTo.PopulatedCells.AutoFitHeight(); //Instantiate ExcelApplication for the workbook being copied from ExcelApplication XLAFrom = new ExcelApplication(); //Open the populated template Workbook wbFrom = XLAFrom.Open(XLTFrom); //Open the first worksheet, with the table Worksheet wsFrom = wbFrom[0]; //Open the second worksheet, with the data Worksheet wsFromData = wbFrom[1]; //Call CopySheet, making sure to copy the data sheet first wbTo.Worksheets.CopySheet(wsFromData, 1, "Data"); wbTo.Worksheets.CopySheet(wsFrom, 1, "Percent by Quarter"); //Save the file XLATo.Save(wbTo, Page.Response, "Part3_Output.xlsx", false); } |
...