// WriteSheet.java
// Andrew Davison, ad@coe.psu.ac.th, Nov. 2022

/* Generate a single sheet Excel file called "WriteSheet.xlsx"
   consiting of six rows of data including a header row.

  Usage:
    > compile WriteSheet.java
    > run WriteSheet
*/

import java.io.*;
import java.util.*;

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.*;


public class WriteSheet
{
  private static final String SS_FNM = "sampleSheet.xlsx";


  public static void main(String[] args)
  {
    XSSFWorkbook workbook = new XSSFWorkbook();
    XSSFSheet sheet = workbook.createSheet("Employee Info");
	
    // set column widths
    sheet.setColumnWidth(0, 10*256);   // 10 chars wide
    sheet.setColumnWidth(1, 20*256); 
    sheet.setColumnWidth(2, 20*256); 
      // column widths are stored as integers in 1/256th's of a character width

    // center the text in column 0
    XSSFCellStyle cs = workbook.createCellStyle();
    cs.setAlignment(HorizontalAlignment.CENTER);
    sheet.setDefaultColumnStyle(0, cs);

    // create data
    Map <String, Object[]> empinfo = new TreeMap<>();
    empinfo.put( "1", new Object[] { "ID", "Name", "Job" });
    empinfo.put( "2", new Object[] { "1", "Rishi Sunak", "Technical Manager" });
    empinfo.put( "3", new Object[] { "2", "Liz Truss", "Proof Reader" });
    empinfo.put( "4", new Object[] { "3", "Boris Johnson", "Technical Writer" });
    empinfo.put( "5", new Object[] { "4", "Theresa May", "Technical Writer" });
    empinfo.put( "6", new Object[] { "5", "David Cameron", "Technical Writer" });

    System.out.println("Writing data to the sheet...");
    Set<String> keyid = empinfo.keySet();
    int rowid = 0;
    for (String key : keyid) {
      XSSFRow row = sheet.createRow(rowid++);
      Object [] objectArr = empinfo.get(key);
      int cellid = 0;
      for (Object obj : objectArr) {
        Cell cell = row.createCell(cellid++);
        cell.setCellValue((String)obj);
      }
    }

    // create a  cell style for the header row
    cs = workbook.createCellStyle();
    IndexedColorMap colorMap = workbook.getStylesSource().getIndexedColors();
    XSSFColor myColor = new XSSFColor(
                  new java.awt.Color(135,206,235), colorMap);    // sky blue
    cs.setFillForegroundColor(myColor);
    cs.setFillPattern(FillPatternType.SOLID_FOREGROUND);

   // apply style to every cell in the row
   XSSFRow headerRow = sheet.getRow(0);
   for (int i = 0; i < headerRow.getLastCellNum(); i++)
     headerRow.getCell(i).setCellStyle(cs);

    // save the workbook
    try (FileOutputStream out = 
             new FileOutputStream(new File(SS_FNM))) {
      System.out.println("Writing sheet to " + SS_FNM);
      workbook.write(out);
    }
    catch(Exception e) 
    {  System.out.println(e); }
  } /* main */


}  // end of WriteSheet class