public class WorkbookUtility
extends java.lang.Object
Constructor and Description |
---|
WorkbookUtility() |
Modifier and Type | Method and Description |
---|---|
static void |
addImage(org.apache.poi.ss.usermodel.Workbook wb,
org.apache.poi.ss.usermodel.Sheet sheet,
int type,
byte[] imageBytes,
int x1,
int y1,
int x2,
int y2)
Method which adds image to sheet.
|
static org.apache.poi.ss.usermodel.Workbook |
createWorkbook(byte[] data)
Method which creates workbook from byte array.
|
static void |
evaluateWorkbook(org.apache.poi.ss.usermodel.Workbook wb)
Method which evaluates all cells, which contain formula, in workbook.
|
static int |
findIndexOfRowWithCellValue(java.lang.String value,
int valueIndexInSheet,
org.apache.poi.ss.usermodel.Sheet sheet)
Method which returns index of first row which contains cell with given value.
|
static java.util.List<org.apache.poi.ss.usermodel.Row> |
findRowsWithCellValue(java.lang.String value,
int valueIndexInSheet,
org.apache.poi.ss.usermodel.Sheet sheet)
Method which finds rows which contain given value at given index.
|
static java.util.List<org.apache.poi.ss.usermodel.Row> |
findRowsWithCellValuePair(java.lang.String firstValue,
int firstIndex,
java.util.HashSet<java.lang.Long> collectionAPU,
int secondIndex,
org.apache.poi.ss.usermodel.Sheet sheet)
Method which finds rows which, at firstIndex, contain firstValue and, at secondIndex, one of values in given set.
|
static java.lang.String |
getCellFormula(org.apache.poi.ss.usermodel.Cell cell)
Method which returns formula of cell.
|
static java.lang.String |
getCellFormula(org.apache.poi.ss.usermodel.Sheet sheet,
org.apache.poi.ss.util.CellReference cellRef)
Method which returns formula of cell.
|
static java.lang.Object |
getCellValue(org.apache.poi.ss.usermodel.Cell cell,
int cellType)
Method which returns value of cell.
|
static java.lang.Object |
getCellValue(org.apache.poi.ss.usermodel.Sheet sheet,
org.apache.poi.ss.util.CellReference cellRef)
Method which returns value of cell.
|
static org.apache.poi.ss.usermodel.Row |
getRow(org.apache.poi.ss.usermodel.Sheet sheet,
int index)
Method which gets row of sheet at index index.
|
static void |
hideColumns(org.apache.poi.ss.usermodel.Sheet sheet,
int startingIndex,
int endingIndex)
Method which hides columns of workbook in range [startingIndex, endingIndex].
|
static void |
hideRows(org.apache.poi.ss.usermodel.Sheet sheet,
int startingIndex,
int endingIndex)
Method which hides rows of workbook in range [startingIndex, endingIndex].
|
static org.apache.poi.ss.usermodel.Workbook |
loadWorkbook(java.lang.String source)
Method which loads workbook from file.
|
static org.apache.poi.ss.usermodel.Workbook |
loadWorkbookXLSM(java.lang.String source)
Method which loads workbook from file.
|
static void |
setCellFormula(org.apache.poi.ss.usermodel.Row row,
int index,
java.lang.String cellFormula)
Method which sets cell formula at given row and index.
|
static void |
setCellFormula(org.apache.poi.ss.usermodel.Sheet sheet,
org.apache.poi.ss.util.CellReference cellRef,
java.lang.String cellFormula)
Method which sets cell formula at given cell reference
|
static byte[] |
toByteArray(org.apache.poi.ss.usermodel.Workbook wb)
Method which outputs workbook content to byte array.
|
static void |
writeCell(org.apache.poi.ss.usermodel.Row row,
int index,
java.math.BigDecimal value)
Method which writes in cell at certain row and index.
|
static void |
writeCell(org.apache.poi.ss.usermodel.Row row,
int index,
boolean value)
Method which writes in cell at certain row and index.
|
static void |
writeCell(org.apache.poi.ss.usermodel.Row row,
int index,
java.util.Date value)
Method which writes in cell at certain row and index.
|
static void |
writeCell(org.apache.poi.ss.usermodel.Row row,
int index,
double value)
Method which writes in cell at certain row and index.
|
static void |
writeCell(org.apache.poi.ss.usermodel.Row row,
int index,
java.lang.Object value)
Method which redirects to another depending on value type.
|
static void |
writeCell(org.apache.poi.ss.usermodel.Row row,
int index,
java.lang.String value)
Method which writes in cell at certain row and index.
|
static void |
writeCell(org.apache.poi.ss.usermodel.Sheet sheet,
org.apache.poi.ss.util.CellReference cellRef,
java.math.BigDecimal value)
Method which writes in cell at given cell reference.
|
static void |
writeCell(org.apache.poi.ss.usermodel.Sheet sheet,
org.apache.poi.ss.util.CellReference cellRef,
java.util.Date value)
Method which writes in cell at given cell reference.
|
static void |
writeCell(org.apache.poi.ss.usermodel.Sheet sheet,
org.apache.poi.ss.util.CellReference cellRef,
double value)
Method which writes in cell at given cell reference.
|
static void |
writeCell(org.apache.poi.ss.usermodel.Sheet sheet,
org.apache.poi.ss.util.CellReference cellRef,
java.lang.String value)
Method which writes in cell at given cell reference.
|
public static org.apache.poi.ss.usermodel.Workbook createWorkbook(byte[] data) throws java.io.IOException
data
- array of bytes.java.io.IOException
public static org.apache.poi.ss.usermodel.Workbook loadWorkbook(java.lang.String source) throws java.io.IOException
source
- location of file.java.io.IOException
public static org.apache.poi.ss.usermodel.Workbook loadWorkbookXLSM(java.lang.String source) throws java.io.IOException, org.apache.poi.openxml4j.exceptions.InvalidFormatException
source
- location of file.java.io.IOException
org.apache.poi.openxml4j.exceptions.InvalidFormatException
public static void evaluateWorkbook(org.apache.poi.ss.usermodel.Workbook wb)
wb
- reference to workbook to be evaulated.public static byte[] toByteArray(org.apache.poi.ss.usermodel.Workbook wb) throws hr.pardus.lims.exceptions.InternaPogreskaException
wb
- workbook to be outputed to byte array.hr.pardus.lims.exceptions.InternaPogreskaException
- occurs when there is error while trying to convert workbook to byte array.public static java.lang.Object getCellValue(org.apache.poi.ss.usermodel.Cell cell, int cellType)
cell
- reference to cell.cellType
- type of cell. (cell.getCellType() method)public static java.lang.Object getCellValue(org.apache.poi.ss.usermodel.Sheet sheet, org.apache.poi.ss.util.CellReference cellRef)
sheet
- reference to sheet.cellRef
- cell reference to cell in sheet.getCellValue(Cell, int)
public static java.lang.String getCellFormula(org.apache.poi.ss.usermodel.Cell cell)
cell
- reference to cell.public static java.lang.String getCellFormula(org.apache.poi.ss.usermodel.Sheet sheet, org.apache.poi.ss.util.CellReference cellRef)
sheet
- reference to sheet.cellRef
- reference to cell which contains location of cell.getCellFormula(Cell).
public static org.apache.poi.ss.usermodel.Row getRow(org.apache.poi.ss.usermodel.Sheet sheet, int index)
sheet
- reference to sheet.index
- index of row.public static int findIndexOfRowWithCellValue(java.lang.String value, int valueIndexInSheet, org.apache.poi.ss.usermodel.Sheet sheet)
value
- value of cell to be found.valueIndexInSheet
- index of cell in row.sheet
- reference to worksheet which will be searched.public static java.util.List<org.apache.poi.ss.usermodel.Row> findRowsWithCellValue(java.lang.String value, int valueIndexInSheet, org.apache.poi.ss.usermodel.Sheet sheet)
value
- value to be found in form of string.valueIndexInSheet
- column index in sheet which values will be searched.sheet
- reference to sheet.public static java.util.List<org.apache.poi.ss.usermodel.Row> findRowsWithCellValuePair(java.lang.String firstValue, int firstIndex, java.util.HashSet<java.lang.Long> collectionAPU, int secondIndex, org.apache.poi.ss.usermodel.Sheet sheet)
firstValue
- value of cell searched at index firstIndex.firstIndex
- firstIndex in sheet.collectionAPU
- set which contains list of strings which will be searched at second index.secondIndex
- secondIndex in sheet.sheet
- reference to sheet which will be searched.public static void writeCell(org.apache.poi.ss.usermodel.Row row, int index, double value)
row
- reference to row.index
- index of cell in row.value
- value to be written into cell in row at index.public static void writeCell(org.apache.poi.ss.usermodel.Row row, int index, java.math.BigDecimal value)
row
- reference to row.index
- index of cell in row.value
- value to be written into cell in row at index.public static void writeCell(org.apache.poi.ss.usermodel.Row row, int index, java.lang.String value)
row
- reference to row.index
- index of cell in row.value
- value to be written into cell in row at index.public static void writeCell(org.apache.poi.ss.usermodel.Row row, int index, java.lang.Object value)
row
- reference to rowindex
- index of cell in rowvalue
- value to be written at specified placepublic static void writeCell(org.apache.poi.ss.usermodel.Row row, int index, java.util.Date value)
row
- reference to row.index
- index of cell in row.value
- value in form of date to be written into cell in row at index.public static void writeCell(org.apache.poi.ss.usermodel.Row row, int index, boolean value)
row
- reference to row.index
- index of cell in row.value
- value in form of date to be written into cell in row at index.public static void writeCell(org.apache.poi.ss.usermodel.Sheet sheet, org.apache.poi.ss.util.CellReference cellRef, double value)
sheet
- reference to sheet.cellRef
- reference to cellrefecence which contains location of cell.value
- value to be written into cell.public static void writeCell(org.apache.poi.ss.usermodel.Sheet sheet, org.apache.poi.ss.util.CellReference cellRef, java.math.BigDecimal value)
sheet
- reference to sheet.cellRef
- reference to cellrefecence which contains location of cell.value
- value to be written into cell.public static void writeCell(org.apache.poi.ss.usermodel.Sheet sheet, org.apache.poi.ss.util.CellReference cellRef, java.lang.String value)
sheet
- reference to sheet.cellRef
- reference to cellrefecence which contains location of cell.value
- value to be written into cell.public static void writeCell(org.apache.poi.ss.usermodel.Sheet sheet, org.apache.poi.ss.util.CellReference cellRef, java.util.Date value)
sheet
- reference to sheet.cellRef
- reference to cellrefecence which contains location of cell.value
- value to be written into cell.public static void setCellFormula(org.apache.poi.ss.usermodel.Row row, int index, java.lang.String cellFormula)
row
- reference to row.index
- index of cell in row.cellFormula
- cell formula to be set to cell.public static void setCellFormula(org.apache.poi.ss.usermodel.Sheet sheet, org.apache.poi.ss.util.CellReference cellRef, java.lang.String cellFormula)
sheet
- reference to sheet.cellRef
- reference to cell which contains location of cell.cellFormula
- formula to be set into cell at given cell reference.public static void addImage(org.apache.poi.ss.usermodel.Workbook wb, org.apache.poi.ss.usermodel.Sheet sheet, int type, byte[] imageBytes, int x1, int y1, int x2, int y2)
wb
- reference to workbook to add image to.sheet
- reference to sheet to add image to.type
- type of image.imageBytes
- byte array of image.x1
- index of column where top left corner of picture will be placed.y1
- index of row where top left corner of picture will be placed.x2
- index of column where bottom right corner of picture will be placed.y2
- index of row where bottom right corner of picture will be placed.Workbook#PICTURE_TYPE_DIB}
,
Workbook#PICTURE_TYPE_EMF}
,
Workbook#PICTURE_TYPE_JPEG}
,
Workbook#PICTURE_TYPE_PICT}
,
Workbook#PICTURE_TYPE_PNG}
,
Workbook#PICTURE_TYPE_WMF}
public static void hideRows(org.apache.poi.ss.usermodel.Sheet sheet, int startingIndex, int endingIndex)
sheet
- reference to sheet of workbook.startingIndex
- index of starting row to hide.endingIndex
- index of ending row to hide.public static void hideColumns(org.apache.poi.ss.usermodel.Sheet sheet, int startingIndex, int endingIndex)
sheet
- reference to sheet of workbook.startingIndex
- index of starting column to hide.endingIndex
- index of ending column to hide.