Java poi生成Excel并合并内容相同的行

116 阅读 0 评论 77 点赞

我是靠谱客的博主俊秀刺猬，这篇文章主要介绍Java poi生成Excel并合并内容相同的行，现在分享给大家，希望可以做个参考。

近日项目中提出需求，需要导出Excel，最开始以为很简单，也没说明需要合并相同内容的行，于是偷个懒，使用原来写好的工具类直接导出。写好后以为okay了，结果，竟然又提供了另一个版本的模板，需要合并内容相同的列，麻烦来了，于是网上各种搜寻，最后找到一个例子，效果是我想要的，感谢大神的贡献，附上链接如下：

https://blog.csdn.net/qq_33142257/article/details/64929145

于是取下来先看看效果，然后再这基础上自己改进了一下做出了自己想要的效果，如下：

记录一下，以后备用

首先在maven工程的pom.xml文件中添加

复制代码

1
2
3
4
5
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>3.14</version>
</dependency>

复制代码

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
package com.test.testexcel;

public class PoiModel {
    private String content;  
	  
    private String oldContent;  
  
    private int rowIndex;  
  
    private int cellIndex;  
  
    public String getOldContent() {  
        return oldContent;  
    }  
  
    public void setOldContent(String oldContent) {  
        this.oldContent = oldContent;  
    }  
  
    public String getContent() {  
        return content;  
    }  
  
    public void setContent(String content) {  
        this.content = content;  
    }  
  
    public int getRowIndex() {  
        return rowIndex;  
    }  
  
    public void setRowIndex(int rowIndex) {  
        this.rowIndex = rowIndex;  
    }  
  
    public int getCellIndex() {  
        return cellIndex;  
    }  
  
    public void setCellIndex(int cellIndex) {  
        this.cellIndex = cellIndex;  
    }
}

复制代码

package com.test.testexcel;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

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.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import com.test.testexcel.PoiModel;

public class Test {
	/** 
     * @param headers 标题集合 tilte的长度应该与list中的model的属性个数一致 
     * @param dataset 内容集合 
     * @param mergeColumns 合并单元格的列 
     */  
    public static String createExcel(String[] headers, List<Map<String, String>> dataset, String[] mergeColumns) {  
        if (headers.length==0){  
            return null;  
        }  
        /*初始化excel模板*/  
        Workbook workbook = new XSSFWorkbook();  
        Sheet sheet = null;  
        try {  
            sheet = workbook.createSheet();  
        }catch (Exception e){  
            e.printStackTrace();  
        }  
        
        /*初始化head，填值标题行（第一行）*/  
        Row row0 = sheet.createRow(0);  
        for(int i = 0; i < headers.length; i++){  
            /*创建单元格，指定类型*/  
            Cell cell_1 = row0.createCell(i, Cell.CELL_TYPE_STRING);  
            cell_1.setCellValue(headers[i]);  
        } 
        
        List<PoiModel> poiModels = new ArrayList<PoiModel>();  
        Iterator<Map<String, String>> iterator = dataset.iterator();  
        int index = 1;	//这里1是从excel的第二行开始，第一行已经塞入标题了  
        while (iterator.hasNext()){  
            Row row = sheet.createRow(index);  
            // 取得当前这行的map，该map中以key，value的形式存着这一行值
            Map<String, String> map = iterator.next();  
            // 循环列数，给当前行塞值  
            for(int i = 0; i < headers.length; i++) {  
                String old = "";  
                // old存的是上一行统一位置的单元的值，第一行是最上一行了，所以从第二行开始记
                if(index > 1) {  
                    old = poiModels.get(i) == null ? "" : poiModels.get(i).getContent();  
                }  
                
                String value = map.get(headers[i]);
                CellRangeAddress cra = null;
            	// 循环需要合并的列  
            	for(int j = 0; j < mergeColumns.length; j++){ 
            		PoiModel poiModel = null;
            		if(index == 1) {  
            			poiModel = new PoiModel();  
            			poiModel.setOldContent(value);  
            			poiModel.setContent(value);  
            			poiModel.setRowIndex(1);  
            			poiModel.setCellIndex(i);  
            			poiModels.add(poiModel);  
            			old = value;
            			break;  
            		}  
            		poiModel = poiModels.get(i);
            		
            		int rowStartIndex = poiModel.getRowIndex();
            		int rowEndIndex = index - 1;
            		int cellIndex = poiModel.getCellIndex();
            		String content = poiModel.getContent();
            		String preOldContent = poiModels.get(0).getOldContent();
            		String preValue = map.get(headers[0]);
            		Boolean isHeaderEquals = mergeColumns[j].equals(headers[i]);
            		
            		if(i == 0 && isHeaderEquals && !content.equals(value)) {  
            			if (rowStartIndex != rowEndIndex) {
            				cra=new CellRangeAddress(rowStartIndex, rowEndIndex, cellIndex, cellIndex);  
            				sheet.addMergedRegion(cra);  
            			}
            			// 重新记录该列的内容为当前内容，行标记改为当前行标记  
            			poiModel.setContent(value);  
            			poiModel.setRowIndex(index);  
            			poiModel.setCellIndex(i);  
            		} else if (i > 0 && isHeaderEquals) {
            			if(!content.equals(value) || (content.equals(value) && !preOldContent.equals(preValue))) {  
            				if (rowStartIndex != rowEndIndex) {
            					cra = new CellRangeAddress(rowStartIndex, rowEndIndex, cellIndex, cellIndex);  
                            	sheet.addMergedRegion(cra);  
                            }
                            poiModels.get(i).setContent(value);  
                            poiModels.get(i).setRowIndex(index);  
                            poiModels.get(i).setCellIndex(i);  
                        } 
            		}
            		if(isHeaderEquals && index == dataset.size()) {
            			if (i == 0) {
            				if (content.equals(value)) {
            					cra = new CellRangeAddress(rowStartIndex, index, cellIndex, cellIndex);  
                				sheet.addMergedRegion(cra);
            				}
            			} else if (i > 0) {
            				if (content.equals(value) && preOldContent.equals(preValue)) {
            					cra = new CellRangeAddress(rowStartIndex, index, cellIndex, cellIndex);  
                				sheet.addMergedRegion(cra);
            				}
            			}
                    } 
            	}  
                Cell cell = row.createCell(i, Cell.CELL_TYPE_STRING);  
                cell.setCellValue(value);  
            	// 在每一个单元格处理完成后，把这个单元格内容设置为old内容  
            	poiModels.get(i).setOldContent(old);  
            }  
            index++;  
        }  
        
        /*生成临时文件*/  
        FileOutputStream out = null;  
        String localPath = null;  
        File tempFile = null;  
        String fileName = String.valueOf(new Date().getTime()/1000);  
        try {  
            tempFile = File.createTempFile(fileName, ".xlsx");  
            localPath = tempFile.getAbsolutePath();  
            out = new FileOutputStream(localPath);  
            workbook.write(out);  
        } catch (IOException e){  
            e.printStackTrace();  
        } finally {  
            try {  
                out.flush();  
                out.close();  
            } catch (IOException e) {  
                e.printStackTrace();  
            }  
        }  
        return localPath;  
    }  
  
