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());
            }  
    }


}