java下对excel文件的上传

来源：互联网发布：李世宏dota2 知乎编辑：程序博客网时间：2024/05/23 23:34
public class ExcelOperatorClass {    private static final String EXCEL2003L = ".xls";    private static final String EXCEL2007U = ".xlsx";    /**     * 描述：获取IO流中的数据，组装成List<List<Object>>对象     *     * @param fileName     * @return     * @throws Exception     */    public List<List<Object>> getSheetData(String fileName) throws Exception {        List<List<Object>> list = null;        //创建Excel工作薄        Workbook work = this.getWorkbook2007(fileName);        if (null == work) {            throw new Exception("创建Excel工作薄为空！");        }        Sheet sheet = null;        Row row = null;        Cell cell = null;        list = new ArrayList<List<Object>>();        //遍历Excel中所有的sheet        for (int i = 0; i < work.getNumberOfSheets(); i++) {            sheet = work.getSheetAt(i);            if (sheet == null) {                continue;            }            //遍历当前sheet中的所有行            for (int j = sheet.getFirstRowNum(); j <= sheet.getLastRowNum(); j++) {                row = sheet.getRow(j);                if (row == null || row.getFirstCellNum() == j) {                    continue;                }                //遍历所有的列                List<Object> li = new ArrayList<Object>();                for (int y = row.getFirstCellNum(); y < row.getLastCellNum(); y++) {                    cell = row.getCell(y);                    li.add(this.getCellValue(cell));                }                list.add(li);            }        }        work.close();        return list;    }    /**     * 描述：获取IO流中的数据，组装成List<List<Object>>对象     *     * @param in,fileName     * @return     * @throws Exception     */    public List<List<Object>> getSheetData(InputStream in, String fileName, int page) throws Exception {        List<List<Object>> list = null;        //创建Excel工作薄        Workbook work = this.getWorkbook(in, fileName);        if (null == work) {            throw new Exception("创建Excel工作薄为空！");        }        Sheet sheet = null;        Row row = null;        Cell cell = null;        list = new ArrayList<List<Object>>();        if (work.getNumberOfSheets() >= page) {            sheet = work.getSheetAt(page - 1);            //遍历当前sheet中的所有行            for (int j = sheet.getFirstRowNum(); j <= sheet.getLastRowNum(); j++) {                row = sheet.getRow(j);                if(row == null || row.getCell(0) == null                        || CommonUtils.isEmptyString(row.getCell(0).getStringCellValue())) {                    break;                }                //遍历所有的列                List<Object> li = new ArrayList<Object>();                for (int y = row.getFirstCellNum(); y < row.getLastCellNum(); y++) {                    cell = row.getCell(y);                    if(cell == null) {                    return null;                }                    li.add(this.getCellValue(cell));                }                list.add(li);            }        }        work.close();        return list;    }    /**     * 描述：根据文件后缀，自适应上传文件的版本     *     * @param inStr,fileName     * @return     * @throws Exception     */    public Workbook getWorkbook(InputStream inStr, String fileName) throws Exception {        Workbook wb = null;        String fileType = fileName.substring(fileName.lastIndexOf("."));        if (EXCEL2003L.equals(fileType)) {            wb = new HSSFWorkbook(inStr);  //2003-        } else if (EXCEL2007U.equals(fileType)) {            wb = new XSSFWorkbook(inStr);  //2007+        } else {            throw new Exception("解析的文件格式有误！");        }        return wb;    }    /**     * 描述：根据文件后缀，自适应上传文件的版本     *     * @param fileName     * @return     * @throws Exception     */    public Workbook getWorkbook2007(String fileName) throws Exception {        return new XSSFWorkbook(fileName);  //2007+;    }    /**     * 描述：对表格中数值进行格式化     *     * @param cell     * @return     */    public Object getCellValue(Cell cell) {        Object value = null;        DecimalFormat df = new DecimalFormat("0");  //格式化number String字符        SimpleDateFormat sdf = new SimpleDateFormat("yyy-MM-dd");  //日期格式化        DecimalFormat df2 = new DecimalFormat("0.00");  //格式化数字        switch (cell.getCellTypeEnum()) {            case STRING:                value = cell.getRichStringCellValue().getString();                break;            case NUMERIC:                if ("General".equals(cell.getCellStyle().getDataFormatString())) {                    value = df.format(cell.getNumericCellValue());                } else if ("m/d/yy".equals(cell.getCellStyle().getDataFormatString())) {                    value = sdf.format(cell.getDateCellValue());                } else {                    value = df2.format(cell.getNumericCellValue());                }                break;            case BOOLEAN:                value = cell.getBooleanCellValue();                break;            case BLANK:                value = "";                break;            default:                break;        }        return value;    }}
阅读全文
0 0