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;
}
