poi 导入excel spring mvc

通过Apache POI进行Excel的导入（Excel数据->数据库）、导出（数据库->Excel数据）

POI是什么？
Apache POI是Apache软件基金会的开放源码函式库，POI提供API给Java程式对Microsoft Office格式档案读和写的功能。
.NET的开发人员则可以利用NPOI (POI for .NET) 来存取 POI 的功能。

如何使用POI？
可查看 POI官方文档 或 POI操作

导入POI包？
下载POI：http://ishare.iask.sina.com.cn/f/19703335.html?from=like
MyEclipse导入POI：右键项目-Build Path-Add Library-User Library-User Libraries-New-Add Jar-选中以下3个包-OK-Finish


数据库创建一个表Academy并插入数据

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20

CREATE TABLE `t_academy` (    `academy_id` int (11) NOT NULL AUTO_INCREMENT,    `academy` varchar (20) DEFAULT NULL ,    PRIMARY KEY (`academy_id`) ) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=utf8;   INSERT INTO `t_academy` VALUES ( '1' , '经济与管理学院' ); INSERT INTO `t_academy` VALUES ( '2' , '政法学院' ); INSERT INTO `t_academy` VALUES ( '3' , '教育学院' ); INSERT INTO `t_academy` VALUES ( '4' , '体育与健康学院' ); INSERT INTO `t_academy` VALUES ( '5' , '文学院' ); INSERT INTO `t_academy` VALUES ( '6' , '外国语学院' ); INSERT INTO `t_academy` VALUES ( '7' , '音乐学院' ); INSERT INTO `t_academy` VALUES ( '8' , '美术学院' ); INSERT INTO `t_academy` VALUES ( '9' , '数学与信息科学学院' ); INSERT INTO `t_academy` VALUES ( '10' , '化学化工' ); INSERT INTO `t_academy` VALUES ( '11' , '生命科学学院' ); INSERT INTO `t_academy` VALUES ( '12' , '电子信息与机电工程学院' ); INSERT INTO `t_academy` VALUES ( '13' , '计算机学院与软件学院' ); INSERT INTO `t_academy` VALUES ( '14' , '旅游与历史文化学院' );

Spring MVC基本配置
web.xml
spring-servlet.xml
applicationContext.xml

定义一个Academy对象 

?

1 2 3 4 5 6

