//*号，为敏感字符（公司平台），此处用*号代替
package ******;
import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.io.UnsupportedEncodingException;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Set;
import java.util.Map.Entry;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import loquat.query.jro.JROQuery;
import org.apache.commons.collections15.map.ListOrderedMap;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
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;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.ResponseBody;
import cn.lds.loa.common.manager.UploadFilesInfoManager;
import cn.lds.loa.common.model.UploadFilesInfo;
import cn.lds.loa.common.utils.CommonFunction;
import cn.lds.loa.gof.manager.PerAddrBookManager;
import cn.lds.loa.gof.model.PerAddrBook;
import cn.lds.loa.gof.model.query.PerAddrBook_;
import *.ajax.model.FailureCommandResponse;
import *.ajax.model.SuccessCommandResponse;
import *.i18n.LocalMessages;
import *.pac.factory.ManagerFactory;
import *.pac.manager.UserManager;
import *.pac.model.User;
import *.restful.svc.AbstractRestfulService;
@Controller
@RequestMapping("/loa/excle/*")
public class ExcelImportOrExportService extends AbstractRestfulService {
PerAddrBook_ qPerAddrBook = PerAddrBook_.PER_ADDR_BOOK;
protected PerAddrBookManager getPerAddrBookManager() {
return ManagerFactory.getRecordObjectManager(PerAddrBook.class);
}
protected UserManager getUserManager() {
return ManagerFactory.getRecordObjectManager(User.class);
}
protected UploadFilesInfoManager getUpLoadFilesInfoManager() {
return ManagerFactory.getRecordObjectManager(UploadFilesInfo.class);
}
private static final String STRING_NAME = "姓名";
private static final String STRING_MOBILE = "手机号";
private static final String STRING_ADDRESS = "住址";
private static final String STRING_COMPANY = "单位";
private static final String STRING_COMMENT = "备注";
/**
* 导入excle表
*
* @param filestream
* 文件流
*/
@ResponseBody
@RequestMapping(value = "in")
public String inputExcle(
@RequestParam(value = "filePath", required = false) String filePath) {
String result = "";
// 根据文件路径获取文件名
String fileName = filePath.substring(filePath.lastIndexOf("/"),filePath.length());
// 默认为2003版本格式
boolean isExcel2003 = true;
try {
// 验证是否为excle表格
if (fileName == null || !fileName.matches("^.+\\.(?i)((xls)|(xlsx))$")) {
SuccessCommandResponse successCommand = new SuccessCommandResponse();
successCommand.setSuccess(true);
successCommand.setUrl("文件格式不正确");
result = marshal(successCommand);
return result;
}
// 验证是否为2003版本格式
if (fileName.matches("^.+\\.(?i)(xlsx)$")) {
isExcel2003 = false;
}
// 检查文件是否存在
// File file = new File(fileName);
// if (file == null || !file.exists()) {
// SuccessCommandResponse successCommand = new SuccessCommandResponse();
// successCommand.setSuccess(true);
// successCommand.setUrl("文件不存在或为空");
// result = marshal(successCommand);
// return result;
// }
UploadFilesInfo upLoadFilesInfo = getUpLoadFilesInfoManager().getByPath(filePath);
if (upLoadFilesInfo == null) {
FailureCommandResponse failureCommand = new FailureCommandResponse();
failureCommand.setErrorMessage("文件为空");
result = marshal(failureCommand);
return result;
}
ByteArrayInputStream inputStream = new ByteArrayInputStream(upLoadFilesInfo.getContent());
// XSSFWorkbook workbook = new XSSFWorkbook(bin);
//获取当前用户
User user = CommonFunction.getCurrentUser();
long userId = user.getId();
result = isEdition(inputStream, userId, isExcel2003,filePath);
} catch (Exception e) {
e.printStackTrace();
FailureCommandResponse failureCommand = new FailureCommandResponse();
failureCommand.setErrorMessage("读取文件失败");
result = marshal(failureCommand);
}
return result;
}
/**
* 导出excle表
*
* @param filestream
* 文件流
*/
@ResponseBody
@RequestMapping(value = "out")
public void outputExcle(HttpServletRequest request, HttpServletResponse response) {
response.reset();
// 获取当前用户id
try {
//获取当前用户
User user = CommonFunction.getCurrentUser();
long userId = user.getId();
List<Map<String,Object>> datas = new ArrayList<Map<String,Object>>();
JROQuery query = getPerAddrBookManager().createModelQuery();
query.where(qPerAddrBook.USER_ID.eq(userId));
query.limit(1000);
List<PerAddrBook> perAddrBooks = query.list(qPerAddrBook);
for(PerAddrBook perAddrBook : perAddrBooks){
Map<String,Object> map = new ListOrderedMap<String,Object>();
map.put(STRING_NAME, perAddrBook.getFullname());
map.put(STRING_MOBILE, perAddrBook.getMobile());
map.put(STRING_ADDRESS, perAddrBook.getHomeAddr());
map.put(STRING_COMPANY, perAddrBook.getCompanyName());
map.put(STRING_COMMENT, perAddrBook.getComments());
datas.add(map);
}
String filename = "我的通讯录.xls";
String contentType = "application/vnd.ms-excel";
response.setContentType(contentType);
//针对不同浏览器，采用不同编码
String agent = request.getHeader("USER-AGENT");
if (null != agent && -1 != agent.indexOf("MSIE")) {
filename = URLEncoder.encode(filename, "UTF-8");
filename = filename.replace("+", "%20");
response.addHeader("Content-Disposition", "attachment; filename="
+ filename);
} else if (null != agent && -1 != agent.indexOf("Mozilla")) {
filename = new String(filename.getBytes(), "ISO8859-1");
response.addHeader("Content-Disposition", "attachment; filename=\""
+ filename+"\"");
}
OutputStream outp = null;
ByteArrayInputStream bin = null;
try {
outp = response.getOutputStream();
bin = ExcelTemplate(datas);
byte[] b = new byte[1024];
int i = 0;
while ((i = bin.read(b)) > 0) {
outp.write(b, 0, i);
}
outp.flush();
} catch (Exception e) {
e.printStackTrace();
} finally {
if (bin != null) {
bin.close();
bin = null;
}
if (outp != null) {
outp.close();
outp = null;
}
}
} catch (UnsupportedEncodingException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
}
private ByteArrayInputStream ExcelTemplate(List<Map<String,Object>> datas){
ByteArrayInputStream result;
Map<String,Workbook> wbmap = new HashMap<String, Workbook>();
//构造2003、2007版excel
wbmap.put("2003", new HSSFWorkbook());
wbmap.put("2007", new XSSFWorkbook());
//此处应该根据页面判断本地安装的excel版本传入参数
//但是不知道怎么判断，姑且给一个默认值
result = createExcel(datas,wbmap.get("2003"));
return result;
}
private ByteArrayInputStream createExcel(List<Map<String,Object>> datas,Workbook wbmap){
ByteArrayInputStream result = null;
//设置样式
CellStyle cellStyle = wbmap.createCellStyle();//创建样式
cellStyle.setAlignment(CellStyle.ALIGN_CENTER);//靠左
String sheetName = "通讯录";
//创建一个新的表\并创建名称
Sheet s = null==sheetName ? s = wbmap.createSheet():wbmap.createSheet(sheetName);
s.setColumnWidth(0, 2816);
s.setColumnWidth(1, 3840);
s.setColumnWidth(2, 14848);
s.setColumnWidth(3, 6144);
s.setColumnWidth(4, 12800);
//定义列(列名第一行)
Row titleColl = s.createRow(0);
Cell titleCell_0 = titleColl.createCell(0);
Cell titleCell_1 = titleColl.createCell(1);
Cell titleCell_2 = titleColl.createCell(2);
Cell titleCell_3 = titleColl.createCell(3);
Cell titleCell_4 = titleColl.createCell(4);
titleCell_0.setCellValue(STRING_NAME);
titleCell_1.setCellValue(STRING_MOBILE);
titleCell_2.setCellValue(STRING_ADDRESS);
titleCell_3.setCellValue(STRING_COMPANY);
titleCell_4.setCellValue(STRING_COMMENT);
titleCell_0.setCellStyle(cellStyle);
titleCell_1.setCellStyle(cellStyle);
titleCell_2.setCellStyle(cellStyle);
titleCell_3.setCellStyle(cellStyle);
titleCell_4.setCellStyle(cellStyle);
for(int i=0; i<datas.size(); i++){ //遍历数据
Map<String,Object> map = datas.get(i);
//定义行(内容第二行起)
Row row = s.createRow(i+1);
Set<Entry<String, Object>> set = map.entrySet();
Iterator<Entry<String, Object>> it = set.iterator();
int x = 0;
//书写一行数据
while(it.hasNext()){
//列名
Entry<String, Object> entry = it.next();
//内容
Cell cell = row.createCell(x);
Object value = entry.getValue();
if(value instanceof String){
cell.setCellValue(value.toString());
}else if(value instanceof Integer){
cell.setCellValue((String)value.toString());
}
//设置内容样式
// cell.setCellStyle(cellStyle);
x++;
}
try {
ByteArrayOutputStream baos = new ByteArrayOutputStream();
try {
wbmap.write(baos);
baos.flush();
byte[] aa = baos.toByteArray();
result = new ByteArrayInputStream(aa,0, aa.length);
baos.close();
} catch (IOException e) {
e.printStackTrace();
}
} catch (Exception e) {
e.printStackTrace();
result = null;
}
}
return result;
}
/**
*
* @param inputStream
* @param userId
* @param isExcel2003
* @return
*/
private String isEdition(ByteArrayInputStream inputStream, long userId,boolean isExcel2003,String filePath) {
String result = "";
try {
// 根据版本调用方法
Workbook workbook = isExcel2003 ? new HSSFWorkbook(inputStream)
: new XSSFWorkbook(inputStream);
// 调用方法读取excle表
result = read(workbook, userId, filePath);
} catch (Exception e) {
e.printStackTrace();
FailureCommandResponse failureCommand = new FailureCommandResponse();
failureCommand.setErrorMessage(LocalMessages.getMessage("loa.msg.saveFailure"));
result = marshal(failureCommand);
}
return result;
}
/**
* 读取excle表格并存入数据库
*
* @param workbook
* @param userId
*/
private String read(Workbook workbook, long userId, String filePath) {
String result = "";
List<Long> listIds = new ArrayList<Long>();
// 行数
int totalRows;
// 列数
// int totalCells;
//保存成功条数
int successCount = 0;
//保存失败条数
int failureCount = 0;
// 得到第一个sheeet
Sheet sheet = workbook.getSheetAt(0);
totalRows = sheet.getPhysicalNumberOfRows();
//一次只能导入2000行数据
if (totalRows > 2001){
FailureCommandResponse failureCommand = new FailureCommandResponse();
failureCommand.setErrorMessage("文件太大，为保证软件性能，一次最多只能导入2000行数据");
result = marshal(failureCommand);
return result;
}
try{
Row row = sheet.getRow(0);
boolean cell_1 = getCellString(row.getCell(0)).equals(STRING_NAME) ? true : false;
boolean cell_2 = getCellString(row.getCell(1)).equals(STRING_MOBILE) ? true : false;
boolean cell_3 = getCellString(row.getCell(2)).equals(STRING_ADDRESS) ? true : false;
boolean cell_4 = getCellString(row.getCell(3)).equals(STRING_COMPANY) ? true : false;
boolean cell_5 = getCellString(row.getCell(4)).equals(STRING_COMMENT) ? true : false;
if(!cell_1 || !cell_2 || !cell_3 || !cell_4 || !cell_5){
FailureCommandResponse failureCommand = new FailureCommandResponse();
failureCommand.setErrorMessage("表格格式请按照:"+STRING_NAME+"、"+STRING_MOBILE+"、"+STRING_ADDRESS+"、"+STRING_COMPANY+"、"+STRING_COMMENT+"进行排序");
result = marshal(failureCommand);
return result;
}
for (int i = 1; i < totalRows; i++) {
row = sheet.getRow(i);
if (null != row) {
PerAddrBook perAddrBook = new PerAddrBook();
// 获得指定单元格中的数据
String name = getCellString(row.getCell(0));
boolean f_cell_0 = name != null ? true : false;
f_cell_0 = name != "" ? true : false;
boolean f_cell_1 = row.getCell(1) != null ? true : false;
boolean f_cell_2 = row.getCell(2) != null ? true : false;
boolean f_cell_3 = row.getCell(3) != null ? true : false;
boolean f_cell_4 = row.getCell(4) != null ? true : false;
//姓名不为空，若为空该条语句作废
if(f_cell_0){
JROQuery query = getPerAddrBookManager().createModelQuery();
query.where(qPerAddrBook.USER_ID.eq(userId).and(qPerAddrBook.FULLNAME.eq(name)));
Long count = query.count();
if(count > 0){
failureCount += 1;
}else{
successCount += 1;
if(f_cell_1){
perAddrBook.setMobile(getCellString(row.getCell(1)));
}
if(f_cell_2){
perAddrBook.setHomeAddr(getCellString(row.getCell(2)));
}
if(f_cell_3){
perAddrBook.setCompanyName(getCellString(row.getCell(3)));
}
if(f_cell_4){
perAddrBook.setComments(getCellString(row.getCell(4)));
}
perAddrBook.setFullname(name);
perAddrBook.setUserId(userId);
perAddrBook.setGender(2);
perAddrBook.setTypeId(0L);
listIds.add(getPerAddrBookManager().savePerAddrBook(perAddrBook).getId());
}
}
}
}
getUpLoadFilesInfoManager().deleteExcelByPath(filePath);
SuccessCommandResponse successCommand = new SuccessCommandResponse();
successCommand.setSuccess(true);
successCommand.setUrl("导入表格成功,成功导入"+successCount+"条,有"+failureCount+"条已存在");
result = marshal(successCommand);
}catch(Exception e){
//导入数据出错，恢复数据库
for(Long id : listIds){
getPerAddrBookManager().deleteById(id);
}
e.printStackTrace();
FailureCommandResponse failureCommand = new FailureCommandResponse();
failureCommand.setErrorMessage(LocalMessages.getMessage("loa.msg.saveFailure"));
result = marshal(failureCommand);
}
return result;
}
private String getCellString(Cell cell) {
String result = "";
if(cell != null){
int cellType = cell.getCellType();
switch (cellType) {
case Cell.CELL_TYPE_STRING:
result = cell.getRichStringCellValue().getString();
break;
case Cell.CELL_TYPE_NUMERIC:
if(DateUtil.isCellDateFormatted(cell))
{
result = cell.getDateCellValue().toString();
}
else
result = Math.round(cell.getNumericCellValue())+"";
break;
case Cell.CELL_TYPE_FORMULA:
result = Math.round(cell.getNumericCellValue())+"";
break;
case Cell.CELL_TYPE_BLANK:
result = null;
break;
case Cell.CELL_TYPE_ERROR:
result = null;
break;
default:
result = null;
break;
}
}
return result;
}
}
扫一扫
472
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
