package com.dbnt.sims.util;


import java.io.IOException;

import java.io.PrintWriter;
import java.time.format.DateTimeFormatter;
import java.util.Date;
import java.util.List;
import java.util.Map;


import javax.servlet.http.HttpServletResponse;


import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFDataFormat;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import com.dbnt.sims.equip.model.CellPhone;
import com.dbnt.sims.equip.model.UseList;
import com.dbnt.sims.fipTarget.model.PartInfo;
import com.dbnt.sims.fipTarget.model.PartWork;


public class Utils {
	

	public static boolean isEmpty(final Object obj) {
    	return !isNotEmpty(obj);
    }

	public static boolean isNotEmpty(final Object obj) {
		if(null == obj) return false;
		else {
			if(obj instanceof String) return "".equals(obj) ? false : true;
			else if(obj instanceof List) return !((List<?>)obj).isEmpty();
			else if(obj instanceof Map) return !((Map<?,?>)obj).isEmpty();
//			else if(obj instanceof Object[]) return 0 == Array.getLength(obj) ? false : true;
			else if(obj instanceof Integer) return !(null == obj);
			else if(obj instanceof Long) return !(null == obj);
			else return false;
		}
	}

	public static String getTimeStampString(final String format) {
    	return getTimeStampString(new Date(), format);
    }
	
	public static String getTimeStampString(final Date date) {
		return getTimeStampString(date, "yyyyMMddHHmmss");
	}
    
    public static String getTimeStampString(final Date date, final String format){
    	java.text.SimpleDateFormat formatter = new java.text.SimpleDateFormat (format, java.util.Locale.KOREA);
    	return formatter.format(date);
    }
    
    public static String getTimeStampString(String date, final String format) {
    	try {
    	    if(null == date || "".equals(date)) return "";
    	    
    	    Date	d	= null;
    	    		date= date.replaceAll("-", "");
    	    
    	    switch(date.length()) {
    	    	case 14: break;
    	    	case 12: date += "00";			break;
    	    	case 10: date += "0000";		break;
    	    	case 8:	 date += "000000";		break;
    	    	case 6:	 date += "01000000";	break;
    	    	case 4:	 date += "0101000000";	break;
    	    	default: return "";
    	    }
    	    
    		java.text.SimpleDateFormat tmpFormat = new java.text.SimpleDateFormat("yyyyMMddHHmmss", java.util.Locale.KOREA);

	    	if("".equals(date)) d = new Date();
	    	else {
	    	    tmpFormat.setLenient(true);
	    		d = tmpFormat.parse(date);
	    	}
	    	
	    	return getTimeStampString(d, format);
    	} catch(Exception e) {
    		e.printStackTrace();
    		return "";
    	}
    }
    
    public static String getFileExtention(final String filename) {
    	if(null == filename || "".equals(filename)) return "";
    	
    	return -1 < filename.lastIndexOf(".") ? filename.substring(filename.lastIndexOf(".") + 1).toLowerCase() : "";
    }
    
    public static String generationSaveName() {
		try {
			Thread.sleep(100);
		} catch (InterruptedException e) {
			e.printStackTrace();
		}
		return Utils.getTimeStampString("yyyyMMdd_HHmmss_SSS");
	}
	
	public static void listToExcel(List<ParamMap> list, HttpServletResponse response, String[] headers, String[] headerNames, String[] columnType, String sheetName, String excelFileName) throws IOException {
		if(Utils.isNotEmpty(list)) {
			// 메모리에 100개의 행을 유지합니다. 행의 수가 넘으면 디스크에 적습니다. 
			XSSFWorkbook wb = new XSSFWorkbook(); 
			Sheet sheet = wb.createSheet(sheetName);
			Row headerRow = sheet.createRow(0);
			CellStyle cellStyle1 = wb.createCellStyle();		//쉼표들어간 숫자 양식
			CellStyle cellStyle2 = wb.createCellStyle();		//숫자양식
			CellStyle headerStyle = wb.createCellStyle();		//숫자양식
			
			XSSFDataFormat format = wb.createDataFormat();
			cellStyle1.setDataFormat(format.getFormat("#,##0"));			
			cellStyle2.setDataFormat(format.getFormat("#"));
			headerStyle.setAlignment(HorizontalAlignment.CENTER);
			headerStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
			headerStyle.setFillForegroundColor((short)3);
			headerStyle.setFillForegroundColor(IndexedColors.LIME.getIndex());
			
			for(int i=0; i<list.size(); i++) {
				ParamMap rowData = list.get(i);
				Row row = sheet.createRow(i+1);
				
				for(int j=0; j<headers.length; j++) {
					Cell cell = row.createCell(j);
					
					if(columnType[j].equalsIgnoreCase("Money")) {
						cell.setCellValue(rowData.getInt(headers[j]));
						cell.setCellStyle(cellStyle1);
					} else if(columnType[j].equalsIgnoreCase("Int")) {
						cell.setCellValue(rowData.getInt(headers[j]));
						cell.setCellStyle(cellStyle2);
					} else if(columnType[j].equalsIgnoreCase("String")) {
						cell.setCellValue(rowData.getString(headers[j]));
					} else {
						cell.setCellValue(rowData.getString(headers[j]));
					}
				}
			}
			
			for(int j=0; j<headerNames.length; j++) {
				Cell cell = headerRow.createCell(j);
				cell.setCellValue(headerNames[j]);
				cell.setCellStyle(headerStyle);
				sheet.autoSizeColumn(j);
				sheet.setColumnWidth(j, (sheet.getColumnWidth(j)) + 1000);
			}
			//엑셀이름 한글깨짐방지
			 String outputFileName = new String(excelFileName.getBytes("KSC5601"), "8859_1");
			 
			response.setHeader("Set-Cookie", "fileDownload=true; path=/"); 
			response.setHeader("Content-Disposition", String.format("attachment; filename=\""+outputFileName+"_"+Utils.getTimeStampString("yyyyMMdd_HHmm")+".xlsx\""));

			wb.write(response.getOutputStream());
			wb.close();
		} else {
			response.setHeader("Content-Type", "text/html; charset=UTF-8");
			PrintWriter		out	= response.getWriter();
			
			out.write("<!DOCTYPE HTML PUBLIC \"-//W3C//DTD HTML 4.01 Transitional//EN\" \"http://www.w3.org/TR/html4/loose.dtd\">");
			out.write("<html lang=\"ko\">");
			out.write("<head>");
			out.write("<script type=\"text/javascript\">");
			out.write("alert('데이터가 없습니다.');");
			out.write("history.back(-1);");
			out.write("</script>");
			out.write("</head>");
			out.write("</html>");
			
			out.flush();
			out.close();
		}

	}
	
