操作excel表

最新推荐文章于 2024-08-26 22:31:41 发布

企鹅漫步

最新推荐文章于 2024-08-26 22:31:41 发布

阅读量781

点赞数

CC 4.0 BY-SA版权

分类专栏： Java_Web

本文链接：https://blog.youkuaiyun.com/bamboo261/article/details/10714747

Java_Web 专栏收录该内容

9 篇文章

订阅专栏

以下是代码演示：

index:

<%@ page language="java" import="java.util.*" pageEncoding="UTF-8"%>
<%
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="downLoad">下载</a>
  </body>
</html>

web.xml:

<?xml version="1.0" encoding="UTF-8"?>
<web-app version="3.0" 
	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_3_0.xsd">
  <display-name></display-name>	
  
  <servlet>
  	<servlet-name>DownLoad</servlet-name>
  	<servlet-class>com.zuxia.yc42.servlet.PoiServlet</servlet-class>
  </servlet>
  <servlet-mapping>
  	<servlet-name>DownLoad</servlet-name>
  	<url-pattern>/downLoad</url-pattern>
  </servlet-mapping>
  
  <!-- 配置JSTL标签库 -->
  <jsp-config>
  	<taglib>
  		<taglib-uri>http://www.zuxia.com/yc42</taglib-uri>
  		<taglib-location>/WEB-INF/tld/c.tld</taglib-location>
  	</taglib>
  	<taglib>
  		<taglib-uri>http://www.zuxia.com/yc42/purview</taglib-uri>
  		<taglib-location>/WEB-INF/tld/purview.tld</taglib-location>
  	</taglib>
  </jsp-config>
  
  <!-- 欢迎页面 -->
  <welcome-file-list>
    <welcome-file>index.jsp</welcome-file>
  </welcome-file-list>
</web-app>

com.zuxia.yc42.po

——public class UserInfo implements Serializable :

package com.zuxia.yc42.po;

import java.io.Serializable;

public class UserInfo implements Serializable 
{

	private Integer usId;
	private String usName;
	private Integer usAge;
	private String usSex;
	public Integer getUsId() {
		return usId;
	}
	public void setUsId(Integer usId) {
		this.usId = usId;
	}
	public String getUsName() {
		return usName;
	}
	public void setUsName(String usName) {
		this.usName = usName;
	}
	public Integer getUsAge() {
		return usAge;
	}
	public void setUsAge(Integer usAge) {
		this.usAge = usAge;
	}
	public String getUsSex() {
		return usSex;
	}
	public void setUsSex(String usSex) {
		this.usSex = usSex;
	}
	
	
}

com.zuxia.yc42.util

——public class ExportExcel<T>：

package com.zuxia.yc42.util;

import java.io.IOException;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Iterator;
import java.util.List;

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 ExportExcel<T>
{

	public void exportExcel(OutputStream out) throws IOException
	{
		//创建文档对象
		HSSFWorkbook work = new HSSFWorkbook();
		//创建表单对象
		HSSFSheet sheet = work.createSheet("工作表单");
		//创建新行
		HSSFRow row = sheet.createRow(0);
		//创建单元格
		HSSFCell cell0 = row.createCell(0);
		HSSFCell cell1 = row.createCell(1);
		//给单元格设置值
		cell0.setCellValue("学号");
		cell1.setCellValue("姓名");
		//给单元格设置样式
		HSSFCellStyle style = work.createCellStyle();
		style.setFillBackgroundColor(HSSFCellStyle.BORDER_DOTTED);
		cell0.setCellStyle(style);
		
		//将文档对象写入输出流
		work.write(out);
		
	}
	
	/**
	 * 
	 * @param headers  标题列
	 * @param dataset  数据集合
	 * @param out      输出流
	 * @throws IOException 
	 */
	public void exportExcel(String[] headers,Collection<T> dataset,OutputStream out) throws IOException
	{
		Field[] fields = null;
		//创建文档对象
		HSSFWorkbook work = new HSSFWorkbook();
		//创建表单对象
		HSSFSheet sheet = work.createSheet("工作表单");
		//创建标题行
		HSSFRow row_0 = sheet.createRow(0);
		
		//设置标题行内容
		for(int i = 0;i<headers.length;i++)
		{
			//创建单元格
			HSSFCell cell = row_0.createCell(i);
			//设置单元格内容
			cell.setCellValue(headers[i]);
		}
		
		
		Iterator<T> iterator = dataset.iterator();
		
		for(int i=1;iterator.hasNext();i++)
		{
			T t = iterator.next();
			
			if(fields==null)
			{
				fields = t.getClass().getDeclaredFields();
			}
			
			//创建标题行
			HSSFRow row = sheet.createRow(i);
			
			//设置标题行内容
			for(int j = 0;j<fields.length;j++)
			{
				//创建单元格
				HSSFCell cell = row.createCell(j);
				//设置单元格内容
				try {
					fields[j].setAccessible(true);
					cell.setCellValue(fields[j].get(t).toString());
				} catch (IllegalArgumentException e) {
					e.printStackTrace();
				} catch (IllegalAccessException e) {
					e.printStackTrace();
				}
			}
		}
		work.write(out);
	}
}

