java读写excel（POI，支持xls和xlsx两种格式）

来源：互联网发布：java web truelicense 编辑：程序博客网时间：2024/04/28 05:22

原文链接

这应该是一个比较全的示例了，更加复杂的功能可以在此基础上扩展。此示例基于apache的POI类库，相关jar包就不列举了。这个类库很通用，网上很好找。

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
 */
publicstatic void writeExcel(OutputStream os, String excelExtName, Map<string, string="">>> data) throwsIOException{
    Workbook wb = null;
    try{
        if("xls".equals(excelExtName)) {
            wb = newHSSFWorkbook();
        }elseif ("xlsx".equals(excelExtName)) {
            wb = newXSSFWorkbook();
        }else{
            thrownew Exception("当前文件不是excel文件");
        }
        for(String sheetName : data.keySet()) {
            Sheet sheet = wb.createSheet(sheetName);
            List<list<string>> rowList = data.get(sheetName);
            for(inti = 0; i < rowList.size(); i++) {
                List<string> cellList = rowList.get(i);
                Row row = sheet.createRow(i);
                for(intj = 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
classExcelData{
    privateString value;//单元格的值
    privateint colSpan = 1;//单元格跨几列
    privateint rowSpan = 1;//单元格跨几行
    privateboolean alignCenter;//单元格是否居中，默认不居中，如果选择是，则水平和上下都居中
    publicboolean isAlignCenter() {
        returnalignCenter;
    }
    publicvoid setAlignCenter(booleanalignCenter) {
        this.alignCenter = alignCenter;
    }
    publicString getValue() {
        returnvalue;
    }
    publicvoid setValue(String value) {
        this.value = value;
    }
    publicint getColSpan() {
        returncolSpan;
    }
    publicvoid setColSpan(intcolSpan) {
        this.colSpan = colSpan;
    }
    publicint getRowSpan() {
        returnrowSpan;
    }
    publicvoid setRowSpan(introwSpan) {
        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
 */
publicstatic void testWrite(OutputStream os, String excelExtName, Map<string, exceldata="">>> data) throwsIOException{
    Workbook wb = null;
    CellStyle cellStyle = null;
    booleanisXls;
    try{
        if("xls".equals(excelExtName)) {
            wb = newHSSFWorkbook();
            isXls = true;
        }elseif ("xlsx".equals(excelExtName)) {
            wb = newXSSFWorkbook();
            isXls = false;
        }else{
            thrownew 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(inti = 0; i < rowList.size(); i++) {
                List<exceldata> cellList = rowList.get(i);
                Row row = sheet.createRow(i);
                intj = 0;//j 代表第几列 从0开始
                for(ExcelData excelData : cellList) {
                    if(excelData != null) {
                        if(excelData.getColSpan() > 1|| excelData.getRowSpan() > 1) {
                            CellRangeAddress cra = newCellRangeAddress(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
    publicstatic void main(String[] args) throwsIOException {
        Map<string, exceldata="">>> data = newLinkedHashMap<>();
        List<list<exceldata>> sheet1 = newArrayList<>();//第一页
         
        List<exceldata> list1 = newArrayList<>();//第一行
        ExcelData excelData = newExcelData();//第一个单元格
        excelData.setColSpan(6);
        excelData.setRowSpan(1);
        excelData.setValue("xxx");
        excelData.setAlignCenter(true);
        list1.add(excelData);
         
        List<exceldata> list2 = newArrayList<>();//第二行
        excelData = newExcelData();//第一个单元格
        excelData.setColSpan(1);
        excelData.setRowSpan(1);
        excelData.setValue("a");
        list2.add(excelData);
        excelData = newExcelData();//第二个单元格
        excelData.setColSpan(1);
        excelData.setRowSpan(1);
        excelData.setValue("b");
        list2.add(excelData);
        excelData = newExcelData();//第三个单元格
        excelData.setColSpan(2);
        excelData.setRowSpan(4);
        excelData.setValue("c");
        excelData.setAlignCenter(true);
        list2.add(excelData);
        excelData = newExcelData();//第四个单元格
        excelData.setColSpan(2);
        excelData.setRowSpan(2);
        excelData.setValue("d");
        excelData.setAlignCenter(true);
        list2.add(excelData);
         
        List<exceldata> list3 = newArrayList<>();//第三行
        excelData = newExcelData();//第一个单元格
        excelData.setColSpan(1);
        excelData.setRowSpan(1);
        excelData.setValue("e");
        list3.add(excelData);
        excelData = newExcelData();//第二个单元格
        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 = newArrayList<>();//第四行
        excelData = newExcelData();//第一个单元格
        excelData.setColSpan(1);
        excelData.setRowSpan(1);
        excelData.setValue("i");
        list4.add(excelData);
        excelData = newExcelData();//第二个单元格
        excelData.setColSpan(1);
        excelData.setRowSpan(1);
        excelData.setValue("j");
        list4.add(excelData);
        list4.add(null);//第三个单元格
        list4.add(null);//第四个单元格
        excelData = newExcelData();//第五个单元格
        excelData.setRowSpan(1);
        excelData.setColSpan(1);
        excelData.setValue("g");
        list4.add(excelData);
        excelData = newExcelData();//第六个单元格
        excelData.setRowSpan(1);
        excelData.setColSpan(1);
        excelData.setValue("h");
        list4.add(excelData);
         
        List<exceldata> list5 = newArrayList<>();//第五行
        excelData = newExcelData();//第一个单元格
        excelData.setColSpan(1);
        excelData.setRowSpan(1);
        excelData.setValue("k");
        list5.add(excelData);
        excelData = newExcelData();//第二个单元格
        excelData.setColSpan(1);
        excelData.setRowSpan(1);
        excelData.setValue("l");
        list5.add(excelData);
        list5.add(null);//第三个单元格
        list5.add(null);//第四个单元格
        excelData = newExcelData();//第五个单元格
        excelData.setRowSpan(1);
        excelData.setColSpan(1);
        excelData.setValue("m");
        list5.add(excelData);
        excelData = newExcelData();//第六个单元格
        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(newFileOutputStream(newFile("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 
 */
publicstatic List<list<map<string, string="">>> readExcelWithTitle(String filepath) throwsException{
    String fileType = filepath.substring(filepath.lastIndexOf(".") + 1, filepath.length());
    InputStream is = null;
    Workbook wb = null;
    try{
        is = newFileInputStream(filepath);
         
        if(fileType.equals("xls")) {
            wb = newHSSFWorkbook(is);
        }elseif (fileType.equals("xlsx")) {
            wb = newXSSFWorkbook(is);
        }else{
            thrownew Exception("读取的不是excel文件");
        }
         
        List<list<map<string, string="">>> result = newArrayList<list<map<string,string>>>();//对应excel文件
         
        intsheetSize = wb.getNumberOfSheets();
        for(inti = 0; i < sheetSize; i++) {//遍历sheet页
            Sheet sheet = wb.getSheetAt(i);
            List<map<string, string="">> sheetList = newArrayList<map<string, string="">>();//对应sheet页
             
            List<string> titles = newArrayList<string>();//放置所有的标题
             
            introwSize = sheet.getLastRowNum() + 1;
            for(intj = 0; j < rowSize; j++) {//遍历行
                Row row = sheet.getRow(j);
                if(row == null) {//略过空行
                    continue;
                }
                intcellSize = row.getLastCellNum();//行中有多少个单元格，也就是有多少列
                if(j == 0) {//第一行是标题行
                    for(intk = 0; k < cellSize; k++) {
                        Cell cell = row.getCell(k);
                        titles.add(cell.toString());
                    }
                }else{//其他行是数据行
                    Map<string, string=""> rowMap = newHashMap<string, string="">();//对应一个数据行
                    for(intk = 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);
        }
         
        returnresult;
    }catch(FileNotFoundException e) {
        throwe;
    }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 
 */
publicstatic List<list<list<string>>> readExcelWithoutTitle(String filepath) throwsException{
    String fileType = filepath.substring(filepath.lastIndexOf(".") + 1, filepath.length());
    InputStream is = null;
    Workbook wb = null;
    try{
        is = newFileInputStream(filepath);
         
        if(fileType.equals("xls")) {
            wb = newHSSFWorkbook(is);
        }elseif (fileType.equals("xlsx")) {
            wb = newXSSFWorkbook(is);
        }else{
            thrownew Exception("读取的不是excel文件");
        }
         
        List<list<list<string>>> result = newArrayList<list<list<string>>>();//对应excel文件
         
        intsheetSize = wb.getNumberOfSheets();
        for(inti = 0; i < sheetSize; i++) {//遍历sheet页
            Sheet sheet = wb.getSheetAt(i);
            List<list<string>> sheetList = newArrayList<list<string>>();//对应sheet页
             
            introwSize = sheet.getLastRowNum() + 1;
            for(intj = 0; j < rowSize; j++) {//遍历行
                Row row = sheet.getRow(j);
                if(row == null) {//略过空行
                    continue;
                }
                intcellSize = row.getLastCellNum();//行中有多少个单元格，也就是有多少列
                List<string> rowList = newArrayList<string>();//对应一个数据行
                for(intk = 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);
        }
         
        returnresult;
    }catch(FileNotFoundException e) {
        throwe;
    }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>

0 0