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