Class SpreadsheetAdapter


  • public class SpreadsheetAdapter
    extends java.lang.Object
    • Field Detail

      • DEFAULT_ENCODING

        public static final java.lang.String DEFAULT_ENCODING
      • LINE_SEPARATOR

        public static final java.lang.String LINE_SEPARATOR
    • Method Detail

      • createWorkbook

        public static Workbook createWorkbook​(java.lang.String filename)
                                       throws java.io.IOException,
                                              org.apache.poi.openxml4j.exceptions.InvalidFormatException
        Creates a Workbook object from the Excel document referenced by filename. Empty cells/rows are omitted.
        Parameters:
        filename - the filename of the Excel document
        Returns:
        a Workbook object
        Throws:
        java.io.IOException
        org.apache.poi.openxml4j.exceptions.InvalidFormatException
      • createWorkbook

        public static Workbook createWorkbook​(java.lang.String filename,
                                              boolean omitEmptyCells)
                                       throws java.io.IOException,
                                              org.apache.poi.openxml4j.exceptions.InvalidFormatException
        Creates a Workbook object from the Excel document referenced by filename.
        Parameters:
        filename - the filename of the Excel document
        omitEmptyCells - if true empty cells/rows are omitted.
        Returns:
        a Workbook object
        Throws:
        java.io.IOException
        org.apache.poi.openxml4j.exceptions.InvalidFormatException
      • createWorkbook

        public static Workbook createWorkbook​(byte[] input)
                                       throws java.io.IOException,
                                              org.apache.poi.openxml4j.exceptions.InvalidFormatException
        Creates a Workbook object from an Excel document. Empty cells/rows are omitted.
        Parameters:
        input - the content of the Excel document
        Returns:
        a Workbook object
        Throws:
        java.io.IOException
        org.apache.poi.openxml4j.exceptions.InvalidFormatException
      • createWorkbook

        public static Workbook createWorkbook​(byte[] input,
                                              boolean omitEmptyCells)
                                       throws java.io.IOException,
                                              org.apache.poi.openxml4j.exceptions.InvalidFormatException
        Creates a Workbook object from an Excel document.
        Parameters:
        input - the content of the Excel document
        omitEmptyCells - if true empty cells/rows are omitted.
        Returns:
        a Workbook object
        Throws:
        java.io.IOException
        org.apache.poi.openxml4j.exceptions.InvalidFormatException
      • toCSV

        public static byte[] toCSV​(java.lang.String filename)
                            throws java.io.IOException,
                                   org.apache.poi.openxml4j.exceptions.InvalidFormatException
        Converts an Excel document to CSV data. This method makes the following assumptions:
      • 1. If the Excel workbook contains more that one worksheet, then only the first one will be converted.
      • 2. The data matrix contained in the CSV file will be square. This means that the number of fields in each record of the CSV file will match the number of cells in the longest row found in the Excel workbook. Any short records will be 'padded' with empty fields - an empty field is represented in the the CSV file in this way - ,,.
      • 3. Empty fields will represent missing cells.
      • 4. A record consisting of empty fields will be used to represent an empty row in the Excel workbook.
      • Therefore, if the worksheet looked like this;

          ___________________________________________
             |       |       |       |       |       |
             |   A   |   B   |   C   |   D   |   E   |
          ___|_______|_______|_______|_______|_______|
             |       |       |       |       |       |
           1 |   1   |   2   |   3   |   4   |   5   |
          ___|_______|_______|_______|_______|_______|
             |       |       |       |       |       |
           2 |       |       |       |       |       |
          ___|_______|_______|_______|_______|_______|
             |       |       |       |       |       |
           3 |       |   A   |       |   B   |       |
          ___|_______|_______|_______|_______|_______|
             |       |       |       |       |       |
           4 |       |       |       |       |   Z   |
          ___|_______|_______|_______|_______|_______|
             |       |       |       |       |       |
           5 | 1,400 |       |  250  |       |       |
          ___|_______|_______|_______|_______|_______|
        
         

        Then, the resulting CSV file will contain the following lines (records);

         1,2,3,4,5
         ,,,,
         ,A,,B,
         ,,,,Z
         "1,400",,250,,
         

        The comma is used to separate each of the fields that, together, constitute a single record or line within the CSV file.

        If a field contains the separator then it will be escaped.

        If a field contains an end of line (EOL) character then it too will be escaped.

        If the field contains double quotes then that character will be escaped. An enclosing set of speech marks will also surround the entire field. Thus, if the following line of text appeared in a cell - "Hello" he said - it would look like this when converted into a field within a CSV file - """Hello"" he said".

Parameters:
filename - the filename of the Excel document
Returns:
UTF-8 encoded CSV data
Throws:
java.io.IOException
org.apache.poi.openxml4j.exceptions.InvalidFormatException
Parameters:
filename - the filename of the Excel document
separator - the separator used to separate the fields
encoding - the encoding
Returns:
encoding encoded CSV data
Throws:
java.io.IOException
org.apache.poi.openxml4j.exceptions.InvalidFormatException