OfficeWriter Documentation
.
ExcelWriter v8 Docs
Space Shortcuts
Page tree
ConfigureSpace tools

Versions Compared

Old Version 8

changes.mady.by.user Alison Bird [softartisans]

Saved on

compared with

New Version Current

changes.mady.by.user Gabe Goodwin [softartisans]

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

Table of Contents

Table of Contents
maxLevel1

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.

Diving right into the code

...

Info

In the _Hello World_ sample

...

web application, ExcelApplication_HelloWorld.aspx

...

has a text box for users to supply a value and ExcelApplication_HelloWorld.aspx.

...

cs/vb contains the code shown below.

 

  1. Include the SoftArtisans.OfficeWriter.ExcelWriter namespace in the code behind.

...

  1. Code Block

...

  1. language

...

  1. c#

...

  1. using SoftArtisans.OfficeWriter.ExcelWriter;

...

...
  1.  Code Block
...
  1. language
...
  1. vb
    Imports SoftArtisans.OfficeWriter.ExcelWriter
...


  2. Instantiate the ExcelApplication
...
  1.  object
...
  1.  Code Block
...
  1. language
...
  1. c#
...
  1. ExcelApplication 
...
  1. XLAPP = new ExcelApplication();
...
...
  1.  Code Block
...
  1. language
...
  1. vb
    Dim
...
  1.  XLAPP As New ExcelApplication()
    Unlike the ExcelTemplate object, which represents a single file, the ExcelApplication works as a file generation engine. The ExcelApplication
...
  1.  object can be used to create, open, and save multiple workbooks.
...
  1.  
     
  2. Create a new workbook 
...
  1. with ExcelApplication.Create(FileFormat)
...
  1.  Code Block
...
  1. language
...
  1. c#
...
  1. Workbook 
...
  1. WB = 
...
  1. XLAPP.Create(ExcelApplication.FileFormat.
...
  1. Xlsx);
...
...
  1.  Code Block
...
  1. language
...
  1. vb
    Dim
...
  1.  WB As Workbook =
...
  1.  XLAPP.Create(ExcelApplication.FileFormat.
...
  1. Xlsx)
     
    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(FileFormat) automatically creates the first worksheet in the workbook for you.
...

  1.  
  2. Access the first worksheet through the Workbook.Worksheets collection
...
  1.  Code Block
...
  1. language
...
  1. c#
...
  1. Worksheet 
...
  1. WKST = 
...
  1. WB.Worksheets[0];
...
...
  1.  Code Block
...
  1. language
...
  1. vb
    Dim
...
  1.  WKST As Worksheet =
...
  1.  WB.Worksheets(0)
    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.
...
  1.  
     
  2. Write the value from the web form into a cell
...
  1.  Code Block
...
  1. language
...
  1. c#
...
  1. string value = DataValueBox.Text.Trim();

...
  1. WKST.Cells[0,0].Value = value;
...
...
  1.  Code Block
...
  1. language
...
  1. vb
    Dim value As String = DataValueBox.Text.Trim()
...
  1. 
WKST.Cells(0, 0).Value = value
    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".
...
  1.  
     
  2. Save the workbook
...
  1.  Code Block
...
  1. language
...
  1. c#
...
  1. XLAPP.Save(
...
  1. WB, Page.Response, "Output.xlsx", false);
...
...
  1.  Code Block
...
  1. language
...
  1. vb
...
  1. XLAPP.Save(
...
  1. WB, Page.Response, "Output.xlsx", False)
    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. 
...
  1.  Remember that the file extension must match the file format specified when the workbook was created.
...
  1.  
     
  2. Run the code. In the output file, you will see that the custom text from the form has been inserted into cell "A1". 
    Image Modified 
Final Code
 Csharpcode
7
language7c#


using SoftArtisans.OfficeWriter.ExcelWriter;
...
ExcelApplication oXLAPPXLAPP = new ExcelApplication();
ExcelApplicationWorkbook oXLAPPWB = new ExcelApplication(XLAPP.Create(ExcelApplication.FileFormat.Xlsx);
Worksheet oWKSTWKST = oWBWB.Worksheets[0];
string value = DataValueBox.Text.Trim();
oWKSTWKST.Cells[0,0].Value = value;
oXLAPPXLAPP.Save(oWBWB, Page.Response, "Output.xlsx", false);


 Vbnetcode
8
language8vb
Include SoftArtisans.OfficeWriter.ExcelWriter


...


Dim
 oXLAPP 
 XLAPP As New ExcelApplication()


Dim
 oWB 
 WB As Workbook =
 oXLAPP
 XLAPP.Create(ExcelApplication.FileFormat.
Excel2007
Xlsx)


Dim
 oWKST 
 WKST As Worksheet =
 oWB
 WB.Worksheets(0)


Dim value As String = DataValueBox.Text.Trim()

 oWKST

WKST.Cells(0, 0).Value = value

 oXLAPP

XLAPP.Save(
oWB
WB, Page.Response, "Output.xlsx", False)
Downloads
You can download the code for the _Hello World_ tutorial as a Visual Studio solution.