	public static void downExcel(List<ParamMap> list, HttpServletResponse response, String[] headers, String[] headerNames,String[] headerNames2, String[] columnType, String sheetName, String excelFileName) throws IOException {
		if(Utils.isNotEmpty(list)) {
			// 메모리에 100개의 행을 유지합니다. 행의 수가 넘으면 디스크에 적습니다. 
			XSSFWorkbook wb = new XSSFWorkbook(); 
			Sheet sheet = wb.createSheet(sheetName);
			Row headerRow = sheet.createRow(0);
			Row headerRow2 = sheet.createRow(1);
			CellStyle cellStyle1 = wb.createCellStyle();		//쉼표들어간 숫자 양식
			CellStyle cellStyle2 = wb.createCellStyle();		//숫자양식
			CellStyle headerStyle = wb.createCellStyle();		//숫자양식
			CellStyle headerStyle2 = wb.createCellStyle();
			
			XSSFDataFormat format = wb.createDataFormat();
			cellStyle1.setDataFormat(format.getFormat("#,##0"));			
			cellStyle2.setDataFormat(format.getFormat("#,##0"));
			cellStyle2.setAlignment(HorizontalAlignment.CENTER);
			headerStyle2.setAlignment(HorizontalAlignment.CENTER);
			headerStyle.setAlignment(HorizontalAlignment.CENTER);
			headerStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
			headerStyle.setFillForegroundColor((short)3);
			headerStyle.setFillForegroundColor(IndexedColors.LIME.getIndex());
			
			//로우그리기
			for(int i=0; i<list.size(); i++) {
				ParamMap rowData = list.get(i);
				Row row = sheet.createRow(i+2);
				
				for(int j=0; j<headers.length; j++) {
					Cell cell = row.createCell(j);
					

					if(columnType[j].equalsIgnoreCase("Int")) {
						cell.setCellValue(rowData.getInt(headers[j]));
						cell.setCellStyle(cellStyle2);
					} else if(columnType[j].equalsIgnoreCase("String")) {
						cell.setCellValue(rowData.getString(headers[j]));
					} else {
						cell.setCellValue(rowData.getString(headers[j]));
					}
				}
			}
			
			//헤더
			for(int j=0; j<headerNames.length; j++) {
				Cell cell = headerRow.createCell(j);
				cell.setCellValue(headerNames[j]);
				cell.setCellStyle(headerStyle);
				sheet.autoSizeColumn(j);
				sheet.setColumnWidth(j, (sheet.getColumnWidth(j)) + 1000);
			}
			sheet.addMergedRegion(new CellRangeAddress(0,0,0,1));
			for(int j=0; j<headerNames2.length; j++) {
				Cell cell = headerRow2.createCell(j);
				cell.setCellValue(headerNames2[j]);
				cell.setCellStyle(headerStyle2);
				sheet.autoSizeColumn(j);
				sheet.setColumnWidth(j, (sheet.getColumnWidth(j)) + 1000);
			}
			//엑셀이름 한글깨짐방지
			 String outputFileName = new String(excelFileName.getBytes("KSC5601"), "8859_1");
			 
			response.setHeader("Set-Cookie", "fileDownload=true; path=/"); 
			response.setHeader("Content-Disposition", String.format("attachment; filename=\""+outputFileName+"_"+Utils.getTimeStampString("yyyyMMdd_HHmm")+".xlsx\""));

			wb.write(response.getOutputStream());
			wb.close();
		} else {
			response.setHeader("Content-Type", "text/html; charset=UTF-8");
			PrintWriter		out	= response.getWriter();
			
			out.write("<!DOCTYPE HTML PUBLIC \"-//W3C//DTD HTML 4.01 Transitional//EN\" \"http://www.w3.org/TR/html4/loose.dtd\">");
			out.write("<html lang=\"ko\">");
			out.write("<head>");
			out.write("<script type=\"text/javascript\">");
			out.write("alert('데이터가 없습니다.');");
			out.write("history.back(-1);");
			out.write("</script>");
			out.write("</head>");
			out.write("</html>");
			
			out.flush();
			out.close();
		}

	}
	
