javaWEB中利用HSSFWorkbook进行Excle的读写案例

今天药忘吃喽~ 2022-05-31 00:18 272阅读 0赞

**javaWEB中 利用HSSFWorkbook进行Excle的读写案例**

今天给大家介绍一下HSSFWorkbook的用法：一个小案例，我没连接数据库，当然连接上数据库，就可以从数据库里进行读取数据。

首先要添加上jar包，这几个是必须的（这里要注意HSSFWorkbook和XSSFWorkbook的用法和区别）当然，这里用的是HSSFWorkbook。后缀是xsl

这是架构

![SouthEast][]

StudentExportController.java

package com.cn.excel;
    
    import java.io.IOException;
    import java.io.OutputStream;
    import java.util.ArrayList;
    import java.util.List;
    
    import javax.servlet.ServletException;
    import javax.servlet.http.HttpServlet;
    import javax.servlet.http.HttpServletRequest;
    import javax.servlet.http.HttpServletResponse;
    
    import ocm.cn.bean.Student;
    
    import org.apache.poi.hssf.usermodel.HSSFWorkbook;
    
    import com.cn.service.StudentExportService;
    
    public class StudentExportController extends HttpServlet {
    	@Override
    	protected void doPost(HttpServletRequest req, HttpServletResponse resp)
    			throws ServletException, IOException {
    		// TODO Auto-generated method stub
    		 List<Student> list = new ArrayList<Student>();  
    	        list.add(new Student(1000,"zhangsan","20"));  
    	        list.add(new Student(1001,"lisi","23"));  
    	        list.add(new Student(1002,"wangwu","25")); 
    	        StudentExportService  service=new StudentExportService();
    	        HSSFWorkbook wb = service.export(list);  
    	        resp.setContentType("application/vnd.ms-excel");  
    	        resp.setHeader("Content-disposition", "attachment;filename=student.xls");  
    	        OutputStream ouputStream = resp.getOutputStream();  
    	        wb.write(ouputStream);  
    	        ouputStream.flush();  
    	        ouputStream.close(); 
    	}
    	@Override
    	protected void doGet(HttpServletRequest req, HttpServletResponse resp)
    			throws ServletException, IOException {
    		// TODO Auto-generated method stub
    		doPost(req, resp);
    	}
    }

StudentExportService.java

package com.cn.service;
    
    import java.util.List;
    
    import ocm.cn.bean.Student;
    
    import org.apache.poi.hssf.usermodel.HSSFCell;
    import org.apache.poi.hssf.usermodel.HSSFCellStyle;
    import org.apache.poi.hssf.usermodel.HSSFRow;
    import org.apache.poi.hssf.usermodel.HSSFSheet;
    import org.apache.poi.hssf.usermodel.HSSFWorkbook;
    
    public class StudentExportService {  
    	  
        String[] excelHeader = { "Sno", "Name", "Age"};  
        public HSSFWorkbook export(List<Student> list) {  
            HSSFWorkbook wb = new HSSFWorkbook();  
            HSSFSheet sheet = wb.createSheet("Student");  
            HSSFRow row = sheet.createRow((int) 0);  
            HSSFCellStyle style = wb.createCellStyle();  
            style.setAlignment(HSSFCellStyle.ALIGN_CENTER);  
      
            for (int i = 0; i < excelHeader.length; i++) {  
                HSSFCell cell = row.createCell(i);  
                cell.setCellValue(excelHeader[i]);  
                cell.setCellStyle(style);  
                sheet.autoSizeColumn(i); 
                sheet.setColumnWidth(0, 9* 256+184);
              // sheet.SetColumnWidth(i, 100 * 256);  
            }  
      
            for (int i = 0; i < list.size(); i++) {  
                row = sheet.createRow(i + 1);  
                Student student = list.get(i);  
                row.createCell(0).setCellValue(student.getSno());  
                row.createCell(1).setCellValue(student.getName());  
                row.createCell(2).setCellValue(student.getAge());  
            }  
            return wb;  
        }  
    }

Student.java

package ocm.cn.bean;
    
    public class Student {
    
    	private int Sno;
    	private String name; 
    	private String age;
    	public Student(int sno, String name, String age) {
    		super();
    		Sno = sno;
    		this.name = name;
    		this.age = age;
    	}
    	public int getSno() {
    		return Sno;
    	}
    	public void setSno(int sno) {
    		Sno = sno;
    	}
    	public String getName() {
    		return name;
    	}
    	public void setName(String name) {
    		this.name = name;
    	}
    	public String getAge() {
    		return age;
    	}
    	public void setAge(String age) {
    		this.age = age;
    	}
    	
    	
    }

index.jsp，里面只加了一个超链接

<%@ page language="java" import="java.util.*" pageEncoding="GBK"%>
    <%
    String path = request.getContextPath();
    String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/";
    %>
    
    <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
    <html>
      <head>
        <base href="<%=basePath%>">
        
        <title>My JSP 'index.jsp' starting page</title>
    	<meta http-equiv="pragma" content="no-cache">
    	<meta http-equiv="cache-control" content="no-cache">
    	<meta http-equiv="expires" content="0">    
    	<meta http-equiv="keywords" content="keyword1,keyword2,keyword3">
    	<meta http-equiv="description" content="This is my page">
    	
      </head>
      
      <body>
         <a href="StudentExportController">下载EXCEL表格</a>
      </body>
    </html>

最后不要忘了 web.xml中配置文件，（因为这是用的简单的servelt）

<?xml version="1.0" encoding="UTF-8"?>
    <web-app version="2.5" 
    	xmlns="http://java.sun.com/xml/ns/javaee" 
    	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
    	xsi:schemaLocation="http://java.sun.com/xml/ns/javaee 
    	http://java.sun.com/xml/ns/javaee/web-app_2_5.xsd">
      <welcome-file-list>
        <welcome-file>index.jsp</welcome-file>
      </welcome-file-list>
      <servlet>
        <servlet-name>StudentExportController</servlet-name>
        <servlet-class>com.cn.excel.StudentExportController</servlet-class>
      </servlet>
    
    
      <servlet-mapping>
        <servlet-name>StudentExportController</servlet-name>
        <url-pattern>/StudentExportController</url-pattern>
      </servlet-mapping>
    </web-app>

![SouthEast 1][] ![SouthEast 2][]  
最后我需要提醒的是

sheet.setColumnWidth(0, 9* 256+184)

**设置excel宽度，这里我在上一篇已经提到过了，如果要需要，请看上篇[http://blog.csdn.net/zhupengqq/article/details/79226067][http_blog.csdn.net_zhupengqq_article_details_79226067]**

**下面这是实例源码，[https://pan.baidu.com/s/1mkorbMo][https_pan.baidu.com_s_1mkorbMo]**

#  #

[SouthEast]: /images/20220531/bd3fbcd9385f43bbae0e294608052a9f.png
[SouthEast 1]: https://img-blog.csdn.net/20180201114416277?watermark/2/text/aHR0cDovL2Jsb2cuY3Nkbi5uZXQvemh1cGVuZ3Fx/font/5a6L5L2T/fontsize/400/fill/I0JBQkFCMA==/dissolve/70/gravity/SouthEast
[SouthEast 2]: /images/20220531/c43b5410653a422d8d3d9429c6bf8bc1.png
[http_blog.csdn.net_zhupengqq_article_details_79226067]: http://blog.csdn.net/zhupengqq/article/details/79226067
[https_pan.baidu.com_s_1mkorbMo]: https://pan.baidu.com/s/1mkorbMo