Java Code Examples for jxl.write.WritableSheet#addCell()

The following examples show how to use jxl.write.WritableSheet#addCell() . You can vote up the ones you like or vote down the ones you don't like, and go to the original project or source file by following the links above each example. You may check out the related API usage on the sidebar.
Example 1
Source File: DownloadFileService.java    From pacbot with Apache License 2.0 8 votes vote down vote up
/**
 * Adds the cell.
 *
 * @param writablesheet the writablesheet
 * @param issueDetail the issue detail
 * @param cellFormat the cell format
 * @param columnIndex the column index
 * @param columns the columns
 * @throws WriteException the write exception
 */
private void addCell(WritableSheet writablesheet, JsonObject issueDetail, WritableCellFormat cellFormat,
        int columnIndex,List<String>columns) throws WriteException {
    int rowIndex = 0;

    for (String clm : columns) {
        if (issueDetail.has(clm)) {
            if (issueDetail.get(clm).isJsonNull()) {
                writablesheet.addCell(new Label(rowIndex++, 1 + columnIndex, "No Data", cellFormat));
            } else {
                writablesheet.addCell(new Label(rowIndex++, 1 + columnIndex, issueDetail.get(clm).getAsString(),
                        cellFormat));
            }

        }
    }
}
 
Example 2
Source File: DownloadFileService.java    From pacbot with Apache License 2.0 8 votes vote down vote up
/**
 * Format writable sheet.
 *
 * @param writablesheet the writable sheet
 * @param columns the columns
 * @throws WriteException the write exception
 */
private void formatWritableSheet(WritableSheet writablesheet,List<String>columns) throws WriteException {
    WritableFont cellFonts = new WritableFont(WritableFont.createFont("Calibri"), ELEVEN, WritableFont.BOLD, false,
            UnderlineStyle.NO_UNDERLINE, jxl.format.Colour.BLACK);
    WritableCellFormat cellFormats = new WritableCellFormat(cellFonts);
    cellFormats.setBorder(Border.ALL, BorderLineStyle.THIN);
    cellFormats.setBackground(Colour.WHITE);
    int labelIndex = 0;
    for (String clm : columns) {
        writablesheet.addCell(new Label(labelIndex, 0, clm.replaceAll("_", ""), cellFormats));
        CellView cell = writablesheet.getColumnView(labelIndex);
        cell.setAutosize(true);
        writablesheet.setColumnView(labelIndex, cell);
        labelIndex++;
    }
}
 
Example 3
Source File: ResultExport.java    From yeti with MIT License 8 votes vote down vote up
public static void ExportForwardLookupsToXLS(String filename, List<Object> data) throws IOException {
    try {
        WritableFont titleFont = new WritableFont(WritableFont.ARIAL, 10, WritableFont.BOLD, true);
        WritableCellFormat titleformat = new WritableCellFormat(titleFont);
        WritableWorkbook workbook = Workbook.createWorkbook(new File(filename));
        WritableSheet sheet = workbook.createSheet("Forward lookups", 0);
        sheet.addCell(new Label(0, 0, "Domain name", titleformat));
        sheet.addCell(new Label(1, 0, "Host name", titleformat));
        sheet.addCell(new Label(2, 0, "IP address", titleformat));
        sheet.addCell(new Label(3, 0, "Type", titleformat));
        int nextRow = 1;
        Iterator i = data.iterator();
        while (i.hasNext()) {
            ForwardLookupResult res = (ForwardLookupResult) i.next();
            sheet.addCell(new Label(0, nextRow, res.getDomainName()));
            sheet.addCell(new Label(1, nextRow, res.getHostName()));
            sheet.addCell(new Label(2, nextRow, res.getIpAddress()));
            sheet.addCell(new Label(3, nextRow, res.getLookupType()));
            nextRow++;
        }
        workbook.write();
        workbook.close();
    } catch (WriteException ex) {
        Logger.getLogger("resultExport.ExportForwardLookupsToXLS").log(Level.SEVERE, null, ex);
    }
}
 
