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

  1 import com.google.common.collect.Maps;
  2 import org.apache.poi.hssf.usermodel.HSSFRichTextString;
  3 import org.apache.poi.hssf.usermodel.HSSFWorkbook;
  4 import org.apache.poi.ss.usermodel.*;
  5 
  6 import java.io.File;
  7 import java.io.FileOutputStream;
  8 import java.io.OutputStream;
  9 import java.util.ArrayList;
 10 import java.util.List;
 11 import java.util.Map;
 12 
 13 public class programTestmmm {
 14     static final float PIXEL = 30.2f;
 15     static Map<Byte, Short> 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 void createHeader4Excel(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 void createHeader(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(new HSSFRichTextString(headers[i]));
 63         }
 64     }
 65 
 66     private static void fillRecords(Workbook wb, Sheet sheet, List<String> records) {
 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 void setCellValue( 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 void main(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 = new HSSFWorkbook();
 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<String> records = new ArrayList<String>();
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 }
posted on 2018-08-24 10:01 SkyBall 阅读(2865) 评论(0) 收藏举报
Java--Excel--poi 边框、单元格换行、 背景色、合并单元格相关

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