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:testpoi.FlatFilesInFolderToExcel.java

License:Open Source License

private static void writeToExcel(String contents, XSSFSheet sheet) {
    //Create a new row in sheet
    Row row = sheet.createRow(rowCount++);
    //Create a new cell in current row
    Cell cell = row.createCell(0);//from  w w  w  . ja v  a2s . c o  m
    //Set value to new value
    cell.setCellValue(contents);
}

From source file:testpoi.SplitOldDepartmentwise.java

License:Open Source License

private static void createDepartmentwiseSheets() {
    HashMap<String, Integer> depttMap = new HashMap<>();
    depttMap.put("Medicine", 1);
    depttMap.put("Surgery", 2);
    depttMap.put("Obs & Gynae", 3);
    depttMap.put("Paediatrics", 4);
    depttMap.put("Orthopaedics", 5);
    depttMap.put("Ophthalmology", 6);
    depttMap.put("ENT", 7);
    depttMap.put("Dental", 8);
    depttMap.put("Casualty", 9);

    int depttSheetCreateFlag = 0;

    Iterator<Row> rowIterator = sheetAllOld.rowIterator();
    //Store the first row to be printed as it is.
    ArrayList<String> heading = new ArrayList<>();
    Row row = rowIterator.next();/*from   ww  w . ja  v  a2s  .  c o m*/
    Iterator<Cell> cellIterator = row.cellIterator();
    while (cellIterator.hasNext())
        heading.add(cellIterator.next().getStringCellValue());

    int rowNums[] = { 1, 1, 1, 1, 1, 1, 1, 1, 1 };
    while (rowIterator.hasNext()) {
        row = rowIterator.next();
        XSSFSheet sheetToWrite = null;

        Cell cell = row.getCell(0);
        if ((depttSheetCreateFlag & 1 << (depttMap.get(cell.getStringCellValue()))) == 0) {
            //that means this deptt came in this sheet for the first time in this row.
            XSSFSheet sheet = sheets[depttMap.get(cell.getStringCellValue()) - 1] = workbookOld
                    .createSheet(cell.getStringCellValue());
            //create heading row in this sheet
            Row headingRow = sheet.createRow(0);
            for (int i = 0; i < heading.size(); i++) {
                String cellString = heading.get(i);
                Cell headingCell = headingRow.createCell(i);
                headingCell.setCellValue(cellString);//sets cell type to string too
            }
            //mark this deptt. as seen
            depttSheetCreateFlag |= (1 << (depttMap.get(cell.getStringCellValue())));
        }

        int sheetNum = depttMap.get(cell.getStringCellValue()) - 1;
        sheetToWrite = sheets[sheetNum];
        assert (sheetToWrite != null);

        //write row to sheetToWrite
        Row rowNew = sheetToWrite.createRow(rowNums[sheetNum]++);

        cellIterator = row.cellIterator();
        int cellNum = 0;
        while (cellIterator.hasNext()) {
            cell = cellIterator.next();

            //write cell
            Cell cellNew = rowNew.createCell(cellNum++);
            String cellValue;
            if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC)
                cellValue = (int) (cell.getNumericCellValue()) + "";
            else
                cellValue = cell.getStringCellValue();
            cellNew.setCellValue(cellValue);
        }
    }
}

From source file:tr.org.liderahenk.liderconsole.core.utils.SWTResourceManager.java

License:Open Source License

private static XSSFWorkbook createWorkbookFromTable(TableViewer tableViewer, String sheetName) {

    // Create workbook & sheet
    XSSFWorkbook wb = new XSSFWorkbook();
    XSSFSheet sheet = wb.createSheet(sheetName == null ? "Sheet1" : sheetName);

    // Shade the background of the header row
    XSSFCellStyle headerStyle = wb.createCellStyle();
    headerStyle.setFillForegroundColor(IndexedColors.LEMON_CHIFFON.getIndex());
    headerStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
    headerStyle.setBorderTop(CellStyle.BORDER_THIN);
    headerStyle.setBorderBottom(CellStyle.BORDER_THIN);
    headerStyle.setBorderLeft(CellStyle.BORDER_THIN);
    headerStyle.setBorderRight(CellStyle.BORDER_THIN);
    headerStyle.setAlignment(HorizontalAlignment.CENTER);

    // Add header row
    Table table = tableViewer.getTable();
    TableColumn[] columns = table.getColumns();
    int rowIndex = 0;
    int cellIndex = 0;
    XSSFRow header = sheet.createRow((short) rowIndex++);
    for (TableColumn column : columns) {
        XSSFCell cell = header.createCell(cellIndex++);
        cell.setCellValue(column.getText());
        cell.setCellStyle(headerStyle);/*from   w  w  w  .jav  a 2  s .c  o  m*/
    }

    // Add data rows
    TableItem[] items = tableViewer.getTable().getItems();
    for (TableItem item : items) {
        // create a new row
        XSSFRow row = sheet.createRow((short) rowIndex++);
        cellIndex = 0;

        for (int i = 0; i < columns.length; i++) {
            // Create a new cell
            XSSFCell cell = row.createCell(cellIndex++);
            String text = item.getText(i);

            // Set the horizontal alignment (default to RIGHT)
            XSSFCellStyle cellStyle = wb.createCellStyle();
            if (LiderCoreUtils.isInteger(text)) {
                cellStyle.setAlignment(HorizontalAlignment.RIGHT);
            } else if (LiderCoreUtils.isValidDate(text,
                    ConfigProvider.getInstance().get(LiderConstants.CONFIG.DATE_FORMAT))) {
                cellStyle.setAlignment(HorizontalAlignment.CENTER);
            } else {
                cellStyle.setAlignment(HorizontalAlignment.LEFT);
            }
            cell.setCellStyle(cellStyle);

            // Set the cell's value
            cell.setCellValue(text);
        }
    }

    // Auto-fit the columns
    for (int i = 0; i < columns.length; i++) {
        sheet.autoSizeColumn((short) i);
    }

    return wb;
}

From source file:trei_big.criar_planilha_excel.java

public criar_planilha_excel(String caminho_salvar, String nome_da_tabela) {

    //Blank workbook
    XSSFWorkbook workbook = new XSSFWorkbook();

    //Create a blank sheet
    XSSFSheet sheet = workbook.createSheet(nome_da_tabela);

    //This data needs to be written (Object[])
    Map<Integer, Object[]> data = new TreeMap<Integer, Object[]>();

    Vector<Vector<String>> dados = banco.obter_dados_da_tabela(nome_da_tabela);

    data.put(0, banco.nome_colunas_consulta.toArray(new Object[] {}));

    for (int i = 0; i < dados.size(); i++) {
        Vector<String> get = dados.get(i);
        data.put((i + 1), get.toArray(new Object[] {}));
    }/*from   ww w.  j  a v a2s.c o m*/

    //Iterate over data and write to sheet
    Set<Integer> keyset = data.keySet();
    int rownum = 0;
    for (Integer 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
        FileOutputStream out = new FileOutputStream(new File(caminho_salvar, nome_da_tabela + extensao));
        workbook.write(out);
        out.close();
        aviso.mensagem_sucesso("Planilha '" + nome_da_tabela + extensao + "' criada com sucesso!");
    } catch (Exception e) {
        e.printStackTrace();
    }

}

From source file:tubessc.Dataset.java

public void calculateFluctuation(String InputFile, String OutputFile)
        throws FileNotFoundException, IOException {
    FileInputStream file = new FileInputStream(new File(InputFile));
    XSSFWorkbook workbook = new XSSFWorkbook(file);
    XSSFSheet sheet = workbook.getSheetAt(0);
    XSSFWorkbook output = new XSSFWorkbook();
    XSSFSheet sheetOutput = output.createSheet("new sheet");
    FileOutputStream fileOut = new FileOutputStream(OutputFile);
    int rowStart = sheet.getFirstRowNum();
    int rowEnd = sheet.getLastRowNum();
    for (int i = rowStart; i <= rowEnd - 1; i++) {
        Row rowIn1 = sheet.getRow(i);/*from www. ja v a  2 s . c om*/
        Cell cellIn1 = rowIn1.getCell(0);
        Row rowIn2 = sheet.getRow(i + 1);
        Cell cellIn2 = rowIn2.getCell(0);
        double value1 = Double.parseDouble(String.valueOf(cellIn1.getNumericCellValue()));
        double value2 = Double.parseDouble(String.valueOf(cellIn2.getNumericCellValue()));
        Row rowOut = sheetOutput.createRow(i);
        Cell cellOut = rowOut.createCell(0);
        cellOut.setCellValue(value2 - value1);
    }
    output.write(fileOut);
    fileOut.close();
}

