com.ncc.excel.ExcelUtil.java Source code

Introduction

Here is the source code for com.ncc.excel.ExcelUtil.java

Source

/*
 * Copyright 2002-2013 the original author or authors.
 *
 * Licensed under the Apache License, Version 2.0 (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at
 *
 *      http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 */

package com.ncc.excel;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.text.DecimalFormat;
import java.util.List;

import org.apache.log4j.Logger;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
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 com.ncc.util.date.DateUtil;

/**
 *  
 * @Title ExcelUtil.java
 * @Package com.ncc.excel
 * @author 
 * @Email fdtomn@gmail.com 
 * @date 2015-3-16 ?3:48:17
 * @Copyright Copyright (c) 2015-2025
 * @Company ???
 * @version V1.0  
 */
public class ExcelUtil {

    //#####################  ?   ##############
    private static final int VERSION = 0;

    private static final int VERSION2003 = 2003;

    private static final int VERSION2007 = 2007;

    /** 
     * ??sheet 
     */
    private final static boolean ONLY_ONE_SHEET = true;

    /** 
     * ?sheet?ONLY_ONE_SHEET = true 
     */
    private final static int SELECTED_SHEET = 0;

    //#####################  ??   ##############

    /** 
     * ???sheet 
     */
    private boolean onlyReadOneSheet = ONLY_ONE_SHEET;

    /** 
     * ?sheet 
     */
    private int selectedSheetIdx = SELECTED_SHEET;

    /** 
     * ?sheet?? 
     */
    private String selectedSheetName = "";

    private static Workbook wb = null;

    public static Logger logger = Logger.getLogger(ExcelUtil.class);

    public boolean isOnlyReadOneSheet() {
        return onlyReadOneSheet;
    }

    public void setOnlyReadOneSheet(boolean onlyReadOneSheet) {
        this.onlyReadOneSheet = onlyReadOneSheet;
    }

    public int getSelectedSheetIdx() {
        return selectedSheetIdx;
    }

    public void setSelectedSheetIdx(int selectedSheetIdx) {
        this.selectedSheetIdx = selectedSheetIdx;
    }

    public String getSelectedSheetName() {
        return selectedSheetName;
    }

    public void setSelectedSheetName(String selectedSheetName) {
        this.selectedSheetName = selectedSheetName;
    }

    public List<Row> readExcel(String excelPath) {
        wb = createWorkbook(excelPath);
        readExcel(wb);
        return null;
    }

    public List<Row> readExcel(Workbook wb) {
        Sheet sheet = null;

        if (onlyReadOneSheet) {//??sheet

            System.out.println("selectedSheetName:" + selectedSheetName);
            // ??sheet(?????)  
            sheet = selectedSheetName.equals("") ? wb.getSheetAt(selectedSheetIdx) : wb.getSheet(selectedSheetName);
            System.out.println(sheet.getSheetName());

        } else {
            for (int i = 0; i < wb.getNumberOfSheets(); i++) {//??Sheet

                sheet = wb.getSheetAt(i);
                logger.info(sheet.getSheetName());

                for (int j = 0; j < sheet.getPhysicalNumberOfRows(); j++) {//??
                    Row row = sheet.getRow(j);
                    for (int k = 0; k < row.getPhysicalNumberOfCells(); k++) {//???
                        System.out.print(row.getCell(k) + "\t");
                    }
                    System.out.println("---Sheet" + i + "?---");
                }
            }
        }
        return null;
    }

    //????
    public static String getCellValue(Cell cell) {
        //        private String getCellValue(Cell cell){
        String str = null;
        if (cell != null) {
            switch (cell.getCellType()) {
            case Cell.CELL_TYPE_NUMERIC:
                //0 
                //?? 1.  2.
                if (HSSFDateUtil.isCellDateFormatted(cell)) {
                    //??
                    str = DateUtil.formatDateForExcelDate(cell.getDateCellValue());
                } else {
                    double dValue = cell.getNumericCellValue();
                    //E
                    if (String.valueOf(dValue).contains("E")) {
                        str = new DecimalFormat("#").format(dValue);
                    } else {
                        str = String.valueOf(dValue);
                    }
                }
                break;
            case Cell.CELL_TYPE_STRING:
                //1
                str = cell.getStringCellValue();
                break;
            case Cell.CELL_TYPE_FORMULA:
                //2 ?
                str = String.valueOf(cell.getNumericCellValue());
                break;
            case Cell.CELL_TYPE_BLANK:
                //3 
                str = "";
                break;
            case Cell.CELL_TYPE_BOOLEAN:
                //4 
                str = String.valueOf(cell.getBooleanCellValue());
                break;
            case Cell.CELL_TYPE_ERROR:
                //5 
                str = "";
                break;
            default:
                str = null;
                break;
            }
        }
        return str;
    }

    /**
     * Excel
     * @param fileName
     * @return
     */
    private static int checkExcelVersion(String fileName) {
        //2007
        if (fileName.matches("^.+\\.(?i)(xlsx)$")) {
            return VERSION2007;
        } else if (fileName.matches("^.+\\.(?i)(xls)$")) { //2003
            return VERSION2003;
        } else { // ???
            return VERSION;
        }
    }

    /**
     * Workbook
     * @param filePath
     * @return
     */
    private static Workbook createWorkbook(String excelPath) {

        File file = new File(excelPath);
        InputStream is = null;
        try {
            is = new FileInputStream(file);
        } catch (FileNotFoundException e) {
            logger.error(e.getMessage());
        }

        if (checkExcelVersion(excelPath) == VERSION2003) {
            logger.info(">>>>>>>>2003");
            try {
                wb = new HSSFWorkbook(is);
            } catch (IOException e) {
                e.printStackTrace();
            }
        } else if (checkExcelVersion(excelPath) == VERSION2007) {
            logger.info(">>>>>>>>2007");
            try {
                wb = new XSSFWorkbook(is);
            } catch (IOException e) {
                e.printStackTrace();
            }
        } else {
            logger.error("Excel........");
        }
        return wb;
    }

    public static void main(String[] args) {
        //      String excelPath = "D:/poi/2003.xls";
        String excelPath = "D:/poi/2007.xlsx";
        ExcelUtil eu = new ExcelUtil();

        eu.setSelectedSheetName("aa");

        eu.readExcel(excelPath);
    }

}