package com.sinosoft.common.excel; import java.io.FileInputStream; import java.io.FileNotFoundException; import java.io.IOException; import java.io.UnsupportedEncodingException; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import java.text.DateFormat; 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.lz.system.util.JsonUtil; /** * 读取Excel将数据返回到頁面 * * @author 彭志超 * */ @Path("/GovernmentExcelToDBTableTool/") public class GovernmentExcelToDBTableTool { private static final Logger log = Logger.getLogger(GovernmentExcelToDBTableTool.class); @ProduceMime("text/html") @POST @Path("/importFile") public String importFile(@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"); GovernmentExcelToDBTableTool xte = new GovernmentExcelToDBTableTool(); result=xte.readExcelByName(path); }else{ //返回失败信息 result=""; } log.info("result======"+result); return result; } public String readExcelByName(String fileName) throws Exception { GovernmentExcelToDBTableTool tool = new GovernmentExcelToDBTableTool(); // 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 * @throws UnsupportedEncodingException */ public String fillMap(List list) throws UnsupportedEncodingException{ List>dataList=new ArrayList>(); for(int i=0;itempMap=new HashMap(); String[]arr=list.get(i); String[] SECRECY_LEVEL_240 = arr[5]==null ? null:arr[5].split(":"); // tempMap.put("COMP_ID_265", arr[0]); tempMap.put("CLASSES_240",new String(java.net.URLEncoder.encode(arr[0]==null ? "":arr[0], "UTF-8").getBytes(), "ISO-8859-1")); tempMap.put("DISPATCH_UNIT_240",new String(java.net.URLEncoder.encode(arr[1]==null ? "":arr[1], "UTF-8").getBytes(), "ISO-8859-1")); tempMap.put("DOCUMENT_NAME_240", new String(java.net.URLEncoder.encode(arr[2]==null ? "":arr[2], "UTF-8").getBytes(), "ISO-8859-1")); tempMap.put("DISPATCH_WORD_SIZE_240", new String(java.net.URLEncoder.encode(arr[3]==null ? "":arr[3], "UTF-8").getBytes(), "ISO-8859-1")); tempMap.put("PRINT_DATE_240", new String(java.net.URLEncoder.encode(arr[4]==null ? "":arr[4], "UTF-8").getBytes(), "ISO-8859-1")); if(SECRECY_LEVEL_240!=null) tempMap.put("SECRECY_LEVEL_240", new String(java.net.URLEncoder.encode(SECRECY_LEVEL_240[0], "UTF-8").getBytes(), "ISO-8859-1")); else tempMap.put("SECRECY_LEVEL_240", ""); tempMap.put("DESCRIPTION_240", new String(java.net.URLEncoder.encode(arr[6]==null ? "":arr[6], "UTF-8").getBytes(), "ISO-8859-1")); 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 */ public String getValue(HSSFCell cell) throws ParseException { String value = ""; String value1 = ""; switch (cell.getCellType()) { case HSSFCell.CELL_TYPE_NUMERIC: // 数值型 System.out.println("数字类型"); if (HSSFDateUtil.isCellDateFormatted(cell)) { log.info("时间类型============="); // 如果是date类型则 ，获取该cell的date值 Date d = cell.getDateCellValue(); log.info(d); DateFormat format = new SimpleDateFormat("yyyy-MM-dd"); value = format.format(d); } else {// 纯数字 cell.setCellType(Cell.CELL_TYPE_STRING); value = cell.getStringCellValue(); } 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; } // public boolean dataIntoDb(String tabName,Listlist) throws ClassNotFoundException, SQLException{ // Class.forName("dm.jdbc.driver.DmDriver"); // Connection conn = DriverManager.getConnection("jdbc:dm://192.168.1.16:5236","NWYJ","NWYJ123456"); // String sql="INSERT INTO SYS_MSG_MODEL(FD_OBJECTID,MSG_MODEL_TYPE,MSG_MODEL_CONTENT,MSG_MODEL_RANGE,MSG_MODEL_COMP,MSG_MODEL_USER,MSG_MODEL_TIME,IS_DEL)VALUES(?,?,?,?,?,?,?,?)"; // PreparedStatement ps=null; // conn.setAutoCommit(false); // ps = conn.prepareStatement(sql); // ps.setString(1, (long) ((Math.random()+1)*1000000000)+""); // ps.setString(2, list.get(0)); // ps.setString(3,list.get(1)); // ps.setString(4, list.get(2)); // ps.setString(5, list.get(3)); // ps.setString(6, list.get(4)); // ps.setString(7, list.get(5)); // ps.setString(8,list.get(6)); // int k=ps.executeUpdate(); // conn.commit(); //// System.out.println("k====="+k); // return false; // } /** * 将界面传输过来的数据保存到数据库 * @param params * @return * @throws ClassNotFoundException */ @SuppressWarnings("static-access") @ProduceMime("application/json") @POST @Path("/governmentToDb") public Msg governmentToDb(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); //单位 String compid = Utils.getParameter("compid", params) == null ? "" : Utils .getParameter("compid", params); log.info("compid===" + compid); //录入人 String userid = Utils.getParameter("userId", params) == null ? "" : Utils .getParameter("userId", params); log.info("userid===" + userid); //录入人姓名 String username = Utils.getParameter("userName", params) == null ? "" : Utils .getParameter("userName", params); log.info("userName===" + username); json=json.replace("_"+classId, ""); Connection conn = null; PreparedStatement st = null; DbConnection db = new DbConnection(); List list = JsonPluginsUtil.jsonToBeanList(json, GovernmentBean.class); String sql="INSERT INTO EMC_AM_GOVERNMENT_DOCUMENT (FD_OBJECTID,CLASSES,DISPATCH_UNIT,DOCUMENT_NAME,DISPATCH_WORD_SIZE," + "PRINT_DATE,STATE,DESCRIPTION,SECRECY_LEVEL,TYPEIN_PEOPLE,TYPEIN_TIME,UNITE,DEPT,OBJ_FIRST,IS_ISSUE," + "TYPEIN_PEOPLE_ID,IS_SHARE,IS_DEL,UPDATEDATE) 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 EMC_AM_GOVERNMENT_DOCUMENT"; 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; } } }