Working with an Excel spreadsheet from Java

    Working with an Excel spreadsheet from Java


    Actually, a problem arose - to process the data from the table and based on them to get another table.

    There are several solutions to this problem:
    1. Macro - the only problem is VBA, for the study of which there is no time at all, and its syntax is not like
    2. An application in C # here everything seems to be fine, but the machine on which this application will be run immediately has many additional requirements:
      • .NET Framework
      • Installed Office
      • Installed Primary Interoperability Assembly (PIA) for the Office application

    3. Java bundle and Apache POI library — I want to dwell on this method in more detail


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

    Actually, the POI-HSSF project is intended for working with Excel tables . At the moment there are 2 library options:
    1. POI 3.5 beta 5, and Office Open XML Support (2009-02-19) —will 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 table is the HSSFWorkbook class of the org.apache.poi.hssf.usermodel package, which represents an Excel workbook.

    To read a book from a file, you can use the following code: The method returns an object of the HSSFWorkbook class if everything is successful and null otherwise. The following method can be used to save changes: The method writes the wb workbook to fileName. The HSSFSheet class is designed to work with workbook sheets. An object of the HSSFSheet class can be obtained in the following ways:
    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 void writeWorkbook(HSSFWorkbook wb, String fileName) {
           try {
                   FileOutputStream fileOut = new FileOutputStream(fileName);
                   wb.write(fileOut);
                   fileOut.close();
           }
           catch (Exception e) {
                   //Обработка ошибки
           }
    }        







    • By name
      HSSFSheet sheet= wb.getSheet("Лист 3")
    • By number (numbering starts from 0)
      HSSFSheet sheet= wb.getSheet(0)
    • Create a new sheet
      HSSFSheet sheet= wb.createSheet(["имя листа"])


    The next object is the line - HSSFRow.
    You can also access the string object in 3 ways:
    • By index (indexing starts at 0)
      HSSFRow row = sheet.getRow(index)
    • Through an iterator
      Iterator rowIter = sheet.rowIterator();
      while (rowIter.hasNext()) {
             HSSFRow row = (HSSFRow) rowIter.next();
      }


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


    Well, the last object on the data access path is an object of the HSSFCell class - a row cell.
    You can access the cell from the row object in the following ways:
    • By cell index (indexing starts at 0)
      HSSFCell cell = row.getCell(0);
    • Through an iterator
      Iterator cellIter = row.cellIterator();
      while (cellIter.hasNext()) {
             HSSFCell cell = (HSSFCell) cellIter.next();
      }
      


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


    You can already receive and set data directly from the cell object:
    • Boolean value
      boolean b = cell.getBooleanCellValue();
      cell.setCellValue(b);

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

    • Numerical 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 process simple tables.
    The library also provides rich opportunities 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.


    Also popular now: