Java读写Excel实例分享

话不多说，请看代码：

ExcelUtil.java

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

调用方式如下

ExcelUtilTester.java

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

以上就是本文的全部内容，希望本文的内容对大家的学习或者工作能带来一定的帮助，同时也希望多多支持我们！