Example usage for org.apache.poi.ss.usermodel Workbook createSheet

List of usage examples for org.apache.poi.ss.usermodel Workbook createSheet

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.ss.usermodel.*
  5. Workbook
  6. createSheet

Introduction

In this page you can find the example usage for org.apache.poi.ss.usermodel Workbook createSheet.

Prototype

Sheet createSheet(String sheetname);

Source Link

Document

Create a new sheet for this Workbook and return the high level representation.

Usage

From source file:org.bbreak.excella.core.util.PoiUtil.java

License:Open Source License

/**
 * ?//from ww  w . j  av a  2 s. c  om
 * 
 * @param fromSheet 
 * @param rangeAddress 
 * @param toSheet 
 * @param toRowNum 
 * @param toColumnNum 
 * @param clearFromRange 
 */
public static void copyRange(Sheet fromSheet, CellRangeAddress rangeAddress, Sheet toSheet, int toRowNum,
        int toColumnNum, boolean clearFromRange) {

    if (fromSheet == null || rangeAddress == null || toSheet == null) {
        return;
    }

    int fromRowIndex = rangeAddress.getFirstRow();
    int fromColumnIndex = rangeAddress.getFirstColumn();

    int rowNumOffset = toRowNum - fromRowIndex;
    int columnNumOffset = toColumnNum - fromColumnIndex;

    // 
    CellRangeAddress toAddress = new CellRangeAddress(rangeAddress.getFirstRow() + rowNumOffset,
            rangeAddress.getLastRow() + rowNumOffset, rangeAddress.getFirstColumn() + columnNumOffset,
            rangeAddress.getLastColumn() + columnNumOffset);

    Workbook fromWorkbook = fromSheet.getWorkbook();
    Sheet baseSheet = fromSheet;

    Sheet tmpSheet = null;
    // ?????
    if (fromSheet.equals(toSheet) && crossRangeAddress(rangeAddress, toAddress)) {
        // ?
        tmpSheet = fromWorkbook.getSheet(TMP_SHEET_NAME);
        if (tmpSheet == null) {
            tmpSheet = fromWorkbook.createSheet(TMP_SHEET_NAME);
        }
        baseSheet = tmpSheet;

        int lastColNum = getLastColNum(fromSheet);
        for (int i = 0; i <= lastColNum; i++) {
            tmpSheet.setColumnWidth(i, fromSheet.getColumnWidth(i));
        }

        copyRange(fromSheet, rangeAddress, tmpSheet, rangeAddress.getFirstRow(), rangeAddress.getFirstColumn(),
                false);

        // ?
        if (clearFromRange) {
            clearRange(fromSheet, rangeAddress);
        }
    }

    // ????
    Set<CellRangeAddress> targetCellSet = getMergedAddress(baseSheet, rangeAddress);
    // ???
    clearRange(toSheet, toAddress);

    // ???
    for (CellRangeAddress mergeAddress : targetCellSet) {

        toSheet.addMergedRegion(new CellRangeAddress(mergeAddress.getFirstRow() + rowNumOffset,
                mergeAddress.getLastRow() + rowNumOffset, mergeAddress.getFirstColumn() + columnNumOffset,
                mergeAddress.getLastColumn() + columnNumOffset));

    }

    for (int i = rangeAddress.getFirstRow(); i <= rangeAddress.getLastRow(); i++) {
        // 
        Row fromRow = baseSheet.getRow(i);
        if (fromRow == null) {
            continue;
        }
        Row row = toSheet.getRow(i + rowNumOffset);
        if (row == null) {
            row = toSheet.createRow(i + rowNumOffset);
            row.setHeight((short) 0);
        }

        // ??????
        int fromRowHeight = fromRow.getHeight();
        int toRowHeight = row.getHeight();
        if (toRowHeight < fromRowHeight) {
            row.setHeight(fromRow.getHeight());
        }

        ColumnHelper columnHelper = null;
        if (toSheet instanceof XSSFSheet) {
            XSSFSheet xssfSheet = (XSSFSheet) toSheet.getWorkbook()
                    .getSheetAt(toSheet.getWorkbook().getSheetIndex(toSheet));
            CTWorksheet ctWorksheet = xssfSheet.getCTWorksheet();
            columnHelper = new ColumnHelper(ctWorksheet);
        }

        for (int j = rangeAddress.getFirstColumn(); j <= rangeAddress.getLastColumn(); j++) {
            Cell fromCell = fromRow.getCell(j);
            if (fromCell == null) {
                continue;
            }
            int maxColumn = SpreadsheetVersion.EXCEL97.getMaxColumns();
            if (toSheet instanceof XSSFSheet) {
                maxColumn = SpreadsheetVersion.EXCEL2007.getMaxColumns();
            }
            if (j + columnNumOffset >= maxColumn) {
                break;
            }
            Cell cell = row.getCell(j + columnNumOffset);
            if (cell == null) {
                cell = row.createCell(j + columnNumOffset);
                if (toSheet instanceof XSSFSheet) {
                    // XSSF??????????
                    CTCol col = columnHelper.getColumn(cell.getColumnIndex(), false);
                    if (col == null || !col.isSetWidth()) {
                        toSheet.setColumnWidth(cell.getColumnIndex(), baseSheet.getColumnWidth(j));
                    }
                }
            }

            // ?
            copyCell(fromCell, cell);

            // ??????
            int fromColumnWidth = baseSheet.getColumnWidth(j);
            int toColumnWidth = toSheet.getColumnWidth(j + columnNumOffset);

            if (toColumnWidth < fromColumnWidth) {
                toSheet.setColumnWidth(j + columnNumOffset, baseSheet.getColumnWidth(j));
            }
        }
    }

    if (tmpSheet != null) {
        // 
        fromWorkbook.removeSheetAt(fromWorkbook.getSheetIndex(tmpSheet));
    } else if (clearFromRange) {
        // ????
        clearRange(fromSheet, rangeAddress);
    }

}

