使用POI读取Excel内容（一）

1、不包含单元格合并的写excel

 

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39

/**  * excel导出到输出流  * 谁调用谁负责关闭输出流  * @param os 输出流  * @param excelExtName excel文件的扩展名，支持xls和xlsx，不带点号  * @param data  * @throws IOException  */ public static  void  writeExcel(OutputStream os, String excelExtName, Map<string, string="">>> data) throws IOException{     Workbook wb = null;     try {         if ("xls".equals(excelExtName)) {             wb = new HSSFWorkbook();         } else if  ("xlsx".equals(excelExtName)) {             wb = new XSSFWorkbook();         } else {             throw new  Exception("当前文件不是excel文件");         }         for (String sheetName : data.keySet()) {             Sheet sheet = wb.createSheet(sheetName);             List<list<string>> rowList = data.get(sheetName);             for (int i = 0; i < rowList.size(); i++) {                 List<string> cellList = rowList.get(i);                 Row row = sheet.createRow(i);                 for (int j = 0; j < cellList.size(); j++) {                     Cell cell = row.createCell(j);                     cell.setCellValue(cellList.get(j));                 }             }         }         wb.write(os);     } catch (Exception e) {         e.printStackTrace();     } finally {         if (wb != null) {             wb.close();         }     } }</string></list<string></string,>

2、包含单元格合并的写excel

 

辅助vo

 

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30

