1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 | package com.gsitm.java.poi; import java.io.FileOutputStream; import org.apache.poi.hssf.usermodel.HSSFCell; import org.apache.poi.hssf.usermodel.HSSFCellStyle; import org.apache.poi.hssf.usermodel.HSSFRow; import org.apache.poi.hssf.usermodel.HSSFSheet; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.hssf.util.HSSFColor; import org.apache.poi.ss.usermodel.BorderExtent; import org.apache.poi.ss.usermodel.BorderStyle; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.CellStyle; import org.apache.poi.ss.usermodel.FillPatternType; import org.apache.poi.ss.usermodel.Font; import org.apache.poi.ss.usermodel.HorizontalAlignment; import org.apache.poi.ss.usermodel.Row; import org.apache.poi.ss.usermodel.Sheet; import org.apache.poi.ss.usermodel.VerticalAlignment; import org.apache.poi.ss.util.CellRangeAddress; import org.apache.poi.ss.util.PropertyTemplate; import org.apache.poi.ss.util.RegionUtil; /** * @Project : test * @Date : 2018. 4. 4. * @작성자 : 뿌따따 * @프로그램 설명 : Poi 활용. */ public class Poi_1062 { static HSSFRow row; static HSSFCell cell; public static void main(String[] args) { // 엑셀파일을 생성한다. HSSFWorkbook workbook = new HSSFWorkbook(); // 시트를 만든다. mysheet HSSFSheet sheet = workbook.createSheet("mySheet"); // 셀병합 sheet.addMergedRegion(CellRangeAddress.valueOf("B3:D3")); sheet.addMergedRegion(CellRangeAddress.valueOf("A5:A6")); sheet.addMergedRegion(CellRangeAddress.valueOf("B5:D5")); sheet.addMergedRegion(CellRangeAddress.valueOf("B6:D6")); sheet.addMergedRegion(CellRangeAddress.valueOf("B8:D8")); sheet.addMergedRegion(CellRangeAddress.valueOf("B9:D9")); // 셀너비 설정 sheet.setColumnWidth(0, (int) 18.25 * 256 + 200); sheet.setColumnWidth(1, (int) 36.25 * 256 + 200); sheet.setColumnWidth(2, (int) 18.25 * 256 + 200); sheet.setColumnWidth(3, (int) 36.25 * 256 + 200); // 폰트설정 Font font = workbook.createFont(); font.setFontName("맑은 고딕"); font.setBold(true); // 아무것도 내용없는 셀의 스타일 설정 HSSFCellStyle borderStyle = workbook.createCellStyle(); borderStyle.setBorderBottom(BorderStyle.THIN); borderStyle.setBorderLeft(BorderStyle.THIN); borderStyle.setBorderRight(BorderStyle.THIN); borderStyle.setBorderTop(BorderStyle.THIN); // 배경 노란색인 셀의 스타일 설정 HSSFCellStyle yellowStyle = workbook.createCellStyle(); yellowStyle.cloneStyleFrom(borderStyle); yellowStyle.setFillForegroundColor(new HSSFColor.YELLOW().getIndex()); yellowStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND); yellowStyle.setFont(font); yellowStyle.setAlignment(HorizontalAlignment.CENTER); yellowStyle.setVerticalAlignment(VerticalAlignment.CENTER); // 노란색 배경은 없지만 글씨가 있는 셀의 스타일 설정 HSSFCellStyle fontStyle = workbook.createCellStyle(); fontStyle.cloneStyleFrom(borderStyle); fontStyle.setFont(font); // 첫번째 row부터 차례대로 생성 row = sheet.createRow(0); row.setHeightInPoints((float) 20.3); row.createCell(0).setCellValue("프로젝트명"); row.createCell(1); row.createCell(2).setCellValue("프로젝트 규모"); row.createCell(3); row = sheet.createRow(1); row.setHeightInPoints((float) 20.3); row.createCell(0).setCellValue("작성자"); row.createCell(1); row.createCell(2).setCellValue("작성일"); row.createCell(3); row = sheet.createRow(2); row.setHeightInPoints((float) 69.9); row.createCell(0).setCellValue("프로젝트 제약사항"); row.createCell(1); row.createCell(2); row.createCell(3); row = sheet.createRow(3); row.setHeightInPoints((float) 6); row = sheet.createRow(4); row.setHeightInPoints((float) 20.3); row.createCell(0).setCellValue("확인"); row.createCell(1).setCellValue( " 프로젝트"); row.createCell(2); row.createCell(3); row = sheet.createRow(5); row.setHeightInPoints((float) 20.3); row.createCell(0); row.createCell(1).setCellValue( " 프로젝트 관리자 : "); row.createCell(2); row.createCell(3); row = sheet.createRow(6); row.setHeightInPoints((float) 6); row = sheet.createRow(7); row.setHeightInPoints((float) 69.9); row.createCell(0).setCellValue("검토의견"); row.createCell(1); row.createCell(2); row.createCell(3); row = sheet.createRow(8); row.setHeightInPoints((float) 20.3); row.createCell(0).setCellValue("검토자"); row.createCell(1).setCellValue( "담당자 : "); row.createCell(2); row.createCell(3); // 전체 셀에 가는선을 입히고 굵은글씨로 만드는 스타일 적용 for (Sheet sheet2 : workbook) { for (Row row : sheet2) { for (Cell cell : row) { if (cell.getStringCellValue().isEmpty()) { cell.setCellStyle(borderStyle); } else if (cell.getStringCellValue().isEmpty() == false) { cell.setCellStyle(fontStyle); } } } } // 노란색 백그라운드를 가진 셀에 대하여 스타일 적용 for (Sheet sheet1 : workbook) { for (Row row : sheet1) { for (Cell cell : row) { if (cell.getAddress().getColumn() == 0) { cell.setCellStyle(yellowStyle); } if (cell.getAddress().getColumn() == 2 && (cell.getAddress().getRow() == 0 || cell.getAddress().getRow() == 1)) { cell.setCellStyle(yellowStyle); } } } } // 굵은선 적용 RegionUtil.setBorderBottom(BorderStyle.THICK, CellRangeAddress.valueOf("A5:D6"), sheet); RegionUtil.setBorderRight(BorderStyle.THICK, CellRangeAddress.valueOf("D1:D6"), sheet); RegionUtil.setBorderBottom(BorderStyle.THICK, CellRangeAddress.valueOf("A9:D9"), sheet); RegionUtil.setBorderTop(BorderStyle.THICK, CellRangeAddress.valueOf("A8:D8"), sheet); RegionUtil.setBorderRight(BorderStyle.THICK, CellRangeAddress.valueOf("D8:D9"), sheet); // 파일로 출력한다. FileOutputStream outFile; try { outFile = new FileOutputStream("C:\\aa\\test.xls"); workbook.write(outFile); outFile.close(); System.out.println("파일생성완료"); } catch (Exception e) { e.printStackTrace(); } } } | cs |
'Web Programming > Java' 카테고리의 다른 글
| 04/05 Java 기본 API 클래스~ (0) | 2018.04.05 |
|---|