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:com.rarediscovery.services.logic.Functions.java

/**
 *  FileName,//from w w w . j a va 2s .c  o  m
 *  Map<worsksheetName,WorksheetData>
 *  WorksheetData  = columnName[] , dataGrid[][]
 * 
 * , Map<String,String[][]> tabTodataGridMap
 */
public static void saveAsExcelWorkbook(String filename, final Map<String, List<String>> sheetToColumns,
        final Models models, DefaultListModel reportAttributes) {

    Workbook wb = new HSSFWorkbook();
    FileOutputStream fileOut;
    try {
        fileOut = new FileOutputStream(filename);

        //CreationHelper createHelper = wb.getCreationHelper();

        // Create worksheets
        int sheetCount = sheetToColumns.size();
        Sheet[] sheets = new Sheet[sheetCount];
        int i = 0;
        for (String sheetName : sheetToColumns.keySet()) {
            sheets[i] = wb.createSheet(" " + sheetName);
            String[] reportHeaders = sheetToColumns.get(sheetName).toArray(new String[0]);
            addContent(sheets[i], reportHeaders, models, reportAttributes);
            i++;
        }

        /*
        // Create a row and put some cells in it. Rows are 0 based.
        Row row = sheet1.createRow((short)0);
        // Create a cell and put a value in it.
        Cell cell = row.createCell(0);
        cell.setCellValue(1);
                
        // Or do it on one line.
        row.createCell(1).setCellValue(1.2);
        row.createCell(2).setCellValue(createHelper.createRichTextString("This is a string"));
        row.createCell(3).setCellValue(true);
        */

        wb.write(fileOut);
        fileOut.flush();
        fileOut.close();

        Functions.log(" Completed generating excel report ! - " + filename);

    } catch (FileNotFoundException ex) {
        Logger.getLogger(Driver.class.getName()).log(Level.SEVERE, null, ex);
    } catch (IOException ex) {
        Logger.getLogger(Driver.class.getName()).log(Level.SEVERE, null, ex);
    }
}

From source file:com.report.template.LoanCalculator.java

License:Apache License

public static void main(String[] args) throws Exception {
    Workbook wb;

    if (args.length > 0 && args[0].equals("-xls"))
        wb = new HSSFWorkbook();
    else/*from   w  w w.j a va  2 s. c o m*/
        wb = new XSSFWorkbook();

    Map<String, CellStyle> styles = createStyles(wb);
    Sheet sheet = wb.createSheet("Loan Calculator");
    sheet.setPrintGridlines(false);
    sheet.setDisplayGridlines(false);

    PrintSetup printSetup = sheet.getPrintSetup();
    printSetup.setLandscape(true);
    sheet.setFitToPage(true);
    sheet.setHorizontallyCenter(true);

    sheet.setColumnWidth(0, 3 * 256);
    sheet.setColumnWidth(1, 3 * 256);
    sheet.setColumnWidth(2, 11 * 256);
    sheet.setColumnWidth(3, 14 * 256);
    sheet.setColumnWidth(4, 14 * 256);
    sheet.setColumnWidth(5, 14 * 256);
    sheet.setColumnWidth(6, 14 * 256);

    createNames(wb);

    Row titleRow = sheet.createRow(0);
    titleRow.setHeightInPoints(35);
    for (int i = 1; i <= 7; i++) {
        titleRow.createCell(i).setCellStyle(styles.get("title"));
    }
    Cell titleCell = titleRow.getCell(2);
    titleCell.setCellValue("Simple Loan Calculator");
    sheet.addMergedRegion(CellRangeAddress.valueOf("$C$1:$H$1"));

    Row row = sheet.createRow(2);
    Cell cell = row.createCell(4);
    cell.setCellValue("Enter values");
    cell.setCellStyle(styles.get("item_right"));

    row = sheet.createRow(3);
    cell = row.createCell(2);
    cell.setCellValue("Loan amount");
    cell.setCellStyle(styles.get("item_left"));
    cell = row.createCell(4);
    cell.setCellValue("123123");
    cell.setCellStyle(styles.get("input_$"));
    cell.setAsActiveCell();

    row = sheet.createRow(4);
    cell = row.createCell(2);
    cell.setCellValue("Annual interest rate");
    cell.setCellStyle(styles.get("item_left"));
    cell = row.createCell(4);
    cell.setCellStyle(styles.get("input_%"));

    row = sheet.createRow(5);
    cell = row.createCell(2);
    cell.setCellValue("Loan period in years");
    cell.setCellStyle(styles.get("item_left"));
    cell = row.createCell(4);
    cell.setCellStyle(styles.get("input_i"));

    row = sheet.createRow(6);
    cell = row.createCell(2);
    cell.setCellValue("Start date of loan");
    cell.setCellStyle(styles.get("item_left"));
    cell = row.createCell(4);
    cell.setCellStyle(styles.get("input_d"));

    row = sheet.createRow(8);
    cell = row.createCell(2);
    cell.setCellValue("Monthly payment");
    cell.setCellStyle(styles.get("item_left"));
    cell = row.createCell(4);
    cell.setCellFormula("IF(Values_Entered,Monthly_Payment,\"\")");
    cell.setCellStyle(styles.get("formula_$"));

    row = sheet.createRow(9);
    cell = row.createCell(2);
    cell.setCellValue("Number of payments");
    cell.setCellStyle(styles.get("item_left"));
    cell = row.createCell(4);
    cell.setCellFormula("IF(Values_Entered,Loan_Years*12,\"\")");
    cell.setCellStyle(styles.get("formula_i"));

    row = sheet.createRow(10);
    cell = row.createCell(2);
    cell.setCellValue("Total interest");
    cell.setCellStyle(styles.get("item_left"));
    cell = row.createCell(4);
    cell.setCellFormula("IF(Values_Entered,Total_Cost-Loan_Amount,\"\")");
    cell.setCellStyle(styles.get("formula_$"));

    row = sheet.createRow(11);
    cell = row.createCell(2);
    cell.setCellValue("Total cost of loan");
    cell.setCellStyle(styles.get("item_left"));
    cell = row.createCell(4);
    cell.setCellFormula("IF(Values_Entered,Monthly_Payment*Number_of_Payments,\"\")");
    cell.setCellStyle(styles.get("formula_$"));

    // Write the output to a file
    String file = "loan-calculator.xls";
    if (wb instanceof XSSFWorkbook)
        file += "x";
    FileOutputStream out = new FileOutputStream(file);
    wb.write(out);
    out.close();
}

From source file:com.runwaysdk.dataaccess.io.excel.ContextBuilder.java

License:Open Source License

@Override
public ImportContext createContext(Sheet sheet, String sheetName, Workbook errorWorkbook, String type) {
    MdClassDAOIF mdClass = MdClassDAO.getMdClassDAO(type);
    if (!(mdClass instanceof MdViewDAO) && !(mdClass instanceof MdBusinessDAO)) {
        throw new UnexpectedTypeException(
                "Excel Importer does not support type [" + mdClass.definesType() + "]");
    }//  www.  j a  va  2s.co  m

    Sheet error = errorWorkbook.createSheet(sheetName);
    return new ImportContext(sheet, sheetName, error, mdClass);
}

From source file:com.runwaysdk.dataaccess.io.ExcelExportSheet.java

License:Open Source License

/**
 * Prepares a new sheet (which represents a type) in the workbook. Fills in all necessary information for the sheet.
 * /*from www . j a  v a 2  s . c om*/
 * @return
 */
public Sheet createSheet(Workbook workbook, CellStyle boldStyle) {
    CreationHelper helper = workbook.getCreationHelper();
    String sheetName = this.getFormattedSheetName();

    Sheet sheet = workbook.createSheet(sheetName);
    Drawing drawing = sheet.createDrawingPatriarch();

    Row typeRow = sheet.createRow(0);
    typeRow.setZeroHeight(true);

    Row nameRow = sheet.createRow(1);
    nameRow.setZeroHeight(true);

    Row labelRow = sheet.createRow(2);

    int i = 0;
    for (ExcelColumn column : this.getExpectedColumns()) {
        writeHeader(sheet, drawing, nameRow, labelRow, i++, column, boldStyle);
    }

    for (ExcelColumn column : this.getExtraColumns()) {
        writeHeader(sheet, drawing, nameRow, labelRow, i++, column, boldStyle);
    }

    typeRow.createCell(0).setCellValue(helper.createRichTextString(this.getType()));

    this.writeRows(sheet);

    return sheet;
}

