1 package com.thinkgem.jeesite.modules.result.utils; 2 3 import java.io.IOException; 4 import java.io.OutputStream; 5 import java.util.Date; 6 7 import javax.servlet.http.HttpServletResponse; 8 9 import org.apache.poi.ss.usermodel.BorderStyle; 10 import org.apache.poi.ss.usermodel.Cell; 11 import org.apache.poi.ss.usermodel.CellStyle; 12 import org.apache.poi.ss.usermodel.Font; 13 import org.apache.poi.ss.usermodel.HorizontalAlignment; 14 import org.apache.poi.ss.usermodel.IndexedColors; 15 import org.apache.poi.ss.usermodel.Row; 16 import org.apache.poi.ss.usermodel.Sheet; 17 import org.apache.poi.ss.usermodel.VerticalAlignment; 18 import org.apache.poi.ss.usermodel.Workbook; 19 import org.apache.poi.ss.util.CellRangeAddress; 20 21 import com.thinkgem.jeesite.common.utils.Encodes; 22 /** 23 * 给单元格设值 24 * @author admin 25 * 26 */ 27 public class ExcelUtil { 28 private Workbook workbook=null; 29 private Sheet sheet = null; 30 31 public ExcelUtil() { 32 super(); 33 } 34 35 public ExcelUtil(Workbook workbook,Sheet sheet) { 36 super(); 37 this.workbook=workbook; 38 this.sheet = sheet; 39 } 40 41 /** 42 * 设置单元格样式 43 * @param row 44 */ 45 public void setCellStyle(Row row) { 46 CellStyle style = workbook.createCellStyle(); 47 style.setAlignment(HorizontalAlignment.CENTER);// 水平居中 48 style.setVerticalAlignment(VerticalAlignment.CENTER);// 垂直居中 49 style.setWrapText(true);// 自动换行 50 Font dataFont =workbook .createFont(); 51 dataFont.setFontName("宋体"); 52 dataFont.setFontHeightInPoints((short) 12); 53 //dataFont.setBold(true); // 字体加粗 54 style.setFont(dataFont); 55 style.setBorderRight(BorderStyle.THIN);// 右边框 56 style.setRightBorderColor(IndexedColors.GREY_50_PERCENT.getIndex()); 57 style.setBorderLeft(BorderStyle.THIN);// 左边框 58 style.setLeftBorderColor(IndexedColors.GREY_50_PERCENT.getIndex()); 59 style.setBorderTop(BorderStyle.THIN); // 上边框 60 style.setTopBorderColor(IndexedColors.GREY_50_PERCENT.getIndex()); 61 style.setBorderBottom(BorderStyle.THIN); // 下边框 62 style.setBottomBorderColor(IndexedColors.GREY_50_PERCENT.getIndex()); 63 64 row.createCell(0).setCellStyle(style); 65 row.createCell(1).setCellStyle(style); 66 row.createCell(2).setCellStyle(style); 67 row.createCell(3).setCellStyle(style); 68 row.createCell(4).setCellStyle(style); 69 row.createCell(5).setCellStyle(style); 70 row.createCell(6).setCellStyle(style); 71 row.createCell(7).setCellStyle(style); 72 row.createCell(8).setCellStyle(style); 73 row.createCell(9).setCellStyle(style); 74 } 75 76 /** 77 * 另选人:创建行并设置样式 78 * @param rowNumber 79 * 创建的行数 80 */ 81 public void createRow(Integer rowNumber) { 82 int lastRowNum = sheet.getLastRowNum(); 83 lastRowNum++; 84 for (int i = 0; i < rowNumber; i++) { 85 Row row = sheet.createRow(lastRowNum); //创建行 86 //合并单元格 87 CellRangeAddress region=new CellRangeAddress(lastRowNum,lastRowNum, (short) 0, (short) 1); 88 CellRangeAddress region1=new CellRangeAddress(lastRowNum,lastRowNum, (short) 3, (short) 4); 89 CellRangeAddress region2=new CellRangeAddress(lastRowNum,lastRowNum, (short) 5, (short) 6); 90 CellRangeAddress region3=new CellRangeAddress(lastRowNum,lastRowNum, (short) 8, (short) 9); 91 sheet.addMergedRegion(region); 92 sheet.addMergedRegion(region1); 93 sheet.addMergedRegion(region2); 94 sheet.addMergedRegion(region3); 95 setCellStyle(row);//设置样式 96 lastRowNum++; 97 } 98 } 99 public void createRow1() { 100 int lastRowNum = sheet.getLastRowNum(); 101 lastRowNum++; 102 Row row = sheet.createRow(lastRowNum); //创建行 103 //合并单元格 104 CellRangeAddress region=new CellRangeAddress(lastRowNum,lastRowNum, (short) 0, (short) 1); 105 CellRangeAddress region1=new CellRangeAddress(lastRowNum,lastRowNum, (short) 2, (short) 3); 106 row.createCell(4); 107 CellRangeAddress region2=new CellRangeAddress(lastRowNum,lastRowNum, (short) 5, (short) 6); 108 row.createCell(7); 109 CellRangeAddress region3=new CellRangeAddress(lastRowNum,lastRowNum, (short) 8, (short) 9); 110 sheet.addMergedRegion(region); 111 sheet.addMergedRegion(region1); 112 sheet.addMergedRegion(region2); 113 sheet.addMergedRegion(region3); 114 setCellStyle(row);//设置样式 115 } 116 117 /** 118 * 正式候选人:创建行并设置样式 119 * @param rowNumber 120 */ 121 public void createRow1(Integer rowNumber) { 122 int row=11; 123 for (int i = 0; i < rowNumber; i++) { 124 sheet.shiftRows(row, sheet.getLastRowNum(), 1,true,true);//从指定行开始创建(插入) 125 Row createRow = sheet.createRow(row); 126 CellRangeAddress region=new CellRangeAddress(row,row, (short) 0, (short) 1); 127 sheet.addMergedRegion(region); 128 CellRangeAddress region1=new CellRangeAddress(row,row, (short) 2, (short) 3); 129 sheet.addMergedRegion(region1); 130 CellRangeAddress region2=new CellRangeAddress(row,row, (short) 4, (short) 5); 131 sheet.addMergedRegion(region2); 132 CellRangeAddress region3=new CellRangeAddress(row,row, (short) 6, (short) 7); 133 sheet.addMergedRegion(region3); 134 CellRangeAddress region4=new CellRangeAddress(row,row, (short) 8, (short) 9); 135 sheet.addMergedRegion(region4); 136 setCellStyle(createRow); 137 } 138 } 139 140 141 142 /** 143 * 设置字符串 144 * @param rowIndex 145 * @param cellnum 146 * @param value 147 */ 148 public void setCellStrValue(int rowIndex, int cellnum, String value) { 149 Cell cell = sheet.getRow(rowIndex).getCell(cellnum); 150 cell.setCellValue(value); 151 } 152 153 /** 154 * 设置日期/时间类型的数据 155 * @param rowIndex 156 * @param cellnum 157 * @param value 158 */ 159 public void setCellDateValue(int rowIndex, int cellnum, Date date) { 160 Cell cell = sheet.getRow(rowIndex).getCell(cellnum); 161 cell.setCellValue(date); 162 } 163 164 /** 165 * 输出到客户端 166 * @param fileName 输出文件名 167 */ 168 public static void write(Workbook workbook,HttpServletResponse response, String fileName) throws IOException{ 169 response.reset(); 170 response.setContentType("application/octet-stream; charset=utf-8"); 171 response.setHeader("Content-Disposition", "attachment; filename="+Encodes.urlEncode(fileName)); 172 OutputStream out = response.getOutputStream(); 173 workbook.write(out); 174 out.close(); 175 } 176 177 public void createRows(Integer rowNumber) { 178 Row row = sheet.createRow(rowNumber); //创建行 179 setCellStyle(row);//设置样式 180 } 181 182 }