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 clummy.classes; import clummy.otherclass.OSFileOrganizer; import java.io.File; import java.io.FileOutputStream; import java.util.ArrayList; import java.util.List; import java.util.Map; import java.util.Set; import java.util.TreeMap; import javax.swing.JFileChooser; import javax.swing.JOptionPane; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.Row; import org.apache.poi.xssf.usermodel.XSSFSheet; import org.apache.poi.xssf.usermodel.XSSFWorkbook; /** * * @author Syariffudin <syariffudinsapri@gmail.com> */ public class WriteToExcel { final int FIRSTNAME = 0; final int LASTNAME = 1; public static List<String> idList; public static List<String> rfidList; public static List<String> firstNameList; public static List<String> lastNameList; public static List<String> fullNameList; public static List<String> otherNameList; public static List<String> modFirstNameList; public static List<String> modLastNameList; public static List<String> modFullNameList; public static List<String> genderList; public static List<String> dobList; public static List<String> addressList; public static List<String> countryList; public static List<String> raceList; public static List<String> scoreList; public static List<String> emailList; public static List<String> bloodTypeList; public static List<String> maritalList; public static List<String> yesNoList; public static List<String> levelList; public static List<String> occupationList; public static List<String> scoreLevelList; public static List<String> ageList; public static List<String> telephoneList; public static List<String> isoAlpha2List; public static List<String> isoAlpha3List; public static List<String> currencyCodeList; public static List<String> currencyNameList; public static List<String> isIndependentList; public static List<String> bankAmount; public static List<String> shuffle1; public static List<String> shuffle1sub; public static List<String> shuffle2; public static List<String> shuffle2sub; public static List<String> shuffle3; public static List<String> shuffle3sub; public static List<String> shuffle4; public static List<String> shuffle4sub; public static List<String> shuffle5; public static List<String> shuffle5sub; public static List<String> shuffle6; public static List<String> shuffle6sub; public static List<String> shuffle7; public static List<String> shuffle7sub; public static List<String> shuffle8; public static List<String> shuffle8sub; public static List<String> shuffle9; public static List<String> shuffle9sub; public static List<String> shuffle10; public static List<String> shuffle10sub; public static List<String> shuffle11; public static List<String> shuffle11sub; public static List<String> shuffle12; public static List<String> shuffle12sub; List<String> nameSourceList; List<String> addressSourceList; List<String> dobSourceList; List<String> genderSourceList; List<String> raceSourceList; XSSFWorkbook workbook; DataHandlingClass dataHandlingClass; String seperatorForShuffleFile = "|"; public WriteToExcel(List<String> nameSourceList, List<String> addressSourceList, List<String> dobSourceList, List<String> genderSourceList, List<String> raceSourceList) { this.nameSourceList = nameSourceList; this.addressSourceList = addressSourceList; this.dobSourceList = dobSourceList; this.genderSourceList = genderSourceList; this.raceSourceList = raceSourceList; //Blank workbook workbook = new XSSFWorkbook(); dataHandlingClass = new DataHandlingClass(); } /** * this method is to write to excel based on the passed lists * @param appendID * @param jfile put null if you want to disable this * @return * @throws java.lang.Exception */ public boolean writeExcel(String appendID, JFileChooser jfile) throws Exception { // if jfile exist then execute the first line, and exist method if (jfile != null) { FileOutputStream out = new FileOutputStream(jfile.getSelectedFile() + ".xlsx"); workbook.write(out); out.close(); System.out.println("File save to" + jfile.getCurrentDirectory()); return true; } idList = new ArrayList<>(); rfidList = new ArrayList<>(); firstNameList = new ArrayList<>(); lastNameList = new ArrayList<>(); fullNameList = new ArrayList<>(); otherNameList = new ArrayList<>(); modFirstNameList = new ArrayList<>(); modLastNameList = new ArrayList<>(); modFullNameList = new ArrayList<>(); genderList = new ArrayList<>(); dobList = new ArrayList<>(); addressList = new ArrayList<>(); countryList = new ArrayList<>(); isoAlpha2List = new ArrayList<>(); isoAlpha3List = new ArrayList<>(); currencyCodeList = new ArrayList<>(); currencyNameList = new ArrayList<>(); isIndependentList = new ArrayList<>(); bankAmount = new ArrayList<>(); raceList = new ArrayList<>(); maritalList = new ArrayList<>(); emailList = new ArrayList<>(); telephoneList = new ArrayList<>(); scoreList = new ArrayList<>(); bloodTypeList = new ArrayList<>(); yesNoList = new ArrayList<>(); levelList = new ArrayList<>(); occupationList = new ArrayList<>(); scoreLevelList = new ArrayList<>(); ageList = new ArrayList<>(); shuffle1 = new ArrayList<>(); shuffle1sub = new ArrayList<>(); shuffle2 = new ArrayList<>(); shuffle2sub = new ArrayList<>(); shuffle3 = new ArrayList<>(); shuffle3sub = new ArrayList<>(); shuffle4 = new ArrayList<>(); shuffle4sub = new ArrayList<>(); shuffle5 = new ArrayList<>(); shuffle5sub = new ArrayList<>(); shuffle6 = new ArrayList<>(); shuffle6sub = new ArrayList<>(); shuffle7 = new ArrayList<>(); shuffle7sub = new ArrayList<>(); shuffle8 = new ArrayList<>(); shuffle8sub = new ArrayList<>(); shuffle9 = new ArrayList<>(); shuffle9sub = new ArrayList<>(); shuffle10 = new ArrayList<>(); shuffle10sub = new ArrayList<>(); shuffle11 = new ArrayList<>(); shuffle11sub = new ArrayList<>(); shuffle12 = new ArrayList<>(); shuffle12sub = new ArrayList<>(); if (appendID == null || appendID.isEmpty()) appendID = ""; //Create a blank sheet XSSFSheet sheet = workbook.createSheet("Dummy Data"); //This data needs to be written (Object[]) Map<String, Object[]> data = new TreeMap<>(); data.put("1", new Object[] { "ID", "REF-ID", "FirstName", "LastName", "FullName", "OtherName", "Mod First Name", "Mod Last Name", "Mod Full Name", "Gender", "DOB", "Age", "Address", "Country", "Country ISO3166-1-Alpha-2", "Country ISO3166-1-Alpha-3", "Country Currency Code", "Currency Name", "Is Country Independent", "Race", "Status", "Occupation", "Email", "Telephone", "Blood Type", "Yes/No", "Random Number", "Number Level", "Random Level", "Bank Amount", "Shuffle List 1", "Sub Shuffle 1", "Shuffle List 2", "Sub Shuffle 2", "Shuffle List 3", "Sub Shuffle 3", "Shuffle List 4", "Sub Shuffle 4", "Shuffle List 5", "Sub Shuffle 5", "Shuffle List 6", "Sub Shuffle 6", "Shuffle List 7", "Sub Shuffle 7", "Shuffle List 8", "Sub Shuffle 8", "Shuffle List 9", "Sub Shuffle 9", "Shuffle List 10", "Sub Shuffle 10", "Shuffle List 11", "Sub Shuffle 11", "Shuffle List 12", "Sub Shuffle 12" }); int z = 2; String tempSourceName[], tempModName[]; String idTemp, refTemp, countryTemp, email, maritalString, bloodString, yesNoString, randomLevelString, occupationString, scoreLevelString, tel, isoA2, isoA3, currencyCode, currencyName, isIndependent, bankAmountString, otherName, modFullName, modFirstName, modLastName, firstShuffle1String, shuffle1String, subsf1, firstShuffle2String, shuffle2String, subsf2, firstShuffle3String, shuffle3String, subsf3, firstShuffle4String, shuffle4String, subsf4, firstShuffle5String, shuffle5String, subsf5, firstShuffle6String, shuffle6String, subsf6, firstShuffle7String, shuffle7String, subsf7, firstShuffle8String, shuffle8String, subsf8, firstShuffle9String, shuffle9String, subsf9, firstShuffle10String, shuffle10String, subsf10, firstShuffle11String, shuffle11String, subsf11, firstShuffle12String, shuffle12String, subsf12; int scoreTemp, age; for (int i = 0; i < nameSourceList.size(); i++) { shuffle1String = returnNullAsEmpty(dataHandlingClass.getShuffleListFile(AllFileList.SHUFFLELISTFILE_1)); shuffle2String = returnNullAsEmpty(dataHandlingClass.getShuffleListFile(AllFileList.SHUFFLELISTFILE_2)); shuffle3String = returnNullAsEmpty(dataHandlingClass.getShuffleListFile(AllFileList.SHUFFLELISTFILE_3)); shuffle4String = returnNullAsEmpty(dataHandlingClass.getShuffleListFile(AllFileList.SHUFFLELISTFILE_4)); shuffle5String = returnNullAsEmpty(dataHandlingClass.getShuffleListFile(AllFileList.SHUFFLELISTFILE_5)); shuffle6String = returnNullAsEmpty(dataHandlingClass.getShuffleListFile(AllFileList.SHUFFLELISTFILE_6)); shuffle7String = returnNullAsEmpty(dataHandlingClass.getShuffleListFile(AllFileList.SHUFFLELISTFILE_7)); shuffle8String = returnNullAsEmpty(dataHandlingClass.getShuffleListFile(AllFileList.SHUFFLELISTFILE_8)); shuffle9String = returnNullAsEmpty(dataHandlingClass.getShuffleListFile(AllFileList.SHUFFLELISTFILE_9)); shuffle10String = returnNullAsEmpty( dataHandlingClass.getShuffleListFile(AllFileList.SHUFFLELISTFILE_10)); shuffle11String = returnNullAsEmpty( dataHandlingClass.getShuffleListFile(AllFileList.SHUFFLELISTFILE_11)); shuffle12String = returnNullAsEmpty( dataHandlingClass.getShuffleListFile(AllFileList.SHUFFLELISTFILE_12)); tempSourceName = nameSourceList.get(i).split(" ", 2); modFullName = dataHandlingClass.getDifferentNameFromFullName(nameSourceList.get(i)); tempModName = modFullName.split(" ", 2); data.put("" + (i + z), new Object[] { idTemp = appendID + String.format("%05d", i + 1), //id refTemp = "REF-" + appendID + String.format("%05d", i + 1), tempSourceName[FIRSTNAME], tempSourceName[LASTNAME], nameSourceList.get(i), otherName = dataHandlingClass.getFirstNameAndLastNameInitial(nameSourceList.get(i)), modFirstName = tempModName[FIRSTNAME], modLastName = tempModName[LASTNAME], modFullName, genderSourceList.get(i), dobSourceList.get(i), age = dataHandlingClass.getAgeOfDobOfReceivedYear(dobSourceList.get(i)), addressSourceList.get(i), countryTemp = (getLastStringOf(addressSourceList.get(i), ",")).toUpperCase().substring(1), //changes for country isoA2 = dataHandlingClass.getISOAlpha2FromList(countryTemp), isoA3 = dataHandlingClass.getISOAlpha3FromList(countryTemp), currencyCode = dataHandlingClass.getCurrencyCodeFromList(countryTemp), currencyName = dataHandlingClass.getCurrencyNameFromList(countryTemp), isIndependent = dataHandlingClass.getInDependentFromList(countryTemp), raceSourceList.get(i), maritalString = dataHandlingClass.getShuffledMaritalString(age), occupationString = dataHandlingClass.getShuffledOccupation(age), email = dataHandlingClass.getEmailAddress(tempSourceName[FIRSTNAME], tempSourceName[LASTNAME]), tel = dataHandlingClass.getRandomPhoneNumberByCountry(countryTemp), bloodString = dataHandlingClass.getShuffledBloodType(), yesNoString = dataHandlingClass.getYesNo(), scoreTemp = DataHandlingClass.randBetween(DataHandlingClass.minMaxScore[0], DataHandlingClass.minMaxScore[1] - 1), scoreLevelString = dataHandlingClass.getScoreCompareLevel(scoreTemp), randomLevelString = dataHandlingClass.getShuffledLevel(), bankAmountString = scoreTemp + " " + currencyCode, //random data string from shuffle file list firstShuffle1String = returnNullAsEmpty( shuffle1String.split("\\" + seperatorForShuffleFile)[0]), subsf1 = getLastStringOf(shuffle1String, seperatorForShuffleFile), firstShuffle2String = returnNullAsEmpty( shuffle2String.split("\\" + seperatorForShuffleFile)[0]), subsf2 = getLastStringOf(shuffle2String, seperatorForShuffleFile), firstShuffle3String = returnNullAsEmpty( shuffle3String.split("\\" + seperatorForShuffleFile)[0]), subsf3 = getLastStringOf(shuffle3String, seperatorForShuffleFile), firstShuffle4String = returnNullAsEmpty( shuffle4String.split("\\" + seperatorForShuffleFile)[0]), subsf4 = getLastStringOf(shuffle4String, seperatorForShuffleFile), firstShuffle5String = returnNullAsEmpty( shuffle5String.split("\\" + seperatorForShuffleFile)[0]), subsf5 = getLastStringOf(shuffle5String, seperatorForShuffleFile), firstShuffle6String = returnNullAsEmpty( shuffle6String.split("\\" + seperatorForShuffleFile)[0]), subsf6 = getLastStringOf(shuffle6String, seperatorForShuffleFile), firstShuffle7String = returnNullAsEmpty( shuffle7String.split("\\" + seperatorForShuffleFile)[0]), subsf7 = getLastStringOf(shuffle7String, seperatorForShuffleFile), firstShuffle8String = returnNullAsEmpty( shuffle8String.split("\\" + seperatorForShuffleFile)[0]), subsf8 = getLastStringOf(shuffle8String, seperatorForShuffleFile), firstShuffle9String = returnNullAsEmpty( shuffle9String.split("\\" + seperatorForShuffleFile)[0]), subsf9 = getLastStringOf(shuffle9String, seperatorForShuffleFile), firstShuffle10String = returnNullAsEmpty( shuffle10String.split("\\" + seperatorForShuffleFile)[0]), subsf10 = getLastStringOf(shuffle10String, seperatorForShuffleFile), firstShuffle11String = returnNullAsEmpty( shuffle11String.split("\\" + seperatorForShuffleFile)[0]), subsf11 = getLastStringOf(shuffle11String, seperatorForShuffleFile), firstShuffle12String = returnNullAsEmpty( shuffle12String.split("\\" + seperatorForShuffleFile)[0]), subsf12 = getLastStringOf(shuffle12String, seperatorForShuffleFile) }); z++; /* to store in global variable*/ idList.add(idTemp); rfidList.add(refTemp); firstNameList.add(tempSourceName[FIRSTNAME]); lastNameList.add(tempSourceName[LASTNAME]); fullNameList.add(nameSourceList.get(i)); otherNameList.add(otherName); modFirstNameList.add(modFirstName); modLastNameList.add(modLastName); modFullNameList.add(modFullName); genderList.add(genderSourceList.get(i)); dobList.add(dobSourceList.get(i)); ageList.add(age + ""); addressList.add(addressSourceList.get(i)); countryList.add(countryTemp); isoAlpha2List.add(isoA2); isoAlpha3List.add(isoA3); currencyCodeList.add(currencyCode); currencyNameList.add(currencyName); isIndependentList.add(isIndependent); raceList.add(raceSourceList.get(i)); maritalList.add(maritalString); occupationList.add(occupationString); emailList.add(email); telephoneList.add(tel); bloodTypeList.add(bloodString); yesNoList.add(yesNoString); scoreList.add(scoreTemp + ""); scoreLevelList.add(scoreLevelString); levelList.add(randomLevelString); bankAmount.add(bankAmountString); shuffle1.add(firstShuffle1String); shuffle1sub.add(returnNullAsEmpty(subsf1)); shuffle2.add(firstShuffle2String); shuffle2sub.add(returnNullAsEmpty(subsf2)); shuffle3.add(firstShuffle3String); shuffle3sub.add(returnNullAsEmpty(subsf3)); shuffle4.add(firstShuffle4String); shuffle4sub.add(returnNullAsEmpty(subsf4)); shuffle5.add(firstShuffle5String); shuffle5sub.add(returnNullAsEmpty(subsf5)); shuffle6.add(firstShuffle6String); shuffle6sub.add(returnNullAsEmpty(subsf6)); shuffle7.add(firstShuffle7String); shuffle7sub.add(returnNullAsEmpty(subsf7)); shuffle8.add(firstShuffle8String); shuffle8sub.add(returnNullAsEmpty(subsf8)); shuffle9.add(firstShuffle9String); shuffle9sub.add(returnNullAsEmpty(subsf9)); shuffle10.add(firstShuffle10String); shuffle10sub.add(returnNullAsEmpty(subsf10)); shuffle11.add(firstShuffle11String); shuffle11sub.add(returnNullAsEmpty(subsf11)); shuffle12.add(firstShuffle12String); shuffle12sub.add(returnNullAsEmpty(subsf12)); } //Iterate over data and write to sheet Set<String> keyset = data.keySet(); int rownum = 0; for (String key : keyset) { Row row = sheet.createRow(rownum++); Object[] objArr = data.get(key); int cellnum = 0; for (Object obj : objArr) { Cell cell = row.createCell(cellnum++); if (obj instanceof String) cell.setCellValue((String) obj); else if (obj instanceof Integer) cell.setCellValue((Integer) obj); } } try { //Write the workbook in file system if (jfile == null) { FileOutputStream out = new FileOutputStream(new File(AllFileList.EXCELFILE)); workbook.write(out); out.close(); System.out.println(AllFileList.EXCELFILE + " written successfully on disk."); } } catch (Exception e) { JOptionPane.showMessageDialog(null, "Please make sure that no file name: " + AllFileList.EXCELFILE + " is open\n" + "Please close the mentioned file and try again", "Operation Failed!", JOptionPane.ERROR_MESSAGE); e.printStackTrace(); return true; } return true; } /** * this method is to open file based on the location and the filename * @param locationWithFile * @param fileName */ public void openfile(String locationWithFile, String fileName) { OSFileOrganizer.openFile(new File(locationWithFile)); System.out.println("File " + fileName + " successfully opened\n"); } /** * get the last string after a specific character, * if no seperator, return empty string * @param fullString * @param charac * @return */ public String getLastStringOf(String fullString, String charac) { if (fullString.substring(fullString.lastIndexOf(charac) + 1).equalsIgnoreCase(fullString)) return ""; else return fullString.substring(fullString.lastIndexOf(charac) + 1); } /** * incase that null error is thrown, replace with "" * @param strg * @return */ public String returnNullAsEmpty(String strg) { if (strg == null || strg.isEmpty()) return ""; else return strg; } }