Example 4
Source File: ResultExport.java    From yeti with MIT License 8 votes vote down vote up
public static void ExportTLDToXLS(String filename, ArrayList<Object> data) throws IOException {
    try {
        WritableFont titleFont = new WritableFont(WritableFont.ARIAL, 10, WritableFont.BOLD, true);
        WritableCellFormat titleformat = new WritableCellFormat(titleFont);
        WritableWorkbook workbook = Workbook.createWorkbook(new File(filename));
        WritableSheet sheet = workbook.createSheet("TLD Expand", 0);
        sheet.addCell(new Label(0, 0, "Domain name", titleformat));
        sheet.addCell(new Label(1, 0, "Name server", titleformat));
        sheet.addCell(new Label(2, 0, "Admin name", titleformat));
        sheet.addCell(new Label(3, 0, "Registrant", titleformat));
        int nextRow = 1;
        Iterator i = data.iterator();
        while (i.hasNext()) {
            DomainResult res = (DomainResult) i.next();
            sheet.addCell(new Label(0, nextRow, res.getDomainName()));
            sheet.addCell(new Label(1, nextRow, res.getNameServer()));
            sheet.addCell(new Label(2, nextRow, res.getAdminName()));
            sheet.addCell(new Label(3, nextRow, res.getRegistrant()));
            nextRow++;
        }
        workbook.write();
        workbook.close();
    } catch (WriteException ex) {
        Logger.getLogger("resultExport.ExportForwardLookupsToXLS").log(Level.SEVERE, null, ex);
    }
}
 
Example 5
Source File: ResultExport.java    From yeti with MIT License 8 votes vote down vote up
public static void ExportCertToXLS(String filename, ArrayList<Object> data) throws IOException {
    try {
        WritableFont titleFont = new WritableFont(WritableFont.ARIAL, 10, WritableFont.BOLD, true);
        WritableCellFormat titleformat = new WritableCellFormat(titleFont);
        WritableWorkbook workbook = Workbook.createWorkbook(new File(filename));
        WritableSheet sheet = workbook.createSheet("SSLCert CN", 0);
        sheet.addCell(new Label(0, 0, "IP address", titleformat));
        sheet.addCell(new Label(1, 0, "Host name", titleformat));
        sheet.addCell(new Label(2, 0, "Domain name", titleformat));
        int nextRow = 1;
        Iterator i = data.iterator();
        while (i.hasNext()) {
            CertResult res = (CertResult) i.next();
            sheet.addCell(new Label(0, nextRow, res.getIpAddress()));
            sheet.addCell(new Label(1, nextRow, res.getHostName()));
            sheet.addCell(new Label(2, nextRow, res.getDomainName()));
            nextRow++;
        }
        workbook.write();
        workbook.close();
    } catch (WriteException ex) {
        Logger.getLogger("resultExport.ExportForwardLookupsToXLS").log(Level.SEVERE, null, ex);
    }
}
 
Example 6
Source File: ResultExport.java    From yeti with MIT License 7 votes vote down vote up
public static void ExportReverseToXLS(String filename, ArrayList<Object> data) throws IOException {
    try {
        WritableFont titleFont = new WritableFont(WritableFont.ARIAL, 10, WritableFont.BOLD, true);
        WritableCellFormat titleformat = new WritableCellFormat(titleFont);
        WritableWorkbook workbook = Workbook.createWorkbook(new File(filename));
        WritableSheet sheet = workbook.createSheet("Bing IP search", 0);
        sheet.addCell(new Label(0, 0, "IP address", titleformat));
        sheet.addCell(new Label(1, 0, "Domain name", titleformat));
        sheet.addCell(new Label(2, 0, "Host name", titleformat));
        int nextRow = 1;
        Iterator i = data.iterator();
        while (i.hasNext()) {
            ReverseLookupResult res = (ReverseLookupResult) i.next();
            sheet.addCell(new Label(0, nextRow, res.getIpAddress()));
            sheet.addCell(new Label(1, nextRow, res.getDomainName()));
            sheet.addCell(new Label(2, nextRow, res.getHostName()));
            nextRow++;
        }
        workbook.write();
        workbook.close();
    } catch (WriteException ex) {
        Logger.getLogger("resultExport.ExportReverseToXLS").log(Level.SEVERE, null, ex);
    }
}
 
Example 7
Source File: SpreadsheetDataSetConsumer.java    From morf with Apache License 2.0 5 votes vote down vote up
/**
 * Create the index worksheet.
 *
 * <p>This also creates links back to the index in each of the worksheets.</p>
 */
