Java POI读取excel的例子

此例子支持xls、xlsx格式（即：2003、2010），使用poi的jar版本是3.8。

导入excel内容：

导出excel模板：

1、TextExcel.java

<span style="font-size:12px;">package com.excel;import java.io.File;import java.io.FileInputStream;import java.io.FileOutputStream;import java.io.InputStream;import java.util.ArrayList;import java.util.Calendar;import java.util.List;import org.apache.poi.ss.usermodel.Cell;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.ss.usermodel.WorkbookFactory;import com.yfli.util.User;public class TestExcel {public static void main(String[] args) throws Exception {//导入importExcel("F:\\xls\\test_import.xls");//导出File modelFile = new File("F:\\xls\\test_export_model.xls");Calendar now = Calendar.getInstance();String dbfFileName = "sfData_" + now.get(Calendar.YEAR)+ (now.get(Calendar.MONTH) + 1) + ""+ now.get(Calendar.DAY_OF_MONTH)+ now.get(Calendar.HOUR_OF_DAY) + now.get(Calendar.MINUTE)+ now.get(Calendar.MINUTE) + ".xls";String directoryPath = "F:\\xls\\";String filePath = "F:\\xls\\" + dbfFileName;File directory = new File(directoryPath);// 目标文件夹if (!directory.exists()) {directory.mkdirs();// 创建目标目录}File outputFile = new File(filePath);if (!outputFile.exists() || outputFile.isDirectory()) {outputFile.createNewFile();}exportExcelContent(modelFile, outputFile, getUserList());}public static void importExcel(String file) {try {FileInputStream fis = new FileInputStream(file);Workbook wb = WorkbookFactory.create(fis);int sheetCount = wb.getNumberOfSheets(); // 得到excel工作表的总数for (int n = 0; n < sheetCount; n++) {Sheet sheet = wb.getSheetAt(n); // 得到Excel工作表对象int rowNum = sheet.getPhysicalNumberOfRows();// 得到行的总数if (rowNum == 0) {continue;}String[] title = readExcelHead(sheet);List<User> users = readExcelContent(sheet, title);for (User user : users) {System.out.println(user.getName() + "---" + user.getSex()+ "---" + user.getBirthday() + "---"+ user.getAge());}}} catch (Exception ex) {ex.printStackTrace();}}/** * 读取Excel表头 */public static String[] readExcelHead(Sheet sheet) {Row row = sheet.getRow(0);int colNum = row.getPhysicalNumberOfCells(); // 标题总列数String[] title = new String[colNum];for (int i = 0; i < colNum; i++) {title[i] = getStringCellValue(row.getCell(i));}return title;}public static List<User> readExcelContent(Sheet sheet, String[] title)throws Exception {int rowNum = sheet.getPhysicalNumberOfRows();// 得到行的总数Row row = sheet.getRow(0);int colNum = row.getPhysicalNumberOfCells(); // 标题总列数List<User> content = new ArrayList<User>();for (int i = 1; i < rowNum; i++) {Row rows = sheet.getRow(i);User user = new User();for (int j = 0; j < colNum; j++) {if (title[j].toString().equals("姓名")) {user.setName(getStringCellValue(rows.getCell(j)));}if (title[j].toString().equals("性别")) {user.setSex(getStringCellValue(rows.getCell(j)));}if (title[j].toString().equals("出生日期")) {user.setBirthday(getStringCellValue(rows.getCell(j)));}if (title[j].toString().equals("年龄")) {user.setAge(getStringCellValue(rows.getCell(j)));}}content.add(user);}return content;}/** * 导出数据到excel中 *  * @param outputFile *            导出excel文件 * @param modelFile *            导出excel模板（test_model.xlsx） */public static void exportExcelContent(File modelFile, File outputFile,List<User> users) {InputStream inp = null;Workbook wb = null;try {inp = new FileInputStream(modelFile);wb = WorkbookFactory.create(inp); // 读取导出excel模板Sheet sheet = wb.getSheetAt(0);String[] title = readExcelHead(sheet);// 生成导出数据int startRow = 1;for (int i = 0; i < users.size(); i++) {User user = users.get(i);Row row = sheet.createRow(startRow++);for (int j = 0; j < title.length; j++) {String titleName = title[j];Cell cell = row.createCell(j);cell.setCellType(Cell.CELL_TYPE_STRING);if (titleName.equals("姓名")) {cell.setCellValue(user.getName());}if (titleName.equals("性别")) {cell.setCellValue(user.getSex());}if (titleName.equals("出生日期")) {cell.setCellValue(user.getBirthday());}if (titleName.equals("年龄")) {cell.setCellValue(user.getAge());}}}// 导出到文件中FileOutputStream fileOut = new FileOutputStream(outputFile);wb.write(fileOut);fileOut.close();} catch (Exception ex) {ex.printStackTrace();}}/** * 获取单元格数据内容为字符串类型的数据 *  * @param cell *            Excel单元格 * @return String 单元格数据内容 */private static String getStringCellValue(Cell cell) {String strCell = "";switch (cell.getCellType()) {case Cell.CELL_TYPE_STRING:strCell = cell.getStringCellValue();break;case Cell.CELL_TYPE_NUMERIC:if(DateUtil.isCellDateFormatted(cell)) {                              strCell = sdf.format(cell.getDateCellValue());                        }else {                              strCell = String.valueOf(cell.getNumericCellValue());                        }break;case Cell.CELL_TYPE_BOOLEAN:strCell = String.valueOf(cell.getBooleanCellValue());break;case Cell.CELL_TYPE_BLANK:strCell = "";break;case Cell.CELL_TYPE_FORMULA:strCell = String.valueOf(cell.getNumericCellValue());break;default:strCell = "";break;}if (strCell.equals("") || strCell == null) {return "";}if (cell == null) {return "";}return strCell;}private static List<User> getUserList() {List<User> users = new ArrayList<User>();User user = new User();user.setName("张三");user.setSex("男");user.setBirthday("2013-12-01");user.setAge("22");users.add(user);user = new User();user.setName("李四");user.setSex("男");user.setBirthday("2013-12-02");user.setAge("23");users.add(user);return users;}}</span>

2、User.java

<span style="font-size:12px;">package com.yfli.util;public class User {public String name;public String sex;public String birthday;public String age;public String getName() {return name;}public void setName(String name) {this.name = name;}public String getSex() {return sex;}public void setSex(String sex) {this.sex = sex;}public String getBirthday() {return birthday;}public void setBirthday(String birthday) {this.birthday = birthday;}public String getAge() {return age;}public void setAge(String age) {this.age = age;}}</span>

附：单元格数据格式