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";

 

   

}

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值