java中的导出_在java中导出excel-CSDN博客

本文链接：https://blog.csdn.net/weixin_39798943/article/details/114162002

package com.huawei.controller;

import java.io.File;

import java.io.IOException;

import java.util.HashMap;

import java.util.List;

import java.util.Map;

import javax.servlet.ServletContext;

import javax.servlet.ServletException;

import javax.servlet.http.HttpServlet;

import javax.servlet.http.HttpServletRequest;

import javax.servlet.http.HttpServletResponse;

import org.apache.commons.fileupload.FileItem;

import org.apache.commons.fileupload.FileUploadException;

import org.apache.commons.fileupload.disk.DiskFileItemFactory;

import org.apache.commons.fileupload.servlet.ServletFileUpload;

import org.apache.poi.ss.usermodel.Workbook;

import com.huawei.common.Page;

import com.huawei.po.Users;

import com.huawei.service.UsersService;

/**

* Servlet implementation class UsersController

public class UsersController extends HttpServlet {

private static final long serialVersionUID = 1L;

private UsersService usersService = new UsersService();

/**

* @see HttpServlet#HttpServlet()

public UsersController() {

super();

// TODO Auto-generated constructor stub

}

/**

* @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)

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

// TODO Auto-generated method stub

this.doPost(request, response);

}

/**

* @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)

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

/**

* 将当前的doPost方法当作中专站

String _method = request.getParameter("_method");

if("findAll".equalsIgnoreCase(_method)){

this.findAll(request, response);

}else if("deleteById".equalsIgnoreCase(_method)){

this.deleteById(request, response);

}else if("register".equalsIgnoreCase(_method)){

this.register(request, response);

}else if("findById".equalsIgnoreCase(_method)){

this.findById(request, response);

}else if("update".equalsIgnoreCase(_method)){

this.update(request, response);

}else if("analyzeAge".equalsIgnoreCase(_method)){

this.analyzeAge(request, response);

}else if("exportExcel".equalsIgnoreCase(_method)){

this.exportExcel(request, response);

}

/**

* 当从前台提交一个请求过来的时候应该去后台数据库将数据查询出来动态的生成WorkBook

* 直接将WorkBook的输出流定向到 response的输出流中去

* @param request

* @param response

* @throws ServletException

* @throws IOException

private void exportExcel(HttpServletRequest request,HttpServletResponse response) throws ServletException, IOException{

response.setHeader("Content-Disposition", "attachment;filename=\"users.xls\"");

//response.getOutputStream();

Workbook wb =this.usersService.exportExcel();

wb.write(response.getOutputStream());

wb.close();

}

private void analyzeAge(HttpServletRequest request,HttpServletResponse response) throws ServletException, IOException{

// TODO Auto-generated method stub

response.getWriter().write(this.usersService.analyzeAge());

}

private void update(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException{

Users curr = (Users) request.getSession().getAttribute("admin");

if(ServletFileUpload.isMultipartContent(request)){

Map map = new HashMap();

//创建工厂

DiskFileItemFactory factory = new DiskFileItemFactory();

//得到JVM提供的缓存目录

ServletContext context = this.getServletContext();

File repository = (File)context.getAttribute("javax.servlet.context.tempdir");

factory.setRepository(repository);

//创建ServletFileUpload

ServletFileUpload upload = new ServletFileUpload(factory);

//解析请求

try {

List items = upload.parseRequest(request);

//users = new Users();

for(FileItem item:items){

//form表单里面的每一个字段

map.put(item.getFieldName(), item);

}

Users u = new Users();

u.setId(Integer.parseInt(map.get("id").getString()));

u.setUsername(map.get("username").getString());

u.setPassword(map.get("password").getString());

u.setEmail(map.get("email").getString());

//u.setImage(Integer.parseInt(map.get("id").getString()));

u.setAge(Integer.parseInt(map.get("age").getString()));

//处理前一步的数据没有顺序的问题

//for(String key:map.keySet()){

//FileItem item = map.get(key);

//if(item.isFormField()){

//没有顺序

//}else{

//得到跟路径

String path = context.getRealPath("/");

//得到附件目录

File attachment = new File(path,"attachment/"+map.get("username").getString());

//如果没有就创建目录

System.out.println(attachment.getAbsolutePath());

System.out.println(attachment.exists());

if(!attachment.exists()){

attachment.mkdirs();

}

FileItem image = map.get("image");

File output = new File(attachment,System.currentTimeMillis()+"."+getSuffix(image.getName()));

image.write(output);

u.setImage("attachment/"+map.get("username").getString()+"/"+output.getName());

//System.currentTimeMillis()

this.usersService.update(u);

this.findAll(request, response);

//}

//map.get("id");

} catch (Exception e) {

e.printStackTrace();

}

private String getSuffix(String name){

if(name!=null){

String[] suffixs = name.split("\\.");

if(suffixs.length>1){

return suffixs[suffixs.length-1];

}

return "";

}

private void findById(HttpServletRequest request,HttpServletResponse response) throws ServletException, IOException {

String id = request.getParameter("id");

if(id!=null && id.trim()!=""){

Users users = this.usersService.findById(Integer.parseInt(id));

request.setAttribute("users", users);

request.getRequestDispatcher("/views/update.jsp").forward(request, response);

return ;

}

private void register(HttpServletRequest request,HttpServletResponse response) throws ServletException, IOException {

// TODO Auto-generated method stub

Users users = null;

//判断是否有文件上传

if(ServletFileUpload.isMultipartContent(request)){

//创建工厂

DiskFileItemFactory factory = new DiskFileItemFactory();

//得到JVM提供的缓存目录

ServletContext context = this.getServletContext();

File repository = (File)context.getAttribute("javax.servlet.context.tempdir");

factory.setRepository(repository);

//创建ServletFileUpload

ServletFileUpload upload = new ServletFileUpload(factory);

//解析请求

try {

List items = upload.parseRequest(request);

users = new Users();

for(FileItem item:items){

//form表单里面的每一个字段

}

//得到跟路径

String path = context.getRealPath("/");

//得到附件目录

File attachment = new File(path,"attachment");

//如果没有就创建目录

if(!attachment.exists()){

attachment.mkdirs();

}

} catch (FileUploadException e) {

e.printStackTrace();

}

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

// TODO Auto-generated method stub

String id = request.getParameter("id");

this.usersService.deleteById(Integer.parseInt(id));

response.sendRedirect("usersController?_method=findAll");

}

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

//List users = this.usersService.findAll();

//request.setAttribute("users", users);

String keyword = request.getParameter("keyword");

if(keyword==null){

keyword = "";

}

Page page = new Page();

String curr = request.getParameter("curr");

if(curr ==null || curr.trim().equals("")){

curr ="1";

}

page.getKeywords().put("username", keyword);

page.setCurr(Integer.parseInt(curr));

page = this.usersService.find4Page(page);

request.setAttribute("page", page);

request.getRequestDispatcher("/views/index.jsp").forward(request, response);

return;

}

package com.huawei.service;

import java.lang.reflect.Field;

import java.lang.reflect.Method;

import java.util.Date;

import java.util.List;

import java.util.regex.Pattern;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import org.apache.poi.ss.usermodel.Cell;

import org.apache.poi.ss.usermodel.CellStyle;

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.ss.util.CellRangeAddress;

import com.huawei.common.Page;

import com.huawei.dao.UsersDAO;

import com.huawei.po.Users;

import com.huawei.utils.BeanUtil;

public class UsersService {

private UsersDAO usersDAO = new UsersDAO();

public List findAll(){

return this.usersDAO.findAll();

}

public void deleteById(Integer id) {

// TODO Auto-generated method stub

this.usersDAO.delete(id);

}

public Page find4Page(Page page){

return this.usersDAO.find4Page(page);

}

public Users findByUsername(String username) {

List users = this.usersDAO.findBy("username",username);

return users.size()>0?users.get(0):null;

}

public Users findById(Integer id){

return this.usersDAO.findById(id);

}

public void update(Users users){

this.usersDAO.update(users);

}

public String analyzeAge(){

return this.usersDAO.analyzeAge();

}

public Workbook exportExcel(){

//创建一个工作薄

Workbook wb = new HSSFWorkbook();

List users = this.findAll();

//用户生成excel的列数

Field []fields = Users.class.getDeclaredFields();

Sheet sheet = wb.createSheet("用户信息");

Row title = sheet.createRow(0);

//设置样式

CellStyle cellStyle = wb.createCellStyle();

//设置字体的对其方式

cellStyle.setAlignment(CellStyle.ALIGN_CENTER);

cellStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);

Cell tCell = title.createCell(0);

tCell.setCellValue("用户信息列表");

tCell.setCellStyle(cellStyle);

//合并单元格

sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, fields.length-1));

Row header = sheet.createRow(1);

for(int i=0;i

Cell cell = header.createCell(i);

//将属性的名字当作表头

cell.setCellValue(fields[i].getName());

}

for(int i=0;i

Row row = sheet.createRow(i+2);

for(int j=0;j

Cell cell = row.createCell(j);

try{

Method method = Users.class.getDeclaredMethod(BeanUtil.getter(fields[j]));

Object value = method.invoke(users.get(i));

if(value!=null){

if(value instanceof Date){

}else if(value instanceof Number){

cell.setCellValue(Double.parseDouble(value+""));

}else if(value instanceof String){

if(Pattern.matches("^\\d+(\\.\\d+)?$", value+"")){

cell.setCellValue(Double.parseDouble(value+""));

}else{

cell.setCellValue(value+"");

}

}else{

cell.setCellValue("");

}

/*if(value==null){

cell.setCellValue("");

}else{

cell.setCellValue(value.toString());

}*/