public void createIndex() {
  WritableSheet sheet = workbook.createSheet(spreadsheetifyName("Index"), 0);
  createTitle(sheet, "Index");

  try {
    // Create links for each worksheet, apart from the first sheet which is the
    // index we're currently creating
    final String[] names = workbook.getSheetNames();
    for (int currentSheet = 1; currentSheet < names.length; currentSheet++) {
      // Create the link from the index to the table's worksheet
      WritableHyperlink link = new WritableHyperlink(0, currentSheet - 1 + NUMBER_OF_ROWS_IN_TITLE, names[currentSheet], workbook.getSheet(currentSheet), 0, 0);
      sheet.addHyperlink(link);

      //Add the filename in column B (stored in cell B2 of each sheet)
      String fileName = workbook.getSheet(currentSheet).getCell(1, 1).getContents();
      Label fileNameLabel = new Label(1, currentSheet - 1 + NUMBER_OF_ROWS_IN_TITLE, fileName);
      WritableFont fileNameFont = new WritableFont(WritableFont.ARIAL,10,WritableFont.NO_BOLD,false,UnderlineStyle.NO_UNDERLINE,Colour.BLACK);
      WritableCellFormat fileNameFormat = new WritableCellFormat(fileNameFont);
      fileNameLabel.setCellFormat(fileNameFormat);
      sheet.addCell(fileNameLabel);

      // Create the link back to the index
      link = new WritableHyperlink(0, 1, "Back to index", sheet, 0, currentSheet + NUMBER_OF_ROWS_IN_TITLE - 1);
      workbook.getSheet(currentSheet).addHyperlink(link);
      //Set column A of each sheet to be wide enough to show "Back to index"
      workbook.getSheet(currentSheet).setColumnView(0, 13);
    }

    // Make Column A fairly wide to show tab names and hide column B
    sheet.setColumnView(0, 35);
    sheet.setColumnView(1, 0);

  } catch (Exception e) {
    throw new RuntimeException(e);
  }
}
 
Example 8
Source File: HRExcelJExcelBuilder.java    From Spring-MVC-Blueprints with MIT License 5 votes vote down vote up
@Override
protected void buildExcelDocument(Map<String, Object> model,
		WritableWorkbook workbook, HttpServletRequest request,
		HttpServletResponse response) throws Exception {
	
	// get data model which is passed by the Spring container
	List<HrmsLogin> users = (List<HrmsLogin>) model.get("allUsers");
	
	// create a new Excel sheet
	WritableSheet sheet = workbook.createSheet("Master List of Users", 0);
	
	// create header row
	sheet.addCell(new Label(0, 0, "Employee ID"));
	sheet.addCell(new Label(1, 0, "Username"));
	sheet.addCell(new Label(2, 0, "Password"));
	sheet.addCell(new Label(3, 0, "Role"));
	
	
	// create data rows
	int rowCount = 1;
	
	for (HrmsLogin user : users) {
		sheet.addCell(new Label(0, rowCount, user.getHrmsEmployeeDetails().getEmpId()+""));
		sheet.addCell(new Label(1, rowCount, user.getUsername()));
		sheet.addCell(new Label(2, rowCount, user.getPassword()));
		sheet.addCell(new Label(3, rowCount, user.getRole()));
		
		rowCount++;
	}
}
 
Example 9
Source File: CreateFilePane.java    From pattypan with MIT License 5 votes vote down vote up
/**
 *
 * @param workbook
 * @throws WriteException
 */
private void createTemplateSheet(WritableWorkbook workbook) throws WriteException {
  WritableSheet templateSheet = workbook.createSheet("Template", 1);
  templateSheet.addCell(new Label(0, 0, "'" + Session.WIKICODE));
  //                                    ^^
  // leading apostrophe prevents turning wikitext into formula in Excel

  autoSizeColumn(0, templateSheet);
}
 
Example 10
Source File: ExcelUtils.java    From jshERP with GNU General Public License v3.0 5 votes vote down vote up
public static File exportObjects(String fileName, String[] names,
		String title, List<String[]> objects) throws Exception {
	File excelFile = new File("fileName.xls");
	WritableWorkbook wtwb = Workbook.createWorkbook(excelFile);
	WritableSheet sheet = wtwb.createSheet(title, 0);
	sheet.getSettings().setDefaultColumnWidth(20);
	WritableFont wfont = new WritableFont(WritableFont.createFont("楷书"), 15);
	WritableCellFormat format = new WritableCellFormat(wfont);
	WritableFont wfc = new WritableFont(WritableFont.ARIAL, 20,
			WritableFont.BOLD, false, UnderlineStyle.NO_UNDERLINE,
			jxl.format.Colour.BLACK);
	WritableCellFormat wcfFC = new WritableCellFormat(wfc);
	wcfFC.setAlignment(Alignment.CENTRE);
	wcfFC.setVerticalAlignment(VerticalAlignment.CENTRE);
	// CellView cellView = new CellView();
	// cellView.setAutosize(true); //设置自动大小
	format.setAlignment(Alignment.LEFT);
	format.setVerticalAlignment(VerticalAlignment.TOP);
	sheet.mergeCells(0, 0, names.length - 1, 0);
	sheet.addCell(new Label(0, 0, title, wcfFC));
	int rowNum = 2;
	for (int i = 0; i < names.length; i++) {
		sheet.addCell(new Label(i, 1, names[i], format));
	}
	for (int j = 0; j < objects.size(); j++) {
		String[] obj = objects.get(j);
		for (int h = 0; h < obj.length; h++) {
			sheet.addCell(new Label(h, rowNum, obj[h], format));
		}
		rowNum = rowNum + 1;

	}
	wtwb.write();
	wtwb.close();
	return excelFile;
}
 