public class Academy implements Serializable{        private Long academyId;      private String academy;      //getter&setter ｝

使用POI操作Excel
注意：POI里不同的模块代表对不同格式档案的操作
HSSF － 提供读写Microsoft Excel XLS格式档案的功能。
XSSF － 提供读写Microsoft Excel OOXML XLSX格式档案的功能。
HWPF － 提供读写Microsoft Word DOC格式档案的功能。
HSLF － 提供读写Microsoft PowerPoint格式档案的功能。
HDGF － 提供读Microsoft Visio格式档案的功能。
HPBF － 提供读Microsoft Publisher格式档案的功能。
HSMF － 提供读Microsoft Outlook格式档案的功能。

POI包
Layouter.java(对导出的Excel进行自定义格式)

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93

package zqu.recruit.poi;   import java.awt.Color; import java.text.SimpleDateFormat; import java.util.Date; import org.apache.poi.ss.usermodel.Font; import org.apache.poi.hssf.usermodel.HSSFCell; import org.apache.poi.hssf.usermodel.HSSFCellStyle; import org.apache.poi.hssf.usermodel.HSSFRow; import org.apache.poi.hssf.usermodel.HSSFSheet; import org.apache.poi.hssf.util.CellRangeAddress; import org.apache.poi.hssf.util.HSSFColor; import org.apache.poi.ss.usermodel.CellStyle;   @SuppressWarnings ( "deprecation" ) //表示不检测过期的方法 public class Layouter {            public static void buildReport(HSSFSheet worksheet, int startRowIndex,              int startColIndex){          //设置列宽          worksheet.setColumnWidth( 0 , 5200 );          worksheet.setColumnWidth( 1 , 5200 );                    buildTitle(worksheet,startRowIndex,startColIndex);          buildHeaders(worksheet,startRowIndex,startColIndex);      }        private static void buildHeaders(HSSFSheet worksheet, int startRowIndex,              int startColIndex) {          // Header字体            Font font = worksheet.getWorkbook().createFont();            font.setBoldweight(( short )Font.BOLDWEIGHT_BOLD);          //font.setColor(HSSFColor.BLUE.index);//设置字体颜色              // 单元格样式            HSSFCellStyle headerCellStyle = worksheet.getWorkbook()                    .createCellStyle();           //headerCellStyle.setFillForegroundColor(HSSFColor.GREY_80_PERCENT.index);//前景色          //headerCellStyle.setFillBackgroundColor(HSSFColor.GREY_80_PERCENT.index);//背景色          //headerCellStyle.setFillPattern(CellStyle.FINE_DOTS);  //设置填充方式                    headerCellStyle.setAlignment(CellStyle.ALIGN_CENTER);            headerCellStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);            headerCellStyle.setWrapText( true );            headerCellStyle.setFont(font);            headerCellStyle.setBorderBottom(CellStyle.BORDER_THIN);          headerCellStyle.setBorderLeft(CellStyle.BORDER_THIN);          headerCellStyle.setBorderRight(CellStyle.BORDER_THIN);          headerCellStyle.setBorderTop(CellStyle.BORDER_THIN);            // 创建字段标题            HSSFRow rowHeader = worksheet.createRow(( short ) startRowIndex + 2 );            rowHeader.setHeight(( short ) 500 );                     HSSFCell cell1 = rowHeader.createCell(startColIndex + 0 );            cell1.setCellValue( "院系ID" );            cell1.setCellStyle(headerCellStyle);                      HSSFCell cell2 = rowHeader.createCell(startColIndex + 1 );            cell2.setCellValue( "院名" );            cell2.setCellStyle(headerCellStyle);        }        private static void buildTitle(HSSFSheet worksheet, int startRowIndex,              int startColIndex) {          //报表标题字体          Font fontTitle = worksheet.getWorkbook().createFont();          fontTitle.setBoldweight(( short )Font.BOLDWEIGHT_BOLD);          fontTitle.setFontHeight(( short ) 280 );                    //标题单元格格式          HSSFCellStyle cellStyleTitle = worksheet.getWorkbook().createCellStyle();          cellStyleTitle.setAlignment(CellStyle.ALIGN_CENTER);          cellStyleTitle.setWrapText( true );          cellStyleTitle.setFont(fontTitle);                        HSSFRow rowTitle = worksheet.createRow(( short )startRowIndex);          rowTitle.setHeight(( short ) 500 );          HSSFCell cellTitle = rowTitle.createCell(startColIndex);          cellTitle.setCellValue( "院系列表" );          cellTitle.setCellStyle(cellStyleTitle);                    worksheet.addMergedRegion( new CellRangeAddress( 0 , 0 , 0 , 1 )); //标题合并列            Date date = new Date();          SimpleDateFormat dateFormat = new SimpleDateFormat( "yyyy-MM-dd" );          HSSFRow dateTitle = worksheet.createRow(( short ) startRowIndex + 1 );            HSSFCell cellDate = dateTitle.createCell(startColIndex);            cellDate.setCellValue( "这个报表创建于: " + dateFormat.format(date));      } }

FillAcademy.java(填充数据的一个类)

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47

package zqu.recruit.poi;   import java.util.List;   import org.apache.poi.hssf.usermodel.HSSFCell; import org.apache.poi.hssf.usermodel.HSSFCellStyle; import org.apache.poi.hssf.usermodel.HSSFRow; import org.apache.poi.hssf.usermodel.HSSFSheet; import org.apache.poi.ss.usermodel.CellStyle;   import zqu.recruit.domain.Academic; import zqu.recruit.domain.Academy;     public class FillAcademy {        public static void fillReport(HSSFSheet worksheet, int startRowIndex,              int startColIndex,List<Academy> datasource){                    startRowIndex += 2 ;                    // Create cell style for the body            HSSFCellStyle bodyCellStyle = worksheet.getWorkbook().createCellStyle();            bodyCellStyle.setAlignment(CellStyle.ALIGN_CENTER);            bodyCellStyle.setWrapText( false ); //是否自动换行.          bodyCellStyle.setBorderBottom(CellStyle.BORDER_THIN);          bodyCellStyle.setBorderLeft(CellStyle.BORDER_THIN);          bodyCellStyle.setBorderRight(CellStyle.BORDER_THIN);          bodyCellStyle.setBorderTop(CellStyle.BORDER_THIN);                    for ( int i=startRowIndex; i+startRowIndex- 2 < datasource.size()+ 2 ; i++) {                // Create a new row                HSSFRow row = worksheet.createRow(( short ) i+ 1 );                    // Retrieve the id value                HSSFCell cell1 = row.createCell(startColIndex+ 0 );                cell1.setCellValue(datasource.get(i- 2 ).getAcademyId());                cell1.setCellStyle(bodyCellStyle);                    // Retrieve the brand value                HSSFCell cell2 = row.createCell(startColIndex+ 1 );                cell2.setCellValue(datasource.get(i- 2 ).getAcademy());                cell2.setCellStyle(bodyCellStyle);            }        } }

Writer.java(报表写入类) 

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29

package zqu.recruit.poi;   import javax.servlet.ServletOutputStream; import javax.servlet.http.HttpServletResponse;   import org.apache.log4j.Logger; import org.apache.poi.hssf.usermodel.HSSFSheet;   public class Writer {        private static Logger logger = Logger.getLogger( "service" );             public static void write(HttpServletResponse response, HSSFSheet worksheet) {                        logger.debug( "Writing report to the stream" );            try {                // Retrieve the output stream                ServletOutputStream outputStream = response.getOutputStream();                // Write to the output stream                worksheet.getWorkbook().write(outputStream);                // 清除缓存                outputStream.flush();                } catch (Exception e) {                logger.error( "报表输入失败!" );            }        }    }

三层架构
持久层DAO-PoiDao.java
getAcademy:查询所有Academy对象并装入一个list类（导出）
insertAcademy：批量插入Academy（导入） 

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47

package zqu.recruit.dao;   import java.sql.PreparedStatement; import java.sql.SQLException; import java.util.List;   import org.springframework.beans.factory.annotation.Autowired; import org.springframework.jdbc.core.BatchPreparedStatementSetter; import org.springframework.jdbc.core.BeanPropertyRowMapper; import org.springframework.jdbc.core.JdbcTemplate; import org.springframework.stereotype.Repository;   import zqu.recruit.domain.Academy;   @Repository public class PoiDao {        @Autowired //自动注入JdbcTemplate的bean      private JdbcTemplate jdbcTemplate;            //报表的导出      public List<Academy> getAcademy() {            String sql = "SELECT * FROM t_academy" ;            return jdbcTemplate.query(sql, new BeanPropertyRowMapper<Academy>(                    Academy. class ));            }            //报表的插入      public int [] insertAcademy( final List<Academy> list) {            String sql = "INSERT INTO t_academy (academy_id,academy) VALUES(null,?)" ;           return jdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter() {                            @Override              public void setValues(PreparedStatement ps, int index) throws SQLException {                  Academy a = list.get(index);                  ps.setString( 1 , a.getAcademy());              }                            @Override              public int getBatchSize() {                  return list.size();              }          });      }    }

业务层Service-PoiService
exportXLSEmpty:导出一个空表
exportXLS：读取数据库并导出Excel表
readReport：读取报表并插入数据库 

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211

package zqu.recruit.service;   import java.io.IOException; import java.io.InputStream; import java.util.ArrayList; import java.util.List;   import javax.servlet.http.HttpServletResponse;   import org.apache.log4j.Logger; import org.apache.poi.hssf.usermodel.HSSFSheet; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.openxml4j.exceptions.InvalidFormatException; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.DateUtil; 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.beans.factory.annotation.Autowired; import org.springframework.stereotype.Service; import org.springframework.transaction.annotation.Transactional;   import zqu.recruit.dao.PoiDao; import zqu.recruit.domain.Academy; import zqu.recruit.poi.FillAcademy; import zqu.recruit.poi.Layouter; import zqu.recruit.poi.Writer;   @Service @Transactional public class PoiService {        @Autowired      private PoiDao poiDao;      private static Logger logger = Logger.getLogger( "service" );        public void exportXLSEmpty(HttpServletResponse response) {            // 1.创建一个 workbook          HSSFWorkbook workbook = new HSSFWorkbook();            // 2.创建一个 worksheet          HSSFSheet worksheet = workbook.createSheet( "Acacemy" );            // 3.定义起始行和列          int startRowIndex = 0 ;          int startColIndex = 0 ;            // 4.创建title,data,headers          Layouter.buildReport(worksheet, startRowIndex, startColIndex);            // 5.填充数据          //FillAcademy.fillReport(worksheet, startRowIndex, startColIndex,          //        getDatasource());            // 6.设置reponse参数          String fileName = "AcademyReport.xls" ;          response.setHeader( "Content-Disposition" , "inline; filename="                  + fileName);          // 确保发送的当前文本格式          response.setContentType( "application/vnd.ms-excel" );            // 7. 输出流          Writer.write(response, worksheet);      }              //读取数据库并导出报表      public void exportXLS(HttpServletResponse response) {            // 1.创建一个 workbook          HSSFWorkbook workbook = new HSSFWorkbook();            // 2.创建一个 worksheet          HSSFSheet worksheet = workbook.createSheet( "Acacemy" );            // 3.定义起始行和列          int startRowIndex = 0 ;          int startColIndex = 0 ;            // 4.创建title,data,headers          Layouter.buildReport(worksheet, startRowIndex, startColIndex);           // 5.填充数据          FillAcademy.fillReport(worksheet, startRowIndex, startColIndex,                  getDatasource());            // 6.设置reponse参数          String fileName = "AcademyReport.xls" ;          response.setHeader( "Content-Disposition" , "inline; filename="                  + fileName);          // 确保发送的当前文本格式          response.setContentType( "application/vnd.ms-excel" );            // 7. 输出流          Writer.write(response, worksheet);      }        /**       * 从数据库获得所有的Academy信息.       */      private List<Academy> getDatasource() {          return poiDao.getAcademy();      }      /**       * 读取报表的数据后批量插入       */       public int [] insertAcademy(List<Academy> list) {           return poiDao.insertAcademy(list);           }          //读取报表并插入数据库中      public List<Academy> readReport(InputStream inp) {            List<Academy> academyList = new ArrayList<Academy>();            try {              String cellStr = null ;              Workbook wb = WorkbookFactory.create(inp);              Sheet sheet = wb.getSheetAt( 0 );                for ( int i = 3 ; i <= sheet.getLastRowNum(); i++) {                  Academy academy = new Academy();                  Academy addAcademy = new Academy();                  Row row = sheet.getRow(i);                  if (row == null ){                      continue ;                  }                  for ( int j = 0 ; j < row.getLastCellNum(); j++){                      Cell cell = row.getCell(j);                      cellStr = ConvertCellStr(cell,cellStr);                      addAcademy = addingAcademy(j,academy,cellStr);                  }                  academyList.add(addAcademy);                                }          } catch (InvalidFormatException e) {              e.printStackTrace();          } catch (IOException e) {              e.printStackTrace();          } finally {              if (inp != null ){                  try {                      inp.close();                  } catch (IOException e) {                      e.printStackTrace();                  }              }              else {                  logger.info( "没有数据流" );              }          }                    return academyList;        }         /**        * 把单元格内的类型转换至String类型        */      private String ConvertCellStr(Cell cell, String cellStr) {          switch (cell.getCellType()) {                        case Cell.CELL_TYPE_STRING:                // 读取String                cellStr = cell.getStringCellValue().toString();                break ;                case Cell.CELL_TYPE_BOOLEAN:                // 得到Boolean对象的方法                cellStr = String.valueOf(cell.getBooleanCellValue());                break ;                case Cell.CELL_TYPE_NUMERIC:                    // 先看是否是日期格式                if (DateUtil.isCellDateFormatted(cell)) {                        // 读取日期格式                    cellStr = cell.getDateCellValue().toString();                    } else {                        // 读取数字                    cellStr = String.valueOf(cell.getNumericCellValue());                }                break ;                case Cell.CELL_TYPE_FORMULA:                // 读取公式                cellStr = cell.getCellFormula().toString();                break ;            }            return cellStr;        }            private Academy addingAcademy( int j, Academy academy, String cellStr) {          switch (j){          case 0 :              academy.setAcademyId( null );          case 1 :              academy.setAcademy(cellStr);          }          return academy;      }         }

控制层Controller-PoiController.java 

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54

package zqu.recruit.web;   import java.io.IOException; import java.util.List;   import javax.servlet.http.HttpServletResponse;   import org.apache.log4j.Logger; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.stereotype.Controller; import org.springframework.web.bind.annotation.RequestMapping; import org.springframework.web.bind.annotation.RequestMethod; import org.springframework.web.bind.annotation.RequestParam; import org.springframework.web.multipart.MultipartFile; import org.springframework.web.servlet.ModelAndView;   import zqu.recruit.domain.Academy; import zqu.recruit.service.PoiService;   @Controller @RequestMapping ( "/insmgr" ) public class PoiController {        private static Logger logger = Logger.getLogger( "controller" );        @Autowired      private PoiService poiService;        @RequestMapping (value = "/report.html" )      public ModelAndView getReport(){          logger.info( "index" );          return new ModelAndView( "/insmgr/report" );      }            @RequestMapping (value = "/exportEmpty.html" , method = RequestMethod.GET)      public void getXLSEmpty(HttpServletResponse response) {          poiService.exportXLSEmpty(response);      }            @RequestMapping (value = "/export.html" , method = RequestMethod.GET)      public void getXLS(HttpServletResponse response) {          poiService.exportXLS(response);      }        @RequestMapping (value = "/read.html" , method = RequestMethod.POST)      public ModelAndView getReadReport( @RequestParam MultipartFile file)              throws IOException {          List<Academy> list = poiService.readReport(file.getInputStream());          poiService.insertAcademy(list);          return new ModelAndView( "/insmgr/addedReport" );        }   }

2个JSP页面
report.jsp

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28

<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c" %> <%@ taglib uri="http://www.springframework.org/tags" prefix="spring" %> <%@ taglib uri="http://www.springframework.org/tags/form" prefix="form" %> <%@ page language="java" contentType="text/html; charset=UTF-8"      pageEncoding="UTF-8"%>     <! DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> < html xmlns = "http://www.w3.org/1999/xhtml" > < head > < meta http-equiv = "Content-Type" content = "text/html; charset=UTF-8" /> < title >Report</ title > </ head > < body >      < c:url var = "exportEmpty" value = "/insmgr/exportEmpty.html" />      < c:url var = "exportUrl" value = "/insmgr/export.html" />      < c:url var = "readUrl" value = "/insmgr/read.html" />            < h3 >< a href = "${exportEmpty}" >导出空表</ a > &nbsp;< a href = "${exportUrl}" >导出数据</ a ></ h3 >      < br />      < form  id = "readReportForm" action = "${readUrl }" method = "post" enctype = "multipart/form-data"  >          < label for = "file" >File</ label >          < input id = "file" type = "file" name = "file" />          < p >< button type = "submit" >导入数据</ button ></ p >         </ form >       </ body > </ html >

addedReport.jsp 

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19

<? xml version = "1.0" encoding = "UTF-8" ?> <%@ page language="java" contentType="text/html; charset=UTF-8"      pageEncoding="UTF-8"%> <% String path = request.getContextPath(); String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/"; %> <! DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> < html xmlns = "http://www.w3.org/1999/xhtml" > < head > < meta http-equiv = "Content-Type" content = "text/html; charset=UTF-8" /> < title >Insert title here</ title > </ head > < body > < h1 >导入成功!</ h1 >   < a href="<%=basePath %>/insmgr/report.html">返回</ a > </ body > </ html >

启动项目后输入：
http://localhost:8080/Recruit20121004/insmgr/report.html



导出空表

导入数据
填写空表保存后浏览导入即可。

导出数据