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(); } }
/** * 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); }
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; }
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 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; }
/** 通过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(); }
/** * {@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<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; }
/** * Discription:[写一行] * * @param row 行 * @param keyValues 一个beanMap对象,包含着这一行中的所有数据 * @param properties 哪些值需要写入行 * @param dateFormat 日期格式,默认:yyyy-MM-dd * @author:[代超] * @throws Exception * @update:[日期YYYY-MM-DD] [更改人姓名][变更描述] */ public void writeRow(Row row, Map keyValues, Map properties, String dateFormat, T t) throws Exception { // 表格内容样式 CellStyle contentStyle = setContentSheetSysle(row.getSheet().getWorkbook()); if (keyValues == null || keyValues.size() < 1 || row == null) { return; } if (dateFormat == null || "".equals(dateFormat.trim())) { dateFormat = "yyyy-MM-dd"; } Iterator it = keyValues.entrySet().iterator(); for (int i = 0; it.hasNext(); i++) { Map.Entry next = (Map.Entry) it.next(); String dataValue = ObjectUtils.toString(properties.get(next.getKey()), ""); if (dataValue == null || "".equals(dataValue.trim())) { // 无需导出当前字段 i--; continue; } Object value = keyValues.get(next.getKey()); // 值为空时仍然创建单元格并且赋予样式。 Cell cell = row.createCell(i); cell.setCellStyle(contentStyle); if (value == null) { // 当值为空的时候,不必做其他操作了 continue; } if (dataValue.toLowerCase().indexOf("formula") > -1) { // 公式 String formula = ExcelFormula.parseFormula(value.toString()) .replaceAll("-1", String.valueOf(cell.getRowIndex() + 1)); cell.setCellFormula(formula); } else { Class c = PropertyUtils.getPropertyType(t, next.getKey().toString()); writeCell(cell, value, c.getSimpleName(), dateFormat); } } }
// sheet Project private void MapTableProject(Sheet sheet) { System.out.println("Table : " + sheet.getSheetName()); String tableName = sheet.getSheetName(); if (tableName.equals("Du an")) { Iterator<Row> rowIterator = sheet.iterator(); while (rowIterator.hasNext()) { // Get the row object Project project = new Project(); Row row = rowIterator.next(); // // Every row has columns, get the column iterator and iterate over // them Iterator<Cell> cellIterator = row.cellIterator(); if (row.getRowNum() > 0) { while (cellIterator.hasNext()) { Cell cell = cellIterator.next(); if (cell.getRow().getRowNum() == 0) { System.out.println("---- Column name:" + cell.getNumericCellValue()); } else { if (cell.getColumnIndex() == 2) { try { if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) { System.out.println("STT " + cell.getRowIndex()); System.out.println("---- Parent Project: " + cell.getNumericCellValue()); if (String.valueOf(cell.getNumericCellValue()) != null) { project.setParentCode(String.valueOf(cell.getNumericCellValue())); } } else { System.out.println("STT " + cell.getRowIndex()); System.out.println("---- Parent Project: " + cell.getStringCellValue()); if (cell.getStringCellValue() != null) { project.setParentCode(cell.getStringCellValue()); } } } catch (Exception e) { e.printStackTrace(); } } if (cell.getColumnIndex() == 1) { System.out.println("---- Name Project: " + cell.getStringCellValue()); project.setName(cell.getStringCellValue()); if (project.getName().trim().length() == 0) { continue; } } if (cell.getColumnIndex() == 0) { try { if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) { project.setCode(String.valueOf(cell.getNumericCellValue())); } else { project.setCode(cell.getStringCellValue()); } } catch (Exception e) { e.printStackTrace(); } } if (cell.getColumnIndex() == 3) { System.out.println("---- Description: " + cell.getStringCellValue()); project.setDepartmentPart(cell.getStringCellValue()); } if (cell.getColumnIndex() == 4) { System.out.println("---- Description: " + cell.getStringCellValue()); project.setDescription(cell.getStringCellValue()); } } } try { if (checkProject(project)) { project.setStatus("Đã hoàn thành"); RestaurantModelManager.getInstance().saveProject(project); } } catch (Exception e) { e.printStackTrace(); } } } } }
// 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; }
/** 将 excel中的sheet解析成对应的实体 */ @Override public <T> Collection<T> getFinanceReprotEntitysByConfig( Sheet sheet, Map<String, FinanceReportBean> financebean, FinanceReportParamBean financeParam, BaseFile bf, CustInfo ci) throws Exception { // TODO Auto-generated method stub String targetClass = financeParam.getTargetClass(); Class<?> targetObject = Class.forName(targetClass); String sheaderCell = financeParam.getHeaderCell(); String sdataCell = financeParam.getDataCell(); String vheaderCell[] = sheaderCell.split(","); String vdataCell[] = sdataCell.split(","); String headerCell = ""; String dataCell = ""; int headerCellRowIndex = 0; int headerCellCellIndex = 0; int dataCellRowIndex = 0; int dataCellCellIndex = 0; int sheetRowNum = sheet.getLastRowNum(); int datalength = 0; String cellvalue = ""; String headerValue = ""; String field = ""; Boolean flag = true; Boolean empty = false; Map<String, JSONObject> FinanceEntiies = new HashMap<String, JSONObject>(); List finance = new ArrayList(); String year = ""; try { for (int i = 0; i < vheaderCell.length; i++) { datalength = 0; headerCellRowIndex = 0; headerCellCellIndex = 0; dataCellRowIndex = 0; dataCellCellIndex = 0; headerCell = vheaderCell[i]; dataCell = vdataCell[i]; ExeclBean headerInfo = PoiExcelUtil.getExcelBean(headerCell); ExeclBean dataInfo = PoiExcelUtil.getExcelBean(dataCell); headerCellRowIndex = headerInfo.getRow_num() + 1; headerCellCellIndex = headerInfo.getCol_num(); dataCellRowIndex = dataInfo.getRow_num(); dataCellCellIndex = dataInfo.getCol_num(); flag = true; if (headerCellRowIndex > sheetRowNum || dataCellRowIndex > sheetRowNum) { throw new BusinessException( "解析excel出错:配置的标题cell(" + headerCellRowIndex + ")或数据cell(" + dataCellRowIndex + ")大于实际 sheet(" + sheet.getSheetName() + ")的row长度"); } else { Row datarow = sheet.getRow(dataCellRowIndex); datalength = 0; // 获得数据列的长度 for (int j = dataCellCellIndex; j < datarow.getLastCellNum(); j++) { Cell datacell = datarow.getCell(j); cellvalue = PoiExcelUtil.getCellValue(datacell); if (flag == true) { if (null != cellvalue && (!"".equals(cellvalue))) { datalength = datalength + 1; } else { flag = false; } } } List<JSONObject> entity = new ArrayList<JSONObject>(); for (int j = 0; j < datalength; j++) { cellvalue = ""; empty = true; JSONObject financeEntityJson = new JSONObject(); datarow = sheet.getRow(dataCellRowIndex); Cell cell = datarow.getCell(dataCellCellIndex + j); year = PoiExcelUtil.getCellValue(datarow.getCell(dataCellCellIndex + j)); // 隐藏列则不写入数据库 if (!sheet.isColumnHidden(dataCellCellIndex + j)) { if (!"".equals(year.trim())) { for (int h = dataCellRowIndex; h < sheetRowNum; h++) { datarow = sheet.getRow(h); if (null != datarow) { if (headerCellCellIndex < datarow.getLastCellNum()) { if (null != datarow.getCell(headerCellCellIndex)) { headerValue = PoiExcelUtil.getCellValue(datarow.getCell(headerCellCellIndex)); } if (dataCellCellIndex + j < datarow.getLastCellNum()) { if (null != datarow.getCell(dataCellCellIndex + j)) { cell = datarow.getCell(dataCellCellIndex + j); cellvalue = PoiExcelUtil.getCellValue(datarow.getCell(dataCellCellIndex + j)); if (null != cellvalue && (!"".equals(cellvalue))) { field = ""; flag = true; if (h == dataCellRowIndex) { flag = false; } field = this.getExcelTableField( headerValue, (cell.getRowIndex() + 1) + "_" + i, financebean, flag); System.out.println( headerValue + ":" + ((cell.getRowIndex() + 1) + "_" + i) + ":" + cellvalue); if (!"".equals(field)) { financeEntityJson.put(field, cellvalue); empty = false; } } } } } } } if (empty == false) { // 根据年进行合并 if (FinanceEntiies.containsKey(year)) { JSONObject temp = FinanceEntiies.get(year); Iterator it = financeEntityJson.keys(); while (it.hasNext()) { String key = (String) it.next(); temp.put(key, financeEntityJson.get(key).toString()); } FinanceEntiies.put(year, temp); } else { FinanceEntiies.put(year, financeEntityJson); } } } } } } } System.out.println(FinanceEntiies); if (!FinanceEntiies.isEmpty()) { Set<String> key = FinanceEntiies.keySet(); for (Iterator it = key.iterator(); it.hasNext(); ) { year = (String) it.next(); JSONObject jsonObj = FinanceEntiies.get(year); Object sourectObject = targetObject.newInstance(); this.tableService.copyAndOverrideExistedValueFromJSONObject(jsonObj, sourectObject, null); try { BeanUtils.getPropertyDescriptor(sourectObject.getClass(), "upLoadId") .getWriteMethod() .invoke(sourectObject, bf); BeanUtils.getPropertyDescriptor(sourectObject.getClass(), "custId") .getWriteMethod() .invoke(sourectObject, ci); } catch (Exception e) { // TODO Auto-generated catch block throw new BusinessException( "在记录上传文件的ID和客户时出请检查实体类中有没有upLoadId和custId字段" + e.getMessage()); } finance.add(sourectObject); } return finance; } } catch (BusinessException b) { b.printStackTrace(); throw new BusinessException(b.getMessage()); } catch (Exception e) { e.printStackTrace(); // TODO Auto-generated catch block throw new BusinessException(e.getMessage()); } return null; }
/** * Create a new CellAddress object. * * @param cell the Cell to get the location of */ public CellAddress(Cell cell) { this(cell.getRowIndex(), cell.getColumnIndex()); }
// sheet Ca nhan private void MapTableUser(Sheet sheet) throws IOException { System.out.println("Table : " + sheet.getSheetName()); String tableName = sheet.getSheetName(); if (tableName.equals("Ca nhan")) { Iterator<Row> rowIterator = sheet.iterator(); while (rowIterator.hasNext()) { // Get the row object account = new Account(); account.setLoginId(new Date().getTime() + ""); account.setPassword("0000"); account.setType(Type.USER); account.setLastLoginTime(new Date()); basicInformation = new BasicInformation(); contact = new Contact(); customer = new Customer(); customer.setOrganizationLoginId(ManagerAuthenticate.getInstance().getOrganizationLoginId()); String str = new Date().getTime() + ""; customer.setCode(str); Row row = rowIterator.next(); // // Every row has columns, get the column iterator and iterate over // them Iterator<Cell> cellIterator = row.cellIterator(); if (row.getRowNum() > 0) { while (cellIterator.hasNext()) { Cell cell = cellIterator.next(); if (cell.getRow().getRowNum() == 0) { System.out.println("---- Column name:" + cell.getStringCellValue()); } else { if (cell.getColumnIndex() == 0) { // firstName try { System.out.println("STT " + cell.getRowIndex()); System.out.println("---- firstName: " + cell.getStringCellValue()); if (cell.getStringCellValue() != null && !cell.getStringCellValue().equals("")) { basicInformation.setFirstName(cell.getStringCellValue()); } } catch (Exception e) { e.printStackTrace(); } } if (cell.getColumnIndex() == 1) { // lastName try { System.out.println("---- lastName: " + cell.getStringCellValue()); if (cell.getStringCellValue() != null && !cell.getStringCellValue().equals("")) { basicInformation.setLastName( basicInformation.getFirstName() + " " + cell.getStringCellValue()); customer.setName(basicInformation.getLastName()); } } catch (Exception e) { e.printStackTrace(); } } if (cell.getColumnIndex() == 2) { // birthday try { if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) { System.out.println("---- birthday " + cell.getNumericCellValue()); basicInformation.setBirthday(String.valueOf(cell.getNumericCellValue())); } else { System.out.println("---- birthday " + cell.getStringCellValue()); basicInformation.setBirthday(cell.getStringCellValue().toString()); } } catch (Exception e) { e.printStackTrace(); } } if (cell.getColumnIndex() == 3) { // gender try { System.out.println("---- gender: " + cell.getStringCellValue()); if (cell.getStringCellValue() != null && !cell.getStringCellValue().equals("")) { basicInformation.setGender(cell.getStringCellValue()); } } catch (Exception e) { e.printStackTrace(); } } if (cell.getColumnIndex() == 4) { // indentityCard try { if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) { System.out.println("---- indentityCard: " + cell.getNumericCellValue()); basicInformation.setPersonalId(String.valueOf(cell.getNumericCellValue())); } else { System.out.println("---- indentityCard: " + cell.getStringCellValue()); basicInformation.setPersonalId((cell.getStringCellValue())); } } catch (Exception e) { e.printStackTrace(); } } // address if (cell.getColumnIndex() == 5) { System.out.println("---- address: " + cell.getStringCellValue()); contact.setAddressNumber(cell.getStringCellValue()); } // telephone if (cell.getColumnIndex() == 6) { try { if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) { System.out.println("---- telephone1: " + cell.getNumericCellValue()); String[] phone = String.valueOf(cell.getNumericCellValue()).split(","); contact.setPhone(phone); } else { System.out.println("---- telephone2: " + cell.getStringCellValue()); String[] phone = cell.getStringCellValue().split(","); contact.setPhone(phone); } } catch (Exception e) { e.printStackTrace(); } } if (cell.getColumnIndex() == 7) { // mobile try { if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) { System.out.println("---- mobile1: " + cell.getNumericCellValue()); String[] arraymobile = String.valueOf(cell.getNumericCellValue()).split(","); contact.setMobile(arraymobile); } else { System.out.println("---- mobile2: " + cell.getStringCellValue()); String[] arraymobile = cell.getStringCellValue().split(","); contact.setMobile(arraymobile); } } catch (Exception e) { } } // Email if (cell.getColumnIndex() == 8) { try { String string = new Date().getTime() + ""; int str1 = cell.getStringCellValue().length(); String str2 = cell.getStringCellValue(); System.out.println("---- Email: " + cell.getStringCellValue()); if (str1 > 4) { account.setEmail(str2); } else { account.setEmail(string + "@gmail.com"); } } catch (Exception e) { e.printStackTrace(); } } // Description if (cell.getColumnIndex() == 9) { try { if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) { System.out.println("---- telephone1: " + cell.getNumericCellValue()); String phone = String.valueOf(cell.getNumericCellValue()); customer.setDescription(phone); } else { System.out.println("---- telephone2: " + cell.getStringCellValue()); customer.setDescription(cell.getStringCellValue()); } } catch (Exception e) { e.printStackTrace(); } } } } try { Profile profileBasic = new Profile(); BasicInformation basic = this.basicInformation; profileBasic.put(BasicInformation.LAST_NAME, basic.getLastName()); profileBasic.put(BasicInformation.GENDER, basic.getGender()); profileBasic.put(BasicInformation.BIRTHDAY, basic.getBirthday()); profileBasic.put(BasicInformation.PERSONAL_ID, basic.getPersonalId()); Profiles profiles = new Profiles(); profiles.setBasic(profileBasic); account.setProfiles(profiles); List<Contact> contacts = new ArrayList<Contact>(); contacts.add(contact); account.setContacts(contacts); Account acc = AccountModelManager.getInstance().saveAccount(account); customer.setLoginId(acc.getLoginId()); customer.setType("Cá nhân"); if (contact.getMobile().length > 0) { customer.setMobile(contact.getMobile()[0]); } try { customer.setBirthDay(new SimpleDateFormat("dd/MM/yyyy").parse(basic.getBirthday())); } catch (Exception e) { } CustomerModelManager.getInstance().saveCustomer(customer); } catch (Exception e) { } } } } }
// sheet DN private void MapTableOrganization(Sheet sheet) throws IOException { System.out.println("Table : " + sheet.getSheetName()); String tableName = sheet.getSheetName(); List<String> list = new ArrayList<String>(); if (tableName.equals("DN")) { Iterator<Row> rowIterator = sheet.iterator(); while (rowIterator.hasNext()) { // Get the row object account = new Account(); account.setLoginId(new Date().getTime() + ""); account.setPassword("0000"); account.setType(Type.ORGANIZATION); account.setLastLoginTime(new Date()); organizationBasic = new OrganizationBasic(); contact = new Contact(); customer = new Customer(); customer.setOrganizationLoginId(ManagerAuthenticate.getInstance().getOrganizationLoginId()); Row row = rowIterator.next(); // // Every row has columns, get the column iterator and iterate over // them Iterator<Cell> cellIterator = row.cellIterator(); if (row.getRowNum() > 0) { while (cellIterator.hasNext()) { Cell cell = cellIterator.next(); if (cell.getRow().getRowNum() == 0) { System.out.println("---- Column name:" + cell.getStringCellValue()); } else { if (cell.getColumnIndex() == 0) { // enterpriseName try { System.out.println("STT " + cell.getRowIndex()); System.out.println("---- Enterprise Name: " + cell.getStringCellValue()); if (cell.getStringCellValue() != null && !cell.getStringCellValue().equals("")) { customer.setName(cell.getStringCellValue()); organizationBasic.setName(cell.getStringCellValue()); } } catch (Exception e) { e.printStackTrace(); } } if (cell.getColumnIndex() == 1) { // enterpriseCode try { if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) { System.out.println("---- Enterprise code: " + cell.getNumericCellValue()); if (list.indexOf(String.valueOf(cell.getNumericCellValue())) > 0) { customer.setCode(String.valueOf(new Date().getTime())); } else { customer.setCode(String.valueOf(cell.getNumericCellValue())); list.add(customer.getCode()); } } else { System.out.println("---- Enterprise code: " + cell.getStringCellValue()); if (list.indexOf(cell.getStringCellValue()) > 0) { customer.setCode(String.valueOf(new Date().getTime())); } else { customer.setCode(cell.getStringCellValue()); list.add(customer.getCode()); } } } catch (Exception e) { e.printStackTrace(); } } // Slogan if (cell.getColumnIndex() == 2) { try { System.out.println("---- Slogan: " + cell.getStringCellValue()); organizationBasic.setSlogan(cell.getStringCellValue()); } catch (Exception e) { e.printStackTrace(); } } // Address if (cell.getColumnIndex() == 3) { try { if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) { System.out.println("---- Address: " + cell.getNumericCellValue()); contact.setAddressNumber(String.valueOf(cell.getNumericCellValue())); } else { System.out.println("---- Address: " + cell.getStringCellValue()); contact.setAddressNumber(cell.getStringCellValue()); } } catch (Exception e) { e.printStackTrace(); } } // Telephone if (cell.getColumnIndex() == 4) { try { if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) { System.out.println("---- Telephone: " + cell.getNumericCellValue()); String[] arrayPhone = String.valueOf(cell.getNumericCellValue()).split(","); contact.setPhone(arrayPhone); if (arrayPhone.length > 0) { customer.setMobile(arrayPhone[0]); } } else { System.out.println("---- Telephone: " + cell.getStringCellValue()); String[] arrayPhone = cell.getStringCellValue().split(","); contact.setPhone(arrayPhone); if (arrayPhone.length > 0) { customer.setMobile(arrayPhone[0]); } } } catch (Exception e) { e.printStackTrace(); } } // Email if (cell.getColumnIndex() == 6) { try { String str = new Date().getTime() + ""; int str1 = cell.getStringCellValue().length(); String str2 = cell.getStringCellValue(); System.out.println("---- Email: " + cell.getStringCellValue()); if (str1 > 4) { account.setEmail(str2); } else { account.setEmail(str + "@gmail.com"); } } catch (Exception e) { e.printStackTrace(); } } // Website if (cell.getColumnIndex() == 7) { try { if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) { customer.setDescription(String.valueOf(cell.getNumericCellValue())); } else { if (cell.getStringCellValue() != null) { customer.setDescription(cell.getStringCellValue()); } } } catch (Exception e) { e.printStackTrace(); } } // Description if (cell.getColumnIndex() == 8) { try { if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) { System.out.println("---- Description: " + cell.getNumericCellValue()); organizationBasic.setDescription(String.valueOf(cell.getNumericCellValue())); } else { System.out.println("---- Description: " + cell.getStringCellValue()); if (cell.getStringCellValue() != null) { if (cell.getStringCellValue().length() > 100) { organizationBasic.setDescription( cell.getStringCellValue().substring(0, 100)); } else { organizationBasic.setDescription(cell.getStringCellValue()); } } } } catch (Exception e) { e.printStackTrace(); } } } } try { if (checkAccount(account)) { Profile profileOrgBasic = new Profile(); OrganizationBasic orgBasic = this.organizationBasic; profileOrgBasic.put(OrganizationBasic.NAME, orgBasic.getName()); profileOrgBasic.put(OrganizationBasic.SLOGAN, orgBasic.getSlogan()); profileOrgBasic.put(OrganizationBasic.DESCRIPTION, orgBasic.getDescription()); Profiles profiles = new Profiles(); profiles.setBasic(profileOrgBasic); account.setProfiles(profiles); List<Contact> contacts = new ArrayList<Contact>(); contacts.add(contact); account.setContacts(contacts); Account acc = AccountModelManager.getInstance().saveAccount(account); customer.setLoginId(acc.getLoginId()); customer.setType("Doanh nghiệp"); customer.setAddress(contact.getAddressNumber()); CustomerModelManager.getInstance().saveCustomer(customer); } } catch (Exception e) { } } } } }
/** * Discription:[写一个单元格] * * @param cell 单元格 * @param value 写入的值 * @param valueType 写入的值的类型 * @param dateFormat 日期格式,默认yyyy-MM-dd * @author:[代超] * @update:[日期YYYY-MM-DD] [更改人姓名][变更描述] */ public void writeCell(Cell cell, Object value, String valueType, String dateFormat) { if (cell == null || value == null) { return; } if (dateFormat == null || "".equals(dateFormat.trim())) { dateFormat = "yyyy-MM-dd"; } String cellValue = ""; if ("String".equals(valueType)) { cellValue = value.toString(); } else if ("int".equals(valueType)) { cellValue = String.valueOf(value); } else if ("float".equals(valueType)) { cellValue = String.valueOf(value); } else if ("double".equals(valueType)) { cellValue = String.valueOf(value); } else if ("Number".equals(valueType)) { cellValue = String.valueOf(value); } else if ("BigDecimal".equals(valueType)) { cellValue = String.valueOf(value); } else if ("byte[]".equals(valueType)) { // 有图片时,设置行高为60px; cell.getRow().setHeightInPoints(60); // 设置图片所在列宽度为80px,注意这里单位的一个换算 cell.getSheet().setColumnWidth(cell.getColumnIndex(), (short) (35.7 * 80)); // sheet.autoSizeColumn(i); byte[] bsValue = (byte[]) value; HSSFClientAnchor anchor = new HSSFClientAnchor( 0, 0, 1023, 255, (short) 6, cell.getRowIndex(), (short) 6, cell.getRowIndex()); anchor.setAnchorType(2); // 声明一个画图的顶级管理器 cell.getSheet() .createDrawingPatriarch() .createPicture( anchor, cell.getSheet().getWorkbook().addPicture(bsValue, HSSFWorkbook.PICTURE_TYPE_JPEG)); return; } else if ("Date".equals(valueType)) { SimpleDateFormat sdf = new SimpleDateFormat(dateFormat); cellValue = sdf.format(value); } else if ("boolean".equals(valueType)) { boolean bool = (Boolean) value; if (bool) { cellValue = "是"; } else { cellValue = "否"; } } else if ("Boolean".equals(valueType)) { boolean bool = (Boolean) value; if (bool) { cellValue = "是"; } else { cellValue = "否"; } } else { cellValue = String.valueOf(value); } cell.setCellValue(cellValue); return; }