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

时间：2022-04-08 13:16:04

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

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

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

这是架构

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

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">
	<!--
	<link rel="stylesheet" type="text/css" href="styles.css">
	-->
  </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>
javaWEB中 利用HSSFWorkbook进行Excle的读写案例javaWEB中 利用HSSFWorkbook进行Excle的读写案例
最后我需要提醒的是 
   sheet.setColumnWidth(0, 9* 256+184)

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


下面这是实例源码，https://pan.baidu.com/s/1mkorbMo

标签：

相关文章