From source file:tubessc.Dataset.java

public void normalization(String InputFile, String outputFile, double minValue, double maxValue)
        throws FileNotFoundException, IOException {
    this.minValue = minValue;
    this.maxValue = maxValue;
    FileInputStream file = new FileInputStream(new File(InputFile));
    XSSFWorkbook workbook = new XSSFWorkbook(file);
    XSSFSheet sheet = workbook.getSheetAt(0);
    XSSFWorkbook output = new XSSFWorkbook();
    XSSFSheet sheetOutput = output.createSheet("new sheet");
    FileOutputStream fileOut = new FileOutputStream(outputFile);
    int rowStart = sheet.getFirstRowNum();
    int rowEnd = sheet.getLastRowNum();
    Row row = sheet.getRow(rowStart);//from  www . ja  v a  2 s . c o m
    Cell cell = row.getCell(0);
    max = Double.parseDouble(String.valueOf(cell.getNumericCellValue()));
    min = Double.parseDouble(String.valueOf(cell.getNumericCellValue()));
    for (int i = rowStart + 1; i <= rowEnd; i++) {
        row = sheet.getRow(i);
        cell = row.getCell(0);
        double value = Double.parseDouble(String.valueOf(cell.getNumericCellValue()));
        if (value > max) {
            max = value;
        }
        if (value < min) {
            min = value;
        }
    }
    for (int i = rowStart; i <= rowEnd; i++) {
        row = sheet.getRow(i);
        cell = row.getCell(0);
        double value = Double.parseDouble(String.valueOf(cell.getNumericCellValue()));
        double newValue = minValue + ((value - min) * (maxValue - minValue) / (max - min));
        Row rowOut = sheetOutput.createRow(i);
        Cell cellOut = rowOut.createCell(0);
        cellOut.setCellValue(newValue);
    }
    output.write(fileOut);
    fileOut.close();
}

From source file:tubessc.TubesSC.java

public static void main(String[] args) {
    // TODO code application logic here
    try {// w  w  w. j a  v a2s  . co  m
        int x = 0;
        XSSFWorkbook output = new XSSFWorkbook();
        XSSFSheet sheetOutput = output.createSheet("new sheet");
        FileOutputStream fileOut = new FileOutputStream("F:\\JST\\dataset\\goldPrice\\HasilFINAL.xlsx");
        ds = new Dataset();
        ds.calculateFluctuation("F:\\JST\\dataset\\goldPrice\\training.xlsx",
                "F:\\JST\\dataset\\goldPrice\\trainingFluctuation.xlsx");
        ds.normalization("F:\\JST\\dataset\\goldPrice\\trainingFluctuation.xlsx",
                "F:\\JST\\dataset\\goldPrice\\trainingNormalization.xlsx", 0.1, 0.9);
        ds.calculateFluctuation("F:\\JST\\dataset\\goldPrice\\testing.xlsx",
                "F:\\JST\\dataset\\goldPrice\\testingFluctuation.xlsx");
        ds.normalization("F:\\JST\\dataset\\goldPrice\\testingFluctuation.xlsx",
                "F:\\JST\\dataset\\goldPrice\\testingNormalization.xlsx", 0.1, 0.9);
        int totalCombination = varInput.length * varHidden.length * varPop.length * times;
        int progress = 0;
        for (int i = 0; i < varInput.length; i++) {
            input = varInput[i];
            ds.clearDatasetTraining();
            ds.clearDatasetTesting();
            ds.addDataSetTrainingExcel("F:\\JST\\dataset\\goldPrice\\trainingNormalization.xlsx",
                    TubesSC.input);
            ds.addDataSetTestingExcel("F:\\JST\\dataset\\goldPrice\\testingNormalization.xlsx", TubesSC.input);
            for (int j = 0; j < varHidden.length; j++) {
                neuron = varHidden[j];
                nhidden = ((input + bias) * neuron);
                noutput = (neuron + bias);
                total = nhidden + noutput;
                batas = total - noutput;
                for (int k = 0; k < varPop.length; k++) {
                    numOfGen = varGen[k];
                    numOfPop = varPop[k];
                    for (int l = 0; l < times; l++) {
                        Row r = sheetOutput.createRow(progress);
                        Kromosom fittest = null;
                        double fittestBefore = 0;
                        Populasi pop = new Populasi(numOfPop, true, ds);
                        for (int y = 0; y < numOfGen; y++) {
                            pop = EP.Evolution(pop);
                            fittest = pop.getFittest();
                            System.out.println(fittest.getFitness());
                        }
                        JST jst = new JST(fittest.getW1(), fittest.getW2(), fittest.getB1(), fittest.getB2(),
                                ds);
                        double MAPE = jst.getMape(true);
                        System.out.println(MAPE);
                        //System.out.println("final MAPE = " + MAPE);
                        for (int z = 0; z < 6; z++) {
                            Cell c = r.createCell(z);
                            switch (z) {
                            case 0:
                                c.setCellValue(input);
                                break;
                            case 1:
                                c.setCellValue(neuron);
                                break;
                            case 2:
                                c.setCellValue(numOfGen);
                                break;
                            case 3:
                                c.setCellValue(numOfPop);
                                break;
                            case 4:
                                c.setCellValue(fittest.getFitness());
                                break;
                            case 5:
                                c.setCellValue(MAPE);
                                break;

                            }
                        }
                        x++;
                        progress++;
                        System.out.println("completed: " + progress + "/" + totalCombination);
                    }
                }
            }
        }
        output.write(fileOut);
        fileOut.close();
    } catch (IOException ex) {
        Logger.getLogger(TubesSC.class.getName()).log(Level.SEVERE, null, ex);
    }

}

