该篇文章使用poi3.5的版本
需要导入包如下
execl导入
/** * execl 数据导入 * @author 小涂 * @title : import_excel * @date Aug 21, 2013 1:04:42 PM * @return String */ public static String import_excel(){ String id=null; String name=null; String sex=null; String Dormitory=null; String Sept=null; Workbook workbook = null; int k=0; int flag = 0; //指示指针所访问的位置 String path="C:/Users/X/Desktop/212.xls";//获取文件的路径 try { InputStream is = new FileInputStream(path); HSSFWorkbook hssfWorkbook = new HSSFWorkbook(is); // 循环工作表Sheet for (int numSheet = 0; numSheet < hssfWorkbook.getNumberOfSheets(); numSheet++) { HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(numSheet); if (hssfSheet == null) { continue; } // 循环行Row for (int rowNum = 1; rowNum <= hssfSheet.getLastRowNum(); rowNum++) { HSSFRow hssfRow = hssfSheet.getRow(rowNum); if (hssfRow == null) { continue; } // 循环列Cell // "学号","姓名","性别","寝室号","所在系"}; for (int cellNum = 0; cellNum <=4; cellNum++) { HSSFCell xh = hssfRow.getCell(cellNum); if (xh == null) { continue; } System.out.print(getValue(xh)+"\t"); } System.out.print("\n"); } } }catch (Exception e) { // TODO: handle exception e.printStackTrace(); } return null; } /** * 得到Excel表中的值 * * @param hssfCell * Excel中的每一个格子 * @return Excel中每一个格子中的值 */ @SuppressWarnings("static-access") private static String getValue(HSSFCell hssfCell) { if (hssfCell.getCellType() == hssfCell.CELL_TYPE_BOOLEAN) { // 返回布尔类型的值 return String.valueOf(hssfCell.getBooleanCellValue()); } else if (hssfCell.getCellType() == hssfCell.CELL_TYPE_NUMERIC) { // 返回数值类型的值 return String.valueOf(hssfCell.getNumericCellValue()); } else { // 返回字符串类型的值 return String.valueOf(hssfCell.getStringCellValue()); } }
导出execl 文件
/** * 导出execl 文件 * @author 小涂 * @title : extport * @date Aug 21, 2013 12:27:38 PM * @return String */ public String extport(){ List studentList=new ArrayList();//学生LIst for(int i=0;i<10;i++) { Student student=new Student();//学生对象 student.setStudentId("200908110"+i); student.setStudentName("杨波"+i); student.setStudentSex("男"); student.setStudentDormitory("14-20"+i); student.setStudentSept("软件工程系"); studentList.add(student); } String []tableHeader={"学号","姓名","性别","寝室号","所在系"}; short cellNumber=(short)tableHeader.length;//表的列数 HSSFWorkbook workbook = new HSSFWorkbook(); //创建一个excel HSSFCell cell = null; //Excel的列 HSSFRow row = null; //Excel的行 HSSFCellStyle style = workbook.createCellStyle(); //设置表头的类型 style.setAlignment(HSSFCellStyle.ALIGN_CENTER); HSSFCellStyle style1 = workbook.createCellStyle(); //设置数据类型 style1.setAlignment(HSSFCellStyle.ALIGN_CENTER); HSSFFont font = workbook.createFont(); //设置字体 HSSFSheet sheet = workbook.createSheet("sheet1"); //创建一个sheet HSSFHeader header = sheet.getHeader();//设置sheet的头 try { if(studentList.size() < 1 ){ header.setCenter("查无资料"); }else{ header.setCenter("学生表"); row = sheet.createRow(0); row.setHeight((short)400); for(int k = 0;k < cellNumber;k++){ cell = row.createCell(k);//创建第0行第k列 cell.setCellValue(tableHeader[k]);//设置第0行第k列的值 sheet.setColumnWidth(k,8000);//设置列的宽度 font.setColor(HSSFFont.COLOR_NORMAL); // 设置单元格字体的颜色. font.setFontHeight((short)350); //设置单元字体高度 style1.setFont(font);//设置字体风格 cell.setCellStyle(style1); } for(int i = 0 ;i < studentList.size() ;i++){ Student student1 = (Student)studentList.get(i);//获取student对象 row = sheet.createRow((short) (i + 1));//创建第i+1行 row.setHeight((short)400);//设置行高 if(student1.getStudentId() != null){ cell = row.createCell(0);//创建第i+1行第0列 cell.setCellValue(student1.getStudentId());//设置第i+1行第0列的值 cell.setCellStyle(style);//设置风格 } if(student1.getStudentName() != null){ cell = row.createCell(1); //创建第i+1行第1列 cell.setCellValue(student1.getStudentName());//设置第i+1行第1列的值 cell.setCellStyle(style); //设置风格 } //由于下面的和上面的基本相同,就不加注释了 if(student1.getStudentSex() != null){ cell = row.createCell(2); cell.setCellValue(student1.getStudentSex()); cell.setCellStyle(style); } if(student1.getStudentDormitory()!= null){ cell = row.createCell(3); cell.setCellValue(student1.getStudentDormitory()); cell.setCellStyle(style); } if(student1.getStudentSept() != null){ cell = row.createCell(4); cell.setCellValue(student1.getStudentSept()); cell.setCellStyle(style); } } } } catch (Exception e) { e.printStackTrace(); } // HttpServletResponse response = null;//创建一个HttpServletResponse对象 OutputStream out = null; try { out = new FileOutputStream(new File("C:/Users/X/Desktop/212.xls")); //response = ServletActionContext.getResponse();//初始化HttpServletResponse对象 //out = response.getOutputStream();// } catch (FileNotFoundException e1) { // TODO Auto-generated catch block e1.printStackTrace(); } try {// String headerStr ="student学生";// headerStr =new String(headerStr.getBytes("gb2312"), "ISO8859-1");//headerString为中文时转码// response.setHeader("Content-disposition","attachment; filename="+ headerStr+".xls");//filename是下载的xls的名,建议最好用英文// response.setContentType("application/msexcel;charset=UTF-8");//设置类型// response.setHeader("Pragma","No-cache");//设置头// response.setHeader("Cache-Control","no-cache");//设置头// response.setDateHeader("Expires", 0);//设置日期头 workbook.write(out); out.flush(); workbook.write(out); } catch (IOException e) { e.printStackTrace(); }finally{ try{ if(out!=null){ out.close(); } }catch(IOException e){ e.printStackTrace(); } } return null; }
如果你是在web应用是使用请将Response 和out 的代码打开
测试结果
导出文件
导入数据