List of usage examples for org.apache.poi.ss.usermodel Workbook createFont
Font createFont();
From source file:com.jeans.iservlet.controller.impl.ExportController.java
/** * ????/* w w w .j a va2s . c o m*/ * * @param ids * ???(AccessoryDischargeInvoice.id)? * @return * @throws IOException */ private void exportAccessoryDischarges(List<Long> idList) throws IOException { StringBuilder fn = new StringBuilder(getCurrentCompany().getName()); fn.append(" - ????.xlsx"); Workbook wb = new XSSFWorkbook(); DataFormat df = wb.createDataFormat(); // ?10?? Font bFont = wb.createFont(); bFont.setBoldweight(Font.BOLDWEIGHT_BOLD); bFont.setFontName(""); bFont.setFontHeightInPoints((short) 10); // ?10? Font nFont = wb.createFont(); nFont.setFontName(""); nFont.setFontHeightInPoints((short) 10); // ??1??????? CellStyle csLeftTextBold = wb.createCellStyle(); csLeftTextBold.setAlignment(CellStyle.ALIGN_LEFT); csLeftTextBold.setVerticalAlignment(CellStyle.VERTICAL_CENTER); csLeftTextBold.setFont(bFont); csLeftTextBold.setDataFormat(HSSFDataFormat.getBuiltinFormat("text")); csLeftTextBold.setWrapText(false); // ??2??????? CellStyle csLeftText = wb.createCellStyle(); csLeftText.setAlignment(CellStyle.ALIGN_LEFT); csLeftText.setVerticalAlignment(CellStyle.VERTICAL_CENTER); csLeftText.setFont(nFont); csLeftText.setDataFormat(HSSFDataFormat.getBuiltinFormat("text")); csLeftText.setWrapText(false); // ??3??????? CellStyle csLeftTime = wb.createCellStyle(); csLeftTime.setAlignment(CellStyle.ALIGN_LEFT); csLeftTime.setVerticalAlignment(CellStyle.VERTICAL_CENTER); csLeftTime.setFont(nFont); csLeftTime.setDataFormat(df.getFormat("yyyy-MM-dd HH:mm")); csLeftTime.setWrapText(false); // ??4??????? CellStyle csCenterTextBold = wb.createCellStyle(); csCenterTextBold.setAlignment(CellStyle.ALIGN_CENTER); csCenterTextBold.setVerticalAlignment(CellStyle.VERTICAL_CENTER); csCenterTextBold.setFont(bFont); csCenterTextBold.setDataFormat(HSSFDataFormat.getBuiltinFormat("text")); csCenterTextBold.setWrapText(false); // ??5??????? CellStyle csCenterText = wb.createCellStyle(); csCenterText.setAlignment(CellStyle.ALIGN_CENTER); csCenterText.setVerticalAlignment(CellStyle.VERTICAL_CENTER); csCenterText.setFont(nFont); csCenterText.setDataFormat(HSSFDataFormat.getBuiltinFormat("text")); csCenterText.setWrapText(false); // ??6??????? CellStyle csCenterNumber = wb.createCellStyle(); csCenterNumber.setAlignment(CellStyle.ALIGN_CENTER); csCenterNumber.setVerticalAlignment(CellStyle.VERTICAL_CENTER); csCenterNumber.setFont(nFont); csCenterNumber.setDataFormat(df.getFormat("#")); csCenterNumber.setWrapText(false); // ??7????8???? CellStyle csLeftId = wb.createCellStyle(); csLeftId.setAlignment(CellStyle.ALIGN_LEFT); csLeftId.setVerticalAlignment(CellStyle.VERTICAL_CENTER); csLeftId.setFont(nFont); csLeftId.setDataFormat(df.getFormat("#00000000")); csLeftId.setWrapText(false); // ??8??????? CellStyle csCenterNumberBold = wb.createCellStyle(); csCenterNumberBold.setAlignment(CellStyle.ALIGN_CENTER); csCenterNumberBold.setVerticalAlignment(CellStyle.VERTICAL_CENTER); csCenterNumberBold.setFont(bFont); csCenterNumberBold.setDataFormat(df.getFormat("#")); csCenterNumberBold.setWrapText(false); // ???sheet for (long id : idList) { AccessoryDischargeInvoice invoice = arService.loadDischargeInvoice(id); if (null == invoice) { continue; } Sheet sheet = wb.createSheet("?#" + id); // ??xxxxxx Row row = sheet.createRow(0); row.setHeightInPoints(20); Cell cell = row.createCell(0, Cell.CELL_TYPE_STRING); cell.setCellStyle(csLeftTextBold); cell.setCellValue("??"); cell = row.createCell(1, Cell.CELL_TYPE_NUMERIC); cell.setCellStyle(csLeftId); cell.setCellValue(id); // yyyy-MM-dd HH:mm row = sheet.createRow(1); row.setHeightInPoints(20); cell = row.createCell(0, Cell.CELL_TYPE_STRING); cell.setCellStyle(csLeftTextBold); cell.setCellValue(""); cell = row.createCell(1, Cell.CELL_TYPE_NUMERIC); cell.setCellStyle(csLeftTime); cell.setCellValue(invoice.getTime()); // xxx row = sheet.createRow(2); row.setHeightInPoints(20); cell = row.createCell(0, Cell.CELL_TYPE_STRING); cell.setCellStyle(csLeftTextBold); cell.setCellValue(""); cell = row.createCell(1, Cell.CELL_TYPE_STRING); cell.setCellStyle(csLeftText); cell.setCellValue(invoice.getApplicant().getName()); // xxx row = sheet.createRow(3); row.setHeightInPoints(20); cell = row.createCell(0, Cell.CELL_TYPE_STRING); cell.setCellStyle(csLeftTextBold); cell.setCellValue(""); cell = row.createCell(1, Cell.CELL_TYPE_STRING); cell.setCellStyle(csLeftText); cell.setCellValue(invoice.getOperator().getName()); // row = sheet.createRow(4); row.setHeightInPoints(20); for (int i = 0; i < 7; i++) { cell = row.createCell(i, Cell.CELL_TYPE_STRING); cell.setCellStyle(csCenterTextBold); cell.setCellValue(DIS_HEADERS[i]); sheet.setColumnWidth(i, DIS_HEADERS_WIDTH[i] * 256); } // List<AccessoryDischarge> discharges = invoice.getDischarges(); int rowNumber = 5; int totalQuantity = 0; for (AccessoryDischarge discharge : discharges) { Accessory acs = discharge.getAccessory(); row = sheet.createRow(rowNumber); row.setHeightInPoints(20); cell = row.createCell(0, Cell.CELL_TYPE_STRING); cell.setCellStyle(csCenterText); cell.setCellValue(acs.getType().getTitle()); cell = row.createCell(1, Cell.CELL_TYPE_STRING); cell.setCellStyle(csCenterText); cell.setCellValue(acs.getName()); cell = row.createCell(2, Cell.CELL_TYPE_STRING); cell.setCellStyle(csCenterText); cell.setCellValue(acs.getBrand()); cell = row.createCell(3, Cell.CELL_TYPE_STRING); cell.setCellStyle(csCenterText); cell.setCellValue(acs.getModel()); cell = row.createCell(4, Cell.CELL_TYPE_STRING); cell.setCellStyle(csCenterText); cell.setCellValue(acs.getDescription()); cell = row.createCell(5, Cell.CELL_TYPE_NUMERIC); cell.setCellStyle(csCenterNumber); cell.setCellValue(discharge.getQuantity()); totalQuantity += discharge.getQuantity(); cell = row.createCell(6, Cell.CELL_TYPE_STRING); cell.setCellStyle(csCenterText); cell.setCellValue(acs.getUnit()); rowNumber++; } // ? row = sheet.createRow(rowNumber); row.setHeightInPoints(20); cell = row.createCell(0, Cell.CELL_TYPE_STRING); cell.setCellStyle(csCenterTextBold); cell.setCellValue("?"); cell = row.createCell(1, Cell.CELL_TYPE_NUMERIC); cell.setCellStyle(csCenterNumberBold); cell.setCellValue(discharges.size()); cell = row.createCell(5, Cell.CELL_TYPE_NUMERIC); cell.setCellStyle(csCenterNumberBold); cell.setCellValue(totalQuantity); } String filename = null; if (isIE()) { filename = URLEncoder.encode(fn.toString(), "UTF-8").replaceAll("\\+", "%20"); } else { filename = new String(fn.toString().getBytes("UTF-8"), "iso8859-1"); } response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"); response.setHeader("Content-disposition", "attachment; filename=" + filename); BufferedOutputStream out = new BufferedOutputStream(response.getOutputStream(), 4096); wb.write(out); wb.close(); out.close(); }
From source file:com.jeans.iservlet.controller.impl.ExportController.java
/** * ?????/* w ww .j av a 2 s .c om*/ * * @param ids * ??(AccessoryInvoiceDetail.id)? * @return * @throws IOException */ private void exportAccessoryRegistries(List<Long> idList) throws IOException { StringBuilder fn = new StringBuilder(getCurrentCompany().getName()); fn.append(" - ???.xlsx"); Workbook wb = new XSSFWorkbook(); DataFormat df = wb.createDataFormat(); // ?10?? Font bFont = wb.createFont(); bFont.setBoldweight(Font.BOLDWEIGHT_BOLD); bFont.setFontName(""); bFont.setFontHeightInPoints((short) 10); // ?10? Font nFont = wb.createFont(); nFont.setFontName(""); nFont.setFontHeightInPoints((short) 10); // ??1??????? CellStyle csLeftTextBold = wb.createCellStyle(); csLeftTextBold.setAlignment(CellStyle.ALIGN_LEFT); csLeftTextBold.setVerticalAlignment(CellStyle.VERTICAL_CENTER); csLeftTextBold.setFont(bFont); csLeftTextBold.setDataFormat(HSSFDataFormat.getBuiltinFormat("text")); csLeftTextBold.setWrapText(false); // ??2??????? CellStyle csLeftText = wb.createCellStyle(); csLeftText.setAlignment(CellStyle.ALIGN_LEFT); csLeftText.setVerticalAlignment(CellStyle.VERTICAL_CENTER); csLeftText.setFont(nFont); csLeftText.setDataFormat(HSSFDataFormat.getBuiltinFormat("text")); csLeftText.setWrapText(false); // ??3??????? CellStyle csCenterTime = wb.createCellStyle(); csCenterTime.setAlignment(CellStyle.ALIGN_CENTER); csCenterTime.setVerticalAlignment(CellStyle.VERTICAL_CENTER); csCenterTime.setFont(nFont); csCenterTime.setDataFormat(df.getFormat("yyyy-MM-dd HH:mm")); csCenterTime.setWrapText(false); // ??4??????? CellStyle csCenterTextBold = wb.createCellStyle(); csCenterTextBold.setAlignment(CellStyle.ALIGN_CENTER); csCenterTextBold.setVerticalAlignment(CellStyle.VERTICAL_CENTER); csCenterTextBold.setFont(bFont); csCenterTextBold.setDataFormat(HSSFDataFormat.getBuiltinFormat("text")); csCenterTextBold.setWrapText(false); // ??5??????? CellStyle csCenterText = wb.createCellStyle(); csCenterText.setAlignment(CellStyle.ALIGN_CENTER); csCenterText.setVerticalAlignment(CellStyle.VERTICAL_CENTER); csCenterText.setFont(nFont); csCenterText.setDataFormat(HSSFDataFormat.getBuiltinFormat("text")); csCenterText.setWrapText(false); // ??6??????? CellStyle csCenterNumber = wb.createCellStyle(); csCenterNumber.setAlignment(CellStyle.ALIGN_CENTER); csCenterNumber.setVerticalAlignment(CellStyle.VERTICAL_CENTER); csCenterNumber.setFont(nFont); csCenterNumber.setDataFormat(df.getFormat("#")); csCenterNumber.setWrapText(false); // ??7????8???? CellStyle csLeftId = wb.createCellStyle(); csLeftId.setAlignment(CellStyle.ALIGN_LEFT); csLeftId.setVerticalAlignment(CellStyle.VERTICAL_CENTER); csLeftId.setFont(nFont); csLeftId.setDataFormat(df.getFormat("#00000000")); csLeftId.setWrapText(false); // ??8????8???? CellStyle csCenterId = wb.createCellStyle(); csCenterId.setAlignment(CellStyle.ALIGN_CENTER); csCenterId.setVerticalAlignment(CellStyle.VERTICAL_CENTER); csCenterId.setFont(nFont); csCenterId.setDataFormat(df.getFormat("#00000000")); csCenterId.setWrapText(false); // ??9??????? CellStyle csCenterNumberBold = wb.createCellStyle(); csCenterNumberBold.setAlignment(CellStyle.ALIGN_CENTER); csCenterNumberBold.setVerticalAlignment(CellStyle.VERTICAL_CENTER); csCenterNumberBold.setFont(bFont); csCenterNumberBold.setDataFormat(df.getFormat("#")); csCenterNumberBold.setWrapText(false); // ????sheet long currAcsId = 0; Sheet sheet = null; int rowNumber = 0, totalIn = 0, totalOut = 0; for (long id : idList) { AccessoryInvoiceDetail registry = arService.loadRegistry(id); if (null == registry) { continue; } Accessory acs = registry.getAccessory(); if (acs.getId() != currAcsId) { // ???sheet?? if (null != sheet) { Row row = sheet.createRow(rowNumber); row.setHeightInPoints(20); Cell cell = row.createCell(0, Cell.CELL_TYPE_STRING); cell.setCellStyle(csCenterTextBold); cell.setCellValue("?"); cell = row.createCell(3, Cell.CELL_TYPE_NUMERIC); cell.setCellStyle(csCenterNumberBold); cell.setCellValue(totalIn); cell = row.createCell(4, Cell.CELL_TYPE_NUMERIC); cell.setCellStyle(csCenterNumberBold); cell.setCellValue(totalOut); } // ?sheet? currAcsId = acs.getId(); sheet = wb.createSheet("???#" + currAcsId); // ??xxxxxx Row row = sheet.createRow(0); row.setHeightInPoints(20); Cell cell = row.createCell(1, Cell.CELL_TYPE_STRING); cell.setCellStyle(csLeftTextBold); cell.setCellValue("??"); cell = row.createCell(2, Cell.CELL_TYPE_NUMERIC); cell.setCellStyle(csLeftId); cell.setCellValue(currAcsId); // xxxxxx row = sheet.createRow(1); row.setHeightInPoints(20); cell = row.createCell(1, Cell.CELL_TYPE_STRING); cell.setCellStyle(csLeftTextBold); cell.setCellValue(""); cell = row.createCell(2, Cell.CELL_TYPE_STRING); cell.setCellStyle(csLeftText); cell.setCellValue(acs.getType().getTitle()); // ??xxxxxx row = sheet.createRow(2); row.setHeightInPoints(20); cell = row.createCell(1, Cell.CELL_TYPE_STRING); cell.setCellStyle(csLeftTextBold); cell.setCellValue("??"); cell = row.createCell(2, Cell.CELL_TYPE_STRING); cell.setCellStyle(csLeftText); cell.setCellValue(acs.getName()); // ?xxxxxx row = sheet.createRow(3); row.setHeightInPoints(20); cell = row.createCell(1, Cell.CELL_TYPE_STRING); cell.setCellStyle(csLeftTextBold); cell.setCellValue("?"); cell = row.createCell(2, Cell.CELL_TYPE_STRING); cell.setCellStyle(csLeftText); cell.setCellValue(acs.getBrand()); // ?xxxxxx row = sheet.createRow(4); row.setHeightInPoints(20); cell = row.createCell(1, Cell.CELL_TYPE_STRING); cell.setCellStyle(csLeftTextBold); cell.setCellValue("?"); cell = row.createCell(2, Cell.CELL_TYPE_STRING); cell.setCellStyle(csLeftText); cell.setCellValue(acs.getModel()); // ??xxxxxx row = sheet.createRow(5); row.setHeightInPoints(20); cell = row.createCell(1, Cell.CELL_TYPE_STRING); cell.setCellStyle(csLeftTextBold); cell.setCellValue("???"); cell = row.createCell(2, Cell.CELL_TYPE_STRING); cell.setCellStyle(csLeftText); cell.setCellValue(acs.getDescription()); // ???xxxxxx row = sheet.createRow(6); row.setHeightInPoints(20); cell = row.createCell(1, Cell.CELL_TYPE_STRING); cell.setCellStyle(csLeftTextBold); cell.setCellValue("???"); cell = row.createCell(2, Cell.CELL_TYPE_STRING); cell.setCellStyle(csLeftText); cell.setCellValue(acs.getUnit()); // row = sheet.createRow(7); row.setHeightInPoints(20); for (int i = 0; i < 6; i++) { cell = row.createCell(i, Cell.CELL_TYPE_STRING); cell.setCellStyle(csCenterTextBold); cell.setCellValue(REG_HEADERS[i]); sheet.setColumnWidth(i, REG_HEADERS_WIDTH[i] * 256); } rowNumber = 8; totalIn = 0; totalOut = 0; } // ? Row row = sheet.createRow(rowNumber); row.setHeightInPoints(20); Cell cell = row.createCell(0, Cell.CELL_TYPE_NUMERIC); cell.setCellStyle(csCenterNumber); cell.setCellValue(rowNumber - 7); cell = row.createCell(1, Cell.CELL_TYPE_NUMERIC); cell.setCellStyle(csCenterTime); cell.setCellValue(registry.getInvoice().getTime()); cell = row.createCell(2, Cell.CELL_TYPE_NUMERIC); cell.setCellStyle(csCenterId); cell.setCellValue(registry.getInvoice().getId()); cell = row.createCell(3, Cell.CELL_TYPE_NUMERIC); cell.setCellStyle(csCenterNumber); cell.setCellValue(id > 0 ? registry.getQuantity() : 0); cell = row.createCell(4, Cell.CELL_TYPE_NUMERIC); cell.setCellStyle(csCenterNumber); cell.setCellValue(id < 0 ? registry.getQuantity() : 0); cell = row.createCell(5, Cell.CELL_TYPE_STRING); cell.setCellStyle(csCenterText); cell.setCellValue( id < 0 ? ((AccessoryDischargeInvoice) registry.getInvoice()).getApplicant().getName() : ""); if (id > 0) { totalIn += registry.getQuantity(); } else { totalOut += registry.getQuantity(); } rowNumber++; } String filename = null; if (isIE()) { filename = URLEncoder.encode(fn.toString(), "UTF-8").replaceAll("\\+", "%20"); } else { filename = new String(fn.toString().getBytes("UTF-8"), "iso8859-1"); } response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"); response.setHeader("Content-disposition", "attachment; filename=" + filename); BufferedOutputStream out = new BufferedOutputStream(response.getOutputStream(), 4096); wb.write(out); wb.close(); out.close(); }
From source file:com.liferay.dynamic.data.lists.internal.exporter.DDLXLSExporter.java
License:Open Source License
protected CellStyle createCellStyle(Workbook workbook, boolean bold, String fontName, short heightInPoints) { Font font = workbook.createFont(); font.setBold(bold);/* w ww .j a va 2 s.c om*/ font.setFontHeightInPoints(heightInPoints); font.setFontName(fontName); CellStyle style = workbook.createCellStyle(); style.setFont(font); return style; }
From source file:com.lufs.java.apache.poi.example.CalendarDemo.java
License:Apache License
/** * cell styles used for formatting calendar sheets *///from ww w. j a va 2s . co m private static Map<String, CellStyle> createStyles(Workbook wb) { Map<String, CellStyle> styles = new HashMap<>(); short borderColor = IndexedColors.GREY_50_PERCENT.getIndex(); CellStyle style; Font titleFont = wb.createFont(); titleFont.setFontHeightInPoints((short) 48); titleFont.setColor(IndexedColors.DARK_BLUE.getIndex()); style = wb.createCellStyle(); style.setAlignment(HorizontalAlignment.CENTER); style.setVerticalAlignment(VerticalAlignment.CENTER); style.setFont(titleFont); styles.put("title", style); Font monthFont = wb.createFont(); monthFont.setFontHeightInPoints((short) 12); monthFont.setColor(IndexedColors.WHITE.getIndex()); monthFont.setBold(true); style = wb.createCellStyle(); style.setAlignment(HorizontalAlignment.CENTER); style.setVerticalAlignment(VerticalAlignment.CENTER); style.setFillForegroundColor(IndexedColors.DARK_BLUE.getIndex()); style.setFillPattern(FillPatternType.SOLID_FOREGROUND); style.setFont(monthFont); styles.put("month", style); Font dayFont = wb.createFont(); dayFont.setFontHeightInPoints((short) 14); dayFont.setBold(true); style = wb.createCellStyle(); style.setAlignment(HorizontalAlignment.LEFT); style.setVerticalAlignment(VerticalAlignment.TOP); style.setFillForegroundColor(IndexedColors.LIGHT_CORNFLOWER_BLUE.getIndex()); style.setFillPattern(FillPatternType.SOLID_FOREGROUND); style.setBorderLeft(BorderStyle.THIN); style.setLeftBorderColor(borderColor); style.setBorderBottom(BorderStyle.THIN); style.setBottomBorderColor(borderColor); style.setFont(dayFont); styles.put("weekend_left", style); style = wb.createCellStyle(); style.setAlignment(HorizontalAlignment.CENTER); style.setVerticalAlignment(VerticalAlignment.TOP); style.setFillForegroundColor(IndexedColors.LIGHT_CORNFLOWER_BLUE.getIndex()); style.setFillPattern(FillPatternType.SOLID_FOREGROUND); style.setBorderRight(BorderStyle.THIN); style.setRightBorderColor(borderColor); style.setBorderBottom(BorderStyle.THIN); style.setBottomBorderColor(borderColor); styles.put("weekend_right", style); style = wb.createCellStyle(); style.setAlignment(HorizontalAlignment.LEFT); style.setVerticalAlignment(VerticalAlignment.TOP); style.setBorderLeft(BorderStyle.THIN); style.setFillForegroundColor(IndexedColors.WHITE.getIndex()); style.setFillPattern(FillPatternType.SOLID_FOREGROUND); style.setLeftBorderColor(borderColor); style.setBorderBottom(BorderStyle.THIN); style.setBottomBorderColor(borderColor); style.setFont(dayFont); styles.put("workday_left", style); style = wb.createCellStyle(); style.setAlignment(HorizontalAlignment.CENTER); style.setVerticalAlignment(VerticalAlignment.TOP); style.setFillForegroundColor(IndexedColors.WHITE.getIndex()); style.setFillPattern(FillPatternType.SOLID_FOREGROUND); style.setBorderRight(BorderStyle.THIN); style.setRightBorderColor(borderColor); style.setBorderBottom(BorderStyle.THIN); style.setBottomBorderColor(borderColor); styles.put("workday_right", style); style = wb.createCellStyle(); style.setBorderLeft(BorderStyle.THIN); style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex()); style.setFillPattern(FillPatternType.SOLID_FOREGROUND); style.setBorderBottom(BorderStyle.THIN); style.setBottomBorderColor(borderColor); styles.put("grey_left", style); style = wb.createCellStyle(); style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex()); style.setFillPattern(FillPatternType.SOLID_FOREGROUND); style.setBorderRight(BorderStyle.THIN); style.setRightBorderColor(borderColor); style.setBorderBottom(BorderStyle.THIN); style.setBottomBorderColor(borderColor); styles.put("grey_right", style); return styles; }
From source file:com.lw.common.utils.ExcelUtil.java
/** * excel//from ww w . j a v a 2 s. co m * @param list ? * @param keys listmapkey? * @param columnNames excel?? * */ public static Workbook createWorkBook(List<Map<String, Object>> list, String[] keys, String columnNames[]) { // excel Workbook wb = new HSSFWorkbook(); // sheet?? Sheet sheet = wb.createSheet(list.get(0).get("sheetName").toString()); // ???n? for (int i = 0; i < keys.length; i++) { sheet.setColumnWidth((short) i, (short) (35.7 * 150)); } // Row row = sheet.createRow((short) 0); // ??? CellStyle cs = wb.createCellStyle(); CellStyle cs2 = wb.createCellStyle(); // ? Font f = wb.createFont(); Font f2 = wb.createFont(); // ???? f.setFontHeightInPoints((short) 10); f.setColor(IndexedColors.BLACK.getIndex()); f.setBoldweight(Font.BOLDWEIGHT_BOLD); // ?? f2.setFontHeightInPoints((short) 10); f2.setColor(IndexedColors.BLACK.getIndex()); // Font f3=wb.createFont(); // f3.setFontHeightInPoints((short) 10); // f3.setColor(IndexedColors.RED.getIndex()); // ????? cs.setFont(f); cs.setBorderLeft(CellStyle.BORDER_THIN); cs.setBorderRight(CellStyle.BORDER_THIN); cs.setBorderTop(CellStyle.BORDER_THIN); cs.setBorderBottom(CellStyle.BORDER_THIN); cs.setAlignment(CellStyle.ALIGN_CENTER); // ??? cs2.setFont(f2); cs2.setBorderLeft(CellStyle.BORDER_THIN); cs2.setBorderRight(CellStyle.BORDER_THIN); cs2.setBorderTop(CellStyle.BORDER_THIN); cs2.setBorderBottom(CellStyle.BORDER_THIN); cs2.setAlignment(CellStyle.ALIGN_CENTER); //?? for (int i = 0; i < columnNames.length; i++) { Cell cell = row.createCell(i); cell.setCellValue(columnNames[i]); cell.setCellStyle(cs); } //?? for (short i = 1; i < list.size(); i++) { // Row ,Cell , Row Cell 0 // sheet Row row1 = sheet.createRow((short) i); // row for (short j = 0; j < keys.length; j++) { Cell cell = row1.createCell(j); cell.setCellValue(list.get(i).get(keys[j]) == null ? " " : list.get(i).get(keys[j]).toString()); cell.setCellStyle(cs2); } } return wb; }
From source file:com.lwr.software.reporter.restservices.ReportExportService.java
License:Open Source License
public Response exportExcel(Report toExport, Set<ReportParameter> reportParams) { Workbook wb = new XSSFWorkbook(); Font boldFont = wb.createFont(); boldFont.setBold(true);//from w w w . j a v a2 s.co m CellStyle headerStyle = wb.createCellStyle(); headerStyle.setBorderBottom(BorderStyle.THIN); headerStyle.setBorderTop(BorderStyle.THIN); headerStyle.setBorderRight(BorderStyle.THIN); headerStyle.setBorderLeft(BorderStyle.THIN); headerStyle.setFillBackgroundColor(IndexedColors.BLUE.getIndex()); headerStyle.setFont(boldFont); CellStyle cellStyle = wb.createCellStyle(); cellStyle.setBorderBottom(BorderStyle.THIN); cellStyle.setBorderTop(BorderStyle.THIN); cellStyle.setBorderRight(BorderStyle.THIN); cellStyle.setBorderLeft(BorderStyle.THIN); CellStyle titleStyle = wb.createCellStyle(); titleStyle.setBorderBottom(BorderStyle.THIN); titleStyle.setBorderTop(BorderStyle.THIN); titleStyle.setBorderRight(BorderStyle.THIN); titleStyle.setBorderLeft(BorderStyle.THIN); List<RowElement> rows = toExport.getRows(); int sheetIndex = 0; for (RowElement rowElement : rows) { List<Element> elements = rowElement.getElements(); for (Element element : elements) { try { element.setParams(reportParams); element.init(); } catch (Exception e) { logger.error("Unable to init '" + element.getTitle() + "' element of report '" + toExport.getTitle() + "' Error " + e.getMessage(), e); return Response.serverError().entity("Unable to init '" + element.getTitle() + "' element of report '" + toExport.getTitle() + "' Error " + e.getMessage()).build(); } String sheetName = element.getTitle().substring(0, element.getTitle().length() > 30 ? 30 : element.getTitle().length()) + (sheetIndex++); Sheet sheet = wb.createSheet(sheetName); Row reportTitleRow = sheet.createRow(0); Cell reportTitleHeader = reportTitleRow.createCell(0); reportTitleHeader.setCellStyle(headerStyle); reportTitleHeader.setCellValue("Report Title:"); Cell reportTitleCell = reportTitleRow.createCell(1); reportTitleCell.setCellStyle(titleStyle); reportTitleCell.setCellValue(toExport.getTitle()); Row elementTitleRow = sheet.createRow(1); Cell elementTitleHeader = elementTitleRow.createCell(0); elementTitleHeader.setCellStyle(headerStyle); elementTitleHeader.setCellValue("Element Title:"); Cell elementTitleCell = elementTitleRow.createCell(1); elementTitleCell.setCellStyle(titleStyle); elementTitleCell.setCellValue(element.getTitle()); List<List<Object>> dataToExport = element.getData(); int rowIndex = 3; Row headerRow = sheet.createRow(rowIndex++); List<Object> unifiedHeaderRow = element.getHeader(); for (int i = 0; i < unifiedHeaderRow.size(); i++) { Cell headerCell = headerRow.createCell(i); String headerCellValue = unifiedHeaderRow.get(i).toString(); headerCell.setCellValue(headerCellValue); headerCell.setCellStyle(headerStyle); } for (int i = 0; i < dataToExport.size(); i++) { Row row = sheet.createRow(rowIndex++); List<Object> unifiedRow = dataToExport.get(i); int cellIndex = 0; for (Object cellValue : unifiedRow) { Cell cell = row.createCell(cellIndex); cell.setCellStyle(cellStyle); try { double val = Double.parseDouble(cellValue.toString()); cell.setCellValue(val); } catch (NumberFormatException e) { cell.setCellValue(cellValue.toString()); } cellIndex++; } } } } try { File file = new File(DashboardConstants.APPLN_TEMP_DIR + System.nanoTime()); logger.info("Export CSV temp file path is " + file.getAbsoluteFile()); wb.write(new FileOutputStream(file)); wb.close(); ResponseBuilder responseBuilder = Response.ok((Object) file); responseBuilder.header("Content-Type", "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"); responseBuilder.header("Content-Transfer-Encoding", "binary"); responseBuilder.header("Content-Disposition", "attachment;filename=" + file.getName()); responseBuilder.header("Content-Length", file.length()); Response responseToSend = responseBuilder.build(); file.deleteOnExit(); return responseToSend; } catch (Exception e1) { return Response.serverError() .entity("Unable to export " + toExport.getTitle() + " report " + e1.getMessage()).build(); } }
From source file:com.mycompany.excelreadandwrite.WritetoExcel.java
public void writeSongsListToExcel(List<Song> songList) { /*/*from ww w. j a v a2s. c om*/ Use XSSF for xlsx format and for xls use HSSF */ Workbook workbook = new XSSFWorkbook(); /* create new sheet */ Sheet songsSheet = workbook.createSheet("Albums"); XSSFCellStyle my_style = (XSSFCellStyle) workbook.createCellStyle(); /* Create XSSFFont object from the workbook */ XSSFFont my_font = (XSSFFont) workbook.createFont(); /* setting cell color */ CellStyle style = workbook.createCellStyle(); style.setFillForegroundColor(IndexedColors.LEMON_CHIFFON.getIndex()); style.setFillPattern(CellStyle.SOLID_FOREGROUND); /* setting Header color */ CellStyle style2 = workbook.createCellStyle(); style2.setFillForegroundColor(IndexedColors.DARK_RED.getIndex()); style2.setFillPattern(CellStyle.SOLID_FOREGROUND); Row rowName = songsSheet.createRow(1); /* Merging the cells */ songsSheet.addMergedRegion(new CellRangeAddress(1, 1, 2, 3)); /* Applying style to attribute name */ int nameCellIndex = 1; Cell namecell = rowName.createCell(nameCellIndex++); namecell.setCellValue("Name"); namecell.setCellStyle(style); Cell cel = rowName.createCell(nameCellIndex++); cel.setCellValue("Lastname, Firstname"); /* Applying underline to Name */ my_font.setUnderline(XSSFFont.U_SINGLE); my_style.setFont(my_font); /* Attaching the style to the cell */ CellStyle combined = workbook.createCellStyle(); combined.cloneStyleFrom(my_style); combined.cloneStyleFrom(style); cel.setCellStyle(combined); /* Applying colors to header */ Row rowMain = songsSheet.createRow(3); SheetConditionalFormatting sheetCF = songsSheet.getSheetConditionalFormatting(); ConditionalFormattingRule rule1 = sheetCF.createConditionalFormattingRule("3"); PatternFormatting fill1 = rule1.createPatternFormatting(); fill1.setFillBackgroundColor(IndexedColors.CORNFLOWER_BLUE.index); fill1.setFillPattern(PatternFormatting.SOLID_FOREGROUND); CellRangeAddress[] regions = { CellRangeAddress.valueOf("A4:G4") }; sheetCF.addConditionalFormatting(regions, rule1); /* setting new rule to apply alternate colors to cells having same Genre */ ConditionalFormattingRule rule2 = sheetCF.createConditionalFormattingRule("4"); PatternFormatting fill2 = rule2.createPatternFormatting(); fill2.setFillBackgroundColor(IndexedColors.LEMON_CHIFFON.index); fill2.setFillPattern(PatternFormatting.SOLID_FOREGROUND); CellRangeAddress[] regionsAction = { CellRangeAddress.valueOf("A5:G5"), CellRangeAddress.valueOf("A6:G6"), CellRangeAddress.valueOf("A7:G7"), CellRangeAddress.valueOf("A8:G8"), CellRangeAddress.valueOf("A13:G13"), CellRangeAddress.valueOf("A14:G14"), CellRangeAddress.valueOf("A15:G15"), CellRangeAddress.valueOf("A16:G16"), CellRangeAddress.valueOf("A23:G23"), CellRangeAddress.valueOf("A24:G24"), CellRangeAddress.valueOf("A25:G25"), CellRangeAddress.valueOf("A26:G26") }; /* setting new rule to apply alternate colors to cells having same Genre */ ConditionalFormattingRule rule3 = sheetCF.createConditionalFormattingRule("4"); PatternFormatting fill3 = rule3.createPatternFormatting(); fill3.setFillBackgroundColor(IndexedColors.LIGHT_CORNFLOWER_BLUE.index); fill3.setFillPattern(PatternFormatting.SOLID_FOREGROUND); CellRangeAddress[] regionsAdv = { CellRangeAddress.valueOf("A9:G9"), CellRangeAddress.valueOf("A10:G10"), CellRangeAddress.valueOf("A11:G11"), CellRangeAddress.valueOf("A12:G12"), CellRangeAddress.valueOf("A17:G17"), CellRangeAddress.valueOf("A18:G18"), CellRangeAddress.valueOf("A19:G19"), CellRangeAddress.valueOf("A20:G20"), CellRangeAddress.valueOf("A21:G21"), CellRangeAddress.valueOf("A22:G22"), CellRangeAddress.valueOf("A27:G27"), CellRangeAddress.valueOf("A28:G28"), CellRangeAddress.valueOf("A29:G29") }; /* Applying above created rule formatting to cells */ sheetCF.addConditionalFormatting(regionsAction, rule2); sheetCF.addConditionalFormatting(regionsAdv, rule3); /* Setting coloumn header values */ int mainCellIndex = 0; rowMain.createCell(mainCellIndex++).setCellValue("SNO"); rowMain.createCell(mainCellIndex++).setCellValue("Genre"); rowMain.createCell(mainCellIndex++).setCellValue("Rating"); rowMain.createCell(mainCellIndex++).setCellValue("Movie Name"); rowMain.createCell(mainCellIndex++).setCellValue("Director"); rowMain.createCell(mainCellIndex++).setCellValue("Release Date"); rowMain.createCell(mainCellIndex++).setCellValue("Budget"); /* populating cell values */ int rowIndex = 4; int sno = 1; for (Song song : songList) { if (song.getSno() != 0) { Row row = songsSheet.createRow(rowIndex++); int cellIndex = 0; /* first place in row is Sno */ row.createCell(cellIndex++).setCellValue(sno++); /* second place in row is Genre */ row.createCell(cellIndex++).setCellValue(song.getGenre()); /* third place in row is Critic score */ row.createCell(cellIndex++).setCellValue(song.getCriticscore()); /* fourth place in row is Album name */ row.createCell(cellIndex++).setCellValue(song.getAlbumname()); /* fifth place in row is Artist */ row.createCell(cellIndex++).setCellValue(song.getArtist()); /* sixth place in row is marks in date */ if (song.getReleasedate() != null) { Cell date = row.createCell(cellIndex++); DataFormat format = workbook.createDataFormat(); CellStyle dateStyle = workbook.createCellStyle(); dateStyle.setDataFormat(format.getFormat("dd-MMM-yyyy")); date.setCellStyle(dateStyle); date.setCellValue(song.getReleasedate()); /* auto-resizing columns */ songsSheet.autoSizeColumn(6); songsSheet.autoSizeColumn(5); songsSheet.autoSizeColumn(4); songsSheet.autoSizeColumn(3); songsSheet.autoSizeColumn(2); } } } /* writing this workbook to excel file. */ try { FileOutputStream fos = new FileOutputStream(FILE_PATH); workbook.write(fos); fos.close(); System.out.println(FILE_PATH + " is successfully written"); } catch (FileNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } }
From source file:com.mycompany.gannaraputakehomeexam.WritingToExcel.java
public void writeSongsToExcel(List<SongsList> songList) { /*/* w ww . j a v a 2 s . c o m*/ Use XSSF for xlsx format and for xls use HSSF */ Workbook workbook = new XSSFWorkbook(); /* create new sheet */ Sheet songsSheet = workbook.createSheet("Gannarapu_Output"); XSSFCellStyle my_style = (XSSFCellStyle) workbook.createCellStyle(); /* Create XSSFFont object from the workbook */ XSSFFont my_font = (XSSFFont) workbook.createFont(); XSSFFont font = (XSSFFont) workbook.createFont(); /* setting cell color */ CellStyle style = workbook.createCellStyle(); style.setFillForegroundColor(IndexedColors.LIGHT_GREEN.getIndex()); style.setFillPattern(CellStyle.SOLID_FOREGROUND); /* setting Header color */ CellStyle style2 = workbook.createCellStyle(); font.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD); style2.setFont(font); style2.setAlignment(CellStyle.ALIGN_CENTER); // style2.setFillForegroundColor(IndexedColors.DARK_RED.getIndex()); // style2.setFillPattern(CellStyle.SOLID_FOREGROUND); // Row rowName = songsSheet.createRow(1); /* Merging the cells */ songsSheet.addMergedRegion(new CellRangeAddress(1, 1, 2, 3)); /* Applying style to attribute name */ int nameCellIndex = 1; Cell namecell = rowName.createCell(nameCellIndex++); namecell.setCellValue("Name"); namecell.setCellStyle(style); Cell cel = rowName.createCell(nameCellIndex++); cel.setCellValue("Gannarapu, Anirudh"); /* Applying underline to Name */ my_font.setUnderline(XSSFFont.U_DOUBLE); my_style.setFont(my_font); /* Attaching the style to the cell */ CellStyle combined = workbook.createCellStyle(); combined.cloneStyleFrom(my_style); combined.cloneStyleFrom(style); cel.setCellStyle(combined); /* Applying colors to header */ Row rowMain = songsSheet.createRow(3); SheetConditionalFormatting sheetCF = songsSheet.getSheetConditionalFormatting(); ConditionalFormattingRule rule1 = sheetCF.createConditionalFormattingRule("5"); PatternFormatting fill1 = rule1.createPatternFormatting(); fill1.setFillBackgroundColor(IndexedColors.RED.index); fill1.setFillPattern(PatternFormatting.SOLID_FOREGROUND); CellRangeAddress[] regions = { CellRangeAddress.valueOf("A4:F4") }; sheetCF.addConditionalFormatting(regions, rule1); /* setting new rule to apply alternate colors to cells having same Genre */ ConditionalFormattingRule rule2 = sheetCF.createConditionalFormattingRule("4"); PatternFormatting fill2 = rule2.createPatternFormatting(); fill2.setFillBackgroundColor(IndexedColors.LEMON_CHIFFON.index); fill2.setFillPattern(PatternFormatting.SOLID_FOREGROUND); CellRangeAddress[] regionsAction = { CellRangeAddress.valueOf("A5:F5"), CellRangeAddress.valueOf("A6:F6"), CellRangeAddress.valueOf("A7:F7"), CellRangeAddress.valueOf("A8:F8"), CellRangeAddress.valueOf("A13:F13"), CellRangeAddress.valueOf("A14:F14"), CellRangeAddress.valueOf("A15:F15"), CellRangeAddress.valueOf("A16:F16"), CellRangeAddress.valueOf("A23:F23"), CellRangeAddress.valueOf("A24:F24"), CellRangeAddress.valueOf("A25:F25"), CellRangeAddress.valueOf("A26:F26") }; /* setting new rule to apply alternate colors to cells having same Genre */ ConditionalFormattingRule rule3 = sheetCF.createConditionalFormattingRule("4"); PatternFormatting fill3 = rule3.createPatternFormatting(); fill3.setFillBackgroundColor(IndexedColors.LIGHT_GREEN.index); fill3.setFillPattern(PatternFormatting.SOLID_FOREGROUND); CellRangeAddress[] regionsAdv = { CellRangeAddress.valueOf("A9:F9"), CellRangeAddress.valueOf("A10:F10"), CellRangeAddress.valueOf("A11:F11"), CellRangeAddress.valueOf("A12:F12"), CellRangeAddress.valueOf("A17:F17"), CellRangeAddress.valueOf("A18:F18"), CellRangeAddress.valueOf("A19:F19"), CellRangeAddress.valueOf("A20:F20"), CellRangeAddress.valueOf("A21:F21"), CellRangeAddress.valueOf("A22:F22"), CellRangeAddress.valueOf("A27:F27"), CellRangeAddress.valueOf("A28:F28"), CellRangeAddress.valueOf("A29:F29") }; /* Applying above created rule formatting to cells */ sheetCF.addConditionalFormatting(regionsAction, rule2); sheetCF.addConditionalFormatting(regionsAdv, rule3); /* Setting coloumn header values */ int mainCellIndex = 0; Cell SNO = rowMain.createCell(mainCellIndex++); SNO.setCellValue("SNO"); SNO.setCellStyle(style2); Cell gen = rowMain.createCell(mainCellIndex++); gen.setCellValue("Genre"); gen.setCellStyle(style2); Cell credit = rowMain.createCell(mainCellIndex++); credit.setCellValue("Credit Score"); credit.setCellStyle(style2); Cell name = rowMain.createCell(mainCellIndex++); name.setCellValue("Album Name"); name.setCellStyle(style2); Cell art = rowMain.createCell(mainCellIndex++); art.setCellValue("Artist"); art.setCellStyle(style2); Cell release = rowMain.createCell(mainCellIndex++); release.setCellValue("Release Date"); release.setCellStyle(style2); /* populating cell values */ int rowIndex = 4; int sno = 1; for (SongsList song : songList) { if (song.getSno() != 0) { Row row = songsSheet.createRow(rowIndex++); int cellIndex = 0; /* first place in row is Sno */ row.createCell(cellIndex++).setCellValue(sno++); /* second place in row is Genre */ row.createCell(cellIndex++).setCellValue(song.getGenre()); /* third place in row is Critic score */ row.createCell(cellIndex++).setCellValue(song.getCriticscore()); /* fourth place in row is Album name */ row.createCell(cellIndex++).setCellValue(song.getAlbumname()); /* fifth place in row is Artist */ row.createCell(cellIndex++).setCellValue(song.getArtist()); /* sixth place in row is marks in date */ if (song.getReleasedate() != null) { Cell date = row.createCell(cellIndex++); DataFormat format = workbook.createDataFormat(); CellStyle dateStyle = workbook.createCellStyle(); dateStyle.setDataFormat(format.getFormat("dd-MMM-yyyy")); date.setCellStyle(dateStyle); date.setCellValue(song.getReleasedate()); /* auto-resizing columns */ songsSheet.autoSizeColumn(6); songsSheet.autoSizeColumn(5); songsSheet.autoSizeColumn(4); songsSheet.autoSizeColumn(3); songsSheet.autoSizeColumn(2); } } } /* writing this workbook to excel file. */ try { FileOutputStream fos = new FileOutputStream(FILE_PATH); workbook.write(fos); fos.close(); System.out.println(FILE_PATH + " is successfully written"); } catch (FileNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } }
From source file:com.mycompany.gayamtakehomeexam.WritetoExcel.java
public void writeSongsListToExcel(List<Song> songList) { /*/* www.j a va2 s .c o m*/ Use XSSF for xlsx format and for xls use HSSF */ Workbook workbook = new XSSFWorkbook(); /* create new sheet */ Sheet songsSheet = workbook.createSheet("Albums"); XSSFCellStyle my_style = (XSSFCellStyle) workbook.createCellStyle(); /* Create XSSFFont object from the workbook */ XSSFFont my_font = (XSSFFont) workbook.createFont(); /* setting cell color */ CellStyle style = workbook.createCellStyle(); style.setFillForegroundColor(IndexedColors.LEMON_CHIFFON.getIndex()); style.setFillPattern(CellStyle.SOLID_FOREGROUND); CellStyle style1 = workbook.createCellStyle();//Create style Font font = workbook.createFont();//Create font font.setBoldweight(Font.BOLDWEIGHT_BOLD);//Make font bold style1.setFont(font);//set it to bold style1.setAlignment(CellStyle.ALIGN_CENTER); CellStyle style13 = workbook.createCellStyle();//Create style Font font13 = workbook.createFont();//Create font style13.setAlignment(CellStyle.ALIGN_LEFT); CellStyle style14 = workbook.createCellStyle();//Create style Font font14 = workbook.createFont();//Create font style14.setAlignment(CellStyle.ALIGN_RIGHT); /* setting Header color */ CellStyle style2 = workbook.createCellStyle(); style2.setFillForegroundColor(IndexedColors.LIGHT_ORANGE.getIndex()); style2.setFillBackgroundColor(IndexedColors.LIGHT_ORANGE.getIndex()); style2.setFillPattern(CellStyle.SOLID_FOREGROUND); Row rowName = songsSheet.createRow(1); /* Merging the cells */ songsSheet.addMergedRegion(new CellRangeAddress(1, 1, 2, 3)); /* Applying style to attribute name */ int nameCellIndex = 1; Cell namecell = rowName.createCell(nameCellIndex++); namecell.setCellValue("Name"); namecell.setCellStyle(style); Cell cel = rowName.createCell(nameCellIndex++); /* Applying underline to Name */ Font underlineFont = workbook.createFont(); underlineFont.setUnderline(HSSFFont.U_SINGLE); /* Attaching the style to the cell */ CellStyle combined = workbook.createCellStyle(); combined.setFont(underlineFont); cel.setCellStyle(combined); cel.setCellValue("Gayam, Prathibha"); /* Applying colors to header */ Row rowMain = songsSheet.createRow(3); SheetConditionalFormatting sheetCF = songsSheet.getSheetConditionalFormatting(); ConditionalFormattingRule rule1 = sheetCF.createConditionalFormattingRule("3"); PatternFormatting fill1 = rule1.createPatternFormatting(); fill1.setFillBackgroundColor(IndexedColors.LIGHT_ORANGE.index); fill1.setFillPattern(PatternFormatting.SOLID_FOREGROUND); CellRangeAddress[] regions = { CellRangeAddress.valueOf("A4:G4") }; sheetCF.addConditionalFormatting(regions, rule1); /* setting new rule to apply alternate colors to cells having same Genre */ ConditionalFormattingRule rule2 = sheetCF.createConditionalFormattingRule("4"); PatternFormatting fill2 = rule2.createPatternFormatting(); fill2.setFillBackgroundColor(IndexedColors.LEMON_CHIFFON.index); fill2.setFillPattern(PatternFormatting.SOLID_FOREGROUND); CellRangeAddress[] regionsAction = { CellRangeAddress.valueOf("A5:G5"), CellRangeAddress.valueOf("A6:G6"), CellRangeAddress.valueOf("A7:G7"), CellRangeAddress.valueOf("A8:G8"), CellRangeAddress.valueOf("A13:G13"), CellRangeAddress.valueOf("A14:G14"), CellRangeAddress.valueOf("A15:G15"), CellRangeAddress.valueOf("A16:G16"), CellRangeAddress.valueOf("A23:G23"), CellRangeAddress.valueOf("A24:G24"), CellRangeAddress.valueOf("A25:G25"), CellRangeAddress.valueOf("A26:G26") }; /* setting new rule to apply alternate colors to cells having same Genre */ ConditionalFormattingRule rule3 = sheetCF.createConditionalFormattingRule("4"); PatternFormatting fill3 = rule3.createPatternFormatting(); fill3.setFillBackgroundColor(IndexedColors.LIGHT_CORNFLOWER_BLUE.index); fill3.setFillPattern(PatternFormatting.SOLID_FOREGROUND); CellRangeAddress[] regionsAdv = { CellRangeAddress.valueOf("A9:G9"), CellRangeAddress.valueOf("A10:G10"), CellRangeAddress.valueOf("A11:G11"), CellRangeAddress.valueOf("A12:G12"), CellRangeAddress.valueOf("A17:G17"), CellRangeAddress.valueOf("A18:G18"), CellRangeAddress.valueOf("A19:G19"), CellRangeAddress.valueOf("A20:G20"), CellRangeAddress.valueOf("A21:G21"), CellRangeAddress.valueOf("A22:G22"), CellRangeAddress.valueOf("A27:G27"), CellRangeAddress.valueOf("A28:G28"), CellRangeAddress.valueOf("A29:G29") }; /* Applying above created rule formatting to cells */ sheetCF.addConditionalFormatting(regionsAction, rule2); sheetCF.addConditionalFormatting(regionsAdv, rule3); /* Setting coloumn header values */ int mainCellIndex = 0; Cell cell0 = rowMain.createCell(mainCellIndex++); cell0.setCellValue("SNO"); cell0.setCellStyle(style1); Cell cell1 = rowMain.createCell(mainCellIndex++); cell1.setCellValue("Genre"); cell1.setCellStyle(style1); Cell cell2 = rowMain.createCell(mainCellIndex++); cell2.setCellValue("Credit Score"); cell2.setCellStyle(style1); Cell cell3 = rowMain.createCell(mainCellIndex++); cell3.setCellValue("Album Name"); cell3.setCellStyle(style1); Cell cell4 = rowMain.createCell(mainCellIndex++); cell4.setCellValue("Artist"); cell4.setCellStyle(style1); Cell cell5 = rowMain.createCell(mainCellIndex++); cell5.setCellValue("Release Date"); cell5.setCellStyle(style1); /* populating cell values */ int rowIndex = 4; int sno = 1; for (Song song : songList) { if (song.getSno() != 0) { Row row = songsSheet.createRow(rowIndex++); int cellIndex = 0; /* first place in row is Sno */ Cell cell20 = row.createCell(cellIndex++); cell20.setCellValue(sno++); cell20.setCellStyle(style14); /* second place in row is Genre */ Cell cell21 = row.createCell(cellIndex++); cell21.setCellValue(song.getGenre()); cell21.setCellStyle(style13); /* third place in row is Critic score */ Cell cell22 = row.createCell(cellIndex++); cell22.setCellValue(song.getCriticscore()); cell22.setCellStyle(style14); /* fourth place in row is Album name */ Cell cell23 = row.createCell(cellIndex++); cell23.setCellValue(song.getAlbumname()); cell23.setCellStyle(style13); /* fifth place in row is Artist */ Cell cell24 = row.createCell(cellIndex++); cell24.setCellValue(song.getArtist()); cell24.setCellStyle(style13); /* sixth place in row is marks in date */ if (song.getReleasedate() != null) { Cell date = row.createCell(cellIndex++); DataFormat format = workbook.createDataFormat(); CellStyle dateStyle = workbook.createCellStyle(); dateStyle.setDataFormat(format.getFormat("dd-MMM-yyyy")); date.setCellStyle(dateStyle); date.setCellValue(song.getReleasedate()); /* auto-resizing columns */ songsSheet.autoSizeColumn(6); songsSheet.autoSizeColumn(5); songsSheet.autoSizeColumn(4); songsSheet.autoSizeColumn(3); songsSheet.autoSizeColumn(2); } } } /* writing this workbook to excel file. */ try { FileOutputStream fos = new FileOutputStream(FILE_PATH); workbook.write(fos); fos.close(); System.out.println(FILE_PATH + " is successfully written"); } catch (FileNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } }
From source file:com.mycompany.modelinglab2.helperModel.ExcelHelper.ExcelHelper.java
/** * distance = x higth = y/*from w ww .j av a 2 s. co m*/ */ public void formExcelFile(List distance, List higth) { File file = new File("C:\\Users\\Roman\\Desktop\\Experiment.xls"); // Workbook wb = new HSSFWorkbook(); try { Sheet sheet = wb.createSheet("Laboratory 2 (Modeling balls)"); // ? Font font = wb.createFont(); font.setBoldweight(font.ANSI_CHARSET); // ? ? ? Row row = sheet.createRow(0); row.createCell(0).setCellValue("???"); row.createCell(1).setCellValue("?"); for (int i = 0; i < distance.size(); i++) { row = sheet.createRow(i + 1); Cell distCell = row.createCell(0); Cell higthCell = row.createCell(1); distCell.setCellValue(distance.get(i).toString().replace('.', ',')); higthCell.setCellValue(higth.get(i).toString().replace('.', ',')); } // sheet.autoSizeColumn(0); // ? ?? wb.write(new FileOutputStream(file)); wb.close(); } catch (IOException ex) { Logger.getLogger(ExcelHelper.class.getName()).log(Level.SEVERE, null, ex); } }