From source file:uk.ac.bbsrc.tgac.miso.core.util.FormUtils.java

License:Open Source License

public static void createPlateExportForm(File outpath, JSONArray jsonArray) throws Exception {
    InputStream in = null;/*from ww  w .j a va 2 s  .  c  o m*/
    in = FormUtils.class.getResourceAsStream("/forms/ods/plate_input.xlsx");
    if (in != null) {
        XSSFWorkbook oDoc = new XSSFWorkbook(in);

        XSSFSheet sheet = oDoc.getSheet("Input");
        FileOutputStream fileOut = new FileOutputStream(outpath);
        int i = 4;
        for (JSONObject jsonObject : (Iterable<JSONObject>) jsonArray) {
            String sampleinwell = jsonObject.getString("value");
            //"sampleid:wellid:samplealias:projectname"
            String sampleId = sampleinwell.split(":")[0];
            String wellId = sampleinwell.split(":")[1];
            String sampleAlias = sampleinwell.split(":")[2];
            String projectName = sampleinwell.split(":")[3];
            XSSFRow row = sheet.createRow(i);
            XSSFCell cellA = row.createCell(0);
            cellA.setCellValue(wellId);
            XSSFCell cellB = row.createCell(1);
            cellB.setCellValue(projectName);
            XSSFCell cellC = row.createCell(2);
            cellC.setCellValue(sampleAlias);
            i++;
        }
        oDoc.write(fileOut);
        fileOut.close();
    } else {
        throw new IOException("Could not read from resource.");
    }

}

From source file:uk.ac.bbsrc.tgac.miso.spring.util.FormUtils.java

License:Open Source License

public static void createSampleExportForm(File outpath, JSONArray jsonArray) throws Exception {
    InputStream in = null;/*  ww  w  .  j a v  a2  s. c o m*/
    in = FormUtils.class.getResourceAsStream("/forms/ods/export_samples.xlsx");
    if (in != null) {
        XSSFWorkbook oDoc = new XSSFWorkbook(in);

        XSSFSheet sheet = oDoc.getSheet("samples_export");
        FileOutputStream fileOut = new FileOutputStream(outpath);
        int i = 5;
        for (JSONObject jsonObject : (Iterable<JSONObject>) jsonArray) {
            if ("sampleinwell".equals(jsonObject.getString("name"))) {
                String sampleinwell = jsonObject.getString("value");
                // "sampleid:wellid:samplealias:projectname:projectalias:dnaOrRNA"
                String sampleId = sampleinwell.split(":")[0];
                String wellId = sampleinwell.split(":")[1];
                String sampleAlias = sampleinwell.split(":")[2];
                String projectName = sampleinwell.split(":")[3];
                String projectAlias = sampleinwell.split(":")[4];
                String dnaOrRNA = sampleinwell.split(":")[5];
                XSSFRow row = sheet.createRow(i);
                XSSFCell cellA = row.createCell(0);
                cellA.setCellValue(projectName);
                XSSFCell cellB = row.createCell(1);
                cellB.setCellValue(projectAlias);
                XSSFCell cellC = row.createCell(2);
                cellC.setCellValue(sampleId);
                XSSFCell cellD = row.createCell(3);
                cellD.setCellValue(sampleAlias);
                XSSFCell cellE = row.createCell(4);
                cellE.setCellValue(wellId);
                XSSFCell cellG = row.createCell(6);
                XSSFCell cellH = row.createCell(7);
                XSSFCell cellI = row.createCell(8);
                XSSFCell cellL = row.createCell(11);
                if ("R".equals(dnaOrRNA)) {
                    cellG.setCellValue("NA");
                    cellL.setCellFormula("1000/H" + (i + 1));
                } else if ("D".equals(dnaOrRNA)) {
                    cellH.setCellValue("NA");
                    cellI.setCellValue("NA");
                    cellL.setCellFormula("1000/G" + (i + 1));
                }
                XSSFCell cellM = row.createCell(12);
                cellM.setCellFormula("50-L" + (i + 1));
                i++;
            }
        }
        oDoc.write(fileOut);
        fileOut.close();
    } else {
        throw new IOException("Could not read from resource.");
    }

}

