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) NOTNULL AUTO_INCREMENT,

  `academy`varchar(20) DEFAULT NULL,

  PRIMARYKEY (`academy_id`)

) ENGINE=InnoDB AUTO_INCREMENT=15DEFAULT 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 implementsSerializable{

 

    privateLong academyId;

    privateString 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 {

     

    publicstatic void buildReport(HSSFSheet worksheet,int startRowIndex,

            intstartColIndex){

        //设置列宽

        worksheet.setColumnWidth(0,5200);

        worksheet.setColumnWidth(1,5200);

         

        buildTitle(worksheet,startRowIndex,startColIndex);

        buildHeaders(worksheet,startRowIndex,startColIndex);

    }

 

    privatestatic void buildHeaders(HSSFSheet worksheet, int startRowIndex,

            intstartColIndex) {

        // 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);

 

    }

 

    privatestatic void buildTitle(HSSFSheet worksheet, int startRowIndex,

            intstartColIndex) {

        //报表标题字体

        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(newCellRangeAddress(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 {

 

    publicstatic void fillReport(HSSFSheet worksheet,int startRowIndex,

            intstartColIndex,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 {

 

    privatestatic Logger logger = Logger.getLogger("service"); 

     

    publicstatic 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

    privateJdbcTemplate jdbcTemplate;

     

    //报表的导出

    publicList<Academy> getAcademy() {  

        String sql ="SELECT * FROM t_academy";  

        returnjdbcTemplate.query(sql, newBeanPropertyRowMapper<Academy>(  

                Academy.class));  

   

    }  

   

    //报表的插入

    publicint[] insertAcademy(finalList<Academy> list) {  

        String sql ="INSERT INTO t_academy (academy_id,academy) VALUES(null,?)"; 

        returnjdbcTemplate.batchUpdate(sql, newBatchPreparedStatementSetter() {

             

            @Override

            publicvoid setValues(PreparedStatement ps,int index) throws SQLException {

                Academy a = list.get(index);

                ps.setString(1, a.getAcademy());

            }

             

            @Override

            publicint getBatchSize() {

                returnlist.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

    privatePoiDao poiDao;

    privatestatic Logger logger = Logger.getLogger("service");

 

    publicvoid exportXLSEmpty(HttpServletResponse response) {

 

        // 1.创建一个 workbook

        HSSFWorkbook workbook =new HSSFWorkbook();

 

        // 2.创建一个 worksheet

        HSSFSheet worksheet = workbook.createSheet("Acacemy");

 

        // 3.定义起始行和列

        intstartRowIndex = 0;

        intstartColIndex = 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);

    }

 

     

    //读取数据库并导出报表

    publicvoid exportXLS(HttpServletResponse response) {

 

        // 1.创建一个 workbook

        HSSFWorkbook workbook =new HSSFWorkbook();

 

        // 2.创建一个 worksheet

        HSSFSheet worksheet = workbook.createSheet("Acacemy");

 

        // 3.定义起始行和列

        intstartRowIndex = 0;

        intstartColIndex = 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信息.

     */

    privateList<Academy> getDatasource() {

        returnpoiDao.getAcademy();

    }

    /**

     * 读取报表的数据后批量插入

     */ 

    publicint[] insertAcademy(List<Academy> list) { 

        returnpoiDao.insertAcademy(list); 

   

    }

 

 

    //读取报表并插入数据库中

    publicList<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(intj = 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("没有数据流");

            }

        }

         

        returnacademyList;

 

    }

 

     /** 

     * 把单元格内的类型转换至String类型 

     */

    privateString ConvertCellStr(Cell cell, String cellStr) {

        switch(cell.getCellType()) {  

           

        caseCell.CELL_TYPE_STRING:  

            // 读取String  

            cellStr = cell.getStringCellValue().toString();  

            break;  

   

        caseCell.CELL_TYPE_BOOLEAN:  

            // 得到Boolean对象的方法  

            cellStr = String.valueOf(cell.getBooleanCellValue());  

            break;  

   

        caseCell.CELL_TYPE_NUMERIC:  

   

            // 先看是否是日期格式  

            if(DateUtil.isCellDateFormatted(cell)) {  

   

                // 读取日期格式  

                cellStr = cell.getDateCellValue().toString();  

   

            }else {  

   

                // 读取数字  

                cellStr = String.valueOf(cell.getNumericCellValue());  

            }  

            break;  

   

        caseCell.CELL_TYPE_FORMULA:  

            // 读取公式  

            cellStr = cell.getCellFormula().toString();  

            break;  

        }  

        returncellStr;  

    }

     

    privateAcademy addingAcademy(intj, Academy academy, String cellStr) {

        switch(j){

        case0:

            academy.setAcademyId(null);

        case1:

            academy.setAcademy(cellStr);

        }

        returnacademy;

    }

 

     

}

控制层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 {

 

    privatestatic Logger logger = Logger.getLogger("controller");

 

    @Autowired

    privatePoiService poiService;

 

    @RequestMapping(value ="/report.html")

    publicModelAndView getReport(){

        logger.info("index");

        returnnew ModelAndView("/insmgr/report");

    }

     

    @RequestMapping(value ="/exportEmpty.html", method = RequestMethod.GET)

    publicvoid getXLSEmpty(HttpServletResponse response) {

        poiService.exportXLSEmpty(response);

    }

     

    @RequestMapping(value ="/export.html", method = RequestMethod.GET)

    publicvoid getXLS(HttpServletResponse response) {

        poiService.exportXLS(response);

    }

 

    @RequestMapping(value ="/read.html", method = RequestMethod.POST)

    publicModelAndView getReadReport(@RequestParamMultipartFile file)

            throwsIOException {

        List<Academy> list = poiService.readReport(file.getInputStream());

        poiService.insertAcademy(list);

        returnnew 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"%>

 

 

<!DOCTYPEhtml PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<htmlxmlns="http://www.w3.org/1999/xhtml">

<head>

<metahttp-equiv="Content-Type"content="text/html; charset=UTF-8"/>

<title>Report</title>

</head>

<body>

    <c:urlvar="exportEmpty"value="/insmgr/exportEmpty.html"/>

    <c:urlvar="exportUrl"value="/insmgr/export.html"/>

    <c:urlvar="readUrl"value="/insmgr/read.html"/>

     

    <h3><ahref="${exportEmpty}">导出空表</a> &nbsp;<ahref="${exportUrl}">导出数据</a></h3>

    <br/>

    <form id="readReportForm"action="${readUrl }"method="post"enctype="multipart/form-data" >

        <labelfor="file">File</label>

        <inputid="file"type="file"name="file"/>

        <p><buttontype="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

<?xmlversion="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+"/";

%>

<!DOCTYPEhtml PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<htmlxmlns="http://www.w3.org/1999/xhtml">

<head>

<metahttp-equiv="Content-Type"content="text/html; charset=UTF-8"/>

<title>Insert title here</title>

</head>

<body>

<h1>导入成功!</h1>

 

<ahref="<%=basePath %>/insmgr/report.html">返回</a>

</body>

</html>

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



导出空表

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

导出数据