	public static void downEquipStatusExcel(List<ParamMap> list, HttpServletResponse response, String[] headers, String[] headerNames,String[] headerNames2, String[] columnType, String sheetName, String excelFileName) throws IOException {
		if(Utils.isNotEmpty(list)) {
			// 메모리에 100개의 행을 유지합니다. 행의 수가 넘으면 디스크에 적습니다. 
			XSSFWorkbook wb = new XSSFWorkbook(); 
			Sheet sheet = wb.createSheet(sheetName);
			Row headerRow = sheet.createRow(0);
			Row headerRow2 = sheet.createRow(1);
			CellStyle cellStyle1 = wb.createCellStyle();		//쉼표들어간 숫자 양식
			CellStyle cellStyle2 = wb.createCellStyle();		//숫자양식
			CellStyle headerStyle = wb.createCellStyle();		//숫자양식
			CellStyle headerStyle2 = wb.createCellStyle();
			CellStyle headerStyle3 = wb.createCellStyle();
			
			XSSFDataFormat format = wb.createDataFormat();
			cellStyle1.setDataFormat(format.getFormat("#,##0"));			
			cellStyle2.setDataFormat(format.getFormat("#,##0"));
			cellStyle2.setAlignment(HorizontalAlignment.CENTER);
			headerStyle2.setBorderTop(BorderStyle.THIN);
			headerStyle2.setBorderBottom(BorderStyle.THIN);
			headerStyle2.setBorderLeft(BorderStyle.THIN);
			headerStyle2.setBorderRight(BorderStyle.THIN);
			headerStyle2.setAlignment(HorizontalAlignment.CENTER);
			headerStyle2.setFillPattern(FillPatternType.SOLID_FOREGROUND);
			headerStyle2.setFillForegroundColor((short)3);
			headerStyle2.setFillForegroundColor(IndexedColors.LIME.getIndex());
			headerStyle.setAlignment(HorizontalAlignment.CENTER);
			headerStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
			headerStyle.setFillForegroundColor((short)3);
			headerStyle.setFillForegroundColor(IndexedColors.LIME.getIndex());
			headerStyle3.setBorderTop(BorderStyle.THIN);
			headerStyle3.setBorderBottom(BorderStyle.THIN);
			headerStyle3.setBorderLeft(BorderStyle.THIN);
			headerStyle3.setBorderRight(BorderStyle.THIN);
			headerStyle3.setAlignment(HorizontalAlignment.CENTER);
			headerStyle3.setFillPattern(FillPatternType.SOLID_FOREGROUND);
			headerStyle3.setFillForegroundColor((short)3);
			headerStyle3.setFillForegroundColor(IndexedColors.LIME.getIndex());
			
			
			//로우그리기
			for(int i=0; i<list.size(); i++) {
				ParamMap rowData = list.get(i);
				Row row = sheet.createRow(i+2);
				
				for(int j=0; j<headers.length; j++) {
					Cell cell = row.createCell(j);
					

					if(columnType[j].equalsIgnoreCase("Int")) {
						cell.setCellValue(rowData.getInt(headers[j]));
						cell.setCellStyle(cellStyle2);
					} else if(columnType[j].equalsIgnoreCase("String")) {
						cell.setCellValue(rowData.getString(headers[j]));
					} else {
						cell.setCellValue(rowData.getString(headers[j]));
					}
				}
			}
			
			//헤더
			for(int j=0; j<headerNames.length; j++) {
				Cell cell = headerRow.createCell(j);
				cell.setCellValue(headerNames[j]);
				cell.setCellStyle(headerStyle3);
				sheet.autoSizeColumn(j);
				sheet.setColumnWidth(j, (sheet.getColumnWidth(j)) + 1000);
			}
			sheet.addMergedRegion(new CellRangeAddress(0,0,0,4));
			sheet.addMergedRegion(new CellRangeAddress(0,0,5,9));
			sheet.addMergedRegion(new CellRangeAddress(0,0,10,15));
			sheet.addMergedRegion(new CellRangeAddress(0,0,16,21));
			sheet.addMergedRegion(new CellRangeAddress(0,0,22,26));
			sheet.addMergedRegion(new CellRangeAddress(0,0,27,29));
			for(int j=0; j<headerNames2.length; j++) {
				Cell cell = headerRow2.createCell(j);
				cell.setCellValue(headerNames2[j]);
				cell.setCellStyle(headerStyle2);
				sheet.autoSizeColumn(j);
				sheet.setColumnWidth(j, (sheet.getColumnWidth(j)) + 1000);
			}
			//엑셀이름 한글깨짐방지
			 String outputFileName = new String(excelFileName.getBytes("KSC5601"), "8859_1");
			 
			response.setHeader("Set-Cookie", "fileDownload=true; path=/"); 
			response.setHeader("Content-Disposition", String.format("attachment; filename=\""+outputFileName+"_"+Utils.getTimeStampString("yyyyMMdd_HHmm")+".xlsx\""));

			wb.write(response.getOutputStream());
			wb.close();
		} else {
			response.setHeader("Content-Type", "text/html; charset=UTF-8");
			PrintWriter		out	= response.getWriter();
			
			out.write("<!DOCTYPE HTML PUBLIC \"-//W3C//DTD HTML 4.01 Transitional//EN\" \"http://www.w3.org/TR/html4/loose.dtd\">");
			out.write("<html lang=\"ko\">");
			out.write("<head>");
			out.write("<script type=\"text/javascript\">");
			out.write("alert('데이터가 없습니다.');");
			out.write("history.back(-1);");
			out.write("</script>");
			out.write("</head>");
			out.write("</html>");
			
			out.flush();
			out.close();
		}

	}

