Package ch.e2e.bridge.server.office
Class SpreadsheetAdapter
- java.lang.Object
-
- ch.e2e.bridge.server.office.SpreadsheetAdapter
-
public class SpreadsheetAdapter extends java.lang.Object
-
-
Field Summary
Fields Modifier and Type Field Description static java.lang.StringDEFAULT_ENCODINGstatic java.lang.StringLINE_SEPARATOR
-
Method Summary
All Methods Static Methods Concrete Methods Modifier and Type Method Description static WorkbookcreateWorkbook(byte[] input)Creates aWorkbookobject from an Excel document.static WorkbookcreateWorkbook(byte[] input, boolean omitEmptyCells)Creates aWorkbookobject from an Excel document.static WorkbookcreateWorkbook(java.lang.String filename)Creates aWorkbookobject from the Excel document referenced byfilename.static WorkbookcreateWorkbook(java.lang.String filename, boolean omitEmptyCells)Creates aWorkbookobject from the Excel document referenced byfilename.static byte[]toCSV(Sheet sheet)The description oftoCSV(String)applies.static byte[]toCSV(Sheet sheet, java.lang.String separator)The description oftoCSV(String, String)applies.static byte[]toCSV(Sheet sheet, java.lang.String separator, java.lang.String encoding)The description oftoCSV(String, String, String)applies.static byte[]toCSV(Workbook workbook)The description oftoCSV(String)applies.static byte[]toCSV(Workbook workbook, int sheetNumber)The description oftoCSV(String, int)applies.static byte[]toCSV(Workbook workbook, int sheetNumber, java.lang.String separator)The description oftoCSV(String, int, String)applies.static byte[]toCSV(Workbook workbook, int sheetNumber, java.lang.String separator, java.lang.String encoding)The description oftoCSV(String, int, String, String)applies.static byte[]toCSV(Workbook workbook, java.lang.String separator)The description oftoCSV(String, String)applies.static byte[]toCSV(Workbook workbook, java.lang.String separator, java.lang.String encoding)The description oftoCSV(Sheet, String, String)applies.static byte[]toCSV(java.lang.String filename)Converts an Excel document to CSV data.static byte[]toCSV(java.lang.String filename, int sheetNumber)The description oftoCSV(String)applies.static byte[]toCSV(java.lang.String filename, int sheetNumber, java.lang.String separator)The description oftoCSV(String)applies.static byte[]toCSV(java.lang.String filename, int sheetNumber, java.lang.String separator, java.lang.String encoding)The description oftoCSV(String)applies.static byte[]toCSV(java.lang.String filename, java.lang.String separator)The description oftoCSV(String)applies.static byte[]toCSV(java.lang.String filename, java.lang.String separator, java.lang.String encoding)The description oftoCSV(String)applies.
-
-
-
Method Detail
-
createWorkbook
public static Workbook createWorkbook(java.lang.String filename) throws java.io.IOException, org.apache.poi.openxml4j.exceptions.InvalidFormatException
Creates aWorkbookobject from the Excel document referenced byfilename. Empty cells/rows are omitted.- Parameters:
filename- the filename of the Excel document- Returns:
- a
Workbookobject - Throws:
java.io.IOExceptionorg.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 aWorkbookobject from the Excel document referenced byfilename.- Parameters:
filename- the filename of the Excel documentomitEmptyCells- if true empty cells/rows are omitted.- Returns:
- a
Workbookobject - Throws:
java.io.IOExceptionorg.apache.poi.openxml4j.exceptions.InvalidFormatException
-
createWorkbook
public static Workbook createWorkbook(byte[] input) throws java.io.IOException, org.apache.poi.openxml4j.exceptions.InvalidFormatException
Creates aWorkbookobject from an Excel document. Empty cells/rows are omitted.- Parameters:
input- the content of the Excel document- Returns:
- a
Workbookobject - Throws:
java.io.IOExceptionorg.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 aWorkbookobject from an Excel document.- Parameters:
input- the content of the Excel documentomitEmptyCells- if true empty cells/rows are omitted.- Returns:
- a
Workbookobject - Throws:
java.io.IOExceptionorg.apache.poi.openxml4j.exceptions.InvalidFormatException
-
toCSV
public static byte[] toCSV(java.lang.String filename) throws java.io.IOException, org.apache.poi.openxml4j.exceptions.InvalidFormatExceptionConverts 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.
___________________________________________ | | | | | | | 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.IOExceptionorg.apache.poi.openxml4j.exceptions.InvalidFormatException
-
toCSV
public static byte[] toCSV(java.lang.String filename, java.lang.String separator) throws java.io.IOException, org.apache.poi.openxml4j.exceptions.InvalidFormatExceptionThe description oftoCSV(String)applies. But the separator is given byseparator.- Parameters:
filename- the filename of the Excel documentseparator- the separator used to separate the fields- Returns:
- UTF-8 encoded CSV data
- Throws:
java.io.IOExceptionorg.apache.poi.openxml4j.exceptions.InvalidFormatException
-
toCSV
public static byte[] toCSV(java.lang.String filename, java.lang.String separator, java.lang.String encoding) throws java.io.IOException, org.apache.poi.openxml4j.exceptions.InvalidFormatExceptionThe description oftoCSV(String)applies. But- - the separator is given by
separator - - the encoding is given by
encoding
- Parameters:
filename- the filename of the Excel documentseparator- the separator used to separate the fieldsencoding- the encoding- Returns:
encodingencoded CSV data- Throws:
java.io.IOExceptionorg.apache.poi.openxml4j.exceptions.InvalidFormatException
- - the separator is given by
-
toCSV
public static byte[] toCSV(java.lang.String filename, int sheetNumber) throws java.io.IOException, org.apache.poi.openxml4j.exceptions.InvalidFormatExceptionThe description oftoCSV(String)applies. But- - the worksheet specified by
sheetNumberis converted to CSV data
- Parameters:
filename- the filename of the Excel documentsheetNumber- the number of the worksheet to be converted- Returns:
- UTF-8 encoded CSV data
- Throws:
java.io.IOExceptionorg.apache.poi.openxml4j.exceptions.InvalidFormatException
- - the worksheet specified by
-
toCSV
public static byte[] toCSV(java.lang.String filename, int sheetNumber, java.lang.String separator) throws java.io.IOException, org.apache.poi.openxml4j.exceptions.InvalidFormatExceptionThe description oftoCSV(String)applies. But- - the worksheet specified by
sheetNumberis converted to CSV data - - the separator is given by
separator
- Parameters:
filename- the filename of the Excel documentsheetNumber- the number of the worksheet to be convertedseparator- the separator used to separate the fields- Returns:
- UTF-8 encoded CSV data
- Throws:
java.io.IOExceptionorg.apache.poi.openxml4j.exceptions.InvalidFormatException
- - the worksheet specified by
-
toCSV
public static byte[] toCSV(java.lang.String filename, int sheetNumber, java.lang.String separator, java.lang.String encoding) throws java.io.IOException, org.apache.poi.openxml4j.exceptions.InvalidFormatExceptionThe description oftoCSV(String)applies. But- - the worksheet specified by
sheetNumberis converted to CSV data - - the separator is given by
separator - - the encoding is given by
encoding
- Parameters:
filename- the filename of the Excel documentsheetNumber- the number of the worksheet to be convertedseparator- the separator used to separate the fieldsencoding- the encoding- Returns:
encodingencoded CSV data- Throws:
java.io.IOExceptionorg.apache.poi.openxml4j.exceptions.InvalidFormatException
- - the worksheet specified by
-
toCSV
public static byte[] toCSV(Workbook workbook) throws java.io.IOException
The description oftoCSV(String)applies.- Parameters:
workbook- theWorkbookobject referencing the Excel document.- Returns:
- UTF-8 encoded CSV data
- Throws:
java.io.IOException
-
toCSV
public static byte[] toCSV(Workbook workbook, java.lang.String separator) throws java.io.IOException
The description oftoCSV(String, String)applies.- Parameters:
workbook- theWorkbookobject referencing the Excel document.separator- the separator used to separate the fields- Returns:
- UTF-8 encoded CSV data
- Throws:
java.io.IOException
-
toCSV
public static byte[] toCSV(Workbook workbook, java.lang.String separator, java.lang.String encoding) throws java.io.IOException
The description oftoCSV(Sheet, String, String)applies.- Parameters:
workbook- theWorkbookobject referencing the Excel document.separator- the separator used to separate the fieldsencoding- the encoding- Returns:
encodingencoded CSV data- Throws:
java.io.IOException
-
toCSV
public static byte[] toCSV(Workbook workbook, int sheetNumber) throws java.io.IOException
The description oftoCSV(String, int)applies.- Parameters:
workbook- theWorkbookobject referencing the Excel document.sheetNumber- the number of the worksheet to be converted- Returns:
- UTF-8 encoded CSV data
- Throws:
java.io.IOException
-
toCSV
public static byte[] toCSV(Workbook workbook, int sheetNumber, java.lang.String separator) throws java.io.IOException
The description oftoCSV(String, int, String)applies.- Parameters:
workbook- theWorkbookobject referencing the Excel document.sheetNumber- the number of the worksheet to be convertedseparator- the separator used to separate the fields- Returns:
- UTF-8 encoded CSV data
- Throws:
java.io.IOException
-
toCSV
public static byte[] toCSV(Workbook workbook, int sheetNumber, java.lang.String separator, java.lang.String encoding) throws java.io.IOException
The description oftoCSV(String, int, String, String)applies.- Parameters:
workbook- theWorkbookobject referencing the Excel document.sheetNumber- the number of the worksheet to be convertedseparator- the separator used to separate the fieldsencoding- the encoding- Returns:
encodingencoded CSV data- Throws:
java.io.IOException
-
toCSV
public static byte[] toCSV(Sheet sheet) throws java.io.IOException
The description oftoCSV(String)applies.- Parameters:
sheet- theSheetobject referencing the Excel worksheet- Returns:
- UTF-8 encoded CSV data
- Throws:
java.io.IOException
-
toCSV
public static byte[] toCSV(Sheet sheet, java.lang.String separator) throws java.io.IOException
The description oftoCSV(String, String)applies.- Parameters:
sheet- theSheetobject referencing the Excel worksheetseparator- the separator used to separate the fields- Returns:
- UTF-8 encoded CSV data
- Throws:
java.io.IOException
-
toCSV
public static byte[] toCSV(Sheet sheet, java.lang.String separator, java.lang.String encoding) throws java.io.IOException
The description oftoCSV(String, String, String)applies.- Parameters:
sheet- theSheetobject referencing the Excel worksheetseparator- the separator used to separate the fieldsencoding- the encoding- Returns:
encodingencoded CSV data- Throws:
java.io.IOException
-
-