java JXL operation Excel example details

Excel JXL operation

Preface:

jxl is a korean-written tool for java to operate excel. In the open source world, there are two more influential sets of API available, one is POI, the other is jExcelAPI. The function of POI is relatively weak 1 point. However, jExcelAPI supports Chinese very well. API is pure Java and does not rely on Windows system. Even running under Linux, it can handle Excel files correctly. It should also be noted that the API has limited support for graphs and charts and only recognizes the PNG format.

Use the following:

Set up the environment

Unpack the downloaded file, get ES29en. jar, put it into classpath, and the installation is completed.

Basic operation

1. Create the file

To generate an Excel file named "test.xls", where the first worksheet is named "Page 1", the general effect is as follows:

 package test;

 //  generate Excel The class of  
 import java.io.File;

 import jxl.Workbook;
 import jxl.write.Label;
 import jxl.write.WritableSheet;
 import jxl.write.WritableWorkbook;

 public  class CreateExcel {
   public  static  void main(String args[]) {
     try  {
       //  Open the file  
       WritableWorkbook book = Workbook.createWorkbook( new File( " test.xls " ));
       //  The generation name is "control" 1 Page "worksheet, parameters 0 That means this is number one 1 page  
       WritableSheet sheet = book.createSheet( "  The first 1 page  " , 0 );
       //  in Label The constructor of an object indicates that the listing cell position is the first 1 The column first 1 line (0,0)
       //  And the cell content is test 
       Label label =  new Label( 0 , 0 , " test " );

       //  Adds the defined cells to the worksheet  
       sheet.addCell(label);

       /* 
       *  generate 1 A number of cells   You must use the Number The full package path, otherwise there is syntactic ambiguity   The cell position is number one 2 The column, the first 1 Line, the value of 789.123
       */ 
      jxl.write.Number number =  new jxl.write.Number( 1 , 0 , 555.12541 );
      sheet.addCell(number);

       //  Write data and close the file  
       book.write();
      book.close();

    }  catch (Exception e) {
      System.out.println(e);
    } 
  } 
} 

When compiled and executed, an Excel file is produced.

3. Read the file

Take the Excel file we just created as an example to do a simple read operation. The program code is as follows:

 package test;

 //  read Excel The class of  
 import java.io.File;

 import jxl.Cell;
 import jxl.Sheet;
 import jxl.Workbook;

 public  class ReadExcel {
   public  static  void main(String args[]) {
     try  {
      Workbook book = Workbook.getWorkbook( new File( " test.xls " ));
       //  For the first 1 Three worksheet objects  
       Sheet sheet = book.getSheet( 0 );
       //  Get the first 1 The column first 1 Row cell  
       Cell cell1 = sheet.getCell( 0 , 0 );
      String result = cell1.getContents();
      System.out.println(result);
      book.close();
    }  catch (Exception e) {
      System.out.println(e);
    } 
  } 
} 

Program execution result: test

4. Modify the document

Using jExcelAPI, you can modify the existing Excel file. When modifying Excel file, the operation and creation of Excel are the same except that the file is opened differently. The following example is to add a worksheet to the Excel file we have generated:

 package test;

 import java.io.File;

 import jxl.Workbook;
 import jxl.write.Label;
 import jxl.write.WritableSheet;
 import jxl.write.WritableWorkbook;

 public  class UpdateExcel {
   public  static  void main(String args[]) {
     try  {
       // Excel Get file  
       Workbook wb = Workbook.getWorkbook( new File( " test.xls " ));
       //  Open the 1 A copy of a file and specifies that the data is written back to the original file  
       WritableWorkbook book = Workbook.createWorkbook( new File( " test.xls " ),
          wb);
       //  add 1 A worksheet  
       WritableSheet sheet = book.createSheet( "  The first 2 page  " , 1 );
      sheet.addCell( new Label( 0 , 0 , "  The first 2 Page of test data  " ));
      book.write();
      book.close();
    }  catch (Exception e) {
      System.out.println(e);
    } 
  } 
} 

Other operating

1. Data formatting

Excel does not involve complex data types, and is able to handle strings, Numbers, and dates well enough to satisfy 1-like applications.

1, string formatting

The formatting of strings involves elements such as font, thickness, and font size, which are mainly handled by the WritableFont and WritableCellFormat classes. Suppose we generate a cell containing a string using the following statement. For the convenience of description, we numbered each line of commands:

 WritableFont font1 = 
 new WritableFont(WritableFont.TIMES, 16 ,WritableFont.BOLD);  1. 

 WritableCellFormat format1 = new WritableCellFormat(font1);  2. 

 Label label = new Label( 0 , 0 , " data 4 test " ,format1)  3. 

Where specifies the string format: font TIMES, font size 16, bold display. WritableFont has a very rich list of constructors for use in different situations. java-doc has a detailed list, which will not be listed here.

The code at USES the WritableCellFormat class, which is very important because it allows you to specify the various properties of the cell, which will be described more later in the cell formatting.

USES the constructor of the Label class to specify which format the string is given.

Another important method in the WritableCellFormat class is to specify the alignment of the data, such as for us

For the above instance, you can specify:

 //  Assign horizontal alignment to center  
 format1.setAlignment(jxl.format.Alignment.CENTRE);

 //  Assign vertical alignment to center  
 format1.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);

2. Cell operation

An important part of Excel is the manipulation of cells, such as row height, column width, cell merging, and so on, which jExcelAPI fortunately provides. These operations are relatively simple, and only the relevant API is described below.

1. Merge cells

 WritableSheet.mergeCells( int m, int n, int p, int q); 

 //  Effect from (m,n) to (p,q) All of the cells are merged, such as:  
 WritableSheet sheet = book.createSheet( "The first 1 Page" , 0 );

 //  Merge the first 1 The column first 1 Line to the first 6 The column first 1 All the cells of a row  
 sheet.mergeCells( 0 , 0 , 5 , 0 );

Mergers can be horizontal or vertical. The merged cell cannot be merged again, otherwise an exception will be raised.

2. Row height and column width

 WritableSheet.setRowView( int i, int height);

 //  The function specifies a control i+1 Row heights, such as: 

 //  Will be the first 1 The row height is set to 200 
 sheet.setRowView( 0 , 200 );

 WritableSheet.setColumnView( int i, int width);

 //  The function specifies a control i+1 The width of the column, for example: 

 //  Will be the first 1 The width of the column is set to 30 
 sheet.setColumnView( 0 , 30 );

jExcelAPI also has some other functions, such as image insertion, which will not be introduced here. Readers can explore them by themselves.

Where: If you read an excel, you need to know how many rows and columns it has, as follows:

 Workbook book = Workbook.getWorkbook( new File( "  test 1.xls " ));
     //  For the first 1 Three worksheet objects  
     Sheet sheet = book.getSheet( 0 );
     //  Get the first 1 The column first 1 Row cell  
     int columnum = sheet.getColumns(); //  Get the number of columns  
     int rownum = sheet.getRows(); //  Get the number of rows  
     System.out.println(columnum);
    System.out.println(rownum);
     for ( int i =  0 ; i < rownum; i ++ ) //  Loop read and write  
      {
       for ( int j =  0 ; j < columnum; j ++ ) {
        Cell cell1 = sheet.getCell(j, i);
        String result = cell1.getContents();
        System.out.print(result);
        System.out.print( " \t " );
      } 
      System.out.println();
    } 
    book.close();

Thank you for reading, I hope to help you, thank you for your support to this site!