List of usage examples for org.apache.poi.ss.usermodel Workbook createDataFormat
DataFormat createDataFormat();
From source file:com.jeans.iservlet.controller.impl.ExportController.java
/** * ?????/* w ww. ja v a 2s. co m*/ * * @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.mycompany.excelreadandwrite.WritetoExcel.java
public void writeSongsListToExcel(List<Song> songList) { /*/* w w w. j a va 2s .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); /* 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) { /*//from w ww.j av a2s . co 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) { /*/*from w ww . java 2 s.com*/ 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.ocs.dynamo.ui.composite.table.export.TableExportActionHandlerTest.java
License:Apache License
@Test public void testExportWithCustomCellStyle() throws IOException { List<EntityModel<?>> models = new ArrayList<>(); models.add(entityModelFactory.getModel(Person.class)); handler = new TableExportActionHandler(ui, entityModelFactory, models, messageService, REPORT_TITLE, columnIds, true, new CustomCellStyleGenerator() { private CellStyle cellStyle; private CellStyle bdStyle; @Override/*w w w . j ava2s . c o m*/ public CellStyle getCustomCellStyle(Workbook workbook, Object propId, Object value, AttributeModel attributeModel) { if (cellStyle == null) { cellStyle = workbook.createCellStyle(); cellStyle.setAlignment(CellStyle.ALIGN_RIGHT); cellStyle.setBorderBottom(CellStyle.BORDER_THIN); cellStyle.setBorderTop(CellStyle.BORDER_THIN); cellStyle.setBorderLeft(CellStyle.BORDER_THIN); cellStyle.setBorderRight(CellStyle.BORDER_THIN); Font font = workbook.createFont(); font.setColor(IndexedColors.BLUE.getIndex()); cellStyle.setFont(font); } if (bdStyle == null) { DataFormat format = workbook.createDataFormat(); bdStyle = workbook.createCellStyle(); bdStyle.setAlignment(CellStyle.ALIGN_RIGHT); bdStyle.setBorderBottom(CellStyle.BORDER_THIN); bdStyle.setBorderTop(CellStyle.BORDER_THIN); bdStyle.setBorderLeft(CellStyle.BORDER_THIN); bdStyle.setBorderRight(CellStyle.BORDER_THIN); bdStyle.setDataFormat(format.getFormat("#,##0.00")); Font font = workbook.createFont(); font.setColor(IndexedColors.BLUE.getIndex()); bdStyle.setFont(font); } if ("name".equals(propId)) { return cellStyle; } else if ("percentage".equals(propId)) { return bdStyle; } return null; } }); handler.handleAction(handler.getActions(null, null)[0], getTable(), null); byte[] bytes = captureSave(); Workbook wb = importer.createWorkbook(bytes); Assert.assertEquals("Bas, Bob", wb.getSheetAt(0).getRow(2).getCell(0).getStringCellValue()); Font font = wb.getFontAt(wb.getSheetAt(0).getRow(2).getCell(0).getCellStyle().getFontIndex()); Assert.assertEquals(IndexedColors.BLUE.getIndex(), font.getColor()); Assert.assertEquals("Patrick", wb.getSheetAt(0).getRow(3).getCell(0).getStringCellValue()); Assert.assertEquals(35, wb.getSheetAt(0).getRow(2).getCell(1).getNumericCellValue(), 0.001); Assert.assertEquals(44, wb.getSheetAt(0).getRow(3).getCell(1).getNumericCellValue(), 0.001); // totals must be summed up Assert.assertEquals(79, wb.getSheetAt(0).getRow(4).getCell(1).getNumericCellValue(), 0.001); // percentage Assert.assertEquals(0.12, wb.getSheetAt(0).getRow(2).getCell(3).getNumericCellValue(), 0.001); Assert.assertEquals(0.15, wb.getSheetAt(0).getRow(3).getCell(3).getNumericCellValue(), 0.001); }
From source file:com.primovision.lutransport.core.util.TollCompanyTagUploadUtil.java
private static void setCellValueFeeFormat(Workbook wb, Cell cell, Object oneCellValue, String vendor) { CellStyle style = wb.createCellStyle(); style.setDataFormat(wb.createDataFormat().getFormat("$#,#0.00")); cell.setCellStyle(style);// w ww.ja v a 2 s.c o m if (oneCellValue == null) { cell.setCellValue(Double.parseDouble("0.0")); return; } String feeStr = StringUtils.replace(oneCellValue.toString(), "$", StringUtils.EMPTY); feeStr = StringUtils.trimToEmpty(feeStr); feeStr = feeStr.replaceAll("\\p{javaSpaceChar}", StringUtils.EMPTY); if (StringUtils.isEmpty(feeStr)) { cell.setCellValue(Double.parseDouble("0.0")); return; } if (StringUtils.contains(vendor, TOLL_COMPANY_EZ_PASS_PA) || StringUtils.contains(vendor, TOLL_COMPANY_IPASS) || StringUtils.contains(vendor, TOLL_COMPANY_SUN_PASS)) { if (StringUtils.startsWith(feeStr, "-")) { feeStr = StringUtils.substring(feeStr, 1); } else { feeStr = "-" + feeStr; } } cell.setCellValue(Double.parseDouble(feeStr)); }
From source file:com.primovision.lutransport.core.util.TollCompanyTagUploadUtil.java
private static void setCellValueDateFormat(Workbook wb, Cell cell, Object oneCellValue, String vendor) throws ParseException { System.out.println("Incoming vendor = " + vendor); String tollCompanyDateFormat = tollCompanyToDateFormatMapping.get(vendor); System.out.println("Value = " + tollCompanyDateFormat); int columnIndex = cell.getColumnIndex(); if (oneCellValue instanceof Date) { System.out.println("Incoming date is a Date Object."); tollCompanyDateFormat = "EEE MMM dd HH:mm:ss z yyyy"; }// ww w .j a v a2s. c o m String dateStr = StringUtils.trimToEmpty(oneCellValue.toString()); if (StringUtils.isEmpty(dateStr)) { cell.setCellValue(StringUtils.EMPTY); } else { if (columnIndex == 7) { // Transaction time cell.setCellValue(convertToExpectedTimeFormatStr(dateStr, tollCompanyDateFormat)); return; } else { cell.setCellValue(convertToExpectedDateFormat(dateStr, tollCompanyDateFormat)); } } CellStyle style = wb.createCellStyle(); style.setDataFormat(wb.createDataFormat().getFormat(expectedDateFormat.toPattern())); cell.setCellStyle(style); }
From source file:com.quanticate.opensource.datalistdownload.DataListDownloadWebScript.java
License:Open Source License
@Override protected void populateBody(Object resource, Workbook workbook, Sheet sheet, List<QName> properties) throws IOException { NodeRef list = (NodeRef) resource;/*from w w w .j a v a 2 s.c o m*/ List<NodeRef> items = getItems(list); // Our various formats DataFormat formatter = workbook.createDataFormat(); CellStyle styleInt = workbook.createCellStyle(); styleInt.setDataFormat(formatter.getFormat("0")); CellStyle styleDate = workbook.createCellStyle(); styleDate.setDataFormat(formatter.getFormat("yyyy-mm-dd")); CellStyle styleDouble = workbook.createCellStyle(); styleDouble.setDataFormat(formatter.getFormat("General")); CellStyle styleNewLines = workbook.createCellStyle(); styleNewLines.setWrapText(true); // Export the items int rowNum = 1, colNum = 0; for (NodeRef item : items) { Row r = sheet.createRow(rowNum); colNum = 0; for (QName prop : properties) { Cell c = r.createCell(colNum); Pair<Object, String> valAndLink = identifyValueAndLink(item, prop); if (valAndLink == null) { // This property isn't set c.setCellType(Cell.CELL_TYPE_BLANK); } else { Object val = valAndLink.getFirst(); // Multi-line property? if (val instanceof String[]) { String[] lines = (String[]) val; StringBuffer text = new StringBuffer(); for (String line : lines) { if (text.length() > 0) { text.append('\n'); } text.append(line); } String v = text.toString(); c.setCellValue(v); if (lines.length > 1) { c.setCellStyle(styleNewLines); r.setHeightInPoints(lines.length * sheet.getDefaultRowHeightInPoints()); } } // Regular properties else if (val instanceof String) { c.setCellValue((String) val); } else if (val instanceof Date) { c.setCellValue((Date) val); c.setCellStyle(styleDate); } else if (val instanceof Integer || val instanceof Long) { double v = 0.0; if (val instanceof Long) v = (double) (Long) val; if (val instanceof Integer) v = (double) (Integer) val; c.setCellValue(v); c.setCellStyle(styleInt); } else if (val instanceof Float || val instanceof Double) { double v = 0.0; if (val instanceof Float) v = (double) (Float) val; if (val instanceof Double) v = (double) (Double) val; c.setCellValue(v); c.setCellStyle(styleDouble); } else { // TODO System.err .println("TODO: Handle Excel output of " + val.getClass().getName() + " - " + val); } } colNum++; } rowNum++; } // Sensible column widths please! colNum = 0; for (QName prop : properties) { sheet.autoSizeColumn(colNum); colNum++; } }
From source file:com.runwaysdk.query.ValueQueryExcelExporter.java
License:Open Source License
public ValueQueryExcelExporter(ValueQuery valueQuery, String sheetName, Set<String> includeAliases, Workbook workbook) { this.includeAliases = includeAliases; this.workbook = workbook; this.dateStyle = this.workbook.createCellStyle(); this.dateStyle.setDataFormat(workbook.createDataFormat().getFormat("dd/mm/yyyy")); this.valueQuery = valueQuery; this.sheetName = sheetName; }
From source file:com.ssic.education.provider.controller.ProSupplierController.java
@RequestMapping(value = "/excel") @ResponseBody//from w w w . ja va 2 s. c o m public ModelAndView exportExcel(SupplierDto supplierDto, HttpServletRequest request, HttpServletResponse response) { SessionInfo info = (SessionInfo) request.getSession().getAttribute(ConfigUtil.SESSIONINFONAME); if (info == null) { return null; } supplierDto.setReceiverId(info.getSupplierId()); SimpleDateFormat sdf = new SimpleDateFormat("yyyy.M.d"); Date date = new Date(); String filename = Tools.date2Str(date, "yyyyMMddHHmmss"); HSSFSheet sheet; HSSFCell cell; response.setContentType("application/octet-stream"); response.setHeader("Content-Disposition", "attachment;filename=" + filename + ".xls"); Workbook workbook = new HSSFWorkbook(); sheet = (HSSFSheet) workbook.createSheet(""); try { List<String> titles = new ArrayList<String>(); titles.add("????"); titles.add("???"); // titles.add("????"); // titles.add("??????"); titles.add("????"); titles.add("???"); // titles.add("?"); // titles.add("???"); titles.add("?"); titles.add("?"); int len = titles.size(); HSSFCellStyle headerStyle = (HSSFCellStyle) workbook.createCellStyle(); // ? headerStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); headerStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); HSSFFont headerFont = (HSSFFont) workbook.createFont(); // headerFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); headerFont.setFontHeightInPoints((short) 11); headerStyle.setFont(headerFont); short width = 20, height = 25 * 20; sheet.setDefaultColumnWidth(width); HSSFRow sheetRow = sheet.createRow(0); for (int i = 0; i < len; i++) { // String title = titles.get(i); cell = sheetRow.createCell(i); cell.setCellStyle(headerStyle); cell.setCellType(HSSFCell.CELL_TYPE_STRING); cell.setCellValue(title); } sheet.getRow(0).setHeight(height); HSSFCellStyle contentStyle = (HSSFCellStyle) workbook.createCellStyle(); // ? contentStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); DataGrid dg = supplierService.findProSupplier(supplierDto, null); List<SupplierDto> expList = dg.getRows(); List<PageData> varList = new ArrayList<PageData>(); if (!CollectionUtils.isEmpty(expList)) { for (int i = 0; i < expList.size(); i++) { PageData vpd = new PageData(); vpd.put("var1", expList.get(i).getSupplierName()); vpd.put("var2", expList.get(i).getAddress()); // vpd.put("var3", expList.get(i).getFoodServiceCode()); // vpd.put("var4", expList.get(i).getFoodBusinessCode()); vpd.put("var3", expList.get(i).getFoodCirculationCode()); vpd.put("var4", expList.get(i).getFoodProduceCode()); // vpd.put("var7", expList.get(i).getBusinessLicense()); // vpd.put("var8", expList.get(i).getSupplierCode()); vpd.put("var5", expList.get(i).getCorporation()); vpd.put("var6", expList.get(i).getContactWay()); varList.add(vpd); } } for (int i = 0; i < varList.size(); i++) { HSSFRow row = sheet.createRow(i + 1); PageData vpd = varList.get(i); for (int j = 0; j < len; j++) { String varstr = vpd.getString("var" + (j + 1)) != null ? vpd.getString("var" + (j + 1)) : ""; cell = row.createCell(j); HSSFCellStyle cellStyle2 = (HSSFCellStyle) workbook.createCellStyle(); HSSFDataFormat format = (HSSFDataFormat) workbook.createDataFormat(); cellStyle2.setDataFormat(format.getFormat("@")); cell.setCellStyle(cellStyle2); cell.setCellType(HSSFCell.CELL_TYPE_STRING); cell.setCellValue(varstr); } } OutputStream os = response.getOutputStream(); workbook.write(os); os.flush(); os.close(); } catch (Exception e) { } return null; }