Example usage for org.apache.poi.xssf.usermodel XSSFWorkbook XSSFWorkbook

List of usage examples for org.apache.poi.xssf.usermodel XSSFWorkbook XSSFWorkbook

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.xssf.usermodel.*
  5. XSSFWorkbook
  6. XSSFWorkbook

Introduction

In this page you can find the example usage for org.apache.poi.xssf.usermodel XSSFWorkbook XSSFWorkbook.

Prototype

public XSSFWorkbook()

Source Link

Document

Create a new SpreadsheetML workbook.

Usage

From source file:com.alvexcore.repo.documents.generation.ExportReportToXlsx.java

License:Open Source License

protected Workbook createXlsx(JSONArray rows, String XLS_SHEET_NAME) {

    Workbook wb = new XSSFWorkbook();
    CreationHelper createHelper = wb.getCreationHelper();
    Sheet sheet = wb.createSheet(XLS_SHEET_NAME);

    for (int k = 0; k < rows.size(); k++) {
        Row row = sheet.createRow((short) k);
        JSONArray cells = (JSONArray) rows.get(k);
        row.createCell(0).setCellValue(createHelper.createRichTextString((String) cells.get(0)));
        for (int c = 1; c < cells.size(); c++)
            row.createCell(c).setCellValue((String) cells.get(c));
    }/*from w  w  w  .j ava 2  s. com*/

    return wb;
}

From source file:com.anphat.customer.controller.CommonUploader.java

public void createFileError(String err, List<Integer> lstRows) {
    if (DataUtil.isListNullOrEmpty(lstRows)) {
        return;//from   ww  w.  ja  va 2s.  c  om
    }
    cellStyle = null;
    if (isCreateSheet) {
        try {
            // sheet
            fileInput = new FileInputStream(tempFile);
            fileOut = new FileOutputStream(fileErrorName);
        } catch (FileNotFoundException ex) {
            ex.printStackTrace();
        }
        // sheet loi
        workbookEp = new XSSFWorkbook();
        worksheetEp = workbookEp.createSheet("error");
        cellStyle = null;
        hSSFWorkbook = null;
        worksheetIp = null;
        row5 = null;
        cellB1 = null;
        isCreateSheet = false;
        //
        workbookIp = null;
        if (mimeType.equalsIgnoreCase(Constants.FORMATFILE.EXCEL_XLSX)) {
            try {
                // xlsx
                workbookIp = new XSSFWorkbook(fileInput);
            } catch (IOException ex) {
                ex.printStackTrace();
            }
        } else if (mimeType.equalsIgnoreCase(Constants.FORMATFILE.EXCEL_XLS)) { // xls
            HSSFWorkbook hSSFWorkbook = null;
            try {
                hSSFWorkbook = new HSSFWorkbook(fileInput);
            } catch (IOException ex) {
                ex.printStackTrace();
            }
            workbookIp = ExcelReaderXLSX.convertWorkbookHSSFToXSSF(hSSFWorkbook);
        }
        XSSFSheet worksheetNew = workbookIp.getSheetAt(0);
        ExcelReaderXLSX.copySheets(worksheetEp, worksheetNew, 8);
        // style
        cellStyle = CommonUtils.styleCell(workbookEp);
    }
    for (Integer row : lstRows) {
        row5 = worksheetEp.getRow(row + 1);
        if (row5 != null) {
            isError = true;
            cellB1 = row5.createCell(9);
            cellB1.setCellValue(err);
            cellB1.setCellStyle(cellStyle);
        }
    }
}

From source file:com.anphat.customer.controller.CommonUploader.java

