java excel operation

2010-04-18  来源:本站原创  分类:Web  人气:450 

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 files through the Web growing demand, now more popular in the 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 CSV file and display. 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 written in, so we Web applications where a JSP, Servlet To call API implementation of the Excel data table access.

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

• From Excel 95,97,2000 other file format to read data;

* Read the Excel formulas (Excel 97 can read the future formula);

* Generate Excel data sheet (format to Excel 97);

* Support for fonts, number, date formatting;

• Support the shadow of the cell operation, and the color of operation;

Modify existing data in the table;

* The following functions 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

Read data from Excel file table

Java Excel API either from the local file system of a file (. Xls), can 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 to operate: (complete code see ExcelReading.java)
java code [list = 1] 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:
    java code [list = 1] / / 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:
    java code [list = 1] / / 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 the Cell's value is only achieved, we can easily through the getContents () method, which can be any type of Cell values as a string to return. 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:
    java code [list = 1] 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 ()); in by 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 method of choosing the appropriate getXXX (), the type of value can be determined. API provides the following basic types, and the corresponding Excel data format, as shown below:

    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:
    java code [list = 1] / / 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.

    · Workbook class provides methods

    1. Int getNumberOfSheets ()

    Get workbook (Workbook) in the worksheet (Sheet) the number, example:
    java code [list = 1] jxl.Workbook rwb = jxl.Workbook.getWorkbook (new File (sourcefile));

  • int sheets = rwb.getNumberOfSheets ();
    2. Sheet [] getSheets ()

    Return workbook (Workbook) in the worksheet (Sheet) array of objects, examples:
    java code [list = 1] jxl.Workbook rwb = jxl.Workbook.getWorkbook (new File (sourcefile));

  • Sheet [] sheets = rwb.getSheets ();
    3. String getVersion ()

    Are using the API to return the version number, as if nothing much effect.
    java code [list = 1] jxl.Workbook rwb = jxl.Workbook.getWorkbook (new File (sourcefile));

  • String apiVersion = rwb.getVersion ();
    · Sheet interface methods

    1. String getName ()

    Get Sheet name, example:
    java code [list = 1] jxl.Workbook rwb = jxl.Workbook.getWorkbook (new File (sourcefile));

  • jxl.Sheet rs = rwb.getSheet (0);
    String sheetName = rs.getName (); 2. Int getColumns ()

    Get Sheet table contains the total number of columns, an example:
    java code [list = 1] jxl.Workbook rwb = jxl.Workbook.getWorkbook (new File (sourcefile));

  • jxl.Sheet rs = rwb.getSheet (0);
    int rsColumns = rs.getColumns ();
  • 3. Cell [] getColumn (int column)

    Access to all cells of a column, returns a cell array of objects, examples:
    java code [list = 1] jxl.Workbook rwb = jxl.Workbook.getWorkbook (new File (sourcefile));

  • jxl.Sheet rs = rwb.getSheet (0);
    Cell [] cell = rs.getColumn (0); 4. Int getRows ()

    Get Sheet table contains number of rows, example:
    java code [list = 1] jxl.Workbook rwb = jxl.Workbook.getWorkbook (new File (sourcefile));

  • jxl.Sheet rs = rwb.getSheet (0);
    int rsRows = rs.getRows (); 5. Cell [] getRow (int row)

    Access to all cells of a row, returns a cell array of objects, sub-sample:
    java code [list = 1] 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)

    Access to the specified object reference cell, to note that its two parameters, the first is the number of columns, the second is the number of rows, which is the usual row, column combinations are different.
    java code [list = 1] jxl.Workbook rwb = jxl.Workbook.getWorkbook (new File (sourcefile));

  • jxl.Sheet rs = rwb.getSheet (0);
    Cell cell = rs.getCell (0, 0);
  • Generate a new Excel workbook

    The following code is mainly to tell you how to generate a simple Excel worksheet, where the contents of the cell without any modification (such as: fonts, colors, etc.), all content is written as a string. (Complete code see ExcelWriting.java)

    Excel worksheet and read similar to, first of all to use the Workbook class factory method to create a writable workbook (Workbook) object, here to note is that only through the API provided by the factory method to create a Workbook, which can not be used WritableWorkbook constructor, because the constructor for the class WritableWorkbook protected type. Sample code snippet is as follows:
    java code [list = 1] import java.io. *;

  • import jxl .*;
    import jxl.write .*;
  • ... ... ... ...
    try
  • (
    / / Build Workbook object Workbook object read-only
  • / / Method 1: Create the Excel workbook can be written
    jxl.write.WritableWorkbook wwb = Workbook.createWorkbook (new File (targetfile));
  • / / Method 2: to write directly to the output stream WritableWorkbook
    / *
  • OutputStream os = new FileOutputStream (targetfile);
    jxl.write.WritableWorkbook wwb = Workbook.createWorkbook (os);
  • * /
    )
  • catch (Exception e)
    (
  • e.printStackTrace ();
    ) API provides two ways to handle write-output stream, one is directly generating a local file, if the file name without full path, then the default file is located in the current directory, if the file cases with the full path Di, then it generates the Excel file will locate the directory in the corresponding; another one kind of object is Excel Zhijiexieru Dao output stream, For example, : user browser to Access to the Web server , Ru Guo HTTP header to the right of it, browser automatically Excel calls the client application to display dynamically generated Excel spreadsheet.

    The next step is to create a work table, work table to create the methods and means to create work almost as thin, as is the factory pattern obtained by the corresponding object, the method requires two parameters, one is the name of the worksheet, the other is worksheet in the workbook in the position, reference the following code fragment:
    java code [list = 1] / / create the Excel worksheet

  • jxl.write.WritableSheet ws = wwb.createSheet ("Test Sheet 1", 0);
    "This pot has good support, and materials prepared to complete, and can Kaishi hack up!", Now need to do is instantiate the Excel API The supplied primitive data type and add them to the worksheet on the can, and reference to the following code fragment:
    java code [list = 1] / / 1. Add a Label object
  • jxl.write.Label labelC = new jxl.write.Label (0, 0, "This is a Label cell");
    ws.addCell (labelC);
  • / / Add the object with a 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 the object with a 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 a Number object
  • jxl.write.Number labelN = new jxl.write.Number (0, 1, 3.1415926);
    ws.addCell (labelN);
  • / / Add the 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 the Boolean object
  • jxl.write.Boolean labelB = new jxl.write.Boolean (0, 2, false);
    ws.addCell (labelB);
  • / / 4. Add a DateTime object
    jxl.write.DateTime labelDT = new jxl.write.DateTime (0, 3, new java.util.Date ());
  • ws.addCell (labelDT);
    / / Add the 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);
    There are two points we must draw our attention. First, in constructing a cell, the cell position in the work table was already set. Once created, the location of the cell can not be changed, although the contents of the cell can be changed. Secondly, the positioning of the cell in accordance with the law follows (column, row), and subscript are from 0, for example, A1 is stored in the (0, 0), B1 is stored in the (1, 0) .

    Finally, do not forget to turn off the open Excel workbook object to release the memory, see the following code fragment:
    java code [list = 1] / / write Exel worksheet

  • wwb.write ();
    / / Close the Excel workbook object
  • wwb.close ();
    This may be related to the operation of reading Excel files are slightly different, before you close Excel object, you must first call the write () method as the previous operations are stored in the cache, it will operate through the contents of this method Save the file. If you turn off the Excel object, it can only get a thin piece of work space.

    Copy, update Excel workbook

    Next, a brief overview of how to update an existing workbook, mainly the following two-step operation, the first step is to construct a read-only Excel workbook, the second step is to use the Excel workbook has been created to create a new writable Excel workbook, reference the following code fragment: (the full code see ExcelModifying.java)
    java code [list = 1] / / create a read-only Excel workbook object

  • jxl.Workbook rw = jxl.Workbook.getWorkbook (new File (sourcefile));
    / / Create object to write the Excel workbook
  • 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);
  • / / Determine the type of cell and make the appropriate conversion
    if (wc.getType () == CellType.LABEL)
  • (
    Label l = (Label) wc;
  • l.setString ("The value has been modified.");
    )
  • / / Write Excel object
    wwb.write ();
  • / / Close the Excel object can be written
    wwb.close ();
  • / / Close the Excel object read-only
    rw.close (); reason to use Excel objects constructed in this way, simply because efficiency reasons, as the above example is the API of the main application. To improve performance, reading worksheet data-related some output with, all the Ge Shi information, such as: fonts, colors, and so is not being dealt with, because our aim is to get OK data values, even Without modification, the value of the data row will not have any effect. The only disadvantage is that, in memory will also save both the same worksheet, so that when the sheet size is large, will take up considerable memory, but if memory size is not a key factor in the.

    Once a worksheet that can be written to the object, we can update the object on the cell operation, and here we do not call the API provided by the add () method, because cells were already in the worksheet, so we only need call the appropriate setXXX () method, you can complete the update operation of the.

    Cells do not modify the original formatting removed, and we can modify the new cell was added to make the contents of the cell performance in different forms.

    Newly generated worksheet object is written, we update the existing unit in addition to extra, you can also add new cells to the worksheet, which is Example 2 of the operation is exactly the same.

    Finally, do not forget the call to write () method will update the contents of the written to the file, then close the workbook object, there are two targets to close the workbook, one is read-only, and the other one is writable.

    Summary

    This is commonly used on the Java Excel API method was introduced, to more detailed understanding of the API, please refer to API documentation, or source code. Java Excel API is an open source project, which we are concerned about the latest progress of friends who are interested can apply to join the project, or offer suggestions.

    Reference material

    Java Excel API documentation

