快捷導(dǎo)航

Java讀寫Excel實(shí)例分享

更新時間：2017年01月04日 09:21:57 作者：kangxu

本文主要分享了Java讀寫Excel的實(shí)例代碼。具有一定的參考價值，下面跟著小編一起來看下吧

話不多說，請看代碼：

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 {
  /**
   * 導(dǎo)出 excel
   * @param filePath 文件全路徑
   * @param sheetName sheet頁名稱
   * @param sheetIndex 當(dāng)前sheet下表 從0開始
   * @param fileHeader 頭部
   * @param datas 內(nèi)容
   */
  public static void writeExcel(String filePath,String sheetName,
                  int sheetIndex,
                  String[] fileHeader,
                  List<String[]> datas){
    // 創(chuàng)建工作簿
    Workbook wb = new HSSFWorkbook();
    // 創(chuàng)建工作表 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); 
    // 設(shè)置頭樣式
    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);//右邊框 
    // 設(shè)置內(nèi)容樣式
    styleContent = wb.createCellStyle();
    styleContent.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下邊框  
    styleContent.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左邊框  
    styleContent.setBorderTop(HSSFCellStyle.BORDER_THIN);//上邊框  
    styleContent.setBorderRight(HSSFCellStyle.BORDER_THIN);//右邊框 
    //設(shè)置頭
    for(int i=0;i<fileHeader.length;){
      c = r.createCell(i);
      c.setCellStyle(styleHeader);
      c.setCellValue(fileHeader[i]);
      i++;
    }
    //設(shè)置內(nèi)容
    for(int rownum=0;rownum<datas.size();){ // 行 row  datas.size()
      r = s.createRow(rownum+1); //創(chuàng)建行
      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 {
      // 創(chuàng)建文件或者文件夾,將內(nèi)容寫進(jìn)去
      if(FileUtil.createFile(new File(filePath))){
        out = new FileOutputStream(filePath);
        wb.write(out);
      }
      
    } catch (Exception e) {
      e.printStackTrace();
    }finally {
      try {
        // 關(guān)閉流
        if(out != null){
          out.flush();
          out.close();
        }
      } catch (IOException e) {
        e.printStackTrace();
      }
    } 
  }
  /**
   * 讀取 excel 文件內(nèi)容
   * @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){ // 將頭放進(jìn)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);
            }
            // 區(qū)別相同的頭
            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  當(dāng)前sheet頁
   * @param firstRow 開始行
   * @param lastRow 結(jié)束行
   * @param firstCol 開始列
   * @param lastCol 結(jié)束列
   */
  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  當(dāng)前sheet頁
   * @param firstRow 開始行
   * @param lastRow 結(jié)束行
   * @param firstCol 開始列
   * @param lastCol 結(jié)束列
   * @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; 
  }
}

調(diào)用方式如下

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

以上就是本文的全部內(nèi)容，希望本文的內(nèi)容對大家的學(xué)習(xí)或者工作能帶來一定的幫助，同時也希望多多支持腳本之家！

您可能感興趣的文章: