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

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

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

Introduction

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

Prototype

void write(OutputStream stream) throws IOException;

Source Link

Document

Write out this workbook to an Outputstream.

Usage

From source file:gov.opm.scrd.services.impl.DeductionCalculationRuleServiceImpl.java

License:Apache License

/**
 * Generate deduction_table.xls based on deduction rates queried from database.
 *///from  w  w w  .ja v a  2  s. c om
private void generateDeductionTable() throws OPMConfigurationException {
    OutputStream templateOutput = null;
    try {
        int currentRow = this.deductionTableTemplate.getStartCellRow();
        int startColumn = this.deductionTableTemplate.getStartCellColumn();
        DateFormat df = new SimpleDateFormat("dd-MMM-yyyy", Locale.US);

        Workbook workbook = WorkbookFactory
                .create(new FileInputStream(this.deductionTableTemplate.getTemplateFile()));
        Sheet sheet = workbook.getSheetAt(0);

        // Query deduction rates
        TypedQuery<DeductionRate> query = entityManager.createQuery(
                "SELECT r FROM DeductionRate r JOIN FETCH r.retirementType WHERE r.deleted = false ORDER BY r.id",
                DeductionRate.class);

        for (DeductionRate rate : query.getResultList()) {
            Row row = sheet.getRow(currentRow++);
            if (row == null) {
                row = sheet.createRow(currentRow - 1);
            }
            // Service Type column
            Cell cell = row.getCell(startColumn);
            if (cell == null) {
                cell = row.createCell(startColumn);
            }
            cell.setCellType(Cell.CELL_TYPE_STRING);
            cell.setCellValue(rate.getServiceType());
            // Retirement Type column
            cell = row.getCell(startColumn + 1);
            if (cell == null) {
                cell = row.createCell(startColumn + 1);
            }
            cell.setCellType(Cell.CELL_TYPE_STRING);
            cell.setCellValue(rate.getRetirementType().getName());
            // Date range column
            cell = row.getCell(startColumn + 2);
            if (cell == null) {
                cell = row.createCell(startColumn + 2);
            }
            cell.setCellType(Cell.CELL_TYPE_STRING);
            cell.setCellValue(df.format(rate.getStartDate()) + "," + df.format(rate.getEndDate()));
            // Date range column
            cell = row.getCell(startColumn + 3);
            if (cell == null) {
                cell = row.createCell(startColumn + 3);
            }
            cell.setCellType(Cell.CELL_TYPE_STRING);
            cell.setCellValue(
                    df.format(rate.getStartDate()) + "," + df.format(rate.getEndDate()) + "," + rate.getRate());
        }
        templateOutput = new FileOutputStream(this.deductionTableTemplate.getDecisionTableFile());
        workbook.write(templateOutput);
    } catch (Exception ex) {
        throw new OPMConfigurationException("Failed to generate deduction rates decision table.", ex);
    } finally {
        if (templateOutput != null) {
            try {
                templateOutput.close();
            } catch (IOException e) {
            }
        }
    }
}

From source file:gov.opm.scrd.services.impl.InterestCalculationRuleServiceImpl.java

License:Apache License

/**
 * Generate interest tables.//w w  w . j ava2 s.  c  o m
 * @throws OPMConfigurationException
 */
