java通过poi对Excel数据在(jsp+ssh)环境下导入导出

POI的下载与安装

　　请到网站http://www.apache.org/dyn/closer.cgi/poi/右击超链接2.5.1.zip下载压缩包poi-bin-2.5.1-final-20040804.zip（或下载最新的这种压缩包），解压缩后得到如图2.1所示的目录结构。我们主要用到poi-2.5.1-final-20040804.jar这个库文件。请把poi-2.5.1-final-20040804.jar这个文件的路径添加到系统环境变量classpath中，否则无法编译下面的示例程序。
1. 首先搭建ssh框架（这儿不做介绍）；
2. 导入jar包（如下）:

3. 导出：（下面以一个例子的形式）
1.  新创建一个jsp页面如（export.jsp）,在页面上添加如下代码：
<a href="<%=path %>/indexAction!export.action">导出数据到excel</a>
2.  进入indexAction.java文件，编写export方法由于要用到一个STUDENT类，就先编写STUDENT类，Student类代码如下：
public class Student {
private String studentId;
private String studentName;
private String studentSex;
private String studentDormitory;
private  String studentSept;
public String getStudentId() {
return studentId;
}
public void setStudentId(String studentId) {
this.studentId = studentId;
}
public String getStudentName() {
return studentName;
}
public void setStudentName(String studentName) {
this.studentName = studentName;
}
public String getStudentSex() {
return studentSex;
}
public void setStudentSex(String studentSex) {
this.studentSex = studentSex;
}
public String getStudentDormitory() {
return studentDormitory;
}
public void setStudentDormitory(String studentDormitory) {
this.studentDormitory = studentDormitory;
}
public String getStudentSept() {
return studentSept;
}
public void setStudentSept(String studentSept) {
this.studentSept = studentSept;
}

}
编写export方法：代码如下

此类中需要导入的jar包如下：
import java.io.IOException;
import java.io.OutputStream;
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.struts2.ServletActionContext;

    public String export()throws Exception
    { 
      
      
        List studentList=new ArrayList<Student>();//学生LIst
      
        for(int i=0;i<10;i++)
        {   Student student=new Student();//学生对象
            student.setStudentId("200908110"+i);
            student.setStudentName("杨波"+i);
            student.setStudentSex("男");
            student.setStudentDormitory("14-20"+i);
            student.setStudentSept("软件工程系");
            studentList.add(student);
        }
            
        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);
        HSSFCellStyle style1 = workbook.createCellStyle();       //设置数据类型
        style1.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        HSSFFont font = workbook.createFont();                 //设置字体
        HSSFSheet sheet = workbook.createSheet("sheet1");        //创建一个sheet
        HSSFHeader header = sheet.getHeader();//设置sheet的头
try {             
            if(studentList.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);
                }
                       
                for(int i = 0 ;i < studentList.size() ;i++){                           
                    Student student1 = (Student)studentList.get(i);//获取student对象
                    row = sheet.createRow((short) (i + 1));//创建第i+1行
                    row.setHeight((short)400);//设置行高
                  
                    if(student1.getStudentId() != null){
                         cell = row.createCell(0);//创建第i+1行第0列
                         cell.setCellValue(student1.getStudentId());//设置第i+1行第0列的值
                cell.setCellStyle(style);//设置风格
                    }
                    if(student1.getStudentName() != null){
                         cell = row.createCell(1); //创建第i+1行第1列

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

                         cell.setCellStyle(style); //设置风格
                    }
//由于下面的和上面的基本相同，就不加注释了
                    if(student1.getStudentSex() != null){
                         cell = row.createCell(2);
                         cell.setCellValue(student1.getStudentSex());
                         cell.setCellStyle(style);
                    }
                    if(student1.getStudentDormitory()!= null){
                         cell = row.createCell(3);
                         cell.setCellValue(student1.getStudentDormitory());
                         cell.setCellStyle(style);
                    }
                    if(student1.getStudentSept() != null){
                         cell = row.createCell(4);
                         cell.setCellValue(student1.getStudentSept());
                         cell.setCellStyle(style);
                    }
                  
                }
              
            }
          
        } catch (Exception e) {
            e.printStackTrace();
        }
      
 
        HttpServletResponse response = null;//创建一个HttpServletResponse对象
        OutputStream out = null;//创建一个输出流对象
        try {
            response = ServletActionContext.getResponse();//初始化HttpServletResponse对象
            out = response.getOutputStream();//
         
    StringheaderStr ="student学生";
    headerStr =new String(headerStr.getBytes("gb2312"), "ISO8859-1");//headerString为中文时转码
    response.setHeader("Content-disposition","attachment; filename="+    headerStr+".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();
            workbook.write(out);
        } catch (IOException e) {
            e.printStackTrace();
        }finally{
            try{
              
                if(out!=null){
                    out.close();
                }
              
            }catch(IOException e){
                e.printStackTrace();
            }
          
        }
      
        return null;
   }
注：最好返回null,否则有可能报错。

====================================================================================================
4.数据的导入（以例子的形式展示）
1.jsp页面的编写：在页面上添加如下代码
其中action可以自己编写，table标签的内容可以不要。（DEMO页面添加TABLE标签主要是展示导入效果）
<s:form action="indexAction!importExcel.action" method="post" enctype="multipart/form-data" theme="simple">

<td>
<s:file name="excelFile" id="excelFile" cssStyle="width:160px"></s:file>//选择导入的文件
</td>
<td>
<input type="submit" value="导入学生数据"/>
</td>
</s:form>
//下面是展示导入效果
<table>
<th>学号</th><th>姓名</th><th>性别</th><th>寝室号</th><th>所在系</th>
<s:iterator value="stuList">
<tr>
<td>
<s:property value="studentId"/>
</td>
<td>
<s:property value="studentName"/>
</td>
<td>
<s:property value="studentSex"/>
</td>
<td>
<s:property value="studentDormitory"/>
</td>
<td>
<s:property value="studentSept"/>
</td>
</tr>
</s:iterator>
</table>
2.strus.xml编写（如果要展示效果，DEMo就编写的返回的页面如下）
   <action name="indexAction" class="com.dev.iex.action.IndexAction">
            <result name="SUCCESS">/index.jsp</result>
   </action>
3. java代码如下
在indexAction中添加
     Import的类：
       import java.io.File;

import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
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.Workbook;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.struts2.ServletActionContext;
import com.dev.iex.po.Student;
  定义的的变量：
private File excelFile;//File对象，目的是获取页面上传的文件
private  List<Student> stuList=new ArrayList<Student>();
定义的方法：
public File getExcelFile() {
    return excelFile;
}
public void setExcelFile(File excelFile) {
    this.excelFile = excelFile;
}
public List<Student> getStuList() {
    return stuList;
}
public void setStuList(List<Student> stuList) {
    this.stuList = stuList;
}

主要编写的是importExcel方法，如下：
    @SuppressWarnings("finally")
