• 官方介紹

• 需要的jar包

• 具體代碼

public static void createWorkbook() throws IOException {        Workbook wb = new HSSFWorkbook();        String safeName1 = WorkbookUtil.createSafeSheetName("[O'sheet1]");        Sheet sheet1 = wb.createSheet(safeName1);        CreationHelper createHelper = wb.getCreationHelper();        // Create a row and put some cells in it. Rows are 0 based.        Row row = sheet1.createRow((short) 0);        // Create a cell and put a value in it.        Cell cell = row.createCell(0);        cell.setCellValue(1234);        // Or do it on one line.        row.createCell(2).setCellValue(                createHelper.createRichTextString("This is a string"));        row.createCell(3).setCellValue(true);        // we style the second cell as a date (and time). It is important to        // create a new cell style from the workbook otherwise you can end up        // modifying the built in style and effecting not only this cell but        // other cells.        CellStyle cellStyle = wb.createCellStyle();        cellStyle.setDataFormat(createHelper.createDataFormat().getFormat(                "m/d/yy h:mm"));        cell = row.createCell(1);        cell.setCellValue(new Date());        cell.setCellStyle(cellStyle);        // you can also set date as java.util.Calendar        CellStyle cellStyle1 = wb.createCellStyle();        cellStyle1.setDataFormat(createHelper.createDataFormat().getFormat(                "yyyyMMdd HH:mm:ss"));        cellStyle1.setBorderBottom(CellStyle.BORDER_THIN);        cellStyle1.setBottomBorderColor(IndexedColors.BLACK.getIndex());        cellStyle1.setBorderLeft(CellStyle.BORDER_THIN);        cellStyle1.setLeftBorderColor(IndexedColors.GREEN.getIndex());        cellStyle1.setBorderRight(CellStyle.BORDER_THIN);        cellStyle1.setRightBorderColor(IndexedColors.BLUE.getIndex());        cellStyle1.setBorderTop(CellStyle.BORDER_MEDIUM_DASHED);        cellStyle1.setTopBorderColor(IndexedColors.BLACK.getIndex());        cell = row.createCell(4);        cell.setCellValue(Calendar.getInstance());        cell.setCellStyle(cellStyle1);        FileOutputStream fileOut = new FileOutputStream("e:/test/workbook.xls");        wb.write(fileOut);        fileOut.close();    }

讀取excel文件的內容：

public static void readExcel() throws InvalidFormatException, IOException {        // Use a file        Workbook wb1 = WorkbookFactory.create(new File("e:/test/userinfo.xls"));        Sheet sheet = wb1.getSheetAt(0);        // Decide which rows to process        // int rowStart = Math.min(10, sheet.getFirstRowNum());        // int rowEnd = Math.max(40, sheet.getLastRowNum());        int rowStart = sheet.getLastRowNum();        int rowEnd = sheet.getLastRowNum() + 1;        logger.info(sheet.getFirstRowNum());        logger.info(sheet.getLastRowNum());        for (int rowNum = rowStart; rowNum < rowEnd; rowNum++) {            Row r = sheet.getRow(rowNum);            int lastColumn = Math.max(r.getLastCellNum(), 10);            logger.info(lastColumn);            // To get the contents of a cell, you first need to know what kind            // of cell it is (asking a string cell for its numeric contents will            // get you a NumberFormatException for example). So, you will want            // to switch on the cell's type, and then call the appropriate            // getter for that cell.            for (int cn = 0; cn < lastColumn; cn++) {                // Cell cell = r.getCell(cn, Row.RETURN_BLANK_AS_NULL);                Cell cell = r.getCell(cn);                switch (cell.getCellType()) {                case Cell.CELL_TYPE_STRING:                    logger.info(cell.getRichStringCellValue().getString());                    break;                case Cell.CELL_TYPE_NUMERIC:                    if (DateUtil.isCellDateFormatted(cell)) {                        logger.info(cell.getDateCellValue());                    } else {                        logger.info(cell.getNumericCellValue());                    }                    break;                case Cell.CELL_TYPE_BOOLEAN:                    logger.info(cell.getBooleanCellValue());                    break;                case Cell.CELL_TYPE_FORMULA:                    logger.info(cell.getCellFormula());                    break;                default:                    logger.info("empty");                }            }        }    }

下面給出一個具體的例子，實例中的excel文件內容如下：

public static void readUserInfo() throws InvalidFormatException,            IOException {        String[] titles = { "收費編號", "收費性質", "姓名", "家庭住址", "工作單位", "電話", "手機",                "小區樓號", "單元號", "樓層", "房間號", "建筑面積（㎡）", "面積依據", "A面積", "A超",                "A輕體", "B面積", "B超", "B輕體", "用戶編號", "所屬樓前表表號" };                //用來存儲標題和順序的map，key為標題，value為順序號        Map<String, Integer> titleMap = new HashMap<String, Integer>();        //將既定順序寫入map        for (int i=0 ; i<titles.length; i++) {            titleMap.put(titles[i], i);        }        Workbook wb = WorkbookFactory.create(new File("e:/test/userinfo.xls"));        for (int numSheet = 0; numSheet < wb.getNumberOfSheets(); numSheet++) {            Sheet xSheet = wb.getSheetAt(numSheet);            if (xSheet == null) {                continue;            }            // 獲取第一行的標題內容            Row tRow = xSheet.getRow(0);            //存儲標題順序的數組            Integer[] titleSort = new Integer[tRow.getLastCellNum()];                        //循環標題            for (int titleNum = 0; titleNum < tRow.getLastCellNum(); titleNum++) {                Cell tCell = tRow.getCell(titleNum);                String title = "";                if (tCell == null || "".equals(tCell)) {                } else if (tCell.getCellType() == XSSFCell.CELL_TYPE_BOOLEAN) {// 布爾類型處理                    // logger.info(xCell.getBooleanCellValue());                } else if (tCell.getCellType() == XSSFCell.CELL_TYPE_NUMERIC) {// 數值類型處理                    title = doubleToString(tCell.getNumericCellValue());                } else {// 其他類型處理                    title = tCell.getStringCellValue();                }                //通過獲取的標題，從map中讀取順訊號，寫入保存標題順序號的數組                Integer ts = titleMap.get(title);                if (ts != null) {                    titleSort[titleNum] = ts;                }            }            // 循環行Row            for (int rowNum = 1; rowNum < xSheet.getLastRowNum() + 1; rowNum++) {                Row xRow = xSheet.getRow(rowNum);                if (xRow == null) {                    continue;                }                // 循環列Cell                String[] v = new String[titleSort.length];                for (int cellNum = 0; cellNum < titleSort.length; cellNum++) {                    Cell xCell = xRow.getCell(cellNum);                    String value = "";                    if (xCell == null || "".equals(xCell)) {                    } else if (xCell.getCellType() == XSSFCell.CELL_TYPE_BOOLEAN) {// 布爾類型處理                     logger.info(xCell.getBooleanCellValue());                    } else if (xCell.getCellType() == XSSFCell.CELL_TYPE_NUMERIC) {// 數值類型處理                        value = doubleToString(xCell.getNumericCellValue());                    } else {// 其他類型處理                        value = xCell.getStringCellValue();                    }                                        //按照標題順序的編號來存儲每一行記錄                    v[titleSort[cellNum]] = value;//                    logger.info("v[" + titleSort[cellNum] + "] = " + v[titleSort[cellNum]]);                }                //循環結果數組，獲取的與既定順序相同                for (int i = 0; i < v.length; i++) {                    logger.info(v[i]);                }            }        }    }

上段程序中用到的工具類doubleToString（將excel中的double類型轉為String類型，處理了科學計數法形式的數）：

private static String doubleToString(double d) {        String str = Double.valueOf(d).toString();        // System.out.println(str);        String result = "";        if (str.indexOf("E") > 2) {            int index = str.indexOf("E");            int power = Integer.parseInt(str.substring(index + 1));            BigDecimal value = new BigDecimal(str.substring(0, index));            value = value.movePointRight(power);            result = value.toString();        } else {            if (str.indexOf(".0") > 0)                result = str.substring(0, str.indexOf(".0"));            else                result = str;        }        return result;    }

目前對于POI的應用只限于此，并沒有再深入，以后寫了新的相關內容會繼續補充，請大大們批評指正！

轉載請注明出處：http://m.survivalescaperooms.com/bxljoy/p/3939409.html