From source file:org.bbreak.excella.reports.processor.ReportsParserInfoTest.java

License:Open Source License

/**
 * {@link org.bbreak.excella.reports.processor.ReportsParserInfo#getMatchTagParser(org.apache.poi.ss.usermodel.Sheet, org.apache.poi.ss.usermodel.Cell)} ????
 *///from ww w .  j  a  va 2 s .  co  m
@Test
public void testGetMatchTagParser() {

    ReportsParserInfo info = new ReportsParserInfo();
    ReportBook reportBook = new ReportBook("", "", new ConvertConfiguration[] {});
    info.setReportBook(reportBook);
    List<ReportsTagParser<?>> reportParsers = new ArrayList<ReportsTagParser<?>>(
            ReportCreateHelper.createDefaultParsers().values());
    info.setReportParsers(reportParsers);

    Workbook hssfWb = new HSSFWorkbook();
    Workbook xssfWb = new XSSFWorkbook();
    Sheet hssfSheet = hssfWb.createSheet("new sheet");
    Sheet xssfSheet = xssfWb.createSheet("new sheet");
    Cell hssfCell0 = hssfSheet.createRow(0).createCell(0);
    Cell hssfCell1 = hssfSheet.createRow(1).createCell(0);
    hssfCell0.setCellValue("${HSSF}");
    hssfCell1.setCellValue("$TEST{HSSF}");
    Cell xssfCell0 = xssfSheet.createRow(0).createCell(0);
    Cell xssfCell1 = xssfSheet.createRow(1).createCell(0);
    xssfCell0.setCellValue("${XSSF}");
    xssfCell1.setCellValue("$TEST{XSSF}");

    TagParser<?> parser = null;
    try {
        parser = info.getMatchTagParser(hssfSheet, hssfCell0);
    } catch (ParseException e) {
        e.printStackTrace();
        fail(e.toString());
    }
    assertEquals(SingleParamParser.class, parser.getClass());

    try {
        parser = info.getMatchTagParser(hssfSheet, hssfCell1);
    } catch (ParseException e) {
        e.printStackTrace();
        fail(e.toString());
    }
    assertNull(parser);

    try {
        parser = info.getMatchTagParser(xssfSheet, xssfCell0);
    } catch (ParseException e) {
        e.printStackTrace();
        fail(e.toString());
    }
    assertEquals(SingleParamParser.class, parser.getClass());

    try {
        parser = info.getMatchTagParser(xssfSheet, xssfCell1);
    } catch (ParseException e) {
        e.printStackTrace();
        fail(e.toString());
    }
    assertNull(parser);

    try {
        hssfWb.close();
    } catch (IOException e) {
    }
    try {
        xssfWb.close();
    } catch (IOException e) {
    }
}

From source file:org.bbreak.excella.reports.util.ReportsUtilTest.java

License:Open Source License

/**
 * {@link org.bbreak.excella.reports.util.ReportsUtil#getMergedAddress(Sheet, int, int)} ????
 *//*  w ww .  ja  va  2  s.  c om*/
