java导出Excel

1. 首先获取poi    jar包
2. 页面加了超链接    <a href="/exportExecl.do">导出Excel</a>
3. 编写java代码

class JavaBean{
		private String name;
		private String id;
		public String getName() {
			return name;
		}
		public void setName(String name) {
			this.name = name;
		}
		public String getId() {
			return id;
		}
		public void setId(String id) {
			this.id = id;
		}
	}
	
	public ModelAndView exportExecl(HttpServletRequest request,HttpServletResponse response) throws ServletException, IOException {
		//先获取需要导出的数据
		List<JavaBean> list = new ArrayList<JavaBean>();
		JavaBean jb = null;
		for(int i=0;i<10;i++){
			jb = new JavaBean();
			jb.setName("李寻欢"+i);
			jb.setId("Num"+i);
			list.add(jb);
		}
		//设置导出格式
		response.reset();
		response.setContentType("application/vnd.ms-excel;charset=UTF-8");
		response.setHeader("Content-Disposition" ,"attachment;filename="+new String("测试导出列表.xls".getBytes(),"iso-8859-1"));
		
		OutputStream os = response.getOutputStream();//将 WritableWorkbook 写入到输出流
		//创建一个Excel
		HSSFWorkbook workbook = new HSSFWorkbook();  
		//创建一个sheet
		HSSFSheet sheet = workbook.createSheet("列表");  
	        //设置表格默认列宽度为15个字节  
	        sheet.setDefaultColumnWidth((short) 20);  
	        //生成一个样式(用于单元格)  
	        HSSFCellStyle style = workbook.createCellStyle();  
	        style.setFillForegroundColor(HSSFColor.SKY_BLUE.index);  
	        style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);  
	        style.setBorderBottom(HSSFCellStyle.BORDER_THIN);  
	        style.setBorderLeft(HSSFCellStyle.BORDER_THIN);  
	        style.setBorderRight(HSSFCellStyle.BORDER_THIN);  
	        style.setBorderTop(HSSFCellStyle.BORDER_THIN);  
	        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);  
	        //生成一个字体(用于单元格)  
	        HSSFFont font = workbook.createFont();  
	        font.setColor(HSSFColor.VIOLET.index);  
	        font.setFontHeightInPoints((short)12);  
	        font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);  
	        //把字体应用到当前的样式  
	        style.setFont(font);  
	    
	        //产生表格标题行  
	        HSSFRow row = sheet.createRow(0); 
	        String[] headers = new String[]{"名称","id"};
	        for(int i = 0; i < headers.length; i++){  
	            HSSFCell cell = row.createCell((short) i);  
	            cell.setCellStyle(style);//设置单元格样式(包含字体)  
	            HSSFRichTextString text = new HSSFRichTextString(headers[i]);  
	            cell.setCellValue(text);//把数据放到单元格中  
	        }  
	        //遍历集合数据,产生数据行  
	        if(list!=null){
	        Iterator<JavaBean> it = list.iterator();  
	        int index = 0;  
	        while(it.hasNext()){  
	            index++;  
	            row = sheet.createRow(index);  
	            JavaBean planBean = (JavaBean)it.next();
	            for(int i = 0; i < headers.length; i++){  
	                HSSFCell cell = row.createCell((short) i);  
	                HSSFCellStyle style2 = getCellStyles(workbook);//内容可以设置另一种样式
	                cell.setCellStyle(style2);  
	                if(i==0){
	                	cell.setCellValue(planBean.getName());	
	                	if(StringUtil.isEmpty(planBean.getName())){
	                		cell.setCellValue("");
	                	}
	                }
	                if(i==1){
	                	cell.setCellValue(planBean.getId());
	                	if(StringUtil.isEmpty(planBean.getId())){
	                		cell.setCellValue("");
	                	}
	                }
	            }  
	        }  
	        try {  
	            workbook.write(os); 
	        } catch (IOException e1) {  
	        	logger.error("导出数据IO异常", e1); 
	        }finally{
	        	if(os!=null){
	        		os.close();
	        	}
	        } 
	    }
	    return null; 
	}
	private HSSFCellStyle getCellStyles(HSSFWorkbook workbook){
		//生成并设置另一个样式    内容
	    HSSFCellStyle style = workbook.createCellStyle();  
	    style.setFillForegroundColor(HSSFColor.WHITE.index);  //.LIGHT_YELLOW.index
	    style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);  
	    style.setBorderBottom(HSSFCellStyle.BORDER_THIN);  
	    style.setBorderLeft(HSSFCellStyle.BORDER_THIN);  
	    style.setBorderRight(HSSFCellStyle.BORDER_THIN);  
	    style.setBorderTop(HSSFCellStyle.BORDER_THIN);  
	    style.setAlignment(HSSFCellStyle.ALIGN_CENTER);  
	    style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);  
	    //生成另一个字体  
	    HSSFFont font = workbook.createFont(); 
        font.setColor(HSSFColor.RED.index);
	    font.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);  
	    //把字体应用到当前的样式  
	    style.setFont(font);
		return style;  
	}



读取Excel到数据库

<pre name="code" class="java">package main;

import java.io.File;
import jxl.Sheet;
import jxl.Workbook;
import db.Db;

public class Test{
	
    public static void main(String[] args) { 
    	 insertData();
    }  
    static String sourceFile = "C://Users//Administrator//Desktop//测试导出列表.xls"; // 源文件

	public static void insertData() {
		try {
			Workbook book = Workbook.getWorkbook(new File(sourceFile));

			// 0代表第一个工作表对象
			Sheet sheet = book.getSheet(0);
			int rows = sheet.getRows();
			int cols = sheet.getColumns();
			int count = 0;
			for (int z = 1; z < rows; z++) {
				String tmp = "";
				// 0代表列数，z代表行数
				String name = sheet.getCell(0, z).getContents();
				String num = sheet.getCell(1, z).getContents();
				Object obj[] = new Object[] {name,num};
				// 插入数据库
				insertToDataBase(obj);

			}
			System.out.println(count);
		} catch (Exception e) {
			//logger.warn("插入失败!!!!!!!!!!!!!", e);
		}
	}

	private static void insertToDataBase(Object[] obj) {
		String sql = "insert into test(name,num) values(?,?)";

		int i = Db.executeUpdate(sql, obj);
		System.out.println(i);
		if (i > 0) {
			//logger.warn("插入成功");
		} else {
			//logger.warn("插入失败!!!!!!!!!!!!!!!!!!!1");
			for (Object o : obj) {
				System.out.println(o);
			}
			return;
		}
	}
    
}