Full excavation Java Excel API to use

2010-04-22  来源:本站原创  分类:Java  人气:294 

Friends use Windows operating system Excel (spreadsheet) will definitely be familiar, but to use the Java language to manipulate the Excel file is not an easy task. Increasingly popular in Web applications today, through the Web to manipulate Excel files demand more and more intense, and most popular JSP or Servlet action is to create a CSV (comma separated values) file and the file to MIME, text / csv type returned to the browser, the browser then calls Excel and show CSV file. This just says access to the Excel file, but it does not really manipulate Excel files, this will give us a pleasant surprise, to tell you about an open source project, Java Excel API, you can easily use it to manipulate Excel files.

Java Excel API Introduction

Java Excel is an open source project through which Java developers can read Excel document, create a new Excel file, update an existing Excel file. Non-Windows operating system using the API can also be pure Java application to handle Excel spreadsheets. Because the use of Java, Web applications, so we can be in the JSP, Servlet to call the API to achieve access to the Excel data tables.

Now released stable version is V2.0, the following functions:

From Excel 95,97,2000 file formats such as read data;
Read Excel formulas (Excel 97 can read the future formula);
Generate Excel data tables (format Excel 97);
Support for fonts, number, date format;
Support the operation of the shadow of the cell, and the color of operation;
Modify existing data in the table;

The following features are not supported, but will soon offer:

Not able to read the chart information;
Can be read, but can not generate the formula, the calculation of any type of formula the final value can read;

Application Example

1, read data from Excel file table

Java Excel API either from the local file system of a file (. Xls), can also be read from the input stream in Excel spreadsheets. Read Excel data table first step is to create a Workbook (terminology: workbook), the following code fragment illustrates how operation: (full code see ExcelReading.java)

import java.io. *;
import jxl .*;
... ... ... ...
try
(
/ / Build Workbook object Workbook object read-only
/ / Create local files directly from the Workbook
/ / Create a Workbook from the input stream
InputStream is = new FileInputStream (sourcefile);
jxl.Workbook rwb = Workbook.getWorkbook (is);
)
catch (Exception e)
(
e.printStackTrace ();
)

Once you create a Workbook, we can use it to access the Excel Sheet (term: work table). Reference to the following code fragment:

/ / Get the first Sheet Form
Sheet rs = rwb.getSheet (0);

We can pass the Sheet name to access it, you can also subscript to access it. Accessed through the subscript if so, should be noted that subscript from 0, as an array.

Once the Sheet, we can use it to access the Excel Cell (term: cell). Reference to the following code fragment:

/ / Get the first row, first column value
Cell c00 = rs.getCell (0, 0);
String strc00 = c00.getContents ();

/ / Get the first row, second column values
Cell c10 = rs.getCell (1, 0);
String strc10 = c10.getContents ();

/ / Get the second row, second column value
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 you only get the value of Cell, we can easily through the getContents () method, which can be any type of Cell values are returned as a string. Sample code in the Cell (0, 0) is a text type, Cell (1, 0) is a digital type, Cell (1,1) is a date type, through the getContents (), three types of return values are character.

If you need to know the exact type of Cell content, API also provides a range of methods. Reference to the following code fragment:

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 obtaining the Cell object, by getType () method can get the cell type, and with the API to provide basic type of match cast into the appropriate type, and finally call the appropriate method of choosing getXXX (), can be determine the type of value. API provides the following basic types, and the corresponding Excel data format, as shown:

The specific meaning of each type, see the Java Excel API document.

When you complete the Excel spreadsheet, data processing, be sure to use the close () method to close the previously created object to the release of the process of reading the data table memory space occupied in the reading is especially important when large amounts of data. Refer to the following code fragment:

/ / Operation is complete, close the object, the release of the memory space
rwb.close ();

Java Excel API provides many ways to access Excel data table, where I briefly introduce several commonly used method, other methods please refer to appendix Java Excel API document.

