POI操作Excel完整示例

    示例使用POI、Servlet完成导入导出

 

导入

ImportServlet

 

package com.servlet;

import java.io.File;
import java.io.IOException;
import java.io.PrintWriter;
import java.util.Iterator;
import java.util.List;

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

import org.apache.commons.fileupload.FileItem;
import org.apache.commons.fileupload.disk.DiskFileItemFactory;
import org.apache.commons.fileupload.servlet.ServletFileUpload;

import com.service.ExcelService;

/**
 * 导入
 */
public class ImportServlet extends HttpServlet {
	private static final long serialVersionUID = 1L;
       
	private ExcelService excelService;
	
    public ImportServlet() {
    	excelService = new ExcelService();
    }

	protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		doPost(request, response);
	}

	protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		response.setContentType("text/html;charset=utf-8");
		PrintWriter out = response.getWriter();
		
		DiskFileItemFactory factory = new DiskFileItemFactory() ;
		factory.setSizeThreshold( 8192 ) ;
		String tempPath = request.getSession().getServletContext().getRealPath("/")
			+ "/upload" ;  
		File tempDir = new File(tempPath) ;
		if(!tempDir.isDirectory()){
			tempDir.mkdirs();
		}
		factory.setRepository(tempDir) ;  
		ServletFileUpload upload = new ServletFileUpload(factory) ;  
		
		List<FileItem> items;
		try {
			items = upload.parseRequest(request);
			Iterator<FileItem> itr = items.iterator();   
			while (itr.hasNext()) {// 依次处理每个 form field
				   FileItem item = (FileItem) itr.next();
				   if(!item.isFormField()){ /* 判断是否为表单控件（非File控件），如果不是表单控件，则上传此文件 */
//					   File savedFile = new File(tempPath,item.getName() ) ;  
					   
					   // 由于不同的浏览器可能取得的文件的名字不同，有的浏览器将整个路径取道，有的浏览器只取到文件名
					   int start = item.getFieldName().lastIndexOf("\\");
					   String fileName = item.getFieldName().substring(start + 1); 
					  
					   File savedFile = new File(tempPath,fileName) ; 
					   item.write(savedFile) ;
					   //解析
					   String msg = excelService.importExcel(savedFile);
					   System.out.println(msg);
					   out.println(msg);
				   }else{/* 如果是表单控件，则保存其值*/
					   System.out.println( item.getFieldName() + "-->" + item.getString() ) ; 
				   }
			}
		}catch (Exception e){
			e.printStackTrace() ; 
		}finally{
			if(out != null){
				out.close();
			}
		}
	}

}

 

导入解析

/**
	 * 解析Excel
	 * @param excelFile
	 * @throws Exception 
	 */
	public String importExcel(File excelFile) throws Exception{
		if(excelFile == null){
			throw new Exception("参数[excelFile]为空.");
		}
		FileInputStream is = new FileInputStream(excelFile);  
        HSSFWorkbook wb=new HSSFWorkbook(is);  
          
        StringBuilder sb = new StringBuilder();
        
        int sheetNum=wb.getNumberOfSheets();  
        System.out.println("Excel页数：" + sheetNum);;
        for(int i=0;i<sheetNum;i++)  
        {  
            HSSFSheet childSheet = wb.getSheetAt(i);  
            int rowNum = childSheet.getLastRowNum();  //行数-1
            System.out.println("Excel记录行数：" + rowNum);
            //j=1 以第二行开始遍历 第一行为标题栏  
            for(int j=1;j<=rowNum;j++){  
                HSSFRow row = childSheet.getRow(j);
                sb.append("\n编号:").append(row.getCell(0).toString())
                .append("\n姓名:").append(row.getCell(1).toString())
                .append("\n性别:").append(row.getCell(2).toString())
                .append("\n出生日期:").append(row.getCell(3).toString());
                
            }  
              
        }  
        return sb.toString();
	}

 

 

导出

 

ExportServlet

 

package com.servlet;

import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;

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

import com.service.ExcelService;
import com.utils.ServletUtils;

/**
 * 导出
 */
public class ExportServlet extends HttpServlet {
	private static final long serialVersionUID = 1L;
       
	private ExcelService excelService;
	
    public ExportServlet() {
    	excelService = new ExcelService();
    }

	protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		doPost(request, response);
	}

	protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		OutputStream outputStream = response.getOutputStream();
		
		InputStream inputStream = excelService.exportExcel();
		//设置文件类型
		response.setContentType(ServletUtils.EXCEL_TYPE);
		//弹出下载对话框
		ServletUtils.setFileDownloadHeader(request, response, "Student.xls");
		
		byte[] buffer = new byte[1024]; 
        int bytesRead;
        while ((bytesRead = inputStream.read(buffer)) != -1){
        	outputStream.write(buffer, 0, bytesRead);
        }
        
        outputStream.close();
		
	}

}

 

导出构造数据

/**
	 * 导出Excel
	 * @return
	 */
	public InputStream exportExcel(){
		HSSFWorkbook wb = new HSSFWorkbook();
		HSSFSheet sheet = wb.createSheet("sheet1");

		HSSFRow row = sheet.createRow(0);

		HSSFCell cell = row.createCell((short) 0);		
		cell.setCellValue("编号");
		
		cell = row.createCell((short) 1);
		cell.setCellValue("姓名");
		
		cell = row.createCell((short) 2);
		cell.setCellValue("性别");
		
		cell = row.createCell((short) 3);
		cell.setCellValue("出生日期");
		List<Student> students = studentService.getStudents();
		for (int i = 0; i < students.size(); ++i){
			Student student = students.get(i);
			
			row = sheet.createRow(i + 1);

			cell = row.createCell((short) 0);
			cell.setCellValue(student.getId());

			cell = row.createCell((short) 1);
			cell.setCellValue(student.getName());

			cell = row.createCell((short) 2);
			cell.setCellValue(student.getSex());
			
			cell = row.createCell((short) 3);
			cell.setCellValue(new SimpleDateFormat("yyyy-MM-dd").format(student.getBirthDate()));
		}
		
        ByteArrayOutputStream os = new ByteArrayOutputStream();
		try{
			wb.write(os);
		}catch (IOException e){
			e.printStackTrace();
		}
		byte[] content = os.toByteArray();
		InputStream is = new ByteArrayInputStream(content);
		return is;
	}

 完整代码参考附件