qileilove

          blog已經(jīng)轉(zhuǎn)移至github,大家請訪問 http://qaseven.github.io/

          Java使用Apache POI操作excel文件

          官方介紹
            HSSF is the POI Project's pure Java implementation of the Excel '97(-2007) file format. XSSF is the POI Project's pure Java implementation of the Excel 2007 OOXML (.xlsx) file format.
            從官方文檔中了解到:POI提供的HSSF包用于操作 Excel '97(-2007)的.xls文件,而XSSF包則用于操作Excel2007之后的.xslx文件。
            需要的jar包
            POI官網(wǎng)上下載包并解壓獲取java操作excel文件必須的jar包:
            其中dom4j-1.6.1.jar和xbean.jar(下載地址:http://mirror.bjtu.edu.cn/apache/xmlbeans/binaries/   網(wǎng)站:http://xmlbeans.apache.org
            并不包含在POI提供的jar包中,需要單獨下載,否則程序會拋出異常:java.lang.ClassNotFoundException:org.apache.xmlbeans.XmlOptions。
            具體代碼
            在Eclipse中創(chuàng)建一個java project,將上面列出來的jar包都加入到工程的classpath中,否則引用不到j(luò)ar包會報錯。
            直接上代碼(代碼基本框架來自Apache POI官方網(wǎng)站,自行調(diào)整部分):
            創(chuàng)建excel文件并寫入內(nèi)容:
          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文件的內(nèi)容:
          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");
          }
          }
          }
          }
            我們的程序要做的事情是:根據(jù)第一行標(biāo)題的順序來讀取每一行文件的內(nèi)容,實際標(biāo)題和內(nèi)容的順序是不確定的,但是我們要求按照給定的順序輸出文件內(nèi)容。
            代碼如下:
          public static void readUserInfo() throws InvalidFormatException,
          IOException {
          String[] titles = { "收費編號", "收費性質(zhì)", "姓名", "家庭住址", "工作單位", "電話", "手機",
          "小區(qū)樓號", "單元號", "樓層", "房間號", "建筑面積(㎡)", "面積依據(jù)", "A面積", "A超",
          "A輕體", "B面積", "B超", "B輕體", "用戶編號", "所屬樓前表表號" };
          //用來存儲標(biāo)題和順序的map,key為標(biāo)題,value為順序號
          Map<String, Integer> titleMap = new HashMap<String, Integer>();
          //將既定順序?qū)懭雖ap
          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;
          }
          // 獲取第一行的標(biāo)題內(nèi)容
          Row tRow = xSheet.getRow(0);
          //存儲標(biāo)題順序的數(shù)組
          Integer[] titleSort = new Integer[tRow.getLastCellNum()];
          //循環(huán)標(biāo)題
          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) {// 數(shù)值類型處理
          title = doubleToString(tCell.getNumericCellValue());
          } else {// 其他類型處理
          title = tCell.getStringCellValue();
          }
          //通過獲取的標(biāo)題,從map中讀取順訊號,寫入保存標(biāo)題順序號的數(shù)組
          Integer ts = titleMap.get(title);
          if (ts != null) {
          titleSort[titleNum] = ts;
          }
          }
          // 循環(huán)行Row
          for (int rowNum = 1; rowNum < xSheet.getLastRowNum() + 1; rowNum++) {
          Row xRow = xSheet.getRow(rowNum);
          if (xRow == null) {
          continue;
          }
          // 循環(huán)列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) {// 數(shù)值類型處理
          value = doubleToString(xCell.getNumericCellValue());
          } else {// 其他類型處理
          value = xCell.getStringCellValue();
          }
          //按照標(biāo)題順序的編號來存儲每一行記錄
          v[titleSort[cellNum]] = value;
          //                    logger.info("v[" + titleSort[cellNum] + "] = " + v[titleSort[cellNum]]);
          }
          //循環(huán)結(jié)果數(shù)組,獲取的與既定順序相同
          for (int i = 0; i < v.length; i++) {
          logger.info(v[i]);
          }
          }
          }
          }
            上段程序中用到的工具類doubleToString(將excel中的double類型轉(zhuǎn)為String類型,處理了科學(xué)計數(shù)法形式的數(shù)):
          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的應(yīng)用只限于此,并沒有再深入,以后寫了新的相關(guān)內(nèi)容會繼續(xù)補充,請大大們批評指正!

          posted on 2014-09-02 09:50 順其自然EVO 閱讀(271) 評論(0)  編輯  收藏 所屬分類: 測試學(xué)習(xí)專欄

          <2014年9月>
          31123456
          78910111213
          14151617181920
          21222324252627
          2829301234
          567891011

          導(dǎo)航

          統(tǒng)計

          常用鏈接

          留言簿(55)

          隨筆分類

          隨筆檔案

          文章分類

          文章檔案

          搜索

          最新評論

          閱讀排行榜

          評論排行榜

          主站蜘蛛池模板: 兴仁县| 萨嘎县| 辽宁省| 巴林左旗| 安远县| 兴山县| 西青区| 余庆县| 古交市| 柘城县| 若尔盖县| 东港市| 宜丰县| 长寿区| 巴楚县| 肃南| 明光市| 信丰县| 汾阳市| 五寨县| 青河县| 延川县| 万源市| 万年县| 汪清县| 山东| 雅安市| 石楼县| 武隆县| 白沙| 永城市| 准格尔旗| 岗巴县| 禄劝| 太湖县| 淅川县| 大英县| 陈巴尔虎旗| 班玛县| 秀山| 乡宁县|