如何创建和读取excel文件-poi的简单使用(一)

Jakarta POI是apache的子项目,它提供了一组操纵Windows文档的Java API,通过它可以用纯Java代码来读取,写入和修改Excel文件.
本实例将excel内容抽象为一个Map<String, Map<String, List<String>>>,第一个key为sheet的名称,第二个key为行号.

1.实现将map写入excel,直接看代码ExcelExprotUtil.

package com.ilucky.poi.util;import java.io.BufferedOutputStream;import java.io.FileOutputStream;import java.util.List;import java.util.Map;import java.util.Map.Entry;import org.apache.poi.hssf.usermodel.HSSFCell;import org.apache.poi.hssf.usermodel.HSSFRichTextString;import org.apache.poi.hssf.usermodel.HSSFRow;import org.apache.poi.hssf.usermodel.HSSFSheet;import org.apache.poi.hssf.usermodel.HSSFWorkbook;/** * @author IluckySi * @date 20140807 */public class ExcelExportUtil {private String path;private String name;private Map<String, Map<String, List<String>>> excel;public ExcelExportUtil(Map<String, Map<String, List<String>>> excel) {this.excel = excel;}public String getPath() {return path;}public void setPath(String path) {this.path = path;}public String getName() {return name;}public void setName(String name) {this.name = name;}public void exportExcel() {FileOutputStream fos = null;BufferedOutputStream bos = null;try {//校验数据源是否合法.if(excel.size() <= 0) {try {throw new Exception("excel数据源有问题!");} catch (Exception e) {e.printStackTrace();}}//创建excel.HSSFWorkbook hssfWorkbook = new HSSFWorkbook();int sheetCount = 0;for(Entry<String, Map<String, List<String>>> sheet : excel.entrySet()) {//创建sheet并命名.String sheetName = sheet.getKey();HSSFSheet hssfSheet = hssfWorkbook.createSheet();hssfWorkbook.setSheetName(sheetCount, sheetName);Map<String, List<String>> sheetValue = sheet.getValue();for(Entry<String, List<String>> row : sheetValue.entrySet()) {//创建行.int rowNumber = Integer.parseInt(row.getKey()); HSSFRow hssfRow = hssfSheet.createRow(rowNumber);   List<String> rowValue = row.getValue(); for(int i = 0; rowValue != null && i < rowValue.size(); i++) { //创建单元格并写入内容.String cellValue = rowValue.get(i);    HSSFCell hssfCell = hssfRow.createCell((short)i);      hssfCell.setCellValue(new HSSFRichTextString(cellValue));   }}sheetCount++;}//导出为excel.fos = new FileOutputStream(path + "/" + name);    bos = new BufferedOutputStream(fos);      hssfWorkbook.write(bos);  } catch (Exception e) {System.out.println("导出excel发生问题: " + e);} finally {try {if(bos != null) {bos.close();bos = null;}if(fos != null) {fos.close();fos = null;}} catch (Exception e) {System.out.println("关闭文件流发生问题: " + e);}}}}

2.实现将excel中的内容放入map,直接看代码ExcelImportUtil.

package com.ilucky.poi.util;import java.io.FileInputStream;import java.util.ArrayList;import java.util.HashMap;import java.util.Iterator;import java.util.List;import java.util.Map;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.poifs.filesystem.POIFSFileSystem;/** * @author IluckySi * @date 20140807 */public class ExcelImportUtil {private String path;private String name;private Map<String, Map<String, List<String>>> excel;public ExcelImportUtil() { excel = new HashMap<String, Map<String, List<String>>>();}public String getPath() {return path;}public void setPath(String path) {this.path = path;}public String getName() {return name;}public void setName(String name) {this.name = name;}public Map<String, Map<String, List<String>>> importExcel () {//创建一个map存放excel中的数据.    FileInputStream fis = null;    try {            //加载要读取的excel文件.    fis = new FileInputStream(path + "/" + name);    POIFSFileSystem pfs = new POIFSFileSystem(fis);    HSSFWorkbook hssfWorkbook = new HSSFWorkbook(pfs);    int sheetCount = getSheetCount(hssfWorkbook);    for(int i = 0; i < sheetCount; i++) {    //获取sheet.    Map<String, List<String>> sheet = new HashMap<String, List<String>>();    String sheetName = hssfWorkbook.getSheetName(i);                HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(i);                @SuppressWarnings("unchecked")Iterator<HSSFRow>rowIteraotr = hssfSheet.rowIterator();                //获取行.                int rowNumber = 0;                while(rowIteraotr.hasNext()) {                List<String> rowList = new ArrayList<String>();                 HSSFRow hssfRow = rowIteraotr.next();                 @SuppressWarnings("unchecked")Iterator<HSSFCell> cellIterator = hssfRow.cellIterator();                 while(cellIterator.hasNext()) {                //获取单元格内数据.                 HSSFCell hssfCell = cellIterator.next();                 String cellValue =hssfCell.getRichStringCellValue().toString();                 rowList.add(cellValue);                 }                 sheet.put(rowNumber + "", rowList);                 rowNumber++;                }                excel.put(sheetName, sheet);    }    } catch (Exception e) {    System.out.println("获取excel数据发生问题: " + e);    } finally {    try {    if(fis != null) {    fis.close();    fis = null;    }    } catch (Exception e) {    System.out.println("关闭文件流发生问题: " + e);    }    }    return excel;}@SuppressWarnings("unused")public static int getSheetCount(HSSFWorkbook hssfWorkbook) {int count = 0;try {for(int i = 0; i < 255; i++) {count = i; HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(i);}} catch (Exception e) {return count;} finally {System.out.println("此excel共有" + count + "个sheet!");}return count;}}

3.最后看测试类MainTest.

package com.ilucky.poi;import java.util.ArrayList;import java.util.HashMap;import java.util.List;import java.util.Map;import java.util.Map.Entry;import com.ilucky.poi.util.ExcelExportUtil;import com.ilucky.poi.util.ExcelImportUtil;/** * @author IluckySi * @date 20140807 */public class MainTest {          public static void main(String[] args) {          //写入excel数据.    exportExcel();        //读取excel数据.    importExcel();    }        public static void exportExcel() {    //创建一个map模拟excel中的数据.    Map<String, Map<String, List<String>>> excel = new HashMap<String, Map<String, List<String>>>();        //创建一个map模拟第一个sheet中的数据.    Map<String, List<String>> sheet1 = new HashMap<String, List<String>>();    //创建一个list模拟第一行的数据.    List<String> sheet1_row1 = new ArrayList<String>();    sheet1_row1.add("姓名");    sheet1_row1.add("年龄");    sheet1_row1.add("学历");    sheet1.put("0", sheet1_row1);    //创建一个list模拟第二行的数据.    List<String> sheet1_row2 = new ArrayList<String>();    sheet1_row2.add("司冬雪");    sheet1_row2.add("26");    sheet1_row2.add("大专");    sheet1.put("1", sheet1_row2);    //创建一个list模拟第三行的数据.    List<String> sheet1_row3 = new ArrayList<String>();    sheet1_row3.add("爱谁谁");    sheet1_row3.add("26");    sheet1_row3.add("本科");    sheet1.put("2", sheet1_row3);    excel.put("sheet_name1", sheet1);        //创建一个map模拟第一个sheet中的数据.    Map<String, List<String>> sheet2 = new HashMap<String, List<String>>();    //创建一个list模拟第一行的数据.    List<String> sheet2_row1 = new ArrayList<String>();    sheet2_row1.add("演员");    sheet2_row1.add("职业");    sheet2_row1.add("年龄");    sheet2.put("0", sheet2_row1);    //创建一个list模拟第二行的数据.    List<String> sheet2_row2 = new ArrayList<String>();    sheet2_row2.add("郭德纲");    sheet2_row2.add("相声演员");    sheet2_row2.add("40");    sheet2.put("1", sheet2_row2);    //创建一个list模拟第三行的数据.    List<String> sheet2_row3 = new ArrayList<String>();    sheet2_row3.add("赵本山");    sheet2_row3.add("小品演员");    sheet2_row3.add("55");    sheet2.put("2", sheet2_row3);    excel.put("sheet_name2", sheet2);        //将数据源写入excel.    ExcelExportUtil eeu = new ExcelExportUtil(excel);    eeu.setPath("D:/");    eeu.setName("excel_exprot.xls");    eeu.exportExcel();    }          public static void importExcel() {    //创建一个map存放excel中的数据.    Map<String, Map<String, List<String>>> excel = new HashMap<String, Map<String, List<String>>>();        //读取excel数据.    ExcelImportUtil eiu = new ExcelImportUtil();    eiu.setPath("D:/");    eiu.setName("excel_exprot.xls");    excel = eiu.importExcel();        //遍历获取的excel数据.    for(Entry<String, Map<String, List<String>>> map : excel.entrySet()) {    String sheetKey = map.getKey();    System.out.println("sheet_name: " + sheetKey);    Map<String, List<String>> sheetValue = map.getValue();    for(Entry<String, List<String>> row : sheetValue.entrySet()) {    String rowNumber = row.getKey();    System.out.println("第" + rowNumber + "行数据: ");    List<String> rowValue = row.getValue();    for(int i = 0; rowValue != null && i < rowValue.size(); i++) {    String cellValue = rowValue.get(i);    System.out.println("第" + i + "个单元格的数据: " + cellValue);    }    }    }    }}/**excel共有2个sheet!sheet_name: sheet_name1第2行数据: 第0个单元格的数据: 爱谁谁第1个单元格的数据: 26第2个单元格的数据: 本科第1行数据: 第0个单元格的数据: 司冬雪第1个单元格的数据: 26第2个单元格的数据: 大专第0行数据: 第0个单元格的数据: 姓名第1个单元格的数据: 年龄第2个单元格的数据: 学历sheet_name: sheet_name2第2行数据: 第0个单元格的数据: 赵本山第1个单元格的数据: 小品演员第2个单元格的数据: 55第1行数据: 第0个单元格的数据: 郭德纲第1个单元格的数据: 相声演员第2个单元格的数据: 40第0行数据: 第0个单元格的数据: 演员第1个单元格的数据: 职业第2个单元格的数据: 年龄*/