List of usage examples for org.apache.poi.hssf.usermodel HSSFWorkbook setSheetName
@Override public void setSheetName(int sheetIx, String name)
From source file:gda.hrpd.data.HSSF.java
License:Apache License
/** * Constructor HSSF - given a filename this outputs a sample sheet with just a set of rows/cells. * * @param filename//from w w w .ja v a2 s . co m * @param write * @exception IOException */ public HSSF(String filename, @SuppressWarnings("unused") boolean write) throws IOException { short rownum = 0; FileOutputStream out = new FileOutputStream(filename); HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet s = wb.createSheet(); HSSFRow r; HSSFCell c = null; HSSFCellStyle cs = wb.createCellStyle(); HSSFCellStyle cs2 = wb.createCellStyle(); HSSFCellStyle cs3 = wb.createCellStyle(); HSSFFont f = wb.createFont(); HSSFFont f2 = wb.createFont(); f.setFontHeightInPoints((short) 12); f.setColor((short) 0xA); f.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); f2.setFontHeightInPoints((short) 10); f2.setColor((short) 0xf); f2.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); cs.setFont(f); cs.setDataFormat(HSSFDataFormat.getBuiltinFormat("($#,##0_);[Red]($#,##0)")); cs2.setBorderBottom(HSSFCellStyle.BORDER_THIN); cs2.setFillPattern((short) 1); // fill w fg cs2.setFillForegroundColor((short) 0xA); cs2.setFont(f2); wb.setSheetName(0, "HSSF Test"); for (rownum = (short) 0; rownum < 300; rownum++) { r = s.createRow(rownum); if ((rownum % 2) == 0) { r.setHeight((short) 0x249); } // r.setRowNum(( short ) rownum); for (short cellnum = (short) 0; cellnum < 50; cellnum += 2) { c = r.createCell(cellnum, HSSFCell.CELL_TYPE_NUMERIC); c.setCellValue(rownum * 10000 + cellnum + (((double) rownum / 1000) + ((double) cellnum / 10000))); if ((rownum % 2) == 0) { c.setCellStyle(cs); } c = r.createCell((short) (cellnum + 1), HSSFCell.CELL_TYPE_STRING); c.setCellValue(new HSSFRichTextString("TEST")); s.setColumnWidth((short) (cellnum + 1), (short) ((50 * 8) / ((double) 1 / 20))); if ((rownum % 2) == 0) { c.setCellStyle(cs2); } } // 50 characters divided by 1/20th of a point } // draw a thick black border on the row at the bottom using BLANKS rownum++; rownum++; r = s.createRow(rownum); cs3.setBorderBottom(HSSFCellStyle.BORDER_THICK); for (short cellnum = (short) 0; cellnum < 50; cellnum++) { c = r.createCell(cellnum, HSSFCell.CELL_TYPE_BLANK); // c.setCellValue(0); c.setCellStyle(cs3); } s.addMergedRegion(new Region((short) 0, (short) 0, (short) 3, (short) 3)); s.addMergedRegion(new Region((short) 100, (short) 100, (short) 110, (short) 110)); // end draw thick black border // create a sheet, set its title then delete it s = wb.createSheet(); wb.setSheetName(1, "DeletedSheet"); wb.removeSheetAt(1); // end deleted sheet wb.write(out); out.close(); }
From source file:ispyb.common.util.upload.ISPyBParser.java
License:Open Source License
/** * PopulateExistingShipment//from www .j av a 2 s . c o m * * @param templateFileName * @param populatedTemplateFileName * @param shippingId * @throws XlsUploadException * @throws Exception */ public void populateExistingShipment(String templateFileName, String populatedTemplateFileName, int shippingId) throws XlsUploadException, Exception { // Create new Excel filesystem to read from POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(templateFileName)); HSSFWorkbook workbook = null; ShippingInformation shippingInformation = DBTools.getShippingInformation(shippingId); try { // Now extract the workbook workbook = new HSSFWorkbook(fs); int nbDewars = shippingInformation.getListDewars().size(); int nbSheetsForDewar = 6; int nbSheetstoCopy = (nbDewars == 0) ? 0 : (nbDewars * nbSheetsForDewar) - 1; // Copy right number of sheets = 1 per dewar for (int d = 1; d <= nbSheetstoCopy; d++) { workbook.cloneSheet(0); } // Populate Sheet for (int dew = 0; dew < nbDewars; dew++) { int sheetStart = (dew == 0) ? 0 : (dew * nbSheetsForDewar); int sheetStop = ((dew + 1) * nbSheetsForDewar) - 1; int puckNumber = 1; for (int s = sheetStart; s <= sheetStop; s++) { String dewarCode = shippingInformation.getListDewars().get(dew).dewar.getCode(); if (dewarCode == null || dewarCode.trim().equalsIgnoreCase("")) dewarCode = Integer.toString(dew); String puckCode = "Puck" + puckNumber; // Populate workbook.setSheetName(s, dewarCode + "_" + puckNumber); HSSFSheet sheet = workbook.getSheetAt(s); sheet.setProtect(false); // Dewar Code HSSFRow row = sheet.getRow(dewarRow); if (row == null) row = sheet.createRow(dewarRow); HSSFCell cell = row.getCell(dewarCol); if (cell == null) cell = row.createCell(dewarCol); cell.setCellType(HSSFCell.CELL_TYPE_STRING); cell.setCellValue(new HSSFRichTextString(dewarCode)); // Puck Code row = sheet.getRow(puckRow); if (row == null) row = sheet.createRow(puckRow); cell = row.getCell(puckCol); if (cell == null) cell = row.createCell(puckCol); cell.setCellType(HSSFCell.CELL_TYPE_STRING); cell.setCellValue(new HSSFRichTextString(puckCode)); // LabelCode row = sheet.getRow(idLabelRow); if (row == null) row = sheet.createRow(idLabelRow); cell = row.getCell(idLabelCol); if (cell == null) cell = row.createCell(idLabelCol); cell.setCellValue(new HSSFRichTextString(ProposalAndShippingLabel)); // ProposalId Integer proposalId = shippingInformation.getShipping().getProposalVOId(); row = sheet.getRow(value1IdRow); if (row == null) row = sheet.createRow(value1IdRow); cell = row.getCell(value1IdCol); if (cell == null) cell = row.createCell(value1IdCol); cell.setCellValue(proposalId); // ShippingId row = sheet.getRow(value2IdRow); if (row == null) row = sheet.createRow(value2IdRow); cell = row.getCell(value2IdCol); if (cell == null) cell = row.createCell(value2IdCol); cell.setCellValue(shippingId); // Courrier Name String courrierName = shippingInformation.getShipping().getDeliveryAgentAgentName(); row = sheet.getRow(courrierNameRow); if (row == null) row = sheet.createRow(courrierNameRow); cell = row.getCell(courrierNameCol); if (cell == null) cell = row.createCell(courrierNameCol); cell.setCellValue(new HSSFRichTextString(courrierName)); // Tracking Number String trackingNumber = shippingInformation.getShipping().getDeliveryAgentAgentCode(); row = sheet.getRow(trackingNumberRow); if (row == null) row = sheet.createRow(trackingNumberRow); cell = row.getCell(trackingNumberCol); if (cell == null) cell = row.createCell(trackingNumberCol); cell.setCellValue(new HSSFRichTextString(trackingNumber)); // Shipping Date Date _shippingDate = shippingInformation.getShipping().getDeliveryAgentShippingDate(); String shippingDate = ""; if (_shippingDate != null) shippingDate = _shippingDate.getDay() + "/" + _shippingDate.getMonth() + "/" + (_shippingDate.getYear() + 1900); row = sheet.getRow(shippingDateRow); if (row == null) row = sheet.createRow(shippingDateRow); cell = row.getCell(shippingDateCol); if (cell == null) cell = row.createCell(shippingDateCol); cell.setCellValue(new HSSFRichTextString(shippingDate)); sheet.setProtect(true); puckNumber++; } } } catch (org.apache.poi.hssf.record.RecordFormatException rfe) { XlsUploadException ex = new XlsUploadException( "[Known APACHE-POI limitation...sorry]A worksheet in the file has a drop-down list selected", "Check all Worksheets in your file and make sure no drop-down list is selected"); throw ex; } // ave Populated template FileOutputStream fout = new FileOutputStream(populatedTemplateFileName); workbook.write(fout); fout.flush(); fout.close(); }
From source file:org.forzaframework.util.XlsUtils.java
License:Apache License
public static HSSFSheet getSheet(HSSFWorkbook wb, String sheetName, Boolean createNewSheet, Integer indexSheet) {/* w w w . jav a2 s . c om*/ HSSFSheet sheet = null;//Revisamos si vamos a crear una hoja nueva o con una ya existente. if ((createNewSheet != null && createNewSheet) || wb.getNumberOfSheets() == 0) { //Creamos una hoja nueva if (sheetName != null) { sheet = wb.createSheet(sheetName); } else { sheet = wb.createSheet(); } } else { //Revisamos si existe la hoja con el nombre especificado if (indexSheet == null && sheetName != null) { sheet = wb.getSheet(sheetName); } if (sheet == null) { //Trabajamos con una hoja ya existente if (indexSheet == null) { indexSheet = 0; } if (sheetName != null) { wb.setSheetName(indexSheet, sheetName); } sheet = wb.getSheetAt(indexSheet); } } return sheet; }
From source file:org.jxstar.report.studio.ExportStatBO.java
/** * ?workbook//from ww w .j a v a 2 s . co m * @param lsData -- ? * @param lsCol -- * @return */ private HSSFWorkbook writeBook(String[][] contents, String[] titles, HSSFWorkbook wb) { //sheet HSSFSheet sheet = wb.getSheetAt(0); //sheet?? String title = "?"; wb.setSheetName(0, title); //?cell? HSSFCellStyle cellStyle = expXls.createCellStyle(wb); // sheet = createTitleArea(title, titles, sheet); //? for (int i = 0, n = contents.length; i < n; i++) { String[] rowconts = contents[i]; HSSFCell sfCell = null; HSSFRow hfRow = sheet.createRow(i + 2); for (int j = 0, m = rowconts.length + 1; j < m; j++) { sfCell = hfRow.createCell(j); if (j == 0) continue; String value = rowconts[j - 1]; //???? boolean isDouble = StringValidator.validValue(value, StringValidator.DOUBLE_TYPE); // boolean isNum = false; if (j > 1 && isDouble && value.length() > 0) { String v = Double.toString(Double.parseDouble(value)); if (v.indexOf('E') < 0) isNum = true; } if (isNum) { sfCell.setCellType(HSSFCell.CELL_TYPE_NUMERIC); sfCell.setCellValue(Double.parseDouble(value)); } else { sfCell.setCellType(HSSFCell.CELL_TYPE_STRING); sfCell.setCellValue(value); } sfCell.setCellStyle(cellStyle); } } return wb; }
From source file:org.jxstar.report.studio.ExportXlsBO.java
public String exportXls(RequestContext request) { //???//from ww w .j a v a 2s. com String funid = request.getRequestValue("query_funid"); String where_sql = request.getRequestValue("where_sql"); String where_value = request.getRequestValue("where_value"); String where_type = request.getRequestValue("where_type"); //String orderclause = request.getParameter("orderclause"); String user_id = request.getRequestValue("user_id"); String selfield = request.getRequestValue("selfield"); //?0 -- 1 -- String querytype = request.getRequestValue("query_type"); //String zerotonull = request.getParameter("zerotonull"); _log.showDebug("==========exp file param funid=" + funid + ";where_sql=" + where_sql + ";where_value=" + where_value + ";where_type=" + where_type); //???where_type String ch = addChar(where_type); if (ch.length() > 0) { _log.showError("............may ignore message: type=" + where_type + ";addchar=" + ch + ";funid=" + funid + ";where_sql=" + where_sql + ";where_value=" + where_value); where_type += ch; } //? Map<String, String> mpDefine = FunDefineDao.queryFun(funid); //?where try { where_sql = WhereUtil.queryWhere(funid, user_id, where_sql, querytype); } catch (BoException e) { _log.showError(e); setMessage(e.getMessage()); return _returnFaild; } _log.showDebug("==========exp file where sql=" + where_sql); String fromSql = mpDefine.get("from_sql"); //?? String maxExpNum = SystemVar.getValue("report.exp.num", "50000"); int dataNum = queryDataNum(fromSql, where_sql, where_type, where_value); if (dataNum > Integer.parseInt(maxExpNum)) { setMessage(JsMessage.getValue("exportxlsbo.maxnum", maxExpNum)); return _returnFaild; } //SQL String sql = "select " + selfield + ' ' + fromSql; if (where_sql != null && where_sql.length() > 0) { sql += " where " + where_sql; } //?? String osql = mpDefine.get("order_sql").trim(); if (osql.length() > 0) { sql += " order by " + osql; } _log.showDebug("==========exp file query sql=" + sql); //?? DaoParam param = _dao.createParam(sql); if (where_type != null && where_type.length() > 0) { param.setType(where_type); param.setValue(where_value); } //??? List<String> hideCols = SysHideField.getHideCols(user_id, funid); if (!hideCols.isEmpty()) param.setHideCols(hideCols); List<Map<String, String>> lsData = _dao.query(param); _log.showDebug("===========exp file query data size=" + lsData.size()); //??key Map<String, Map<String, String>> mpCombo = queryComboValue(funid); //excel HSSFWorkbook wb = new HSSFWorkbook(); //sheet HSSFSheet sheet = wb.createSheet(); //sheet?? String title = mpDefine.get("fun_name"); wb.setSheetName(0, title); //? String[] fields = selfield.split(","); // List<Map<String, String>> lsCol = queryCol(funid, fields); sheet = createTitleArea(title, lsCol, sheet); //?? HSSFCellStyle cellStyle = createCellStyle(wb); for (int i = 0, n = lsData.size(); i < n; i++) { Map<String, String> mpData = lsData.get(i); HSSFRow hfRow = sheet.createRow(i + 3); HSSFCell sfCell = hfRow.createCell(0); for (int j = 0, m = lsCol.size(); j < m; j++) { Map<String, String> mpcol = lsCol.get(j); String colcode = StringUtil.getNoTableCol(mpcol.get("col_code")); String ctltype = mpcol.get("col_control"); String ctlname = mpcol.get("control_name"); String colformat = mpcol.get("format_id"); String colvalue = mpData.get(colcode); if (colvalue == null) colvalue = ""; if (ctltype.equals("combo") && colvalue.length() > 0 && ctlname.length() > 0) { Map<String, String> mpcombo = mpCombo.get(ctlname); if (mpcombo != null && !mpcombo.isEmpty()) { colvalue = mpcombo.get(colvalue); } } //??? colvalue = StringFormat.getDataValue(colvalue, colformat); sfCell = hfRow.createCell(j + 1); sfCell.setCellType(HSSFCell.CELL_TYPE_STRING); if (colformat.equals("int") || colformat.indexOf("num") == 0) { if (colvalue.length() == 0) colvalue = "0"; sfCell.setCellValue(Double.parseDouble(colvalue)); } else { sfCell.setCellValue(colvalue); } sfCell.setCellStyle(cellStyle); } } //?? request.setRequestValue("ContentType", "application/vnd.ms-excel"); request.setRequestValue("Attachment", title + ".xls"); //xls request.setReturnObject(wb); _log.showDebug("---------file output end!"); return _returnSuccess; }
From source file:org.openelis.bean.QcChartReport1Bean.java
License:Open Source License
private void finishSheet(HSSFSheet sheet, HSSFWorkbook wb, String qcName, String qcType, String sheetName) { int i, columnIndex; ArrayList<DictionaryDO> tempQcColumns; DictionaryDO dict;/*from w ww. jav a2s. co m*/ HashSet<Integer> emptyColumns; Name rangeName; Row row; String rangeFormula; if (qcColumns != null && !qcColumns.isEmpty()) row = sheet.getRow(32); else row = sheet.getRow(3); emptyColumns = new HashSet<Integer>(); for (i = 0; i < row.getLastCellNum(); i++) { if (i >= maxChars.size() || maxChars.get(i) == 0) emptyColumns.add(i); } setHeaderCells(sheet, qcName, qcType, sheetName); if (qcColumns != null && !qcColumns.isEmpty()) { tempQcColumns = new ArrayList<DictionaryDO>(); tempQcColumns.addAll(qcColumns); for (i = tempQcColumns.size() - 1; i > -1; i--) { if (emptyColumns.contains(i + 5)) { tempQcColumns.remove(i); removeColumn(sheet, i + 5); maxChars.remove(i + 5); } } rangeName = getName(wb, sheet, "RowNumber"); if (rangeName == null) { rangeName = wb.createName(); rangeName.setSheetIndex(wb.getSheetIndex(sheet)); rangeName.setNameName("RowNumber"); } rangeFormula = sheet.getSheetName() + "!$" + CellReference.convertNumToColString(0) + "$33:" + "$" + CellReference.convertNumToColString(0) + "$" + (sheet.getLastRowNum() + 1); rangeName.setRefersToFormula(rangeFormula); /* * Create named ranges for the graph to be able to locate the appropriate * data */ columnIndex = 5; for (i = 0; i < tempQcColumns.size(); i++) { dict = tempQcColumns.get(i); if (!DataBaseUtil.isEmpty(dict.getCode())) { rangeName = getName(wb, sheet, dict.getCode()); if (rangeName == null) { rangeName = wb.createName(); rangeName.setSheetIndex(wb.getSheetIndex(sheet)); rangeName.setNameName(dict.getCode()); } rangeFormula = rangeName.getRefersToFormula(); if (rangeFormula != null && rangeFormula.length() > 0 && !"$A$2".equals(rangeFormula.substring(rangeFormula.indexOf("!") + 1))) rangeFormula += ","; else rangeFormula = ""; rangeFormula += sheet.getSheetName() + "!$" + CellReference.convertNumToColString(columnIndex) + "$33:" + "$" + CellReference.convertNumToColString(columnIndex) + "$" + (sheet.getLastRowNum() + 1); rangeName.setRefersToFormula(rangeFormula); } columnIndex++; } /* * 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 = 5; i < maxChars.size(); i++) sheet.setColumnWidth(i, maxChars.get(i) * 256); } else if (worksheetHeaders != null && worksheetHeaders.size() > 0) { /* * 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); } wb.setSheetName(wb.getSheetIndex(sheet), sheetName); sheet.setForceFormulaRecalculation(true); maxChars.clear(); }
From source file:org.orbeon.oxf.processor.serializer.legacy.XLSSerializer.java
License:Open Source License
protected void readInput(final PipelineContext pipelineContext, ProcessorInput input, Config config, OutputStream outputStream) { try {//w w w.j a v a 2s. c om Document dataDocument = readInputAsDOM4J(pipelineContext, INPUT_DATA); final DocumentWrapper wrapper = new DocumentWrapper(dataDocument, null, XPathCache.getGlobalConfiguration()); Document configDocument = readInputAsDOM4J(pipelineContext, INPUT_CONFIG); // Read template sheet String templateName = configDocument.getRootElement().attributeValue("template"); //String fileName = configDocument.getRootElement().attributeValue("filename"); InputStream templateInputStream = URLFactory.createURL(templateName).openStream(); final HSSFWorkbook workbook = new HSSFWorkbook(new POIFSFileSystem(templateInputStream)); final HSSFDataFormat dataFormat = workbook.createDataFormat(); templateInputStream.close(); int sheetIndex = 0; PooledXPathExpression expr = XPathCache.getXPathExpression(wrapper.getConfiguration(), wrapper, "/workbook/sheet", getLocationData()); List<Object> nodes = expr.evaluateToJavaReturnToPool(); for (Iterator i = nodes.iterator(); i.hasNext();) { final Element sheetElement = (Element) i.next(); HSSFSheet sheet = workbook.cloneSheet(0); workbook.setSheetName(sheetIndex + 1, sheetElement.attributeValue("name")); // Duplicate rows if we find a "repeat-row" in the config for (Iterator j = configDocument.selectNodes("/config/repeat-row").iterator(); j.hasNext();) { // Get info about row to repeat Element repeatRowElement = (Element) j.next(); final int rowNum = Integer.parseInt(repeatRowElement.attributeValue("row-num")); final String forEach = repeatRowElement.attributeValue("for-each"); HSSFRow templateRow = sheet.getRow(rowNum); int repeatCount = ((Double) sheetElement.selectObject("count(" + forEach + ")")).intValue(); // Move existing rows lower int lastRowNum = sheet.getLastRowNum(); for (int k = lastRowNum; k > rowNum; k--) { HSSFRow sourceRow = sheet.getRow(k); HSSFRow newRow = sheet.createRow(k + repeatCount - 1); XLSUtils.copyRow(workbook, newRow, sourceRow); } // Create rows, copying the template row for (int k = rowNum + 1; k < rowNum + repeatCount; k++) { HSSFRow newRow = sheet.createRow(k); XLSUtils.copyRow(workbook, newRow, templateRow); } // Modify the XPath expression on each row for (int k = rowNum; k < rowNum + repeatCount; k++) { HSSFRow newRow = sheet.getRow(k); for (short m = 0; m <= newRow.getLastCellNum(); m++) { HSSFCell cell = newRow.getCell(m); if (cell != null) { String currentFormat = dataFormat.getFormat(cell.getCellStyle().getDataFormat()); final Matcher matcher = FORMAT_XPATH.matcher(currentFormat); if (matcher.find()) { String newFormat = matcher.group(1) + "\"" + forEach + "[" + (k - rowNum + 1) + "]/" + matcher.group(2) + "\""; cell.getCellStyle().setDataFormat(dataFormat.getFormat(newFormat)); } } } } } // Set values in cells with an XPath expression XLSUtils.walk(dataFormat, sheet, new XLSUtils.Handler() { public void cell(HSSFCell cell, String sourceXPath, String targetXPath) { if (sourceXPath.charAt(0) == '/') sourceXPath = sourceXPath.substring(1); // Set cell value PooledXPathExpression expr = XPathCache.getXPathExpression(wrapper.getConfiguration(), wrapper.wrap(sheetElement), "string(" + sourceXPath + ")", getLocationData()); String newValue = (String) expr.evaluateSingleToJavaReturnToPoolOrNull(); if (newValue == null) { throw new OXFException("Nothing matches the XPath expression '" + sourceXPath + "' in the input document"); } try { cell.setCellValue(Double.parseDouble(newValue)); } catch (NumberFormatException e) { cell.setCellValue(newValue); } // Set cell format Object element = sheetElement.selectObject(sourceXPath); if (element instanceof Element) { // NOTE: We might want to support other properties here String bold = ((Element) element).attributeValue("bold"); if (bold != null) { HSSFFont originalFont = workbook.getFontAt(cell.getCellStyle().getFontIndex()); HSSFFont newFont = workbook.createFont(); XLSUtils.copyFont(newFont, originalFont); if ("true".equals(bold)) newFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); cell.getCellStyle().setFont(newFont); } } } }); sheetIndex++; } workbook.removeSheetAt(0); // Write out the workbook workbook.write(outputStream); } catch (IOException e) { throw new OXFException(e); } }
From source file:org.springframework.web.servlet.view.document.ExcelTestSuite.java
License:Apache License
public void testExcel() throws Exception { AbstractExcelView excelView = new AbstractExcelView() { protected void buildExcelDocument(Map model, HSSFWorkbook wb, HttpServletRequest request, HttpServletResponse response) throws Exception { HSSFSheet sheet = wb.createSheet(); wb.setSheetName(0, "Test Sheet"); // test all possible permutation of row or column not existing HSSFCell cell = getCell(sheet, 2, 4); cell.setCellValue("Test Value"); cell = getCell(sheet, 2, 3); setText(cell, "Test Value"); cell = getCell(sheet, 3, 4); setText(cell, "Test Value"); cell = getCell(sheet, 2, 4); setText(cell, "Test Value"); }/*from w w w . ja va2 s. c o m*/ }; excelView.render(new HashMap(), request, response); POIFSFileSystem poiFs = new POIFSFileSystem(new ByteArrayInputStream(response.getContentAsByteArray())); HSSFWorkbook wb = new HSSFWorkbook(poiFs); assertEquals("Test Sheet", wb.getSheetName(0)); HSSFSheet sheet = wb.getSheet("Test Sheet"); HSSFRow row = sheet.getRow(2); HSSFCell cell = row.getCell((short) 4); assertEquals("Test Value", cell.getStringCellValue()); }
From source file:org.webguitoolkit.ui.util.export.ExcelTableExport.java
License:Apache License
public void writeTo(Table table, OutputStream out) { TableExportOptions exportOptions = table.getExportOptions(); HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet sheet = wb.createSheet();//from w w w.j a v a 2 s . co m HSSFFont fontbold = wb.createFont(); fontbold.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); HSSFCellStyle headstyle = wb.createCellStyle(); headstyle.setFont(fontbold); setExcelheadstyle(headstyle); // create dateStyle HSSFCellStyle cellStyleDate = wb.createCellStyle(); if (StringUtils.isNotEmpty(exportOptions.getExcelDateFormat())) { cellStyleDate.setDataFormat(HSSFDataFormat.getBuiltinFormat(exportOptions.getExcelDateFormat())); } else { cellStyleDate.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy h:mm")); } setExcelDateStyle(cellStyleDate); sheet = excelExport(table, sheet); String sheetName = exportOptions.getExcelSheetName(); if (StringUtils.isEmpty(sheetName)) { sheetName = StringUtils.isNotEmpty(table.getTitle()) ? table.getTitle() : "sheet"; } if (sheetName.length() > 30) { sheetName = sheetName.substring(0, 30); } // DM: 19.11.2010: Slashes in Sheetname are not allowed, e.g. "Planned deliveries / disposals" did throw // IllegalArgumentException. // --> catch Exception and set 'Sheet1' as default. try { wb.setSheetName(0, sheetName); } catch (IllegalArgumentException e) { logger.error("Sheetname is not valid:" + sheetName + " using Sheet1 as default.", e); wb.setSheetName(0, "Sheet1"); } try { wb.write(out); } catch (IOException e) { logger.error(e); } }
From source file:poi.hssf.usermodel.examples.BigExample.java
License:Apache License
public static void main(String[] args) throws IOException { int rownum;//from w ww . j a va 2s . co m // create a new file FileOutputStream out = new FileOutputStream("workbook.xls"); // create a new workbook HSSFWorkbook wb = new HSSFWorkbook(); // create a new sheet HSSFSheet s = wb.createSheet(); // declare a row object reference HSSFRow r = null; // declare a cell object reference HSSFCell c = null; // create 3 cell styles HSSFCellStyle cs = wb.createCellStyle(); HSSFCellStyle cs2 = wb.createCellStyle(); HSSFCellStyle cs3 = wb.createCellStyle(); // create 2 fonts objects HSSFFont f = wb.createFont(); HSSFFont f2 = wb.createFont(); //set font 1 to 12 point type f.setFontHeightInPoints((short) 12); //make it red f.setColor(HSSFColor.RED.index); // make it bold //arial is the default font f.setBoldweight(f.BOLDWEIGHT_BOLD); //set font 2 to 10 point type f2.setFontHeightInPoints((short) 10); //make it the color at palette index 0xf (white) f2.setColor(HSSFColor.WHITE.index); //make it bold f2.setBoldweight(f2.BOLDWEIGHT_BOLD); //set cell stlye cs.setFont(f); //set the cell format see HSSFDataFromat for a full list cs.setDataFormat(HSSFDataFormat.getBuiltinFormat("($#,##0_);[Red]($#,##0)")); //set a thin border cs2.setBorderBottom(cs2.BORDER_THIN); //fill w fg fill color cs2.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); // set foreground fill to red cs2.setFillForegroundColor(HSSFColor.RED.index); // set the font cs2.setFont(f2); // set the sheet name to HSSF Test wb.setSheetName(0, "HSSF Test"); // create a sheet with 300 rows (0-299) for (rownum = 0; rownum < 300; rownum++) { // create a row r = s.createRow(rownum); // on every other row if ((rownum % 2) == 0) { // make the row height bigger (in twips - 1/20 of a point) r.setHeight((short) 0x249); } //r.setRowNum(( short ) rownum); // create 50 cells (0-49) (the += 2 becomes apparent later for (int cellnum = 0; cellnum < 50; cellnum += 2) { // create a numeric cell c = r.createCell(cellnum); // do some goofy math to demonstrate decimals c.setCellValue(rownum * 10000 + cellnum + (((double) rownum / 1000) + ((double) cellnum / 10000))); // on every other row if ((rownum % 2) == 0) { // set this cell to the first cell style we defined c.setCellStyle(cs); } // create a string cell (see why += 2 in the c = r.createCell(cellnum + 1); // set the cell's string value to "TEST" c.setCellValue("TEST"); // make this column a bit wider s.setColumnWidth(cellnum + 1, (int) ((50 * 8) / ((double) 1 / 20))); // on every other row if ((rownum % 2) == 0) { // set this to the white on red cell style // we defined above c.setCellStyle(cs2); } } } //draw a thick black border on the row at the bottom using BLANKS // advance 2 rows rownum++; rownum++; r = s.createRow(rownum); // define the third style to be the default // except with a thick black border at the bottom cs3.setBorderBottom(cs3.BORDER_THICK); //create 50 cells for (int cellnum = 0; cellnum < 50; cellnum++) { //create a blank type cell (no value) c = r.createCell(cellnum); // set it to the thick black border style c.setCellStyle(cs3); } //end draw thick black border // demonstrate adding/naming and deleting a sheet // create a sheet, set its title then delete it s = wb.createSheet(); wb.setSheetName(1, "DeletedSheet"); wb.removeSheetAt(1); //end deleted sheet // write the workbook to the output stream // close our file (don't blow out our file handles wb.write(out); out.close(); }