java 导出excel表格

来源：互联网发布：网络主播loli王春雨编辑：程序博客网时间：2024/05/23 15:43

工具类import org.apache.poi.hssf.util.HSSFColor;import org.apache.poi.ss.util.CellRangeAddress;import org.apache.poi.xssf.usermodel.XSSFCell;import org.apache.poi.xssf.usermodel.XSSFCellStyle;import org.apache.poi.xssf.usermodel.XSSFFont;import org.apache.poi.xssf.usermodel.XSSFRow;import org.apache.poi.xssf.usermodel.XSSFSheet;import org.apache.poi.xssf.usermodel.XSSFWorkbook;/** * @author Administrator * */public class ExportUtil {    private XSSFWorkbook wb = null;          private XSSFSheet sheet = null;        /**      * @param wb      * @param sheet      */      public ExportUtil(XSSFWorkbook wb, XSSFSheet sheet)      {          this.wb = wb;          this.sheet = sheet;      }        /**      * 合并单元格后给合并后的单元格加边框      *       * @param region      * @param cs      */      public void setRegionStyle(CellRangeAddress region, XSSFCellStyle cs)      {            int toprowNum = region.getFirstRow();          for (int i = toprowNum; i <= region.getLastRow(); i++)          {              XSSFRow row = sheet.getRow(i);              for (int j = region.getFirstColumn(); j <= region.getLastColumn(); j++)              {                  XSSFCell cell = row.getCell(j);// XSSFCellUtil.getCell(row,                                                  // (short) j);                  cell.setCellStyle(cs);              }          }      }        /**      * 设置表头的单元格样式      *       * @return      */      public XSSFCellStyle getHeadStyle()      {          // 创建单元格样式          XSSFCellStyle cellStyle = wb.createCellStyle();          // 设置单元格的背景颜色为淡蓝色          cellStyle.setFillForegroundColor(HSSFColor.PALE_BLUE.index);          cellStyle.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);          // 设置单元格居中对齐          cellStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);          // 设置单元格垂直居中对齐          cellStyle.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);          // 创建单元格内容显示不下时自动换行          cellStyle.setWrapText(true);          // 设置单元格字体样式          XSSFFont font = wb.createFont();          // 设置字体加粗          font.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);          font.setFontName("宋体");          font.setFontHeight((short) 200);          cellStyle.setFont(font);          // 设置单元格边框为细线条          cellStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN);          cellStyle.setBorderBottom(XSSFCellStyle.BORDER_THIN);          cellStyle.setBorderRight(XSSFCellStyle.BORDER_THIN);          cellStyle.setBorderTop(XSSFCellStyle.BORDER_THIN);          return cellStyle;      }        /**      * 设置表体的单元格样式      *       * @return      */      public XSSFCellStyle getBodyStyle()      {          // 创建单元格样式          XSSFCellStyle cellStyle = wb.createCellStyle();          // 设置单元格居中对齐          cellStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);          // 设置单元格垂直居中对齐          cellStyle.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);          // 创建单元格内容显示不下时自动换行          cellStyle.setWrapText(true);          // 设置单元格字体样式          XSSFFont font = wb.createFont();          // 设置字体加粗          font.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);          font.setFontName("宋体");          font.setFontHeight((short) 200);          cellStyle.setFont(font);          // 设置单元格边框为细线条          cellStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN);          cellStyle.setBorderBottom(XSSFCellStyle.BORDER_THIN);          cellStyle.setBorderRight(XSSFCellStyle.BORDER_THIN);          cellStyle.setBorderTop(XSSFCellStyle.BORDER_THIN);          return cellStyle;      }  }

业务层

<pre name="code" class="html">@RequestMapping(value="reportExcel")public void reportExcel(HttpServletResponse response){//解决浏览器跨域问题response.setHeader("Access-Control-Allow-Origin", "*");XSSFWorkbook workBook = new XSSFWorkbook();          XSSFSheet sheet = workBook.createSheet("用户管理系统统计");          ExportUtil exportUtil = new ExportUtil(workBook, sheet);          XSSFCellStyle headStyle = exportUtil.getHeadStyle();          XSSFCellStyle bodyStyle = exportUtil.getBodyStyle();          XSSFRow headRow = sheet.createRow(0);          XSSFCell cell = null; String[] title = {"编号","电话","用户姓名","地址","生日","职业","支付宝","圈子","状态","注册时间"};for (int i = 0; i < title.length; i++)          {              cell = headRow.createCell(i);              cell.setCellStyle(headStyle);              cell.setCellValue(title[i]);          } <span style="white-space:pre"></span>//用户的信息List<User> list = userService.selectUserRec();if(list != null && list.size() > 0){for(int i=0;i<list.size();i++){User user = list.get(i);XSSFRow bodyRow = sheet.createRow(i + 1);cell = bodyRow.createCell(0);                  cell.setCellStyle(bodyStyle);                cell.setCellValue(user.getRownum());                                cell = bodyRow.createCell(1);                  cell.setCellStyle(bodyStyle);                cell.setCellValue(user.getTelephone_col());                                cell = bodyRow.createCell(2);                  cell.setCellStyle(bodyStyle);                cell.setCellValue(user.getRealname_col());                                cell = bodyRow.createCell(3);                  cell.setCellStyle(bodyStyle);                cell.setCellValue(user.getLocation_col());                                cell = bodyRow.createCell(4);                  cell.setCellStyle(bodyStyle);                cell.setCellValue(user.getBirthday_col());                                cell = bodyRow.createCell(5);                  cell.setCellStyle(bodyStyle);                cell.setCellValue(user.getProfessional_col());                                cell = bodyRow.createCell(6);                  cell.setCellStyle(bodyStyle);                cell.setCellValue(user.getAccount_col());                                cell = bodyRow.createCell(7);                  cell.setCellStyle(bodyStyle);                cell.setCellValue(user.getCircle());                                cell = bodyRow.createCell(8);                  cell.setCellStyle(bodyStyle);                if(user.getState_col().equals("Y") || user.getState_col()=="Y"){                cell.setCellValue("启用");                }else{                cell.setCellValue("禁用");                }                                cell = bodyRow.createCell(9);                  cell.setCellStyle(bodyStyle);                cell.setCellValue(DateAndString.getString(user.getCreateTime()));}}String fileName="用户信息汇总"+System.currentTimeMillis()+".xlsx";try {OutputStream os = response.getOutputStream();response.addHeader("Content-disposition", "attachment;filename="+ new String(fileName.getBytes("gb2312"), "ISO8859-1"));response.setContentType("application/application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");response.setCharacterEncoding("utf-8");workBook.write(os);os.flush();  os.close();os.flush();os.close();} catch (Exception e) {e.printStackTrace();}}

0 0