public void run() {
try {
FileInputStream fis = new FileInputStream(file);
XSSFWorkbook workbook = new XSSFWorkbook(fis);
XSSFSheet sheet = workbook.getSheetAt(0);
Iterator<Row> rowIterator = sheet.iterator();
Iterator<Row> rowIterator2 = sheet.iterator();
if (rowIterator.hasNext()) {
Row row = rowIterator.next();
Iterator<Cell> cellIterator = row.cellIterator();
String aux = "";
while (cellIterator.hasNext()) {
Cell cell = cellIterator.next();
switch (cell.getCellType()) {
case Cell.CELL_TYPE_STRING:
aux += cell.getStringCellValue().toUpperCase() + " ";
break;
}
}
System.out.println("Aux: " + aux);
if (aux.trim().equals(VALID_HEAD)) {
textArea.append("EXCEL VALIDO\n\n");
int size = 0;
rowIterator2.next();
while (rowIterator2.hasNext()) {
rowIterator2.next();
size++;
}
progressBar.setMaximum(size);
progressBar.setValue(0);
textArea.append("Clientes en fichero: " + size + "\n\n");
int num = 0;
while (rowIterator.hasNext()) {
num++;
textArea.append("--- CLIENT NUMBER " + num + " ---\n");
Row row2 = rowIterator.next();
processLine(row2.cellIterator());
progressBar.setValue(num);
Thread.sleep(1000);
}
} else {
textArea.append(
"El fichero excel seleccionado no es valido. "
+ "Selecciona un fichero excel con las columnas: \n"
+ VALID_HEAD);
}
}
fis.close();
} catch (Exception ex) {
ex.printStackTrace();
}
}
public void loadData(Data data)
throws EncryptedDocumentException, InvalidFormatException, IOException {
FileInputStream file = new FileInputStream(new File(path));
org.apache.poi.ss.usermodel.Workbook workbook = WorkbookFactory.create(file);
org.apache.poi.ss.usermodel.Sheet sheet = workbook.getSheetAt(0);
try {
Iterator<Row> rowIterator = sheet.iterator();
while (rowIterator.hasNext()) {
Row row = rowIterator.next();
Iterator<Cell> cellIterator = row.cellIterator();
ArrayList<String> arrayRow = new ArrayList<String>();
while (cellIterator.hasNext()) {
Cell cell = cellIterator.next();
testDataType(cell, arrayRow);
}
data.getData().add(arrayRow);
}
} catch (Exception e) {
e.printStackTrace();
}
workbook.close();
file.close();
sheet = null;
workbook = null;
file = null;
}
private static void retrieveSheetInformation(Sheet sheet) {
// every sheet has rows, iterate over them
Iterator<Row> rowIterator = sheet.iterator();
while (rowIterator.hasNext()) {
// Get the row object
Row row = rowIterator.next();
// Every row has columns, get the column iterator and
// iterate over them
Iterator<Cell> cellIterator = row.cellIterator();
StringBuffer sb = new StringBuffer();
while (cellIterator.hasNext()) {
// Get the Cell object
Cell cell = cellIterator.next();
// check the cell type and process accordingly
switch (cell.getCellType()) {
case Cell.CELL_TYPE_STRING:
sb.append(cell.getStringCellValue()).append(" $ ");
break;
case Cell.CELL_TYPE_NUMERIC:
sb.append(cell.getNumericCellValue()).append(" ");
}
} // end of cell iterator
System.out.println(sb.toString());
} // end of rows iterator
}
@Override
public List<Map<String, Object>> parse() throws ReadCensusException {
XSSFWorkbook listaVotantes = null;
String fileName = Paths.get(filePath).getFileName().toString();
try {
FileInputStream file = new FileInputStream(new File(filePath));
listaVotantes = new XSSFWorkbook(file);
XSSFSheet hoja = listaVotantes.getSheetAt(0);
Iterator<Row> rowIterator = hoja.iterator();
Row row;
while (rowIterator.hasNext()) {
row = rowIterator.next();
Iterator<Cell> columns = row.cellIterator();
if (!columns.hasNext()) continue;
try {
voter = new HashMap<String, Object>();
voter.put("name", columns.next().getStringCellValue());
voter.put("email", columns.next().getStringCellValue());
voter.put("nif", columns.next().getStringCellValue());
voter.put("code", String.valueOf((int) columns.next().getNumericCellValue()));
voter.put("password", PasswordGenerator.generate(8));
voter.put("file", fileName);
voter.put("line", row.getRowNum());
} catch (Exception e) {
throw new ReadCensusException(
"[ERROR] ["
+ fileName
+ ":"
+ row.getRowNum()
+ "] El usuario no tiene el formato correcto");
}
voters.add(voter);
}
} catch (FileNotFoundException e) {
throw new ReadCensusException("[ERROR] [" + fileName + "] El fichero no existe");
} catch (Exception e) {
throw new ReadCensusException(
"[ERROR] [" + fileName + "] Fallo inesperado al leer el fichero: " + e.getMessage());
} finally {
try {
if (listaVotantes != null) listaVotantes.close();
} catch (Exception e) {
throw new ReadCensusException("[ERROR] [" + fileName + "] I/O Error: " + e.getMessage());
}
}
if (voters.isEmpty()) {
throw new ReadCensusException("[AVISO] [" + fileName + "] El censo está vacío");
}
return voters;
}
public static void main(String args[]) throws Exception {
File file = new File("d:/delete/createworkbook.xlsx");
FileInputStream fis = new FileInputStream(file);
if (file.isFile() && file.exists()) {
// Get the workbook instance for XLSX file
XSSFWorkbook workbook = new XSSFWorkbook(fis);
System.out.println("openworkbook.xlsx file open successfully.");
// get worksheet, 0 based
XSSFSheet sheet = workbook.getSheetAt(0);
// get rows
Iterator<Row> it = sheet.iterator();
while (it.hasNext()) {
Row row = (XSSFRow) it.next();
// get cells
Iterator<Cell> cellIt = row.cellIterator();
while (cellIt.hasNext()) {
Cell cell = cellIt.next();
switch (cell.getCellType()) {
case Cell.CELL_TYPE_NUMERIC:
System.out.print(cell.getNumericCellValue() + " \t\t ");
break;
case Cell.CELL_TYPE_STRING:
System.out.print(cell.getStringCellValue() + " \t\t ");
break;
}
}
}
} else {
System.out.println("Error to open openworkbook.xlsx file.");
}
}
public List<Specialite> readBooksFromExcelFile(String excelFilePath) throws IOException {
List<Specialite> listSpecialite = new ArrayList<>();
FileInputStream file = new FileInputStream(new File(excelFilePath));
// Create Workbook instance holding reference to .xlsx file
XSSFWorkbook workbook = new XSSFWorkbook(file);
// Get first/desired sheet from the workbook
XSSFSheet sheet = workbook.getSheetAt(0);
Sheet firstSheet = workbook.getSheetAt(0);
Iterator<Row> iterator = firstSheet.iterator();
while (iterator.hasNext()) {
Row nextRow = iterator.next();
Iterator<Cell> cellIterator = nextRow.cellIterator();
Specialite sp = new Specialite();
Admin admin = new Admin();
while (cellIterator.hasNext()) {
Cell nextCell = cellIterator.next();
int columnIndex = nextCell.getColumnIndex();
switch (columnIndex) {
case 0:
sp.setIntitule((String) getCellValue(nextCell));
break;
}
}
listSpecialite.add(sp);
}
file.close();
return listSpecialite;
}
/**
* 验证是否是空行
*
* @param cells
* @return
*/
public static boolean checkIsNullCell(Row row) {
String result = "";
Iterator<Cell> cellIt = row.cellIterator();
for (; cellIt.hasNext(); ) {
Cell cell = cellIt.next();
result = result + cell.toString();
}
return "".equals(result.trim());
}
private int findIndexCellByName(String name, Row row) {
Iterator<Cell> iterator = row.cellIterator();
while (iterator.hasNext()) {
Cell cell = iterator.next();
if (getCellValue(cell).trim().equalsIgnoreCase(name)) {
return cell.getColumnIndex();
}
}
return -1;
}
public boolean isEmpty(Row row) {
boolean isEmpty = true;
Iterator<Cell> cellIterator = row.cellIterator();
while (cellIterator.hasNext()) {
Cell cell = cellIterator.next();
if (!isCellEmpty(cell)) {
isEmpty = false;
break;
}
}
return isEmpty;
}
public String parseExcelData(InputStream is) {
try {
System.out.println("--> open workbook");
workbook = new XSSFWorkbook(is);
System.out.println("start get sheet");
// Taking first sheet from the workbook
// XSSFSheet sheet = workbook.getSheetAt(0);
Sheet sheet = workbook.getSheetAt(0);
// Iterate through each rows from first sheet
Iterator<Row> rowIterator = sheet.iterator();
currentString = new StringBuilder();
while (rowIterator.hasNext()) {
Row row = rowIterator.next();
// For each row, iterate through each columns
Iterator<Cell> cellIterator = row.cellIterator();
while (cellIterator.hasNext()) {
Cell cell = cellIterator.next();
switch (cell.getCellType()) {
case Cell.CELL_TYPE_BOOLEAN:
bytesRead++;
currentString.append(cell.getBooleanCellValue() + "\t");
System.out.print(cell.getBooleanCellValue());
break;
case Cell.CELL_TYPE_NUMERIC:
bytesRead++;
currentString.append(cell.getNumericCellValue() + "\t");
System.out.print(cell.getNumericCellValue());
break;
case Cell.CELL_TYPE_STRING:
bytesRead++;
currentString.append(cell.getStringCellValue() + "\t");
System.out.print(cell.getStringCellValue());
break;
}
}
currentString.append("\n");
}
is.close();
} catch (IOException e) {
LOG.error("IO Exception : File not found " + e);
}
return currentString.toString();
}
public void processDataRows() {
try {
while (this.rowIterator.hasNext()) {
Row row = (Row) this.rowIterator.next();
Iterator<Cell> cellIterator = row.cellIterator();
this.processDataCells(cellIterator);
this.currentRow++;
}
this.getCMSBlocks();
} catch (Exception exception) {
exception.printStackTrace();
}
}
private boolean searchInExcel(String file) throws Exception {
Row row;
Cell cell;
String text;
boolean found = false;
InputStream in = null;
try {
in = new FileInputStream(file);
HSSFWorkbook wb = new HSSFWorkbook(in);
int sheets = wb.getNumberOfSheets();
OUTERMOST:
for (int i = 0; i < sheets; i++) {
HSSFSheet sheet = wb.getSheetAt(i);
Iterator<Row> rowIterator = sheet.rowIterator();
while (rowIterator.hasNext()) {
row = (Row) rowIterator.next();
Iterator<Cell> cellIterator = row.cellIterator();
while (cellIterator.hasNext()) {
cell = cellIterator.next();
int type = cell.getCellType();
if (type == HSSFCell.CELL_TYPE_STRING) {
text = cell.getStringCellValue();
found = searchText(text);
break OUTERMOST;
}
}
}
}
return found;
} finally {
if (in != null)
try {
in.close();
} catch (Exception e) {
}
}
}
public static void main(String args[]) {
try {
FileInputStream file =
new FileInputStream(new File("c:\\dev\\temp\\howtodoinjava_demo.xlsx"));
// Create Workbook instance holding reference to .xlsx file
XSSFWorkbook workbook = new XSSFWorkbook(file);
// Get first/desired sheet from the workbook
XSSFSheet sheet = workbook.getSheetAt(0);
// Iterate through each rows one by one
Iterator<Row> rowIterator = sheet.iterator();
while (rowIterator.hasNext()) {
Row row = rowIterator.next();
// For each row, iterate through all the columns
Iterator<Cell> cellIterator = row.cellIterator();
while (cellIterator.hasNext()) {
Cell cell = cellIterator.next();
// Check the cell type and format accordingly
switch (cell.getCellType()) {
case Cell.CELL_TYPE_NUMERIC:
System.out.print(cell.getNumericCellValue() + "\t");
break;
case Cell.CELL_TYPE_STRING:
System.out.print(cell.getStringCellValue() + "\t");
break;
}
}
System.out.println("");
}
file.close();
} catch (Exception e) {
e.printStackTrace();
}
}
// 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 static void main(String[] args) {
File file = null;
FileInputStream fileInputStream = null;
XSSFWorkbook workbook = null;
XSSFSheet sheet = null;
attributes = new ArrayList<>();
values = new ArrayList<>();
json = new ArrayList<>();
try {
file =
new File(
"/home/tony/Desktop/Spread_Sheet_Simple/2015 Winter_Final Marks_L1_Grammar_Ildiko Horvath.xlsx");
fileInputStream = new FileInputStream(file);
workbook = new XSSFWorkbook(fileInputStream);
sheet = workbook.getSheetAt(0);
Iterator<Row> rowIterator = sheet.rowIterator();
while (rowIterator.hasNext()) {
Row row = rowIterator.next();
Iterator<Cell> cellIterator = row.cellIterator();
while (cellIterator.hasNext()) {
Cell cell = cellIterator.next();
if (rowCount < 1) {
switch (cell.getCellType()) {
case Cell.CELL_TYPE_BLANK:
attributes.add("");
break;
case Cell.CELL_TYPE_NUMERIC:
attributes.add(String.valueOf((int) cell.getNumericCellValue()));
break;
case Cell.CELL_TYPE_STRING:
attributes.add(cell.getStringCellValue());
break;
}
} else {
switch (cell.getCellType()) {
case Cell.CELL_TYPE_BLANK:
values.add("");
break;
case Cell.CELL_TYPE_NUMERIC:
values.add(String.valueOf((int) cell.getNumericCellValue()));
break;
case Cell.CELL_TYPE_STRING:
values.add(cell.getStringCellValue());
break;
}
}
} // finished all cells in one row
System.out.println(values);
if (rowCount >= 1 && values.size() == attributes.size()) {
item = new JSONHashMap<String, String>();
for (int i = 0; i < attributes.size(); i++) {
item.put(attributes.get(i), values.get(i));
}
json.add(item);
}
values.clear();
rowCount++;
} // finish all rows in one sheet
System.out.println(attributes);
System.out.println(values);
System.out.println(json);
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
fileInputStream.close();
} catch (IOException e) {
e.printStackTrace();
}
} // finished try
String stringJson = json.toString();
ObjectMapper mapper = new ObjectMapper();
try {
System.out.println(json.get(0));
// List<ScoresDetail> scores = (List<ScoresDetail>) mapper.readValue(stringJson,
// ScoresDetail.class);
String socreJson = json.get(0).toString();
String scoreJsons = json.toString();
ScoresDetail socre = mapper.readValue(socreJson, ScoresDetail.class);
socre.setCourseCode("2016FSpe");
System.out.println(socre);
List<ScoresDetail> scoresDetails =
mapper.readValue(scoreJsons, new TypeReference<List<ScoresDetail>>() {});
System.out.println(scoresDetails);
// Iterator iterator = scores.iterator();
// while(iterator.hasNext()){
//
// iterator.next();
//
// }
} catch (JsonParseException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (JsonMappingException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
/**
* 导入 excel
*
* @param inputstream : 文件输入流
* @param pojoClass : 对应的导入对象 (每行记录)
* @return
*/
public static Collection importExcelByIs(InputStream inputstream, Class pojoClass) {
Collection dist = new ArrayList<Object>();
try {
// 得到目标目标类的所有的字段列表
Field filed[] = pojoClass.getDeclaredFields();
// 将所有标有Annotation的字段，也就是允许导入数据的字段,放入到一个map中
Map<String, Method> fieldSetMap = new HashMap<String, Method>();
Map<String, Method> fieldSetConvertMap = new HashMap<String, Method>();
// 循环读取所有字段
for (int i = 0; i < filed.length; i++) {
Field f = filed[i];
// 得到单个字段上的Annotation
Excel excel = f.getAnnotation(Excel.class);
// 如果标识了Annotationd的话
if (excel != null) {
// 构造设置了Annotation的字段的Setter方法
String fieldname = f.getName();
String setMethodName =
"set" + fieldname.substring(0, 1).toUpperCase() + fieldname.substring(1);
// 构造调用的method，
Method setMethod = pojoClass.getMethod(setMethodName, new Class[] {f.getType()});
// 将这个method以Annotaion的名字为key来存入。
// 对于重名将导致 覆盖 失败，对于此处的限制需要
fieldSetMap.put(excel.exportName(), setMethod);
if (excel.importConvertSign() == 1) {
// ----------------------------------------------------------------
// update-begin--Author:Quainty Date:20130524 for：[8]excel导出时间问题
// 用get/setXxxxConvert方法名的话， 由于直接使用了数据库绑定的Entity对象，注入会有冲突
StringBuffer setConvertMethodName = new StringBuffer("convertSet");
setConvertMethodName.append(fieldname.substring(0, 1).toUpperCase());
setConvertMethodName.append(fieldname.substring(1));
// update-end--Author:Quainty Date:20130524 for：[8]excel导出时间问题
// ----------------------------------------------------------------
Method getConvertMethod =
pojoClass.getMethod(setConvertMethodName.toString(), new Class[] {String.class});
fieldSetConvertMap.put(excel.exportName(), getConvertMethod);
}
}
}
// 将传入的File构造为FileInputStream;
// // 得到工作表
HSSFWorkbook book = new HSSFWorkbook(inputstream);
// // 得到第一页
HSSFSheet sheet = book.getSheetAt(0);
// // 得到第一面的所有行
Iterator<Row> row = sheet.rowIterator();
// 得到第一行，也就是标题行
Row title = row.next();
// 得到第一行的所有列
Iterator<Cell> cellTitle = title.cellIterator();
// 将标题的文字内容放入到一个map中。
Map titlemap = new HashMap();
// 从标题第一列开始
int i = 0;
// 循环标题所有的列
while (cellTitle.hasNext()) {
Cell cell = cellTitle.next();
String value = cell.getStringCellValue();
titlemap.put(i, value);
i = i + 1;
}
// 用来格式化日期的DateFormat
// SimpleDateFormat sf;
while (row.hasNext()) {
// 标题下的第一行
Row rown = row.next();
// 行的所有列
Iterator<Cell> cellbody = rown.cellIterator();
// 得到传入类的实例
Object tObject = pojoClass.newInstance();
int k = 0;
// 遍历一行的列
while (cellbody.hasNext()) {
Cell cell = cellbody.next();
// 这里得到此列的对应的标题
String titleString = (String) titlemap.get(k);
// 如果这一列的标题和类中的某一列的Annotation相同，那么则调用此类的的set方法，进行设值
if (fieldSetMap.containsKey(titleString)) {
Method setMethod = (Method) fieldSetMap.get(titleString);
// 得到setter方法的参数
Type[] ts = setMethod.getGenericParameterTypes();
// 只要一个参数
String xclass = ts[0].toString();
// 判断参数类型
if (Cell.CELL_TYPE_STRING == cell.getCellType()
&& fieldSetConvertMap.containsKey(titleString)) {
// 目前只支持从String转换
fieldSetConvertMap.get(titleString).invoke(tObject, cell.getStringCellValue());
} else {
if (xclass.equals("class java.lang.String")) {
// 先设置Cell的类型，然后就可以把纯数字作为String类型读进来了：
cell.setCellType(Cell.CELL_TYPE_STRING);
setMethod.invoke(tObject, cell.getStringCellValue());
} else if (xclass.equals("class java.util.Date")) {
// update-start--Author:Quainty Date:20130523 for：日期类型数据导入不对(顺便扩大支持了Excel的数据类型)
Date cellDate = null;
if (Cell.CELL_TYPE_NUMERIC == cell.getCellType()) {
// 日期格式
cellDate = cell.getDateCellValue();
} else { // 全认为是 Cell.CELL_TYPE_STRING: 如果不是 yyyy-mm-dd hh:mm:ss 的格式就不对(wait to
// do:有局限性)
cellDate = stringToDate(cell.getStringCellValue());
}
setMethod.invoke(tObject, cellDate);
//// update-start--Author:lihuan Date:20130423 for：导入bug修复直接将导出的Excel导入出现的bug的修复
////
// --------------------------------------------------------------------------------------------
// String cellValue = cell.getStringCellValue();
// Date theDate = stringToDate(cellValue);
// setMethod.invoke(tObject, theDate);
//// update-end--Author:lihuan Date:20130423 for：导入bug修复直接将导出的Excel导入出现的bug的修复
////
// --------------------------------------------------------------------------------------------
} else if (xclass.equals("class java.lang.Boolean")) {
boolean valBool;
if (Cell.CELL_TYPE_BOOLEAN == cell.getCellType()) {
valBool = cell.getBooleanCellValue();
} else { // 全认为是 Cell.CELL_TYPE_STRING
valBool =
cell.getStringCellValue().equalsIgnoreCase("true")
|| (!cell.getStringCellValue().equals("0"));
}
setMethod.invoke(tObject, valBool);
} else if (xclass.equals("class java.lang.Integer")) {
Integer valInt;
if (Cell.CELL_TYPE_NUMERIC == cell.getCellType()) {
valInt = (new Double(cell.getNumericCellValue())).intValue();
} else { // 全认为是 Cell.CELL_TYPE_STRING
valInt = new Integer(cell.getStringCellValue());
}
setMethod.invoke(tObject, valInt);
} else if (xclass.equals("class java.lang.Long")) {
Long valLong;
if (Cell.CELL_TYPE_NUMERIC == cell.getCellType()) {
valLong = (new Double(cell.getNumericCellValue())).longValue();
} else { // 全认为是 Cell.CELL_TYPE_STRING
valLong = new Long(cell.getStringCellValue());
}
setMethod.invoke(tObject, valLong);
} else if (xclass.equals("class java.math.BigDecimal")) {
BigDecimal valDecimal;
if (Cell.CELL_TYPE_NUMERIC == cell.getCellType()) {
valDecimal = new BigDecimal(cell.getNumericCellValue());
} else { // 全认为是 Cell.CELL_TYPE_STRING
valDecimal = new BigDecimal(cell.getStringCellValue());
}
setMethod.invoke(tObject, valDecimal);
//// ----------------------------------------------------------------
//// update-begin--Author:sky Date:20130422
// for：取值类型调整cell.getNumberCellValue-->>getStringCellValue
// setMethod.invoke(tObject, new BigDecimal(cell.getStringCellValue()));
//// update-end--Author:sky Date:20130422 for：取值类型调整
//// ----------------------------------------------------------------
// update-end--Author:Quainty Date:20130523 for：日期类型数据导入不对(顺便扩大支持了Excel的数据类型)
}
}
}
// 下一列
k = k + 1;
}
dist.add(tObject);
}
} catch (Exception e) {
e.printStackTrace();
return null;
}
return dist;
}
private static void load() {
List studentList = new ArrayList();
String FILE_PATH =
"C:/Users/g656736/Desktop/Workspaces/Training/Mukundh/Sample/Resources/Input.xlsx";
FileInputStream fis = null;
String tempKey = "";
String tempValue = "";
int cellCount = 0;
try {
fis = new FileInputStream(FILE_PATH);
// Using XSSF for xlsx format, for xls use HSSF
Workbook workbook = new XSSFWorkbook(fis);
int numberOfSheets = workbook.getNumberOfSheets();
// looping over each workbook sheet
for (int i = 0; i < numberOfSheets; i++) {
Sheet sheet = workbook.getSheetAt(i);
Iterator rowIterator = sheet.iterator();
// iterating over each row
while (rowIterator.hasNext()) {
Row row = (Row) rowIterator.next();
Iterator cellIterator = row.cellIterator();
// Iterating over each cell (column wise) in a particular row.
while (cellIterator.hasNext()) {
Cell cell = (Cell) cellIterator.next();
// The Cell Containing String will is name.
if (Cell.CELL_TYPE_NUMERIC == cell.getCellType()) {
if (cellCount == 0)
tempKey = new Integer(new Double(cell.getNumericCellValue()).intValue()).toString();
else tempValue = cell.getStringCellValue();
cellCount++;
} else if (Cell.CELL_TYPE_STRING == cell.getCellType()) {
if (cellCount == 0) tempKey = cell.getStringCellValue();
else tempValue = cell.getStringCellValue();
cellCount++;
// The Cell Containing numeric value will contain marks
}
}
cellCount = 0;
dataMap.put(tempKey, tempValue);
}
// end iterating a row, add all the elements of a row in list
}
fis.close();
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
}
private void readBOM(File fileItem) throws Exception {
FileInputStream file = new FileInputStream(fileItem);
XSSFWorkbook workbook = new XSSFWorkbook(file);
XSSFSheet sheet = workbook.getSheetAt(0);
Iterator<Row> rowIterator = sheet.iterator();
Connection con = Connect.getconnection();
PreparedStatement ps1 = con.prepareStatement("TRUNCATE TABLE bom");
ps1.executeUpdate();
Row nextRow = rowIterator.next();
while (rowIterator.hasNext()) {
nextRow = rowIterator.next();
Iterator<Cell> cellIterator = nextRow.cellIterator();
PreparedStatement ps =
con.prepareStatement(
"INSERT INTO `star`.`bom` (`FGNo`, `Parent`, `Child`, `Qty`, `Decs`, `Unit`, `Mat_grp`,`Mat_grp_desc`,`Mat_sub_grp`,`Mat_sub_grp_desc`,`Stock`) VALUES(?,?,?,?,?,?,?,?,?,?,?)");
while (cellIterator.hasNext()) {
Cell cell = cellIterator.next();
switch (cell.getCellType()) {
case Cell.CELL_TYPE_STRING:
if (cell.getColumnIndex() == 0) {
// FG
ps.setString(1, cell.getStringCellValue());
// System.out.println(cell.getStringCellValue());
break;
} else if (cell.getColumnIndex() == 1) {
// Parent
ps.setString(2, cell.getStringCellValue());
// System.out.println(cell.getStringCellValue());
break;
} else if (cell.getColumnIndex() == 2) {
// Child
ps.setString(3, cell.getStringCellValue());
// System.out.println(cell.getStringCellValue());
break;
} else if (cell.getColumnIndex() == 4) {
// Decsription
ps.setString(5, cell.getStringCellValue());
// System.out.println(cell.getNumericCellValue());
break;
} else if (cell.getColumnIndex() == 5) {
// Unit
ps.setString(6, cell.getStringCellValue());
// System.out.println(cell.getNumericCellValue());
break;
} else if (cell.getColumnIndex() == 6) {
// Mat_grp
ps.setString(7, cell.getStringCellValue());
// System.out.println(cell.getNumericCellValue());
break;
} else if (cell.getColumnIndex() == 7) {
// Mat_grp_desc
ps.setString(8, cell.getStringCellValue());
// System.out.println(cell.getNumericCellValue());
break;
} else if (cell.getColumnIndex() == 8) {
// Mat_sub_grp
ps.setString(9, cell.getStringCellValue());
// System.out.println(cell.getNumericCellValue());
break;
} else if (cell.getColumnIndex() == 9) {
// Mat_sub_grp_desc
ps.setString(10, cell.getStringCellValue());
// System.out.println(cell.getNumericCellValue());
break;
}
case Cell.CELL_TYPE_NUMERIC:
if (cell.getColumnIndex() == 3) {
// Quantity
ps.setDouble(4, cell.getNumericCellValue());
// System.out.println(cell.getNumericCellValue());
break;
} else if (cell.getColumnIndex() == 10) {
// Stock
ps.setInt(11, (int) cell.getNumericCellValue());
// System.out.println(cell.getNumericCellValue());
break;
}
case Cell.CELL_TYPE_BLANK:
if (cell.getColumnIndex() == 0) {
// FG
ps.setString(1, " ");
// System.out.println(cell.getStringCellValue());
break;
} else if (cell.getColumnIndex() == 1) {
// Parent
ps.setString(2, " ");
// System.out.println(cell.getStringCellValue());
break;
} else if (cell.getColumnIndex() == 2) {
// Child
ps.setString(3, " ");
// System.out.println(cell.getStringCellValue());
break;
} else if (cell.getColumnIndex() == 3) {
// Quantity
ps.setDouble(4, 0);
// System.out.println(cell.getNumericCellValue());
break;
} else if (cell.getColumnIndex() == 4) {
// Decsription
ps.setString(5, " ");
// System.out.println(cell.getNumericCellValue());
break;
} else if (cell.getColumnIndex() == 5) {
// Unit
ps.setString(6, " ");
// System.out.println(cell.getNumericCellValue());
break;
} else if (cell.getColumnIndex() == 6) {
// Mat_grp
ps.setString(7, " ");
// System.out.println(cell.getNumericCellValue());
break;
} else if (cell.getColumnIndex() == 7) {
// Mat_grp_desc
ps.setString(8, " ");
// System.out.println(cell.getNumericCellValue());
break;
} else if (cell.getColumnIndex() == 8) {
// Mat_sub_grp
ps.setString(9, " ");
// System.out.println(cell.getNumericCellValue());
break;
} else if (cell.getColumnIndex() == 9) {
// Mat_sub_grp_desc
ps.setString(10, " ");
// System.out.println(cell.getNumericCellValue());
break;
} else if (cell.getColumnIndex() == 10) {
// Stock
ps.setInt(11, 0);
// System.out.println(cell.getNumericCellValue());
break;
}
case Cell.CELL_TYPE_ERROR:
if (cell.getColumnIndex() == 0) {
// FG
ps.setString(1, " ");
// System.out.println(cell.getStringCellValue());
break;
} else if (cell.getColumnIndex() == 1) {
// Parent
ps.setString(2, " ");
// System.out.println(cell.getStringCellValue());
break;
} else if (cell.getColumnIndex() == 2) {
// Child
ps.setString(3, " ");
// System.out.println(cell.getStringCellValue());
break;
} else if (cell.getColumnIndex() == 3) {
// Quantity
ps.setDouble(4, 0);
// System.out.println(cell.getNumericCellValue());
break;
} else if (cell.getColumnIndex() == 4) {
// Decsription
ps.setString(5, " ");
// System.out.println(cell.getNumericCellValue());
break;
} else if (cell.getColumnIndex() == 5) {
// Unit
ps.setString(6, " ");
// System.out.println(cell.getNumericCellValue());
break;
} else if (cell.getColumnIndex() == 6) {
// Mat_grp
ps.setString(7, " ");
// System.out.println(cell.getNumericCellValue());
break;
} else if (cell.getColumnIndex() == 7) {
// Mat_grp_desc
ps.setString(8, " ");
// System.out.println(cell.getNumericCellValue());
break;
} else if (cell.getColumnIndex() == 8) {
// Mat_sub_grp
ps.setString(9, " ");
// System.out.println(cell.getNumericCellValue());
break;
} else if (cell.getColumnIndex() == 9) {
// Mat_sub_grp_desc
ps.setString(10, " ");
// System.out.println(cell.getNumericCellValue());
break;
} else if (cell.getColumnIndex() == 10) {
// Stock
ps.setInt(11, 0);
// System.out.println(cell.getNumericCellValue());
break;
}
}
// out.print("  ");
}
ps.executeUpdate();
// out.println("<br>");
}
workbook.close();
file.close();
con.close();
}
// 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();
}
}
}
}
}
// 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 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) {
}
}
}
}
}
private ArrayList<WorkInfoDto> readExcelFromInputstream(InputStream inputstream) {
// // HSSFWorkbook wb = new HSSFWorkbook(new
// // FileInputStream("e:\\workbook.xls"));
// // HSSFSheet sheet = wb.getSheetAt(0);
// //
// // for (Iterator<Row> iter = (Iterator<Row>) sheet.rowIterator();
// // iter.hasNext();) {
// // Row row = iter.next();
// // for (Iterator<Cell> iter2 = (Iterator<Cell>) row.cellIterator();
// // iter2.hasNext();) {
// // Cell cell = iter2.next();
// // String content = cell.getStringCellValue();// 除非是sring类型，否则这样迭代读取会有错误
// // System.out.println(content);
// HSSFWorkbook rwb = null;
// try {
//
// rwb = new HSSFWorkbook(inputstream);
// HSSFSheet st = rwb.getSheetAt(0);
// if (st != null) {
// ArrayList<WorkInfoDto> list = new ArrayList<WorkInfoDto>();
// ArrayList<String> lables = new ArrayList<String>();
// boolean first = true;
// for (Iterator<Row> iter = st.rowIterator(); iter.hasNext();) {
// Row row = iter.next();
// if (first) {
// for (Iterator<Cell> iter2 = row.cellIterator(); iter2
// .hasNext();) {
// Cell cell = iter2.next();
// lables.add(cell.getStringCellValue());
// }
// first = false;
// }
//
// else {
// int j = 0;
// WorkInfoDto e = new WorkInfoDto();
// for (Iterator<Cell> iter2 = row.cellIterator(); iter2
// .hasNext();) {
// Cell cell = iter2.next();
// String val = "";
// if (cell.getCellType() == HSSFCell.CELL_TYPE_BOOLEAN)
// val = String
// .valueOf(cell.getBooleanCellValue());
// else if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC)
// val = String.valueOf((int) cell
// .getNumericCellValue());
// else if (cell.getCellType() == HSSFCell.CELL_TYPE_STRING)
// val = cell.getStringCellValue();
// setEmployeeValue(lables.get(j++), val, e);
// }
// list.add(e);
// }
// }
// return list;
// }
// } catch (Exception ex) {
// ex.printStackTrace();
// } finally {
// try {
// inputstream.close();
// } catch (IOException e) {
// e.printStackTrace();
// }
// }
// return null;
boolean success = true;
HSSFWorkbook rwb = null;
try {
rwb = new HSSFWorkbook(inputstream);
HSSFSheet st = rwb.getSheetAt(0);
if (st != null) {
ArrayList<WorkInfoDto> list = new ArrayList<WorkInfoDto>();
ArrayList<String> lables = new ArrayList<String>();
boolean first = true;
for (Iterator<Row> iter = st.rowIterator(); iter.hasNext(); ) {
Row row = iter.next();
if (first) {
for (Iterator<Cell> iter2 = row.cellIterator(); iter2.hasNext(); ) {
Cell cell = iter2.next();
lables.add(cell.getStringCellValue());
}
first = false;
} else {
int j = 0;
WorkInfoDto e = new WorkInfoDto();
boolean complete = true;
for (Iterator<Cell> iter2 = row.cellIterator(); iter2.hasNext(); ) {
Cell cell = iter2.next();
String val = "";
if (cell.getCellType() == HSSFCell.CELL_TYPE_BOOLEAN)
val = String.valueOf(cell.getBooleanCellValue());
else if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC)
val = String.valueOf((int) cell.getNumericCellValue());
else if (cell.getCellType() == HSSFCell.CELL_TYPE_STRING)
val = cell.getStringCellValue();
if (lables.get(j).toLowerCase().equals("id") && exist(val)) {
System.out.println(lables.get(j) + " : " + val);
complete = false;
success = false;
}
setEmployeeValue(lables.get(j++), val, e);
}
if (complete) list.add(e);
else {
sessionManager.addGlobalMessageWarn(
"ID: " + e.getId() + " has existed. This employee is not added into system.",
null);
// FacesMessage message = new FacesMessage("ID: " +
// e.getId() +
// " has existed. This employee is not added into system.",null);
}
}
}
if (success)
sessionManager.addGlobalMessageInfo("The data file is successfully uploaded.", null);
else
sessionManager.addGlobalMessageWarn(
"There are employees that not be added to the system.", null);
return list;
}
} catch (Exception ex) {
ex.printStackTrace();
} finally {
try {
inputstream.close();
} catch (IOException e) {
e.printStackTrace();
}
}
return null;
}
public static void main(String[] args) {
int i, j = 0, col = 0, row1 = 0, col1 = 0, cluster;
try {
int itr = 0;
ArrayList<Double> al = new ArrayList<Double>();
ArrayList<Integer> index = new ArrayList<Integer>();
ArrayList<Double> al4 = new ArrayList<Double>();
FileInputStream fileInputStream = new FileInputStream(new File("E:\\NewExcelFile.xls"));
XSSFWorkbook workbook = new XSSFWorkbook(fileInputStream);
XSSFSheet worksheet = workbook.getSheet("FirstSheet");
Scanner in = new Scanner(System.in);
Iterator<Row> rowIterator = worksheet.iterator();
// FOR GETTING ROWS AND COLUMNS FROM A FILE
while (rowIterator.hasNext()) {
row1++;
Row row = rowIterator.next();
Iterator<Cell> cellIterator = row.cellIterator();
while (cellIterator.hasNext()) {
Cell cell = cellIterator.next();
double a1Val = cell.getNumericCellValue();
al.add(a1Val);
col++;
}
col1 = col;
col = 0;
}
al.clear();
// for getting the ELEMENTS IN MATRIX B
double b[][] = new double[row1][col1];
Iterator<Row> rowIterator1 = worksheet.iterator();
i = 0;
j = 0;
while (rowIterator1.hasNext()) {
Row row = rowIterator1.next();
Iterator<Cell> cellIterator1 = row.cellIterator();
j = 0;
while (cellIterator1.hasNext()) {
Cell cell = cellIterator1.next();
double a1Val = cell.getNumericCellValue();
b[i][j] = a1Val;
j++;
}
i++;
}
// double result[][]=new double[row1][col+1];
// PRINTING ELEMENTS OF 2-D ARRAY DATA
System.out.println("Printing The Content Of The File...\n");
for (i = 0; i < row1; i++) {
for (j = 0; j < col1; j++) {
System.out.print(b[i][j] + " ");
}
System.out.print("\n");
}
System.out.println("Enter The Number Of Clusters - ");
cluster = in.nextInt();
// Randomly Select
int z = 0;
Random rand = new Random();
int indexArr[] = new int[cluster];
while (z != cluster) {
int randIndex = rand.nextInt(row1);
if (!index.contains(randIndex)) {
index.add(randIndex);
indexArr[z] = randIndex;
z++;
}
}
// First Cenroid Initialization
double centroid[][] = new double[cluster][col1];
for (i = 0; i < cluster; i++) {
for (j = 0; j < col1; j++) {
centroid[i][j] = b[indexArr[i]][j];
}
}
System.out.println("\n\nPrinting Initial Centroids at iteration " + itr);
for (i = 0; i < cluster; i++) {
for (j = 0; j < col1; j++) {
System.out.print(" " + centroid[i][j]);
}
System.out.println();
}
// distance calculation
double dist_mat[][] = new double[row1][cluster];
int k;
double dist = 0, res = 0;
for (i = 0; i < cluster; i++) {
for (j = 0; j < row1; j++) {
for (k = 0; k < col1; k++) {
dist = dist + Math.pow(centroid[i][k] - b[j][k], 2);
}
res = Math.round(Math.sqrt(dist) * 100.0) / 100.0;
al.add(res);
dist = 0;
}
}
k = 0;
for (j = 0; j < cluster; j++) {
for (i = 0; i < row1; i++) {
dist_mat[i][j] = al.get(k++);
}
}
System.out.println("Displaying Initial Distance Matrix for iteration " + itr + " is");
for (i = 0; i < row1; i++) {
for (j = 0; j < cluster; j++) {
System.out.print(+dist_mat[i][j] + "\t\t");
}
System.out.println();
}
// Finding the minimum frm distance matrix..
double dist_cost = 0;
int mat_pos[] = new int[row1];
for (i = 0; i < row1; i++) {
double min = dist_mat[i][0];
for (j = 0; j < cluster; j++) {
if (dist_mat[i][j] < min) {
min = dist_mat[i][j];
mat_pos[i] = j;
}
}
// System.out.println("The minimum value in row"+i+" is "+min);
dist_cost += min;
}
System.out.print(
"\n\nThe Initial Total cost calculated for iteration " + itr + " is \n" + dist_cost);
// Printing minimum position array..
for (i = 0; i < row1; i++) {
System.out.print(" " + mat_pos[i]);
}
// Saving the mat_pos and totalcost in temp arry mat_pos1 totalcost1
int mat_pos1[] = new int[row1];
for (i = 0; i < row1; i++) {
mat_pos1[i] = mat_pos[i];
}
double total_cost = dist_cost;
int randIndex = rand.nextInt(row1);
// BIG LOOP
itr = 0;
int s = 0;
while (s < (row1 - cluster)) {
// System.out.println("S = :"+s);
randIndex = rand.nextInt(row1);
if (!index.contains(randIndex)) {
index.add(randIndex);
indexArr[cluster - 1] = randIndex;
for (i = 0; i < cluster; i++) {
for (j = 0; j < col1; j++) {
centroid[i][j] = b[indexArr[i]][j];
}
}
itr++;
System.out.println(
"\n\n---------------------------------------------------------------------------");
System.out.println("\nPrinting Centroids In Iteration... " + itr);
for (i = 0; i < cluster; i++) {
for (j = 0; j < col1; j++) {
System.out.print(" " + centroid[i][j]);
}
System.out.println();
}
al.clear();
dist = 0;
res = 0;
for (i = 0; i < cluster; i++) {
for (j = 0; j < row1; j++) {
for (k = 0; k < col1; k++) {
dist = dist + Math.pow(centroid[i][k] - b[j][k], 2);
}
res = Math.round(Math.sqrt(dist) * 100.0) / 100.0;
al.add(res);
dist = 0;
}
}
k = 0;
for (j = 0; j < cluster; j++) {
for (i = 0; i < row1; i++) {
dist_mat[i][j] = al.get(k++);
}
}
System.out.println("\n\nDisplaying Distance Matrix In Iteration..." + itr + "\n");
for (i = 0; i < row1; i++) {
for (j = 0; j < cluster; j++) {
System.out.print(+dist_mat[i][j] + "\t\t");
}
System.out.println();
}
// Finding the minimum frm distance matrix..
dist_cost = 0;
for (i = 0; i < row1; i++) {
double min = dist_mat[i][0];
for (j = 0; j < cluster; j++) {
if (dist_mat[i][j] < min) {
min = dist_mat[i][j];
mat_pos[i] = j;
}
}
// System.out.println("The minimum value in row"+i+" is "+min);
dist_cost += min;
}
System.out.print("\n\nThe Total Cost In Iteration " + itr + " Is " + dist_cost + "\n");
// Printing minimum position array..
System.out.println("\n\nPosition Of Objects In Cluster In Iteration " + itr + "\n");
for (i = 0; i < row1; i++) {
System.out.print(" " + mat_pos[i]);
}
if (dist_cost < total_cost) {
total_cost = dist_cost;
for (i = 0; i < row1; i++) {
mat_pos1[i] = mat_pos[i];
}
}
s++;
}
}
System.out.println(
"\n\n---------------------------------------------------------------------------");
System.out.println("\n..........FINAL RESULT.............");
System.out.println("Mat Position....");
for (i = 0; i < row1; i++) {
System.out.print(" " + mat_pos1[i]);
}
System.out.println("\n\nValue Of Minimum Cost Is " + total_cost);
double result[][] = new double[row1][col1 + 1];
for (i = 0; i < row1; i++) {
for (j = 0; j < col1; j++) {
result[i][j] = b[i][j];
}
result[i][col1] = mat_pos1[i];
}
// PRINTING THE FINAL RESULT
System.out.println("\n\nTHE FINAL RESULT WHICH IS TO BE SHOWN IS");
for (i = 0; i < cluster; i++) {
for (j = 0; j < row1; j++) {
if (mat_pos1[j] == i) {
for (k = 0; k < col1; k++) {
al4.add(b[j][k]);
}
}
}
// System.out.println("AL4 SIZE : "+al4.size());
System.out.println("the value of cluster " + (i + 1) + " is ");
System.out.print("{");
for (k = 0; k < al4.size(); k++) {
System.out.print("(");
for (j = 0; j < col1; j++) {
System.out.print(al4.get(0) + ",");
al4.remove(0);
}
System.out.print(")");
}
System.out.println("}");
}
/* System.out.println("\n\nTHE FINAL RESULT WHICH IS TO BE SHOWN IS");
for(i=0;i<row1;i++)
{
for(j=0;j<col1;j++)
{
System.out.print((result[i][j])+"\t");
}
System.out.print((int)(result[i][j]));
System.out.println();
}
*/
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
}