pom.xml文件需要导入:
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.15-beta1</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.15-beta1</version>
</dependency>
这两个JAR包是工具类。
action:
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;
import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
/**
* 奖品发送快递单导入
*
*/
public class PrizeSendImportAction extends BaseAction{
private static final long serialVersionUID = 3864744293081464610L;
@Autowired
private AwardService awardService;
private String uploadfile;//上传文件
private List<String> messages;//返回信息
private int prizeType;//奖品发放类型 1-快递单 2-大奖发放
public String execute(){
messages = new ArrayList<String>();
StringBuffer message = new StringBuffer();
try {
if(StringUtils.isNotEmpty(uploadfile)){
if(prizeType != 0){
InputStream is = new FileInputStream(uploadfile);
int count = awardService.importExpressNum(is,prizeType);
message.append("本次共"+count+"个订单导入成功!");
}else{
message.append("未获取到发奖类型!");
}
}
} catch (FileNotFoundException e) {
e.printStackTrace();
message.append("文件导入失败!");
}
messages.add(message.toString());
return SUCCESS;
}
public String getUploadfile() {
return uploadfile;
}
public void setUploadfile(String uploadfile) {
this.uploadfile = uploadfile;
}
public List<String> getMessages() {
return messages;
}
public void setMessages(List<String> messages) {
this.messages = messages;
}
public int getPrizeType() {
return prizeType;
}
public void setPrizeType(int prizeType) {
this.prizeType = prizeType;
}
}
Service:
/**
* @author Bean
* 抽奖业务逻辑
*/
@Service
@Transactional
public class AwardService {
/**
* 导入奖品快递单号
* @param is
* @param prizeType
*/
public int importExpressNum(InputStream is,int prizeType){
int count=0;//记录更新单号条数
ExcelReader excelReader = new ExcelReader();
//获取数据内容,从第三行获取到最后
List<Map<Integer, String>> contentList = excelReader.readExcelContent(is,3,0);
//处理excel数据内容
//根据现有模板,数据是从内容获取的第二条内容开始计算
if(contentList != null && contentList.size()>0){
for(int i=0;i<contentList.size();i++){
Map<Integer, String> map = contentList.get(i);
String ordernum = map.get(0);//获取订单流水号
String tracknum = map.get(9);//获取快递单号
UserAwardPO userAward = userAwardManager.getById(Long.valueOf(ordernum));
if(prizeType == 1){//快递单
//判断快递单号是否存在以及导入文件中快递单号是否为空
if(userAward != null && StringUtils.isEmpty(userAward.getTrackingNumber())
&&StringUtils.isNotEmpty(tracknum)){
userAward.setTrackingNumber(tracknum);
userAward.setStatus(EnumUserAwardStatus.ONSHIP.getValue());//更改状态
userAward.setUpdatedAt(new Date());
userAwardRepository.save(userAward);
count++;
}
}else if(prizeType == 2){//体彩大奖
//判断状态是否需要更改
if(userAward != null && userAward.getStatus() == EnumUserAwardStatus.NOTSHIP.getValue()){
userAward.setStatus(EnumUserAwardStatus.ONSHIP.getValue());//更改状态
userAward.setUpdatedAt(new Date());
userAwardRepository.save(userAward);
count++;
}
}
}
}
return count;
}
}
调用示例:
import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
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.xssf.usermodel.XSSFWorkbook;
/**
* 操作Excel表格的功能类
* @author dhq
*
*/
public class ExcelReader {
private Workbook wb;
private Sheet sheet;
private Row row;
/**
* 读取Excel表格表头的内容
* @param InputStream
* @return String 表头内容的数组
*/
public String[] readExcelTitle(InputStream is) {
//文件流重用
ByteArrayOutputStream baos = new ByteArrayOutputStream();
byte[] buffer = new byte[1024];
int len;
try {
while ((len = is.read(buffer)) > -1 ) {
baos.write(buffer, 0, len);
}
baos.flush();
} catch (IOException e2) {
e2.printStackTrace();
}
try {
//.xlsx文件处理
wb = new XSSFWorkbook( new ByteArrayInputStream(baos.toByteArray()));
} catch (Exception e) {
// e.printStackTrace();
//.xls文件处理
try {
wb = new HSSFWorkbook( new ByteArrayInputStream(baos.toByteArray()));
} catch (IOException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
}
}
sheet = wb.getSheetAt(0);
row = sheet.getRow(0);
// 标题总列数
int colNum = row.getPhysicalNumberOfCells();
System.out.println("colNum:" + colNum);
String[] title = new String[colNum];
for (int i = 0; i < colNum; i++) {
//title[i] = getStringCellValue(row.getCell((short) i));
title[i] = getCellFormatValue(row.getCell((short) i));
}
return title;
}
/**
*
* 读取Excel数据内容
* @param is 文件流
* @param beginnum 开始行 (包含本行)
* @param endnum 结束行 ( 包含本行,如为 0 表示最终行)
* @return List 包含单元格数据内容的Map对象
*/
public List<Map<Integer, String>> readExcelContent(InputStream is,int beginnum,int endnum) {
List<Map<Integer, String>> list = new ArrayList<Map<Integer, String>>();
//文件流重用
ByteArrayOutputStream baos = new ByteArrayOutputStream();
byte[] buffer = new byte[1024];
int len;
try {
while ((len = is.read(buffer)) > -1 ) {
baos.write(buffer, 0, len);
}
baos.flush();
} catch (IOException e2) {
e2.printStackTrace();
}
try {
//.xlsx文件处理
wb = new XSSFWorkbook( new ByteArrayInputStream(baos.toByteArray()));
} catch (Exception e) {
// e.printStackTrace();
//.xls文件处理
try {
wb = new HSSFWorkbook( new ByteArrayInputStream(baos.toByteArray()));
} catch (IOException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
}
}
sheet = wb.getSheetAt(0);
// 得到总行数
int rowNum = sheet.getLastRowNum();
int colNum = 0;
if(rowNum >0 ){
row = sheet.getRow(0);
colNum = row.getPhysicalNumberOfCells();
}
//判断内容起始行
if(beginnum < 1 ){
beginnum = 1;
}
if(endnum == 0 ||endnum > (rowNum+1)){
endnum = rowNum+1;
}
for (int i = beginnum-1; i < endnum; i++) {
Map<Integer, String> content = new HashMap<Integer, String>();
row = sheet.getRow(i);
int j = 0;
while (j < colNum) {
// 每个单元格的数据内容用"-"分割开,以后需要时用String类的replace()方法还原数据
// 也可以将每个单元格的数据设置到一个javabean的属性中,此时需要新建一个javabean
// str += getStringCellValue(row.getCell((short) j)).trim() +
// "-";
String str = getCellFormatValue(row.getCell((short) j)).trim();
content.put(j, str);
j++;
}
list.add(content);
}
return list;
}
/**
* 获取单元格数据内容为字符串类型的数据
*
* @param cell Excel单元格
* @return String 单元格数据内容
*/
private String getStringCellValue(Cell cell) {
String strCell = "";
switch (cell.getCellType()) {
case Cell.CELL_TYPE_STRING:
strCell = cell.getStringCellValue();
break;
case Cell.CELL_TYPE_NUMERIC:
strCell = String.valueOf(cell.getNumericCellValue());
break;
case Cell.CELL_TYPE_BOOLEAN:
strCell = String.valueOf(cell.getBooleanCellValue());
break;
case Cell.CELL_TYPE_BLANK:
strCell = "";
break;
default:
strCell = "";
break;
}
if (strCell.equals("") || strCell == null) {
return "";
}
if (cell == null) {
return "";
}
return strCell;
}
/**
* 获取单元格数据内容为日期类型的数据
*
* @param cell
* Excel单元格
* @return String 单元格数据内容
*/
private String getDateCellValue(Cell cell) {
String result = "";
try {
int cellType = cell.getCellType();
if (cellType == Cell.CELL_TYPE_NUMERIC) {
Date date = cell.getDateCellValue();
result = (date.getYear() + 1900) + "-" + (date.getMonth() + 1)
+ "-" + date.getDate();
} else if (cellType == Cell.CELL_TYPE_STRING) {
String date = getStringCellValue(cell);
result = date.replaceAll("[年月]", "-").replace("日", "").trim();
} else if (cellType == Cell.CELL_TYPE_BLANK) {
result = "";
}
} catch (Exception e) {
System.out.println("日期格式不正确!");
e.printStackTrace();
}
return result;
}
/**
* 根据Cell类型设置数据
* @param cell
* @return
*/
private String getCellFormatValue(Cell cell) {
String cellvalue = "";
if (cell != null) {
// 判断当前Cell的Type
switch (cell.getCellType()) {
// 如果当前Cell的Type为NUMERIC
case Cell.CELL_TYPE_NUMERIC:
case Cell.CELL_TYPE_FORMULA: {
// 判断当前的cell是否为Date
if (DateUtil.isCellDateFormatted(cell)) {
// 如果是Date类型则,转化为Data格式
//方法1:这样子的data格式是带时分秒的:2011-10-12 0:00:00
//cellvalue = cell.getDateCellValue().toLocaleString();
//方法2:这样子的data格式是不带带时分秒的:2011-10-12
Date date = cell.getDateCellValue();
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
cellvalue = sdf.format(date);
}
// 如果是纯数字
else {
// 取得当前Cell的数值
cellvalue = String.valueOf(cell.getNumericCellValue());
}
break;
}
// 如果当前Cell的Type为STRIN
case Cell.CELL_TYPE_STRING:
// 取得当前的Cell字符串
cellvalue = cell.getRichStringCellValue().getString();
break;
// 默认的Cell值
default:
cellvalue = " ";
}
} else {
cellvalue = "";
}
return cellvalue;
}
}