Use jxl and poi to create/parse Excel files and jxlpoi

Use jxl and poi to create/parse Excel files and jxlpoi

A few days ago, the company's website had a new demand. You need to export data to an Excel file with one click, or directly Insert the data in the Excel file to the database to view the one-day api documentation and Baidu. I also had a little experience and shared it with you. comment out any errors.

In this experiment, the jxl and POI methods are parsed.

The jar packages and toolkit used in the experiment are all in my Baidu cloud. You can download them and click the link to open them.

I. jxl

1. Jxl is a pure Java API that performs perfectly across platforms. The code can run on windows or Linux without having to be rewritten.
2. Support all versions of Excel 95-2000 (I have not tried it yet)
3. Generate standard Excel 2000 format
4. Supports font, number, and date operations
5. Ability to modify cell attributes
6. Images and charts are supported, but this API has limited support for graphics and charts and only recognizes PNG formats.

Disadvantages: low efficiency, incomplete image support, and less powerful format support than POI

Ii. POI

1. High efficiency, but complicated to write.
2. Supported formulas and macros
3. Ability to modify cell attributes
4. Supports one-click conversion of fonts, numbers, and dates

Jxl method:

/*** Create an Excel Workbook with jxl and write data * @ param fullPath */public static void writeExcel (String fullPath) {// define the header title String [] labelTitle = new String [] {"ID", "name", "gender", "Age "}; file file = new File (fullPath); // defines the table cell format WritableCellFormat cellFormat = new WritableCellFormat (); try {// horizontally centered cellFormat. setAlignment (Alignment. CENTRE); // vertically centered cellFormat. setVerticalAlignment (verticalignment. CENTRE); // create a workbook Instance WritableWorkbook workbook = Workbook. createWorkbook (file); // create the sheet page parameter: "sheet1" -- sheet Page name, 0 -- sheet Index WritableSheet sheet = workbook. createSheet ("sheet1", 0); // Text object Label label = null; for (int j = 0; j <labelTitle. length; j ++) {// parameter -- column, row, content label = new Label (j, 0, labelTitle [j], cellFormat); // Add a cell sheet. addCell (label) ;}for (int I = 1; I <= 10; I ++) {label = new Label (0, I, "" + I, c EllFormat); sheet. addCell (label); label = new Label (1, I, "", cellFormat); sheet. addCell (label); label = new Label (2, I, "male", cellFormat); sheet. addCell (label); label = new Label (3, I, "" + (I + 20), cellFormat); sheet. addCell (label);} // write to the workbook. write (); // close the write stream workbook. close (); System. out. println ("Write finished! ");} Catch (Exception e) {e. printStackTrace ();}}

Experiment results:

 

Parsing Excel files

Public static void readExcel (String filePath) throws BiffException, IOException {File file = new File (filePath); if (! File. exists () {throw new FileNotFoundException ("file not found");} // create a workbook Workbook = workbook. getWorkbook (file); // get the first sheet page Sheet sheet = workbook. getSheet (0);/** cyclically traverse each cell * sheet. getRows () Get the number of rows in the sheet page * sheet. getColumns () Get the number of columns in the sheet page * sheet. getCell () gets the cell * sheet. getContents () Get the cell content */for (int I = 0; I <sheet. getRows (); I ++) {for (int j = 0; j <sheet. getColumns (); j ++) {// parameter list ---------------- column, row Cell = sheet. getCell (j, I); System. out. print (cell. getContents () + ",");} System. out. println ();} workbook. close ();}

Experiment results:

POI mode:

/*** Create an Excel Workbook using POI * @ param fullPath * @ throws IOException */public static void CreateExcel (String fullPath) throws IOException {String [] title = new String [] {"id", "name", "age"}; // create an Excel workbook HSSFWorkbook workbook = new HSSFWorkbook (); // create the sheet page HSSFSheet sheet = workbook. createSheet (); // create a row. The first row is 0 HSSFRow row = sheet. createRow (0); HSSFCell cell = null; for (int I = 0; I <title. length; I ++) {// create cell = row. createCell (I); // set the cell value. setCellValue (title [I]);} HSSFCell nextCell = null; for (int I = 1; I <= 10; I ++) {HSSFRow nextRow = sheet. createRow (I); nextCell = nextRow. createCell (0); nextCell. setCellValue (I); nextCell = nextRow. createCell (1); nextCell. setCellValue ("Zhang San" + I); nextCell = nextRow. createCell (2); nextCell. setCellValue (30 + I);} // create the File file File = new File (fullPath); // create the output stream FileOutputStream stream = FileUtils. openOutputStream (file); // write to the workbook. write (stream); // stream. close (); workbook. close ();}

Experiment results:

/*** POI resolution Excel Workbook * @ param fullPath * @ throws IOException */public static void readExcel (String fullPath) throws IOException {File file File = new File (fullPath ); if (! File. exists () {throw new FileNotFoundException ("this file does not exist");} // obtain the workbook HSSFWorkbook workbook = new HSSFWorkbook (FileUtils. openInputStream (file); // obtain the sheet page HSSFSheet = workbook through the index. getSheetAt (0); // get the last row int rowIndex = sheet. getLastRowNum (); HSSFRow row; short lastCellIndex; SimpleDateFormat sdf = new SimpleDateFormat ("yyyy-mm-dd"); DecimalFormat df = new DecimalFormat ("0 "); for (int I = 0; I <rowIndex; I ++) {row = sheet. getRow (I); // get the last number of cell columns lastCellIndex = row. getLastCellNum (); for (int j = 0; j <lastCellIndex; j ++) {HSSFCell cell = row. getCell (j); switch (cell. getCellType () {case HSSFCell. CELL_TYPE_NUMERIC: // number if (HSSFDateUtil. isCellDateFormatted (cell) {Date date = cell. getDateCellValue (); if (date! = Null) {System. out. print (sdf. format (date);} else {System. out. print ("") ;}} else {System. out. print (df. format (cell. getNumericCellValue () + "");} break; case HSSFCell. CELL_TYPE_STRING: // string System. out. print (cell. getStringCellValue () + ""); break; case HSSFCell. CELL_TYPE_BOOLEAN: // Boolean System. out. print (cell. getBooleanCellValue () + ""); break; case HSSFCell. CELL_TYPE_BLANK: // null value System. out. print (""); break; case HSSFCell. CELL_TYPE_FORMULA: // formula System. out. print (cell. getCellFormula () + ""); break; case HSSFCell. CELL_TYPE_ERROR: // error System. out. print ("invalid character"); break; default: System. out. print ("unknown type") ;}} System. out. println ();}}

Experiment results:

View comments