本文实例讲述了Java Web使用POI导出Excel的方法。分享给大家供大家参考,具体如下:
采用Spring mvc架构:
Controller层代码如下
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
|
@Controller
public class StudentExportController{
@Autowired
private StudentExportService studentExportService;
@RequestMapping (value = "/excel/export" )
public void exportExcel(HttpServletRequest request, HttpServletResponse response)
throws Exception {
List<Student> list = new ArrayList<Student>();
list.add( new Student( 1000 , "zhangsan" , "20" ));
list.add( new Student( 1001 , "lisi" , "23" ));
list.add( new Student( 1002 , "wangwu" , "25" ));
HSSFWorkbook wb = studentExportService.export(list);
response.setContentType( "application/vnd.ms-excel" );
response.setHeader( "Content-disposition" , "attachment;filename=student.xls" );
OutputStream ouputStream = response.getOutputStream();
wb.write(ouputStream);
ouputStream.flush();
ouputStream.close();
}
}
|
Service层代码如下:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
|
@Service
public class StudentExportService {
String[] excelHeader = { "Sno" , "Name" , "Age" };
public HSSFWorkbook export(List<Campaign> list) {
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.createSheet( "Campaign" );
HSSFRow row = sheet.createRow(( int ) 0 );
HSSFCellStyle style = wb.createCellStyle();
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
for ( int i = 0 ; i < excelHeader.length; i++) {
HSSFCell cell = row.createCell(i);
cell.setCellValue(excelHeader[i]);
cell.setCellStyle(style);
sheet.autoSizeColumn(i);
}
for ( int i = 0 ; i < list.size(); i++) {
row = sheet.createRow(i + 1 );
Student student = list.get(i);
row.createCell( 0 ).setCellValue(student.getSno());
row.createCell( 1 ).setCellValue(student.getName());
row.createCell( 2 ).setCellValue(student.getAge());
}
return wb;
}
}
|
前台的js代码如下:
1
2
3
4
5
6
|
<script>
function exportExcel(){
location.href= "excel/export" rel= "external nofollow" ;
<!--这里不能用ajax请求,ajax请求无法弹出下载保存对话框-->
}
</script>
|
设置Excel样式以及注意点:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
|
String[] excelHeader = { "所属区域(地市)" , "机房" , "机架资源情况" , "" , "" , "" , "" ,
"" , "端口资源情况" , "" , "" , "" , "" , "" , "机位资源情况" , "" , "" , "设备资源情况" ,
"" , "" , "IP资源情况" , "" , "" , "" , "" , "网络设备数" };
String[] excelHeader1 = { "" , "" , "总量(个)" , "空闲(个)" , "预占(个)" , "实占(个)" ,
"自用(个)" , "其它(个)" , "总量(个) " , "在用(个)" , "空闲(个)" , "总带宽(M)" ,
"在用带宽(M)" , "空闲带宽(M)" , "总量(个)" , "在用(个)" , "空闲(个)" , "设备总量(个)" ,
"客户设备(个)" , "电信设备(个)" , "总量(个)" , "空闲(个)" , "预占用(个)" , "实占用(个)" ,
"自用(个)" , "" };
// 单元格列宽
int [] excelHeaderWidth = { 150 , 120 , 100 , 100 , 100 , 100 , 100 , 100 , 100 ,
100 , 100 , 120 , 120 , 120 , 120 , 120 , 120 , 150 , 150 , 150 , 120 ,
120 , 150 , 150 , 120 , 150 };
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.createSheet( "机房报表统计" );
HSSFRow row = sheet.createRow(( int ) 0 );
HSSFCellStyle style = wb.createCellStyle();
// 设置居中样式
style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 水平居中
style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); // 垂直居中
// 设置合计样式
HSSFCellStyle style1 = wb.createCellStyle();
Font font = wb.createFont();
font.setColor(HSSFColor.RED.index);
font.setBoldweight(Font.BOLDWEIGHT_BOLD); // 粗体
style1.setFont(font);
style1.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 水平居中
style1.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); // 垂直居中
// 合并单元格
// first row (0-based) last row (0-based) first column (0-based) last
// column (0-based)
sheet.addMergedRegion( new CellRangeAddress( 0 , 1 , 0 , 0 ));
sheet.addMergedRegion( new CellRangeAddress( 0 , 1 , 1 , 1 ));
sheet.addMergedRegion( new CellRangeAddress( 0 , 0 , 2 , 7 ));
sheet.addMergedRegion( new CellRangeAddress( 0 , 0 , 8 , 13 ));
sheet.addMergedRegion( new CellRangeAddress( 0 , 0 , 14 , 16 ));
sheet.addMergedRegion( new CellRangeAddress( 0 , 0 , 17 , 19 ));
sheet.addMergedRegion( new CellRangeAddress( 0 , 0 , 20 , 24 ));
sheet.addMergedRegion( new CellRangeAddress( 0 , 1 , 25 , 25 ));
// 设置列宽度(像素)
for ( int i = 0 ; i < excelHeaderWidth.length; i++) {
sheet.setColumnWidth(i, 32 * excelHeaderWidth[i]);
}
// 添加表格头
for ( int i = 0 ; i < excelHeader.length; i++) {
HSSFCell cell = row.createCell(i);
cell.setCellValue(excelHeader[i]);
cell.setCellStyle(style);
}
row = sheet.createRow(( int ) 1 );
for ( int i = 0 ; i < excelHeader1.length; i++) {
HSSFCell cell = row.createCell(i);
cell.setCellValue(excelHeader1[i]);
cell.setCellStyle(style);
}
|
注意点1:合并单元格 new CellRangeAddress(int,int,int,int)
first row (0-based) ,last row (0-based), first column (0-based),last column (0-based)
注意点2:合并单元格
String[] excelHeader = { "所属区域(地市)", "机房", "机架资源情况", "", "", "", "","", "端口资源情况", "", "", "", "", "", "机位资源情况", "", "", "设备资源情况","", "", "IP资源情况", "", "", "", "", "网络设备数" };
合并以后的单元格虽然是一个,但是仍然要保留其单元格内容,此处用空字符串代替,否则后续表头显示不出
注意点3:填充单元格
正确写法:
1
2
3
|
HSSFCell cell = row.createCell(i);
cell.setCellValue(excelHeader1[i]);
cell.setCellStyle(style);
|
错误写法:
1
2
|
row.createCell(i).setCellValue(excelHeader1[i]);
row.createCell(i).setCellStyle(style);
|
本人为了省一个HSSFCell对象,使用了错误写法,导致HSSFCell对象创建了2次,最后只保留了样式,而内容无法显示
希望本文所述对大家java程序设计有所帮助。