Example usage for org.apache.poi.ss.usermodel Workbook 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.gtwm.pb.servlets.ReportDownloader.java

License:Open Source License

/**
 * Add a worksheet to the report for the specified workbook
 */// ww  w .  j a  v a 2  s.co m
private void addSummaryWorksheet(CompanyInfo company, SessionDataInfo sessionData, ChartInfo reportSummary,
        Workbook workbook) throws SQLException, CantDoThatException {
    ChartDataInfo reportSummaryData = this.databaseDefn.getDataManagement().getChartData(company, reportSummary,
            sessionData.getReportFilterValues(), false);
    if (reportSummaryData == null) {
        return;
    }
    int rowNum;
    Row row;
    Cell cell;
    int columnNum;
    String fieldValue;
    Sheet summarySheet;
    String summaryTitle = reportSummary.getTitle();
    if (summaryTitle == null) {
        summaryTitle = "Summary";
    } else if (summaryTitle.equals("")) {
        summaryTitle = "Summary";
    }
    // Replace any invalid characters : \ / ? * [ or ]
    // http://support.microsoft.com/kb/215205
    summaryTitle = summaryTitle.replaceAll("[\\/\\:\\\\\\?\\*\\[\\]]", "-");
    try {
        summarySheet = workbook.createSheet(summaryTitle);
    } catch (IllegalArgumentException iaex) {
        // sheet name must be unique
        summarySheet = workbook.createSheet(summaryTitle + " " + reportSummary.getId());
    }
    // header
    rowNum = 0;
    row = summarySheet.createRow(rowNum);
    columnNum = 0;
    CellStyle boldCellStyle = workbook.createCellStyle();
    Font font = workbook.createFont();
    font.setBoldweight(Font.BOLDWEIGHT_BOLD);
    boldCellStyle.setFont(font);
    Set<ChartAggregateInfo> aggregateFunctions = reportSummary.getAggregateFunctions();
    Set<ChartGroupingInfo> groupings = reportSummary.getGroupings();
    for (ChartGroupingInfo grouping : groupings) {
        BaseField groupingBaseField = grouping.getGroupingReportField().getBaseField();
        if (groupingBaseField instanceof RelationField) {
            fieldValue = groupingBaseField.getTableContainingField() + ": "
                    + ((RelationField) groupingBaseField).getDisplayField();
        } else {
            fieldValue = groupingBaseField.getFieldName();
        }
        cell = row.createCell(columnNum);
        cell.setCellValue(fieldValue);
        cell.setCellStyle(boldCellStyle);
        columnNum++;
    }
    for (ChartAggregateInfo aggregateFunction : aggregateFunctions) {
        fieldValue = aggregateFunction.toString();
        cell = row.createCell(columnNum);
        cell.setCellValue(fieldValue);
        cell.setCellStyle(boldCellStyle);
        columnNum++;
    }
    List<ChartDataRowInfo> reportSummaryDataRows = reportSummaryData.getChartDataRows();
    rowNum++;
    for (ChartDataRowInfo summaryDataRow : reportSummaryDataRows) {
        row = summarySheet.createRow(rowNum);
        columnNum = 0;
        for (ChartGroupingInfo grouping : groupings) {
            fieldValue = summaryDataRow.getGroupingValue(grouping);
            row.createCell(columnNum).setCellValue(fieldValue);
            columnNum++;
        }
        for (ChartAggregateInfo aggregateFunction : aggregateFunctions) {
            Double number = summaryDataRow.getAggregateValue(aggregateFunction).doubleValue();
            row.createCell(columnNum, Cell.CELL_TYPE_NUMERIC).setCellValue(number);
            columnNum++;
        }
        rowNum++;
    }
}

---

From source file:com.guardias.excel.CalendarToExcel.java

License:Apache License

public static void GenerateExcel(String RutaFile, Calendar calendar, String JSONContenidos,
        Medico AdministratorUser) throws IOException {

    Guardias[] lGuardias;/*from   www  . j  ava  2s.  c  o  m*/

    Gson gson = new GsonBuilder().create();

    lGuardias = gson.fromJson(JSONContenidos, Guardias[].class);

    boolean xlsx = true;
    int year = calendar.get(Calendar.YEAR);
    int month = calendar.get(Calendar.MONTH);

    DateFormat _format = new SimpleDateFormat("yyyy-MM-dd");

    Workbook wb = xlsx ? new XSSFWorkbook() : new HSSFWorkbook();

    Map<String, CellStyle> styles = createStyles(wb);

    calendar.set(Calendar.MONTH, month);
    calendar.set(Calendar.DAY_OF_MONTH, 1);

    calendar.setFirstDayOfWeek(Calendar.MONDAY);
    //create a sheet for each month
    Sheet sheet = wb.createSheet(_format.format(calendar.getTime()));

    CellStyle styleBORDER = wb.createCellStyle();
    styleBORDER.setBorderRight(CellStyle.BORDER_THICK);
    styleBORDER.setBorderBottom(CellStyle.BORDER_THICK);
    styleBORDER.setBorderTop(CellStyle.BORDER_THICK);
    styleBORDER.setBorderLeft(CellStyle.BORDER_THICK);
    styleBORDER.setRightBorderColor(IndexedColors.LIGHT_ORANGE.getIndex());
    styleBORDER.setLeftBorderColor(IndexedColors.LIGHT_ORANGE.getIndex());
    styleBORDER.setTopBorderColor(IndexedColors.LIGHT_ORANGE.getIndex());
    styleBORDER.setBottomBorderColor(IndexedColors.LIGHT_ORANGE.getIndex());
    //

    //turn off gridlines
    sheet.setDisplayGridlines(true);
    sheet.autoSizeColumn(0);
    sheet.setPrintGridlines(true);
    sheet.setFitToPage(true);
    sheet.setHorizontallyCenter(true);
    PrintSetup printSetup = sheet.getPrintSetup();
    printSetup.setLandscape(true);

    //header with month titles
    Row monthRow = sheet.createRow(1);
    Font fontH = wb.createFont();
    CellStyle CStyleH = wb.createCellStyle();
    CStyleH.setBorderRight(CellStyle.BORDER_THICK);
    CStyleH.setRightBorderColor(IndexedColors.LIGHT_ORANGE.getIndex());
    fontH.setBold(true);
    CStyleH.setFont(fontH);
    for (int i = 0; i < days.length; i++) {

        Cell monthCell = monthRow.createCell(i);

        monthCell.setCellStyle(CStyleH);
        monthCell.setCellValue(days[i]);
        sheet.autoSizeColumn(i);

    }

    int cnt = 1, day = 1;
    int rownum = 2;
    for (int j = 0; j < 6; j++) {
        Row row = sheet.createRow(rownum++);
        Row rowGuardias;
        boolean bRowsCreated = false;

        // row.setHeightInPoints(100);
        for (int i = 0; i < days.length; i++) {
            Cell dayCell_1 = row.createCell(i);
            //  Cell dayCell_2 = row.createCell(i*2 + 1);

            int currentDayOfWeek = (calendar.get(Calendar.DAY_OF_WEEK) + 7 - calendar.getFirstDayOfWeek()) % 7;
            //int day_of_week = calendar.get(Calendar.DAY_OF_WEEK);  
            if (cnt > currentDayOfWeek && calendar.get(Calendar.MONTH) == month) {

                Font font = wb.createFont();
                CellStyle CStyle = wb.createCellStyle();
                short colorI = HSSFColor.AQUA.index; // presencia
                //font.set(colorI);
                CStyle.setFillForegroundColor(colorI);
                CStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
                //CStyle.setBorderBottom( colorBorder);
                CStyle.setBorderRight(CellStyle.BORDER_THICK);
                CStyle.setBorderBottom(CellStyle.BORDER_THICK);
                CStyle.setBorderTop(CellStyle.BORDER_THICK);
                CStyle.setBorderLeft(CellStyle.BORDER_THICK);
                CStyle.setRightBorderColor(IndexedColors.LIGHT_ORANGE.getIndex());
                CStyle.setLeftBorderColor(IndexedColors.LIGHT_ORANGE.getIndex());
                CStyle.setTopBorderColor(IndexedColors.LIGHT_ORANGE.getIndex());
                CStyle.setBottomBorderColor(IndexedColors.LIGHT_ORANGE.getIndex());
                //CStyle.setFont(font);

                dayCell_1.setCellValue(day);
                dayCell_1.setCellStyle(CStyle);

                sheet.autoSizeColumn(i);

                String _Dia = _format.format(calendar.getTime());

                int DataRowCont = 1; // esto sirve para coger la fila de los datos de cada dia
                for (int d = 0; d < lGuardias.length; d++) {

                    Guardias oGuardias = lGuardias[d];
                    if (oGuardias.getDiaGuardia().equals(_Dia)) {

                        if (!bRowsCreated)
                            rowGuardias = sheet.createRow(rownum++);
                        else
                            rowGuardias = sheet.getRow(row.getRowNum() + DataRowCont);

                        Cell dayCell_1_GUARDIAS = rowGuardias.createCell(i);
                        //    Cell dayCell_2_GUARDIAS = rowGuardias.createCell(i*2 + 1);

                        List<Medico> _lMedico = MedicoDBImpl.getMedicos(oGuardias.getIdMedico(),
                                AdministratorUser.getServicioId());

                        Medico _oMedico = _lMedico.get(0);

                        font = wb.createFont();
                        CStyle = wb.createCellStyle();
                        // PRESENCIA 
                        // LOCALIZADA
                        //XSSFRichTextString richString = new HSSFRichTextString(_oMedico.getApellidos() + " " + _oMedico.getNombre());
                        colorI = HSSFColor.LIGHT_ORANGE.index; // presencia                        
                        if (oGuardias.getTipo().equals(Util.eTipoGuardia.LOCALIZADA.toString().toLowerCase()))
                            colorI = HSSFColor.GREEN.index;
                        else if (oGuardias.getTipo()
                                .equals(Util.eTipoGuardia.REFUERZO.toString().toLowerCase()))
                            colorI = HSSFColor.BLUE.index;
                        else if (oGuardias.getTipo().equals("")) // residente
                            colorI = HSSFColor.RED.index;

                        font.setColor(colorI);

                        CStyle.setFont(font);
                        //CStyle.setBorderBottom( colorBorder);
                        CStyle.setBorderRight(CellStyle.BORDER_THICK);
                        CStyle.setRightBorderColor(IndexedColors.LIGHT_ORANGE.getIndex());

                        dayCell_1_GUARDIAS.setCellValue(_oMedico.getApellidos() + " " + _oMedico.getNombre()
                                + "[" + _oMedico.getIDMEDICO() + "]");
                        dayCell_1_GUARDIAS.setCellStyle(CStyle);

                        DataRowCont++;

                    }

                }
                bRowsCreated = true;

                //      dayCell_1_GUARDIAS.setCellValue(TextoGuardias.toString());

                calendar.set(Calendar.DAY_OF_MONTH, ++day);

                /*if(i == 0 || i == days.length-1) {
                    dayCell_1.setCellStyle(styles.get("weekend_left"));
                    dayCell_2.setCellStyle(styles.get("weekend_right"));
                } else {
                    dayCell_1.setCellStyle(styles.get("workday_left"));
                    dayCell_2.setCellStyle(styles.get("workday_right"));
                }
                } else {
                dayCell_1.setCellStyle(styles.get("grey_left"));
                dayCell_2.setCellStyle(styles.get("grey_right"));*/
            }
            cnt++;
        }
        if (calendar.get(Calendar.MONTH) > month)
            break;
    }
    //  }

    // Write the output to a file
    String file = RutaFile;
    FileOutputStream out = new FileOutputStream(file);
    wb.write(out);
    out.close();

    wb.close();

}

---

From source file:com.helger.genetic.tsp.result.evaluation.MainTSPRunnerBerlin52BestSeveral.java

License:Apache License

public static void main(final String[] args) throws IOException {
    GlobalDebug.setDebugModeDirect(true);
    final Matrix aDistances = readTSPFromFile(new ClassPathResource("tsp/berlin52.tsp"), true);

    final int nOptimumDistance = 7542;
    final int nCities = aDistances.getRowDimension();
    final TSPFitnessFunction ff = new TSPFitnessFunction(aDistances);
    final TSPChromosomeValidator cv = true ? null : new TSPChromosomeValidator(nCities);

    // Use fixed seed only once
    RandomGenerator.setRandomGenerator(new RandomGeneratorRandom(new Random(789234789989L)));

    final int nRepeats = 5;
    final List<TIntList> aDistanceListPerPopulations = new ArrayList<TIntList>(nRepeats);
    final List<TIntList> aDistanceListBest = new ArrayList<TIntList>(nRepeats);
    for (int i = 0; i < nRepeats; ++i) {
        final int nPopulationSize = nCities;
        final TSPEventHandlerGenerationTracker eh = new TSPEventHandlerGenerationTracker(ff);
        IContinuation cont = null;//from   w  w w. j a v a2 s . c  o  m
        cont = new ContinuationTotalGeneration(1000, cont);
        cont = new ContinuationKnownOptimum(ff.getFitness(nOptimumDistance), eh, cont);
        if (false)
            cont = new ContinuationTimeBased(20 * CGlobal.MILLISECONDS_PER_SECOND, cont);
        final IPopulationCreator pc = new TSPPopulationCreatorRandom(nCities, nPopulationSize, ff, cv);
        final ISelector s = new SelectorAllSortedBest(2);
        final ICrossover c = new CrossoverEdgeRecombination(new DecisionMakerPercentage(2));
        final IMutation m = new TSPMutationGreedyBeginning(aDistances, eh,
                new MutationRandomPartialReverse(new DecisionMakerPercentage(80)));

        new TSPRunner("berlin52").run(aDistances, nOptimumDistance, ff, eh, cont, pc, s, c, m);
        aDistanceListPerPopulations.add(eh.getDistanceListPerPopulation());
        aDistanceListBest.add(eh.getDistanceListBest());
    }

    final boolean bWithBest = false;
    final Workbook aWB = EExcelVersion.XLSX.createWorkbook();
    final Sheet aSheet = aWB.createSheet("STW CT");
    int nColumn = 0;
    Row aRow = aSheet.createRow(0);
    aRow.createCell(nColumn++).setCellValue("Generation");
    aRow.createCell(nColumn++).setCellValue("100%");
    aRow.createCell(nColumn++).setCellValue("110%");
    aRow.createCell(nColumn++).setCellValue("125%");
    for (int i = 0; i < nRepeats; ++i) {
        aRow.createCell(nColumn++).setCellValue("Run " + (i + 1));
        if (bWithBest)
            aRow.createCell(nColumn++).setCellValue("Best " + (i + 1));
    }

    // Ensure all rows are present
    int nMaxRows = 0;
    for (final TIntList aIL : aDistanceListPerPopulations)
        nMaxRows = Math.max(nMaxRows, aIL.size());

    // Create rows and set generation and optimum
    final double dOptimumDistance110 = nOptimumDistance * 1.1;
    final double dOptimumDistance125 = nOptimumDistance * 1.25;
    for (int i = 0; i < nMaxRows; ++i) {
        aRow = aSheet.createRow(1 + i);
        aRow.createCell(0).setCellValue(i + 1);
        aRow.createCell(1).setCellValue(nOptimumDistance);
        aRow.createCell(2).setCellValue(dOptimumDistance110);
        aRow.createCell(3).setCellValue(dOptimumDistance125);
    }

    // Set all values
    nColumn = 4;
    for (int i = 0; i < nRepeats; ++i) {
        int nRow = 1;
        final TIntIterator itPerPop = aDistanceListPerPopulations.get(i).iterator();
        while (itPerPop.hasNext())
            aSheet.getRow(nRow++).createCell(nColumn).setCellValue(itPerPop.next());
        ++nColumn;

        if (bWithBest) {
            nRow = 1;
            final TIntIterator itBest = aDistanceListBest.get(i).iterator();
            while (itBest.hasNext())
                aSheet.getRow(nRow++).createCell(nColumn).setCellValue(itBest.next());
            ++nColumn;
        }
    }
    aWB.write(FileHelper.getOutputStream(new File("data/berlin52/results-best-multiple.xlsx")));
}

---

From source file:com.hp.amss.util.HyperlinkExample.java

License:Apache License

public static void main(String[] args) throws Exception {
    Workbook wb = new XSSFWorkbook(); //or new HSSFWorkbook();
    CreationHelper createHelper = wb.getCreationHelper();

    //cell style for hyperlinks
    //by default hyperlinks are blue and underlined
    CellStyle hlink_style = wb.createCellStyle();
    Font hlink_font = wb.createFont();
    hlink_font.setUnderline(Font.U_SINGLE);
    hlink_font.setColor(IndexedColors.BLUE.getIndex());
    hlink_style.setFont(hlink_font);//w ww . j a v a2  s  . c  o  m

    Cell cell;
    Sheet sheet = wb.createSheet("Hyperlinks");
    //URL
    cell = sheet.createRow(0).createCell((short) 0);
    cell.setCellValue("URL Link");

    Hyperlink link = createHelper.createHyperlink(Hyperlink.LINK_URL);
    link.setAddress("http://poi.apache.org/");
    cell.setHyperlink(link);
    cell.setCellStyle(hlink_style);

    //link to a file in the current directory
    cell = sheet.createRow(1).createCell((short) 0);
    cell.setCellValue("File Link");
    link = createHelper.createHyperlink(Hyperlink.LINK_FILE);
    link.setAddress("link1.xls");
    cell.setHyperlink(link);
    cell.setCellStyle(hlink_style);

    //e-mail link
    cell = sheet.createRow(2).createCell((short) 0);
    cell.setCellValue("Email Link");
    link = createHelper.createHyperlink(Hyperlink.LINK_EMAIL);
    //note, if subject contains white spaces, make sure they are url-encoded
    link.setAddress("mailto:poi@apache.org?subject=Hyperlinks");
    cell.setHyperlink(link);
    cell.setCellStyle(hlink_style);
    //TODO
    cell.setCellValue(createHelper.createRichTextString(""));
    cell.setCellType(Cell.CELL_TYPE_STRING);

    //link to a place in this workbook

    //create a target sheet and cell
    Sheet sheet2 = wb.createSheet("Target Sheet");
    sheet2.createRow(0).createCell((short) 0).setCellValue("Target Cell");

    cell = sheet.createRow(3).createCell((short) 0);
    cell.setCellValue("Worksheet Link");
    Hyperlink link2 = createHelper.createHyperlink(Hyperlink.LINK_DOCUMENT);
    link2.setAddress("'Target Sheet'!A1");
    cell.setHyperlink(link2);
    cell.setCellStyle(hlink_style);

    FileOutputStream out = new FileOutputStream("C:\\hyperinks.xlsx");
    wb.write(out);
    out.close();

}