From source file:com.seer.datacruncher.profiler.spring.ExporterController.java

License:Open Source License

public ModelAndView handleRequest(HttpServletRequest request, HttpServletResponse response)
        throws ServletException, IOException {

    String type = CommonUtil.notNullValue(request.getParameter("exportaction"));
    String columns = CommonUtil.notNullValue(request.getParameter("exportcolumns"));
    String data = CommonUtil.notNullValue(request.getParameter("exportdata"));

    if (type.equals("csv")) {
        PrintWriter out = response.getWriter();
        response.setContentType("application/csv");
        response.setHeader("content-disposition", "attachment;filename=analysis_data.csv"); // set the file
        // name to
        // whatever
        // required..
        out.println(columns.replace("&&&&&", ","));
        for (String strData : data.split("@@@@@")) {
            out.println(strData.replace("&&&&&", ","));
        }//from w  ww. j a  v a2  s . c o m
        out.flush();
        out.close();
    } else if (type.equals("xml")) {
        PrintWriter out = response.getWriter();
        response.setContentType("text/xml");
        response.setHeader("content-disposition", "attachment;filename=analysis_data.xml"); // set the file
        // name to
        // whatever
        // required..
        try {
            StringBuffer xml = new StringBuffer("<?xml version=\"1.0\" encoding=\"utf-8\"?>\n");
            xml.append("<table><header>");
            String colArr[] = columns.split("&&&&&");
            for (String col : colArr) {
                xml.append("<columnName>" + col + "</columnName>");
            }
            xml.append("</header>");

            for (String strData : data.split("@@@@@")) {
                xml.append("<row>");
                int ind = 0;
                for (String val : strData.split("&&&&&")) {
                    xml.append("<" + colArr[ind] + ">" + val + "</" + colArr[ind] + "/>");
                    ind++;
                }
                xml.append("</row>");
            }
            xml.append("</table>");
            out.print(xml.toString());
        } catch (Exception e) {
            e.printStackTrace();
        }
        out.flush();
        out.close();
    } else if (type.equals("excel")) {
        response.setContentType("application/vnd.ms-excel");
        response.setHeader("Content-Disposition", "attachment; filename=analysis_data.xls");
        Workbook wb = new HSSFWorkbook();
        Sheet sheet = wb.createSheet("new sheet");
        String colArr[] = columns.split("&&&&&");
        short ind = 0;
        CellStyle style = wb.createCellStyle();
        Font font = wb.createFont();
        font.setBoldweight(Font.BOLDWEIGHT_BOLD);
        style.setFont(font);
        Row row = sheet.createRow(0);
        for (String col : colArr) {
            Cell cell = row.createCell(ind);
            cell.setCellValue(col);
            cell.setCellStyle(style);
            ind++;
        }
        ind = 1;
        for (String strData : data.split("@@@@@")) {
            Row valRow = sheet.createRow(ind);
            short cellInd = 0;
            for (String val : strData.split("&&&&&")) {
                valRow.createCell(cellInd).setCellValue(val);
                cellInd++;
            }
            ind++;
        }

        // Write the output to a file
        OutputStream resOout = response.getOutputStream();
        wb.write(resOout);
        resOout.close();

    }

    return null;
}

From source file:com.setu.hsapiassistance.service.ReportService.java

