Jxl Analysis 2 http://www.emlog.net/fei

[<Excerpt> Fei: jxl analysis: http://www.emlog.net/fei]

(2) Applications

Before practice,
We need to have a general understanding of Excel, Excel
A file is composed of a workbook. A Workbook consists of a worksheet.
(Sheet), each worksheet consists of multiple cells
The workbook has its own characteristics,
The same is true for worksheets and cells.
Excel files for better use of jxl
Is Helpful. For details, see (1) API

In jxl
The approximate level is as follows:

Name	Attribute
Workbook	Workbooksettings
Sheet	Sheetsettings
Cell	Cellformat cellfeatures cellview celltype

Jxl read Excel:

The following methods are used to obtain a workbook:

Getworkbook (File file)

Getworkbook (File file, worksettings ws)

Getworkbook (inputstream is)

Getworkbook (inputstream is, worksettings ws)

There are two parameters:

The first parameter is required:
File or input stream,

The second parameter:
The workbook ws are some conventions for reading Excel.
Such as region and encoding.

If the file is incorrectly formatted
Biffexception or ioexception.

Code segment:

Workbook WB = NULL;

Try

{

WB = Workbook. getworkbook (excelfile );

WB. Close ();

}

Catch (biffexception ex ){

// Conversion error

} Catch (ioexception ex ){

// Io Error

}

If everything above is normal, you will get this
The Workbook of an Excel file. Next we can use this file book to get other things.
. The following are methods for getting a worksheet

Int getnumberofsheets ()//
Number of worksheets

Sheet getsheet (string name );//
Get the worksheet with the corresponding name

Sheet getsheet (INT index); // worksheet for which the serial number is obtained

Sheet [] getsheets (); // get the worksheet Array

String [] getsheetnames (); // get the worksheet name Array

The sample code is as follows:

Sheet sheet;

If (WB. getnumberofsheets ()> 0)

{

Sheet = WB. getsheet (0 );

}

Unless for special purpose, the following methods are not used much.

Range [] finaname (string name); // obtain the table area with this name

Cell findcellbyname (string name); // obtain
Cell

Cell getcell (string LOC)

String [] getrangenames ();

Related to whether the file is writable

Boolean isproected ();

When a worksheet of the workbook file is obtained.

The main content of a worksheet is cells.

// View the content

Cell findcell (pattern, int firstcol, int firstrow, int lastcol, int lastrow, Boolean reverse)

Cell findcell (string content)

Cell findcell (string content, OMT forstcol, int firstrow, int lastcol, int lastrow, Boolean reverse)

Labelcell findlabelcell (string contents)

// Obtain by location

Cell getcell (INT Col, int row)

Cell getcell (string LOC) // The format of LOC is
A3/B1/C3, which is the same

Getcell (cellreferencehelper. getcolumn (loc0, cellreferencehelper. getrow (LOC )),
Therefore, cell getcell (INT Col, int row) is faster than cell getcell.

// CELL ARRAY

Cell [] getrows (INT row) // array of cells in the current row

Cell [] getcols (INT col) // array of cells in the current column

// Special CELL ARRAY

// Hyperlink table

Hyperlink [] gethyperlinks (); // hyperlink Array

// Region table

Range [] getmergedcells (); // array of area Cells

// Image table

Int getnumberofimages () // number of images owned

Image getdrawing (INT index) // the image with the corresponding serial number

// Some attributes of a worksheet or multiple tables

String getname () // worksheet name

Int getrows () // number of rows

Int getcols () // Number of Columns

Sheetsettings getsettings () // worksheet settings

Cellview getcolumnview (INT col) // table view of this column

Cellview getrowview (INT row) // table view of this row

The following are cell-related operations:

Cellfeatures getcellfeatures () // some properties related to the table content, such as verification

Cellformat getcellformat () // It is mainly related to the external presentation format of the table.

Int getcolumn () // Column

Int getrow () // row

String getcontents (); // content

Boolean ishidden (); // visible No

Celltype GetType (); // If the table type is not determined, you can use this method to determine the table type.

The following describes how to write an Excel file.
File.

There are two types of writing files. One is to create a file, and the other is to update the file.

Create an Excel file

Creating an Excel file is simple:

// File

Writableworkbook createworkbook (File file )//
Create an Excel file without any form

Writableworkbook createworkbook (File file workbook in)

// Create
An Excel file whose initial content is the same as an in file
(This method can be used to update files)

Writeableworkbook createworkbook (File file, worksetting ws )//
Create a file with WS settings

Writableworkbook createworkbook (File file, workbook in, worksetting ws)

// Create
The in file is the same.

// Stream

Writableworkbook createworkbook (outputstream OS)

Writableworkbook createworkbook (outputstream OS workbook in)

Writeableworkbook createworkbook (outputstream OS, worksetting ws)

Writableworkbook createworkbook (outputstream OS, workbook in, worksetting ws)

Sample Code:

Writableworkbook WRB;

Writeableworkbook wrbin;

Try

{

WRB = Workbook. createworkbook (targetfile );

Wrbin = Workbook. createworkbook (targetfile, WB );

}

Catch (ioexception E)

{

// Io Error

}

When creating a workbook, we need to create a new worksheet.

Writablesheet createsheet (string name, int index );

Writablesheet WRS = WRB. createsheet ("Demo", 0 );

// Create a demo
Is the first worksheet in the workbook.

Next, we can perform other operations.

Add text cells:

Label Label = new label (0, 0 ,"");

WRB. addcell (Label );

// Add a data cell

Number = new number (1234 );

WRB. addcell (number );

// Add a Time Cell

Datetime dt = new datetime (0, 4, new date ());

WRB. addcell (DT );

// Add a formula Cell

Fornual formual = new formual (0, 11, "sum (A1: A9 )");

WRB. addcell (formual );

Add a hyperlink Cell

Wirtablehyperlink wrlink =

New writablehyperlink (0, 1, 1, new URL ("www.emlog.net/fei")," emlog ");

WRB. addhyperlink (wrlink );

// Add an image

Writableimage wrimage = new writableimage (1, 5, 10, 10, new file (imagefilepath ));

WRB. addimage (wrimage );

// Note,
The API indicates that only PNG files are supported. However, if I use images in other formats
PNG can also be placed in an Excel file. indeed, when viewing the specific source code, I found that the author only detected the file suffix. If no warning is given, I did not check the specific format of the image file.

Of course, the data in the table is the core part of the table.
The API also provides a large number of other methods to enrich the display of tables. Here, you can use it in the specific process.

// Finally, after writing the table, do not forget to perform the write operation, that is, to save it.

WRB. Write ()

WRB. Close ();
Closing the corresponding resources after saving is a qualified programmer.

Next, let's talk about another aspect of writing a file: updating a file. Sometimes, not only new files need to be written, but existing files may need to be updated. At this time, we can do this.

Workbook WB = Workbook. getworkbook (modifyfile );

Writableworkbook WRB = Workbook. createworkbook (modifyfile, WB );

The preceding figure shows the modifiable copy of an existing file.

// Obtain the first Worksheet

Writablesheet WRS = WRB. getsheet (0 );

// Obtain the A0 Cell

Cell cell = WRS. getcell (0, 0 );

// Determine the cell format

If (cell. GetType () = celltype. Label ){

Label Label = (Label) cell;

Label. setstring ("u r modified .");

} // Modify end

// Save Excel

WRB. Write ();

WB. Close ();

WRB. Close ();

The above is a simple modification of the Excel file.
The API also provides operations such as copying a worksheet and copying a cell. You can query
To understand its specific usage. We can get a lot of examples of how to use the API from the package provided by the author. Here not ugly. jxl Official Website: http://www.andykhan.com/jexcelapi/index.html