Java create excel sample of JXL using methods

Using this API, non-windows operating systems can also be used to work with Excel spreadsheets through pure Java applications. Because it is written in Java, we can use JSP, Servlet to call API to achieve access to Excel data sheet in the Web application.

package com.yonyou.test;
import java.io.File;
import java.io.IOException;
import jxl.Workbook;
import jxl.format.Alignment;
import jxl.format.Border;
import jxl.format.BorderLineStyle;
import jxl.format.Colour;
import jxl.format.UnderlineStyle;
import jxl.write.Label;
import jxl.write.NumberFormats;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;
public class JxlTable {
 private final static JxlTable jxlTable = new JxlTable();
 public static JxlTable getInstance() {
  return jxlTable;
 }
 public JxlTable() {
 }
 public boolean createTable(String header, String[] body, String filePath) {
  boolean createFlag = true;
  WritableWorkbook book;
  try {
   //Generate excel files by path
   book = Workbook.createWorkbook(new File(filePath));
   //Create a sheet called "table"
   WritableSheet sheet = book.createSheet(" form ", 0);
   //Set the NO column width
   sheet.setColumnView(1, 5);
   //Remove the grid lines from the entire sheet
   sheet.getSettings().setShowGridLines(false);
   Label tempLabel = null;
   //Header output
   String[] headerArr = header.split(",");
   int headerLen = headerArr.length;
   //Loops to write header content
   for (int i = 0; i < headerLen; i++) {
    tempLabel = new Label(1 + i, 1, headerArr[i],
      getHeaderCellStyle());
    sheet.addCell(tempLabel);
   }
   //The table body output
   int bodyLen = body.length;
   //Loop to write table body content
   for (int j = 0; j < bodyLen; j++) {
    String[] bodyTempArr = body[j].split(",");
    for (int k = 0; k < bodyTempArr.length; k++) {
     WritableCellFormat tempCellFormat = null;
     tempCellFormat = getBodyCellStyle();
     if (tempCellFormat != null) {
      if (k == 0 || k == (bodyTempArr.length - 1)) {
       tempCellFormat.setAlignment(Alignment.CENTRE);
      }
     }
     tempLabel = new Label(1 + k, 2 + j, bodyTempArr[k],
       tempCellFormat);
     sheet.addCell(tempLabel);
    }
   }
   book.write();
   book.close();
  } catch (IOException e) {
   createFlag = false;
   System.out.println("EXCEL Creation failed! ");
   e.printStackTrace();
  } catch (RowsExceededException e) {
   createFlag = false;
   System.out.println("EXCEL Cell setup creation failed! ");
   e.printStackTrace();
  } catch (WriteException e) {
   createFlag = false;
   System.out.println("EXCEL Write failed! ");
   e.printStackTrace();
  }
  return createFlag;
 }
 public WritableCellFormat getHeaderCellStyle() {
  WritableFont font = new WritableFont(WritableFont.createFont(" Song typeface "), 10,
    WritableFont.BOLD, false, UnderlineStyle.NO_UNDERLINE);
  WritableCellFormat headerFormat = new WritableCellFormat(
    NumberFormats.TEXT);
  try {
   //Add font Settings
   headerFormat.setFont(font);
   //Sets the cell background color: the header is yellow
   headerFormat.setBackground(Colour.YELLOW);
   //Sets the header table border style
   //The line of the whole table is bold and black
   headerFormat.setBorder(Border.ALL, BorderLineStyle.THICK,
     Colour.BLACK);
   //The header content is centered horizontally
   headerFormat.setAlignment(Alignment.CENTRE);
  } catch (WriteException e) {
   System.out.println(" Header cell style setting failed! ");
  }
  return headerFormat;
 }
 public WritableCellFormat getBodyCellStyle() {
  WritableFont font = new WritableFont(WritableFont.createFont(" Song typeface "), 10,
    WritableFont.NO_BOLD, false, UnderlineStyle.NO_UNDERLINE);
  WritableCellFormat bodyFormat = new WritableCellFormat(font);
  try {
   //Sets the cell background color: the table body is white
   bodyFormat.setBackground(Colour.WHITE);
   //Sets the header table border style
   //The whole table line is fine line, black
   bodyFormat
     .setBorder(Border.ALL, BorderLineStyle.THIN, Colour.BLACK);
  } catch (WriteException e) {
   System.out.println(" Table body cell style setting failed! ");
  }
  return bodyFormat;
 }
 public static void main(String[] args) {
  String header = "NO, The name , gender , age ";
  String[] body = new String[4];
  body[0] = "1, Sun feng in Europe , male ,68";
  body[1] = "2, huang , male ,67";
  body[2] = "3, Hong seven , male ,70";
  body[3] = "4, The guo jing , male ,32";
  String filePath = "e:/test.xls";
  JxlTable testJxl = JxlTable.getInstance();
  boolean flag = testJxl.createTable(header, body, filePath);
  if (flag) {
   System.out.println(" Table created successfully!! ");
  }
 }
}

Step-by-step explanation:

Read excel file

To read an excel file, you should first create a wordbook:

Workbook wb=Workbook.getWorkbook(File file);
Workbook wb=Workbook.getWorkbook(InputStream is);

And then you can Get its worksheet :

Sheet[] sheets=wb.getSheets();            //Get all the worksheets
Sheet sheet=wb.getSheet(0);               //Gets the first worksheet.

Finally, you can get the values for certain cells of a worksheet:

Cell cell=sheet.getCell(0,0);          //Get the data for the first column and the first row. The first argument is a column
String value=cell.getContents();    //Gets the value as a string for the cell
String type=cell.getType();           //Gets the data type of the cell.

Close the workbook workflow :
When you are done with the Excel spreadsheet data, be sure to use the close() method to close the previously created object to free up the memory space used in reading the table, which is especially important when reading large amounts of data. Refer to the following code snippet:

wb.close();

Note: this can only be closed after the completion of the operation on the excel, closed in the call such as Sheet s= wb.getsheet (0) will be null.

Common methods are introduced :

The methods provided by the Workbook class
1. Int method getNumberOfSheets ()
Get the number of sheets in the Workbook, for example:

jxl.Workbook rwb = jxl.Workbook.getWorkbook(new File(sourcefile));
int sheets = rwb.getNumberOfSheets();

2. Sheet [] getSheets ()
Returns an array of Sheet objects in the Workbook, for example:

jxl.Workbook rwb = jxl.Workbook.getWorkbook(new File(sourcefile));
Sheet[] sheets = rwb.getSheets();

The Sheet interface provides the method

1. String getName ()
Get the name of the Sheet, example:

jxl.Workbook rwb = jxl.Workbook.getWorkbook(new File(sourcefile));
jxl.Sheet rs = rwb.getSheet(0);
String sheetName = rs.getName();

2. Int getColumns ()
Get 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. The Cell [] getColumn (int column)
Gets all the cells of a column, returns an array of cell objects, example:

jxl.Workbook rwb = jxl.Workbook.getWorkbook(new File(sourcefile));
jxl.Sheet rs = rwb.getSheet(0);
Cell[] cell = rs.getColumn(0);

4. Int getRows ()
Get the total number of rows contained in the Sheet table. Example:

jxl.Workbook rwb = jxl.Workbook.getWorkbook(new File(sourcefile));
jxl.Sheet rs = rwb.getSheet(0);
int rsRows = rs.getRows();

5. The Cell [] getRow (int row)