数据库表导出成Excel工具类

package com.chinasofti.articlesys.util;


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

	import javax.servlet.http.HttpServletResponse;

	import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFHeader;
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.ss.usermodel.HorizontalAlignment;
import org.apache.struts2.ServletActionContext;

import com.chinasofti.articlesys.bean.ArchiveBean;
import com.chinasofti.articlesys.bean.ArchiveBean;
import com.chinasofti.articlesys.service.ArchiveTypeService;




	public class ExportUtilForQuery {
	    
	    
	    public static void export(List<ArchiveBean> list)throws Exception
	    {  
	    	if(list==null){
	    		return;
	    	}

	    
	    	/*HSSFWorkbook wb = new HSSFWorkbook();  
	        // 第二步，在webbook中添加一个sheet,对应Excel文件中的sheet  
	        HSSFSheet sheet = wb.createSheet("档案表");
	        sheet.setDefaultColumnWidth(20);
	        sheet.setDefaultRowHeight((short)15);
	        // 创建字体对象   
	        HSSFCellStyle style = wb.createCellStyle();
	        HSSFFont font = wb.createFont();
	        font.setFontHeightInPoints((short)12);
//	       font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
	       font.setBold(true);
	       
	        style.setAlignment(HorizontalAlignment.CENTER); // 居中
	        style.setFont(font);       
	        // 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制short  
	        HSSFRow row = sheet.createRow(0);
	        
	        HSSFCell cell = row.createCell(0);
	        cell.setCellValue("档案编号");
	        cell.setCellStyle(style);      
	        cell=row.createCell(1);
	        cell.setCellValue("档案标题");
	        cell.setCellStyle(style);
	        cell=row.createCell(2);
	        cell.setCellValue("档案内容");
	        cell.setCellStyle(style);
	        cell=row.createCell(3);
	        cell.setCellValue("创建人");        
	        cell.setCellStyle(style);
	        
	        for (int i = 0; i < list.size(); i++)  
	        {  
	            row = sheet.createRow(i+1);  
	            ArchiveBean bean =list.get(i);  
	            // 第四步，创建单元格，并设置值  
	            row.createCell(0).setCellValue(bean.getArchiveNo());  
	            row.createCell(1).setCellValue(bean.getTitle());  
	            row.createCell(2).setCellValue(bean.getContent());  
	            row.createCell(3).setCellValue(bean.getCreater().getName()); 
	        }  */
	    	
	    	 String []tableHeader={"档案编号","档案标题","档案内容","创建人"};
	 	    /*
	 	                 *下面的都可以拷贝不用编写
	 	                 */
	 	    short cellNumber=(short)tableHeader.length;//表的列数
	 	    HSSFWorkbook workbook = new HSSFWorkbook(); //创建一个excel
	 	    HSSFCell cell = null; //Excel的列
	 	    HSSFRow row = null; //Excel的行
	 	    HSSFCellStyle style = workbook.createCellStyle(); //设置表头的类型
//	 	    style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
	 	    style.setAlignment(HorizontalAlignment.CENTER);
	 	    HSSFCellStyle style1 = workbook.createCellStyle(); //设置数据类型
//	 	    style1.setAlignment(HSSFCellStyle.ALIGN_CENTER);
	 	    style1.setAlignment(HorizontalAlignment.CENTER);
	 	    HSSFFont font = workbook.createFont(); //设置字体
	 	    HSSFSheet sheet = workbook.createSheet("sheet1"); //创建一个sheet
	 	    HSSFHeader header = sheet.getHeader();//设置sheet的头
	 	    try {              /**
	 	                         *根据是否取出数据，设置header信息
	 	                         *
	 	                         */
	 	    if(list.size() < 1 ){
	 	    header.setCenter("查无资料");
	 	    }else{
	 	    header.setCenter("档案表");
	 	    row = sheet.createRow(0);
	 	    row.setHeight((short)400);
	 	    for(int k = 0;k < cellNumber;k++){
	 	    cell = row.createCell(k);//创建第0行第k列
	 	    cell.setCellValue(tableHeader[k]);//设置第0行第k列的值
	 	    sheet.setColumnWidth(k,8000);//设置列的宽度
	 	    font.setColor(HSSFFont.COLOR_NORMAL); // 设置单元格字体的颜色.
	 	    font.setFontHeight((short)350); //设置单元字体高度
	 	    style1.setFont(font);//设置字体风格
	 	    cell.setCellStyle(style1);
	 	    }
	 	                            /*
	 	                             * // 给excel填充数据这里需要编写
	 	                             *    
	 	                             */
	 	    for(int i = 0 ;i < list.size() ;i++){   
	 	     ArchiveBean beanType = list.get(i);//获取sign对象
	 	        row = sheet.createRow((short) (i + 1));//创建第i+1行
	 	        row.setHeight((short)400);//设置行高
	 	       
	 	        if(beanType.getArchiveNo()!= null){
	 	        cell = row.createCell(0);//创建第i+1行第0列
	 	        cell.setCellValue(beanType.getArchiveNo());//设置第i+1行第0列的值
	 	    cell.setCellStyle(style);//设置风格
	 	        }
	 	        if(beanType.getTitle() != null){
	 	        cell = row.createCell(1); //创建第i+1行第1列

	 	        cell.setCellValue(beanType.getTitle());//设置第i+1行第1列的值

	 	        cell.setCellStyle(style); //设置风格
	 	        }
	 	    //由于下面的和上面的基本相同，就不加注释了
	 	        if(beanType.getContent() != null){
	 	        cell = row.createCell(2);
	 	        cell.setCellValue(beanType.getContent());
	 	        cell.setCellStyle(style);
	 	        }
	 	        if(beanType.getCreater()!= null){
	 	        cell = row.createCell(3);
	 	        cell.setCellValue(""+beanType.getCreater());
	 	        cell.setCellStyle(style);
	 	        }
	 	        /*if(sign.getSclass() != null){
	 	        cell = row.createCell(4);
	 	        cell.setCellValue(sign.getSclass());
	 	        cell.setCellStyle(style);
	 	        }
	 	        if(sign.getMdate()!= null){
	 	            cell = row.createCell(5);
	 	            cell.setCellValue(sign.getMdate());
	 	            cell.setCellStyle(style);
	 	            }*/
	 	       
	 	    }

	 	    }

	 	    } catch (Exception e) {
	 	    e.printStackTrace();
	 	    }
	    	
	    	
	        try  
	        {   HttpServletResponse response=null;
	        	OutputStream out = null;//创建一个输出流对象
			    try {
			    response = ServletActionContext.getResponse();//初始化HttpServletResponse对象
			    out = response.getOutputStream();//
			            response.setHeader("Content-disposition","attachment; filename="+"SearchResult"+".xls");//filename是下载的xls的名，建议最好用英文
			            response.setContentType("application/msexcel;charset=UTF-8");//设置类型
			            response.setHeader("Pragma","No-cache");//设置头
			            response.setHeader("Cache-Control","no-cache");//设置头
			            response.setDateHeader("Expires", 0);//设置日期头
			            workbook.write(out);
			            out.flush();
			       
			    } catch (IOException e) {
			    e.printStackTrace();
			    }finally{
			    try{

			    if(out!=null){
			    out.close();
			    }

			    }catch(IOException e){
			    e.printStackTrace();
			    }

			    }
	            
	        }  
	        catch (Exception e)  
	        {  
	            e.printStackTrace(); 
	            
	        } 
	        
	        
	        
	        
//	        return null;
	    } 
	}

转载于:https://www.cnblogs.com/LingRenne/p/7821130.html