Shulou(Shulou.com)06/02 Report--

This article is about how to use JAVA to manipulate EXCEL files. The editor thinks it is very practical, so share it with you as a reference and follow the editor to have a look.

Using JAVA to operate EXCEL File

Friends who use Windows operating system must be familiar with Excel (spreadsheet), but they should use Java language to manipulate Excel files.

It's not an easy thing. Today, with the increasing popularity of Web applications, the demand for manipulating Excel files through Web is becoming stronger and stronger.

The popular operation is to create a CSV (comma separated values) file in JSP or Servlet and change the file to

The MIME,text/csv type is returned to the browser, which then calls Excel and displays the CSV file. This just means that it can be accessed.

Excel file, but can not really manipulate Excel file, this article will give you a surprise, introduce you to an open source project

Java Excel API, you can easily manipulate Excel files with it.

Introduction to JAVA EXCEL API

Java Excel is an open source project through which Java developers can read the contents of Excel files, create new Excel files, and more

A new existing Excel file. The API non-Windows operating system can also be used to process Excel data tables through pure Java applications. Because

It is written in Java, so we can call API to access the Excel data table through JSP and Servlet in the Web application.

The stable version now released is V2.0, which provides the following features:

Read data from files in Excel 95, 97, 2000, etc.

Read Excel formula (you can read formulas after Excel 97)

Generate Excel data table (format: Excel 97)

Support formatting of fonts, numbers and dates

Support cell shadow operation and color operation

Modify an existing data table

The following features are not supported yet, but will be available soon:

Unable to read chart information

Can read, but can not generate a formula, any type of formula the final calculated value can be read out

Application example

1 read the data table from the Excel file

Java Excel API can read Excel data tables from either a file (.xls) on the local file system or from the input stream. Read

The first step in an Excel datasheet is to create a Workbook (terminology: workbook). The following code snippet illustrates how to do this: (complete

For the code, see ExcelReading.java)

Import java.io.*

Import jxl.*

... ... ... ...

Try

{

/ / build Workbook object, read-only Workbook object

/ / create a Workbook directly from a local file

/ / create a Workbook from the input stream

InputStream is = new FileInputStream (sourcefile)

Jxl.Workbook rwb = Workbook.getWorkbook (is)

}

Catch (Exception e)

{

E.printStackTrace ()

}

Once the Workbook is created, we can use it to access the Excel Sheet (terminology: worksheet). Refer to the following code snippet:

/ / get the first Sheet table

Sheet rs = rwb.getSheet (0)

We can access it either by its name or by subscript. If you access it through subscript, you should pay attention to

One point is that the subscript starts at 0, just like an array.

Once we have the Sheet, we can access the Excel Cell (terminology: cells) through it. Refer to the following code snippet:

/ / get the value of the first row and the first column

Cell c00 = rs.getCell (0,0)

String strc00 = c00.getContents ()

/ / get the value of the first row and the second column

Cell c10 = rs.getCell (1,0)

String strc10 = c10.getContents ()

/ / get the value of the second row and the second column

Cell c11 = rs.getCell (1,1)

String strc11 = c11.getContents ()

System.out.println ("Cell (0,0)" + "value:" + strc00 + "; type:" + c00.getType ())

System.out.println ("Cell (1,0)" + "value:" + strc10 + "; type:" + c10.getType ())

System.out.println ("Cell (1,1)" + "value:" + strc11 + "; type:" + c11.getType ())

If we just get the value of Cell, we can easily use the getContents () method, which can take any type of cell value as

A string is returned. In the sample code, Cell (0,0) is text type, Cell (1,0) is numeric, and Cell (1mem1) is date type.

GetContents (), the return values of the three types are all character types.

If you need to know the exact type of Cell content, API also provides a series of methods. Refer to the following code snippet:

String strc00 = null

Double strc10 = 0.00

Date strc11 = null

Cell c00 = rs.getCell (0,0)

Cell c10 = rs.getCell (1,0)

Cell c11 = rs.getCell (1,1)

