java POI创建HSSFWorkbook工作簿

1. POI

Apache POI 是基于 Office Open XML 标准（OOXML）和 Microsoft 的 OLE 2 复合文档格式（OLE2）处理各种文件格式的开源项目。

2. 样式设置

//设置标题

HSSFCellStyle titleStyle  = workbook.createCellStyle(); // 创建标题样式

titleStyle.setAlignment(HorizontalAlignment.CENTER);// 设置标题水平居中显示

titleStyle.setVerticalAlignment(VerticalAlignment.CENTER);// 设置标题垂直居中显示

//设置上下左右四个边框 实线 or虚线

// titleStyle.setBorderTop(HSSFBorderFormatting.BORDER_THIN);

titleStyle.setBorderTop(BorderStyle.THICK);

titleStyle.setBorderBottom(BorderStyle.THICK);

titleStyle.setBorderLeft(BorderStyle.THICK);

titleStyle.setBorderRight(BorderStyle.THICK);

//设置上下左右四个边框颜色

titleStyle.setTopBorderColor(HSSFColor.RED.index);

titleStyle.setBottomBorderColor(HSSFColor.RED.index);

titleStyle.setLeftBorderColor(HSSFColor.RED.index);

titleStyle.setRightBorderColor(HSSFColor.RED.index);

//设置背景颜色

titleStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);    //设置填充方案

// titleStyle.setFillForegroundColor(new XSSFColor(new Color(181,181,181)));  //设置填充颜色

//titleStyle.setFillForegroundColor((short) 80);

HSSFFont titleFont = workbook.createFont();    // 创建标题字体

titleFont.setItalic(false);                     // 设置字体为斜体字

titleFont.setColor(Font.COLOR_RED);            // 将字体设置颜色

titleFont.setFontHeightInPoints((short)16);    // 将字体大小

titleFont.setFontName("宋体");             // 设置字体为 宋体 应用到当前单元格上

titleFont.setBold(true);//加粗

titleStyle.setFont(titleFont); // 字体应用到 标题样式上

//设置内容

HSSFCellStyle contextCellStyle  = workbook.createCellStyle();

//contextCellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);

//contextCellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);

contextCellStyle.setAlignment(HorizontalAlignment.CENTER);

contextCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);

Font cellFont = workbook.createFont();

cellFont.setColor(Font.COLOR_NORMAL);

cellFont.setFontHeightInPoints((short)15);

cellFont.setFontName("宋体");

contextCellStyle.setFont(cellFont);

3. 创建、设置、生成工作簿

//创建工作簿

HSSFWorkbook workbook=new HSSFWorkbook();

Sheet sheet = workbook.createSheet("会议导出");

// 设置 列 的宽度

//sheet.setColumnWidth(0, 900 * 10);

//sheet.setColumnWidth(1, 900 * 10);

//sheet.setColumnWidth(2, 900 * 10);

//sheet.setColumnWidth(3, 900 * 10);

//sheet.setColumnWidth(4, 900 * 10);

//标题

Row titleRow = sheet.createRow(0);

//设置行高40px

titleRow.setHeight((short)(15.625*40));

titleRow.setHeightInPoints((float)40);

for (int i = 0; i < titles.length; i++) {

   //设置每列宽高

   sheet.setColumnWidth(i, 900 * 15);

   Cell cell = titleRow.createCell(i);

   cell.setCellValue(titles[i]);

   cell.setCellStyle(titleStyle);

}

//内容

for (int i = 0; i < userList.size(); i++) {

   Row contextRow = sheet.createRow(i+1);

   for (int j = 0; j < column.length; j++) {

       Cell contextRowCell = contextRow.createCell(j);

       contextRowCell.setCellValue(list.get(i).get(column[j]).toString());

       contextRowCell.setCellStyle(contextCellStyle);

   }

}

File file = new File("C:\\Users\\Desktop\\export-dep");

if (!file.exists()){

   file.mkdir();

}

String path = file.getPath()+"\\"+execlName;

FileOutputStream fileOutputStream = new FileOutputStream(path);

workbook.write(fileOutputStream);

fileOutputStream.flush();

// 操作结束，关闭文件

fileOutputStream.close();

workbook.close();

3.Maven依赖

<dependency>

    <groupId>org.apache.poi</groupId>

    <artifactId>poi</artifactId>

    <version>3.17</version>

</dependency>

<dependency>

    <groupId>org.apache.poi</groupId>

    <artifactId>poi-ooxml</artifactId>

    <version>3.17</version>

</dependency>

<dependency>

    <groupId>org.apache.poi</groupId>

    <artifactId>poi-ooxml-schemas</artifactId>

    <version>3.17</version>

</dependency>