SpringMvc+POI处理excel表数据导入

SpringMvc+POI处理excel表数据导入

一.概念介绍

ApachePOI是Apache软件基金会的开放源码函式库，POI提供API给java程序对Microsoft Office格式档案读和写的功能

二.功能相关代码

1.环境说明：JDK1.7+tomcat7+spring

2.配置文件的配置

pom文件中添加POI所需依赖

org.apache.poi

poi

3.13

org.apache.poi

poi-ooxml

3.13

spring-mvc.xml配置文件上传

class="org.springframework.web.multipart.commons.CommonsMultipartResolver">

class="org.springframework.web.multipart.commons.CommonsMultipartResolver">

3.相关工具类及代码编写

Excel解析工具类（ImportExcelUtil.java）

package com.jointem.hrm.utils;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import org.apache.poi.ss.usermodel.Cell;

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.util.CellRangeAddress;

import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.IOException;

import java.io.InputStream;

import java.text.DecimalFormat;

import java.text.SimpleDateFormat;

import java.util.ArrayList;

import java.util.List;

/**

* Created by jenking on 2017/9/8.

*/

public class ImportExcelUtil {

private final static String excel2003L =".xls"; //2003- 版本的excel

private final static String excel2007U =".xlsx"; //2007+ 版本的excel

/**

* 描述：获取IO流中的数据，组装成List>对象

* @param in,fileName

* @return

* @throws IOException

*/

public List> getBankListByExcel(InputStream in,String fileName) throws Exception{

List> list = null;

//创建Excel工作薄

Workbook work = this.getWorkbook(in,fileName);

if(null == work){

throw new Exception("创建Excel工作薄为空！");

}

Sheet sheet = null;

Row row = null;

Cell cell = null;

list = new ArrayList>();

//遍历Excel中所有的sheet

for (int i = 0; i < work.getNumberOfSheets(); i++) {

sheet = work.getSheetAt(i);

if(sheet==null){continue;}

//遍历当前sheet中的所有行

System.out.println(sheet.getLastRowNum());

for (int j = sheet.getFirstRowNum(); j <=sheet.getLastRowNum()-11; j++)

{

row = sheet.getRow(j);

// if(row==null||row.getFirstCellNum()==j)

// {

// continue;

// }

//遍历所有的列

List

for (int y = row.getFirstCellNum(); y < row.getLastCellNum(); y++)

{

cell = row.getCell(y);

if(this.isMergedRegion(sheet,j,y))

{

li.add(this.getMergedRegionValue(sheet,j,y));

}

else

{

li.add(this.getCellValue(cell));

}

}

list.add(li);

}

}

work.close();

return list;

}

/**

* 描述：根据文件后缀，自适应上传文件的版本

* @param inStr,fileName

* @return

* @throws Exception

*/

public Workbook getWorkbook(InputStream inStr,String fileName) throws Exception{

Workbook wb = null;

String fileType = fileName.substring(fileName.lastIndexOf("."));

if(excel2003L.equals(fileType)){

wb = new HSSFWorkbook(inStr); //2003-

}else if(excel2007U.equals(fileType)){

wb = new XSSFWorkbook(inStr); //2007+

}else{

throw new Exception("解析的文件格式有误！");

}

return wb;

}

/**

* 描述：对表格中数值进行格式化

* @param cell

* @return

*/

public Object getCellValue(Cell cell){

Object value = null;

DecimalFormat df = new DecimalFormat("0"); //格式化number String字符

SimpleDateFormat sdf = new SimpleDateFormat("yyy-MM-dd"); //日期格式化

DecimalFormat df2 = new DecimalFormat("0"); //格式化数字

switch (cell.getCellType()) {

case Cell.CELL_TYPE_STRING:

value = cell.getRichStringCellValue().getString();

break;

case Cell.CELL_TYPE_NUMERIC:

if("General".equals(cell.getCellStyle().getDataFormatString())){

value = df.format(cell.getNumericCellValue());

}else if("m/d/yy".equals(cell.getCellStyle().getDataFormatString())){

value = sdf.format(cell.getDateCellValue());

}else{

value = df2.format(cell.getNumericCellValue());

}

break;

case Cell.CELL_TYPE_BOOLEAN:

value = cell.getBooleanCellValue();

break;

case Cell.CELL_TYPE_BLANK:

value = "";

break;

default:

break;

}

return value;

}

/**

* 获取合并单元格的内容

* @param sheet

* @param row

* @param column

* @return

*/

public Object getMergedRegionValue(Sheet sheet, int row, int column)

{

int sheetMergeCount = sheet.getNumMergedRegions();

for (int i = 0; i < sheetMergeCount; i++)

{

CellRangeAddress ca = sheet.getMergedRegion(i);

int firstColumn = ca.getFirstColumn();

int lastColumn = ca.getLastColumn();

int firstRow = ca.getFirstRow();

int lastRow = ca.getLastRow();

if (row >= firstRow && row <= lastRow)

{

if (column >= firstColumn && column <= lastColumn)

{

Row fRow = sheet.getRow(firstRow);

Cell fCell opnRk= fRow.getCell(firstColumn);

return this.getCellValue(fCell);

}

}

}

return null;

}

/**

* 判断是否是合并单元格

* @param sheet

* @param row

* @param column

* @return

*/

public boolean isMergedRegion(Sheet sheet,int row ,int column) {

int sheetMergeCount = sheet.getNumMergedRegions();

for (int i = 0; i < sheetMergeCount; i++) {

CellRangeAddress range = sheet.getMergedRegion(i);

int firstColumn = range.getFirstColumn();

int lastColumn = range.getLastColumn();

int firstRow = range.getFirstRow();

int lastRow = range.getLastRow();

if(row >= firstRow && row <= lastRow){

if(column >= firstColumn && column <= lastColumn){

return true;

}

}

}

return false;

}

}

