List of usage examples for org.apache.poi.hssf.usermodel HSSFCellStyle setLocked
@Override public void setLocked(boolean locked)
From source file:com.eryansky.core.excelTools.ExcelUtils.java
License:Apache License
public static void copyCellStyle(HSSFWorkbook destwb, HSSFCellStyle dest, HSSFWorkbook srcwb, HSSFCellStyle src) {//from w w w . ja v a 2 s .c o m if (src == null || dest == null) return; dest.setAlignment(src.getAlignment()); dest.setBorderBottom(src.getBorderBottom()); dest.setBorderLeft(src.getBorderLeft()); dest.setBorderRight(src.getBorderRight()); dest.setBorderTop(src.getBorderTop()); dest.setBottomBorderColor(findColor(src.getBottomBorderColor(), srcwb, destwb)); dest.setDataFormat( destwb.createDataFormat().getFormat(srcwb.createDataFormat().getFormat(src.getDataFormat()))); dest.setFillPattern(src.getFillPattern()); dest.setFillForegroundColor(findColor(src.getFillForegroundColor(), srcwb, destwb)); dest.setFillBackgroundColor(findColor(src.getFillBackgroundColor(), srcwb, destwb)); dest.setHidden(src.getHidden()); dest.setIndention(src.getIndention()); dest.setLeftBorderColor(findColor(src.getLeftBorderColor(), srcwb, destwb)); dest.setLocked(src.getLocked()); dest.setRightBorderColor(findColor(src.getRightBorderColor(), srcwb, destwb)); dest.setRotation(src.getRotation()); dest.setTopBorderColor(findColor(src.getTopBorderColor(), srcwb, destwb)); dest.setVerticalAlignment(src.getVerticalAlignment()); dest.setWrapText(src.getWrapText()); HSSFFont f = srcwb.getFontAt(src.getFontIndex()); HSSFFont nf = findFont(f, srcwb, destwb); if (nf == null) { nf = destwb.createFont(); nf.setBoldweight(f.getBoldweight()); nf.setCharSet(f.getCharSet()); nf.setColor(findColor(f.getColor(), srcwb, destwb)); nf.setFontHeight(f.getFontHeight()); nf.setFontHeightInPoints(f.getFontHeightInPoints()); nf.setFontName(f.getFontName()); nf.setItalic(f.getItalic()); nf.setStrikeout(f.getStrikeout()); nf.setTypeOffset(f.getTypeOffset()); nf.setUnderline(f.getUnderline()); } dest.setFont(nf); }
From source file:com.fufang.bi.controllers.ChainReportController.java
/*** * ?excel//ww w. j a v a 2 s . c om * @param orderType * @param list * @param inputStream * @return */ public HSSFWorkbook createUploadSplitExcel(List<?> list, InputStream inputStream, Integer select, Object obj) { try { HSSFWorkbook workbook = null; workbook = new HSSFWorkbook(inputStream); HSSFSheet sheet = null;// sheet = workbook.getSheetAt(0);// HSSFCellStyle cellStyle = workbook.createCellStyle(); cellStyle.setLocked(false); HSSFRow row = null; HSSFCell cell = null; if (list != null && list.size() > 0) { for (int i = 0, size = list.size(); i < size; i++) { HSSFRow rows = null; rows = sheet.createRow(i + 5); if (select == 3) { row = sheet.createRow(i + 3); } else { row = sheet.createRow(i + 2); } if (select == 1) { StorageTotal data = (StorageTotal) list.get(i); Converter.setCellText(row, cell, 0, Converter.toBlank(data.getId()), cellStyle); Converter.setCellText(row, cell, 1, Converter.toBlank(data.getPharmacycode()), cellStyle); Converter.setCellText(row, cell, 2, Converter.toBlank(data.getName()), cellStyle); Converter.setCellText(row, cell, 3, Converter.toBlank(data.getQckc()), cellStyle); Converter.setCellText(row, cell, 4, Converter.toBlank(data.getQckcje()), cellStyle); Converter.setCellText(row, cell, 5, Converter.toBlank(data.getQjrk()), cellStyle); Converter.setCellText(row, cell, 6, Converter.toBlank(data.getQjrkje()), cellStyle); Converter.setCellText(row, cell, 7, Converter.toBlank(data.getQjck()), cellStyle); Converter.setCellText(row, cell, 8, Converter.toBlank(data.getQjckje()), cellStyle); Converter.setCellText(row, cell, 9, Converter.toBlank(data.getQmkc()), cellStyle); Converter.setCellText(row, cell, 10, Converter.toBlank(data.getQmkcje()), cellStyle); } if (select == 2) { StorageMilde data = (StorageMilde) list.get(i); Converter.setCellText(row, cell, 0, Converter.toBlank(data.getId()), cellStyle); Converter.setCellText(row, cell, 1, Converter.toBlank(data.getPharmacycode()), cellStyle); Converter.setCellText(row, cell, 2, Converter.toBlank(data.getName()), cellStyle); Converter.setCellText(row, cell, 3, Converter.toBlank(data.getMatcode()), cellStyle); Converter.setCellText(row, cell, 4, Converter.toBlank(data.getBarcode()), cellStyle); Converter.setCellText(row, cell, 5, Converter.toBlank(data.getMatname()), cellStyle); Converter.setCellText(row, cell, 6, Converter.toBlank(data.getCommonname()), cellStyle); Converter.setCellText(row, cell, 7, Converter.toBlank(data.getDosage()), cellStyle); Converter.setCellText(row, cell, 8, Converter.toBlank(data.getSpec()), cellStyle); Converter.setCellText(row, cell, 9, Converter.toBlank(data.getUnitname()), cellStyle); Converter.setCellText(row, cell, 10, Converter.toBlank(data.getManufname()), cellStyle); Converter.setCellText(row, cell, 11, Converter.toBlank(data.getProductarea()), cellStyle); Converter.setCellText(row, cell, 12, Converter.toBlank(data.getLicensenum()), cellStyle); Converter.setCellText(row, cell, 13, Converter.toBlank(data.getRetail()), cellStyle); Converter.setCellText(row, cell, 14, Converter.toBlank(data.getQckc()), cellStyle); Converter.setCellText(row, cell, 15, Converter.toBlank(data.getQckcje()), cellStyle); Converter.setCellText(row, cell, 16, Converter.toBlank(data.getQjrk()), cellStyle); Converter.setCellText(row, cell, 17, Converter.toBlank(data.getQjrkje()), cellStyle); Converter.setCellText(row, cell, 18, Converter.toBlank(data.getQjck()), cellStyle); Converter.setCellText(row, cell, 19, Converter.toBlank(data.getQjckje()), cellStyle); Converter.setCellText(row, cell, 20, Converter.toBlank(data.getQmkc()), cellStyle); Converter.setCellText(row, cell, 21, Converter.toBlank(data.getQmkcje()), cellStyle); } if (select == 3) { StorageDetail data = (StorageDetail) list.get(i); Converter.setCellText(row, cell, 0, Converter.toBlank(data.getId()), cellStyle); Converter.setCellText(row, cell, 1, Converter.toBlank(data.getPharmacycode()), cellStyle); Converter.setCellText(row, cell, 2, Converter.toBlank(data.getName()), cellStyle); Converter.setCellText(row, cell, 3, Converter.toBlank(data.getMatcode()), cellStyle); Converter.setCellText(row, cell, 4, Converter.toBlank(data.getBarcode()), cellStyle); Converter.setCellText(row, cell, 5, Converter.toBlank(data.getMatname()), cellStyle); Converter.setCellText(row, cell, 6, Converter.toBlank(data.getCommonname()), cellStyle); Converter.setCellText(row, cell, 7, Converter.toBlank(data.getDosage()), cellStyle); Converter.setCellText(row, cell, 8, Converter.toBlank(data.getSpec()), cellStyle); Converter.setCellText(row, cell, 9, Converter.toBlank(data.getUnitname()), cellStyle); Converter.setCellText(row, cell, 10, Converter.toBlank(data.getManufname()), cellStyle); Converter.setCellText(row, cell, 11, Converter.toBlank(data.getProductarea()), cellStyle); Converter.setCellText(row, cell, 12, Converter.toBlank(data.getLicensenum()), cellStyle); Converter.setCellText(row, cell, 13, Converter.toBlank(data.getRetail()), cellStyle); Converter.setCellText(row, cell, 14, Converter.toBlank(data.getQckc()), cellStyle); Converter.setCellText(row, cell, 15, Converter.toBlank(data.getQckcje()), cellStyle); Converter.setCellText(row, cell, 16, Converter.toBlank(data.getCg()), cellStyle); Converter.setCellText(row, cell, 17, Converter.toBlank(data.getCgje()), cellStyle); Converter.setCellText(row, cell, 18, Converter.toBlank(data.getPyrk()), cellStyle); Converter.setCellText(row, cell, 19, Converter.toBlank(data.getPyrkje()), cellStyle); Converter.setCellText(row, cell, 20, Converter.toBlank(data.getQtrk()), cellStyle); Converter.setCellText(row, cell, 21, Converter.toBlank(data.getQtrkje()), cellStyle); Converter.setCellText(row, cell, 22, Converter.toBlank(data.getClrk()), cellStyle); Converter.setCellText(row, cell, 23, Converter.toBlank(data.getClrkje()), cellStyle); Converter.setCellText(row, cell, 24, Converter.toBlank(data.getPsrk()), cellStyle); Converter.setCellText(row, cell, 25, Converter.toBlank(data.getPsrkje()), cellStyle); Converter.setCellText(row, cell, 26, Converter.toBlank(data.getQcrk()), cellStyle); Converter.setCellText(row, cell, 27, Converter.toBlank(data.getQcrkje()), cellStyle); Converter.setCellText(row, cell, 28, Converter.toBlank(data.getXsck()), cellStyle); Converter.setCellText(row, cell, 29, Converter.toBlank(data.getXsckje()), cellStyle); Converter.setCellText(row, cell, 30, Converter.toBlank(data.getPkck()), cellStyle); Converter.setCellText(row, cell, 31, Converter.toBlank(data.getPkckje()), cellStyle); Converter.setCellText(row, cell, 32, Converter.toBlank(data.getQtck()), cellStyle); Converter.setCellText(row, cell, 33, Converter.toBlank(data.getQtckje()), cellStyle); Converter.setCellText(row, cell, 34, Converter.toBlank(data.getClck()), cellStyle); Converter.setCellText(row, cell, 35, Converter.toBlank(data.getClckje()), cellStyle); Converter.setCellText(row, cell, 36, Converter.toBlank(data.getPsck()), cellStyle); Converter.setCellText(row, cell, 37, Converter.toBlank(data.getPsckje()), cellStyle); Converter.setCellText(row, cell, 38, Converter.toBlank(data.getQdck()), cellStyle); Converter.setCellText(row, cell, 39, Converter.toBlank(data.getQdckje()), cellStyle); Converter.setCellText(row, cell, 40, Converter.toBlank(data.getXhck()), cellStyle); Converter.setCellText(row, cell, 41, Converter.toBlank(data.getXhckje()), cellStyle); Converter.setCellText(row, cell, 42, Converter.toBlank(data.getQmkc()), cellStyle); Converter.setCellText(row, cell, 43, Converter.toBlank(data.getQmkcje()), cellStyle); } if (i == size - 1) { if (select == 1) { StorageTotal sum = (StorageTotal) obj; Converter.setCellText(rows, cell, 0, "? ", cellStyle); Converter.setCellText(rows, cell, 3, Converter.toBlank(sum.getQckc()), cellStyle); Converter.setCellText(rows, cell, 4, Converter.toBlank(sum.getQckcje()), cellStyle); Converter.setCellText(rows, cell, 5, Converter.toBlank(sum.getQjrk()), cellStyle); Converter.setCellText(rows, cell, 6, Converter.toBlank(sum.getQjrkje()), cellStyle); Converter.setCellText(rows, cell, 7, Converter.toBlank(sum.getQjck()), cellStyle); Converter.setCellText(rows, cell, 8, Converter.toBlank(sum.getQjckje()), cellStyle); Converter.setCellText(rows, cell, 9, Converter.toBlank(sum.getQmkc()), cellStyle); Converter.setCellText(rows, cell, 10, Converter.toBlank(sum.getQmkcje()), cellStyle); } if (select == 2) { StorageMilde sum = (StorageMilde) obj; Converter.setCellText(rows, cell, 0, Converter.toBlank("? "), cellStyle); Converter.setCellText(rows, cell, 14, Converter.toBlank(sum.getQckc()), cellStyle); Converter.setCellText(rows, cell, 15, Converter.toBlank(sum.getQckcje()), cellStyle); Converter.setCellText(rows, cell, 16, Converter.toBlank(sum.getQjrk()), cellStyle); Converter.setCellText(rows, cell, 17, Converter.toBlank(sum.getQjrkje()), cellStyle); Converter.setCellText(rows, cell, 18, Converter.toBlank(sum.getQjck()), cellStyle); Converter.setCellText(rows, cell, 19, Converter.toBlank(sum.getQjckje()), cellStyle); Converter.setCellText(rows, cell, 20, Converter.toBlank(sum.getQmkc()), cellStyle); Converter.setCellText(rows, cell, 21, Converter.toBlank(sum.getQmkcje()), cellStyle); } if (select == 3) { StorageDetail sum = (StorageDetail) obj; Converter.setCellText(rows, cell, 0, Converter.toBlank("? "), cellStyle); // Converter.setCellText(rows, cell, 1,Converter.toBlank(sum.getPharmacycode()), cellStyle); // Converter.setCellText(rows, cell, 2,Converter.toBlank(sum.getName()), cellStyle); // Converter.setCellText(rows, cell, 3,Converter.toBlank(sum.getMatcode()), cellStyle); // Converter.setCellText(rows, cell, 4,Converter.toBlank(sum.getBarcode()), cellStyle); // Converter.setCellText(rows, cell, 5,Converter.toBlank(sum.getMatname()), cellStyle); // Converter.setCellText(rows, cell, 6,Converter.toBlank(sum.getCommonname()), cellStyle); // Converter.setCellText(rows, cell, 7,Converter.toBlank(sum.getDosage()), cellStyle); // Converter.setCellText(rows, cell, 8,Converter.toBlank(sum.getSpec()), cellStyle); // Converter.setCellText(rows, cell, 9,Converter.toBlank(sum.getUnitname()), cellStyle); // Converter.setCellText(rows, cell, 10,Converter.toBlank(sum.getManufname()), cellStyle); // Converter.setCellText(rows, cell, 11,Converter.toBlank(sum.getProductarea()), cellStyle); // Converter.setCellText(rows, cell, 12,Converter.toBlank(sum.getLicensenum()), cellStyle); // Converter.setCellText(rows, cell, 13,Converter.toBlank(sum.getRetail()), cellStyle); Converter.setCellText(rows, cell, 14, Converter.toBlank(sum.getQckc()), cellStyle); Converter.setCellText(rows, cell, 15, Converter.toBlank(sum.getQckcje()), cellStyle); Converter.setCellText(rows, cell, 16, Converter.toBlank(sum.getCg()), cellStyle); Converter.setCellText(rows, cell, 17, Converter.toBlank(sum.getCgje()), cellStyle); Converter.setCellText(rows, cell, 18, Converter.toBlank(sum.getPyrk()), cellStyle); Converter.setCellText(rows, cell, 19, Converter.toBlank(sum.getPyrkje()), cellStyle); Converter.setCellText(rows, cell, 20, Converter.toBlank(sum.getQtrk()), cellStyle); Converter.setCellText(rows, cell, 21, Converter.toBlank(sum.getQtrkje()), cellStyle); Converter.setCellText(rows, cell, 22, Converter.toBlank(sum.getClrk()), cellStyle); Converter.setCellText(rows, cell, 23, Converter.toBlank(sum.getClrkje()), cellStyle); Converter.setCellText(rows, cell, 24, Converter.toBlank(sum.getPsrk()), cellStyle); Converter.setCellText(rows, cell, 25, Converter.toBlank(sum.getPsrkje()), cellStyle); Converter.setCellText(rows, cell, 26, Converter.toBlank(sum.getQcrk()), cellStyle); Converter.setCellText(rows, cell, 27, Converter.toBlank(sum.getQcrkje()), cellStyle); Converter.setCellText(rows, cell, 28, Converter.toBlank(sum.getXsck()), cellStyle); Converter.setCellText(rows, cell, 29, Converter.toBlank(sum.getXsckje()), cellStyle); Converter.setCellText(rows, cell, 30, Converter.toBlank(sum.getPkck()), cellStyle); Converter.setCellText(rows, cell, 31, Converter.toBlank(sum.getPkckje()), cellStyle); Converter.setCellText(rows, cell, 32, Converter.toBlank(sum.getQtck()), cellStyle); Converter.setCellText(rows, cell, 33, Converter.toBlank(sum.getQtckje()), cellStyle); Converter.setCellText(rows, cell, 34, Converter.toBlank(sum.getClck()), cellStyle); Converter.setCellText(rows, cell, 35, Converter.toBlank(sum.getClckje()), cellStyle); Converter.setCellText(rows, cell, 36, Converter.toBlank(sum.getPsck()), cellStyle); Converter.setCellText(rows, cell, 37, Converter.toBlank(sum.getPsckje()), cellStyle); Converter.setCellText(rows, cell, 38, Converter.toBlank(sum.getQdck()), cellStyle); Converter.setCellText(rows, cell, 39, Converter.toBlank(sum.getQdckje()), cellStyle); Converter.setCellText(rows, cell, 40, Converter.toBlank(sum.getXhck()), cellStyle); Converter.setCellText(rows, cell, 41, Converter.toBlank(sum.getXhckje()), cellStyle); Converter.setCellText(rows, cell, 42, Converter.toBlank(sum.getQmkc()), cellStyle); Converter.setCellText(rows, cell, 43, Converter.toBlank(sum.getQmkcje()), cellStyle); } } } } return workbook; } catch (Exception e) { e.printStackTrace(); return null; } }
From source file:com.haulmont.yarg.formatters.impl.xls.hints.CustomCellStyleHint.java
License:Apache License
@Override public void apply() { for (DataObject dataObject : data) { HSSFCell templateCell = dataObject.templateCell; HSSFCell resultCell = dataObject.resultCell; BandData bandData = dataObject.bandData; HSSFWorkbook resultWorkbook = resultCell.getSheet().getWorkbook(); HSSFWorkbook templateWorkbook = templateCell.getSheet().getWorkbook(); String templateCellValue = templateCell.getStringCellValue(); Matcher matcher = pattern.matcher(templateCellValue); if (matcher.find()) { String paramName = matcher.group(1); String styleName = (String) bandData.getParameterValue(paramName); if (styleName == null) continue; HSSFCellStyle cellStyle = styleCache.getStyleByName(styleName); if (cellStyle == null) continue; HSSFCellStyle resultStyle = styleCache.getNamedCachedStyle(cellStyle); if (resultStyle == null) { HSSFCellStyle newStyle = resultWorkbook.createCellStyle(); // color newStyle.setFillBackgroundColor(cellStyle.getFillBackgroundColor()); newStyle.setFillForegroundColor(cellStyle.getFillForegroundColor()); newStyle.setFillPattern(cellStyle.getFillPattern()); // borders newStyle.setBorderLeft(cellStyle.getBorderLeft()); newStyle.setBorderRight(cellStyle.getBorderRight()); newStyle.setBorderTop(cellStyle.getBorderTop()); newStyle.setBorderBottom(cellStyle.getBorderBottom()); // border colors newStyle.setLeftBorderColor(cellStyle.getLeftBorderColor()); newStyle.setRightBorderColor(cellStyle.getRightBorderColor()); newStyle.setBottomBorderColor(cellStyle.getBottomBorderColor()); newStyle.setTopBorderColor(cellStyle.getTopBorderColor()); // alignment newStyle.setAlignment(cellStyle.getAlignment()); newStyle.setVerticalAlignment(cellStyle.getVerticalAlignment()); // misc DataFormat dataFormat = resultWorkbook.getCreationHelper().createDataFormat(); newStyle.setDataFormat(dataFormat.getFormat(cellStyle.getDataFormatString())); newStyle.setHidden(cellStyle.getHidden()); newStyle.setLocked(cellStyle.getLocked()); newStyle.setIndention(cellStyle.getIndention()); newStyle.setRotation(cellStyle.getRotation()); newStyle.setWrapText(cellStyle.getWrapText()); // font HSSFFont cellFont = cellStyle.getFont(templateWorkbook); HSSFFont newFont = fontCache.getFontByTemplate(cellFont); if (newFont == null) { newFont = resultWorkbook.createFont(); newFont.setFontName(cellFont.getFontName()); newFont.setItalic(cellFont.getItalic()); newFont.setStrikeout(cellFont.getStrikeout()); newFont.setTypeOffset(cellFont.getTypeOffset()); newFont.setBoldweight(cellFont.getBoldweight()); newFont.setCharSet(cellFont.getCharSet()); newFont.setColor(cellFont.getColor()); newFont.setUnderline(cellFont.getUnderline()); newFont.setFontHeight(cellFont.getFontHeight()); newFont.setFontHeightInPoints(cellFont.getFontHeightInPoints()); fontCache.addCachedFont(cellFont, newFont); }//from w ww . ja va 2 s .c o m newStyle.setFont(newFont); resultStyle = newStyle; styleCache.addCachedNamedStyle(cellStyle, resultStyle); } fixNeighbourCellBorders(cellStyle, resultCell); resultCell.setCellStyle(resultStyle); Sheet sheet = resultCell.getSheet(); for (int i = 0; i < sheet.getNumMergedRegions(); i++) { CellRangeAddress mergedRegion = sheet.getMergedRegion(i); if (mergedRegion.isInRange(resultCell.getRowIndex(), resultCell.getColumnIndex())) { int firstRow = mergedRegion.getFirstRow(); int lastRow = mergedRegion.getLastRow(); int firstCol = mergedRegion.getFirstColumn(); int lastCol = mergedRegion.getLastColumn(); for (int row = firstRow; row <= lastRow; row++) for (int col = firstCol; col <= lastCol; col++) sheet.getRow(row).getCell(col).setCellStyle(resultStyle); // cell includes only in one merged region break; } } } } }
From source file:com.insoul.ti.controller.ContestProjectEntryController.java
@RequestMapping("/download") public ModelAndView download(HttpServletResponse response) throws Exception { HSSFWorkbook workbook = new HSSFWorkbook();// Excel HSSFSheet sheet = workbook.createSheet();// ExcelSheet sheet.createFreezePane(1, 2);// // /*from w ww. j a v a2 s. c o m*/ sheet.setColumnWidth(0, 1000); sheet.setColumnWidth(1, 3500); sheet.setColumnWidth(2, 3500); sheet.setColumnWidth(3, 6500); sheet.setColumnWidth(4, 6500); sheet.setColumnWidth(5, 6500); sheet.setColumnWidth(6, 6500); sheet.setColumnWidth(7, 2500); // Sheet? // HSSFCellStyle sheetStyle = workbook.createCellStyle(); // // sheetStyle.setFillBackgroundColor(HSSFColor.GREY_25_PERCENT.index); // ? // sheetStyle.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index); // ? // sheetStyle.setFillPattern(HSSFCellStyle.FINE_DOTS); // ? // for (int i = 0; i <= 14; i++) { // sheet.setDefaultColumnStyle((short) i, sheetStyle); // } // HSSFFont headfont = workbook.createFont(); headfont.setFontName(""); headfont.setFontHeightInPoints((short) 22);// ? headfont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);// // ?? HSSFCellStyle headstyle = workbook.createCellStyle(); headstyle.setFont(headfont); headstyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);// ? headstyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// headstyle.setLocked(true); headstyle.setWrapText(true);// ? // ?? HSSFFont columnHeadFont = workbook.createFont(); columnHeadFont.setFontName(""); columnHeadFont.setFontHeightInPoints((short) 10); columnHeadFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); // ? HSSFCellStyle columnHeadStyle = workbook.createCellStyle(); columnHeadStyle.setFont(columnHeadFont); columnHeadStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);// ? columnHeadStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// columnHeadStyle.setLocked(true); columnHeadStyle.setWrapText(true); columnHeadStyle.setLeftBorderColor(HSSFColor.BLACK.index);// columnHeadStyle.setBorderLeft((short) 1);// ? columnHeadStyle.setRightBorderColor(HSSFColor.BLACK.index);// ? columnHeadStyle.setBorderRight((short) 1);// ? columnHeadStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); // ? columnHeadStyle.setBottomBorderColor(HSSFColor.BLACK.index); // ? // ???? columnHeadStyle.setFillForegroundColor(HSSFColor.WHITE.index); HSSFFont font = workbook.createFont(); font.setFontName(""); font.setFontHeightInPoints((short) 10); // ?? HSSFCellStyle style = workbook.createCellStyle(); style.setFont(font); style.setAlignment(HSSFCellStyle.ALIGN_LEFT);// ? style.setVerticalAlignment(HSSFCellStyle.VERTICAL_TOP);// style.setWrapText(true); style.setLeftBorderColor(HSSFColor.BLACK.index); style.setBorderLeft((short) 1); style.setRightBorderColor(HSSFColor.BLACK.index); style.setBorderRight((short) 1); style.setBorderBottom(HSSFCellStyle.BORDER_THIN); // ? style.setBottomBorderColor(HSSFColor.BLACK.index); // ? style.setFillForegroundColor(HSSFColor.WHITE.index);// ? // ?? HSSFCellStyle centerstyle = workbook.createCellStyle(); centerstyle.setFont(font); centerstyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);// ? centerstyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// centerstyle.setWrapText(true); centerstyle.setLeftBorderColor(HSSFColor.BLACK.index); centerstyle.setBorderLeft((short) 1); centerstyle.setRightBorderColor(HSSFColor.BLACK.index); centerstyle.setBorderRight((short) 1); centerstyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); // ? centerstyle.setBottomBorderColor(HSSFColor.BLACK.index); // ? centerstyle.setFillForegroundColor(HSSFColor.WHITE.index);// ? try { // HSSFRow row0 = sheet.createRow(0); // row0.setHeight((short) 900); // HSSFCell cell0 = row0.createCell(0); cell0.setCellValue(new HSSFRichTextString("???")); cell0.setCellStyle(headstyle); /** * ?? ??0 ??0 ??0 ??0 */ CellRangeAddress range = new CellRangeAddress(0, 0, 0, 14); sheet.addMergedRegion(range); // HSSFRow row1 = sheet.createRow(1); row1.setHeight((short) 750); HSSFCell cell = row1.createCell(0); cell.setCellValue(new HSSFRichTextString("??")); cell.setCellStyle(columnHeadStyle); cell = row1.createCell(1); cell.setCellValue(new HSSFRichTextString("")); cell.setCellStyle(columnHeadStyle); cell = row1.createCell(2); cell.setCellValue(new HSSFRichTextString("??")); cell.setCellStyle(columnHeadStyle); cell = row1.createCell(3); cell.setCellValue(new HSSFRichTextString("")); cell.setCellStyle(columnHeadStyle); cell = row1.createCell(4); cell.setCellValue(new HSSFRichTextString("??")); cell.setCellStyle(columnHeadStyle); cell = row1.createCell(5); cell.setCellValue(new HSSFRichTextString("")); cell.setCellStyle(columnHeadStyle); cell = row1.createCell(6); cell.setCellValue(new HSSFRichTextString("?")); cell.setCellStyle(columnHeadStyle); cell = row1.createCell(7); cell.setCellValue(new HSSFRichTextString("")); cell.setCellStyle(columnHeadStyle); cell = row1.createCell(8); cell.setCellValue(new HSSFRichTextString("")); cell.setCellStyle(columnHeadStyle); cell = row1.createCell(9); cell.setCellValue(new HSSFRichTextString("??")); cell.setCellStyle(columnHeadStyle); cell = row1.createCell(10); cell.setCellValue(new HSSFRichTextString("??")); cell.setCellStyle(columnHeadStyle); cell = row1.createCell(11); cell.setCellValue(new HSSFRichTextString("")); cell.setCellStyle(columnHeadStyle); cell = row1.createCell(12); cell.setCellValue(new HSSFRichTextString("??")); cell.setCellStyle(columnHeadStyle); cell = row1.createCell(13); cell.setCellValue(new HSSFRichTextString("?")); cell.setCellStyle(columnHeadStyle); cell = row1.createCell(14); cell.setCellValue(new HSSFRichTextString("??")); cell.setCellStyle(columnHeadStyle); List<ContestEntry> projectList = contestEntryDAO.findAll(); int m = 2; int len = projectList.size(); for (int i = 0; i < len; i++) { ContestEntry c = projectList.get(i); HSSFRow row = sheet.createRow(m + i); cell = row.createCell(0); cell.setCellValue(new HSSFRichTextString(c.getId() + "")); cell.setCellStyle(style); cell = row.createCell(1); cell.setCellValue(new HSSFRichTextString(c.getLocation())); cell.setCellStyle(style); cell = row.createCell(2); cell.setCellValue(new HSSFRichTextString(c.getInstance())); cell.setCellStyle(style); cell = row.createCell(3); cell.setCellValue(new HSSFRichTextString(c.getIndustry())); cell.setCellStyle(style); cell = row.createCell(4); cell.setCellValue(new HSSFRichTextString(c.getLegalFormation())); cell.setCellStyle(style); cell = row.createCell(5); cell.setCellValue(new HSSFRichTextString(c.getRegtime())); cell.setCellStyle(style); cell = row.createCell(6); cell.setCellValue(new HSSFRichTextString(c.getEmployqty() + "")); cell.setCellStyle(style); cell = row.createCell(7); cell.setCellValue(new HSSFRichTextString(c.getLegalPerson())); cell.setCellStyle(style); cell = row.createCell(8); cell.setCellValue(new HSSFRichTextString(c.getUserCategory())); cell.setCellStyle(style); cell = row.createCell(9); cell.setCellValue(new HSSFRichTextString(c.getContact())); cell.setCellStyle(style); cell = row.createCell(10); cell.setCellValue(new HSSFRichTextString(c.getIdNumber())); cell.setCellStyle(style); cell = row.createCell(11); cell.setCellValue(new HSSFRichTextString(c.getBankName())); cell.setCellStyle(style); cell = row.createCell(12); cell.setCellValue(new HSSFRichTextString(c.getBankUserName())); cell.setCellStyle(style); cell = row.createCell(13); cell.setCellValue(new HSSFRichTextString(c.getBankAccount())); cell.setCellStyle(style); cell = row.createCell(14); cell.setCellValue(new HSSFRichTextString(c.getSupportMoney())); cell.setCellStyle(style); } String filename = System.nanoTime() + ".xls";// Excel?? response.setContentType("application/vnd.ms-excel"); response.setHeader("Content-disposition", "attachment;filename=" + filename); OutputStream ouputStream = response.getOutputStream(); workbook.write(ouputStream); ouputStream.flush(); ouputStream.close(); } catch (Exception e) { log.error("download excel Error.", e); } return null; }
From source file:com.sevenorcas.openstyle.app.service.spreadsheet.SpreadSheet.java
/** * Set a cell style as header/*from w w w . j av a 2s. c o m*/ * * Thanks to http://www.experts-exchange.com/Programming/Languages/Java/Q_24242777.html * * @param workbook * @param style id * @param rowStyle * @param column * @param wb * @return */ protected void setStyleHeader(HSSFWorkbook wb, HSSFCellStyle style, int styleId) { //EX1, int row, int column, SpreadsheetCell cell){ HSSFFont font = wb.createFont(); font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); style.setFont(font); style.setLocked(true); style.setBottomBorderColor(HSSFColor.WHITE.index); style.setLeftBorderColor(HSSFColor.WHITE.index); style.setRightBorderColor(HSSFColor.WHITE.index); style.setTopBorderColor(HSSFColor.WHITE.index); style.setFillForegroundColor(getHeaderBGColorIndex()); style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); }
From source file:net.sf.jasperreports.engine.export.JRXlsExporter.java
License:Open Source License
protected HSSFCellStyle getLoadedCellStyle(StyleInfo style) { HSSFCellStyle cellStyle = loadedCellStyles.get(style); if (cellStyle == null) { cellStyle = workbook.createCellStyle(); cellStyle.setFillForegroundColor(style.backcolor); cellStyle.setFillPattern(style.mode); cellStyle.setAlignment(style.horizontalAlignment); cellStyle.setVerticalAlignment(style.verticalAlignment); cellStyle.setRotation(style.rotation); cellStyle.setFont(style.font);// ww w . ja v a2s . c o m cellStyle.setWrapText(style.lcWrapText); cellStyle.setLocked(style.lcCellLocked); cellStyle.setHidden(style.lcCellHidden); if (style.hasDataFormat()) { cellStyle.setDataFormat(style.getDataFormat()); } boolean isIgnoreCellBorder = getCurrentItemConfiguration().isIgnoreCellBorder(); if (!isIgnoreCellBorder) { BoxStyle box = style.box; cellStyle.setBorderTop(box.borderStyle[BoxStyle.TOP]); cellStyle.setTopBorderColor(box.borderColour[BoxStyle.TOP]); cellStyle.setBorderLeft(box.borderStyle[BoxStyle.LEFT]); cellStyle.setLeftBorderColor(box.borderColour[BoxStyle.LEFT]); cellStyle.setBorderBottom(box.borderStyle[BoxStyle.BOTTOM]); cellStyle.setBottomBorderColor(box.borderColour[BoxStyle.BOTTOM]); cellStyle.setBorderRight(box.borderStyle[BoxStyle.RIGHT]); cellStyle.setRightBorderColor(box.borderColour[BoxStyle.RIGHT]); } loadedCellStyles.put(style, cellStyle); } return cellStyle; }
From source file:net.sf.jasperreports.engine.export.JRXlsMetadataExporter.java
License:Open Source License
protected HSSFCellStyle getLoadedCellStyle(StyleInfo style) { HSSFCellStyle cellStyle = loadedCellStyles.get(style); if (cellStyle == null) { cellStyle = workbook.createCellStyle(); cellStyle.setFillForegroundColor(style.backcolor); cellStyle.setFillPattern(style.mode); cellStyle.setAlignment(style.horizontalAlignment); cellStyle.setVerticalAlignment(style.verticalAlignment); cellStyle.setRotation(style.rotation); cellStyle.setFont(style.font);//from w ww .j a v a2 s . co m cellStyle.setWrapText(style.lcWrapText); cellStyle.setLocked(style.lcCellLocked); cellStyle.setHidden(style.lcCellHidden); if (style.hasDataFormat()) { cellStyle.setDataFormat(style.getDataFormat()); } if (!getCurrentItemConfiguration().isIgnoreCellBorder()) { BoxStyle box = style.box; cellStyle.setBorderTop(box.borderStyle[BoxStyle.TOP]); cellStyle.setTopBorderColor(box.borderColour[BoxStyle.TOP]); cellStyle.setBorderLeft(box.borderStyle[BoxStyle.LEFT]); cellStyle.setLeftBorderColor(box.borderColour[BoxStyle.LEFT]); cellStyle.setBorderBottom(box.borderStyle[BoxStyle.BOTTOM]); cellStyle.setBottomBorderColor(box.borderColour[BoxStyle.BOTTOM]); cellStyle.setBorderRight(box.borderStyle[BoxStyle.RIGHT]); cellStyle.setRightBorderColor(box.borderColour[BoxStyle.RIGHT]); } loadedCellStyles.put(style, cellStyle); } return cellStyle; }
From source file:org.apache.cocoon.components.elementprocessor.impl.poi.hssf.elements.EPStyle.java
License:Apache License
/** * Override of Initialize() implementation * @param attributes the array of Attribute instances; may be empty, will * never be null// w ww . jav a 2 s . c om * @param parent the parent ElementProcessor; may be null * @exception IOException if anything is wrong */ public void initialize(final Attribute[] attributes, final ElementProcessor parent) throws IOException { super.initialize(attributes, parent); EPStyleRegion sregion = (EPStyleRegion) parent; if (sregion.isValid()) { Hashtable colorhash = sregion.getColorHash(); HSSFCellStyle style = sregion.getStyle(); short cnvhalign = convertAlignment(getHorizontalAlignment().getCode()); style.setAlignment(cnvhalign); short cnvvalign = convertVAlignment(getVerticalAlignment().getCode()); style.setVerticalAlignment(cnvvalign); style.setFillPattern((short) getShade()); Workbook workbook = getWorkbook(); HSSFDataFormat dataformat = workbook.createDataFormat(); if (getShade() == 1) { // TODO: change to constant when upgrade to new HSSF // solid w/foreground, bg doesn't matter if (getLogger().isDebugEnabled()) { getLogger().debug("shade = 1"); } HSSFColor color = (HSSFColor) colorhash.get(getBackgroundColor().toString()); if (color == null) { if (getLogger().isDebugEnabled()) { getLogger().debug("s1 BG couldn't find color for " + getBackgroundColor().toString()); } color = new HSSFColor.WHITE(); } style.setFillForegroundColor(color.getIndex()); color = (HSSFColor) colorhash.get(getPatternColor().toString()); if (color == null) { if (getLogger().isDebugEnabled()) { getLogger().debug("s1 PC couldn't find color for " + getPatternColor().toString()); } color = new HSSFColor.BLACK(); } style.setFillBackgroundColor(color.getIndex()); } else { HSSFColor color = (HSSFColor) colorhash.get(getBackgroundColor().toString()); if (color == null) { if (getLogger().isDebugEnabled()) { getLogger().debug("BG couldn't find color for " + getBackgroundColor().toString()); } color = new HSSFColor.BLACK(); } style.setFillBackgroundColor(color.getIndex()); color = (HSSFColor) colorhash.get(getPatternColor().toString()); if (color == null) { if (getLogger().isDebugEnabled()) { getLogger().debug("PC couldn't find color for " + getPatternColor().toString()); } color = new HSSFColor.WHITE(); } style.setFillForegroundColor(color.getIndex()); } style.setWrapText(getWrapText()); style.setLocked(true); String format = null; try { format = getFormat(); } catch (NullPointerException e) { format = _general_format; } if (!_general_format.equals(format)) { short valuenumber; format = kludgeForGnumericMisformats(format); format = kludgeForGnumericDateDivergence(format); if (getLogger().isDebugEnabled()) { getLogger().debug("setting format to " + format); } Object o = workbook.getValidate(format, dataformat.getFormat(format)); Short sh = null; sh = (Short) o; valuenumber = sh.shortValue(); style.setDataFormat(valuenumber); } } else { invalid = true; } }
From source file:org.orbeon.oxf.util.XLSUtils.java
License:Open Source License
public static void copyCell(HSSFWorkbook workbook, HSSFCell destination, HSSFCell source) { // Copy cell content destination.setCellType(source.getCellType()); switch (source.getCellType()) { case HSSFCell.CELL_TYPE_BOOLEAN: destination.setCellValue(source.getBooleanCellValue()); break;//www . java2 s .c o m case HSSFCell.CELL_TYPE_FORMULA: case HSSFCell.CELL_TYPE_STRING: destination.setCellValue(source.getStringCellValue()); break; case HSSFCell.CELL_TYPE_NUMERIC: destination.setCellValue(source.getNumericCellValue()); break; } // Copy cell style HSSFCellStyle sourceCellStyle = source.getCellStyle(); HSSFCellStyle destinationCellStyle = workbook.createCellStyle(); destinationCellStyle.setAlignment(sourceCellStyle.getAlignment()); destinationCellStyle.setBorderBottom(sourceCellStyle.getBorderBottom()); destinationCellStyle.setBorderLeft(sourceCellStyle.getBorderLeft()); destinationCellStyle.setBorderRight(sourceCellStyle.getBorderRight()); destinationCellStyle.setBorderTop(sourceCellStyle.getBorderTop()); destinationCellStyle.setBottomBorderColor(sourceCellStyle.getBottomBorderColor()); destinationCellStyle.setDataFormat(sourceCellStyle.getDataFormat()); destinationCellStyle.setFillBackgroundColor(sourceCellStyle.getFillForegroundColor()); destinationCellStyle.setFillForegroundColor(sourceCellStyle.getFillForegroundColor()); destinationCellStyle.setFillPattern(sourceCellStyle.getFillPattern()); destinationCellStyle.setFont(workbook.getFontAt(sourceCellStyle.getFontIndex())); destinationCellStyle.setHidden(sourceCellStyle.getHidden()); destinationCellStyle.setIndention(sourceCellStyle.getIndention()); destinationCellStyle.setLeftBorderColor(sourceCellStyle.getLeftBorderColor()); destinationCellStyle.setLocked(sourceCellStyle.getLocked()); destinationCellStyle.setRightBorderColor(sourceCellStyle.getRightBorderColor()); destinationCellStyle.setRotation(sourceCellStyle.getRotation()); destinationCellStyle.setTopBorderColor(sourceCellStyle.getTopBorderColor()); destinationCellStyle.setVerticalAlignment(sourceCellStyle.getVerticalAlignment()); destinationCellStyle.setWrapText(sourceCellStyle.getWrapText()); destination.setCellStyle(destinationCellStyle); }
From source file:org.sevenorcas.style.app.mod.ss.SpreadSheet.java
/** * Set a cell style as header//ww w . j ava2s. co m * * Thanks to http://www.experts-exchange.com/Programming/Languages/Java/Q_24242777.html * * @param workbook * @param style id * @param rowStyle * @param column * @param wb * @return */ protected void setStyleHeader(HSSFWorkbook wb, HSSFCellStyle style, int styleId) { //EX1, int row, int column, SpreadsheetCell cell){ HSSFFont font = wb.createFont(); font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); style.setFont(font); style.setLocked(true); style.setBottomBorderColor(HSSFColor.WHITE.index); style.setLeftBorderColor(HSSFColor.WHITE.index); style.setRightBorderColor(HSSFColor.WHITE.index); style.setTopBorderColor(HSSFColor.WHITE.index); style.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index); style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); }