相关文章
  • 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

  • Ask questions about java excel operation (such as jxl.write.WritableWorkbook.write () method) 2010-06-11

    First set down my question: I have written a program is to first create an excel table, then create a working table (Table I), followed by a write data to the worksheet, write a 600 record, and then just created worksheet to create a statement of Wor

  • 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 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

  • 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

  • poi3.6 read and write excel operation 2010-09-25

    Apache POI is an open source Java to read and write Excel, WORD and other Microsoft OLE2 component documentation projects. Download: http://poi.apache.org/download.html Unzip package by copying all the relevant jar lib the following items. Operation

  • 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

  • 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文件,

  • Excel file of java POI operation 2011-08-13

    Microsoft's success on the desktop, so we have a lot of use of office products, such as: Word, Excel. Today, it is still not open source code has blocked our further application and development. However, in demanding server, Microsoft's own products

  • Java Excel file download 2010-03-29

    import jxl.Workbook; import jxl.write.Label; import jxl.write.WritableSheet; import jxl.write.WritableWorkbook; public class ExcelDownload extends HttpServlet { public void doGet(HttpServletRequest request, HttpServletResponse response) throws Servle

  • Commons-POI - Java Excel operations 2010-04-19

    ★ , POI, many components are not able to spend, according to need to select their own needs, and I used here is the SS (HSSF + XSSF), and were for xls and xlsx, generally should be ok to use HSSF to, or it more people with office2003 ★ , most of the

  • java excel convert the contents of key / value pairs into the database of basic common method 2010-06-01

    Sometimes we need to import large amounts of data execel, programming process would face the situation, eg: excel format as the file name: eg: e: \ minn \ minn.xls NAME SEX SCHOOL Name Sex Schools minn1 male extension work minn2 male extension work j

  • JXL package used to read Excel operation JRuby 2010-06-11

    Recent clients often asked to do some secondary development, which involves the conversion between the data, it is the customer will be sent an Excel file, which records the relationships between data, such as: 001 on behalf of managers, and I will b

  • jxl use cases (Java -> Excel) 2010-07-14

    Prerequisite for running this example : Go online to download a call jxl.jar of jar Package, and then put your project WEB-INF/lib Next . package gzu.lyq.jxl; import java.io.FileOutputStream; import java.io.OutputStream; import java.text.SimpleDateFo

  • java file operation 2010-03-29

    java text file operations personally think that the most complicated to modify operation! Need to rewrite the entire text! public class txtfile ( public void txtfileQ () throws IOException (/ / txt file query FileReader fr = new FileReader ( "c: / /

  • example of java Excel operations 2009-07-08

    1. package excel.jxl; 2. 3. import java.io.File; 4. import java.io.FileOutputStream; 5. import java.io.OutputStream; 6. import java.util.ArrayList; 7. import java.util.Date; 8. 9. import jxl.Cell; 10. import jxl.CellType; 11. import jxl.Sheet; 12. im

  • POI excel operation compared with the JXL 2010-07-21

    Apache POI is a standard project, provides a complete interface, but also because of the POI will be more complicated to use up the difficulty of use. Compared JXL on is easier to use a lot of functionality seems less certain, but still more than eno

  • Java, the operation rules of the shift operator 2010-08-21

    Java in the shift, if it is short, char, byte, then, will be converted to int in the form of further translocation. Look: 1.) Byte a = 27; / / convert int to 00000000000000000000000000011011 byte b = -1; convert int to 1111111111111111111111111111111

  • Read and write simple java excel 2010-08-29

    First Reading of Excel: package August; import java.io.File; import jxl.Cell; import jxl.CellType; import jxl.DateCell; import jxl.NumberCell; import jxl.Sheet; import jxl.Workbook; public class ReadExcel { public static void main(String[] args) { Wo

  • Java Excel function of the effective interest rate demand RATE 2010-10-22

    There is a function in excel RATE is to get the real interest rate, have identified a half-line, the formula'd find a lot of, you can not have a realization of the algorithm, depressed, their own research and write one out to be a memo Formula: The a