package com.ruoyi.trade.service.impl;

import java.io.OutputStream;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.springframework.stereotype.Service;

import com.ruoyi.report.service.impl.AbstractExcelExp;
import com.ruoyi.reportrzs.domain.RzsApplicantRankingInfo;
import com.ruoyi.trade.domain.CTrademark;
import com.ruoyi.trade.service.ICTrademarkExcel;
import com.ruoyi.trade.vo.TrademarkDTO;
import com.ruoyi.trade.vo.TrademarkVO;

@Service
public class CTrademarkExcelImpl extends AbstractExcelExp implements ICTrademarkExcel {

	@Override
	public void exportReportApply(OutputStream out, CTrademark req, TrademarkDTO dto) {

		SXSSFWorkbook wb = new SXSSFWorkbook(500);

		try {
			// response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
			// response.setCharacterEncoding("utf-8");
			short headHeight = 30 * 20;
			int minWidth = "四个字宽".getBytes().length * 256;
			String title = "中国商标申请统计报表";

			int dataHeight = 30;

			SXSSFSheet sheet = wb.createSheet();
			wb.setSheetName(0, title);
			sheet.trackAllColumnsForAutoSizing();

			Map<String, XSSFCellStyle> styles = createStyles(wb);

			int column = 0;
			int row = 0;
			Row titleRow0 = sheet.createRow(row);
			titleRow0.setHeightInPoints(dataHeight);
			Cell titleCell = titleRow0.createCell(column);
			titleCell.setCellStyle(styles.get("title"));
			titleCell.setCellValue(title);

			int titleSize = 0;
			if (dto.getYears() != null && dto.getYears().size() > 0) {
				int size = dto.getYears().size();
				titleSize = titleSize + size;
			}

			// 合并单元格语法： 开始行、结束行、开始列、结束列
			// 对应代码：new CellRangeAddress(startRowIndex, rowIndex - 1, i, i);
			sheet.addMergedRegion(
					new CellRangeAddress(titleRow0.getRowNum(), titleRow0.getRowNum(), column, titleSize));

			row++;
			Row titleRow1 = sheet.createRow(row);
			titleRow1.setHeight(headHeight);
			String val = "地区";
			XSSFCellStyle header = styles.get("header");
			createCell(titleRow1, column, val, header);
			CellRangeAddress titleRowRange = new CellRangeAddress(titleRow1.getRowNum(), titleRow1.getRowNum() + 1, 0,
					0);
			sheet.addMergedRegion(titleRowRange);

			val = "统计时期";
			if (StringUtils.isNotBlank(req.getCountTimeStr())) {
				val = req.getCountTimeStr();
			}
			column++;
			createCell(titleRow1, column, val, header);
			
			CellRangeAddress titleRange1 = null;
			if(dto.getYears().size()>1) {
				titleRange1 = new CellRangeAddress(titleRow1.getRowNum(), titleRow1.getRowNum(), column, titleSize);
				sheet.addMergedRegion(titleRange1);
			}
			

			row++;
			Row titleRow2 = sheet.createRow(row);
			titleRow2.setHeight(headHeight);
			column = 0;
			for (int i = 0; i < dto.getYears().size(); i++) {
				column++;
				createCell(titleRow2, column, dto.getYears().get(i), header);
			}

			if(titleRange1!=null) {
				setRegionStyle(sheet, titleRange1, styles.get("header"));
			}
			setRegionStyle(sheet, titleRowRange, styles.get("header"));

			// 冻结
			sheet.createFreezePane(0, 3, 0, 3);

			XSSFCellStyle data = styles.get("data");

			for (int i = 0; i < dto.getList().size(); i++) {
				TrademarkVO vo = dto.getList().get(i);

				Row dataRow = sheet.createRow(3 + i);
				dataRow.setHeightInPoints(dataHeight);

				column = 0;
				createCell(dataRow, column, vo.getAreaName(), data);
				for (int y = 0; y < dto.getYears().size(); y++) {
					String year = dto.getYears().get(y);
					if (vo.getTotals() == null || !vo.getTotals().containsKey(year)) {
						vo.getTotals().put(year, 0L);
					}
					column++;
					createCell(dataRow, column, vo.getTotals().get(year), data);
				}

			}

			autoColumnWidthForChineseChar(sheet, 0, titleSize + 1, (minWidth / 2 * 3), (minWidth / 2 * 3));

		} finally {
			writeToOut(wb, out);
		}

	}

