java的poi技术读取和导入Excel实例

//该类有main方法，主要负责运行程序，同时该类中也包含了用poi读取Excel(2003版) import java.io.FileInputStream;import java.io.IOException;import java.io.InputStream;import java.util.ArrayList;import java.util.List; 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;  /** *  * @author Hongten</br> *  *  */public class XlsMain {   public static void main(String[] args) throws IOException {    XlsMain xlsMain = new XlsMain();    XlsDto xls = null;    List<XlsDto> list = xlsMain.readXls();         try {      XlsDto2Excel.xlsDto2Excel(list);    } catch (Exception e) {      e.printStackTrace();    }    for (int i = 0; i < list.size(); i++) {      xls = (XlsDto) list.get(i);      System.out.println(xls.getXh() + "  " + xls.getXm() + "  "          + xls.getYxsmc() + "  " + xls.getKcm() + "  "          + xls.getCj());    }   }   /**   * 读取xls文件内容   *    * @return List<XlsDto>对象   * @throws IOException   *       输入/输出(i/o)异常   */  private List<XlsDto> readXls() throws IOException {    InputStream is = new FileInputStream("pldrxkxxmb.xls");    HSSFWorkbook hssfWorkbook = new HSSFWorkbook(is);    XlsDto xlsDto = null;    List<XlsDto> list = new ArrayList<XlsDto>();    // 循环工作表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;        }        xlsDto = new XlsDto();        // 循环列Cell        // 0学号 1姓名 2学院 3课程名 4 成绩        // for (int cellNum = 0; cellNum <=4; cellNum++) {        HSSFCell xh = hssfRow.getCell(0);        if (xh == null) {          continue;        }        xlsDto.setXh(getValue(xh));        HSSFCell xm = hssfRow.getCell(1);        if (xm == null) {          continue;        }        xlsDto.setXm(getValue(xm));        HSSFCell yxsmc = hssfRow.getCell(2);        if (yxsmc == null) {          continue;        }        xlsDto.setYxsmc(getValue(yxsmc));        HSSFCell kcm = hssfRow.getCell(3);        if (kcm == null) {          continue;        }        xlsDto.setKcm(getValue(kcm));        HSSFCell cj = hssfRow.getCell(4);        if (cj == null) {          continue;        }        xlsDto.setCj(Float.parseFloat(getValue(cj)));        list.add(xlsDto);      }    }    return list;  }   /**   * 得到Excel表中的值   *    * @param hssfCell   *      Excel中的每一个格子   * @return Excel中每一个格子中的值   */  @SuppressWarnings("static-access")  private 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());    }  } }

//该类主要负责向Excel(2003版)中插入数据import java.io.FileOutputStream;import java.io.OutputStream;import java.util.List; import org.apache.poi.hssf.usermodel.HSSFCell;import org.apache.poi.hssf.usermodel.HSSFRichTextString;import org.apache.poi.hssf.usermodel.HSSFRow;import org.apache.poi.hssf.usermodel.HSSFSheet;import org.apache.poi.hssf.usermodel.HSSFWorkbook;  public class XlsDto2Excel {   /**   *    * @param xls   *      XlsDto实体类的一个对象   * @throws Exception   *       在导入Excel的过程中抛出异常   */  public static void xlsDto2Excel(List<XlsDto> xls) throws Exception {    // 获取总列数    int CountColumnNum = xls.size();    // 创建Excel文档    HSSFWorkbook hwb = new HSSFWorkbook();    XlsDto xlsDto = null;    // sheet 对应一个工作页    HSSFSheet sheet = hwb.createSheet("pldrxkxxmb");    HSSFRow firstrow = sheet.createRow(0); // 下标为0的行开始    HSSFCell[] firstcell = new HSSFCell[CountColumnNum];    String[] names = new String[CountColumnNum];    names[0] = "学号";    names[1] = "姓名";    names[2] = "学院";    names[3] = "课程名";    names[4] = "成绩";    for (int j = 0; j < CountColumnNum; j++) {      firstcell[j] = firstrow.createCell(j);      firstcell[j].setCellValue(new HSSFRichTextString(names[j]));    }    for (int i = 0; i < xls.size(); i++) {      // 创建一行      HSSFRow row = sheet.createRow(i + 1);      // 得到要插入的每一条记录      xlsDto = xls.get(i);      for (int colu = 0; colu <= 4; colu++) {        // 在一行内循环        HSSFCell xh = row.createCell(0);        xh.setCellValue(xlsDto.getXh());        HSSFCell xm = row.createCell(1);        xm.setCellValue(xlsDto.getXm());        HSSFCell yxsmc = row.createCell(2);        yxsmc.setCellValue(xlsDto.getYxsmc());        HSSFCell kcm = row.createCell(3);        kcm.setCellValue(xlsDto.getKcm());        HSSFCell cj = row.createCell(4);        cj.setCellValue(xlsDto.getCj());(xlsDto.getMessage());      }    }    // 创建文件输出流，准备输出电子表格    OutputStream out = new FileOutputStream("POI2Excel/pldrxkxxmb.xls");    hwb.write(out);    out.close();    System.out.println("数据库导出成功");  } } 

//该类是一个实体类public class XlsDto {  /**   * 选课号   */  private Integer xkh;  /**   * 学号   */  private String xh;  /**   * 姓名   */  private String xm;  /**   * 学院   */  private String yxsmc;  /**   * 课程号   */  private Integer kch;  /**   * 课程名   */  private String kcm;  /**   * 成绩   */  private float cj;  public Integer getXkh() {    return xkh;  }  public void setXkh(Integer xkh) {    this.xkh = xkh;  }  public String getXh() {    return xh;  }  public void setXh(String xh) {    this.xh = xh;  }  public String getXm() {    return xm;  }  public void setXm(String xm) {    this.xm = xm;  }  public String getYxsmc() {    return yxsmc;  }  public void setYxsmc(String yxsmc) {    this.yxsmc = yxsmc;  }  public Integer getKch() {    return kch;  }  public void setKch(Integer kch) {    this.kch = kch;  }  public String getKcm() {    return kcm;  }  public void setKcm(String kcm) {    this.kcm = kcm;  }  public float getCj() {    return cj;  }  public void setCj(float cj) {    this.cj = cj;  }   }