@Test
public void testgetMergedAddress() {

    // ?
    Workbook hssfWb = new HSSFWorkbook();

    // ?
    Sheet hssfSheet = hssfWb.createSheet("testsheet");

    // ?
    Row hssfRow = hssfSheet.createRow(0);

    // ?
    hssfRow.createCell(0);
    hssfRow.createCell(1);
    hssfRow.createCell(2);

    // ??
    CellRangeAddress address1 = new CellRangeAddress(0, 1, 1, 1);
    hssfSheet.addMergedRegion(address1);
    CellRangeAddress address2 = new CellRangeAddress(0, 0, 2, 3);
    hssfSheet.addMergedRegion(address2);

    // 
    assertNull(ReportsUtil.getMergedAddress(hssfSheet, 0, 0));
    assertEquals(address1.toString(), ReportsUtil.getMergedAddress(hssfSheet, 0, 1).toString());
    assertEquals(address2.toString(), ReportsUtil.getMergedAddress(hssfSheet, 0, 2).toString());

    try {
        hssfWb.close();
    } catch (IOException e) {
    }

    // ?
    Workbook xssfWb = new XSSFWorkbook();

    // ?
    Sheet xssfSheet = xssfWb.createSheet("testsheet");

    // ?
    Row xssfRow = xssfSheet.createRow(0);

    // ?
    xssfRow.createCell(0);
    xssfRow.createCell(1);
    xssfRow.createCell(2);

    // ??
    address1 = new CellRangeAddress(0, 1, 1, 1);
    xssfSheet.addMergedRegion(address1);
    address2 = new CellRangeAddress(0, 0, 2, 3);
    xssfSheet.addMergedRegion(address2);

    // 
    assertNull(ReportsUtil.getMergedAddress(xssfSheet, 0, 0));
    assertEquals(address1.toString(), ReportsUtil.getMergedAddress(xssfSheet, 0, 1).toString());
    assertEquals(address2.toString(), ReportsUtil.getMergedAddress(xssfSheet, 0, 2).toString());

    try {
        xssfWb.close();
    } catch (IOException e) {
    }
}

From source file:org.bonitasoft.connectors.excel.AddDimensionedImage.java

License:Apache License

/**
 * The main entry point to the program. It contains code that demonstrates
 * one way to use the program.//from   ww  w  . ja v  a 2  s  .c om
 *
 * Note, the code is not restricted to use on new workbooks only. If an
 * image is to be inserted into an existing workbook. just open that
 * workbook, gat a reference to a sheet and pass that;
 *
 *      AddDimensionedImage addImage = new AddDimensionedImage();
 *
 *      File file = new File("....... Existing Workbook .......");
 *      FileInputStream fis = new FileInputStream(file);
 *      Workbook workbook = new HSSFWorkbook(fis);
 *      HSSFSheet sheet = workbook.getSheetAt(0);
 *      addImage.addImageToSheet("C3", sheet, "image.jpg", 30, 20,
 *          AddDimensionedImage.EXPAND.ROW);
 *
 * @param args the command line arguments
 */
public static void main(String[] args) {
    String imageFile = null;
    String outputFile = null;
    FileOutputStream fos = null;
    Workbook workbook = null;
    Sheet sheet = null;
    try {
        if (args.length < 2) {
            System.err.println("Usage: AddDimensionedImage imageFile outputFile");
            return;
        }
        workbook = new HSSFWorkbook();
        sheet = workbook.createSheet("Picture Test");
        // Note that as the code has been ported to the SS model, the following
        // would be equally as valid - workbook = new XSSFWorkbook();
        imageFile = args[0];
        outputFile = args[1];
        new AddDimensionedImage().addImageToSheet("B5", sheet, sheet.createDrawingPatriarch(),
                new File(imageFile).toURI().toURL(), 100, 40, AddDimensionedImage.EXPAND_ROW_AND_COLUMN);
        fos = new FileOutputStream(outputFile);
        workbook.write(fos);
    } catch (FileNotFoundException fnfEx) {
        System.out.println("Caught an: " + fnfEx.getClass().getName());
        System.out.println("Message: " + fnfEx.getMessage());
        System.out.println("Stacktrace follows...........");
        fnfEx.printStackTrace(System.out);
    } catch (IOException ioEx) {
        System.out.println("Caught an: " + ioEx.getClass().getName());
        System.out.println("Message: " + ioEx.getMessage());
        System.out.println("Stacktrace follows...........");
        ioEx.printStackTrace(System.out);
    } finally {
        if (fos != null) {
            try {
                fos.close();
                fos = null;
            } catch (IOException ioEx) {
                // I G N O R E
            }
        }
    }
}

From source file:org.cerberus.service.export.ExportServiceFactory.java

License:Open Source License

