List of usage examples for org.apache.poi.hssf.usermodel HSSFWorkbook HSSFWorkbook
public HSSFWorkbook()
From source file:com.ferid.app.classroom.statistics.StatisticsFragment.java
License:Apache License
/** * Converts all attendances into excel format *//*from ww w . j a v a2 s . com*/ private void convertToExcel() { //create workbook Workbook wb = new HSSFWorkbook(); for (Classroom classroom : classroomArrayList) { //each sheet //create sheet Sheet sheet = wb.createSheet(classroom.getName()); //header HashMap<String, Integer> date_column_map = new HashMap<>(); ArrayList<String> dates = new ArrayList<>(); int rowNumber = 0; int colNumber = 1; Row row = sheet.createRow(rowNumber); //dates columns for (Attendance attendance : attendanceArrayList) { if (classroom.getId() == attendance.getClassroomId() && !dates.contains(attendance.getDateTime())) { Cell cellDate = row.createCell(colNumber); cellDate.setCellStyle(ExcelStyleManager.getHeaderCellStyle(wb)); cellDate.setCellValue(attendance.getDateTime()); dates.add(attendance.getDateTime()); date_column_map.put(attendance.getDateTime(), colNumber); //set width of the dates columns sheet.setColumnWidth(colNumber, getResources().getInteger(R.integer.statistics_excel_column_width_dates)); colNumber++; } } //set width of the students column //it is always the first column sheet.setColumnWidth(0, getResources().getInteger(R.integer.statistics_excel_column_width_students)); //students list at the left column HashMap<Integer, Integer> student_row_map = new HashMap<>(); ArrayList<Integer> studentIds = new ArrayList<>(); rowNumber = 1; for (Attendance attendance : attendanceArrayList) { if (classroom.getId() == attendance.getClassroomId() && !studentIds.contains(attendance.getStudentId())) { //another student row = sheet.createRow(rowNumber); Cell cellStudent = row.createCell(0); cellStudent.setCellStyle(ExcelStyleManager.getHeaderCellStyle(wb)); cellStudent.setCellValue(attendance.getStudentName()); studentIds.add(attendance.getStudentId()); student_row_map.put(attendance.getStudentId(), rowNumber); rowNumber++; } } //now get column number from date columns //and get row number from student rows //match row-column pair and print into cell for (Attendance attendance : attendanceArrayList) { if (classroom.getId() == attendance.getClassroomId()) { rowNumber = student_row_map.get(attendance.getStudentId()); colNumber = date_column_map.get(attendance.getDateTime()); row = sheet.getRow(rowNumber); Cell cellPresence = row.createCell(colNumber); cellPresence.setCellStyle(ExcelStyleManager.getContentCellStyle(wb)); cellPresence.setCellValue(attendance.getPresent()); } } } if (classroomArrayList.size() > 0) { writeIntoFile(wb); } swipeRefreshLayout.setRefreshing(false); }
From source file:com.firstonesoft.poi.TimesheetDemo.java
License:Apache License
public static void main(String[] args) throws Exception { Workbook wb;/* w w w . j av a2 s . co m*/ if (args.length > 0 && args[0].equals("-xls")) wb = new HSSFWorkbook(); else wb = new XSSFWorkbook(); Map<String, CellStyle> styles = createStyles(wb); Sheet sheet = wb.createSheet("Timesheet"); PrintSetup printSetup = sheet.getPrintSetup(); printSetup.setLandscape(true); sheet.setFitToPage(true); sheet.setHorizontallyCenter(true); //title row Row titleRow = sheet.createRow(0); titleRow.setHeightInPoints(45); Cell titleCell = titleRow.createCell(0); titleCell.setCellValue("Resumen de Horas"); titleCell.setCellStyle(styles.get("title")); sheet.addMergedRegion(CellRangeAddress.valueOf("$A$1:$L$1")); //header row Row headerRow = sheet.createRow(1); headerRow.setHeightInPoints(40); Cell headerCell; for (int i = 0; i < titles.length; i++) { headerCell = headerRow.createCell(i); headerCell.setCellValue(titles[i]); headerCell.setCellStyle(styles.get("header")); } int rownum = 2; for (int i = 0; i < 10; i++) { Row row = sheet.createRow(rownum++); for (int j = 0; j < titles.length; j++) { Cell cell = row.createCell(j); if (j == 9) { //the 10th cell contains sum over week days, e.g. SUM(C3:I3) String ref = "C" + rownum + ":I" + rownum; cell.setCellFormula("SUM(" + ref + ")"); cell.setCellStyle(styles.get("formula")); } else if (j == 11) { cell.setCellFormula("J" + rownum + "-K" + rownum); cell.setCellStyle(styles.get("formula")); } else { cell.setCellStyle(styles.get("cell")); } } } //row with totals below Row sumRow = sheet.createRow(rownum++); sumRow.setHeightInPoints(35); Cell cell; cell = sumRow.createCell(0); cell.setCellStyle(styles.get("formula")); cell = sumRow.createCell(1); cell.setCellValue("Total Hrs:"); cell.setCellStyle(styles.get("formula")); for (int j = 2; j < 12; j++) { cell = sumRow.createCell(j); String ref = (char) ('A' + j) + "3:" + (char) ('A' + j) + "12"; cell.setCellFormula("SUM(" + ref + ")"); if (j >= 9) cell.setCellStyle(styles.get("formula_2")); else cell.setCellStyle(styles.get("formula")); } rownum++; sumRow = sheet.createRow(rownum++); sumRow.setHeightInPoints(25); cell = sumRow.createCell(0); cell.setCellValue("Total Regular Hours"); cell.setCellStyle(styles.get("formula")); cell = sumRow.createCell(1); cell.setCellFormula("L13"); cell.setCellStyle(styles.get("formula_2")); sumRow = sheet.createRow(rownum++); sumRow.setHeightInPoints(25); cell = sumRow.createCell(0); cell.setCellValue("Total Overtime Hours"); cell.setCellStyle(styles.get("formula")); cell = sumRow.createCell(1); cell.setCellFormula("K13"); cell.setCellStyle(styles.get("formula_2")); //set sample data for (int i = 0; i < sample_data.length; i++) { Row row = sheet.getRow(2 + i); for (int j = 0; j < sample_data[i].length; j++) { if (sample_data[i][j] == null) continue; if (sample_data[i][j] instanceof String) { row.getCell(j).setCellValue((String) sample_data[i][j]); } else { row.getCell(j).setCellValue((Double) sample_data[i][j]); } } } //finally set column widths, the width is measured in units of 1/256th of a character width sheet.setColumnWidth(0, 30 * 256); //30 characters wide for (int i = 2; i < 9; i++) { sheet.setColumnWidth(i, 6 * 256); //6 characters wide } sheet.setColumnWidth(10, 10 * 256); //10 characters wide // Write the output to a file String file = "D://timesheet.xls"; if (wb instanceof XSSFWorkbook) file += "x"; FileOutputStream out = new FileOutputStream(file); wb.write(out); out.close(); }
From source file:com.fjn.helper.common.io.file.office.excel.ExcelMgr.java
License:Apache License
/** * ?WorkBook/*from ww w .j a va2 s . co m*/ * * @param type * @return */ public static Workbook createWorkBook(Excel.ExcelType type) { Workbook wb = null; switch (type) { case XLSX: wb = new XSSFWorkbook(); break; case XLS: wb = new HSSFWorkbook(); break; default: wb = new SXSSFWorkbook(); break; } return wb; }
From source file:com.fjn.helper.common.io.file.office.excel.ExcelUtil.java
License:Apache License
/** * @param excelFormat// w ww. j ava2 s . co m * ???<br> * XLS 97-2003?Excel<br> * XLSX 2007?2010?Excel<br> * SXLSX Excel * @return */ public static Workbook newWorkbook(String excelFormat) { Workbook wb = null; if (XLSX.equals(excelFormat)) { wb = new XSSFWorkbook(); } else if (XLS.equals(excelFormat)) { wb = new HSSFWorkbook(); } else { wb = new SXSSFWorkbook(); } return wb; }
From source file:com.frameworkset.platform.sanylog.util.POIExcelUtil.java
License:Open Source License
/** * Excel Workbook?./* ww w . ja v a2 s. c om*/ * * @param colDesc 17"?:user_id,??:user_name,:type_name" * @param dataList * @return * @author gw_liaozh * @throws InvocationTargetException * @throws IllegalAccessException * @throws IllegalArgumentException */ public static HSSFWorkbook createHSSFWorkbook(String colDesc, List<?> dataList) throws IllegalArgumentException, IllegalAccessException, InvocationTargetException { //???17 //TODO: ? List<String> colTitleList = getColumnTitleList(colDesc); List<String> colFieldList = getColumnFieldList(colDesc); HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet sheet = wb.createSheet(); HSSFFont font = getBaseFont(wb); HSSFCellStyle headCellStyle = getHeadCellStyle(wb, font); //? CellStyle dateCellStyle = getDateTimeCellStyle(wb); //CellStyle strCellStyle = getStringCellStyle(wb); //??17 HSSFDataValidationHelper dvHelper = new HSSFDataValidationHelper(sheet); Map<String, Class<?>> fieldTypeMap = new HashMap<String, Class<?>>(); // HSSFRow titleRow = sheet.createRow(0); for (int i = 0; i < colTitleList.size(); i++) { HSSFCell cell = titleRow.createCell(i); cell.setCellStyle(headCellStyle); cell.setCellType(HSSFCell.CELL_TYPE_STRING); cell.setCellValue(colTitleList.get(i)); } //?17 for (int i = 0; i < dataList.size(); i++) { Object obj = dataList.get(i); HSSFRow row = sheet.createRow(i + 1); for (int j = 0; j < colFieldList.size(); j++) { String fieldName = colFieldList.get(j); HSSFCell cell = row.createCell(j); if (obj == null) { continue; } Object value = BeanConvertUtil.getProperty(obj, fieldName); //ClassInfo classInfo = ClassUtil.getClassInfo(obj.getClass()); //Object value = classInfo.getPropertyDescriptor(fieldName).getValue(obj); if (value == null) { continue; } //?? if (value instanceof Number) { cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC); cell.setCellValue(((Number) value).doubleValue()); } else if (value instanceof Date) { cell.setCellStyle(dateCellStyle); cell.setCellValue((Date) value); } else { cell.setCellType(HSSFCell.CELL_TYPE_STRING); //cell.setCellStyle(strCellStyle); cell.setCellValue(value.toString()); } fieldTypeMap.put(fieldName, value.getClass()); } } //?? for (int i = 0; i < colFieldList.size(); i++) { String fieldName = colFieldList.get(i); Class<?> fieldClass = fieldTypeMap.get(fieldName); if (fieldClass == null) { continue; } CellRangeAddressList range = new CellRangeAddressList(1, 65535, i, i); DataValidationConstraint constraint = null; if (Integer.class.isAssignableFrom(fieldClass)) { constraint = dvHelper.createIntegerConstraint(DataValidationConstraint.OperatorType.NOT_BETWEEN, "0", "-1"); sheet.addValidationData(dvHelper.createValidation(constraint, range)); } else if (Number.class.isAssignableFrom(fieldClass)) { constraint = dvHelper.createNumericConstraint(DataValidationConstraint.ValidationType.DECIMAL, DataValidationConstraint.OperatorType.NOT_BETWEEN, "0", "-1"); sheet.addValidationData(dvHelper.createValidation(constraint, range)); } else if (Date.class.isAssignableFrom(fieldClass)) { constraint = dvHelper.createDateConstraint(DataValidationConstraint.OperatorType.NOT_BETWEEN, "0000-01-02", "0000-01-01", "yyyy-MM-dd"); sheet.addValidationData(dvHelper.createValidation(constraint, range)); } } // for (int i = 0; i < colTitleList.size(); i++) { //?? //sheet.autoSizeColumn(i); } return wb; }
From source file:com.github.camaral.sheeco.Sheeco.java
License:Apache License
/** * Creates a spreadsheet with only the headers as described by the set of * payloadClass, but no data. For each payloadClass a sheet will be created. * /*from w w w. ja v a2 s. c o m*/ * @param stream * The output which will receive the content of the spreadsheet * @param payloadClasses * Java types annotated with {@link SpreadsheetPayload} * */ public void toSpreadsheet(final OutputStream stream, final Set<Class<? extends Object>> payloadClasses) throws IOException { if (payloadClasses.size() < 1) { throw new IllegalArgumentException("At least one payload class must be present"); } final HSSFWorkbook wb = new HSSFWorkbook(); final CreationHelper creationHelper = wb.getCreationHelper(); for (final Class<? extends Object> clazz : payloadClasses) { final Payload<? extends Object> payload = new Payload<>(clazz); final Sheet sheet = createSheet(wb, payload.getName()); final Row row = createRow(sheet); createCells(payload, row, creationHelper); } wb.write(stream); }
From source file:com.github.camellabs.iot.cloudlet.geofencing.service.DefaultRouteService.java
License:Apache License
@Override public byte[] exportRoutes(String client, String format) { try {/*from w w w . j a v a2 s.c o m*/ List<List<String>> exportedRoutes = exportRoutes(client); Workbook workbook = new HSSFWorkbook(); Sheet sheet = workbook.createSheet("Routes report for " + client); for (int i = 0; i < exportedRoutes.size(); i++) { Row row = sheet.createRow(i); for (int j = 0; j < exportedRoutes.get(i).size(); j++) { row.createCell(j).setCellValue(exportedRoutes.get(i).get(j)); } } ByteArrayOutputStream xlsBytes = new ByteArrayOutputStream(); workbook.write(xlsBytes); xlsBytes.close(); return xlsBytes.toByteArray(); } catch (IOException e) { throw new RuntimeException(e); } }
From source file:com.github.crab2died.ExcelUtils.java
License:Open Source License
private Workbook exportExcelNoTemplateHandler(List<?> data, Class clazz, boolean isWriteHeader, String sheetName, boolean isXSSF) throws Excel4JException { Workbook workbook;/*from w ww .j ava2 s . c o m*/ if (isXSSF) { workbook = new XSSFWorkbook(); } else { workbook = new HSSFWorkbook(); } generateSheet(workbook, data, clazz, isWriteHeader, sheetName); return workbook; }
From source file:com.github.crab2died.ExcelUtils.java
License:Open Source License
private Workbook exportExcelNoTemplateHandler(List<NoTemplateSheetWrapper> sheetWrappers, boolean isXSSF) throws Excel4JException { Workbook workbook;// w w w . j av a 2 s . c o m if (isXSSF) { workbook = new XSSFWorkbook(); } else { workbook = new HSSFWorkbook(); } // sheet for (NoTemplateSheetWrapper sheet : sheetWrappers) { generateSheet(workbook, sheet.getData(), sheet.getClazz(), sheet.isWriteHeader(), sheet.getSheetName()); } return workbook; }
From source file:com.github.crab2died.ExcelUtils.java
License:Open Source License
private Workbook exportExcelBySimpleHandler(List<?> data, List<String> header, String sheetName, boolean isXSSF) { Workbook workbook;//from w ww . j a v a 2 s . com if (isXSSF) { workbook = new XSSFWorkbook(); } else { workbook = new HSSFWorkbook(); } // ?sheet this.generateSheet(workbook, data, header, sheetName); return workbook; }