public String importExcel()throws Exception
{       
   
    String id=null;
    String name=null;
    String  sex=null;
    String  Dormitory=null;
    String Sept=null;
    
    Workbook workbook = null;
    int k=0;
    int flag = 0;   //指示指针所访问的位置
    if(excelFile!=null)
    {
        String path=excelFile.getAbsolutePath();//获取文件的路径
         try {
            workbook = new XSSFWorkbook(path);//初始化workbook对象
            for (int numSheets = 0; numSheets < workbook.getNumberOfSheets(); numSheets++) {                                                //读取每一个sheet
                 System.out.println("2007版进入读取sheet的循环");
                    if (null != workbook.getSheetAt(numSheets)) {  
                        XSSFSheet aSheet = (XSSFSheet)workbook.getSheetAt(numSheets);//定义Sheet对象
                        for (int rowNumOfSheet = 0; rowNumOfSheet <= aSheet.getLastRowNum(); rowNumOfSheet++) {
                           //进入当前sheet的行的循环 
                            if (null != aSheet.getRow(rowNumOfSheet)) {
                                XSSFRow  aRow = aSheet.getRow(rowNumOfSheet); //定义行，并赋值
                                for (int cellNumOfRow = 0; cellNumOfRow <= aRow.getLastCellNum(); cellNumOfRow++)
                                {                          //读取rowNumOfSheet值所对应行的数据
                                   XSSFCell  xCell = aRow.getCell(cellNumOfRow); //获得行的列数                                                           //获得列值 
                                   //System.out.println("type="+xCell.getCellType());
                                   if (null != aRow.getCell(cellNumOfRow))
                                   {
                                     
                                        if(rowNumOfSheet == 0)
                                        {                                                                                   // 如果rowNumOfSheet的值为0，则读取表头，判断excel的格式和预定格式是否相符            
                                               if(xCell.getCellType() == XSSFCell .CELL_TYPE_NUMERIC)
                                               {
                                              
                                             }else if(xCell.getCellType() == XSSFCell .CELL_TYPE_BOOLEAN)
                                             {
                                          
                                             }else if(xCell.getCellType() == XSSFCell .CELL_TYPE_STRING)
                                             {
                                            if(cellNumOfRow == 0)
                                            { 
                                                 if(xCell.getStringCellValue().replace('\t', ' ').replace('\n', ' ').replace('\r', ' ').trim().equals("学号"))
                                                 {
                                                    flag++;
                                                 }else{
                                                   System.out.println("错误：第一行的学号不符合约定格式");
                                                 }
                                            }else if(cellNumOfRow == 1)
                                            {
                                                 if(xCell.getStringCellValue().replace('\t', ' ').replace('\n', ' ').replace('\r', ' ').trim().equals("姓名"))
                                                 {
                                                     flag++;
                                                 }else{
                                                     System.out.println("错误：第一行的姓名不符合约定格式");
                                                 }              
                                             }else if(cellNumOfRow == 2)
                                             {
                                                 if(xCell.getStringCellValue().replace('\t', ' ').replace('\n', ' ').replace('\r', ' ').trim().equals("性别")){
                                                     flag++;
                                                   
                                                 }else{
                                                 System.out.println("第一行的性别不符合约定格式");
        }
                                               
                                             }else if (cellNumOfRow == 3) {
                                                 if(xCell.getStringCellValue().replace('\t', ' ').replace('\n', ' ').replace('\r', ' ').trim().equals("寝室号"))
                                                 {
                                                     flag++;
                                                     System.out.println("=========flag:" + flag);
                                                 }else{
                                                 System.out.println("第一行的寝室号不符合约定格式");
        }
                                               
                                             }else if (cellNumOfRow == 4)
        {
                                                 if(xCell.getStringCellValue().replace('\t', ' ').replace('\n', ' ').replace('\r', ' ').trim().equals("所在系")){
                                                     flag++;
                                                     System.out.println("=========flag:" + flag);
                                                  }else{
                                                 System.out.println("第一行的所在系不符合约定格式");
        }
        }
      }
    }
    else {
                                      
    //rowNumOfSheet != 0 即开始打印内容

                            if(id != null && name != null && sex != null && Dormitory != null && Sept != null ){
                               Student stu=new Student();
                               stu.setStudentId(id);
                               stu.setStudentName(name);
                               stu.setStudentSept(Sept);
                               stu.setStudentSex(sex);
                               stu.setStudentDormitory(Dormitory);
                               stuList.add(stu);
                                k++;
                            }
             } //获得一行，即读取每一行 
            } 
                //读取每一个sheet
          
             }
            }catch (Exception e) {
                    /********************************************                         下面使用的是2003除了workbook的赋值不同其它与2007基本相同，就不作介绍了
                     *********************************************
            InputStream is = new FileInputStream(path);      
            workbook = new HSSFWorkbook(is);
            try {
                 for (int numSheets = 0; numSheets < workbook.getNumberOfSheets(); numSheets++) {                                                      //读取每一个sheet
                      System.out.println("2003版进入读取sheet的循环");
                    if (null != workbook.getSheetAt(numSheets)) {  
                        HSSFSheet aSheet = (HSSFSheet)workbook.getSheetAt(numSheets);
                        for (int rowNumOfSheet = 0; rowNumOfSheet <= aSheet.getLastRowNum(); rowNumOfSheet++) {                                    //获得一行 
                        
                            if (null != aSheet.getRow(rowNumOfSheet)) {
                                HSSFRow  aRow = aSheet.getRow(rowNumOfSheet);
                                for (int cellNumOfRow = 0; cellNumOfRow <= aRow.getLastCellNum(); cellNumOfRow++) {                                 //读取rowNumOfSheet值所对应行的数据
                                   HSSFCell  aCell = aRow.getCell(cellNumOfRow);                                                                 //获得列值 
                                 
                                   if (null != aRow.getCell(cellNumOfRow)){
                                        if(rowNumOfSheet == 0){                                                                                      // 如果rowNumOfSheet的值为0，则读取表头，判断excel的格式和预定格式是否相符            
                                            if(aCell.getCellType() == HSSFCell .CELL_TYPE_NUMERIC){
                                             }else if(aCell.getCellType() == HSSFCell .CELL_TYPE_BOOLEAN){
                                             }else if(aCell.getCellType() == HSSFCell .CELL_TYPE_STRING){
                                              if(cellNumOfRow == 0){ 
                                                     if(aCell.getStringCellValue().replace('\t', ' ').replace('\n', ' ').replace('\r', ' ').trim().equals("学号")){
                                                        flag++;
                                                        System.out.println("=========flag:" + flag);
                                                     }else{
                                                       System.out.println("错误：第一行的学号不符合约定格式");
                                                     }
                                                }else if(cellNumOfRow == 1){
                                                     if(aCell.getStringCellValue().replace('\t', ' ').replace('\n', ' ').replace('\r', ' ').trim().equals("姓名")){
                                                         flag++;
                                                         System.out.println("=========flag:" + flag);
                                                     }else{
                                                         System.out.println("错误：第一行的姓名不符合约定格式");
                                                     }              
                                                 }else if(cellNumOfRow == 2){
                                                     if(aCell.getStringCellValue().replace('\t', ' ').replace('\n', ' ').replace('\r', ' ').trim().equals("性别")){
                                                         flag++;
                                                         System.out.println("=========flag:" + flag);
                                                     }else{
                                                     System.out.println("第一行的性别不符合约定格式");
                                                     }
                                                   
                                                 }else if (cellNumOfRow == 3){
                                                     if(aCell.getStringCellValue().replace('\t', ' ').replace('\n', ' ').replace('\r', ' ').trim().equals("寝室号")){
                                                         flag++;
                                                         System.out.println("=========flag:" + flag);
                                                     }else{
                                                     System.out.println("第一行的寝室号不符合约定格式");
                                                     }
                                                   
                                                 }else if (cellNumOfRow == 4){
                                                     if(aCell.getStringCellValue().replace('\t', ' ').replace('\n', ' ').replace('\r', ' ').trim().equals("所在系")){
                                                         flag++;
                                                         System.out.println("=========flag:" + flag);
                                                     }else{
                                                     System.out.println("第一行的所在系不符合约定格式");
                                                     }
                                                 }
                                             }
                                        }
                                        else {
                                            if(aCell.getCellType() == HSSFCell .CELL_TYPE_NUMERIC){                                                                         //为数值型
                                                System.out.println("======进入XSSFCell .CELL_TYPE_NUMERIC模块==========");
                                                if(cellNumOfRow == 0){
                                                        id = String.valueOf(aCell.getStringCellValue().replace('\t', ' ').replace('\n', ' ').replace('\r', ' ').trim());
                                                        if(id == null){
                                                            System.out.println("错误：在Sheet"+(numSheets+1)+"中的第"+(rowNumOfSheet+1)+"行的第"+(cellNumOfRow+1)+"列的学号不能为空");
                                                        }
                                                    }else if(cellNumOfRow == 1){                                                                                          
                                                        name = String.valueOf(aCell.getStringCellValue().replace('\t', ' ').replace('\n', ' ').replace('\r', ' ').trim());
                                                        if(name == null){
                                                            System.out.println("错误：在Sheet"+(numSheets+1)+"中的第"+(rowNumOfSheet+1)+"行的第"+(cellNumOfRow+1)+"列的姓名不能为空");
                                                        }
                                                    }else if(cellNumOfRow == 2){                                                                                          
                                                        sex = String.valueOf(aCell.getStringCellValue().replace('\t', ' ').replace('\n', ' ').replace('\r', ' ').trim());
                                                        if(sex == null){
                                                            System.out.println("错误：在Sheet"+(numSheets+1)+"中的第"+(rowNumOfSheet+1)+"行的第"+(cellNumOfRow+1)+"列的性别不能为空");
                                                        }                                             
                                                    }else if (cellNumOfRow == 3){                                                                                                                                                   Dormitory = String.valueOf(aCell.getStringCellValue().replace('\t', ' ').replace('\n', ' ').replace('\r', ' ').trim());
                                                        if(Dormitory == null){
                                                         System.out.println("错误：在Sheet"+(numSheets+1)+"中的第"+(rowNumOfSheet+1)+"行的第"+(cellNumOfRow+1)+"列的寝室号不能为空");
                                                        }
                                                    }else if (cellNumOfRow == 4){                                                                                                                                                   Sept = String.valueOf(aCell.getStringCellValue().replace('\t', ' ').replace('\n', ' ').replace('\r', ' ').trim());
                                                        if(Sept == null){                     
                                                            System.out.println("错误：在Sheet"+(numSheets+1)+"中的第"+(rowNumOfSheet+1)+"行的第"+(cellNumOfRow+1)+"列的所在系不能为空");
                                                        }
                                                  
                                                    }                                         
                                            }else if(aCell.getCellType() == HSSFCell .CELL_TYPE_STRING){                                                                      //为字符串型
                                                System.out.print("===============进入XSSFCell .CELL_TYPE_STRING模块============");
                                                if(cellNumOfRow == 0){
                                                        id = aCell.getStringCellValue().replace('\t', ' ').replace('\n', ' ').replace('\r', ' ').trim();
                                                        if(id == null){
                                                            System.out.println("错误：在Sheet"+(numSheets+1)+"中的第"+(rowNumOfSheet+1)+"行的第"+(cellNumOfRow+1)+"列的学号不能为空");
                                                        }
                                                    }else if(cellNumOfRow == 1){                                                                                          
                                                        name = aCell.getStringCellValue().replace('\t', ' ').replace('\n', ' ').replace('\r', ' ').trim();
                                                        if(name == null){
                                                            System.out.println("错误：在Sheet"+(numSheets+1)+"中的第"+(rowNumOfSheet+1)+"行的第"+(cellNumOfRow+1)+"列的姓名不能为空");
                                                        }
                                                    }else if(cellNumOfRow == 2){                                                                                          
                                                        sex = aCell.getStringCellValue().replace('\t', ' ').replace('\n', ' ').replace('\r', ' ').trim();
                                                        if(sex == null){
                                                            System.out.println("错误：在Sheet"+(numSheets+1)+"中的第"+(rowNumOfSheet+1)+"行的第"+(cellNumOfRow+1)+"列的性别不能为空");
                                                        }                                             
                                                    }else if (cellNumOfRow == 3){                                                                                         
                                                        Dormitory =aCell.getStringCellValue().replace('\t', ' ').replace('\n', ' ').replace('\r', ' ').trim();
                                                        if(Dormitory == null){
                                                         System.out.println("错误：在Sheet"+(numSheets+1)+"中的第"+(rowNumOfSheet+1)+"行的第"+(cellNumOfRow+1)+"列的寝室号不能为空");
                                                        }
                                                    }else if (cellNumOfRow == 4){                                                                                                                                                   Sept =aCell.getStringCellValue().replace('\t', ' ').replace('\n', ' ').replace('\r', ' ').trim();
                                                        if(Sept == null){                    
                                                            System.out.println("错误：在Sheet"+(numSheets+1)+"中的第"+(rowNumOfSheet+1)+"行的第"+(cellNumOfRow+1)+"列的所在系不能为空");
                                                        }
                                                    }    
                                              
                                             }else if (aCell.getCellType() == HSSFCell .CELL_TYPE_BLANK) {
                                              System.out.println("提示：在Sheet"+(numSheets+1)+"中的第"+(rowNumOfSheet+1)+"行的第"+(cellNumOfRow+1)+"列的值为空，请查看核对是否符合约定要求".toString());
                                             }
                                        }                                                                       
                                    }                                
                                }
                               
                                if (flag!=5){
                                   System.out.println("请核对后重试");
                                  
                                    }
                            }
               
                            if(id != null && name != null && sex != null && Dormitory != null && Sept != null ){
                                   Student stu=new Student();
                                   stu.setStudentId(id);
                                   stu.setStudentName(name);
                                   stu.setStudentSept(Sept);
                                   stu.setStudentSex(sex);
                                   stu.setStudentDormitory(Dormitory);
                                   stuList.add(stu);
                                    k++;
                                }
                           
                        }
                        if(k!=0){
                          System.out.println("提示：您导入的数据已存在于数据库，请核对！k 为：" + k);
                          }else{
                              System.out.println("提示：成功导入了"+k+"条数据");
                          }
                    }  
                }
          
              
                } catch (Exception ex) {
                    ex.printStackTrace();
                }finally{
                    try {
                        if(is!=null)
                            is.close();
                    }catch (Exception e1) {
                            e1.printStackTrace();
                    }
                }
            }
    }
    return "SUCCESS";

  
}

评论

1 楼 18335864773 2017-06-05  

偶然间看到一篇文章。JAVA 无需JXL和POI生成excel。可以用PageOffice自动生成Excel表格。把网址分享给大家。http://6949400.blog.51cto.com/6939400/1243568