导出exec，合并单元格

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;
	}

}