    public static void main(String[] args) throws IOException{
    	String[] headers = {"单位名称", "IP", "IP分类", "危险程度"};    
        List<Map<String, String>> list = new ArrayList<Map<String, String>>();    
          
        Map<String, String> map = new HashMap<String, String>();    
        map.put("单位名称","test");    
        map.put("IP","10.130.138.96");    
        map.put("IP分类","主机IP");  
        map.put("危险程度","高危");  
        list.add(map);   
        map = new HashMap<String, String>();    
        map.put("单位名称","test");    
        map.put("IP","10.130.138.96");    
        map.put("IP分类","主机IP");    
        map.put("危险程度","高危");  
        list.add(map);  
        list.add(map);  
        map = new HashMap<String, String>();    
        map.put("单位名称","test111");    
        map.put("IP","10.130.138.96");    
        map.put("IP分类","主机IP");  
        map.put("危险程度","高危");  
        list.add(map);  
        list.add(map);  
        list.add(map);  
        list.add(map);  
        String[] regions = new String[]{"单位名称", "IP", "IP分类", "危险程度"};  
        System.out.println(createExcel(headers, list, regions));
    }  
}

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
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
package com.test.testexcel;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

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.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import com.test.testexcel.PoiModel;  

public class Test {
	/** 
     * @param headers 标题集合 tilte的长度应该与list中的model的属性个数一致 
     * @param dataset 内容集合 
     * @param mergeColumns 合并单元格的列 
     */  
    public static String createExcel(String[] headers, List<Map<String, String>> dataset, String[] mergeColumns) {  
        if (headers.length==0){  
            return null;  
        }  
        /*初始化excel模板*/  
        Workbook workbook = new XSSFWorkbook();  
        Sheet sheet = null;  
        try {  
            sheet = workbook.createSheet();  
        }catch (Exception e){  
            e.printStackTrace();  
        }  
        
        /*初始化head，填值标题行（第一行）*/  
        Row row0 = sheet.createRow(0);  
        for(int i = 0; i < headers.length; i++){  
            /*创建单元格，指定类型*/  
            Cell cell_1 = row0.createCell(i, Cell.CELL_TYPE_STRING);  
            cell_1.setCellValue(headers[i]);  
        } 
        
        List<PoiModel> poiModels = new ArrayList<PoiModel>();  
        Iterator<Map<String, String>> iterator = dataset.iterator();  
        int index = 1;	//这里1是从excel的第二行开始，第一行已经塞入标题了  
        while (iterator.hasNext()){  
            Row row = sheet.createRow(index);  
            // 取得当前这行的map，该map中以key，value的形式存着这一行值
            Map<String, String> map = iterator.next();  
            // 循环列数，给当前行塞值  
            for(int i = 0; i < headers.length; i++) {  
                String old = "";  
                // old存的是上一行统一位置的单元的值，第一行是最上一行了，所以从第二行开始记
                if(index > 1) {  
                    old = poiModels.get(i) == null ? "" : poiModels.get(i).getContent();  
                }  
                
                String value = map.get(headers[i]);
                CellRangeAddress cra = null;
            	// 循环需要合并的列  
            	for(int j = 0; j < mergeColumns.length; j++){ 
            		PoiModel poiModel = null;
            		if(index == 1) {  
            			poiModel = new PoiModel();  
            			poiModel.setOldContent(value);  
            			poiModel.setContent(value);  
            			poiModel.setRowIndex(1);  
            			poiModel.setCellIndex(i);  
            			poiModels.add(poiModel);  
            			old = value;
            			break;  
            		}  
            		poiModel = poiModels.get(i);
            		
            		int rowStartIndex = poiModel.getRowIndex();
            		int rowEndIndex = index - 1;
            		int cellIndex = poiModel.getCellIndex();
            		String content = poiModel.getContent();
            		String preOldContent = poiModels.get(0).getOldContent();
            		String preValue = map.get(headers[0]);
            		Boolean isHeaderEquals = mergeColumns[j].equals(headers[i]);
            		
            		if(i == 0 && isHeaderEquals && !content.equals(value)) {  
            			if (rowStartIndex != rowEndIndex) {
            				cra=new CellRangeAddress(rowStartIndex, rowEndIndex, cellIndex, cellIndex);  
            				sheet.addMergedRegion(cra);  
            			}
            			// 重新记录该列的内容为当前内容，行标记改为当前行标记  
            			poiModel.setContent(value);  
            			poiModel.setRowIndex(index);  
            			poiModel.setCellIndex(i);  
            		} else if (i > 0 && isHeaderEquals) {
            			if(!content.equals(value) || (content.equals(value) && !preOldContent.equals(preValue))) {  
            				if (rowStartIndex != rowEndIndex) {
            					cra = new CellRangeAddress(rowStartIndex, rowEndIndex, cellIndex, cellIndex);  
                            	sheet.addMergedRegion(cra);  
                            }
                            poiModels.get(i).setContent(value);  
                            poiModels.get(i).setRowIndex(index);  
                            poiModels.get(i).setCellIndex(i);  
                        } 
            		}
            		if(isHeaderEquals && index == dataset.size()) {
            			if (i == 0) {
            				if (content.equals(value)) {
            					cra = new CellRangeAddress(rowStartIndex, index, cellIndex, cellIndex);  
                				sheet.addMergedRegion(cra);
            				}
            			} else if (i > 0) {
            				if (content.equals(value) && preOldContent.equals(preValue)) {
            					cra = new CellRangeAddress(rowStartIndex, index, cellIndex, cellIndex);  
                				sheet.addMergedRegion(cra);
            				}
            			}
                    } 
            	}  
                Cell cell = row.createCell(i, Cell.CELL_TYPE_STRING);  
                cell.setCellValue(value);  
            	// 在每一个单元格处理完成后，把这个单元格内容设置为old内容  
            	poiModels.get(i).setOldContent(old);  
            }  
            index++;  
        }  
        
        /*生成临时文件*/  
        FileOutputStream out = null;  
        String localPath = null;  
        File tempFile = null;  
        String fileName = String.valueOf(new Date().getTime()/1000);  
        try {  
            tempFile = File.createTempFile(fileName, ".xlsx");  
            localPath = tempFile.getAbsolutePath();  
            out = new FileOutputStream(localPath);  
            workbook.write(out);  
        } catch (IOException e){  
            e.printStackTrace();  
        } finally {  
            try {  
                out.flush();  
                out.close();  
            } catch (IOException e) {  
                e.printStackTrace();  
            }  
        }  
        return localPath;  
    }  
  
    public static void main(String[] args) throws IOException{
    	String[] headers = {"单位名称", "IP", "IP分类", "危险程度"};    
        List<Map<String, String>> list = new ArrayList<Map<String, String>>();    
          
        Map<String, String> map = new HashMap<String, String>();    
        map.put("单位名称","test");    
        map.put("IP","10.130.138.96");    
        map.put("IP分类","主机IP");  
        map.put("危险程度","高危");  
        list.add(map);   
        map = new HashMap<String, String>();    
        map.put("单位名称","test");    
        map.put("IP","10.130.138.96");    
        map.put("IP分类","主机IP");    
        map.put("危险程度","高危");  
        list.add(map);  
        list.add(map);  
        map = new HashMap<String, String>();    
        map.put("单位名称","test111");    
        map.put("IP","10.130.138.96");    
        map.put("IP分类","主机IP");  
        map.put("危险程度","高危");  
        list.add(map);  
        list.add(map);  
        list.add(map);  
        list.add(map);  
        String[] regions = new String[]{"单位名称", "IP", "IP分类", "危险程度"};  
        System.out.println(createExcel(headers, list, regions));
    }  
}