	@Override
	public void exportReportNcGrowth(OutputStream out, CTrademark req, Map<String, Object> map) {
		// TODO Auto-generated method stub

		SXSSFWorkbook wb = new SXSSFWorkbook(500);

		try {
			// response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
			// response.setCharacterEncoding("utf-8");
			short headHeight = 30 * 20;
			int minWidth = "四个字宽".getBytes().length * 256;
			String title = "中国商标-尼斯分类增长率报表";
			
			int dataHeight = 30;

			SXSSFSheet sheet = wb.createSheet();
			wb.setSheetName(0, title);
			sheet.trackAllColumnsForAutoSizing();

			Map<String, XSSFCellStyle> styles = createStyles(wb);
			XSSFCellStyle header=styles.get("header");
			XSSFCellStyle data=styles.get("data");
			XSSFCellStyle data2=styles.get("data2");
			
			Row titleRow0 = sheet.createRow(0);
			titleRow0.setHeightInPoints(dataHeight);
			Cell titleCell = titleRow0.createCell(0);
			titleCell.setCellStyle(styles.get("title"));
			titleCell.setCellValue(title);
			int titleSize = 3;
			List<String> areaList=(List<String>)map.get("areaNames");
			if(areaList!=null) {
				titleSize=titleSize+areaList.size();
			}else {
				areaList=new ArrayList<String>();
			}
			// 合并单元格语法： 开始行、结束行、开始列、结束列
			// 对应代码：new CellRangeAddress(startRowIndex, rowIndex - 1, i, i);
			sheet.addMergedRegion(new CellRangeAddress(titleRow0.getRowNum(), titleRow0.getRowNum(),0, titleSize-1));
			
			
			int column=0;
			Row titleRow1 = sheet.createRow(1);
			titleRow1.setHeight(headHeight);
			createCell(titleRow1, column, "尼斯类别号", header);
			CellRangeAddress titleRegion1 = new CellRangeAddress(titleRow1.getRowNum(), titleRow1.getRowNum()+1, column, column);
			sheet.addMergedRegion(titleRegion1);
			
			column++;
			createCell(titleRow1, column, "尼斯类别名称", header);
			CellRangeAddress titleRegion2 = new CellRangeAddress(titleRow1.getRowNum(), titleRow1.getRowNum()+1, column, column);
			sheet.addMergedRegion(titleRegion2);
			
			column++;
			createCell(titleRow1, column, "数据", header);
			
			String queryType=req.getQueryType();
			String queryTypeStr="";
			if("act".equals(queryType)) {
				queryTypeStr="有效";
			}else if("reg".equals(queryType)) {
				queryTypeStr="注册";
			}else if("act".equals(queryType)) {
				queryTypeStr="申请";
			}
			
			column++;
			createCell(titleRow1, column, queryTypeStr, header);
			CellRangeAddress titleRegion3 = null;
			if(areaList.size()>1) {
				titleRegion3 = new CellRangeAddress(titleRow1.getRowNum(), titleRow1.getRowNum(), column, column+areaList.size()-1);
				sheet.addMergedRegion(titleRegion3);
			}
		
			
			column=2;
			Row titleRow2 = sheet.createRow(2);
			titleRow2.setHeight(headHeight);
			createCell(titleRow2, column, "对比日期", header);
			
			for(int i=0;i<areaList.size();i++) {
				String area=areaList.get(i);
				column++;
				createCell(titleRow2, column, area, header);
			}
			
			setRegionStyle(sheet, titleRegion1, styles.get("header"));
			setRegionStyle(sheet, titleRegion2, styles.get("header"));
			if(titleRegion3!=null) {
				setRegionStyle(sheet, titleRegion3, styles.get("header"));
			}
			
			// 冻结
			sheet.createFreezePane(0, 3, 0, 3);
			List<TrademarkDTO> list=(List<TrademarkDTO>)map.get("dataList");
			
//			TODO	
			if(list==null) {
				list=new ArrayList<TrademarkDTO>();
			}
			
			int row=2;
			int ncStrColumn=1;
			for(int i=0;i<list.size();i++) {
				TrademarkDTO vo=list.get(i);
				row++;
				Row dataRow = sheet.createRow(row);
				dataRow.setHeightInPoints(dataHeight*2);
				
				column = 0;
				createCell(dataRow, column++, vo.getNc(), data);
				createCell(dataRow, column++, vo.getNcStr(), data2);
				doCreateCellOneRowNc(dataRow, column, vo.getNcItem1(),areaList, data);

				
				row++;
				Row dataRow2 = sheet.createRow(row);
				dataRow2.setHeightInPoints(dataHeight*2);
				column = 0;
				createCell(dataRow2, column++, "", data);
				createCell(dataRow2, column++, "", data);
				doCreateCellOneRowNc(dataRow2, column, vo.getNcItem2(),areaList, data);

				
				row++;
				Row dataRow3 = sheet.createRow(row);
				dataRow3.setHeightInPoints(dataHeight*2);
				column = 0;
				createCell(dataRow3, column++, "", data);
				createCell(dataRow3, column++, "", data);
				doCreateCellOneRowNc(dataRow3, column, vo.getNcGrowth(),areaList, data);
				
			}
			
			
			autoColumnWidthForChineseChar(sheet, 0, titleSize, minWidth,minWidth*2);
		} finally {
			writeToOut(wb, out);
		}

	}

