1. java对Excel解析(求助)
这篇blog主要是讲述java中poi读取excel，而excel的版本包括：2003-2007和2010两个版本， 即excel的后缀名为：xls和xlsx。
读取excel和MySQL相关： java的poi技术读取Excel数据到MySQL
代码如下
/****/packagecom.b510.common;/***@authorHongten*@created2014-5-21*/publicclassCommon{publicstaticfinalStringOFFICE_EXCEL_2003_POSTFIX="xls";publicstaticfinalStringOFFICE_EXCEL_2010_POSTFIX="xlsx";publicstaticfinalStringEMPTY="";publicstaticfinalStringPOINT=".";publicstaticfinalStringLIB_PATH="lib";_INFO_XLS_PATH=LIB_PATH+"/student_info"+POINT+OFFICE_EXCEL_2003_POSTFIX;_INFO_XLSX_PATH=LIB_PATH+"/student_info"+POINT+OFFICE_EXCEL_2010_POSTFIX;publicstaticfinalStringNOT_EXCEL_FILE=":NottheExcelfile!";="Processing…";}/****/packagecom.b510.excel;importjava.io.FileInputStream;importjava.io.IOException;importjava.io.InputStream;importjava.util.ArrayList;importjava.util.List;importorg.apache.poi.hssf.usermodel.HSSFCell;importorg.apache.poi.hssf.usermodel.HSSFRow;importorg.apache.poi.hssf.usermodel.HSSFSheet;importorg.apache.poi.hssf.usermodel.HSSFWorkbook;importorg.apache.poi.xssf.usermodel.XSSFCell;importorg.apache.poi.xssf.usermodel.XSSFRow;importorg.apache.poi.xssf.usermodel.XSSFSheet;importorg.apache.poi.xssf.usermodel.XSSFWorkbook;importcom.b510.common.Common;importcom.b510.excel.util.Util;importcom.b510.excel.vo.Student;/***@authorHongten*@created2014-5-20*/publicclassReadExcel{/***readtheExcelfile*@*@return*@throwsIOException*/publicList<Student>readExcel(Stringpath)throwsIOException{if(path==null||Common.EMPTY.equals(path)){returnnull;}else{Stringpostfix=Util.getPostfix(path);if(!Common.EMPTY.equals(postfix)){if(Common.OFFICE_EXCEL_2003_POSTFIX.equals(postfix)){returnreadXls(path);}elseif(Common.OFFICE_EXCEL_2010_POSTFIX.equals(postfix)){returnreadXlsx(path);}}else{System.out.println(path+Common.NOT_EXCEL_FILE);}}returnnull;}/***ReadtheExcel2010*@*@return*@throwsIOException*/publicList<Student>readXlsx(Stringpath)throwsIOException{System.out.println(Common.PROCESSING+path);InputStreamis=newFileInputStream(path);XSSFWorkbookxssfWorkbook=newXSSFWorkbook(is);Studentstudent=null;List<Student>list=newArrayList<Student>();//ReadtheSheetfor(intnumSheet=0;numSheet<xssfWorkbook.getNumberOfSheets();numSheet++){XSSFSheetxssfSheet=xssfWorkbook.getSheetAt(numSheet);if(xssfSheet==null){continue;}//ReadtheRowfor(introwNum=1;rowNum<=xssfSheet.getLastRowNum();rowNum++){XSSFRowxssfRow=xssfSheet.getRow(rowNum);if(xssfRow!=null){student=newStudent();XSSFCellno=xssfRow.getCell(0);XSSFCellname=xssfRow.getCell(1);XSSFCellage=xssfRow.getCell(2);XSSFCellscore=xssfRow.getCell(3);student.setNo(getValue(no));student.setName(getValue(name));student.setAge(getValue(age));student.setScore(Float.valueOf(getValue(score)));list.add(student);}}}returnlist;}/***ReadtheExcel2003-2007*@parampaththepathoftheExcel*@return*@throwsIOException*/publicList<Student>readXls(Stringpath)throwsIOException{System.out.println(Common.PROCESSING+path);InputStreamis=newFileInputStream(path);HSSFWorkbookhssfWorkbook=newHSSFWorkbook(is);Studentstudent=null;List<Student>list=newArrayList<Student>();//ReadtheSheetfor(intnumSheet=0;numSheet<hssfWorkbook.getNumberOfSheets();numSheet++){HSSFSheethssfSheet=hssfWorkbook.getSheetAt(numSheet);if(hssfSheet==null){continue;}//ReadtheRowfor(introwNum=1;rowNum<=hssfSheet.getLastRowNum();rowNum++){HSSFRowhssfRow=hssfSheet.getRow(rowNum);if(hssfRow!=null){student=newStudent();HSSFCellno=hssfRow.getCell(0);HSSFCellname=hssfRow.getCell(1);HSSFCellage=hssfRow.getCell(2);HSSFCellscore=hssfRow.getCell(3);student.setNo(getValue(no));student.setName(getValue(name));student.setAge(getValue(age));student.setScore(Float.valueOf(getValue(score)));list.add(student);}}}returnlist;}@SuppressWarnings("static-access")privateStringgetValue(XSSFCellxssfRow){if(xssfRow.getCellType()==xssfRow.CELL_TYPE_BOOLEAN){returnString.valueOf(xssfRow.getBooleanCellValue());}elseif(xssfRow.getCellType()==xssfRow.CELL_TYPE_NUMERIC){returnString.valueOf(xssfRow.getNumericCellValue());}else{returnString.valueOf(xssfRow.getStringCellValue());}}@SuppressWarnings("static-access")privateStringgetValue(HSSFCellhssfCell){if(hssfCell.getCellType()==hssfCell.CELL_TYPE_BOOLEAN){returnString.valueOf(hssfCell.getBooleanCellValue());}elseif(hssfCell.getCellType()==hssfCell.CELL_TYPE_NUMERIC){returnString.valueOf(hssfCell.getNumericCellValue());}else{returnString.valueOf(hssfCell.getStringCellValue());}}}/****/packagecom.b510.excel.client;importjava.io.IOException;importjava.util.List;importcom.b510.common.Common;importcom.b510.excel.ReadExcel;importcom.b510.excel.vo.Student;/***@authorHongten*@created2014-5-21*/publicclassClient{publicstaticvoidmain(String[]args)throwsIOException{Stringexcel2003_2007=Common.STUDENT_INFO_XLS_PATH;Stringexcel2010=Common.STUDENT_INFO_XLSX_PATH;//readthe2003-2007excelList<Student>list=newReadExcel().readExcel(excel2003_2007);if(list!=null){for(Studentstudent:list){System.out.println("No.:"+student.getNo()+",name:"+student.getName()+",age:"+student.getAge()+",score:"+student.getScore());}}System.out.println("======================================");//readthe2010excelList<Student>list1=newReadExcel().readExcel(excel2010);if(list1!=null){for(Studentstudent:list1){System.out.println("No.:"+student.getNo()+",name:"+student.getName()+",age:"+student.getAge()+",score:"+student.getScore());}}}}/****/packagecom.b510.excel.util;importcom.b510.common.Common;/***@authorHongten*@created2014-5-21*/publicclassUtil{/***getpostfixofthepath*@parampath*@return*/publicstaticStringgetPostfix(Stringpath){if(path==null||Common.EMPTY.equals(path.trim())){returnCommon.EMPTY;}if(path.contains(Common.POINT)){returnpath.substring(path.lastIndexOf(Common.POINT)+1,path.length());}returnCommon.EMPTY;}}/****/packagecom.b510.excel.vo;/***Student**@authorHongten*@created2014-5-18*/publicclassStudent{/***id*/privateIntegerid;/***学号*/privateStringno;/***姓名*/privateStringname;/***学院*/privateStringage;/***成绩*/privatefloatscore;publicIntegergetId(){returnid;}publicvoidsetId(Integerid){this.id=id;}publicStringgetNo(){returnno;}publicvoidsetNo(Stringno){this.no=no;}publicStringgetName(){returnname;}publicvoidsetName(Stringname){this.name=name;}publicStringgetAge(){returnage;}publicvoidsetAge(Stringage){this.age=age;}publicfloatgetScore(){returnscore;}publicvoidsetScore(floatscore){this.score=score;}}
2. java哪个导出excel效率比较高
我推荐使用 POI，POI 3.6 版本的话，可以处于 Excel 2003 和 Excel 2007 格式的。
3. 在java中导出数据到Excel中时老提示版本不对，why
Java Build Path里面的JRE也要改成JRE1.6才行
4. java 如何 判断 读入excel文件的版本(20032007) 请高手指点
apache poi Workbook hssWB = null;try {//2003 hssWB = new HSSFWorkbook(new FileInputStream("excel文件")); } catch (Exception e) { // TODO: handle exception//2007 hssWB = new XSSFWorkbook(new FileInputStream("excel文件"));}
5. java poi 包读取excel文件如何判断文件如何判断是2003或者其他版本
后缀名字可以区分，03是xls，07是xlsx
6. JAVA读取EXCEL的版本错误问题
如果你用MyEclipse确认以下两内点：容1.[window]-[preferences]-[java]-[Compiler]:Compiler compiliance level>=1.52.[project]-[properties]-[java Compiler]:JDK Compilance>=1.5
7. java读取excel问题 同时兼容2003和2007
poi针对2003和2007有不同的处理类，必须要判断excel版本，然后采用不同的类，针对2003是HSSF开头的泪，针对2007是XSSF开头的类
8. java中怎么读取excel文件
package com.jqgj.test;import java.io.File;import java.io.FileInputStream;import java.io.IOException;import java.io.InputStream;import java.text.DecimalFormat;import java.util.ArrayList;import java.util.List;import org.apache.commons.io.FilenameUtils;import org.apache.poi.hssf.usermodel.HSSFWorkbook;import org.apache.poi.ss.usermodel.Cell;import org.apache.poi.ss.usermodel.CellValue;import org.apache.poi.ss.usermodel.DateUtil;import org.apache.poi.ss.usermodel.FormulaEvaluator;import org.apache.poi.ss.usermodel.Row;import org.apache.poi.ss.usermodel.Sheet;import org.apache.poi.ss.usermodel.Workbook;import org.apache.poi.xssf.usermodel.XSSFWorkbook;public class ImportNameTest {/*** Excel 2003*/private final static String XLS = "xls";/*** Excel 2007*/private final static String XLSX = "xlsx";/*** 分隔符*/private final static String SEPARATOR = "|";/*** 由Excel文件的Sheet导出至List* * @param file* @param sheetNum* @return*/public static List<String> exportListFromExcel(File file, int sheetNum)throws IOException {return exportListFromExcel(new FileInputStream(file),FilenameUtils.getExtension(file.getName()), sheetNum);}/*** 由Excel流的Sheet导出至List* * @param is* @param extensionName* @param sheetNum* @return* @throws IOException*/public static List<String> exportListFromExcel(InputStream is,String extensionName, int sheetNum) throws IOException {Workbook workbook = null;if (extensionName.toLowerCase().equals(XLS)) {workbook = new HSSFWorkbook(is);} else if (extensionName.toLowerCase().equals(XLSX)) {workbook = new XSSFWorkbook(is);}return exportListFromExcel(workbook, sheetNum);}/*** 由指定的Sheet导出至List* * @param workbook* @param sheetNum* @return* @throws IOException*/private static List<String> exportListFromExcel(Workbook workbook,int sheetNum) {Sheet sheet = workbook.getSheetAt(sheetNum);// 解析公式结果FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();List<String> list = new ArrayList<String>();int minRowIx = sheet.getFirstRowNum();int maxRowIx = sheet.getLastRowNum();for (int rowIx = minRowIx; rowIx <= maxRowIx; rowIx++) {Row row = sheet.getRow(rowIx);StringBuilder sb = new StringBuilder();short minColIx = row.getFirstCellNum();short maxColIx = row.getLastCellNum();for (short colIx = minColIx; colIx <= maxColIx; colIx++) {Cell cell = row.getCell(new Integer(colIx));CellValue cellValue = evaluator.evaluate(cell);if (cellValue == null) {continue;}// 经过公式解析，最后只存在Boolean、Numeric和String三种数据类型，此外就是Error了// 其余数据类型，根据官方文档，完全可以忽略http://poi.apache.org/spreadsheet/eval.htmlswitch (cellValue.getCellType()) {case Cell.CELL_TYPE_BOOLEAN:sb.append(SEPARATOR + cellValue.getBooleanValue());break;case Cell.CELL_TYPE_NUMERIC:// 这里的日期类型会被转换为数字类型，需要判别后区分处理if (DateUtil.isCellDateFormatted(cell)) {sb.append(SEPARATOR + cell.getDateCellValue());} else {//把手机号码转换为字符串DecimalFormat df = new DecimalFormat("#");sb.append(SEPARATOR + df.format(cellValue.getNumberValue()));}break;case Cell.CELL_TYPE_STRING:sb.append(SEPARATOR + cellValue.getStringValue());break;case Cell.CELL_TYPE_FORMULA:break;case Cell.CELL_TYPE_BLANK:break;case Cell.CELL_TYPE_ERROR:break;default:break;}}list.add(sb.toString());}return list;}/*** @param args*/public static void main(String[] args) {String path = "f:\\telName.xlsx";try {List<String> listS= exportListFromExcel(new File(path),0);for(int i=0;i<listS.size();i++){System.out.println(listS.get(i));}} catch (IOException e) {// TODO Auto-generated catch blocke.printStackTrace();}}}