private void generateInterestTables() throws OPMConfigurationException {
    OutputStream csrsInterestTemplateOutput = null;
    OutputStream csrsPeaceCorpsInterestTemplateOutput = null;
    OutputStream csrsRedepositInterestTemplateOutput = null;
    OutputStream fersInterestTemplateOutput = null;
    OutputStream fersPeaceCorpsInterestTemplateOutput = null;
    OutputStream fersRedepositInterestTemplateOutput = null;
    try {
        // Query interest rates
        TypedQuery<InterestRate> query = entityManager.createQuery(
                "SELECT r FROM InterestRate r WHERE r.deleted = false ORDER BY r.interestYear",
                InterestRate.class);
        List<InterestRate> interestRates = query.getResultList();

        // csrs_interest.xls
        int currentRow = this.csrsInterestTemplate.getStartCellRow();
        int startColumn = this.csrsInterestTemplate.getStartCellColumn();

        Workbook workbook = WorkbookFactory
                .create(new FileInputStream(this.csrsInterestTemplate.getTemplateFile()));
        Sheet sheet = workbook.getSheetAt(0);

        for (InterestRate rate : interestRates) {
            Row row = sheet.getRow(currentRow++);
            if (row == null) {
                row = sheet.createRow(currentRow - 1);
            }
            // Year column
            Cell cell = row.getCell(startColumn);
            if (cell == null) {
                cell = row.createCell(startColumn);
            }
            cell.setCellType(Cell.CELL_TYPE_STRING);
            cell.setCellValue(String.valueOf(rate.getInterestYear()));
            // Rate column
            cell = row.getCell(startColumn + 1);
            if (cell == null) {
                cell = row.createCell(startColumn + 1);
            }
            cell.setCellType(Cell.CELL_TYPE_STRING);
            cell.setCellValue(rate.getInterestRate().toString());
        }
        csrsInterestTemplateOutput = new FileOutputStream(this.csrsInterestTemplate.getDecisionTableFile());
        workbook.write(csrsInterestTemplateOutput);

        // csrs_peacecorps_interest.xls
        currentRow = this.csrsPeaceCorpsInterestTemplate.getStartCellRow();
        startColumn = this.csrsPeaceCorpsInterestTemplate.getStartCellColumn();

        workbook = WorkbookFactory
                .create(new FileInputStream(this.csrsPeaceCorpsInterestTemplate.getTemplateFile()));
        sheet = workbook.getSheetAt(0);

        for (int i = 0; i < interestRates.size() - 1; i++) {
            InterestRate rate1 = interestRates.get(i);
            if (rate1.getInterestYear() >= 1995) {
                InterestRate rate2 = interestRates.get(i + 1);
                Row row = sheet.getRow(currentRow++);
                if (row == null) {
                    row = sheet.createRow(currentRow - 1);
                }
                // Year column
                Cell cell = row.getCell(startColumn);
                if (cell == null) {
                    cell = row.createCell(startColumn);
                }
                cell.setCellType(Cell.CELL_TYPE_STRING);
                cell.setCellValue(String.valueOf(rate1.getInterestYear()) + ","
                        + String.valueOf(rate2.getInterestYear()));
                // Rate column
                cell = row.getCell(startColumn + 1);
                if (cell == null) {
                    cell = row.createCell(startColumn + 1);
                }
                cell.setCellType(Cell.CELL_TYPE_STRING);
                cell.setCellValue(
                        rate1.getInterestRate().toString() + "," + rate2.getInterestRate().toString());
            }
        }
        csrsPeaceCorpsInterestTemplateOutput = new FileOutputStream(
                this.csrsPeaceCorpsInterestTemplate.getDecisionTableFile());
        workbook.write(csrsPeaceCorpsInterestTemplateOutput);

        // csrs_redeposit_interest.xls
        currentRow = this.csrsRedepositInterestTemplate.getStartCellRow();
        startColumn = this.csrsRedepositInterestTemplate.getStartCellColumn();

        workbook = WorkbookFactory
                .create(new FileInputStream(this.csrsRedepositInterestTemplate.getTemplateFile()));
        sheet = workbook.getSheetAt(0);

        for (InterestRate rate : interestRates) {
            Row row = sheet.getRow(currentRow++);
            if (row == null) {
                row = sheet.createRow(currentRow - 1);
            }
            // Year column
            Cell cell = row.getCell(startColumn);
            if (cell == null) {
                cell = row.createCell(startColumn);
            }
            cell.setCellType(Cell.CELL_TYPE_STRING);
            cell.setCellValue(String.valueOf(rate.getInterestYear()));
            // Rate column
            cell = row.getCell(startColumn + 1);
            if (cell == null) {
                cell = row.createCell(startColumn + 1);
            }
            cell.setCellType(Cell.CELL_TYPE_STRING);
            cell.setCellValue(rate.getInterestRate().toString());
        }
        csrsRedepositInterestTemplateOutput = new FileOutputStream(
                this.csrsRedepositInterestTemplate.getDecisionTableFile());
        workbook.write(csrsRedepositInterestTemplateOutput);

        // fers_interest.xls
        currentRow = this.fersInterestTemplate.getStartCellRow();
        startColumn = this.fersInterestTemplate.getStartCellColumn();

        workbook = WorkbookFactory.create(new FileInputStream(this.fersInterestTemplate.getTemplateFile()));
        sheet = workbook.getSheetAt(0);

        for (InterestRate rate : interestRates) {
            Row row = sheet.getRow(currentRow++);
            if (row == null) {
                row = sheet.createRow(currentRow - 1);
            }
            // Year column
            Cell cell = row.getCell(startColumn);
            if (cell == null) {
                cell = row.createCell(startColumn);
            }
            cell.setCellType(Cell.CELL_TYPE_STRING);
            cell.setCellValue(String.valueOf(rate.getInterestYear()));
            // Rate column
            cell = row.getCell(startColumn + 1);
            if (cell == null) {
                cell = row.createCell(startColumn + 1);
            }
            cell.setCellType(Cell.CELL_TYPE_STRING);
            cell.setCellValue(rate.getInterestRate().toString());
        }
        fersInterestTemplateOutput = new FileOutputStream(this.fersInterestTemplate.getDecisionTableFile());
        workbook.write(fersInterestTemplateOutput);

        // fers_peacecorps_interest.xls
        currentRow = this.fersPeaceCorpsInterestTemplate.getStartCellRow();
        startColumn = this.fersPeaceCorpsInterestTemplate.getStartCellColumn();

        workbook = WorkbookFactory
                .create(new FileInputStream(this.fersPeaceCorpsInterestTemplate.getTemplateFile()));
        sheet = workbook.getSheetAt(0);

        for (int i = 0; i < interestRates.size() - 1; i++) {
            InterestRate rate1 = interestRates.get(i);
            if (rate1.getInterestYear() >= 1995) {
                InterestRate rate2 = interestRates.get(i + 1);
                Row row = sheet.getRow(currentRow++);
                if (row == null) {
                    row = sheet.createRow(currentRow - 1);
                }
                // Year column
                Cell cell = row.getCell(startColumn);
                if (cell == null) {
                    cell = row.createCell(startColumn);
                }
                cell.setCellType(Cell.CELL_TYPE_STRING);
                cell.setCellValue(String.valueOf(rate1.getInterestYear()) + ","
                        + String.valueOf(rate2.getInterestYear()));
                // Rate column
                cell = row.getCell(startColumn + 1);
                if (cell == null) {
                    cell = row.createCell(startColumn + 1);
                }
                cell.setCellType(Cell.CELL_TYPE_STRING);
                cell.setCellValue(
                        rate1.getInterestRate().toString() + "," + rate2.getInterestRate().toString());
            }
        }
        fersPeaceCorpsInterestTemplateOutput = new FileOutputStream(
                this.fersPeaceCorpsInterestTemplate.getDecisionTableFile());
        workbook.write(fersPeaceCorpsInterestTemplateOutput);

        // fers_redeposit_interest.xls
        currentRow = this.fersRedepositInterestTemplate.getStartCellRow();
        startColumn = this.fersRedepositInterestTemplate.getStartCellColumn();

        workbook = WorkbookFactory
                .create(new FileInputStream(this.fersRedepositInterestTemplate.getTemplateFile()));
        sheet = workbook.getSheetAt(0);

        for (InterestRate rate : interestRates) {
            Row row = sheet.getRow(currentRow++);
            if (row == null) {
                row = sheet.createRow(currentRow - 1);
            }
            // Year column
            Cell cell = row.getCell(startColumn);
            if (cell == null) {
                cell = row.createCell(startColumn);
            }
            cell.setCellType(Cell.CELL_TYPE_STRING);
            cell.setCellValue(String.valueOf(rate.getInterestYear()));
            // Rate column
            cell = row.getCell(startColumn + 1);
            if (cell == null) {
                cell = row.createCell(startColumn + 1);
            }
            cell.setCellType(Cell.CELL_TYPE_STRING);
            cell.setCellValue(rate.getInterestRate().toString());
        }
        fersRedepositInterestTemplateOutput = new FileOutputStream(
                this.fersRedepositInterestTemplate.getDecisionTableFile());
        workbook.write(fersRedepositInterestTemplateOutput);
    } catch (Exception ex) {
        throw new OPMConfigurationException("Failed to generate interest rates decision table.", ex);
    } finally {
        if (csrsInterestTemplateOutput != null) {
            try {
                csrsInterestTemplateOutput.close();
            } catch (IOException e) {
            }
        }
        if (csrsPeaceCorpsInterestTemplateOutput != null) {
            try {
                csrsPeaceCorpsInterestTemplateOutput.close();
            } catch (IOException e) {
            }
        }
        if (csrsRedepositInterestTemplateOutput != null) {
            try {
                csrsRedepositInterestTemplateOutput.close();
            } catch (IOException e) {
            }
        }
        if (fersInterestTemplateOutput != null) {
            try {
                fersInterestTemplateOutput.close();
            } catch (IOException e) {
            }
        }
        if (fersPeaceCorpsInterestTemplateOutput != null) {
            try {
                fersInterestTemplateOutput.close();
            } catch (IOException e) {
            }
        }
        if (fersRedepositInterestTemplateOutput != null) {
            try {
                fersInterestTemplateOutput.close();
            } catch (IOException e) {
            }
        }
    }
}

From source file:gr.open.loglevelsmanager.loglevel.LogLevelsManager.java

License:Open Source License

@Override
public void serveResource(ResourceRequest resourceRequest, ResourceResponse resourceResponse)
        throws PortletException, IOException {

    resourceResponse.setContentType("text/javascript");
    String resourceId = resourceRequest.getResourceID();

    if (Validator.isNotNull(resourceId) && resourceId.length() != 0) {

        if (resourceId.equalsIgnoreCase("exportFullLogLevelResourceURL")) {

            ThemeDisplay themeDisplay = (ThemeDisplay) resourceRequest.getAttribute(WebKeys.THEME_DISPLAY);
            Locale locale = themeDisplay.getLocale();
            PortletConfig portletConfig = (PortletConfig) resourceRequest
                    .getAttribute(JavaConstants.JAVAX_PORTLET_CONFIG);

            resourceResponse.setContentType("application/vnd.ms-excel");
            resourceResponse.setProperty("expires", "-1d");
            resourceResponse.setProperty("Pragma", "no-cache");
            resourceResponse.setProperty("Cache-control", "no-cache");
            resourceResponse.addProperty(HttpHeaders.CONTENT_DISPOSITION, "attachment; filename=\" LogLevel\"");

            try {
                Workbook book = LogLevelExporter.generateFullExcel(themeDisplay.getScopeGroupId(),
                        portletConfig, locale);
                OutputStream out = resourceResponse.getPortletOutputStream();
                book.write(out);
                out.flush();//  w w w .j  a  v  a2  s.  c  o  m
                out.close();
            } catch (SystemException e) {
                e.printStackTrace();
                throw new PortletException("Export Excel Error", e);
            }
        }

    }
}

From source file:guru.qas.martini.report.DefaultTraceabilityMatrix.java

License:Apache License

@Override
public void createReport(JsonReader reader, OutputStream outputStream) throws IOException {
    checkNotNull(reader, "null JsonReader");
    checkNotNull(outputStream, "null OutputStream");

    Workbook workbook = new XSSFWorkbook();
    Sheet sheet = workbook.createSheet("Results");
    addHeader(sheet);//from www .j  a v  a 2  s. c o m

    State state = new DefaultState();
    while (reader.hasNext()) {
        JsonToken peek = reader.peek();

        JsonObject object;
        switch (peek) {
        case BEGIN_ARRAY:
            reader.beginArray();
            continue;
        case BEGIN_OBJECT:
            object = gson.fromJson(reader, JsonObject.class);
            break;
        case END_ARRAY:
            reader.endArray();
            continue;
        case END_DOCUMENT:
            reader.skipValue();
            continue;
        default:
            JsonElement element = gson.fromJson(reader, JsonElement.class);
            LOGGER.warn("skipping unhandled element {}", element);
            continue;
        }

        switch (JsonObjectType.evaluate(object)) {
        case SUITE:
            JsonObject suite = SUITE.get(object);
            state.addSuite(suite);
            break;
        case FEATURE:
            JsonObject feature = FEATURE.get(object);
            state.addFeature(feature);
            break;
        case RESULT:
            JsonObject result = RESULT.get(object);
            addResult(state, sheet, result);
            break;
        default:
            LOGGER.warn("skipping unrecognized JsonObject: {}", object);
        }
    }

    state.updateResults();
    resizeColumns(sheet);

    Sheet suiteSheet = workbook.createSheet("Suites");
    state.updateSuites(suiteSheet);

    workbook.write(outputStream);
    outputStream.flush();
}

From source file:hedicim.Patient.java

static void makeStatFile(String filename) throws FileNotFoundException, IOException {
    try (FileOutputStream fileOut = new FileOutputStream(filename)) {
        Workbook wb = new XSSFWorkbook();
        Sheet sheet = wb.createSheet("HEDICIM AMIA 2015");
        int r = 0;
        Row header_row = sheet.createRow(r++);
        for (String stat_cell : stat_cells)
            header_row.createCell(Arrays.asList(stat_cells).indexOf(stat_cell)).setCellValue(stat_cell);
        for (Patient patient : map.values()) {
            if (!patient.eligible)
                continue;
            Row row = sheet.createRow(r++);
            for (String stat_cell : stat_cells)
                patient.makeStatCell(row, stat_cell);
        }/*from  ww w. j  ava 2 s . c  o m*/
        wb.write(fileOut);
        fileOut.close();
        System.out.println("Statistics file " + filename + " succesfully written.");
    } catch (Exception ex) {
        System.err.println("Exception at makeStatFile(\"" + filename + "\"): " + ex.getLocalizedMessage());
    }
}

From source file:hjow.hgtable.util.XLSXUtil.java

License:Apache License

/**
 * <p>?  ? XLSX ? ? .</p>//  w  w w  .  j a v a  2 s  . c  o  m
 * 
 * @param tableSet : ?  ?
 * @param file : ? ?
 */
public static void save(TableSet tableSet, File file) {
    org.apache.poi.ss.usermodel.Workbook workbook = null;
    if (file == null)
        throw new NullPointerException(Manager.applyStringTable("Please select file !!"));

    FileOutputStream fileStream = null;
    ChainOutputStream chainStream = null;

    try {
        String targetPath = StreamUtil.getDirectoryPathOfFile(file);
        File dir = new File(targetPath);
        // Main.println(dir);
        if (!dir.exists())
            dir.mkdir();
    } catch (Throwable e) {
        Main.logError(e, "On mkdir on saving xlsx");
    }

    try {
        fileStream = new FileOutputStream(file);
        chainStream = new ChainOutputStream(fileStream);
        StreamUtil.additionalSetting(chainStream);

        workbook = new org.apache.poi.xssf.streaming.SXSSFWorkbook();
        //         workbook = new org.apache.poi.xssf.usermodel.XSSFWorkbook();
        org.apache.poi.ss.usermodel.Sheet sheet = workbook.createSheet();

        org.apache.poi.ss.usermodel.Row headerRow = sheet.createRow(0);
        for (int i = 0; i < tableSet.getColumns().size(); i++) {
            org.apache.poi.ss.usermodel.Cell headerCell = headerRow.createCell(i);
            headerCell.setCellValue(tableSet.getColumns().get(i).getName());
        }

        for (int i = 0; i < tableSet.getRecordCount(); i++) {
            org.apache.poi.ss.usermodel.Row row = sheet.createRow(i + 1);
            for (int j = 0; j < tableSet.getColumns().size(); j++) {
                org.apache.poi.ss.usermodel.Cell cell = row.createCell(j);

                if (Column.TYPE_STRING == tableSet.getColumns().get(j).getType()) {
                    cell.setCellValue(tableSet.getColumns().get(j).getData().get(i));
                } else if (Column.TYPE_NUMERIC == tableSet.getColumns().get(j).getType()) {
                    cell.setCellValue(Double.parseDouble(tableSet.getColumns().get(j).getData().get(i)));
                } else if (Column.TYPE_DATE == tableSet.getColumns().get(j).getType()) {
                    cell.setCellValue(tableSet.getColumns().get(j).getData().get(i));
                } else if (Column.TYPE_BOOLEAN == tableSet.getColumns().get(j).getType()) {
                    cell.setCellValue(DataUtil.parseBoolean(tableSet.getColumns().get(j).getData().get(i)));
                } else if (Column.TYPE_BLANK == tableSet.getColumns().get(j).getType()) {
                    cell.setCellValue("");
                } else {
                    cell.setCellValue(tableSet.getColumns().get(j).getData().get(i));
                }
            }
        }

        workbook.write(chainStream.getOutputStream());
        workbook.close();
    } catch (Throwable e) {
        e.printStackTrace();
    } finally {
        try {
            workbook.close();
        } catch (Throwable e) {

        }
        try {
            chainStream.close();
        } catch (Throwable e) {

        }
        try {
            fileStream.close();
        } catch (Throwable e) {

        }

    }
}

From source file:http.RequestHeadersSpreadsheetServlet.java

