SpringMVC + JDBC + POI Excel导出与导入 +下载 资源

原创 2017年01月19日 15:35:35

最近用 POI 做了个 导入 导出,所以自己就搭了个 SpringMVC + JDBC + POI 的简单框架,把过程 记录一下,方便回忆

demo框架: SpringMVC + JDBC  数据库:mysql

我们 侧重点在 表格导入导出,关于框架部分 不在描述


直接贴代码:

页面部分:show.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8"%>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core"%>
<%@ taglib prefix="fn" uri="http://java.sun.com/jsp/jstl/functions"%>
<c:set var="baseurl" value="${pageContext.request.contextPath}" />
<%
String path = request.getContextPath();
String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/";
%>

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<script type="text/javascript" src="${baseurl }/js/jquery.js"></script>
<script type="text/javascript" src="${baseurl }/js/jquery.form.js"></script>
<title>demo</title>
</head>
<body>


	<form method="post"  action="<%=basePath%>/excel/exportExcel" data-validate-type="server">
	<input type="submit" value="导出Excel">
	</form>
	<form id="file_form" action="<%=basePath%>/excel/importExcel" enctype="multipart/form-data"
        method="post">
        <input type="file" name="file" id="file_input" /> 
        <input type="submit" value="文件上传" id='upFile-btn'>
    </form>
    <script type="text/javascript">
        $(function() {

            $("#file_form").submit(
                    function() {
                        //首先验证文件格式
                        var fileName = $('#file_input').val();
                        if (fileName === '') {
                            alert('请选择文件');
                            return false;
                        }
                        var fileType = (fileName.substring(fileName
                                .lastIndexOf(".") + 1, fileName.length))
                                .toLowerCase();
                        if (fileType !== 'xls' && fileType !== 'xlsx') {
                            alert('文件格式不正确,excel文件!');
                            return false;
                        }

                        $("#file_form").ajaxSubmit({
                           // dataType : "json",
                            success : function(data) {
                                if (data === 'OK') {
                                    console.log('上传文件成功');
                                } else {
                                    console.log('文件格式错误');
                                }
                                return false;
                            },
							error : function(){
							
                               alert("error....");
                                return false;
							}
                        });
                        return false;
                    });

        });
    </script>
</body>
</html>

controller 部分:

package com.demo.main.excel.controller;

import java.io.File;
import java.io.IOException;
import java.io.OutputStream;
import java.io.PrintWriter;
import java.io.UnsupportedEncodingException;
import java.net.URLEncoder;

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

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseBody;

import com.demo.main.excel.service.ExcelService;
import com.demo.main.util.WebUtil;

@Controller
@RequestMapping("/excel")
public class ExcelController {

	// 引入 service 层
	@Autowired
	private ExcelService excelService;
	
	
	@RequestMapping("/show")
	public String main(){
	
		System.out.println("main...");
		return "show";
	}
	// excel 表格导出
	@RequestMapping("/exportExcel")
	public String exportExcel(HttpServletRequest request,HttpServletResponse response){
		OutputStream os = null;
		HSSFWorkbook wb = excelService.writeExcel();
		
		String fileName = "部门名单.xls";
		response.setContentType("application/vnd.ms-excel");
		try {
			response.setHeader("Content-disposition", "attachment;filename="+ URLEncoder.encode(fileName, "utf-8"));  
			os = response.getOutputStream();  
			wb.write(os);
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} 
		return null;
	}
	
	@RequestMapping("/importExcel")
	@ResponseBody
	public String importExcel(HttpServletRequest request,HttpServletResponse response){
		PrintWriter out = null;
		try {
			 out = response.getWriter();
			 String msg = excelService.importExcel(request, response);
			 out.write(msg);
			 out.flush();
			 out.close();
		} catch (Exception e) {
			e.printStackTrace();
			
		}
		
		return null;
	}
	@RequestMapping("/downLoadFile")
	public String downLoadFile(HttpServletRequest request,HttpServletResponse response){
		
		try {
			//baseUrl:E:\gyy\local_tomcat_7.0.53\webapps\demo
			String path = WebUtil.getBaseUrl()+File.separator+"downloads"+File.separator+"botnet"+File.separator+"CCBlack.xls";
			
			excelService.dowmloads(path, response, request );
		} catch (Exception e) {
			e.printStackTrace();
			
		}
		
		return null;
	}
	
	
	
}

service 部分:

package com.demo.main.excel.service;

import java.io.BufferedInputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.io.PrintWriter;
import java.net.URL;
import java.net.URLEncoder;
import java.text.DecimalFormat;
import java.text.NumberFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

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