Example 11
Source File: ExcelUtils.java    From jshERP with GNU General Public License v3.0 5 votes vote down vote up
public static String createTempFile(String[] names, String title, List<String[]> objects) throws Exception {
	File excelFile = File.createTempFile(System.currentTimeMillis() + "", ".xls");
	WritableWorkbook wtwb = Workbook.createWorkbook(excelFile);
	WritableSheet sheet = wtwb.createSheet(title, 0);
	sheet.getSettings().setDefaultColumnWidth(20);
	WritableFont wfont = new WritableFont(WritableFont.createFont("楷书"), 15);
	WritableCellFormat format = new WritableCellFormat(wfont);
	WritableFont wfc = new WritableFont(WritableFont.ARIAL, 20,
			WritableFont.BOLD, false, UnderlineStyle.NO_UNDERLINE,
			jxl.format.Colour.BLACK);
	WritableCellFormat wcfFC = new WritableCellFormat(wfc);
	wcfFC.setAlignment(Alignment.CENTRE);
	wcfFC.setVerticalAlignment(VerticalAlignment.CENTRE);
	// CellView cellView = new CellView();
	// cellView.setAutosize(true); //设置自动大小
	format.setAlignment(Alignment.LEFT);
	format.setVerticalAlignment(VerticalAlignment.TOP);
	sheet.mergeCells(0, 0, names.length - 1, 0);
	sheet.addCell(new Label(0, 0, title, wcfFC));
	int rowNum = 2;
	for (int i = 0; i < names.length; i++) {
		sheet.addCell(new Label(i, 1, names[i], format));
	}
	for (int j = 0; j < objects.size(); j++) {
		String[] obj = objects.get(j);
		for (int h = 0; h < obj.length; h++) {
			sheet.addCell(new Label(h, rowNum, obj[h], format));
		}
		rowNum = rowNum + 1;
	}
	wtwb.write();
	wtwb.close();
	return excelFile.getName();
}
 
Example 12
Source File: TableOutputter.java    From morf with Apache License 2.0 4 votes vote down vote up
/**
 * @param workSheet to add the help to
 * @param table to fetch metadata from
 * @param startRow to start adding rows at
 * @param helpTextRowNumbers - map to insert row numbers for each help field into
 * @return the index of the next row to use
 * @throws WriteException if any of the writes to workSheet failed
 */
private int outputHelp(WritableSheet workSheet, Table table, final int startRow, final Map<String, Integer> helpTextRowNumbers) throws WriteException {
  int currentRow = startRow;

  // Title for the descriptions
  Label dataLabel = new Label(0, currentRow, "Column Descriptions");
  dataLabel.setCellFormat(getBoldFormat());
  workSheet.addCell(dataLabel);
  currentRow++;

  int currentColumn = 0;

  for (Column column : table.columns()) {
    if (!column.getName().equals("id") && !column.getName().equals("version")) {
      // Field name to go with the description
      Label fieldName = new Label(0, currentRow, spreadsheetifyName(column.getName()));
      fieldName.setCellFormat(getBoldFormat());
      workSheet.addCell(fieldName);

      // The type/width
      String typeString = column.getType() + "(" + column.getWidth() + (column.getScale() == 0 ? "" : "," + column.getScale()) + ")";
      Label fieldType = new Label(1, currentRow, typeString);
      fieldType.setCellFormat(getStandardFormat());
      workSheet.addCell(fieldType);

      // The default
      String defaultValue = additionalSchemaData.columnDefaultValue(table, column.getName());
      Label fieldDefault = new Label(2, currentRow, defaultValue);
      fieldDefault.setCellFormat(getStandardFormat());
      workSheet.addCell(fieldDefault);

      // The field documentation
      workSheet.mergeCells(3, currentRow, 12, currentRow);
      String documentation = additionalSchemaData.columnDocumentation(table, column.getName());
      Label documentationLabel = new Label(3, currentRow, documentation);
      WritableCellFormat format = new WritableCellFormat(getStandardFormat());
      format.setWrap(true);
      format.setVerticalAlignment(VerticalAlignment.TOP);
      documentationLabel.setCellFormat(format);
      workSheet.addCell(documentationLabel);

      //If we've exceed the maximum number of columns - then output truncated warnings
      if(currentColumn >= MAX_EXCEL_COLUMNS) {
        Label truncatedWarning = new Label(13, currentRow, "[TRUNCATED]");
        truncatedWarning.setCellFormat(getBoldFormat());
        workSheet.addCell(truncatedWarning);
      }

      // We are aiming for 150px. 1px is 15 Excel "Units"
      workSheet.setRowView(currentRow, 150 * 15);

      // Remember at what row we created the help text for this column
      helpTextRowNumbers.put(column.getName(), currentRow);

      currentRow++;
      currentColumn++;
    }

  }

  // Group all the help rows together
  workSheet.setRowGroup(startRow + 1, currentRow - 1, true);

  // Some extra blank space for neatness
  currentRow++;

  return currentRow;
}
 
