poi

package cn.itcast.bos.web.action;


import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;


import javax.persistence.criteria.CriteriaBuilder;
import javax.persistence.criteria.CriteriaQuery;
import javax.persistence.criteria.Predicate;
import javax.persistence.criteria.Root;
import javax.servlet.http.HttpServletResponse;


import org.apache.commons.lang3.StringUtils;
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.Row;
import org.apache.struts2.ServletActionContext;
import org.apache.struts2.convention.annotation.Action;
import org.apache.struts2.convention.annotation.Namespace;
import org.apache.struts2.convention.annotation.ParentPackage;
import org.apache.struts2.convention.annotation.Result;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.context.annotation.Scope;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.domain.Specification;
import org.springframework.stereotype.Controller;


import com.opensymphony.xwork2.ActionContext;
import com.opensymphony.xwork2.ActionSupport;
import com.opensymphony.xwork2.ModelDriven;


import cn.itcast.bos.Utils.FileUtils;
import cn.itcast.bos.Utils.PinYin4jUtils;
import cn.itcast.bos.domain.base.Area;
import cn.itcast.bos.service.AreaService;


@Controller
@Namespace("/")
@ParentPackage("json-default")
@Scope("prototype")
public class AreaAction extends ActionSupport implements ModelDriven<Area> {
private File file;
private Area area = new Area();
@Autowired
@Qualifier("areaService")
private AreaService areaService;
@Override
public Area getModel() {
// TODO Auto-generated method stub
return area;
}

public void setFile(File file) {
this.file = file;
}


@Action(value="area_batchImport")
public String batchImport() throws IOException {
List<Area> areas = new ArrayList<Area>();
HSSFWorkbook hssfWorkbook = new HSSFWorkbook(new FileInputStream(file));
//读取一个sheet
HSSFSheet sheet = hssfWorkbook.getSheetAt(0);
//读取sheet中的每一行
for (Row row : sheet) {
if(row.getRowNum() == 0) {
continue;
}
if(row.getCell(0) == null || StringUtils.isBlank(row.getCell(0).getStringCellValue())) {
continue;
}
Area area = new Area();
area.setId(row.getCell(0).getStringCellValue());
area.setProvince(row.getCell(1).getStringCellValue());
area.setCity(row.getCell(2).getStringCellValue());
area.setDistrict(row.getCell(3).getStringCellValue());
area.setPostcode(row.getCell(4).getStringCellValue());

String province = area.getProvince();
String city = area.getCity();
String district = area.getDistrict();
province = province.substring(0, province.length()-1);
city = city.substring(0, city.length()-1);
district = district.substring(0, district.length()-1);
String[] headArry = PinYin4jUtils.getHeadByString(province + city + district);
StringBuffer buffer = new StringBuffer();
for (String str : headArry) {
buffer.append(str);
}
String shortcode = buffer.toString();
area.setShortcode(shortcode);
//城市编码
String cityCode = PinYin4jUtils.hanziToPinyin(city,"");
area.setCitycode(cityCode);
areas.add(area);
}
//调用业务层
areaService.saveBatch(areas);
return null;
}
private int page;
private int rows;

public void setPage(int page) {
this.page = page;
}


public void setRows(int rows) {
this.rows = rows;
}


@Action(value="area_pageQuery",results={@Result(name="success",type="json")})
public String area_pageQuery() {
Pageable pageable = new PageRequest(page-1, rows);
Specification<Area> specification = new Specification<Area>() {


@Override
public Predicate toPredicate(Root<Area> root, CriteriaQuery<?> query, CriteriaBuilder cb) {
// TODO Auto-generated method stub
List<Predicate> list = new ArrayList<Predicate>();
if(StringUtils.isNotBlank(area.getProvince())) {
Predicate p1 = cb.like(root.get("province").as(String.class), "%" + area.getProvince() + "%");
list.add(p1);
}
if(StringUtils.isNotBlank(area.getCity())) {
Predicate p2 = cb.like(root.get("city").as(String.class),"%" + area.getCity() + "%");
list.add(p2);
}
if(StringUtils.isNoneBlank(area.getDistrict())) {
Predicate p3 = cb.like(root.get("district").as(String.class),"%" + area.getDistrict() + "%");
list.add(p3);
}
return cb.and(list.toArray(new Predicate[0]));
}
};
Page<Area> pageData = areaService.findPageData(specification,pageable);
HashMap<String, Object> map = new HashMap<String,Object>();
map.put("total", pageData.getTotalElements());
map.put("rows", pageData.getContent());
ActionContext.getContext().getValueStack().push(map);
return "success";
}
//导出Excel
@Action(value = "area_export")
public String export() throws IOException {
//1.查询所有数据
List<Area> list = areaService.findAll();
//2.将list集合中的数据写入excel
HSSFWorkbook workbook = new HSSFWorkbook();
//2.1创建sheet
HSSFSheet sheet = workbook.createSheet("区域数据");
//2.2创建表头行
HSSFRow row = sheet.createRow(0);
row.createCell(0).setCellValue("区域编号");
row.createCell(1).setCellValue("省");
row.createCell(2).setCellValue("市");
row.createCell(3).setCellValue("区");
row.createCell(4).setCellValue("邮编");
row.createCell(5).setCellValue("简码");
row.createCell(6).setCellValue("城市编码");
//2.3循环集合，将数据写入每一行
for (int i = 1; i < list.size(); i++) {
HSSFRow rows = sheet.createRow(i);
rows.createCell(0).setCellValue(list.get(i-1).getId());
rows.createCell(1).setCellValue(list.get(i-1).getProvince());
rows.createCell(2).setCellValue(list.get(i-1).getCity());
rows.createCell(3).setCellValue(list.get(i-1).getDistrict());
rows.createCell(4).setCellValue(list.get(i-1).getPostcode());
rows.createCell(5).setCellValue(list.get(i-1).getShortcode());
rows.createCell(6).setCellValue(list.get(i-1).getCitycode());
}
//3:将excel写到页面上，以附件的形式打开
HttpServletResponse response = ServletActionContext.getResponse();
String filename = "分区数据.xls";
String agent = ServletActionContext.getRequest().getHeader("user-agent");
filename = FileUtils.getFileName(agent, filename);
//3.1设置头content-disposition,inline打开方式
response.setHeader("content-disposition", "attachment,filename="+filename);
//3.2设置头content-type文件类型
String mimeType = ServletActionContext.getServletContext().getMimeType(filename);
response.setContentType(mimeType);
//设置输出流
workbook.write(response.getOutputStream());
return NONE;
}
@Action(value="findArea",results={@Result(name="success",type="json")})
public String findArea() {
//System.out.println("---------");
List<Area> list = areaService.findAll();
ActionContext.getContext().getValueStack().push(list);
return "success";
}


}