import static com.liferay.portal.kernel.util.PropsKeys.DL_HOOK_FILE_SYSTEM_ROOT_DIR;
import static com.liferay.portal.kernel.util.PropsUtil.get;
import static com.liferay.portal.kernel.util.StringPool.BACK_SLASH;
import static com.liferay.portal.kernel.util.StringPool.BLANK;
import static com.liferay.portal.kernel.util.StringPool.FORWARD_SLASH;
import static com.liferay.portal.kernel.util.StringPool.UTF8;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import org.apache.commons.fileupload.FileItem;
import org.apache.commons.fileupload.FileUploadException;
import org.apache.commons.fileupload.disk.DiskFileItemFactory;
import org.apache.commons.fileupload.servlet.ServletFileUpload;
import org.json.simple.JSONValue;
import com.liferay.portal.kernel.log.Log;
import com.liferay.portal.kernel.log.LogFactoryUtil;
import com.liferay.portal.kernel.util.StringPool;
import com.liferay.portal.kernel.util.Validator;
import com.liferay.util.servlet.ServletResponseUtil;
/**
* Servlet implementation class ExportOrUploadServlet
*/
public class ExportOrUploadServlet extends HttpServlet {
private static Log _log = LogFactoryUtil.getLog(ExportOrUploadServlet.class);
private static final long serialVersionUID = 1L;
/** 上传文件的目录 */
public static final String DOCUMENT_PATH = get(DL_HOOK_FILE_SYSTEM_ROOT_DIR).replaceAll(FORWARD_SLASH ,BACK_SLASH+BACK_SLASH);
public static final String UPLOAD_PATH = DOCUMENT_PATH.substring(0,DOCUMENT_PATH.lastIndexOf(BACK_SLASH)) + "\\fileupload\\";
/** 上传文件的大小 1GB*/
public static final long UPLOAD_FILE_MAX_SIZE = 1024*1024*1024;
private static Log log = LogFactoryUtil.getLog(ExportServlet.class);
/**
* @see HttpServlet#service(HttpServletRequest request, HttpServletResponse
* response)
*/
protected void service(HttpServletRequest request,
HttpServletResponse response) throws ServletException, IOException
{
request.setCharacterEncoding(UTF8);
response.setCharacterEncoding(UTF8);
if(ServletFileUpload.isMultipartContent(request)){
uploadFile(request, response);
}else{
exportFile(request, response);
}
}
/**
* 上载文件功能
* @param request
* @param response
* @throws IOException
*/
@SuppressWarnings("unchecked")
public void uploadFile(HttpServletRequest request, HttpServletResponse response) throws IOException{
Map<String, String> json = new HashMap<String, String>(2);
String tempDir = createFolder(UPLOAD_PATH + getId());
DiskFileItemFactory dif = new DiskFileItemFactory();
ServletFileUpload sfu = new ServletFileUpload(dif);
File serverSideFile = null;
sfu.setSizeMax(UPLOAD_FILE_MAX_SIZE);
sfu.setHeaderEncoding(UTF8);
try
{
String fieldName;
List<FileItem> list = sfu.parseRequest(request);
Iterator<FileItem> iterator = list.iterator();
FileItem item = null;
String name = BLANK;
boolean hasNext = iterator.hasNext();
while(hasNext){
item = iterator.next();
if(!item.isFormField()){
name = item.getName().trim();
name = name.substring(name.lastIndexOf(BACK_SLASH)+1,name.length());
if(name.length()== 0) {
break;
}
serverSideFile = new File(tempDir, name);
item.write(serverSideFile);
item.delete();
}else{
fieldName = item.getFieldName();
request.setAttribute(fieldName, item.getString());
}
hasNext = iterator.hasNext();
}
}catch (FileUploadException e)
{
log.error(" 文件上传失败,用户中止上传! "+ e.toString());
json.put("flag", "ERROR");
}catch (Exception e) {
log.error(" - failed to upload file - ", e);
json.put("flag", "ERROR");
}
log.info("文件已成功上传到 "+ serverSideFile.getAbsolutePath());
json.put("flag", "SUCCESS");
json.put("filepath", serverSideFile.getAbsolutePath());
response.getWriter().write(JSONValue.toJSONString(json));
request.setAttribute("filepath", serverSideFile.getAbsolutePath());
}
/**
* 导出功能
* @param request
* @param response
*/
@SuppressWarnings("unchecked")
private void exportFile(HttpServletRequest request, HttpServletResponse response){
File file = null;
final String suffix = ".xls";
try
{
String param = request.getParameter("param");
Map<String,Object> jsonStr = (Map<String,Object>) JSONValue.parse(param);
//获取beanName
String beanName = (String) jsonStr.get("beanName");
//获取标识是否传入了用重写ExportServlet的标识对象
String exportTab = (String) jsonStr.get("exportTab");
//获取List数据中的键值
List<String> keyList = (List<String>) jsonStr.get("keyList");
//获取List数据中的键值的类型
List<String> dataTypeList = (List<String>) jsonStr.get("dataTypeList");
//获取表头
List<String> headers = (List<String>) jsonStr.get("headers");
//表格需要链接的对应字段
List<String> urlKeyList = (List<String>)jsonStr.get("urlKeyList");
//获取总的记录数
Long obj = (Long)jsonStr.get("records");
Integer records = Validator.isNull(obj) ? null : obj.intValue();
//获取查询条件
String condition = JSONValue.toJSONString((Map<String,Object>) jsonStr.get("condition"));
//构造查询参数
ExportModel exportParam = new ExportModel(headers,keyList,beanName,condition,records);
//导出文件的名字
String fileName = (String)jsonStr.get("exportFileName");
fileName = Validator.isNull(fileName) ? "表格详情" : fileName;
//创建临时文件
file = File.createTempFile(fileName+'_'+getCurrentTime(), suffix);
ExportTable table = null;
// if(Validator.isNotNull(exportTab)){
// table = new OverrideExportTable();
// }else{
table = new ExportTable();
// }
//通过POI组件生成Excel表格
table.generateTable(file, exportParam);
//发送文件到客户端
ServletResponseUtil.sendFile(request, response, fileName+'_'+getCurrentTime()+suffix, new FileInputStream(file));
}
catch (IOException e)
{
log.error("文件读写异常", e);
}
finally
{
file.deleteOnExit();
}
}
//创建文件夹，如果冲突使用GUID
public static String createFolder(String sFolder) {
File file=new File(sFolder);
try{
if(!file.exists()){
boolean state = file.mkdirs();
_log.info("mkdirs : " + state);
}else{
sFolder=sFolder+StringPool.UNDERLINE+getGUID();
createFolder(sFolder);
}
return sFolder;
}catch(Exception ex){
_log.error(ex.toString());
return null;
}
}
public static String getGUID(){
java.util.UUID uuid = java.util.UUID.randomUUID();
return uuid.toString();
}
/**
* 获取唯一的文件夹名称
*
* @date 2012-3-13 上午09:50:33
* @author yuanshifang
*
* @return
*/
public static String getId()
{
DateFormat dateFormat = new SimpleDateFormat("yyyyMMddHHmmssS");
return dateFormat.format(new Date());
}
private String getCurrentTime(){
DateFormat format = new SimpleDateFormat("yyyy_MM_dd_HH_mm_ss");
return format.format(new Date());
}
}
import java.util.List;
/**
* 导出参数封装类
* @author yuanshifang
*
*/
public class ExportModel
{
/** 表头 **/
private List<String> headers;
/** bean名 **/
private String beanName;
/** 查询条件 **/
private String condition;
/** 需要导出的记录数 **/
private Integer records;
/** 表格数据在List中的键名字 **/
private List<String> keyList;
/**
* 构造方法
* @param headers
* @param beanName
* @param condition
* @param reconds
*/
public ExportModel(List<String> headers,List<String> keyList,String beanName ,
String condition,Integer records)
{
this.headers = headers;
this.keyList = keyList;
this.beanName = beanName;
this.condition = condition;
this.records = records;
}
}
import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.List;
import java.util.Map;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFHyperlink;
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.hssf.util.HSSFColor;
import com.liferay.portal.kernel.log.Log;
import com.liferay.portal.kernel.log.LogFactoryUtil;
import com.liferay.portal.kernel.util.Validator;
/**
* 导出表格的数据
* @author yuanshifang
*
*/
public class ExportTable
{
private static Log log = LogFactoryUtil.getLog(ExportTable.class);
int maxsize = 60000; //excel的每个sheet最大只能6.5W
/**
* 对数据量大的数据，分页处理，用多个表格显示
*/
public void generateTable(File file, ExportModel exportParam)
{
String beanName = exportParam.getBeanName();
BaseStatisticDispose bean = (BaseStatisticDispose) ApplicationContextBeanUtil.getBean(beanName);
try {
List<Map<String, String>> list = bean.getDataForTable(0, exportParam.getRecords(), exportParam.getCondition());
int listSize = list.size();
int listnumber = listSize/maxsize;
//生成excel文档
HSSFWorkbook wk = new HSSFWorkbook();
for(int i=0;i<=listnumber ; i++){
generateTableForMore(file, exportParam, list, i,wk);
}
} catch (Exception e) {
log.error("数据查询异常 ", e);
}
}
public void generateTableForMore(File file,ExportModel exportParam,List<Map<String, String>> list,int number,HSSFWorkbook wk)
{
FileOutputStream os = null;
try
{
//获取bean对象并进行数据查询
os = new FileOutputStream(file);
// 生成工作区
HSSFSheet sheet = wk.createSheet("sheet" + number);
//生成表头
generateHeader(fillCellStyle(wk.createCellStyle()), sheet, exportParam);
//生成表格数据
generateDataCell(wk, sheet, list, exportParam,number);
//设置表格的默认宽度
sheet.setDefaultColumnWidth(30);
//设置表格的默认高度
sheet.setDefaultRowHeight((short) 600);
//将Excel表格输出
wk.write(os);
}
catch (FileNotFoundException e)
{
log.error("文件不存在异常 ", e);
}
catch (IOException ioe)
{
log.error("文件读写异常 ", ioe);
}
catch (Exception e)
{
log.error("数据查询异常 ", e);
}
finally
{
try
{
if (null != os)
{
os.close();
}
}
catch (IOException e)
{
log.error("关闭输出流异常 ", e);
}
}
}
/**
*
*
* @date 2011-12-1 下午02:22:40
* @author yuanshifang
*
* @param wk
* @return
*/
protected HSSFCellStyle fillCellStyle(HSSFCellStyle style)
{
//设置表格数据居中
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
//设置表格数据垂直居中
style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
//设置单元格填写充颜色
style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
//设置下边框为实线
style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
//设置左边框为实线
style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
//设置右边框为实线
style.setBorderRight(HSSFCellStyle.BORDER_THIN);
//设置上边框为实线
style.setBorderTop(HSSFCellStyle.BORDER_THIN);
// style.setWrapText(true);
//返回
return style;
}
/**
* 该方法用于数据导出时表格表头部分的生成
* @param wk
* @param sheet
* @param model
*/
protected void generateHeader(HSSFCellStyle style, HSSFSheet sheet, ExportModel model)
{
List<String>headers = model.getHeaders();
int lastRowIndex = sheet.getLastRowNum();
HSSFRow headerRow = sheet.createRow(lastRowIndex == 0 ? 0 : (lastRowIndex + 1));
style.setFillForegroundColor(HSSFColor.YELLOW.index);
HSSFCell cell = null;
int headersSize = headers.size();
for (int i = 0; i < headersSize; i++)
{
cell = headerRow.createCell(i);
cell.setCellValue(headers.get(i));
cell.setCellStyle(style);
}
headerRow.setHeight((short) 400);
}
/**
* 生成表格数据行
* @param wk
* @param sheet
* @param dataList
* @param model
*/
protected void generateDataCell(HSSFWorkbook wk, HSSFSheet sheet,
List<Map<String, String>> dataList, ExportModel model,int number){
List<String> dataTypeList = model.getDateTypeList();
List<String> keyList = model.getKeyList();
List<String> urlList = model.getUrlKeyList();
//创建普通单元格的样式
HSSFCellStyle style = fillCellStyle(wk.createCellStyle());
style.setFillForegroundColor(HSSFColor.LEMON_CHIFFON.index);
//创建带有Link 的单元格样式
HSSFCellStyle linkStyle = fillCellStyle(wk.createCellStyle());
linkStyle.setFillForegroundColor(HSSFColor.LEMON_CHIFFON.index);
//创建字体
HSSFFont font = wk.createFont();;
//设置链接的字体为蓝色
font.setColor(HSSFColor.BLUE.index);
//设置链接有下划线
font.setUnderline(HSSFFont.DEFAULT_CHARSET);
//使当前的字体生效
linkStyle.setFont(font);
//link
HSSFHyperlink link = null;
Map<String, String> temMap = null;
HSSFRow rowN = null;
int lastRowIndex = sheet.getLastRowNum();
int len = dataList.size();
HSSFCell cell = null;
int size = keyList.size();
Double doubleVal = 0.0;
for (int m = number * maxsize,i = 0; (m < len && m<(number+1) * maxsize); m++,i++)
{
temMap = dataList.get(i + number * maxsize);
rowN = sheet.createRow(lastRowIndex + i + 1);
rowN.setHeight((short) 400);
for(int j=0;j<size;j++){
cell = rowN.createCell(j);
cell.setCellStyle(style);
if(dataTypeList != null && !dataTypeList.isEmpty()){
//将表格的列数据类型 设置为数字
if(CommUtil.compareTo("db",dataTypeList.get(j))){
String value = temMap.get(keyList.get(j));
if(Validator.isNotNull(value)){
doubleVal = Double.valueOf(value);
}
cell.setCellValue(doubleVal);
//设置单元格的类型 为数字
cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
}else{ //将表格的列数据类型默认为字符串
cell.setCellValue(temMap.get(keyList.get(j)));
}
}else{
cell.setCellValue(temMap.get(keyList.get(j)));
}
//如果有需要链接的字段，则加上链接
if(Validator.isNotNull(urlList)
&& urlList.size() > j
&& Validator.isNotNull(urlList.get(j))
&& Validator.isNotNull(temMap.get(urlList.get(j))))
{
//创建带URL链接的单元格
link = new HSSFHyperlink(HSSFHyperlink.LINK_URL);
//设置单元格链接的地址
link.setAddress(temMap.get(urlList.get(j)));
//在当前的单元格上生效
cell.setHyperlink(link);
cell.setCellStyle(linkStyle);
}
}
}
if(len < 40)
{
for (int i = len; i < 40; i++)
{
rowN = sheet.createRow(sheet.getLastRowNum()+ 1);
for(int k=0;k<size;k++)
{
cell = rowN.createCell(k);
cell.setCellStyle(style);
}
}
}
}
}
扫一扫
2986
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
