• excel创建行、插入行、设置样式


      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 }
  • 相关阅读:
    CSP第二轮比赛注意事项
    2021 CCF非专业级别软件能力认证第一轮 (CSP-S)提高级 C++答案及解析
    2021 CCF非专业级别软件能力认证第一轮 (CSP-J)入门级 C++答案及解析
    Python编程与实际运用
    CCF NOI大纲
    搭建自己的kms服务器激活windows和office
    腾讯云数据库连接navicat
    Java unicode编码转换
    git 强制更新版本
    oracle相关
  • 原文地址:https://www.cnblogs.com/mxggx/p/13937074.html
Copyright © 2020-2023  润新知