qileilove
          

          blog已經(jīng)轉(zhuǎn)移至github,大家請(qǐng)?jiān)L問(wèn) 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包中，需要單獨(dú)下載，否則程序會(huì)拋出異常：java.lang.ClassNotFoundException:org.apache.xmlbeans.XmlOptions。
          　　具體代碼
          　　在Eclipse中創(chuàng)建一個(gè)java project，將上面列出來(lái)的jar包都加入到工程的classpath中，否則引用不到j(luò)ar包會(huì)報(bào)錯(cuò)。
          　　直接上代碼（代碼基本框架來(lái)自Apache POI官方網(wǎng)站，自行調(diào)整部分）：
          　　創(chuàng)建excel文件并寫(xiě)入內(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)題的順序來(lái)讀取每一行文件的內(nèi)容，實(shí)際標(biāo)題和內(nèi)容的順序是不確定的，但是我們要求按照給定的順序輸出文件內(nèi)容。
          　　代碼如下：
          public static void readUserInfo() throws InvalidFormatException,
          IOException {
          String[] titles = { "收費(fèi)編號(hào)", "收費(fèi)性質(zhì)", "姓名", "家庭住址", "工作單位", "電話", "手機(jī)",
          "小區(qū)樓號(hào)", "單元號(hào)", "樓層", "房間號(hào)", "建筑面積（㎡）", "面積依據(jù)", "A面積", "A超",
          "A輕體", "B面積", "B超", "B輕體", "用戶編號(hào)", "所屬樓前表表號(hào)" };
          //用來(lái)存儲(chǔ)標(biāo)題和順序的map，key為標(biāo)題，value為順序號(hào)
          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);
          //存儲(chǔ)標(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();
          }
          //通過(guò)獲取的標(biāo)題，從map中讀取順訊號(hào)，寫(xiě)入保存標(biāo)題順序號(hà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)題順序的編號(hào)來(lái)存儲(chǔ)每一行記錄
          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é)計(jì)數(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;
          }
          　　目前對(duì)于POI的應(yīng)用只限于此，并沒(méi)有再深入，以后寫(xiě)了新的相關(guān)內(nèi)容會(huì)繼續(xù)補(bǔ)充，請(qǐng)大大們批評(píng)指正！
          
		
          
			posted on 2014-09-02 09:50 順其自然EVO 閱讀(271) 評(píng)論(0)  編輯  收藏  所屬分類: 測(cè)試學(xué)習(xí)專欄 
		
          
	
          
	
	


	


          
	    
    




          








          

				
			
          
			
          
				
					
          
	
          
		
          <2014年9月>
          
	
          日一二三四五六
          31123456
          78910111213
          14151617181920
          21222324252627
          2829301234
          567891011
          

          

					

          導(dǎo)航
          

					

          統(tǒng)計(jì)
          
	
					
					

          常用鏈接
          


          留言簿(55)
          


		
          隨筆分類
          
		
				
			
	
		
          隨筆檔案
          
		
				
			
	
		
          文章分類
          
		
				
			
	
		
          文章檔案
          
		
				
			
	



          搜索
          



          最新評(píng)論
	
          



          閱讀排行榜
          



          評(píng)論排行榜
          


				
			
          			
			

	

    







          
        
	




主站蜘蛛池模板：
商河县|
霸州市|
万源市|
攀枝花市|
佛山市|
河间市|
双峰县|
汉寿县|
乐东|
塔河县|
平远县|
沾益县|
台安县|
广丰县|
马山县|
虞城县|
深圳市|
青阳县|
东乡县|
金溪县|
上栗县|
栾城县|
同仁县|
遂宁市|
睢宁县|
潼关县|
临颍县|
满洲里市|
深水埗区|
灵石县|
伊川县|
且末县|
唐河县|
乌鲁木齐市|
屏东县|
淳安县|
犍为县|
砚山县|
麻栗坡县|
南和县|
江阴市|