class ExcelData{     private String value;//单元格的值     private int  colSpan = 1;//单元格跨几列     private int  rowSpan = 1;//单元格跨几行     private boolean  alignCenter;//单元格是否居中，默认不居中，如果选择是，则水平和上下都居中     public boolean  isAlignCenter() {         return alignCenter;     }     public void  setAlignCenter(boolean alignCenter) {         this.alignCenter = alignCenter;     }     public String getValue() {         return value;     }     public void  setValue(String value) {         this.value = value;     }     public int  getColSpan() {         return colSpan;     }     public void  setColSpan(int colSpan) {         this.colSpan = colSpan;     }     public int  getRowSpan() {         return rowSpan;     }     public void  setRowSpan(int rowSpan) {         this.rowSpan = rowSpan;     } }

写excel文件的逻辑

 

 

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65

/**  * excel导出到输出流  * 谁调用谁负责关闭输出流  * @param os 输出流  * @param excelExtName excel文件的扩展名，支持xls和xlsx，不带点号  * @param data excel数据，map中的key是标签页的名称，value对应的list是标签页中的数据。list中的子list是标签页中的一行，子list中的对象是一个单元格的数据，包括是否居中、跨几行几列以及存的值是多少  * @throws IOException  */ public static  void  testWrite(OutputStream os, String excelExtName, Map<string, exceldata="">>> data) throws IOException{     Workbook wb = null;     CellStyle cellStyle = null;     boolean isXls;     try {         if ("xls".equals(excelExtName)) {             wb = new HSSFWorkbook();             isXls = true;         } else if  ("xlsx".equals(excelExtName)) {             wb = new XSSFWorkbook();             isXls = false;         } else {             throw new  Exception("当前文件不是excel文件");         }         cellStyle = wb.createCellStyle();         if (isXls) {             cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);             cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);         } else {             cellStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);             cellStyle.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);         }         for (String sheetName : data.keySet()) {             Sheet sheet = wb.createSheet(sheetName);             List<list<exceldata>> rowList = data.get(sheetName);             //i 代表第几行 从0开始             for (int i = 0; i < rowList.size(); i++) {                 List<exceldata> cellList = rowList.get(i);                 Row row = sheet.createRow(i);                 int j = 0;//j 代表第几列 从0开始                 for (ExcelData excelData : cellList) {                     if (excelData != null) {                         if (excelData.getColSpan() > 1 || excelData.getRowSpan() > 1) {                             CellRangeAddress cra = new CellRangeAddress(i, i + excelData.getRowSpan() - 1, j, j + excelData.getColSpan() - 1);                             sheet.addMergedRegion(cra);                         }                         Cell cell = row.createCell(j);                         cell.setCellValue(excelData.getValue());                         if (excelData.isAlignCenter()) {                             cell.setCellStyle(cellStyle);                         }                         j = j + excelData.getColSpan();                     } else {                         j++;                     }                 }             }         }         wb.write(os);     } catch (Exception e) {         e.printStackTrace();     } finally {         if (wb != null) {             wb.close();         }     } }</exceldata></list<exceldata></string,>

测试代码

 

 

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111

public static  void  main(String[] args) throws IOException {         Map<string, exceldata="">>> data = new LinkedHashMap<>();         List<list<exceldata>> sheet1 = new ArrayList<>();//第一页                   List<exceldata> list1 = new ArrayList<>();//第一行         ExcelData excelData = new ExcelData();//第一个单元格         excelData.setColSpan(6);         excelData.setRowSpan(1);         excelData.setValue("xxx");         excelData.setAlignCenter(true);         list1.add(excelData);                   List<exceldata> list2 = new ArrayList<>();//第二行         excelData = new ExcelData();//第一个单元格         excelData.setColSpan(1);         excelData.setRowSpan(1);         excelData.setValue("a");         list2.add(excelData);         excelData = new ExcelData();//第二个单元格         excelData.setColSpan(1);         excelData.setRowSpan(1);         excelData.setValue("b");         list2.add(excelData);         excelData = new ExcelData();//第三个单元格         excelData.setColSpan(2);         excelData.setRowSpan(4);         excelData.setValue("c");         excelData.setAlignCenter(true);         list2.add(excelData);         excelData = new ExcelData();//第四个单元格         excelData.setColSpan(2);         excelData.setRowSpan(2);         excelData.setValue("d");         excelData.setAlignCenter(true);         list2.add(excelData);                   List<exceldata> list3 = new ArrayList<>();//第三行         excelData = new ExcelData();//第一个单元格         excelData.setColSpan(1);         excelData.setRowSpan(1);         excelData.setValue("e");         list3.add(excelData);         excelData = new ExcelData();//第二个单元格         excelData.setColSpan(1);         excelData.setRowSpan(1);         excelData.setValue("f");         list3.add(excelData);         list3.add(null);//第三个单元格         list3.add(null);//第四个单元格         list3.add(null);//第五个单元格         list3.add(null);//第六个单元格                   List<exceldata> list4 = new ArrayList<>();//第四行         excelData = new ExcelData();//第一个单元格         excelData.setColSpan(1);         excelData.setRowSpan(1);         excelData.setValue("i");         list4.add(excelData);         excelData = new ExcelData();//第二个单元格         excelData.setColSpan(1);         excelData.setRowSpan(1);         excelData.setValue("j");         list4.add(excelData);         list4.add(null);//第三个单元格         list4.add(null);//第四个单元格         excelData = new ExcelData();//第五个单元格         excelData.setRowSpan(1);         excelData.setColSpan(1);         excelData.setValue("g");         list4.add(excelData);         excelData = new ExcelData();//第六个单元格         excelData.setRowSpan(1);         excelData.setColSpan(1);         excelData.setValue("h");         list4.add(excelData);                   List<exceldata> list5 = new ArrayList<>();//第五行         excelData = new ExcelData();//第一个单元格         excelData.setColSpan(1);         excelData.setRowSpan(1);         excelData.setValue("k");         list5.add(excelData);         excelData = new ExcelData();//第二个单元格         excelData.setColSpan(1);         excelData.setRowSpan(1);         excelData.setValue("l");         list5.add(excelData);         list5.add(null);//第三个单元格         list5.add(null);//第四个单元格         excelData = new ExcelData();//第五个单元格         excelData.setRowSpan(1);         excelData.setColSpan(1);         excelData.setValue("m");         list5.add(excelData);         excelData = new ExcelData();//第六个单元格         excelData.setRowSpan(1);         excelData.setColSpan(1);         excelData.setValue("n");         list5.add(excelData);                   sheet1.add(list1);         sheet1.add(list2);         sheet1.add(list3);         sheet1.add(list4);         sheet1.add(list5);                   data.put("表1", sheet1);                   testWrite(new FileOutputStream(new File("D:/temp/my.xlsx")), "xlsx", data);     } }</exceldata></exceldata></exceldata></exceldata></exceldata></list<exceldata></string,>

3、读取excel，这个方法的返回值带有一点业务逻辑，适用于没有单元格合并的excel，并且第一行是title的情况。返回的结果中，把第一行之外的每一个单元格包装成一个map，key是这个单元格的第一行的数值，也就是标题，value是这个单元格的值。逻辑有些绕，直接看代码。

 

 

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74

/**  * 适用于第一行是标题行的excel，例如  * 姓名   年龄  性别  身高  * 张三   25  男   175  * 李四   22  女   160  * 每一行构成一个map，key值是列标题，value是列值。没有值的单元格其value值为null  * 返回结果最外层的list对应一个excel文件，第二层的list对应一个sheet页，第三层的map对应sheet页中的一行  * @throws Exception  */ public static  List<list<map<string, string="">>> readExcelWithTitle(String filepath) throws Exception{     String fileType = filepath.substring(filepath.lastIndexOf(".") + 1, filepath.length());     InputStream is = null;     Workbook wb = null;     try {         is = new FileInputStream(filepath);                   if (fileType.equals("xls")) {             wb = new HSSFWorkbook(is);         } else if  (fileType.equals("xlsx")) {             wb = new XSSFWorkbook(is);         } else {             throw new  Exception("读取的不是excel文件");         }                   List<list<map<string, string="">>> result = new ArrayList<list<map<string,string>>>();//对应excel文件                   int sheetSize = wb.getNumberOfSheets();         for (int i = 0; i < sheetSize; i++) {//遍历sheet页             Sheet sheet = wb.getSheetAt(i);             List<map<string, string="">> sheetList = new ArrayList<map<string, string="">>();//对应sheet页                           List<string> titles = new ArrayList<string>();//放置所有的标题                           int rowSize = sheet.getLastRowNum() + 1;             for (int j = 0; j < rowSize; j++) {//遍历行                 Row row = sheet.getRow(j);                 if (row == null) {//略过空行                     continue;                 }                 int cellSize = row.getLastCellNum();//行中有多少个单元格，也就是有多少列                 if (j == 0) {//第一行是标题行                     for (int k = 0; k < cellSize; k++) {                         Cell cell = row.getCell(k);                         titles.add(cell.toString());                     }                 } else {//其他行是数据行                     Map<string, string=""> rowMap = new HashMap<string, string="">();//对应一个数据行                     for (int k = 0; k < titles.size(); k++) {                         Cell cell = row.getCell(k);                         String key = titles.get(k);                         String value = null;                         if (cell != null) {                             value = cell.toString();                         }                         rowMap.put(key, value);                     }                     sheetList.add(rowMap);                 }             }             result.add(sheetList);         }                   return result;     } catch (FileNotFoundException e) {         throw e;     } finally {         if (wb != null) {             wb.close();         }         if (is != null) {             is.close();         }     } }</string,></string,></string></string></map<string,></map<string,></list<map<string,string></list<map<string,></list<map<string,>

4、读取excel，适合于没有合并单元格且没有标题行的情况

 

 

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63

/**  * 适用于没有标题行的excel，例如  * 张三   25岁     男   175cm  * 李四   22岁     女   160cm  * 每一行构成一个map，key值是列标题，value是列值。没有值的单元格其value值为null  * 返回结果最外层的list对应一个excel文件，第二层的list对应一个sheet页，第三层的map对应sheet页中的一行  * @throws Exception  */ public static  List<list<list<string>>> readExcelWithoutTitle(String filepath) throws Exception{     String fileType = filepath.substring(filepath.lastIndexOf(".") + 1, filepath.length());     InputStream is = null;     Workbook wb = null;     try {         is = new FileInputStream(filepath);                   if (fileType.equals("xls")) {             wb = new HSSFWorkbook(is);         } else if  (fileType.equals("xlsx")) {             wb = new XSSFWorkbook(is);         } else {             throw new  Exception("读取的不是excel文件");         }                   List<list<list<string>>> result = new ArrayList<list<list<string>>>();//对应excel文件                   int sheetSize = wb.getNumberOfSheets();         for (int i = 0; i < sheetSize; i++) {//遍历sheet页             Sheet sheet = wb.getSheetAt(i);             List<list<string>> sheetList = new ArrayList<list<string>>();//对应sheet页                           int rowSize = sheet.getLastRowNum() + 1;             for (int j = 0; j < rowSize; j++) {//遍历行                 Row row = sheet.getRow(j);                 if (row == null) {//略过空行                     continue;                 }                 int cellSize = row.getLastCellNum();//行中有多少个单元格，也就是有多少列                 List<string> rowList = new ArrayList<string>();//对应一个数据行                 for (int k = 0; k < cellSize; k++) {                     Cell cell = row.getCell(k);                     String value = null;                     if (cell != null) {                         value = cell.toString();                     }                     rowList.add(value);                 }                 sheetList.add(rowList);             }             result.add(sheetList);         }                   return result;     } catch (FileNotFoundException e) {         throw e;     } finally {         if (wb != null) {             wb.close();         }         if (is != null) {             is.close();         }     } }</string></string></list<string></list<string></list<list<string></list<list<string></list<list<string>