Java Code Examples for jxl.write.WritableWorkbook#createSheet()

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);
    }
}
 

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);
    }
}
 

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);
    }
}
 

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);
    }
}
 

/**
 * Gets the file bytes.
 *
 * @param issueDetails the issue details
 * @param methodType the method type
 * @param columns the columns
 * @return the file bytes
 * @throws WriteException the write exception
 * @throws IOException Signals that an I/O exception has occurred.
 */
private byte[] getFileBytes(JsonArray issueDetails, String methodType, List<String>columns) throws WriteException, IOException {
    ByteArrayOutputStream baos = new ByteArrayOutputStream();
    WritableWorkbook workbook = Workbook.createWorkbook(baos);
    WritableSheet writablesheet = workbook.createSheet(methodType, 0);
    formatWritableSheet(writablesheet,columns);
    addWritableSheetCells(writablesheet, issueDetails,columns);
    workbook.write();
    workbook.close();
    return baos.toByteArray();
}
 

public java.io.InputStream write( List<WorkItem> list) throws IOException, WriteException {
    java.io.OutputStream os = new java.io.ByteArrayOutputStream() ;
    WorkbookSettings wbSettings = new WorkbookSettings();

    wbSettings.setLocale(new Locale("en", "EN"));

    // Create a Workbook - pass the OutputStream
    WritableWorkbook workbook = Workbook.createWorkbook(os, wbSettings);
    workbook.createSheet("Work Item Report", 0);
    WritableSheet excelSheet = workbook.getSheet(0);
    createLabel(excelSheet) ;
    int size = createContent(excelSheet, list);

    // Close the workbook
    workbook.write();
    workbook.close();

    // Get an inputStram that represents the Report
    java.io.ByteArrayOutputStream stream = new java.io.ByteArrayOutputStream();
    stream = (java.io.ByteArrayOutputStream)os;
    byte[] myBytes = stream.toByteArray();
    java.io.InputStream is = new java.io.ByteArrayInputStream(myBytes) ;

    return is ;
}
 

protected static void jxl_buildStatSheets(WritableWorkbook wb, String basePath, PerformanceMetric metric, int statIndex) {
    String metricPath = basePath + metric + "/";
    String testMetricPath = metricPath + testLabel + "/";

    WritableSheet testSheet = wb.createSheet(metric+"Test", wb.getNumberOfSheets());
    String testCSV = testMetricPath+ fileNameBuild_avgsFile(testLabel, metric);
    jxl_copyCSVIntoSheet(testSheet, testCSV);

    WritableSheet summarySheet = wb.createSheet(metric+"TestSigDiffs", wb.getNumberOfSheets());
    String summaryCSV = testMetricPath + fileNameBuild_summaryFile(testLabel, metric);
    jxl_copyCSVIntoSheet(summarySheet, summaryCSV);
}
 

protected static void jxl_buildStatSheets_timings(WritableWorkbook wb, String basePath, PerformanceMetric metric, int statIndex, String evalSet, String timingType) {
    // ************* the difference: timings folder assumed instead of going by the specific metric name
    //i.e Timings/TRAIN/TrainTimings and Timings/TEST/TestTimings    
    //instead of TrainTimings/TRAIN/TrainTimings ... 
    String metricPath = basePath + "Timings"+timingType+"/" + evalSet + "/";

    WritableSheet avgsSheet = wb.createSheet(metric.name, wb.getNumberOfSheets());
    String testCSV = metricPath + fileNameBuild_avgsFile(evalSet, metric);
    jxl_copyCSVIntoSheet(avgsSheet, testCSV);

    WritableSheet summarySheet = wb.createSheet(metric.name+"SigDiffs", wb.getNumberOfSheets());
    String summaryCSV = metricPath + fileNameBuild_summaryFile(evalSet, metric);
    jxl_copyCSVIntoSheet(summarySheet, summaryCSV);

}
 

@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++;
	}
}
 

/**
 *
 * @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);
}
 

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;
}
 

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();
}
 

protected StreamInfo getStreamInfo(ActionMapping mapping, ActionForm form,
		HttpServletRequest request, HttpServletResponse response) throws Exception {
	ProjectObject mProject = SessionManager.getProjectObject(request);
	IUserSession mUserSession = (IUserSession) request.getSession().getAttribute("UserSession");
	// get all stories
	ArrayList<StoryObject> mStories = new ProductBacklogLogic(mProject).getStories();

	// set the path of the temp file
	File mTempFile = File.createTempFile("ezScrumExcel", Long.toString(System.nanoTime()));
	String mPath = mTempFile.getAbsolutePath();

	try {
		// 建立 Excel
		WritableWorkbook workbook = Workbook.createWorkbook(new File(mPath));
		WritableSheet sheet = workbook.createSheet("BACKLOG", 0);
		// delicate to excel handler
		ExcelHandler handler = new ExcelHandler(mProject.getId(), sheet);
		handler.save(mStories);

		// 寫入excel
		workbook.write();
		// 關閉excel的連結
		workbook.close();
	} catch (Exception e) {
		e.printStackTrace();
	}
	response.setHeader("Content-disposition", "inline; filename=" + mProject.getName() + "_ProductBacklog.xls");

	String contentType = "application/vnd.ms-excel";
	File file = new File(mPath);

	ScrumRole sr = new ScrumRoleLogic().getScrumRole(mProject, mUserSession.getAccount());
	if (sr.getAccessProductBacklog()) {
		return new FileStreamInfo(contentType, file);
	} else {
		return null;
	}
}
 

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();
}
 

public PanExcelWriter(int year) throws Exception {
	
	WritableWorkbook workbook = null;

	
	PanchangList pl = new PanchangList(year);
	
	WritableSheet sheet= null;
	
	int sheetNo = 0;
	
	for(Panchang p:pl){
		
		Calendar cal = p.getDate();
		
		if (cal.get(Calendar.DATE) == 1) {
			
			workbook = Workbook.createWorkbook(new File(PARENT_DIR, "pan_" + monFormat.format(cal.getTime()) + ".xls" )); 
			sheetNo = 0;
		}
		
		sheet = workbook.createSheet(dateFormat.format(cal.getTime()), sheetNo++);
		
		sheet.setColumnView(0, 10);
		sheet.setColumnView(1, 30);
		
		TableData<MapTableRow> data = p.getPanchangTableData();
		
		
		for(int i = 0; i < data.getRowCount(); i++){

			TableRowData row = data.getRow(i);
			
			String key = row.getColumnData(AstrosoftTableColumn.Key).toString();
			if (!key.equals("Auspicious Time") && !key.equals("Rahu Kala") && !key.equals("Yama Kanda") ){
		
				if (key.indexOf("End") >= 0){
					key = key.substring(0, key.length()-9);
				}
				writeRow(sheet, i, key, row.getColumnData(AstrosoftTableColumn.Value).toString());
			}
		}
	
		
		if (cal.get(Calendar.DATE) == cal.getActualMaximum(Calendar.DATE)){
			workbook.write(); 
			workbook.close(); 
		}
	}
	
	
}