com.zuxia.yc42.test

——public class Main：

package com.zuxia.yc42.test;

import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;

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 Main {

	/**
	 * @param args
	 */
	public static void main(String[] args)
	{
		//创建文档对象
		HSSFWorkbook work = new HSSFWorkbook();
		//创建表单对象
		HSSFSheet sheet = work.createSheet("工作表单");
		//创建新行
		HSSFRow row = sheet.createRow(0);
		//创建单元格
		HSSFCell cell0 = row.createCell(0);
		HSSFCell cell1 = row.createCell(1);
		//给单元格设置值
		cell0.setCellValue("学号");
		cell1.setCellValue("姓名");
		//给单元格设置样式
		HSSFCellStyle style = work.createCellStyle();
		style.setFillBackgroundColor(HSSFCellStyle.BORDER_DOTTED);
		cell0.setCellStyle(style);
		
		FileOutputStream stream = null;
		try 
		{
			stream = new FileOutputStream("e:\\text.xls");
			work.write(stream);
			stream.flush();
			
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		}
		finally
		{
			if(stream!=null)
			{
				try {
					stream.close();
				} catch (IOException e) {
					e.printStackTrace();
				}
			}
		}
		
		
		
	}

}

com.zuxia.yc42.servlet

——public class PoiServlet extends HttpServlet：

package com.zuxia.yc42.servlet;

import java.io.IOException;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.List;

import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import com.zuxia.yc42.po.UserInfo;
import com.zuxia.yc42.util.ExportExcel;

public class PoiServlet extends HttpServlet {

	private ExportExcel exportExcel;
	
	public PoiServlet()
	{
		exportExcel = new ExportExcel();
	}
	
	public void doPost(HttpServletRequest request,HttpServletResponse response)
	{
		String[] headers = {"编号","用户名","年龄","性别"};
		List<UserInfo> dataset = new ArrayList<UserInfo>();
		UserInfo usInfo1 =new UserInfo();
		usInfo1.setUsId(1);
		usInfo1.setUsName("小强");
		usInfo1.setUsAge(18);
		usInfo1.setUsSex("女");
		UserInfo usInfo2 =new UserInfo();
		usInfo2.setUsId(2);
		usInfo2.setUsName("小明");
		usInfo2.setUsAge(20);
		usInfo2.setUsSex("男");
		dataset.add(usInfo1);
		dataset.add(usInfo2);
		
		response.setContentType("octets/stream");

	  	response.addHeader("Content-Disposition", "attachment;filename=test.xls");
	  	
	  	OutputStream out = null;
	  	try 
	  	{
	  		out = response.getOutputStream();
			//exportExcel.exportExcel(out);
	  		exportExcel.exportExcel(headers, dataset, out);
			out.flush();
			System.out.println("excel导出成功！");
		} 
	  	catch (IOException e) 
		{
			e.printStackTrace();
		}
	  	finally
	  	{
	  		if(out!=null)
	  		{
	  			try {
					out.close();
				} catch (IOException e) {
					e.printStackTrace();
				}
	  		}
	  	}


	}

	public void doGet(HttpServletRequest request,HttpServletResponse response)
	{
		doPost(request,response);
	}
}