用户注册



邮箱:

密码:

用户登录


邮箱:

密码:
记住登录一个月忘记密码?

发表随想


还能输入:200字
云代码 - java代码库

强制换行合并单元格自动换行

2013-08-26 作者: 云代码会员举报

[java]代码库

package com.projectcycle.second.report;
import java.io.BufferedInputStream;
import java.io.BufferedOutputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.io.UnsupportedEncodingException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.Hashtable;
import java.util.List;
import java.util.Map;
import java.util.Vector;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import jxl.Workbook;
import jxl.format.Alignment;
import jxl.format.UnderlineStyle;
import jxl.format.VerticalAlignment;
import jxl.write.Label;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableImage;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;

import org.apache.commons.lang.StringUtils;
import org.apache.struts.action.Action;
import org.apache.struts.action.ActionForward;

import com.projectcycle.business.utils.DBManager;

@SuppressWarnings("deprecation")
public class MonthDetailExcel extends Action {

	String titleName = "";
	@SuppressWarnings("unchecked")
	public void dumpExcel(HttpServletRequest request,
			HttpServletResponse response,String datasql, String excelType, 
			String fileName_, String[] heads, String[] param, String[] parame,String[]xy,String[] xye,Integer width,Integer height,Integer Pwidth,Integer Pheight) {
		DBManager db = new DBManager();
		OutputStream os=null;
		try {
			System.out.println("-----开始导出excel------");
			Date date = new Date();
			String dateStr = new SimpleDateFormat("yyyyMMddhhmmss").format(date);
			String pathDownload =request.getRealPath("/")+"download/excelTemp/";
			isChartPathExist(pathDownload);
			titleName=fileName_;
			String fileName = fileName_ + dateStr + ".xls";
			String pathOfExcel = pathDownload + fileName;
			String[]result =new String[2];
			result[0]=fileName;
			result[1]=pathOfExcel;
			String pathOfPicture = pathDownload + "company.png";
			List<Map<String,String>>datalist=new ArrayList<Map<String,String>>();
			db.newConnection();
			Vector vector = db.executeQueryVector3LowerCase(datasql.toString());
			if (vector.size()>0||!vector.isEmpty()) {
				for(int i=0;i<vector.size();i++){
					Hashtable ht =(Hashtable)vector.get(i);
					Map<String, String> map = new HashMap<String, String>();
					for(int j=0;j<param.length;j++){
						map.put(param[j], getStringValue((String) ht.get(param[j])));
					}
					datalist.add(map);
				}
			}
			 os= new FileOutputStream(pathOfExcel);
			if("1".equals(excelType)){//正常的grid
				CreateExcel(os, datalist,heads,param,parame);
			}
			
			response.setContentType("appliction/json;charset=utf-8");
			 response.getWriter().write(fileName+","+pathOfExcel);
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			db.closeConnection();
			if(os!=null){
				try {
					os.close();
				} catch (IOException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
		}
		
		 
	}
	/**
	 * 创建excel表格
	 * 
	 * @param os
	 * @param list
	 * @param heads
	 * @param param
	 * @param parame
	 */
	@SuppressWarnings("unchecked")
	public void CreateExcel(OutputStream os, List list, String[] heads,
			String[] param, String[] parame) {
		try {
			// 创建工作簿
			WritableWorkbook workbook = Workbook.createWorkbook(os);
			// 创建一页
			WritableSheet sheet = workbook.createSheet("第一页", 0);
			// 构造表头
			WritableCellFormat wcflist = new WritableCellFormat(); // 单元格定义
				wcflist.setAlignment(Alignment.CENTRE);
				wcflist.setVerticalAlignment(VerticalAlignment.CENTRE);
				wcflist.setBorder(jxl.format.Border.ALL,jxl.format.BorderLineStyle.THIN);
			WritableCellFormat wcflist1 = new WritableCellFormat(); // 单元格定义
				wcflist1.setAlignment(Alignment.LEFT);
				wcflist1.setVerticalAlignment(VerticalAlignment.CENTRE);
				wcflist1.setBorder(jxl.format.Border.ALL,jxl.format.BorderLineStyle.THIN);
				wcflist1.setWrap(true);
			// 加粗 10
			WritableFont font1 = new WritableFont(WritableFont.ARIAL,10,WritableFont.BOLD,false,UnderlineStyle.NO_UNDERLINE);
			WritableCellFormat newFormat1 = new WritableCellFormat(font1); // 单元格定义
				newFormat1.setAlignment(Alignment.CENTRE);
				newFormat1.setBackground(jxl.format.Colour.GRAY_25);
				newFormat1.setVerticalAlignment(VerticalAlignment.CENTRE);
				newFormat1.setBorder(jxl.format.Border.ALL,jxl.format.BorderLineStyle.THIN);
			WritableCellFormat newFormat = new WritableCellFormat(font1); // 单元格定义
				newFormat.setAlignment(Alignment.CENTRE);
				newFormat.setVerticalAlignment(VerticalAlignment.CENTRE);
				newFormat.setBorder(jxl.format.Border.ALL,jxl.format.BorderLineStyle.THIN);

			Label title = new Label(0, 0, titleName);
			sheet.mergeCells(0, 0, heads.length-1, 0);// 起始列,起始行,终止列,终止行
			title.setCellFormat(newFormat);
			sheet.addCell(title);
			sheet.setRowView(0, 600);
			Label head;
			for (int i = 0; i < heads.length; i++) {
				head = new Label(i, 1, heads[i]);
				head.setCellFormat(newFormat1);
				sheet.addCell(head);
				if (heads[i].length() * 2 < 20) {
					sheet.setColumnView(i, 20);
				} else {
					sheet.setColumnView(i, heads[i].length() * 2);
				}
				sheet.setRowView(1, 400);
				sheet.setRowView(2, 400);
			}
			String nameCell;
			String cellTemp = "";
			String deptName="";
			int k = 0;
			Label bodys;
			for (int j = 0; j < param.length; j++) {
				for (int i = 0; i < list.size(); i++) {
					Map<String, String> map = (Map<String, String>) list.get(i);
					String name = param[j];
					nameCell = (String) map.get(name);
					int w=0;
					String syjh=(String) map.get("syjh");
					deptName =(String) map.get("departmentname");
					String xyjh=(String) map.get("xyjh");
					if (param[j].equals("departmentname")) {
						if (deptName.equals(cellTemp)) {
							k++;
							if (i == list.size() - 1) {
								sheet.mergeCells(j, (i + 2 - k), j, (i +2));
								k = 0;
							}
						} else {
							cellTemp = deptName;
							sheet.mergeCells(j, (i+1 - k), j, (i+1));
							//System.out.println(k);
							k = 0;
						}
					}
					if((syjh.length())>=(xyjh.length())){
						w=syjh.length()/14;
					}else{
						w=xyjh.length()/14;
					}
					if(("syjh".equals(name))||("xyjh".equals(name))){
						nameCell=nameCell.replace(",", "\012");
					}
					bodys = new Label(j, i + 2, nameCell);
					if(("syjh".equals(name))||("xyjh".equals(name))){
						bodys.setCellFormat(wcflist1);
					}else{
						bodys.setCellFormat(wcflist);
					}
					
					sheet.addCell(bodys);
					sheet.setRowView(i + 2, (w+1)*400);
			}
			cellTemp = "";
			k = 0;
		}
			workbook.write();
			workbook.close();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (os != null) {
				try {
					os.close();
				} catch (IOException e) {
					e.printStackTrace();
				}
			}
		}
	}
	
	/**
	 * 下载的方法
	 * @param pathOfExcel
	 * @param fileName
	 * @param request
	 * @param response
	 * @return
	 */
	public ActionForward download(String pathOfExcel, String fileName,
			HttpServletRequest request, HttpServletResponse response) {

		try {
			request.setCharacterEncoding("iso-8859-1");
		} catch (UnsupportedEncodingException e1) {
			e1.printStackTrace();
		}

		BufferedInputStream bis = null;
		BufferedOutputStream bos = null;
		OutputStream os = null;
		InputStream is = null;
		String filePath = pathOfExcel;// 项目路径
		try {
			File downloadFile = new File(filePath);
			is = new FileInputStream(downloadFile);
			bis = new BufferedInputStream(is);
			os = response.getOutputStream();
			bos = new BufferedOutputStream(os);

			fileName = java.net.URLEncoder.encode(fileName, "UTF-8");// 处理中文文件名的问题
			fileName = new String(fileName.getBytes("UTF-8"), "GBK");// 处理中文文件名的问题

			response.reset();
			response.setCharacterEncoding("utf-8");
			response.setContentType("application/-excel");// 文件类型contenttype
			response.setHeader("Content-Disposition", "attachment; filename="
					+ fileName); // 关键部分,打开一个下载框

			int bytesRead = 0;
			byte[] buffer = new byte[8192];

			while ((bytesRead = bis.read(buffer, 0, 8192)) != -1) {
				bos.write(buffer, 0, bytesRead);
			}
			bos.flush();
			is.close();
			bis.close();
			os.close();
			bos.close();
		} catch (Exception e) {
		}
		System.gc();
		return null;
	}
	/**
	 * 删除图片
	 * @param pathOfPicture:图片的路径
	 */
	public void deleteImage(String pathOfPicture){

		try {
			File file = new File(pathOfPicture);
			if (file.exists()) {
				boolean d = file.delete();

				if (d) {
					System.out.print("删除成功!");
				} else {
					System.out.print("删除失败!");
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	 /**
	  * 判断文件夹是否存在,如果不存在则新建
	  * 
	  * @param chartPath
	  */
	 @SuppressWarnings("unused")
	private void isChartPathExist(String chartPath) {
	  File file = new File(chartPath);
	  if (!file.exists()) {
	   file.mkdirs();
	   // log.info("CHART_PATH="+CHART_PATH+"create.");
	  }
	 }
	 
	 public String getStringValue(String sv) { 
		return StringUtils.isBlank(sv)?"":sv;
	}
	public int getIntValue(String sv) { 
		return Integer.parseInt(StringUtils.isBlank(sv)?"0":sv);
	}
}


网友评论    (发表评论)


发表评论:

评论须知:

  • 1、评论每次加2分,每天上限为30;
  • 2、请文明用语,共同创建干净的技术交流环境;
  • 3、若被发现提交非法信息,评论将会被删除,并且给予扣分处理,严重者给予封号处理;
  • 4、请勿发布广告信息或其他无关评论,否则将会删除评论并扣分,严重者给予封号处理。


扫码下载

加载中,请稍后...

输入口令后可复制整站源码

加载中,请稍后...