If (c00.getType () = = CellType.LABEL)

{

LabelCell labelc00 = (LabelCell) c00

Strc00 = labelc00.getString ()

}

If (c10.getType () = = CellType.NUMBER)

{

NmberCell numc10 = (NumberCell) c10

Strc10 = numc10.getValue ()

}

If (c11.getType () = = CellType.DATE)

{

DateCell datec11 = (DateCell) c11

Strc11 = datec11.getDate ()

}

System.out.println ("Cell (0,0)" + "value:" + strc00 + "; type:" + c00.getType ())

System.out.println ("Cell (1,0)" + "value:" + strc10 + "; type:" + c10.getType ())

System.out.println ("Cell (1,1)" + "value:" + strc11 + "; type:" + c11.getType ())

After you get the Cell object, you can get the type of the cell through the getType () method, and then match the basic type provided by API.

Convert the system to the corresponding type, and finally call the corresponding value method getXXX (), you can get the value of the determined type. API provides the following bases

This type corresponds to the data format of Excel, as shown in the following figure:

For the specific meaning of each type, see Java Excel API Document.

When you have finished working with the Excel spreadsheet data, be sure to use the close () method to close the previously created object to release the readings

According to the memory space occupied in the process of the table, it is particularly important to read a large amount of data. Refer to the following code snippet:

/ / when the operation is complete, close the object and release the occupied memory space

Rwb.close ()

Java Excel API provides many ways to access Excel data tables. Here I will briefly introduce a few commonly used methods and others.

Please refer to the Java Excel API Document in the appendix.

Methods provided by the Workbook class

1. Int getNumberOfSheets ()

Get the number of worksheets (Sheet) in the workbook (Workbook), for example:

Jxl.Workbook rwb = jxl.Workbook.getWorkbook (new File (sourcefile))

Int sheets = rwb.getNumberOfSheets ()

2. Sheet [] getSheets ()

Returns an array of worksheet (Sheet) objects in the workbook (Workbook), for example:

Jxl.Workbook rwb = jxl.Workbook.getWorkbook (new File (sourcefile))

Sheet [] sheets = rwb.getSheets ()

3. String getVersion ()

Returning the version number of the API you are using does not seem to have much effect.

Jxl.Workbook rwb = jxl.Workbook.getWorkbook (new File (sourcefile))

String apiVersion = rwb.getVersion ()

Methods provided by the Sheet interface

1) String getName ()

Get the name of the Sheet, for example:

Jxl.Workbook rwb = jxl.Workbook.getWorkbook (new File (sourcefile))

Jxl.Sheet rs = rwb.getSheet (0)

String sheetName = rs.getName ()

2) int getColumns ()

Gets the total number of columns contained in the Sheet table, for example:

Jxl.Workbook rwb = jxl.Workbook.getWorkbook (new File (sourcefile))

Jxl.Sheet rs = rwb.getSheet (0)

Int rsColumns = rs.getColumns ()

3) Cell [] getColumn (int column)

Gets all the cells in a column and returns an array of cell objects, for example:

Jxl.Workbook rwb = jxl.Workbook.getWorkbook (new File (sourcefile))

Jxl.Sheet rs = rwb.getSheet (0)

Cell [] cell = rs.getColumn (0)

4) int getRows ()

Gets the total number of rows contained in the Sheet table, for example:

Jxl.Workbook rwb = jxl.Workbook.getWorkbook (new File (sourcefile))

Jxl.Sheet rs = rwb.getSheet (0)

Int rsRows = rs.getRows ()

5) Cell [] getRow (int row)

Gets all the cells in a row and returns an array of cell objects, as shown in the example:

Jxl.Workbook rwb = jxl.Workbook.getWorkbook (new File (sourcefile))

Jxl.Sheet rs = rwb.getSheet (0)

Cell [] cell = rs.getRow (0)

6) Cell getCell (int column, int row)

To get the object reference of the specified cell, you should pay attention to its two parameters, the first is the number of columns, and the second is the number of rows, which is the same as the usual rows.

