您好,登錄后才能下訂單哦!
在Java Web中Excel文件如何使用POI實(shí)現(xiàn)導(dǎo)出?針對這個(gè)問題,這篇文章詳細(xì)介紹了相對應(yīng)的分析和解答,希望可以幫助更多想解決這個(gè)問題的小伙伴找到更簡單易行的方法。
采用Spring mvc架構(gòu):
Controller層代碼如下
@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層代碼如下:
@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代碼如下:
<script> function exportExcel(){ location.href="excel/export" rel="external nofollow" ; <!--這里不能用ajax請求,ajax請求無法彈出下載保存對話框--> } </script>
設(shè)置Excel樣式以及注意點(diǎn):
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:填充單元格
正確寫法:
HSSFCell cell = row.createCell(i); cell.setCellValue(excelHeader1[i]); cell.setCellStyle(style);
錯(cuò)誤寫法:
row.createCell(i).setCellValue(excelHeader1[i]); row.createCell(i).setCellStyle(style);
關(guān)于在Java Web中Excel文件如何使用POI實(shí)現(xiàn)導(dǎo)出問題的解答就分享到這里了,希望以上內(nèi)容可以對大家有一定的幫助,如果你還有很多疑惑沒有解開,可以關(guān)注億速云行業(yè)資訊頻道了解更多相關(guān)知識。
免責(zé)聲明:本站發(fā)布的內(nèi)容(圖片、視頻和文字)以原創(chuàng)、轉(zhuǎn)載和分享為主,文章觀點(diǎn)不代表本網(wǎng)站立場,如果涉及侵權(quán)請聯(lián)系站長郵箱:is@yisu.com進(jìn)行舉報(bào),并提供相關(guān)證據(jù),一經(jīng)查實(shí),將立刻刪除涉嫌侵權(quán)內(nèi)容。