JavaWeb实现服务器SSXSFWorkbook 导出excel到本地(包含c3p0包链接和SSXSFWorkbook 包链接)

JavaWeb实现服务器SSXSFWorkbook 导出excel到本地(包含c3p0包链接和SSXSFWorkbook 包链接),第1张

JavaWeb实现服务器SSXSFWorkbook 导出excel到本地(包含c3p0包链接和SSXSFWorkbook 包链接) SSXSFWorkbook

该包为apache的poi组件,我使用的是3.16,需要的可以自行下载,链接如下:

链接:https://pan.baidu.com/s/1hSd2N0RikUHuSonxy2EwHQ 
提取码:kndm

当我们只要使用xls格式时、只要导入poi-version-yyyymmdd.jar就可以了。
当我们还要使用xlsx格式、还要导入poi-ooxml-version-yyyymmdd.jar。

路径为web的lib下:

我用到的jar包为一以下的包:

由于我使用了c3p0数据库连接池,以及数据库的工具类util因此我将c3p0数据库连接池等的依赖包链接也放到下面:

链接:https://pan.baidu.com/s/1hFmw8J3N97gpulMr4L6ltg 
提取码:jv1l

HTML代码如下:

只有用以导出的按钮

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>




Insert title here


	    
	
	

生成一个名为SsxsfWorkbook的类代码如下:

代码里面有一些简介,可以参考一些组件的作用,详细参考可自行百度。

