/**
* 导出客户列表
* custom/exportCustom
*
* @return
*/
@RequestMapping("/exportCustom")
public void exportCustom(HttpServletRequest request, HttpServletResponse response) {
DataPaging<Map<String, Object>> customs;
response.setCharacterEncoding("utf-8");
response.setContentType("multipart/form-data");
String fileName = "Custom.xlsx";
response.setHeader("Content-Disposition", "attachment;fileName=" + fileName);
Map<String, Object> param = new HashedMap();
try {
customs = customHandler.getList(param, new PageForm());
List<Map<String, Object>> maps = new ArrayList<>();
for (Map<String, Object> custom : customs.getList()) {
Map<String, Object> map = new HashMap<>();
map.put("customCode", custom.get("customCode"));
map.put("customName", custom.get("customName"));
map.put("customState", custom.get("customState"));
map.put("fullAddress", custom.get("fullAddress"));
map.put("income", custom.get("income"));
map.put("labelName", custom.get("labelName"));
map.put("levelName", custom.get("levelName"));
map.put("marry", custom.get("marry"));
map.put("hobby", custom.get("hobby"));
map.put("occupation",custom.get("occupation"));
map.put("occupationLevel", custom.get("occupationLevel"));
map.put("phone",custom.get("phone"));
map.put("sex", custom.get("sex"));
map.put("memo", custom.get("memo"));
map.put("source",custom.get("source"));
map.put("QQ", custom.get("QQ"));
map.put("weChat", custom.get("weChat"));
map.put("age", custom.get("age"));
map.put("categoryName", custom.get("categoryName"));
map.put("companyHigher", custom.get("companyHigher"));
map.put("companyIndustry", custom.get("companyIndustry"));
map.put("companyName", custom.get("companyName"));
map.put("companyNature",custom.get("companyNature"));
map.put("companyPhone", custom.get("companyPhone"));
map.put("companySize", custom.get("companySize"));
map.put("customGroupName",custom.get("customGroupName"));
maps.add(map);
}
LinkedHashMap<String, String> keys2titlesMap = new LinkedHashMap<String, String>();
keys2titlesMap.put("customCode", "客户编号");
keys2titlesMap.put("customName", "客户名称");
keys2titlesMap.put("customState", "客户状态");
keys2titlesMap.put("fullAddress", "客户地址");
keys2titlesMap.put("income", "客户收入");
keys2titlesMap.put("labelName", "客户标签");
keys2titlesMap.put("levelName", "客户等级");
keys2titlesMap.put("marry", "婚姻状况");
keys2titlesMap.put("hobby", "客户爱好");
keys2titlesMap.put("occupation", "职业");
keys2titlesMap.put("occupationLevel", "职级");
keys2titlesMap.put("phone", "客户联系方式");
keys2titlesMap.put("sex", "性别");
keys2titlesMap.put("memo", "备注");
keys2titlesMap.put("source", "客户来源");
keys2titlesMap.put("QQ", "QQ");
keys2titlesMap.put("weChat", "微信");
keys2titlesMap.put("age", "年龄");
keys2titlesMap.put("categoryName", "类别名称");
keys2titlesMap.put("companyHigher", "公司上级");
keys2titlesMap.put("companyIndustry", "公司行业");
keys2titlesMap.put("companyName", "公司名称");
keys2titlesMap.put("companyNature", "公司性质");
keys2titlesMap.put("companyPhone", "公司联系方式");
keys2titlesMap.put("companySize", "公司规模");
keys2titlesMap.put("customGroupName", "客户分组");
ExcelToolUtils.exportExcel(request.getInputStream(), response.getOutputStream(), maps, keys2titlesMap);
} catch (Exception e) {
e.printStackTrace();
}
}
package com.td.cms.util;
import com.td.util.MapUtils;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import java.io.InputStream;
import java.io.OutputStream;
import java.math.BigDecimal;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.*;
public class ExcelToolUtils {
public static void exportExcel(InputStream input, OutputStream out, List<Map<String, Object>> list, LinkedHashMap<String, String> keys2titlesMap) throws Exception {
SXSSFWorkbook wb = new SXSSFWorkbook(100);
Sheet sh = wb.createSheet("data");
Row rowHeader = sh.createRow(0);
Iterator<String> keys = keys2titlesMap.keySet().iterator();
SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
int i = 0;
List<String> keyList = new ArrayList<String>();
while(keys.hasNext()) {
String key = keys.next();
keyList.add(key);
String title = keys2titlesMap.get(key);
Cell cellHeader = rowHeader.createCell(i++);
cellHeader.setCellValue(title);
}
for(int rownum = 1; rownum <= list.size(); rownum++) {
Row row = sh.createRow(rownum);
Map<String, Object> dataMap = list.get(rownum-1);
i = 0;
// while(keys.hasNext()) {
for(String key: keyList){
// String key = keys.next();
Cell cell = row.createCell(i++);
if(dataMap.get(key) instanceof String) {
cell.setCellValue(MapUtils.getString(dataMap, key));
} else if(dataMap.get(key) instanceof Date) {
cell.setCellValue(simpleDateFormat.format(MapUtils.getDate(dataMap, key)));
} else if(dataMap.get(key) instanceof Number) {
cell.setCellValue(MapUtils.getDouble(dataMap, key));
} else if(dataMap.get(key) instanceof Boolean) {
cell.setCellValue(MapUtils.getBooleanValue(dataMap, key));
}
}
if(rownum % 100 == 0) {
((SXSSFSheet) sh).flushRows();
}
}
wb.write(out);
out.close();
}
public static List<Map<String, Object>> importExcel(InputStream input, String[] keys) throws Exception {
Workbook wb = WorkbookFactory.create(input);
Sheet sheet = wb.getSheetAt(0);
List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
for(int rownum = 1; rownum <= sheet.getLastRowNum(); rownum++) {
Row row = sheet.getRow(rownum);
if(row == null) {
continue;
}
Map<String, Object> map = new HashMap<String, Object>();
for(int cellnum = 0; cellnum<row.getLastCellNum(); cellnum++){
Cell cell = row.getCell(cellnum);
if(cell==null)
continue;
int valType = cell.getCellType();
if(valType == Cell.CELL_TYPE_STRING) {
map.put(keys[cellnum], cell.getStringCellValue());
} else if(valType == Cell.CELL_TYPE_BOOLEAN) {
map.put(keys[cellnum], cell.getBooleanCellValue());
} else if(valType == Cell.CELL_TYPE_NUMERIC) {
if(HSSFDateUtil.isCellDateFormatted(cell)){
//用于转化为日期格式
Date d = cell.getDateCellValue();
DateFormat formater = new SimpleDateFormat("yyyy-MM-dd");
map.put(keys[cellnum], formater.format(d));
} else {
map.put(keys[cellnum], numToStringFormat(String.valueOf(cell.getNumericCellValue())));
}
}
}
list.add(map);
}
return list;
}
/**
* 判断这个数字是否是科学计数法 如果是 则转换成普通模式
* @param num
* @return
*/
private static String numToStringFormat(String num){
if(num.contains("E10"))
{
BigDecimal bd = new BigDecimal(num);
String str = bd.toPlainString();
return str;
}else if(num.contains(".0")){
return num.substring(0,num.length()-2);
}else
return num;
}
}
/**
* 导入客户
* @param num
* @return
*/
@RequestMapping("/customImport")
@ResponseBody
public Object StructureImport(HttpServletRequest request, HttpServletResponse response, Long driveId) {
MultipartHttpServletRequest re = (MultipartHttpServletRequest) request;
System.out.println("开始");
Custom custom;
AreaLocation areaLocation;
Company company;
MultipartFile mf = re.getFile("upFile");
String[] keys = {"name", "phone", "customState", "fullAddress", "income", "marry", "hobby", "occupation",
"occupationLevel", "sex", "memo", "source", "QQ", "weChat", "age", "companyHigher", "companyIndustry",
"companyName", "companyNature", "companyPhone", "companySize"};
try {
List<Map<String, Object>> list = ExcelToolUtils.importExcel(mf.getInputStream(), keys);
for (Map<String, Object> map : list) {
if (map.get("customName") == null || map.get("phone") == null) {
continue;
}
custom = MapUtils.toObject(Custom.class, map);
areaLocation = MapUtils.toObject(AreaLocation.class, map);
company = MapUtils.toObject(Company.class, map);
customHandler.saveImportCustom(custom, company, areaLocation);
}
return ResultForm.createSuccessResultForm(null, "导入成功");
} catch (Exception e) {
e.printStackTrace();
System.out.println("上传文件错误!");
return ResultForm.createErrorResultForm(null, "导入失败");
}
}
java导出导入工具类使用demo
最新推荐文章于 2024-06-21 11:53:37 发布