1. 通过maven引入poi,在pom.xml文件中加入以下代码
<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>
2. HSSFWorkbook wb = new HSSFWorkbook(); //创建excel对象
HSSFSheet sheet = wb.createSheet("日报汇总"); //创建工作表
sheet.setDefaultColumnWidth(18); //设置默认列宽
HSSFCellStyle cellStyle = wb.createCellStyle(); //单元格样式
cellStyle.setWrapText(true); //自动换行
HSSFFont font = wb.createFont(); //创建字体
font.setFontHeightInPoints((short) 12); //设置字体大小
font.setBold(true); //粗体显示
cellStyle.setFont(font); //将字体应用到单元格样式
cellStyle.setAlignment(HorizontalAlignment.CENTER); //水平居中
cellStyle.setVerticalAlignment(VerticalAlignment.CENTER); //垂直居中
//设置边框
cellStyle.setBorderBottom(BorderStyle.THIN); //下边框
cellStyle.setBorderLeft(BorderStyle.THIN);//左边框
cellStyle.setBorderTop(BorderStyle.THIN);//上边框
cellStyle.setBorderRight(BorderStyle.THIN);//右边框
3.//在sheet里创建第一行,参数为行索引(excel的行),可以是0~65535之间的任何一个
HSSFRow row1 = sheet.createRow(0);
//合并单元格CellRangeAddress构造参数依次表示起始行,截至行,起始列, 截至列
CellRangeAddress cellRangeAddress =new CellRangeAddress(0,0,0,11);
sheet.addMergedRegion(cellRangeAddress);
//单元格赋值
row1.createCell(0).setCellValue("江西煌上煌集团食品股份有限公司");
//设置单元格样式
row1.getCell(0).setCellStyle(cellStyle);
//设置行高
row1.setHeight((short)(30*20));
注意:在合并单元格后并给单元格设置样式后,合并单元格的边框显示会有问题,需要在最后加上以下代码
RegionUtil.setBorderLeft(1, cellRangeAddress, sheet);
RegionUtil.setBorderBottom(1, cellRangeAddress, sheet);
RegionUtil.setBorderRight(1, cellRangeAddress, sheet);
RegionUtil.setBorderTop(1, cellRangeAddress, sheet);
4.写入浏览器
try{
response.setHeader("content-disposition", "attachment;filename=" + URLEncoder.encode(day+"日报汇总", "UTF-8")+".xls");
OutputStream outputStream=response.getOutputStream();
wb.write(outputStream);
outputStream.flush();
outputStream.close();
}catch(Exception e){
e.printStackTrace();
}