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 }