private void createReportByTagExport(Workbook workbook) {
    //handles the export of the execution by tag data
    HashMap<String, SummaryStatisticsDTO> summaryMap = new HashMap<String, SummaryStatisticsDTO>();

    HashMap<String, HashMap<String, List<TestCaseExecution>>> mapList = new HashMap<String, HashMap<String, List<TestCaseExecution>>>();
    List<String> mapCountries = new ArrayList<String>();
    List<CellStyle> stylesList = new LinkedList<CellStyle>();

    if (exportOptions.contains("chart") || exportOptions.contains("list")) {
        //then we need to create the default colors for each cell
        HSSFWorkbook hwb = new HSSFWorkbook();
        HSSFPalette palette = hwb.getCustomPalette();

        CellStyle okStyle = workbook.createCellStyle();

        // get the color which most closely matches the color you want to use
        // code to get the style for the cell goes here
        okStyle.setFillForegroundColor(palette.findSimilarColor(92, 184, 0).getIndex());
        okStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
        //okStyle.setFont();

        stylesList.add(okStyle);/*w ww.  ja  v a  2 s  . co  m*/

    }
    for (TestCaseExecution execution : (List<TestCaseExecution>) list) {
        //check if the country and application shows

        if (exportOptions.contains("chart") || exportOptions.contains("summary")) {
            String keySummaryTable = execution.getApplication() + " " + execution.getCountry() + " "
                    + execution.getEnvironment();
            SummaryStatisticsDTO stats;

            String status = execution.getControlStatus();

            if (summaryMap.containsKey(keySummaryTable)) {
                stats = summaryMap.get(keySummaryTable);
            } else {
                stats = new SummaryStatisticsDTO();
                stats.setApplication(execution.getApplication());
                stats.setCountry(execution.getCountry());
                stats.setEnvironment(execution.getEnvironment());
            }
            stats.updateStatisticByStatus(status);
            summaryMap.put(keySummaryTable, stats); //updates the map
        }
        if (exportOptions.contains("list")) {
            if (exportOptions.contains("filter")) {
                //filter active
            } else {
                //all data is saved

            }
            HashMap<String, List<TestCaseExecution>> listExecution;
            List<TestCaseExecution> testCaseList;
            String testKey = execution.getTest();
            String testCaseKey = execution.getTestCase();

            if (mapList.containsKey(testKey)) {
                listExecution = mapList.get(testKey);
            } else {
                listExecution = new HashMap<String, List<TestCaseExecution>>();
            }
            if (listExecution.containsKey(testCaseKey)) {
                testCaseList = listExecution.get(testCaseKey);
            } else {
                testCaseList = new ArrayList<TestCaseExecution>();
            }
            testCaseList.add(execution);
            listExecution.put(testCaseKey, testCaseList);
            mapList.put(testKey, listExecution);

            if (mapCountries.indexOf(execution.getCountry()) == -1) {
                mapCountries.add(execution.getCountry());
            }

        }

    }
    int rowCount = -1;

    //Create a blank sheet
    Sheet sheet = workbook.createSheet("Report by Tag");
    sheet.getPrintSetup().setLandscape(true);

    PrintSetup ps = sheet.getPrintSetup();

    sheet.setAutobreaks(true);

    //ps.setFitHeight((short) 1);
    ps.setFitWidth((short) 1);
    sheet.setFitToPage(true);
    sheet.setColumnWidth(0, 9000);

    if (exportOptions.contains("chart")) {
        SummaryStatisticsDTO sumsTotal = calculateTotalValues(summaryMap);

        Row row = sheet.createRow(++rowCount);
        row.createCell(0).setCellValue("Report By Status");

        row = sheet.createRow(++rowCount);
        row.createCell(0).setCellValue("Status");
        row.createCell(1).setCellValue("Total");
        row.createCell(2).setCellValue("Percentage");

        row = sheet.createRow(++rowCount);
        CellStyle okStyle = stylesList.get(0);
        Cell cellOk = row.createCell(0);
        cellOk.setCellValue("OK");
        cellOk.setCellStyle(okStyle);

        row.createCell(1).setCellValue(sumsTotal.getOk());
        row.createCell(2).setCellValue(sumsTotal.getPercOk());

        row = sheet.createRow(++rowCount);
        row.createCell(0).setCellValue("KO");
        row.createCell(1).setCellValue(sumsTotal.getKo());
        row.createCell(2).setCellValue(sumsTotal.getPercKo());

        row = sheet.createRow(++rowCount);
        row.createCell(0).setCellValue("FA");
        row.createCell(1).setCellValue(sumsTotal.getFa());
        row.createCell(2).setCellValue(sumsTotal.getPercFa());

        row = sheet.createRow(++rowCount);
        row.createCell(0).setCellValue("NA");
        row.createCell(1).setCellValue(sumsTotal.getNa());
        row.createCell(2).setCellValue(sumsTotal.getPercNa());

        row = sheet.createRow(++rowCount);
        row.createCell(0).setCellValue("NE");
        row.createCell(1).setCellValue(sumsTotal.getNe());
        row.createCell(2).setCellValue(sumsTotal.getPercNe());

        row = sheet.createRow(++rowCount);
        row.createCell(0).setCellValue("PE");
        row.createCell(1).setCellValue(sumsTotal.getPe());
        row.createCell(2).setCellValue(sumsTotal.getPercPe());

        row = sheet.createRow(++rowCount);
        row.createCell(0).setCellValue("CA");
        row.createCell(1).setCellValue(sumsTotal.getCa());
        row.createCell(2).setCellValue(sumsTotal.getPercCa());

        row = sheet.createRow(++rowCount);
        row.createCell(0).setCellValue("Total");
        row.createCell(1).setCellValue(sumsTotal.getTotal());

        sheet.createRow(++rowCount).createCell(0).setCellValue("");
        sheet.createRow(++rowCount).createCell(0).setCellValue("");
        sheet.createRow(++rowCount).createCell(0).setCellValue("");
        sheet.createRow(++rowCount).createCell(0).setCellValue("");

    }
    if (exportOptions.contains("summary")) {
        //draw the table with data

        Row row = sheet.createRow(++rowCount);
        row.createCell(0).setCellValue("Summary Table");

        //start creating data
        row = sheet.createRow(++rowCount);

        row.createCell(0).setCellValue("Application");
        row.createCell(1).setCellValue("Country");
        row.createCell(2).setCellValue("Environment");
        row.createCell(3).setCellValue("OK");
        row.createCell(4).setCellValue("KO");
        row.createCell(5).setCellValue("FA");
        row.createCell(6).setCellValue("NA");
        row.createCell(7).setCellValue("NE");
        row.createCell(8).setCellValue("PE");
        row.createCell(9).setCellValue("CA");
        row.createCell(10).setCellValue("NOT OK");
        row.createCell(11).setCellValue("Total");

        /*temporary styles*/
        CellStyle styleBlue = workbook.createCellStyle();
        CellStyle styleGreen = workbook.createCellStyle();
        HSSFWorkbook hwb = new HSSFWorkbook();
        HSSFPalette palette = hwb.getCustomPalette();
        // get the color which most closely matches the color you want to use
        HSSFColor myColor = palette.findSimilarColor(66, 139, 202);

        // get the palette index of that color 
        short palIndex = myColor.getIndex();
        // code to get the style for the cell goes here
        styleBlue.setFillForegroundColor(palIndex);
        styleBlue.setFillPattern(CellStyle.SPARSE_DOTS);

        HSSFColor myColorGreen = palette.findSimilarColor(92, 184, 0);
        styleGreen.setFillForegroundColor(myColorGreen.getIndex());
        styleGreen.setFillPattern(CellStyle.SPARSE_DOTS);

        int startRow = (rowCount + 2);
        TreeMap<String, SummaryStatisticsDTO> sortedSummaryMap = new TreeMap<String, SummaryStatisticsDTO>(
                summaryMap);
        for (String key : sortedSummaryMap.keySet()) {
            row = sheet.createRow(++rowCount);
            SummaryStatisticsDTO sumStats = summaryMap.get(key);
            //application
            row.createCell(0).setCellValue((String) sumStats.getApplication());
            //country
            row.createCell(1).setCellValue((String) sumStats.getCountry());
            //environment
            row.createCell(2).setCellValue((String) sumStats.getEnvironment());

            //OK
            row.createCell(3).setCellValue(sumStats.getOk());
            //KO
            row.createCell(4).setCellValue(sumStats.getKo());
            //FA
            row.createCell(5).setCellValue(sumStats.getFa());
            //NA
            row.createCell(6).setCellValue(sumStats.getNa());
            //NE
            row.createCell(7).setCellValue(sumStats.getNe());
            //PE
            row.createCell(8).setCellValue(sumStats.getPe());
            //CA
            row.createCell(9).setCellValue(sumStats.getCa());
            int rowNumber = row.getRowNum() + 1;
            //NOT OK
            //row.createCell(11).setCellValue(sumStats.getNotOkTotal());
            row.createCell(10).setCellFormula("SUM(E" + rowNumber + ":J" + rowNumber + ")");
            //Total
            row.createCell(11).setCellFormula("SUM(D" + rowNumber + ",K" + rowNumber + ")");
            //row.createCell(12).setCellValue(sumStats.getTotal());

            if (sumStats.getOk() == sumStats.getTotal()) {
                for (int i = 0; i < 12; i++) {
                    row.getCell(i).setCellStyle(styleGreen);
                }
            }
        }
        //TODO:FN percentages missing
        //Total row
        row = sheet.createRow(++rowCount);

        row.createCell(0).setCellValue("Total");
        row.createCell(1).setCellValue("");
        row.createCell(2).setCellValue("");
        //OK
        row.createCell(3).setCellFormula("SUM(D" + startRow + ":D" + rowCount + ")");
        //KO
        row.createCell(4).setCellFormula("SUM(E" + startRow + ":E" + rowCount + ")");
        //FA
        row.createCell(5).setCellFormula("SUM(F" + startRow + ":F" + rowCount + ")");
        //NA
        row.createCell(6).setCellFormula("SUM(G" + startRow + ":G" + rowCount + ")");
        //NE
        row.createCell(7).setCellFormula("SUM(H" + startRow + ":H" + rowCount + ")");
        //PE
        row.createCell(8).setCellFormula("SUM(I" + startRow + ":I" + rowCount + ")");
        //CA
        row.createCell(9).setCellFormula("SUM(J" + startRow + ":J" + rowCount + ")");

        int rowNumberTotal = row.getRowNum() + 1;
        //NOT OK
        row.createCell(10).setCellFormula("SUM(E" + rowNumberTotal + ":J" + rowNumberTotal + ")");
        //Total
        row.createCell(11).setCellFormula("SUM(D" + rowNumberTotal + ",K" + rowNumberTotal + ")");
        for (int i = 0; i < 12; i++) {
            row.getCell(i).setCellStyle(styleBlue);
        }

        //add some empty rows
        sheet.createRow(++rowCount).createCell(0).setCellValue("");
        sheet.createRow(++rowCount).createCell(0).setCellValue("");
        sheet.createRow(++rowCount).createCell(0).setCellValue("");
        sheet.createRow(++rowCount).createCell(0).setCellValue("");

    }

    if (exportOptions.contains("list")) {
        //exports the data from test cases' executions
        Row r = sheet.createRow(++rowCount);
        r.createCell(0).setCellValue("Test");
        r.createCell(1).setCellValue("Test Case");
        r.createCell(2).setCellValue("Description");
        r.createCell(3).setCellValue("Application");
        r.createCell(4).setCellValue("Environment");
        r.createCell(5).setCellValue("Browser");
        //creates the country list

        Collections.sort(mapCountries);//sorts the list of countries
        int startIndexForCountries = 6;
        for (String country : mapCountries) {
            r.createCell(startIndexForCountries).setCellValue(country);
            startIndexForCountries++;
        }

        TreeMap<String, HashMap<String, List<TestCaseExecution>>> sortedKeys = new TreeMap<String, HashMap<String, List<TestCaseExecution>>>(
                mapList);
        rowCount++;
        for (String keyMapList : sortedKeys.keySet()) {
            rowCount = createRow(keyMapList, mapList.get(keyMapList), sheet, rowCount, mapCountries);
        }
    }
}

