将数据库查询到的数据导入Excel

最新推荐文章于 2024-06-27 17:55:30 发布

九宫格输入法

最新推荐文章于 2024-06-27 17:55:30 发布

阅读量778

点赞数

分类专栏： Java

本文链接：https://blog.csdn.net/Stubborn_bull/article/details/94032728

版权

Java 专栏收录该内容

4 篇文章 0 订阅

订阅专栏

import java.io.BufferedInputStream;
import java.io.BufferedOutputStream;
import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;

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.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

	/**列表
	 * @param page
	 * @throws Exception
	 */
	@RequestMapping(value="/list")
	public ModelAndView list(HttpServletRequest request, HttpServletResponse response) throws Exception{
		ModelAndView mv = this.getModelAndView();
		PageData pd = new PageData();
		pd = this.getPageData();
		String keywords = pd.getString("keywords");				//关键词检索条件
		if(null != keywords && !"".equals(keywords)){
			pd.put("keywords", keywords.trim());
		}
		page.setPd(pd);
		List<PageData>	varList = qms_iqcstatisticsService.list(page);	//列出QMS_iqcStatistics列表
		StringBuffer buffer=new StringBuffer();
		for (PageData pageData : varList) {
			String INSPECTIONNAME="";
			String INSPECTIONNO="";
			INSPECTIONNAME+=pageData.get("INSPECTIONNAME");
			INSPECTIONNO+=pageData.get("INSPECTIONNO");
			buffer.append(INSPECTIONNAME.trim()+","+INSPECTIONNO.trim()+";");
		}
        //数据结构是    String s="1,2;4,5,6;7,8,9";  就行
		String s=buffer.toString();
		String[] sFirst=s.split(";");//获取一维数组的长度
		String[][] d=new String[sFirst.length][];
		for(int i=0;i<sFirst.length;i++){
			String[] split = sFirst[i].split(",");
			d[i]=new String[split.length];
			for (int j = 0; j < split.length; j++) {
				d[i][j]=split[j].toString();
			}
		}
		
		HSSFWorkbook wb = new HSSFWorkbook();
		HSSFSheet sheet = wb.createSheet("table");  //创建table工作薄
		HSSFRow row;
		HSSFCell cell;
		for(int i = 0; i < d.length; i++) {
		    row = sheet.createRow(i);//创建表格行
		    for(int j = 0; j < d[i].length; j++) {
		        cell = row.createCell(j);//根据表格行创建单元格
		        cell.setCellValue(String.valueOf(d[i][j]));
		    }
		}
		   ByteArrayOutputStream os = new ByteArrayOutputStream();
		   wb.write(os);
		   byte[] content = os.toByteArray();
		   InputStream is = new ByteArrayInputStream(content);
		   response.reset();
		   response.setContentType("application/vnd.ms-excel;charset=utf-8");
		   response.setHeader("Content-Disposition", "attachment;filename=table.xls");
		   
		   ServletOutputStream out = response.getOutputStream();
		      BufferedInputStream bis = null;
		      BufferedOutputStream bos = null;
		 
		      try {
		        bis = new BufferedInputStream(is);
		        bos = new BufferedOutputStream(out);
		        byte[] buff = new byte[2048];
		        int bytesRead;
		        while (-1 != (bytesRead = bis.read(buff, 0, buff.length))) {
		          bos.write(buff, 0, bytesRead);
		        }
		      } catch (Exception e) {
		        e.printStackTrace();
		      } finally {
		        if (bis != null)
		          bis.close();
		        if (bos != null)
		          bos.close();
		      }
    // 有下面这段代码会下载两个一样的文件，只是路径不同：一个是下面的路径一个是谷歌的默认路径
	//	wb.write(new FileOutputStream("D:/test/table.xls"));	

		return mv;
	}