From source file:uk.ac.bbsrc.tgac.miso.spring.util.FormUtils.java

License:Open Source License

public static void createLibraryPoolExportFormFromWeb(File outpath, JSONArray jsonArray, String indexFamily)
        throws Exception {
    InputStream in = null;//from   ww w  .  j  av a2s .com
    in = FormUtils.class.getResourceAsStream("/forms/ods/export_libraries_pools.xlsx");
    if (in != null) {
        XSSFWorkbook oDoc = new XSSFWorkbook(in);

        XSSFSheet sheet = oDoc.getSheet("library_pool_export");
        FileOutputStream fileOut = new FileOutputStream(outpath);
        XSSFRow row2 = sheet.getRow(1);

        int i = 6;
        for (JSONObject jsonObject : (Iterable<JSONObject>) jsonArray) {
            if ("paired".equals(jsonObject.getString("name"))) {
                XSSFCell row2cellA = row2.createCell(0);
                row2cellA.setCellValue(jsonObject.getString("value"));
            } else if ("platform".equals(jsonObject.getString("name"))) {
                XSSFCell row2cellB = row2.createCell(1);
                row2cellB.setCellValue(jsonObject.getString("value"));
            } else if ("type".equals(jsonObject.getString("name"))) {
                XSSFCell row2cellC = row2.createCell(2);
                row2cellC.setCellValue(jsonObject.getString("value"));
            } else if ("selection".equals(jsonObject.getString("name"))) {
                XSSFCell row2cellD = row2.createCell(3);
                row2cellD.setCellValue(jsonObject.getString("value"));
            } else if ("strategy".equals(jsonObject.getString("name"))) {
                XSSFCell row2cellE = row2.createCell(4);
                row2cellE.setCellValue(jsonObject.getString("value"));
            }
            if ("sampleinwell".equals(jsonObject.getString("name"))) {
                String sampleinwell = jsonObject.getString("value");
                // "sampleid:wellid:samplealias:projectname:projectalias:dnaOrRNA"
                String sampleId = sampleinwell.split(":")[0];
                String wellId = sampleinwell.split(":")[1];
                String sampleAlias = sampleinwell.split(":")[2];
                String projectName = sampleinwell.split(":")[3];
                String projectAlias = sampleinwell.split(":")[4];
                XSSFRow row = sheet.createRow(i);
                XSSFCell cellA = row.createCell(0);
                cellA.setCellValue(projectName);
                XSSFCell cellB = row.createCell(1);
                cellB.setCellValue(projectAlias);
                XSSFCell cellC = row.createCell(2);
                cellC.setCellValue(sampleId);
                XSSFCell cellD = row.createCell(3);
                cellD.setCellValue(sampleAlias);
                XSSFCell cellE = row.createCell(4);
                cellE.setCellValue(wellId);
                if (indexFamily != null) {
                    XSSFCell cellJ = row.createCell(9);
                    cellJ.setCellValue(indexFamily);
                }
                i++;
            }
        }
        oDoc.write(fileOut);
        fileOut.close();
    } else {
        throw new IOException("Could not read from resource.");
    }

}