Example usage for org.apache.poi.xssf.usermodel XSSFSheet createRow

List of usage examples for org.apache.poi.xssf.usermodel XSSFSheet createRow

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.xssf.usermodel.*
  5. XSSFSheet
  6. createRow

Introduction

In this page you can find the example usage for org.apache.poi.xssf.usermodel XSSFSheet createRow.

Prototype

@Override
public XSSFRow createRow(int rownum)

Source Link

Document

Create a new row within the sheet and return the high level representation Note: If a row already exists at this position, it is removed/overwritten and any existing cell is removed!

Usage

From source file:ch.admin.isb.hermes5.business.userszenario.projektstrukturplan.ProjektstrukturplanGeneratorExcel.java

License:Apache License

private int addModul(XSSFSheet sheet, int currentRow, LocalizationEngine localizationEngine,
        XSSFCellStyle ergebnisStyle, XSSFCellStyle modulStyle, XSSFCellStyle defaultStyle, Phase phase,
        Modul modul, List<Aufgabe> aufgabenInPhase, SzenarioItem szenarioTree) {

    XSSFRow modulRow = sheet.createRow(currentRow++);
    String modulName = localizationEngine.localize(modul.getPresentationName());
    XSSFCell modulCell = modulRow.createCell(MAIN_COL);
    modulCell.setCellStyle(modulStyle);/* w ww  .j  av a 2s  .co m*/
    modulCell.setCellValue(
            modulIndent.getStringValue() + (isNotBlank(modulName) ? modulName.toUpperCase() : modulName));
    for (Aufgabe aufgabe : modul.getAufgaben()) {
        if (aufgabenInPhase.contains(aufgabe)) {
            currentRow = addAufgabe(sheet, currentRow, phase, modul, aufgabe, localizationEngine, ergebnisStyle,
                    defaultStyle, szenarioTree);
        }
    }
    return currentRow;
}

From source file:ch.admin.isb.hermes5.business.userszenario.projektstrukturplan.ProjektstrukturplanGeneratorExcel.java

License:Apache License

private int addAufgabe(XSSFSheet sheet, int currentRow, Phase phase, Modul modul, Aufgabe aufgabe,
        LocalizationEngine localizationEngine, XSSFCellStyle ergebnisStyle, XSSFCellStyle defaultStyle,
        SzenarioItem szenarioTree) {//ww w  .jav  a  2  s. c o  m

    XSSFRow row = sheet.createRow(currentRow++);
    XSSFCell aufgabeCell = row.createCell(MAIN_COL);
    aufgabeCell.setCellStyle(defaultStyle);
    aufgabeCell.setCellValue(
            aufgabeIndent.getStringValue() + localizationEngine.localize(aufgabe.getPresentationName()));
    Rolle verantwortlicheRolle = aufgabe.getVerantwortlicheRolle();
    if (verantwortlicheRolle != null) {
        XSSFCell rollCell = row.createCell(VERANTWORTLICH_COL);
        rollCell.setCellStyle(defaultStyle);
        rollCell.setCellValue(localizationEngine.localize(verantwortlicheRolle.getPresentationName()));
    }

    List<Ergebnis> ergebnisse = aufgabe.getErgebnisse();
    for (Ergebnis ergebnis : ergebnisse) {
        if (szenarioTree == null || modul.isCustom()
                || szenarioItemUtil.isErgebnisSelected(szenarioTree, phase, modul, aufgabe, ergebnis)) {

            XSSFRow ergebnisRow = sheet.createRow(currentRow++);
            XSSFCell ergebnisCell = ergebnisRow.createCell(MAIN_COL);
            ergebnisCell.setCellStyle(ergebnisStyle);
            ergebnisCell.setCellValue(ergebnisIndent.getStringValue()
                    + localizationEngine.localize(ergebnis.getPresentationName()));
            XSSFCell verantwortlichCell = ergebnisRow.createCell(VERANTWORTLICH_COL);
            verantwortlichCell.setCellStyle(defaultStyle);
            verantwortlichCell.setCellValue(getVerantwortlichFuerErgebnis(localizationEngine, ergebnis));
        }
    }
    return currentRow;
}

