Work with Excel spreadsheet from Java

Actually there was a problem - to process the data from the table and on the basis of them get another table.

There are several options for solving this problem:

1. Macro - the only problem is VBA, on the study of which there is no time at all, and I don’t like its syntax
2. The C # application here seems to be all right, but the machine on which the application will run will immediately have many additional requirements:
   
   • .NET Framework
   • Established office
   • installed primary interop assembly (PIA) for an Office application
   
   
3. a bunch of Java and the Apache POI library — I want to dwell on this method

Apache POI is a Java API for accessing Microsoft-format documents.

Actually, the POI-HSSF project is intended for working with Excel spreadsheets. At the moment there are 2 library options:

1. POI 3.5 beta 5, and Office Open XML Support (2009-02-19) —Work on support for the Office 2007 format
2. POI 3.2-FINAL Released (2008-10-19) - latest stable release

')
I will talk about working with version 3.2
The main class for working with an Excel spreadsheet is the HSSFWorkbook class of the org.apache.poi.hssf.usermodel package, which represents the Excel workbook.

To read a book from a file, you can apply the following code:

 public static HSSFWorkbook readWorkbook(String filename) { try { POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(filename)); HSSFWorkbook wb = new HSSFWorkbook(fs); return wb; } catch (Exception e) { return null; } } 
 

public static HSSFWorkbook readWorkbook(String filename) { try { POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(filename)); HSSFWorkbook wb = new HSSFWorkbook(fs); return wb; } catch (Exception e) { return null; } }


The method returns an object of the HSSFWorkbook class if everything is successful and null otherwise.

To save changes, you can use the following method:

 public static void writeWorkbook(HSSFWorkbook wb, String fileName) { try { FileOutputStream fileOut = new FileOutputStream(fileName); wb.write(fileOut); fileOut.close(); } catch (Exception e) { //  } } 
 

public static void writeWorkbook(HSSFWorkbook wb, String fileName) { try { FileOutputStream fileOut = new FileOutputStream(fileName); wb.write(fileOut); fileOut.close(); } catch (Exception e) { // } }


Method writes wb book to fileName

The class HSSFSheet is intended for working with workbook sheets.
The object of the HSSFSheet class can be obtained in the following ways:

• By name
  HSSFSheet sheet= wb.getSheet(" 3")
  
• By number (numbering starts from 0)
  HSSFSheet sheet= wb.getSheet(0)
  
• Creating a new sheet
  HSSFSheet sheet= wb.createSheet([" "])
  

The next object is the row - HSSFRow.
You can also access the string object in 3 ways:

• By index (indexing starts at 0)
  HSSFRow row = sheet.getRow(index)
  
• Through iterator
  

    Iterator rowIter = sheet.rowIterator ();
   while (rowIter.hasNext ()) {
          HSSFRow row = (HSSFRow) rowIter.next ();
   } 

  
  
  
• Creating a new line
  HSSFRow row=sheet.createRow(0);
  

Well, the last object in the data access path is an object of the HSSFCell class - a cell of the row.
You can access the cell from the row object in the following ways:

• By cell index (indexing starts from 0)
  HSSFCell cell = row.getCell(0);
  
• Through iterator
  

   Iterator cellIter = row.cellIterator ();
   while (cellIter.hasNext ()) {
          HSSFCell cell = (HSSFCell) cellIter.next ();
   }
  

  
  
  
• Creating a new cell
  HSSFCell cell = row.createCell(0);
  

From the cell object, you can already get and set data directly:

• Boolean value
  boolean b = cell.getBooleanCellValue();
cell.setCellValue(b);

  
• date
  Date date = cell.getDateCellValue();
cell.setCellValue(date);

  
• Numeric value
  double d = cell.getNumericCellValue();
cell.setCellValue(d);

  
• String value
  String str = cell.getRichStringCellValue().getString();
cell.setCellValue(new HSSFRichTextString(str));

  
• Formula
  String formula = cell.getCellFormula();
cell.setCellFormula(formula);

  

This knowledge is enough to handle simple tables.
The library also provides rich possibilities for formatting cells, for merging, freezing, etc.
A detailed description of the functions can be found on their website.
This method is primarily valuable in that it does not require the installation of the office itself and the PIA package.