@Override
public void doGet(HttpServletRequest request, HttpServletResponse response)
        throws IOException, ServletException {

    // create workbook and sheet for spreadsheet
    Workbook workbook = new HSSFWorkbook();
    Sheet sheet = workbook.createSheet("Request Headers");

    Enumeration headerNames = request.getHeaderNames();
    int i = 0;/*www  .  j  av a 2s  . c o  m*/
    while (headerNames.hasMoreElements()) {
        String name = (String) headerNames.nextElement();
        String value = request.getHeader(name);

        // create the row and store data in its cells
        Row row = sheet.createRow(i);
        row.createCell(0).setCellValue(name);
        row.createCell(1).setCellValue(value);
        i++;
    }

    // set the response headers to return an attached .xls file
    response.setHeader("content-disposition", "attachment; filename=request_headers.xls");
    response.setHeader("cache-control", "no-cache");

    // get the output stream and send the workbook to the browser
    OutputStream out = response.getOutputStream();
    workbook.write(out);
    out.close();
}

From source file:Import.Utils.XSSFConvert.java

public void convert() throws IOException {
    Workbook[] wbs = new Workbook[] { new HSSFWorkbook(), new XSSFWorkbook() };
    for (int i = 0; i < wbs.length; i++) {
        Workbook wb = wbs[i];
        CreationHelper createHelper = wb.getCreationHelper();

        // create a new sheet
        org.apache.poi.ss.usermodel.Sheet s = wb.createSheet();
        // declare a row object reference
        Row r = null;/*from   w  ww . ja  va 2  s. c  o m*/
        // declare a cell object reference
        Cell c = null;
        // create 2 cell styles
        CellStyle cs = wb.createCellStyle();
        CellStyle cs2 = wb.createCellStyle();
        DataFormat df = wb.createDataFormat();

        // create 2 fonts objects
        Font f = wb.createFont();
        Font f2 = wb.createFont();

        // Set font 1 to 12 point type, blue and bold
        f.setFontHeightInPoints((short) 12);
        f.setColor(IndexedColors.RED.getIndex());
        f.setBoldweight(Font.BOLDWEIGHT_BOLD);

        // Set font 2 to 10 point type, red and bold
        f2.setFontHeightInPoints((short) 10);
        f2.setColor(IndexedColors.RED.getIndex());
        f2.setBoldweight(Font.BOLDWEIGHT_BOLD);

        // Set cell style and formatting
        cs.setFont(f);
        cs.setDataFormat(df.getFormat("#,##0.0"));

        // Set the other cell style and formatting
        cs2.setBorderBottom(cs2.BORDER_THIN);
        cs2.setDataFormat(df.getFormat("text"));
        cs2.setFont(f2);

        // Define a few rows
        for (int rownum = 0; rownum < 30; rownum++) {
            r = s.createRow(rownum);
            for (int cellnum = 0; cellnum < 10; cellnum += 2) {
                c = r.createCell(cellnum);
                Cell c2 = r.createCell(cellnum + 1);

                c.setCellValue((double) rownum + (cellnum / 10));
                c2.setCellValue(createHelper.createRichTextString("Hello! " + cellnum));
            }
        }

        // Save
        String filename = "workbook.xls";
        if (wb instanceof XSSFWorkbook) {
            filename = filename + "x";
        }

        FileOutputStream out = null;
        try {
            out = new FileOutputStream(filename);
        } catch (FileNotFoundException ex) {
            Logger.getLogger(XSSFConvert.class.getName()).log(Level.SEVERE, null, ex);
        }
        wb.write(out);
        out.close();
    }
}

From source file:info.informationsea.java.excel2csv.Converter.java

License:Open Source License

