private Object getCellValue(Cell cell, FormulaEvaluator evaluator)
throws ExcelManipulateException {
if (cell == null) return null;
// log.debug("Read Value for: " + ExcelUtil.getCellIndex(cell.getRowIndex(),
// cell.getColumnIndex()));
Object value = null;
CellValue cellValue = evaluator.evaluate(cell);
if (cellValue == null) {
logger.debug("{}: null", ExcelUtil.getCellIndex(cell.getRowIndex(), cell.getColumnIndex()));
return null;
}
switch (cellValue.getCellType()) {
case Cell.CELL_TYPE_BLANK:
case Cell.CELL_TYPE_ERROR:
break;
case Cell.CELL_TYPE_BOOLEAN:
value = cellValue.getBooleanValue();
break;
case Cell.CELL_TYPE_NUMERIC:
if (DateUtil.isCellDateFormatted(cell)) {
value = DateUtil.getJavaDate(cellValue.getNumberValue());
} else value = cellValue.getNumberValue();
break;
case Cell.CELL_TYPE_STRING:
value = cellValue.getStringValue();
}
logger.debug(
"{}: {}", ExcelUtil.getCellIndex(cell.getRowIndex(), cell.getColumnIndex()), value);
return value;
}
@Override
public void close() {
try {
int nbS = wb.getNumberOfSheets();
for (int i = 0; i < nbS; i++) {
Sheet s = wb.getSheetAt(i);
if (s.getRow(0)
!= null) { // ceci arrive si on vide la mémoire tampon pour les grands fichiers. Dans ce
// cas pas de possibilité de traiter la mise en page de gros fichiers
for (int j = 0; j < s.getRow(0).getLastCellNum(); j++) {
Cell c = s.getRow(0).getCell(j);
c.getSheet().autoSizeColumn(c.getColumnIndex());
}
Cell firstCell = s.getRow(0).getCell(0);
Cell lastCell = s.getRow(0).getCell((int) s.getRow(0).getLastCellNum() - 1);
s.setAutoFilter(
new CellRangeAddress(
firstCell.getRowIndex(),
lastCell.getRowIndex(),
lastCell.getRowIndex(),
lastCell.getColumnIndex()));
}
}
wb.write(fileOut);
fileOut.flush();
fileOut.close();
} catch (FileNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
public void printWorksheetContent() throws Exception {
FrontDesk desk = FrontDesk.getInstance();
// read policy & rule
DynamicPolicy policy = SifMarshaller.unmarshal(new File(policyPath));
@SuppressWarnings("unchecked")
DynamicInspectionRequest<Workbook> req =
(DynamicInspectionRequest<Workbook>)
desk.requestNewDynamicInspection("fubar", new File(filepath));
FrontDesk.getInstance().scan();
FrontDesk.getInstance().register(policy);
FrontDesk.getInstance().setPolicy(policy);
DynamicPolicyRule rule = (DynamicPolicyRule) policy.getRuleByName("greaterThan0");
assertTrue("No rule with name \"greaterThan0\" was found", rule != null);
// write TestInput in PoiWorkbook
POIWriter writer = new POIWriter();
writer.insertTestInput(rule, req.getExternalSpreadsheet());
Workbook wb = req.getExternalSpreadsheet();
Sheet sheet1 = wb.getSheetAt(0);
for (Row row : sheet1) {
for (Cell cell : row) {
CellReference cellRef = new CellReference(row.getRowNum(), cell.getColumnIndex());
System.out.print(row.getRowNum() + ", " + cell.getColumnIndex());
System.out.print(" - ");
System.out.print(cellRef.formatAsString());
System.out.print(" - ");
switch (cell.getCellType()) {
case Cell.CELL_TYPE_STRING:
System.out.println(cell.getRichStringCellValue().getString());
break;
case Cell.CELL_TYPE_NUMERIC:
if (DateUtil.isCellDateFormatted(cell)) {
System.out.println(cell.getDateCellValue());
} else {
System.out.println(cell.getNumericCellValue());
}
break;
case Cell.CELL_TYPE_BOOLEAN:
System.out.println(cell.getBooleanCellValue());
break;
case Cell.CELL_TYPE_FORMULA:
System.out.println(cell.getCellFormula());
break;
default:
System.out.println();
}
}
}
}
public List<String> getCellConstraints(Cell cell) {
for (DataValidation _validation : getValidations())
for (CellRangeAddress _region : _validation.getRegions().getCellRangeAddresses())
if (_region.isInRange(cell.getRowIndex(), cell.getColumnIndex())) {
if (_validation.getValidationConstraint().getExplicitListValues() != null)
return Arrays.asList(_validation.getValidationConstraint().getExplicitListValues());
else if (_validation.getValidationConstraint().getFormula1() != null) {
String formula = _validation.getValidationConstraint().getFormula1().split("\"")[1];
String[] _names = formula.split("!");
String _sheetName = _names[0];
String _arrName = _names[1];
int sheetIndex = evalWorkbook.getSheetIndex(_sheetName);
EvaluationName nm = evalWorkbook.getName(_arrName, sheetIndex);
if (nm == null || !nm.isRange()) {
throw new RuntimeException(
"Specified name '" + _arrName + "' is not a range as expected.");
}
OperationEvaluationContext ec =
new OperationEvaluationContext(
new WorkbookEvaluator(evalWorkbook, null, null),
evalWorkbook,
defaultSheet,
cell.getRowIndex(),
cell.getColumnIndex(),
null);
Ptg[] ptgs = nm.getNameDefinition();
if (ptgs.length == 1 && ptgs[0] instanceof Area3DPtg) {
ValueEval result = ec.getArea3DEval((Area3DPtg) ptgs[0]);
if (result instanceof AreaEvalBase) {
AreaEvalBase _area = (AreaEvalBase) result;
ArrayList<String> resultStrings = new ArrayList<>();
for (int i = _area.getFirstRow(); i <= _area.getLastRow(); i++) {
String value =
getStringValue(
new CellCoord(_area.getFirstSheetIndex(), _area.getFirstColumn(), i));
if (value != null && value.length() > 0) resultStrings.add(value);
}
return resultStrings;
}
return null;
}
}
}
return null;
}
public List<Specialite> readBooksFromExcelFile(String excelFilePath) throws IOException {
List<Specialite> listSpecialite = new ArrayList<>();
FileInputStream file = new FileInputStream(new File(excelFilePath));
// Create Workbook instance holding reference to .xlsx file
XSSFWorkbook workbook = new XSSFWorkbook(file);
// Get first/desired sheet from the workbook
XSSFSheet sheet = workbook.getSheetAt(0);
Sheet firstSheet = workbook.getSheetAt(0);
Iterator<Row> iterator = firstSheet.iterator();
while (iterator.hasNext()) {
Row nextRow = iterator.next();
Iterator<Cell> cellIterator = nextRow.cellIterator();
Specialite sp = new Specialite();
Admin admin = new Admin();
while (cellIterator.hasNext()) {
Cell nextCell = cellIterator.next();
int columnIndex = nextCell.getColumnIndex();
switch (columnIndex) {
case 0:
sp.setIntitule((String) getCellValue(nextCell));
break;
}
}
listSpecialite.add(sp);
}
file.close();
return listSpecialite;
}
private boolean isMerged(Cell cell) {
if (cell == null) return false;
for (CellRangeAddress range : mergedRegions) {
if (range.isInRange(cell.getRowIndex(), cell.getColumnIndex())) return true;
}
return false;
}
public static void generateTrainingDataFromFile(
String
fileLocation) // Requires that the original file had the metadata and requires that this
// file is formated the same in first sheet
{
testDataLL = (LinkedList<String[]>) dataLL.clone();
actualClassifications = (LinkedList<String>) classificationsLL.clone();
FileInputStream file;
try {
file = new FileInputStream(new File(fileLocation));
Workbook excelFile = new HSSFWorkbook(file);
Sheet sheet1 = excelFile.getSheetAt(0); // Data sheet
for (Row row : sheet1) {
String data[] = new String[row.getPhysicalNumberOfCells() - 1];
String classification = "";
int offset =
0; // Used so that we can declare an array of the size of the attributes without the
// classification
for (Cell cell : row) {
int index = cell.getColumnIndex();
if (classificationLocation != index) {
data[index - offset] = cell.toString();
} else {
classification = cell.toString();
offset++;
}
}
// Even though data and classifications are not really used add it onto the end so it is
// still complete for in the event they end up being used in a later version
dataLL.add(data);
classificationsLL.add(classification);
trainingDataLL.add(data);
knownClassifications.add(classification);
// Check to see if we have seen that classification yet
int occurrences = 0;
for (int i = 0; i < classificationTypes.size() && occurrences == 0; i++) {
if (classificationTypes.get(i).compareTo(classification) == 0) {
occurrences = 1;
}
}
if (occurrences == 0) {
classificationTypes.add(classification);
}
}
excelFile.close();
} catch (FileNotFoundException e) {
System.out.println("Error file not found");
System.exit(0);
} catch (IOException e) {
System.out.println("Unable to read file, disk drive may be failing");
e.printStackTrace();
System.exit(0);
}
}
/**
* Extract a couple of data elements for easy reference, such as row and column
*
* @param sheetName
* @param cell
*/
public SpreadsheetItem(String sheetName, Cell cell) {
super();
this.sheetName = sheetName;
this.spreadsheetCell = cell;
column = spreadsheetCell.getColumnIndex();
row = spreadsheetCell.getRowIndex();
this.cellIdentifier = new CellIdentifier(this.sheetName, spreadsheetCell.getRowIndex(), column);
}
private String[] rowToStringArray(Row row) {
stringCache.clear();
for (Cell cell : CollectionUtils.asIterable(row.iterator())) {
while (stringCache.size() < cell.getColumnIndex()) stringCache.add(""); // handle empty cells
stringCache.add(formatter.formatCellValue(cell, evaluator));
}
return stringCache.toArray(new String[stringCache.size()]);
}
private void analyzeTable(XSSFRow row) {
Iterator<org.apache.poi.ss.usermodel.Cell> cellIterator = row.cellIterator();
while (cellIterator.hasNext()) {
org.apache.poi.ss.usermodel.Cell cell = cellIterator.next();
// System.out.println(cell.getStringCellValue());
switch (readCells(cell)) {
case "Name ":
NAME_COLUMN = cell.getColumnIndex();
break;
case "Value ":
VALUE_COLUMN = cell.getColumnIndex();
break;
case "Modification Status ":
STATUS_COLUMN = cell.getColumnIndex();
break;
}
}
}
/**
* Load the Data from Excel spreadsheet.It uses Apache POI classes to load the data.
*
* @param excelFile the excel file input stream to load the data from
* @return the loaded data.
* @throws IOException if an exception occurs while loading the data
*/
private Map<String, List<Map<String, Object>>> loadFromSpreadsheet(final InputStream excelFile)
throws IOException {
HSSFWorkbook workbook = new HSSFWorkbook(excelFile);
data = new HashMap<String, List<Map<String, Object>>>();
Sheet sheet = workbook.getSheetAt(0);
LOG.debug("Sheet {} is being read", sheet);
Map<String, List<Map<String, Object>>> finalData =
new HashMap<String, List<Map<String, Object>>>();
Map<Integer, Object> tempData = new HashMap<Integer, Object>();
List<Map<String, Object>> dataValues = null;
LinkedHashMap<String, Object> actualData = new LinkedHashMap<String, Object>();
for (Row row : sheet) {
boolean keyRow = isKeyRow(row, workbook);
actualData = initializeRowData(row, workbook, actualData);
// Map<String, Object> actualData = new LinkedHashMap<String, Object>();
StringBuilder debugInfo = new StringBuilder("Row data being read is ");
for (Cell cell : row) {
Object cellData = objectFrom(workbook, cell);
debugInfo.append(":" + cellData);
if ((cell.getColumnIndex() == 0) && keyRow) {
// Indicates that this is a new set of test data.
dataValues = new ArrayList<Map<String, Object>>();
finalData.put(cellData.toString().trim(), dataValues);
} else {
if (keyRow) {
tempData.put(cell.getColumnIndex(), cellData);
} else {
if (tempData.get(cell.getColumnIndex()) != null) {
actualData.put(tempData.get(cell.getColumnIndex()).toString(), cellData);
}
}
}
}
LOG.debug(debugInfo.toString());
if (!keyRow) {
dataValues.add(actualData);
}
}
return finalData;
}
private static List<Product> readProductFile(String filePath) {
List<Product> productList = new ArrayList<Product>();
try {
FileInputStream file = new FileInputStream(FILE_PATH);
// Use XSSF for xlsx format, for xls use HSSF
Workbook workbook = new HSSFWorkbook(file);
int numberOfSheets = workbook.getNumberOfSheets();
System.out.println("Number of sheets in the workbook : " + numberOfSheets);
String productType = null;
Sheet firstSheet = workbook.getSheetAt(0);
for (Row myRow : firstSheet) {
Product product = new Product();
Boolean skipProduct = Boolean.FALSE;
for (Cell myCell : myRow) {
switch (myCell.getColumnIndex()) {
case 0:
product.setProductName(myCell.getRichStringCellValue().getString().trim());
break;
case 1:
product.setSellingRate(myCell.getNumericCellValue());
break;
case 2:
product.setCostRate(myCell.getNumericCellValue());
break;
case 3:
if (myCell.getCellType() == Cell.CELL_TYPE_NUMERIC)
product.setProductCode(String.valueOf((int) myCell.getNumericCellValue()));
else product.setProductCode(myCell.getStringCellValue().trim());
break;
case 4:
if (myCell.getRichStringCellValue().getString() != null) {
productType = myCell.getRichStringCellValue().getString().trim();
skipProduct = Boolean.TRUE;
} else skipProduct = Boolean.FALSE;
break;
}
}
if (!skipProduct) {
product.setProductType(productType);
productList.add(product);
}
}
file.close();
} catch (Exception ex) {
ex.printStackTrace();
}
return productList;
}
private int findIndexCellByName(String name, Row row) {
Iterator<Cell> iterator = row.cellIterator();
while (iterator.hasNext()) {
Cell cell = iterator.next();
if (getCellValue(cell).trim().equalsIgnoreCase(name)) {
return cell.getColumnIndex();
}
}
return -1;
}
/**
* Remove the Cell from this row.
*
* @param cell the cell to remove
*/
public void removeCell(Cell cell) {
if (cell.getRow() != this) {
throw new IllegalArgumentException("Specified cell does not belong to this row");
}
XSSFCell xcell = (XSSFCell) cell;
if (xcell.isPartOfArrayFormulaGroup()) {
xcell.notifyArrayFormulaChanging();
}
_cells.remove(cell.getColumnIndex());
}
/**
* 判断excel标题与对象的标签标题是否一致
*
* @param titleRow 开始行
* @param clz 对象
* @return 判断类型
*/
private Map<Integer, String> getHeaderMap(Row titleRow, Class clz) {
List<ExcelHeader> headers = getHeaderList(clz); // 取后台标题
Map<Integer, String> maps = new HashMap<Integer, String>();
for (Cell c : titleRow) {
String title = c.getStringCellValue(); // 取excel的标题栏
for (ExcelHeader eh : headers) {
if (eh.getTitle().equals(title.trim())) { // 相等则设定对应的字段顺序与方法名
maps.put(c.getColumnIndex(), eh.getMethodName().replace("get", "set"));
break;
}
}
}
return maps;
}
public static Object getCellValue(Cell cell) {
Object value = new Object();
// Prevent a NullPointerException
if (cell != null) {
if (cell.getHyperlink() != null) {
Workbook workbook = new XSSFWorkbook();
FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();
Hyperlink link = cell.getHyperlink();
String address = link.getAddress();
if (logger.isTraceEnabled()) {
logger.trace(
"Found a Hyperlink to "
+ cell.getHyperlink().getAddress()
+ " in cell "
+ cell.getRowIndex()
+ ","
+ cell.getColumnIndex());
}
cell = evaluator.evaluateInCell(cell);
}
// Depending on the cell type, the value is read using Apache POI methods
switch (cell.getCellType()) {
// String are easy to handle
case Cell.CELL_TYPE_STRING:
logger.trace("Found string " + cell.getStringCellValue());
value = cell.getStringCellValue();
break;
// Since date formatted cells are also of the numeric type, this needs to be processed
case Cell.CELL_TYPE_NUMERIC:
if (DateUtil.isCellDateFormatted(cell)) {
Date date = cell.getDateCellValue();
DateFormat df = SimpleDateFormat.getDateInstance();
logger.trace("Found date " + df.format(date));
value = date;
} else {
logger.trace("Found general number " + cell.getNumericCellValue());
value = cell.getNumericCellValue();
}
break;
}
} else {
logger.trace("Found cell with NULL value");
}
return value;
}
private void fillRowWith(Row r, String[] data) {
Cell c = null;
for (String s : data) {
if (c == null) c = r.createCell(0);
else c = r.createCell(c.getColumnIndex() + 1);
try {
double d = Double.valueOf(s);
c.setCellValue(d);
} catch (NumberFormatException e) {
c.setCellValue(s);
}
}
}
@Test
public void thatBuildCellReturnsCellWithCorrectTypeIndexAndValue() throws Exception {
String value = "test value";
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = workbook.createSheet("test sheet");
Row row = sheet.createRow(0);
Cell cell = posreadyFieldsBuilder.buildStringCell(row, 0, value);
assertEquals(0, cell.getColumnIndex());
assertEquals(Cell.CELL_TYPE_STRING, cell.getCellType());
assertEquals(value, cell.getStringCellValue());
}
/** 通过cell得到A3 B6 */
public String getRowColmunNumByCell(Cell cell) {
if (cell == null) return "";
String res = "";
String alpha = "abcdefghijklmnopqrstuvwxyz";
int row = cell.getRowIndex();
int col = cell.getColumnIndex() + 1;
while (true) {
int a = col % 26;
if (a == 0) {
a = 26;
}
res = alpha.charAt(a - 1) + res;
col = (col - a) / 26;
if (col == 0) break;
}
res += row;
return res.toUpperCase();
}
public List<Object> readExcel(Workbook wb, Class clz, int readLine, int tailLine) {
Sheet sheet = wb.getSheetAt(0); // 取第一张表
List<Object> objs = null;
try {
Row row = sheet.getRow(readLine); // 开始行，主题栏
objs = new ArrayList<Object>();
Map<Integer, String> maps = getHeaderMap(row, clz); // 设定对应的字段顺序与方法名
if (maps == null || maps.size() <= 0)
throw new RuntimeException("要读取的Excel的格式不正确，检查是否设定了合适的行"); // 与order顺序不符
for (int i = readLine + 1; i <= sheet.getLastRowNum() - tailLine; i++) { // 取数据
row = sheet.getRow(i);
Object obj = clz.newInstance(); // 调用无参结构
for (Cell c : row) {
int ci = c.getColumnIndex();
String mn = maps.get(ci).substring(3); // 消除get
mn = mn.substring(0, 1).toLowerCase() + mn.substring(1);
Map<String, Object> params = new HashMap<String, Object>();
if (!"enterDate".equals(mn)) c.setCellType(Cell.CELL_TYPE_STRING); // 设置单元格格式
else c.setCellType(Cell.CELL_TYPE_NUMERIC);
if (this.getCellValue(c).trim().equals("是")) {
BeanUtils.copyProperty(obj, mn, 1);
} else if (this.getCellValue(c).trim().equals("否")) {
BeanUtils.copyProperty(obj, mn, 0);
} else BeanUtils.copyProperty(obj, mn, this.getCellValue(c));
}
objs.add(obj);
}
} catch (InstantiationException e) {
e.printStackTrace();
logger.error(e);
} catch (IllegalAccessException e) {
e.printStackTrace();
logger.error(e);
} catch (InvocationTargetException e) {
e.printStackTrace();
logger.error(e);
} catch (NumberFormatException e) {
e.printStackTrace();
logger.error(e);
}
return objs;
}
@Override
public List<Object> getRowValues(int rowIndex) {
Row row = dataSheet.getRow(rowIndex);
ArrayList<Object> values = new ArrayList<>();
if (row == null) return values;
int nullCount = 0;
int colCount = row.getLastCellNum();
for (int col = 0; col < colCount; col++) {
Cell cell = row.getCell(col);
// treat rows with merged cells as "empty"
if (isMerged(cell)) {
LogMgr.logDebug(
"ExcelReader.getRowValues()",
dataSheet.getSheetName()
+ ": column:"
+ cell.getColumnIndex()
+ ", row:"
+ cell.getRowIndex()
+ " is merged. Ignoring row!");
return Collections.emptyList();
}
Object value = getCellValue(cell);
if (value == null) {
nullCount++;
}
values.add(value);
}
if (nullCount == values.size()) {
// return an empty list if all columns are null
values.clear();
}
return values;
}
/**
* {@inheritDoc}
*
* @see
* org.eclipse.emfforms.spi.spreadsheet.core.converter.EMFFormsSpreadsheetValueConverter#getCellValue(org.apache.poi.ss.usermodel.Cell,
* org.eclipse.emf.ecore.EStructuralFeature)
*/
@Override
public Object getCellValue(Cell cell, EStructuralFeature eStructuralFeature)
throws EMFFormsConverterException {
String string;
try {
string = cell.getStringCellValue();
} catch (final IllegalStateException e) {
throw new EMFFormsConverterException(
String.format(
"Cell value of column %1$s in row %2$s on sheet %3$s must be a string.", //$NON-NLS-1$
cell.getColumnIndex() + 1, cell.getRowIndex() + 1, cell.getSheet().getSheetName()),
e);
}
if (string == null || string.length() == 0) {
return Collections.emptyList();
}
final EAttribute eAttribute = EAttribute.class.cast(eStructuralFeature);
final EDataType eDataType = eAttribute.getEAttributeType();
if (isDecimalNumber(eDataType.getInstanceClass())) {
string =
string.replace(
DecimalFormatSymbols.getInstance(localeProvider.getLocale()).getDecimalSeparator(),
'.');
}
final List<Object> result = new ArrayList<Object>();
final EFactory eFactory = eDataType.getEPackage().getEFactoryInstance();
for (final String element : string.split(SEPARATOR)) {
try {
result.add(eFactory.createFromString(eDataType, element));
} // BEGIN SUPRESS CATCH EXCEPTION
catch (final RuntimeException ex) { // END SUPRESS CATCH EXCEPTION
throw new EMFFormsConverterException(
MessageFormat.format(
"The cell value {0} could not converted to a model value.", string)); // $NON-NLS-1$
}
}
return result;
}
@Override
public List<Entity> parse(File file) throws Exception {
List<Entity> retVal = new ArrayList<Entity>();
Workbook wb = null;
FileInputStream fileIn = new FileInputStream(file);
try {
wb = WorkbookFactory.create(fileIn);
Map<String, Integer> headers = new HashMap<String, Integer>();
for (int i = 0; i < wb.getNumberOfSheets(); i++) {
Sheet sheet = wb.getSheetAt(i);
RowDataAccessor dataAccessor = new RowDataAccessor();
for (Row row : sheet) {
if (row.getRowNum() == sheet.getFirstRowNum()) {
for (Cell cell : row) {
headers.put(cell.getStringCellValue(), cell.getColumnIndex());
}
dataAccessor.setHeaders(headers);
} else {
dataAccessor.setDataRow(row);
Entity entity = Parser.EntityFiller.fillEntity(dataAccessor, dbHelper);
if (entity != null) {
retVal.add(entity);
}
}
}
}
} catch (Exception ex) {
throw ex;
} finally {
fileIn.close();
}
return retVal;
}
private LinkedHashMap<String, Object> initializeRowData(
Row row, HSSFWorkbook workbook, LinkedHashMap<String, Object> actualData) {
LinkedHashMap<String, Object> result = new LinkedHashMap<String, Object>();
if (isKeyRow(row, workbook)) {
// reset the actualData
actualData = new LinkedHashMap<String, Object>();
for (Cell cell : row) {
if (cell.getColumnIndex() != 0) {
Object cellData = objectFrom(workbook, cell);
if (cellData != null) actualData.put(cellData.toString(), null);
}
}
} else {
// not a key row, so reinitialize each key to null
for (String key : actualData.keySet()) {
result.put(key, null);
}
return result;
}
return actualData;
}
// Setup column data here
private void setValue(Cell cell, ConfirmationRecord data) {
switch (cell.getColumnIndex()) {
case 0:
cell.setCellValue(cell.getRowIndex() - 4);
break;
case 1:
cell.setCellValue(DateUtil.convDateToString("yyyy", data.getApplicationDate()));
break;
case 2:
cell.setCellValue(DateUtil.convDateToString("M", data.getApplicationDate()));
break;
case 3:
cell.setCellValue(DateUtil.convDateToString("d", data.getApplicationDate()));
break;
case 4:
cell.setCellValue(DateUtil.convDateToString("yyyy", data.getSettleDate()));
break;
case 5:
cell.setCellValue(DateUtil.convDateToString("M", data.getSettleDate()));
break;
case 6:
cell.setCellValue(DateUtil.convDateToString("d", data.getSettleDate()));
break;
case 7:
cell.setCellValue(data.getPolicyNo());
break;
case 8:
cell.setCellValue(data.getPlanCode());
break;
case 9:
cell.setCellValue(data.getInsuredName());
break;
case 10:
cell.setCellValue(data.getModalPremium().doubleValue());
break;
case 11:
cell.setCellValue(data.getApe().doubleValue());
break;
case 12:
cell.setCellValue(data.getSumInsured().doubleValue());
break;
case 13:
cell.setCellValue(data.getMode());
break;
case 14:
cell.setCellValue(data.getStatus());
break;
case 15:
cell.setCellValue(data.getUnitCode());
break;
case 16:
cell.setCellValue(data.getBankCard());
break;
case 17:
cell.setCellValue(data.getCreditCardNo());
break;
case 18:
cell.setCellValue(data.getExpiredDate());
break;
case 19:
cell.setCellValue(
data.getBirthDate() != null
? DateUtil.convDateToString("yyyy", data.getBirthDate())
: null);
break;
case 20:
cell.setCellValue(
data.getBirthDate() != null
? DateUtil.convDateToString("M", data.getBirthDate())
: null);
break;
case 21:
cell.setCellValue(
data.getBirthDate() != null
? DateUtil.convDateToString("d", data.getBirthDate())
: null);
break;
case 22:
cell.setCellValue(data.getCitizenId());
break;
case 23:
cell.setCellValue(data.getAddress1());
break;
case 24:
cell.setCellValue(data.getAddress2());
break;
case 25:
cell.setCellValue(data.getAddress3());
break;
case 26:
cell.setCellValue(data.getProvince());
break;
case 27:
cell.setCellValue(data.getPostCode());
break;
case 28:
cell.setCellValue(data.getTel1());
break;
case 29:
cell.setCellValue(data.getTel2());
break;
case 30:
cell.setCellValue(data.getTel3());
break;
case 31:
cell.setCellValue(data.getMobile1());
break;
case 32:
cell.setCellValue(data.getMobile2());
break;
case 33:
cell.setCellValue(data.getTsrName());
break;
case 34:
cell.setCellValue(data.getAction() != null ? data.getAction().getParamValue() : null);
break;
case 35:
cell.setCellValue(data.getRemark());
break;
default:
break;
}
}
public String process2xml() {
String result = "";
// String sourcefilenametag = config.getString("conversion.tags.sourcefilename");
String sourcefilename = file.getAbsolutePath();
try {
sourcefilename = file.getCanonicalPath();
} catch (IOException ioe) {
log.warn("Could not get cannonical path for file!", ioe);
}
String templatedir = config.getString("conversion.template.path");
// result += "<" + sourcefilenametag + "><![CDATA[" + file.toString() + "]]></" +
// sourcefilenametag + ">";
try {
Workbook input = WorkbookFactory.create(file);
log.debug("Processing " + file.toString() + " as an Excel file.");
FormulaEvaluator evaluator = input.getCreationHelper().createFormulaEvaluator();
String templateName = "";
STGroup g = new STRawGroupDir(templatedir, '$', '$');
// Go through each sheet
for (int sheetno = 0; sheetno < input.getNumberOfSheets(); sheetno++) {
Sheet sheet = input.getSheetAt(sheetno);
log.debug("Processing sheet #" + sheetno + ": " + sheet.getSheetName());
LandmarkMatchList lml = new LandmarkMatchList(landmarks.size());
for (Row row : sheet) {
// Go through each cell
for (Cell cell : row) {
String cellvalue = lml.getCellValue(cell, evaluator);
if (!cellvalue.equals("")) {
log.trace(
"Cell value is: "
+ cellvalue
+ " [Row,Col]=["
+ cell.getRowIndex()
+ ","
+ cell.getColumnIndex()
+ "]");
log.trace("Matching landmarks: " + landmarks.getLandmarksFor(cellvalue));
// Does Cell contents match a landmark?
lml.addMatches(landmarks.getLandmarksFor(cellvalue), cell);
} else {
log.trace(
"Cell value is blank. [Row,Col]=["
+ cell.getRowIndex()
+ ","
+ cell.getColumnIndex()
+ "]");
}
}
}
templateName = lml.getTemplateName(landmarks);
if (!templateName.equals("")) {
ST st = g.getInstanceOf(templateName);
if (st != null) {
// Set landmark name to value of cell given direction and distance
Hashtable templateValues =
lml.getCellTemplateValues(
templateName, sheet, landmarks, evaluator, sourcefilename, sheetno);
Enumeration templateValuesKeys = templateValues.keys();
while (templateValuesKeys.hasMoreElements()) {
String key = (String) templateValuesKeys.nextElement();
st.add(key, (String) templateValues.get(key));
}
ArrayList<String> sectionNames =
lml.getSectionNamesForTemplate(templateName, landmarks);
for (String sectionName : sectionNames) {
ArrayList<Hashtable> sectionrows =
lml.getSectionRows(templateName, sheet, landmarks, evaluator, sectionName);
st.add(sectionName, sectionrows);
}
result += st.render();
} else {
log.error(
"Unable to load template "
+ templateName
+ ".st! Cannot render data to template while processing "
+ file.toString()
+ " sheet number "
+ sheetno);
}
}
}
} catch (IOException ioe) {
log.error("Unable to open " + file.toString() + " as an Excel file.", ioe);
} catch (InvalidFormatException ife) {
log.error("Unable to open " + file.toString() + ". Format not recognized as Excel. ", ife);
} catch (IllegalArgumentException iae) {
log.error("Unable to open " + file.toString() + " as an Excel file.", iae);
} catch (Exception e) {
log.error("Unable to open " + file.toString() + " as an Excel file.", e);
}
// If cannot render, make sure don't reprocess unnecessarily
if (result.equals("")) {
result = "<Notemplatedata></Notemplatedata>";
}
return result;
}
private void readBOM(File fileItem) throws Exception {
FileInputStream file = new FileInputStream(fileItem);
XSSFWorkbook workbook = new XSSFWorkbook(file);
XSSFSheet sheet = workbook.getSheetAt(0);
Iterator<Row> rowIterator = sheet.iterator();
Connection con = Connect.getconnection();
PreparedStatement ps1 = con.prepareStatement("TRUNCATE TABLE bom");
ps1.executeUpdate();
Row nextRow = rowIterator.next();
while (rowIterator.hasNext()) {
nextRow = rowIterator.next();
Iterator<Cell> cellIterator = nextRow.cellIterator();
PreparedStatement ps =
con.prepareStatement(
"INSERT INTO `star`.`bom` (`FGNo`, `Parent`, `Child`, `Qty`, `Decs`, `Unit`, `Mat_grp`,`Mat_grp_desc`,`Mat_sub_grp`,`Mat_sub_grp_desc`,`Stock`) VALUES(?,?,?,?,?,?,?,?,?,?,?)");
while (cellIterator.hasNext()) {
Cell cell = cellIterator.next();
switch (cell.getCellType()) {
case Cell.CELL_TYPE_STRING:
if (cell.getColumnIndex() == 0) {
// FG
ps.setString(1, cell.getStringCellValue());
// System.out.println(cell.getStringCellValue());
break;
} else if (cell.getColumnIndex() == 1) {
// Parent
ps.setString(2, cell.getStringCellValue());
// System.out.println(cell.getStringCellValue());
break;
} else if (cell.getColumnIndex() == 2) {
// Child
ps.setString(3, cell.getStringCellValue());
// System.out.println(cell.getStringCellValue());
break;
} else if (cell.getColumnIndex() == 4) {
// Decsription
ps.setString(5, cell.getStringCellValue());
// System.out.println(cell.getNumericCellValue());
break;
} else if (cell.getColumnIndex() == 5) {
// Unit
ps.setString(6, cell.getStringCellValue());
// System.out.println(cell.getNumericCellValue());
break;
} else if (cell.getColumnIndex() == 6) {
// Mat_grp
ps.setString(7, cell.getStringCellValue());
// System.out.println(cell.getNumericCellValue());
break;
} else if (cell.getColumnIndex() == 7) {
// Mat_grp_desc
ps.setString(8, cell.getStringCellValue());
// System.out.println(cell.getNumericCellValue());
break;
} else if (cell.getColumnIndex() == 8) {
// Mat_sub_grp
ps.setString(9, cell.getStringCellValue());
// System.out.println(cell.getNumericCellValue());
break;
} else if (cell.getColumnIndex() == 9) {
// Mat_sub_grp_desc
ps.setString(10, cell.getStringCellValue());
// System.out.println(cell.getNumericCellValue());
break;
}
case Cell.CELL_TYPE_NUMERIC:
if (cell.getColumnIndex() == 3) {
// Quantity
ps.setDouble(4, cell.getNumericCellValue());
// System.out.println(cell.getNumericCellValue());
break;
} else if (cell.getColumnIndex() == 10) {
// Stock
ps.setInt(11, (int) cell.getNumericCellValue());
// System.out.println(cell.getNumericCellValue());
break;
}
case Cell.CELL_TYPE_BLANK:
if (cell.getColumnIndex() == 0) {
// FG
ps.setString(1, " ");
// System.out.println(cell.getStringCellValue());
break;
} else if (cell.getColumnIndex() == 1) {
// Parent
ps.setString(2, " ");
// System.out.println(cell.getStringCellValue());
break;
} else if (cell.getColumnIndex() == 2) {
// Child
ps.setString(3, " ");
// System.out.println(cell.getStringCellValue());
break;
} else if (cell.getColumnIndex() == 3) {
// Quantity
ps.setDouble(4, 0);
// System.out.println(cell.getNumericCellValue());
break;
} else if (cell.getColumnIndex() == 4) {
// Decsription
ps.setString(5, " ");
// System.out.println(cell.getNumericCellValue());
break;
} else if (cell.getColumnIndex() == 5) {
// Unit
ps.setString(6, " ");
// System.out.println(cell.getNumericCellValue());
break;
} else if (cell.getColumnIndex() == 6) {
// Mat_grp
ps.setString(7, " ");
// System.out.println(cell.getNumericCellValue());
break;
} else if (cell.getColumnIndex() == 7) {
// Mat_grp_desc
ps.setString(8, " ");
// System.out.println(cell.getNumericCellValue());
break;
} else if (cell.getColumnIndex() == 8) {
// Mat_sub_grp
ps.setString(9, " ");
// System.out.println(cell.getNumericCellValue());
break;
} else if (cell.getColumnIndex() == 9) {
// Mat_sub_grp_desc
ps.setString(10, " ");
// System.out.println(cell.getNumericCellValue());
break;
} else if (cell.getColumnIndex() == 10) {
// Stock
ps.setInt(11, 0);
// System.out.println(cell.getNumericCellValue());
break;
}
case Cell.CELL_TYPE_ERROR:
if (cell.getColumnIndex() == 0) {
// FG
ps.setString(1, " ");
// System.out.println(cell.getStringCellValue());
break;
} else if (cell.getColumnIndex() == 1) {
// Parent
ps.setString(2, " ");
// System.out.println(cell.getStringCellValue());
break;
} else if (cell.getColumnIndex() == 2) {
// Child
ps.setString(3, " ");
// System.out.println(cell.getStringCellValue());
break;
} else if (cell.getColumnIndex() == 3) {
// Quantity
ps.setDouble(4, 0);
// System.out.println(cell.getNumericCellValue());
break;
} else if (cell.getColumnIndex() == 4) {
// Decsription
ps.setString(5, " ");
// System.out.println(cell.getNumericCellValue());
break;
} else if (cell.getColumnIndex() == 5) {
// Unit
ps.setString(6, " ");
// System.out.println(cell.getNumericCellValue());
break;
} else if (cell.getColumnIndex() == 6) {
// Mat_grp
ps.setString(7, " ");
// System.out.println(cell.getNumericCellValue());
break;
} else if (cell.getColumnIndex() == 7) {
// Mat_grp_desc
ps.setString(8, " ");
// System.out.println(cell.getNumericCellValue());
break;
} else if (cell.getColumnIndex() == 8) {
// Mat_sub_grp
ps.setString(9, " ");
// System.out.println(cell.getNumericCellValue());
break;
} else if (cell.getColumnIndex() == 9) {
// Mat_sub_grp_desc
ps.setString(10, " ");
// System.out.println(cell.getNumericCellValue());
break;
} else if (cell.getColumnIndex() == 10) {
// Stock
ps.setInt(11, 0);
// System.out.println(cell.getNumericCellValue());
break;
}
}
// out.print("  ");
}
ps.executeUpdate();
// out.println("<br>");
}
workbook.close();
file.close();
con.close();
}
/** @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response) */
protected void doPost(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
// TODO Auto-generated method stub
String filepath = null;
String id = null,
name = null,
email = null,
mobile = null,
doj = null,
password = null,
man1 = null,
man2 = null,
check = "success";
int level = 0, layer = 0;
/* filepath=request.getParameter("file");
*/
String message = "";
// out.print("<html><body><h3>"+filepath+"</h3></body></html>");
boolean isMultiPart = ServletFileUpload.isMultipartContent(request);
HttpSession session = request.getSession();
if (isMultiPart) {
ServletFileUpload upload = new ServletFileUpload();
try {
FileItemIterator itr = upload.getItemIterator(request);
while (itr.hasNext()) {
FileItemStream item = itr.next();
if (!item.isFormField()) {
String path = getServletContext().getInitParameter("file-upload");
request.setAttribute("path", path);
if (FileUpload.processFile(path, item)) {
filepath =
"C:\\Users\\shagayaraj\\Documents\\Tina\\webtechlab\\Ideation\\WebContent"
+ "\\"
+ item.getName();
String[] ext = filepath.split("\\.");
String ext1 = ext[0];
String ext2 = ext[1];
if (ext2.equals("xlsx")) {
int rc = 0;
FileInputStream fis = new FileInputStream(new File(filepath));
XSSFWorkbook wb = new XSSFWorkbook(fis);
XSSFSheet spreadsheet = wb.getSheetAt(0);
// System.out.println(list);
Iterator<Row> rowIterator = spreadsheet.iterator();
while (rowIterator.hasNext()) {
check = "success";
row = (XSSFRow) rowIterator.next();
rc++;
Iterator<Cell> cellIterator = row.cellIterator();
while (cellIterator.hasNext()) {
Cell cell = cellIterator.next();
// out.print("<h3>"+cell.getCellType()+"</h3><br>");
switch (cell.getCellType()) {
case Cell.CELL_TYPE_NUMERIC:
if (cell.getColumnIndex() == 4) {
DataFormatter df = new DataFormatter();
if ((df.formatCellValue(cell))
.matches("^([0-9]{4})-([0-9]{2})-([0-9]{2})$"))
doj = df.formatCellValue(cell);
else {
message += "<p>Invalid Date format at row:" + rc + " and Column:5</p>";
check = "error";
}
// out.print( "<h3>"+cvd+"</h3><br>");
} else if (cell.getColumnIndex() == 7) {
cell.setCellType(Cell.CELL_TYPE_STRING);
if ((cell.getStringCellValue()).matches("[0-9]{10}"))
mobile = cell.getStringCellValue();
else {
message +=
"<p>Invalid Mobile Number format at row:"
+ rc
+ " and Column:8</p>";
check = "error";
}
} else if (cell.getColumnIndex() == 5) {
cell.setCellType(Cell.CELL_TYPE_STRING);
if ((cell.getStringCellValue()).matches("[0-9]+"))
level = Integer.parseInt(cell.getStringCellValue());
else {
message += "<p>Invalid Level at row:" + rc + " and Column:6</p>";
check = "error";
}
} else if (cell.getColumnIndex() == 6) {
cell.setCellType(Cell.CELL_TYPE_STRING);
if ((cell.getStringCellValue()).matches("[0-9]+"))
layer = Integer.parseInt(cell.getStringCellValue());
else {
message += "<p>Invalid Layer at row:" + rc + " and Column:7</p>";
check = "error";
}
}
break;
case Cell.CELL_TYPE_STRING:
if (cell.getColumnIndex() == 0) {
id = cell.getStringCellValue();
String test;
if (id.isEmpty()) test = "invalid";
else {
userdao userd = new userdao();
userbean user = userd.getUserbyId(id);
if (user == null) {
test = "valid";
} else test = "invalid";
}
if (test.equals("invalid")) {
message +=
"<p>User Id is either null or already exists at row:"
+ rc
+ " and Column:1</p>";
check = "error";
}
}
if (cell.getColumnIndex() == 8) {
man1 = cell.getStringCellValue();
String test;
if (man1.isEmpty()) test = "valid";
else {
userdao userd = new userdao();
userbean user = userd.getUserbyId(man1);
if (user == null) {
test = "invalid";
} else test = "valid";
}
if (test.equals("invalid")) {
message += "<p>Invalid Manager Id at row:" + rc + " and Column:9</p>";
check = "error";
}
}
if (cell.getColumnIndex() == 9) {
man2 = cell.getStringCellValue();
String test;
if (man2.isEmpty()) test = "valid";
else {
userdao userd = new userdao();
userbean user = userd.getUserbyId(man2);
if (user == null) {
test = "invalid";
} else test = "valid";
}
if (test.equals("invalid")) {
message += "<p>Invalid Manager Id at row:" + rc + " and Column:10</p>";
check = "error";
}
}
if (cell.getColumnIndex() == 1) {
if ((cell.getStringCellValue()).matches("^[a-zA-Z\\s]+"))
name = cell.getStringCellValue();
else {
message += "<p>Invalid name format at row:" + rc + " and Column:2</p>";
check = "error";
}
}
if (cell.getColumnIndex() == 2) {
if ((cell.getStringCellValue())
.matches("^\\w+([\\.-]?\\w+)*@\\w+([\\.-]?\\w+)*(\\.\\w{2,3})+$"))
email = cell.getStringCellValue();
else {
message += "<p>Invalid email format at row:" + rc + " and Column:3</p>";
check = "error";
}
}
if (cell.getColumnIndex() == 3) password = cell.getStringCellValue();
break;
}
}
if (check.equals("success")) {
java.sql.Date date_joined = java.sql.Date.valueOf(doj);
userdao userd = new userdao();
userd.setUser(
id,
name,
email,
password,
date_joined,
level,
layer,
0,
mobile,
man1,
man2,
false,
true);
user_roledao user_roled = new user_roledao();
user_roled.setUserRolebyUId(id, "3");
}
}
fis.close();
} // if closed
else if (ext2.equals("xls")) {
int rc = 0;
File file = new File(filepath);
InputStream input = new BufferedInputStream(new FileInputStream(file));
POIFSFileSystem fs = new POIFSFileSystem(input);
HSSFWorkbook wb = new HSSFWorkbook(fs);
HSSFSheet sheet = wb.getSheetAt(0);
Iterator rows = sheet.rowIterator();
while (rows.hasNext()) {
HSSFRow row = (HSSFRow) rows.next();
rc++;
check = "success";
Iterator cells = row.cellIterator();
while (cells.hasNext()) {
HSSFCell cell = (HSSFCell) cells.next();
if (HSSFCell.CELL_TYPE_NUMERIC == cell.getCellType()) {
if (cell.getColumnIndex() == 4) {
DataFormatter df = new DataFormatter();
if ((df.formatCellValue(cell))
.matches("^([0-9]{4})-([0-9]{2})-([0-9]{2})$"))
doj = df.formatCellValue(cell);
else {
message += "<p>Invalid Date format at row:" + rc + " and Column:5</p>";
check = "error";
}
// out.print( "<h3>"+cvd+"</h3><br>");
} else if (cell.getColumnIndex() == 7) {
cell.setCellType(Cell.CELL_TYPE_STRING);
if ((cell.getStringCellValue()).matches("[0-9]{10}"))
mobile = cell.getStringCellValue();
else {
message +=
"<p>Invalid Mobile Number format at row:" + rc + " and Column:8</p>";
check = "error";
}
} else if (cell.getColumnIndex() == 5) {
cell.setCellType(Cell.CELL_TYPE_STRING);
if ((cell.getStringCellValue()).matches("[0-9]+"))
level = Integer.parseInt(cell.getStringCellValue());
else {
message += "<p>Invalid Level at row:" + rc + " and Column:6</p>";
check = "error";
}
} else if (cell.getColumnIndex() == 6) {
cell.setCellType(Cell.CELL_TYPE_STRING);
if ((cell.getStringCellValue()).matches("[0-9]+"))
layer = Integer.parseInt(cell.getStringCellValue());
else {
message += "<p>Invalid Layer at row:" + rc + " and Column:7</p>";
check = "error";
}
}
} else if (HSSFCell.CELL_TYPE_STRING == cell.getCellType()) {
if (cell.getColumnIndex() == 0) {
id = cell.getStringCellValue();
String test;
if (id.isEmpty()) test = "invalid";
else {
userdao userd = new userdao();
userbean user = userd.getUserbyId(id);
if (user == null) {
test = "valid";
} else test = "invalid";
}
if (test.equals("invalid")) {
message +=
"<p>User Id is either null or already exists at row:"
+ rc
+ " and Column:1</p>";
check = "error";
}
}
if (cell.getColumnIndex() == 8) {
man1 = cell.getStringCellValue();
String test;
if (man1.isEmpty()) test = "valid";
else {
userdao userd = new userdao();
userbean user = userd.getUserbyId(man1);
if (user == null) {
test = "invalid";
} else test = "valid";
}
if (test.equals("invalid")) {
message += "<p>Invalid Manager Id at row:" + rc + " and Column:9</p>";
check = "error";
}
}
if (cell.getColumnIndex() == 9) {
man2 = cell.getStringCellValue();
String test;
if (man2.isEmpty()) test = "valid";
else {
userdao userd = new userdao();
userbean user = userd.getUserbyId(man2);
if (user == null) {
test = "invalid";
} else test = "valid";
}
if (test.equals("invalid")) {
message += "<p>Invalid Manager Id at row:" + rc + " and Column:10</p>";
check = "error";
}
}
if (cell.getColumnIndex() == 1) {
if ((cell.getStringCellValue()).matches("^[a-zA-Z\\s]+"))
name = cell.getStringCellValue();
else {
message += "<p>Invalid name format at row:" + rc + " and Column:2</p>";
check = "error";
}
}
if (cell.getColumnIndex() == 2) {
if ((cell.getStringCellValue())
.matches("^\\w+([\\.-]?\\w+)*@\\w+([\\.-]?\\w+)*(\\.\\w{2,3})+$"))
email = cell.getStringCellValue();
else {
message += "<p>Invalid email format at row:" + rc + " and Column:3</p>";
check = "error";
}
}
if (cell.getColumnIndex() == 3) password = cell.getStringCellValue();
}
}
if (check.equals("success")) {
java.sql.Date date_joined = java.sql.Date.valueOf(doj);
userdao userd = new userdao();
userd.setUser(
id,
name,
email,
password,
date_joined,
level,
layer,
0,
mobile,
man1,
man2,
false,
true);
user_roledao user_roled = new user_roledao();
user_roled.setUserRolebyUId(id, "3");
}
}
} else {
message += "Unsupported File format!Choose files of format .xls or .xlsx only!";
}
if (message == null) {
message = "<p>Users added successfully</p>";
}
} else {
message += "File upload unsuccessful";
}
}
}
request.setAttribute("errormsg", message);
RequestDispatcher rd = request.getRequestDispatcher("exceltodbmessage.jsp");
rd.forward(request, response);
} catch (FileNotFoundException e) {
message += "The given file cannot be found!";
request.setAttribute("errormsg", message);
RequestDispatcher rd = request.getRequestDispatcher("exceltodbmessage.jsp");
rd.forward(request, response);
} catch (Exception e) {
e.printStackTrace();
}
}
}
private void readExcel() throws IOException, URISyntaxException {
URL inputStream = this.getClass().getResource("ReleaseNotes-002.xlsx");
File file = new File(inputStream.toURI());
FileInputStream fIP = new FileInputStream(file);
XSSFRow row;
// Get the workbook instance for XLSX file
XSSFWorkbook workbook = new XSSFWorkbook(fIP);
for (int i = 0; i < workbook.getNumberOfSheets(); i++) {
XSSFSheet spreadsheet = workbook.getSheetAt(i);
// System.out.println(spreadsheet.getSheetName());
if (!spreadsheet.getSheetName().equals("EAR SAI")) {
continue;
}
StringBuilder sb = new StringBuilder();
Iterator<Row> rowIterator = spreadsheet.iterator();
while (rowIterator.hasNext()) {
row = (XSSFRow) rowIterator.next();
System.out.println(row.getRowNum() + " " + row.getRowStyle());
if (row.getRowStyle() != null) {
System.out.println(row.getRowNum() + " " + row.getRowStyle().getBorderBottom());
}
Iterator<org.apache.poi.ss.usermodel.Cell> cellIterator = row.cellIterator();
if (row.getRowNum() < 4) {
if (row.getRowNum() == 3) analyzeTable(row);
continue;
}
while (cellIterator.hasNext()) {
org.apache.poi.ss.usermodel.Cell cell = cellIterator.next();
if (cell.getColumnIndex() == NAME_COLUMN) {
if (!(sb.lastIndexOf("\n") == sb.length() - 1)) sb.append("\n");
}
if (cell.getColumnIndex() < VALUE_COLUMN) {
sb.append(readCells(cell));
continue;
}
if (cell.getColumnIndex() == VALUE_COLUMN) {
sb.append(" -> " + readCells(cell));
if (!cellIterator.hasNext()) {
sb.append("\n");
}
}
if (cell.getColumnIndex() == STATUS_COLUMN) {
sb.append(" => " + readCells(cell));
if (!cellIterator.hasNext()) {
sb.append("\n");
}
}
// if (!cellIterator.hasNext()) {
// sb.append("\n");
// }
}
// while (cellIterator.hasNext()) {
// Cell cell = cellIterator.next();
// sb.append(readCells(cell));
// if (cellIterator.hasNext()) {
//
// if (cell.getColumnIndex() == 5 && cell.getCellType() != 3) {
// sb.append("\t->\t");
// sb.append(readCells(cell));
// if (!cellIterator.hasNext()) {
// sb.append("\t=>\tstate\n");
// continue;
// }
// cell = cellIterator.next();
// if (cell.getCellType() != 3) {
// sb.append("\t=>\t");
// sb.append(readCells(cell));
// sb.append("\n");
// } else {
// sb.append("\t=>\tstate\n");
// }
// continue;
// }
// sb.append(readCells(cell));
// } else {
// sb.append("\\");
// }
// }
}
// System.out.println();
System.out.println(sb);
}
if (file.isFile() && file.exists()) {
System.out.println("openworkbook.xlsx file open successfully.");
} else {
System.out.println("Error to open openworkbook.xlsx file.");
}
}
/**
* Compute width of a single cell
*
* @param cell the cell whose width is to be calculated
* @param defaultCharWidth the width of a single character
* @param formatter formatter used to prepare the text to be measured
* @param useMergedCells whether to use merged cells
* @return the width in pixels
*/
public static double getCellWidth(
Cell cell, int defaultCharWidth, DataFormatter formatter, boolean useMergedCells) {
Sheet sheet = cell.getSheet();
Workbook wb = sheet.getWorkbook();
Row row = cell.getRow();
int column = cell.getColumnIndex();
int colspan = 1;
for (int i = 0; i < sheet.getNumMergedRegions(); i++) {
CellRangeAddress region = sheet.getMergedRegion(i);
if (containsCell(region, row.getRowNum(), column)) {
if (!useMergedCells) {
// If we're not using merged cells, skip this one and move on to the next.
return -1;
}
cell = row.getCell(region.getFirstColumn());
colspan = 1 + region.getLastColumn() - region.getFirstColumn();
}
}
CellStyle style = cell.getCellStyle();
int cellType = cell.getCellType();
// for formula cells we compute the cell width for the cached formula result
if (cellType == Cell.CELL_TYPE_FORMULA) cellType = cell.getCachedFormulaResultType();
Font font = wb.getFontAt(style.getFontIndex());
AttributedString str;
TextLayout layout;
double width = -1;
if (cellType == Cell.CELL_TYPE_STRING) {
RichTextString rt = cell.getRichStringCellValue();
String[] lines = rt.getString().split("\\n");
for (int i = 0; i < lines.length; i++) {
String txt = lines[i] + defaultChar;
str = new AttributedString(txt);
copyAttributes(font, str, 0, txt.length());
if (rt.numFormattingRuns() > 0) {
// TODO: support rich text fragments
}
layout = new TextLayout(str.getIterator(), fontRenderContext);
if (style.getRotation() != 0) {
/*
* Transform the text using a scale so that it's height is increased by a multiple of the leading,
* and then rotate the text before computing the bounds. The scale results in some whitespace around
* the unrotated top and bottom of the text that normally wouldn't be present if unscaled, but
* is added by the standard Excel autosize.
*/
AffineTransform trans = new AffineTransform();
trans.concatenate(
AffineTransform.getRotateInstance(style.getRotation() * 2.0 * Math.PI / 360.0));
trans.concatenate(AffineTransform.getScaleInstance(1, fontHeightMultiple));
width =
Math.max(
width,
((layout.getOutline(trans).getBounds().getWidth() / colspan) / defaultCharWidth)
+ cell.getCellStyle().getIndention());
} else {
width =
Math.max(
width,
((layout.getBounds().getWidth() / colspan) / defaultCharWidth)
+ cell.getCellStyle().getIndention());
}
}
} else {
String sval = null;
if (cellType == Cell.CELL_TYPE_NUMERIC) {
// Try to get it formatted to look the same as excel
try {
sval = formatter.formatCellValue(cell, dummyEvaluator);
} catch (Exception e) {
sval = String.valueOf(cell.getNumericCellValue());
}
} else if (cellType == Cell.CELL_TYPE_BOOLEAN) {
sval = String.valueOf(cell.getBooleanCellValue()).toUpperCase();
}
if (sval != null) {
String txt = sval + defaultChar;
str = new AttributedString(txt);
copyAttributes(font, str, 0, txt.length());
layout = new TextLayout(str.getIterator(), fontRenderContext);
if (style.getRotation() != 0) {
/*
* Transform the text using a scale so that it's height is increased by a multiple of the leading,
* and then rotate the text before computing the bounds. The scale results in some whitespace around
* the unrotated top and bottom of the text that normally wouldn't be present if unscaled, but
* is added by the standard Excel autosize.
*/
AffineTransform trans = new AffineTransform();
trans.concatenate(
AffineTransform.getRotateInstance(style.getRotation() * 2.0 * Math.PI / 360.0));
trans.concatenate(AffineTransform.getScaleInstance(1, fontHeightMultiple));
width =
Math.max(
width,
((layout.getOutline(trans).getBounds().getWidth() / colspan) / defaultCharWidth)
+ cell.getCellStyle().getIndention());
} else {
width =
Math.max(
width,
((layout.getBounds().getWidth() / colspan) / defaultCharWidth)
+ cell.getCellStyle().getIndention());
}
}
}
return width;
}