The combination of columns is somewhat different.

Jxl.Workbook rwb = jxl.Workbook.getWorkbook (new File (sourcefile))

Jxl.Sheet rs = rwb.getSheet (0)

Cell cell = rs.getCell (0,0)

2 generate a new Excel workbook

The following code mainly shows you how to generate a simple Excel worksheet, where the contents of the cell are undecorated (such as:

Fonts, colors, etc.), all the contents are written as strings. (see ExcelWriting.java for the complete code)

Similar to reading an Excel worksheet, you first create a writable workbook (Workbook) object using the factory method of the Workbook class

It should be noted here that Workbook can only be created through the factory methods provided by API, not the constructor of WritableWorkbook

Number, because the constructor of class WritableWorkbook is of type protected. The sample code snippet is as follows:

Import java.io.*

Import jxl.*

Import jxl.write.*

... ... ... ...

Try

{

/ / build Workbook object, read-only Workbook object

/ / Method 1: create a writable Excel workbook

Jxl.write.WritableWorkbook wwb = Workbook.createWorkbook (new File (targetfile))

/ / Method 2: write WritableWorkbook directly to the output stream

/ *

OutputStream os = new FileOutputStream (targetfile)

Jxl.write.WritableWorkbook wwb = Workbook.createWorkbook (os)

, /

}

Catch (Exception e)

{

E.printStackTrace ()

}

API provides two ways to handle writable output streams, one is to generate local files directly, and if the file name does not have a full path,

The provincial files will be located in the current directory, and if the file name has a full path, the resulting Excel file will be located in the corresponding directory.

The other is to write the Excel object directly to the output stream, for example, the user accesses the Web server through the browser, if the HTTP header setting is positive

If so, the browser automatically invokes the client's Excel application to display the dynamically generated Excel spreadsheet.

The next step is to create a worksheet in almost the same way as a workbook, which is also obtained through the factory schema method

To get the corresponding object, this method requires two parameters, one is the name of the worksheet, and the other is the position of the worksheet in the workbook, refer to

Code snippet of the face:

/ / create an Excel worksheet

Jxl.write.WritableSheet ws = wwb.createSheet ("Test Sheet 1", 0)

"the pot is ready, and the materials are ready, so you can start the pot!" Now all you have to do is instantiate the Excel basics provided by API

Data types and add them to the worksheet, refer to the following code snippet:

/ / 1. Add Label object

Jxl.write.Label labelC = new jxl.write.Label (0,0, "This is a Label cell")

Ws.addCell (labelC)

/ / add an object with font Formatting

Jxl.write.WritableFont wf = new jxl.write.WritableFont (WritableFont.TIMES, 18

WritableFont.BOLD, true)

Jxl.write.WritableCellFormat wcfF = new jxl.write.WritableCellFormat (wf)

Jxl.write.Label labelCF = new jxl.write.Label (1,0, "This is a Label Cell", wcfF)

Ws.addCell (labelCF)

/ / add an object with font color Formatting

Jxl.write.WritableFont wfc = new jxl.write.WritableFont (WritableFont.ARIAL, 10

WritableFont.NO_BOLD, false

UnderlineStyle.NO_UNDERLINE, jxl.format.Colour.RED)

Jxl.write.WritableCellFormat wcfFC = new jxl.write.WritableCellFormat (wfc)

Jxl.write.Label labelCFC = new jxl.write.Label (1,0, "This is a Label Cell", wcfFC)

Ws.addCell (labelCF)

/ / 2. Add Number object

Jxl.write.Number labelN = new jxl.write.Number (0, 1, 3.1415926)

Ws.addCell (labelN)

/ / add a Number object with formatting

Jxl.write.NumberFormat nf = new jxl.write.NumberFormat ("#")

Jxl.write.WritableCellFormat wcfN = new jxl.write.WritableCellFormat (nf)

Jxl.write.Number labelNF = new jxl.write.Number (1,1,3.1415926, wcfN)

Ws.addCell (labelNF)

