页面表格成Execl

最新推荐文章于 2024-10-11 13:58:34 发布

jingliankui123

最新推荐文章于 2024-10-11 13:58:34 发布

阅读量104

点赞数

分类专栏： java 文章标签： WCF json C++ C# C

java 专栏收录该内容

29 篇文章 0 订阅

订阅专栏

ExcleBuild.java//生成Execl所要的类 package com.richsoft.sxdl.session.tools.graphics; import java.io.OutputStream; import java.util.ArrayList; import java.util.List; import jxl.Workbook; import jxl.WorkbookSettings; import jxl.format.Alignment; import jxl.format.Border; import jxl.format.BorderLineStyle; import jxl.format.VerticalAlignment; import jxl.write.Label; import jxl.write.WritableCellFormat; import jxl.write.WritableFont; import jxl.write.WritableSheet; import jxl.write.WritableWorkbook; import net.sf.json.JSONArray; import net.sf.json.JSONObject; import com.richsoft.sxdl.session.tools.graphics.Module.Position; /** * 生成Excle 封闭类 * @author Administrator * */ public class ExcleBuild { /** * 根据 json 串封装数组 * @param json * @return */ public List<Module> getList(String json){ List<Module> list = new ArrayList<Module>();//生成Excle所要的数据 JSONArray jons = JSONArray.fromObject(json); int col_length = 0; //2维数组长 int row_length = jons.size(); //2维数组宽 for(int i=0;i<jons.getJSONArray(0).size();i++){ JSONObject ojb = jons.getJSONArray(0).getJSONObject(i); col_length+=ojb.getInt("colspan"); } //根据table封装数据 String[][] table = new String[row_length][col_length];//把json数组转化为2维数组 /** * 给2维数组赋初始值 */ for(int i=0;i<table.length;i++){ for(int j=0;j<table[i].length;j++){ table[i][j]="#"; } } /** * 给2维数组赋值 */ for(int i=0;i<row_length;i++){ JSONArray array = jons.getJSONArray(i); int col_index = 0;//记录二维数组到那个例 for(int j=0;j<array.size();j++){ Module m = new Module(); JSONObject ojb = jons.getJSONArray(i).getJSONObject(j); String title = ojb.getString("title"); int col_num = ojb.getInt("colspan"); int row_num = ojb.getInt("rowspan"); m.setTitle(title); if(col_num>1 || row_num>1){//跨行或跨例 if(col_num>1 && row_num>1){//根据json数据中colspan和rowspan都大于1时就是即跨行，又跨例 for(int w=i;w<(i+row_num);w++){ for(int c=col_index;c<(col_index+col_num);c++){ if("#".equals(table[w][c])){//当二维数组还是初始值时（也就是/跨行或跨例没有影响这个单元格） m.addList(m.getPosition(w, c)); table[w][c] = title; }else{ for(int h=col_index;h<table.length;h++){ if(!"#".equals(table[w][h])){ continue; } m.addList(m.getPosition(w, h)); table[w][h]=title; break; } } } } col_index+=row_num; }else{//跨行或跨例 if(col_num>1){//只跨例时 for(int c=col_index;c<(col_index+col_num);c++){ if("#".equals(table[i][c])){ m.addList(m.getPosition(i, c)); table[i][c] = title; }else{ for(int h=col_index;h<table.length;h++){ if(!"#".equals(table[i][h])){ continue; } m.addList(m.getPosition(i, h)); table[i][h]=title; break; } } } col_index+=col_num-1;//当跨例时我们的两维数组的列只加了一个，所以还要把它跨几例也要加上 } if(row_num>1){//只跨行 for(int c=i;c<(i+row_num);c++){ if("#".equals(table[c][col_index])){ m.addList(m.getPosition(c, col_index)); table[c][col_index]= title; }else{ for(; col_index<table[i].length;col_index++){ if(!"#".equals(table[c][col_index])){ continue; }else{ m.addList(m.getPosition(c, col_index)); table[c][col_index] = title; break; } } } } } } }else{//没跨行也没有跨例 if("#".equals(table[i][col_index])){ m.addList(m.getPosition(i, col_index)); table[i][col_index] = title; }else{ for(;col_index<table[i].length;col_index++){ if(!"#".equals(table[i][col_index])){ continue; } m.addList(m.getPosition(i, col_index)); table[i][col_index] = title; break; } } } col_index++; list.add(m); } } return list; } /** * 生成成Excle * @param list 数据 * @param out 输出流 */ public static void writeTable(List<Module> list,OutputStream out){ try { WritableFont NormalFont = new WritableFont(WritableFont.ARIAL, 10); WritableFont BoldFont = new WritableFont(WritableFont.ARIAL, 14,WritableFont.BOLD); WritableFont tableFont = new WritableFont(WritableFont.ARIAL, 12,WritableFont.NO_BOLD); WritableFont baodanFont = new WritableFont(WritableFont.ARIAL, 10,WritableFont.BOLD); WritableCellFormat wcf_center = new WritableCellFormat(NormalFont); wcf_center.setBorder(Border.ALL, BorderLineStyle.THIN); //把垂直对齐方式指定为居中 wcf_center.setVerticalAlignment(VerticalAlignment.CENTRE); //把水平对齐方式指定为居中 wcf_center.setAlignment(Alignment.CENTRE); wcf_center.setWrap(true); WorkbookSettings setting = new WorkbookSettings();//对excl基础信息的设置 java.util.Locale locale = new java.util.Locale("zh", "CN"); setting.setLocale(locale); setting.setEncoding("utf-8"); // 打开文件 WritableWorkbook book = Workbook.createWorkbook(out,setting); WritableSheet sheet = book.createSheet("第一页", 0); // 写入数据并关闭文件 for (Module module : list) { List<Position> plist = module.getList(); if(plist.size()>1){ sheet.mergeCells(plist.get(0).getCol(),plist.get(0).getRow() , plist.get(plist.size()-1).getCol(), plist.get(plist.size()-1).getRow()); Label label = new Label(plist.get(0).getCol(),plist.get(0).getRow(),module.getTitle(),wcf_center); sheet.addCell(label); }else if(plist.size()==1){ Label label = new Label(plist.get(0).getCol(),plist.get(0).getRow() , module.getTitle(),wcf_center); sheet.addCell(label); } } book.write(); book.close(); } catch (Exception e) { e.printStackTrace(); } } }