请求控制器（处理页面excel导入请求）

package com.poiexcel.control;

import java.io.InputStream;

import java.io.PrintWriter;

import java.util.List;

import javax.servlet.http.HttpServletRequest;

import javax.servlet.http.HttpServletResponse;

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.ResponseBody;

import org.springframework.web.multipart.MultipartFile;

import org.springframework.web.multipart.MultipartHttpServletRequest;

import com.poiexcel.util.ImportExcelUtil;

import com.poiexcel.vo.InfoVo;

@Controller

@RequestMapping("/uploadExcel/*")

public class UploadExcelControl {

/**

* 描述：通过传统方式form表单提交方式导入excel文件

* @param request

* @throws Exception

*/

@RequestMapping(value="upload.do",method={RequestMethod.GET,RequestMethod.POST})

public String uploadExcel(HttpServletRequest request) throws Exception {

MultipartHttpServletRequest multipartRequest = (MultipartHttpServletRequest) request;

InputStream in =null;

List> listob = null;

MultipartFile file = multipartRequest.getFile("upfile");

if(file.isEmpty()){

throw new Exception("文件不存在！");

}

in = file.getInputStream();

listob = new ImportExcelUtil().getBankListByExcel(in,file.getOriginalFilename());

in.close();

//该处可调用service相应方法进行数据保存到数据库中，现只对数据输出

for (int i = 0; i < listob.size(); i++) {

List

InfoVo vo = new InfoVo();

vo.setCode(String.valueOf(lo.get(0)));

vo.setName(String.valueOf(lo.get(1)));

vo.setDate(String.valueOf(lo.get(2)));

vo.setMoney(String.valueOf(lo.get(3)));

System.out.println("打印信息-->机构:"+vo.getCode()+" 名称："+vo.getName()+" 时间："+vo.getDate()+" 资产："+vo.getMoney());

}

return "result";

}

前端代码

前端运用了bootstrap的文件上传组件fileinput,需要引入fileinput.css，fileinput.js，zh.js，bootstrap.css，bootstrap.js,jquery.min.js

Vo对象，保存Excel数据对应的对象

package com.poiexcel.vo;

//将Excel每一行数值转换为对象

public class InfoVo {

private String code;

private String name;

private String date;

private String money;

public String getCode() {

return code;

}

public void setCode(String code) {

this.code = code;

}

public String getName() {

return name;

}

public void setName(String name) {

this.name = name;

}

public String getDate() {

return date;

}

public void setDate(String date) {

this.date = date;

}

public String getMoney() {

return money;

}

public void setMoney(String money) {

this.money = money;

}

}

三.效果展示

1.页面展示

2.后台信息打印

四.总结

该例子只在控制台对导入的数据进行了输出，并没有进行持久化。如果要持久化，只需在注释的位置调用service层即可

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。