POI处理excel

需要包:

　　poi-3.9-20121203.jar

　　poi-ooxml-3.9-20121203.jar

　　poi-ooxml-schemas-3.9-20121203.jar

　　xbean.jar

代码

package com.keertech.miis.news.action;

import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.lang.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellValue;
import org.apache.poi.ss.usermodel.DateUtil;
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.xssf.usermodel.XSSFWorkbook;
import org.apache.struts2.ServletActionContext;

import com.keertech.base.action.JSONAction;
import com.keertech.miis.news.bean.News;
import com.keertech.miis.news.bean.NewsType;

@SuppressWarnings("serial")
public class MainPageAction extends JSONAction{
    
    private final String directoryPath = ServletActionContext.getServletContext().getInitParameter("filedirectory")+"/mainPageFile";
    
    public String turnToMainPage() throws Exception{
        
//        List<NewsType> newsTypes = dao.findAll(NewsType.class, " order by createddate desc Fetch First 5 Rows Only");
        List<NewsType> newsTypes = (List<NewsType>)dao.findListBySql("select * from miis_news_type order by createddate desc Fetch First 5 Rows Only",null,null);
        
        String filterString = "";
        List<News> news = dao.findAll(News.class, filterString);
        
        List<List<String>> excelInfo = readExcel();
        System.out.println(excelInfo.size());
        
        request.setAttribute("newsTypes", newsTypes);
        request.setAttribute("newses", news);
        return SUCCESS;
    }
    
    private List<List<String>> readExcel() throws Exception{
        List<List<String>> result = new ArrayList<List<String>>();
        InputStream input = null;    //excel输入流
        try {
            String webPath = request.getSession().getServletContext().getRealPath("/");    //web项目部署的实际物理地址E:/tomcat/webapps/em/
            String fullPath = webPath+directoryPath;
            File directory = new File(fullPath);
            
            if (directory.isDirectory()) {
                System.out.println(fullPath);
                File files[] = directory.listFiles();
                
                Workbook workbook = null;    //excel工作簿
                File excelFile = null ;
                String excelFileName = null;
                String separator = null;
                for (File file : files) {
                    excelFileName = file.getName();
                    Integer separatorIndex = excelFileName.lastIndexOf(".");//获取扩展名所在位置
                    separator = excelFileName.substring(separatorIndex);//扩展名
                    if(".xls".equals(separator)||".xlsx".equals(separator)) {
                        excelFile = file;
                        input = new FileInputStream(file);
                        break;
                    } 
                }
                //生成excel表格对象
                if (excelFile == null) {
                    throw new Exception("无法获取当前excel表格");
                }
                if(".xls".equals(separator)) {
                    workbook = new HSSFWorkbook(input);
                } else {
                    workbook = new XSSFWorkbook(input);
                }
                
                //解析工作表
                for (int i = 0; i < workbook.getNumberOfSheets(); i++) {
                    Sheet sheet = workbook.getSheetAt(i);
                    if( sheet != null ){
                        for (int j = 5; j <= sheet.getLastRowNum(); j++) {
                            Row row = sheet.getRow(j);    //获取excel行信息
                            List<String> rowList = null;
                            if( row != null ){
                                rowList = new ArrayList<String>();
                                int cellNumber = row.getPhysicalNumberOfCells();
                                //将列数据装载到list中
                                for (int k = 0; k < cellNumber; k++) {
                                    String cellValue = getCellValue(workbook,row.getCell(k)).toString();
                                    if (StringUtils.isNotBlank(cellValue)) {
                                        rowList.add(cellValue);
                                    }
                                }
                                result.add(rowList);
                            }else {
                                continue;
                            }
                        }
                    }
                }
            }
            
        } catch (Exception e) {
            e.printStackTrace();
            throw new Exception(e.getMessage());
        } finally{
            if(input!=null){
                input.close();
            }
        }
        return result;
    }
    
    /**
     * 获取单元格值
     * @param wb
     * @param cell
     * @return
     */
    protected Object getCellValue(Workbook wb,Cell cell) {
        Object result = "";
        if(cell!=null){
            switch (cell.getCellType()){
                case Cell.CELL_TYPE_STRING:
                    result =  cell.getStringCellValue().trim();
                    break;
                case Cell.CELL_TYPE_NUMERIC:
                    if (DateUtil.isCellDateFormatted(cell)){
                        result = cell.getDateCellValue();
                    } else {
                        long longData = (long)cell.getNumericCellValue();
                        if(longData == cell.getNumericCellValue()){
                            result = String.valueOf(longData).trim();
                        }else{
                            result = String.valueOf(cell.getNumericCellValue()).trim();
                        }
                    }
                    break;
                case Cell.CELL_TYPE_FORMULA:    //处理公式
                    //cell.setCellFormula(cell.getCellFormula());
                    FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator();  
                    CellValue cellValue = evaluator.evaluate(cell);  
                    //BigDecimal bigDecimal = new BigDecimal(cell.getCachedFormulaResultType());
                    BigDecimal bigDecimal = new BigDecimal(cellValue.getNumberValue());
                    //截取2位小数
                    result = bigDecimal.setScale(2, BigDecimal.ROUND_HALF_UP).toString().trim();
                    break;
                case Cell.CELL_TYPE_BOOLEAN:
                    result = cell.getBooleanCellValue();
                    break;
                default:
                    break;
            }
        }
        return result;
    }
}