Servlet

package com.richsoft.sxdl.session.tools.servlet;

import java.io.IOException;
import java.util.List;

import javax.servlet.ServletException;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import com.richsoft.sxdl.session.tools.graphics.ExcleBuild;
import com.richsoft.sxdl.session.tools.graphics.Module;

public class DowlaodExcle extends HttpServlet {

 private static final long serialVersionUID = 1L;

 public DowlaodExcle() {
  super();
 }

 public void destroy() {
  super.destroy(); 
 }

 public void doGet(HttpServletRequest request, HttpServletResponse response)
   throws ServletException, IOException {

            doPost(request, response);
 }

 
 
 public void doPost(HttpServletRequest request, HttpServletResponse response)
   throws ServletException, IOException {
  response.setContentType("application/vnd.ms-excel");
        response.setHeader("Content-Disposition", "attachment;filename=download.xls");
  String json = request.getParameter("json");
  ServletOutputStream out = response.getOutputStream();  
  ExcleBuild eb = new ExcleBuild();
  List<Module> list = eb.getList(json);
  eb.writeTable(list,out);
 }

 
 
 
 public void init() throws ServletException {
 }

}

package com.richsoft.sxdl.session.tools.graphics;

import java.util.ArrayList;
import java.util.List;

public class Module {
    
	private String title;
	
	List<Position> list = new ArrayList<Position>();
	
	public void addList(Position p){
		list.add(p);
	}
	
	public  Position getPosition(int row,int col){
		return new Position(row,col);
	}

	class Position {
		
		private int row;
		
		private int col;
        
		public Position() {
		}
		
		public Position(int row,int col) {
			this.row = row;
			this.col = col;
		}
		
		public int getRow() {
			return row;
		}

		public void setRow(int row) {
			this.row = row;
		}

		public int getCol() {
			return col;
		}

		public void setCol(int col) {
			this.col = col;
		}

	}

	public String getTitle() {
		return title;
	}

	public void setTitle(String title) {
		this.title = title;
	}

	public List<Position> getList() {
		return list;
	}

	public void setList(List<Position> list) {
		this.list = list;
	}
}

 function dowloadExcle(id){//id 是你表格id的名子
            var trs = $("tr",$("#"+id));
		      var str = "[";
		      $.each(trs,function(i,n){
	               var tds = $("td,th",$(n));
	               str+="["
	               $.each(tds,function(j,f){
	                    var rowspan = $(f).attr("rowspan")?$(f).attr("rowspan"):1;
	                    var colspan = $(f).attr("colspan")?$(f).attr("colspan"):1;
	                    str+="{'title':'"+fileScript($.trim($(f).text()))+"','rowspan':"+rowspan+",'colspan':"+colspan+"},";
	               });
	               if(tds.length>0)
	                    str=str.substring(0,str.length-1);
	               str+="],";
		      });
		      if(str.length>2)
		      str=str.substring(0,str.length-1);
		      str+="]";
       
       
       var html ="<form style='display:none;' id='excleForm' action='"+path+"/tool/DowlaodExcle' method='post'>";
           html+="<input type='hidden' id='exjson' name='json' value=\""+str+"\"/>";
           html+="</form>";
       if($("#excleForm").size()==0){
       	  $(document.body).append(html);
       }else{
          var obj = document.getElementById("exjson");
          if(obj){
             obj.value = str;
          }
       }
      
      document.getElementById("excleForm").submit();
 }

	 String json = "[" +
	 		"[{'title':'所有仓库','rowspan':3,'colspan':1},{'title':'所有货架','rowspan':3,'colspan':1},{'title':'工器具统计','rowspan':1,'colspan':7}]," +
	 		"[{'title':'工器具总数','rowspan':2,'colspan':1},{'title':'在库','rowspan':1,'colspan':4},{'title':'出库','rowspan':1,'colspan':2}]," +
	 		"[{'title':'正常','rowspan':1,'colspan':1},{'title':'损坏','rowspan':1,'colspan':1},{'title':'报废','rowspan':1,'colspan':1},{'title':'试验过期','rowspan':1,'colspan':1},{'title':'使用出库','rowspan':1,'colspan':1},{'title':'试验出库','rowspan':1,'colspan':1}]," +
	 		"[{'title':'山西仓库1','rowspan':2,'colspan':1},{'title':'未分配货架工器具','rowspan':1,'colspan':1},{'title':'8','rowspan':1,'colspan':1},{'title':'5','rowspan':1,'colspan':1},{'title':'3','rowspan':1,'colspan':1},{'title':'0','rowspan':1,'colspan':1},{'title':'0','rowspan':1,'colspan':1},{'title':'0','rowspan':1,'colspan':1},{'title':'0','rowspan':1,'colspan':1}]," +
	 		"[{'title':'货架总计','rowspan':1,'colspan':1},{'title':'8','rowspan':1,'colspan':1},{'title':'5','rowspan':1,'colspan':1},{'title':'3','rowspan':1,'colspan':1},{'title':'0','rowspan':1,'colspan':1},{'title':'0','rowspan':1,'colspan':1},{'title':'0','rowspan':1,'colspan':1},{'title':'0','rowspan':1,'colspan':1}]," +
	 		"[{'title':'仓库总数','rowspan':1,'colspan':2},{'title':'8','rowspan':1,'colspan':1},{'title':'5','rowspan':1,'colspan':1},{'title':'3','rowspan':1,'colspan':1},{'title':'0','rowspan':1,'colspan':1},{'title':'0','rowspan':1,'colspan':1},{'title':'0','rowspan':1,'colspan':1},{'title':'0','rowspan':1,'colspan':1}]" +
	 		"]";