全栈工程师开发手册 （作者：栾鹏）
java教程全解
java使用poi读取存储excel表格，包括xls和xlsx格式。
需要导入的包
poi-3.14.jar
poi-ooxml-3.14.jar
poi-ooxml-schemas-3.14.jar
xmlbeans-2.6.0.jar
代码中需要引入
import org.apache.poi.hssf.usermodel.HSSFCell;import org.apache.poi.hssf.usermodel.HSSFRow;import org.apache.poi.hssf.usermodel.HSSFSheet;import org.apache.poi.hssf.usermodel.HSSFWorkbook;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 static void main(String[] arges){try {List<List<String>> alldata=readExcel("myfile.xlsx");for (List<String> arrayList : alldata) {for (String string : arrayList) {System.out.println(string);}}} catch (Exception e) {// TODO: handle exception}}
操作excel表格函数类的实现
package com.lp.app.io;import java.io.FileInputStream;import java.io.IOException;import java.io.InputStream;import java.util.ArrayList;import java.util.List;import org.apache.poi.hssf.usermodel.HSSFCell;import org.apache.poi.hssf.usermodel.HSSFRow;import org.apache.poi.hssf.usermodel.HSSFSheet;import org.apache.poi.hssf.usermodel.HSSFWorkbook;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;//使用poi读取excel表格，包括xls和xlsx格式public class MyExcel_poi {//读取表格文件public static List<List<String>> readExcel(String path) throws IOException {if (path == null ||"".equals(path)) {return null;} else {String type = gettype(path);if (!"".equals(type)) {if ("xls".equals(type)) {return readXls(path);} else if ("xlsx".equals(type)) {return readXlsx(path);}} else {System.out.println(path + " : Not the Excel file!");}}return null;}//根据路径查询是xls文件还是xlsx文件public static String gettype(String path) {if (path == null || "".equals(path.trim())) {return "";}if (path.contains(".")) {return path.substring(path.lastIndexOf(".") + 1, path.length());}return "";}//读取xlsx文件public static List<List<String>> readXlsx(String path) throws IOException {System.out.println("Processing..." + path);InputStream is = new FileInputStream(path);XSSFWorkbook xssfWorkbook = new XSSFWorkbook(is);List<List<String>> list = new ArrayList<List<String>>();// Read the Sheetfor (int numSheet = 0; numSheet < xssfWorkbook.getNumberOfSheets(); numSheet++) {XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(numSheet);if (xssfSheet == null) {continue;}// Read the Rowfor (int rowNum = 1; rowNum <= xssfSheet.getLastRowNum(); rowNum++) {XSSFRow xssfRow = xssfSheet.getRow(rowNum);if (xssfRow != null) {//hssfSheet.getLastRowNum();//最后一行行标，比行数小1//hssfSheet.getRow(k).getLastCellNum();//获取列数，比最后一列列标大1List<String> rowStrings = new ArrayList<String>();for (int columnNum = 0; columnNum < xssfRow.getLastCellNum(); columnNum++) {rowStrings.add(getValue(xssfRow.getCell(columnNum)));}list.add(rowStrings);}}}xssfWorkbook.close();return list;}//读取xls文件public static List<List<String>> readXls(String path) throws IOException {System.out.println("Processing..." + path);InputStream is = new FileInputStream(path);HSSFWorkbook hssfWorkbook = new HSSFWorkbook(is);List<List<String>> list = new ArrayList<List<String>>();// Read the Sheetfor (int numSheet = 0; numSheet < hssfWorkbook.getNumberOfSheets(); numSheet++) {HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(numSheet);if (hssfSheet == null) {continue;}// Read the Rowfor (int rowNum = 0; rowNum <= hssfSheet.getLastRowNum(); rowNum++) {HSSFRow hssfRow = hssfSheet.getRow(rowNum);if (hssfRow != null) {//hssfSheet.getLastRowNum();//最后一行行标，比行数小1//hssfSheet.getRow(k).getLastCellNum();//获取列数，比最后一列列标大1List<String> rowStrings = new ArrayList<String>();for (int columnNum = 0; columnNum < hssfRow.getLastCellNum(); columnNum++) {rowStrings.add(getValue(hssfRow.getCell(columnNum)));}list.add(rowStrings);}}}hssfWorkbook.close();return list;}@SuppressWarnings("static-access")private static String getValue(XSSFCell xssfRow) {if (xssfRow.getCellType() == xssfRow.CELL_TYPE_BOOLEAN) {return String.valueOf(xssfRow.getBooleanCellValue());} else if (xssfRow.getCellType() == xssfRow.CELL_TYPE_NUMERIC) {return String.valueOf(xssfRow.getNumericCellValue());} else {return String.valueOf(xssfRow.getStringCellValue());}}@SuppressWarnings("static-access")private static String getValue(HSSFCell hssfCell) {if (hssfCell.getCellType() == hssfCell.CELL_TYPE_BOOLEAN) {return String.valueOf(hssfCell.getBooleanCellValue());} else if (hssfCell.getCellType() == hssfCell.CELL_TYPE_NUMERIC) {return String.valueOf(hssfCell.getNumericCellValue());} else {return String.valueOf(hssfCell.getStringCellValue());}}}