From source file:org.citydb.plugins.spreadsheet_gen.controller.SpreadsheetExporter.java

License:Open Source License

public void convertToXSLX(String csvPath, String path, String filename) throws Exception {
    // convert csv to excel
    Workbook workbook = new XSSFWorkbook();
    Sheet sheet = workbook.createSheet("Countries");
    CsvReader reader = null;/*from  www  .j ava2  s.  c o  m*/

    int rowIndex = 0;

    String xlsxFullpath = path + File.separator + filename + ".xlsx";

    reader = new CsvReader(csvPath, SeparatorPhrase.getInstance().getIntoCloudDefaultSeperator().charAt(0),
            Charset.forName("UTF-8"));

    // avoid error message of CsvReader in case of column lengths greater than 100,000 characters
    reader.setSafetySwitch(false);

    reader.readRecord();
    String[] spshColumnNames = reader.getValues();
    Row row = sheet.createRow(rowIndex);
    for (int i = 0; i < spshColumnNames.length; i++) {
        Cell cell = row.createCell(i);
        cell.setCellValue(spshColumnNames[i]);
    }
    rowIndex++;
    Map<String, String> templateMap = Translator.getInstance().getTemplateHashmap();

    try {
        while (reader.readRecord()) {
            row = sheet.createRow(rowIndex);
            String[] valueArray = reader.getValues();
            if (valueArray != null && valueArray.length > 0) {
                for (int i = 0; i < valueArray.length; i++) {
                    if (valueArray[i] != null && String.valueOf(valueArray[i].trim()).length() > 0) {
                        String dbTableColumn = templateMap.get(spshColumnNames[i]);
                        Cell cell = row.createCell(i);
                        int dataType = Util._3DCITYDB_TABLES_AND_COLUMNS.get(dbTableColumn);
                        if (dataType == Util.NUMBER_COLUMN_VALUE) {
                            try {
                                cell.setCellValue(Double.valueOf(valueArray[i]));
                            } catch (NumberFormatException nfe) {
                                cell.setCellValue(String.valueOf(valueArray[i]));
                            }
                        } else {
                            cell.setCellValue(String.valueOf(valueArray[i]));
                        }
                    }
                }
                rowIndex++;
            }
        }
    } catch (IOException e1) {
        e1.printStackTrace();
    }
    reader.close();

    // lets write the excel data to file now
    FileOutputStream fos = null;
    try {
        fos = new FileOutputStream(new File(xlsxFullpath));
        workbook.write(fos);
        fos.close();
    } catch (IOException ioe) {
        logController.error(ioe.getMessage());
        shouldRun = false;
    }

}

