java关于excel读取表单

来源：互联网发布：大智慧行情分析软件编辑：程序博客网时间：2024/05/22 03:13

第一步：在maven中导入poi相关的jar

<dependency><groupId>org.apache.poi</groupId>    <artifactId>poi-ooxml</artifactId>    <version>3.9</version></dependency><dependency>    <groupId>org.apache.poi</groupId>    <artifactId>poi</artifactId>    <version>3.9</version></dependency><dependency>    <groupId>org.apache.poi</groupId>    <artifactId>poi-ooxml-schemas</artifactId>    <version>3.9</version></dependency><!-- 支持Word文档的操作 --><dependency>    <groupId>org.apache.poi</groupId>    <artifactId>poi-scratchpad</artifactId>    <version>3.9</version></dependency>

第二步：

import org.apache.poi.hssf.usermodel.HSSFWorkbook;import org.apache.poi.ss.usermodel.*;import org.apache.poi.xssf.usermodel.XSSFWorkbook;import org.slf4j.Logger;import org.slf4j.LoggerFactory;import java.io.FileInputStream;import java.io.FileNotFoundException;import java.io.IOException;import java.io.InputStream;import java.util.*;/** * Created by fanjunwei on 2017/2/14. */public class excel{    private static Logger logger = LoggerFactory.getLogger(World1.class);    private static Workbook wb;    private static Sheet sheet;    private static Row row;    /**     * 读取Excel表格表头的内容     *     * @param     * @return String 表头内容的数组     * @author zengwendong     */    public static String[] readExcelTitle(Workbook wb) 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] = getStringCellValue(row.getCell((short) i));            title[i] = row.getCell(i).getCellFormula();        }        return title;    }    /**     * 读取Excel数据内容     *     * @param     * @return Map 包含单元格数据内容的Map对象     * @author zengwendong     */    public static List<Map> readExcelContent(Workbook wb) throws Exception{        if(wb==null){            throw new Exception("Workbook对象为空！");        }       List<Map> content = new ArrayList<Map>();        sheet = wb.getSheetAt(0);        // 得到总行数        int rowNum = sheet.getLastRowNum();        row = sheet.getRow(0);        int colNum = row.getPhysicalNumberOfCells();        // 正文内容应该从第二行开始,第一行为表头的标题        for (int i = 1; i <= rowNum; i++) {            row = sheet.getRow(i);            int j = 0;            Map<String,Object> cellValue = new HashMap<String, Object>();            while (j < colNum) {                Object obj = getCellFormatValue(row.getCell(j));                             cellValue.put(j, obj);                j++;            }            content.add(cellValue);        }        return content;    }    /**     *     * 根据Cell类型设置数据     *     * @param cell     * @return     * @author zengwendong     */    private static Object getCellFormatValue(Cell cell) {        Object cellvalue = "";        if (cell != null) {            // 判断当前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;    }    /**     * 根据路径解析文档     * @param url     */    public static List excel(String url){    String ext = url.substring(url.lastIndexOf("."));    try {        InputStream is = new FileInputStream(url);        if(".xls".equals(ext)){            wb = new HSSFWorkbook(is);        }else if(".xlsx".equals(ext)){            wb = new XSSFWorkbook(is);        }else{            wb=null;        }       return  World1.readExcelContent(wb);    } catch (FileNotFoundException e) {        logger.error("FileNotFoundException", e);        return null;    } catch (IOException e) {        logger.error("IOException", e);        return null;    }catch (Exception e){        logger.error("Exception", e);        return null;    }}}

0 0