org.apache.poi.ss.usermodel.Workbook#createSheet

Source File: FlatFileExtractor.java From Open-Lowcode with Eclipse Public License 2.0

6 votes

/**
 * Extracts to excel a tree of objects
 * 
 * @param objecttree object trees
 * @return the binary file
 */
public SFile extractToExcel(NodeTree<E> objecttree) {
	try {
		Workbook workbook = new XSSFWorkbook();
		Sheet sheet = workbook.createSheet("Export Data");
		loadWorkbook(sheet, objecttree);
		ByteArrayOutputStream documentinmemory = new ByteArrayOutputStream();
		workbook.write(documentinmemory);
		workbook.close();
		SFile returnresult = new SFile("OpenLowcodeExport-" + sdf.format(new Date()) + ".xlsx",
				documentinmemory.toByteArray());
		return returnresult;
	} catch (IOException e) {
		String exceptionstring = "Exception in extracting objects to array " + definition.getName()
				+ ", original IOException " + e.getMessage();
		logger.severe(exceptionstring);
		for (int i = 0; i < e.getStackTrace().length; i++) {
			logger.severe("    " + e.getStackTrace()[i]);
		}
		throw new RuntimeException(exceptionstring);
	}
}

Source File: PackageInfoSheetV2d1.java From tools with Apache License 2.0

6 votes

public static void create(Workbook wb, String sheetName) {
	int sheetNum = wb.getSheetIndex(sheetName);
	if (sheetNum >= 0) {
		wb.removeSheetAt(sheetNum);
	}
	Sheet sheet = wb.createSheet(sheetName);
	CellStyle headerStyle = AbstractSheet.createHeaderStyle(wb);
	CellStyle defaultStyle = AbstractSheet.createLeftWrapStyle(wb);
	Row row = sheet.createRow(0);
	for (int i = 0; i < HEADER_TITLES.length; i++) {
		sheet.setColumnWidth(i, COLUMN_WIDTHS[i]*256);
		sheet.setDefaultColumnStyle(i, defaultStyle);
		Cell cell = row.createCell(i);
		cell.setCellStyle(headerStyle);
		cell.setCellValue(HEADER_TITLES[i]);
	}
}

Source File: ReviewersSheet.java From tools with Apache License 2.0

6 votes

public static void create(Workbook wb, String sheetName) {
	int sheetNum = wb.getSheetIndex(sheetName);
	if (sheetNum >= 0) {
		wb.removeSheetAt(sheetNum);
	}
	Sheet sheet = wb.createSheet(sheetName);
	CellStyle headerStyle = AbstractSheet.createHeaderStyle(wb);
	CellStyle centerStyle = AbstractSheet.createCenterStyle(wb);
	CellStyle wrapStyle = AbstractSheet.createLeftWrapStyle(wb);
	Row row = sheet.createRow(0);
	for (int i = 0; i < HEADER_TITLES.length; i++) {
		sheet.setColumnWidth(i, COLUMN_WIDTHS[i]*256);
		if (LEFT_WRAP[i]) {
			sheet.setDefaultColumnStyle(i, wrapStyle);
		} else if (CENTER_NOWRAP[i]) {
			sheet.setDefaultColumnStyle(i, centerStyle);
		}
		Cell cell = row.createCell(i);
		cell.setCellStyle(headerStyle);
		cell.setCellValue(HEADER_TITLES[i]);
	}
}

Source File: JU_FontReport.java From hy.common.report with Apache License 2.0

6 votes

@Test
public void test_FontOne() throws IOException
{
    Workbook wb = new XSSFWorkbook();
    Sheet sheet = wb.createSheet("sheet 01");
    Row row = sheet.createRow(1);
    Font font = wb.createFont();
    font.setBold(true);
    font.setColor((short) 13);
    font.setFontHeightInPoints((short) 24);
    font.setFontName("宋体");
    CellStyle cellStyle = wb.createCellStyle();
    cellStyle.setFont(font);
    Cell cell = row.createCell(1);
    cell.setCellValue("这是测试字体格式的");
    cell.setCellStyle(cellStyle);
    FileOutputStream fileOutputStream = new FileOutputStream("D://font.xlsx");
    wb.write(fileOutputStream);
    wb.close();
}