import org.apache.commons.fileupload.FileItem;
import org.apache.commons.fileupload.FileItemFactory;
import org.apache.commons.fileupload.disk.DiskFileItemFactory;
import org.apache.commons.fileupload.servlet.ServletFileUpload;
import org.apache.commons.fileupload.util.mime.MimeUtility;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CreationHelper;
import org.apache.poi.ss.usermodel.DateUtil;
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.usermodel.WorkbookFactory;
import org.apache.poi.ss.util.CellRangeAddress;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import com.demo.main.excel.dao.PeopleDao;
import com.demo.main.excel.data.AllPeople;

@Service("excelService")
public class ExcelService {

	@Autowired
	private PeopleDao peopleDao;// = new PeopleDao() 
//	peopleDao = new PeoPleDao();
	public HSSFWorkbook writeExcel(){
		HSSFWorkbook wook = new HSSFWorkbook();
		HSSFSheet sheet=wook.createSheet("部门名单");  
		CreationHelper createHelper=wook.getCreationHelper();  
        HSSFCellStyle style=wook.createCellStyle(); 
        //在sheet中创建一行  
		 HSSFRow rowTop=sheet.createRow(0); 
		 HSSFCell createCell = rowTop.createCell(0);
		 createCell.setCellValue("部门名额");
		 style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		 createCell.setCellStyle(style);
		//合并单元格CellRangeAddress构造参数依次表示起始行,截至行,起始列, 截至列
		 sheet.addMergedRegion(new CellRangeAddress(0,0,0,2));
		 
		 HSSFRow row=sheet.createRow(1);  
          
        //在该行写入各种类型的数据  
        row.createCell(0).setCellValue("序号");  
        row.createCell(1).setCellValue("姓名");  
        row.createCell(2).setCellValue("时间");
        // 遍历数据库 取出数据,放入表格里
        //在写入 日期格式的 数据需要进行特殊处理(这是一种 简单的处理方式)  
         
        style.setDataFormat(createHelper.createDataFormat().getFormat("yyyy-MM-dd"));
        int rowNum = 1;
        List<AllPeople> peopleList = peopleDao.queryList(0);
        for (AllPeople allPeople : peopleList) {
        	rowNum ++;
        	 HSSFRow rowInsert=sheet.createRow(rowNum);
        	 rowInsert.createCell(0).setCellValue(allPeople.getId());
        	 rowInsert.createCell(1).setCellValue(allPeople.getName());
        	 HSSFCell cell = rowInsert.createCell(2);
        	 cell.setCellValue(new Date());
        	 cell.setCellStyle(style);
		}
     return wook;
	}
	
	/**
	 * 从页面上传 Excel 表格
	 * @param request
	 * @param response
	 */
	public String importExcel(HttpServletRequest request,HttpServletResponse response){
		    response.setCharacterEncoding("utf-8");
	     //   response.setContentType("application/json");
	        try {
				FileItemFactory factory = new DiskFileItemFactory();
		        ServletFileUpload upload = new ServletFileUpload(factory);
		        upload.setHeaderEncoding(request.getCharacterEncoding());
		        List<FileItem> list = upload.parseRequest(request);
		        // 暂时 只读取一个 sheet
	            for (int i = 0; i <1; i++) {
	                FileItem item = list.get(0);
	                if (item.getName().endsWith(".xls")||item.getName().endsWith(".xlsx")) {
	                    
	                	List<AllPeople> excelList = getExcelList(item.getInputStream());
	                	peopleDao.insertData(excelList);
	                	
	                	return "ok";
	                } else {
	                	return "false";
	                }
	            }
	          
	        } catch (Exception e) {
				e.printStackTrace();
				return "false";
			}
	       
	        return "false";
	}
	/**
	 * 下载模板 
	 */
	public  void dowmloads(String filepath, HttpServletResponse response,
			HttpServletRequest request) throws IOException {
		File file = new File(filepath);
		if (!(file.exists())) {
			response.sendError(404);
			return;
		}
		BufferedInputStream bis = new BufferedInputStream(new FileInputStream(
				file));
		byte[] buy = new byte[1024];

		response.reset();
		
		//1.设置文件ContentType类型,这样设置,会自动判断下载文件类型  
        response.setContentType("multipart/form-data");
      //2.设置文件头:最后一个参数是设置下载文件名(假如我们叫a.pdf)  
        response.setHeader("Content-Disposition", "attachment;fileName="+ file.getName());
	
		OutputStream ops = response.getOutputStream();
		int len;
		while ((len = bis.read(buy)) > 0) {
			
			ops.write(buy, 0, len);
		}
		bis.close();
		ops.close();
		ops.flush();
	}
	