public void createFileError(String err, int i) {
    cellStyle = null;//  ww w  . j a  v a  2s .com
    if (isCreateSheet) {
        try {
            // sheet
            flieInput = new FileInputStream(tempFile);
            fileOut = new FileOutputStream(fileErrorName);
        } catch (FileNotFoundException ex) {
            ex.printStackTrace();
        }
        workbookIp = null;
        // sheet loi
        workbookEp = new XSSFWorkbook();
        worksheetEp = workbookEp.createSheet("error");
        cellStyle = null;

        hSSFWorkbook = null;
        worksheetIp = null;
        row5 = null;
        cellB1 = null;
        isCreateSheet = false;
        //
        workbookIp = null;
        if (mimeType.equalsIgnoreCase(Constants.FORMATFILE.EXCEL_XLSX)) {
            try {
                // xlsx
                workbookIp = new XSSFWorkbook(flieInput);
            } catch (IOException ex) {
            }
        } else if (mimeType.equalsIgnoreCase(Constants.FORMATFILE.EXCEL_XLS)) { // xls
            HSSFWorkbook hSSFWorkbook = null;
            try {
                hSSFWorkbook = new HSSFWorkbook(flieInput);
            } catch (IOException ex) {
            }
            workbookIp = ExcelReaderXLSX.convertWorkbookHSSFToXSSF(hSSFWorkbook);
        }
        XSSFSheet worksheetIp = workbookIp.getSheetAt(0);
        ExcelReaderXLSX.copySheets(worksheetEp, worksheetIp, 8);
        // style
        cellStyle = CommonUtils.styleCell(workbookEp);
    }
    row5 = worksheetEp.getRow(i + 1);
    if (row5 != null) {
        cellB1 = row5.createCell(9);
        cellB1.setCellValue(err);
        cellB1.setCellStyle(cellStyle);
    }
}

From source file:com.asakusafw.testdata.generator.excel.WorkbookGenerator.java

License:Apache License

/**
 * Creates a new empty workbook for the target version.
 * @param version the target version/*from w  ww.j av  a2 s.  c  o m*/
 * @return the created workbook
 * @throws IOException if failed to create the workbook
 * @since 0.5.3
 */
public static Workbook createEmptyWorkbook(SpreadsheetVersion version) throws IOException {
    if (version == null) {
        throw new IllegalArgumentException("version must not be null"); //$NON-NLS-1$
    }
    switch (version) {
    case EXCEL97:
        return new HSSFWorkbook();
    case EXCEL2007:
        return new XSSFWorkbook();
    default:
        throw new IOException(
                MessageFormat.format(Messages.getString("WorkbookGenerator.errorUnsupportedWorkbookVersion"), //$NON-NLS-1$
                        version));
    }
}

From source file:com.asakusafw.testdriver.excel.Util.java

License:Apache License

static Workbook createEmptyWorkbookFor(String path) {
    if (isHssf(path)) {
        return new HSSFWorkbook();
    } else if (isXssf(path)) {
        return new XSSFWorkbook();
    } else {//from  w  ww .j a va 2s .  c  om
        return new HSSFWorkbook();
    }
}

From source file:com.automaster.autoview.server.servlet.ExcelServlet.java