	@Override
	public void exportReportGrowth(OutputStream out, CTrademark req, List<TrademarkDTO> list) {
		// TODO Auto-generated method stub

		// TODO Auto-generated method stub

		SXSSFWorkbook wb = new SXSSFWorkbook(500);

		try {
			// response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
			// response.setCharacterEncoding("utf-8");
			short headHeight = 30 * 20;
			int minWidth = "四个字宽".getBytes().length * 256;
			String title = "中国商标-同比增长率报表";

			int dataHeight = 30;

			SXSSFSheet sheet = wb.createSheet();
			wb.setSheetName(0, title);
			sheet.trackAllColumnsForAutoSizing();

			Map<String, XSSFCellStyle> styles = createStyles(wb);
			
			XSSFCellStyle header=styles.get("header");
			XSSFCellStyle data=styles.get("data");

			Row titleRow0 = sheet.createRow(0);
			titleRow0.setHeightInPoints(dataHeight);
			Cell titleCell = titleRow0.createCell(0);
			titleCell.setCellStyle(styles.get("title"));
			titleCell.setCellValue(title);

			int titleSize = 5;
			// 合并单元格语法： 开始行、结束行、开始列、结束列
			// 对应代码：new CellRangeAddress(startRowIndex, rowIndex - 1, i, i);
			sheet.addMergedRegion(new CellRangeAddress(titleRow0.getRowNum(), titleRow0.getRowNum(),0, titleSize-1));
			
			
			Row titleRow1 = sheet.createRow(1);
			titleRow1.setHeight(headHeight);
			
			createCell(titleRow1, 0, "对比地区", header);
			
			CellRangeAddress areaRegion = new CellRangeAddress(titleRow1.getRowNum(), titleRow1.getRowNum()+1, 0, 0);
			sheet.addMergedRegion(areaRegion);
			
			
			createCell(titleRow1, 1, "对比数据", header);

			createCell(titleRow1, 2, "申请", header);
			CellRangeAddress aplRegion = new CellRangeAddress(titleRow1.getRowNum(), titleRow1.getRowNum()+1, 2, 2);
			sheet.addMergedRegion(aplRegion);
			
			createCell(titleRow1, 3, "注册", header);
			CellRangeAddress regRegion = new CellRangeAddress(titleRow1.getRowNum(), titleRow1.getRowNum()+1, 3, 3);
			sheet.addMergedRegion(regRegion);
			
			createCell(titleRow1, 4, "有效", header);
			CellRangeAddress actRegion = new CellRangeAddress(titleRow1.getRowNum(), titleRow1.getRowNum()+1, 4, 4);
			sheet.addMergedRegion(actRegion);
			
			
			Row titleRow2 = sheet.createRow(2);
			titleRow2.setHeight(headHeight);
			createCell(titleRow2, 1, "对比周期", header);
			
			// 冻结
			sheet.createFreezePane(0, 3, 0, 3);
			
			int column=0;
			int row=2;
			for(int i=0;i<list.size();i++) {
				TrademarkDTO vo=list.get(i);
				
				
				row++;
				Row dataRow = sheet.createRow(row);
				dataRow.setHeightInPoints(dataHeight);
				column = 0;
				doCreateCellOneRowHv(dataRow, column, vo.getItem1(), data);

				column = 0;
				row++;
				Row dataRow2 = sheet.createRow(row);
				dataRow2.setHeightInPoints(dataHeight);
				doCreateCellOneRowHv(dataRow2, column, vo.getItem2(), data);

				column = 0;
				row++;
				Row dataRow3 = sheet.createRow(row);
				dataRow3.setHeightInPoints(dataHeight);
				doCreateCellOneRowHv(dataRow3, column, vo.getGrowth(), data);
				
				
			}
			

			setRegionStyle(sheet, aplRegion, styles.get("header"));
			setRegionStyle(sheet, regRegion, styles.get("header"));
			setRegionStyle(sheet, actRegion, styles.get("header"));
			setRegionStyle(sheet, areaRegion, styles.get("header"));
			

			autoColumnWidthForChineseChar(sheet, 0, titleSize, minWidth);
			

		} finally {
			writeToOut(wb, out);
		}

	}
	
	
	private void doCreateCellOneRowNc(Row dataRow, int column, Map<String,String> item,List<String> areaList, XSSFCellStyle style) {
		createCell(dataRow, column++, item.get("compareRate"), style);
		for(int i=0;i<areaList.size();i++) {
			if("同比增长".equals(item.get("compareRate"))) {
				if(item.containsKey(areaList.get(i))) {
					createCell(dataRow, column++, item.get(areaList.get(i)), style);
				}else {
					createCell(dataRow, column++, "--", style);
				}
			}else {
				if(item.containsKey(areaList.get(i))) {
					createCell(dataRow, column++, Long.parseLong(item.get(areaList.get(i))), style);
				}else {
					createCell(dataRow, column++, 0, style);
				}
			}
			
			
		}
	}
	