From source file:ch.admin.isb.hermes5.business.userszenario.projektstrukturplan.ProjektstrukturplanGeneratorExcel.java

License:Apache License

private void addHeader(LocalizationEngine localizationEngine, XSSFWorkbook wb, XSSFSheet sheet) {
    XSSFRow headerRow = sheet.createRow(0);
    XSSFCellStyle headerStyle = getHeaderStyle(wb);
    addHeaderCell(headerRow, headerStyle, MAIN_COL,
            localizationEngine.text("al_projektstrukturplan_aufgabeergebnis"));
    addHeaderCell(headerRow, headerStyle, VERANTWORTLICH_COL,
            localizationEngine.text("al_projektstrukturplan_verantwortlich"));
}

From source file:chocanproject.MemberReportGUI.java

private void writeToExcel() {
    XSSFWorkbook wb = new XSSFWorkbook();
    XSSFSheet ws = wb.createSheet();
    //Load data to Treemap
    TreeMap<String, Object[]> data = new TreeMap<>();
    //Add column headers
    data.put("-1",
            new Object[] { dm.getColumnName(0), dm.getColumnName(1), dm.getColumnName(2), dm.getColumnName(3),
                    dm.getColumnName(4), dm.getColumnName(5), dm.getColumnName(6), dm.getColumnName(7),
                    dm.getColumnName(8), dm.getColumnName(9), dm.getColumnName(10) });
    //Add rows and cells
    for (int i = 0; i < dm.getRowCount(); i++) {
        data.put(Integer.toString(i),
                new Object[] { getCellValue(i, 0), getCellValue(i, 1), getCellValue(i, 2), getCellValue(i, 3),
                        getCellValue(i, 4), getCellValue(i, 5), getCellValue(i, 6), getCellValue(i, 7),
                        getCellValue(i, 8), getCellValue(i, 9), getCellValue(i, 10) });
    }// w  w w.  jav a 2  s  .c o  m
    //write to excel file
    Set<String> ids = data.keySet();
    XSSFRow row;
    int rowID = 0;
    for (String key : ids) {
        row = ws.createRow(rowID++);
        //get data as per key
        Object[] values = data.get(key);
        int cellID = 0;
        for (Object o : values) {
            Cell cell = row.createCell(cellID++);
            cell.setCellValue(o.toString());
        }
    }
    //write to filesystem
    try {
        String MName, fName;
        DateFormat dateFormat = new SimpleDateFormat("MM-dd-YYYY");
        Date date = new Date();
        String Cdate;
        MName = fn.toString();
        Cdate = (dateFormat.format(date));
        fName = MName + "_" + Cdate;
        FileOutputStream fos = new FileOutputStream(new File("D:/Excel/" + fName + ".xlsx"));
        wb.write(fos);
        fos.close();
    } catch (FileNotFoundException ex) {
        ex.printStackTrace();
        // Logger.getLogger(WorkBookNSheet.class.getName()).log(Level.SEVERE, null, ex);
    } catch (IOException ex) {
        ex.printStackTrace();
        //Logger.getLogger(MemberReportGUI.class.getName()).log(Level.SEVERE, null, ex);

    }
}

From source file:chocanproject.ProviderReportGUI.java