protected void doGet(HttpServletRequest request, HttpServletResponse response)
        throws ServletException, IOException {
    /*response.setContentType("application/vnd.ms-excel");
     response.setHeader("Content-Disposition", "attachment; filename=filename.xls");
     HSSFWorkbook workbook = new HSSFWorkbook();
     // .../*from w w w.j  a va2 s  .  c o  m*/
     // Now populate workbook the usual way.
     // ...
     OutputStream arqSaida = response.getOutputStream();
     workbook.write(arqSaida); // Write workbook to response.
     arqSaida.flush();
     arqSaida.close();*/
    //getServletContext().getRealPath("/")
    String tempoDecorrido = " 0";
    String url = getServletContext().getRealPath("/");
    String placa = request.getParameter("placa");
    //TimeZone timeZoneMundial = TimeZone.getTimeZone(ZoneId.ofOffset("UTC", ZoneOffset.UTC));
    Timestamp dataInicio = new Timestamp(Long.parseLong(request.getParameter("dataInicio")));
    Timestamp dataFim = new Timestamp(Long.parseLong(request.getParameter("dataFim")));
    //String timeZoneInterface = request.getParameter("timeZone");
    /*String timeZone = "Z";
    if(timeZoneInterface.equalsIgnoreCase("0")){
    timeZone = "Z";
    } else {
    timeZone = String.valueOf((-1) * (Integer.parseInt(timeZoneInterface) / 60));
    }  */
    TimeZone timeZonePadrao = TimeZone.getTimeZone(ZoneId.of("-3"));
    System.out.println("Time zone Cliente : " + timeZonePadrao);
    //System.out.println("timeZoneInterface: "+timeZoneInterface);
    //System.out.println("timeZone: "+timeZone);
    //String ign = request.getParameter("ign");
    ZzzPosPlacaVeiculoDAO zzzPosPlacaVeiculoDAO = new ZzzPosPlacaVeiculoDAO();
    ArrayList<TreeMap<String, String>> posicoes = zzzPosPlacaVeiculoDAO.buscarPosicoesPorIntervaloData(placa,
            dataInicio, dataFim);

    response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
    response.setHeader("Content-Disposition", "attachment; filename=Historico-" + placa + ".xlsx");

    Workbook wb = new XSSFWorkbook();
    Sheet sheet = wb.createSheet("Histrico - " + placa);
    int pictureIdx;
    try ( //add picture data to this workbook.
            InputStream is = new FileInputStream(url + "/imagens/logo.jpg")) {
        byte[] bytes = IOUtils.toByteArray(is);
        pictureIdx = wb.addPicture(bytes, Workbook.PICTURE_TYPE_JPEG);
    }

    CreationHelper helper = wb.getCreationHelper();

    // Create the drawing patriarch.  This is the top level container for all shapes. 
    Drawing drawing = sheet.createDrawingPatriarch();

    //add a picture shape
    ClientAnchor anchor = helper.createClientAnchor();
    //set top-left corner of the picture,
    //subsequent call of Picture#resize() will operate relative to it
    anchor.setCol1(1);
    anchor.setRow1(0);
    Picture pict = drawing.createPicture(anchor, pictureIdx);
    //auto-size picture relative to its top-left corner
    pict.resize(3, 3);
    //pict.resize();
    //sheet.setColumnWidth(0, 200);

    Font fonte = wb.createFont();
    fonte.setFontHeightInPoints((short) 24);
    fonte.setFontName("Arial");
    fonte.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
    fonte.setItalic(true);
    CellStyle estiloTitulo = wb.createCellStyle();
    estiloTitulo.setAlignment(HSSFCellStyle.ALIGN_CENTER);
    estiloTitulo.setFont(fonte);

    Font fonteCabecalho = wb.createFont();
    fonteCabecalho.setFontHeightInPoints((short) 14);
    fonteCabecalho.setFontName("Arial");
    fonteCabecalho.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
    //fonteCabecalho.setItalic(true);
    CellStyle estiloCabecalho = wb.createCellStyle();
    estiloCabecalho.setAlignment(HSSFCellStyle.ALIGN_CENTER);
    estiloCabecalho.setFont(fonteCabecalho);

    Font fonteTituloTabela = wb.createFont();
    //fonteTituloTabela.setFontHeightInPoints((short) 14);
    fonteTituloTabela.setFontName("Arial");
    fonteTituloTabela.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);

    CellStyle estilo = wb.createCellStyle();
    estilo.setAlignment(HSSFCellStyle.ALIGN_CENTER);
    estilo.setFont(fonteTituloTabela);

    CellStyle estiloCorpo = wb.createCellStyle();
    estiloCorpo.setAlignment(HSSFCellStyle.ALIGN_CENTER);
    //estiloCorpo.setFillBackgroundColor(HSSFColor.WHITE.index);
    //estiloCorpo.setFont(fonteTituloTabela);

    XSSFRow linha6 = (XSSFRow) sheet.createRow(6);
    XSSFCell cell046 = linha6.createCell(3);
    cell046.setCellValue("Relatrio de Posies");
    cell046.setCellStyle(estiloTitulo);
    //sheet.addMergedRegion(new CellRangeAddress(6, 6, 0, 3));

    XSSFRow linha7 = (XSSFRow) sheet.createRow(7);
    XSSFCell cell047 = linha7.createCell(3);
    cell047.setCellValue("Veculo : " + placa);
    cell047.setCellStyle(estiloCabecalho);
    //sheet.addMergedRegion(new CellRangeAddress(7, 7, 0, 3));

    XSSFRow linha8 = (XSSFRow) sheet.createRow(8);
    XSSFCell cell038 = linha8.createCell(3);
    //TimeZone.setDefault(timeZoneMundial);
    //Date dataHoraInicio0 = new Date(Long.parseLong(request.getParameter("dataInicio")));
    //TimeZone.setDefault(timeZoneCliente);
    //Date dataHoraInicio = new Date(dataHoraInicio0.getTime());
    SimpleDateFormat dataFormatadaCabecalho = new SimpleDateFormat("dd/MM/yyyy HH:mm:ss");
    dataFormatadaCabecalho.setTimeZone(timeZonePadrao);
    cell038.setCellValue("Perodo: De: " + dataFormatadaCabecalho.format(dataInicio) + " at: "
            + dataFormatadaCabecalho.format(dataFim));
    cell038.setCellStyle(estiloCabecalho);
    //sheet.addMergedRegion(new CellRangeAddress(8, 8, 0, 3));

    sheet.setColumnWidth(0, 5000);
    sheet.setColumnWidth(1, 3000);
    sheet.setColumnWidth(2, 3500);
    //sheet.setColumnWidth(3, 4000);
    sheet.setColumnWidth(3, 30000);
    //sheet.setColumnWidth(4, 4000);
    //sheet.setColumnWidth(4, 30000);
    sheet.setColumnWidth(5, 3000);
    sheet.setColumnWidth(6, 3000);
    sheet.setColumnWidth(7, 3000);
    sheet.setColumnWidth(8, 3000);
    sheet.setColumnWidth(9, 3000);
    //sheet.setColumnWidth(10, 20000);        
    sheet.setColumnWidth(11, 3000);
    XSSFRow linha9 = (XSSFRow) sheet.createRow(10);
    XSSFCell cell0 = linha9.createCell(0);
    cell0.setCellValue("Data e hora");
    cell0.setCellStyle(estilo);
    XSSFCell cell1 = linha9.createCell(1);
    cell1.setCellValue("Velocidade");
    cell1.setCellStyle(estilo);
    XSSFCell cell2 = linha9.createCell(2);
    cell2.setCellValue("Ignio");
    cell2.setCellStyle(estilo);
    //XSSFCell cell3 = linha9.createCell(3);
    //cell3.setCellValue("Latitude");
    //cell3.setCellStyle(estilo);
    //XSSFCell cell4 = linha9.createCell(4);
    //cell4.setCellValue("Longitude");
    //cell4.setCellStyle(estilo);
    //XSSFCell cell5 = linha9.createCell(5);
    //cell5.setCellValue("Satlite");
    //cell5.setCellStyle(estilo);
    //XSSFCell cell6 = linha9.createCell(6);
    //cell6.setCellValue("GPS");
    //cell6.setCellStyle(estilo);
    //XSSFCell cell7 = linha9.createCell(7);
    //cell7.setCellValue("Entrada");
    //cell7.setCellStyle(estilo);
    //XSSFCell cell8 = linha9.createCell(8);
    //cell8.setCellValue("Sada");
    //cell8.setCellStyle(estilo);
    //XSSFCell cell9 = linha9.createCell(9);
    //cell9.setCellValue("Evento");
    //cell9.setCellStyle(estilo);
    XSSFCell cell10 = linha9.createCell(3);
    cell10.setCellValue("Endereo");
    cell10.setCellStyle(estilo);
    //sheet.addMergedRegion(new CellRangeAddress(11, 11, 4, 8));
    //XSSFCell cell11 = linha9.createCell(11);
    //cell11.setCellValue("Direo");
    //cell11.setCellStyle(estilo);
    int linha = 0;
    int j = 11;
    double latAnt = 0;
    double lonAnt = 0;
    double latAtual = 0;
    double lonAtual = 0;
    double distancia = 0;
    double distanciaTotal = 0;
    for (int i = 0; i < posicoes.size(); i++) {
        XSSFRow row = (XSSFRow) sheet.createRow(j);
        if (i == 0) {
            distancia = 0;
            //System.out.println("linha 00 - EXCEL");
        } else {
            //System.out.println("linha 01 - EXCEL");
            latAnt = Double.parseDouble(posicoes.get(i - 1).get("lat"));
            lonAnt = Double.parseDouble(posicoes.get(i - 1).get("lon"));
            latAtual = Double.parseDouble(posicoes.get(i).get("lat"));
            lonAtual = Double.parseDouble(posicoes.get(i).get("lon"));
            //System.out.println("linha 02 - PDF");
            if (latAnt == latAtual && lonAnt == lonAtual) {
                distancia = 0;
            } else {
                distancia = caculaDistanciaEntreDoisPontos(latAnt, lonAnt, latAtual, lonAtual);
                //System.out.println("linha 03 - PDF");
            }

        }
        distanciaTotal = distanciaTotal + distancia;
        /*if(i==0) {
         latAnt = Double.parseDouble(posicoes.get(i).get("lat"));
         lonAnt = Double.parseDouble(posicoes.get(i).get("lon"));
        } else{
            latAnt = Double.parseDouble(posicoes.get(i-1).get("lat"));
            lonAnt = Double.parseDouble(posicoes.get(i-1).get("lon"));
        }
        double latAtual = Double.parseDouble(posicoes.get(i).get("lat"));
        double lonAtual = Double.parseDouble(posicoes.get(i).get("lon"));
        double distancia = caculaDistanciaEntreDoisPontos(latAnt, lonAnt, latAtual, lonAtual);
        distanciaTotal = distanciaTotal + distancia;*/
        for (int col = 0; col < posicoes.get(linha).size(); col++) {
            XSSFCell cell = row.createCell(col);
            cell.setCellStyle(estiloCorpo);
            switch (col) {
            case 0:
                Date dataHora0 = new Date(Long.parseLong(posicoes.get(i).get("dataHora")));
                Date dataHora = new Date(dataHora0.getTime());
                SimpleDateFormat dataFormatada = new SimpleDateFormat("dd/MM/yyyy HH:mm:ss");
                dataFormatada.setTimeZone(timeZonePadrao);
                cell.setCellValue(dataFormatada.format(dataHora));
                break;
            case 1:
                cell.setCellValue(posicoes.get(linha).get("vel"));
                break;
            case 2:
                cell.setCellValue(
                        posicoes.get(linha).get("ign").equalsIgnoreCase("True") ? "Ligada" : "Desligada");
                break;
            /*case 3:
                cell.setCellValue(posicoes.get(linha).get("lat"));
                break;
            case 4:
                cell.setCellValue(posicoes.get(linha).get("lon"));
                break;
            case 5:
                cell.setCellValue(posicoes.get(linha).get("sat"));
                break;
            case 6:
                cell.setCellValue(posicoes.get(linha).get("gps"));
                break;
            case 7:
                cell.setCellValue(posicoes.get(linha).get("entrada"));
                break;
            case 8:
                cell.setCellValue(posicoes.get(linha).get("saida"));
                break;
            case 9:
                cell.setCellValue(posicoes.get(linha).get("evento"));
                break;*/
            case 3:
                cell.setCellValue(posicoes.get(linha).get("endereco") == null ? "Sem endereo"
                        : posicoes.get(linha).get("endereco"));
                break;
            /*case 11:
                cell.setCellValue(posicoes.get(linha).get("direcao"));
                break;*/
            }

        }
        j = j + 1;
        linha = linha + 1;

    }
    tempoDecorrido = calculaDatas(Long.parseLong(posicoes.get(0).get("dataHora")),
            Long.parseLong(posicoes.get(posicoes.size() - 1).get("dataHora")));
    int index = 0;
    String kms = "0";
    String m = "";
    double metros = 0;
    if (distanciaTotal > 0) {
        BigDecimal decimalFormatado = new BigDecimal(distanciaTotal).setScale(2, RoundingMode.HALF_EVEN);
        index = String.valueOf(decimalFormatado).indexOf(".");
        kms = String.valueOf(decimalFormatado).substring(0, index);
        m = "0" + (String.valueOf(decimalFormatado).substring(index));
        metros = Double.parseDouble(m) * 1000;
    }
    //String formatted = NumberFormat.getFormat("000.00").format(metros);
    //System.out.println("Percorridos aproximadamente : "+kms+" KM e "+metros+" metros");
    XSSFRow linhaX = (XSSFRow) sheet.createRow(linha + 12);
    XSSFCell cellX = linhaX.createCell(0);
    sheet.addMergedRegion(new CellRangeAddress(linha + 12, linha + 12, 0, 3));
    cellX.setCellStyle(estilo);
    cellX.setCellValue(
            "Percorridos: " + kms + " KM e " + String.valueOf(metros) + " metros. Tempo: " + tempoDecorrido);
    ByteArrayOutputStream outByteStream = new ByteArrayOutputStream();
    wb.write(outByteStream);

    byte[] outArray = outByteStream.toByteArray();
    OutputStream outStream = response.getOutputStream();
    outStream.write(outArray);
    outStream.flush();

}

From source file:com.axelor.apps.admin.service.ViewDocExportService.java

License:Open Source License

public MetaFile export(MetaFile docFile, boolean onlyPanel) {

    this.onlyPanel = onlyPanel;

    List<MetaMenu> menus = metaMenuRepo.all()
            .filter("self.parent is null " + "and self.left = true and self.action is null").order("order")
            .fetch();/*from w  ww  . j  av a  2  s  .  co m*/

    workBook = new XSSFWorkbook();
    addStyle();

    if (docFile != null && !onlyPanel) {
        updateDocMap(docFile);
    }

    processRootMenu(menus.iterator());

    setColumnWidth();

    return createExportFile(docFile);
}

From source file:com.axelor.studio.service.data.validator.ValidatorService.java

License:Open Source License

public void addLog(String log, String sheetName, int rowNum) throws IOException {

    if (logFile == null) {
        logFile = File.createTempFile("ImportLog", ".xlsx");
        logBook = new XSSFWorkbook();
    }//w  ww . ja va2s .  co m

    XSSFSheet sheet = logBook.getSheet(sheetName);

    if (sheet == null) {
        sheet = logBook.createSheet(sheetName);
        XSSFRow titleRow = sheet.createRow(0);
        titleRow.createCell(0).setCellValue("Row Number");
        titleRow.createCell(1).setCellValue("Issues");
    }

    Iterator<Row> rowIterator = sheet.rowIterator();
    Row logRow = null;
    while (rowIterator.hasNext()) {
        Row sheetRow = rowIterator.next();
        Cell cell = sheetRow.getCell(0);
        if (cell.getCellType() != Cell.CELL_TYPE_NUMERIC) {
            continue;
        }
        double value = cell.getNumericCellValue();
        if (value == rowNum + 1) {
            logRow = sheetRow;
            break;
        }
    }

    if (logRow == null) {
        logRow = sheet.createRow(sheet.getPhysicalNumberOfRows());
    }

    Cell cell = logRow.getCell(0);
    if (cell == null) {
        cell = logRow.createCell(0);
        cell.setCellValue(rowNum + 1);
    }
    cell = logRow.getCell(1);
    if (cell == null) {
        cell = logRow.createCell(1);
    }
    String oldValue = cell.getStringCellValue();
    if (oldValue == null) {
        cell.setCellValue(log);
    } else {
        cell.setCellValue(oldValue + "\n" + log);
    }

}

From source file:com.b2international.snowowl.snomed.exporter.server.dsv.SnomedSimpleTypeRefSetExcelExporter.java

License:Apache License

public SnomedSimpleTypeRefSetExcelExporter(final String userId, final IBranchPath branchPath,
        final String refSetId) {
    super(userId, branchPath);

    this.refSet = SnomedRequests.prepareGetReferenceSet(refSetId)
            .build(SnomedDatastoreActivator.REPOSITORY_UUID, branchPath.getPath()).execute(getBus()).getSync();
    this.context = new SnomedEditingContext(branchPath);
    this.workbook = new XSSFWorkbook();

    final Font headerFont = workbook.createFont();
    headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
    headerFont.setFontName(FONT_STYLE);/*from www.j a  v a  2  s. c  o  m*/

    final Font defaultFont = workbook.createFont();
    defaultFont.setFontName(FONT_STYLE);

    BOLD_STYLE = workbook.createCellStyle();
    headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
    BOLD_STYLE.setAlignment(CellStyle.ALIGN_LEFT);
    BOLD_STYLE.setFont(headerFont);

    DEFAULT_STYLE = workbook.createCellStyle();
    DEFAULT_STYLE.setFont(defaultFont);
}

From source file:com.b510.excel.client.BusinessPlan.java