Source File: PerFileSheet.java From tools with Apache License 2.0

6 votes

public static void create(Workbook wb, String sheetName) {
	int sheetNum = wb.getSheetIndex(sheetName);
	if (sheetNum >= 0) {
		wb.removeSheetAt(sheetNum);
	}
	Sheet sheet = wb.createSheet(sheetName);
	CellStyle headerStyle = AbstractSheet.createHeaderStyle(wb);
	CellStyle centerStyle = AbstractSheet.createCenterStyle(wb);
	CellStyle wrapStyle = AbstractSheet.createLeftWrapStyle(wb);
	Row row = sheet.createRow(0);
	for (int i = 0; i < HEADER_TITLES.length; i++) {
		sheet.setColumnWidth(i, COLUMN_WIDTHS[i]*256);
		if (LEFT_WRAP[i]) {
			sheet.setDefaultColumnStyle(i, wrapStyle);
		} else if (CENTER_NOWRAP[i]) {
			sheet.setDefaultColumnStyle(i, centerStyle);
		}
		Cell cell = row.createCell(i);
		cell.setCellStyle(headerStyle);
		cell.setCellValue(HEADER_TITLES[i]);
	}
}

Source File: RelationshipsSheet.java From tools with Apache License 2.0

6 votes

public static void create(Workbook wb, String sheetName) {
	int sheetNum = wb.getSheetIndex(sheetName);
	if (sheetNum >= 0) {
		wb.removeSheetAt(sheetNum);
	}
	Sheet sheet = wb.createSheet(sheetName);
	CellStyle headerStyle = AbstractSheet.createHeaderStyle(wb);
	CellStyle centerStyle = AbstractSheet.createCenterStyle(wb);
	CellStyle wrapStyle = AbstractSheet.createLeftWrapStyle(wb);
	Row row = sheet.createRow(0);
	for (int i = 0; i < HEADER_TITLES.length; i++) {
		sheet.setColumnWidth(i, COLUMN_WIDTHS[i]*256);
		if (LEFT_WRAP[i]) {
			sheet.setDefaultColumnStyle(i, wrapStyle);
		} else if (CENTER_NOWRAP[i]) {
			sheet.setDefaultColumnStyle(i, centerStyle);
		}
		Cell cell = row.createCell(i);
		cell.setCellStyle(headerStyle);
		cell.setCellValue(HEADER_TITLES[i]);
	}
}

Source File: OriginsSheetV1d2.java From tools with Apache License 2.0

6 votes

public static void create(Workbook wb, String sheetName) {
	int sheetNum = wb.getSheetIndex(sheetName);
	if (sheetNum >= 0) {
		wb.removeSheetAt(sheetNum);
	}
	
	CellStyle headerStyle = AbstractSheet.createHeaderStyle(wb);
	CellStyle centerStyle = AbstractSheet.createCenterStyle(wb);
	CellStyle wrapStyle = AbstractSheet.createLeftWrapStyle(wb);
	Sheet sheet = wb.createSheet(sheetName);
	Row row = sheet.createRow(0);
	for (int i = 0; i < HEADER_TITLES.length; i++) {
		sheet.setColumnWidth(i, COLUMN_WIDTHS[i]*256);
		if (LEFT_WRAP[i]) {
			sheet.setDefaultColumnStyle(i, wrapStyle);
		} else if (CENTER_NOWRAP[i]) {
			sheet.setDefaultColumnStyle(i, centerStyle);
		}
		Cell cell = row.createCell(i);
		cell.setCellStyle(headerStyle);
		cell.setCellValue(HEADER_TITLES[i]);
	}
	Row dataRow = sheet.createRow(1);
	Cell ssVersionCell = dataRow.createCell(SPREADSHEET_VERSION_COL);
	ssVersionCell.setCellValue(SPDXSpreadsheet.CURRENT_VERSION);
}