	private void doCreateCellOneRowHv(Row dataRow, int column, TrademarkVO item, XSSFCellStyle style) {
		
		createCell(dataRow, column++, item.getAreaName(), style);
		createCell(dataRow, column++, item.getCompareRate(), style);
		
		if("同比增长".equals(item.getCompareRate())) {
			createCell(dataRow, column++, item.getAplGrowth(), style);
			createCell(dataRow, column++, item.getRegGrowth(), style);
			createCell(dataRow, column++, item.getActGrowth(), style);
		}else {
			createCell(dataRow, column++, item.getAplTotalCnt(), style);
			createCell(dataRow, column++, item.getRegTotalCnt(), style);
			createCell(dataRow, column++, item.getActTotalCnt(), style);
		}
		
		
		
	}

	@Override
	public void exportReportReg(OutputStream out, CTrademark req, TrademarkDTO dto) {

		SXSSFWorkbook wb = new SXSSFWorkbook(500);

		try {
			// response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
			// response.setCharacterEncoding("utf-8");
			short headHeight = 30 * 20;
			int minWidth = "四个字宽".getBytes().length * 256;
			String title = "中国商标注册统计报表";

			int dataHeight = 30;

			SXSSFSheet sheet = wb.createSheet();
			wb.setSheetName(0, title);
			sheet.trackAllColumnsForAutoSizing();

			Map<String, XSSFCellStyle> styles = createStyles(wb);

			int column = 0;
			int row = 0;
			Row titleRow0 = sheet.createRow(row);
			titleRow0.setHeightInPoints(dataHeight);
			Cell titleCell = titleRow0.createCell(column);
			titleCell.setCellStyle(styles.get("title"));
			titleCell.setCellValue(title);

			int titleSize = 0;
			if (dto.getYears() != null && dto.getYears().size() > 0) {
				int size = dto.getYears().size();
				titleSize = titleSize + size;
			}

			// 合并单元格语法： 开始行、结束行、开始列、结束列
			// 对应代码：new CellRangeAddress(startRowIndex, rowIndex - 1, i, i);
			sheet.addMergedRegion(
					new CellRangeAddress(titleRow0.getRowNum(), titleRow0.getRowNum(), column, titleSize));

			row++;
			Row titleRow1 = sheet.createRow(row);
			titleRow1.setHeight(headHeight);
			String val = "地区";
			XSSFCellStyle header = styles.get("header");
			createCell(titleRow1, column, val, header);
			CellRangeAddress titleRowRange = new CellRangeAddress(titleRow1.getRowNum(), titleRow1.getRowNum() + 1, 0,
					0);
			sheet.addMergedRegion(titleRowRange);

			val = "统计时期";
			if (StringUtils.isNotBlank(req.getCountTimeStr())) {
				val = req.getCountTimeStr();
			}
			column++;
			createCell(titleRow1, column, val, header);
			CellRangeAddress titleRange1 = new CellRangeAddress(titleRow1.getRowNum(), titleRow1.getRowNum(), column,
					titleSize);
			sheet.addMergedRegion(titleRange1);

			row++;
			Row titleRow2 = sheet.createRow(row);
			titleRow2.setHeight(headHeight);
			column = 0;
			for (int i = 0; i < dto.getYears().size(); i++) {
				column++;
				createCell(titleRow2, column, dto.getYears().get(i), header);
			}

			setRegionStyle(sheet, titleRange1, styles.get("header"));
			setRegionStyle(sheet, titleRowRange, styles.get("header"));

			// 冻结
			sheet.createFreezePane(0, 3, 0, 3);

			XSSFCellStyle data = styles.get("data");

			for (int i = 0; i < dto.getList().size(); i++) {
				TrademarkVO vo = dto.getList().get(i);

				Row dataRow = sheet.createRow(3 + i);
				dataRow.setHeightInPoints(dataHeight);

				column = 0;
				createCell(dataRow, column, vo.getAreaName(), data);
				for (int y = 0; y < dto.getYears().size(); y++) {
					String year = dto.getYears().get(y);
					if (vo.getTotals() == null || !vo.getTotals().containsKey(year)) {
						vo.getTotals().put(year, 0L);
					}
					column++;
					createCell(dataRow, column, vo.getTotals().get(year), data);
				}

			}

			autoColumnWidthForChineseChar(sheet, 0, titleSize + 1, (minWidth / 2 * 3), (minWidth / 2 * 3));

		} finally {
			writeToOut(wb, out);
		}

	}

