java poi 读取Excel

来源:转载

java 用poi读取excel并区分2003和2007
2003 需要的jar:poi-x.jar
2007 需要的jar:poi-ooxml-x.jar poi-ooxml-schemas-x.jar,dom4j.jar,xmlbeans-2.6.0.jar

如果项目用了maven的话 pom配置中加入以下就可以了:

<properties> <poi-version>3.9</poi-version></properties><dependencies><dependency> <groupId>org.apache.poi</groupId> <artifactId>poi</artifactId> <version>${poi-version}</version></dependency><dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-ooxml</artifactId> <version>${poi-version}</version></dependency><dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-ooxml-schemas</artifactId> <version>${poi-version}</version></dependency><dependency> <groupId>org.apache.xmlbeans</groupId> <artifactId>xmlbeans</artifactId> <version>2.6.0</version></dependency><dependency> <groupId>dom4j</groupId> <artifactId>dom4j-core</artifactId> <version>1.4-dev-8</version></dependency></dependencies> 


package com.jynine.utils;import java.io.File;import java.io.FileInputStream;import java.text.DecimalFormat;import java.text.SimpleDateFormat;import java.util.Iterator;import java.util.LinkedList;import java.util.List;import org.apache.poi.hssf.usermodel.HSSFDateUtil;import org.apache.poi.hssf.usermodel.HSSFWorkbook;import org.apache.poi.ss.usermodel.Cell;import org.apache.poi.ss.usermodel.CellStyle;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;/** * * @author jynine * */public class ExcelUtil {private static SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");// 字符private static DecimalFormat df = new DecimalFormat("0");// 格式化 number Stringprivate static DecimalFormat nf = new DecimalFormat("0.00");// 格式化数字public static Workbook getWorkbook(File file) throws Exception {String fileName = file.getName();String extension = fileName.lastIndexOf(".") == -1 ? "" : fileName.substring(fileName.lastIndexOf(".") + 1);FileInputStream fis = new FileInputStream(file);// 根据不同的文件名返回不同类型的WorkBookif (extension.equals("xls")) {return new HSSFWorkbook(fis);} else if (extension.equals("xlsx")) {return new XSSFWorkbook(fis);} else {throw new Exception("不支持该格式的文件!");}}/** * 读取excel 文件 * @param file * @param startSheet * @param startRow * @return */public static List<List<Object>> readExcel(File file,int startSheet,int startRow) {List<List<Object>> list = new LinkedList<List<Object>>();Workbook wb = null;try {wb = getWorkbook(file);Sheet sheet = wb.getSheetAt(startSheet);Object value = null;Row row = null;Cell cell = null;CellStyle cs = null;String csStr = null;Double numval = null;Iterator<Row> rows = sheet.rowIterator();while (rows.hasNext()) {row = (Row) rows.next();if(row.getRowNum() >= startRow){List<Object> cellList = new LinkedList<Object>();;Iterator<Cell> cells = row.cellIterator();while (cells.hasNext()) {cell = (Cell) cells.next();switch (cell.getCellType()) {case Cell.CELL_TYPE_NUMERIC:cs = cell.getCellStyle();csStr = cs.getDataFormatString();numval = cell.getNumericCellValue();if ("@".equals(csStr)) {value = df.format(numval);} else if ("General".equals(csStr)) {value = nf.format(numval);} else {value = sdf.format(HSSFDateUtil.getJavaDate(numval));}break;case Cell.CELL_TYPE_STRING:value = cell.getStringCellValue();break;case Cell.CELL_TYPE_FORMULA:if (!cell.getStringCellValue().equals("")) { value = cell.getStringCellValue(); } else { value = cell.getNumericCellValue() + ""; }break;case Cell.CELL_TYPE_BLANK:value = "";break;case Cell.CELL_TYPE_BOOLEAN:value = cell.getBooleanCellValue();break;default:value = cell.toString();}cellList.add(value);}list.add(cellList);}}} catch (Exception e) {e.printStackTrace();}return list;}public static void main(String[] args) {File file = new File("D://test.xlsx");List<List<Object>> list = readExcel(file, 0, 0);for (int i = 0; i < list.size(); i++) {List<Object> objects = list.get(i);for (int j = 0; j < objects.size(); j++) {System.out.print(objects.get(j)+"=====");}System.out.println();}}}

poi-ooxml-3.9.jar (914.7 KB) 下载次数: 4 dom4j-core-1.4-dev-8.jar (283.1 KB) 下载次数: 2 poi-3.9.jar (1.8 MB) 下载次数: 1 poi-ooxml-schemas-3.9.jar (4.6 MB) 下载次数: 5 xmlbeans-2.6.0.jar (2.6 MB) 下载次数: 2



分享给朋友:
您可能感兴趣的文章:
随机阅读: