package com.sinosoft.em.gps.service; import java.io.FileInputStream; import java.io.FileNotFoundException; import java.io.IOException; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import java.text.ParseException; import java.text.SimpleDateFormat; import java.util.ArrayList; import java.util.Date; import java.util.HashMap; import java.util.Iterator; import java.util.List; import java.util.Map; import java.util.Vector; import javax.servlet.http.HttpServletRequest; import javax.servlet.http.HttpServletResponse; import javax.ws.rs.POST; import javax.ws.rs.Path; import javax.ws.rs.ProduceMime; import javax.ws.rs.core.Context; import org.apache.log4j.Logger; import org.apache.poi.hssf.usermodel.HSSFCell; import org.apache.poi.hssf.usermodel.HSSFDateUtil; import org.apache.poi.hssf.usermodel.HSSFRow; import org.apache.poi.hssf.usermodel.HSSFSheet; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.poifs.filesystem.POIFSFileSystem; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.DateUtil; import org.apache.poi.xssf.usermodel.XSSFCell; import org.apache.poi.xssf.usermodel.XSSFRow; import org.apache.poi.xssf.usermodel.XSSFSheet; import org.apache.poi.xssf.usermodel.XSSFWorkbook; import com.formaction.Parameter; import com.formaction.Utils; import com.formaction.vo.Msg; import com.persistence.DbConnection; import com.sinosoft.common.upLoad.service.UploadManageImpl; import com.sinosoft.common.util.JsonPluginsUtil; import com.sinosoft.lz.system.util.JsonUtil; /** * 读取Excel将数据返回到頁面 * * @author 彭志超 * */ @Path("/BGDmingxibiao/") public class BGDmingxibiao { private static final Logger log = Logger.getLogger(BGDmingxibiao.class); @ProduceMime("application/json") @POST @Path("/importExpert") public String importExpert(@Context HttpServletRequest request, @Context HttpServletResponse response) throws Exception{//新增保存 // Msg m=new Msg(); UploadManageImpl upload2 = new UploadManageImpl(); String path = upload2.uploadFilePath(request, response); String result=""; log.info("saveFile.path===="+path); if("success".equals(path.split(";")[1])){ //将数据返回到界面 path=path.split(";")[2]; // String report_type=request.getParameter("report_type"); BGDmingxibiao xte = new BGDmingxibiao(); result=xte.readExcelByName(path); }else{ //返回失败信息 result=""; } return result; } @ProduceMime("application/json") @POST @Path("/getGeneratorCarExcel") public String readExcelByName(String fileName) throws Exception { BGDmingxibiao tool = new BGDmingxibiao(); // String fileName=Utils.getParameter("fileName", params) == null ? "" : Utils.getParameter( // "fileName", params); // String fileName="E:\\javaDemo\\person.xls"; int i=fileName.lastIndexOf("."); String type=fileName.substring(i+1); log.info("fileName===="+fileName); String result=""; if("xls".equals(type)){ result=tool.readExcel(fileName,"person"); }else if("xlsx".equals(type)){ result= tool.readExcel2007(fileName,"person"); } result="{\"rows\":"+result+"}"; log.info(result); return result; } // public void readExcel2007(DBModel dbConn, String filePath, String tableName) @SuppressWarnings({ "unused", "rawtypes", "unchecked", "resource" }) public String readExcel2007( String filePath, String tableName) throws Exception { String result=""; try { // InputStream inp = new FileInputStream(filePath); // Workbook wb = WorkbookFactory.create(inp); XSSFWorkbook wb = new XSSFWorkbook(filePath); int sheetSize = wb.getNumberOfSheets(); // for (int i = 0; i < sheetSize; i++) { List list=new ArrayList(); XSSFSheet sheet = wb.getSheetAt(0); // Sheet sheet = wb.getSheetAt(i); for (Iterator rit = sheet.rowIterator(); rit.hasNext();) { // 迭代行 XSSFRow row = (XSSFRow) rit.next(); // 迭代单元格 Vector datas = new Vector(); StringBuffer sb=new StringBuffer(); MaplineMap=new HashMap(); for (Iterator cit = row.cellIterator(); cit.hasNext();) { // 定义集合datas用于存Excel中一个行的数据 XSSFCell cell = (XSSFCell) cit.next(); String value = getValue2007(cell); sb.append(value+"|"); // 注意行和列是基于0索引的 // System.out.print(cell.getRowIndex() + ":" + cell.getColumnIndex() + " "); datas.add(value); // System.out.println(); // 开始操作单元格 } String[] arr=sb.toString().split("[|]"); list.add(arr); // result = fillMap(list);; // 向表中插入数据 // DBFactory.insertData(dbConn, tableName, datas); } result = fillMap(list); System.out.println(result); // } } catch (FileNotFoundException e) { e.printStackTrace(); throw e; } catch (IOException e) { e.printStackTrace(); throw e; } return result; } /** * 將数据转换成json * @param list * @return */ public String fillMap(List list){ List>dataList=new ArrayList>(); for(int i=0;itempMap=new HashMap(); String[]arr=list.get(i); tempMap.put("TASK_NAME_615", arr[0]); tempMap.put("TASK_TIME_615", arr[1]); tempMap.put("INPUT_MEM_615", arr[2]); tempMap.put("INPUT_CAR_615", arr[3]); tempMap.put("LEVEL_615", arr[4]); tempMap.put("DEPT_615", arr[5]); dataList.add(tempMap); } String result=JsonUtil.list2json(dataList); return result; } /** * 将Excel数据导入到表中 * * @param filePath * Excel路径 * @param tableName * 表名 */ // public void readExcel(DBModel dbConn, String filePath, String tableName) @SuppressWarnings({ "deprecation", "unchecked", "rawtypes", "resource" }) public String readExcel( String filePath, String tableName) throws Exception { String result=""; try { POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream( filePath)); // 创建工作簿 HSSFWorkbook workBook = new HSSFWorkbook(fs); log.info(workBook.getNumberOfSheets()); // System.out.println("工作表个数 :" + workBook.getNumberOfSheets()); for (int i = 0; i < workBook.getNumberOfSheets(); i++) { // 创建工作表 HSSFSheet sheet = workBook.getSheetAt(i); int rows = sheet.getPhysicalNumberOfRows(); // 获得行数 if (rows > 0) { sheet.getMargin(HSSFSheet.TopMargin); List list=new ArrayList(); for (int r = 0; r < rows; r++) { // 行循环 HSSFRow row = sheet.getRow(r); if (row != null && r != 0 && r != 1) {// 不取第一行,第二行 int cells = row.getLastCellNum();// 获得列数 // 定义集合datas用于存Excel中一个行的数据 Vector datas = new Vector(); String[]arr=new String[cells]; ListparamList=new ArrayList(); for (short c = 0; c < cells; c++) { // 列循环 HSSFCell cell = row.getCell(c); if (cell != null) { String value = getValue(cell); // System.out.println("第" + r + "行 " + "第" + c + "列：" + value); datas.add(value); paramList.add(value); arr[c]=value; } } list.add(arr); result = fillMap(list); // dataIntoDb(tableName, paramList); // 向表中插入数据 // DBFactory.insertData(dbConn, tableName, datas); } } } else { } } } catch (Exception ex) { ex.printStackTrace(); log.info(ex); throw ex; } return result; } public String getValue2007(XSSFCell cell) { String value = ""; switch (cell.getCellType()) { case Cell.CELL_TYPE_STRING: // System.out.println(cell.getRichStringCellValue().getString()); break; case Cell.CELL_TYPE_NUMERIC: if (DateUtil.isCellDateFormatted(cell)) { // System.out.println(cell.getDateCellValue()); java.util.Date date = cell.getDateCellValue(); SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd"); value = format.format(date); } else { // System.out.println(cell.getNumericCellValue()); value = String.valueOf(cell.getNumericCellValue()); } break; case Cell.CELL_TYPE_BOOLEAN: // System.out.println(cell.getBooleanCellValue()); value = " " + cell.getBooleanCellValue(); break; case Cell.CELL_TYPE_FORMULA: // System.out.println(cell.getCellFormula()); value = cell.getCellFormula(); break; default: System.out.println(); } return value; } /** * 获取Excel中某个单元格的值 * * @param cell * @return * @throws ParseException */ @SuppressWarnings("deprecation") public String getValue(HSSFCell cell) throws ParseException { String value = ""; switch (cell.getCellType()) { case HSSFCell.CELL_TYPE_NUMERIC: // 数值型 System.out.println("数字类型"); // if (HSSFDateUtil.isCellDateFormatted(cell)) { // // 如果是date类型则 ，获取该cell的date值 // value = HSSFDateUtil.getJavaDate(cell.getNumericCellValue()) // .toString(); // java.util.Date date1 = new Date(value); // SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd"); // value = format.format(date1); // } else {// 纯数字 value = String.valueOf(cell.getNumericCellValue()).split("[.]")[0]; } break; /* 此行表示单元格的内容为string类型 */ case HSSFCell.CELL_TYPE_STRING: // 字符串型 value = cell.getStringCellValue(); break; case HSSFCell.CELL_TYPE_FORMULA:// 公式型 // 读公式计算值 value = String.valueOf(cell.getNumericCellValue()); if (value.equals("NaN")) {// 如果获取的数据值为非法值,则转换为获取字符串 value = cell.getStringCellValue().toString(); } cell.getCellFormula(); break; case HSSFCell.CELL_TYPE_BOOLEAN:// 布尔 value = " " + cell.getBooleanCellValue(); break; /* 此行表示该单元格值为空 */ case HSSFCell.CELL_TYPE_BLANK: // 空值 value = ""; break; case HSSFCell.CELL_TYPE_ERROR: // 故障 value = ""; break; default: value = cell.getStringCellValue().toString(); } return value; } /** * 将界面传输过来的数据保存到数据库 * @param params * @return * @throws ClassNotFoundException */ @SuppressWarnings("static-access") @ProduceMime("application/json") @POST @Path("/saveBGDmingxibiao") public Msg saveBGDmingxibiao(String params) throws ClassNotFoundException{ Msg m=new Msg(); String json = Utils.getParameter("json", params) == null ? "" : Utils.getParameter( "json", params); log.info("json===" + json); String classId = Utils.getParameter("classId", params) == null ? "" : Utils .getParameter("classId", params); log.info("classId===" + classId); String deptId = Utils.getParameter("deptId", params) == null ? "" : Utils .getParameter("deptId", params); log.info("deptId===" + deptId); json=json.replace("_"+classId, ""); Connection conn = null; PreparedStatement st = null; DbConnection db = new DbConnection(); List list = JsonPluginsUtil.jsonToBeanList(json, BGDmingxibiaoGS.class); String sql="INSERT INTO ECM_GPS_LIST (FD_OBJECTID,TASK_NAME,TASK_TIME,INPUT_MEM,INPUT_CAR," + "LEVEL,IS_DEL,UPDATEDATE,WRITER,WRITE_TIME,WRITE_DEPT,DEPT) VALUES(?,?,?,?,?,?,?,?,?,?,?,?)"; try { conn = db.getConnection(); int num=0; conn.setAutoCommit(false); st = conn.prepareStatement(sql); for(int i=0;i getMsgId() throws ClassNotFoundException { String sql = "SELECT FD_OBJECTID FROM ECM_GPS_LIST"; Connection conn = null; Statement stat = null; ResultSet rs = null; DbConnection db = new DbConnection(); try { conn = db.getConnection(); stat = conn.createStatement(); rs = stat.executeQuery(sql); List list = new ArrayList(); while (rs.next()) { list.add(rs.getString("FD_OBJECTID")); } return list; } catch (SQLException e) { // this.log.error(e.getMessage(), e); throw new ClassNotFoundException("DAO　Layou: 获得数据库消息ID集合" + sql, e); } finally { db.close(rs); db.close(stat); db.close(conn); } } public String createMsgId(List list) throws ClassNotFoundException{ long l=(long) ((Math.random()+1)*1000000000); String msgId=l+""; if(list.contains(msgId)){ return createMsgId(list); } else{ // log.info("msgid==="+msgId); return msgId; } } }