    /***
     * 这种方法支持03,和07版本的excel读取
     * 但是对于合并的单元格,除了第一行第一列之外,其他部分读取的值为空
     * @param is
     * @throws IOException 
     * @throws Exception 
     */
    public List<AllPeople>  getExcelList(InputStream is) throws Exception {
    	ArrayList<AllPeople> peopleList = new ArrayList<AllPeople>();
       
            Workbook wb = WorkbookFactory.create(is);
            int sheetNumber = wb.getNumberOfSheets();
          // 所有的 sheet 
            for (int i = 0; i < sheetNumber; i++) {
                Sheet sheet = wb.getSheetAt(i);
                // 跳过表头
                for (int j = 2; j <= sheet.getLastRowNum(); j++) {
                   
                    Row row = sheet.getRow(j);
                    AllPeople people = new AllPeople();
                    // 读取每一个单元格
                   
                    people.setId(Integer.parseInt(getValue(row.getCell(0))));//.split(".")[0]
                  
                    people.setName(getValue(row.getCell(1)));
                    
                    people.setSelectTime(getValue(row.getCell(2)));
                    
                    peopleList.add(people);
                }
            }
       
        
        return peopleList;
    }
    
    /**
     * 获取单元格内容
     *
     * @param cell
     * @return
     */
    public static String getValue(Cell cell) {
        String cellValue = "";
        if (cell == null)
            return cellValue;
        switch (cell.getCellType()) {
            case HSSFCell.CELL_TYPE_NUMERIC:
                if (HSSFDateUtil.isCellDateFormatted(cell)) {
                    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                    cellValue = sdf.format(HSSFDateUtil.getJavaDate(cell.getNumericCellValue())).toString();
                    return cellValue;
                } else {
                    double strCell = cell.getNumericCellValue();
                    DecimalFormat formatCell = (DecimalFormat) NumberFormat.getPercentInstance();
                    formatCell.applyPattern("0");
                    cellValue = formatCell.format(strCell);
                    if (Double.parseDouble(cellValue) != strCell) {
                        formatCell.applyPattern("0.00");
                        cellValue = formatCell.format(strCell);
                    }
                    return cellValue;
                }
            case HSSFCell.CELL_TYPE_STRING:
                cellValue = cell.getStringCellValue();
                return cellValue.trim();
            case HSSFCell.CELL_TYPE_FORMULA:
                cellValue = cell.getCellFormula();
                return cellValue.trim();
            case HSSFCell.CELL_TYPE_BLANK:
                return cellValue.trim();
            case HSSFCell.CELL_TYPE_BOOLEAN:
                cellValue = cell.getBooleanCellValue() + "";
                return cellValue.trim();
            case HSSFCell.CELL_TYPE_ERROR:
                cellValue = cell.getErrorCellValue() + "";
                return cellValue.trim();
            default:
                return cellValue.trim();
        }
    }


	public static void main(String[] args) {
		new ExcelService().writeExcel();
	}
}

dao 层 部分:

package com.demo.main.excel.dao;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Random;

import org.springframework.stereotype.Repository;

import com.demo.main.excel.data.AllPeople;
import com.demo.main.util.JdbcUtil;
import com.mysql.jdbc.PreparedStatement;

@Repository("peopleDao")
public class PeopleDao {