	public static void partInfolistToExcel(List<PartInfo> partInfoList, HttpServletResponse response, String[] headers,
			String[] headerNames,String[] headerNames2,String[] headerNames3, String[] columnType, String sheetName, String excelFileName) throws IOException  {
		if(Utils.isNotEmpty(partInfoList)) {
			// 메모리에 100개의 행을 유지합니다. 행의 수가 넘으면 디스크에 적습니다. 
			XSSFWorkbook wb = new XSSFWorkbook(); 
			Sheet sheet = wb.createSheet(sheetName);
			Row headerRow = sheet.createRow(0);
			Row headerRow2 = sheet.createRow(1);
			Row headerRow3 = sheet.createRow(2);
			CellStyle cellStyle1 = wb.createCellStyle();		//쉼표들어간 숫자 양식
			CellStyle cellStyle2 = wb.createCellStyle();		//숫자양식
			CellStyle headerStyle = wb.createCellStyle();		//숫자양식
			CellStyle headerStyle2 = wb.createCellStyle();
			CellStyle headerStyle3 = wb.createCellStyle();
			
			XSSFDataFormat format = wb.createDataFormat();
			cellStyle1.setAlignment(HorizontalAlignment.CENTER);		
			cellStyle2.setDataFormat(format.getFormat("#,##0"));
			cellStyle2.setAlignment(HorizontalAlignment.CENTER);
			headerStyle2.setBorderTop(BorderStyle.THIN);
			headerStyle2.setBorderBottom(BorderStyle.THIN);
			headerStyle2.setBorderLeft(BorderStyle.THIN);
			headerStyle2.setBorderRight(BorderStyle.THIN);
			headerStyle2.setAlignment(HorizontalAlignment.CENTER);
			headerStyle2.setFillPattern(FillPatternType.SOLID_FOREGROUND);
			headerStyle2.setFillForegroundColor((short)3);
			headerStyle2.setFillForegroundColor(IndexedColors.LIME.getIndex());
			headerStyle.setAlignment(HorizontalAlignment.CENTER);
			headerStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
			headerStyle.setFillForegroundColor((short)3);
			headerStyle.setFillForegroundColor(IndexedColors.LIME.getIndex());
			headerStyle3.setBorderTop(BorderStyle.THIN);
			headerStyle3.setBorderBottom(BorderStyle.THIN);
			headerStyle3.setBorderLeft(BorderStyle.THIN);
			headerStyle3.setBorderRight(BorderStyle.THIN);
			headerStyle3.setAlignment(HorizontalAlignment.CENTER);
			headerStyle3.setFillPattern(FillPatternType.SOLID_FOREGROUND);
			headerStyle3.setFillForegroundColor((short)3);
			headerStyle3.setFillForegroundColor(IndexedColors.LIME.getIndex());			
			
			//로우그리기
			for(int i=0; i<partInfoList.size(); i++) {
				ParamMap rowData = new ParamMap();
				Row row = sheet.createRow(i+3);
				rowData.set("mgt_organ", partInfoList.get(i).getMgtOrgan());
				rowData.set("land_police", partInfoList.get(i).getLandPolice());
				rowData.set("terminal_nm", partInfoList.get(i).getTerminalNm());
				rowData.set("mp_work_type", partInfoList.get(i).getMpWorkType());
				rowData.set("mp_people_cnt", partInfoList.get(i).getMpPeopleCnt());
				rowData.set("mp_description", partInfoList.get(i).getMpDescription());
				rowData.set("pl_work_type", partInfoList.get(i).getPlWorkType());
				rowData.set("pl_people_cnt", partInfoList.get(i).getPlPeopleCnt());
				rowData.set("pl_description", partInfoList.get(i).getPlDescription());
				rowData.set("pi_manager_name", partInfoList.get(i).getPiManagerName());
				if(partInfoList.get(i).getRentType().equals("Y")) {
					rowData.set("rent_price", partInfoList.get(i).getRentPrice().toString()+"만");
				}else {
					rowData.set("rent_price", "무상");
				}
				if(partInfoList.get(i).getUtilityType().equals("Y")) {
					rowData.set("utility_price", partInfoList.get(i).getUtilityPrice().toString()+"만");
				}else {
					rowData.set("utility_price", "무상");
				}
				rowData.set("wrt_dt", partInfoList.get(i).getWrtDt().format(DateTimeFormatter.ofPattern("yyyy-MM-dd")));
				for(int j=0; j<headers.length; j++) {
					Cell cell = row.createCell(j);
			
					if(columnType[j].equalsIgnoreCase("Int")) {
						cell.setCellValue(rowData.getInt(headers[j]));
						cell.setCellStyle(cellStyle2);
					} else if(columnType[j].equalsIgnoreCase("String")) {
						cell.setCellValue(rowData.getString(headers[j]));
						cell.setCellStyle(cellStyle1);
					} else {
						cell.setCellValue(rowData.getString(headers[j]));
						cell.setCellStyle(cellStyle1);
					}
				}
						
			}			
		//헤더
			for(int j=0; j<headerNames.length; j++) {
				Cell cell = headerRow.createCell(j);
				cell.setCellValue(headerNames[j]);
				cell.setCellStyle(headerStyle3);
				sheet.autoSizeColumn(j);
				sheet.setColumnWidth(j, (sheet.getColumnWidth(j)) + 1024);
			}

			for(int j=0; j<headerNames2.length; j++) {
				Cell cell = headerRow2.createCell(j);
				cell.setCellValue(headerNames2[j]);
				cell.setCellStyle(headerStyle2);
				sheet.autoSizeColumn(j);
				sheet.setColumnWidth(j, (sheet.getColumnWidth(j)) + 1024);
			}
			for(int j=0; j<headerNames3.length; j++) {
				Cell cell = headerRow3.createCell(j);
				cell.setCellValue(headerNames3[j]);
				cell.setCellStyle(headerStyle2);
				sheet.autoSizeColumn(j);
				sheet.setColumnWidth(j, (sheet.getColumnWidth(j)) + 1024);
			}
			sheet.addMergedRegion(new CellRangeAddress(0,2,0,0));
			sheet.addMergedRegion(new CellRangeAddress(0,2,1,1));
			sheet.addMergedRegion(new CellRangeAddress(0,2,2,2));
			sheet.addMergedRegion(new CellRangeAddress(0,0,3,5));
			sheet.addMergedRegion(new CellRangeAddress(0,0,6,8));
			sheet.addMergedRegion(new CellRangeAddress(0,1,9,12));
						
			//엑셀이름 한글깨짐방지
			 String outputFileName = new String(excelFileName.getBytes("KSC5601"), "8859_1");
			 
			response.setHeader("Set-Cookie", "fileDownload=true; path=/"); 
			response.setHeader("Content-Disposition", String.format("attachment; filename=\""+outputFileName+"_"+Utils.getTimeStampString("yyyyMMdd_HHmm")+".xlsx\""));

			wb.write(response.getOutputStream());
			wb.close();
		} else {
			response.setHeader("Content-Type", "text/html; charset=UTF-8");
			PrintWriter		out	= response.getWriter();
			
			out.write("<!DOCTYPE HTML PUBLIC \"-//W3C//DTD HTML 4.01 Transitional//EN\" \"http://www.w3.org/TR/html4/loose.dtd\">");
			out.write("<html lang=\"ko\">");
			out.write("<head>");
			out.write("<script type=\"text/javascript\">");
			out.write("alert('데이터가 없습니다.');");
			out.write("history.back(-1);");
			out.write("</script>");
			out.write("</head>");
			out.write("</html>");
			
			out.flush();
			out.close();
		}		
	}

	public static void partWorkListToExcel(List<PartWork> partWorkList, HttpServletResponse response, String[] headers,
			String[] headerNames, String[] headerNames2, String[] columnType, String sheetName, String excelFileName) throws IOException {
		if(Utils.isNotEmpty(partWorkList)) {
			// 메모리에 100개의 행을 유지합니다. 행의 수가 넘으면 디스크에 적습니다. 
			XSSFWorkbook wb = new XSSFWorkbook(); 
			Sheet sheet = wb.createSheet(sheetName);
			Row headerRow = sheet.createRow(0);
			Row headerRow2 = sheet.createRow(1);
			CellStyle cellStyle1 = wb.createCellStyle();		//쉼표들어간 숫자 양식
			CellStyle cellStyle2 = wb.createCellStyle();		//숫자양식
			CellStyle headerStyle = wb.createCellStyle();		//숫자양식
			CellStyle headerStyle2 = wb.createCellStyle();
			CellStyle headerStyle3 = wb.createCellStyle();
			
			XSSFDataFormat format = wb.createDataFormat();
			cellStyle1.setAlignment(HorizontalAlignment.CENTER);		
			cellStyle2.setDataFormat(format.getFormat("#,##0"));
			cellStyle2.setAlignment(HorizontalAlignment.CENTER);
			headerStyle2.setBorderTop(BorderStyle.THIN);
			headerStyle2.setBorderBottom(BorderStyle.THIN);
			headerStyle2.setBorderLeft(BorderStyle.THIN);
			headerStyle2.setBorderRight(BorderStyle.THIN);
			headerStyle2.setAlignment(HorizontalAlignment.CENTER);
			headerStyle2.setFillPattern(FillPatternType.SOLID_FOREGROUND);
			headerStyle2.setFillForegroundColor((short)3);
			headerStyle2.setFillForegroundColor(IndexedColors.LIME.getIndex());
			headerStyle.setAlignment(HorizontalAlignment.CENTER);
			headerStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
			headerStyle.setFillForegroundColor((short)3);
			headerStyle.setFillForegroundColor(IndexedColors.LIME.getIndex());
			headerStyle3.setBorderTop(BorderStyle.THIN);
			headerStyle3.setBorderBottom(BorderStyle.THIN);
			headerStyle3.setBorderLeft(BorderStyle.THIN);
			headerStyle3.setBorderRight(BorderStyle.THIN);
			headerStyle3.setAlignment(HorizontalAlignment.CENTER);
			headerStyle3.setFillPattern(FillPatternType.SOLID_FOREGROUND);
			headerStyle3.setFillForegroundColor((short)3);
			headerStyle3.setFillForegroundColor(IndexedColors.LIME.getIndex());
			//로우그리기
			for(int i=0; i<partWorkList.size(); i++) {
				ParamMap rowData = new ParamMap();
				Row row = sheet.createRow(i+2);
				rowData.set("terminal_nm", partWorkList.get(i).getTerminalNm());
				rowData.set("wrt_nm", partWorkList.get(i).getWrtNm());
				if(partWorkList.get(i).getWorkDt() != null) {
					rowData.set("work_dt", partWorkList.get(i).getWorkDt().format(DateTimeFormatter.ofPattern("yyyy-MM-dd")));
				}else {
					rowData.set("work_dt", "");	
				}
				rowData.set("work_type_ch", partWorkList.get(i).getWorkTypeCh());
				rowData.set("work_type_poci", partWorkList.get(i).getWorkTypePoci());
				rowData.set("work_type_sri", partWorkList.get(i).getWorkTypeSri());
				rowData.set("work_type_ji", partWorkList.get(i).getWorkTypeJi());
				rowData.set("work_type_mt", partWorkList.get(i).getWorkTypeMt());
				rowData.set("work_type_etc", partWorkList.get(i).getWorkTypeEtc());
				rowData.set("description", partWorkList.get(i).getDescription());
				if(partWorkList.get(i).getFileCnt() > 0) {
					rowData.set("file_cnt", "●");
				}else {
					rowData.set("file_cnt", "");
				}
				rowData.set("wrt_dt", partWorkList.get(i).getWrtDt().format(DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm")));
				for(int j=0; j<headers.length; j++) {
					Cell cell = row.createCell(j);
			
					if(columnType[j].equalsIgnoreCase("Int")) {
						cell.setCellValue(rowData.getInt(headers[j]));
						cell.setCellStyle(cellStyle2);
					} else if(columnType[j].equalsIgnoreCase("String")) {
						cell.setCellValue(rowData.getString(headers[j]));
						cell.setCellStyle(cellStyle1);
					} else {
						cell.setCellValue(rowData.getString(headers[j]));
						cell.setCellStyle(cellStyle1);
					}
				}
						
			}
						
		//헤더
			for(int j=0; j<headerNames.length; j++) {
				Cell cell = headerRow.createCell(j);
				cell.setCellValue(headerNames[j]);
				cell.setCellStyle(headerStyle3);
				sheet.autoSizeColumn(j);
				sheet.setColumnWidth(j, (sheet.getColumnWidth(j)) + 1024);
			}

			for(int j=0; j<headerNames2.length; j++) {
				Cell cell = headerRow2.createCell(j);
				cell.setCellValue(headerNames2[j]);
				cell.setCellStyle(headerStyle2);
				sheet.autoSizeColumn(j);
				sheet.setColumnWidth(j, (sheet.getColumnWidth(j)) + 1024);
			}
			sheet.addMergedRegion(new CellRangeAddress(0,1,0,0));
			sheet.addMergedRegion(new CellRangeAddress(0,1,1,1));
			sheet.addMergedRegion(new CellRangeAddress(0,1,2,2));
			sheet.addMergedRegion(new CellRangeAddress(0,0,3,8));
			sheet.addMergedRegion(new CellRangeAddress(0,1,9,9));
			sheet.addMergedRegion(new CellRangeAddress(0,1,10,10));
			sheet.addMergedRegion(new CellRangeAddress(0,1,11,11));
						
			//엑셀이름 한글깨짐방지
			 String outputFileName = new String(excelFileName.getBytes("KSC5601"), "8859_1");
			 
			response.setHeader("Set-Cookie", "fileDownload=true; path=/"); 
			response.setHeader("Content-Disposition", String.format("attachment; filename=\""+outputFileName+"_"+Utils.getTimeStampString("yyyyMMdd_HHmm")+".xlsx\""));

			wb.write(response.getOutputStream());
			wb.close();
		} else {
			response.setHeader("Content-Type", "text/html; charset=UTF-8");
			PrintWriter		out	= response.getWriter();
			
			out.write("<!DOCTYPE HTML PUBLIC \"-//W3C//DTD HTML 4.01 Transitional//EN\" \"http://www.w3.org/TR/html4/loose.dtd\">");
			out.write("<html lang=\"ko\">");
			out.write("<head>");
			out.write("<script type=\"text/javascript\">");
			out.write("alert('데이터가 없습니다.');");
			out.write("history.back(-1);");
			out.write("</script>");
			out.write("</head>");
			out.write("</html>");
			
			out.flush();
			out.close();
		}		
		
	}

	public static void cellPhoneListToExcel(List<CellPhone> cellPhoneList, HttpServletResponse response,
			String[] headers, String[] headerNames, String[] columnType, String sheetName, String excelFileName) throws IOException {
		if(Utils.isNotEmpty(cellPhoneList)) {
			// 메모리에 100개의 행을 유지합니다. 행의 수가 넘으면 디스크에 적습니다. 
			XSSFWorkbook wb = new XSSFWorkbook(); 
			Sheet sheet = wb.createSheet(sheetName);
			Row headerRow = sheet.createRow(0);
			CellStyle cellStyle1 = wb.createCellStyle();		//쉼표들어간 숫자 양식
			CellStyle cellStyle2 = wb.createCellStyle();		//숫자양식
			CellStyle headerStyle = wb.createCellStyle();		//숫자양식
			CellStyle headerStyle2 = wb.createCellStyle();
			CellStyle headerStyle3 = wb.createCellStyle();
			
			XSSFDataFormat format = wb.createDataFormat();
			cellStyle1.setAlignment(HorizontalAlignment.CENTER);		
			cellStyle2.setDataFormat(format.getFormat("#,##0"));
			cellStyle2.setAlignment(HorizontalAlignment.CENTER);
			headerStyle2.setBorderTop(BorderStyle.THIN);
			headerStyle2.setBorderBottom(BorderStyle.THIN);
			headerStyle2.setBorderLeft(BorderStyle.THIN);
			headerStyle2.setBorderRight(BorderStyle.THIN);
			headerStyle2.setAlignment(HorizontalAlignment.CENTER);
			headerStyle2.setFillPattern(FillPatternType.SOLID_FOREGROUND);
			headerStyle2.setFillForegroundColor((short)3);
			headerStyle2.setFillForegroundColor(IndexedColors.LIME.getIndex());
			headerStyle.setAlignment(HorizontalAlignment.CENTER);
			headerStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
			headerStyle.setFillForegroundColor((short)3);
			headerStyle.setFillForegroundColor(IndexedColors.LIME.getIndex());
			headerStyle3.setBorderTop(BorderStyle.THIN);
			headerStyle3.setBorderBottom(BorderStyle.THIN);
			headerStyle3.setBorderLeft(BorderStyle.THIN);
			headerStyle3.setBorderRight(BorderStyle.THIN);
			headerStyle3.setAlignment(HorizontalAlignment.CENTER);
			headerStyle3.setFillPattern(FillPatternType.SOLID_FOREGROUND);
			headerStyle3.setFillForegroundColor((short)3);
			headerStyle3.setFillForegroundColor(IndexedColors.LIME.getIndex());
			//로우그리기
			for(int i=0; i<cellPhoneList.size(); i++) {
				ParamMap rowData = new ParamMap();
				Row row = sheet.createRow(i+1);
				rowData.set("phone_key", cellPhoneList.get(i).getPhoneKey());
				rowData.set("sosok", cellPhoneList.get(i).getSosok());
				rowData.set("tel_no", cellPhoneList.get(i).getTelNo());
				rowData.set("user_nm", cellPhoneList.get(i).getUserNm());
				rowData.set("ext_mail", cellPhoneList.get(i).getExtMail());
				rowData.set("webex_no", cellPhoneList.get(i).getWebexNo());
				rowData.set("katalk_id", cellPhoneList.get(i).getKatalkId());
				
				for(int j=0; j<headers.length; j++) {
					Cell cell = row.createCell(j);
			
					if(columnType[j].equalsIgnoreCase("Int")) {
						cell.setCellValue(rowData.getInt(headers[j]));
						cell.setCellStyle(cellStyle2);
					} else if(columnType[j].equalsIgnoreCase("String")) {
						cell.setCellValue(rowData.getString(headers[j]));
						cell.setCellStyle(cellStyle1);
					} else {
						cell.setCellValue(rowData.getString(headers[j]));
						cell.setCellStyle(cellStyle1);
					}
				}
						
			}
						
		//헤더
			for(int j=0; j<headerNames.length; j++) {
				Cell cell = headerRow.createCell(j);
				cell.setCellValue(headerNames[j]);
				cell.setCellStyle(headerStyle3);
				sheet.autoSizeColumn(j);
				sheet.setColumnWidth(j, (sheet.getColumnWidth(j)) + 1024);
			}
						
			//엑셀이름 한글깨짐방지
			 String outputFileName = new String(excelFileName.getBytes("KSC5601"), "8859_1");
			 
			response.setHeader("Set-Cookie", "fileDownload=true; path=/"); 
			response.setHeader("Content-Disposition", String.format("attachment; filename=\""+outputFileName+"_"+Utils.getTimeStampString("yyyyMMdd_HHmm")+".xlsx\""));

			wb.write(response.getOutputStream());
			wb.close();
		} else {
			response.setHeader("Content-Type", "text/html; charset=UTF-8");
			PrintWriter		out	= response.getWriter();
			
			out.write("<!DOCTYPE HTML PUBLIC \"-//W3C//DTD HTML 4.01 Transitional//EN\" \"http://www.w3.org/TR/html4/loose.dtd\">");
			out.write("<html lang=\"ko\">");
			out.write("<head>");
			out.write("<script type=\"text/javascript\">");
			out.write("alert('데이터가 없습니다.');");
			out.write("history.back(-1);");
			out.write("</script>");
			out.write("</head>");
			out.write("</html>");
			
			out.flush();
			out.close();
		}		
		
	}

	public static void useListToExcel(List<UseList> useExcelList, HttpServletResponse response, String[] headers,
			String[] headerNames, String[] columnType, String sheetName, String excelFileName) throws IOException  {
		if(Utils.isNotEmpty(useExcelList)) {
			// 메모리에 100개의 행을 유지합니다. 행의 수가 넘으면 디스크에 적습니다. 
			XSSFWorkbook wb = new XSSFWorkbook(); 
			Sheet sheet = wb.createSheet(sheetName);
			Row headerRow = sheet.createRow(0);
			CellStyle cellStyle1 = wb.createCellStyle();		//쉼표들어간 숫자 양식
			CellStyle cellStyle2 = wb.createCellStyle();		//숫자양식
			CellStyle headerStyle = wb.createCellStyle();		//숫자양식
			CellStyle headerStyle2 = wb.createCellStyle();
			CellStyle headerStyle3 = wb.createCellStyle();
			
			XSSFDataFormat format = wb.createDataFormat();
			cellStyle1.setAlignment(HorizontalAlignment.CENTER);		
			cellStyle2.setDataFormat(format.getFormat("#,##0"));
			cellStyle2.setAlignment(HorizontalAlignment.CENTER);
			headerStyle2.setBorderTop(BorderStyle.THIN);
			headerStyle2.setBorderBottom(BorderStyle.THIN);
			headerStyle2.setBorderLeft(BorderStyle.THIN);
			headerStyle2.setBorderRight(BorderStyle.THIN);
			headerStyle2.setAlignment(HorizontalAlignment.CENTER);
			headerStyle2.setFillPattern(FillPatternType.SOLID_FOREGROUND);
			headerStyle2.setFillForegroundColor((short)3);
			headerStyle2.setFillForegroundColor(IndexedColors.LIME.getIndex());
			headerStyle.setAlignment(HorizontalAlignment.CENTER);
			headerStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
			headerStyle.setFillForegroundColor((short)3);
			headerStyle.setFillForegroundColor(IndexedColors.LIME.getIndex());
			headerStyle3.setBorderTop(BorderStyle.THIN);
			headerStyle3.setBorderBottom(BorderStyle.THIN);
			headerStyle3.setBorderLeft(BorderStyle.THIN);
			headerStyle3.setBorderRight(BorderStyle.THIN);
			headerStyle3.setAlignment(HorizontalAlignment.CENTER);
			headerStyle3.setFillPattern(FillPatternType.SOLID_FOREGROUND);
			headerStyle3.setFillForegroundColor((short)3);
			headerStyle3.setFillForegroundColor(IndexedColors.LIME.getIndex());
			//로우그리기
			for(int i=0; i<useExcelList.size(); i++) {
				ParamMap rowData = new ParamMap();
				Row row = sheet.createRow(i+1);
				rowData.set("use_no", useExcelList.get(i).getUseNo());
				rowData.set("sosok", useExcelList.get(i).getSosok());
				if(useExcelList.get(i).getUseDt() != null) {
					rowData.set("use_dt", useExcelList.get(i).getUseDt().format(DateTimeFormatter.ofPattern("yyyy-MM-dd")));
				}else {
					rowData.set("use_dt", "");	
				}
				if(useExcelList.get(i).getDetailType().equals("PVREUSE007") || useExcelList.get(i).getDetailType().equals("QIRUSE006")) {
					rowData.set("detail_type_name", useExcelList.get(i).getDetailSelf());
				}else {
					rowData.set("detail_type_name", useExcelList.get(i).getDetailTypeName());
				}
				rowData.set("people_cnt", useExcelList.get(i).getPeopleCnt());
				rowData.set("description", useExcelList.get(i).getDescription());
				rowData.set("wrt_dt", useExcelList.get(i).getWrtDt().format(DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm")));			
				for(int j=0; j<headers.length; j++) {
					Cell cell = row.createCell(j);
			
					if(columnType[j].equalsIgnoreCase("Int")) {
						cell.setCellValue(rowData.getInt(headers[j]));
						cell.setCellStyle(cellStyle2);
					} else if(columnType[j].equalsIgnoreCase("String")) {
						cell.setCellValue(rowData.getString(headers[j]));
						cell.setCellStyle(cellStyle1);
					} else {
						cell.setCellValue(rowData.getString(headers[j]));
						cell.setCellStyle(cellStyle1);
					}
				}
						
			}
						
		//헤더
			for(int j=0; j<headerNames.length; j++) {
				Cell cell = headerRow.createCell(j);
				cell.setCellValue(headerNames[j]);
				cell.setCellStyle(headerStyle3);
				sheet.autoSizeColumn(j);
				sheet.setColumnWidth(j, (sheet.getColumnWidth(j)) + 1024);
			}
						
			//엑셀이름 한글깨짐방지
			 String outputFileName = new String(excelFileName.getBytes("KSC5601"), "8859_1");
			 
			response.setHeader("Set-Cookie", "fileDownload=true; path=/"); 
			response.setHeader("Content-Disposition", String.format("attachment; filename=\""+outputFileName+"_"+Utils.getTimeStampString("yyyyMMdd_HHmm")+".xlsx\""));

			wb.write(response.getOutputStream());
			wb.close();
		} else {
			response.setHeader("Content-Type", "text/html; charset=UTF-8");
			PrintWriter		out	= response.getWriter();
			
			out.write("<!DOCTYPE HTML PUBLIC \"-//W3C//DTD HTML 4.01 Transitional//EN\" \"http://www.w3.org/TR/html4/loose.dtd\">");
			out.write("<html lang=\"ko\">");
			out.write("<head>");
			out.write("<script type=\"text/javascript\">");
			out.write("alert('데이터가 없습니다.');");
			out.write("history.back(-1);");
			out.write("</script>");
			out.write("</head>");
			out.write("</html>");
			
			out.flush();
			out.close();
		}
		
	}
	
	
		

}
