java生成Excel方法

最新推荐文章于 2023-08-18 17:03:40 发布

原创最新推荐文章于 2023-08-18 17:03:40 发布 · 316 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#java

java优秀例子专栏收录该内容

40 篇文章

订阅专栏

这篇博客介绍了如何在Java中利用Apache POI库来读写Excel文件。首先，引入了所需的依赖包，然后创建了一个Student实体类用于存储数据。接着，展示了如何将数据转换为Excel并导出，包括设置表格头、填充数据和写入响应流。最后，提供了一个导出Excel的完整实现示例。

一、引入对应的包

       <!-- poi读写Excel工具类 -->
        <dependency>
			<groupId>org.apache.poi</groupId>
			<artifactId>poi</artifactId>
			<version>4.1.1</version>
		</dependency>
		<dependency>
			<groupId>org.apache.poi</groupId>
			<artifactId>poi-ooxml</artifactId>
			<version>4.1.1</version>
		</dependency>

二、具体实现

Excel信息实体类

public class student {
	private String name;
	private Integer age;
	private String address;
	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
	public Integer getAge() {
		return age;
	}
	public void setAge(Integer age) {
		this.age = age;
	}	
	public String getAddress() {
		return address;
	}
	public void setAddress(String address) {
		this.address = address;
	}
	public Object[] toObject(){
        Object[] obj = {name,age,address};
        return obj;
    }
}

生成Excel具体实现

   public void exportFindPageBill(List<student> students, HttpServletResponse response)
             throws IOException {
         
         String[] headers = {"姓名","年龄","住址"};         
        
         Workbook workbook= Exports(headers,students);
         
         OutputStream out = null;
       try {
           writeExcel("学生信息", response);
           out = response.getOutputStream();
           workbook.write(out);
       } catch (IOException e) {
           e.printStackTrace();
       } finally {
           IOUtils.closeQuietly(workbook);
           IOUtils.closeQuietly(out);
       }
    }
    
 
    public Workbook Exports(String[] headers,List<student>  data) {
       SXSSFWorkbook workbook = new SXSSFWorkbook();
       SXSSFSheet sheet = workbook.createSheet();
       // 新增数据行
       int rowNum = 1;
       // 设置单元格样式
       SXSSFRow row = sheet.createRow(0);
       row.setHeight((short) 800);
       for(int i=0;i<headers.length;i++){
           SXSSFCell cell = row.createCell(i);
           XSSFRichTextString text = new XSSFRichTextString(headers[i]);
           cell.setCellValue(text);
           // cell.setCellStyle(headStyle);
       }
       //在表中存放查询到的数据放入对应的列
       if(data!=null) {
           for(student da:data) {
               Object[] os=da.toObject();
               SXSSFRow row1 = sheet.createRow(rowNum);
               row1.setHeight((short) 400);
               for(int j = 0;j<os.length;j++) {
                   SXSSFCell cell = row1.createCell(j);
                   cell.setCellValue(os[j].toString());
                   //cell.setCellStyle(cellStyle);
               }
               rowNum++;
           }
       }
       return workbook;
   }

    /**
     * Excel写入响应输出流
     * @param title 标题
     * @param response 响应
     */
    public static void writeExcel(String title, HttpServletResponse response) throws IOException {
        StringBuffer filename = new StringBuffer();
        filename.append(title);
        filename.append("-");
        filename.append(LocalDateTime.now().format(DateTimeFormatter.ofPattern("yyyyMMddHHmmss")));
        filename.append(".xlsx");
        try {
            response.setContentType("application/octet-stream");
            response.setHeader("Content-disposition",
                    "attachment;filename=" + new String(filename.toString().getBytes("UTF-8"), "ISO8859-1"));
        } catch (IOException e) {
            e.printStackTrace();
        }
    }