	@Override
	public void exportReportAct(OutputStream out, CTrademark req, TrademarkDTO dto) {

		SXSSFWorkbook wb = new SXSSFWorkbook(500);

		try {
			// response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
			// response.setCharacterEncoding("utf-8");
			short headHeight = 30 * 20;
			int minWidth = "四个字宽".getBytes().length * 256;
			String title = "中国商标注册有效量统计报表";

			int dataHeight = 30;

			SXSSFSheet sheet = wb.createSheet();
			wb.setSheetName(0, title);
			sheet.trackAllColumnsForAutoSizing();

			Map<String, XSSFCellStyle> styles = createStyles(wb);

			int column = 0;
			int row = 0;
			Row titleRow0 = sheet.createRow(row);
			titleRow0.setHeightInPoints(dataHeight);
			Cell titleCell = titleRow0.createCell(column);
			titleCell.setCellStyle(styles.get("title"));
			titleCell.setCellValue(title);

			int titleSize = 0;
			if (dto.getYears() != null && dto.getYears().size() > 0) {
				int size = dto.getYears().size();
				titleSize = titleSize + size;
			}

			// 合并单元格语法： 开始行、结束行、开始列、结束列
			// 对应代码：new CellRangeAddress(startRowIndex, rowIndex - 1, i, i);
			sheet.addMergedRegion(
					new CellRangeAddress(titleRow0.getRowNum(), titleRow0.getRowNum(), column, titleSize));

			row++;
			Row titleRow1 = sheet.createRow(row);
			titleRow1.setHeight(headHeight);
			String val = "地区";
			XSSFCellStyle header = styles.get("header");
			createCell(titleRow1, column, val, header);
			CellRangeAddress titleRowRange = new CellRangeAddress(titleRow1.getRowNum(), titleRow1.getRowNum() + 1, 0,
					0);
			sheet.addMergedRegion(titleRowRange);

			val = "统计时期";
			if (StringUtils.isNotBlank(req.getCountTimeStr())) {
				val = req.getCountTimeStr();
			}
			column++;
			createCell(titleRow1, column, val, header);
			CellRangeAddress titleRange1 = new CellRangeAddress(titleRow1.getRowNum(), titleRow1.getRowNum(), column,
					titleSize);
			sheet.addMergedRegion(titleRange1);

			row++;
			Row titleRow2 = sheet.createRow(row);
			titleRow2.setHeight(headHeight);
			column = 0;
			for (int i = 0; i < dto.getYears().size(); i++) {
				column++;
				createCell(titleRow2, column, dto.getYears().get(i), header);
			}

			setRegionStyle(sheet, titleRange1, styles.get("header"));
			setRegionStyle(sheet, titleRowRange, styles.get("header"));

			// 冻结
			sheet.createFreezePane(0, 3, 0, 3);

			XSSFCellStyle data = styles.get("data");

			for (int i = 0; i < dto.getList().size(); i++) {
				TrademarkVO vo = dto.getList().get(i);

				Row dataRow = sheet.createRow(3 + i);
				dataRow.setHeightInPoints(dataHeight);

				column = 0;
				createCell(dataRow, column, vo.getAreaName(), data);
				for (int y = 0; y < dto.getYears().size(); y++) {
					String year = dto.getYears().get(y);
					if (vo.getTotals() == null || !vo.getTotals().containsKey(year)) {
						vo.getTotals().put(year, 0L);
					}
					column++;
					createCell(dataRow, column, vo.getTotals().get(year), data);
				}

			}

			autoColumnWidthForChineseChar(sheet, 0, titleSize + 1, (minWidth / 2 * 3), (minWidth / 2 * 3));

		} finally {
			writeToOut(wb, out);
		}

	}

	@Override
	public void exportReportHouse(OutputStream out, CTrademark req, List<TrademarkVO> list) {

		SXSSFWorkbook wb = new SXSSFWorkbook(500);

		try {
			short headHeight = 30 * 20;
			int minWidth = "四个字宽".getBytes().length * 256;
			String title = "万户主体有效商标拥有量统计报表";

			int dataHeight = 30;

			SXSSFSheet sheet = wb.createSheet();
			wb.setSheetName(0, title);
			sheet.trackAllColumnsForAutoSizing();

			Map<String, XSSFCellStyle> styles = createStyles(wb);

			Row titleRow0 = sheet.createRow(0);
			titleRow0.setHeightInPoints(dataHeight);
			Cell titleCell = titleRow0.createCell(0);
			titleCell.setCellStyle(styles.get("title"));
			titleCell.setCellValue(title);

			int titleSize = 5;

			// 合并单元格语法： 开始行、结束行、开始列、结束列
			// 对应代码：new CellRangeAddress(startRowIndex, rowIndex - 1, i, i);
			sheet.addMergedRegion(new CellRangeAddress(titleRow0.getRowNum(), titleRow0.getRowNum(), 0, titleSize-1));
			
			String year=req.getYear();
			XSSFCellStyle header=styles.get("header");
			
			int column=0;
			Row titleRow1 = sheet.createRow(1);
			titleRow1.setHeightInPoints(dataHeight);
			createCell(titleRow1, column++, "排名", header);
			createCell(titleRow1, column++, "地区", header);
			createCell(titleRow1, column++, year+"年万户市场主体拥有有效商标平均数(年/万户)", header);
			createCell(titleRow1, column++, year+"年商标注册有效量", header);
			createCell(titleRow1, column++, year+"年市场主体数(万户)", header);
			
			
			XSSFCellStyle data=styles.get("data");
			
			
			// 冻结
			sheet.createFreezePane(0, 2, 0, 2);
			
			for(int i=0;i<list.size();i++) {
				TrademarkVO vo=list.get(i);
				
				Row dataRow = sheet.createRow(2+i);
				dataRow.setHeightInPoints(dataHeight);
				column=0;
				createCell(dataRow, column++, vo.getRanking()==null?"":vo.getRanking(), data);
				createCell(dataRow, column++, vo.getAreaName(), data);

				createCell(dataRow, column++, vo.getMillionArg(), data);
				createCell(dataRow, column++, vo.getTotalCnt(), data);
				createCell(dataRow, column++, vo.getSubjectCnt(), data);
				
			}
			

			autoColumnWidthForChineseChar(sheet, 0, titleSize, minWidth);
			

		} finally {
			writeToOut(wb, out);
		}

	}

	@Override
	public void exportReportNc(OutputStream out, CTrademark req, List<TrademarkVO> list) {

		SXSSFWorkbook wb = new SXSSFWorkbook(500);

		try {
			// response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
			// response.setCharacterEncoding("utf-8");
			short headHeight = 30 * 20;
			int minWidth = "四个字宽".getBytes().length * 256;
			String title = "中国商标尼斯分类统计报表";

			int dataHeight = 30;

			SXSSFSheet sheet = wb.createSheet();
			wb.setSheetName(0, title);
			sheet.trackAllColumnsForAutoSizing();

			Map<String, XSSFCellStyle> styles = createStyles(wb);

			Row titleRow0 = sheet.createRow(0);
			titleRow0.setHeightInPoints(dataHeight);
			Cell titleCell = titleRow0.createCell(0);
			titleCell.setCellStyle(styles.get("title"));
			titleCell.setCellValue(title);

			int titleSize = 4;

			// 合并单元格语法： 开始行、结束行、开始列、结束列
			// 对应代码：new CellRangeAddress(startRowIndex, rowIndex - 1, i, i);
			sheet.addMergedRegion(new CellRangeAddress(titleRow0.getRowNum(), titleRow0.getRowNum(), 0, titleSize));

			Row titleRow1 = sheet.createRow(1);
			titleRow1.setHeight(headHeight);
			
			XSSFCellStyle header=styles.get("header");
			int column=0;
			createCell(titleRow1, column, "类别号", header);
			CellRangeAddress areaRegion = new CellRangeAddress(titleRow1.getRowNum(), titleRow1.getRowNum()+1, column, column);
			sheet.addMergedRegion(areaRegion);
			
			column=1;
			createCell(titleRow1, column, "类别名称", header);
			CellRangeAddress areaRegion1 = new CellRangeAddress(titleRow1.getRowNum(), titleRow1.getRowNum()+1, column, column);
			sheet.addMergedRegion(areaRegion1);
			
			column++ ;
			title = "统计时期";
			if (StringUtils.isNotBlank(req.getCountTimeStr())) {
				title = req.getCountTimeStr();
			}
			CellRangeAddress areaRegion2 = new CellRangeAddress(titleRow1.getRowNum(), titleRow1.getRowNum(), column, titleSize);
			sheet.addMergedRegion(areaRegion2);
			createCell(titleRow1, column, title, header);
			
			Row titleRow2 = sheet.createRow(2);
			titleRow2.setHeight(headHeight);
			createCell(titleRow2, column++, "申请量", header);
			createCell(titleRow2, column++, "注册量", header);
			createCell(titleRow2, column++, "有效注册量", header);
			
			setRegionStyle(sheet, areaRegion2, styles.get("header"));
			setRegionStyle(sheet, areaRegion1, styles.get("header"));
			setRegionStyle(sheet, areaRegion, styles.get("header"));
			
			
			// 冻结
			sheet.createFreezePane(0, 3, 0, 3);
			
			for(int i=0;i<list.size();i++) {
				TrademarkVO vo=list.get(i);
				
				Row dataRow = sheet.createRow(3+i);
				dataRow.setHeightInPoints(dataHeight*2);
				
				XSSFCellStyle data=styles.get("data");
				int row=3+i;
				column = 0;
				createCell(dataRow, column++, vo.getNc(), data);
				
				createCell(dataRow, column++, vo.getNcStr(), styles.get("data2"));
				
				createCell(dataRow, column++, vo.getAplTotalCnt(), data);
				createCell(dataRow, column++, vo.getRegTotalCnt(), data);
				createCell(dataRow, column++, vo.getActTotalCnt(), data);
				
				
			}
			
			autoColumnWidthForChineseChar(sheet, 0, titleSize, minWidth,minWidth*2);

		} finally {
			writeToOut(wb, out);
		}

	}