	/**
	 *  初始化数据 导入,把图片数据(all 中) 全部导入 数据库
	 * @param allPepple
	 */
	public void initData(List<String>  allPepple){
		Connection con = JdbcUtil.getConnection();
		try {
			Statement statemenet = con.createStatement();
			if(allPepple != null && allPepple.size() > 0){
				for (String name : allPepple) {
					String query = "insert into allpeople (name, isSelected) values('"
				            + name + "','" + 0 + "')";
				    statemenet.addBatch(query);

				}
			}
			
			int[] number = statemenet.executeBatch();
			System.out.println(" 共插入 数据:"+number.length +"条!");
			statemenet.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			//JdbcUtil.close(con);
		}
	}
	/**
	 *  初始化数据 导入,把图片数据(all 中) 全部导入 数据库
	 * @param allPepple
	 */
	public void insertData(List<AllPeople>  peppleList){
		Connection con = JdbcUtil.getConnection();
		try {
			Statement statemenet = con.createStatement();
			if(peppleList != null && peppleList.size() > 0){
				for (AllPeople people : peppleList) {
					String query = "insert into allpeople (name, isSelected,selectTime) values('"
							+ people.getName() + "','" +people.getIsSelect() + "','" + people.getSelectTime() + "')";
					statemenet.addBatch(query);
					
				}
			}
			
			int[] number = statemenet.executeBatch();
			System.out.println(" 共插入 数据:"+number.length +"条!");
			statemenet.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	/**
	 * 抽中后 ,更新状态
	 * @param name
	 */
	public void updateInfo(String name ,int isSelected){
		Connection con = JdbcUtil.getConnection();
		String insertTime ="";
		if(isSelected == 1){
			SimpleDateFormat now = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
			String nowTime = now.format( new Date() ); 
		
			insertTime = nowTime;
			 
		}
	 String sql ="update allpeople SET selectTime = '"+insertTime+"',isSelected = '" + isSelected + "' where name='" + name + "'";
	 PreparedStatement pstmt;
	
	    try {
	        pstmt = (PreparedStatement) con.prepareStatement(sql);
	        pstmt.executeUpdate();
	        pstmt.close();
	        
	    } catch (SQLException e) {
	        e.printStackTrace();
	    }finally{
	    	//JdbcUtil.close(con);
	    }
	   
	}

	/**
	 * 
	 * @param isSelected 1: 选择  0:没有选中
	 * @return
	 */
	public List<AllPeople> queryList(int isSelected){
		Connection con = JdbcUtil.getConnection();
		List<AllPeople> list = new ArrayList<AllPeople>();
		String sql = "select * from allpeople where isSelected = ?";
		PreparedStatement pstmt;

		try {
			//根据sql语句创建预处理对象
			pstmt = (PreparedStatement) con.prepareStatement(sql);
			
			pstmt.setObject(1, isSelected);
			//执行更新
			ResultSet rs = pstmt.executeQuery();
			while(rs.next()){
			AllPeople people = new AllPeople();
				String name=rs.getString("name");
				int  id=rs.getInt("id");
				people.setId(id);
				people.setName(name);
				list.add(people);
			}
			pstmt.close();
			} catch (SQLException e) {
			e.printStackTrace();

			}
		
		return list;
}
	public void clearData(){
		Connection con = JdbcUtil.getConnection();
		try {
			Statement sta = con.createStatement();
			String sql = "TRUNCATE TABLE allpeople";
			sta.execute(sql);
			sta.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
}

实体类部分:

package com.demo.main.excel.data;

public class AllPeople {

	private int id;
	private String name;
	private int isSelect;
	private String selectTime;
	public int getId() {
		return id;
	}
	public void setId(int id) {
		this.id = id;
	}
	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
	public int getIsSelect() {
		return isSelect;
	}
	public void setIsSelect(int isSelect) {
		this.isSelect = isSelect;
	}
	public String getSelectTime() {
		return selectTime;
	}
	public void setSelectTime(String selectTime) {
		this.selectTime = selectTime;
	}
	
}



版权声明:本文为博主原创文章,未经博主允许不得转载。

相关文章推荐

SpringMvc 使用poi导入导出Excel

转载自http://webook-java.iteye.com/blog/1699621 下面2个博客也不错,简洁明了 http://www.cnblogs.com/littlecharacter...

SpringMvc 使用poi导入导出Excel

Util类   package com.common.util;      public class ExportUtil   {     ...

SpringMvc 使用poi导入导出Excel

controller @ResponseBody @RequestMapping(value = "/chxm/exportAndUpdate") public void ex...

SpringMVC实现poi 解析excel 导入导出

SpringMVC实现poi 解析excel 导入导出 详解听说csdn编辑支持markdown了,果断从博客园转了过来(谁让我是markdown中毒依赖者~~)。这是我在csdn博客上的第一篇文章。...

在springmvc项目中使用poi导入导出excel

首先要导入spring相关包,poi,和fileupload包,我是使用maven构建的。 一.导入excel (1)使用spring上传文件 a.前台页面提交

在springmvc项目中使用poi导入导出excel

首先要导入spring相关包,poi,和fileupload包,我是使用maven构建的。 一.导入excel (1)使用spring上传文件 a.前台页面提...

SpringMVC+POI下载文件模板和导出Excel

如果文件模板是固定的,直接放到服务目录下,然后可以用下面的代码下载 下载office模版       如果是需要从数据库查询出来导出的,则参考下面的代码 @RequestMapping(...

SpringMvc+POI 导入Excel

  • 2015-11-18 10:17
  • 17.23MB
  • 下载
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:深度学习:神经网络中的前向传播和反向传播算法推导
举报原因:
原因补充:

(最多只允许输入30个字)