List of usage examples for org.apache.poi.xssf.usermodel XSSFWorkbook createSheet
@Override
public XSSFSheet createSheet()
From source file:minor.Bill.java
private void writetoexcel() { XSSFWorkbook wb = new XSSFWorkbook(); XSSFSheet ws = wb.createSheet(); //load data to treemap TreeMap<String, Object[]> data = new TreeMap<>(); //add column headers data.put("-1", new Object[] { model.getColumnName(0), model.getColumnName(1), model.getColumnName(2), model.getColumnName(3), model.getColumnName(4) }); //add rows and cells for (int i = 0; i < model.getRowCount(); i++) { data.put(Integer.toString(i), new Object[] { getcellvalue(i, 0), getcellvalue(i, 1), getcellvalue(i, 2), getcellvalue(i, 3), getcellvalue(i, 4) }); }/* w ww. j av a 2s . co m*/ //write to excel Set<String> ids = data.keySet(); XSSFRow row; int rowID = 0; for (String key : ids) { row = ws.createRow(rowID++); //get data as per key Object[] values = data.get(key); int cellID = 0; for (Object O : values) { XSSFCell cell = row.createCell(cellID++); cell.setCellValue(O.toString()); } } //write to filesystem try { FileOutputStream fos = new FileOutputStream(new File("E:/excel/bill.xlsx")); wb.write(fos); fos.close(); } catch (Exception ex) { ex.printStackTrace(); JOptionPane.showMessageDialog(null, ex); } }
From source file:minor.dbook.java
private void writetoexcel() { XSSFWorkbook wb = new XSSFWorkbook(); XSSFSheet ws = wb.createSheet(); //load data to treemap TreeMap<String, Object[]> data = new TreeMap<>(); //add column headers data.put("-1", new Object[] { model.getColumnName(0), model.getColumnName(1), model.getColumnName(2), model.getColumnName(3), model.getColumnName(4) }); //add rows and cells for (int i = 0; i < model.getRowCount(); i++) { data.put(Integer.toString(i), new Object[] { getcellvalue(i, 0), getcellvalue(i, 1), getcellvalue(i, 2), getcellvalue(i, 3), getcellvalue(i, 4) }); }//from www. j a va 2s .c o m //write to excel Set<String> ids = data.keySet(); XSSFRow row; int rowID = 0; for (String key : ids) { row = ws.createRow(rowID++); //get data as per key Object[] values = data.get(key); int cellID = 0; for (Object O : values) { XSSFCell cell = row.createCell(cellID++); cell.setCellValue(O.toString()); } } //write to filesystem try { FileOutputStream fos = new FileOutputStream(new File("E:/excel/daybook.xlsx")); wb.write(fos); fos.close(); } catch (Exception ex) { ex.printStackTrace(); JOptionPane.showMessageDialog(null, ex); } }
From source file:minor.UpdateMenu.java
private void writetoexcel() { XSSFWorkbook wb = new XSSFWorkbook(); XSSFSheet ws = wb.createSheet(); //load data to treemap TreeMap<String, Object[]> data = new TreeMap<>(); //add column headers data.put("-1", new Object[] { model.getColumnName(0), model.getColumnName(1), model.getColumnName(2) }); //add rows and cells for (int i = 0; i < model.getRowCount(); i++) { data.put(Integer.toString(i), new Object[] { getcellvalue(i, 0), getcellvalue(i, 1), getcellvalue(i, 2) }); }/*from w ww . j av a 2 s.co m*/ //write to excel Set<String> ids = data.keySet(); XSSFRow row; int rowID = 0; for (String key : ids) { row = ws.createRow(rowID++); //get data as per key Object[] values = data.get(key); int cellID = 0; for (Object O : values) { XSSFCell cell = row.createCell(cellID++); cell.setCellValue(O.toString()); } } //write to filesystem try { FileOutputStream fos = new FileOutputStream(new File("E:/excel/menurecords.xlsx")); wb.write(fos); fos.close(); } catch (Exception ex) { ex.printStackTrace(); JOptionPane.showMessageDialog(null, ex); } }
From source file:minor.UpdateRecords.java
private void writetoexcel() { XSSFWorkbook wb = new XSSFWorkbook(); XSSFSheet ws = wb.createSheet(); //load data to treemap TreeMap<String, Object[]> data = new TreeMap<>(); //add column headers data.put("-1", new Object[] { model.getColumnName(0), model.getColumnName(1), model.getColumnName(2), model.getColumnName(3), model.getColumnName(4), model.getColumnName(5), model.getColumnName(6), model.getColumnName(7), model.getColumnName(8), model.getColumnName(9) }); //add rows and cells for (int i = 0; i < model.getRowCount(); i++) { data.put(Integer.toString(i), new Object[] { getcellvalue(i, 0), getcellvalue(i, 1), getcellvalue(i, 2), getcellvalue(i, 3), getcellvalue(i, 4), getcellvalue(i, 5), getcellvalue(i, 6), getcellvalue(i, 7), getcellvalue(i, 8), getcellvalue(i, 9) }); }/* w w w . ja va 2 s . c o m*/ //write to excel Set<String> ids = data.keySet(); XSSFRow row; int rowID = 0; for (String key : ids) { row = ws.createRow(rowID++); //get data as per key Object[] values = data.get(key); int cellID = 0; for (Object O : values) { XSSFCell cell = row.createCell(cellID++); cell.setCellValue(O.toString()); } } //write to filesystem try { FileOutputStream fos = new FileOutputStream(new File("E:/excel/employeerecords.xlsx")); wb.write(fos); fos.close(); } catch (Exception ex) { ex.printStackTrace(); JOptionPane.showMessageDialog(null, ex); } }
From source file:no.hild1.excelsplit.ES.java
private void handleRow(Row row, int j, Row header, Map<String, XSSFWorkbook> header2types) throws IOException { int HEADER1 = 0, HEADER2 = 1, HEADER3 = 2, HEADER4 = 3; String header2forthisrow = text(row, HEADER2); XSSFWorkbook w = null; Sheet s = null;/* w ww.ja va 2 s .c o m*/ Row r = null; if (!header2types.containsKey(header2forthisrow)) { w = new XSSFWorkbook(); s = w.createSheet(); r = s.createRow(0); // insert "header" into "r" somehow header2types.put(header2forthisrow, w); } else { w = header2types.get(header2forthisrow); s = w.getSheetAt(0); } r = s.createRow(s.getLastRowNum() + 1); // insert data "row" into "r" somehow }
From source file:org.apache.fineract.infrastructure.dataexport.helper.XlsFileHelper.java
License:Apache License
public static void createFile(final SqlRowSet sqlRowSet, final File file, final HashMap<Long, CodeValueData> codeValueMap, final HashMap<Long, AppUserData> appUserMap, final DataExportCoreTable coreTable) { try {//from w w w . j ava 2 s . co m final SqlRowSetMetaData sqlRowSetMetaData = sqlRowSet.getMetaData(); final int columnCount = sqlRowSetMetaData.getColumnCount(); // Create a new spreadsheet workbook XSSFWorkbook workbook = new XSSFWorkbook(); // Create a blank sheet for the workbook XSSFSheet sheet = workbook.createSheet(); // create a new cell style object XSSFCellStyle cellStyle = workbook.createCellStyle(); // create a new data format object XSSFDataFormat dataFormat = workbook.createDataFormat(); int rowIndex = 0; int columnIndex = 0; Row row = sheet.createRow(rowIndex++); for (int i = 1; i <= columnCount; i++) { // create a new cell for each columns for the header row Cell cell = row.createCell(columnIndex++); // get the column label of the dataset String columnLabel = DataExportUtils .createHumanReadableTableColumnLabel(sqlRowSetMetaData.getColumnLabel(i), coreTable); // set the value of the cell cell.setCellValue(WordUtils.capitalize(columnLabel)); } while (sqlRowSet.next()) { columnIndex = 0; row = sheet.createRow(rowIndex++); for (int i = 1; i <= columnCount; i++) { Cell cell = row.createCell(columnIndex++); String columnTypeName = sqlRowSetMetaData.getColumnTypeName(i); MysqlDataType mysqlDataType = MysqlDataType.newInstance(columnTypeName); String columnValue = sqlRowSet.getString(i); String columnName = sqlRowSetMetaData.getColumnName(i); // replace code value id with the code value name AbstractMap.SimpleEntry<String, MysqlDataType> columnValueDataType = DataExportUtils .replaceCodeValueIdWithValue(codeValueMap, columnName, columnValue, mysqlDataType); // update the column value columnValue = columnValueDataType.getKey(); // update the data type mysqlDataType = columnValueDataType.getValue(); // replace app user id with respective username columnValueDataType = DataExportUtils.replaceAppUserIdWithUserName(appUserMap, columnName, columnValue, mysqlDataType); // update the column value columnValue = columnValueDataType.getKey(); // update the data type mysqlDataType = columnValueDataType.getValue(); if (columnValue != null) { switch (mysqlDataType.getCategory()) { case NUMERIC: // TINYINT(1) is also treated as an alias for a BOOL in MySQL in certain versions of the JDBC connector, option tinyInt1isBit // See: http://stackoverflow.com/questions/16798744/why-does-tinyint1-function-as-a-boolean-but-int1-does-not/35488212#35488212 if (mysqlDataType.equals(MysqlDataType.TINYINT) && sqlRowSetMetaData.getPrecision(i) == 1 && (columnValue.equals("true") || columnValue.equals("false"))) { // Handle the cell as string, it is already a casted boolean: cell.setCellType(Cell.CELL_TYPE_STRING); cell.setCellValue(columnValue); } else { double numberAsDouble = Double.parseDouble(columnValue); cell.setCellType(Cell.CELL_TYPE_NUMERIC); cell.setCellValue(numberAsDouble); } break; case DATE_TIME: DateFormat dateFormat; Date date; switch (mysqlDataType) { case DATE: case DATETIME: String mysqlDateFormat = "yyyy-MM-dd"; String excelDateFormat = "MM/DD/YYYY"; if (mysqlDataType.equals(MysqlDataType.DATETIME)) { mysqlDateFormat = "yyyy-MM-dd HH:mm:ss"; excelDateFormat = "MM/DD/YYYY HH:MM:SS"; } dateFormat = new SimpleDateFormat(mysqlDateFormat); date = dateFormat.parse(columnValue); cellStyle.setDataFormat(dataFormat.getFormat(excelDateFormat)); cell.setCellValue(date); cell.setCellType(Cell.CELL_TYPE_NUMERIC); cell.setCellStyle(cellStyle); break; default: cell.setCellValue(columnValue); break; } break; default: cell.setCellType(Cell.CELL_TYPE_STRING); cell.setCellValue(columnValue); break; } } else { cell.setCellValue(columnValue); } } } //Write the workbook in file system FileOutputStream fileOutputStream = new FileOutputStream(file); workbook.write(fileOutputStream); fileOutputStream.close(); } catch (Exception exception) { exception.printStackTrace(); } }
From source file:org.mifosplatform.infrastructure.dataexport.helper.XlsFileHelper.java
License:Mozilla Public License
public static void createFile(final SqlRowSet sqlRowSet, final File file, final HashMap<Long, CodeValueData> codeValueMap, final HashMap<Long, AppUserData> appUserMap, final DataExportCoreTable coreTable) { try {//from www .j av a 2 s .c o m final SqlRowSetMetaData sqlRowSetMetaData = sqlRowSet.getMetaData(); final int columnCount = sqlRowSetMetaData.getColumnCount(); // Create a new spreadsheet workbook XSSFWorkbook workbook = new XSSFWorkbook(); // Create a blank sheet for the workbook XSSFSheet sheet = workbook.createSheet(); // create a new cell style object XSSFCellStyle cellStyle = workbook.createCellStyle(); // create a new data format object XSSFDataFormat dataFormat = workbook.createDataFormat(); int rowIndex = 0; int columnIndex = 0; Row row = sheet.createRow(rowIndex++); for (int i = 1; i <= columnCount; i++) { // create a new cell for each columns for the header row Cell cell = row.createCell(columnIndex++); // get the column label of the dataset String columnLabel = DataExportUtils .createHumanReadableTableColumnLabel(sqlRowSetMetaData.getColumnLabel(i), coreTable); // set the value of the cell cell.setCellValue(WordUtils.capitalize(columnLabel)); } while (sqlRowSet.next()) { columnIndex = 0; row = sheet.createRow(rowIndex++); for (int i = 1; i <= columnCount; i++) { Cell cell = row.createCell(columnIndex++); String columnTypeName = sqlRowSetMetaData.getColumnTypeName(i); MysqlDataType mysqlDataType = MysqlDataType.newInstance(columnTypeName); String columnValue = sqlRowSet.getString(i); String columnName = sqlRowSetMetaData.getColumnName(i); // replace code value id with the code value name AbstractMap.SimpleEntry<String, MysqlDataType> columnValueDataType = DataExportUtils .replaceCodeValueIdWithValue(codeValueMap, columnName, columnValue, mysqlDataType); // update the column value columnValue = columnValueDataType.getKey(); // update the data type mysqlDataType = columnValueDataType.getValue(); // replace app user id with respective username columnValueDataType = DataExportUtils.replaceAppUserIdWithUserName(appUserMap, columnName, columnValue, mysqlDataType); // update the column value columnValue = columnValueDataType.getKey(); // update the data type mysqlDataType = columnValueDataType.getValue(); if (columnValue != null) { switch (mysqlDataType.getCategory()) { case NUMERIC: // TINYINT(1), BIT(1), etc are also treated as an alias for a BOOL in MySQL in certain versions of the JDBC connector, option tinyInt1isBit // See: http://stackoverflow.com/questions/16798744/why-does-tinyint1-function-as-a-boolean-but-int1-does-not/35488212#35488212 if (sqlRowSetMetaData.getPrecision(i) == 1 && (columnValue.equals("true") || columnValue.equals("false"))) { // Handle the cell as string, it is already a casted boolean: cell.setCellType(Cell.CELL_TYPE_STRING); cell.setCellValue(columnValue); } else { double numberAsDouble = Double.parseDouble(columnValue); cell.setCellType(Cell.CELL_TYPE_NUMERIC); cell.setCellValue(numberAsDouble); } break; case DATE_TIME: DateFormat dateFormat; Date date; switch (mysqlDataType) { case DATE: case DATETIME: String mysqlDateFormat = "yyyy-MM-dd"; String excelDateFormat = "MM/DD/YYYY"; if (mysqlDataType.equals(MysqlDataType.DATETIME)) { mysqlDateFormat = "yyyy-MM-dd HH:mm:ss"; excelDateFormat = "MM/DD/YYYY HH:MM:SS"; } dateFormat = new SimpleDateFormat(mysqlDateFormat); date = dateFormat.parse(columnValue); cellStyle.setDataFormat(dataFormat.getFormat(excelDateFormat)); cell.setCellValue(date); cell.setCellType(Cell.CELL_TYPE_NUMERIC); cell.setCellStyle(cellStyle); break; default: cell.setCellValue(columnValue); break; } break; default: cell.setCellType(Cell.CELL_TYPE_STRING); cell.setCellValue(columnValue); break; } } else { cell.setCellValue(columnValue); } } } //Write the workbook in file system FileOutputStream fileOutputStream = new FileOutputStream(file); workbook.write(fileOutputStream); fileOutputStream.close(); } catch (Exception exception) { exception.printStackTrace(); } }
From source file:org.openelis.bean.DataViewReportBean.java
License:Open Source License
/** * Creates and returns a workbook that gets converted to an Excel file; each * row in the workbook shows fields from some part of a sample and analytes * and values/*from w ww. jav a 2s . c om*/ * * @param results * the list of VOs containing result info to be shown * @param auxiliary * the list of VOs containing aux data info to be shown * @param noResAux * the list of VOs containing info to be shown when both results and * aux data are excluded * @param testAnaResMap * the map containing result analytes and values selected by the * user; if an analyte or value is not in the map, the result is not * shown * @param auxFieldValMap * the map containing aux data analytes and values selected by the * user; if an analyte or value is not in the map, the aux data is * not shown * @param moduleName * the name of a security module for the logged in user; the module's * clause is used to restrict the fetched data to specific records * e.g. organizations * @param showReportableColumnsOnly * if true, only reportable column analytes are shown * @param headers * the list of labels for the column headers * @param data * the VO containing the user's choices for the data shown e.g. the * meta keys for selected columns and "include" and "exclude" flags * @param smMap * the map that provides the data for the columns belonging to * various parts of a sample e.g. domain, organization, project etc. * @param status * the percent completion in this ReportStatus is updated every time * a new row is added to the workbook */ private XSSFWorkbook getWorkbook(List<DataViewResultVO> results, List<DataViewResultVO> auxiliary, List<DataViewResultVO> noResAux, HashMap<Integer, HashSet<String>> testAnaResMap, HashMap<Integer, HashSet<String>> auxFieldValMap, String moduleName, boolean showReportableColumnsOnly, ArrayList<String> headers, DataView1VO data, HashMap<Integer, SampleManager1> smMap, ReportStatus status) throws Exception { boolean excludeOverride, excludeRes, excludeAux, samOverridden, anaOverridden, addRow; int i, j, resIndex, auxIndex, noResAuxIndex, rowIndex, numRes, numAux, numNoResAux, lastCol, currCol; Integer samId, prevSamId, resAccNum, auxAccNum, itemId, anaId, prevAnaId, anaIndex; String value; SampleManager1 sm; XSSFWorkbook wb; XSSFSheet sheet; DataViewResultVO res; ResultViewDO rowRes, colRes; Row currRow, prevRow; RowData rd; Cell cell; ArrayList<Integer> maxChars; ArrayList<ResultViewDO> smResults; HashMap<String, Integer> colAnaMap; HashMap<Integer, HashSet<String>> anaValMap; numRes = results == null ? 0 : results.size(); numAux = auxiliary == null ? 0 : auxiliary.size(); numNoResAux = noResAux == null ? 0 : noResAux.size(); excludeOverride = "Y".equals(data.getExcludeResultOverride()); excludeRes = "Y".equals(data.getExcludeResults()); excludeAux = "Y".equals(data.getExcludeAuxData()); resIndex = 0; auxIndex = 0; noResAuxIndex = 0; lastCol = 0; currCol = 0; rowIndex = 1; prevSamId = null; prevAnaId = null; anaIndex = null; samOverridden = false; anaOverridden = false; currRow = null; prevRow = null; sm = null; wb = new XSSFWorkbook(); sheet = wb.createSheet(); colAnaMap = new HashMap<String, Integer>(); maxChars = new ArrayList<Integer>(); rd = new RowData(); status.setMessage(Messages.get().report_genDataView()); status.setPercentComplete(0); session.setAttribute("DataViewReportStatus", status); /* * the lists of results and aux data are iterated through until there * are no more elements left in each of them to read from */ while (resIndex < numRes || auxIndex < numAux || noResAuxIndex < numNoResAux) { /* * the user wants to stop the report */ if (ReportStatus.Status.CANCEL.equals(status.getStatus())) { status.setMessage(Messages.get().report_stopped()); return null; } status.setPercentComplete( 100 * (resIndex + auxIndex + noResAuxIndex) / (numRes + numAux + numNoResAux)); res = null; anaValMap = null; value = null; if (excludeRes && excludeAux) { res = noResAux.get(noResAuxIndex++); } else { if (resIndex < numRes && auxIndex < numAux) { resAccNum = results.get(resIndex).getSampleAccessionNumber(); auxAccNum = auxiliary.get(auxIndex).getSampleAccessionNumber(); /* * if this result's accession number is less than or equal * to this aux data's, add a row for this result, otherwise * add a row for the aux data; this makes sure that the * results for a sample are shown before the aux data; * accession numbers are compared instead of sample ids * because the former is the field shown in the report and * not the latter */ if (resAccNum <= auxAccNum) { res = results.get(resIndex++); anaValMap = testAnaResMap; } else { res = auxiliary.get(auxIndex++); anaValMap = auxFieldValMap; } } else if (resIndex < numRes) { /* * no more aux data left to show */ res = results.get(resIndex++); anaValMap = testAnaResMap; } else if (auxIndex < numAux) { /* * no more results left to show */ res = auxiliary.get(auxIndex++); anaValMap = auxFieldValMap; } } samId = res.getSampleId(); itemId = res.getSampleItemId(); anaId = res.getAnalysisId(); if (!samId.equals(prevSamId)) { /* * don't show any data for this sample if it's overridden and * such samples are excluded; whether the sample is overridden * is checked even if such samples are not excluded because * overridden result values are not shown in the report */ sm = smMap.get(samId); samOverridden = false; if ((getSampleQAs(sm) != null)) { for (SampleQaEventViewDO sqa : getSampleQAs(sm)) { if (Constants.dictionary().QAEVENT_OVERRIDE.equals(sqa.getTypeId())) { samOverridden = true; if (excludeOverride) prevSamId = samId; break; } } } } if (samOverridden && excludeOverride) { prevSamId = samId; continue; } /* * don't show any data for this analysis if it's overridden and such * analyses are excluded; whether the analysis is overridden is * checked even if such analyses are not excluded because overridden * values are not shown in the report */ if (anaId != null) { if (!anaId.equals(prevAnaId)) { anaOverridden = false; if ((getAnalysisQAs(sm) != null)) { for (AnalysisQaEventViewDO aqa : getAnalysisQAs(sm)) { if (aqa.getAnalysisId().equals(anaId) && Constants.dictionary().QAEVENT_OVERRIDE.equals(aqa.getTypeId())) { anaOverridden = true; if (excludeOverride) break; } } } } if (anaOverridden && excludeOverride) { prevSamId = samId; prevAnaId = anaId; continue; } } if (anaValMap != null) { /* * show this result or aux data only if its value was selected * by the user */ value = getValue(anaValMap, res.getAnalyteId(), res.getValue(), res.getTypeId()); if (value == null) { prevSamId = samId; prevAnaId = anaId; continue; } } currRow = sheet.createRow(rowIndex++); /* * fill the passed row's cells for all columns except the ones for * analytes and values */ setBaseCells(sm, itemId, anaId, rd, data.getColumns(), moduleName != null, wb, currRow, maxChars); if (value != null) { /* * this row is for either a result or aux data; show the analyte */ cell = currRow.createCell(currRow.getPhysicalNumberOfCells()); setCellValue(cell, res.getAnalyteName(), null); setMaxChars(cell.getColumnIndex(), res.getAnalyteName(), maxChars, null); cell = currRow.createCell(currRow.getPhysicalNumberOfCells()); if (anaId != null && !excludeRes) { /* * this row is for a result; show the value only if the * analysis and sample are not overridden */ if (!anaOverridden && !samOverridden) setCellValue(cell, value, null); setMaxChars(cell.getColumnIndex(), cell.getStringCellValue(), maxChars, null); /* * if this analyte has column analytes, show them in the * header and their values in the columns; results for a * sample can be null if it has no results with values but * has aux data with values and aux data is not excluded */ smResults = getResults(sm); if (smResults != null) { for (i = 0; i < smResults.size(); i++) { rowRes = smResults.get(i); if (!res.getId().equals(rowRes.getId())) continue; j = i + 1; if (j < smResults.size() && "Y".equals(smResults.get(j).getIsColumn())) { /* * this analyte has column analytes; "lastCol" * is the right-most column in the workbook; if * an analyte doesn't have a column yet, that * column will be added after "lastCol"; * "currCol" keeps track of the current column */ if (lastCol == 0) lastCol = currRow.getPhysicalNumberOfCells(); currCol = currRow.getPhysicalNumberOfCells(); while (j < smResults.size()) { colRes = smResults.get(j++); if ("N".equals(colRes.getIsColumn())) break; if (showReportableColumnsOnly && "N".equals(colRes.getIsReportable())) continue; anaIndex = colAnaMap.get(colRes.getAnalyte()); /* * if this column analyte's name is not * found in the map, create a new column and * start adding values in it; set the value * in this cell if the analyte is shown in * this column; if the analyte is not shown * in this column, find the column in which * it is shown and set the value */ if (anaIndex == null) { anaIndex = lastCol++; colAnaMap.put(colRes.getAnalyte(), anaIndex); headers.add(colRes.getAnalyte()); setMaxChars(cell.getColumnIndex(), colRes.getAnalyte(), maxChars, null); cell = currRow.createCell(anaIndex); } else if (anaIndex == currCol) { cell = currRow.createCell(currCol++); } else { cell = currRow.createCell(anaIndex); } /* * set the value if the analysis and sample * are not overridden */ if (!anaOverridden && !samOverridden) setCellValue(cell, getValue(colRes.getValue(), colRes.getTypeId()), null); setMaxChars(cell.getColumnIndex(), cell.getStringCellValue(), maxChars, null); } } } } } else { /* * this row is for an aux data; show the value */ setCellValue(cell, value, null); setMaxChars(cell.getColumnIndex(), value, maxChars, null); } } prevAnaId = anaId; prevSamId = samId; /* * an empty row can't be created and then added to the sheet, it has * to be obtained from the sheet; thus it has to be removed if it * shouldn't be shown because it has the same data as the previous * row in all cells; this can happen if e.g. a user selects only * container and sample type but all sample items in a sample have * the same values for these fields */ if (isSameDataInRows(currRow, prevRow)) { sheet.removeRow(currRow); rowIndex--; } else { prevRow = currRow; } } /* * add the header row and set the header labels for all columns */ setHeaderCells(sheet, wb, headers, maxChars); /* * make each column wide enough to show the longest string in it; the * width for each column is set as the maximum number of characters in * that column multiplied by 256; this is because the default width of * one character is 1/256 units in Excel */ for (i = 0; i < maxChars.size(); i++) sheet.setColumnWidth(i, maxChars.get(i) * 256); return wb; }
From source file:org.wise.vle.web.VLEGetXLS.java
License:Open Source License
/** * Creates an excel workbook that contains student navigation data * Each sheet represents one student's work. The rows in each * sheet are sequential so the earliest navigation data is at * the top and the latest navigation data is at the bottom * //from ww w. jav a2 s . co m * @param nodeIdToNodeTitlesMap a HashMap that contains nodeId to * nodeTitle mappings * @param workgroupIds a vector of workgroup ids * @param runId the run id * @param nodeIdToNode a mapping of node id to node * @param nodeIdToNodeContent a mapping of node id to node content * @param workgroupIdToPeriodId a mapping of workgroup id to period id * @param teacherWorkgroupIds a list of teacher workgroup ids * * @return an excel workbook that contains the student navigation */ private XSSFWorkbook getIdeaBasketsExcelExport(HashMap<String, String> nodeIdToNodeTitlesMap, Vector<String> workgroupIds, String runId, HashMap<String, JSONObject> nodeIdToNode, HashMap<String, JSONObject> nodeIdToNodeContent, HashMap<Integer, Integer> workgroupIdToPeriodId, List<String> teacherWorkgroupIds) { //get the idea basket version that this run uses int ideaBasketVersion = getIdeaBasketVersion(projectMetaData); /** * The idea manager settings from the project meta data will look something * like this * * "ideaManagerSettings": { * "ideaTermPlural": "ideas", * "ideaAttributes": [ * { * "id": "eCE74fj87q", * "allowCustom": false, * "isRequired": true, * "name": "Source", * "type": "source", * "options": [ * "Evidence Step", * "Visualization or Model", * "Movie/Video", * "Everyday Observation", * "School or Teacher" * ] * }, * { * "id": "KuHD6rZVBm", * "allowCustom": false, * "isRequired": true, * "name": "Water Bottle", * "type": "label", * "options": [ * "Water", * "Orange Juice" * ] * } * ], * "basketTerm": "Idea Basket", * "addIdeaTerm": "Add Idea", * "ideaTerm": "idea", * "ebTerm": "Explanation Builder", * "version": "2" * } */ JSONObject ideaManagerSettings = null; //will contain the ideaAttributes array from the idea manager settings JSONArray ideaAttributes = null; //will contain the ideaAttribute ids JSONArray ideaAttributeIds = new JSONArray(); if (ideaBasketVersion > 1) { if (projectMetaData != null) { //check if there is a tools field in the project meta data if (projectMetaData.has("tools")) { try { //get the tools field JSONObject tools = projectMetaData.getJSONObject("tools"); if (tools != null) { //check if there is an ideaManagerSettings field if (tools.has("ideaManagerSettings")) { //get the ideaManagerSettings field ideaManagerSettings = tools.getJSONObject("ideaManagerSettings"); if (ideaManagerSettings != null) { //check if there is an ideaAttributes field if (ideaManagerSettings.has("ideaAttributes")) { //get the ideaAttributes ideaAttributes = ideaManagerSettings.getJSONArray("ideaAttributes"); } } } } } catch (JSONException e) { e.printStackTrace(); } } } } //the excel workbook XSSFWorkbook wb = null; XSSFSheet mainSheet = null; if (isFileTypeXLS(fileType)) { //we are generating an xls file so we will create the workbook wb = new XSSFWorkbook(); //this export will only contain one sheet mainSheet = wb.createSheet(); } int rowCounter = 0; int columnCounter = 0; //create all the header fields Vector<String> headerFields = new Vector<String>(); headerFields.add("Is Basket Public"); headerFields.add("Basket Revision"); headerFields.add("Action"); headerFields.add("Action Performer"); headerFields.add("Changed Idea Id"); headerFields.add("Changed Idea Workgroup Id"); headerFields.add("Idea Id"); headerFields.add("Idea Workgroup Id"); headerFields.add("Idea Text"); if (ideaBasketVersion == 1) { //this run uses the first version of the idea basket which always has flag, tags, and source headerFields.add("Flag"); headerFields.add("Tags"); headerFields.add("Source"); } else { //this run uses the newer version of the idea basket which can have variable and authorable fields if (ideaAttributes != null) { //loop through all the idea attributes for (int x = 0; x < ideaAttributes.length(); x++) { try { //get an idea attribute JSONObject ideaAttribute = ideaAttributes.getJSONObject(x); if (ideaAttribute.has("name")) { //get the name of the attribute String ideaAttributeName = ideaAttribute.getString("name"); //add the header for the attribute headerFields.add(ideaAttributeName); //get the id of the attribute String ideaAttributeId = ideaAttribute.getString("id"); //add the id to our array of attribute ids ideaAttributeIds.put(ideaAttributeId); } } catch (JSONException e) { e.printStackTrace(); } } } } headerFields.add("Node Type"); headerFields.add("Node Id Created On"); headerFields.add("Node Name Created On"); headerFields.add("Steps Used In Count"); headerFields.add("Steps Used In"); headerFields.add("Was Copied From Public"); headerFields.add("Is Published To Public"); headerFields.add("Times Copied"); headerFields.add("Workgroup Ids That Have Copied"); headerFields.add("Trash"); headerFields.add("Timestamp Basket Saved (Server Clock)"); headerFields.add("Timestamp Idea Created (Student Clock)"); headerFields.add("Timestamp Idea Last Edited (Student Clock)"); headerFields.add("New"); headerFields.add("Copied From Public In This Revision"); headerFields.add("Revised"); headerFields.add("Repositioned"); headerFields.add("Steps Used In Changed"); headerFields.add("Deleted In This Revision"); headerFields.add("Restored In This Revision"); headerFields.add("Made Public"); headerFields.add("Made Private"); headerFields.add("Copied In This Revision"); headerFields.add("Uncopied In This Revision"); int headerColumn = 0; //output the user header rows such as workgroup id, wise id 1, etc. Row headerRow = createRow(mainSheet, rowCounter++); Vector<String> headerRowVector = createRowVector(); columnCounter = createUserDataHeaderRow(headerColumn, headerRow, headerRowVector, true, true); //loop through all the header fields to add them to the excel for (int x = 0; x < headerFields.size(); x++) { //the header column to just keep track of each row (which represents a step visit) columnCounter = setCellValue(headerRow, headerRowVector, columnCounter, headerFields.get(x)); } //write the csv row if we are generating a csv file writeCSV(headerRowVector); /* * get all the idea basket revisions for this run. all the revisions * for a workgroup are ordered chronologically and all the basket * revisions for a workgroup are grouped together * e.g. * * list[0] = workgroup1, basket revision 1 * list[1] = workgroup1, basket revision 2 * list[2] = workgroup1, basket revision 3 * list[3] = workgroup2, basket revision 1 * list[4] = workgroup2, basket revision 2 * etc. */ List<IdeaBasket> ideaBasketRevisions = vleService.getIdeaBasketsForRunId(new Long(runId)); /* * used for comparing basket revisions. we need to make sure we are * comparing a basket revision for the same workgroup since these * idea basket revisions are all in the list one after the other */ long previousWorkgroupId = -2; //counter for the basket revision for a workgroup int basketRevision = 1; //variable that will hold the previous basket revision JSONObject previousIdeaBasketJSON = null; //object to format timestamps DateFormat dateTimeInstance = DateFormat.getDateTimeInstance(); //loop through all the basket revisions for (int x = 0; x < ideaBasketRevisions.size(); x++) { //get the IdeaBasket java object IdeaBasket ideaBasket = ideaBasketRevisions.get(x); //get the workgroup id long workgroupId = ideaBasket.getWorkgroupId(); if (workgroupId == previousWorkgroupId) { /* * previous basket revision was for the same workgroup so * we will increment the basket revision counter */ basketRevision++; } else { /* * previous basket revision was for a different workgroup * so we will reset these values */ previousWorkgroupId = -2L; basketRevision = 1; previousIdeaBasketJSON = null; } //get the JSON for the basket revision String data = ideaBasket.getData(); if (data != null) { JSONObject ideaBasketJSON = new JSONObject(); try { //create a JSON object from the basket revision ideaBasketJSON = new JSONObject(data); JSONArray ideas = ideaBasketJSON.getJSONArray("ideas"); //loop through all the active ideas for (int ideasCounter = 0; ideasCounter < ideas.length(); ideasCounter++) { JSONObject idea = ideas.getJSONObject(ideasCounter); rowCounter = createIdeaBasketRow(mainSheet, dateTimeInstance, nodeIdToNodeTitlesMap, ideaBasket, ideaAttributeIds, rowCounter, workgroupId, basketRevision, ideaBasketVersion, ideaBasketJSON, idea, previousIdeaBasketJSON); } JSONArray deleted = ideaBasketJSON.getJSONArray("deleted"); //loop through all the private ideas for (int deletedCounter = 0; deletedCounter < deleted.length(); deletedCounter++) { JSONObject deletedIdea = deleted.getJSONObject(deletedCounter); rowCounter = createIdeaBasketRow(mainSheet, dateTimeInstance, nodeIdToNodeTitlesMap, ideaBasket, ideaAttributeIds, rowCounter, workgroupId, basketRevision, ideaBasketVersion, ideaBasketJSON, deletedIdea, previousIdeaBasketJSON); } /* * remember the workgroupid and basket so we can * compare them to the next revision */ previousWorkgroupId = workgroupId; previousIdeaBasketJSON = ideaBasketJSON; if (ideaBasket.isPublic() != null && ideaBasket.isPublic() && ideas.length() == 0 && deleted.length() == 0) { /* * the first public idea basket revision will be empty so we have * skipped it and now need to decrement the counter to set it back to 1 */ basketRevision--; } } catch (JSONException e1) { e1.printStackTrace(); } } } return wb; }
From source file:packtest.AligningCells.java
License:Apache License
public static void main(String[] args) throws IOException { XSSFWorkbook wb = new XSSFWorkbook(); XSSFSheet sheet = wb.createSheet(); XSSFRow row = sheet.createRow((short) 2); row.setHeightInPoints(30);// w w w. ja v a2 s .c o m for (int i = 0; i < 8; i++) { //column width is set in units of 1/256th of a character width sheet.setColumnWidth(i, 256 * 15); } createCell(wb, row, (short) 0, XSSFCellStyle.ALIGN_CENTER, XSSFCellStyle.VERTICAL_BOTTOM); createCell(wb, row, (short) 1, XSSFCellStyle.ALIGN_CENTER_SELECTION, XSSFCellStyle.VERTICAL_BOTTOM); createCell(wb, row, (short) 2, XSSFCellStyle.ALIGN_FILL, XSSFCellStyle.VERTICAL_CENTER); createCell(wb, row, (short) 3, XSSFCellStyle.ALIGN_GENERAL, XSSFCellStyle.VERTICAL_CENTER); createCell(wb, row, (short) 4, XSSFCellStyle.ALIGN_JUSTIFY, XSSFCellStyle.VERTICAL_JUSTIFY); createCell(wb, row, (short) 5, XSSFCellStyle.ALIGN_LEFT, XSSFCellStyle.VERTICAL_TOP); createCell(wb, row, (short) 6, XSSFCellStyle.ALIGN_RIGHT, XSSFCellStyle.VERTICAL_TOP); //center text over B4, C4, D4 row = sheet.createRow((short) 3); centerAcrossSelection(wb, row, (short) 1, (short) 3, XSSFCellStyle.VERTICAL_CENTER); // Write the output to a file FileOutputStream fileOut = new FileOutputStream("xssf-align.xlsx"); wb.write(fileOut); fileOut.close(); wb.close(); }