本文實(shí)例講述了Java Web使用POI導(dǎo)出Excel的方法。分享給大家供大家參考，具體如下：
采用Spring mvc架構(gòu)：
Controller層代碼如下
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
|
@Controllerpublic 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
|
@Servicepublic 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; }} |
前臺(tái)的js代碼如下：
|
1
2
3
4
5
6
|
<script>function exportExcel(){ location.href="excel/export" rel="external nofollow" ; <!--這里不能用ajax請(qǐng)求，ajax請(qǐng)求無(wú)法彈出下載保存對(duì)話框--> }</script> |
設(shè)置Excel樣式以及注意點(diǎn)：
|
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 = { "所屬區(qū)域（地市）", "機(jī)房", "機(jī)架資源情況", "", "", "", "", "", "端口資源情況", "", "", "", "", "", "機(jī)位資源情況", "", "", "設(shè)備資源情況", "", "", "IP資源情況", "", "", "", "", "網(wǎng)絡(luò)設(shè)備數(shù)" };String[] excelHeader1 = { "", "", "總量(個(gè))", "空閑(個(gè))", "預(yù)占(個(gè))", "實(shí)占(個(gè))", "自用(個(gè))", "其它(個(gè))", "總量(個(gè)) ", "在用(個(gè))", "空閑(個(gè))", "總帶寬(M)", "在用帶寬(M)", "空閑帶寬(M)", "總量(個(gè))", "在用(個(gè))", "空閑(個(gè))", "設(shè)備總量(個(gè))", "客戶設(shè)備(個(gè))", "電信設(shè)備(個(gè))", "總量(個(gè))", "空閑(個(gè))", "預(yù)占用(個(gè))", "實(shí)占用(個(gè))", "自用(個(gè))", "" };// 單元格列寬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("機(jī)房報(bào)表統(tǒng)計(jì)");HSSFRow row = sheet.createRow((int) 0);HSSFCellStyle style = wb.createCellStyle();// 設(shè)置居中樣式style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 水平居中style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); // 垂直居中// 設(shè)置合計(jì)樣式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));// 設(shè)置列寬度（像素）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);} |
注意點(diǎn)1：合并單元格 new CellRangeAddress(int,int,int,int)
first row (0-based) ,last row (0-based), first column (0-based),last column (0-based)
注意點(diǎn)2：合并單元格
String[] excelHeader = { "所屬區(qū)域（地市）", "機(jī)房", "機(jī)架資源情況", "", "", "", "","", "端口資源情況", "", "", "", "", "", "機(jī)位資源情況", "", "", "設(shè)備資源情況","", "", "IP資源情況", "", "", "", "", "網(wǎng)絡(luò)設(shè)備數(shù)" };
合并以后的單元格雖然是一個(gè)，但是仍然要保留其單元格內(nèi)容，此處用空字符串代替，否則后續(xù)表頭顯示不出
注意點(diǎn)3：填充單元格
正確寫(xiě)法：
|
1
2
3
|
HSSFCell cell = row.createCell(i);cell.setCellValue(excelHeader1[i]);cell.setCellStyle(style); |
錯(cuò)誤寫(xiě)法：
|
1
2
|
row.createCell(i).setCellValue(excelHeader1[i]);row.createCell(i).setCellStyle(style); |
本人為了省一個(gè)HSSFCell對(duì)象，使用了錯(cuò)誤寫(xiě)法，導(dǎo)致HSSFCell對(duì)象創(chuàng)建了2次，最后只保留了樣式，而內(nèi)容無(wú)法顯示
希望本文所述對(duì)大家java程序設(shè)計(jì)有所幫助。