private void writeToExcel() {
    XSSFWorkbook wb = new XSSFWorkbook();
    XSSFSheet ws = wb.createSheet();
    //Load data to Treemap
    TreeMap<String, Object[]> data = new TreeMap<>();
    //Add column headers
    data.put("-1",
            new Object[] { dp.getColumnName(0), dp.getColumnName(1), dp.getColumnName(2), dp.getColumnName(3),
                    dp.getColumnName(4), dp.getColumnName(5), dp.getColumnName(6), dp.getColumnName(7),
                    dp.getColumnName(8), dp.getColumnName(9), dp.getColumnName(10), dp.getColumnName(11),
                    dp.getColumnName(12), dp.getColumnName(13) });
    //Add rows and cells
    for (int i = 0; i < dp.getRowCount(); i++) {
        data.put(Integer.toString(i),
                new Object[] { getCellValue(i, 0), getCellValue(i, 1), getCellValue(i, 2), getCellValue(i, 3),
                        getCellValue(i, 4), getCellValue(i, 5), getCellValue(i, 6), getCellValue(i, 7),
                        getCellValue(i, 8), getCellValue(i, 9), getCellValue(i, 10), getCellValue(i, 11),
                        getCellValue(i, 12), getCellValue(i, 13) });
    }/*from  w ww  .  ja va 2 s.c o m*/
    //write to excel file
    Set<String> ids = data.keySet();
    XSSFRow row;
    int rowID = 0;
    for (String key : ids) {
        row = ws.createRow(rowID++);
        //get data as per key
        Object[] values = data.get(key);
        int cellID = 0;
        for (Object o : values) {
            Cell cell = row.createCell(cellID++);
            cell.setCellValue(o.toString());
        }
    }
    //write to filesystem
    try {
        String fName;
        DateFormat dateFormat = new SimpleDateFormat("MM-dd-YYYY");
        Date date = new Date();
        String Cdate;
        Cdate = (dateFormat.format(date));
        fName = pfn + "_" + Cdate;
        FileOutputStream fos = new FileOutputStream(new File("D:/Excel/" + fName + ".xlsx"));
        wb.write(fos);
        fos.close();
    } catch (FileNotFoundException ex) {
        ex.printStackTrace();
        // Logger.getLogger(WorkBookNSheet.class.getName()).log(Level.SEVERE, null, ex);
    } catch (IOException ex) {
        ex.printStackTrace();
        //Logger.getLogger(MemberReportGUI.class.getName()).log(Level.SEVERE, null, ex);

    }
}

From source file:chocanproject.SummaryReports.java

private void writeToExcel() {
    XSSFWorkbook wb = new XSSFWorkbook();
    XSSFSheet ws = wb.createSheet();
    //Load data to Treemap
    TreeMap<String, Object[]> data = new TreeMap<>();
    //Add column headers
    data.put("-1", new Object[] { dm.getColumnName(0), dm.getColumnName(1), dm.getColumnName(2) });
    //Add rows and cells
    for (int i = 0; i < dm.getRowCount(); i++) {
        data.put(Integer.toString(i),
                new Object[] { getCellValue(i, 0), getCellValue(i, 1), getCellValue(i, 2) });
    }/* ww  w  . ja  v a2  s.c  o m*/
    //write to excel file
    Set<String> ids = data.keySet();
    XSSFRow row;
    int rowID = 0;
    for (String key : ids) {
        row = ws.createRow(rowID++);
        //get data as per key
        Object[] values = data.get(key);
        int cellID = 0;
        for (Object o : values) {
            Cell cell = row.createCell(cellID++);
            cell.setCellValue(o.toString());
        }
    }
    //write to filesystem
    try {
        DateFormat dateFormat = new SimpleDateFormat("MM-dd-YYYY");
        Date date = new Date();
        String Cdate;
        Cdate = (dateFormat.format(date));
        FileOutputStream fos = new FileOutputStream(
                new File("D:/Excel/SummaryReport-ToBePaid" + Cdate + ".xlsx"));
        wb.write(fos);
        fos.close();
    } catch (FileNotFoundException ex) {
        ex.printStackTrace();
        // Logger.getLogger(WorkBookNSheet.class.getName()).log(Level.SEVERE, null, ex);
    } catch (IOException ex) {
        ex.printStackTrace();
        //Logger.getLogger(MemberReportGUI.class.getName()).log(Level.SEVERE, null, ex);

    }
}

From source file:chocanproject.TotalSummaryReport.java