Source File: PackageInfoSheetV9d1.java From tools with Apache License 2.0

6 votes

public static void create(Workbook wb, String sheetName) {
	int sheetNum = wb.getSheetIndex(sheetName);
	if (sheetNum >= 0) {
		wb.removeSheetAt(sheetNum);
	}
	Sheet sheet = wb.createSheet(sheetName);
	CellStyle headerStyle = AbstractSheet.createHeaderStyle(wb);
	CellStyle defaultStyle = AbstractSheet.createLeftWrapStyle(wb);
	Row row = sheet.createRow(0);
	for (int i = 0; i < HEADER_TITLES.length; i++) {
		sheet.setColumnWidth(i, COLUMN_WIDTHS[i]*256);
		sheet.setDefaultColumnStyle(i, defaultStyle);
		Cell cell = row.createCell(i);
		cell.setCellStyle(headerStyle);
		cell.setCellValue(HEADER_TITLES[i]);
	}
}

Source File: ExportResponsesBean.java From sakai with Educational Community License v2.0

5 votes

protected Workbook getAsWorkbookTest(List<List<Object>> spreadsheetData) {
	Workbook wb = new HSSFWorkbook();
	Sheet sheet = wb.createSheet();
	Iterator<List<Object>> dataIter = spreadsheetData.iterator();

	// By convention, the first list in the list contains column headers.
	Row headerRow = sheet.createRow((short)0);
	List<Object> headerList = dataIter.next();
	for (short i = 0; i < headerList.size(); i++) {
		createCell(headerRow, i, null).setCellValue(headerList.get(i).toString());
	}
	short rowPos = 1;
	while (dataIter.hasNext()) {
		List<Object> rowData = dataIter.next();
		Row row = sheet.createRow(rowPos++);
		for (short i = 0; i < rowData.size(); i++) {
			Cell cell = createCell(row, i, null);
			Object data = rowData.get(i);
			if (data != null) {
				if (data instanceof Double) {
					cell.setCellValue(((Double)data).doubleValue());
				} 
				else {
					cell.setCellValue(data.toString());
				}
			}
		}
	}
	return wb;
}

Source File: Tests.java From kbase-doc with Apache License 2.0

5 votes

@Test
public void test() throws IOException {
    Workbook wb = new XSSFWorkbook();
    Sheet sheet = wb.createSheet("会计考试");
    addTitle(sheet);

    List<String> list = FileUtils.readLines(new File("E:\\会计成绩名单.txt"), "UTF-8");
    int i=0;
    for (String line : list){
        if (line.contains("麻城")){
            line = line.substring(1, line.length()-1).replace("\"", "");
            System.out.println(line);
            String[] arr = line.split(",");
            i++;
            Row row = sheet.createRow(i);
            row.createCell(0).setCellValue(arr[0]);
            row.createCell(1).setCellValue(arr[1]);
            row.createCell(2).setCellValue(arr[2]);
            row.createCell(3).setCellValue(arr[3]);
            row.createCell(4).setCellValue(arr[4]);
            row.createCell(5).setCellValue(arr[5]);
            row.createCell(6).setCellValue(arr[6]);
            row.createCell(7).setCellValue(arr[7]);
            row.createCell(8).setCellValue(arr[8]);
            row.createCell(9).setCellValue(arr[9]);
        }
    }
    System.out.println(i);
    //==============================================================


    try (OutputStream fileOut = new FileOutputStream("E://workbook.xlsx")) {
        wb.write(fileOut);
    }
}

Source File: AssetExportSMOImpl.java From MicroCommunity with Apache License 2.0

5 votes

/**
 * 获取 房屋信息
 *
 * @param componentValidateResult
 * @param workbook
 */
private void getRooms(IPageData pd, ComponentValidateResult componentValidateResult, Workbook workbook) {
    Sheet sheet = workbook.createSheet("房屋信息");
    Row row = sheet.createRow(0);
    row.createCell(0).setCellValue("房屋编号");
    row.createCell(1).setCellValue("楼栋编号");
    row.createCell(2).setCellValue("单元编号");
    row.createCell(3).setCellValue("房屋楼层");
    row.createCell(4).setCellValue("房屋户型");
    row.createCell(5).setCellValue("建筑面积");
    row.createCell(6).setCellValue("业主编号");

    //查询楼栋信息
    JSONArray rooms = this.getExistsRoom(pd, componentValidateResult);
    if(rooms == null){
        return;
    }
    for (int roomIndex = 0; roomIndex < rooms.size(); roomIndex++) {
        row = sheet.createRow(roomIndex + 1);
        row.createCell(0).setCellValue(rooms.getJSONObject(roomIndex).getString("roomNum"));
        row.createCell(1).setCellValue(rooms.getJSONObject(roomIndex).getString("floorNum"));
        row.createCell(2).setCellValue(rooms.getJSONObject(roomIndex).getString("unitNum"));
        row.createCell(3).setCellValue(rooms.getJSONObject(roomIndex).getString("layer"));
        row.createCell(4).setCellValue(rooms.getJSONObject(roomIndex).getString("section"));
        row.createCell(5).setCellValue(rooms.getJSONObject(roomIndex).getString("builtUpArea"));
        if ("2001".equals(rooms.getJSONObject(roomIndex).getString("state"))) { //已出售 时查询业主信息
            JSONArray ownerDtos = this.getExistsOwner(pd, componentValidateResult, rooms.getJSONObject(roomIndex).getString("roomId"));
            if (ownerDtos == null || ownerDtos.size() == 0) {
                row.createCell(6).setCellValue("");
                continue;
            }
            row.createCell(6).setCellValue(ownerDtos.getJSONObject(0).getString("ownerId"));
        }
    }
}

Source File: ExportResponsesBean.java From sakai with Educational Community License v2.0

5 votes

protected Workbook getAsWorkbookTest(List<List<Object>> spreadsheetData) {
	Workbook wb = new HSSFWorkbook();
	Sheet sheet = wb.createSheet();
	Iterator<List<Object>> dataIter = spreadsheetData.iterator();

	// By convention, the first list in the list contains column headers.
	Row headerRow = sheet.createRow((short)0);
	List<Object> headerList = dataIter.next();
	for (short i = 0; i < headerList.size(); i++) {
		createCell(headerRow, i, null).setCellValue(headerList.get(i).toString());
	}
	short rowPos = 1;
	while (dataIter.hasNext()) {
		List<Object> rowData = dataIter.next();
		Row row = sheet.createRow(rowPos++);
		for (short i = 0; i < rowData.size(); i++) {
			Cell cell = createCell(row, i, null);
			Object data = rowData.get(i);
			if (data != null) {
				if (data instanceof Double) {
					cell.setCellValue(((Double)data).doubleValue());
				} 
				else {
					cell.setCellValue(data.toString());
				}
			}
		}
	}
	return wb;
}

Source File: PoiExport.java From hrms with Apache License 2.0

4 votes

public InputStream exportExcel(String fileName, String[] headTitle,
		List<String> list) throws IOException {

	Workbook wb = new HSSFWorkbook();
	//FileOutputStream fileOut = new FileOutputStream(fileName);
	HSSFCellStyle style = (HSSFCellStyle) wb.createCellStyle();
	
	// 设置这些样式
	style.setFillForegroundColor(HSSFColor.SKY_BLUE.index);
	style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
	style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
	style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
	style.setBorderRight(HSSFCellStyle.BORDER_THIN);
	style.setBorderTop(HSSFCellStyle.BORDER_THIN);
	style.setAlignment(HSSFCellStyle.ALIGN_CENTER);

	// 生成一个字体
	HSSFFont font = (HSSFFont) wb.createFont();
	font.setColor(HSSFColor.VIOLET.index);
	font.setFontHeightInPoints((short) 12);
	font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);

	// 把字体应用到当前的样式
	style.setFont(font);
	// 创建工作簿
	Sheet sheet = wb.createSheet("sheet1");
	// sheet.setDefaultColumnWidth((short)15);

	// 创建头部
	Row row = sheet.createRow((short) 0);
	for (int i = 0; i < headTitle.length; i++) {
		row.createCell(i).setCellValue(headTitle[i]);
	}
	// 填充数据
	for (int i = 0; i < list.size(); i++) {
		Row r = sheet.createRow((short) (i + 1));
		String[] strArray = list.get(i).split(",");
		for (int j = 0; j < headTitle.length; j++) {
			r.createCell(j).setCellValue(strArray[j]);
			if (strArray[j].length()>3) {
				sheet.setColumnWidth((short)j, 5000);
			}
		}
		
	}
	ByteArrayOutputStream baos = new ByteArrayOutputStream();
	try {
		wb.write(baos);
	} catch (IOException e) {
		e.printStackTrace();
	}
	byte[] ba = baos.toByteArray();
	ByteArrayInputStream excelStream = new ByteArrayInputStream(ba);
	return excelStream;

}

Source File: AssetExportSMOImpl.java From MicroCommunity with Apache License 2.0

4 votes

/**
 * 获取车位信息
 *
 * @param workbook
 */
private void getParkingSpaces(IPageData pd, ComponentValidateResult componentValidateResult, Workbook workbook) {
    Sheet sheet = workbook.createSheet("车位信息");
    Row row = sheet.createRow(0);
    row.createCell(0).setCellValue("车位编码");
    row.createCell(1).setCellValue("车位类型");
    row.createCell(2).setCellValue("面积");
    row.createCell(3).setCellValue("业主编号");
    row.createCell(4).setCellValue("车牌号");
    row.createCell(5).setCellValue("车品牌");
    row.createCell(6).setCellValue("车类型");
    row.createCell(7).setCellValue("颜色");
    row.createCell(8).setCellValue("出租还是出售（H出租S出售）");

    //查询楼栋信息
    JSONArray parkSpaces = this.getExistsParkSpace(pd, componentValidateResult);

    if(parkSpaces == null){
        return;
    }


    for (int psIndex = 0; psIndex < parkSpaces.size(); psIndex++) {
        row = sheet.createRow(psIndex + 1);
        row.createCell(0).setCellValue(parkSpaces.getJSONObject(psIndex).getString("num"));
        row.createCell(1).setCellValue(parkSpaces.getJSONObject(psIndex).getString("typeCd"));
        row.createCell(2).setCellValue(parkSpaces.getJSONObject(psIndex).getString("area"));
        if (!"H".equals(parkSpaces.getJSONObject(psIndex).getString("state"))) { //已出售 时查询业主信息
            JSONArray ownerCars = this.getExistsParkingSpaceCar(pd, componentValidateResult, parkSpaces.getJSONObject(psIndex).getString("psId"));
            if (ownerCars == null || ownerCars.size() == 0) {
                row.createCell(3).setCellValue("");
                row.createCell(4).setCellValue("");
                row.createCell(5).setCellValue("");
                row.createCell(6).setCellValue("");
                row.createCell(7).setCellValue("");
                continue;
            }
            row.createCell(3).setCellValue(ownerCars.getJSONObject(0).getString("ownerId"));
            row.createCell(4).setCellValue(ownerCars.getJSONObject(0).getString("carNum"));
            row.createCell(5).setCellValue(ownerCars.getJSONObject(0).getString("carBrand"));
            row.createCell(6).setCellValue(ownerCars.getJSONObject(0).getString("carType"));
            row.createCell(7).setCellValue(ownerCars.getJSONObject(0).getString("carColor"));
        }
        row.createCell(8).setCellValue(parkSpaces.getJSONObject(psIndex).getString("state"));

    }
}

Source File: WorkBookUtil.java From easyexcel with Apache License 2.0

4 votes

public static Sheet createSheet(Workbook workbook, String sheetName) {
    return workbook.createSheet(sheetName);
}

Source File: SpreadsheetDataFileWriterXlsx.java From sakai with Educational Community License v2.0

4 votes

private Workbook getAsWorkbook(List<List<Object>> spreadsheetData) {
	Workbook wb = new SXSSFWorkbook();
	Sheet sheet = wb.createSheet();
	CellStyle headerCs = wb.createCellStyle();
	Iterator<List<Object>> dataIter = spreadsheetData.iterator();
	
	// Set the header style
	headerCs.setBorderBottom(BorderStyle.THICK);
	headerCs.setFillBackgroundColor(IndexedColors.BLUE_GREY.getIndex());


	// Set the font
	CellStyle cellStyle = null;
	String fontName = ServerConfigurationService.getString("spreadsheet.font");
	if (fontName != null) {
		Font font = wb.createFont();
		font.setFontName(fontName);
		headerCs.setFont(font);
		cellStyle = wb.createCellStyle();
		cellStyle.setFont(font);
	}

	// By convention, the first list in the list contains column headers.
	Row headerRow = sheet.createRow((short)0);
	List<Object> headerList = dataIter.next();
	for (short i = 0; i < headerList.size(); i++) {
		Cell headerCell = createCell(headerRow, i);
		headerCell.setCellValue((String)headerList.get(i));
		headerCell.setCellStyle(headerCs);
		//TODO
		//sheet.autoSizeColumn(i);
	}
	
	short rowPos = 1;
	while (dataIter.hasNext()) {
		List<Object> rowData = dataIter.next();
		Row row = sheet.createRow(rowPos++);
		for (short i = 0; i < rowData.size(); i++) {
			Cell cell = createCell(row, i);
			Object data = rowData.get(i);
			if (data != null) {
				if (data instanceof Double) {
					cell.setCellValue(((Double)data).doubleValue());
				} else {
					cell.setCellValue(data.toString());
				}
				if (cellStyle != null) {
					cell.setCellStyle(cellStyle);
				}
			}
		}
	}
	
	return wb;
}

Source File: ActionExportAbnormalDetail.java From o2oa with GNU Affero General Public License v3.0

4 votes

private Workbook composeDetail(String fileName, String sheetName, List<AttendanceDetail> detailList) {
	AttendanceDetail attendanceDetail = null;
	AttendanceAppealInfo attendanceAppealInfo = null;
	
	Workbook wb = new HSSFWorkbook();
	Row row = null;
	if (detailList != null && detailList.size() > 0) {
		// 创建新的表格
		Sheet sheet = wb.createSheet(sheetName);
		
		// 先创建表头
		row = sheet.createRow(0);
		row.createCell(0).setCellValue("顶层组织名称");
		row.createCell(1).setCellValue("组织名称");
		row.createCell(2).setCellValue("员工姓名");
		row.createCell(3).setCellValue("打卡日期");
		row.createCell(4).setCellValue("异常原因");
		row.createCell(5).setCellValue("签到时间");
		row.createCell(6).setCellValue("签退时间");
		row.createCell(7).setCellValue("申诉原因");
		row.createCell(8).setCellValue("申诉具体原因");
		row.createCell(9).setCellValue("直接主管审批");

		for (int i = 0; i < detailList.size(); i++) {
			attendanceDetail = detailList.get(i);
			row = sheet.createRow(i + 1);
			row.createCell(0).setCellValue(attendanceDetail.getTopUnitName());
			row.createCell(1).setCellValue(attendanceDetail.getUnitName());
			row.createCell(2).setCellValue(attendanceDetail.getEmpName());
			row.createCell(3).setCellValue(attendanceDetail.getRecordDateString());
			if (attendanceDetail.getIsAbsent()) {
				row.createCell(4).setCellValue("缺勤");
			} else if (attendanceDetail.getIsLackOfTime()) {
				row.createCell(4).setCellValue("工时不足");
			} else if (attendanceDetail.getIsAbnormalDuty()) {
				row.createCell(4).setCellValue("异常打卡");
			} else if (attendanceDetail.getIsLate()) {
				row.createCell(4).setCellValue("迟到");
			} else {
				row.createCell(4).setCellValue("未知原因");
			}
			row.createCell(5).setCellValue(attendanceDetail.getOnDutyTime());
			row.createCell(6).setCellValue(attendanceDetail.getOffDutyTime());
			row.createCell(7).setCellValue(attendanceDetail.getAppealReason());
			if (attendanceDetail.getAppealStatus() != 0) {
				// 查询该条打卡信息的申诉信息
				try (EntityManagerContainer emc = EntityManagerContainerFactory.instance().create()) {
					Business business = new Business(emc);
					attendanceAppealInfo = business.getAttendanceAppealInfoFactory().get(attendanceDetail.getId());
				} catch (Exception e) {
					logger.info("系统在查询所有["+sheetName+"]非正常打卡记录时发生异常。" );
					e.printStackTrace();
				}
				if (attendanceAppealInfo != null) {
					row.createCell(8).setCellValue(attendanceAppealInfo.getAppealDescription());
					if (attendanceAppealInfo.getStatus() == 0) {
						row.createCell(9).setCellValue("未审批");
					} else if (attendanceAppealInfo.getStatus() == -1) {
						row.createCell(9).setCellValue("已审批未通过");
					}
				}
			}
		}
	}
	return wb;
}

Source File: AbstractExtractorTest.java From TomboloDigitalConnector with MIT License

4 votes

protected Workbook makeWorkbook(String sheetName, List<List<Object>> rowSpecs){
    Workbook workbook = new HSSFWorkbook();
    Sheet sheet = workbook.createSheet(sheetName);

    for (List<Object> rowSpec : rowSpecs) {
        int rowId = (sheet.getPhysicalNumberOfRows()==0)?0:sheet.getLastRowNum()+1;
        Row row = sheet.createRow(rowId);

        for (Object cellSpec : rowSpec) {
            // Note that sheet.getLastRowNum() and row.getLastCellNum() do not behave alike
            int cellId = (row.getPhysicalNumberOfCells()==0)?0:row.getLastCellNum();
            if (cellSpec == null) {
                row.createCell(cellId).setCellType(Cell.CELL_TYPE_BLANK);
                continue;
            }
            switch (cellSpec.getClass().getCanonicalName()) {
                case "java.lang.Integer":
                    row.createCell(cellId).setCellValue((Integer)cellSpec);
                    break;
                case "java.lang.String":
                    row.createCell(cellId).setCellValue((String)cellSpec);
                    break;
                case "java.lang.Double":
                    row.createCell(cellId).setCellValue((Double)cellSpec);
                    break;
                case "java.lang.Boolean":
                    row.createCell(cellId).setCellValue((Boolean)cellSpec);
                    break;
                case "java.util.Date":
                    row.createCell(cellId).setCellValue((Date)cellSpec);
                    break;
                case "java.util.Calendar":
                    row.createCell(cellId).setCellValue((Calendar)cellSpec);
                    break;
                case "org.apache.poi.ss.formula.Formula":
                    row.createCell(cellId).setCellType(Cell.CELL_TYPE_FORMULA);
                    Cell cell = row.getCell(row.getLastCellNum());
                    cell.setCellFormula(((Formula)cellSpec).toString());
                    break;
                default:
                    row.createCell(cellId).setCellType(Cell.CELL_TYPE_BLANK);
            }
        }
    }
    return workbook;
}

Source File: ActionExportHolidayDetail.java From o2oa with GNU Affero General Public License v3.0

4 votes

private Workbook composeDetail(String fileName, String sheetName, List<AttendanceSelfHoliday> holidayList) throws Exception {
	AttendanceSelfHoliday attendanceSelfHoliday = null;
	
	Workbook wb = new HSSFWorkbook();
	Row row = null;
	if (holidayList != null && holidayList.size() > 0) {
		// 创建新的表格
		Sheet sheet = wb.createSheet(sheetName);
		
		// 先创建表头
		row = sheet.createRow(0);
		row.createCell(0).setCellValue("顶层组织名称");
		row.createCell(1).setCellValue("组织名称");
		row.createCell(2).setCellValue("员工姓名");
		row.createCell(3).setCellValue("请假类型");
		row.createCell(4).setCellValue("开始时间");
		row.createCell(5).setCellValue("结束时间");
		row.createCell(6).setCellValue("请假天数");
		row.createCell(7).setCellValue("其他说明");

		if ( holidayList != null && holidayList.size() > 0 ) {
			logger.info("一共有"+holidayList.size()+"条请求记录可以输出。");					
			for (int i = 0; i < holidayList.size(); i++) {
				attendanceSelfHoliday = holidayList.get(i);
				if( attendanceSelfHoliday != null ){
					row = sheet.createRow(i + 1);
					row.createCell(0).setCellValue(attendanceSelfHoliday.getTopUnitName());
					row.createCell(1).setCellValue(attendanceSelfHoliday.getUnitName());
					row.createCell(2).setCellValue(attendanceSelfHoliday.getEmployeeName());
					row.createCell(3).setCellValue(attendanceSelfHoliday.getLeaveType());
					
					if( attendanceSelfHoliday.getStartTime() != null ){
						row.createCell(4).setCellValue( dateOperation.getDateStringFromDate( attendanceSelfHoliday.getStartTime(), "yyyy-MM-dd HH:mm:ss") );
					}else{
						row.createCell(4).setCellValue("");
					}
					if( attendanceSelfHoliday.getEndTime() != null ){
						row.createCell(5).setCellValue( dateOperation.getDateStringFromDate( attendanceSelfHoliday.getEndTime(), "yyyy-MM-dd HH:mm:ss") );
					}else{
						row.createCell(5).setCellValue("");
					}
					if( attendanceSelfHoliday.getLeaveDayNumber() != null ){
						row.createCell(6).setCellValue(attendanceSelfHoliday.getLeaveDayNumber());
					}else{
						row.createCell(6).setCellValue( "0" );
					}
					if( attendanceSelfHoliday.getDescription() != null ){
						row.createCell(7).setCellValue(attendanceSelfHoliday.getDescription());
					}
				}
			}
		}
	}
	return wb;
}

Source File: SpreadsheetDataFileWriterXls.java From sakai with Educational Community License v2.0

4 votes

private Workbook getAsWorkbook(List<List<Object>> spreadsheetData) {
	Workbook wb = new HSSFWorkbook();
	Sheet sheet = wb.createSheet();
	CellStyle headerCs = wb.createCellStyle();
	Iterator<List<Object>> dataIter = spreadsheetData.iterator();
	
	// Set the header style
	headerCs.setBorderBottom(BorderStyle.THICK);
	headerCs.setFillBackgroundColor(IndexedColors.BLUE_GREY.getIndex());
	// Set the font
	CellStyle cellStyle = null;
	String fontName = ServerConfigurationService.getString("spreadsheet.font");
	if (fontName != null) {
		Font font = wb.createFont();
		font.setFontName(fontName);
		headerCs.setFont(font);
		cellStyle = wb.createCellStyle();
		cellStyle.setFont(font);
	}

	// By convention, the first list in the list contains column headers.
	Row headerRow = sheet.createRow((short)0);
	List<Object> headerList = dataIter.next();
	for (short i = 0; i < headerList.size(); i++) {
		Cell headerCell = createCell(headerRow, i);
		headerCell.setCellValue((String)headerList.get(i));
		headerCell.setCellStyle(headerCs);
		sheet.autoSizeColumn(i);
	}
	
	short rowPos = 1;
	while (dataIter.hasNext()) {
		List<Object> rowData = dataIter.next();
		Row row = sheet.createRow(rowPos++);
		for (short i = 0; i < rowData.size(); i++) {
			Cell cell = createCell(row, i);
			Object data = rowData.get(i);
			if (data != null) {
				if (data instanceof Double) {
					cell.setCellValue(((Double)data).doubleValue());
				} else {
					cell.setCellValue(data.toString());
				}
				if (cellStyle != null) {
					cell.setCellStyle(cellStyle);
				}
			}
		}
	}
	
	return wb;
}

Java Code Examples for org.apache.poi.ss.usermodel.Workbook#createSheet()