---

From source file:com.hp.idc.common.upload.JSONToExcelServlet.java

License:Open Source License

public void doGet(HttpServletRequest request, HttpServletResponse response)
        throws IOException, ServletException {
    response.setContentType("application/xml;charset=gbk");
    response.setCharacterEncoding("gbk");
    request.setCharacterEncoding("gbk");

    String JSONStr = request.getParameter("JSONStr");

    JSONObject jsonObj = null;/*  w ww .  j a  va  2  s . c o  m*/
    try {
        jsonObj = new JSONObject(JSONStr);

        String fileName = null;

        JSONArray headja = null;

        JSONArray valja = null;

        JSONArray sheetja = null;

        fileName = jsonObj.getString("fileName");
        if ((fileName == null) || (fileName.equals("")))
            fileName = "data.xls";
        //fileName = URLEncoder.encode(fileName, "utf-8");

        fileName = StringUtil.getEncodeStr(
                fileName + DateTimeUtil.formatDate(new Date(), "yyyy-MM-dd") + ".xls", "GB2312", "ISO8859-1");

        response.reset();
        response.setContentType("application/x-msdownload");
        response.setHeader("Content-Disposition", "attachment; filename=" + fileName);

        Workbook wb = new HSSFWorkbook();

        if (jsonObj.has("sheet"))
            sheetja = jsonObj.getJSONArray("sheet");
        else {
            sheetja = new JSONArray("[{name:'sheet',_sys_create:true}]");
        }
        for (int sheetIndex = 0; sheetIndex < sheetja.length(); sheetIndex++) {
            JSONObject sheetjo = sheetja.getJSONObject(sheetIndex);
            String sheetName = "sheet" + sheetIndex;
            if (sheetjo.has("sheetName"))
                sheetName = sheetjo.getString("sheetName");

            Sheet sheet = wb.createSheet(sheetName);
            if ((sheetjo.has("_sys_create")) && (sheetjo.getBoolean("_sys_create"))) {
                headja = jsonObj.getJSONArray("head");
                valja = jsonObj.getJSONArray("value");
            } else {
                headja = sheetjo.getJSONArray("head");
                valja = sheetjo.getJSONArray("value");
            }
            try {
                JSONArray names = new JSONArray();
                Row row = sheet.createRow(0);
                if (headja != null) {
                    for (int i = 0; i < headja.length(); i++) {
                        JSONObject jo = headja.getJSONObject(i);
                        if (jo != null)
                            names.put(jo.getString("id"));
                        writeHead(jo, row, i);
                    }
                }
                if (valja != null)
                    for (int i = 0; i < valja.length(); i++) {
                        JSONObject jo = valja.getJSONObject(i);
                        writeValue(names, jo, sheet, i + 1);
                    }
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        OutputStream out = response.getOutputStream();
        out.flush();
        wb.write(out);
        out.close();
    } catch (JSONException e2) {
        e2.printStackTrace();
    }
}

---

From source file:com.hp.idc.resm.util.ExcelUtil.java

License:Open Source License

/**
 * Excel/*from www .  jav a2  s . com*/
 * 
 * @param id
 *            ID
 * @return Excel
 */
public String getModelExcel(String id) {
    List<ModelAttribute> list = ServiceManager.getModelService().getModelAttributesByModelId(id);

    // 
    String[] IGNORATTR = new String[] { "id", "create_time", "contract_start", "searchcode", "last_update_time",
            "contract_end", "task_link", "order_id", "customer_id", "status", "last_update_by" };
    List<String> l = new ArrayList<String>(Arrays.asList(IGNORATTR));

    Workbook wb = new HSSFWorkbook();
    // Workbook wb = new XSSFWorkbook();
    Sheet sheet = wb.createSheet(id);
    CellStyle style = wb.createCellStyle();
    Font font = wb.createFont();
    font.setColor(HSSFColor.RED.index);
    style.setFont(font);
    // Create a row and put some cells in it. Rows are 0 based.
    Row row = sheet.createRow((short) 0);
    int i = 0;
    Cell cell = null;
    HSSFRichTextString textString;
    for (ModelAttribute ma : list) {
        if (l.contains(ma.getAttrId()))
            continue;
        cell = row.createCell(i);
        textString = new HSSFRichTextString(ma.getName() + "/" + ma.getAttrId());
        cell.setCellValue(textString);
        if (!ma.isNullable())
            cell.setCellStyle(style);
        sheet.autoSizeColumn(i);
        i++;
    }
    for (int k = 0; k < list.size(); k++) {
        sheet.autoSizeColumn(k);
    }
    // Write the output to a file
    FileOutputStream fileOut;
    String file;
    try {
        file = System.getProperty("user.dir") + "/../temp/" + id + new Random().nextLong() + ".xls";
        fileOut = new FileOutputStream(file);
        wb.write(fileOut);
        fileOut.close();
    } catch (FileNotFoundException e) {
        e.printStackTrace();
        return "";
    } catch (IOException e) {
        e.printStackTrace();
        return "";
    }
    return file;
}

---

From source file:com.hp.idc.resm.util.ExcelUtil.java

License:Open Source License

/**
 * , Excel/*w  ww .j ava 2 s. c  o  m*/
 * 
 * @param modelId
 *            Id
 * @return Excel
 */
public String getResouceDataToExcel(String modelId) {
    Model m = ServiceManager.getModelService().getModelById(modelId);

    List<ResourceObject> l = ServiceManager.getResourceService().getResourcesByModelId(modelId, 1);

    List<ModelAttribute> mas = m.getAttributes();
    Workbook wb = new HSSFWorkbook();
    CellStyle style = wb.createCellStyle();
    Font font = wb.createFont();
    font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
    font.setFontHeightInPoints((short) 12);
    font.setFontName("");
    style.setFont(font);
    Sheet sheet = wb.createSheet(modelId);
    Row row = sheet.createRow(0);
    int i = 0;
    HSSFRichTextString textString;
    for (ModelAttribute ma : mas) {
        Cell cell = row.createCell(i);
        textString = new HSSFRichTextString(ma.getDefine().getName());
        cell.setCellStyle(style);
        cell.setCellValue(textString);
        i++;
    }
    i = 1;
    for (ResourceObject ro : l) {
        row = sheet.createRow(i);
        int j = 0;
        for (ModelAttribute ma : mas) {
            textString = new HSSFRichTextString(ro.getAttributeValue(ma.getAttrId()));
            row.createCell(j).setCellValue(textString);
            j++;
        }
        i++;
    }

    for (int k = 0; k < mas.size(); k++) {
        sheet.autoSizeColumn(k);
    }

    // Write the output to a file
    FileOutputStream fileOut;
    String file;
    try {
        file = System.getProperty("user.dir") + "/../temp/" + modelId + new Random().nextLong() + "_data.xls";
        fileOut = new FileOutputStream(file);
        wb.write(fileOut);
        fileOut.close();
    } catch (FileNotFoundException e) {
        e.printStackTrace();
        return "";
    } catch (IOException e) {
        e.printStackTrace();
        return "";
    }
    return file;
}

---

From source file:com.ideaspymes.proyecttemplate.stock.web.ProductoConsultaBean.java

@Override
public Workbook getWorkBook() {
    Workbook wb = new XSSFWorkbook();
    Sheet sheet = wb.createSheet("My Sample Excel");
    List<CatalogoProductos> lista = (List<CatalogoProductos>) getDetalles();

    sheet.setDefaultRowHeight((short) (sheet.getDefaultRowHeight() * new Short("6")));

    org.apache.poi.ss.usermodel.Font fontTitulo = wb.createFont();
    fontTitulo.setFontHeightInPoints((short) 12);
    fontTitulo.setBoldweight(org.apache.poi.ss.usermodel.Font.BOLDWEIGHT_BOLD);

    org.apache.poi.ss.usermodel.Font fontTituloPricipal = wb.createFont();
    fontTituloPricipal.setFontHeightInPoints((short) 22);
    fontTituloPricipal.setBoldweight(org.apache.poi.ss.usermodel.Font.BOLDWEIGHT_BOLD);

    DataFormat format = wb.createDataFormat();

    CellStyle styleTituloPrincipal = wb.createCellStyle();
    styleTituloPrincipal.setFont(fontTituloPricipal);
    styleTituloPrincipal.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
    styleTituloPrincipal.setAlignment(CellStyle.ALIGN_CENTER);

    CellStyle styleTitulo = wb.createCellStyle();
    styleTitulo.setFont(fontTitulo);//  www  .  ja  v a2 s  .  com
    styleTitulo.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
    styleTitulo.setFillForegroundColor(IndexedColors.BLUE_GREY.getIndex());
    styleTitulo.setFillPattern(CellStyle.SOLID_FOREGROUND);
    styleTitulo.setWrapText(true);

    CellStyle styleNumero = wb.createCellStyle();
    styleNumero.setDataFormat(format.getFormat("#,##0"));
    styleNumero.setWrapText(true);
    styleNumero.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
    styleNumero.setAlignment(CellStyle.ALIGN_CENTER);

    CellStyle styleFecha = wb.createCellStyle();
    styleFecha.setDataFormat(format.getFormat("dd/MM/yyyy"));
    styleFecha.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
    styleFecha.setAlignment(CellStyle.ALIGN_CENTER);

    CellStyle style = wb.createCellStyle();
    style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
    style.setWrapText(true);

    CellStyle styleCenter = wb.createCellStyle();
    styleCenter.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
    styleCenter.setAlignment(CellStyle.ALIGN_CENTER);
    styleCenter.setWrapText(true);

    Row rowTitle = sheet.createRow(0);
    Cell cellTitle = rowTitle.createCell(1);
    cellTitle.setCellStyle(styleTituloPrincipal);

    sheet.addMergedRegion(new CellRangeAddress(0, //first row (0-based)
            1, //last row  (0-based)
            1, //first column (0-based)
            11 //last column  (0-based)
    ));

    cellTitle.setCellValue("Listado de Activos");

    int i = 2;

    Row row0 = sheet.createRow(i);
    row0.setHeight((short) 500);

    Cell cell1 = row0.createCell(1);
    cell1.setCellValue("Foto");
    cell1.setCellStyle(styleTitulo);

    Cell cellFecha = row0.createCell(3);
    cellFecha.setCellValue("Fecha Ingreso");
    cellFecha.setCellStyle(styleTitulo);

    Cell cellFechaCarga = row0.createCell(4);
    cellFechaCarga.setCellValue("Fecha Carga");
    cellFechaCarga.setCellStyle(styleTitulo);

    Cell cell3 = row0.createCell(5);
    cell3.setCellValue("Nombre");
    cell3.setCellStyle(styleTitulo);

    Cell cell4 = row0.createCell(6);
    cell4.setCellValue("Cdigo");
    cell4.setCellStyle(styleTitulo);

    Cell cell5 = row0.createCell(7);
    cell5.setCellValue("Descripcin");
    cell5.setCellStyle(styleTitulo);

    Cell cell6 = row0.createCell(8);
    cell6.setCellValue("Es Regalo?");
    cell6.setCellStyle(styleTitulo);

    Cell cell7 = row0.createCell(9);
    cell7.setCellValue("Familia");
    cell7.setCellStyle(styleTitulo);

    Cell cell8 = row0.createCell(10);
    cell8.setCellValue("Ubicaciones");
    cell8.setCellStyle(styleTitulo);

    Cell cell9 = row0.createCell(11);
    cell9.setCellValue("Stock");
    cell9.setCellStyle(styleTitulo);

    for (CatalogoProductos cp : lista) {

        int indexFila = i + 1;
        if (cp.getImagen() != null) {
            int pictureIdx = wb.addPicture(cp.getImagen(), Workbook.PICTURE_TYPE_PNG);
            CreationHelper helper = wb.getCreationHelper();

            //Creates the top-level drawing patriarch.
            Drawing drawing = sheet.createDrawingPatriarch();

            //Create an anchor that is attached to the worksheet
            ClientAnchor anchor = helper.createClientAnchor();
            //set top-left corner for the image
            anchor.setCol1(1);
            anchor.setRow1(indexFila);

            //Creates a picture
            Picture pict = drawing.createPicture(anchor, pictureIdx);
            //Reset the image to the original size
            pict.resize(0.4);
        }
        Row row1 = sheet.createRow(indexFila);
        row1.setHeightInPoints(80f);

        Cell cellColFecha = row1.createCell(3);

        if (cp.getFecha() != null) {
            cellColFecha.setCellValue(cp.getFecha());
            cellColFecha.setCellStyle(styleFecha);

        } else {
            cellColFecha.setCellValue("");
            cellColFecha.setCellStyle(styleFecha);
        }

        Cell cellColFechaCarga = row1.createCell(4);

        if (cp.getFechaCarga() != null) {
            cellColFechaCarga.setCellValue(cp.getFechaCarga());
            cellColFechaCarga.setCellStyle(styleFecha);

        } else {
            cellColFechaCarga.setCellValue("");
            cellColFechaCarga.setCellStyle(styleFecha);
        }

        Cell cellCol1 = row1.createCell(5);
        cellCol1.setCellValue(cp.getProducto());
        cellCol1.setCellStyle(style);

        Cell cellCol2 = row1.createCell(6);
        cellCol2.setCellValue(cp.getCodigo());
        cellCol2.setCellStyle(styleNumero);

        Cell cellCol3 = row1.createCell(7);
        cellCol3.setCellValue(cp.getDescripcion());
        cellCol3.setCellStyle(style);

        Cell cellCol4 = row1.createCell(8);
        cellCol4.setCellValue(cp.isEsRegalo() ? "SI" : "NO");
        cellCol4.setCellStyle(styleCenter);

        Cell cellCol5 = row1.createCell(9);
        cellCol5.setCellValue(cp.getFamilia());
        cellCol5.setCellStyle(style);

        Cell cellCol6 = row1.createCell(10);
        cellCol6.setCellValue(cp.getUbicaciones());
        cellCol6.setCellStyle(style);

        Cell cellCol7 = row1.createCell(11);
        cellCol7.setCellValue(cp.getStock());
        cellCol7.setCellStyle(styleNumero);

        i++;

    }

    sheet.setColumnWidth(1, 4000);
    sheet.setColumnWidth(2, 0);
    sheet.setColumnWidth(3, 4000);
    sheet.setColumnWidth(4, 4000);
    sheet.setColumnWidth(5, 10000);
    sheet.setColumnWidth(6, 3000);
    sheet.setColumnWidth(7, 10000);
    sheet.setColumnWidth(8, 3500);
    sheet.setColumnWidth(9, 6000);
    sheet.setColumnWidth(10, 10000);
    sheet.setColumnWidth(11, 2000);

    return wb;
}

---

From source file:com.inet.web.service.mail.utils.ExportUtils.java

License:Open Source License

/**
 * /*  w  w w  .  j  a v a2s.  c  o m*/
 * @param users
 * @param domain
 * @return
 * @throws WebOSException
 */
public static byte[] exportAccount(List<LdapUser> users, String domain) throws WebOSException {
    try {
        ByteArrayOutputStream output = new ByteArrayOutputStream();

        Workbook workbook = new HSSFWorkbook();
        //Map<String, CellStyle> styles = createStyles(workbook);

        Sheet sheet = workbook.createSheet("Email list");
        PrintSetup printSetup = sheet.getPrintSetup();
        printSetup.setLandscape(true);
        sheet.setFitToPage(true);
        sheet.setHorizontallyCenter(true);

        for (int i = 0; i < users.size(); i++) {
            writeRecordAccount(sheet, users.get(i), i + 1);
        }
        workbook.write(output);
        output.close();

        return output.toByteArray();
    } catch (Exception e) {
        e.printStackTrace();
        throw new WebOSException(e.getMessage(), e);
    }
}

---

From source file:com.inet.web.service.mail.utils.ExportUtils.java

License:Open Source License

/**
 * /*from  w w  w.  j  av  a 2 s. c  o m*/
 * @param key
 * @return
 * @throws WebOSException
 */
public static byte[] exportErrorAccount(String key) throws WebOSException {
    AccountImport accountImport = AccountImportCacheService.get(key);
    if (accountImport == null) {
        return null;
    }
    try {
        ByteArrayOutputStream output = new ByteArrayOutputStream();
        Workbook workbook = new HSSFWorkbook();

        Sheet sheet = workbook.createSheet("Email list");
        PrintSetup printSetup = sheet.getPrintSetup();
        printSetup.setLandscape(true);
        sheet.setFitToPage(true);
        sheet.setHorizontallyCenter(true);

        Row headerRow = sheet.createRow(0);
        headerRow.setHeightInPoints(30);
        headerRow.createCell(STT).setCellValue("STT");
        headerRow.createCell(FULL_NAME).setCellValue("FULL NAME");
        headerRow.createCell(USER).setCellValue("USER");
        headerRow.createCell(LAST_NAME).setCellValue("LAST NAME");
        headerRow.createCell(MIDDLE_NAME).setCellValue("MIDDLE NAME");
        headerRow.createCell(FIRST_NAME).setCellValue("FIRST NAME");
        headerRow.createCell(STATUS).setCellValue("STATUS");
        headerRow.createCell(DUPLICATE).setCellValue("DUPLICATE");

        for (int i = 0; i < accountImport.getError().size(); i++) {
            AccountImportInfo account = accountImport.getError().get(i);

            Row row = sheet.createRow(i + 1);
            row.setHeightInPoints(40);
            row.createCell(STT).setCellValue(account.getNumber());
            row.createCell(FULL_NAME).setCellValue(account.getFullName());
            row.createCell(USER).setCellValue(account.getAccount());
            row.createCell(LAST_NAME).setCellValue(account.getLastName());
            row.createCell(MIDDLE_NAME).setCellValue(account.getMiddleName());
            row.createCell(FIRST_NAME).setCellValue(account.getFirstName());
            row.createCell(STATUS).setCellValue(getStatus(account.getStatus()));
            row.createCell(DUPLICATE).setCellValue(account.getExistAccount());
        }

        workbook.write(output);
        output.close();

        return output.toByteArray();
    } catch (Exception e) {
        e.printStackTrace();
        throw new WebOSException(e.getMessage(), e);
    }
}

---

• «
• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9
• 10
• 11
• 12
• 13
• 14
• 15
• 16
• 17
• 18
• 19
• 20
• 21
• 22
• 23
• 24
• 25
• 26
• 27
• 28
• 29
• 30
• 31
• 32
• 33
• 34
• 35
• 36
• 37
• 38
• 39
• 40
• 41
• 42
• »