相关文章
  • Full excavation Java Excel API to use 2010-04-22

    Friends use Windows operating system Excel (spreadsheet) will definitely be familiar, but to use the Java language to manipulate the Excel file is not an easy task. Increasingly popular in Web applications today, through the Web to manipulate Excel f

  • EXCEL file using JAVA operation (JAVA EXCEL API) 2010-04-22

    JAVA EXCEL API Introduction Java Excel is an open source project through which Java developers can read Excel document, create a new Excel file, update an existing Excel file. Non-Windows operating system using the API can also be pure Java applicati

  • JAVA EXCEL API详解 2012-06-10

    使用Windows操作系统的朋友对Excel(电子表格)一定不会陌生,但是要使用Java语言来操纵Excel文件并不是一件容易的事.在Web应用日益盛行的今天,通过Web来操作Excel文件的需求越来越强烈,目前较为流行的操作是在JSP或Servlet 中创建一个CSV (comma separated values)文件,并将这个文件以MIME,text/csv类型返回给浏览器,接着浏览器调用Excel并且显示CSV文件.这样只是说可以访问到Excel文件,但是还不能真正的操纵Excel文件,

  • java excel operation 2010-04-18

    Using the Windows operating system, a friend of the Excel (spreadsheet) will definitely be familiar, but to use the Java language to manipulate the Excel file is not an easy task. Increasingly popular in Web applications today, to manipulate Excel fi

  • java excel table operation 2010-07-05

    About Java Excel JAVA EXCEL API is an open source project through which Java developers can read Excel document, create a new Excel file, update Existing Excel file. Non-Windows operating system using the API can also be pure Java application to hand

  • Java Servlet API documentation (a) 2009-11-13

    Introduction This is a report on version 2.1 of the Java Servlet API documentation, as a supplement of this document, you can download the Javadoc format http://java.sun.com/products/servlet/index.html the following documents. Who needs to read this

  • How JAVA Security API to qualitative IBM FileNet P8 content query and storage of security 2009-12-10

    How JAVA Security API to qualitative IBM FileNet P8 content query and storage of security http://www.ibm.com/developerworks/cn/data/library/techarticles/dm-0910wangchao/index.html

  • java mail api Xiangjie 2010-03-30

    JavaMail API Xiangjie Study: Source: Release Date: April 7, 2007 Views: 26 Abstract: JavaMail API is to read, write, send an electronic message optional package. We use it to build as Eudora, Foxmail, MS Outlook Express mail user agent generally (Mai

  • Oracle XML DB - Java DOM API for XMLType (1) 2010-05-28

    Access XMLType data using JDBC oracle.xdb.XMLType class createXML () method is used to access an Oracle database, any data, including the Oracle XML DB for XML documents in SQL based Java application methods. Java applications use JDBC to access the

  • [Reprinted] on the Java Collections API 5 things you do not know, Part 1 2010-06-16

    Original: http://www.ibm.com/developerworks/cn/java/j-5things2.html On the Java Collections API 5 things you do not know, Part 1 Customization and extension Java Collections Ted Neward , competent, Neward & Associates Ted Neward is Neward & Associ

  • Java Collections API on 5 things you do not know, Part 1 2010-06-24

    For many Java developers, Java Collections API is a standard Java array and all the shortcomings of a much-needed alternative. Collections main contact will be together with ArrayList not wrong in itself, but for those exploring the spirit of people,

  • Java Servlet API documentation in Chinese 2010-08-21

    This document describes the latest version of Java Servlet API 2.1. Therefore, this book is for developers and Servlet servlet engine developers apply. The composition of Java Servlet API Java Servlet API package consists of two components: one is th

  • Java Servlet API documentation in Chinese (1) 2010-10-20

    This document describes the Java Servlet API in the latest version of the 2.1 version. Therefore, this book for Servlet servlet engine developers and developers apply. Java Servlet API composition Java Servlet API package consists of two components:

  • On the Java Collections API 5 things you do not know, Part 1 2010-10-23

    For many Java developers, Java Collections API is a standard Java array and all of its shortcomings, a much-needed alternative. Collections will be linked together primarily ArrayList not wrong in itself, but for those people who have the spirit of e

  • Java Scripting API on the 5 things you do not know 2010-10-24

    Introduction: Java ™ language sufficient to meet the needs of your projects, but the scripting language has been underperforming in terms of performance. Java Scripting API (javax.script) support the Java program to call the script, and vice versa, t

  • Java Persistence on pooling of resources related to: Java Persistence API 2010-11-12

    BEA's first to a webinar (Webinar): EJB3 Java Persistence API: Good, the Bad and the general function of attachment is the PPT data. Java Persistence API: The Good, the Bad, and the Ugly Java Persistence API: Good, the Bad and the general features JP

  • java excel export data file format is not valid 2010-12-04

    java excel export data file format is invalid because WritableWorkbook workbook=Workbook.createWorkbook(new File("h:\\a.xls")); workbook.createSheet("abc", 0); workbook.write();// Do not call this function to be a problem workbook.clos

  • (R) Java Transaction API Overview 2010-12-11

    Introduction JTA (Java Transaction API) allows applications to perform distributed transactions - in two or more networked computer resources to access and update data. JTA JDBC driver support for greatly enhanced data access capabilities. The purpos

  • 基于java math API 的详细解释说明 2015-01-19

    本篇文章是对java math API进行了详细的分析介绍,需要的朋友参考下 Math.PI 记录的圆周率 Math.E 记录e的常量 Math中还有一些类似的常量,都是一些工程数学常用量. Math.abs 求绝对值 Math.sin 正弦函数 Math.asin 反正弦函数 Math.cos 余弦函数 Math.acos 反余弦函数 Math.tan 正切函数 Math.atan 反正切函数 Math.atan2 商的反正切函数 Math.toDegrees 弧度转化为角度 Math.toR

  • NetBeans下使用Java Persistence API修改数据库表行(二) 2013-12-02

    文是本系列文章(共两篇)的第二部分.在第一篇文章(在Visual Web应用程序中使用Java Persistence API) 中,您已了解了如何使用Java Persistence API访问数据库表和检索其中的数据,以及如何使用Visual Web Table组件显示该数据.在本文中,我将介绍如何使用相同的Java Persistence API和Visual Web Table组件添加.更新和删除数据库表行. 有关如何使用NetBeans IDE的更多信息,请参阅NetBeans网站上的