private Object getCellValue(Cell cell, FormulaEvaluator evaluator)
throws ExcelManipulateException {
if (cell == null) return null;
// log.debug("Read Value for: " + ExcelUtil.getCellIndex(cell.getRowIndex(),
// cell.getColumnIndex()));
Object value = null;
CellValue cellValue = evaluator.evaluate(cell);
if (cellValue == null) {
logger.debug("{}: null", ExcelUtil.getCellIndex(cell.getRowIndex(), cell.getColumnIndex()));
return null;
}
switch (cellValue.getCellType()) {
case Cell.CELL_TYPE_BLANK:
case Cell.CELL_TYPE_ERROR:
break;
case Cell.CELL_TYPE_BOOLEAN:
value = cellValue.getBooleanValue();
break;
case Cell.CELL_TYPE_NUMERIC:
if (DateUtil.isCellDateFormatted(cell)) {
value = DateUtil.getJavaDate(cellValue.getNumberValue());
} else value = cellValue.getNumberValue();
break;
case Cell.CELL_TYPE_STRING:
value = cellValue.getStringValue();
}
logger.debug(
"{}: {}", ExcelUtil.getCellIndex(cell.getRowIndex(), cell.getColumnIndex()), value);
return value;
}
@Override
public void close() {
try {
int nbS = wb.getNumberOfSheets();
for (int i = 0; i < nbS; i++) {
Sheet s = wb.getSheetAt(i);
if (s.getRow(0)
!= null) { // ceci arrive si on vide la mémoire tampon pour les grands fichiers. Dans ce
// cas pas de possibilité de traiter la mise en page de gros fichiers
for (int j = 0; j < s.getRow(0).getLastCellNum(); j++) {
Cell c = s.getRow(0).getCell(j);
c.getSheet().autoSizeColumn(c.getColumnIndex());
}
Cell firstCell = s.getRow(0).getCell(0);
Cell lastCell = s.getRow(0).getCell((int) s.getRow(0).getLastCellNum() - 1);
s.setAutoFilter(
new CellRangeAddress(
firstCell.getRowIndex(),
lastCell.getRowIndex(),
lastCell.getRowIndex(),
lastCell.getColumnIndex()));
}
}
wb.write(fileOut);
fileOut.flush();
fileOut.close();
} catch (FileNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
/**
* Extract a couple of data elements for easy reference, such as row and column
*
* @param sheetName
* @param cell
*/
public SpreadsheetItem(String sheetName, Cell cell) {
super();
this.sheetName = sheetName;
this.spreadsheetCell = cell;
column = spreadsheetCell.getColumnIndex();
row = spreadsheetCell.getRowIndex();
this.cellIdentifier = new CellIdentifier(this.sheetName, spreadsheetCell.getRowIndex(), column);
}
public List<String> getCellConstraints(Cell cell) {
for (DataValidation _validation : getValidations())
for (CellRangeAddress _region : _validation.getRegions().getCellRangeAddresses())
if (_region.isInRange(cell.getRowIndex(), cell.getColumnIndex())) {
if (_validation.getValidationConstraint().getExplicitListValues() != null)
return Arrays.asList(_validation.getValidationConstraint().getExplicitListValues());
else if (_validation.getValidationConstraint().getFormula1() != null) {
String formula = _validation.getValidationConstraint().getFormula1().split("\"")[1];
String[] _names = formula.split("!");
String _sheetName = _names[0];
String _arrName = _names[1];
int sheetIndex = evalWorkbook.getSheetIndex(_sheetName);
EvaluationName nm = evalWorkbook.getName(_arrName, sheetIndex);
if (nm == null || !nm.isRange()) {
throw new RuntimeException(
"Specified name '" + _arrName + "' is not a range as expected.");
}
OperationEvaluationContext ec =
new OperationEvaluationContext(
new WorkbookEvaluator(evalWorkbook, null, null),
evalWorkbook,
defaultSheet,
cell.getRowIndex(),
cell.getColumnIndex(),
null);
Ptg[] ptgs = nm.getNameDefinition();
if (ptgs.length == 1 && ptgs[0] instanceof Area3DPtg) {
ValueEval result = ec.getArea3DEval((Area3DPtg) ptgs[0]);
if (result instanceof AreaEvalBase) {
AreaEvalBase _area = (AreaEvalBase) result;
ArrayList<String> resultStrings = new ArrayList<>();
for (int i = _area.getFirstRow(); i <= _area.getLastRow(); i++) {
String value =
getStringValue(
new CellCoord(_area.getFirstSheetIndex(), _area.getFirstColumn(), i));
if (value != null && value.length() > 0) resultStrings.add(value);
}
return resultStrings;
}
return null;
}
}
}
return null;
}
private boolean isMerged(Cell cell) {
if (cell == null) return false;
for (CellRangeAddress range : mergedRegions) {
if (range.isInRange(cell.getRowIndex(), cell.getColumnIndex())) return true;
}
return false;
}
public static Object getCellValue(Cell cell) {
Object value = new Object();
// Prevent a NullPointerException
if (cell != null) {
if (cell.getHyperlink() != null) {
Workbook workbook = new XSSFWorkbook();
FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();
Hyperlink link = cell.getHyperlink();
String address = link.getAddress();
if (logger.isTraceEnabled()) {
logger.trace(
"Found a Hyperlink to "
+ cell.getHyperlink().getAddress()
+ " in cell "
+ cell.getRowIndex()
+ ","
+ cell.getColumnIndex());
}
cell = evaluator.evaluateInCell(cell);
}
// Depending on the cell type, the value is read using Apache POI methods
switch (cell.getCellType()) {
// String are easy to handle
case Cell.CELL_TYPE_STRING:
logger.trace("Found string " + cell.getStringCellValue());
value = cell.getStringCellValue();
break;
// Since date formatted cells are also of the numeric type, this needs to be processed
case Cell.CELL_TYPE_NUMERIC:
if (DateUtil.isCellDateFormatted(cell)) {
Date date = cell.getDateCellValue();
DateFormat df = SimpleDateFormat.getDateInstance();
logger.trace("Found date " + df.format(date));
value = date;
} else {
logger.trace("Found general number " + cell.getNumericCellValue());
value = cell.getNumericCellValue();
}
break;
}
} else {
logger.trace("Found cell with NULL value");
}
return value;
}
/** 通过cell得到A3 B6 */
public String getRowColmunNumByCell(Cell cell) {
if (cell == null) return "";
String res = "";
String alpha = "abcdefghijklmnopqrstuvwxyz";
int row = cell.getRowIndex();
int col = cell.getColumnIndex() + 1;
while (true) {
int a = col % 26;
if (a == 0) {
a = 26;
}
res = alpha.charAt(a - 1) + res;
col = (col - a) / 26;
if (col == 0) break;
}
res += row;
return res.toUpperCase();
}
/**
* {@inheritDoc}
*
* @see
* org.eclipse.emfforms.spi.spreadsheet.core.converter.EMFFormsSpreadsheetValueConverter#getCellValue(org.apache.poi.ss.usermodel.Cell,
* org.eclipse.emf.ecore.EStructuralFeature)
*/
@Override
public Object getCellValue(Cell cell, EStructuralFeature eStructuralFeature)
throws EMFFormsConverterException {
String string;
try {
string = cell.getStringCellValue();
} catch (final IllegalStateException e) {
throw new EMFFormsConverterException(
String.format(
"Cell value of column %1$s in row %2$s on sheet %3$s must be a string.", //$NON-NLS-1$
cell.getColumnIndex() + 1, cell.getRowIndex() + 1, cell.getSheet().getSheetName()),
e);
}
if (string == null || string.length() == 0) {
return Collections.emptyList();
}
final EAttribute eAttribute = EAttribute.class.cast(eStructuralFeature);
final EDataType eDataType = eAttribute.getEAttributeType();
if (isDecimalNumber(eDataType.getInstanceClass())) {
string =
string.replace(
DecimalFormatSymbols.getInstance(localeProvider.getLocale()).getDecimalSeparator(),
'.');
}
final List<Object> result = new ArrayList<Object>();
final EFactory eFactory = eDataType.getEPackage().getEFactoryInstance();
for (final String element : string.split(SEPARATOR)) {
try {
result.add(eFactory.createFromString(eDataType, element));
} // BEGIN SUPRESS CATCH EXCEPTION
catch (final RuntimeException ex) { // END SUPRESS CATCH EXCEPTION
throw new EMFFormsConverterException(
MessageFormat.format(
"The cell value {0} could not converted to a model value.", string)); // $NON-NLS-1$
}
}
return result;
}
@Override
public List<Object> getRowValues(int rowIndex) {
Row row = dataSheet.getRow(rowIndex);
ArrayList<Object> values = new ArrayList<>();
if (row == null) return values;
int nullCount = 0;
int colCount = row.getLastCellNum();
for (int col = 0; col < colCount; col++) {
Cell cell = row.getCell(col);
// treat rows with merged cells as "empty"
if (isMerged(cell)) {
LogMgr.logDebug(
"ExcelReader.getRowValues()",
dataSheet.getSheetName()
+ ": column:"
+ cell.getColumnIndex()
+ ", row:"
+ cell.getRowIndex()
+ " is merged. Ignoring row!");
return Collections.emptyList();
}
Object value = getCellValue(cell);
if (value == null) {
nullCount++;
}
values.add(value);
}
if (nullCount == values.size()) {
// return an empty list if all columns are null
values.clear();
}
return values;
}
/**
* Discription:[写一行]
*
* @param row 行
* @param keyValues 一个beanMap对象，包含着这一行中的所有数据
* @param properties 哪些值需要写入行
* @param dateFormat 日期格式，默认：yyyy-MM-dd
* @author:[代超]
* @throws Exception
* @update:[日期YYYY-MM-DD] [更改人姓名][变更描述]
*/
public void writeRow(Row row, Map keyValues, Map properties, String dateFormat, T t)
throws Exception {
// 表格内容样式
CellStyle contentStyle = setContentSheetSysle(row.getSheet().getWorkbook());
if (keyValues == null || keyValues.size() < 1 || row == null) {
return;
}
if (dateFormat == null || "".equals(dateFormat.trim())) {
dateFormat = "yyyy-MM-dd";
}
Iterator it = keyValues.entrySet().iterator();
for (int i = 0; it.hasNext(); i++) {
Map.Entry next = (Map.Entry) it.next();
String dataValue = ObjectUtils.toString(properties.get(next.getKey()), "");
if (dataValue == null || "".equals(dataValue.trim())) {
// 无需导出当前字段
i--;
continue;
}
Object value = keyValues.get(next.getKey());
// 值为空时仍然创建单元格并且赋予样式。
Cell cell = row.createCell(i);
cell.setCellStyle(contentStyle);
if (value == null) {
// 当值为空的时候，不必做其他操作了
continue;
}
if (dataValue.toLowerCase().indexOf("formula") > -1) {
// 公式
String formula =
ExcelFormula.parseFormula(value.toString())
.replaceAll("-1", String.valueOf(cell.getRowIndex() + 1));
cell.setCellFormula(formula);
} else {
Class c = PropertyUtils.getPropertyType(t, next.getKey().toString());
writeCell(cell, value, c.getSimpleName(), dateFormat);
}
}
}
// sheet Project
private void MapTableProject(Sheet sheet) {
System.out.println("Table : " + sheet.getSheetName());
String tableName = sheet.getSheetName();
if (tableName.equals("Du an")) {
Iterator<Row> rowIterator = sheet.iterator();
while (rowIterator.hasNext()) {
// Get the row object
Project project = new Project();
Row row = rowIterator.next(); //
// Every row has columns, get the column iterator and iterate over
// them
Iterator<Cell> cellIterator = row.cellIterator();
if (row.getRowNum() > 0) {
while (cellIterator.hasNext()) {
Cell cell = cellIterator.next();
if (cell.getRow().getRowNum() == 0) {
System.out.println("---- Column name:" + cell.getNumericCellValue());
} else {
if (cell.getColumnIndex() == 2) {
try {
if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
System.out.println("STT " + cell.getRowIndex());
System.out.println("---- Parent Project: " + cell.getNumericCellValue());
if (String.valueOf(cell.getNumericCellValue()) != null) {
project.setParentCode(String.valueOf(cell.getNumericCellValue()));
}
} else {
System.out.println("STT " + cell.getRowIndex());
System.out.println("---- Parent Project: " + cell.getStringCellValue());
if (cell.getStringCellValue() != null) {
project.setParentCode(cell.getStringCellValue());
}
}
} catch (Exception e) {
e.printStackTrace();
}
}
if (cell.getColumnIndex() == 1) {
System.out.println("---- Name Project: " + cell.getStringCellValue());
project.setName(cell.getStringCellValue());
if (project.getName().trim().length() == 0) {
continue;
}
}
if (cell.getColumnIndex() == 0) {
try {
if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
project.setCode(String.valueOf(cell.getNumericCellValue()));
} else {
project.setCode(cell.getStringCellValue());
}
} catch (Exception e) {
e.printStackTrace();
}
}
if (cell.getColumnIndex() == 3) {
System.out.println("---- Description: " + cell.getStringCellValue());
project.setDepartmentPart(cell.getStringCellValue());
}
if (cell.getColumnIndex() == 4) {
System.out.println("---- Description: " + cell.getStringCellValue());
project.setDescription(cell.getStringCellValue());
}
}
}
try {
if (checkProject(project)) {
project.setStatus("Đã hoàn thành");
RestaurantModelManager.getInstance().saveProject(project);
}
} catch (Exception e) {
e.printStackTrace();
}
}
}
}
}
// Setup column data here
private void setValue(Cell cell, ConfirmationRecord data) {
switch (cell.getColumnIndex()) {
case 0:
cell.setCellValue(cell.getRowIndex() - 4);
break;
case 1:
cell.setCellValue(DateUtil.convDateToString("yyyy", data.getApplicationDate()));
break;
case 2:
cell.setCellValue(DateUtil.convDateToString("M", data.getApplicationDate()));
break;
case 3:
cell.setCellValue(DateUtil.convDateToString("d", data.getApplicationDate()));
break;
case 4:
cell.setCellValue(DateUtil.convDateToString("yyyy", data.getSettleDate()));
break;
case 5:
cell.setCellValue(DateUtil.convDateToString("M", data.getSettleDate()));
break;
case 6:
cell.setCellValue(DateUtil.convDateToString("d", data.getSettleDate()));
break;
case 7:
cell.setCellValue(data.getPolicyNo());
break;
case 8:
cell.setCellValue(data.getPlanCode());
break;
case 9:
cell.setCellValue(data.getInsuredName());
break;
case 10:
cell.setCellValue(data.getModalPremium().doubleValue());
break;
case 11:
cell.setCellValue(data.getApe().doubleValue());
break;
case 12:
cell.setCellValue(data.getSumInsured().doubleValue());
break;
case 13:
cell.setCellValue(data.getMode());
break;
case 14:
cell.setCellValue(data.getStatus());
break;
case 15:
cell.setCellValue(data.getUnitCode());
break;
case 16:
cell.setCellValue(data.getBankCard());
break;
case 17:
cell.setCellValue(data.getCreditCardNo());
break;
case 18:
cell.setCellValue(data.getExpiredDate());
break;
case 19:
cell.setCellValue(
data.getBirthDate() != null
? DateUtil.convDateToString("yyyy", data.getBirthDate())
: null);
break;
case 20:
cell.setCellValue(
data.getBirthDate() != null
? DateUtil.convDateToString("M", data.getBirthDate())
: null);
break;
case 21:
cell.setCellValue(
data.getBirthDate() != null
? DateUtil.convDateToString("d", data.getBirthDate())
: null);
break;
case 22:
cell.setCellValue(data.getCitizenId());
break;
case 23:
cell.setCellValue(data.getAddress1());
break;
case 24:
cell.setCellValue(data.getAddress2());
break;
case 25:
cell.setCellValue(data.getAddress3());
break;
case 26:
cell.setCellValue(data.getProvince());
break;
case 27:
cell.setCellValue(data.getPostCode());
break;
case 28:
cell.setCellValue(data.getTel1());
break;
case 29:
cell.setCellValue(data.getTel2());
break;
case 30:
cell.setCellValue(data.getTel3());
break;
case 31:
cell.setCellValue(data.getMobile1());
break;
case 32:
cell.setCellValue(data.getMobile2());
break;
case 33:
cell.setCellValue(data.getTsrName());
break;
case 34:
cell.setCellValue(data.getAction() != null ? data.getAction().getParamValue() : null);
break;
case 35:
cell.setCellValue(data.getRemark());
break;
default:
break;
}
}
public String process2xml() {
String result = "";
// String sourcefilenametag = config.getString("conversion.tags.sourcefilename");
String sourcefilename = file.getAbsolutePath();
try {
sourcefilename = file.getCanonicalPath();
} catch (IOException ioe) {
log.warn("Could not get cannonical path for file!", ioe);
}
String templatedir = config.getString("conversion.template.path");
// result += "<" + sourcefilenametag + "><![CDATA[" + file.toString() + "]]></" +
// sourcefilenametag + ">";
try {
Workbook input = WorkbookFactory.create(file);
log.debug("Processing " + file.toString() + " as an Excel file.");
FormulaEvaluator evaluator = input.getCreationHelper().createFormulaEvaluator();
String templateName = "";
STGroup g = new STRawGroupDir(templatedir, '$', '$');
// Go through each sheet
for (int sheetno = 0; sheetno < input.getNumberOfSheets(); sheetno++) {
Sheet sheet = input.getSheetAt(sheetno);
log.debug("Processing sheet #" + sheetno + ": " + sheet.getSheetName());
LandmarkMatchList lml = new LandmarkMatchList(landmarks.size());
for (Row row : sheet) {
// Go through each cell
for (Cell cell : row) {
String cellvalue = lml.getCellValue(cell, evaluator);
if (!cellvalue.equals("")) {
log.trace(
"Cell value is: "
+ cellvalue
+ " [Row,Col]=["
+ cell.getRowIndex()
+ ","
+ cell.getColumnIndex()
+ "]");
log.trace("Matching landmarks: " + landmarks.getLandmarksFor(cellvalue));
// Does Cell contents match a landmark?
lml.addMatches(landmarks.getLandmarksFor(cellvalue), cell);
} else {
log.trace(
"Cell value is blank. [Row,Col]=["
+ cell.getRowIndex()
+ ","
+ cell.getColumnIndex()
+ "]");
}
}
}
templateName = lml.getTemplateName(landmarks);
if (!templateName.equals("")) {
ST st = g.getInstanceOf(templateName);
if (st != null) {
// Set landmark name to value of cell given direction and distance
Hashtable templateValues =
lml.getCellTemplateValues(
templateName, sheet, landmarks, evaluator, sourcefilename, sheetno);
Enumeration templateValuesKeys = templateValues.keys();
while (templateValuesKeys.hasMoreElements()) {
String key = (String) templateValuesKeys.nextElement();
st.add(key, (String) templateValues.get(key));
}
ArrayList<String> sectionNames =
lml.getSectionNamesForTemplate(templateName, landmarks);
for (String sectionName : sectionNames) {
ArrayList<Hashtable> sectionrows =
lml.getSectionRows(templateName, sheet, landmarks, evaluator, sectionName);
st.add(sectionName, sectionrows);
}
result += st.render();
} else {
log.error(
"Unable to load template "
+ templateName
+ ".st! Cannot render data to template while processing "
+ file.toString()
+ " sheet number "
+ sheetno);
}
}
}
} catch (IOException ioe) {
log.error("Unable to open " + file.toString() + " as an Excel file.", ioe);
} catch (InvalidFormatException ife) {
log.error("Unable to open " + file.toString() + ". Format not recognized as Excel. ", ife);
} catch (IllegalArgumentException iae) {
log.error("Unable to open " + file.toString() + " as an Excel file.", iae);
} catch (Exception e) {
log.error("Unable to open " + file.toString() + " as an Excel file.", e);
}
// If cannot render, make sure don't reprocess unnecessarily
if (result.equals("")) {
result = "<Notemplatedata></Notemplatedata>";
}
return result;
}
/** 将 excel中的sheet解析成对应的实体 */
@Override
public <T> Collection<T> getFinanceReprotEntitysByConfig(
Sheet sheet,
Map<String, FinanceReportBean> financebean,
FinanceReportParamBean financeParam,
BaseFile bf,
CustInfo ci)
throws Exception {
// TODO Auto-generated method stub
String targetClass = financeParam.getTargetClass();
Class<?> targetObject = Class.forName(targetClass);
String sheaderCell = financeParam.getHeaderCell();
String sdataCell = financeParam.getDataCell();
String vheaderCell[] = sheaderCell.split(",");
String vdataCell[] = sdataCell.split(",");
String headerCell = "";
String dataCell = "";
int headerCellRowIndex = 0;
int headerCellCellIndex = 0;
int dataCellRowIndex = 0;
int dataCellCellIndex = 0;
int sheetRowNum = sheet.getLastRowNum();
int datalength = 0;
String cellvalue = "";
String headerValue = "";
String field = "";
Boolean flag = true;
Boolean empty = false;
Map<String, JSONObject> FinanceEntiies = new HashMap<String, JSONObject>();
List finance = new ArrayList();
String year = "";
try {
for (int i = 0; i < vheaderCell.length; i++) {
datalength = 0;
headerCellRowIndex = 0;
headerCellCellIndex = 0;
dataCellRowIndex = 0;
dataCellCellIndex = 0;
headerCell = vheaderCell[i];
dataCell = vdataCell[i];
ExeclBean headerInfo = PoiExcelUtil.getExcelBean(headerCell);
ExeclBean dataInfo = PoiExcelUtil.getExcelBean(dataCell);
headerCellRowIndex = headerInfo.getRow_num() + 1;
headerCellCellIndex = headerInfo.getCol_num();
dataCellRowIndex = dataInfo.getRow_num();
dataCellCellIndex = dataInfo.getCol_num();
flag = true;
if (headerCellRowIndex > sheetRowNum || dataCellRowIndex > sheetRowNum) {
throw new BusinessException(
"解析excel出错:配置的标题cell("
+ headerCellRowIndex
+ ")或数据cell("
+ dataCellRowIndex
+ ")大于实际 sheet("
+ sheet.getSheetName()
+ ")的row长度");
} else {
Row datarow = sheet.getRow(dataCellRowIndex);
datalength = 0;
// 获得数据列的长度
for (int j = dataCellCellIndex; j < datarow.getLastCellNum(); j++) {
Cell datacell = datarow.getCell(j);
cellvalue = PoiExcelUtil.getCellValue(datacell);
if (flag == true) {
if (null != cellvalue && (!"".equals(cellvalue))) {
datalength = datalength + 1;
} else {
flag = false;
}
}
}
List<JSONObject> entity = new ArrayList<JSONObject>();
for (int j = 0; j < datalength; j++) {
cellvalue = "";
empty = true;
JSONObject financeEntityJson = new JSONObject();
datarow = sheet.getRow(dataCellRowIndex);
Cell cell = datarow.getCell(dataCellCellIndex + j);
year = PoiExcelUtil.getCellValue(datarow.getCell(dataCellCellIndex + j));
// 隐藏列则不写入数据库
if (!sheet.isColumnHidden(dataCellCellIndex + j)) {
if (!"".equals(year.trim())) {
for (int h = dataCellRowIndex; h < sheetRowNum; h++) {
datarow = sheet.getRow(h);
if (null != datarow) {
if (headerCellCellIndex < datarow.getLastCellNum()) {
if (null != datarow.getCell(headerCellCellIndex)) {
headerValue =
PoiExcelUtil.getCellValue(datarow.getCell(headerCellCellIndex));
}
if (dataCellCellIndex + j < datarow.getLastCellNum()) {
if (null != datarow.getCell(dataCellCellIndex + j)) {
cell = datarow.getCell(dataCellCellIndex + j);
cellvalue =
PoiExcelUtil.getCellValue(datarow.getCell(dataCellCellIndex + j));
if (null != cellvalue && (!"".equals(cellvalue))) {
field = "";
flag = true;
if (h == dataCellRowIndex) {
flag = false;
}
field =
this.getExcelTableField(
headerValue,
(cell.getRowIndex() + 1) + "_" + i,
financebean,
flag);
System.out.println(
headerValue
+ ":"
+ ((cell.getRowIndex() + 1) + "_" + i)
+ ":"
+ cellvalue);
if (!"".equals(field)) {
financeEntityJson.put(field, cellvalue);
empty = false;
}
}
}
}
}
}
}
if (empty == false) {
// 根据年进行合并
if (FinanceEntiies.containsKey(year)) {
JSONObject temp = FinanceEntiies.get(year);
Iterator it = financeEntityJson.keys();
while (it.hasNext()) {
String key = (String) it.next();
temp.put(key, financeEntityJson.get(key).toString());
}
FinanceEntiies.put(year, temp);
} else {
FinanceEntiies.put(year, financeEntityJson);
}
}
}
}
}
}
}
System.out.println(FinanceEntiies);
if (!FinanceEntiies.isEmpty()) {
Set<String> key = FinanceEntiies.keySet();
for (Iterator it = key.iterator(); it.hasNext(); ) {
year = (String) it.next();
JSONObject jsonObj = FinanceEntiies.get(year);
Object sourectObject = targetObject.newInstance();
this.tableService.copyAndOverrideExistedValueFromJSONObject(jsonObj, sourectObject, null);
try {
BeanUtils.getPropertyDescriptor(sourectObject.getClass(), "upLoadId")
.getWriteMethod()
.invoke(sourectObject, bf);
BeanUtils.getPropertyDescriptor(sourectObject.getClass(), "custId")
.getWriteMethod()
.invoke(sourectObject, ci);
} catch (Exception e) {
// TODO Auto-generated catch block
throw new BusinessException(
"在记录上传文件的ID和客户时出请检查实体类中有没有upLoadId和custId字段" + e.getMessage());
}
finance.add(sourectObject);
}
return finance;
}
} catch (BusinessException b) {
b.printStackTrace();
throw new BusinessException(b.getMessage());
} catch (Exception e) {
e.printStackTrace();
// TODO Auto-generated catch block
throw new BusinessException(e.getMessage());
}
return null;
}
/**
* Create a new CellAddress object.
*
* @param cell the Cell to get the location of
*/
public CellAddress(Cell cell) {
this(cell.getRowIndex(), cell.getColumnIndex());
}
// sheet Ca nhan
private void MapTableUser(Sheet sheet) throws IOException {
System.out.println("Table : " + sheet.getSheetName());
String tableName = sheet.getSheetName();
if (tableName.equals("Ca nhan")) {
Iterator<Row> rowIterator = sheet.iterator();
while (rowIterator.hasNext()) {
// Get the row object
account = new Account();
account.setLoginId(new Date().getTime() + "");
account.setPassword("0000");
account.setType(Type.USER);
account.setLastLoginTime(new Date());
basicInformation = new BasicInformation();
contact = new Contact();
customer = new Customer();
customer.setOrganizationLoginId(ManagerAuthenticate.getInstance().getOrganizationLoginId());
String str = new Date().getTime() + "";
customer.setCode(str);
Row row = rowIterator.next(); //
// Every row has columns, get the column iterator and iterate over
// them
Iterator<Cell> cellIterator = row.cellIterator();
if (row.getRowNum() > 0) {
while (cellIterator.hasNext()) {
Cell cell = cellIterator.next();
if (cell.getRow().getRowNum() == 0) {
System.out.println("---- Column name:" + cell.getStringCellValue());
} else {
if (cell.getColumnIndex() == 0) {
// firstName
try {
System.out.println("STT " + cell.getRowIndex());
System.out.println("---- firstName: " + cell.getStringCellValue());
if (cell.getStringCellValue() != null && !cell.getStringCellValue().equals("")) {
basicInformation.setFirstName(cell.getStringCellValue());
}
} catch (Exception e) {
e.printStackTrace();
}
}
if (cell.getColumnIndex() == 1) {
// lastName
try {
System.out.println("---- lastName: " + cell.getStringCellValue());
if (cell.getStringCellValue() != null && !cell.getStringCellValue().equals("")) {
basicInformation.setLastName(
basicInformation.getFirstName() + " " + cell.getStringCellValue());
customer.setName(basicInformation.getLastName());
}
} catch (Exception e) {
e.printStackTrace();
}
}
if (cell.getColumnIndex() == 2) {
// birthday
try {
if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
System.out.println("---- birthday " + cell.getNumericCellValue());
basicInformation.setBirthday(String.valueOf(cell.getNumericCellValue()));
} else {
System.out.println("---- birthday " + cell.getStringCellValue());
basicInformation.setBirthday(cell.getStringCellValue().toString());
}
} catch (Exception e) {
e.printStackTrace();
}
}
if (cell.getColumnIndex() == 3) {
// gender
try {
System.out.println("---- gender: " + cell.getStringCellValue());
if (cell.getStringCellValue() != null && !cell.getStringCellValue().equals("")) {
basicInformation.setGender(cell.getStringCellValue());
}
} catch (Exception e) {
e.printStackTrace();
}
}
if (cell.getColumnIndex() == 4) {
// indentityCard
try {
if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
System.out.println("---- indentityCard: " + cell.getNumericCellValue());
basicInformation.setPersonalId(String.valueOf(cell.getNumericCellValue()));
} else {
System.out.println("---- indentityCard: " + cell.getStringCellValue());
basicInformation.setPersonalId((cell.getStringCellValue()));
}
} catch (Exception e) {
e.printStackTrace();
}
}
// address
if (cell.getColumnIndex() == 5) {
System.out.println("---- address: " + cell.getStringCellValue());
contact.setAddressNumber(cell.getStringCellValue());
}
// telephone
if (cell.getColumnIndex() == 6) {
try {
if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
System.out.println("---- telephone1: " + cell.getNumericCellValue());
String[] phone = String.valueOf(cell.getNumericCellValue()).split(",");
contact.setPhone(phone);
} else {
System.out.println("---- telephone2: " + cell.getStringCellValue());
String[] phone = cell.getStringCellValue().split(",");
contact.setPhone(phone);
}
} catch (Exception e) {
e.printStackTrace();
}
}
if (cell.getColumnIndex() == 7) {
// mobile
try {
if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
System.out.println("---- mobile1: " + cell.getNumericCellValue());
String[] arraymobile = String.valueOf(cell.getNumericCellValue()).split(",");
contact.setMobile(arraymobile);
} else {
System.out.println("---- mobile2: " + cell.getStringCellValue());
String[] arraymobile = cell.getStringCellValue().split(",");
contact.setMobile(arraymobile);
}
} catch (Exception e) {
}
}
// Email
if (cell.getColumnIndex() == 8) {
try {
String string = new Date().getTime() + "";
int str1 = cell.getStringCellValue().length();
String str2 = cell.getStringCellValue();
System.out.println("---- Email: " + cell.getStringCellValue());
if (str1 > 4) {
account.setEmail(str2);
} else {
account.setEmail(string + "@gmail.com");
}
} catch (Exception e) {
e.printStackTrace();
}
}
// Description
if (cell.getColumnIndex() == 9) {
try {
if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
System.out.println("---- telephone1: " + cell.getNumericCellValue());
String phone = String.valueOf(cell.getNumericCellValue());
customer.setDescription(phone);
} else {
System.out.println("---- telephone2: " + cell.getStringCellValue());
customer.setDescription(cell.getStringCellValue());
}
} catch (Exception e) {
e.printStackTrace();
}
}
}
}
try {
Profile profileBasic = new Profile();
BasicInformation basic = this.basicInformation;
profileBasic.put(BasicInformation.LAST_NAME, basic.getLastName());
profileBasic.put(BasicInformation.GENDER, basic.getGender());
profileBasic.put(BasicInformation.BIRTHDAY, basic.getBirthday());
profileBasic.put(BasicInformation.PERSONAL_ID, basic.getPersonalId());
Profiles profiles = new Profiles();
profiles.setBasic(profileBasic);
account.setProfiles(profiles);
List<Contact> contacts = new ArrayList<Contact>();
contacts.add(contact);
account.setContacts(contacts);
Account acc = AccountModelManager.getInstance().saveAccount(account);
customer.setLoginId(acc.getLoginId());
customer.setType("Cá nhân");
if (contact.getMobile().length > 0) {
customer.setMobile(contact.getMobile()[0]);
}
try {
customer.setBirthDay(new SimpleDateFormat("dd/MM/yyyy").parse(basic.getBirthday()));
} catch (Exception e) {
}
CustomerModelManager.getInstance().saveCustomer(customer);
} catch (Exception e) {
}
}
}
}
}
// sheet DN
private void MapTableOrganization(Sheet sheet) throws IOException {
System.out.println("Table : " + sheet.getSheetName());
String tableName = sheet.getSheetName();
List<String> list = new ArrayList<String>();
if (tableName.equals("DN")) {
Iterator<Row> rowIterator = sheet.iterator();
while (rowIterator.hasNext()) {
// Get the row object
account = new Account();
account.setLoginId(new Date().getTime() + "");
account.setPassword("0000");
account.setType(Type.ORGANIZATION);
account.setLastLoginTime(new Date());
organizationBasic = new OrganizationBasic();
contact = new Contact();
customer = new Customer();
customer.setOrganizationLoginId(ManagerAuthenticate.getInstance().getOrganizationLoginId());
Row row = rowIterator.next(); //
// Every row has columns, get the column iterator and iterate over
// them
Iterator<Cell> cellIterator = row.cellIterator();
if (row.getRowNum() > 0) {
while (cellIterator.hasNext()) {
Cell cell = cellIterator.next();
if (cell.getRow().getRowNum() == 0) {
System.out.println("---- Column name:" + cell.getStringCellValue());
} else {
if (cell.getColumnIndex() == 0) {
// enterpriseName
try {
System.out.println("STT " + cell.getRowIndex());
System.out.println("---- Enterprise Name: " + cell.getStringCellValue());
if (cell.getStringCellValue() != null && !cell.getStringCellValue().equals("")) {
customer.setName(cell.getStringCellValue());
organizationBasic.setName(cell.getStringCellValue());
}
} catch (Exception e) {
e.printStackTrace();
}
}
if (cell.getColumnIndex() == 1) {
// enterpriseCode
try {
if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
System.out.println("---- Enterprise code: " + cell.getNumericCellValue());
if (list.indexOf(String.valueOf(cell.getNumericCellValue())) > 0) {
customer.setCode(String.valueOf(new Date().getTime()));
} else {
customer.setCode(String.valueOf(cell.getNumericCellValue()));
list.add(customer.getCode());
}
} else {
System.out.println("---- Enterprise code: " + cell.getStringCellValue());
if (list.indexOf(cell.getStringCellValue()) > 0) {
customer.setCode(String.valueOf(new Date().getTime()));
} else {
customer.setCode(cell.getStringCellValue());
list.add(customer.getCode());
}
}
} catch (Exception e) {
e.printStackTrace();
}
}
// Slogan
if (cell.getColumnIndex() == 2) {
try {
System.out.println("---- Slogan: " + cell.getStringCellValue());
organizationBasic.setSlogan(cell.getStringCellValue());
} catch (Exception e) {
e.printStackTrace();
}
}
// Address
if (cell.getColumnIndex() == 3) {
try {
if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
System.out.println("---- Address: " + cell.getNumericCellValue());
contact.setAddressNumber(String.valueOf(cell.getNumericCellValue()));
} else {
System.out.println("---- Address: " + cell.getStringCellValue());
contact.setAddressNumber(cell.getStringCellValue());
}
} catch (Exception e) {
e.printStackTrace();
}
}
// Telephone
if (cell.getColumnIndex() == 4) {
try {
if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
System.out.println("---- Telephone: " + cell.getNumericCellValue());
String[] arrayPhone = String.valueOf(cell.getNumericCellValue()).split(",");
contact.setPhone(arrayPhone);
if (arrayPhone.length > 0) {
customer.setMobile(arrayPhone[0]);
}
} else {
System.out.println("---- Telephone: " + cell.getStringCellValue());
String[] arrayPhone = cell.getStringCellValue().split(",");
contact.setPhone(arrayPhone);
if (arrayPhone.length > 0) {
customer.setMobile(arrayPhone[0]);
}
}
} catch (Exception e) {
e.printStackTrace();
}
}
// Email
if (cell.getColumnIndex() == 6) {
try {
String str = new Date().getTime() + "";
int str1 = cell.getStringCellValue().length();
String str2 = cell.getStringCellValue();
System.out.println("---- Email: " + cell.getStringCellValue());
if (str1 > 4) {
account.setEmail(str2);
} else {
account.setEmail(str + "@gmail.com");
}
} catch (Exception e) {
e.printStackTrace();
}
}
// Website
if (cell.getColumnIndex() == 7) {
try {
if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
customer.setDescription(String.valueOf(cell.getNumericCellValue()));
} else {
if (cell.getStringCellValue() != null) {
customer.setDescription(cell.getStringCellValue());
}
}
} catch (Exception e) {
e.printStackTrace();
}
}
// Description
if (cell.getColumnIndex() == 8) {
try {
if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
System.out.println("---- Description: " + cell.getNumericCellValue());
organizationBasic.setDescription(String.valueOf(cell.getNumericCellValue()));
} else {
System.out.println("---- Description: " + cell.getStringCellValue());
if (cell.getStringCellValue() != null) {
if (cell.getStringCellValue().length() > 100) {
organizationBasic.setDescription(
cell.getStringCellValue().substring(0, 100));
} else {
organizationBasic.setDescription(cell.getStringCellValue());
}
}
}
} catch (Exception e) {
e.printStackTrace();
}
}
}
}
try {
if (checkAccount(account)) {
Profile profileOrgBasic = new Profile();
OrganizationBasic orgBasic = this.organizationBasic;
profileOrgBasic.put(OrganizationBasic.NAME, orgBasic.getName());
profileOrgBasic.put(OrganizationBasic.SLOGAN, orgBasic.getSlogan());
profileOrgBasic.put(OrganizationBasic.DESCRIPTION, orgBasic.getDescription());
Profiles profiles = new Profiles();
profiles.setBasic(profileOrgBasic);
account.setProfiles(profiles);
List<Contact> contacts = new ArrayList<Contact>();
contacts.add(contact);
account.setContacts(contacts);
Account acc = AccountModelManager.getInstance().saveAccount(account);
customer.setLoginId(acc.getLoginId());
customer.setType("Doanh nghiệp");
customer.setAddress(contact.getAddressNumber());
CustomerModelManager.getInstance().saveCustomer(customer);
}
} catch (Exception e) {
}
}
}
}
}
/**
* Discription:[写一个单元格]
*
* @param cell 单元格
* @param value 写入的值
* @param valueType 写入的值的类型
* @param dateFormat 日期格式，默认yyyy-MM-dd
* @author:[代超]
* @update:[日期YYYY-MM-DD] [更改人姓名][变更描述]
*/
public void writeCell(Cell cell, Object value, String valueType, String dateFormat) {
if (cell == null || value == null) {
return;
}
if (dateFormat == null || "".equals(dateFormat.trim())) {
dateFormat = "yyyy-MM-dd";
}
String cellValue = "";
if ("String".equals(valueType)) {
cellValue = value.toString();
} else if ("int".equals(valueType)) {
cellValue = String.valueOf(value);
} else if ("float".equals(valueType)) {
cellValue = String.valueOf(value);
} else if ("double".equals(valueType)) {
cellValue = String.valueOf(value);
} else if ("Number".equals(valueType)) {
cellValue = String.valueOf(value);
} else if ("BigDecimal".equals(valueType)) {
cellValue = String.valueOf(value);
} else if ("byte[]".equals(valueType)) {
// 有图片时，设置行高为60px;
cell.getRow().setHeightInPoints(60);
// 设置图片所在列宽度为80px,注意这里单位的一个换算
cell.getSheet().setColumnWidth(cell.getColumnIndex(), (short) (35.7 * 80));
// sheet.autoSizeColumn(i);
byte[] bsValue = (byte[]) value;
HSSFClientAnchor anchor =
new HSSFClientAnchor(
0, 0, 1023, 255, (short) 6, cell.getRowIndex(), (short) 6, cell.getRowIndex());
anchor.setAnchorType(2);
// 声明一个画图的顶级管理器
cell.getSheet()
.createDrawingPatriarch()
.createPicture(
anchor,
cell.getSheet().getWorkbook().addPicture(bsValue, HSSFWorkbook.PICTURE_TYPE_JPEG));
return;
} else if ("Date".equals(valueType)) {
SimpleDateFormat sdf = new SimpleDateFormat(dateFormat);
cellValue = sdf.format(value);
} else if ("boolean".equals(valueType)) {
boolean bool = (Boolean) value;
if (bool) {
cellValue = "是";
} else {
cellValue = "否";
}
} else if ("Boolean".equals(valueType)) {
boolean bool = (Boolean) value;
if (bool) {
cellValue = "是";
} else {
cellValue = "否";
}
} else {
cellValue = String.valueOf(value);
}
cell.setCellValue(cellValue);
return;
}
