- String real_path = request.getSession().getServletContext().getRealPath("/");//获取文件路径,我是通过模板进行导出,在项目中拿到模板
- if (!real_path.endsWith("/")) {
- real_path = real_path + "/";
- }
- String r_url = real_path + "excel/bixuan.xls";//拼接好文件的完整路径
- String excelPath = r_url;
- String sheetName = "Sheet1";//获取到不同的sheet页。
- String sheetName1 = "Sheet2";
//拿到文件路径后创建excel对象
- File f = new File(excelPath );
- FileInputStream fis =fis = new FileInputStream(f);
- HSSFWorkbook wb =wb = new HSSFWorkbook(fis);
- HSSFWorkbook wb = util.createWorkBook(excelPath);
- HSSFSheet sheet = wb.getSheet(sheetName)
- HSSFSheet sheet1 = wb.getSheet(sheetName1)
//设置样式
- HSSFCellStyle style = wb.createCellStyle();
- style.setWrapText(true);
- style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
- style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
//设置边框
- style.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框
- style.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
- style.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
- style.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
//设置字体
- HSSFFont font2 = wb.createFont();
- font2.setFontName("仿宋_GB2312");
- font2.setFontHeightInPoints((short) 9);//字体大小
- style.setFont(font2);
- sheet.protectSheet("123456");//设置密码,保护单元格
- sheet1.protectSheet("123456");
- style.setWrapText(true);//一定要有这个,否则设置单元格密码失效
//在excel中插入的行
- sheet.shiftRows(len-1, len+20, find.size()-5, true, false);//参数详解 1从这行行开始移动,2到那行结束 3移动的数量
//合并单元格后,边框样式对合并的单元格不起作用,需要用下面代码处理
- CellRangeAddress cellRangeAddress = new CellRangeAddress(len-1,len-1,3,5);//参数,合并单元格的开始行数,与结束行数。
- int addMergedRegion = sheet.addMergedRegion(cellRangeAddress);//合并单元格
- sheet.getRow(len-1).getCell(2).setCellStyle(style);设置样式
//不同浏览器对导出的excel有不同的编码,做如下处理
- if(isMSBrowser(request)){
- xlsName = java.net.URLEncoder.encode(xlsName, "utf-8") + ".xls";
- } else {
- xlsName = new String(xlsName.getBytes("utf-8"), "iso-8859-1") + ".xls";
- }
//判断是不是ie浏览器
- public boolean isMSBrowser(HttpServletRequest request) {
- String[] IEBrowserSignals = {"MSIE", "Trident", "Edge"};
- String userAgent = request.getHeader("User-Agent");
- for (String signal : IEBrowserSignals) {
- if (userAgent.contains(signal)){
- return true;
- }
- }
- return false;
- }
//封装一下对合并单元格的边框处理
- public void setBorderStyle(int border, CellRangeAddress region, HSSFSheet sheet, HSSFWorkbook wb){
- RegionUtil.setBorderBottom(border, region, sheet, wb);//下边框
- RegionUtil.setBorderLeft(border, region, sheet, wb);//左边框
- RegionUtil.setBorderRight(border, region, sheet, wb); //右边框
- RegionUtil.setBorderTop(border, region, sheet, wb); //上边框
- }
借鉴博客:https://www.cnblogs.com/staticxy/p/6122336.html
https://www.cnblogs.com/dcncy/p/8041657.html