private static void verifyNumericCell(Cell cell, String cell_description) throws ParseException { verifyCellNotEmpty(cell, cell_description); if (cell.getCellType() != Cell.CELL_TYPE_NUMERIC) { throw new ParseException( cell_description + " in row " + cell.getRow().getRowNum() + " is not numeric type!", 0); } }
private static void verifyCategoryCell(Cell cell) throws ParseException { verifyStringCell(cell, "category cell"); Category cat = Category.getCategory(cell.getStringCellValue()); if (cat == Category.UNKNOWN) throw new ParseException( "Unknown category: " + cell.getStringCellValue() + "in row " + cell.getRow().getRowNum(), 0); }
private static void verifySpeakerCell(Cell cell) throws ParseException { verifyStringCell(cell, "speaker cell"); Speaker speaker = Speaker.getSpeaker(cell.getStringCellValue()); if (speaker == Speaker.UNKNOWN) { throw new ParseException( "Unknown speaker: " + cell.getStringCellValue() + "in row " + cell.getRow().getRowNum(), 0); } }
/** * 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()); }
private Integer getMethodRowNumFromExcel(Sheet sheet, String methodName) { Integer rowNum = null; for (Row row : sheet) { // getting first cell value as method name is available in first column Cell cell = row.getCell(0); if (cell != null) { String cellData = cell.getStringCellValue(); if (cellData != null && methodName.equals(cellData.trim())) { rowNum = cell.getRow().getRowNum(); break; } } } LOG.debug("getMethodRowNumFromExcel finished:" + methodName + rowNum); return rowNum; }
private void contentProcess( Sheet tempSheet, Sheet newSheet, int contentRow, ConfirmationRecord data) throws Exception { int tempRowNum = -1; if (data.getAction() == null) { tempRowNum = 7; } else if (data.getAction().getParamKey().equals("CONFIRMATION_LOG_ACTION_ACCEPT")) { tempRowNum = 5; } else { tempRowNum = 6; } Row tempRow = tempSheet.getRow(tempRowNum); Row newRow = newSheet.createRow(contentRow); for (int c = 0; c < tempRow.getLastCellNum(); c++) { Cell tCell = tempRow.getCell(c); Cell cell = newRow.createCell(c, tCell.getCellType()); setValue(cell, data); cell.setCellStyle(tCell.getCellStyle()); cell.getRow().getSheet().setColumnWidth(c, tempRow.getSheet().getColumnWidth(c)); } }
private static void verifyCellNotEmpty(Cell cell, String cell_description) throws ParseException { if (cell.getCellType() == Cell.CELL_TYPE_BLANK) throw new ParseException( cell_description + " in row " + cell.getRow().getRowNum() + " is blank!", 0); }
/** * 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; }
// 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(); } } } } }
// 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) { } } } } }
// sheet Invoice private void MapTableInvoice(Sheet sheet) { System.out.println("Table : " + sheet.getSheetName()); String tableName = sheet.getSheetName(); if (tableName.equals("invoice")) { try { AccountGroup acc1 = new AccountGroup(); acc1.setName("Hành Chính"); acc1.setLabel("Hành Chính"); acc1.setOwner(ManagerAuthenticate.getInstance().getOrganizationLoginId()); acc1.setParent(HRModelManager.getInstance().getRootDepartment()); AccountModelManager.getInstance().saveGroup(acc1); AccountGroup acc2 = new AccountGroup(); acc2.setName("Hội Đồng Quản Trị"); acc2.setLabel("Hội Đồng Quản Trị"); acc2.setOwner(ManagerAuthenticate.getInstance().getOrganizationLoginId()); acc2.setParent(HRModelManager.getInstance().getRootDepartment()); AccountModelManager.getInstance().saveGroup(acc2); AccountGroup acc3 = new AccountGroup(); acc3.setName("Kinh Doanh"); acc3.setLabel("Kinh Doanh"); acc3.setOwner(ManagerAuthenticate.getInstance().getOrganizationLoginId()); acc3.setParent(HRModelManager.getInstance().getRootDepartment()); AccountModelManager.getInstance().saveGroup(acc3); AccountGroup acc4 = new AccountGroup(); acc4.setName("Ban Lãnh Đạo"); acc4.setLabel("Ban Lãnh Đạo"); acc4.setOwner(ManagerAuthenticate.getInstance().getOrganizationLoginId()); acc4.setParent(HRModelManager.getInstance().getRootDepartment()); AccountModelManager.getInstance().saveGroup(acc4); AccountGroup acc5 = new AccountGroup(); acc5.setName("Nghiên Cứu"); acc5.setLabel("Nghiên Cứu"); acc5.setOwner(ManagerAuthenticate.getInstance().getOrganizationLoginId()); acc5.setParent(HRModelManager.getInstance().getRootDepartment()); AccountModelManager.getInstance().saveGroup(acc5); } catch (Exception e1) { e1.printStackTrace(); } Iterator<Row> rowIterator = sheet.iterator(); while (rowIterator.hasNext()) { // Get the row object InvoiceDetail invoicedetail = new InvoiceDetail(); invoicedetail.setType(AccountingModelManager.typeThuChi); invoicedetail.setStatus(Status.Paid); invoicedetail.setCurrencyUnit("VND"); invoicedetail.setCurrencyRate(1); 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() == 0) { // Tên phòng try { // System.out.println("STT " + cell.getRowIndex()); // System.out.println("---- Accountgroup name: " + // cell.getStringCellValue()); if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) { // AccountGroup accountGroup = // AccountModelManager.getInstance().getGroupByName(String.valueOf(cell.getNumericCellValue())); // System.out.println("---- aaaaaaaaaaaa:" + // cell.getNumericCellValue()+" "+accountGroup.getPath()); String path = "hkt/Phòng ban/" + String.valueOf(cell.getNumericCellValue()); invoicedetail.setDepartmentCode(path); } else { // AccountGroup accountGroup = // AccountModelManager.getInstance().getGroupByName(cell.getStringCellValue()); // System.out.println("---- aaaaaaaaaaaa:" + // cell.getStringCellValue()+" "+accountGroup.getPath()); String path = "hkt/Phòng ban/" + cell.getStringCellValue(); invoicedetail.setDepartmentCode(path); } } catch (Exception e) { e.printStackTrace(); } } if (cell.getColumnIndex() == 1) { // Mã dự án try { if (cell.getStringCellValue() != null && !cell.getStringCellValue().equals("")) { // System.out.println("---- Project code: " + // cell.getStringCellValue()); invoicedetail.setProjectCode("/" + cell.getStringCellValue()); } } catch (Exception e) { e.printStackTrace(); } } // Operation Name if (cell.getColumnIndex() == 2) { try { System.out.println("---- Operation Name: " + cell.getStringCellValue()); if (cell.getStringCellValue().length() > 100) { invoicedetail.setInvoiceName(cell.getStringCellValue().substring(0, 100)); } else { invoicedetail.setInvoiceName(cell.getStringCellValue()); } if (invoicedetail.getInvoiceName().trim().length() == 0) { continue; } } catch (Exception e) { e.printStackTrace(); } } // Operation code if (cell.getColumnIndex() == 3) { try { String str = DateUtil.asCompactDateId(new Date()) + ":"; if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) { // System.out.println("---- Operation Code: " + // cell.getNumericCellValue()); invoicedetail.setInvoiceCode( "TC" + str + "TC" + String.valueOf(cell.getNumericCellValue())); } else { // System.out.println("---- Operation Code2: " + // cell.getStringCellValue()); invoicedetail.setInvoiceCode("TC" + str + "TC" + cell.getStringCellValue()); } } catch (Exception e) { e.printStackTrace(); } } // description if (cell.getColumnIndex() == 4) { // System.out.println("---- Description: " + // cell.getStringCellValue()); // invoicedetail.setDescription(String.valueOf(cell.getStringCellValue())); } // idOperationType if (cell.getColumnIndex() == 5) { try { // System.out.println("---- ActivityType: " + // cell.getStringCellValue()); if (cell.getStringCellValue().equals("5")) { invoicedetail.setActivityType(ActivityType.Receipt); } else if (cell.getStringCellValue().equals("6")) { invoicedetail.setActivityType(ActivityType.Payment); } else { invoicedetail.setActivityType(ActivityType.Payment); invoicedetail.setType(AccountingModelManager.typeSanXuat); } } catch (Exception e) { e.printStackTrace(); } } // TotalBeforeDiscount if (cell.getColumnIndex() == 6) { try { if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) { // System.out.println("---- Total: " + // cell.getNumericCellValue()); invoicedetail.setTotal(cell.getNumericCellValue()); } else { // System.out.println("---- Total: " + // cell.getStringCellValue()); invoicedetail.setTotal(Double.parseDouble(cell.getStringCellValue())); } } catch (Exception e) { e.printStackTrace(); } } // PercenDiscount if (cell.getColumnIndex() == 7) { try { if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) { // System.out.println("---- Percent Discount: " + // cell.getNumericCellValue()); invoicedetail.setDiscountRate(cell.getNumericCellValue()); } else { // System.out.println("---- Percent Discount: " + // cell.getStringCellValue()); invoicedetail.setDiscountRate(Double.parseDouble(cell.getStringCellValue())); } } catch (Exception e) { e.printStackTrace(); } } // MoneyDiscount if (cell.getColumnIndex() == 8) { try { if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) { // System.out.println("---- MoneyDiscount: " + // cell.getNumericCellValue()); invoicedetail.setDiscount(cell.getNumericCellValue()); } else { // System.out.println("---- MoneyDiscount: " + // cell.getStringCellValue()); invoicedetail.setDiscount(Double.parseDouble(cell.getStringCellValue())); } } catch (Exception e) { e.printStackTrace(); } } // MoneyTax if (cell.getColumnIndex() == 9) { try { if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) { // System.out.println("---- Total Money After Tax " + // cell.getNumericCellValue()); invoicedetail.setTotalTax(cell.getNumericCellValue()); } else { // System.out.println("---- Total Money After Tax " + // cell.getStringCellValue()); invoicedetail.setTotalTax(Double.parseDouble(cell.getStringCellValue())); } } catch (Exception e) { e.printStackTrace(); } } // TotalMoneyAfterDiscount if (cell.getColumnIndex() == 10) {} // TotalMoneyAfterTax if (cell.getColumnIndex() == 11) { try { if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) { // System.out.println("---- Total Money After tax " + // cell.getNumericCellValue()); invoicedetail.setFinalCharge(cell.getNumericCellValue()); } else { // System.out.println("---- Total Money After tax " + // cell.getStringCellValue()); invoicedetail.setFinalCharge(Double.parseDouble(cell.getStringCellValue())); } } catch (Exception e) { e.printStackTrace(); } } // DateExcute if (cell.getColumnIndex() == 12) { try { if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) { // System.out.println("---- Date Execute " + // cell.getNumericCellValue()); Date startDate = df.parse(String.valueOf(cell.getNumericCellValue())); // System.out.println(startDate); invoicedetail.setStartDate(startDate); invoicedetail.setEndDate(startDate); } else { Date startDate = df.parse(cell.getStringCellValue()); // System.out.println(startDate); invoicedetail.setStartDate(startDate); invoicedetail.setEndDate(startDate); } } catch (Exception e) { e.printStackTrace(); } } } } try { if (checkInvoiDetail(invoicedetail)) { // invoicedetail.calculate(new DefaultInvoiceCalculator()); invoicedetail.setTotalPaid(invoicedetail.getFinalCharge()); InvoiceTransaction transactions = new InvoiceTransaction(); transactions.setTransactionType(TransactionType.CreditCard); transactions.setDepartmentCode(invoicedetail.getDepartmentCode()); transactions.setLocationCode(invoicedetail.getLocationCode()); transactions.setTableCode(invoicedetail.getTableCode()); transactions.setCustomerCode(invoicedetail.getCustomerCode()); transactions.setProjectCode(invoicedetail.getProjectCode()); transactions.setCreatedBy(invoicedetail.getDepartmentCode()); transactions.setCurrencyRate(1); transactions.setCurrencyUnit(invoicedetail.getCurrencyUnit()); transactions.setTotal(invoicedetail.getFinalCharge()); transactions.setTransactionDate(invoicedetail.getStartDate()); if (invoicedetail.getActivityType().equals(ActivityType.Receipt)) { transactions.setActivityType(InvoiceTransaction.ActivityType.Receipt); } else if (invoicedetail.getActivityType().equals(ActivityType.Payment)) { transactions.setActivityType(InvoiceTransaction.ActivityType.Payment); } else { transactions.setActivityType(null); } invoicedetail.add(transactions); invoicedetail.setLocationCode("other"); invoicedetail.setTableCode("other"); invoicedetail.setCustomerCode("hkt/Khách hàng/groupCustomer-other"); // if // (invoicedetail.getType().equals(AccountingModelManager.typeSanXuat)) // { AccountingModelManager.getInstance().saveInvoice(invoicedetail); // } } } catch (Exception e) { e.printStackTrace(); } } } } }
/** * 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; }