java 解析excel工具类

CreateTime--2018年3月5日16:48:08

Author:Marydon

　　ReadExcelUtils.java

import java.io.FileInputStream;import java.io.FileNotFoundException;import java.io.IOException;import java.io.InputStream;import java.util.ArrayList;import java.util.Date;import java.util.HashMap;import java.util.List;import java.util.Map;import org.apache.log4j.Logger;import org.apache.poi.hssf.usermodel.HSSFWorkbook;import org.apache.poi.ss.usermodel.Cell;import org.apache.poi.ss.usermodel.DateUtil;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;/** * excel解析工具类 * @author Marydon * @createTime 2018年2月1日下午12:54:09 * @updateTime * @Email:Marydon20170307@163.com * @version:1.0.0 */public class ReadExcelUtils {    private Logger logger = Logger.getLogger(this.getClass());    private Workbook wb;    private Sheet sheet;    private Row row;    public ReadExcelUtils(String filepath) {        if (filepath == null) {            return;        }        String fileType = filepath.substring(filepath.lastIndexOf("."));        try {            InputStream is = new FileInputStream(filepath);            if (".xls".equals(fileType)) {                wb = new HSSFWorkbook(is);            } else if (".xlsx".equals(fileType)) {                wb = new XSSFWorkbook(is);            } else {                wb = null;            }        } catch (FileNotFoundException e) {            logger.error("FileNotFoundException", e);        } catch (IOException e) {            logger.error("IOException", e);        }    }    /**     * 读取Excel表格表头的内容     *      * @param InputStream     * @return String 表头内容的数组     * @author zengwendong     */    public String[] readExcelTitle() throws Exception {        if (wb == null) {            throw new Exception("Workbook对象为空！");        }        sheet = wb.getSheetAt(0);        row = sheet.getRow(0);        // 标题总列数        int colNum = row.getPhysicalNumberOfCells();        System.out.println("colNum:" + colNum);        String[] title = new String[colNum];        for (int i = 0; i < colNum; i++) {            title[i] = row.getCell(i).getCellFormula();        }        return title;    }    /**     * 读取Excel数据内容     * @description     * @return 包含单元格数据内容的List对象     * @throws Exception     */    public List readExcelContent(List
       
         columnsList) throws Exception {        if (wb == null) {            throw new Exception("Workbook对象为空！");        }        //        List
         content = new ArrayList();        sheet = wb.getSheetAt(0);        // 得到总行数        int rowNum = sheet.getLastRowNum();        row = sheet.getRow(0);        int colNum = row.getPhysicalNumberOfCells();        //        Map
          
            cellValue = null;        //         if (null == columnsList || columnsList.size() != colNum || columnsList.isEmpty()) {            // 正文内容应该从第二行开始,第一行为表头的标题            for (int i = 1; i <= rowNum; i++) {                // 获取当前行                row = sheet.getRow(i);                int j = 0;                cellValue = new HashMap
           
            (); while (j < colNum) { Object obj = getCellFormatValue(row.getCell(j)); cellValue.put(String.valueOf(j), obj); j++; } content.add(cellValue); } } else { // 正文内容应该从第二行开始,第一行为表头的标题 for (int i = 1; i <= rowNum; i++) { // 获取当前行 row = sheet.getRow(i); int j = 0; cellValue = new HashMap
            
             (); while (j < colNum) { Object obj = getCellFormatValue(row.getCell(j)); cellValue.put(columnsList.get(j), obj); j++; } content.add(cellValue); } } return content; } /** * * 根据Cell类型设置数据 * * @param cell * @return * @author zengwendong */ private Object getCellFormatValue(Cell cell) { Object cellvalue = ""; // 非空 if (null != cell && (!"".equals(cell.toString()))) { // 判断当前Cell的Type switch (cell.getCellType()) { case Cell.CELL_TYPE_NUMERIC:// 如果当前Cell的Type为NUMERIC case Cell.CELL_TYPE_FORMULA: { // 判断当前的cell是否为Date if (DateUtil.isCellDateFormatted(cell)) { // 如果是Date类型则，转化为Data格式 // data格式是带时分秒的：2013-7-10 0:00:00 // cellvalue = cell.getDateCellValue().toLocaleString(); // data格式是不带带时分秒的：2013-7-10 Date date = cell.getDateCellValue(); cellvalue = date; } else { // 如果是纯数字 // 取得当前Cell的数值 cellvalue = String.valueOf(cell.getNumericCellValue()); } break; } case Cell.CELL_TYPE_STRING:// 如果当前Cell的Type为STRING // 取得当前的Cell字符串 cellvalue = cell.getRichStringCellValue().getString(); break; default:// 默认的Cell值 cellvalue = ""; } } else { cellvalue = ""; } return cellvalue; }}

　　测试

public static void main(String[] args) {    try {        String filepath = "C:\\Users\\Marydon\\Desktop\\值班名单汇总 (20170714).xlsx";        ReadExcelUtils excelReader = new ReadExcelUtils(filepath);        // 说明：excel的列的所代表的字段的顺序必须与list集合的字段名一致（excel的列名和list的元素名没必要相同）        List
      
        columnsList = new ArrayList
       
        ();        columnsList.add("ORG_ID");        columnsList.add("DEPENT_ID");        columnsList.add("DEPENT_NAME");        columnsList.add("DOCTOR_ID");        columnsList.add("DOCTOR_NAME");        columnsList.add("DOCTOR_PHONE");        columnsList.add("SCHEDULE_DATE");        columnsList.add("WEEK_TXT");        columnsList.add("WB_TYPE");        columnsList.add("CLOSE_TZ");        columnsList.add("REPLACE_TZ");        columnsList.add("DOCTOR_ID_TZ");        columnsList.add("DOCTOR_NAME_TZ");        columnsList.add("REMARK");                // 对读取Excel表格内容测试        List
         list = excelReader.readExcelContent(columnsList);        System.out.println("获得Excel表格的内容:");        for (int i = 0; i < list.size(); i++) {            System.out.println(list.get(i));        }    } catch (FileNotFoundException e) {        System.out.println("未找到指定路径的文件!");        e.printStackTrace();    } catch (Exception e) {        e.printStackTrace();    }}

　　前端+后台完整版，请移步至文章：

转载地址：http://ekvta.baihongyu.com/

你可能感兴趣的文章

Android SharedPreferences