/ / 3. Add Boolean object

Jxl.write.Boolean labelB = new jxl.write.Boolean (0,2, false)

Ws.addCell (labelB)

/ / 4. Add DateTime object

Jxl.write.DateTime labelDT = new jxl.write.DateTime (0,3, new java.util.Date ())

Ws.addCell (labelDT)

/ / add a DateFormat object with formatting

Jxl.write.DateFormat df = new jxl.write.DateFormat ("dd MM yyyy hh:mm:ss")

Jxl.write.WritableCellFormat wcfDF = new jxl.write.WritableCellFormat (df)

Jxl.write.DateTime labelDTF = new jxl.write.DateTime (1,3, new java.util.Date (), wcfDF)

Ws.addCell (labelDTF)

Here are two points for everyone to pay attention to. First, when you construct a cell, the location of the cell in the worksheet is determined.

Once created, the location of the cell cannot be changed, although the contents of the cell can be changed. Second, the positioning of the cell is

According to the following rule (column, row), and the subscript starts at 0, for example, A1 is stored in (0,0) and B1 is stored in (1).

0).

Finally, don't forget to close the open Excel workbook object to free up memory, see the following code snippet:

/ / write to Exel worksheet

Wwb.write ()

/ / close the Excel workbook object

Wwb.close ()

This may be slightly different from reading the Excel file. Before you can close the Excel object, you must call the write () method, because

Previous operations are stored in the cache, so use this method to save the contents of the operation in a file. If you shut down Excel first,

Object, then you can only get an empty workbook.

3 copy and update Excel workbook

Next, let's briefly introduce how to update an existing workbook, mainly in the following two steps. The first step is to construct a read-only Excel.

Workbook, the second step is to create a new writable Excel workbook using the Excel workbook you have created, refer to the following code snippet:

For the complete code, see ExcelModifying.java)

/ / create a read-only Excel workbook object

Jxl.Workbook rw = jxl.Workbook.getWorkbook (new File (sourcefile))

/ / create a writable Excel workbook object

Jxl.write.WritableWorkbook wwb = Workbook.createWorkbook (new File (targetfile), rw)

/ / read the first worksheet

Jxl.write.WritableSheet ws = wwb.getSheet (0)

/ / get the first cell object

Jxl.write.WritableCell wc = ws.getWritableCell (0,0)

/ / judge the type of cell and make the corresponding transformation

If (wc.getType () = = CellType.LABEL)

{

Label l = (Label) wc

L.setString ("The value has been modified.")

}

/ / write Excel object

Wwb.write ()

/ / close writable Excel objects

Wwb.close ()

/ / close read-only Excel objects

Rw.close ()

The reason for building Excel objects in this way is entirely for efficiency reasons, because the above example is the main application of API. In order to

To improve performance, when reading worksheets, some output information related to the data, all format information, such as fonts, colors, etc., are not

Is processed, because our purpose is to get the value of the row data, even without modification, it will not have any effect on the value of the row data. Only

The disadvantage is that two identical worksheets are saved in memory at the same time, so that when the worksheet is relatively large, it will take up a lot of money.

Memory, but now it seems that the size of memory is not a key factor.

Once we have a writable worksheet object, we can update the cell object, where we don't have to call API

The add () method provided, because the cell is already in the worksheet, we only need to call the corresponding setXXX () method to finish

It's an updated operation.

The original formatting modification of the cell can not be removed, but we can still add the new cell decoration to make the contents of the cell

Different forms of expression.

The newly generated worksheet object is writable. In addition to updating the original cells, we can also add new cells to the worksheet. This

The operation is exactly the same as in example 2.

Finally, don't forget to call the write () method, write the updated content to the file, and then close the workbook object. Here are two jobs.

Thin objects are turned off, one is read-only and the other is writable.

Thank you for reading! On "how to use JAVA to operate EXCEL files" this article is shared here, I hope the above content can be of some help to you, so that you can learn more knowledge, if you think the article is good, you can share it out for more people to see it!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.