From source file:org.controldepersonal.excel.ConexionExcel.java

public void exportaExcel() {
    try {//  ww w.  ja  v  a  2s.com
        File archivoXLSX = new File(rutaArchivo);
        if (archivoXLSX.exists())
            archivoXLSX.delete();
        archivoXLSX.createNewFile();
        Workbook libro = new HSSFWorkbook();
        FileOutputStream archivo = new FileOutputStream(archivoXLSX);
        Sheet hoja = libro.createSheet("Exportacin de SGCP");
        /*for(int f=0;f<10;f++){
        Row fila = hoja.createRow(f);
        for(int c=0;c<5;c++){
        Cell celda = fila.createCell(c);
        }
        }*/
        //Escribir en celdas
        /*
        celda.setCellValue("Encabezado #"+c);
        */
        for (int i = 0; i < 1; i++) {
            Row fila = hoja.createRow(i);
            for (int c = 0; c < 2; c++) {
                Cell celda = fila.createCell(c);
                if (i == 0 && c == 0) {
                    celda.setCellValue("Valor 1");
                }
            }
        }
        libro.write(archivo);
        archivo.close();
        Desktop.getDesktop().open(archivoXLSX);
    } catch (IOException ex) {
        Logger.getLogger(ConexionExcel.class.getName()).log(Level.SEVERE, null, ex);
    }
}

