@Override
public List<String> getHeaderColumns() {
if (headerColumns == null) {
headerColumns = new ArrayList<>();
Row row = dataSheet.getRow(0);
int colCount = row != null ? row.getLastCellNum() : 0;
if (row == null || colCount == 0) {
LogMgr.logError(
"ExcelReader.getHeaderColumns()",
"Cannot retrieve column names because no data is available in the first row of the sheet: "
+ dataSheet.getSheetName(),
null);
String msg = ResourceMgr.getFormattedString("ErrExportNoCols", dataSheet.getSheetName());
messages.append(msg);
messages.appendNewLine();
return headerColumns;
}
for (int i = 0; i < colCount; i++) {
Cell cell = row.getCell(i);
Object value = getCellValue(cell);
if (value != null) {
headerColumns.add(value.toString());
} else {
headerColumns.add("Col" + Integer.toString(i));
}
}
}
return headerColumns;
}
public void make(File source, DataSet header, DataSet master) throws Exception {
this.header = header;
this.master = master;
if (header == null || master == null) throw new Exception("Dataset is empty");
long t = System.currentTimeMillis();
InputStream inp = new FileInputStream(source);
Workbook oldBook = WorkbookFactory.create(inp);
Sheet oldSheet = oldBook.getSheetAt(0);
Workbook newBook = new HSSFWorkbook();
Sheet newSheet = newBook.createSheet(oldSheet.getSheetName());
init(newBook);
process(oldSheet, newSheet);
File target = File.createTempFile("libra", ".xls");
target.deleteOnExit();
FileOutputStream fileOut = new FileOutputStream(target);
newBook.write(fileOut);
fileOut.close();
oldBook.close();
inp.close();
Desktop.getDesktop().open(target);
System.out.println(System.currentTimeMillis() - t);
}
private boolean convertToXML() throws InvalidFormatException {
Sheet sheet = null;
System.out.println("Starter konvertering til SDC XML");
int numSheets = this.workbook.getNumberOfSheets();
boolean foundAtleastOneValid = false;
for (int i = 0; i < numSheets; i++) {
sheet = this.workbook.getSheetAt(i);
String name = sheet.getSheetName();
if (name.contains("innland")) {
System.out.println("Fant innland-regneark, prosesserer");
foundAtleastOneValid = true;
this.processInnland(sheet);
} else if (name.contains("utland")) {
System.out.println("Fant utland-regneark, vet ikke hvordan dette prosesseres");
} else {
System.out.println("Fant ukjent regneark: " + name + ", ignorerer");
}
}
if (!foundAtleastOneValid) {
throw new InvalidFormatException(
"Fant ikke innland-regneark, ingen output laget. (utland-konvertering støttes ikke atm.)");
}
return true;
}
/**
* 出力対象のシートか判定する
*
* @param sheet テンプレートシート
* @param reportBook 帳票ワークブック情報
* @return
*/
private boolean isOutputSheet(Sheet sheet, ReportBook reportBook) {
for (ReportSheet reportSheet : reportBook.getReportSheets()) {
if (reportSheet != null && sheet.getSheetName().equals(reportSheet.getSheetName())) {
return true;
}
}
return false;
}
// traversal cell
public void traversalCell(String filePath) {
try {
Workbook workBook = null;
try {
workBook = new XSSFWorkbook(filePath); // 支持2007
} catch (Exception ex) {
workBook = new HSSFWorkbook(new FileInputStream(filePath)); // 支持2003及以前
}
// 获得Excel中工作表个数
System.out.println("工作表个数 :" + workBook.getNumberOfSheets());
// 循环每个工作表
for (int i = 0; i < workBook.getNumberOfSheets(); i++) {
// 创建工作表
Sheet sheet = workBook.getSheetAt(i);
int rows = sheet.getPhysicalNumberOfRows(); // 获得行数
System.out.println(
"工作表" + sheet.getSheetName() + " 行数 :" + sheet.getPhysicalNumberOfRows());
if (rows > 0) {
sheet.getMargin(Sheet.TopMargin);
for (int r = 0; r < rows; r++) { // 行循环
Row row = sheet.getRow(r);
if (row != null) {
int cells = row.getLastCellNum(); // 获得列数
for (short c = 0; c < cells; c++) { // 列循环
Cell cell = row.getCell(c);
if (cell != null) {
String value = getCellData(cell);
System.out.println("第" + r + "行 " + "第" + c + "列：" + value);
}
}
}
}
}
// 查询合并的单元格
for (i = 0; i < sheet.getNumMergedRegions(); i++) {
System.out.println("第" + i + "个合并单元格");
CellRangeAddress region = sheet.getMergedRegion(i);
int row = region.getLastRow() - region.getFirstRow() + 1;
int col = region.getLastColumn() - region.getFirstColumn() + 1;
System.out.println("起始行:" + region.getFirstRow());
System.out.println("起始列:" + region.getFirstColumn());
System.out.println("所占行:" + row);
System.out.println("所占列:" + col);
}
}
} catch (Exception ex) {
ex.printStackTrace();
}
}
private void writeDataToCell(Sheet sheet, int rowNum, int columnNum, Object value) {
LOG.debug(
"writeDataToCell started:"
+ sheet.getSheetName()
+ ",rowNum:"
+ rowNum
+ ",columnNum:"
+ columnNum
+ ",value:"
+ value);
Row row = sheet.getRow(rowNum);
if (row == null) {
row = sheet.createRow(rowNum);
}
Cell cell = row.getCell(columnNum);
if (cell == null) {
int lastColumn = row.getLastCellNum();
if (lastColumn < 0) lastColumn = 0;
// if there are any blank cells, then last cell number will return value higher than actual
// column number.
// so set that value back to your required column number.
if (lastColumn > columnNum) {
lastColumn = columnNum;
}
for (int i = lastColumn; i <= columnNum; i++) {
cell = row.createCell(i);
}
}
if (value instanceof String) {
cell.setCellType(Cell.CELL_TYPE_STRING);
cell.setCellValue((String) trimActualResult(value.toString()));
} else if (value instanceof Double) {
cell.setCellType(Cell.CELL_TYPE_NUMERIC);
cell.setCellValue((Double) value);
} else if (value instanceof Integer) {
cell.setCellType(Cell.CELL_TYPE_NUMERIC);
cell.setCellValue((Integer) value);
} else if (value instanceof Long) {
cell.setCellType(Cell.CELL_TYPE_NUMERIC);
cell.setCellValue((Long) value);
} else if (value instanceof Float) {
cell.setCellType(Cell.CELL_TYPE_NUMERIC);
cell.setCellValue((Float) value);
} else if (value != null) {
cell.setCellType(Cell.CELL_TYPE_STRING);
cell.setCellValue((String) trimActualResult(value.toString()));
}
}
@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;
}
// 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) {
}
}
}
}
}
@Override
public RecordSet getRecords() throws MalformedSourceException {
try {
openReader();
} catch (IOException e) {
logger_.info("Problem loading file: " + filename + " (" + e.getMessage() + ")");
throw new MalformedSourceException(
"Problem loading file: " + filename + " (" + e.getMessage() + ")");
} catch (InvalidFormatException e) {
logger_.info("Problem loading file: " + filename + " (" + e.getMessage() + ")");
throw new MalformedSourceException(
"Problem loading file: " + filename + " (" + e.getMessage() + ")");
}
RecordSet ret = new RecordSet();
// Currently we need this flag in order for
// TransformationEngine not to go into an infinite loop.
if (!isRead_) {
logger_.info("Opening file: " + filename);
int nSheets = wb_.getNumberOfSheets();
logger_.info("number of sheets: " + nSheets);
for (int i = 0; i < nSheets; i++) {
Sheet cSheet = wb_.getSheetAt(i);
String cSheetName = cSheet.getSheetName();
for (int j = skipLines_; j <= cSheet.getLastRowNum(); j++) {
if (ignoreLinesAfter_ != 0 && j >= ignoreLinesAfter_) {
break;
}
Row row = cSheet.getRow(j);
MapRecord rec = new MapRecord();
for (int k = 0; k < row.getLastCellNum(); k++) {
if (!fieldMap_.keySet().contains(k)) {
continue;
}
StringValue val;
Cell cCell = row.getCell(k);
if (cCell == null) {
continue;
}
int cellType = cCell.getCellType();
switch (cellType) {
case Cell.CELL_TYPE_STRING:
val = new StringValue(cCell.getStringCellValue());
break;
case Cell.CELL_TYPE_NUMERIC:
if (DateUtil.isCellDateFormatted(cCell)) {
SimpleDateFormat sdf = new SimpleDateFormat("dd/MM/yyyy");
val = new StringValue(sdf.format(cCell.getDateCellValue()));
} else {
val = new StringValue(String.valueOf(cCell.getNumericCellValue()));
}
break;
case Cell.CELL_TYPE_BLANK:
val = new StringValue("");
break;
case Cell.CELL_TYPE_BOOLEAN:
val = new StringValue(String.valueOf(cCell.getBooleanCellValue()));
break;
default:
val = new StringValue("Unsupported cell type");
}
rec.addValue(fieldMap_.get(k), val);
}
// TODO remove the hardcoded value
rec.addValue("ExcelSheetName", new StringValue(cSheetName));
ret.addRecord(rec);
}
}
isRead_ = true;
}
return ret;
}
/**
* 将excel内的内容读取到xml文件中，并添加dtd验证
*
* @param xmlFile
* @param sheetNum
* @return 1代表成功，0失败，-1超过最大sheet,2跳过当前失败的xml
*/
public int excelToXml(String xmlFile, int sheetNum) {
if (sheetNum >= workBook.getNumberOfSheets()) return -1;
else sheet = workBook.getSheetAt(sheetNum);
xmlFile = xmlFile + ".xml";
try {
Document document = DocumentHelper.createDocument();
// 使用sheet名称命名跟节点
String rootName = sheet.getSheetName().replaceAll(" ", "");
Element root = document.addElement(rootName);
// 添加dtd文件说明
DocumentType documentType = new DOMDocumentType();
documentType.setElementName(rootName);
List<ElementDecl> declList = new ArrayList<>();
declList.add(new ElementDecl(rootName, "(row*)"));
// 判断sheet是否为空,为空则不执行任何操作
if (sheet.getRow(0) == null) return 1;
// 遍历sheet第一行,获取元素名称
row = sheet.getRow(0);
String rowString = null;
List<String> pcdataList = new ArrayList<>();
for (int y = 0; y < row.getPhysicalNumberOfCells(); y++) {
Object object = this.getCellValueObject(0, y);
// 判断是否有合并单元格，有的话跳过
if (object == null) return 2;
// 去除表头字符串中的空格
String objectStr = object.toString().replaceAll(" ", "");
if (rowString != null) rowString += "|" + objectStr;
else rowString = objectStr;
pcdataList.add(objectStr);
}
// 设置行节点
declList.add(new ElementDecl("row", "(" + rowString + ")*"));
// 遍历list设置行的下级节点
for (String tmp : pcdataList) {
declList.add(new ElementDecl(tmp, "(#PCDATA)"));
}
documentType.setInternalDeclarations(declList);
// 遍历读写excel数据到xml中
for (int x = 1; x < sheet.getLastRowNum(); x++) {
row = sheet.getRow(x);
Element rowElement = root.addElement("row");
for (int y = 0; y < row.getPhysicalNumberOfCells(); y++) {
// cell = row.getCell(y);
Object object = this.getCellValueObject(x, y);
if (object != null) {
// 将sheet第一行的行首元素当作元素名称
String pcdataString = pcdataList.get(y);
Element element = rowElement.addElement(pcdataString);
// Element element = rowElement.addElement("name");
element.setText(object.toString());
}
}
}
// 写入文件和dtd
document.setDocType(documentType);
this.docToXmlFile(document, xmlFile);
} catch (Exception e) {
e.printStackTrace();
}
return 1;
}
public void parse(Sheet sheet) {
logger.debug("开始解析Sheet页:" + sheet.getSheetName());
startSheet(sheet);
int rowCount = sheet.getPhysicalNumberOfRows();
logger.debug("行数：" + rowCount);
// 遇到表头区域的时候记录开始列
int headerStartCol = 0;
for (Row r : sheet) {
HSSFRow row = (HSSFRow) r;
if (row == null) continue;
this.rowIndex = row.getRowNum();
if (rowIndex < startRow) {
continue;
}
// sundl: row.getFirstCellNum()不是很稳定，第一列的空格有时候会是null，有时候又是空字符串，这样导致这个值不可信
// 目前强制写成1， 后续可以考虑改成自己写找第一个非空列。
int firstCellNum = row.getFirstCellNum();
if (firstCellNum == -1) continue;
else firstCellNum = 1;
HSSFCell firstCell = row.getCell(firstCellNum);
String firstcellString = getCellStringValue(firstCell, evaluator);
if (!firstcellString.isEmpty()) {
if (aresStart.contains(rowIndex)) {
endArea();
logger.debug("Area开始,行号: " + rowIndex);
startArea(firstcellString);
} else if (areaTags.indexOf(firstcellString) != -1) {
endArea();
logger.debug("Area开始,行号: " + rowIndex);
startArea(firstcellString);
}
if (blockStart.keySet().contains(rowIndex)) {
endBlock();
startBlock(firstcellString, blockStart.get(rowIndex));
} else if (blocks.containsKey(firstcellString)) {
endBlock();
startBlock(firstcellString, blocks.get(firstcellString));
// Text区域第一行暂时不解析
if (currentBlockType == BlockTypes.TEXT) {
continue;
}
}
}
String[] strings = readRowStrings(row, 1, evaluator);
switch (currentBlockType) {
case BlockTypes.KEY_VALUE:
parseKeyValue(row, 1);
break;
case BlockTypes.TABLE:
if (!headerFound) {
// header按理说不应该存在空字符串，开头应该不会，但结尾可能存在
while (StringUtils.isEmpty(strings[strings.length - 1])) {
strings = (String[]) ArrayUtils.remove(strings, strings.length - 1);
}
headerStartCol = firstCellNum;
header(strings);
headerFound = true;
} else {
strings = POIUtils.readRowStrings(row, headerStartCol, evaluator);
row(strings);
}
break;
case BlockTypes.TEXT:
text.append(StringUtils.join(strings));
text.append('\n');
break;
}
}
endBlock();
endArea();
endSheet();
}
/** 将 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;
}
// 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();
}
}
}
}
}
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;
}
// 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();
}
}
}
}
}
private void rebuildFormula(Sheet sheet) {
ProductType type = ProductType.get(sheet.getSheetName());
SizeInfo sizeInfo = SizeInfo.getByType(type);
// size -> list<columnIndex>
Map<String, List<Integer>> columns = new HashMap<String, List<Integer>>();
for (int colIndex = 6 + sizeInfo.getSizeNumber() + 1;
colIndex < sheet.getRow(2).getLastCellNum();
colIndex++) {
Row row = sheet.getRow(2);
String value = row.getCell(colIndex).getStringCellValue().trim();
if ("数量".equals(value) || StringUtils.isBlank(value)) {
continue;
}
if (!columns.containsKey(value)) {
columns.put(value, new ArrayList<Integer>());
}
columns.get(value).add(colIndex);
}
// for(int rowIndex = 3; rowIndex <= sheet.getLastRowNum(); rowIndex++) {
// Row row = sheet.getRow(rowIndex);
//
// for(int colIndex = 7; colIndex <= 7 + sizeRange - 1; colIndex++) {
// String size = sheet.getRow(2).getCell(colIndex).getStringCellValue().trim();
//
// List<Integer> cols = columns.get(size);
// row.getCell(colIndex).setCellType(Cell.CELL_TYPE_FORMULA);
// row.getCell(colIndex).setCellFormula(this.buildSumFormula(rowIndex, cols));
// }
// }
for (int rowIndex = 4; rowIndex <= sheet.getLastRowNum(); rowIndex++) {
Row row = sheet.getRow(rowIndex);
if (row == null) {
continue;
}
for (int colIndex = 7; colIndex <= row.getLastCellNum(); colIndex++) {
if ("数量".equals(CellUtils.getStringValue(sheet.getRow(2).getCell(colIndex)))) {
Cell cell = row.getCell(colIndex);
cell.setCellType(Cell.CELL_TYPE_FORMULA);
String formula =
String.format(
"SUM(%s%s:%s%s)",
CellUtils.convertToABC(colIndex - sizeInfo.getSizeNumber() + 1),
rowIndex + 1,
CellUtils.convertToABC(colIndex - 1 + 1),
rowIndex + 1);
cell.setCellFormula(formula);
}
}
}
for (int rowIndex = 4; rowIndex <= sheet.getLastRowNum(); rowIndex++) {
Row row = sheet.getRow(rowIndex);
if ("合计".equals(CellUtils.getStringValue(row.getCell(0)))) {
for (int colIndex = 7; colIndex <= sheet.getLastRowNum(); colIndex++) {
Cell cell = row.getCell(colIndex);
if (cell == null) {
continue;
}
cell.setCellType(Cell.CELL_TYPE_FORMULA);
String formula =
String.format(
"SUM(%s%s:%s%s)",
CellUtils.convertToABC(colIndex + 1),
5,
CellUtils.convertToABC(colIndex + 1),
rowIndex);
cell.setCellFormula(formula);
}
}
}
}
/**
* Discription:[读取xls或者xlsx文档的所有页，并且以map格式输出：sheet名：sheet内容。
* 其中sheet内容是以列表存储，列表中存储的是String数组，sheet中的每一行则是一个String数组]
*
* @param wb 工作表格,文档
* @return
* @author:[代超]
* @update:[日期YYYY-MM-DD] [更改人姓名][变更描述]
*/
public Map<String, Object> readExcelFile(Workbook wb) {
if (wb == null) {
return null;
}
DecimalFormat df = new DecimalFormat("#.##");
int sheetNumber = wb.getNumberOfSheets();
List list = new ArrayList();
Map<String, Object> excelMap = new HashMap<String, Object>();
for (int i = 0; i < sheetNumber; i++) {
Sheet sheet = wb.getSheetAt(i);
List<String[]> strs = new ArrayList<String[]>();
// 注意得到的行数是基于0的索引 遍历所有的行
for (int k = 0; k <= sheet.getLastRowNum(); k++) {
Row rows = sheet.getRow(k);
if (rows == null) {
continue;
}
String[] str = new String[rows.getLastCellNum()];
// 遍历每一列
for (int l = 0; l < rows.getLastCellNum(); l++) {
Cell cell = rows.getCell(l);
// 单元格类型
if (cell == null) {
continue;
}
int cellType = cell.getCellType();
switch (cellType) {
case 0: // 数字类型
str[l] = df.format(cell.getNumericCellValue());
break;
case 1: // String类型
str[l] = cell.getStringCellValue();
break;
case 2: // Formula Cell type 公式类型
FormulaEvaluator he = null;
try {
he = new HSSFFormulaEvaluator((HSSFWorkbook) wb);
} catch (Exception e) {
he = new XSSFFormulaEvaluator((XSSFWorkbook) wb);
}
if (he != null && he.evaluateFormulaCell(cell) == 0) {
str[l] = df.format(he.evaluate(cell).getNumberValue());
} else {
str[l] = he.evaluate(cell).getStringValue();
}
break;
case 3: // 空格
break;
case 4: // Boolean Cell type
str[l] = BooleanUtils.toStringTrueFalse(cell.getBooleanCellValue());
break;
case 5: // Errors
break;
default: // 其它格式的数据
break;
}
}
strs.add(str);
}
// list.add(strs);
excelMap.put(sheet.getSheetName(), strs);
}
return excelMap;
}
/**
* Read Excel format file.
*
* @param filename
*/
private void readExcel(String filename) {
try {
FileInputStream fin = new FileInputStream(filename);
if (flgDoubleQuotes) {
delimiter = "\"" + delimiter + "\"";
}
try {
Workbook wb = WorkbookFactory.create(fin);
// HSSFFormulaEvaluator.evaluateAllFormulaCells(wb);
for (int i = 0; i < wb.getNumberOfSheets(); i++) {
Sheet sheet = wb.getSheetAt(i);
String sheetname = sheet.getSheetName();
String fname = sheetname;
if (delimiter.equals("\t")) {
fname = fname + ".tsv";
} else {
fname = fname + ".csv";
}
File file = new File(fname);
FileOutputStream fout = new FileOutputStream(file);
OutputStreamWriter ow = new OutputStreamWriter(fout, charset);
BufferedWriter bw = new BufferedWriter(ow);
for (Iterator<Row> rowIter = sheet.rowIterator(); rowIter.hasNext(); ) {
Row row = rowIter.next();
String tmp = "";
if (flgDoubleQuotes) {
tmp = "\"";
}
if (row != null) {
for (int k = 0; k < row.getLastCellNum(); k++) {
Cell cell = row.getCell(k);
// CellValue celv = evaluator.evaluate(cell);
if (cell == null) {
tmp = tmp + delimiter;
continue;
}
switch (cell.getCellType()) {
case Cell.CELL_TYPE_BLANK:
tmp = tmp + " " + delimiter;
break;
case Cell.CELL_TYPE_NUMERIC:
tmp = tmp + getNumericValue(cell) + delimiter;
break;
case Cell.CELL_TYPE_STRING:
tmp = tmp + getStringValue(cell) + delimiter;
break;
case Cell.CELL_TYPE_FORMULA:
try {
FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator();
CellValue value = evaluator.evaluate(cell);
if (value.getCellType() == Cell.CELL_TYPE_NUMERIC) {
tmp = tmp + getNumericValue(cell) + delimiter;
} else if (value.getCellType() == Cell.CELL_TYPE_STRING) {
tmp = tmp + getStringValue(cell) + delimiter;
}
} catch (FormulaParseException e) {
// error
tmp = tmp + " " + delimiter;
System.err.println(e.getLocalizedMessage());
} catch (NotImplementedException e) {
// error
tmp = tmp + " " + delimiter;
System.err.println(e.getLocalizedMessage());
}
break;
default:
tmp = tmp + " " + delimiter;
}
}
tmp = tmp.substring(0, tmp.length() - 1);
}
bw.write(tmp + "\n");
}
bw.flush();
bw.close();
ow.close();
fout.close();
System.gc();
}
} catch (InvalidFormatException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
} catch (FileNotFoundException e) {
e.printStackTrace();
}
}
@Override
public String toString() {
return getClass().getSimpleName() + ": " + sheet.getSheetName();
}
// 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) {
}
}
}
}
}
