之前在csdn上面也看到许多的使用poi导出excel，不过想对来说比较简单，本人查阅相关的资料，对于工作中遇到的问题，有一定的帮助，不过对于复杂的excel导出，就没办法解决，当然对于简单的excel导出，推荐使用freemarker的方式实现（前提是非动态的数据)，这类的资料 很多，就不一一阐述了，这里介绍使用poi导出动态的excel的方式。
首先，需要添加相关的poi的依赖，这里推荐大家一个远程依赖包的地址：http://mvnrepository.com/，在导航栏搜索你想要的技术名称，比如这里是poi：
这里推荐使用使用人数最多的一个选项：
这里有各种导入依赖的方式，其中省略了一步，图片太多就不贴出来了，请大家自行摸索。
解决了依赖问题之后：
这里写了一个测试类给大家方便大家观看：本人使用的是springboot的环境，所以有环境问题导致错误，需要自行解决，仅供参考流程。
public class WorkBookTest {
static final Logger log = LoggerFactory.getLogger(WorkBookTest.class);
@Test
public void testCellStyle(){
//创建HSSFWorkbook 对象
HSSFWorkbook hw = new HSSFWorkbook();
HSSFSheet sheet = hw.createSheet();
//创建单元格
HSSFRow row = sheet.createRow(1);
CellRangeAddress cra = new CellRangeAddress(1,3,1,3);
sheet.addMergedRegion(cra);
HSSFCell cell = row.createCell(1);
cell.setCellValue(121212);
//设置样式
HSSFCellStyle hssfCellStyle = hw.createCellStyle();
hssfCellStyle.setAlignment(HorizontalAlignment.CENTER); //设置居中样式
hssfCellStyle.setBorderBottom(BorderStyle.THIN);
hssfCellStyle.setBorderLeft(BorderStyle.THIN);
hssfCellStyle.setBorderRight(BorderStyle.THIN);
hssfCellStyle.setBorderTop(BorderStyle.THIN);
hssfCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);//设置上下居中
//添加边框，这个方法可以解决合并单元格没有边框的问题
for (int i = cra.getFirstRow(); i <=cra.getLastRow() ; i++) {
if (sheet.getRow(i)==null){
HSSFRow row1 = sheet.createRow(i);
}
HSSFRow row1 = sheet.getRow(i);
for (int j = cra.getFirstColumn(); j <= cra.getLastColumn(); j++) {
if (row1.getCell(j)==null){
HSSFCell cell1 = row1.createCell(j);
}
HSSFCell cell1 = row1.getCell(j);
cell1.setCellValue("");
cell1.setCellStyle(hssfCellStyle);
}
}
//文件名称，输出到项目中，浏览器需要使用request,和response的方式，请自行检索，很多！！！
String fileName = new Date().getTime() + "";
String filePath = "C:\\23\\tt_research\\tt_research\\src\\main\\resources\\";
File file = new File(filePath + fileName + ".xls");
try {
OutputStream os = new FileOutputStream(file);
hw.write(os);
log.info("test --> success");
} catch (Exception e) {
e.printStackTrace();
log.info("test --> failed");
}
}
}
通过简单的demo之后，你已经了解了具体的步骤，如何实现动态的单元格合并和插入数据的问题。
因为单元格能先合并，再插入数据，因此需要先合并单元格，再插入数据，
这里贴部分代码，本人代码比较多，以为数据类型很复杂，请看
int typeCount = baseCount+1;
int timeCount = baseCount;
int projectCount = baseCount;
int serialCount = baseCount;
这几个参数的变化情况，
/*
* 第二个方式，重新写用于实现数据单元格的合并
* */
private int setExcelStyleModel(Map<String,List<Map<String, List<Map<String, List<Map<String, List<ProductItemDto>>>>>>>> map,HSSFCellStyle hssfCellStyle,Integer baseCount,String typeName,HSSFSheet sheet){
//定义一些基础数据，用户获取最开始的row在哪一行
int typeCount = baseCount+1;
int timeCount = baseCount;
int projectCount = baseCount;
int serialCount = baseCount;
//拿到时间类型的集合
List<Map<String, List<Map<String, List<Map<String, List<ProductItemDto>>>>>>> timeMapList = map.get(typeName);
if (timeMapList.size() > 1){
//不止一种时间类型集合,接下来就不需要判断，因为处于不同的时间集合类型中
for (int i = 0; i < timeMapList.size(); i++) {
timeCount = baseCount+1;
Map<String, List<Map<String, List<Map<String, List<ProductItemDto>>>>>> timeMap = timeMapList.get(i);
Set<Map.Entry<String, List<Map<String, List<Map<String, List<ProductItemDto>>>>>>> entrySetProject = timeMap.entrySet();
for (Map.Entry<String, List<Map<String, List<Map<String, List<ProductItemDto>>>>>> entryProject : entrySetProject) {
List<Map<String, List<Map<String, List<ProductItemDto>>>>> projectNoMapList = entryProject.getValue();
//判断每个时间类型下面的projectNo是否只存在一个
if (projectNoMapList.size() >1){
for (int i1 = 0; i1 < projectNoMapList.size(); i1++) {
projectCount=baseCount+1;
Map<String, List<Map<String, List<ProductItemDto>>>> projectMap = projectNoMapList.get(i1);
Set<Map.Entry<String, List<Map<String, List<ProductItemDto>>>>> entrySetSerialNo = projectMap.entrySet();
for (Map.Entry<String, List<Map<String, List<ProductItemDto>>>> entrySerialNo : entrySetSerialNo) {
List<Map<String, List<ProductItemDto>>> serialNoMapList = entrySerialNo.getValue();
for (int i2 = 0; i2 < serialNoMapList.size(); i2++) {
serialCount=baseCount+1;
Map<String, List<ProductItemDto>> serialNoMap = serialNoMapList.get(i2);
Set<Map.Entry<String, List<ProductItemDto>>> entries = serialNoMap.entrySet();
for (Map.Entry<String, List<ProductItemDto>> entry : entries) {
List<ProductItemDto> productItemDtoList = entry.getValue();
for (int i3 = 0; i3 < productItemDtoList.size(); i3++) {
baseCount++;
}
}
//合并serialNo单元格
if (serialCount==baseCount){
continue;
}else {
CellRangeAddress cra = new CellRangeAddress(serialCount, baseCount, 3, 3);
sheet.addMergedRegion(cra);
setCellStyle(hssfCellStyle, sheet, cra);
}
}
}
//合并projectNo单元格
if (projectCount==baseCount){
continue;
}else{
CellRangeAddress cra = new CellRangeAddress(projectCount, baseCount, 2, 2);
sheet.addMergedRegion(cra);
setCellStyle(hssfCellStyle, sheet, cra);
}
}
}else {
//只有一种projectNoList类型的集合
final int projectCountTemp=baseCount+1;
Map<String, List<Map<String, List<ProductItemDto>>>> projectNoMap = projectNoMapList.get(0);
Set<Map.Entry<String, List<Map<String, List<ProductItemDto>>>>> entrySetSerialNo = projectNoMap.entrySet();
for (Map.Entry<String, List<Map<String, List<ProductItemDto>>>> entrySerialNo : entrySetSerialNo) {
List<Map<String, List<ProductItemDto>>> serialNoMapList = entrySerialNo.getValue();
//判断是否只有一个serialNo单据号
if (serialNoMapList.size() > 1){
//不存在一种项目号
for (int i1 = 0; i1 < serialNoMapList.size(); i1++) {
//从下一个单元格开始合并
serialCount=baseCount+1;
Map<String, List<ProductItemDto>> serialNoMap = serialNoMapList.get(i1);
Set<Map.Entry<String, List<ProductItemDto>>> entries = serialNoMap.entrySet();
for (Map.Entry<String, List<ProductItemDto>> entry : entries) {
List<ProductItemDto> productItemDtoList = entry.getValue();
for (int i2 = 0; i2 < productItemDtoList.size(); i2++) {
baseCount++;
}
}
//当行数不止一条的时候
if (serialCount==baseCount){
continue;
}else {
//存在不止一种serialNo的数据,每次遍历完之后就需要合并
CellRangeAddress cra = new CellRangeAddress(serialCount, baseCount, 3, 3);
sheet.addMergedRegion(cra);
setCellStyle(hssfCellStyle, sheet, cra);
}
}
}else {
//只存在一个serialNo的数据
serialCount=baseCount+1;
Map<String, List<ProductItemDto>> serialNoMap = serialNoMapList.get(0);
Set<Map.Entry<String, List<ProductItemDto>>> entries = serialNoMap.entrySet();
for (Map.Entry<String, List<ProductItemDto>> entry : entries) {
List<ProductItemDto> productItemDtoList = entry.getValue();
for (int i1 = 0; i1 < productItemDtoList.size(); i1++) {
baseCount++;
}
}
//合并serialNoCount单元格
if (serialCount==baseCount){
continue;
}else {
//只有一种serialNo类型的数据
CellRangeAddress cra = new CellRangeAddress(serialCount, baseCount, 3, 3);
sheet.addMergedRegion(cra);
setCellStyle(hssfCellStyle, sheet, cra);
}
}
}
//合并projectNo单元格
if (projectCountTemp==baseCount){
continue;
}else {
CellRangeAddress cra = new CellRangeAddress(projectCountTemp, baseCount, 2, 2);
sheet.addMergedRegion(cra);
setCellStyle(hssfCellStyle, sheet, cra);
}
}
}
//合并time
if (timeCount==baseCount){
continue;
}else {
CellRangeAddress cra = new CellRangeAddress(timeCount, baseCount, 1, 1);
sheet.addMergedRegion(cra);
setCellStyle(hssfCellStyle, sheet, cra);
}
}
}else {
timeCount=baseCount+1;
//只有一种时间类型集合
//判断时间类型集合里面是否只要一种project集合
Map<String, List<Map<String, List<Map<String, List<ProductItemDto>>>>>> timeMap = timeMapList.get(0);
//遍历timeMap
Set<Map.Entry<String, List<Map<String, List<Map<String, List<ProductItemDto>>>>>>> entrySetProject = timeMap.entrySet();
for (Map.Entry<String, List<Map<String, List<Map<String, List<ProductItemDto>>>>>> entryProject : entrySetProject) {
List<Map<String, List<Map<String, List<ProductItemDto>>>>> projectMapList = entryProject.getValue();
//判断是否只有一种类型的 projectMap
if (projectMapList.size() >1){
for (int i = 0; i < projectMapList.size(); i++) {
projectCount=baseCount+1;
Map<String, List<Map<String, List<ProductItemDto>>>> projectMap = projectMapList.get(i);
Set<Map.Entry<String, List<Map<String, List<ProductItemDto>>>>> entrySetSerialNo = projectMap.entrySet();
for (Map.Entry<String, List<Map<String, List<ProductItemDto>>>> entrySerialNo : entrySetSerialNo) {
List<Map<String, List<ProductItemDto>>> serialNoMapList = entrySerialNo.getValue();
for (int i1 = 0; i1 < serialNoMapList.size(); i1++) {
serialCount=baseCount+1;
Map<String, List<ProductItemDto>> serialNoMap = serialNoMapList.get(i1);
Set<Map.Entry<String, List<ProductItemDto>>> entries = serialNoMap.entrySet();
for (Map.Entry<String, List<ProductItemDto>> entry : entries) {
List<ProductItemDto> productItemDtoList = entry.getValue();
for (int i2 = 0; i2 < productItemDtoList.size(); i2++) {
baseCount++;
}
}
//合并serialNo单元格
if (serialCount==baseCount){
continue;
}else {
CellRangeAddress cra = new CellRangeAddress(serialCount, baseCount, 3, 3);
sheet.addMergedRegion(cra);
setCellStyle(hssfCellStyle, sheet, cra);
}
}
}
//合并projectNo单元格
if (projectCount==baseCount){
continue;
}else{
CellRangeAddress cra = new CellRangeAddress(projectCount, baseCount, 2, 2);
sheet.addMergedRegion(cra);
setCellStyle(hssfCellStyle, sheet, cra);
}
}
}else {
//只有一种projectNoList类型的集合
projectCount=baseCount+1;
Map<String, List<Map<String, List<ProductItemDto>>>> projectNoMap = projectMapList.get(0);
Set<Map.Entry<String, List<Map<String, List<ProductItemDto>>>>> entrySetSerialNo = projectNoMap.entrySet();
for (Map.Entry<String, List<Map<String, List<ProductItemDto>>>> entrySerialNo : entrySetSerialNo) {
List<Map<String, List<ProductItemDto>>> serialNoMapList = entrySerialNo.getValue();
//判断是否只有一个serialNo单据号
if (serialNoMapList.size() > 1){
//不存在一种项目号
for (int i = 0; i < serialNoMapList.size(); i++) {
//从下一个单元格开始合并
serialCount=baseCount+1;
Map<String, List<ProductItemDto>> serialNoMap = serialNoMapList.get(i);
Set<Map.Entry<String, List<ProductItemDto>>> entries = serialNoMap.entrySet();
for (Map.Entry<String, List<ProductItemDto>> entry : entries) {
List<ProductItemDto> productItemDtoList = entry.getValue();
for (int i1 = 0; i1 < productItemDtoList.size(); i1++) {
baseCount++;
}
}
//当行数不止一条的时候
if (serialCount==baseCount){
continue;
}else {
//存在不止一种serialNo的数据,每次遍历完之后就需要合并
CellRangeAddress cra = new CellRangeAddress(serialCount, baseCount, 3, 3);
sheet.addMergedRegion(cra);
setCellStyle(hssfCellStyle, sheet, cra);
}
}
}else {
//只存在一个serialNo的数据
serialCount=baseCount+1;
Map<String, List<ProductItemDto>> serialNoMap = serialNoMapList.get(0);
Set<Map.Entry<String, List<ProductItemDto>>> entries = serialNoMap.entrySet();
for (Map.Entry<String, List<ProductItemDto>> entry : entries) {
List<ProductItemDto> productItemDtoList = entry.getValue();
for (int i = 0; i < productItemDtoList.size(); i++) {
baseCount++;
}
}
//合并serialNoCount单元格
if (serialCount==baseCount){
continue;
}else {
//只有一种serialNo类型的数据
CellRangeAddress cra = new CellRangeAddress(serialCount, baseCount, 3, 3);
sheet.addMergedRegion(cra);
setCellStyle(hssfCellStyle, sheet, cra);
}
}
}
//合并projectNo单元格
if (projectCount==baseCount){
continue;
}else {
CellRangeAddress cra = new CellRangeAddress(projectCount, baseCount, 2, 2);
sheet.addMergedRegion(cra);
setCellStyle(hssfCellStyle, sheet, cra);
}
}
}
//只有一种时间类型，直接合并
if (timeCount==baseCount){
log.info("product-->只有一种时间类型");
}else {
CellRangeAddress cra = new CellRangeAddress(timeCount, baseCount, 1, 1);
sheet.addMergedRegion(cra);
setCellStyle(hssfCellStyle, sheet, cra);
}
}
//合并种类
if (typeCount==baseCount){
log.info("种类单元格，只有一条数据不需要合并");
}else {
CellRangeAddress cra = new CellRangeAddress(typeCount, baseCount, 0, 0);
sheet.addMergedRegion(cra);
setCellStyle(hssfCellStyle, sheet, cra);
}
//合并合计单元格
CellRangeAddress cra = new CellRangeAddress(baseCount+1, baseCount+1, 0, 6);
sheet.addMergedRegion(cra);
setCellStyle(hssfCellStyle, sheet, cra);
//返回baseCount数据
return ++baseCount;
}
最后插入数据：
注意这几条数据变化情况
timeRowCount++;
projectNoRowCount++;
serialNORowCount++;
baseCount++;
//将数据封装成大map的形式
private Integer getExcelModel(Map<String,List<Map<String, List<Map<String, List<Map<String, List<ProductItemDto>>>>>>>> map, Map<String, Double> goodsTotalMap,HSSFCellStyle hssfCellStyle, HSSFWorkbook hw, HSSFSheet sheet,Integer baseCount,String typeName) {
//用来计数，循环多少次
int serialNORowCount = 0;
int projectNoRowCount = 0;
int timeRowCount = 0;
//设置种类
HSSFRow row1 = sheet.createRow(baseCount+1);
HSSFCell cell = row1.createCell(0);
cell.setCellValue(typeName);
cell.setCellStyle(hssfCellStyle);
//设置完样式之后开始插入数据
//遍历timeMap
//行列数重新置零
int timeCount = baseCount + 1;
int projectNoCount = baseCount + 1;
int serialNoCount = baseCount + 1;
List<Map<String, List<Map<String, List<Map<String, List<ProductItemDto>>>>>>> timeMapList = map.get(typeName);
for (int i = 0; i < timeMapList.size(); i++) {
Map<String, List<Map<String, List<Map<String, List<ProductItemDto>>>>>> timeMap = timeMapList.get(i);
Set<Map.Entry<String, List<Map<String, List<Map<String, List<ProductItemDto>>>>>>> entrySetTime = timeMap.entrySet();
for (Map.Entry<String, List<Map<String, List<Map<String, List<ProductItemDto>>>>>> entryTime : entrySetTime) {
//遍历timeMap集合
String timeKey = entryTime.getKey();
List<Map<String, List<Map<String, List<ProductItemDto>>>>> projectMapList = entryTime.getValue();
for (int i1 = 0; i1 < projectMapList.size(); i1++) {
Map<String, List<Map<String, List<ProductItemDto>>>> projectMap = projectMapList.get(i1);
//遍历projectMap集合
Set<Map.Entry<String, List<Map<String, List<ProductItemDto>>>>> entrySetProject = projectMap.entrySet();
for (Map.Entry<String, List<Map<String, List<ProductItemDto>>>> entryProject : entrySetProject) {
String projectKey = entryProject.getKey();
List<Map<String, List<ProductItemDto>>> serialNoMapList = entryProject.getValue();
for (int i2 = 0; i2 < serialNoMapList.size(); i2++) {
Map<String, List<ProductItemDto>> serialNoMap = serialNoMapList.get(i2);
Set<Map.Entry<String, List<ProductItemDto>>> entrySetSerialNo = serialNoMap.entrySet();
//遍历serialNoMap集合
for (Map.Entry<String, List<ProductItemDto>> entrySerialNo : entrySetSerialNo) {
String serialNoKey = entrySerialNo.getKey();
List<ProductItemDto> productItemDtoList = entrySerialNo.getValue();
for (int i3 = 0; i3 < productItemDtoList.size(); i3++) {
timeRowCount++;
projectNoRowCount++;
serialNORowCount++;
baseCount++;
//判断创建的列是否存在
if (sheet.getRow(baseCount)!=null){
//存在的话，直接插入数据
HSSFRow row2 = sheet.getRow(baseCount);
HSSFCell cell4 = row2.createCell(4);
cell4.setCellValue(productItemDtoList.get(i3).getSupply());
cell4.setCellStyle(hssfCellStyle);
HSSFCell cell5 = row2.createCell(5);
cell5.setCellValue(productItemDtoList.get(i3).getTotal());
cell5.setCellStyle(hssfCellStyle);
HSSFCell cell6 = row2.createCell(6);
cell6.setCellValue(productItemDtoList.get(i3).getBatchCode());
cell6.setCellStyle(hssfCellStyle);
}else {
//创建新的列对象
HSSFRow row2 = sheet.createRow(baseCount);
HSSFCell cell4 = row2.createCell(4);
cell4.setCellValue(productItemDtoList.get(i3).getSupply());
cell4.setCellStyle(hssfCellStyle);
HSSFCell cell5 = row2.createCell(5);
cell5.setCellValue(productItemDtoList.get(i3).getTotal());
cell5.setCellStyle(hssfCellStyle);
HSSFCell cell6 = row2.createCell(6);
cell6.setCellValue(productItemDtoList.get(i3).getBatchCode());
cell6.setCellStyle(hssfCellStyle);
}
}
//因为从右到左插入，肯定有数据，row已经创建可以直接获取
HSSFRow row2 = sheet.getRow(serialNoCount);
HSSFCell cell3 = row2.createCell(3);
cell3.setCellValue(serialNoKey);
//用于记录serial中有多少个数据rowCount
serialNoCount+=serialNORowCount;
cell3.setCellStyle(hssfCellStyle);
//每次遍历完成之后都需要置位为0，不然下次会出问题
serialNORowCount=0;
}
}
HSSFRow row2 = sheet.getRow(projectNoCount);
HSSFCell cell2 = row2.createCell(2);
cell2.setCellValue(projectKey);
projectNoCount+=projectNoRowCount;
cell2.setCellStyle(hssfCellStyle);
projectNoRowCount=0;
}
}
HSSFRow row2 = sheet.getRow(timeCount);
HSSFCell cell1 = row2.createCell(1);
cell1.setCellValue(timeKey);
timeCount+=timeRowCount;
cell1.setCellStyle(hssfCellStyle);
timeRowCount=0;
}
}
//设置总记录数
HSSFRow rowTotal = sheet.createRow(++baseCount);
HSSFCell cellTotal = rowTotal.createCell(0);
Double total = goodsTotalMap.get("total");
//对取出的total处理，保留三位有效数字
Double totalCount = new Double(Math.ceil(total*1000))/1000.0;
//这里只对总计操作
for (int i = baseCount;i<baseCount+1;i++){
HSSFRow row = sheet.getRow(i);
for (int j = 0;j <= 6;j++){
if (row.getCell(j)==null){
HSSFCell cell1 = row.createCell(j);
}
HSSFCell cell1 = row.getCell(j);
cell1.setCellValue("");
cell1.setCellStyle(hssfCellStyle);
}
cellTotal.setCellValue("总计 " + totalCount);
}
return baseCount;
}
最后给大家看一下效果：
祝大家工作生活愉快！！。本人第一次写文章，可能很多地方不是很清楚，逻辑也比较混乱，望理解。
扫一扫
465
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