Example 13
Source File: ExcelUtils.java    From jshERP with GNU General Public License v3.0 4 votes vote down vote up
public static String createCheckRandomTempFile(String[] names, String title, List<String[]> objects,Map<String,String> infoMap) throws Exception {
	File excelFile = File.createTempFile(System.currentTimeMillis() + "", ".xls");
	WritableWorkbook wtwb = Workbook.createWorkbook(excelFile);
	WritableSheet sheet = wtwb.createSheet(title, 0);
	sheet.getSettings().setDefaultColumnWidth(20);
	WritableFont wfont = new WritableFont(WritableFont.createFont("楷书"), 14);

	WritableCellFormat format = new WritableCellFormat(wfont);
	format.setBorder(Border.ALL, BorderLineStyle.THIN);
	format.setAlignment(Alignment.CENTRE);
	format.setVerticalAlignment(VerticalAlignment.CENTRE);

	WritableFont wfc = new WritableFont(WritableFont.ARIAL, 20,
			WritableFont.BOLD, false, UnderlineStyle.NO_UNDERLINE,
			jxl.format.Colour.BLACK);
	WritableCellFormat wcfFC = new WritableCellFormat(wfc);
	wcfFC.setAlignment(Alignment.LEFT);
	wcfFC.setVerticalAlignment(VerticalAlignment.CENTRE);

	WritableFont nameWfc = new WritableFont(WritableFont.ARIAL, 14,
			WritableFont.BOLD, false, UnderlineStyle.NO_UNDERLINE,
			jxl.format.Colour.BLACK);
	WritableCellFormat nameFormat = new WritableCellFormat(nameWfc);
	nameFormat.setBorder(Border.ALL, BorderLineStyle.THIN);
	nameFormat.setAlignment(Alignment.CENTRE);
	nameFormat.setVerticalAlignment(VerticalAlignment.CENTRE);

	WritableCellFormat infoFormat = new WritableCellFormat(wfont);
	infoFormat.setAlignment(Alignment.LEFT);
	infoFormat.setVerticalAlignment(VerticalAlignment.CENTRE);


	sheet.mergeCells(0, 0, names.length - 1, 0);
	sheet.addCell(new Label(0, 0, infoMap.get("title"), wcfFC));

	sheet.addCell(new Label(0, 2, infoMap.get("info"), infoFormat));
	sheet.addCell(new Label(2, 2, infoMap.get("dvrnvr"), infoFormat));
	sheet.addCell(new Label(4, 2, infoMap.get("char"), infoFormat));
	sheet.addCell(new Label(0, 3, infoMap.get("infoPercent"), infoFormat));
	sheet.addCell(new Label(2, 3, infoMap.get("dvrnvrPercent"), infoFormat));
	sheet.addCell(new Label(4, 3, infoMap.get("charPercent"), infoFormat));

	int rowNum = 5;
	for (int i = 0; i < names.length; i++) {
		sheet.addCell(new Label(i, 4, names[i], nameFormat));
	}
	for (int j = 0; j < objects.size(); j++) {
		String[] obj = objects.get(j);
		for (int h = 0; h < obj.length; h++) {
			sheet.addCell(new Label(h, rowNum, obj[h], format));
		}
		rowNum = rowNum + 1;
	}
	wtwb.write();
	wtwb.close();
	return excelFile.getName();
}