package com.ordobill.webapp.common;
import java.io.InputStream;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.GregorianCalendar;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.struts.upload.FormFile;
import com.ibatis.sqlmap.client.SqlMapClient;
import com.ordobill.webapp.beans.ImportBean;
import com.ordobill.webapp.beans.Project;
public class PoiManager {
private SqlMapClient sqlMap = SqlMapClientManager.getSqlMapClient();
/*static FormFile importfile;
public PoiManager(FormFile file) {
// TODO Auto-generated constructor stub
importfile = file;
}*/
public void excelData(FormFile importfile,String pro_id) throws Exception {
int fieldsize = 1;
int rowSize = 0;
Row row = null;
Cell cell = null;
ArrayList<ImportBean> resultdata = new ArrayList<ImportBean>();
InputStream data = importfile.getInputStream();
//System.out.println(data.);
XSSFWorkbook wb = new XSSFWorkbook(data);
Sheet sheet = wb.getSheetAt(0);
if (sheet != null) {
while (true) {
ImportBean importBean = new ImportBean();
row = sheet.getRow(fieldsize);
//System.out.println(row.getPhysicalNumberOfCells());
try {
if (row.getCell(0).equals(null)) {
break;
}
importBean.setSubsystem(row.getCell(0).toString().trim());
importBean.setCategory(row.getCell(1).toString().trim());
importBean.setItem(row.getCell(2).toString().trim());
importBean.setFail_mode(row.getCell(3).toString().trim());
importBean.setFailure_distribution(row.getCell(4).toString().trim());
importBean.setFparam1(chkCellfloat(row.getCell(5)));
importBean.setFparam2(chkCellfloat(row.getCell(6)));
importBean.setFparam3(chkCellfloat(row.getCell(7)));
//importBean.setFparam3(row.getCell(7));
importBean.setRparam1(chkCellfloat(row.getCell(8)));
importBean.setRparam2(chkCellfloat(row.getCell(9)));
importBean.setRparam3(chkCellfloat(row.getCell(10)));
importBean.setFailure(chkCellfloat(row.getCell(11)));
importBean.setRepair(chkCellfloat(row.getCell(12)));
importBean.setConnection((int)row.getCell(13).getNumericCellValue());
importBean.setUserID(pro_id);
resultdata.add(rowSize, importBean);
fieldsize++;
rowSize++;
System.out.println(resultdata.size());
} catch(NullPointerException e) {
break;
} finally {
//System.out.println(resultdata.get(rowSize).getItem());
}
}
}
System.out.println(resultdata.size());
this.importData(resultdata);
}
public int LinkData(String[] _data,String pro_id) throws Exception {
int fieldsize = 1;
int rowSize = 0;
Row row = null;
Cell cell = null;
ArrayList<ImportBean> resultdata = new ArrayList<ImportBean>();
for (int i=0 ; i < _data.length; i++)
{
String[] spStr = _data[i].split("┘");
ImportBean importBean = new ImportBean();
importBean.setSubsystem(spStr[0].trim());
importBean.setCategory(spStr[1].trim());
importBean.setItem(spStr[2].trim());
importBean.setFail_mode(spStr[3].trim());
importBean.setFailure_distribution(spStr[4].trim());
importBean.setFparam1(Float.valueOf(spStr[5].trim()).floatValue());
importBean.setFparam2(Float.valueOf(spStr[6].trim()).floatValue());
importBean.setFparam3(Float.valueOf(spStr[7].trim()).floatValue());
//importBean.setFparam3(row.getCell(7));
importBean.setRparam1(Float.valueOf(spStr[8].trim()).floatValue());
importBean.setRparam2(Float.valueOf(spStr[9].trim()).floatValue());
importBean.setRparam3(Float.valueOf(spStr[10].trim()).floatValue());
importBean.setFailure(Float.valueOf(spStr[11].trim()).floatValue());
importBean.setRepair(Float.valueOf(spStr[12].trim()).floatValue());
importBean.setConnection(Integer.parseInt(spStr[13].trim()));
importBean.setUserID(pro_id);
resultdata.add(rowSize, importBean);
rowSize++;
}
System.out.println(resultdata.size());
return this.importData(resultdata);
}
public int importData (ArrayList<ImportBean> result) throws Exception{
ImportBean importBean = new ImportBean();
GregorianCalendar calender = new GregorianCalendar();
SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
String dateTime = dateFormat.format(calender.getTime());
//System.out.println(result.size());
int prouid = (Integer) sqlMap.queryForObject("Import.getPK", importBean);
for (int i=0;i<result.size();i++) {
result.get(i).setProUid(prouid);
System.out.println((Integer)sqlMap.insert("Import.importInsert",result.get(i)));
}
sqlMap.insert("Import.importInsertPR",importBean);
sqlMap.insert("Import.importInsertBR",importBean);
sqlMap.insert("Import.importInsertEQ",importBean);
sqlMap.insert("Import.importInsertFM",importBean);
sqlMap.delete("Import.importDelete",importBean);
//projectBean.setProName(result.get(1).getCell(1));
//return (Integer)sqlMap.insert("Project.projectInsert", projectBean);
return prouid;
}
public float chkCellfloat(Cell cell) {
/*System.out.println(cell.);
if (cell.equals("") ||cell == null) {
//cell.setCellValue("");
System.out.println("->"+cell);
}*/
float value = 0f;
if (cell != null) {
value = Float.valueOf(cell.toString().trim()).floatValue();
}
System.out.println(value);
return value;
}
}