Formatting a Spreadsheet Based on A Designer Sheet


#1

Hi

I’m currently evaluating Aspose.Excel as well as SoftArtisans and Speedgen components.

With Aspose.Excel is it possible to load a designer spreadsheet to base your finished spreadsheet on, but add additional formatting to it at runtime.

I have a designer I want to use, but the datasource used at runtime has a variable amount of columns, hence I need to format a range of cells as headings at runtime.

Below is the sample code I am using. The data displays fine, but the formatting will not work.

// Open designer file

ExcelDesigner excel = new ExcelDesigner();

excel.Open(Server.MapPath(“ExampleTemplateAspose.xls”));

// Replace simple variables

excel.SetDataSource(“ReportTitle”,reportTitle);

excel.SetDataSource(“PubDate”,pubDate);

excel.SetDataSource(“TableTitle”,tableTitle);

excel.SetDataSource(“DataSource”,dataSource);

// Set data table as a data source. Number of columns is unknown at design time.

excel.SetDataSource(dt);

// Format sheet

Excel excelApp = excel.Excel;

int styleIndex = excelApp.Styles.Add();

Aspose.Excel.Style shaded = excelApp.Styles[styleIndex];

shaded.Font.IsBold = true;

shaded.Font.Color = Color.White;

shaded.BackgroundColor = Color.DarkBlue;

Aspose.Excel.Range r = excelApp.Worksheets[0].Cells.CreateRange(7,1,7,cols);

r.Style = shaded;

excel.Process();

excel.Save(“result.xls”, SaveType.OpenInBrowser, FileFormatType.Default, this.Response);

Any help will be gratefully received.

Regards
Mike Cook


#2

Sure it’s possible.

Have you tried to put the style setting code after processing smart markers?

// Open designer file

ExcelDesigner excel = new ExcelDesigner();

excel.Open(Server.MapPath(“ExampleTemplateAspose.xls”));

// Replace simple variables

excel.SetDataSource(“ReportTitle”,reportTitle);

excel.SetDataSource(“PubDate”,pubDate);

excel.SetDataSource(“TableTitle”,tableTitle);

excel.SetDataSource(“DataSource”,dataSource);

// Set data table as a data source. Number of columns is unknown at design time.

excel.SetDataSource(dt);

excel.Process();

// Format sheet

Excel excelApp = excel.Excel;

int styleIndex = excelApp.Styles.Add();

Aspose.Excel.Style shaded = excelApp.Styles[styleIndex];

shaded.Font.IsBold = true;

shaded.Font.Color = Color.White;

shaded.BackgroundColor = Color.DarkBlue;

Aspose.Excel.Range r = excelApp.Worksheets[0].Cells.CreateRange(7,1,7,cols);

r.Style = shaded;

excel.Save(“result.xls”, SaveType.OpenInBrowser, FileFormatType.Default, this.Response);