java:poi读写2003版excel

写出excel

package cn.manager.controller;import java.io.IOException;import java.io.OutputStream;import java.util.Iterator;import java.util.List;import javax.servlet.http.HttpServletRequest;import javax.servlet.http.HttpServletResponse;import org.apache.poi.hssf.usermodel.HSSFCell;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.hssf.util.CellRangeAddress;import cn.manager.model.Student;import cn.manager.model.Teacher;public class AdminExportExcel {   //导出教师信息public static void expotTeacher(HttpServletRequest request,HttpServletResponse response,List<Teacher> list) throws IOException{//创建HSSFWorkbook对象(excel的文档对象)        HSSFWorkbook wb = new HSSFWorkbook();  //建立新的sheet对象（excel的表单）  HSSFSheet sheet=wb.createSheet("信息表");  //在sheet里创建第一行，参数为行索引(excel的行)，可以是0～65535之间的任何一个  HSSFRow row1=sheet.createRow(0);  //创建单元格（excel的单元格，参数为列索引，可以是0～255之间的任何一个  HSSFCell cell=row1.createCell(0);        //设置单元格内容  cell.setCellValue("教师信息一览表");  //合并单元格CellRangeAddress构造参数依次表示起始行，截至行，起始列， 截至列  sheet.addMergedRegion(new CellRangeAddress(0,0,0,3));  //在sheet里创建第二行  HSSFRow row2=sheet.createRow(1);            //创建单元格并设置单元格内容        row2.createCell(0).setCellValue("工号");        row2.createCell(1).setCellValue("姓名");            row2.createCell(2).setCellValue("学院");        row2.createCell(3).setCellValue("电话");            //在sheet里创建第三行       List<Teacher> e = list;      Iterator< Teacher> it = e.iterator();      int i = 2;      while(it.hasNext()){      HSSFRow row3=sheet.createRow(i);       Teacher teacher = it.next();      row3.createCell(0).setCellValue(teacher.getJobnumber());        row3.createCell(1).setCellValue(teacher.getUsername());        row3.createCell(2).setCellValue(teacher.getCollege());            row3.createCell(3).setCellValue(teacher.getPhone());       i+=1;      System.out.println(teacher);      }      //.....省略部分代码      //输出Excel文件      OutputStream output=response.getOutputStream();      response.reset();      response.setHeader("Content-disposition", "attachment; filename=teacherdetails.xls");      response.setContentType("application/msexcel");              wb.write(output);      output.close();  }//导出学生信息public static void expotStudent(HttpServletRequest request,HttpServletResponse response,List<Student> list) throws IOException{//创建HSSFWorkbook对象(excel的文档对象)        HSSFWorkbook wb = new HSSFWorkbook();  //建立新的sheet对象（excel的表单）  HSSFSheet sheet=wb.createSheet("信息表");  //在sheet里创建第一行，参数为行索引(excel的行)，可以是0～65535之间的任何一个  HSSFRow row1=sheet.createRow(0);  //创建单元格（excel的单元格，参数为列索引，可以是0～255之间的任何一个  HSSFCell cell=row1.createCell(0);        //设置单元格内容  cell.setCellValue("学生信息一览表");  //合并单元格CellRangeAddress构造参数依次表示起始行，截至行，起始列， 截至列  sheet.addMergedRegion(new CellRangeAddress(0,0,0,3));  //在sheet里创建第二行  HSSFRow row2=sheet.createRow(1);            //创建单元格并设置单元格内容        row2.createCell(0).setCellValue("学号");        row2.createCell(1).setCellValue("姓名");            row2.createCell(2).setCellValue("学院");        row2.createCell(3).setCellValue("电话");         row2.createCell(3).setCellValue("qq");       //在sheet里创建第三行       List<Student> e = list;      Iterator< Student> it = e.iterator();      int i = 2;      while(it.hasNext()){      HSSFRow row3=sheet.createRow(i);       Student stu = it.next();      row3.createCell(0).setCellValue(stu.getSn());        row3.createCell(1).setCellValue(stu.getUsername());        row3.createCell(2).setCellValue(stu.getCollege());         row3.createCell(3).setCellValue(stu.getPhone());       row3.createCell(3).setCellValue(stu.getQq());       i+=1;      System.out.println(stu);      }      //.....省略部分代码      //输出Excel文件      OutputStream output=response.getOutputStream();      response.reset();      response.setHeader("Content-disposition", "attachment; filename=studentdetails.xls");      response.setContentType("application/msexcel");              wb.write(output);      output.close();  }}

读取excel内容

 

package cn.manager.controller;import java.io.FileInputStream;import java.io.IOException;import java.util.LinkedList;import java.util.List;import org.apache.poi.hssf.usermodel.HSSFWorkbook;import org.apache.poi.ss.usermodel.Cell;import org.apache.poi.ss.usermodel.Row;import org.apache.poi.ss.usermodel.Sheet;import org.apache.poi.ss.usermodel.Workbook;import org.apache.poi.xssf.usermodel.XSSFWorkbook;import cn.manager.model.Student;import cn.manager.model.Teacher;public class ExcelMk {public static void main(String[] args) {        String filePath = "C:\\Users\\admin\\Desktop\\1.xls";get(filePath);}public static List<Teacher> get(String filePath){List<Teacher> list = new LinkedList<Teacher>();//判断是否为excel类型文件    if(!filePath.endsWith(".xls")&&!filePath.endsWith(".xlsx")){      System.out.println("文件不是excel类型");    }    FileInputStream fis =null;    Workbook wookbook = null;    try  {      //获取一个绝对地址的流        fis = new FileInputStream(filePath);    }    catch(Exception e) {      e.printStackTrace();    }    try{      //2003版本的excel，用.xls结尾       System.out.println("xls");       wookbook = new HSSFWorkbook(fis);//得到工作簿    }     catch (Exception ex){      try   {      System.out.println("xlsx");        //2007版本的excel，用.xlsx结尾        wookbook = new XSSFWorkbook(fis);//得到工作簿      } catch (IOException e)  {      }    }    //得到一个工作表    Sheet sheet = wookbook.getSheetAt(0);    //获得表头    Row rowHead = sheet.getRow(0);    //判断表头是否正确    if(rowHead.getPhysicalNumberOfCells() != 4)   {      System.out.println("表头的数量不对!");    }    //获得数据的总行数    int totalRowNum = sheet.getLastRowNum();    //要获得属性    int jobnumber ,phone;    String username = null,college = null;     //获得所有数据    for(int i = 1 ; i <= totalRowNum ; i++){      //获得第i行对象      Row row = sheet.getRow(i);      //获得获得第i行第0列的 String类型对象      Cell cell = row.getCell((short)0);      jobnumber = (int) cell.getNumericCellValue();      //获得一个数字类型的数据      cell = row.getCell((short)1);      username = cell.getStringCellValue().toString();      cell = row.getCell((short)2);      college = cell.getStringCellValue().toString();      cell = row.getCell((short)3);      phone = (int) cell.getNumericCellValue();      Teacher tea = new Teacher();      tea.setJobnumber(jobnumber+"");      tea.setUsername(username);      tea.setCollege(college);      tea.setPhone(phone+"");      tea.setPassword("123456");      tea.setRole("ROLE_TEACHER");      list.add(tea);    }    return list;  }public static List<Student> getStudent(String filePath){List<Student> list = new LinkedList<Student>();//判断是否为excel类型文件    if(!filePath.endsWith(".xls")&&!filePath.endsWith(".xlsx")){      System.out.println("文件不是excel类型");    }    FileInputStream fis =null;    Workbook wookbook = null;    try  {      //获取一个绝对地址的流        fis = new FileInputStream(filePath);    }    catch(Exception e) {      e.printStackTrace();    }    try{      //2003版本的excel，用.xls结尾       System.out.println("xls");       wookbook = new HSSFWorkbook(fis);//得到工作簿    }     catch (Exception ex){      try   {      System.out.println("xlsx");        //2007版本的excel，用.xlsx结尾        wookbook = new XSSFWorkbook(fis);//得到工作簿      } catch (IOException e)  {      }    }    //得到一个工作表    Sheet sheet = wookbook.getSheetAt(0);    //获得表头    Row rowHead = sheet.getRow(0);    //判断表头是否正确    if(rowHead.getPhysicalNumberOfCells() != 5)   {      System.out.println("表头的数量不对!");    }    //获得数据的总行数    int totalRowNum = sheet.getLastRowNum();    //要获得属性    int sn ,phone,qq;    String username = null,college = null;     //获得所有数据    for(int i = 1 ; i <= totalRowNum ; i++){      //获得第i行对象      Row row = sheet.getRow(i);      //获得获得第i行第0列的 String类型对象      Cell cell = row.getCell((short)0);      sn = (int) cell.getNumericCellValue();      //获得一个数字类型的数据      cell = row.getCell((short)1);      username = cell.getStringCellValue().toString();      cell = row.getCell((short)2);      college = cell.getStringCellValue().toString();      cell = row.getCell((short)3);      phone = (int) cell.getNumericCellValue();      cell = row.getCell((short)4);      qq = (int) cell.getNumericCellValue();      Student stu = new Student();      stu.setCollege(college);      stu.setPhone(phone+"");      stu.setQq(qq+"");      stu.setUsername(username);      stu.setSn(sn+"");      stu.setPassword("$2a$10$959Czz6tokHXpkYti7jksOf8rXPY9NTN.IRE8cdODnoivYdzLdgfm");      stu.setRole("ROLE_STUDENT");      list.add(stu);    }    return list;  }}