Example usage for org.apache.poi.ss.util WorkbookUtil createSafeSheetName

List of usage examples for org.apache.poi.ss.util WorkbookUtil createSafeSheetName

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.ss.util.*
  5. WorkbookUtil
  6. createSafeSheetName

Introduction

In this page you can find the example usage for org.apache.poi.ss.util WorkbookUtil createSafeSheetName.

Prototype

public static String createSafeSheetName(final String nameProposal)

Source Link

Document

Creates a valid sheet name, which is conform to the rules.

Usage

From source file:com.skt.adcas.lte.action.DownLinkBySTDAction.java

public String selectCellTrafficThrpCompGraphExcelDownload() {

    this.log.debug("selectCellTrafficThrpCompGraphExcelDownload Start");
    SqlSession session = null;//from  ww  w  .  j av a2  s. com
    FileOutputStream fileOut = null;

    try {
        //parseParam();
        Type type = new TypeToken<Map<String, Object>>() {
        }.getType();
        Gson gson = new Gson();
        Map<String, Object> map = gson.fromJson(this.JSONDATA, type);

        log.debug("json data : " + this.JSONDATA);

        Workbook wb = new HSSFWorkbook();
        //CreationHelper createHelper = wb.getCreationHelper();

        String sheetName = " ?";
        String safeName = WorkbookUtil.createSafeSheetName(sheetName);

        //sheet 
        Sheet sheet = wb.createSheet(safeName);

        //header ?
        Row hrow0 = sheet.createRow((short) 0);
        hrow0.setHeightInPoints(20);
        hrow0.createCell(0).setCellValue("");
        hrow0.createCell(1).setCellValue("(" + this.FROMYMD + ")");
        hrow0.createCell(2).setCellValue("(" + this.TOYMD + ")");

        StringMap categories = (StringMap) map.get("categories");
        StringMap beforeSeries = (StringMap) map.get("beforeSeries");
        StringMap afterSeries = (StringMap) map.get("afterSeries");

        short i = 1;
        for (int j = 0; j < categories.size(); j++) {
            //  
            Row row = sheet.createRow((short) i);
            row.setHeightInPoints(20);
            row.createCell(0)
                    .setCellValue(categories.get(String.valueOf(j)).toString().replaceAll("<br>", " : "));
            row.createCell(1).setCellValue(Double.parseDouble(beforeSeries.get(String.valueOf(j)).toString()));
            row.createCell(2).setCellValue(Double.parseDouble(afterSeries.get(String.valueOf(j)).toString()));
            i++;
        }

        log.debug("selectCellTrafficThrpCompGraphExcelDownload : file start");

        String writeFolderPath = (String) super.properties.get("TEMP_FOLDER_PATH");
        String tempFolder = "/" + UUID.randomUUID().toString();
        String xlsFileName = "/DownLinkThrpCompGraph(STD).xls";

        if (!(new File(writeFolderPath + tempFolder)).mkdir()) {
            throw new Exception("? ??  .");
        }

        String xlsFileFullPath = writeFolderPath + tempFolder + xlsFileName;
        fileOut = new FileOutputStream(xlsFileFullPath);
        wb.write(fileOut);
        log.debug("selectCellTrafficThrpCompGraphExcelDownload : file end");

        this.msg = "? ? ?";
        this.status = "SUCCESS";
        this.downloadurl = "download" + tempFolder + xlsFileName;

    } catch (Exception e) {
        this.msg = e.getMessage();
        this.status = "ERROR";
        this.error = true;
        if (session != null) {
            session.rollback();
        }
        e.printStackTrace();
    } finally {
        try {
            if (fileOut != null)
                fileOut.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
        if (session != null) {
            session.close();
        }
    }

    this.log.debug("selectCellTrafficThrpCompGraphExcelDownload End");
    return SUCCESS;
}

From source file:com.skt.adcas.lte.action.DownLinkBySTDStatsAction.java

public String selectCellTrafficStatsCQIExcelDownload() {

    this.log.debug("selectCellTrafficStatsCQIExcelDownload Start");
    FileOutputStream fileOut = null;

    try {/*from www.j av a2 s .c  o m*/
        //parseParam();
        Type type = new TypeToken<Map<String, Object>>() {
        }.getType();
        Gson gson = new Gson();
        Map<String, Object> map = gson.fromJson(this.JSONDATA, type);

        log.debug("json data : " + this.JSONDATA);

        String searchType = this.SEARCHTYPE;
        log.debug("SEARCHTYPE : " + searchType);

        Workbook wb = new HSSFWorkbook();

        //PDFsheet 
        String sheetName = "CQI PDF";
        String safeName = WorkbookUtil.createSafeSheetName(sheetName);
        Sheet PDFsheet = wb.createSheet(safeName);

        createCellTrafficStatsCQIExcelSheet(PDFsheet, map, searchType, "PDF");

        //CDFsheet 
        sheetName = "CQI CDF";
        safeName = WorkbookUtil.createSafeSheetName(sheetName);
        Sheet CDFsheet = wb.createSheet(safeName);

        createCellTrafficStatsCQIExcelSheet(CDFsheet, map, searchType, "CDF");

        String writeFolderPath = (String) super.properties.get("TEMP_FOLDER_PATH");
        String tempFolder = "/" + UUID.randomUUID().toString();
        String xlsFileName = "/DownLinkStatsCQI(PDF_CDF)(STD).xls";

        if (!(new File(writeFolderPath + tempFolder)).mkdir()) {
            throw new Exception("? ??  .");
        }

        String xlsFileFullPath = writeFolderPath + tempFolder + xlsFileName;
        fileOut = new FileOutputStream(xlsFileFullPath);
        wb.write(fileOut);

        this.msg = "? ? ?";
        this.status = "SUCCESS";
        this.downloadurl = "download" + tempFolder + xlsFileName;

    } catch (Exception e) {
        this.msg = e.getMessage();
        this.status = "ERROR";
        this.error = true;
        e.printStackTrace();
    } finally {
        try {
            if (fileOut != null)
                fileOut.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    this.log.debug("selectCellTrafficStatsCQIExcelDownload End");
    return SUCCESS;
}

From source file:com.skt.adcas.lte.action.DownLinkBySTDStatsAction.java

public String selectCellTrafficStatsCompCQIExcelDownload() {

    this.log.debug("selectCellTrafficStatsCompCQIExcelDownload Start");
    FileOutputStream fileOut = null;

    try {// w ww .  j ava 2  s. c o m
        //parseParam();
        Type type = new TypeToken<Map<String, Object>>() {
        }.getType();
        Gson gson = new Gson();
        Map<String, Object> map = gson.fromJson(this.JSONDATA, type);
        Map<String, Object> mapAfter = gson.fromJson(this.JSONDATA2, type);

        log.debug("json data : " + this.JSONDATA);
        log.debug("json data : " + this.JSONDATA2);

        String searchType = this.SEARCHTYPE;
        log.debug("SEARCHTYPE : " + searchType);

        Workbook wb = new HSSFWorkbook();

        String sheetName = " CQI PDF";
        String safeName = WorkbookUtil.createSafeSheetName(sheetName);
        Sheet PDFsheet = wb.createSheet(safeName);

        createCellTrafficStatsCQIExcelSheet(PDFsheet, map, searchType, "PDF");

        sheetName = " CQI CDF";
        safeName = WorkbookUtil.createSafeSheetName(sheetName);
        Sheet CDFsheet = wb.createSheet(safeName);

        createCellTrafficStatsCQIExcelSheet(CDFsheet, map, searchType, "CDF");

        sheetName = " CQI PDF";
        safeName = WorkbookUtil.createSafeSheetName(sheetName);
        Sheet PDFsheetAfter = wb.createSheet(safeName);

        createCellTrafficStatsCQIExcelSheet(PDFsheetAfter, mapAfter, searchType, "PDF");

        sheetName = " CQI CDF";
        safeName = WorkbookUtil.createSafeSheetName(sheetName);
        Sheet CDFsheetAfter = wb.createSheet(safeName);

        createCellTrafficStatsCQIExcelSheet(CDFsheetAfter, mapAfter, searchType, "CDF");

        String writeFolderPath = (String) super.properties.get("TEMP_FOLDER_PATH");
        String tempFolder = "/" + UUID.randomUUID().toString();
        String xlsFileName = "/DownLinkStatsCompCQI(PDF_CDF)(STD).xls";

        if (!(new File(writeFolderPath + tempFolder)).mkdir()) {
            throw new Exception("? ??  .");
        }

        String xlsFileFullPath = writeFolderPath + tempFolder + xlsFileName;
        fileOut = new FileOutputStream(xlsFileFullPath);
        wb.write(fileOut);

        this.msg = "? ? ?";
        this.status = "SUCCESS";
        this.downloadurl = "download" + tempFolder + xlsFileName;

    } catch (Exception e) {
        this.msg = e.getMessage();
        this.status = "ERROR";
        this.error = true;
        e.printStackTrace();
    } finally {
        try {
            if (fileOut != null)
                fileOut.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    this.log.debug("selectCellTrafficStatsCQIExcelDownload End");
    return SUCCESS;
}

From source file:com.skt.adcas.lte.action.DownLinkBySTDStatsAction.java

public String selectCellTrafficStatsExcelDownload() {

    this.log.debug("selectCellTrafficStatsExcelDownload Start");
    FileOutputStream fileOut = null;

    try {/*from  ww w . ja  v  a2s .c o m*/
        //parseParam();
        Type type = new TypeToken<Map<String, Object>>() {
        }.getType();
        Gson gson = new Gson();
        Map<String, Object> map = gson.fromJson(this.JSONDATA, type);

        log.debug("json data : " + this.JSONDATA);

        String searchType = this.SEARCHTYPE;

        Workbook wb = new HSSFWorkbook();
        CreationHelper createHelper = wb.getCreationHelper();

        String sheetName = "data";
        String safeName = WorkbookUtil.createSafeSheetName(sheetName);

        //sheet 
        Sheet sheet = wb.createSheet(safeName);

        createCellTrafficStatsExcelSheet(sheet, map, searchType);

        String writeFolderPath = (String) super.properties.get("TEMP_FOLDER_PATH");
        String tempFolder = "/" + UUID.randomUUID().toString();
        String xlsFileName = "/DownLinkStatsData(STD).xls";

        if (!(new File(writeFolderPath + tempFolder)).mkdir()) {
            throw new Exception("? ??  .");
        }

        String xlsFileFullPath = writeFolderPath + tempFolder + xlsFileName;
        fileOut = new FileOutputStream(xlsFileFullPath);
        wb.write(fileOut);

        this.msg = "? ? ?";
        this.status = "SUCCESS";
        this.downloadurl = "download" + tempFolder + xlsFileName;

    } catch (Exception e) {
        this.msg = e.getMessage();
        this.status = "ERROR";
        this.error = true;
        e.printStackTrace();
    } finally {
        try {
            if (fileOut != null)
                fileOut.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    this.log.debug("selectCellTrafficStatsExcelDownload End");
    return SUCCESS;
}

From source file:com.skt.adcas.lte.action.DownLinkBySTDStatsAction.java

public String selectCellTrafficStatsCompExcelDownload() {

    this.log.debug("selectCellTrafficStatsCompExcelDownload Start");
    FileOutputStream fileOut = null;

    try {//from  w  w w.  j  a va2  s . co  m
        //parseParam();
        Type type = new TypeToken<Map<String, Object>>() {
        }.getType();
        Gson gson = new Gson();
        Map<String, Object> map = gson.fromJson(this.JSONDATA, type);
        Map<String, Object> mapAfter = gson.fromJson(this.JSONDATA2, type);

        log.debug("json data : " + this.JSONDATA);
        log.debug("json data : " + this.JSONDATA2);

        String searchType = this.SEARCHTYPE;

        Workbook wb = new HSSFWorkbook();
        CreationHelper createHelper = wb.getCreationHelper();

        String sheetName = "";
        String safeName = WorkbookUtil.createSafeSheetName(sheetName);
        Sheet sheet = wb.createSheet(safeName);

        createCellTrafficStatsExcelSheet(sheet, map, searchType);

        sheetName = "";
        safeName = WorkbookUtil.createSafeSheetName(sheetName);
        Sheet sheetAfter = wb.createSheet(safeName);

        createCellTrafficStatsExcelSheet(sheetAfter, mapAfter, searchType);

        String writeFolderPath = (String) super.properties.get("TEMP_FOLDER_PATH");
        String tempFolder = "/" + UUID.randomUUID().toString();
        String xlsFileName = "/DownLinkStatsCompData(STD).xls";

        if (!(new File(writeFolderPath + tempFolder)).mkdir()) {
            throw new Exception("? ??  .");
        }

        String xlsFileFullPath = writeFolderPath + tempFolder + xlsFileName;
        fileOut = new FileOutputStream(xlsFileFullPath);
        wb.write(fileOut);

        this.msg = "? ? ?";
        this.status = "SUCCESS";
        this.downloadurl = "download" + tempFolder + xlsFileName;

    } catch (Exception e) {
        this.msg = e.getMessage();
        this.status = "ERROR";
        this.error = true;
        e.printStackTrace();
    } finally {
        try {
            if (fileOut != null)
                fileOut.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    this.log.debug("selectCellTrafficStatsCompExcelDownload End");
    return SUCCESS;
}

From source file:com.skt.adcas.lte.action.DownLinkBySTDStatsAction.java

public String selectCellTrafficStatsThrpCompGraphExcelDownload() {

    this.log.debug("selectCellTrafficStatsThrpCompGraphExcelDownload Start");
    SqlSession session = null;/* ww  w.j av  a2  s  .com*/
    FileOutputStream fileOut = null;

    try {
        //parseParam();
        Type type = new TypeToken<Map<String, Object>>() {
        }.getType();
        Gson gson = new Gson();
        Map<String, Object> map = gson.fromJson(this.JSONDATA, type);

        log.debug("json data : " + this.JSONDATA);

        Workbook wb = new HSSFWorkbook();
        //CreationHelper createHelper = wb.getCreationHelper();

        String sheetName = " ?";
        String safeName = WorkbookUtil.createSafeSheetName(sheetName);

        //sheet 
        Sheet sheet = wb.createSheet(safeName);

        //header ?
        Row hrow0 = sheet.createRow((short) 0);
        hrow0.setHeightInPoints(20);
        hrow0.createCell(0).setCellValue("");
        hrow0.createCell(1).setCellValue("(" + this.FROMYMD + ")");
        hrow0.createCell(2).setCellValue("(" + this.TOYMD + ")");

        StringMap categories = (StringMap) map.get("categories");
        StringMap beforeSeries = (StringMap) map.get("beforeSeries");
        StringMap afterSeries = (StringMap) map.get("afterSeries");

        short i = 1;
        for (int j = 0; j < categories.size(); j++) {
            //  
            Row row = sheet.createRow((short) i);
            row.setHeightInPoints(20);
            row.createCell(0)
                    .setCellValue(categories.get(String.valueOf(j)).toString().replaceAll("<br>", " : "));
            row.createCell(1).setCellValue(Double.parseDouble(beforeSeries.get(String.valueOf(j)).toString()));
            row.createCell(2).setCellValue(Double.parseDouble(afterSeries.get(String.valueOf(j)).toString()));
            i++;
        }

        log.debug("selectCellTrafficStatsThrpCompGraphExcelDownload : file start");

        String writeFolderPath = (String) super.properties.get("TEMP_FOLDER_PATH");
        String tempFolder = "/" + UUID.randomUUID().toString();
        String xlsFileName = "/DownLinkThrpCompGraph(STD).xls";

        if (!(new File(writeFolderPath + tempFolder)).mkdir()) {
            throw new Exception("? ??  .");
        }

        String xlsFileFullPath = writeFolderPath + tempFolder + xlsFileName;
        fileOut = new FileOutputStream(xlsFileFullPath);
        wb.write(fileOut);
        log.debug("selectCellTrafficStatsThrpCompGraphExcelDownload : file end");

        this.msg = "? ? ?";
        this.status = "SUCCESS";
        this.downloadurl = "download" + tempFolder + xlsFileName;

    } catch (Exception e) {
        this.msg = e.getMessage();
        this.status = "ERROR";
        this.error = true;
        if (session != null) {
            session.rollback();
        }
        e.printStackTrace();
    } finally {
        try {
            if (fileOut != null)
                fileOut.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
        if (session != null) {
            session.close();
        }
    }

    this.log.debug("selectCellTrafficStatsThrpCompGraphExcelDownload End");
    return SUCCESS;
}

From source file:com.tremolosecurity.scale.ui.reports.GenerateSpreadsheet.java

License:Apache License

@Override
protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {

    resp.setHeader("Cache-Control", "private, no-store, no-cache, must-revalidate");
    resp.setHeader("Pragma", "no-cache");

    ReportViewer scaleReport = (ReportViewer) req.getSession().getAttribute("scaleReportCached");

    Workbook wb = new XSSFWorkbook();

    Font font = wb.createFont();/*from ww  w . j  a v a2  s .co  m*/
    font.setBold(true);

    Font titleFont = wb.createFont();
    titleFont.setBold(true);
    titleFont.setFontHeightInPoints((short) 16);

    Sheet sheet = wb.createSheet(WorkbookUtil.createSafeSheetName(scaleReport.getReportInfo().getName()));

    //Create a header
    Row row = sheet.createRow(0);
    Cell cell = row.createCell(0);

    RichTextString title = new XSSFRichTextString(scaleReport.getReportInfo().getName());
    title.applyFont(titleFont);

    sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 3));

    cell.setCellValue(title);

    row = sheet.createRow(1);
    cell = row.createCell(0);
    cell.setCellValue(scaleReport.getReportInfo().getDescription());

    sheet.addMergedRegion(new CellRangeAddress(1, 1, 0, 3));

    row = sheet.createRow(2);
    cell = row.createCell(0);
    cell.setCellValue(scaleReport.getRunDateTime());

    sheet.addMergedRegion(new CellRangeAddress(2, 2, 0, 3));

    row = sheet.createRow(3);

    int rowNum = 4;

    if (scaleReport.getResults().getGrouping().isEmpty()) {
        row = sheet.createRow(rowNum);
        cell = row.createCell(0);
        cell.setCellValue("There is no data for this report");
    } else {

        for (ReportGrouping group : scaleReport.getResults().getGrouping()) {
            for (String colHeader : scaleReport.getResults().getHeaderFields()) {
                row = sheet.createRow(rowNum);
                cell = row.createCell(0);

                RichTextString rcolHeader = new XSSFRichTextString(colHeader);
                rcolHeader.applyFont(font);

                cell.setCellValue(rcolHeader);
                cell = row.createCell(1);
                cell.setCellValue(group.getHeader().get(colHeader));

                rowNum++;
            }

            row = sheet.createRow(rowNum);

            int cellNum = 0;
            for (String colHeader : scaleReport.getResults().getDataFields()) {
                cell = row.createCell(cellNum);

                RichTextString rcolHeader = new XSSFRichTextString(colHeader);
                rcolHeader.applyFont(font);
                cell.setCellValue(rcolHeader);
                cellNum++;
            }

            rowNum++;

            for (Map<String, String> dataRow : group.getData()) {
                cellNum = 0;
                row = sheet.createRow(rowNum);
                for (String colHeader : scaleReport.getResults().getDataFields()) {
                    cell = row.createCell(cellNum);
                    cell.setCellValue(dataRow.get(colHeader));
                    cellNum++;
                }
                rowNum++;
            }

            row = sheet.createRow(rowNum);
            rowNum++;
        }

    }

    resp.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
    wb.write(resp.getOutputStream());

}

From source file:com.tremolosecurity.scalejs.ws.ScaleMain.java

License:Apache License

private void exportToExcel(HttpFilterRequest request, HttpFilterResponse response, Gson gson)
        throws IOException {
    int lastslash = request.getRequestURI().lastIndexOf('/');
    int secondlastslash = request.getRequestURI().lastIndexOf('/', lastslash - 1);

    String id = request.getRequestURI().substring(secondlastslash + 1, lastslash);

    ReportResults res = (ReportResults) request.getSession().getAttribute(id);

    if (res == null) {
        response.setStatus(404);//from w ww  . ja v a  2 s .com
        ScaleError error = new ScaleError();
        error.getErrors().add("Report no longer available");
        ScaleJSUtils.addCacheHeaders(response);
        response.getWriter().print(gson.toJson(error).trim());
        response.getWriter().flush();
    } else {

        response.setHeader("Cache-Control", "private, no-store, no-cache, must-revalidate");
        response.setHeader("Pragma", "no-cache");

        Workbook wb = new XSSFWorkbook();

        Font font = wb.createFont();
        font.setBold(true);

        Font titleFont = wb.createFont();
        titleFont.setBold(true);
        titleFont.setFontHeightInPoints((short) 16);

        Sheet sheet = wb.createSheet(WorkbookUtil.createSafeSheetName(res.getName()));

        //Create a header
        Row row = sheet.createRow(0);
        Cell cell = row.createCell(0);

        RichTextString title = new XSSFRichTextString(res.getName());
        title.applyFont(titleFont);

        sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 3));

        cell.setCellValue(title);

        row = sheet.createRow(1);
        cell = row.createCell(0);
        cell.setCellValue(res.getDescription());

        sheet.addMergedRegion(new CellRangeAddress(1, 1, 0, 3));

        row = sheet.createRow(2);
        cell = row.createCell(0);
        //cell.setCellValue(new DateTime().toString("MMMM Do, YYYY h:mm:ss a"));

        sheet.addMergedRegion(new CellRangeAddress(2, 2, 0, 3));

        row = sheet.createRow(3);

        int rowNum = 4;

        if (res.getGrouping().isEmpty()) {
            row = sheet.createRow(rowNum);
            cell = row.createCell(0);
            cell.setCellValue("There is no data for this report");
        } else {

            for (ReportGrouping group : res.getGrouping()) {
                for (String colHeader : res.getHeaderFields()) {
                    row = sheet.createRow(rowNum);
                    cell = row.createCell(0);

                    RichTextString rcolHeader = new XSSFRichTextString(colHeader);
                    rcolHeader.applyFont(font);

                    cell.setCellValue(rcolHeader);
                    cell = row.createCell(1);
                    cell.setCellValue(group.getHeader().get(colHeader));

                    rowNum++;
                }

                row = sheet.createRow(rowNum);

                int cellNum = 0;
                for (String colHeader : res.getDataFields()) {
                    cell = row.createCell(cellNum);

                    RichTextString rcolHeader = new XSSFRichTextString(colHeader);
                    rcolHeader.applyFont(font);
                    cell.setCellValue(rcolHeader);
                    cellNum++;
                }

                rowNum++;

                for (Map<String, String> dataRow : group.getData()) {
                    cellNum = 0;
                    row = sheet.createRow(rowNum);
                    for (String colHeader : res.getDataFields()) {
                        cell = row.createCell(cellNum);
                        cell.setCellValue(dataRow.get(colHeader));
                        cellNum++;
                    }
                    rowNum++;
                }

                row = sheet.createRow(rowNum);
                rowNum++;
            }

        }

        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        wb.write(response.getOutputStream());
    }
}

From source file:contestTabulation.Setup.java

License:Open Source License

@Override
public void doPost(HttpServletRequest req, HttpServletResponse resp) throws IOException {
    HttpTransport httpTransport = new NetHttpTransport();
    JacksonFactory jsonFactory = new JacksonFactory();
    DatastoreService datastore = DatastoreServiceFactory.getDatastoreService();

    Entity contestInfo = Retrieve.contestInfo();

    GoogleCredential credential = new GoogleCredential.Builder().setJsonFactory(jsonFactory)
            .setTransport(httpTransport)
            .setClientSecrets((String) contestInfo.getProperty("OAuth2ClientId"),
                    (String) contestInfo.getProperty("OAuth2ClientSecret"))
            .build().setFromTokenResponse(new JacksonFactory().fromString(
                    ((Text) contestInfo.getProperty("OAuth2Token")).getValue(), GoogleTokenResponse.class));

    String docName = null, docLevel = null;
    for (Level level : Level.values()) {
        docName = req.getParameter("doc" + level.getName());
        if (docName != null) {
            docLevel = level.toString();
            break;
        }/*from   w ww. ja  v  a 2s .  co m*/
    }

    if (docLevel == null) {
        resp.sendError(HttpServletResponse.SC_BAD_REQUEST,
                "Spreadsheet creation request must have paramater document name parameter set");
        return;
    }

    Query query = new Query("registration")
            .setFilter(new FilterPredicate("schoolLevel", FilterOperator.EQUAL, docLevel))
            .addSort("schoolName", SortDirection.ASCENDING);
    List<Entity> registrations = datastore.prepare(query).asList(FetchOptions.Builder.withDefaults());

    Map<String, List<JSONObject>> studentData = new HashMap<String, List<JSONObject>>();
    for (Entity registration : registrations) {
        String regSchoolName = ((String) registration.getProperty("schoolName")).trim();
        String regStudentDataJSON = unescapeHtml4(((Text) registration.getProperty("studentData")).getValue());

        JSONArray regStudentData = null;
        try {
            regStudentData = new JSONArray(regStudentDataJSON);
        } catch (JSONException e) {
            e.printStackTrace();
            resp.sendError(HttpServletResponse.SC_INTERNAL_SERVER_ERROR, e.toString());
            return;
        }

        for (int i = 0; i < regStudentData.length(); i++) {
            if (!studentData.containsKey(regSchoolName)) {
                studentData.put(regSchoolName, new ArrayList<JSONObject>());
            }
            try {
                studentData.get(regSchoolName).add(regStudentData.getJSONObject(i));
            } catch (JSONException e) {
                resp.setStatus(HttpServletResponse.SC_INTERNAL_SERVER_ERROR);
                e.printStackTrace();
                return;
            }
        }
    }

    for (List<JSONObject> students : studentData.values()) {
        Collections.sort(students, new Comparator<JSONObject>() {
            @Override
            public int compare(JSONObject a, JSONObject b) {
                try {
                    return a.getString("name").compareTo(b.getString("name"));
                } catch (JSONException e) {
                    e.printStackTrace();
                    return 0;
                }
            }
        });
    }

    Workbook workbook = new XSSFWorkbook();

    XSSFCellStyle boldStyle = (XSSFCellStyle) workbook.createCellStyle();
    Font boldFont = workbook.createFont();
    boldFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
    boldStyle.setFont(boldFont);

    Map<Subject, XSSFCellStyle> subjectCellStyles = new HashMap<Subject, XSSFCellStyle>();
    for (Subject subject : Subject.values()) {
        final double ALPHA = .144;
        String colorStr = (String) contestInfo.getProperty("color" + subject.getName());
        byte[] backgroundColor = new byte[] { Integer.valueOf(colorStr.substring(1, 3), 16).byteValue(),
                Integer.valueOf(colorStr.substring(3, 5), 16).byteValue(),
                Integer.valueOf(colorStr.substring(5, 7), 16).byteValue() };
        // http://en.wikipedia.org/wiki/Alpha_compositing#Alpha_blending
        byte[] borderColor = new byte[] { (byte) ((backgroundColor[0] & 0xff) * (1 - ALPHA)),
                (byte) ((backgroundColor[1] & 0xff) * (1 - ALPHA)),
                (byte) ((backgroundColor[2] & 0xff) * (1 - ALPHA)) };

        XSSFCellStyle style = (XSSFCellStyle) workbook.createCellStyle();
        style.setFillBackgroundColor(new XSSFColor(backgroundColor));
        style.setFillPattern(CellStyle.ALIGN_FILL);

        style.setBorderBottom(CellStyle.BORDER_THIN);
        style.setBottomBorderColor(new XSSFColor(borderColor));
        style.setBorderTop(CellStyle.BORDER_THIN);
        style.setTopBorderColor(new XSSFColor(borderColor));
        style.setBorderRight(CellStyle.BORDER_THIN);
        style.setRightBorderColor(new XSSFColor(borderColor));
        style.setBorderLeft(CellStyle.BORDER_THIN);
        style.setLeftBorderColor(new XSSFColor(borderColor));
        subjectCellStyles.put(subject, style);
    }

    Entry<String, List<JSONObject>>[] studentDataEntries = studentData.entrySet().toArray(new Entry[] {});
    Arrays.sort(studentDataEntries, Collections.reverseOrder(new Comparator<Entry<String, List<JSONObject>>>() {
        @Override
        public int compare(Entry<String, List<JSONObject>> arg0, Entry<String, List<JSONObject>> arg1) {
            return Integer.compare(arg0.getValue().size(), arg1.getValue().size());
        }
    }));

    for (Entry<String, List<JSONObject>> studentDataEntry : studentDataEntries) {
        Sheet sheet = workbook.createSheet(WorkbookUtil.createSafeSheetName(studentDataEntry.getKey()));
        Row row = sheet.createRow((short) 0);

        String[] columnNames = { "Name", "Grade", "N", "C", "M", "S" };
        for (int i = 0; i < columnNames.length; i++) {
            String columnName = columnNames[i];
            Cell cell = row.createCell(i);
            cell.setCellValue(columnName);
            cell.setCellStyle(boldStyle);
            CellUtil.setAlignment(cell, workbook, CellStyle.ALIGN_CENTER);
        }

        int longestNameLength = 7;
        int rowNum = 1;
        for (JSONObject student : studentDataEntry.getValue()) {
            try {
                row = sheet.createRow((short) rowNum);
                row.createCell(0).setCellValue(student.getString("name"));
                row.createCell(1).setCellValue(student.getInt("grade"));

                for (Subject subject : Subject.values()) {
                    String value = student.getBoolean(subject.toString()) ? "" : "X";
                    Cell cell = row.createCell(Arrays.asList(columnNames).indexOf(subject.toString()));
                    cell.setCellValue(value);
                    cell.setCellStyle(subjectCellStyles.get(subject));
                }

                if (student.getString("name").length() > longestNameLength) {
                    longestNameLength = student.getString("name").length();
                }

                rowNum++;
            } catch (JSONException e) {
                e.printStackTrace();
                resp.sendError(HttpServletResponse.SC_INTERNAL_SERVER_ERROR, e.toString());
                return;
            }
        }

        sheet.createFreezePane(0, 1, 0, 1);
        // sheet.autoSizeColumn((short) 0); Not supported by App Engine
        sheet.setColumnWidth((short) 0, (int) (256 * longestNameLength * 1.1));
    }

    Drive drive = new Drive.Builder(httpTransport, jsonFactory, credential)
            .setApplicationName("contestTabulation").build();

    File body = new File();
    body.setTitle(docName);
    body.setMimeType("application/vnd.google-apps.spreadsheet");

    ByteArrayOutputStream outStream = new ByteArrayOutputStream();
    workbook.write(outStream);
    ByteArrayInputStream inStream = new ByteArrayInputStream(outStream.toByteArray());
    InputStreamContent content = new InputStreamContent(
            "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", inStream);

    drive.files().insert(body, content).execute();
    workbook.close();
}

From source file:de.alpharogroup.export.excel.poi.ExcelPoiFactory.java

License:Open Source License

/**
 * Creates a new Sheet with the given name.
 * <p>/*ww  w  . j  a  v a2  s  .  com*/
 * Note that sheet name is Excel must not exceed 31 characters and must not contain any of the
 * any of the following characters:
 * <ul>
 * <li>0x0000</li>
 * <li>0x0003</li>
 * <li>colon (:)</li>
 * <li>backslash (\)</li>
 * <li>asterisk (*)</li>
 * <li>question mark (?)</li>
 * <li>forward slash (/)</li>
 * <li>opening square bracket ([)</li>
 * <li>closing square bracket (])</li>
 * </ul>
 * 
 * @param workbook
 *            the workbook
 * @param name
 *            the name
 * @return the Sheet
 */
public static Sheet newSheet(final Workbook workbook, final String name) {
    return workbook.createSheet(WorkbookUtil.createSafeSheetName(name));
}