<span style="font-family: Arial, Helvetica, sans-serif;"> /**</span> * response
* title 标题
* headers 表头
* dataset 数据
* pattern 日期类型格式
* fileName 文件名称
* @author Administrator
*
* @param <T>
*/
public class ExportExcel<T>{
@SuppressWarnings("unchecked")
public void exportExcel(HttpServletResponse response,String title, String[] headers, Collection<T> dataset, String pattern,String fileName){
// 声明一个工作薄
HSSFWorkbook workbook = new HSSFWorkbook();
// 生成一个表格
HSSFSheet sheet = workbook.createSheet(title);
// 设置表格默认列宽度为10个字节
sheet.setDefaultColumnWidth(20);
// 生成一个样式
HSSFCellStyle style = workbook.createCellStyle();
// 设置这些样式
//前景色彩
// style.setFillForegroundColor(HSSFColor.SKY_BLUE.index);
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.setAlignment(HSSFCellStyle.ALIGN_CENTER);
// 生成一个字体
HSSFFont font = workbook.createFont();
font.setColor(HSSFColor.VIOLET.index);
font.setFontHeightInPoints((short) 12);
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
// 把字体应用到当前的样式
style.setFont(font);
// 生成并设置另一个样式
HSSFCellStyle style2 = workbook.createCellStyle();
style2.setFillForegroundColor(HSSFColor.LIGHT_YELLOW.index);
style2.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
style2.setBorderBottom(HSSFCellStyle.BORDER_THIN);
style2.setBorderLeft(HSSFCellStyle.BORDER_THIN);
style2.setBorderRight(HSSFCellStyle.BORDER_THIN);
style2.setBorderTop(HSSFCellStyle.BORDER_THIN);
style2.setAlignment(HSSFCellStyle.ALIGN_CENTER);
style2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
// 生成另一个字体
HSSFFont font2 = workbook.createFont();
font2.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
// 把字体应用到当前的样式
style2.setFont(font2);
// 产生表格标题行
HSSFRow row = sheet.createRow(0);
for (int i = 0; i < headers.length; i++)
{
HSSFCell cell = row.createCell(i);
cell.setCellStyle(style);
HSSFRichTextString text = new HSSFRichTextString(headers[i]);
cell.setCellValue(text);
}
// 遍历集合数据，产生数据行
Iterator<T> it = dataset.iterator();
int index = 0;
while (it.hasNext()){
index++;
row = sheet.createRow(index);
T t = (T)it.next();
// 利用反射，根据javabean属性的先后顺序，动态调用getXxx()方法得到属性值
Field[] fields = t.getClass().getDeclaredFields();
int j = 0;
for (int i = 0; i < fields.length; i++){
Field field = fields[i];
String fieldName = field.getName();
String getMethodName = "get" + fieldName.substring(0, 1).toUpperCase() + fieldName.substring(1);
try{
@SuppressWarnings("rawtypes")
Class tCls = t.getClass();
Method getMethod = tCls.getMethod(getMethodName,new Class[]{});
Object value = getMethod.invoke(t, new Object[]{});
// 判断值的类型后进行强制类型转换
String textValue = null;
HSSFCell cell = row.createCell(i-j);
cell.setCellStyle(style2);
if(value != null) {
if (value instanceof Date){
Date date = (Date) value;
SimpleDateFormat sdf = new SimpleDateFormat(pattern);
textValue = sdf.format(date);
cell.setCellStyle(style2);
cell.setCellValue(textValue);
}else if(value instanceof Boolean){
textValue = "是";
boolean bValue = (Boolean)value;
if(!bValue){
textValue = "否";
}
cell.setCellStyle(style2);
cell.setCellValue(textValue);
}else{
// 其它数据类型都当作字符串简单处理
textValue = value.toString();
cell.setCellStyle(style2);
cell.setCellValue(textValue);
}
}else{
j++;
textValue = null;
}
// // 如果不是图片数据，就利用正则表达式判断textValue是否全部由数字组成
// if (textValue != null){
// Pattern p = Pattern.compile("^//d+(//.//d+)?$");
// Matcher matcher = p.matcher(textValue);
// if (matcher.matches()){
// // 是数字当作double处理
// cell.setCellValue(Double.parseDouble(textValue));
// }else{
// HSSFRichTextString richString = new HSSFRichTextString(textValue);
// HSSFFont font3 = workbook.createFont();
// font3.setColor(HSSFColor.BLUE.index);
// richString.applyFont(font3);
// cell.setCellValue(richString);
// }
// }
}catch (SecurityException e){
e.printStackTrace();
}catch(NoSuchMethodException e){
e.printStackTrace();
}catch (IllegalArgumentException e){
e.printStackTrace();
}catch (IllegalAccessException e){
e.printStackTrace();
}catch (InvocationTargetException e){
e.printStackTrace();
}finally{
// 清理资源
}
}
}
try{
OutputStream os = response.getOutputStream();
// 这个是弹出下载对话框的关键代码
response.setHeader("Content-disposition", "attachment;filename=" + URLEncoder.encode((((null == fileName) || ("".equals(fileName.trim()))) ? ((new Date().getTime()) + "") : fileName.trim()) + ".xls", "utf-8"));
workbook.write(os);
os.flush();
os.close();
}catch (IOException e){
e.printStackTrace();
}
}
}
以上为一个Class此方法继承上面的类使用，用作Controller<pre name="code" class="java"> //菜品信息 导出功能
@RequestMapping(value = "/dishes/exportExcel")
public void exportExcel(
@RequestParam(value = "ids", required = false) String[] ids,
@RequestParam(value = "currentPage", required = false) Integer currentPage,
@RequestParam(value = "mCode", required = false) String mCode,
@RequestParam(value = "mName", required = false) String mName,
@RequestParam(value = "mForShort", required = false) String mForShort,
@RequestParam(value = "dishesName", required = false) String dishesName,
String message2,
HttpSession session,
HttpServletResponse response) throws ParseException{
// DateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
// /*当前登陆用户，先获取userId*/
// MyUserDetails curUserDetails=this.getCurrentUser(session.getId());
// if(curUserDetails!=null){
// userId=curUserDetails.getId();
// }
// user = userService.getUserById(userId);
// String deptId = user.getBusinessDeptId();
//
List<Message> messageList = new ArrayList<Message>();
//
// salesCondition.setShoppingGuide(shoppingGuide);
// if(StringUtils.isNotBlank(startTime)){
// salesCondition.setStartTime(format.parse(startTime));
// }
// if(StringUtils.isNotBlank(endTime)){
// salesCondition.setEndTime(format.parse(endTime));
// }
// salesCondition.setDeptId(deptId);
//
messageList = messageService.findMessageForView(mName,mCode,mForShort,dishesName);
if(messageList != null && messageList.size() > 0){
//导出excel功能
ExportExcel<XlsDto> ex = new ExportExcel<XlsDto>();
//设置表头
String[] headers = {"编号","菜品名称","菜品分类","菜品单位","菜品价格","菜品优惠价","菜品会员价","菜品成本价","是否折扣","积分倍数","是否提成","提成金额","支持外卖","支持预订","排序号"};
//创建excel存储数据
List<XlsDto> dataset = new ArrayList<XlsDto>();
for(Message s : messageList){
XlsDto xl = new XlsDto();
xl.setMCode(s.getmCode());
xl.setMName(s.getmName());
xl.setDishesName(s.getDishesName());
xl.setUnitName(s.getUnitName());
xl.setMSale(s.getmSale());
xl.setMFavourable(s.getmFavourable());
xl.setMMember(s.getmMember());
xl.setMCost(s.getmCost());
xl.setMAgio(s.getmAgio());
xl.setMTimes(s.getmTimes());
xl.setMDeduct(s.getmDeduct());
xl.setMMoney(s.getmMoney());
xl.setMTakeOut(s.getmTakeOut());
xl.setMDestine(s.getmDestine());
xl.setMSort(s.getmSort());
dataset.add(xl);
}
SimpleDateFormat formatter = new SimpleDateFormat("yyyyMMdd");
String fileName = "菜品信息单"+formatter.format(new Date());
ex.exportExcel(response,"菜品信息单", headers, dataset, "yyyy-MM-dd",fileName);
message2 = "exportExcelSuccess";
// return messageList(currentPage,mCode, mName, mForShort, dishesName);
}else{
message2 = "exportExcelError";
// return messageList(currentPage,mCode, mName, mForShort, dishesName);
}
}Dao层方法为
<pre name="code" class="java"> @Transactional(propagation=Propagation.REQUIRED,readOnly=true)
public List<Message> findMessageForView(String mName,String mCode,String mForShort,String dishesName) {
StringBuffer hql=new StringBuffer();
hql.append(" from Message where 1=1");
Map<String,Object> params=new HashMap<String, Object>();
if(StringUtils.isNotBlank(mName)){
hql.append(" ");
hql.append("and mName like :mName");
params.put("mName", "%"+mName+"%");
}if(StringUtils.isNotBlank(mCode)){
hql.append(" ");
hql.append("and mCode like :mCode");
params.put("mCode", "%"+mCode+"%");
}if(StringUtils.isNotBlank(mForShort)){
hql.append(" ");
hql.append("and mForShort like :mForShort");
params.put("mForShort","%"+mForShort+"%");
}if(StringUtils.isNotBlank(dishesName)){
hql.append(" ");
hql.append("and dishesName like :dishesName");
params.put("dishesName", "%"+dishesName+"%");
}
List<Message> data=this.find(hql.toString(), params);
return data;
}条件设置，返回List集合用作条件导出。
2879
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