From source file:org.datanucleus.store.excel.ExcelPersistenceHandler.java

License:Open Source License

/**
 * Method to insert the object into the datastore.
 * @param op ObjectProvider of the object
 *///from www  .  j  a va2  s . co m
public void insertObject(final ObjectProvider op) {
    // Check if read-only so update not permitted
    assertReadOnlyForUpdateOfObject(op);

    AbstractClassMetaData cmd = op.getClassMetaData();
    ExecutionContext ec = op.getExecutionContext();
    ManagedConnection mconn = storeMgr.getConnection(ec);
    try {
        long startTime = System.currentTimeMillis();
        if (NucleusLogger.DATASTORE_PERSIST.isDebugEnabled()) {
            NucleusLogger.DATASTORE_PERSIST.debug(
                    Localiser.msg("Excel.Insert.Start", op.getObjectAsPrintable(), op.getInternalObjectId()));
        }

        Workbook wb = (Workbook) mconn.getConnection();
        if (!storeMgr.managesClass(cmd.getFullClassName())) {
            // Make sure schema exists, using this connection
            ((ExcelStoreManager) storeMgr).manageClasses(new String[] { cmd.getFullClassName() },
                    ec.getClassLoaderResolver(), wb);
        }
        Table table = ec.getStoreManager().getStoreDataForClass(cmd.getFullClassName()).getTable();

        if (cmd.getIdentityType() == IdentityType.APPLICATION
                || cmd.getIdentityType() == IdentityType.DATASTORE) {
            // Enforce uniqueness of datastore rows
            try {
                locateObject(op);
                throw new NucleusUserException(Localiser.msg("Excel.Insert.ObjectWithIdAlreadyExists",
                        op.getObjectAsPrintable(), op.getInternalObjectId()));
            } catch (NucleusObjectNotFoundException onfe) {
                // Do nothing since object with this id doesn't exist
            }
        }

        int[] fieldNumbers = cmd.getAllMemberPositions();
        String sheetName = table.getName();
        Sheet sheet = wb.getSheet(sheetName);
        int rowNum = 0;
        if (sheet == null) {
            // Sheet doesn't exist so create it
            sheet = wb.createSheet(sheetName);
            if (NucleusLogger.DATASTORE_PERSIST.isDebugEnabled()) {
                NucleusLogger.DATASTORE_PERSIST.debug(
                        Localiser.msg("Excel.Insert.SheetCreated", op.getObjectAsPrintable(), sheetName));
            }
        } else {
            // Find number of active rows in this sheet
            rowNum += ExcelUtils.getNumberOfRowsInSheetOfWorkbook(op, wb);
        }

        // Create the object in the datastore
        Row row = sheet.getRow(rowNum);
        if (row == null) {
            // No row present so create holder for the cells
            row = sheet.createRow(rowNum);
        }

        op.provideFields(fieldNumbers, new StoreFieldManager(op, row, true, table));

        if (NucleusLogger.DATASTORE_PERSIST.isDebugEnabled()) {
            NucleusLogger.DATASTORE_PERSIST
                    .debug(Localiser.msg("Excel.ExecutionTime", (System.currentTimeMillis() - startTime)));
        }
        if (ec.getStatistics() != null) {
            ec.getStatistics().incrementNumWrites();
            ec.getStatistics().incrementInsertCount();
        }

        if (cmd.getIdentityType() == IdentityType.DATASTORE) {
            // Set the datastore identity column value
            int idCellNum = table.getDatastoreIdColumn().getPosition();
            Object key = IdentityUtils.getTargetKeyForDatastoreIdentity(op.getInternalObjectId());
            Cell idCell = row.getCell(idCellNum);
            if (idCell == null) {
                idCell = row.createCell(idCellNum);
            }
            if (key instanceof String) {
                idCell.setCellValue(wb.getCreationHelper().createRichTextString((String) key));
            } else {
                idCell.setCellValue(((Long) key).longValue());
            }
        }

        VersionMetaData vermd = cmd.getVersionMetaDataForClass();
        if (vermd != null) {
            // versioned object so set its version
            int verCellNum = table.getVersionColumn().getPosition();
            Cell verCell = row.getCell(verCellNum);
            if (verCell == null) {
                verCell = row.createCell(verCellNum);
            }

            Object nextVersion = VersionHelper.getNextVersion(vermd.getVersionStrategy(), null);
            op.setTransactionalVersion(nextVersion);
            if (nextVersion instanceof Long) {
                if (NucleusLogger.DATASTORE.isDebugEnabled()) {
                    NucleusLogger.DATASTORE.debug(Localiser.msg("Excel.Insert.ObjectPersistedWithVersion",
                            op.getObjectAsPrintable(), op.getInternalObjectId(), "" + nextVersion));
                }
                verCell.setCellValue((Long) nextVersion);
            } else if (nextVersion instanceof Timestamp) {
                if (NucleusLogger.DATASTORE.isDebugEnabled()) {
                    NucleusLogger.DATASTORE.debug(Localiser.msg("Excel.Insert.ObjectPersistedWithVersion",
                            op.getObjectAsPrintable(), op.getInternalObjectId(), "" + nextVersion));
                }
                Date date = new Date();
                date.setTime(((Timestamp) nextVersion).getTime());
                verCell.setCellValue(date);
            }
        } else {
            if (NucleusLogger.DATASTORE.isDebugEnabled()) {
                NucleusLogger.DATASTORE.debug(Localiser.msg("Excel.Insert.ObjectPersisted",
                        op.getObjectAsPrintable(), op.getInternalObjectId()));
            }
        }
    } finally {
        mconn.release();
    }
}

