/* * Don't hardcode US Locale for datetimes in Excel. Experiment a bit. */ public static void issue26() throws InvalidFormatException, IOException { System.out.println("Testing issue26 ================================="); Workbook wb = new XSSFWorkbook(); CreationHelper createHelper = wb.getCreationHelper(); Sheet sheet = wb.createSheet("Sheet1"); Row row = sheet.createRow(0); // first cell Cell cell0 = row.createCell(0); CellStyle cellStyle = wb.createCellStyle(); cellStyle.setDataFormat(createHelper.createDataFormat().getFormat("m/d/yy h:mm:ss")); cell0.setCellValue(new Date(1385903303326L)); // 12/1/2013 08:08 AM cell0.setCellStyle(cellStyle); // second cell using another format with French locale CellStyle cs2 = wb.createCellStyle(); String excelFormatPrefix = DateFormatConverter.getPrefixForLocale(Locale.FRENCH); System.out.println("The LOCALE prefix is: " + excelFormatPrefix); String excelFormatPattern = excelFormatPrefix + "dd MMM, yyyy;@"; System.out.println( "Converted pattern in FRENCH locale is: " + DateFormatConverter.convert(Locale.FRENCH, "m/d/yy h:mm:ss")); DataFormat df = wb.createDataFormat(); cs2.setDataFormat(df.getFormat(excelFormatPattern)); Cell cell1 = row.createCell(1); cell1.setCellValue(new Date(1385903303326L)); cell1.setCellStyle(cs2); FileOutputStream out = new FileOutputStream("/tmp/issue26_out.xlsx"); wb.write(out); out.close(); System.out.println("Wrote /tmp/issue26_out.xlsx"); }
@Override public void createCellStyles(Workbook wb) { if (wb == null) { return; } CreationHelper createHelper = wb.getCreationHelper(); // Headers CellStyle headerStyle = wb.createCellStyle(); headerStyle.setFillBackgroundColor(IndexedColors.BLACK.getIndex()); headerStyle.setFillPattern(CellStyle.SOLID_FOREGROUND); Font font = wb.createFont(); font.setBoldweight(Font.BOLDWEIGHT_BOLD); font.setColor(HSSFColor.WHITE.index); headerStyle.setFont(font); cellStyleMap.put(STYLE_HELP_HEADER, headerStyle); cellStyleMap.put(STYLE_RECORD_HEADER, headerStyle); cellStyleMap.put(STYLE_LOCATION_HEADER, headerStyle); cellStyleMap.put(STYLE_TAXONOMY_HEADER, headerStyle); // Date and Time CellStyle dateStyle = wb.createCellStyle(); dateStyle.setDataFormat(createHelper.createDataFormat().getFormat("D MMM YYYY")); cellStyleMap.put(STYLE_DATE_CELL, dateStyle); CellStyle timeStyle = wb.createCellStyle(); timeStyle.setDataFormat(createHelper.createDataFormat().getFormat("HH:MM")); cellStyleMap.put(STYLE_TIME_CELL, timeStyle); }
private Map<String, CellStyle> createStyles(Workbook wb) { Map<String, CellStyle> styles = new HashMap<String, CellStyle>(); CellStyle style; // Title Style Font titleFont = wb.createFont(); titleFont.setFontHeightInPoints((short) 16); titleFont.setBoldweight(Font.BOLDWEIGHT_BOLD); style = wb.createCellStyle(); style.setAlignment(CellStyle.ALIGN_CENTER); style.setVerticalAlignment(CellStyle.VERTICAL_CENTER); style.setFillForegroundColor(IndexedColors.LIGHT_YELLOW.getIndex()); style.setFillPattern(CellStyle.SOLID_FOREGROUND); style.setFont(titleFont); style.setWrapText(true); styles.put("title", style); // Sub Title Style Font subTitleFont = wb.createFont(); subTitleFont.setFontHeightInPoints((short) 14); subTitleFont.setBoldweight(Font.BOLDWEIGHT_BOLD); style = wb.createCellStyle(); style.setAlignment(CellStyle.ALIGN_CENTER); style.setVerticalAlignment(CellStyle.VERTICAL_CENTER); style.setFillForegroundColor(IndexedColors.BRIGHT_GREEN.index); style.setFillPattern(CellStyle.SOLID_FOREGROUND); style.setFont(subTitleFont); style.setWrapText(true); styles.put("subTitle", style); Font headerFont = wb.createFont(); headerFont.setFontHeightInPoints((short) 11); headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD); style = wb.createCellStyle(); style.setAlignment(CellStyle.ALIGN_CENTER); style.setVerticalAlignment(CellStyle.VERTICAL_CENTER); style.setFillForegroundColor(IndexedColors.GREY_50_PERCENT.getIndex()); style.setFillPattern(CellStyle.SOLID_FOREGROUND); style.setFont(headerFont); styles.put("header", style); style = wb.createCellStyle(); style.setAlignment(CellStyle.ALIGN_CENTER); style.setWrapText(true); style.setBorderRight(CellStyle.BORDER_THIN); style.setRightBorderColor(IndexedColors.BLACK.getIndex()); style.setBorderLeft(CellStyle.BORDER_THIN); style.setLeftBorderColor(IndexedColors.BLACK.getIndex()); style.setBorderTop(CellStyle.BORDER_THIN); style.setTopBorderColor(IndexedColors.BLACK.getIndex()); style.setBorderBottom(CellStyle.BORDER_THIN); style.setBottomBorderColor(IndexedColors.BLACK.getIndex()); styles.put("cell", style); return styles; }
/** Create a library of cell styles */ private static Map<String, CellStyle> createStyles(Workbook wb) { Map<String, CellStyle> styles = new HashMap<String, CellStyle>(); CellStyle style; Font titleFont = wb.createFont(); titleFont.setFontHeightInPoints((short) 18); titleFont.setBoldweight(Font.BOLDWEIGHT_BOLD); style = wb.createCellStyle(); style.setAlignment(CellStyle.ALIGN_CENTER); style.setVerticalAlignment(CellStyle.VERTICAL_CENTER); style.setFont(titleFont); styles.put("title", style); Font monthFont = wb.createFont(); monthFont.setFontHeightInPoints((short) 11); monthFont.setColor(IndexedColors.WHITE.getIndex()); style = wb.createCellStyle(); style.setAlignment(CellStyle.ALIGN_CENTER); style.setVerticalAlignment(CellStyle.VERTICAL_CENTER); style.setFillForegroundColor(IndexedColors.GREY_50_PERCENT.getIndex()); style.setFillPattern(CellStyle.SOLID_FOREGROUND); style.setFont(monthFont); style.setWrapText(true); styles.put("header", style); style = wb.createCellStyle(); style.setAlignment(CellStyle.ALIGN_CENTER); style.setWrapText(true); style.setBorderRight(CellStyle.BORDER_THIN); style.setRightBorderColor(IndexedColors.BLACK.getIndex()); style.setBorderLeft(CellStyle.BORDER_THIN); style.setLeftBorderColor(IndexedColors.BLACK.getIndex()); style.setBorderTop(CellStyle.BORDER_THIN); style.setTopBorderColor(IndexedColors.BLACK.getIndex()); style.setBorderBottom(CellStyle.BORDER_THIN); style.setBottomBorderColor(IndexedColors.BLACK.getIndex()); styles.put("cell", style); style = wb.createCellStyle(); style.setAlignment(CellStyle.ALIGN_CENTER); style.setVerticalAlignment(CellStyle.VERTICAL_CENTER); style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex()); style.setFillPattern(CellStyle.SOLID_FOREGROUND); style.setDataFormat(wb.createDataFormat().getFormat("0.00")); styles.put("formula", style); style = wb.createCellStyle(); style.setAlignment(CellStyle.ALIGN_CENTER); style.setVerticalAlignment(CellStyle.VERTICAL_CENTER); style.setFillForegroundColor(IndexedColors.GREY_40_PERCENT.getIndex()); style.setFillPattern(CellStyle.SOLID_FOREGROUND); style.setDataFormat(wb.createDataFormat().getFormat("0.00")); styles.put("formula_2", style); return styles; }
private Result populateLoansTable(Sheet savingsTransactionSheet) { Result result = new Result(); Workbook workbook = savingsTransactionSheet.getWorkbook(); CellStyle dateCellStyle = workbook.createCellStyle(); short df = workbook.createDataFormat().getFormat("dd/mm/yy"); dateCellStyle.setDataFormat(df); int rowIndex = 1; Row row; Collections.sort(savings, CompactSavingsAccount.ClientNameComparator); try { for (CompactSavingsAccount savingsAccount : savings) { row = savingsTransactionSheet.createRow(rowIndex++); writeString(LOOKUP_CLIENT_NAME_COL, row, savingsAccount.getClientName()); writeLong(LOOKUP_ACCOUNT_NO_COL, row, Long.parseLong(savingsAccount.getAccountNo())); writeString(LOOKUP_PRODUCT_COL, row, savingsAccount.getSavingsProductName()); writeDouble(LOOKUP_OPENING_BALANCE_COL, row, savingsAccount.getMinRequiredOpeningBalance()); writeDate( LOOKUP_SAVINGS_ACTIVATION_DATE_COL, row, savingsAccount.getTimeline().getActivatedOnDate().get(2) + "/" + savingsAccount.getTimeline().getActivatedOnDate().get(1) + "/" + savingsAccount.getTimeline().getActivatedOnDate().get(0), dateCellStyle); } } catch (Exception e) { result.addError(e.getMessage()); logger.error(e.getMessage()); } return result; }
// ����Excel public String ExcelExport() throws Exception { HttpServletRequest request = ServletActionContext.getRequest(); String ids = request.getParameter("ids"); List<Dise> list = new ArrayList<Dise>(); String[] array = ids.split(","); int[] id = new int[array.length]; for (int i = 0; i < id.length; i++) { Dise dise = DiseService.findById(Integer.valueOf(array[i])); list.add(dise); } Workbook workbook = new HSSFWorkbook(); Sheet sheet = workbook.createSheet("ѧ����Ϣ"); Row row = sheet.createRow(0); row.createCell(0).setCellValue("ѧ��"); row.createCell(1).setCellValue("����"); row.createCell(2).setCellValue("����"); row.createCell(3).setCellValue("�Ա�"); row.createCell(4).setCellValue("��ַ"); CellStyle cellStyle = workbook.createCellStyle(); cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy")); for (int i = 1; i <= list.size(); i++) { Dise stu = list.get(i - 1); row = sheet.createRow(i); row.createCell(0).setCellValue(stu.getIds()); row.createCell(1).setCellValue(stu.getName()); row.createCell(2).setCellValue(stu.getA()); row.createCell(3).setCellValue(stu.getB()); row.createCell(4).setCellValue(stu.getC()); } ByteArrayOutputStream baos = new ByteArrayOutputStream(); workbook.write(baos); excelFile = new ByteArrayInputStream(baos.toByteArray()); baos.close(); return "excel"; }
public void insertDataToExcel(int numRow, Object[] object) { try { if (null != wb.getSheetAt(0)) { Sheet aSheet = wb.getSheetAt(0); Row row = aSheet.getRow((short) numRow); if (row == null) row = aSheet.createRow((short) numRow); for (int i = 0; i < object.length; i++) { Cell csCell = row.createCell((short) i); CellStyle style = wb.createCellStyle(); style.setBorderBottom(HSSFCellStyle.BORDER_THIN); style.setBottomBorderColor(HSSFColor.BLACK.index); style.setBorderLeft(HSSFCellStyle.BORDER_THIN); style.setLeftBorderColor(HSSFColor.BLACK.index); style.setBorderRight(HSSFCellStyle.BORDER_THIN); style.setRightBorderColor(HSSFColor.BLACK.index); style.setBorderTop(HSSFCellStyle.BORDER_THIN); style.setTopBorderColor(HSSFColor.BLACK.index); csCell.setCellStyle(style); if (object[i] != null) csCell.setCellValue(object[i].toString()); else csCell.setCellValue("0"); } } } catch (Exception e) { System.out.println("insertDataToExcel" + e); } }
private CellStyle getAmtCellStyle(Workbook wb) { Font font = wb.createFont(); font.setColor(IndexedColors.GREEN.getIndex()); font.setBoldweight(Font.BOLDWEIGHT_BOLD); CellStyle cellStyle = wb.createCellStyle(); cellStyle.setFont(font); getAllAroundBorder(cellStyle); return cellStyle; }
protected CellStyle createMetaDataCellStyle(Workbook wb) { synchronized (syncObj) { // if( isClosed() )throw new IllegalStateException("is closed"); CellStyle cs = wb.createCellStyle(); Font font = wb.createFont(); font.setBold(true); cs.setFont(font); return cs; } }
private void makeCellAutosizeAndBold(Workbook wb, Row row) { CellStyle style = wb.createCellStyle(); Font font = wb.createFont(); font.setBoldweight(Font.BOLDWEIGHT_BOLD); style.setFont(font); for (int i = 0; i < row.getLastCellNum(); i++) { row.getCell(i).setCellStyle(style); } makeCellsAutosize(wb, row); }
private CellStyle getHeaderStyle(Workbook wb) { Font font = wb.createFont(); font.setBoldweight(Font.BOLDWEIGHT_BOLD); CellStyle headerStyle = wb.createCellStyle(); headerStyle.setFont(font); getAllAroundBorder(headerStyle); headerStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex()); headerStyle.setFillPattern(CellStyle.SOLID_FOREGROUND); headerStyle.setAlignment(CellStyle.ALIGN_CENTER); headerStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER); return headerStyle; }
private static void setStyle(Workbook wb) { titleFont = wb.createFont(); titleFont.setBoldweight(Font.BOLDWEIGHT_BOLD); titleStyle = wb.createCellStyle(); titleStyle.setBorderBottom(CellStyle.BORDER_THIN); titleStyle.setBorderLeft(CellStyle.BORDER_THIN); titleStyle.setBorderRight(CellStyle.BORDER_THIN); titleStyle.setBorderTop(CellStyle.BORDER_THIN); titleStyle.setAlignment(CellStyle.ALIGN_CENTER); titleStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER); titleStyle.setFont(titleFont); contentStyle = wb.createCellStyle(); contentStyle.setBorderBottom(CellStyle.BORDER_THIN); contentStyle.setBorderLeft(CellStyle.BORDER_THIN); contentStyle.setBorderRight(CellStyle.BORDER_THIN); contentStyle.setBorderTop(CellStyle.BORDER_THIN); contentStyle.setAlignment(CellStyle.ALIGN_CENTER); contentStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER); }
private void makeTemporalCell(Cell retVal, Date cellObj, String format) { CreationHelper creationHelper = workbook.getCreationHelper(); CellStyle cellStyle = workbook.createCellStyle(); cellStyle.setDataFormat(creationHelper.createDataFormat().getFormat(format)); retVal.setCellStyle(cellStyle); if (null != cellObj) { Calendar calendar = Calendar.getInstance(); calendar.setTime((Date) cellObj); retVal.setCellValue(calendar); } }
/** * 创建表格样式 * * @param wb 工作薄对象 * @return 样式列表 */ private Map<String, CellStyle> createStyles(Workbook wb) { Map<String, CellStyle> styles = new HashMap<String, CellStyle>(); CellStyle style = wb.createCellStyle(); style.setAlignment(CellStyle.ALIGN_CENTER); style.setVerticalAlignment(CellStyle.VERTICAL_CENTER); Font titleFont = wb.createFont(); titleFont.setFontName("Arial"); titleFont.setFontHeightInPoints((short) 16); titleFont.setBoldweight(Font.BOLDWEIGHT_BOLD); style.setFont(titleFont); styles.put("title", style); style = wb.createCellStyle(); style.setVerticalAlignment(CellStyle.VERTICAL_CENTER); style.setBorderRight(CellStyle.BORDER_THIN); style.setRightBorderColor(IndexedColors.GREY_50_PERCENT.getIndex()); style.setBorderLeft(CellStyle.BORDER_THIN); style.setLeftBorderColor(IndexedColors.GREY_50_PERCENT.getIndex()); style.setBorderTop(CellStyle.BORDER_THIN); style.setTopBorderColor(IndexedColors.GREY_50_PERCENT.getIndex()); style.setBorderBottom(CellStyle.BORDER_THIN); style.setBottomBorderColor(IndexedColors.GREY_50_PERCENT.getIndex()); Font dataFont = wb.createFont(); dataFont.setFontName("Arial"); dataFont.setFontHeightInPoints((short) 10); style.setFont(dataFont); styles.put("data", style); style = wb.createCellStyle(); style.cloneStyleFrom(styles.get("data")); style.setAlignment(CellStyle.ALIGN_LEFT); styles.put("data1", style); style = wb.createCellStyle(); style.cloneStyleFrom(styles.get("data")); style.setAlignment(CellStyle.ALIGN_CENTER); styles.put("data2", style); style = wb.createCellStyle(); style.cloneStyleFrom(styles.get("data")); style.setAlignment(CellStyle.ALIGN_RIGHT); styles.put("data3", style); style = wb.createCellStyle(); style.cloneStyleFrom(styles.get("data")); // style.setWrapText(true); style.setAlignment(CellStyle.ALIGN_CENTER); style.setFillForegroundColor(IndexedColors.GREY_50_PERCENT.getIndex()); style.setFillPattern(CellStyle.SOLID_FOREGROUND); Font headerFont = wb.createFont(); headerFont.setFontName("Arial"); headerFont.setFontHeightInPoints((short) 10); headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD); headerFont.setColor(IndexedColors.WHITE.getIndex()); style.setFont(headerFont); styles.put("header", style); return styles; }
/** * This is used to get the cell style of the header cells. If you want a custom style for the * column header cells, customize this method. * * @param theWorkbook the Excel workbook we are working in * @return the cell style for the header */ public static CellStyle GetHeaderCellStyle(Workbook theWorkbook) { CellStyle HeaderStyle = theWorkbook.createCellStyle(); Font HeaderFont = theWorkbook.createFont(); /*sets the style and font appropriately * NOTE: This is the code block where customization should be done */ HeaderFont.setBoldweight(Font.BOLDWEIGHT_BOLD); HeaderStyle.setFont(HeaderFont); return HeaderStyle; }
private CellStyle buildCellStyle() { CellStyle cellStyle = workbook.createCellStyle(); cellStyle.setAlignment(CellStyle.ALIGN_CENTER); cellStyle.setBorderTop(CellStyle.BORDER_THIN); cellStyle.setTopBorderColor(IndexedColors.BLACK.getIndex()); cellStyle.setBorderLeft(CellStyle.BORDER_THIN); cellStyle.setLeftBorderColor(IndexedColors.BLACK.getIndex()); cellStyle.setBorderRight(CellStyle.BORDER_THIN); cellStyle.setRightBorderColor(IndexedColors.BLACK.getIndex()); cellStyle.setBorderBottom(CellStyle.BORDER_THIN); cellStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex()); return cellStyle; }
/** * This is used to get the cell style of the body cells. If you want a custom style for the cells * that say the attribute values of the entries in the list, customize this method. * * @param theWorkbook the Excel workbook we are working in * @return the cell style for the body */ public static CellStyle GetBodyCellStyle(Workbook theWorkbook) { CellStyle theStyle = theWorkbook.createCellStyle(); /*set the style to wrap the text and center vertical alignment * NOTE: This is the code block where customization should be done */ theStyle.setWrapText(true); theStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER); theStyle.setAlignment(CellStyle.ALIGN_LEFT); return theStyle; }
public void outputIntAdapter(DataBean dataBean, Object fieldValue, String fieldName, Cell cell) throws AdapterException { log.debug( "in DefaultOutputAdapter:outputIntAdapter fieldName:{} fieldValue:{}", fieldName, fieldValue); if (ObjectHelper.isNullOrEmptyString(fieldValue)) return; Workbook workbook = cell.getSheet().getWorkbook(); CellStyle cellStyle = workbook.createCellStyle(); CreationHelper createHelper = workbook.getCreationHelper(); cellStyle.setDataFormat(createHelper.createDataFormat().getFormat("#")); cell.setCellValue(NumberUtils.format(fieldValue, 0)); cell.setCellStyle(cellStyle); }
/** * 创建单元格的样式 * * @param workbook 工作表 * @param font 字代 * @param alignment 水平对齐 如:CellStyle.ALIGN_CENTER * @param verticalAlignment 垂直对齐 如:CellStyle.VERTICAL_CENTER * @param Border 边框 如:XSSFCellStyle.BORDER_THIN * @param foregroundColor 前置背景色 如:CellStyle.BORDER_THIN * @param fillPattern 填充模式 如: * @return */ public CellStyle createCellStyle( Workbook workbook, Font font, short alignment, short verticalAlignment, short Border, short foregroundColor, short fillPattern) { XSSFCellStyle cellStyle = (XSSFCellStyle) workbook.createCellStyle(); cellStyle.setFont(font); cellStyle.setAlignment(alignment); cellStyle.setVerticalAlignment(verticalAlignment); cellStyle.setBorderRight(Border); cellStyle.setFillForegroundColor(foregroundColor); cellStyle.setFillPattern(fillPattern); return cellStyle; }
/** * Discription:[设置文件内容的显示样式] * * @param wb * @return * @author:[代超] * @update:[日期YYYY-MM-DD] [更改人姓名][变更描述] */ public CellStyle setContentSheetSysle(Workbook wb) { CellStyle style = wb.createCellStyle(); // 生成并设置另一个样式 style.setFillForegroundColor(HSSFColor.LIGHT_YELLOW.index); style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); style.setBorderBottom(HSSFCellStyle.BORDER_THIN); style.setBorderLeft(HSSFCellStyle.BORDER_THIN); style.setBorderRight(HSSFCellStyle.BORDER_THIN); style.setBorderTop(HSSFCellStyle.BORDER_THIN); style.setAlignment(HSSFCellStyle.ALIGN_CENTER); style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); // 生成一个字体 Font font = wb.createFont(); font.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL); // 把字体应用到当前的样式 style.setFont(font); return style; }
/** * Discription:[设置文件头显示样式] * * @param wb * @return * @author:[代超] * @update:[日期YYYY-MM-DD] [更改人姓名][变更描述] */ public CellStyle setRootSheetSysle(Workbook wb) { CellStyle style = wb.createCellStyle(); // 设置这些样式 style.setFillForegroundColor(HSSFColor.SKY_BLUE.index); style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); style.setBorderBottom(HSSFCellStyle.BORDER_THIN); style.setBorderLeft(HSSFCellStyle.BORDER_THIN); style.setBorderRight(HSSFCellStyle.BORDER_THIN); style.setBorderTop(HSSFCellStyle.BORDER_THIN); style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 对齐方式:居中对齐 // 生成一个字体 Font font = wb.createFont(); font.setColor(HSSFColor.VIOLET.index); font.setFontHeightInPoints((short) 12); font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); // 把字体应用到当前的样式 style.setFont(font); return style; }
public void outputNumericAdapter( DataBean dataBean, Object fieldValue, String fieldName, Cell cell) throws AdapterException { log.debug( "in DefaultOutputAdapter:outputNumericAdapter fieldName:{} fieldValue:{}", fieldName, fieldValue); if (ObjectHelper.isNullOrEmptyString(fieldValue)) return; OutputNumericConfig config = dataBean.getOutputConfig(fieldName); Workbook workbook = cell.getSheet().getWorkbook(); CellStyle cellStyle = workbook.createCellStyle(); CreationHelper createHelper = workbook.getCreationHelper(); StringBuilder format = new StringBuilder("0"); for (int i = 0; i < config.floatCount(); i++) { if (i == 0) format.append("."); format.append("0"); } cellStyle.setDataFormat(createHelper.createDataFormat().getFormat(format.toString())); cell.setCellValue(NumberUtils.format(fieldValue, config.floatCount())); cell.setCellStyle(cellStyle); }
private CellStyle getStyle(Workbook workbook) { CellStyle style = workbook.createCellStyle(); style.setAlignment(CellStyle.ALIGN_CENTER); style.setVerticalAlignment(CellStyle.VERTICAL_CENTER); // 设置单元格字体 Font headerFont = workbook.createFont(); // 字体 headerFont.setFontHeightInPoints((short) 14); headerFont.setColor(HSSFColor.BLACK.index); headerFont.setFontName("宋体"); style.setFont(headerFont); style.setWrapText(true); // 设置单元格边框及颜色 style.setBorderBottom((short) 1); style.setBorderLeft((short) 1); style.setBorderRight((short) 1); style.setBorderTop((short) 1); style.setWrapText(true); return style; }
/** * 导出时间适配器 * * @param fieldValue * @param fieldName * @return * @throws AdapterException */ public void outputDateAdapter(DataBean dataBean, Object fieldValue, String fieldName, Cell cell) throws AdapterException { log.debug( "in DefaultOutputAdapter:outputDateAdapter fieldName:{} fieldValue:{}", fieldName, fieldValue); Date date = null; if (fieldValue == null) { log.debug("fieldValue is null return"); cell.setCellValue(""); return; } else if (fieldValue instanceof Date) { log.debug("fieldValue instanceof Date "); date = (Date) fieldValue; } else if (fieldValue instanceof String) { log.debug("fieldValue instanceof String "); InputDateConfig config = dataBean.getInputConfig(fieldName); try { date = DateUtil.formatToDate((String) fieldValue, config.format()); } catch (ParseException e) { throw new AdapterException(fieldName, Message.DATE_TYPE_ERROR, cell); } } else if (fieldValue instanceof Long) { log.debug("fieldValue instanceof Long "); date = new Date((Long) fieldValue); } else { throw new AdapterException(fieldName, Message.DATE_TYPE_ERROR, cell); } Workbook workbook = cell.getSheet().getWorkbook(); OutputDateConfig outputConfig = dataBean.getOutputConfig(fieldName); CellStyle cellStyle = cell.getCellStyle(); if (cellStyle == null) cellStyle = workbook.createCellStyle(); CreationHelper createHelper = workbook.getCreationHelper(); cellStyle.setDataFormat(createHelper.createDataFormat().getFormat(outputConfig.format())); cell.setCellStyle(cellStyle); cell.setCellValue(date); }
public void setCellMoney(int numRow, int numCol) { try { if (wb.getSheetAt(0) != null) { Sheet aSheet = wb.getSheetAt(0); DataFormat format = wb.createDataFormat(); Row row = aSheet.getRow((short) numRow); Cell csCell = row.getCell((short) numCol); CellStyle style = wb.createCellStyle(); style.setDataFormat(format.getFormat("0.00")); style.setBorderBottom(HSSFCellStyle.BORDER_THIN); style.setBottomBorderColor(HSSFColor.BLACK.index); style.setBorderLeft(HSSFCellStyle.BORDER_THIN); style.setLeftBorderColor(HSSFColor.BLACK.index); style.setBorderRight(HSSFCellStyle.BORDER_THIN); style.setRightBorderColor(HSSFColor.BLACK.index); style.setBorderTop(HSSFCellStyle.BORDER_THIN); style.setTopBorderColor(HSSFColor.BLACK.index); csCell.setCellStyle(style); } } catch (Exception e) { System.out.println("insertDataToExcel" + e); } }
/** * Creates a new instance. * * @param workbook target workbook * @throws IllegalArgumentException if some parameters were {@code null} */ WorkbookInfo(Workbook workbook) { if (workbook == null) { throw new IllegalArgumentException("workbook must not be null"); // $NON-NLS-1$ } this.workbook = workbook; Font font = workbook.createFont(); commonStyle = workbook.createCellStyle(); commonStyle.setFont(font); commonStyle.setBorderTop(BorderStyle.THIN); commonStyle.setBorderBottom(BorderStyle.THIN); commonStyle.setBorderLeft(BorderStyle.THIN); commonStyle.setBorderRight(BorderStyle.THIN); titleStyle = workbook.createCellStyle(); titleStyle.cloneStyleFrom(commonStyle); titleStyle.setLocked(true); titleStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND); titleStyle.setFillForegroundColor(IndexedColors.LIGHT_GREEN.getIndex()); titleStyle.setAlignment(HorizontalAlignment.CENTER); CreationHelper helper = workbook.getCreationHelper(); DataFormat df = helper.createDataFormat(); dataStyle = workbook.createCellStyle(); dataStyle.cloneStyleFrom(commonStyle); dateDataStyle = workbook.createCellStyle(); dateDataStyle.cloneStyleFrom(commonStyle); dateDataStyle.setDataFormat(df.getFormat("yyyy-mm-dd")); // $NON-NLS-1$ timeDataStyle = workbook.createCellStyle(); timeDataStyle.cloneStyleFrom(commonStyle); timeDataStyle.setDataFormat(df.getFormat("hh:mm:ss")); // $NON-NLS-1$ datetimeDataStyle = workbook.createCellStyle(); datetimeDataStyle.cloneStyleFrom(commonStyle); datetimeDataStyle.setDataFormat(df.getFormat("yyyy-mm-dd hh:mm:ss")); // $NON-NLS-1$ }
private CellStyle getFirsetCellStyle(Workbook wb) { CellStyle cellStyle = wb.createCellStyle(); getAllAroundBorder(cellStyle); cellStyle.setAlignment(CellStyle.ALIGN_CENTER); return cellStyle; }
private CellStyle getCommonCellStyle(Workbook wb) { CellStyle cellStyle = wb.createCellStyle(); getAllAroundBorder(cellStyle); return cellStyle; }
private void saveExcelPoject(File file) throws IOException { Workbook wb = new XSSFWorkbook(); Sheet sheet = wb.createSheet("timeplan"); // Заголовок в 0 строке Row row = sheet.createRow(0); Cell cell = row.createCell(0); cell.setCellValue("Филиал"); cell = row.createCell(1); cell.setCellValue("Город"); Calendar cal = Calendar.getInstance(); cal.set(2017, 0, 5); // Начальная дата проекта SimpleDateFormat sdf = new SimpleDateFormat("dd.MM.yy"); for (int i = 0; i < 3 * 52; i++) { // Счетчик по неделям cell = row.createCell(i + 2); cell.setCellValue(sdf.format(cal.getTime())); cal.add(Calendar.WEEK_OF_YEAR, 1); // Следующая неделя } // sheet.setColumnWidth(0, 256); // Цвета ячеек CellStyle[] styles = new CellStyle[6]; styles[0] = wb.createCellStyle(); styles[0].setFillForegroundColor(HSSFColor.RED.index); styles[0].setFillPattern(FillPatternType.SOLID_FOREGROUND); styles[1] = wb.createCellStyle(); styles[1].setFillForegroundColor(HSSFColor.GREEN.index); styles[1].setFillPattern(FillPatternType.SOLID_FOREGROUND); styles[2] = wb.createCellStyle(); styles[2].setFillForegroundColor(HSSFColor.BLUE.index); styles[2].setFillPattern(FillPatternType.SOLID_FOREGROUND); styles[3] = wb.createCellStyle(); styles[3].setFillForegroundColor(HSSFColor.ROSE.index); styles[3].setFillPattern(FillPatternType.SOLID_FOREGROUND); styles[4] = wb.createCellStyle(); styles[4].setFillForegroundColor(HSSFColor.LIGHT_BLUE.index); styles[4].setFillPattern(FillPatternType.SOLID_FOREGROUND); styles[5] = wb.createCellStyle(); styles[5].setFillForegroundColor(HSSFColor.LIGHT_GREEN.index); styles[5].setFillPattern(FillPatternType.SOLID_FOREGROUND); short rowIdx = 0; for (Region region : this.regions) { row = sheet.createRow(++rowIdx); cell = row.createCell(0); cell.setCellValue(region.filial); cell = row.createCell(1); cell.setCellValue(region.name); cal = Calendar.getInstance(); cal.set(2017, 0, 5); // Начальная дата проекта for (int i = 0; i < 3 * 52; i++) { // Счетчик по неделям short color = region.getDateColorIndex(cal.getTime()); if (color >= 0) { cell = row.createCell(i + 2); cell.setCellStyle(styles[color]); } cal.add(Calendar.WEEK_OF_YEAR, 1); // Следующая неделя } } try (FileOutputStream fileOut = new FileOutputStream(file)) { wb.write(fileOut); } }
@Test public void generateExcelPoiReport() throws IOException { Workbook wb = new HSSFWorkbook(); Sheet sheet = wb.createSheet("Accounts"); int rowNum = 1; Row row = sheet.createRow(rowNum++); Font font = wb.createFont(); font.setFontHeightInPoints((short) 24); font.setFontName(FONT_TYPE); font.setColor(FONT_COLOR_TITLE); /** * Header **** */ CellStyle style = wb.createCellStyle(); style.setFont(font); style.setAlignment(CellStyle.ALIGN_CENTER); style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); style.setFillForegroundColor(BACKGROUND_COLOR); Cell cell = row.createCell((short) 1); cell.setCellValue("Account Report per Beneficiary"); cell.setCellStyle(style); sheet.addMergedRegion( new CellRangeAddress( 1, // first row (0-based) 2, // last row (0-based) 1, // first column (0-based) 16 // last column (0-based) )); /** * Body **** */ font = wb.createFont(); font.setFontHeightInPoints((short) 12); font.setFontName(FONT_TYPE); font.setColor(FONT_COLOR); style = wb.createCellStyle(); style.setFont(font); rowNum = rowNum + 3; List<Account> accounts = accountManager.getAllAccounts(); for (Account account : accounts) { row = sheet.createRow(rowNum++); cell = row.createCell((short) 1); cell.setCellStyle(style); cell.setCellValue(account.getName()); cell = row.createCell((short) 2); cell.setCellValue(account.getNumber()); cell.setCellStyle(style); cell = row.createCell((short) 3); cell.setCellValue(account.getEntityId()); cell.setCellStyle(style); } sheet.autoSizeColumn(1); sheet.autoSizeColumn(2); sheet.autoSizeColumn(3); // Write the output to a file FileOutputStream fileOut = new FileOutputStream("C:/Temp/workbook.xls"); wb.write(fileOut); fileOut.close(); }