java excel 边框_Java--Excel--poi 边框、单元格换行、 背景色、合并单元格相关

1 importcom.google.common.collect.Maps;2 importorg.apache.poi.hssf.usermodel.HSSFRichTextString;3 importorg.apache.poi.hssf.usermodel.HSSFWorkbook;4 import org.apache.poi.ss.usermodel.*;5

6 importjava.io.File;7 importjava.io.FileOutputStream;8 importjava.io.OutputStream;9 importjava.util.ArrayList;10 importjava.util.List;11 importjava.util.Map;12

13 public classprogramTestmmm {14 static final float PIXEL = 30.2f;15 static Map headerWidth =Maps.newHashMap();16 static{17 headerWidth.put((byte)0, (short)(PIXEL * 50));18 headerWidth.put((byte)1, (short)(PIXEL * 50));19 headerWidth.put((byte)2, (short)(PIXEL * 50));20 headerWidth.put((byte)3, (short)(PIXEL * 50));21 headerWidth.put((byte)4, (short)(PIXEL * 150));22 headerWidth.put((byte)5, (short)(PIXEL * 150));23 headerWidth.put((byte)6, (short)(PIXEL * 130));24 headerWidth.put((byte)7, (short)(PIXEL * 130));25 headerWidth.put((byte)8, (short)(PIXEL * 130));26 }27

28 public static voidcreateHeader4Excel(Workbook wb, Sheet sheet, String number) {29 Row row = sheet.createRow((short)0);30 row.setHeight((short)512);31 String numStr = "newline\r\n(II：" + number + ")";32 String[] headers = { "序号", "AA", "BB", "CC", "DD", numStr, "EE", "FF", "GG"};33 createHeader(wb, sheet, row, headers);34

35 for( int i = 0; i < headers.length; i++)36 sheet.setColumnWidth((short)i, (short) headerWidth.get(Byte.valueOf((byte) i)));37 }38

39 private static voidcreateHeader(Workbook wb, Sheet sheet, Row row, String[] headers) {40 for( int i = 0; i < headers.length; i++){41 Cell cell =row.createCell(i);42 CellStyle cellStyle =wb.createCellStyle();43 cellStyle.setAlignment(CellStyle.ALIGN_CENTER);//左右居中

44 cellStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);//上下居中

45 cellStyle.setWrapText(true);//先设置为自动换行46

47 //设置边框

48 cellStyle.setBorderBottom(CellStyle.BORDER_THIN); //底部边框

49 cellStyle.setBorderLeft(CellStyle.BORDER_THIN); //左边边框

50 cellStyle.setBorderRight(CellStyle.BORDER_THIN); //右边边框

51 cellStyle.setBorderTop(CellStyle.BORDER_MEDIUM_DASHED); //上边边框52

53 //背景色

54 cellStyle.setFillForegroundColor(IndexedColors.GREY_40_PERCENT.index);55 cellStyle.setFillBackgroundColor(IndexedColors.GREY_40_PERCENT.index);56 cellStyle.setFillPattern(CellStyle.FINE_DOTS);57

58 cell.setCellStyle(cellStyle);59 cell.setCellValue(headers[i]);60

61 if(i == 5)62 cell.setCellValue(newHSSFRichTextString(headers[i]));63 }64 }65

66 private static void fillRecords(Workbook wb, Sheet sheet, Listrecords) {67 for( int i = 0; i < records.size(); i++){68 Row row = sheet.createRow((short)i+1);69

70 setCellValue( wb, row.createCell(0), records.get(i));71 setCellValue( wb, row.createCell(1), records.get(i) );72 setCellValue( wb, row.createCell(2), records.get(i) );73 setCellValue( wb, row.createCell(3), records.get(i));74 setCellValue( wb, row.createCell(4), records.get(i) );75 setCellValue( wb, row.createCell(5), records.get(i) );76 }77 }78

79 //Excel最多支持4000个style

80 private static voidsetCellValue( Workbook wb, Cell cell, String value ){81 //CellStyle cellStyle = wb.createCellStyle();82 //cell.setCellStyle(cellStyle);

83 cell.setCellValue(value);84 cell.setCellType(Cell.CELL_TYPE_STRING);85 }86

87 public static voidmain(String[] args) {88 File file = new File("C://Documents//createExcel.xls");89 OutputStream os = null;90 try{91 os = new FileOutputStream(file, true);92 Workbook wb = newHSSFWorkbook();93 Sheet sheet =wb.createSheet();94

95 //合并单元格，参数说明：1：开始行 2：结束行 3：开始列 4：结束列96 //sheet.addMergedRegion(new CellRangeAddress(0,0,2,3));

97

98 createHeader4Excel( wb, sheet, "150");99

100 List records = new ArrayList();101 records.add("11");102 records.add("22");103 records.add("33");104 records.add("44");105 records.add("55");106 records.add("66");107 fillRecords( wb, sheet, records );108 　　　　　　　wb.setSheetName(0, "first sheet");//设置sheet名字

109 wb.write(os);110 } catch(Exception e) {111 e.printStackTrace();112 }113 }114 }