private void doConvertAllSheets(List<File> inputFiles, File outputFile) throws Exception {
    Workbook workbook;

    if (outputFile.isFile() && outputFile.length() > 512) {
        switch (Utilities.suggestFileTypeFromName(outputFile.getName())) {
        case FILETYPE_XLS:
        case FILETYPE_XLSX:
            workbook = WorkbookFactory.create(outputFile);
            break;
        default://from  w ww .jav  a2s.com
            throw new IllegalArgumentException("Output file format should be Excel format");
        }
    } else {
        switch (Utilities.suggestFileTypeFromName(outputFile.getName())) {
        case FILETYPE_XLS:
            workbook = new HSSFWorkbook();
            break;
        case FILETYPE_XLSX:
            if (largeExcelMode)
                workbook = new SXSSFWorkbook();
            else
                workbook = new XSSFWorkbook();
            break;
        default:
            throw new IllegalArgumentException("Output file format should be Excel format");
        }
    }

    if (largeExcelMode && !(workbook instanceof SXSSFWorkbook)) {
        log.warn("Streaming output mode is disabled");
    }
    //log.info("workbook: {}", workbook.getClass());

    for (File oneInput : inputFiles) {
        switch (Utilities.suggestFileTypeFromName(oneInput.getName())) {
        case FILETYPE_XLSX:
        case FILETYPE_XLS: {
            Workbook inputWorkbook = WorkbookFactory.create(oneInput);
            int sheetNum = inputWorkbook.getNumberOfSheets();
            for (int i = 0; i < sheetNum; i++) {
                try (TableReader reader = new ExcelSheetReader(inputWorkbook.getSheetAt(i))) {
                    ExcelSheetWriter sheetWriter = new ExcelSheetWriter(
                            Utilities.createUniqueNameSheetForWorkbook(workbook, inputWorkbook.getSheetName(i),
                                    overwriteSheet));
                    sheetWriter.setPrettyTable(prettyTable);
                    try (TableWriter tableWriter = new FilteredWriter(sheetWriter, convertCellTypes,
                            fistCount)) {
                        Utilities.copyTable(reader, tableWriter, useHeader);
                    }
                }
            }
            break;
        }
        default: {
            try (TableReader reader = Utilities.openReader(oneInput, inputSheetIndex, inputSheetName)) {
                ExcelSheetWriter sheetWriter = new ExcelSheetWriter(Utilities
                        .createUniqueNameSheetForWorkbook(workbook, oneInput.getName(), overwriteSheet));
                sheetWriter.setPrettyTable(prettyTable);
                try (TableWriter tableWriter = new FilteredWriter(sheetWriter, convertCellTypes, fistCount)) {
                    Utilities.copyTable(reader, tableWriter, useHeader);
                }
            }
            break;
        }
        }
    }

    workbook.write(new FileOutputStream(outputFile));
}

From source file:info.informationsea.java.excel2csv.Utilities.java

License:Open Source License

public static TableWriter openWriter(final File outputFile, String sheetName, boolean overWrite,
        boolean enablePretty, boolean largeExcelMode) throws IOException {
    if (outputFile == null) {
        return new TableCSVWriter(new OutputStreamWriter(System.out), new TabDelimitedFormat());
    } else {//  www.  j ava2 s.co m
        FileType type = suggestFileTypeFromName(outputFile.getName());
        switch (type) {
        case FILETYPE_XLS:
        case FILETYPE_XLSX: {
            final Workbook workbook;
            if (outputFile.exists() && outputFile.length() > 512) {
                if (type == FileType.FILETYPE_XLSX)
                    workbook = new XSSFWorkbook(new FileInputStream(outputFile));
                else
                    workbook = new HSSFWorkbook(new FileInputStream(outputFile));
            } else {
                if (type == FileType.FILETYPE_XLSX) {
                    if (largeExcelMode)
                        workbook = new SXSSFWorkbook();
                    else
                        workbook = new XSSFWorkbook();
                } else {
                    workbook = new HSSFWorkbook();
                }
            }

            //log.info("workbook: {}", workbook.getClass());

            if (largeExcelMode && !(workbook instanceof SXSSFWorkbook)) {
                log.warn("Streaming output mode is disabled");
            }

            Sheet sheet = createUniqueNameSheetForWorkbook(workbook, sheetName, overWrite);
            final ExcelSheetWriter excelSheetWriter = new ExcelSheetWriter(sheet);
            excelSheetWriter.setPrettyTable(enablePretty);
            return new AbstractTableWriter() {
                @Override
                public void printRecord(Object... values) throws Exception {
                    for (int i = 0; i < values.length; i++) {
                        if (values[i] == null)
                            values[i] = "";
                    }
                    excelSheetWriter.printRecord(values);
                }

                @Override
                public void close() throws Exception {
                    excelSheetWriter.close();
                    try (OutputStream os = new FileOutputStream(outputFile)) {
                        workbook.write(os);
                    }
                }
            };
        }
        case FILETYPE_CSV:
            return new TableCSVWriter(new FileWriter(outputFile));
        case FILETYPE_TAB:
        default:
            return new TableCSVWriter(new FileWriter(outputFile), new TabDelimitedFormat());
        }
    }
}