excel使用poi操作。

时间:2022-12-04 20:17:30
  • 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