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