List of usage examples for org.apache.poi.ss.usermodel CellStyle setDataFormat
void setDataFormat(short fmt);
From source file:com.miraisolutions.xlconnect.App.java
License:Open Source License
public static void main(String[] args) throws Exception { String file = "/home/mstuder/test.xlsx"; File f = new File(file); if (f.exists()) f.delete();/*w ww. j a va 2 s . c o m*/ Workbook wb = Workbook.getWorkbook(f, true); wb.setStyleAction(StyleAction.DATATYPE); CellStyle cs = wb.createCellStyle(); cs.setDataFormat("d/m/yy"); wb.setCellStyleForDataType(DataType.DateTime, cs); DataFrame df = new DataFrame(); boolean[] missing = new boolean[] { false, false, false, false, false }; Date date = new Date(); df.addColumn("A", new Column(new double[] { 1.0, 2.0, 3.0, 4.0, 5.0 }, missing, DataType.Numeric)); df.addColumn("B", new Column(new Date[] { date, date, date, date, date }, missing, DataType.DateTime)); wb.createSheet("data"); wb.writeWorksheet(df, "data", true); wb.save(); printDataFrame(df); }
From source file:com.miraisolutions.xlconnect.Workbook.java
License:Open Source License
private CellStyle initGeneralStyle(String name, DataType type) { CellStyle style = getCellStyle(name); if (style == null) { style = createCellStyle(name);//from w w w. j av a 2 s. co m if (type == null) style.setDataFormat("General"); else style.setDataFormat(dataFormatMap.get(type)); style.setWrapText(true); } return style; }
From source file:com.miraisolutions.xlconnect.Workbook.java
License:Open Source License
private void initDefaultStyles() { // Header style CellStyle headerStyle = getCellStyle(HEADER_STYLE); if (headerStyle == null) { headerStyle = initGeneralStyle(HEADER_STYLE); headerStyle.setFillPattern(org.apache.poi.ss.usermodel.CellStyle.SOLID_FOREGROUND); headerStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex()); }// w w w. j a va2 s . c om // String / boolean / numeric style CellStyle stringStyle = initGeneralStyle(STRING_STYLE); dataTypeStyles.put(DataType.String, stringStyle); CellStyle numericStyle = initGeneralStyle(NUMERIC_STYLE); dataTypeStyles.put(DataType.Numeric, numericStyle); CellStyle booleanStyle = initGeneralStyle(BOOLEAN_STYLE); dataTypeStyles.put(DataType.Boolean, booleanStyle); // Date style CellStyle dateStyle = getCellStyle(DATETIME_STYLE); if (dateStyle == null) { dateStyle = createCellStyle(DATETIME_STYLE); dateStyle.setDataFormat(dataFormatMap.get(DataType.DateTime)); dateStyle.setWrapText(true); } dataTypeStyles.put(DataType.DateTime, dateStyle); defaultStyles.put(HEADER_STYLE, headerStyle); defaultStyles.put(STRING_STYLE, stringStyle); defaultStyles.put(NUMERIC_STYLE, numericStyle); defaultStyles.put(BOOLEAN_STYLE, booleanStyle); defaultStyles.put(DATETIME_STYLE, dateStyle); }
From source file:com.miraisolutions.xlconnect.Workbook.java
License:Open Source License
public void setCellStyle(Cell c, CellStyle cs) { if (cs != null) { if (cs instanceof HCellStyle) { HCellStyle.set((HSSFCell) c, (HCellStyle) cs); } else if (cs instanceof XCellStyle) { XCellStyle.set((XSSFCell) c, (XCellStyle) cs); } else if (cs instanceof DataFormatOnlyCellStyle) { CellStyle csx = getCellStyle(c); csx.setDataFormat(dataFormatMap.get(((DataFormatOnlyCellStyle) cs).getDataType())); SSCellStyle.set(c, (SSCellStyle) csx); } else {//from ww w. ja v a2 s .c o m SSCellStyle.set(c, (SSCellStyle) cs); } } }
From source file:com.mycompany.excelreadandwrite.WritetoExcel.java
public void writeSongsListToExcel(List<Song> songList) { /*//from w ww .ja 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("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 a v a 2s. c om*/ 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) { /*//ww w .j a v a2 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.ncc.excel.test.ExcelUtil.java
License:Apache License
/** * ????? // www. j ava 2 s .c om * * @param fromStyle * @param toStyle */ public static void copyCellStyle(CellStyle fromStyle, CellStyle toStyle) { toStyle.setAlignment(fromStyle.getAlignment()); // toStyle.setBorderBottom(fromStyle.getBorderBottom()); toStyle.setBorderLeft(fromStyle.getBorderLeft()); toStyle.setBorderRight(fromStyle.getBorderRight()); toStyle.setBorderTop(fromStyle.getBorderTop()); toStyle.setTopBorderColor(fromStyle.getTopBorderColor()); toStyle.setBottomBorderColor(fromStyle.getBottomBorderColor()); toStyle.setRightBorderColor(fromStyle.getRightBorderColor()); toStyle.setLeftBorderColor(fromStyle.getLeftBorderColor()); // ? toStyle.setFillBackgroundColor(fromStyle.getFillBackgroundColor()); toStyle.setFillForegroundColor(fromStyle.getFillForegroundColor()); // ?? toStyle.setDataFormat(fromStyle.getDataFormat()); toStyle.setFillPattern(fromStyle.getFillPattern()); // toStyle.setFont(fromStyle.getFont(null)); toStyle.setHidden(fromStyle.getHidden()); toStyle.setIndention(fromStyle.getIndention());// toStyle.setLocked(fromStyle.getLocked()); toStyle.setRotation(fromStyle.getRotation());// toStyle.setVerticalAlignment(fromStyle.getVerticalAlignment()); toStyle.setWrapText(fromStyle.getWrapText()); }
From source file:com.netxforge.netxstudio.server.logic.reporting.ResourceReportingEngine.java
License:Open Source License
public void writeRange(List<Marker> markers, Sheet sheet, Row valueRow, List<Value> range) { CreationHelper createHelper = this.getWorkBook().getCreationHelper(); CellStyle cellStyle = this.getWorkBook().createCellStyle(); cellStyle.setDataFormat(createHelper.createDataFormat().getFormat("m-d-yy h:mm")); // Styles for markers. // CellStyle markerStyleRed = this.getWorkBook().createCellStyle(); // markerStyleRed.setFillForegroundColor(IndexedColors.RED.getIndex()); // markerStyleRed.setFillPattern(CellStyle.SOLID_FOREGROUND); CellStyle markerStyleRed = createRedBorderStyle(); CellStyle markerStyleAmber = createAmberBorderStyle(); // CellStyle markerStyleAmber = this.getWorkBook().createCellStyle(); // markerStyleAmber.setFillPattern(CellStyle.SOLID_FOREGROUND); // markerStyleAmber // .setFillForegroundColor(IndexedColors.ORANGE.getIndex()); // Write the values. for (Value v : range) { // lookup the value. int valueIndex = tsColumnForValue(v); if (valueIndex == -1) { continue; }// w w w. j a va 2s .com Cell valueCell = valueRow.createCell(valueIndex); valueCell.setCellValue(v.getValue()); // Adapt the width of the column for this value. sheet.setColumnWidth(valueIndex, 14 * 256); // Set the markers. if (markers != null) { Marker m; if ((m = StudioUtils.markerForValue(markers, v)) != null) { if (m instanceof ToleranceMarker) { switch (((ToleranceMarker) m).getLevel().getValue()) { case LevelKind.RED_VALUE: { valueCell.setCellStyle(markerStyleRed); } break; case LevelKind.AMBER_VALUE: { valueCell.setCellStyle(markerStyleAmber); } break; } } } } } }
From source file:com.netxforge.netxstudio.server.logic.reporting.ResourceReportingEngine.java
License:Open Source License
/** * Has a side effect of populating the columnTSMap with the index and date. * //ww w . j a v a 2s .co m */ public int writeTS(Map<Integer, Date> columnTSMap, Sheet sheet, Row tsRow, List<Date> range, int weekNumber, int columnIndex) { CreationHelper createHelper = this.getWorkBook().getCreationHelper(); CellStyle dateStyle = this.getWorkBook().createCellStyle(); dateStyle.setDataFormat(createHelper.createDataFormat().getFormat("m-d-yy h:mm")); // Write the values. int valueIndex = columnIndex; for (Date d : range) { Cell tsCell = tsRow.createCell(valueIndex); // sheet.setColumnWidth(valueIndex, 14 * 256); tsCell.setCellValue(d); tsCell.setCellStyle(dateStyle); valueIndex++; columnTS.put(valueIndex, d); } return valueIndex; }