Java读写Excel实例分享

package pers.kangxu.datautils.utils;
import java.io.file;
import java.io.fileinputstream;
import java.io.fileoutputstream;
import java.io.ioexception;
import java.io.inputstream;
import java.util.arraylist;
import java.util.hashmap;
import java.util.iterator;
import java.util.list;
import java.util.map;
import org.apache.poi.hssf.usermodel.hssfcellstyle;
import org.apache.poi.hssf.usermodel.hssffont;
import org.apache.poi.hssf.usermodel.hssfworkbook;
import org.apache.poi.ss.usermodel.cell;
import org.apache.poi.ss.usermodel.cellstyle;
import org.apache.poi.ss.usermodel.cellvalue;
import org.apache.poi.ss.usermodel.font;
import org.apache.poi.ss.usermodel.formulaevaluator;
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.ss.util.cellrangeaddress;
/**
 * 
 * <b>
 *  excel 工具
 * </b>
 * @author kangxu
 *
 */
public class excelutil {
  /**
   * 导出 excel
   * @param filepath 文件全路径
   * @param sheetname sheet页名称
   * @param sheetindex 当前sheet下表 从0开始
   * @param fileheader 头部
   * @param datas 内容
   */
  public static void writeexcel(string filepath,string sheetname,
                  int sheetindex,
                  string[] fileheader,
                  list<string[]> datas){
    // 创建工作簿
    workbook wb = new hssfworkbook();
    // 创建工作表 sheet
    sheet s = wb.createsheet();
    wb.setsheetname(sheetindex, sheetname);
    row r = s.createrow(0);
    cell c = null;
    font font = null; 
    cellstyle styleheader = null; 
    cellstyle stylecontent = null;
    //粗体 
    font = wb.createfont(); 
    font.setboldweight(hssffont.boldweight_bold); 
    // 设置头样式
    styleheader = wb.createcellstyle(); 
    styleheader.setfont(font); 
    styleheader.setborderbottom(hssfcellstyle.border_thin); //下边框  
    styleheader.setborderleft(hssfcellstyle.border_thin);//左边框  
    styleheader.setbordertop(hssfcellstyle.border_thin);//上边框  
    styleheader.setborderright(hssfcellstyle.border_thin);//右边框 
    // 设置内容样式
    stylecontent = wb.createcellstyle();
    stylecontent.setborderbottom(hssfcellstyle.border_thin); //下边框  
    stylecontent.setborderleft(hssfcellstyle.border_thin);//左边框  
    stylecontent.setbordertop(hssfcellstyle.border_thin);//上边框  
    stylecontent.setborderright(hssfcellstyle.border_thin);//右边框 
    //设置头
    for(int i=0;i<fileheader.length;){
      c = r.createcell(i);
      c.setcellstyle(styleheader);
      c.setcellvalue(fileheader[i]);
      i++;
    }
    //设置内容
    for(int rownum=0;rownum<datas.size();){ // 行 row  datas.size()
      r = s.createrow(rownum+1); //创建行
      for(int cellnum=0;cellnum<fileheader.length;){
        c = r.createcell(cellnum);
        c.setcellvalue(datas.get(rownum)[cellnum]);
        c.setcellstyle(stylecontent);
        cellnum++;
      }
      rownum++;
    }
    fileoutputstream out = null;
    try {
      // 创建文件或者文件夹,将内容写进去
      if(fileutil.createfile(new file(filepath))){
        out = new fileoutputstream(filepath);
        wb.write(out);
      }
      
    } catch (exception e) {
      e.printstacktrace();
    }finally {
      try {
        // 关闭流
        if(out != null){
          out.flush();
          out.close();
        }
      } catch (ioexception e) {
        e.printstacktrace();
      }
    } 
  }
  /**
   * 读取 excel 文件内容
   * @param filepath
   * @param sheetindex
   */
  public static list<map<string,string>> readexcel(string filepath,int sheetindex){
    list<map<string,string>> maplist = new arraylist<map<string,string>>();
    // 头
    list<string> list = new arraylist<string>();
    // 
    int cnt = 0;
    int idx = 0;
    try { 
      inputstream input = new fileinputstream(filepath); //建立输入流 
      workbook wb = null; 
      wb = new hssfworkbook(input); 
      // 获取sheet页
      sheet sheet = wb.getsheetat(sheetindex); 
      iterator<row> rows = sheet.rowiterator(); 
      while (rows.hasnext()) { 
        row row = rows.next();
        iterator<cell> cells = row.celliterator(); 
        map<string,string> map = new hashmap<string,string>();
        if(cnt == 0){ // 将头放进list中
          while (cells.hasnext()) { 
            cell cell = cells.next(); 
            if(iscontainmergecell(sheet)){
              cancelmergecell(sheet);
            }
            list.add(getstringcellvalue(cell));
          }
          cnt ++;
          continue;
        }else {
          while (cells.hasnext()) { 
            cell cell = cells.next(); 
            if(iscontainmergecell(sheet)){
              cancelmergecell(sheet);
            }
            // 区别相同的头
            list = listutil.changesameval(list); 
            map.put(list.get(idx++), getstringcellvalue(cell));
          }
        }
        idx = 0;
        maplist.add(map);
      } 
      return maplist;
    } catch (ioexception ex) { 
      ex.printstacktrace(); 
    }
    return null;
  }
  /**
   * 合并单元格
   * @param sheet  当前sheet页
   * @param firstrow 开始行
   * @param lastrow 结束行
   * @param firstcol 开始列
   * @param lastcol 结束列
   */
  public static int mergecell(sheet sheet,int firstrow,int lastrow,int firstcol,int lastcol){
    if(sheet == null){
      return -1;
    }
    return sheet.addmergedregion(new cellrangeaddress(firstrow, lastrow, firstcol, lastcol));
  }
  /**
   * 取消合并单元格
   * @param sheet
   * @param idx
   */
  public static void cancelmergecell(sheet sheet){
    int sheetmergecount = sheet.getnummergedregions();
    for(int idx = 0; idx < sheetmergecount;){
      cellrangeaddress range = sheet.getmergedregion(idx);
      string val = getmergecellvalue(sheet,range.getfirstrow(),range.getlastrow());
      // 取消合并单元格
      sheet.removemergedregion(idx);
      for(int rownum=range.getfirstrow();rownum<range.getlastrow()+1;){
        for(int cellnum=range.getfirstcolumn();cellnum<range.getlastcolumn()+1;){
          sheet.getrow(rownum).getcell(cellnum).setcellvalue(val);
          cellnum ++;
        }
        rownum ++;
      }
      idx++;
    }
  }
  /**
   * 判断指定单元格是否是合并单元格
   * @param sheet  当前sheet页
   * @param firstrow 开始行
   * @param lastrow 结束行
   * @param firstcol 开始列
   * @param lastcol 结束列
   * @return
   */
  public static boolean ismergecell(sheet sheet,
      int row ,int column){
    int sheetmergecount = sheet.getnummergedregions();
    for(int i = 0; i < sheetmergecount;){
      cellrangeaddress range = sheet.getmergedregion(i);
      int firstcolumn = range.getfirstcolumn(); 
      int lastcolumn = range.getlastcolumn(); 
      int firstrow = range.getfirstrow(); 
      int lastrow = range.getlastrow();
      if(row >= firstrow && row <= lastrow){
        if(column >= firstcolumn && column <= lastcolumn){ 
          return true; 
        } 
      }
      i++;
    }
    return false;
  }
  /**
   * 判断sheet页中是否含有合并单元格
   * @param sheet
   * @return
   */
  public static boolean iscontainmergecell(sheet sheet){
    if(sheet == null){
      return false;
    }
    return sheet.getnummergedregions()>0 ? true : false;
  }
  /**
   * 获取指定合并单元的值
   * @param sheet
   * @param row
   * @param column
   * @return
   */
  public static string getmergecellvalue(sheet sheet,
      int row ,int column){
    int sheetmergecount = sheet.getnummergedregions();
    for(int i = 0; i < sheetmergecount;){
      cellrangeaddress range = sheet.getmergedregion(i);
      int firstcolumn = range.getfirstcolumn(); 
      int lastcolumn = range.getlastcolumn(); 
      int firstrow = range.getfirstrow(); 
      int lastrow = range.getlastrow();
      if(row >= firstrow && row <= lastrow){
        if(column >= firstcolumn && column <= lastcolumn){ 
          row frow = sheet.getrow(firstrow);  
          cell fcell = frow.getcell(firstcolumn);
          return getstringcellvalue(fcell) ;  
        } 
      }
      i++;
    }
    return null;
  }
  /**
   * 获取单元格的值
   * @param cell
   * @return
   */
  public static string getstringcellvalue(cell cell) { 
    string strcell = ""; 
    if(cell==null) return strcell; 
    switch (cell.getcelltype()) { 
      case cell.cell_type_string:
        strcell = cell.getrichstringcellvalue().getstring().trim(); 
        break; 
      case cell.cell_type_numeric:  
        strcell = string.valueof(cell.getnumericcellvalue()); 
        break; 
      case cell.cell_type_boolean:   
        strcell = string.valueof(cell.getbooleancellvalue()); 
        break; 
      case cell.cell_type_formula:   
        formulaevaluator evaluator = cell.getsheet().getworkbook().getcreationhelper().createformulaevaluator(); 
        evaluator.evaluateformulacell(cell); 
        cellvalue cellvalue = evaluator.evaluate(cell); 
        strcell = string.valueof(cellvalue.getnumbervalue()) ; 
        break; 
      default: 
        strcell = ""; 
    } 
    return strcell; 
  }
}

package pers.kangxu.datautils.test;
import java.util.arraylist;
import java.util.list;
import pers.kangxu.datautils.utils.excelutil;
public class excelutiltester {
  public static void main(string[] args) {
    list<string[]> datas = new arraylist<string[]>();
    datas.add(new string[]{"狗熊","母","250"});
    datas.add(new string[]{"猪粮","不明","251"});
    //excelutil.writeexcel("c:\\users\\administrator\\desktop\\test\\test\\test.xls","sheet1",0, new string[]{"姓名","年龄","性别"}, datas);
    system.out.println(excelutil.readexcel("c:\\users\\administrator\\desktop\\test\\test\\test.xls", 0));
  }
}