1.poi解析excel表格数据
//读取上传的Excel文件HSSFWorkbook workbook = new HSSFWorkbook(new FileInputStream(myFile));//读取第一个标签页HSSFSheet sheet = workbook.getSheetAt(0);//遍历标签页中的所有行for (Row row : sheet) {int rowNum = row.getRowNum();// 行索引,从0开始if(rowNum == 0){//标题行，忽略continue;}String id = row.getCell(0).getStringCellValue();String province = row.getCell(1).getStringCellValue();String city = row.getCell(2).getStringCellValue();String district = row.getCell(3).getStringCellValue();String postcode = row.getCell(4).getStringCellValue();Region region = new Region(id, province, city, district, postcode, null, null, null);list.add(region);}regionService.saveBatch(list); 2.poi生成excel表格数据
//查询所有的分区数据Listlist = subareaService.findAll();//使用POI将分区数据写到Excel文件中（内存文件）HSSFWorkbook workbook = new HSSFWorkbook();//创建一个标签页HSSFSheet sheet = workbook.createSheet("分区数据");HSSFCellStyle style = wb.createCellStyle(); style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式 //创建 标题行HSSFRow headRow = sheet.createRow(0);//创建单元格headRow.createCell(0).setCellValue("分区编号");headRow.createCell(1).setCellValue("地址关键字");headRow.createCell(2).setCellValue("省市区");headRow.createCell(3).setCellValue("地址详细信息");for (Subarea subarea : list) {//创建数据行HSSFRow dataRow = sheet.createRow(sheet.getLastRowNum() + 1);dataRow.createCell(0).setCellValue(subarea.getId());dataRow.createCell(1).setCellValue(subarea.getAddresskey());dataRow.createCell(2).setCellValue(subarea.getRegion().getName());dataRow.createCell(3).setCellValue(subarea.getPosition());}String filename = "分区数据.xls";String agent = ServletActionContext.getRequest().getHeader("User-Agent");filename = FileUtils.encodeDownloadFilename(filename, agent);String contentType = ServletActionContext.getServletContext().getMimeType(filename);//一个流（输出流）、两个头（服务端响应的头信息）ServletActionContext.getResponse().setContentType(contentType);ServletActionContext.getResponse().setHeader("content-disposition", "attachement;filename="+filename);//使用输出流向客户端写文件ServletOutputStream out = ServletActionContext.getResponse().getOutputStream();workbook.write(out);out.close();
以上是ssh框架中的部分代码：
如果要使用表格的样式：
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.createSheet("考勤表"); HSSFRow row = sheet.createRow((int) 0); HSSFCellStyle style = wb.createCellStyle(); style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式 HSSFCell cell = row.createCell((short) 0);cell.setCellValue("姓名");
cell.setCellStyle(style);以下是ssm框架中的代码：
/** * 考勤统计导出 **/ public String toExcel(String string,String name){ System.out.println(name); System.out.println(string); JSONArray array =JSONArray.fromObject(string); Object[] obj = new Object[array.size()]; List list = new ArrayList(); for(int i = 0; i < array.size(); i++){ JSONObject jsonObject = array.getJSONObject(i); Attendance user = new Attendance( (String) jsonObject.get("name"), (Integer) jsonObject.get("day"), (Integer) jsonObject.get("normal"), (Integer) jsonObject.get("late"), (Integer) jsonObject.get("lateMin"), (Integer) jsonObject.get("advance"), (Integer) jsonObject.get("leaveMin"), (Integer) jsonObject.get("absenteeism"), (Integer) jsonObject.get("miss"), (Integer) jsonObject.get("repair"), (Integer) jsonObject.get("out"), (Integer) jsonObject.get("rest"), (Integer) jsonObject.get("leave"), (Integer) jsonObject.get("travel"), (String) jsonObject.get("workHours")); list.add(user); } String url=toExcel(list, name); return url; } public static long length(String dir){ File f = new File(dir); if(f.exists()&&f.isFile()){ return f.length(); }else{ return 0; } } public static String toExcel(List list,String name){ try{ HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet sheet = wb.createSheet("考勤表"); HSSFRow row = sheet.createRow((int) 0); HSSFCellStyle style = wb.createCellStyle(); style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式 HSSFCell cell = row.createCell((short) 0); cell.setCellValue("姓名"); cell.setCellStyle(style); cell = row.createCell((short) 1); cell.setCellValue("出勤天数"); cell.setCellStyle(style); cell = row.createCell((short) 2); cell.setCellValue("正常打卡"); cell.setCellStyle(style); cell = row.createCell((short) 3); cell.setCellValue("迟到次数"); cell.setCellStyle(style); cell = row.createCell((short) 4); cell.setCellValue("迟到总分钟数"); cell.setCellStyle(style); cell = row.createCell((short) 5); cell.setCellValue("早退次数"); cell.setCellStyle(style); cell = row.createCell((short) 6); cell.setCellValue("早退总分钟数"); cell.setCellStyle(style); cell = row.createCell((short) 7); cell.setCellValue("旷工次数"); cell.setCellStyle(style); cell = row.createCell((short) 8); cell.setCellValue("缺卡次数"); cell.setCellStyle(style); cell = row.createCell((short) 9); cell.setCellValue("补卡次数"); cell.setCellStyle(style); cell = row.createCell((short) 10); cell.setCellValue("外出打卡次数"); cell.setCellStyle(style); cell = row.createCell((short) 11); cell.setCellValue("休息次数"); cell.setCellStyle(style); cell = row.createCell((short) 12); cell.setCellValue("请假次数"); cell.setCellStyle(style); cell = row.createCell((short) 13); cell.setCellValue("出差次数"); cell.setCellStyle(style); cell = row.createCell((short) 14); cell.setCellValue("平均工时"); cell.setCellStyle(style); for (int i = 0; i < list.size(); i++) { row = sheet.createRow((int) i + 1); Attendance a = (Attendance) list.get(i); row.createCell((short) 0).setCellValue(a.getName()); row.createCell((short) 1).setCellValue(a.getDay()); row.createCell((short) 2).setCellValue(a.getNormal()); row.createCell((short) 3).setCellValue(a.getLate()); row.createCell((short) 4).setCellValue(a.getLateMin()); row.createCell((short) 5).setCellValue(a.getAdvance()); row.createCell((short) 6).setCellValue(a.getLateMin()); row.createCell((short) 7).setCellValue(a.getAbsenteeism()); row.createCell((short) 8).setCellValue(a.getMiss()); row.createCell((short) 9).setCellValue(a.getRepair()); row.createCell((short) 10).setCellValue(a.getOut()); row.createCell((short) 11).setCellValue(a.getRest()); row.createCell((short) 12).setCellValue(a.getLeave()); row.createCell((short) 13).setCellValue(a.getTravel()); row.createCell((short) 14).setCellValue(a.getWorkHours()); } //这里调用咱们六楼的牛逼产品返回一个路径给前端，记得改一下 //FileOutputStream fout = new FileOutputStream("/usr/local/tomcat8/webapps/hiteamDDBES/upload/"+name+".xls"); //FileOutputStream fout = new FileOutputStream("D:/workspace/apache-tomcat-8.0.30/webapps/hiteamDDBES/upload/"+name+".xls"); String str = Thread.currentThread().getContextClassLoader().getResource("").toString(); String filePath = str.substring(str.indexOf("/")+1,str.lastIndexOf("/W"))+"/upload/"+name+".xls"; FileOutputStream fout = new FileOutputStream(filePath); wb.write(fout); fout.close(); 代码仅供参考