meidu2021/daju/target/classes/cn/com/lzt/excel/view/HglMapExcelView.java

package cn.com.lzt.excel.view;

import cn.com.lzt.excel.HGLExcelConstant;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.xssf.usermodel.*;
import org.jeecgframework.poi.excel.ExcelExportUtil;
import org.jeecgframework.poi.excel.entity.ExportParams;
import org.jeecgframework.poi.excel.entity.params.ExcelExportEntity;
import org.jeecgframework.poi.excel.entity.vo.MapExcelConstants;
import org.jeecgframework.poi.excel.view.MiniAbstractExcelView;
import org.springframework.stereotype.Controller;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.util.Collection;
import java.util.List;
import java.util.Map;

/**
 * Map 对象接口
 * 
 */
@SuppressWarnings("unchecked")
@Controller("hglexcelview")
public class HglMapExcelView extends MiniAbstractExcelView {

	public HglMapExcelView() {
		super();
	}

	@Override
	protected void renderMergedOutputModel(Map<String, Object> model, HttpServletRequest request, HttpServletResponse response) throws Exception {
		String codedFileName = "临时文件";
		Workbook workbook = ExcelExportUtil.exportExcel((ExportParams) model.get(MapExcelConstants.PARAMS), (List<ExcelExportEntity>) model.get(MapExcelConstants.ENTITY_LIST), (Collection<? extends Map<?, ?>>) model.get(MapExcelConstants.MAP_LIST));
		if (model.containsKey(MapExcelConstants.FILE_NAME)) {
			codedFileName = (String) model.get(MapExcelConstants.FILE_NAME);
		}
		if (workbook instanceof HSSFWorkbook) {
			codedFileName += HSSF;
		} else {
			codedFileName += XSSF;
		}
		if (isIE(request)) {
			codedFileName = java.net.URLEncoder.encode(codedFileName, "UTF8");
		} else {
			codedFileName = new String(codedFileName.getBytes("UTF-8"), "ISO-8859-1");
		}
		if (workbook instanceof HSSFWorkbook) {
			HSSFSheet sheet = ((HSSFWorkbook)workbook).getSheetAt(0);
			sheet.createFreezePane(4, 3);
			if(model.get(HGLExcelConstant.VALIDPARAM) != null) {
				List<Map<String,Object>> validParamM = (List<Map<String,Object>>)model.get(HGLExcelConstant.VALIDPARAM);
				String[] arr = {"A","B","C","D","E","F","G","H","I","J","K","L","M","N","O","P","Q","R","S","T","U","V","W","X","Y","Z"};
		        int index = 0;
				HSSFSheet hiddenSheet = ((HSSFWorkbook)workbook).createSheet("Sheet2"); 
				HSSFRow row = null;
				for(Map<String,Object> param :validParamM) {
					List<String> paramL = (List<String>)param.get(HGLExcelConstant.VALIDTIONSTRS);
			        // 准备下拉列表数据
			        String[] dlData = new String[paramL.size()];
			        paramL.toArray(dlData);
			        
			        if(dlData.length<5){ //255以内的下拉
		                //255以内的下拉,参数分别是：作用的sheet、下拉内容数组、起始行、终止行、起始列、终止列
				        sheet.addValidationData(setDataValidation(sheet, dlData, 
				        		(Integer)param.get(HGLExcelConstant.FIRSTROW), 
				        		(Integer)param.get(HGLExcelConstant.ENDROW), 
				        		(Integer)param.get(HGLExcelConstant.FIRSTCOL), 
				        		(Integer)param.get(HGLExcelConstant.ENDCOL))); //超过255个报错 
					} else { // 255以上的下拉，即下拉列表元素很多的情况
						// 1、设置有效性
						String strFormula = "Sheet2!$" + arr[index] + "$1:$" + arr[index] + "$"+dlData.length; // Sheet2第A1到A5000作为下拉列表来源数据
						hiddenSheet.setColumnWidth(0, 4000); // 设置每列的列宽
						// 设置数据有效性加载在哪个单元格上,参数分别是：从sheet2获取A1到A5000作为一个下拉的数据、起始行、终止行、起始列、终止列
						sheet.addValidationData(
								SetDataValidation(strFormula, (Integer) param.get(HGLExcelConstant.FIRSTROW),
										(Integer) param.get(HGLExcelConstant.ENDROW),
										(Integer) param.get(HGLExcelConstant.FIRSTCOL),
										(Integer) param.get(HGLExcelConstant.ENDCOL))); // 下拉列表元素很多的情况
	
						// 2、生成sheet2内容
						for (int j = 0; j < dlData.length; j++) {
							if (index == 0) { // 第1个下拉选项，直接创建行、列
								row = hiddenSheet.createRow(j); // 创建数据行
								hiddenSheet.setColumnWidth(j, 4000); // 设置每列的列宽
								row.createCell(0).setCellValue(dlData[j]); // 设置对应单元格的值
	
							} else { // 非第1个下拉选项
	
								int rowCount = hiddenSheet.getLastRowNum();
								// System.out.println("========== LastRowNum =========" + rowCount);
								if (j <= rowCount) { // 前面创建过的行，直接获取行，创建列
									// 获取行，创建列
									hiddenSheet.getRow(j).createCell(index).setCellValue(dlData[j]); // 设置对应单元格的值
	
								} else { // 未创建过的行，直接创建行、创建列
									hiddenSheet.setColumnWidth(j, 4000); // 设置每列的列宽
									// 创建行、创建列
									hiddenSheet.createRow(j).createCell(index).setCellValue(dlData[j]); // 设置对应单元格的值
								}
							}
						}
						index++;
					}
		        }
	//		        //将第二个sheet设置为隐藏
				workbook.setSheetHidden(1, true); 
			}
			if(model.get(HGLExcelConstant.CHECKPARAM) != null) {
				HSSFSheet checkSheet = ((HSSFWorkbook)workbook).createSheet("check"); 
				checkSheet.createRow(0).createCell(0).setCellValue(model.get(HGLExcelConstant.CHECKPARAM).toString());
				workbook.setSheetHidden(2, true); 
			}
			response.setHeader("content-disposition", "attachment;filename=" + codedFileName);
			ServletOutputStream out = response.getOutputStream();
			workbook.write(out);
			out.flush();
		}else if(workbook instanceof XSSFWorkbook) {
			XSSFSheet sheet = ((XSSFWorkbook)workbook).getSheetAt(0);
			sheet.createFreezePane(4, 3);
			if(model.get(HGLExcelConstant.VALIDPARAM) != null) {
				List<Map<String,Object>> validParamM = (List<Map<String,Object>>)model.get(HGLExcelConstant.VALIDPARAM);
				String[] arr = {"A","B","C","D","E","F","G","H","I","J","K","L","M","N","O","P","Q","R","S","T","U","V","W","X","Y","Z"};
		        int index = 0;
				XSSFSheet hiddenSheet = ((XSSFWorkbook)workbook).createSheet("Sheet2"); 
				XSSFRow row = null;
				for(Map<String,Object> param :validParamM) {
					List<String> paramL = (List<String>)param.get(HGLExcelConstant.VALIDTIONSTRS);
			        // 准备下拉列表数据
			        String[] dlData = new String[paramL.size()];
			        paramL.toArray(dlData);
			        
			        if(dlData.length<5){ //255以内的下拉
		                //255以内的下拉,参数分别是：作用的sheet、下拉内容数组、起始行、终止行、起始列、终止列
//			        	DataValidation dstDataValidation = helper.createValidation(helper.createExplicitListConstraint(dlData),
//			                    dstAddrList);
				        sheet.addValidationData(setDataValidation(sheet, dlData, 
				        		(Integer)param.get(HGLExcelConstant.FIRSTROW), 
				        		(Integer)param.get(HGLExcelConstant.ENDROW), 
				        		(Integer)param.get(HGLExcelConstant.FIRSTCOL), 
				        		(Integer)param.get(HGLExcelConstant.ENDCOL))); //超过255个报错 
					} else { // 255以上的下拉，即下拉列表元素很多的情况
						// 1、设置有效性
						String strFormula = "Sheet2!$" + arr[index] + "$1:$" + arr[index] + "$"+dlData.length; // Sheet2第A1到A5000作为下拉列表来源数据
						hiddenSheet.setColumnWidth(0, 4000); // 设置每列的列宽
						// 设置数据有效性加载在哪个单元格上,参数分别是：从sheet2获取A1到A5000作为一个下拉的数据、起始行、终止行、起始列、终止列
						sheet.addValidationData(
								SetDataValidation4XSSF(sheet, strFormula, (Integer) param.get(HGLExcelConstant.FIRSTROW),
										(Integer) param.get(HGLExcelConstant.ENDROW),
										(Integer) param.get(HGLExcelConstant.FIRSTCOL),
										(Integer) param.get(HGLExcelConstant.ENDCOL))); // 下拉列表元素很多的情况
	
						// 2、生成sheet2内容
						for (int j = 0; j < dlData.length; j++) {
							if (index == 0) { // 第1个下拉选项，直接创建行、列
								row = hiddenSheet.createRow(j); // 创建数据行
								hiddenSheet.setColumnWidth(j, 4000); // 设置每列的列宽
								row.createCell(0).setCellValue(dlData[j]); // 设置对应单元格的值
	
							} else { // 非第1个下拉选项
	
								int rowCount = hiddenSheet.getLastRowNum();
								// System.out.println("========== LastRowNum =========" + rowCount);
								if (j <= rowCount) { // 前面创建过的行，直接获取行，创建列
									// 获取行，创建列
									hiddenSheet.getRow(j).createCell(index).setCellValue(dlData[j]); // 设置对应单元格的值
	
								} else { // 未创建过的行，直接创建行、创建列
									hiddenSheet.setColumnWidth(j, 4000); // 设置每列的列宽
									// 创建行、创建列
									hiddenSheet.createRow(j).createCell(index).setCellValue(dlData[j]); // 设置对应单元格的值
								}
							}
						}
						index++;
					}
		        }
	//		        //将第二个sheet设置为隐藏
				workbook.setSheetHidden(1, true); 
			}
			if(model.get(HGLExcelConstant.CHECKPARAM) != null) {
				XSSFSheet checkSheet = ((XSSFWorkbook)workbook).createSheet("check"); 
				checkSheet.createRow(0).createCell(0).setCellValue(model.get(HGLExcelConstant.CHECKPARAM).toString());
				workbook.setSheetHidden(2, true); 
			}
			response.setHeader("content-disposition", "attachment;filename=" + codedFileName);
			ServletOutputStream out = response.getOutputStream();
			workbook.write(out);
			out.flush();
		}
	}
	
/**
 * 
 * @Title: SetDataValidation 
 * @Description: 下拉列表元素很多的情况 (255以上的下拉)
 * @param @param strFormula
 * @param @param firstRow   起始行
 * @param @param endRow     终止行
 * @param @param firstCol   起始列
 * @param @param endCol     终止列
 * @param @return
 * @return HSSFDataValidation
 * @throws
 */
private static HSSFDataValidation SetDataValidation(String strFormula, 
        int firstRow, int endRow, int firstCol, int endCol) {
    
    // 设置数据有效性加载在哪个单元格上。四个参数分别是：起始行、终止行、起始列、终止列
    CellRangeAddressList regions = new CellRangeAddressList(firstRow, endRow, firstCol, endCol);
    
    DVConstraint constraint = DVConstraint.createFormulaListConstraint(strFormula);
    HSSFDataValidation dataValidation = new HSSFDataValidation(regions,constraint);
    
    dataValidation.createErrorBox("Error", "Error");
    dataValidation.createPromptBox("", null);

    return dataValidation;
}

private static XSSFDataValidation SetDataValidation4XSSF(Sheet sheet, String strFormula, 
        int firstRow, int endRow, int firstCol, int endCol) {
	DataValidationHelper dvHelper = sheet.getDataValidationHelper();
    // 设置数据有效性加载在哪个单元格上。四个参数分别是：起始行、终止行、起始列、终止列
    CellRangeAddressList addressList = new CellRangeAddressList(firstRow, endRow, firstCol, endCol);
//    CTDataValidationImpl ctv = new CTDataValidationImpl(SchemaTypeImpl.);
//    dvHelper.createFormulaListConstraint
//    DVConstraint constraint = DVConstraint.createFormulaListConstraint(strFormula);
//    XSSFDataValidation dataValidation = new XSSFDataValidation(constraint,regions,);
//    
//    dataValidation.createErrorBox("Error", "Error");
//    dataValidation.setSuppressDropDownArrow(true);
//    dataValidation.createPromptBox("", null);
//
//    return dataValidation;
    XSSFDataValidationConstraint dvConstraint = (XSSFDataValidationConstraint) dvHelper.createFormulaListConstraint(strFormula);
	XSSFDataValidation validation = (XSSFDataValidation)dvHelper.createValidation(dvConstraint, addressList);
	validation.setShowErrorBox(true);
	validation.setSuppressDropDownArrow(true);
	validation.setShowPromptBox(true);
	return validation;
//	sheetTracing.addValidationData(validation);
}


/**
 * 
 * @Title: setDataValidation 
 * @Description: 下拉列表元素不多的情况(255以内的下拉)
 * @param @param sheet
 * @param @param textList
 * @param @param firstRow
 * @param @param endRow
 * @param @param firstCol
 * @param @param endCol
 * @param @return
 * @return DataValidation
 * @throws
 */
private static DataValidation setDataValidation(Sheet sheet, String[] textList, int firstRow, int endRow, int firstCol, int endCol) {

    DataValidationHelper helper = sheet.getDataValidationHelper();
    //加载下拉列表内容
    DataValidationConstraint constraint = helper.createExplicitListConstraint(textList);
    //DVConstraint constraint = new DVConstraint();
    constraint.setExplicitListValues(textList);
    
    //设置数据有效性加载在哪个单元格上。四个参数分别是：起始行、终止行、起始列、终止列
    CellRangeAddressList regions = new CellRangeAddressList((short) firstRow, (short) endRow, (short) firstCol, (short) endCol);

    //数据有效性对象
    DataValidation data_validation = helper.createValidation(constraint, regions);
    //DataValidation data_validation = new DataValidation(regions, constraint);

    return data_validation;
}

}