xml
<select id="selectShopList" resultType="java.util.HashMap">
SELECT
p.PARTY_NAME partyName,
c.CUSTOMER_NAME customerName,
s.SHOP_ID shopId,
s.SHOP_NAME shopName FROM shop_info s,customer_info c,party p
WHERE s.CUSTOMER_ID=c.CUSTOMER_ID AND s.PARTY_ID=p.PARTY_ID AND p.COUNTRY_ID=#{countryId}
</select> <select id="selectShopRole" resultType="java.util.HashMap">
SELECT
u.USER_NAME userName,
m.salertype salerType
FROM shop_saler_mapping m,user_login u WHERE m.`user_login_id`=u.`USER_LOGIN_ID`
AND m.shop_id=#{shopId}
</select>
dao
//获取门店信息
public List<HashMap<String, Object>> selectShopList(@Param("countryId")String countryId) throws Exception; //获取门店中的角色信息
public List<HashMap<String,Object>> selectShopRole(@Param("shopId")String shopId) throws Exception;
service
//导出门店人员信息
public XSSFWorkbook exportShopRole( String[] excelHeader,
String title) throws Exception;
service.impl
public XSSFWorkbook exportShopInfo(String searchStr,String conditions, String[] excelHeader,
String title) throws Exception {
//门店信息集合
List<Shop> list = shopDao.exportShopInfo(searchStr,conditions); int[] excelWidth = {,,,,,,,,,,,,}; XSSFWorkbook workbook = new XSSFWorkbook(); XSSFSheet sheet = workbook.createSheet(title); XSSFRow row = sheet.createRow(); //导出字体样式
XSSFFont font = workbook.createFont();
font.setFontHeightInPoints((short) ); // 字体大小 //导出样式
XSSFCellStyle style = workbook.createCellStyle();
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
style.setFont(font); for (int i = ; i < excelWidth.length; i++) {
sheet.setColumnWidth(i, * excelWidth[i]);
} //表头数据
for (int i = ; i < excelHeader.length; i++) {
XSSFCell cell = row.createCell(i);
cell.setCellValue(excelHeader[i]);
cell.setCellStyle(style); } //表体数据
for (int i = ; i < list.size(); i++) {
row = sheet.createRow(i+);
Shop shop = list.get(i); //门店名
XSSFCell cell0 = row.createCell();
cell0.setCellValue(shop.getShopName());
cell0.setCellStyle(style); //客户编码
XSSFCell cell1 = row.createCell();
cell1.setCellValue(shop.getCustomerCode());
cell1.setCellStyle(style); //门店等级
XSSFCell cell2 = row.createCell();
if(shop.getLevel()!=null){
if(shop.getLevel().equals("")){
cell2.setCellValue("A");
}else if(shop.getLevel().equals("")){
cell2.setCellValue("B");
}else if(shop.getLevel().equals("")){
cell2.setCellValue("C");
}else if(shop.getLevel().equals("")){
cell2.setCellValue("D");
}else if(shop.getLevel().equals("")){
cell2.setCellValue("S");
}else if(shop.getLevel().equals("")){
cell2.setCellValue("AA");
}else{
cell2.setCellValue(shop.getLevel());
}
}else{
cell2.setCellValue(shop.getLevel());
} cell2.setCellStyle(style); //进驻时间
XSSFCell cell3 = row.createCell();
if(shop.getEnterDate()==null || shop.getEnterDate().equals("")){
cell3.setCellValue("");
}else{
cell3.setCellValue(DateFormatUtils.format(shop.getEnterDate(), "yyyy-MM-dd"));
}
cell3.setCellStyle(style); //区域
XSSFCell cell4 = row.createCell();
cell4.setCellValue(shop.getPartyName());
cell4.setCellStyle(style); //省份
XSSFCell cell5 = row.createCell();
cell5.setCellValue(shop.getProvinceName());
cell5.setCellStyle(style); //地址
XSSFCell cell6 = row.createCell();
cell6.setCellValue(shop.getDetailAddress());
cell6.setCellStyle(style); //联系人
XSSFCell cell7 = row.createCell();
cell7.setCellValue(shop.getContactName());
cell7.setCellStyle(style); //联系人电话
XSSFCell cell8 = row.createCell();
cell8.setCellValue(shop.getPhone());
cell8.setCellStyle(style); //邮箱
XSSFCell cell9 = row.createCell();
cell9.setCellValue(shop.getEmail());
cell9.setCellStyle(style); //经度
XSSFCell cell10 = row.createCell();
if( shop.getLng()==null){
cell10.setCellValue();
}else{
cell10.setCellValue(shop.getLng());
}
cell10.setCellStyle(style); //纬度
XSSFCell cell11 = row.createCell();
if( shop.getLat()==null){
cell11.setCellValue();
}else{
cell11.setCellValue(shop.getLat());
} cell11.setCellStyle(style); //Location
XSSFCell cell12 = row.createCell();
cell12.setCellValue(shop.getLocation());
cell12.setCellStyle(style);
} return workbook;
} @Override
public XSSFWorkbook exportShopRole(
String[] excelHeader, String title) throws Exception {
//门店信息
List<HashMap<String, Object>> shopList = shopDao.selectShopList(WebPageUtil.getLoginedUser().getPartyId()); int[] excelWidth = {,,,,,};
XSSFWorkbook workbook = new XSSFWorkbook();
XSSFSheet sheet = workbook.createSheet(title); //导出字体样式
XSSFFont font = workbook.createFont();
font.setFontHeightInPoints((short) ); // 字体大小 //导出样式
XSSFCellStyle style = workbook.createCellStyle();
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
style.setFont(font); XSSFCellStyle style1 = workbook.createCellStyle();
style1.setAlignment(HSSFCellStyle.ALIGN_CENTER);
style1.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
style1.setFillForegroundColor(HSSFColor.GOLD.index);
style1.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
style1.setFont(font); for (int i = ; i < excelWidth.length; i++) {
sheet.setColumnWidth(i, * excelWidth[i]);
} XSSFRow row = sheet.createRow(); //表头数据
for (int i = ; i < excelHeader.length; i++) {
XSSFCell cell = row.createCell(i);
cell.setCellValue(excelHeader[i]);
cell.setCellStyle(style1);
} //左边区域，渠道，门店名
for (int i = ; i < shopList.size(); i++) {
//返回map,每一个shopId对应一个map
HashMap<String, Object> shopMap=shopList.get(i); String shopId = shopMap.get("shopId").toString(); //门店id中对应的用户名
List<HashMap<String,Object>> listRole = shopDao.selectShopRole(shopId); String promoter="";
String ywy="";
String dd="";
for (int j = ; j < listRole.size(); j++) {
HashMap<String,Object> shop2 = listRole.get(j); if(shop2.get("salerType").toString().equals(""))
{
ywy+=shop2.get("userName")+"/";
}
else if(shop2.get("salerType").toString().equals(""))
{
promoter+=shop2.get("userName")+"/";
}
else if(shop2.get("salerType").toString().equals(""))
{
dd+=shop2.get("userName")+"/";
} } //结束循环，结果放在循环外
shopMap.put("promoter", promoter);
shopMap.put("ywy", ywy);
shopMap.put("dd", dd); }
////////////end/////////// //excel export for (int j = ; j < shopList.size(); j++) { row = sheet.createRow(j+); //区域
XSSFCell cell0 = row.createCell();
cell0.setCellValue(shopList.get(j).get("partyName").toString());
cell0.setCellStyle(style); //渠道
XSSFCell cell1 = row.createCell();
cell1.setCellValue(shopList.get(j).get("customerName").toString());
cell1.setCellStyle(style); //门店
XSSFCell cell2 = row.createCell();
cell2.setCellValue(shopList.get(j).get("shopName").toString());
cell2.setCellStyle(style); XSSFCell cell3 = row.createCell(+);
cell3.setCellValue(shopList.get(j).get("promoter").toString());
cell3.setCellStyle(style); XSSFCell cell4 = row.createCell(+);
cell4.setCellValue(shopList.get(j).get("ywy").toString());
cell4.setCellStyle(style); XSSFCell cell5 = row.createCell(+);
cell5.setCellValue(shopList.get(j).get("dd").toString());
cell5.setCellStyle(style);
} return workbook;
}
}
action
//导出门店人员信息
public void exportShopRole(){
try {
String title ="Export All Info";
String fileName = getExportExcelName(title);
final String userAgent = request.getHeader("USER-AGENT");
if (null != userAgent){
if (- != userAgent.indexOf("Firefox")) {//Firefox
fileName = new String(fileName.getBytes(), "ISO8859-1");
}else if (- != userAgent.indexOf("Chrome")) {//Chrome
fileName = new String(fileName.getBytes(), "ISO8859-1");
} else {//IE7+
fileName = URLEncoder.encode(fileName, "UTF-8");
fileName = StringUtils.replace(fileName, "+", "%20");//替换空格
}
} else {
fileName = fileName;
} //国家化列表头
String[] excelHeader = {"Party","Dealer Name","Store Name",
"Promoter Name","Sales Man Name","MD Name"}; XSSFWorkbook workbook = shopService.exportShopRole(excelHeader, title);
response.setContentType("application/vnd.ms-excel");
response.setHeader("Content-Disposition", "attachment; filename=\"" + fileName + "\"");
OutputStream ouputStream = response.getOutputStream();
workbook.write(ouputStream);
ouputStream.flush();
ouputStream.close();
} catch (Exception e) {
e.printStackTrace();
log.error(e.getMessage(),e);
}
}