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