Apache POI 导入导出EXCLE
来源：互联网 发布：php 二进制 编辑：程序博客网 时间：2024/05/22 00:39
导出数据库数据
public void exportData() { System.out.println("<<<<<<<<<<<<<<< BEGIN >>>>>>>>>>>>>>>"); List<LytUser> userLst = userDao.getUserLst(); List<UserVo> data = addData(userLst); //创建一个excle文件 HSSFWorkbook hb = new HSSFWorkbook(); //创建一个工作簿 HSSFSheet sheet = hb.createSheet("老用户数据列表"); //设置工作簿长度 //TODO设置生日的时候要改成YYYY-MM-DD形式 sheet.setDefaultColumnWidth(150); //生成一个样式 HSSFCellStyle style = hb.createCellStyle(); //创建第一行（也可以称为表头） HSSFRow row = sheet.createRow(0); //设置字体居中 style.setAlignment(HSSFCellStyle.ALIGN_CENTER); //创建各个数据列 HSSFCell cell = row.createCell(0); cell.setCellStyle(style); cell.setCellValue("序号"); cell = row.createCell(1); cell.setCellStyle(style); cell.setCellValue("昵称"); cell = row.createCell(2); cell.setCellStyle(style); cell.setCellValue("手机"); cell = row.createCell(3); cell.setCellStyle(style); cell.setCellValue("签名"); cell = row.createCell(4); cell.setCellStyle(style); cell.setCellValue("性别"); cell = row.createCell(5); cell.setCellStyle(style); cell.setCellValue("头像"); cell = row.createCell(6); cell.setCellStyle(style); cell.setCellValue("生日"); cell = row.createCell(7); cell.setCellStyle(style); cell.setCellValue("邮箱"); //遍历录入数据 for (int i = 0;i<data.size();i++ ) { row = sheet.createRow(i+1); row.createCell(0).setCellValue(i+1); row.createCell(1).setCellValue(data.get(i).getNickName()); row.createCell(2).setCellValue(data.get(i).getPhone()); row.createCell(3).setCellValue(data.get(i).getSignature()); row.createCell(4).setCellValue(data.get(i).getSex()); row.createCell(5).setCellValue(data.get(i).getHeadImg()); row.createCell(6).setCellValue(data.get(i).getBirthday()); row.createCell(7).setCellValue(data.get(i).getEmail()); } try { //默认导出到E盘下 FileOutputStream out = new FileOutputStream("E://用户列表.xls"); hb.write(out); hb.close(); out.close(); System.out.println("<<<<<<<<<<<<<<< FINISH AND SUCCESS >>>>>>>>>>>>>>>"); } catch (FileNotFoundException e) { System.out.println("<<<<<<<<<<<<<<< FAILED >>>>>>>>>>>>>>>"); e.printStackTrace(); } catch (IOException e) { System.out.println("<<<<<<<<<<<<<<< FAILED >>>>>>>>>>>>>>>"); e.printStackTrace(); } }导入数据库
public void insertData(){ System.out.println(">>>>>>>>>>>>>>> INSERT BEGIN <<<<<<<<<<<<<<<"); try { //读取xls文件 InputStream is = new FileInputStream(new File("C:\\Users\\lk\\Desktop\\用户列表.xls")); HSSFWorkbook xw = new HSSFWorkbook(is); SitUser user = null; //读取数据 for(int numSheet = 0;numSheet<xw.getNumberOfSheets();numSheet++){ HSSFSheet xssfSheet = xw.getSheetAt(numSheet); if(xssfSheet==null){ continue; } for(int rowNum = 1;rowNum<=xssfSheet.getLastRowNum();rowNum++){ HSSFRow hssfRow = xssfSheet.getRow(rowNum); if(hssfRow!=null){ user = new SitUser(); HSSFCell nick = hssfRow.getCell(1); HSSFCell account = hssfRow.getCell(2); HSSFCell signature = hssfRow.getCell(3); HSSFCell sex = hssfRow.getCell(4); HSSFCell headImg = hssfRow.getCell(5); HSSFCell birthday = hssfRow.getCell(6); HSSFCell email = hssfRow.getCell(7); user.setNickName(getValue(nick)); if(getValue(account)!=null&&getValue(account).length()==11){ user.setMobile(getValue(account)); user.setAccountType(Short.valueOf("0")); SitUserInfo sitUserInfo = new SitUserInfo(); sitUserInfo.setAccount(getValue(account)); sitUserInfo.setPassword(null); sitUserInfo.setAccountType(Short.valueOf("0")); sitUserInfo.setIsDisable(false); ByteBuffer buffer = ByteBuffer.allocate(SSOConst.MODEL_MEM_SIZE); String key = myRedis.calcKey(SSOConst.REDIS_SYSTEM, SSOConst.REDIS_APP_USER, SSOConst.APP_SIT, getValue(account)); myRedis.setObj(key, sitUserInfo, -1, true, buffer); }else if(getValue(account)!=null&&getValue(account).length()==10){ String openId = getValue(account); String newAccount = "3-"+getValue(account); user.setMobile(newAccount); user.setAccountType(Short.valueOf("3")); SitUserInfo sitUserInfo = new SitUserInfo(); sitUserInfo.setAccount(newAccount); sitUserInfo.setPassword(null); sitUserInfo.setAccountType(Short.valueOf("3")); sitUserInfo.setIsDisable(false); ByteBuffer buffer = ByteBuffer.allocate(SSOConst.MODEL_MEM_SIZE); String key = myRedis.calcKey(SSOConst.REDIS_SYSTEM, SSOConst.REDIS_APP_USER, SSOConst.APP_SIT, newAccount); myRedis.setObj(key, sitUserInfo, -1, true, buffer); AppThirdInfo userThird = new AppThirdInfo(); userThird.setAccount(newAccount); userThird.setAccountType(Short.valueOf("3")); key = myRedis.calcKey(SSOConst.REDIS_SYSTEM, SSOConst.REDIS_APP_THIRD, ThirdPartConst.THIRD_TYPE_WEIBO, openId); myRedis.setObj(key, userThird, -1, true, buffer); }else if(getValue(account)!=null&&(getValue(account).contains("-")||getValue(account).startsWith("oG"))){ String openId = getValue(account); String newAccount = "2-"+getValue(account); user.setMobile(newAccount); user.setAccountType(Short.valueOf("2")); SitUserInfo sitUserInfo = new SitUserInfo(); sitUserInfo.setAccount(newAccount); sitUserInfo.setPassword(null); sitUserInfo.setAccountType(Short.valueOf("2")); sitUserInfo.setIsDisable(false); ByteBuffer buffer = ByteBuffer.allocate(SSOConst.MODEL_MEM_SIZE); String key = myRedis.calcKey(SSOConst.REDIS_SYSTEM, SSOConst.REDIS_APP_USER, SSOConst.APP_SIT, newAccount); myRedis.setObj(key, sitUserInfo, -1, true, buffer); AppThirdInfo userThird = new AppThirdInfo(); userThird.setAccount(newAccount); userThird.setAccountType(Short.valueOf("2")); key = myRedis.calcKey(SSOConst.REDIS_SYSTEM, SSOConst.REDIS_APP_THIRD, ThirdPartConst.THIRD_TYPE_WEIXIN, openId); myRedis.setObj(key, userThird, -1, true, buffer); }else{ String openId = getValue(account); String newAccount = "1-"+getValue(account); user.setMobile(newAccount); user.setAccountType(Short.valueOf("1")); SitUserInfo sitUserInfo = new SitUserInfo(); sitUserInfo.setAccount(newAccount); sitUserInfo.setPassword(null); sitUserInfo.setAccountType(Short.valueOf("1")); sitUserInfo.setIsDisable(false); ByteBuffer buffer = ByteBuffer.allocate(SSOConst.MODEL_MEM_SIZE); String key = myRedis.calcKey(SSOConst.REDIS_SYSTEM, SSOConst.REDIS_APP_USER, SSOConst.APP_SIT, newAccount); myRedis.setObj(key, sitUserInfo, -1, true, buffer); AppThirdInfo userThird = new AppThirdInfo(); userThird.setAccount(newAccount); userThird.setAccountType(Short.valueOf("1")); key = myRedis.calcKey(SSOConst.REDIS_SYSTEM, SSOConst.REDIS_APP_THIRD, ThirdPartConst.THIRD_TYPE_QQ, openId); myRedis.setObj(key, userThird, -1, true, buffer); } user.setSignature(getValue(signature)); if(getValue(sex).trim()=="男"){ user.setGender(Short.valueOf("0")); } if(getValue(sex).trim()=="女"){ user.setGender(Short.valueOf("1")); } user.setAvatar(getValue(headImg)); user.setBirthday(getValue(birthday)); user.setEmail(getValue(email)); sitUserDao.addUserInfo(user); System.out.println(">>>>>>>>>>>>>>> INSERT SUCCESS <<<<<<<<<<<<<<<"); } } System.out.println(">>>>>>>>>>>>>>> INSERT FINISHED <<<<<<<<<<<<<<<"); } } catch (Exception e) { e.printStackTrace(); } }@SuppressWarnings("static-access") private String getValue(HSSFCell hssfRow) { if (hssfRow.getCellType() == hssfRow.CELL_TYPE_BOOLEAN) { return String.valueOf(hssfRow.getBooleanCellValue()); } else if (hssfRow.getCellType() == hssfRow.CELL_TYPE_NUMERIC) { return String.valueOf(hssfRow.getNumericCellValue()); } else { return String.valueOf(hssfRow.getStringCellValue()); } } 0 0
- Apache POI 导入导出EXCLE
- Apache poi导出word和Excle
- POI导出Excle HSSF
- poi导出excle
- POI导出excle数据
- springMVC导入excle poi
- excle导入导出
- excle 导入导出数据
- java excle导入 导出
- yii 导入导出excle
- POI根据模板导出Excle
- poi导出excle方式二
- excle导出纵向表格(POI)
- 导入/导出Excel利用apache.poi
- 关于apache POI excel文件导入导出
- POI导出excle，文件导出到本地
- POI实现大数据EXCLE导入导出，解决内存溢出问题
- Java操作POI导出excle（单个excle）
- 个人对C\C++的体会
- USCAO The Castle
- 使用libcaffe为工程添加深度学习功能
- Android软键盘遮挡输入框解决方法
- 手动调整Ubuntu16系统的分辨率
- Apache POI 导入导出EXCLE
- 用例设计过程
- togo
- 【spring】——依赖注入
- Linux学习笔记--多命令执行
- Scala学习笔记 --- scala中class与object
- eclipse安装jadeclipse
- 快速实现python c扩展模块
- PAT A1028. List Sorting (25)