License:Apache License

public static void main(String[] args) throws Exception {
    Workbook wb;//from   w  w w  .ja v  a 2 s . c om

    if (args.length > 0 && args[0].equals("-xls"))
        wb = new HSSFWorkbook();
    else
        wb = new XSSFWorkbook();

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

    Sheet sheet = wb.createSheet("Business Plan");

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

    //the following three statements are required only for HSSF
    sheet.setAutobreaks(true);
    printSetup.setFitHeight((short) 1);
    printSetup.setFitWidth((short) 1);

    //the header row: centered text in 48pt font
    Row headerRow = sheet.createRow(0);
    headerRow.setHeightInPoints(12.75f);
    for (int i = 0; i < titles.length; i++) {
        Cell cell = headerRow.createCell(i);
        cell.setCellValue(titles[i]);
        cell.setCellStyle(styles.get("header"));
    }
    //columns for 11 weeks starting from 9-Jul
    Calendar calendar = Calendar.getInstance();
    int year = calendar.get(Calendar.YEAR);

    calendar.setTime(fmt.parse("9-Jul"));
    calendar.set(Calendar.YEAR, year);
    for (int i = 0; i < 11; i++) {
        Cell cell = headerRow.createCell(titles.length + i);
        cell.setCellValue(calendar);
        cell.setCellStyle(styles.get("header_date"));
        calendar.roll(Calendar.WEEK_OF_YEAR, true);
    }
    //freeze the first row
    sheet.createFreezePane(0, 1);

    Row row;
    Cell cell;
    int rownum = 1;
    for (int i = 0; i < data.length; i++, rownum++) {
        row = sheet.createRow(rownum);
        if (data[i] == null)
            continue;

        for (int j = 0; j < data[i].length; j++) {
            cell = row.createCell(j);
            String styleName;
            boolean isHeader = i == 0 || data[i - 1] == null;
            switch (j) {
            case 0:
                if (isHeader) {
                    styleName = "cell_b";
                    cell.setCellValue(Double.parseDouble(data[i][j]));
                } else {
                    styleName = "cell_normal";
                    cell.setCellValue(data[i][j]);
                }
                break;
            case 1:
                if (isHeader) {
                    styleName = i == 0 ? "cell_h" : "cell_bb";
                } else {
                    styleName = "cell_indented";
                }
                cell.setCellValue(data[i][j]);
                break;
            case 2:
                styleName = isHeader ? "cell_b" : "cell_normal";
                cell.setCellValue(data[i][j]);
                break;
            case 3:
                styleName = isHeader ? "cell_b_centered" : "cell_normal_centered";
                cell.setCellValue(Integer.parseInt(data[i][j]));
                break;
            case 4: {
                calendar.setTime(fmt.parse(data[i][j]));
                calendar.set(Calendar.YEAR, year);
                cell.setCellValue(calendar);
                styleName = isHeader ? "cell_b_date" : "cell_normal_date";
                break;
            }
            case 5: {
                int r = rownum + 1;
                String fmla = "IF(AND(D" + r + ",E" + r + "),E" + r + "+D" + r + ",\"\")";
                cell.setCellFormula(fmla);
                styleName = isHeader ? "cell_bg" : "cell_g";
                break;
            }
            default:
                styleName = data[i][j] != null ? "cell_blue" : "cell_normal";
            }

            cell.setCellStyle(styles.get(styleName));
        }
    }

    //group rows for each phase, row numbers are 0-based
    sheet.groupRow(4, 6);
    sheet.groupRow(9, 13);
    sheet.groupRow(16, 18);

    //set column widths, the width is measured in units of 1/256th of a character width
    sheet.setColumnWidth(0, 256 * 6);
    sheet.setColumnWidth(1, 256 * 33);
    sheet.setColumnWidth(2, 256 * 20);
    sheet.setZoom(3, 4);

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