.
| Wiki Markup |
|---|
{div:class=kate-style}
ExcelWriter offers two approaches to generating, processing and manipulating Excel documents: ExcelTemplate and ExcelApplication. This tutorial will walk you through assigning the contents of a cell using ExcelTemplate and ExcelApplication.
[Download the Hello World sample code]
{info}You will need ExcelWriter 8 installed in order to run the _Hello World_ sample.
* If you haven't purchased ExcelWriter, you can download an evaluation version [here].
* If you need to install ExcelWriter, please visit the [install page| Docs:Installing OfficeWriter] for instructions.{info}
h2. Getting started with ExcelWriter in .NET
After ExcelWriter is installed, the next step is to create a .NET project and add a reference to the ExcelWriter library. These steps have already been done for you in the _Hello World_ sample.
# Open Visual Studio and create a .NET project (this tutorial uses a web application).
# Add a reference to SoftArtisans.OfficeWriter.ExcelWriter.dll
#* SoftArtisans.OfficeWriter.ExcelWriter.dll is located under *Program Files > SoftArtisans > OfficeWriter > bin*
h2. _Hello World_ with ExcelTemplate
ExcelWriter's ExcelTemplate approach allows you to write data to a template file that contains [data markers]. The data markers tell ExcelWriter where to bind specific sets of data. This tutorial will show you the basics on how to dynamically insert data into a worksheet using ExcelTemplate by taking custom text from a web form textbox and inserting it into a template file.
h4. Setting up the template file
The template file for _Hello World_ can be found under *\templates\Hello World.xlsx*.
We are going to bind a single string value to a cell in a template file. To do this, we will first need to add a data marker to the cell where we want the value to appear.
We've already added the single data marker '%%=$DataValue' to the *Hello World.xlsx* template file. All ExcelWriter data markers are prefaced with '%%=' and the additional '$' sign means that the data source for this data marker is 1-dimensional (e.g. 1-dimensional array or single value). 'DataValue' is the data marker ID we'll use to bind the data to this data marker.
SCREEN SHOT
Now the template file is done. Next is writing the code to bind the string value to the data marker.
h4. Writing the code
This section refers to the code-behind for the ExcelTemplate _Hello World_ sample: *ExcelTemplate_HelloWorld.aspx.\[cs/vb\]*. The corresponding web form is *ExcelTemplate_HelloWorld.aspx*.
1. Include the {{SoftArtisans.OfficeWriter.ExcelWriter}} namespace in the code behind.
{csharp:1}
using SoftArtisans.OfficeWriter.ExcelWriter;
{csharp}
{vbnet}
Imports SoftArtisans.OfficeWriter.ExcelWriter
{vbnet}
2. Instantiate the {{[ExcelTemplate]}} object.
{csharp:2}
ExcelTemplate oXLT = new ExcelTemplate();
{csharp}
{vbnet}
{vbnet}
3. Open the template file.
The {{ExcelTemplate}} object corresponds to a single template file, so a given {{ExcelTemplate}} instance can only have one template file open.
{csharp:3}
oXLT.Open(Page.MapPath("Hello World.xlsx"));
{csharp}
{vbnet}
{vbnet}
4. Create a {{[DataBindingProperties]}} object
{csharp:4}
DataBindingProperties oDataProps = oXLT.CreateDataBindingProperties();
{csharp}
{vbnet}
{vbnet}
The {{DataBindingProperties}} object can be used to change the behavior of how data is imported. For example, if we were importing multiple rows of data, we can use the {{[DataBindingProperties.MaxRows]}} property to limit the number of rows that are imported. In this sample, we won't be changing any of the import properties, but we still need the {{DataBindingProperties}} object to bind data.
5. Get the data and call {{[ExcelTemplate.BindCellData()]}} to bind the data to the data marker
{csharp:5}
string value = DataValueBox.Text.Trim();
oXLT.BindCellData(value, "DataValue", oDataProps);
{csharp}
{vbnet}
{vbnet}
In this sample, we're pulling the single value from the text box on the web form.
Since we're binding a single value, we use {{BindCellData()}} and specify the data marker ID. Note that we need to pass the {{DataBindingProperties}} object, even though none of the {{DataBindingProperties}} are active.
6. Call {{[ExcelTemplate.Process()]}} to insert the data into the file
{csharp:6}
oXLT.Process();
{csharp}
{vbnet}
{vbnet}
{{ExcelTemplate.Process()}} handles everything relating to inserting the data into the file. If we were importing multiple rows of data, {{Process()}} would handle inserting the new physical rows into the Excel worksheet.
7. Save the output
{csharp:7}
oXLT.Save(Page.Response, "Output.xlsx", false);
{csharp}
{vbnet}
{vbnet}
There are several options for {{[ExcelApplicationExcelTemplate.Save()]}} including: save to disk, save to memory stream, stream back to the client inline, and stream back to the client as an attachment. In this case, we're streaming the workbook back to the client as an attachment.
Also, ExcelWriter does not convert between file formats, so it is important that the file extension on the output file matches the file extension of the original template file.
8. Go to the web form page, *ExcelTemplate_HelloWorld.aspx*, to try out the sample. In the output file, you will see that the data marker has been replaced with the custom text entered in the form.
SCREENSHOT
Congratulations, you have completed _Hello World_ for ExcelTemplate!
h2. _Hello World_ with ExcelApplication
ExcelWriter's ExcelApplication approach provides you with full programmatic control over the Excel file formats (XLS, XLSX, XLSM). This includes the ability to create and modify: charts, formulas, formatting, data validation, conditional formatting, worksheet protection, images and more! This tutorial will show you how to create a new workbook and write a value to a cell.
h5. Diving right into the code
This section refers to the code-behind for the ExcelApplication _Hello World_ sample: *ExcelApplication_HelloWorld.aspx.\[cs/vb\]*. The corresponding web form is *ExcelApplication_HelloWorld.aspx*.
1. Include the {{SoftArtisans.OfficeWriter.ExcelWriter}} namespace in the code behind.
{csharp:8}
using SoftArtisans.OfficeWriter.ExcelWriter;
{csharp}
{vbnet}
Imports SoftArtisans.OfficeWriter.ExcelWriter
{vbnet}
2. Instantiate the {{[ExcelApplication]}} object
{csharp:9}
ExcelApplication oXLAPP = new ExcelApplication();
{csharp}
{vbnet}
{vbnet}
Unlike the {{[ExcelTemplate]}} object, which represents a single file, the {{ExcelApplication}} works as a file generation engine. The {{ExcelApplication}} object can be used to create, open, and save multiple workbooks.
3. Create a new workbook with {{[ExcelApplication.Create()]}}
{csharp:10}
Workbook oWB = oXLAPP.Create(ExcelApplication.FileFormat.Excel2007);
{csharp}
{vbnet}
{vbnet}
ExcelWriter has the ability to create Excel 2003 (XLS) files and Excel 2007 (XLSX) files, but cannot convert between formats. The file format must be declared when the workbook is created and the file extension of the output file must match when the file is saved.
{{ExcelApplication.Create()}} automatically creates the first worksheet in the workbook for you.
4. Access the first worksheet through the {{[Workbook.Worksheets]}} collection
{csharp:11}
Worksheet oWKST = oWB.Worksheets[0];
{csharp}
{vbnet}
{vbnet}
You can access worksheets by name (e.g. "Sheet1") or by index (shown above), but ExcelWriter will throw an exception if you attempt to access a worksheet that does not exist.
5. Write the value from the web form into a cell
{csharp:12}
string value = DataValueBox.Text.Trim();
oWKST.Cells[0,0].Value = value;
{csharp}
{vbnet}
{vbnet}
It is important to note that ExcelWriter indices are all 0-indexed, unlike Excel indices, which are 1-indexed. This is importing when working with cells, rows, columns, and worksheet positions in ExcelWriter. You can also reference cells by name; in this case, it would be "A1".
6. Save the workbook
{csharp:13}
oXLAPP.Save(oWB, Page.Response, "Output.xlsx", false);
{csharp}
{vbnet}
{vbnet}
{{[ExcelApplication.Save()]}} has the same output options as {{ExcelTemplate}}: save to disk, save to memory stream, stream back to the client inline, and stream back to the client as an attachment. In this case, we're streaming the workbook back to the client as an attachment.
Remember that the file extension must match the file format specified when the workbook was created.
7. Go to the web form page, *ExcelApplication_HelloWorld.aspx*, to try out the sample. In the output file, you will see that the custom text from the form has been inserted into cell "A1".
SCREEN SHOT
h2. Next Steps
*[Tutorials]*
{excerpt-include:Tutorials|nopanel=true}
*[API Reference]*
{excerpt-include:Programmer's API Reference|nopanel=true}
*[Help]*
{excerpt-include:Help|nopanel=true}
{div}
|