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

/* Read in all the data from the Excel file supplied on the
   command line, and generate A Word file holding a table of
   that data.

   Compare this to Extractor.java

  Usage:
    > compile SheetToWordTable.java
    > run SheetToWordTable <Excel filename>

    e.g.
    > run SheetToWordTable readings.xlsx
       -- generates readings.docx

    > run SheetToWordTable Thailand.xlsx
       -- generates Thailand.docx
*/

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

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xwpf.usermodel.*;
import org.apache.poi.xwpf.model.*;

import org.openxmlformats.schemas.wordprocessingml.x2006.main.*;
    // needed for CT classes used in createSimpleField();
    /* CT classes are for (XML) functionality that isn't 
       implemented in POI yet
    */


public class SheetToWordTable
{

  public static void main(String[] args)
  {
    if (args.length != 1) {
      System.out.println("Usage: run SheetToWordTable <Excel fnm>");
      System.exit(0);
    }
    String fnm = args[0];

    if (!POIUtils.isExcel(fnm)) {
      System.out.println(fnm + " is not an Excel file");
      System.exit(1);
    }

    ArrayList<ArrayList<String>> table = readTable(fnm);
    writeTable(table, POIUtils.stripExt(fnm)+".docx");
  }  // end of main()



  private static ArrayList<ArrayList<String>> readTable(String fnm)
  /* Store each row of the Excel file as a String list, and
     return the table data as a list of those lists.
  */
  {
    ArrayList<ArrayList<String>> table =  new ArrayList<>();

    try (FileInputStream fis = new FileInputStream(fnm)) {
      // Handles both XSSF and HSSF automatically
      Workbook workbook = WorkbookFactory.create(fis);
      Sheet sheet = workbook.getSheetAt(0);
      int numRows = sheet.getLastRowNum();
      int numMaxCols = sheet.getRow(0).getLastCellNum();

      for (int r=0; r < numRows; r++) {
        Row row = sheet.getRow(r);
        ArrayList<String> rowList = new ArrayList<>();

        int numCols = row.getLastCellNum();

        if (numCols < 1) {
          System.out.println("Ignoring empty row: " + (r+1));
        }
        else if (numCols != numMaxCols) {
          System.out.println("Mismatch of columns in row " + 
                  (r+1) +  ": " + numCols + " != " + numMaxCols);
          if (numCols > numMaxCols)
            numMaxCols = numCols;
        }

        for(int c=0; c < numCols; c++) {
          Cell cell = row.getCell(c,  
                           Row.MissingCellPolicy.CREATE_NULL_AS_BLANK);
          // System.out.print(cell + "; ");
          if (cell.getCellType() == CellType.NUMERIC) {
            double val = cell.getNumericCellValue();
            if ((val%1) == 0)  // test if val is an integer
              rowList.add(""+(int)val);
            else
              rowList.add(""+val);
          }
          else
            rowList.add(cell.toString());
        }

        // System.out.println();
        if (!rowList.isEmpty())
          table.add(rowList);
      }
      System.out.println("Table no. rows: " + numRows);
      System.out.println("Table no. cols: " + numMaxCols);
    }
    catch (Exception e) {
      System.out.println(e);
      System.exit(1);
    } 

    return table;
  } /* readTable */


  private static void writeTable(ArrayList<ArrayList<String>> table,
                                 String fnm)
  /* Use each String list in the list to populate one row of a
     Word table.
  */
  {
    int numRows = table.size();
    int numCols = table.get(0).size();

    XWPFDocument doc = new XWPFDocument();

    // add a title to the Word document
    XWPFParagraph p = doc.createParagraph();
    p.setAlignment(ParagraphAlignment.CENTER);
    p.setVerticalAlignment(TextAlignment.TOP);

    XWPFRun run = p.createRun();
    run.setBold(true);
    run.setFontSize(18);
    run.setText("Reading List");

    pageFooter(doc);

    XWPFTable wordTable = doc.createTable(numRows, numCols);

    for (int r=0; r < numRows; r++) {
      ArrayList<String> rowTable = table.get(r);
      for (int c=0; c < rowTable.size(); c++) {
        XWPFParagraph para = wordTable.getRow(r).getCell(c).
                                         getParagraphs().get(0);
        XWPFRun prun = para.createRun();
        prun.setText(rowTable.get(c));
      }
    }

    try (OutputStream out = new FileOutputStream(fnm)) {
      System.out.println("Writing table to " + fnm);
      doc.write(out);
    }
    catch(Exception e) {
      System.out.println(e);
    }
  } /* writeTable() */



  private static void pageFooter(XWPFDocument doc) 
  // add a page number in the document's footer
  {
    XWPFHeaderFooterPolicy hfp = doc.createHeaderFooterPolicy();
    XWPFFooter ftr = hfp.createFooter(XWPFHeaderFooterPolicy.DEFAULT);

    XWPFParagraph p = ftr.createParagraph();
    p.setAlignment(ParagraphAlignment.CENTER);
    XWPFRun pr = p.createRun();
    // pr.setText("Page ");
    pr = createSimpleField(p, "PAGE", "1");
  }  // end of createFooter()


  private static XWPFRun createSimpleField(XWPFParagraph para, 
                              String instr, String dft) 
  // https://stackoverflow.com/questions/51422109/how-to-add-page-number-on-the-docx-creating-using-apache-poi-and-java
  {
    CTP ctp = para.getCTP();
    CTSimpleField field = ctp.addNewFldSimple();
    field.setInstr(instr);
    CTR r = field.addNewR();  // "r" element
    CTText t = r.addNewT();   // "t" element
    t.setStringValue(dft);
    return new XWPFFieldRun(field, r, para);
  }  // end of createSimpleField()



}  // end of SheetToWordTable class