private void writeToExcel() {
    XSSFWorkbook wb = new XSSFWorkbook();
    XSSFSheet ws = wb.createSheet();
    //Load data to Treemap
    TreeMap<String, Object[]> data = new TreeMap<>();
    //Add column headers
    data.put("-1", new Object[] { dm.getColumnName(0), dm.getColumnName(1), dm.getColumnName(2) });
    //Add rows and cells
    for (int i = 0; i < dm.getRowCount(); i++) {
        data.put(Integer.toString(i),
                new Object[] { getCellValue(i, 0), getCellValue(i, 1), getCellValue(i, 2) });
    }//from   w w  w  .  j  ava2 s  .  c  o  m
    //write to excel file
    Set<String> ids = data.keySet();
    XSSFRow row;
    int rowID = 0;
    for (String key : ids) {
        row = ws.createRow(rowID++);
        //get data as per key
        Object[] values = data.get(key);
        int cellID = 0;
        for (Object o : values) {
            Cell cell = row.createCell(cellID++);
            cell.setCellValue(o.toString());
        }
    }
    //write to filesystem
    try {
        DateFormat dateFormat = new SimpleDateFormat("MM-dd-YYYY");
        Date date = new Date();
        String Cdate;
        Cdate = (dateFormat.format(date));
        FileOutputStream fos = new FileOutputStream(new File("D:/Excel/TotalSummaryReport" + Cdate + ".xlsx"));
        wb.write(fos);
        fos.close();
    } catch (FileNotFoundException ex) {
        ex.printStackTrace();
        // Logger.getLogger(WorkBookNSheet.class.getName()).log(Level.SEVERE, null, ex);
    } catch (IOException ex) {
        ex.printStackTrace();
        //Logger.getLogger(MemberReportGUI.class.getName()).log(Level.SEVERE, null, ex);

    }
}

From source file:clummy.classes.WriteToExcel.java

