java exports an Excel code example with multiple sheet

  • 2021-07-03 00:13:05
  • OfStack

This article example for everyone to share java export contains a number of sheet Excel specific code, for your reference, the specific content is as follows

To export multiple sheet, the key is to set the time of Excel export. Before executing the export file, create multiple worksheets


HSSFSheet sheet = workbook.createSheet(sheettitle); 

Thus, for every worksheet created, a new sheet table will be generated, which will be exported once when Excel is finally exported.

Example:
Class Java:


try { 
  HSSFWorkbook workbook = new HSSFWorkbook(); 
  OutputStream out = response.getOutputStream(); 
  for(int j=0;j<n;j++){ 
    BaseResult<List<T>> teasalList = service.select(teasal); 
    // Next cycle list Put Excel In the table  
    if(teasalList.isSuccess()&&teasalList.getResult().size()>0){ 
      // Document title  
      SimpleDateFormat formatter1 = new SimpleDateFormat("yyyy-MM-dd"); 
      String nowdate = formatter1.format(new Date()); 
      String title = null; 
      title = "excel Table title -" + nowdate + ".xls"; 
      String sheettitle = "sheet Table name "; 
      // Set table header row  
      String oneheaders = " Head line header " ; 
      String dateheaders = nowdate ; 
      String[] headers = new String[] {" Column 1"," Column 2"," Column 3"," Column 4"}; 
      List<Object[]> dataList = new ArrayList<Object[]>(); 
      Object[] objs = null; 
      for(int i =0; i<3 ; i++){ // Loop per 1 Bar data  
        objs = new Object[headers.length]; 
        objs[1] = " Zhang 3";  // Name  
        objs[2] = "3"; // Serial number  
        // Data is added to the excel Forms  
        dataList.add(objs); 
      } 
      // Exporting data using streams  
      // Prevent Chinese garbled codes  
      String headStr = "attachment; filename=\"" + new String( title.getBytes("gb2312"), "ISO8859-1" ) + "\""; 
      response.setContentType("octets/stream"); 
      response.setContentType("APPLICATION/OCTET-STREAM"); 
      response.setHeader("Content-Disposition", headStr); 
      ExportExcelDownFee ex ; 
      ex = new ExportExcelDownFee(sheettitle, oneheaders, dateheaders,headers, dataList);// No title  
      ex.export(workbook,out); 
    } 
  } 
  workbook.write(out); // Loop to generate multiple sheet After exporting Excel
  out.close(); // Close flow 
} catch (Exception e) { 
  e.printStackTrace(); 
}

Tool class:


public class ExportExcelDownFee { 
 
  //导出表的列名 
  private String[] rowName ; 
  //导出表的小标题 
  private String oneheaders; 
  //导出表的日期 
  private String dateheaders; 
  //sheet表表名 
  private String sheettitle; 
 
  private List<Object[]> dataList = new ArrayList<Object[]>(); 
 
  HttpServletResponse response; 
   
  //构造方法2,传入要导出的数据 
  public ExportExcelDownFee( String sheettitle, String oneheaders, String dateheaders, String[] rowName,List<Object[]> dataList){ 
    this.dataList = dataList; 
    this.oneheaders = oneheaders; 
    this.dateheaders = dateheaders; 
    this.rowName = rowName; 
    this.sheettitle = sheettitle; 
  } 
   