Workbook createReport(List<History> histories) {
    Workbook wb = new HSSFWorkbook();
    //Workbook wb = new XSSFWorkbook();
    CreationHelper createHelper = wb.getCreationHelper();
    Sheet sheet = wb.createSheet("new sheet");

    // Create a row and put some cells in it. Rows are 0 based.
    Row row = sheet.createRow((short) 0);
    // Create a cell and put a value in it.
    Cell contactHeader = row.createCell(0);
    contactHeader.setCellValue("Contact");
    Cell dateHeader = row.createCell(1);
    dateHeader.setCellValue("Date");
    Cell actionHeader = row.createCell(2);
    actionHeader.setCellValue("Action");

    for (int i = 0; i < histories.size(); i++) {
        row = sheet.createRow(i + 1);/*from w  w  w .j  a  v  a  2s .c om*/
        Cell contact = row.createCell(0);
        contact.setCellValue(histories.get(i).getEmail());
        Cell date = row.createCell(1);
        date.setCellValue(getFormattedDate(histories.get(i).getDate()));
        Cell action = row.createCell(2);
        action.setCellValue(histories.get(i).getAction());
    }

    return wb;
}

From source file:com.shiyq.poi.HSSFTest.java

public static boolean createExcel(String excelName) {
    boolean created = false;
    Workbook wb = new HSSFWorkbook();
    Font font = wb.createFont();/*from ww w .j a  v a  2  s  . c  o m*/
    font.setBold(true);
    CellStyle headStyle = wb.createCellStyle();
    headStyle.setFont(font);

    Sheet sheet = wb.createSheet("20165???");
    String[] head = { "??", "?", "??", "???", "????",
            "????", "?", "??", "", "",
            "??" };
    String[] code = { "card_no", "card_type", "spread_time", "spread_emp_no", "spread_emp_name", "owner_name",
            "plate", "blance", "start_time", "end_time", "month_money" };

    List<Map<String, Object>> list = setList();
    setSheet(sheet, list, head, headStyle, code);
    //
    int startRow = 4;
    int endRow = 8;
    int startColumn = head.length + 4;
    int endColumn = head.length + 8;

    String describe = "1?" + (new Date().toString()) + "\n";
    describe += "2.\n";
    describe += "3.?2016-4-42016-5-4";
    CellStyle descStyle = wb.createCellStyle();
    descStyle.setAlignment(CellStyle.ALIGN_LEFT);
    descStyle.setVerticalAlignment(CellStyle.VERTICAL_TOP);
    setSheet(sheet, startRow, endRow, startColumn, endColumn, describe, descStyle);

    Sheet sheet1 = wb.createSheet("20166???");
    setSheet(sheet1, list, head, headStyle, code);
    setSheet(sheet1, startRow, endRow, startColumn, endColumn, describe, descStyle);
    try {
        try (FileOutputStream fileOut = new FileOutputStream(excelName)) {
            wb.write(fileOut);
            created = true;
        }
    } catch (FileNotFoundException ex) {
        Logger.getLogger(HSSFTest.class.getName()).log(Level.SEVERE, null, ex);
    } catch (IOException ex) {
        Logger.getLogger(HSSFTest.class.getName()).log(Level.SEVERE, null, ex);
    }
    return created;
}

From source file:com.siberhus.tdfl.DflBaseTest.java

License:Apache License

@Before
public void createXLSFile() throws Exception {
    if (new File(XLS_FILE_IN_NAME).exists()) {
        return;// ww  w .j a v  a  2 s. c  o  m
    }
    Workbook workbook = new HSSFWorkbook();
    Sheet sheet = workbook.createSheet("Untitled");
    String dataArray[][] = getTestData();
    for (int i = 0; i < dataArray.length; i++) {
        String data[] = dataArray[i];
        Row row = sheet.createRow(i);
        for (int j = 0; j < data.length; j++) {
            row.createCell(j).setCellValue(data[j]);
        }
    }
    OutputStream out = new FileOutputStream(XLS_FILE_IN_NAME);
    workbook.write(out);
    IOUtils.closeQuietly(out);
}

From source file:com.siemens.sw360.exporter.ExcelExporter.java

License:Open Source License

public InputStream makeExcelExport(List<T> documents) throws IOException {
    final Workbook workbook = new XSSFWorkbook();

    Sheet sheet = workbook.createSheet("Component Data");

    /** Adding styles to cells */
    CellStyle cellStyte = createCellStyte(workbook);
    CellStyle headerStyle = createHeaderStyle(workbook);

    /** Create header row */
    Row headerRow = sheet.createRow(0);//from   w  w w .  j  a  v a 2 s  . c o m
    List<String> headerNames = helper.getHeaders();
    fillRow(headerRow, headerNames, headerStyle);

    /** Create data rows */
    fillValues(sheet, documents, cellStyte);

    /** Resize the columns */
    for (int iColumns = 0; iColumns < nColumns; iColumns++) {
        sheet.autoSizeColumn(iColumns);
    }

    /** Copy the streams */
    final ByteArrayOutputStream out = new ByteArrayOutputStream();
    workbook.write(out);

    return new ByteArrayInputStream(out.toByteArray());
}

From source file:com.sirelab.controller.reportes.ControllerGeneradorReportes.java

public void reporteUsuariosSistema() throws Exception {
    String rutaArchivo = "";
    if (validarNombreReporte()) {
        rutaArchivo = System.getProperty("user.home") + "/" + nombreReporte + ".xls";
    } else {/*from w  w w.  j  a  v  a2 s.  c  o  m*/
        rutaArchivo = System.getProperty("user.home") + "/" + "USUARIOS_REGISTRADOS_SIRELAB" + ".xls";
    }
    File archivoXLS = new File(rutaArchivo);
    if (archivoXLS.exists()) {
        archivoXLS.delete();
    }
    archivoXLS.createNewFile();
    Workbook libro = new HSSFWorkbook();
    FileOutputStream archivo = new FileOutputStream(archivoXLS);
    Sheet hoja = libro.createSheet("USUARIOS REGISTRADOS");
    List<Persona> personas = administradorGeneradorReportesBO.obtenerPersonasDelSistema();
    int tamtotal = personas.size();
    for (int f = 0; f < tamtotal; f++) {
        Row fila = hoja.createRow(f);
        Persona persona = personas.get(f);
        for (int c = 0; c < 10; c++) {
            Cell celda = fila.createCell(c);
            if (f == 0) {
                if (c == 0) {
                    celda.setCellValue("NOMBRES_USUARIO");
                } else if (c == 1) {
                    celda.setCellValue("APELLIDOS_USUARIO");
                } else if (c == 2) {
                    celda.setCellValue("IDENTIFICACION");
                } else if (c == 3) {
                    celda.setCellValue("CORREO");
                } else if (c == 4) {
                    celda.setCellValue("TELEFONO_1");
                } else if (c == 5) {
                    celda.setCellValue("TELEFONO_2");
                } else if (c == 6) {
                    celda.setCellValue("DIRECCION");
                } else if (c == 7) {
                    celda.setCellValue("USUARIO");
                } else if (c == 8) {
                    celda.setCellValue("TIPO_USUARIO");
                } else if (c == 9) {
                    celda.setCellValue("ESTADO");
                }
            } else {
                if (c == 0) {
                    celda.setCellValue(persona.getNombrespersona());
                } else if (c == 1) {
                    celda.setCellValue(persona.getApellidospersona());
                } else if (c == 2) {
                    celda.setCellValue(persona.getIdentificacionpersona());
                } else if (c == 3) {
                    celda.setCellValue(persona.getEmailpersona());
                } else if (c == 4) {
                    celda.setCellValue(persona.getTelefono1persona());
                } else if (c == 5) {
                    celda.setCellValue(persona.getTelefono2persona());
                } else if (c == 6) {
                    celda.setCellValue(persona.getDireccionpersona());
                } else if (c == 7) {
                    celda.setCellValue(persona.getUsuario().getNombreusuario());
                } else if (c == 8) {
                    celda.setCellValue(persona.getUsuario().getTipousuario().getNombretipousuario());
                } else if (c == 9) {
                    celda.setCellValue(persona.getUsuario().getStrEstado());
                }
            }
        }
    }
    libro.write(archivo);
    archivo.close();
    descargarArchivo(rutaArchivo);
}