//cell.setcellva

}catch (Exception e) {

e.printStackTrace();

}

return wb;

}

package com.huawei.dao;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.util.List;

import com.huawei.base.BaseDAO;

import com.huawei.common.CallBack;

import com.huawei.common.Page;

import com.huawei.po.Users;

import com.huawei.utils.BeanUtil;

public class UsersDAO extends BaseDAO{

@Override

public Page find4Page(Page page) {

//构建sql语句

String sql = "SELECT * FROM "+BeanUtil.getTableName(clazz)+" WHERE username LIKE ? LIMIT ?,?";

String count = "SELECT COUNT(1) FROM "+BeanUtil.getTableName(clazz)+" WHERE username LIKE ?";

//得到数据

List data = this.find(sql, new Object[]{"%"+page.getKeywords().get("username")+"%",(page.getCurr()-1)*page.getPageSize(),page.getPageSize()});

page.setRowCount(this.getCount(count,new Object[]{"%"+page.getKeywords().get("username")+"%"}));

page.setData(data);

return page;

}

public String analyzeAge(){

final StringBuilder sb = new StringBuilder("[");

String sql = "SELECT "+

"CASE "+

"WHEN age>0 && age<11 THEN '十岁以下' "+

"WHEN age>10 && age<21 THEN '11-20' "+

"WHEN age>20 && age<31 THEN '21-30' "+

"WHEN age>30 && age<41 THEN '31-40' "+

"WHEN age>40 && age<51 THEN '41-50' "+

"WHEN age>50 && age<61 THEN '51-60' "+

"WHEN age>60 && age<71 THEN '61-70' "+

"WHEN age>70 && age<81 THEN '71-80' "+

"WHEN age>80 && age<91 THEN '81-90' "+

"WHEN age>90 && age<101 THEN '91-100' "+

"WHEN age>100 THEN '一百岁以上' "+

"END as 'label',count(age) as 'value' "+

"FROM "+

"users "+

"GROUP BY label ";

this.executeQuery(sql, null, new CallBack() {

@Override

public void execute(ResultSet rs) throws SQLException {

//处理数据

//"{\"name\":\"\",\"value\":\"\"}"

while(rs.next()){

sb.append("{").append("\"name\":\"").append(rs.getString("label")+"\",\"value\":\"").append(rs.getInt("value")+"\"").append("},");

}

});

if(sb.length()>1){

sb.deleteCharAt(sb.length()-1);

}

sb.append("]");

return sb.toString();

}

package com.huawei.base;

import java.io.Serializable;

import java.lang.reflect.Array;

import java.lang.reflect.Field;

import java.lang.reflect.Method;

import java.lang.reflect.ParameterizedType;

import java.sql.Connection;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.ResultSetMetaData;

import java.sql.SQLException;

import java.util.ArrayList;

import java.util.List;

import com.huawei.common.CallBack;

import com.huawei.common.Page;

import com.huawei.utils.BeanUtil;

import com.huawei.utils.DBUtil;

/**

* @author Administrator

* void executeQuery(String sql,Object[] parmas,CallBack callback)通用的查询

* void executeUpdate(String sql,Object[] parmas)//通用的执行

* List findAll()//查询全部

* Object findOne(Integer id)//根据id查询一个

* void save(Object o)//给定一个对象然后存储到数据库

* void update(Object o)//根据一个对象更新数据库中所对应的字段

* void delete(Object o)//根据一个对象删除数据库中对应的记录

* void deleteById(Integer id)//根据传入的id删除

* void delete(String sql,Object[] params)//自定义删除

public abstract class BaseDAO {

//目标类用于反射

protected Class clazz;

@SuppressWarnings("unchecked")

public BaseDAO() {

ParameterizedType type = (ParameterizedType) this.getClass().getGenericSuperclass();

//得到类上面的泛型参数的实际类型

clazz = (Class) type.getActualTypeArguments()[0];

}

/**

* 通用的查寻方法

* @param sql给定一个sql语句

* @param params给定与sql语句中相对应的参数

* @param callBack用于处理结果集的回调函数

public void executeQuery(String sql,Object []params,CallBack callBack){

Connection connection = DBUtil.getConnection();

PreparedStatement ps = null;

ResultSet rs = null;

try {

ps = connection.prepareStatement(sql);

//处理参数

if(params!=null && params.length>0){

for(int i=0;i

ps.setObject(i+1, params[i]);

}

System.out.println("ORM:"+sql);

rs = ps.executeQuery();

//处理业务逻辑

callBack.execute(rs);

} catch (SQLException e) {

e.printStackTrace();

}finally{

DBUtil.close(rs,ps,connection);

}

/**

* 除了查询以外的所有操作

* @param sql给定一个sql语句

* @param params 参数

public void executeUpdate(String sql,Object []params){

Connection connection = DBUtil.getConnection();

PreparedStatement ps = null;

try {

ps = connection.prepareStatement(sql);

//处理参数

if(params!=null && params.length>0){

for(int i=0;i

ps.setObject(i+1, params[i]);

}

System.out.println("ORM:"+sql);

ps.executeUpdate();

} catch (SQLException e) {

e.printStackTrace();

}finally{

DBUtil.close(ps,connection);

}

/**

* 通用查询

* @param sql

* @param params

* @return

public List find(String sql,Object []params){

final List result = new ArrayList();

this.executeQuery(sql, params, new CallBack() {

@Override

public void execute(ResultSet rs) throws SQLException {

//处理结果

try {

//得到虚拟表的结构信息

ResultSetMetaData rsmd = rs.getMetaData();

while(rs.next()){

//每一行代表一个对象

T o = clazz.newInstance();

//每一个单元格代表对象中的一个属性

for(int i=0;i

//得到列明

String column_name = rsmd.getColumnName(i+1);

//根据列名去得到 clazz中的属性

Field field = clazz.getDeclaredField(column_name);

//得到 set方法 setUsername(String name)

Method method = clazz.getDeclaredMethod(BeanUtil.setter(field), field.getType());

method.invoke(o, rs.getObject(column_name));

}

result.add(o);

}

} catch (Exception e) {

e.printStackTrace();

}

});

return result;

}

/**

* 查找全部

* @return返回一个结果集

public List findAll(){

//存储结果集

String sql = "SELECT * FROM "+BeanUtil.getTableName(clazz);

return this.find(sql, null);

}

public T findById(ID id){

String sql = "SELECT * FROM "+BeanUtil.getTableName(clazz) +" WHERE id=?";

List result = this.find(sql, new Object[]{id});

return result.size()>0?result.get(0):null;

}

public List findBy(String prop,Object param){

String sql = "SELECT * FROM "+BeanUtil.getTableName(clazz) +" WHERE "+prop+"=?";

return this.find(sql, new Object[]{param});

}

public Page find4Page(Page page){

//构建sql语句

String sql = "SELECT * FROM "+BeanUtil.getTableName(clazz)+" LIMIT ?,?";

//得到数据

List data = this.find(sql, new Object[]{(page.getCurr()-1)*page.getPageSize(),page.getPageSize()});

page.setRowCount(this.getCount());

page.setData(data);

return page;

}

public Long getCount(){

String sql = "SELECT COUNT(1) FROM "+BeanUtil.getTableName(clazz);

return this.getCount(sql, null);

}

public Long getCount(String sql,Object[] params){

final long []count = new long[]{0L};

this.executeQuery(sql, params, new CallBack() {

@Override

public void execute(ResultSet rs) throws SQLException {

while(rs.next()){

count[0] = rs.getLong(1);

}

});

return count[0];

}

/**

* 将给定的对象持久化到数据库

* @param o被持久化对象

public void save(T o){

StringBuilder sb = new StringBuilder("INSERT INTO "+BeanUtil.getTableName(clazz)+" (");

StringBuilder values = new StringBuilder(" VALUES (");

//存储参数

List params = new ArrayList();

//得到所有的属性

Field []fields = clazz.getDeclaredFields();

try{

for(Field field:fields){

sb.append(BeanUtil.getColumnName(field)).append(",");

values.append("?,");

Method method = clazz.getDeclaredMethod(BeanUtil.getter(field));

//得到属性的值

params.add(method.invoke(o));

}

}catch (Exception e) {

e.printStackTrace();

}

//处理sql语句

sb.deleteCharAt(sb.length()-1).append(")");

values.deleteCharAt(values.length()-1).append(")");

sb.append(values);

this.executeUpdate(sb.toString(), params.toArray());

}

/**

* 更新更新的对象中一定要包含id

* @param o

public void update(T o){

StringBuilder sb = new StringBuilder("UPDATE "+BeanUtil.getTableName(clazz)+" SET ");

//存储参数

List params = new ArrayList();

//得到所有的属性

Field []fields = clazz.getDeclaredFields();

Object id = null;

try{

for(Field field:fields){

//UPDATE USERS SET USERNAME=?,PASSWORD=?

String name = BeanUtil.getColumnName(field);

Method method = clazz.getDeclaredMethod(BeanUtil.getter(field));

//得到属性的值

Object value = method.invoke(o);

if("id".equals(name)){

id = value;

continue;

}

sb.append(name+"=?").append(",");

params.add(value);

}

}catch (Exception e) {

e.printStackTrace();

}

//处理sql语句

sb.deleteCharAt(sb.length()-1).append(" WHERE id=?");

if(id==null){

System.out.println("ID不能为空");

return;

}

params.add(id);

this.executeUpdate(sb.toString(), params.toArray());

}

@SuppressWarnings("unchecked")

public void delete(ID id){

//动态创建泛型数组

ID []ids = (ID[])Array.newInstance(id.getClass(), 1);

ids[0] =id;

this.delete(ids);

}

@SuppressWarnings("unchecked")

public void delete(T o){

try {

ID id = (ID)this.clazz.getDeclaredMethod("getId").invoke(o);

if(id!=null){

this.delete(id);

return ;

}

System.out.println("ID不能为空");

} catch (Exception e) {

e.printStackTrace();

}

public void delete(ID[] ids){

String sql = "DELETE FROM "+BeanUtil.getTableName(clazz) + " WHERE id in (?)";

this.executeUpdate(sql, ids);

}

public void delete(String sql,Object[] params){

this.executeUpdate(sql, params);

}

package com.huawei.utils;

import java.lang.reflect.Field;

/**

* bean 工具

* @author Administrator

public class BeanUtil {

/**

* 获取set方法名

* 获取标名

* @param clazz

public static String getTableName(Class> clazz){

//获取类名

String name = clazz.getSimpleName();

name = name.substring(0, 1).toLowerCase()+name.substring(1);

return name;

}

/**

* @param field

* @return

public static String setter(Field field){

String name = field.getName();

return "set"+name.substring(0,1).toUpperCase()+name.substring(1);

}

public static String getter(Field field){

return getter(field.getName());

}

public static String getter(String name){

return "get"+name.substring(0,1).toUpperCase()+name.substring(1);

}

public static String getColumnName(Field field){

String name = field.getName();

return name.substring(0,1).toLowerCase()+name.substring(1);

}