  /* 
   * 导出数据 
   * */ 
  public void export(HSSFWorkbook workbook,OutputStream out) throws Exception{ 
    try{ 
      HSSFSheet sheet = workbook.createSheet(sheettitle);         // 创建工作表 
 
      HSSFCellStyle columnTopStyle = this.getColumnTopStyle(workbook);//获取列头样式对象 
      HSSFCellStyle style = this.getStyle(workbook);         //单元格样式对象 
 
      //第1行 
      HSSFRow rowfirstName = sheet.createRow(0); 
      HSSFCell oneCellRowName = rowfirstName.createCell(0);        //创建列头对应个数的单元格 
      oneCellRowName.setCellType(HSSFCell.CELL_TYPE_STRING);       //设置列头单元格的数据类型 
      HSSFRichTextString onetext = new HSSFRichTextString(oneheaders); 
      oneCellRowName.setCellValue(onetext);                 //设置列头单元格的值 
      //合并单元格CellRangeAddress构造参数依次表示起始行,截至行,起始列, 截至列  
      sheet.addMergedRegion(new CellRangeAddress(0,0,0,3));  
      oneCellRowName.setCellStyle(columnTopStyle);            //设置列头单元格样式 
       
      //第2行 
      HSSFRow rowDateName = sheet.createRow(1); 
      HSSFCell DateCellRowName = rowDateName.createCell(3); 
      DateCellRowName.setCellValue(dateheaders); 
      DateCellRowName.setCellStyle(columnTopStyle);  
       
      // 定义所需列数 
      int columnNum = rowName.length; 
      HSSFRow rowRowName = sheet.createRow(2);        // 在索引2的位置创建行(最顶端的行开始的第2行) 
 
      // 将列头设置到sheet的单元格中 
      for(int n=0;n<columnNum;n++){ 
        HSSFCell cellRowName = rowRowName.createCell(n);        //创建列头对应个数的单元格 
        cellRowName.setCellType(HSSFCell.CELL_TYPE_STRING);       //设置列头单元格的数据类型 
        HSSFRichTextString text = new HSSFRichTextString(rowName[n]); 
        cellRowName.setCellValue(text);                 //设置列头单元格的值 
        cellRowName.setCellStyle(style);            //设置列头单元格样式 
      } 
 
      //将查询出的数据设置到sheet对应的单元格中 
      for(int i=0;i<dataList.size();i++){ 
 
        Object[] obj = dataList.get(i);//遍历每个对象 
        HSSFRow row = sheet.createRow(i+3);//创建所需的行数(从第2行开始写数据) 
 
        for(int j=0; j<obj.length; j++){ 
          HSSFCell cell = null;  //设置单元格的数据类型 
          if(j == 0){ 
            cell = row.createCell(j,HSSFCell.CELL_TYPE_NUMERIC); 
            cell.setCellValue(i+1);  
          }else{ 
            cell = row.createCell(j,HSSFCell.CELL_TYPE_STRING); 
            if(!"".equals(obj[j]) && obj[j] != null){ 
              cell.setCellValue(obj[j].toString());            //设置单元格的值 
            } 
          } 
          cell.setCellStyle(style);                  //设置单元格样式 
        } 
      } 
       
      //让列宽随着导出的列长自动适应 
      for (int colNum = 0; colNum < columnNum; colNum++) { 
        int columnWidth = sheet.getColumnWidth(colNum) / 256; 
        for (int rowNum = 0; rowNum < sheet.getLastRowNum(); rowNum++) { 
          HSSFRow currentRow; 
          //当前行未被使用过 
          if (sheet.getRow(rowNum) == null) { 
            currentRow = sheet.createRow(rowNum); 
          } else { 
            currentRow = sheet.getRow(rowNum); 
          } 
          if (currentRow.getCell(colNum) != null) { 
            HSSFCell currentCell = currentRow.getCell(colNum); 
            if (currentCell.getCellType() == HSSFCell.CELL_TYPE_STRING) { 
              int length = 0; 
              try { 
                length = currentCell.getStringCellValue().getBytes().length; 
              } catch (Exception e) { 
                e.printStackTrace(); 
              } 
              if (columnWidth < length) { 
                columnWidth = length; 
              } 
            } 
          } 
 
        } 
        if(colNum == 0){ 
          sheet.setColumnWidth(colNum, (columnWidth-2) * 256); 
        }else{ 
          sheet.setColumnWidth(colNum, (columnWidth+4) * 256); 
        } 
      } 
 
    }catch(Exception e){ 
      e.printStackTrace(); 
    } 
  } 
 
  /* 
   * 列头单元格样式 
   */   
  public HSSFCellStyle getColumnTopStyle(HSSFWorkbook workbook) { 
 
     // 设置字体 
     HSSFFont font = workbook.createFont(); 
     //设置字体大小 
     font.setFontHeightInPoints((short)11); 
     //字体加粗 
     //font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); 
     //设置字体名字  
     font.setFontName("宋体"); 
     //设置样式;  
     HSSFCellStyle style = workbook.createCellStyle(); 
     //在样式用应用设置的字体;  
     style.setFont(font); 
     //设置自动换行;  
     style.setWrapText(false); 
     //设置水平对齐的样式为居中对齐;  
     style.setAlignment(HSSFCellStyle.ALIGN_CENTER); 
     //设置垂直对齐的样式为居中对齐;  
     style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); 
 
     return style; 
 
  } 
 
  /*  
   * 列数据信息单元格样式 
   */  
  public HSSFCellStyle getStyle(HSSFWorkbook workbook) { 
     // 设置字体 
     HSSFFont font = workbook.createFont(); 
     //设置字体名字  
     font.setFontName("宋体"); 
     //设置样式;  
     HSSFCellStyle style = workbook.createCellStyle(); 
     //设置底边框;  
     style.setBorderBottom(HSSFCellStyle.BORDER_THIN); 
     //设置底边框颜色;  
     style.setBottomBorderColor(HSSFColor.BLACK.index); 
     //设置左边框;   
     style.setBorderLeft(HSSFCellStyle.BORDER_THIN); 
     //设置左边框颜色;  
     style.setLeftBorderColor(HSSFColor.BLACK.index); 
     //设置右边框;  
     style.setBorderRight(HSSFCellStyle.BORDER_THIN); 
     //设置右边框颜色;  
     style.setRightBorderColor(HSSFColor.BLACK.index); 
     //设置顶边框;  
     style.setBorderTop(HSSFCellStyle.BORDER_THIN); 
     //设置顶边框颜色;  
     style.setTopBorderColor(HSSFColor.BLACK.index); 
     //在样式用应用设置的字体;  
     style.setFont(font); 
     //设置自动换行;  
     style.setWrapText(false); 
     //设置水平对齐的样式为居中对齐;  
     style.setAlignment(HSSFCellStyle.ALIGN_CENTER); 
     //设置垂直对齐的样式为居中对齐;  
     style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); 
 
     return style; 
 
   } 
}

Related articles: