package debug;

import java.io.FileOutputStream;
import java.util.HashMap;
import java.util.Map;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.DataFormat;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.PrintSetup;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class Debug {
	private FileOutputStream outbook;
    public String[] headers = new String[] {"USER ID", "FULL NAME", "GROUP MEMBERSHIP"};

	public static void main(String[] args) {
		Debug exec = new Debug();
		Object[] userInfo = new Object[5];
		userInfo[0] = new String[] {"JDOE", "Doe, John", "Everyone"};
		userInfo[1] = new String[] {"FDOE",	"Doe, Fred", "General Users"};
		userInfo[2] = new String[] {"JSMITH", "Smith, John", "General Users"};
		userInfo[3] = new String[] {"SSMITH", "Smith, Sally", "LBR_Read"};
		userInfo[4] = new String[] {"WHAYES", "Hayes, Woody", "Administrator"};

		exec.processExcelFile(userInfo);
	}
	
	private boolean processExcelFile(Object[] records) {
		boolean fileSucceeded = true;
    	
		int listSize = records.length;
		try {
			outbook = new FileOutputStream("C:\\Temp\\Debug_1_7_21.xlsx");
			Workbook wb = null;
	        wb = new XSSFWorkbook();
	        Sheet sheet = wb.createSheet("User Membership Listing");

	        //Set Sheet Defaults
	        sheet.setDisplayGridlines(true);
	        sheet.setPrintGridlines(true);
	        sheet.setFitToPage(false);
	        sheet.setHorizontallyCenter(true);
	        PrintSetup printSetup = sheet.getPrintSetup();
	        printSetup.setLandscape(true);
	        Map<String, CellStyle> styles = createStyles(wb);
	        
	        //the header row: centered text in 48pt font
	        Row headerRow = sheet.createRow(0);
	        headerRow.setHeightInPoints(12.75f);
	        int columnSize = headers.length;
	        for (int i = 0; i < columnSize; i++) {
	            Cell cell = headerRow.createCell(i);
	            cell.setCellValue(headers[i]);
	            cell.setCellStyle(styles.get("header"));
	        }
	        //freeze the first row
	        sheet.createFreezePane(0, 1);
	        int rownum = 1;
	        for (int u = 0; u < listSize; u++, rownum++) {
		        String[] record = (String[]) records[u];
		        Row row;
		        Cell cell;
	            row = sheet.createRow(rownum);
	            
	            // Set the row Style
                String styleName;
               	styleName = "cell_normal";

	            for (int j = 0; j < columnSize; j++) {
	                cell = row.createCell(j);
	                cell.setCellValue(record[j]);
	                cell.setCellStyle(styles.get(styleName));
		        }
	        }
	        
			 //AutoSize the column? 
            sheet.autoSizeColumn((short)0);
            sheet.autoSizeColumn((short)1);
            sheet.autoSizeColumn((short)2);
            sheet.autoSizeColumn((short)3);
            sheet.autoSizeColumn((short)5);
            sheet.autoSizeColumn((short)6);
            sheet.autoSizeColumn((short)7);
            sheet.autoSizeColumn((short)8);

	       
	        // Write the output to a file
        	wb.write(outbook);
        } catch (Exception ex) {
        	System.out.println("Could not create Exel file." + ex.getMessage());
        	ex.printStackTrace();
        	fileSucceeded =  false;
        } finally {
        	try {
        		outbook.close();
        	} catch (Exception closeE) {
        		System.out.println(closeE.getMessage());
        	}
        }
		return fileSucceeded;
	}
	
	
    /**
     * create a library of cell styles
     */
    private static Map<String, CellStyle> createStyles(Workbook wb){
        Map<String, CellStyle> styles = new HashMap<String, CellStyle>();
        DataFormat df = wb.createDataFormat();

        CellStyle style;
        org.apache.poi.ss.usermodel.Font headerFont = wb.createFont();
        headerFont.setBoldweight(org.apache.poi.ss.usermodel.Font.BOLDWEIGHT_BOLD);
        style = createBorderedStyle(wb);
        style.setAlignment(CellStyle.ALIGN_CENTER);
        
        style.setFillForegroundColor(IndexedColors.ORANGE.getIndex());
        style.setFillPattern(CellStyle.SOLID_FOREGROUND);
        style.setFont(headerFont);
        styles.put("header", style);

        style = createBorderedStyle(wb);
        style.setAlignment(CellStyle.ALIGN_CENTER);
        style.setFillForegroundColor(IndexedColors.LIGHT_CORNFLOWER_BLUE.getIndex());
        style.setFillPattern(CellStyle.SOLID_FOREGROUND);
        style.setFont(headerFont);
        style.setDataFormat(df.getFormat("d-mmm"));
        styles.put("header_date", style);

        org.apache.poi.ss.usermodel.Font font1 = wb.createFont();
        font1.setBoldweight(org.apache.poi.ss.usermodel.Font.BOLDWEIGHT_BOLD);
        style = createBorderedStyle(wb);
        style.setAlignment(CellStyle.ALIGN_LEFT);
        style.setFont(font1);
        styles.put("cell_b", style);

        style = createBorderedStyle(wb);
        style.setAlignment(CellStyle.ALIGN_CENTER);
        style.setFont(font1);
        styles.put("cell_b_centered", style);

        style = createBorderedStyle(wb);
        style.setAlignment(CellStyle.ALIGN_RIGHT);
        style.setFont(font1);
        style.setDataFormat(df.getFormat("d-mmm"));
        styles.put("cell_b_date", style);

        style = createBorderedStyle(wb);
        style.setAlignment(CellStyle.ALIGN_RIGHT);
        style.setFont(font1);
        style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
        style.setFillPattern(CellStyle.SOLID_FOREGROUND);
        style.setDataFormat(df.getFormat("d-mmm"));
        styles.put("cell_g", style);

        org.apache.poi.ss.usermodel.Font font2 = wb.createFont();
        font2.setColor(IndexedColors.BLUE.getIndex());
        font2.setBoldweight(org.apache.poi.ss.usermodel.Font.BOLDWEIGHT_BOLD);
        style = createBorderedStyle(wb);
        style.setAlignment(CellStyle.ALIGN_LEFT);
        style.setFont(font2);
        styles.put("cell_bb", style);

        style = createBorderedStyle(wb);
        style.setAlignment(CellStyle.ALIGN_RIGHT);
        style.setFont(font1);
        style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
        style.setFillPattern(CellStyle.SOLID_FOREGROUND);
        style.setDataFormat(df.getFormat("d-mmm"));
        styles.put("cell_bg", style);

        org.apache.poi.ss.usermodel.Font font3 = wb.createFont();
        font3.setFontHeightInPoints((short)14);
        font3.setColor(IndexedColors.DARK_BLUE.getIndex());
        font3.setBoldweight(org.apache.poi.ss.usermodel.Font.BOLDWEIGHT_BOLD);
        style = createBorderedStyle(wb);
        style.setAlignment(CellStyle.ALIGN_LEFT);
        style.setFont(font3);
        style.setWrapText(true);
        styles.put("cell_h", style);

        style = createBorderedStyle(wb);
        style.setAlignment(CellStyle.ALIGN_LEFT);
        style.setWrapText(true);
        styles.put("cell_normal", style);

        style = createBorderedStyle(wb);
        style.setAlignment(CellStyle.ALIGN_CENTER);
        style.setWrapText(true);
        styles.put("cell_normal_centered", style);

        style = createBorderedStyle(wb);
        style.setAlignment(CellStyle.ALIGN_RIGHT);
        style.setWrapText(true);
        style.setDataFormat(df.getFormat("d-mmm"));
        styles.put("cell_normal_date", style);

        style = createBorderedStyle(wb);
        style.setAlignment(CellStyle.ALIGN_LEFT);
        style.setIndention((short)1);
        style.setWrapText(true);
        styles.put("cell_indented", style);

        style = createBorderedStyle(wb);
        style.setFillForegroundColor(IndexedColors.SKY_BLUE.getIndex());
        style.setFillPattern(CellStyle.SOLID_FOREGROUND);
        styles.put("cell_blue", style);
        
        style = createBorderedStyle(wb);
        style.setFillForegroundColor(IndexedColors.ROSE.getIndex());
        style.setFillPattern(CellStyle.SOLID_FOREGROUND);
        styles.put("cell_disabled", style);
        
        style = createBorderedStyle(wb);
        style.setFillForegroundColor(IndexedColors.LIGHT_ORANGE.getIndex());
        style.setFillPattern(CellStyle.SOLID_FOREGROUND);
        styles.put("cell_sched", style);

        return styles;
    }
	
    private static CellStyle createBorderedStyle(Workbook wb){
        CellStyle style = wb.createCellStyle();
        style.setBorderRight(CellStyle.BORDER_THIN);
        style.setRightBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderBottom(CellStyle.BORDER_THIN);
        style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderLeft(CellStyle.BORDER_THIN);
        style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderTop(CellStyle.BORDER_THIN);
        style.setTopBorderColor(IndexedColors.BLACK.getIndex());
        return style;
    }

}