import java.util.List;
import java.util.Map;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class SsxsfWorkbook {
	
    
    public static Workbook createWorkBook(List> list,String []keys,String columnNames[]) {
        // 创建excel工作簿
        SXSSFWorkbook wb = new SXSSFWorkbook(100);//在内存中只保留100行记录,超过100就将之前的存储到磁盘里
        // 创建第一个sheet(页),并命名
        Sheet sheet = wb.createSheet(list.get(0).get("sheetName").toString()); 
        // 手动设置列宽。第一个参数表示要为第几列设;,第二个参数表示列的宽度,n为列高的像素数。
        for(int i=0;i 

生成一个Servlet类,名为text,代码如下:

import java.io.BufferedInputStream;
import java.io.BufferedOutputStream;
import java.io.ByteArrayInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.sql.SQLException;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.servlet.ServletException;
import javax.servlet.ServletOutputStream;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.tomcat.util.http.fileupload.ByteArrayOutputStream;

import cn.SSXSFWorkbook.ExportExcel;
import cn.SSXSFWorkbook.SsxsfWorkbook;
import cn.javabean.User;
import cn.jdbc.DBUtilsDao;




@WebServlet(name ="test",urlPatterns ="/test")
public class test extends HttpServlet {
	private static final long serialVersionUID = 1L;
       
    
    public test() {
        super();
        
        
    }

	
	protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
	
	}

	
	protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		request.setCharacterEncoding("utf-8");
		response.setCharacterEncoding("utf-8");
		response.setContentType("text/html");
		DBUtilsDao dao = new DBUtilsDao();
        Date d = new Date();
        SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMddHHmmss");
        String formatTime = sdf.format(d);
        String fileName="系统用户表-"+formatTime;
        String columnNames[]={"ID", "用户名", "姓名", "性别", "电话号码"};//列名
        String keys[] = {"uid", "username", "name", "sex", "phone"};//map中的key
   
		try {
			List userlist = dao.findAll();
			System.out.println(userlist.size());
			
			
			List> list=createExcelRecord(userlist);
			 
			
			SXSSFWorkbook workbook = (SXSSFWorkbook) SsxsfWorkbook.createWorkBook(list,keys,columnNames);//.write(os);
	        
			if(response !=null)
			{
            response.setContentType("application/vnd.ms-excel;charset=utf-8");
            response.setHeader("Content-Disposition",
                    "attachment;filename="" + new String(("excel" + ".xlsx").getBytes("utf-8"), "utf-8"));
            OutputStream out =  response.getOutputStream();
			workbook.write(out);
			out.close();
			}
            
        
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
       
		 
	}


	private List> createExcelRecord(List userlist) {
		
		 List> listmap = new ArrayList>();
		 
	        Map map = new HashMap();
	        
	        map.put("sheetName", "sheet1");
	        
	        listmap.add(map);
	        
	        User user = null;
	        for (int j = 0; j < userlist.size(); j++) {
	            user = userlist.get(j);
	            Map mapValue = new HashMap();
	            mapValue.put("uid", user.getUid());
	            mapValue.put("username",user.getUsername());
	            mapValue.put("name",user.getName());
	            mapValue.put("sex", user.getSex());
	            mapValue.put("phone", user.getPhone());
				

	            listmap.add(mapValue);
	        }
	        return listmap;
		
	}

}

 由于我是从数据库里提取出的数据,因此我用了数据库连接池c3p0,你们也可以自己写一个自己的数据库连接,替换掉我的代码:

        DBUtilsDao dao = new DBUtilsDao();
        List userlist = dao.findAll();

也可以自行修改createExcelRecord方法。

如果照做的话,创建一个javabean User类

public class User {
	private  int uid;
	private  String username;
	private  String name;
	private  String sex;
	private  String phone;
	
	
	}
//自行create Getters 和Setters方法

数据库连接池C3p0Utils类如下:

import java.beans.PropertyVetoException;
import java.io.IOException;
import java.sql.SQLException;
import java.util.Properties;
import javax.sql.DataSource;

import com.mchange.v2.c3p0.ComboPooledDataSource;


public class C3p0Utils {
	private static DataSource ds = null;
	
	public	static DataSource getDataSource(){

		ComboPooledDataSource cpds = new ComboPooledDataSource();
		//设置连接数据库需要的配置信息
		try {

			cpds.setDriverClass("com.mysql.cj.jdbc.Driver");
			cpds.setJdbcUrl("jdbc:mysql://localhost:3306/shopping_system?useUnicode=true&characterEncoding=utf-8&allowPublicKeyRetrieval=true&useSSL=false&serverTimezone=GMT%2B8");
			cpds.setUser("root");//自行修改mysql账户名
			cpds.setPassword("123");//修改mysql密码
			cpds.setInitialPoolSize(10);//可以不改(数据库连接池数量)
			cpds.setMaxPoolSize(39);//可以不改(数据库连接池最大数量)
			ds = cpds;
			
		} catch (PropertyVetoException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return ds;
	}
	

}

该c3p0数据库连接池的依赖包如下:

数据库控制语句:

创建DBUtilsDao类

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import cn.javabean.User;



public class DBUtilsDao {
	
	
	//查询所有users
	public List findAll() throws SQLException{
		
		QueryRunner runner = new QueryRunner(C3p0Utils.getDataSource());
		String sql = "select *from users";
		
		List  list = (List) runner.query(sql, new BeanListHandler(User.class));
		
		return list;
	}
	
}

存入到数据库的SQL代码:

create database shopping_system;

create table users(
uid int(8),
username varchar(8) not null,
name varchar(12) not null,
sex varchar(2) not null,
phone varchar(12)
);

insert into users (uid,username,sex,phone,name) values (1,'10001','男','183456789','张三');
insert into users (uid,username,sex,phone,name) values (2,'10002','男','135689456','张四');
insert into users (uid,username,sex,phone,name) values (3,'10003','女','125874568','王五');
insert into users (uid,username,sex,phone,name) values (4,'10004','男','134585354','李六');
insert into users (uid,username,sex,phone,name) values (5,'10005','女','123569845','林伟');
insert into users (uid,username,sex,phone,name) values (6,'10006','男','148546985','陈贺');

完成之后,直接打开text.jsp点击确认即可导出:

点击之后d出:

 

 

欢迎分享,转载请注明来源:内存溢出

原文地址: http://outofmemory.cn/zaji/5660004.html

(0)
打赏 微信扫一扫 微信扫一扫 支付宝扫一扫 支付宝扫一扫
上一篇 2022-12-16
下一篇 2022-12-16

发表评论

登录后才能评论

评论列表(0条)

保存