private void createStyles(HSSFWorkbook workBook, HSSFSheet workSheet) { /*Font bold = workBook.createFont(); bold.setBoldweight(Font.BOLDWEIGHT_BOLD); bold.setFontHeightInPoints((short) 10); bold.setColor(Font.COLOR_RED);*/ HSSFFont hssfFont = workBook.createFont(); hssfFont.setBoldweight(Font.BOLDWEIGHT_BOLD); hssfFont.setColor(Font.COLOR_RED); boldStyle = workBook.createCellStyle(); boldStyle.setBorderBottom(CellStyle.BORDER_THIN); boldStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex()); boldStyle.setFont(hssfFont); boldStyle.setFillBackgroundColor(HSSFColor.BLUE.index); defaultFont = workBook.createFont(); defaultFont.setFontHeightInPoints((short) 10); defaultFont.setFontName("Arial"); defaultFont.setColor(IndexedColors.BLACK.getIndex()); defaultFont.setBoldweight(Font.BOLDWEIGHT_BOLD); defaultFont.setItalic(true); newStyle = workBook.createCellStyle(); // newStyle.setFillBackgroundColor(IndexedColors.DARK_GREEN.getIndex()); // newStyle.setFillPattern(CellStyle.SOLID_FOREGROUND); newStyle.setAlignment(CellStyle.ALIGN_CENTER); newStyle.setFont(defaultFont); /* hssfCellStyle = workBook.createCellStyle(); hssfCellStyle.setBorderBottom(CellStyle.BORDER_THIN); hssfCellStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex()); hssfCellStyle.setFont(bold);*/ }
/** * ************************************************************************* Public methods * ************************************************************************ */ @Override public void open(String filepath) throws Exception { this.filepath = filepath; String author = ini.getValue("Excel", "Author", "Toël Hartmann"); String keywords = ini.getValue("Excel", "Keywords", ""); wb = new HSSFWorkbook(); // Set some properties wb.createInformationProperties(); wb.getSummaryInformation().setAuthor(author); wb.getSummaryInformation().setKeywords(keywords); wb.getSummaryInformation().setCreateDateTime(new Date()); // Create the cell style for column titles titleStyle = wb.createCellStyle(); HSSFFont font = wb.createFont(); font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); titleStyle.setFont(font); String sheetName = getConfigValue("Excel", "sheet", null); if (sheetName == null || sheetName.isEmpty()) sheetName = FileUtils.getFileNameWithoutExtention(filepath); sheet = wb.createSheet(sheetName); if (getConfigValue("Excel", "freezeFirstRow", "true").equalsIgnoreCase("true")) { sheet.createFreezePane(0, 1); } }
public HSSFCellStyle createLastCellStyle(boolean white) { HSSFCellStyle style = workbook.createCellStyle(); style.setBorderBottom(HSSFCellStyle.BORDER_THIN); style.setBorderLeft(HSSFCellStyle.BORDER_THIN); style.setBorderRight(HSSFCellStyle.BORDER_THIN); style.setRightBorderColor(HSSFColor.DARK_BLUE.index); style.setLeftBorderColor(HSSFColor.DARK_BLUE.index); style.setBottomBorderColor(HSSFColor.DARK_BLUE.index); style.setAlignment(HSSFCellStyle.ALIGN_CENTER); style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); if (white) { style.setFillBackgroundColor(HSSFColor.WHITE.index); style.setFillForegroundColor(HSSFColor.WHITE.index); } else { style.setFillBackgroundColor(HSSFColor.LIGHT_CORNFLOWER_BLUE.index); style.setFillForegroundColor(HSSFColor.LIGHT_CORNFLOWER_BLUE.index); } HSSFFont font = workbook.createFont(); font.setFontName("Arial"); font.setFontHeightInPoints((short) 12); style.setFont(font); return style; }
private CoverageResults() { fileName = "Coverage-results.xls"; workbook = new HSSFWorkbook(); allElementsSheet = workbook.createSheet("All Elements"); packagesSheet = workbook.createSheet("Packages"); boundClassesSheet = workbook.createSheet("Bound Classes"); allClassesSheet = workbook.createSheet("All Classes"); boundMethodsSheet = workbook.createSheet("Bound Methods"); allMethodsSheet = workbook.createSheet("All Methods"); normalFont = workbook.createFont(); normalFont.setFontName(HSSFFont.FONT_ARIAL); normalFont.setFontHeightInPoints((short) 10); boldFont = workbook.createFont(); boldFont.setFontName(HSSFFont.FONT_ARIAL); boldFont.setFontHeightInPoints((short) 10); boldFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); centerAlignmentCellStyle = workbook.createCellStyle(); centerAlignmentCellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); leftAlignmentCellStyle = workbook.createCellStyle(); leftAlignmentCellStyle.setAlignment(HSSFCellStyle.ALIGN_LEFT); rightAlignmentCellStyle = workbook.createCellStyle(); rightAlignmentCellStyle.setAlignment(HSSFCellStyle.ALIGN_RIGHT); dataFormatCellStyle = workbook.createCellStyle(); CreationHelper createHelper = workbook.getCreationHelper(); dataFormatCellStyle.setDataFormat(createHelper.createDataFormat().getFormat("dd/MM/yyyy")); }
public void initStyles() { // HSSFDataFormat format = workbook.createDataFormat(); redStyle = workbook.createCellStyle(); setBorder(redStyle, 1); redStyle.setFillForegroundColor(HSSFColor.RED.index); yellowStyle = workbook.createCellStyle(); setBorder(yellowStyle, 1); yellowStyle.setFillForegroundColor(HSSFColor.YELLOW.index); blueStyle = workbook.createCellStyle(); setBorder(blueStyle, 1); blueStyle.setFillForegroundColor(HSSFColor.BLUE_GREY.index); whiteStyle = workbook.createCellStyle(); setBorder(whiteStyle, 1); whiteStyle.setFillForegroundColor(HSSFColor.WHITE.index); normalStyle = workbook.createCellStyle(); setBorder(normalStyle, 0); // normalStyle.setFillForegroundColor(HSSFColor.WHITE.index); normalPStyle = workbook.createCellStyle(); setBorder(normalPStyle, 0); normalPStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("0.00%")); // normalPStyle.setFillForegroundColor(HSSFColor.WHITE.index); headerStyle = workbook.createCellStyle(); setBorder(headerStyle, 0); HSSFFont font = workbook.createFont(); font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); headerStyle.setFont(font); redPStyle = workbook.createCellStyle(); setBorder(redPStyle, 1); redPStyle.setFillForegroundColor(HSSFColor.RED.index); // redPStyle.setDataFormat(format.getFormat(NUMBER_FORMAT)); redPStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("0.00%")); yellowPStyle = workbook.createCellStyle(); setBorder(yellowPStyle, 1); yellowPStyle.setFillForegroundColor(HSSFColor.YELLOW.index); // yellowPStyle.setDataFormat(format.getFormat(NUMBER_FORMAT)); yellowPStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("0.00%")); _fonts = new HashMap<String, HSSFFont>(2); HSSFFont t13y_red = workbook.createFont(); t13y_red.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); t13y_red.setColor(HSSFFont.COLOR_RED); _fonts.put("t13yr", t13y_red); HSSFFont t13y_blue = workbook.createFont(); t13y_blue.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); t13y_blue.setColor(HSSFColor.BLUE_GREY.index); _fonts.put("t13yb", t13y_blue); }
/** * 生成文件 * * @throws IOException */ public byte[] run() throws IOException { // 初始化参数 initParameter(); // 实例化decorator initDecorator(); // 初始化 HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet sheet = wb.createSheet(); sheet.setDefaultColumnWidth((short) 20); // 设置默认宽度 try { wb.setSheetName(0, sheetName, HSSFWorkbook.ENCODING_UTF_16); } catch (Exception ex) { logger.error("生成表单时出错:怀疑是表单名导致。说明:poi对中文的支持不好,可能会有问题(仅表单名)如中文?--中文字加英文标点的情况"); } baseCellStyle = wb.createCellStyle(); baseCellStyle.setAlignment(align); baseCellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("text")); dateCellStyle = wb.createCellStyle(); dateCellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat(dateFormat)); // 创建标题行 HSSFRow row = sheet.createRow((short) 0); // 创建标题 for (int i = 0; i < nameList.size(); i++) { HSSFCell cell = row.createCell((short) i); writeCell(cell, (String) nameList.get(i)); } // 创建数据 for (int i = 0; i < dataList.size(); i++) { row = sheet.createRow((short) i + 1); for (int j = 0; j < fieldList.size(); j++) { HSSFCell cell = row.createCell((short) j); Object value = getValue(dataList.get(i), (String) fieldList.get(j)); writeCell(cell, value); } } // 写入文件 // FileOutputStream fileOut = new FileOutputStream(fileName); ByteArrayOutputStream out = new ByteArrayOutputStream(); wb.write(out); return out.toByteArray(); // fileOut.close(); }
@Override protected void buildExcelDocument( Map<String, Object> model, HSSFWorkbook workbook, HttpServletRequest request, HttpServletResponse response) throws Exception { /* * if (workbook.getNumberOfSheets() == 0) { setUrl("/resources/xls/Product_Locator_API_Usage_Report"); renderMergedOutputModel(model, request, response); } else { */ HSSFSheet sheet = workbook.getSheetAt(0); HSSFCellStyle ordinStyle = workbook.createCellStyle(); ordinStyle.setBorderRight(CellStyle.BORDER_THIN); ordinStyle.setBorderLeft(CellStyle.BORDER_THIN); HSSFCellStyle lineStyle = workbook.createCellStyle(); lineStyle.setBorderTop(CellStyle.BORDER_THIN); short currentRow = 1; Report report = (Report) model.get(SEARCH_LIST_KEY); if (report != null) { List<ReportAPIUsage> list = report.getRepotAPIUsageList(); HSSFRow row = sheet.getRow(0); row.createCell(0) .setCellValue("Date Range: " + report.getDateFrom() + " - " + report.getDateTo()); for (ReportAPIUsage object : list) { currentRow++; row = sheet.createRow(currentRow); HSSFCell cellBrName = row.createCell(BRAND_NAME_COLUMN); cellBrName.setCellValue(object.getClientKey()); cellBrName.setCellStyle(ordinStyle); HSSFCell cellAnyPac = row.createCell(ANY_PACKAGE_COLUMN); cellAnyPac.setCellValue(object.getClientName()); cellAnyPac.setCellStyle(ordinStyle); HSSFCell cellSpecPac = row.createCell(SPEC_PACKAGE_COLUMN); cellSpecPac.setCellValue(object.getCountOfQueries()); cellSpecPac.setCellStyle(ordinStyle); } currentRow++; row = sheet.createRow(currentRow); HSSFCell cellBrName = row.createCell(BRAND_NAME_COLUMN); cellBrName.setCellStyle(lineStyle); HSSFCell cellAnyPac = row.createCell(ANY_PACKAGE_COLUMN); cellAnyPac.setCellStyle(lineStyle); HSSFCell cellSpecPac = row.createCell(SPEC_PACKAGE_COLUMN); cellSpecPac.setCellStyle(lineStyle); } /* } */ }
/** * Insert a bulleted multi-level list into a cell. * * @param workbook A reference to the HSSFWorkbook that 'contains' the cell. * @param multiLevelListItems An ArrayList whose elements contain instances of the * MultiLevelListItem class. Each element encapsulates the text for the high level item along * with an ArrayList. Each element of this ArrayList encapsulates the text for a lower level * item. * @param cell An instance of the HSSFCell class that encapsulates a reference to the spreadsheet * cell into which the list will be written. */ public void multiLevelBulletedListInCell( HSSFWorkbook workbook, ArrayList<MultiLevelListItem> multiLevelListItems, HSSFCell cell) { StringBuffer buffer = new StringBuffer(); ArrayList<String> lowerLevelItems = null; // Note that again, an HSSFCellStye object is required and that // it's wrap text property should be set to 'true' HSSFCellStyle wrapStyle = workbook.createCellStyle(); wrapStyle.setWrapText(true); // Step through the ArrayList of MultilLevelListItem instances. for (MultiLevelListItem multiLevelListItem : multiLevelListItems) { // For each element in the ArrayList, get the text for the high // level list item...... buffer.append(InCellLists.BULLET_CHARACTER); buffer.append(" "); buffer.append(multiLevelListItem.getItemText()); buffer.append("\n"); // and then an ArrayList whose elements encapsulate the text // for the lower level list items. lowerLevelItems = multiLevelListItem.getLowerLevelItems(); if (!(lowerLevelItems == null) && !(lowerLevelItems.isEmpty())) { for (String item : lowerLevelItems) { buffer.append(InCellLists.TAB); buffer.append(InCellLists.BULLET_CHARACTER); buffer.append(" "); buffer.append(item); buffer.append("\n"); } } } // The StringBuffer's contents are the source for the contents // of the cell. cell.setCellValue(new HSSFRichTextString(buffer.toString().trim())); cell.setCellStyle(wrapStyle); }
/** * Inserts a numbered list into a single cell. * * @param workbook A reference to the HSSFWorkbook that 'contains' the cell. * @param listItems An ArrayList whose elements encapsulate the text for the lists items. * @param cell An instance of the HSSFCell class that encapsulates a reference to the spreadsheet * cell into which the list will be written. * @param startingValue A primitive int containing the number for the first item in the list. * @param increment A primitive int containing the value that should be used to calculate * subsequent item numbers. */ public void numberedListInCell( HSSFWorkbook workbook, ArrayList<String> listItems, HSSFCell cell, int startingValue, int increment) { StringBuffer buffer = new StringBuffer(); int itemNumber = startingValue; // Note that again, an HSSFCellStye object is required and that // it's wrap text property should be set to 'true' HSSFCellStyle wrapStyle = workbook.createCellStyle(); wrapStyle.setWrapText(true); // Note that the basic method is identical to the listInCell() method // with one difference; a number prefixed to the items text. for (String listItem : listItems) { buffer.append(String.valueOf(itemNumber) + ". "); buffer.append(listItem); buffer.append("\n"); itemNumber += increment; } // The StringBuffer's contents are the source for the contents // of the cell. cell.setCellValue(new HSSFRichTextString(buffer.toString().trim())); cell.setCellStyle(wrapStyle); }
@SuppressWarnings("rawtypes") private void generateHeaderRow( HSSFWorkbook workBook, Sheet sheet, JSONArray columnConfigJSONObject) { CellStyle center = workBook.createCellStyle(); center.setAlignment(HSSFCellStyle.ALIGN_CENTER); int columnType = Cell.CELL_TYPE_STRING; String col = null, text = null; short width = 15; sheet.setDefaultColumnWidth(width); Row headerRow = createRow(sheet, headerRowNum); for (short i = 0; i < columnConfigJSONObject.length(); i++) { JSONObject headerObj = columnConfigJSONObject.getJSONObject(i); for (Iterator iterator = headerObj.keys(); iterator.hasNext(); ) { String key = (String) iterator.next(); if ("columnType".equals(key)) { // if(isNumber(headerObj.getString(key))) { // columnType = Cell.CELL_TYPE_NUMERIC; // } } else { col = key; text = headerObj.getString(key); } } headerIdArray.add(col); Cell cell = createCell(headerRow, i, columnType); cell.setCellValue(new HSSFRichTextString(text)); cell.setCellStyle(center); } }
private void writeHeaderRow(Entity entity, HSSFSheet sheet) { HSSFRow headerRow = sheet.createRow(0); int colnum = 0; for (Map.Entry<String, Object> component : getComponents(entity)) { String componentName = component.getKey(); headerRow.createCell(colnum).setCellValue(new HSSFRichTextString(componentName)); ComponentDescriptor cd = entity.descriptor().getComponent(componentName); PrimitiveType primitiveType; if (cd.getTypeDescriptor() instanceof SimpleTypeDescriptor) primitiveType = ((SimpleTypeDescriptor) cd.getTypeDescriptor()).getPrimitiveType(); else throw new UnsupportedOperationException( "Can only export simple type attributes, " + "failed to export " + entity.type() + '.' + cd.getName()); Class<?> javaType = (primitiveType != null ? primitiveType.getJavaType() : String.class); String formatString = null; if (BeanUtil.isIntegralNumberType(javaType)) formatString = getIntegralPattern(); else if (BeanUtil.isDecimalNumberType(javaType)) formatString = getDecimalPattern(); else if (Time.class.isAssignableFrom(javaType)) formatString = getTimePattern(); else if (Timestamp.class.isAssignableFrom(javaType)) formatString = getTimestampPattern(); else if (Date.class.isAssignableFrom(javaType)) formatString = getDatePattern(); if (formatString != null) { HSSFDataFormat dataFormat = workbook.createDataFormat(); CellStyle columnStyle = workbook.createCellStyle(); columnStyle.setDataFormat(dataFormat.getFormat(formatString)); sheet.setDefaultColumnStyle(colnum, columnStyle); } colnum++; } }
private void createWorkbook() { this.workbook = new HSSFWorkbook(); this.dateCellStyle = workbook.createCellStyle(); HSSFDataFormat format = workbook.createDataFormat(); short dateFormat = format.getFormat(getDatePattern()); this.dateCellStyle.setDataFormat(dateFormat); }
/** * 测试单元格样式 * * @author David * @param wb * @param cell * @param td */ private static void setType(HSSFWorkbook wb, HSSFCell cell, Element td) { Attribute typeAttr = td.getAttribute("type"); String type = typeAttr.getValue(); HSSFDataFormat format = wb.createDataFormat(); HSSFCellStyle cellStyle = wb.createCellStyle(); if ("NUMERIC".equalsIgnoreCase(type)) { cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC); Attribute formatAttr = td.getAttribute("format"); String formatValue = formatAttr.getValue(); formatValue = StringUtils.isNotBlank(formatValue) ? formatValue : "#,##0.00"; cellStyle.setDataFormat(format.getFormat(formatValue)); } else if ("STRING".equalsIgnoreCase(type)) { cell.setCellValue(""); cell.setCellType(HSSFCell.CELL_TYPE_STRING); cellStyle.setDataFormat(format.getFormat("@")); } else if ("DATE".equalsIgnoreCase(type)) { cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC); cellStyle.setDataFormat(format.getFormat("yyyy-m-d")); } else if ("ENUM".equalsIgnoreCase(type)) { CellRangeAddressList regions = new CellRangeAddressList( cell.getRowIndex(), cell.getRowIndex(), cell.getColumnIndex(), cell.getColumnIndex()); Attribute enumAttr = td.getAttribute("format"); String enumValue = enumAttr.getValue(); // 加载下拉列表内容 DVConstraint constraint = DVConstraint.createExplicitListConstraint(enumValue.split(",")); // 数据有效性对象 HSSFDataValidation dataValidation = new HSSFDataValidation(regions, constraint); wb.getSheetAt(0).addValidationData(dataValidation); } cell.setCellStyle(cellStyle); }
/** * Creates a cell and aligns it a certain way. * * @param wb the workbook * @param row the row to create the cell in * @param column the column number to create the cell in * @param align the alignment for the cell. */ private static void createCell(HSSFWorkbook wb, HSSFRow row, int column, int align) { HSSFCell cell = row.createCell(column); cell.setCellValue("Align It"); HSSFCellStyle cellStyle = wb.createCellStyle(); cellStyle.setAlignment((short) align); cell.setCellStyle(cellStyle); }
public void gerar(String caminho, int qtdLinhas, int qtdColunas) throws IOException { HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet sheet1 = wb.createSheet("planilha um"); for (int i = 0; i < qtdLinhas; i++) { HSSFRow row = sheet1.createRow(i); HSSFCellStyle style = wb.createCellStyle(); style.setVerticalAlignment(HSSFCellStyle.VERTICAL_JUSTIFY); for (int j = 0; j < qtdColunas; j++) { HSSFRichTextString strValor = new HSSFRichTextString("Linha: " + i + " Coluna " + j); HSSFCell cell = row.createCell(j); cell.setCellStyle(style); cell.setCellValue(strValor); } } FileOutputStream stream = new FileOutputStream(caminho + "/planilha1.xls"); wb.write(stream); }
/** * 摘要: @说明:Excel样式 @创建:作者:yxy 创建时间:2011-8-17 * * @param workBook * @param cellStyle 样式模型 * @return @修改历史: [序号](yxy 2011-8-17)<修改说明> */ public static HSSFCellStyle getNewStyle(HSSFWorkbook workBook, CellStyle cellStyle) { HSSFCellStyle style = workBook.createCellStyle(); // 对齐方式 style.setAlignment(cellStyle.getAlignment()); style.setVerticalAlignment(cellStyle.getVAlignment()); // 设置背景颜色 // 最好的设置Pattern style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); // 单元格背景的显示模式 style.setFillForegroundColor(cellStyle.getColor()); // 单元格背景的显示模式. // 设置边框 style.setBorderBottom(cellStyle.getBorderBottom()); // 下边框 style.setBorderLeft(cellStyle.getBorderLeft()); // 左边框 style.setBorderTop(cellStyle.getBorderTop()); // 上边框 style.setBorderRight(cellStyle.getBorderRight()); // 右边框 // 设置边框颜色 style.setBottomBorderColor(cellStyle.getBottomBorderColor()); style.setTopBorderColor(cellStyle.getTopBorderColor()); style.setLeftBorderColor(cellStyle.getLeftBorderColor()); style.setRightBorderColor(cellStyle.getRightBorderColor()); // 设置自动换行 style.setWrapText(cellStyle.getWrapText()); style.setHidden(cellStyle.getHidden()); // 数据格式 style.setDataFormat(cellStyle.getDataFormate()); style.setLocked(cellStyle.getLocked()); // 文本旋转 请注意,这里的Rotation取值是从-90到90,而不是0-180度 style.setRotation(cellStyle.getRotation()); // 文本缩进 style.setIndention(cellStyle.getIndention()); return style; }
/** * Fills given row with headers * * @param wb * @param sheet * @param row */ private void fillHeaderRow(HSSFWorkbook wb, HSSFSheet sheet, HSSFRow row) { // create style of header font HSSFFont font = wb.createFont(); font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); font.setFontHeightInPoints((short) 11); HSSFCellStyle style = wb.createCellStyle(); style.setFont(font); // here we could create array of strings, I mean headers String[] headers = { this.iwrb.getLocalizedString("school.child", "Child"), this.iwrb.getLocalizedString("school.personal_id", "Personal ID"), this.iwrb.getLocalizedString("school.e-mail", "Email"), this.iwrb.getLocalizedString("school.address", "Address"), this.iwrb.getLocalizedString("school.zip_code", "Zip code"), this.iwrb.getLocalizedString("school.city", "City"), this.iwrb.getLocalizedString("school.phone", "Phone"), this.iwrb.getLocalizedString("school.gender", "Gender"), this.iwrb.getLocalizedString("school.last_provider", "Last provider"), this.iwrb.getLocalizedString("school.rejection_date", "Rejection date") }; int[] headerWidths = {30, 14, 25, 25, 10, 16, 16, 8, 30, 16}; HSSFCell cell; for (int i = 0; i < headers.length; i++) { cell = row.createCell((short) i); cell.setCellValue(headers[i]); cell.setCellStyle(style); sheet.setColumnWidth((short) i, (short) (headerWidths[i] * 256)); } }
public ExcelUtils(List items, ItemSearch itemSearch) { this.wb = new HSSFWorkbook(); this.sheet = wb.createSheet("jtrac"); this.sheet.setDefaultColumnWidth((short) 12); HSSFFont fBold = wb.createFont(); fBold.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); this.csBold = wb.createCellStyle(); this.csBold.setFont(fBold); this.csDate = wb.createCellStyle(); this.csDate.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy")); this.items = items; this.itemSearch = itemSearch; }
@SuppressWarnings("deprecation") public void Daochu() throws Exception { HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet sheet = wb.createSheet("库存盘点表"); HSSFRow row = sheet.createRow((int) 0); HSSFCellStyle style = wb.createCellStyle(); style.setAlignment(HSSFCellStyle.ALIGN_CENTER); HSSFCell cell = row.createCell((short) 0); cell.setCellValue("快递编号"); cell.setCellStyle(style); cell = row.createCell((short) 1); cell.setCellValue("目的地"); cell.setCellStyle(style); cell = row.createCell((short) 2); cell.setCellValue("入库日期"); cell.setCellStyle(style); cell = row.createCell((short) 3); cell.setCellValue("区号"); cell.setCellStyle(style); cell = row.createCell((short) 4); cell.setCellValue("排号"); cell.setCellStyle(style); cell = row.createCell((short) 5); cell.setCellValue("架号"); cell.setCellStyle(style); cell = row.createCell((short) 6); cell.setCellValue("位号"); cell.setCellStyle(style); cell = row.createCell((short) 7); cell.setCellValue("中转中心"); cell.setCellStyle(style); FileInputStream fis = new FileInputStream("src/main/java/data/save/instock.txt"); ObjectInputStream ois = new ObjectInputStream(fis); @SuppressWarnings("unchecked") List<InStoringpo> list = (List<InStoringpo>) ois.readObject(); ois.close(); InStoringpo[] sp = new InStoringpo[list.size()]; for (int i = 0; i < list.size(); i++) { row = sheet.createRow((int) i + 1); sp[i] = list.get(i); // 第四步,创建单元格,并设置值 row.createCell((short) 0).setCellValue(sp[i].bianhao); row.createCell((short) 1).setCellValue(sp[i].destination); row.createCell((short) 2).setCellValue(sp[i].year + "-" + sp[i].month + "-" + sp[i].day); row.createCell((short) 3).setCellValue(sp[i].quhao); row.createCell((short) 4).setCellValue(sp[i].paihao); row.createCell((short) 5).setCellValue(sp[i].jiahao); row.createCell((short) 6).setCellValue(sp[i].weihao); row.createCell((short) 7).setCellValue(sp[i].zhongzhuan); } try { FileOutputStream fout = new FileOutputStream("C:/大作业代码/kucundaochu.xls"); wb.write(fout); fout.close(); } catch (Exception e) { e.printStackTrace(); } System.out.println("导出成功!"); }
/** * Creates a cell and aligns it a certain way. * * @param wb the workbook * @param row the row to create the cell in * @param column the column number to create the cell in * @param align the alignment for the cell. */ @SuppressWarnings("deprecation") private static void createCell(HSSFWorkbook wb, HSSFRow row, short column, short align) { HSSFCell cell = row.createCell(column); cell.setCellValue("Align It"); HSSFCellStyle cellStyle = wb.createCellStyle(); cellStyle.setAlignment(align); cell.setCellStyle(cellStyle); }
private CellStyle getDateFormatStyle(HSSFCell poiCell) { final HSSFWorkbook poiWorkbook = poiCell.getRow().getSheet().getWorkbook(); CellStyle dateFormatStyle = poiWorkbook.createCellStyle(); CreationHelper createHelper = poiWorkbook.getCreationHelper(); dateFormatStyle.setDataFormat(createHelper.createDataFormat().getFormat("yyyy/mm/dd hh:MM:ss")); dateFormatStyle.setAlignment(CellStyle.ALIGN_LEFT); return dateFormatStyle; }
public void setCell(int index, Calendar value) { HSSFCell cell = this.row.createCell((short) index); // cell.setEncoding(XLS_ENCODING); cell.setCellValue(value.getTime()); // for new cell style HSSFCellStyle cellStyle = workbook.createCellStyle(); cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat(DATE_FORMAT)); cell.setCellStyle(cellStyle); }
/** * to build the response as excel file. * * @see org.springframework.web.servlet.view.document.AbstractExcelView# * buildExcelDocument(java.util.Map, org.apache.poi.hssf.usermodel.HSSFWorkbook, * javax.servlet.http.HttpServletRequest, javax.servlet.http.HttpServletResponse) */ @SuppressWarnings(AppConstants.SUPPRESS_WARNINGS_UNCHECKED) @Override protected void buildExcelDocument( Map<String, Object> model, HSSFWorkbook myWorkBook, HttpServletRequest arg2, HttpServletResponse arg3) throws Exception { List<QuestionDTO> questionDTOs = (List<QuestionDTO>) model.get("questions"); HSSFSheet questionsSheet = myWorkBook.createSheet(AppConstants.EXCEL_SHEET_NAME); HSSFRow headerRow = questionsSheet.createRow(0); HSSFCellStyle wrapCellStyle = myWorkBook.createCellStyle(); wrapCellStyle.setWrapText(true); wrapCellStyle.setAlignment(CellStyle.ALIGN_JUSTIFY); createNewCell(headerRow, QUESTIONID_CELLNUM, "Question ID", wrapCellStyle); createNewCell(headerRow, QUESTION_CELLNUM, "Question", wrapCellStyle); createNewCell(headerRow, ANSWER_CELLNUM, "Answer", wrapCellStyle); createNewCell( headerRow, CATEGORIES_CELLNUM, "Categories (Category seperated by comma)", wrapCellStyle); HSSFRow dataRow = null; if (!CoreUtil.isEmpty(questionDTOs)) { int i = 0; for (QuestionDTO questionDTO : questionDTOs) { dataRow = questionsSheet.createRow(i + 1); createNewCell(dataRow, QUESTIONID_CELLNUM, Long.toString(questionDTO.getId()), null); createNewCell(dataRow, QUESTION_CELLNUM, questionDTO.getQuestion(), wrapCellStyle); createNewCell(dataRow, ANSWER_CELLNUM, questionDTO.getAnswer(), wrapCellStyle); createNewCell( dataRow, CATEGORIES_CELLNUM, parseCategoryDTOs(questionDTO.getCategoryDTOs()), wrapCellStyle); // Increment row i = i + 1; } } questionsSheet.autoSizeColumn(QUESTIONID_CELLNUM); questionsSheet.autoSizeColumn(QUESTION_CELLNUM); questionsSheet.autoSizeColumn(ANSWER_CELLNUM); questionsSheet.autoSizeColumn(CATEGORIES_CELLNUM); questionsSheet.setDefaultColumnStyle(QUESTION_CELLNUM, wrapCellStyle); questionsSheet.setDefaultColumnStyle(ANSWER_CELLNUM, wrapCellStyle); questionsSheet.setDefaultColumnStyle(CATEGORIES_CELLNUM, wrapCellStyle); }
private void initEmptyStyle() { emptyStyle = wb.createCellStyle(); emptyStyle.setFillForegroundColor(HSSFColor.RED.index); emptyStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); emptyStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); emptyStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN); emptyStyle.setBorderRight(HSSFCellStyle.BORDER_THIN); emptyStyle.setBorderTop(HSSFCellStyle.BORDER_THIN); emptyStyle.setAlignment(HSSFCellStyle.ALIGN_LEFT); }
private void initKeyStyle() { keyStyle = wb.createCellStyle(); keyStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); keyStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN); keyStyle.setBorderRight(HSSFCellStyle.BORDER_THIN); keyStyle.setBorderTop(HSSFCellStyle.BORDER_THIN); keyStyle.setAlignment(HSSFCellStyle.ALIGN_LEFT); keyStyle.setWrapText(true); keyStyle.setFont(normalFont); }
private void initMessageStyle() { messageStyle = wb.createCellStyle(); messageStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); messageStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN); messageStyle.setBorderRight(HSSFCellStyle.BORDER_THIN); messageStyle.setBorderTop(HSSFCellStyle.BORDER_THIN); messageStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); messageStyle.setWrapText(true); messageStyle.setFont(normalFont); }
@Override public void setDateTimePattern(String dateTimePattern) { this.dateTimePattern = dateTimePattern; DateTimeConverter dtConverter = (DateTimeConverter) converters.lookup(Date.class); dtConverter.setDateTimePattern(dateTimePattern); dateCellStyle = workbook.createCellStyle(); CreationHelper helper = workbook.getCreationHelper(); dateCellStyle.setDataFormat(helper.createDataFormat().getFormat(dateTimePattern)); }
public HSSFCellStyle createScriptStyle() { HSSFCellStyle style = workbook.createCellStyle(); style.setAlignment(HSSFCellStyle.ALIGN_LEFT); HSSFFont font = workbook.createFont(); font.setFontName("Arial"); font.setFontHeightInPoints((short) 12); font.setColor(HSSFColor.BLACK.index); style.setFont(font); return style; }
private void initNormalStyle() { normalStyle = wb.createCellStyle(); normalStyle.setFillForegroundColor(HSSFColor.WHITE.index); normalStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); normalStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN); normalStyle.setBorderRight(HSSFCellStyle.BORDER_THIN); normalStyle.setBorderTop(HSSFCellStyle.BORDER_THIN); normalStyle.setAlignment(HSSFCellStyle.ALIGN_LEFT); normalStyle.setWrapText(true); normalStyle.setFont(normalFont); }
// 默认cell样式 public HSSFCellStyle getDefaultStyle(HSSFWorkbook wb) { HSSFCellStyle style = wb.createCellStyle(); // 设置边框样式 style.setBorderTop(HSSFCellStyle.BORDER_THIN); style.setBorderBottom(HSSFCellStyle.BORDER_THIN); style.setBorderLeft(HSSFCellStyle.BORDER_THIN); style.setBorderRight(HSSFCellStyle.BORDER_THIN); return style; }