java io excel导入导出详解

本章介绍excel导入导出后代代码，jsp页面导入参考下章文件上传下载，本章不作页面介绍.
本章基于spring框架。

jar包及源码下载：http://download.csdn.net/download/qq_22860341/9839553

excel导入

1. 传入文件参数即可，或者通过File对象直接new地址。

package cy.component;

import java.io.IOException;
import java.io.InputStream;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Cell;
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.springframework.web.multipart.MultipartFile;

public class Import {

	/*
	 * excel导入
	 */
	public String importexcel(MultipartFile excelFile){
		
		try {
			 InputStream inputStream = excelFile.getInputStream();
			 POIFSFileSystem fs = new POIFSFileSystem(inputStream); 
	         Workbook workbook = WorkbookFactory.create(fs);
	           //获取sheet，若有多个用循环
	         Sheet sheet = workbook.getSheetAt(0);
	          //获取总行数
	         int rowCount = sheet.getPhysicalNumberOfRows();  
	         
	          //循环每一行
	         for (int i = 1; i <rowCount; i++){
	        	//得到行对象
	        	 Row row = sheet.getRow(i);
	        	 
	        	 //得到该行的第1列
	        	 Cell cell0 = row.getCell(0); 
	        	 //得到该行的第2列
	        	 Cell cell1 = row.getCell(1); 
	        	 //得到该行的第3列
	        	 Cell cell2 = row.getCell(2); 
	        	 
	        	 //知道多少列，有多少列加多少列
	        	 //不知道多少列，获取每行有多少列，用循环
	        	 //已经得到每行每列的数据，可以进行自己业务操作
	         }
	         
	         
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}  
        
		
		return null;
		
	}
}

excel导出

package cy.component;

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

import javax.servlet.http.HttpServletResponse;

import org.apache.log4j.Logger;
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.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.util.CellRangeAddress;


public class Export {

	/**
	 * 导出excel
	 */
	
	 
	private static Logger log = Logger.getLogger(Export.class);
	/**
	 * 
	 * @param response
	 * @param list  数据集合
	 * @param titles  列名,需要导出那些字段
	 * @param title   excel标题
	 */
	
	public static void export(HttpServletResponse response,List<Object[]> list,String[] titles,String title){
		 
		//创建一个workbook，对应一个excel文件
	       HSSFWorkbook workBook = new HSSFWorkbook();
	       
	     //添加一个sheet
			HSSFSheet sheet = workBook.createSheet();
			 for(int i=0;i<titles.length;i++){
				 sheet.setColumnWidth(i, 4000);
			 }
			
			//第一行样式
 			HSSFFont font = workBook.createFont();
 			font.setFontHeightInPoints((short)16); //设置字体大小
 			font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); //粗体
 			//设置单元格样式
 			HSSFCellStyle style = workBook.createCellStyle();
 			style.setAlignment(HSSFCellStyle.ALIGN_CENTER); //居中
 			//创建第一行并合并
 			HSSFRow row = sheet.createRow(0);
 			HSSFCell cell = row.createCell(0);
 			cell.setCellValue(title);      //主标题
 			cell.setCellStyle(style);
 			style.setFont(font);
 			sheet.addMergedRegion(new CellRangeAddress(0, 0, 0,titles.length-1 ));	//指定合并区域	
			 
 			//设置字体
			style = workBook.createCellStyle();
			style.setBorderBottom((short)2);
			style.setBorderLeft((short)2);
			style.setBorderRight((short)2);
			style.setBorderTop((short)2);
			font = workBook.createFont();
			font.setFontHeightInPoints((short)16); //设置字体大小
			style.setFont(font); //选择字体格式
			
			//在sheet中添加表头第1行
			row = sheet.createRow(1);
			for(int i = 0; i < titles.length; i++){
				String title1 = titles[i];
				cell = row.createCell(i);
				cell.setCellValue(title1);
				cell.setCellStyle(style);
			}
			
			//写入信息
			font = workBook.createFont();
			font.setFontHeightInPoints((short)12); //设置字体大小
			style.setAlignment(HSSFCellStyle.ALIGN_LEFT); //居中
			style.setFont(font); //选择字体格式
			
		
			for(int i = 0; i < list.size(); i++){
				Object[] obj = (Object[]) list.get(i);
				row = sheet.createRow(i + 2);
				for(int j=0;j<titles.length;j++){
					cell = row.createCell(j);
					if(obj[j]==null ||obj[j]==""){
						cell.setCellValue("");
					}else{
						cell.setCellValue(obj[j].toString());
					}
					
					cell.setCellStyle(style);
				}
				
			}
			
		
			response.setContentType("application/vnd.ms-excel");  
	        response.setHeader("Content-disposition", "attachment;filename=loco" +PubTools.getDate()+ ".xls");
	
	    	try {
				OutputStream ouputStream = response.getOutputStream();
				workBook.write(ouputStream); 
		        ouputStream.flush();  
		        ouputStream.close();
			} catch (IOException e) {
				e.printStackTrace();
				log.error(title+"导出失败：" + e.getMessage());
			}  
	}
	
	
}