Syncfusions Essential XlsIO

Vertex42 The Excel Nexus

Professional Excel Templates

Get Instant Access

Syncfusion's (www.syncfusion.com) Essential XlsIO is written completely in .NET code (C#) and optionally comes with the source code. This product offers PageSetup objects, row height settings, page breaks, document property settings, data validation, conditional formatting, comments, rich text, autofilters, charts, hyperlinks, PivotTables, and images. Essential XlsIO offers everything but macros, but it preserves them if they're present in template spreadsheets. Listing 7-9 shows how to create a simple spreadsheet.

Listing 7-9. Setting Up Syncfusion Essential XlsIO Code

//Instantiate the spreadsheet creation engine oExcelEngine = new ExcelEngine(); //Create a workbook oWorkBook = oExcelEngine.Excel.Workbooks.Create(); //Reference the first worksheet oWS = oWorkBook.Worksheets[0];

//Set orientation and paper size oWS.PageSetup.Orientation = ExcelPageOrientation.Portrait; oWS.PageSetup.PaperSize = ExcelPaperSize.PaperLetter;

//Set margins oWS.PageSetup.LeftMargin = 0.25; oWS.PageSetup.RightMargin = 0.25; oWS.PageSetup.TopMargin = 1.25; oWS.PageSetup.BottomMargin = 1.0;

//Set the first row to print at the top of every page oWS.PageSetup.PrintTitleRows = "$A$1:$IV$1";

//Set header and footer text oWS.PageSetup.LeftFooter = "Page &P of &N\n&D &T"; oWS.PageSetup.CenterHeader = "Sample Report";

//Set column widths oWS.SetColumnWidth(1, 20); oWS.SetColumnWidth(2, 10);

//Set workbook's summary and custom document properties oWorkBook.BuiltInDocumentProperties.Author = "Essential Essential XlsIO"; oWorkBook.CustomDocumentProperties["Today"].DateTime = DateTime.Today;

You need to instantiate the spreadsheet-creation engine first and then reference the existing workbook and worksheet as you would in Excel. The PageSetup object encapsulates the same settings as its Excel counterpart, and Essential XlsIO provides its own enumerated values to refer to such properties as Orientation and PaperSize. Headers and footers are set as you expect. Finally, you set the workbook's summary and custom document properties. Listing 7-10 shows how the cells are filled with data from the data source.

Listing 7-10. Populating the Cells

//Set column headers oWS.Range[sRow, 1].Text = "Product"; oWS.Range[sRow, 2].Text = "Sales";

//Display headers in bold, centered, with a yellow background oWS.Range[sRow, 1, sRow, 2].CellStyle.Color = Color.Yellow; oWS.Range[sRow, 1, sRow, 2].CellStyle.HorizontalAlignment = ExcelHAlign.HAlignCenter;

oWS.Range[sRow, 1, sRow, 2].CellStyle.Font.Bold = true; sRow++;

//Get sample data as a DataTable object, move through the results, //and write data to cells oDT = GetData();

foreach(DataRow oDR in oDT.Rows) {

oWS.Range[sRow, 1].Text = oDR["Product"].ToString(); oWS.Range[sRow, 2].Value = oDR["Sales"].ToString(); sRow++;

//Display total line via formula in bold oWS.Range[sRow, 1].Text = "Grand Total";

oWS.Range[sRow, 2].Formula = "SUM(B2:B" + (sRow - 1).ToString() + ")"; oWS.Range[sRow, 1, sRow, 2].CellStyle.Font.Bold = true;

//Format Sales column oWS.Range[2, 2, sRow, 2].NumberFormat = "0.00"; oWorkBook.SaveAs(@"c:\temp\sample.xls"); oWorkBook.Close();

Rather than refer to individual cells, Essential XlsIO refers to ranges. As in Excel, you can use range references to apply data formats and cell attributes like color and font. You can even use ranges to assign values. The last printed page of the spreadsheet produced by this code is shown in Figure 7-3.

Figure 7-3. Spreadsheet output from Essential XXlsIO

You can also use several helper methods like the worksheet object's ImportDataTable() method that make importing ADO.NET data sources like Array, DataTable, DataColumn, and DataView easier than iterating through the data source.

In addition to creating Excel reports purely using code, it's also possible to use existing spreadsheets as templates for report generation. Often, it's easier to design the look and feel of the report using the Excel GUI and then use XlsIO to dynamically fill data during runtime. Using this approach, you can also embed elements like macros in your report that Essential XlsIO doesn't directly support but retains on resaving if they're present in the template spreadsheet. Listing 7-11 shows how to generate a report based on a template spreadsheet.

Listing 7-11. Report Based on Spreadsheet Templates

//Instantiate the spreadsheet creation engine oExcelEngine = new ExcelEngine();

//Create a workbook oWorkBook = oExcelEngine.Excel.Workbooks.

Open(@"..\..\Data\Template.xls", ExcelOpenType.Automatic);

//Reference the first worksheet oWS = oWorkBook.Worksheets[0];

//Import DataTable into worksheet oWS.ImportDataTable(oDT,false,2,1,-1,-1,false);

//Display total line via formula in bold oWS.Range[oDT.Rows.Count+1, 1].Text = "Grand Total"; oWS.Range[oDT.Rows.Count+1, 2].Formula =

"SUM(B2:B" + (oDT.Rows.Count - 1).ToString() + ")"; oWS.Range[oDT.Rows.Count+1, 1, oDT.Rows.Count+1, 2].CellStyle.Font.Bold = true;

After the workbook is loaded into memory using the Open() method, all the elements in the workbook become accessible through the Essential XlsIO API. This makes it possible to read data from existing spreadsheets that users may upload back to the server after making modifications on their local machines. It's also possible to read and write the spreadsheets to streams, which can be convenient at times. No temporary files are generated on the server when saving to a stream. It only requires mediumtrust security settings so the stream an be deployed on shared hosting environments and cloud computing environments like Windows Azure.

Essential XlsIO also has support for another variant of the template-based approach for report generation, whereby the end user can design the template and place special markers in it that are replaced with data dynamically during runtime.

In addition, Essential XlsIO can read and write SpreadsheetML files, which are Excel-compatible XML files; XLSX files, which are the default file format for Excel 2007; and CSV files; in addition to the default Excel (97-2003) binary format.

Was this article helpful?

+1 0

Responses

Post a comment