/**
 * this method is to write to excel based on the passed lists
 * @param appendID // www.j av a 2s. c om
 * @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;
}

From source file:cn.comgroup.tzmedia.server.report.CustomerOrderReport.java

public CustomerOrderReportResult runOrderReport(String deployPath, List<CustomerOrder> orders)
        throws ParseException, FileNotFoundException, IOException {
    String reportTemplate = deployPath + File.separator + "template" + File.separator
            + "CustomerOrderReport.xlsx";
    final XSSFWorkbook workbook = new XSSFWorkbook(new FileInputStream(reportTemplate));
    DateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");
    XSSFSheet sheet = workbook.getSheetAt(0);
    int startRow = 1;
    for (CustomerOrder co : orders) {
        XSSFRow row = sheet.getRow(startRow);
        if (row == null) {
            row = sheet.createRow(startRow);
        }//from   w w w .  j av  a2s.  c  o m
        XSSFCell cellOrderNumber = row.getCell(0);
        if (cellOrderNumber == null) {
            cellOrderNumber = row.createCell(0);
        }

        XSSFCell cellOrderDate = row.getCell(1);
        if (cellOrderDate == null) {
            cellOrderDate = row.createCell(1);
        }
        XSSFCell cellOrderType = row.getCell(2);
        if (cellOrderType == null) {
            cellOrderType = row.createCell(2);
        }
        XSSFCell cellOrderStatus = row.getCell(3);
        if (cellOrderStatus == null) {
            cellOrderStatus = row.createCell(3);
        }
        XSSFCell cellETN = row.getCell(4);
        if (cellETN == null) {
            cellETN = row.createCell(4);
        }
        XSSFCell cellShop = row.getCell(5);
        if (cellShop == null) {
            cellShop = row.createCell(5);
        }
        XSSFCell cellUserId = row.getCell(6);
        if (cellUserId == null) {
            cellUserId = row.createCell(6);
        }
        XSSFCell cellUserName = row.getCell(7);
        if (cellUserName == null) {
            cellUserName = row.createCell(7);
        }
        XSSFCell cellPaymentTerm = row.getCell(8);
        if (cellPaymentTerm == null) {
            cellPaymentTerm = row.createCell(8);
        }
        XSSFCell cellOrderAmount = row.getCell(9);
        if (cellOrderAmount == null) {
            cellOrderAmount = row.createCell(9);
        }
        XSSFCell cellCouponAmount = row.getCell(10);
        if (cellCouponAmount == null) {
            cellCouponAmount = row.createCell(10);
        }
        cellOrderNumber.setCellValue(co.getOrderNumber());
        if (co.getOrderDate() != null) {
            cellOrderDate.setCellValue(dateFormat.format(co.getOrderDate().getTime()));
        } else {
            cellOrderDate.setCellValue(dateFormat.format(co.getOrderTime().getTime()));
        }

        cellOrderType.setCellValue(co.getOrderType().toString());
        cellOrderStatus.setCellValue(co.getOrderStatus().toString());
        if (co.getExternalTransactionNumber() != null) {
            cellETN.setCellValue(co.getExternalTransactionNumber());
        }

        cellShop.setCellValue(co.getShopName());
        cellUserId.setCellValue(co.getUserId());
        cellUserName.setCellValue(co.getUserName());
        cellPaymentTerm.setCellValue(co.getPaymentTerm().toString());
        cellOrderAmount.setCellValue(co.getOrderAmount());
        cellCouponAmount.setCellValue(co.getCouponAmount());
        startRow++;
        System.out.println(startRow + " orders in the report");
    }
    DateFormat dateTimeFormat = new SimpleDateFormat("yyyy-MM-dd-HHMMSS");
    String reportName = "CustomerOrderReport" + dateTimeFormat.format(new Date()) + ".xlsx";
    String reportPath = deployPath + File.separator + reportName;
    FileOutputStream fos = new FileOutputStream(reportPath);
    try (BufferedOutputStream bout = new BufferedOutputStream(fos)) {
        workbook.write(bout);
        bout.flush();
    }
    return new CustomerOrderReportResult(reportName);
}

From source file:co.com.runt.runistac.logica.ReporteLogica.java

public static byte[] generar(InputStream plantilla, Map<String, String> parametros, List<Object[]> datos)
        throws Exception {
    XSSFWorkbook wb = new XSSFWorkbook(plantilla);
    XSSFSheet mySheet = wb.getSheetAt(0);
    for (int i = 0; i < mySheet.getLastRowNum(); i++) {
        Row row = mySheet.getRow(i);//from w  w w.ja  va2 s  . c  o  m
        if (row != null && row.getCell(0) != null && row.getCell(0).getCellTypeEnum() == CellType.STRING) {
            for (String key : parametros.keySet()) {
                String valor = row.getCell(0).getStringCellValue();
                valor = valor.replaceAll("\\{" + key + "\\}", parametros.get(key));
                row.getCell(0).setCellValue(valor);
            }
        }
    }

    int rows = mySheet.getLastRowNum();
    int i = 0;
    Row base = mySheet.getRow(rows);
    CellStyle[] cs = null;
    if (!datos.isEmpty()) {
        int cant = datos.get(0).length;
        cs = new CellStyle[cant];
        for (int j = 0; j < cant; j++) {
            cs[j] = base.getCell(j).getCellStyle();
        }
    }
    for (Object[] o : datos) {
        Row row = mySheet.createRow(rows + i);
        for (int j = 0; j < o.length; j++) {
            Cell c = row.createCell(j);
            String value = "";
            if (o[j] != null) {
                if (o[j] instanceof String) {
                    value = (String) o[j];
                    c.setCellValue(value);
                } else if (o[j] instanceof Integer) {//integer
                    c.setCellValue((Integer) o[j]);
                } else if (o[j] instanceof Double) {
                    c.setCellValue((Double) o[j]);
                } else if (o[j] instanceof Float) {
                    c.setCellValue((Float) o[j]);
                } else if (o[j] instanceof BigDecimal) {
                    c.setCellValue(((BigDecimal) o[j]).doubleValue());
                } else if (o[j] instanceof Date) {
                    c.setCellValue(((Date) o[j]));
                } else if (o[j] instanceof BigInteger) {
                    c.setCellValue(((BigInteger) o[j]).intValue());
                } else {
                    c.setCellValue(o[j].toString());
                    System.out.println("No se encontro tipo: " + j + "-" + o[j].getClass());
                }
            }
            c.setCellStyle(cs[j]);
        }
        i++;
    }
    ByteArrayOutputStream baos = new ByteArrayOutputStream();
    wb.write(baos);

    return baos.toByteArray();
}