poi导出excel设置样式

朱雀 2020-10-01 02:19 1188阅读 0赞

示例代码

  1. /**
  2.      *
  3.      * @Date 2018年9月11日 下午3:53:26
  4.      * @Fcunction exportExcel
  5.      * @param sheetName
  6.      * @param value_columns1
  7.      * @param value_columns2
  8.      * @param value_columns3
  9.      * @param list
  10.      * @return HSSFWorkbook
  11.      *
  12.      */
  13.     public static HSSFWorkbook exportExcel(String sheetName, String[] value_columns1,String[] value_columns2,String[] value_columns3, List<List<String>> list,String name){
  14.         //创建一个Excel文件
  15.         HSSFWorkbook workbook = new HSSFWorkbook();
  16.         //创建一个工作表
  17.         HSSFSheet sheet = workbook.createSheet(sheetName);
  18.         //设置宽度
  19.         for (int i = 0; i < value_columns1.length; i++) {
  20.             sheet.setColumnWidth(i, 3000);
  21.         }
  22.         //设置单元格格式居中
  23.         HSSFCellStyle titleStyle = workbook.createCellStyle(); //标题
  24.         HSSFCellStyle headStyle = workbook.createCellStyle(); //表头
  25.         HSSFCellStyle cellStyle = workbook.createCellStyle(); //表格内容
  26.         HSSFCellStyle twoStyle = workbook.createCellStyle(); //第二行不居中显示
  27.         HSSFCellStyle remarkStyle = workbook.createCellStyle(); //备注，第二行不居中显示
  28.         HSSFCellStyle recordStyle = workbook.createCellStyle(); //记录内容
  29.         HSSFCellStyle recordHumanLeftStyle = workbook.createCellStyle(); //记录人左边
  30.         HSSFCellStyle recordHumanRightStyle = workbook.createCellStyle(); //记录人右边
  31.         //设置单元格样式
  32.         HSSFFont titleFont = workbook.createFont(); //标题字体
  33.         titleFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); //宽度
  34.         titleFont.setFontHeightInPoints((short)16); //字号
  35.         titleStyle.setFont(titleFont);
  36.         titleStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
  38.         HSSFFont headFont = workbook.createFont(); //表头字体
  39.         headFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); //宽度
  40.         headFont.setFontHeightInPoints((short)16); //字号
  41.         headStyle.setFont(headFont);
  42.         headStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
  43.         headStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
  44.         headStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
  45.         headStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); //水平居中
  46.         headStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); //垂直居中
  47.         headStyle.setWrapText(true); //自动换行
  49.         cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
  50.         cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
  51.         cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
  52.         cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
  53.         cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); //水平居中
  54.         cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); //垂直居中
  56.         remarkStyle.setWrapText(true); //自动换行
  57.         remarkStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); //垂直居中
  58.         remarkStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
  59.         remarkStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
  60.         remarkStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
  61.         remarkStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
  62.         remarkStyle.setAlignment(HSSFCellStyle.ALIGN_LEFT);
  64.         twoStyle.setAlignment(HSSFCellStyle.ALIGN_LEFT);
  66.         recordStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_TOP); //垂直居上
  67.         recordStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
  68.         recordStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
  69.         recordStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
  70.         recordStyle.setAlignment(HSSFCellStyle.ALIGN_LEFT);
  72.         recordHumanLeftStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
  73.         recordHumanLeftStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
  75.         recordHumanRightStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
  76.         recordHumanRightStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
  77.         recordHumanRightStyle.setAlignment(HSSFCellStyle.ALIGN_LEFT);
  79.         //添加表头行
  80.         HSSFRow hssfRow = sheet.createRow(0);
  81.         //第一行标题
  82.         HSSFCell titleCell = hssfRow.createCell(0);
  83.         titleCell.setCellValue(sheetName);
  84.         titleCell.setCellStyle(titleStyle);
  85.         //合并第一行所有列
  86.         sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, value_columns1.length-1));
  87.         //第二行
  88.         hssfRow = sheet.createRow(1);
  89.         for (int i = 0; i < value_columns1.length; i++) {
  90.             HSSFCell twoCell = hssfRow.createCell(i);
  91.             if(i==0){
  92.                 twoCell.setCellValue("日期："+new SimpleDateFormat("yyyy年M月d日").format(new Date()));
  93.                 twoCell.setCellStyle(twoStyle);
  94.             }else if(i==value_columns1.length-3){
  95.                 twoCell.setCellValue("交班领导："+name);
  96.                 twoCell.setCellStyle(twoStyle);
  97.             }
  98.         }
  99.         //合并第二行
  100.         sheet.addMergedRegion(new CellRangeAddress(1, 1, 0, value_columns1.length-4));
  101.         sheet.addMergedRegion(new CellRangeAddress(1, 1, value_columns1.length-3, value_columns1.length-1));
  102.         //第三行开始表格
  103.         hssfRow = sheet.createRow(2);
  104.         for (int i = 0; i < value_columns1.length; i++) {
  105.             //添加表头内容
  106.             HSSFCell headCell = hssfRow.createCell(i);
  107.             headCell.setCellValue(value_columns1[i]);
  108.             headCell.setCellStyle(cellStyle);
  109.         }
  110.         hssfRow = sheet.createRow(3);
  111.         for (int i = 0; i < value_columns2.length; i++) {
  112.             //添加表头内容
  113.             HSSFCell headCell = hssfRow.createCell(i);
  114.             headCell.setCellValue(value_columns2[i]);
  115.             headCell.setCellStyle(cellStyle);
  116.         }
  117.         hssfRow = sheet.createRow(4);
  118.         for (int i = 0; i < value_columns3.length; i++) {
  119.             //添加表头内容
  120.             HSSFCell headCell = hssfRow.createCell(i);
  121.             headCell.setCellValue(value_columns3[i]);
  122.             headCell.setCellStyle(cellStyle);
  123.         }
  124.         //合并表头（3、4、5行）
  125.         sheet.addMergedRegion(new CellRangeAddress(2, 4, 0, 0));
  126.         sheet.addMergedRegion(new CellRangeAddress(2, 3, 1, 2));
  127.         sheet.addMergedRegion(new CellRangeAddress(2, 3, 3, 4));
  128.         sheet.addMergedRegion(new CellRangeAddress(2, 2, 5, 8));
  129.         sheet.addMergedRegion(new CellRangeAddress(3, 3, 5, 6));
  130.         sheet.addMergedRegion(new CellRangeAddress(3, 3, 7, 8));
  131.         sheet.addMergedRegion(new CellRangeAddress(2, 3, 9, 10));
  132.         //把数据添加到excel
  133.         for (int i = 0; i < list.size(); i++) {
  134.             hssfRow = sheet.createRow(i + 5);
  135.             for (int j = 0; j < list.get(i).size(); j++) {
  136.                 //创建单元格，并设置值
  137.                 HSSFCell cell = hssfRow.createCell(j);
  138.                 cell.setCellValue(list.get(i).get(j));
  139.                 if(i==list.size()-1&&j!=0){
  140.                     cell.setCellStyle(remarkStyle);
  141.                 }else {
  142.                     cell.setCellStyle(cellStyle);
  143.                 }
  144.             }
  145.         }
  146.         for (int i = 5+list.size(); i < 5+list.size()+2; i++) {
  147.             hssfRow = sheet.createRow(i);
  148.             for (int j = 0; j < value_columns1.length; j++) {
  149.                 HSSFCell cell = hssfRow.createCell(j);
  150.                 cell.setCellValue("");
  151.                 cell.setCellStyle(remarkStyle);
  152.             }
  153.         }
  154.         sheet.addMergedRegion(new CellRangeAddress(4+list.size(), 4+list.size()+2, 0, 0));
  155.         sheet.addMergedRegion(new CellRangeAddress(4+list.size(), 4+list.size()+2, 1, 10));
  156.         //会议记录
  157.         for (int i = 7+list.size(); i < 7+list.size()+5; i++) {
  158.             hssfRow = sheet.createRow(i);
  159.             for (int j = 0; j < value_columns1.length; j++) {
  160.                 HSSFCell cell = hssfRow.createCell(j);
  161.                 if(i == 7+list.size()&&j==0) {
  162.                     cell.setCellValue("会议记录：");
  163.                 }else {
  164.                     cell.setCellValue("");
  165.                 }
  166.                 cell.setCellStyle(recordStyle);
  167.             }
  168.         }
  169.         sheet.addMergedRegion(new CellRangeAddress(7+list.size(), 7+list.size()+4, 0, 10));
  170.         //记录人
  171.         hssfRow = sheet.createRow(7+list.size()+5);
  172.         for (int j = 0; j < value_columns1.length; j++) {
  173.             HSSFCell cell = hssfRow.createCell(j);
  174.             if(j>=value_columns1.length-3) {
  175.                 cell.setCellValue("记录人：");
  176.                 cell.setCellStyle(recordHumanRightStyle);
  177.             }else {
  178.                 cell.setCellValue("");
  179.                 cell.setCellStyle(recordHumanLeftStyle);
  180.             }
  181.         }
  182.         sheet.addMergedRegion(new CellRangeAddress(7+list.size()+5, 7+list.size()+5, 0, value_columns1.length-4));
  183.         sheet.addMergedRegion(new CellRangeAddress(7+list.size()+5, 7+list.size()+5, value_columns1.length-3, value_columns1.length-1));
  184.         return workbook;
  185.     }

setColumnWidth方法设置具体某一列的宽度（第一个参数是第几列从0开始，第二个参数是宽度值）

  1. HSSFSheet sheet = workbook.createSheet(date+sheetName);
  2. sheet.setColumnWidth((short) 6, (short) 1600);

设置不同字体，如下，titleStyle是加粗的，默认字号大小为10;

  1. HSSFCellStyle titleStyle = workbook.createCellStyle();
  2. //设置字体
  3. HSSFFont font =workbook.createFont();
  4. font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); //加粗
  5. font.setFontHeightInPoints((short)16); //字号
  6. titleStyle.setFont(font);
  7. HSSFCellStyle cellStyle = workbook.createCellStyle();

设置自动换行，setWrapText(true)；内容里加\r\n，在对应地方自动换行；不加\r\n按照单元格宽度自动换行；不调用setWrapText(true)方法，只在内容加\r\n，只有点开单元格时才换行

  1. remarkStyle.setWrapText(true); //自动换行
  2. cell.setCellValue("hello\r\n word");

发表评论

表情：
评论列表 （有 0 条评论，1188人围观）

还没有评论，来说两句吧...

相关阅读