package com.pig4cloud.pigx.common.core.util;
import com.pig4cloud.pigx.common.core.entity.PoiModel;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
public class Tesdtsss {
/**
* @param headers 标题集合 tilte的长度应该与list中的model的属性个数一致
* @param dataset 内容集合
* @param mergeColumns 合并单元格的列
*/
public void createExcel(String[] headers, List<Map<String, String>> dataset, String[] mergeColumns, HttpServletResponse response, HSSFSheet sheet, HSSFWorkbook workbook , String title) {
if (headers.length == 0) {
throw new RuntimeException("请检查标题列，标题列为空");
}
try {
OutputStream output = response.getOutputStream();
response.reset();
response.setContentType("application/octet-stream; charset=UTF-8");
response.setHeader("Content-disposition",
"attachment; filename=" + URLEncoder.encode(title + ".xls"));
/*初始化head，填值标题行（第一行）*/
HSSFRow rowm = sheet.createRow(0); // 产生表格标题行
HSSFCell cellTiltle = rowm.createCell(0); //创建表格标题列
// sheet样式定义; getColumnTopStyle(); getStyle()均为自定义方法 --在下面,可扩展
HSSFCellStyle columnTopStyle = this.getColumnTopStyle(workbook ,15);// 获取列头样式对象
HSSFCellStyle style = this.getStyle(workbook); // 获取单元格样式对象
//合并表格标题行，合并列数为列名的长度,第一个0为起始行号，第二个1为终止行号，第三个0为起始列好，第四个参数为终止列号
sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, (headers.length - 1)));
cellTiltle.setCellStyle(columnTopStyle); //设置标题行样式
cellTiltle.setCellValue(title); //设置标题行值
Row row0 = sheet.createRow(1);
for (int i = 0; i < headers.length; i++) {
/*创建单元格，指定类型*/
Cell cell_1 = row0.createCell(i, Cell.CELL_TYPE_STRING);
cell_1.setCellStyle(getColumnTopStyle(workbook,15));
cell_1.setCellValue(headers[i]);
sheet.setColumnWidth(i,5000);
}
List<PoiModel> poiModels = new ArrayList<PoiModel>();
Iterator<Map<String, String>> iterator = dataset.iterator();
int index = 2; //这里2是从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 > 2) {
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 == 2) {
poiModel = new PoiModel();
poiModel.setOldContent(value);
poiModel.setContent(value);
poiModel.setRowIndex(2);
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 (null != content) {
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()+1) {
if (i == 0) {
if (content.equals(value)) {
cra = new CellRangeAddress(rowStartIndex, index, cellIndex, cellIndex);
sheet.addMergedRegion(cra);
}
} else if (i > 0) {
if (null != content) {
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++;
}
workbook.write(output);
output.flush();
output.close();
} catch (IOException e) {
e.printStackTrace();
}
}
/*
* 列头单元格样式
* @param i 字体大小
*/
private HSSFCellStyle getColumnTopStyle(HSSFWorkbook workbook , int i) {
// 设置字体
HSSFFont font = workbook.createFont();
// 设置字体大小
font.setFontHeightInPoints((short) i);
// 字体加粗
font.setBold(true);
// 设置字体名字
font.setFontName("Courier New");
// 设置样式;
HSSFCellStyle style = workbook.createCellStyle();
// 设置底边框;
style.setBorderBottom(BorderStyle.THIN);
// 设置底边框颜色;
style.setBottomBorderColor(HSSFColor.BLACK.index);
// 设置左边框;
style.setBorderLeft(BorderStyle.THIN);
// 设置左边框颜色;
style.setLeftBorderColor(HSSFColor.BLACK.index);
// 设置右边框;
style.setBorderRight(BorderStyle.THIN);
// 设置右边框颜色;
style.setRightBorderColor(HSSFColor.BLACK.index);
// 设置顶边框;
style.setBorderTop(BorderStyle.THIN);
// 设置顶边框颜色;
style.setTopBorderColor(HSSFColor.BLACK.index);
// 在样式用应用设置的字体;
style.setFont(font);
// 设置自动换行;
style.setWrapText(false);
// 设置水平对齐的样式为居中对齐;
style.setAlignment(HorizontalAlignment.CENTER);
// 设置垂直对齐的样式为居中对齐;
style.setVerticalAlignment(VerticalAlignment.CENTER);
return style;
}
/*
* 列数据信息单元格样式
*/
private HSSFCellStyle getStyle(HSSFWorkbook workbook) {
// 设置字体
HSSFFont font = workbook.createFont();
// 设置字体大小
font.setFontHeightInPoints((short) 10);
// 字体加粗
// font.setBold(true);
// 设置字体名字
font.setFontName("Courier New");
// 设置样式;
HSSFCellStyle style = workbook.createCellStyle();
// 设置底边框;
style.setBorderBottom(BorderStyle.THIN);
// 设置底边框颜色;
style.setBottomBorderColor(HSSFColor.BLACK.index);
// 设置左边框;
style.setBorderLeft(BorderStyle.THIN);
// 设置左边框颜色;
style.setLeftBorderColor(HSSFColor.BLACK.index);
// 设置右边框;
style.setBorderRight(BorderStyle.THIN);
// 设置右边框颜色;
style.setRightBorderColor(HSSFColor.BLACK.index);
// 设置顶边框;
style.setBorderTop(BorderStyle.THIN);
// 设置顶边框颜色;
style.setTopBorderColor(HSSFColor.BLACK.index);
// 在样式用应用设置的字体;
style.setFont(font);
// 设置自动换行;
style.setWrapText(false);
// 设置水平对齐的样式为居中对齐;
style.setAlignment(HorizontalAlignment.CENTER);
// 设置垂直对齐的样式为居中对齐;
style.setVerticalAlignment(VerticalAlignment.CENTER);
return style;
}
}
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
