Java tutorial
/* * To change this license header, choose License Headers in Project Properties. * To change this template file, choose Tools | Templates * and open the template in the editor. */ package Utilities; import java.io.*; import java.text.ParseException; import java.text.SimpleDateFormat; import java.util.*; import java.util.logging.Level; import java.util.logging.Logger; import javax.swing.JOptionPane; 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.Font; 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.usermodel.Workbook; import org.apache.poi.xssf.usermodel.XSSFWorkbook; /** * * @author bob */ public class ExportToXLSX { private static final SimpleDateFormat fmt = new SimpleDateFormat("yyMMdd"); private final Workbook wb = new XSSFWorkbook(); private final CellStyle dateStyle = createDateStyle(); private final CellStyle standardStyle = createStandardStyle(); private final CellStyle formulaStyle = createFormulaStyle(); private final CellStyle ssnStyle = createSSNStyle(); private Sheet sheet; private Boolean isCopy; // if isCopy, the resulting xlsx file contains copy surfix. // given destination fileName and export to the xlsx file public ExportToXLSX(String destFileName, Boolean isNew) throws ParseException, IOException { // //XlsxWriter xw = new XlsxWriter(isNew); //check if the fileName contains the extension isCopy = false; String[] fileInfo = destFileName.split("\\."); if (fileInfo.length == 1) { destFileName += ".xlsx"; } File file = new File(destFileName); if (file.exists()) { int response = JOptionPane.showConfirmDialog(null, destFileName + " " + "already exsits. Do you want to overwrite it?", "Confirm", JOptionPane.YES_NO_OPTION, JOptionPane.QUESTION_MESSAGE); if (response == JOptionPane.YES_OPTION) { //createXlsxMain(isNew, destFileName); createHeaderRow(isNew, destFileName); isCopy = false; } else if (response == JOptionPane.NO_OPTION) { destFileName = fileInfo[0] + " copy.xlsx"; createHeaderRow(isNew, destFileName); isCopy = true; } } else { //createXlsxMain(isNew, destFileName); createHeaderRow(isNew, destFileName); } } public Boolean getCopyStatus() { return isCopy; } // add data to the excel sheets public void createLeaveBody(boolean isNew, String destFileName) throws ParseException, IOException { if (!isNew) { String dbPath = GlobalVar.FOLDER_NAME + "/" + GlobalVar.SIGN_IN_LEAVE_DB_NAME + GlobalVar.FILE_EXT; //createSignedLeaveBody(dbPath, wb); createSignedLeaveBody(dbPath, destFileName); } else { //signed leave form database String dbPath = GlobalVar.FOLDER_NAME + "/" + GlobalVar.NEW_LEAVE_DB_NAME + GlobalVar.FILE_EXT; createNewLeaveBody(dbPath, destFileName); } } // private void createXlsxMain(Boolean isNew, String destFileName) throws ParseException{ // // try { // createHeaderRow(isNew, destFileName); // } catch (IOException ex) { // Logger.getLogger(ExportToXLSX.class.getName()).log(Level.SEVERE, null, ex); // JOptionPane.showMessageDialog(null, "Failed: worksheet is used by another user!"); // } // // // signed leave form database // if (!isNew) { // String dbPath = GlobalVar.FOLDER_NAME + "/" // + GlobalVar.SIGN_IN_LEAVE_DB_NAME + GlobalVar.FILE_EXT; // //createSignedLeaveBody(dbPath, wb); // createSignedLeaveBody(dbPath, destFileName); // // } else { //signed leave form database // String dbPath = GlobalVar.FOLDER_NAME + "/" // + GlobalVar.NEW_LEAVE_DB_NAME + GlobalVar.FILE_EXT; // createNewLeaveBody(dbPath, destFileName); // } // } // create header for the xlsx file private void createHeaderRow(Boolean isNewLeave, String fileName) throws IOException { // WB = new XSSFWorkbook(); FileOutputStream output; //Sheet sheet; int titleLen; String[] header; // the header of the spreadsheet if (isNewLeave) { // titleLen = GlobalVar.NEW_LEAVE_TITLES.length; // sheet = wb.createSheet("New Leave Form"); // header = GlobalVar.NEW_LEAVE_TITLES; titleLen = GlobalVar.LEAVE_TITLES_V1.length; sheet = wb.createSheet("New Leave Form"); header = GlobalVar.LEAVE_TITLES_V1; } else { // titleLen = GlobalVar.SIGNED_LEAVE_TITLES.length; // sheet = wb.createSheet("SIGNED Leave Form"); // header = GlobalVar.SIGNED_LEAVE_TITLES; titleLen = GlobalVar.LEAVE_TITLES_V1.length; sheet = wb.createSheet("SIGNED Leave Form"); header = GlobalVar.LEAVE_TITLES_V1; } //set column widths, the width is measured in units of 1/256th of a character width for (int i = 0; i < titleLen; i++) { sheet.setColumnWidth(i, 256 * GlobalVar.COL_WIDTH); } // create style for the first row CellStyle style; Font headerFont = wb.createFont(); headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD); style = createStandardStyle(); style.setAlignment(CellStyle.ALIGN_CENTER); style.setFillForegroundColor(IndexedColors.LIGHT_CORNFLOWER_BLUE.getIndex()); style.setFillPattern(CellStyle.SOLID_FOREGROUND); style.setFont(headerFont); Row row = sheet.createRow(0); // first row //set value for the first row for (int i = 0; i < header.length; i++) { Cell cell = row.createCell(i); cell.setCellValue(header[i]); cell.setCellStyle(style); } //freeze the first row sheet.createFreezePane(0, 1); try { output = new FileOutputStream(fileName); wb.write(output); output.close(); } catch (FileNotFoundException ex) { Logger.getLogger(ExportToXLSX.class.getName()).log(Level.SEVERE, null, ex); JOptionPane.showMessageDialog(null, "Failed: Excel worksheet is used by another user!"); } } // add a row of new leave to the spread sheet public void addNewLeave(String destFilePath, String[] data, int rowNum) throws ParseException, FileNotFoundException, IOException { // WB = new XSSFWorkbook(); FileOutputStream output; Calendar calendar = Calendar.getInstance(); // for dates; Row row; Cell cell; //String[] data = input.nextLine().split(GlobalVar.PARSE); //System.out.println(Arrays.toString(data)); row = sheet.createRow(rowNum); // row in excel // String[] header = GlobalVar.NEW_LEAVE_TITLES; String[] header = GlobalVar.LEAVE_TITLES_V1; for (int i = 0; i < header.length; i++) { cell = row.createCell(i); cell.setCellStyle(standardStyle); // matches the items in the header switch (i) { case 0: cell.setCellValue(data[GlobalVar.CN_ID]); // cell.setCellType(GlobalVar.CTRL_NUM_TYPE); break; case 1: cell.setCellValue(Integer.parseInt(data[GlobalVar.LAST4SSN_ID])); cell.setCellStyle(ssnStyle); //cell.setCellType(GlobalVar.SSN_TYPE); break; case 2: cell.setCellValue(data[GlobalVar.LN_ID]); // cell.setCellType(HSSFCell.CELL_TYPE_); break; case 3: calendar.setTime(fmt.parse(data[GlobalVar.PSO_D_ID])); cell.setCellValue(calendar); cell.setCellStyle(dateStyle); break; case 4: calendar.setTime(fmt.parse(data[GlobalVar.PSI_D_ID])); cell.setCellValue(calendar); cell.setCellStyle(dateStyle); break; case 5: switch (data[GlobalVar.LV_A_ID].charAt(0)) { case '1': cell.setCellValue("CONUS"); break; case '2': cell.setCellValue("OCONUS"); break; default: cell.setCellValue(""); } //cell.setCellType(HSSFCell.CELL_TYPE_BLANK); break; case 6: switch (data[GlobalVar.LV_T_ID].charAt(0)) { case 'A': cell.setCellValue("Ordinary"); break; case 'D': cell.setCellValue("Emergency"); break; case 'T': cell.setCellValue("Permissive TDY"); break; case 'F': cell.setCellValue("Convalescent"); break; default: cell.setCellValue(""); } //cell.setCellType(HSSFCell.CELL_TYPE_ERROR); break; case 7: int r = rowNum + 1; //switch from zero based to one based (label on worksheet) // System.out.println(r); String fmla = "IF(AND(E" + r + ",D" + r + "),E" + r + "-D" + r + "+1" + ",\"\")"; cell.setCellFormula(fmla); cell.setCellStyle(formulaStyle); //cell.setCellValue(data[GlobalVar.P_ND_ID]); break; case 8: cell.setCellValue(data[GlobalVar.L5_ID]); // print out the first five chars of names // cell.setCellType(GlobalVar.LAST5_TYPE); break; } } output = new FileOutputStream(destFilePath); wb.write(output); output.close(); } // add a row of signed leave to the spread sheet public void addSignedLeave() { } // generate the body of the spread sheet private void createNewLeaveBody(String dbPath, String destFilePath) throws ParseException, IOException { Scanner input = null; //Calendar calendar = Calendar.getInstance(); // for dates; //CellStyle style = createStandardStyle(); try { input = new Scanner(new File(dbPath)); } catch (FileNotFoundException ex) { Logger.getLogger(ExportToXLSX.class.getName()).log(Level.SEVERE, null, ex); JOptionPane.showMessageDialog(null, "No unsigned leaves found."); } int rowNum = 1; // skip the header line //System.out.println(input.hasNextLine()); while (input.hasNextLine()) { String[] data = input.nextLine().split(GlobalVar.PARSE); addNewLeave(destFilePath, data, rowNum); rowNum++; } // hide the last column (first five chars of the names) sheet.setColumnHidden(8, true); // this will hide the column index 8 FileOutputStream output; try { output = new FileOutputStream(destFilePath); wb.write(output); output.close(); } catch (FileNotFoundException ex) { Logger.getLogger(ExportToXLSX.class.getName()).log(Level.SEVERE, null, ex); } catch (IOException ex) { Logger.getLogger(ExportToXLSX.class.getName()).log(Level.SEVERE, null, ex); } } private void createSignedLeaveBody(String dbPath, String destFilePath) throws ParseException { Scanner input = null; // CellStyle style = createStandardStyle(); Calendar calendar = Calendar.getInstance(); // for dates; try { input = new Scanner(new File(dbPath)); } catch (FileNotFoundException ex) { Logger.getLogger(ExportToXLSX.class.getName()).log(Level.SEVERE, null, ex); JOptionPane.showMessageDialog(null, "No signed leaves found."); } //Sheet sheet = wb.getSheetAt(0); //get the first sheet int rowNum = 1; // skip the header line while (input.hasNextLine()) { Row row; Cell cell; String[] data = input.nextLine().split("`"); // System.out.println(Arrays.toString(data)); row = sheet.createRow(rowNum); // String[] header = GlobalVar.SIGNED_LEAVE_TITLES; for (int i = 0; i < header.length; i++) { cell = row.createCell(i); cell.setCellStyle(standardStyle); // matches the items in the signed leave header switch (i) { case 0: cell.setCellValue(data[GlobalVar.CN_ID]); //cell.setCellType(GlobalVar.CTRL_NUM_TYPE); break; case 1: cell.setCellValue(Integer.parseInt(data[GlobalVar.LAST4SSN_ID])); cell.setCellStyle(ssnStyle); //cell.setCellType(Cell.CELL_TYPE_STRING); break; case 2: cell.setCellValue(data[GlobalVar.LN_ID]); // cell.setCellType(GlobalVar.LASTNAME_TYPE); break; case 3: calendar.setTime(fmt.parse(data[GlobalVar.SO_D_ID])); cell.setCellValue(calendar); cell.setCellStyle(dateStyle); break; case 4: calendar.setTime(fmt.parse(data[GlobalVar.SI_D_ID])); cell.setCellValue(calendar); cell.setCellStyle(dateStyle); //cell.setCellStyle(styles.get(styleName)); break; case 5: switch (data[GlobalVar.LV_A_ID].charAt(0)) { case '1': cell.setCellValue("CONUS"); break; case '2': cell.setCellValue("OCONUS"); break; default: cell.setCellValue(""); } //cell.setCellType(HSSFCell.CELL_TYPE_BLANK); break; case 6: switch (data[GlobalVar.LV_T_ID].charAt(0)) { case 'A': cell.setCellValue("Ordinary"); break; case 'D': cell.setCellValue("Emergency"); break; case 'T': cell.setCellValue("Permissive TDY"); break; case 'F': cell.setCellValue("Convalescent"); break; default: cell.setCellValue(""); } break; case 7: int r = rowNum + 1; //switch from zero based to one based (label on worksheet) String fmla = "IF(AND(E" + r + ",D" + r + "),E" + r + "- D" + r + "+1" + ",\"\")"; //inclusive days cell.setCellFormula(fmla); cell.setCellStyle(formulaStyle); break; case 8: cell.setCellValue(data[GlobalVar.L5_ID]); // print out the first five chars of names //cell.setCellType(GlobalVar.LAST5_TYPE); break; case 9: calendar.setTime(fmt.parse(data[GlobalVar.PSO_D_ID])); cell.setCellValue(calendar); cell.setCellStyle(dateStyle); break; case 10: calendar.setTime(fmt.parse(data[GlobalVar.PSI_D_ID])); cell.setCellValue(calendar); cell.setCellStyle(dateStyle); break; } //switch loop } // for loop rowNum++; } // hide the last column (first five chars of the names) sheet.setColumnHidden(8, true); // this will hide the column index 8 FileOutputStream output; try { output = new FileOutputStream(destFilePath); wb.write(output); output.close(); } catch (FileNotFoundException ex) { Logger.getLogger(ExportToXLSX.class.getName()).log(Level.SEVERE, null, ex); } catch (IOException ex) { Logger.getLogger(ExportToXLSX.class.getName()).log(Level.SEVERE, null, ex); } } private CellStyle createStandardStyle() { 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()); style.setWrapText(true); return style; } private CellStyle createDateStyle() { DataFormat df = wb.createDataFormat(); CellStyle style = createStandardStyle(); style.setDataFormat(df.getFormat("yyMMdd")); style.setAlignment(CellStyle.ALIGN_LEFT); return style; } private CellStyle createSSNStyle() { Font font3 = wb.createFont(); DataFormat df = wb.createDataFormat(); CellStyle style = wb.createCellStyle(); style = createStandardStyle(); style.setDataFormat(df.getFormat("0000")); style.setAlignment(CellStyle.ALIGN_LEFT); //style.setFont(font3); style.setWrapText(true); return style; } private CellStyle createFormulaStyle() { Font font3 = wb.createFont(); DataFormat df = wb.createDataFormat(); CellStyle style = wb.createCellStyle(); // font3.setFontHeightInPoints((short)14); // font3.setColor(IndexedColors.DARK_BLUE.getIndex()); // font3.setBoldweight(Font.BOLDWEIGHT_BOLD); style = createStandardStyle(); style.setAlignment(CellStyle.ALIGN_LEFT); //style.setFont(font3); style.setWrapText(true); return style; } }