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));  }}