	@Override
	public void exportReportRank(OutputStream out, CTrademark req, List<TrademarkVO> list) {
		// TODO Auto-generated method stub

		SXSSFWorkbook wb = new SXSSFWorkbook(500);

		try {
			// response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
			// response.setCharacterEncoding("utf-8");
			short headHeight = 30 * 20;
			int minWidth = "四个字宽".getBytes().length * 256;
			String title = "中国商标全国排名统计报表";

			int dataHeight = 30;

			SXSSFSheet sheet = wb.createSheet();
			wb.setSheetName(0, title);
			sheet.trackAllColumnsForAutoSizing();

			Map<String, XSSFCellStyle> styles = createStyles(wb);

			Row titleRow0 = sheet.createRow(0);
			titleRow0.setHeightInPoints(dataHeight);
			Cell titleCell = titleRow0.createCell(0);
			titleCell.setCellStyle(styles.get("title"));
			titleCell.setCellValue(title);

			int titleSize = 3;

			// 合并单元格语法： 开始行、结束行、开始列、结束列
			// 对应代码：new CellRangeAddress(startRowIndex, rowIndex - 1, i, i);
			sheet.addMergedRegion(new CellRangeAddress(titleRow0.getRowNum(), titleRow0.getRowNum(),0, titleSize - 1));
			
			
			XSSFCellStyle header = styles.get("header");
			Row titleRow1 = sheet.createRow(1);
			titleRow1.setHeightInPoints(dataHeight);
			int column=0;
			createCell(titleRow1, column++, "排名", header);
			createCell(titleRow1, column++, "省(区市)", header);
			createCell(titleRow1, column++, req.getYear()+"年商标有效注册量", header);

			
			// 冻结
			sheet.createFreezePane(0, 2, 0, 2);
			
			
			for(int i=0;i<list.size();i++) {
				TrademarkVO vo=list.get(i);
				
				Row dataRow = sheet.createRow(2+i);
				dataRow.setHeightInPoints(dataHeight);
				
				
				column = 0;
				createCell(dataRow, column++, vo.getRanking(), styles.get("data"));
				createCell(dataRow, column++, vo.getProvince(), styles.get("data"));
				createCell(dataRow, column++, vo.getNumber(), styles.get("data"));
				
				
				
			}
			
			autoColumnWidthForChineseChar(sheet, 0, titleSize, minWidth);


		} finally {
			writeToOut(wb, out);
		}

	}

}
