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>