近期处理的数据规模比较大,正好又是统计合并的事情,想着借助excel就可以完成了,然后就了解了下java读取excel的事情。
读取的文件主要分两类:xls文件、xlsx文件。xls文件的相关操作用的是jxl.jar包,只要将这个包导入即可。xlsx文件的相关操作是利用apache的poi包。
一、xls文件(一个jar包:jxl.jar)
1)创建
package jexcel; import java.io.*; import jxl.*; import jxl.write.*; /** * @author Ken * * To change the template for this generated type comment go to * Window>Preferences>Java>Code Generation>Code and Comments */ public class CreateXLS { public static void main(String[] args) { try { //open file. WritableWorkbook book = Workbook.createWorkbook(new File("d:/Test.xls")); //create Sheet named "Sheet_1". 0 means this is 1st page. WritableSheet sheet = book.createSheet("Sheet_1", 0); //define cell column and row in Label Constructor, and cell content write "test". //cell is 1st-Column,1st-Row. value is "test". Label label = new Label(0, 0, "test"); //add defined cell above to sheet instance. sheet.addCell(label); //create cell using add numeric. WARN:necessarily use integrated package-path, otherwise will be throws path-error. //cell is 2nd-Column, 1st-Row. value is 789.123. jxl.write.Number number = new jxl.write.Number(1, 0, 789.123); //add defined cell above to sheet instance. sheet.addCell(number); //add defined all cell above to case. book.write(); //close file case. book.close(); } catch (Exception e) { e.printStackTrace(); } } }
2)读取
package jexcel; import java.io.*; import jxl.*; /** * @author Ken * * To change the template for this generated type comment go to * Window>Preferences>Java>Code Generation>Code and Comments */ public class ReadXLS { public static void main(String[] args) { try { Workbook book = Workbook.getWorkbook(new File("e:/pos/rule.xls")); //get a Sheet object. Sheet sheet = book.getSheet(0); //get 1st-Column,1st-Row content. // Cell cell = sheet.getCell(1, 2); //先是列序号,然后是行序号,都是从0开始算起 Cell[] cell=sheet.getColumn(0); for(int i=0;i<cell.length;i++){ String result = cell[i].getContents(); System.out.println(result); } book.close(); } catch (Exception e) { e.printStackTrace(); } } }
3)修改
package jexcel; import java.io.*; import jxl.*; import jxl.write.*; /** * @author Ken * * To change the template for this generated type comment go to * Window>Preferences>Java>Code Generation>Code and Comments */ public class UpdateXLS { public static void main(String[] args) { try { //get file. Workbook wb = Workbook.getWorkbook(new File("d:/Test.xls")); //open a copy file(new file), then write content with same content with Test.xls. WritableWorkbook book = Workbook.createWorkbook(new File("d:/Test.xls"), wb); //add a Sheet. WritableSheet sheet = book.createSheet("Sheet_2", 1); sheet.addCell(new Label(0, 0, "test2")); book.write(); book.close(); } catch (Exception e) { e.printStackTrace(); } } }
二、xlsx文件(五个jar包:poi-3.8-20120326.jar; poi-ooxml-3.8-20120326.jar; poi-ooxml-schemas-3.8-20120326.jar; xmlbeans-2.3.0.jar; dom4j-1.6.1.jar)
import java.io.File; import java.io.FileInputStream; import java.io.IOException; import java.io.InputStream; import java.sql.Date; import java.text.SimpleDateFormat; import java.util.TimeZone; import java.util.logging.Logger; import org.apache.poi.xssf.usermodel.XSSFCell; import org.apache.poi.xssf.usermodel.XSSFRow; import org.apache.poi.xssf.usermodel.XSSFSheet; import org.apache.poi.xssf.usermodel.XSSFWorkbook; public class xlsx { public static void main(String[] args) { // TODO Auto-generated method stub poiExcel("e:/pos/rules.xlsx"); } private static void poiExcel(String saveName){ try{ String realPath =saveName; File fileDes = new File(realPath); InputStream str = new FileInputStream(fileDes); XSSFWorkbook xwb = new XSSFWorkbook(str); //利用poi读取excel文件流 XSSFSheet st = xwb.getSheetAt(0); //读取sheet的第一个工作表 int rows=st.getLastRowNum();//总行数 int cols;//总列数 //log.info("========行========"+rows); for(int i=0;i<rows;i++){ XSSFRow row=st.getRow(i);//读取某一行数据 if(row!=null){ //获取行中所有列数据 cols=row.getLastCellNum(); //log.info("========行========"+rows+"=====列========"+cols); for(int j=0;j<cols;j++){ XSSFCell cell=row.getCell(j); if(cell==null){ System.out.print(" "); }else{ //判断单元格的数据类型 switch (cell.getCellType()) { case XSSFCell.CELL_TYPE_NUMERIC: // 数字 System.out.print(cell.getNumericCellValue() + " "); break; case XSSFCell.CELL_TYPE_STRING: // 字符串 System.out.print(cell.getStringCellValue() + " "); break; case XSSFCell.CELL_TYPE_BOOLEAN: // Boolean System.out.println(cell.getBooleanCellValue() + " "); break; case XSSFCell.CELL_TYPE_FORMULA: // 公式 System.out.print(cell.getCellFormula() + " "); break; case XSSFCell.CELL_TYPE_BLANK: // 空值 System.out.println(""); break; case XSSFCell.CELL_TYPE_ERROR: // 故障 System.out.println("故障"); break; default: System.out.print("未知类型 "); break; } } } } } }catch(IOException e){ e.printStackTrace(); } } }
代码全是网上贴来的,大家一起分享。。
想把jar包也贴上来的,但是不知道是不支持还是我自己摸索不出来,反正最后是没贴出来。。。