From source file:org.datanucleus.store.excel.ExcelSchemaHandler.java

License:Open Source License

@Override
public void createSchemaForClasses(Set<String> classNames, Properties props, Object connection) {
    Workbook wb = (Workbook) connection;
    ManagedConnection mconn = null;//from ww w .j a v a  2  s . c  o m
    try {
        if (wb == null) {
            mconn = storeMgr.getConnection(-1);
            wb = (Workbook) mconn.getConnection();
        }

        Iterator<String> classIter = classNames.iterator();
        ClassLoaderResolver clr = storeMgr.getNucleusContext().getClassLoaderResolver(null);
        while (classIter.hasNext()) {
            String className = classIter.next();
            AbstractClassMetaData cmd = storeMgr.getMetaDataManager().getMetaDataForClass(className, clr);
            if (cmd != null) {
                StoreData storeData = storeMgr.getStoreDataForClass(cmd.getFullClassName());
                Table table = null;
                if (storeData != null) {
                    table = storeData.getTable();
                } else {
                    table = new CompleteClassTable(storeMgr, cmd, null);
                }

                String sheetName = table.getName();
                Sheet sheet = wb.getSheet(sheetName);
                if (sheet == null) {
                    // Sheet doesn't exist so create it
                    sheet = wb.createSheet(sheetName);
                    if (NucleusLogger.DATASTORE_PERSIST.isDebugEnabled()) {
                        NucleusLogger.DATASTORE_PERSIST.debug(
                                Localiser.msg("Excel.SchemaCreate.Class", cmd.getFullClassName(), sheetName));
                    }

                    // Create columns of sheet
                    for (int i = 0; i < table.getNumberOfColumns(); i++) {
                        // TODO Create header row
                    }
                }
            }
        }
    } finally {
        if (mconn != null) {
            mconn.release();
        }
    }
}

From source file:org.datanucleus.store.excel.valuegenerator.IncrementGenerator.java

License:Open Source License

protected ValueGenerationBlock<Long> reserveBlock(long size) {
    if (size < 1) {
        return null;
    }/* w w w.j a  v a 2s.c  o  m*/

    // Allocate value(s)
    ManagedConnection mconn = connectionProvider.retrieveConnection();
    List<Long> oids = new ArrayList<Long>();
    try {
        // Create the worksheet if not existing
        Workbook spreadsheetDoc = (Workbook) mconn.getConnection();
        Sheet sheet = spreadsheetDoc.getSheet(worksheetName);
        Row row = null;
        Cell valueCell = null;
        if (sheet == null) {
            if (!storeMgr.getSchemaHandler().isAutoCreateTables()) {
                throw new NucleusUserException(Localiser.msg("040011", worksheetName));
            }

            sheet = spreadsheetDoc.createSheet(worksheetName);
            row = sheet.createRow(0);
            Cell cell = row.createCell(0);
            cell.setCellValue(key);
            valueCell = row.createCell(1);
            valueCell.setCellValue(Double.valueOf(0));
        } else {
            for (int i = sheet.getFirstRowNum(); i < sheet.getLastRowNum() + 1; i++) {
                Row tblRow = sheet.getRow(i);
                if (tblRow != null) {
                    Cell tblCell = tblRow.getCell(0);
                    if (tblCell.getStringCellValue().equals(key)) {
                        row = tblRow;
                        valueCell = row.getCell(1);
                        break;
                    }
                }
            }
            if (row == null) {
                row = sheet.createRow(sheet.getLastRowNum() + 1);

                Cell cell1 = row.createCell(0);
                cell1.setCellValue(key);
                valueCell = row.createCell(1);
                valueCell.setCellValue(Double.valueOf(0));
            }
        }

        // Update the row
        if (valueCell != null) {
            NucleusLogger.VALUEGENERATION
                    .debug("Allowing " + size + " values for increment generator for " + key);
            long currentVal = (long) valueCell.getNumericCellValue();
            valueCell.setCellValue(Double.valueOf(currentVal + size));
            for (int i = 0; i < size; i++) {
                oids.add(currentVal + 1);
                currentVal++;
            }
        }
    } finally {
        connectionProvider.releaseConnection();
    }
    return new ValueGenerationBlock<Long>(oids);
}