登录站点

用户名

密码

ReportAnywhere Excel文档导出程序

3已有 5104 次阅读  2013-02-02 00:27

ReportAnywhere Excel文档导出程序

       ReportAnywhere 是我自己写的一个小程序,这个程序致力于解决OA、MIS、网站、大型集成系统、PDM、Team center、SAP和物联网系统的Excel文档导出问题。在信息化的过程中,不可避免的会遇到客户要求“导出Excel文档”这一类的要求。但是由于每一个Excel的格式、样式有诸多不同、繁琐等问题,给程序员造成了很多烦恼。处于上述因素考虑,我编写的程序可能在某一个特殊的Excel格式中还不适合,可能会报错。但针对大多数情况,这个程序是完全兼容的。您必要时可以根据自己的需求进行特殊的处理和改进。但请您切记:不要用于任何尝试收费的、商业的用途,本程序最终所有权归作者本人所有。
       当前用于 
通过java操作excel表格的工具类库 这一目的手段有两种:1 jxl,一位日本程序员的开源类库。缺点是不能够支持Office 2003以上版本,对图片识别和处理能力仅限于PNG格式。2 POI, Apache POI是Apache软件基金会的开放源码函式库,POI提供API给Java程序对Microsoft Office格式档案读和写的功能。功能强悍、性能稳定。相对与jxl来讲,几乎占尽各方面优势。 ReportAnywhere 使用POI作为操作Excel的工具。
        

ReportAnywhere设计思路

          qq截图20130201214829.png

ReportBuilder

       这个类提供了向Excel中单个单元格写入的方法:writeInTemplate(),包括写入字符串和写入图片两种。加载模板的方法:loadTemplate() 。保存模版中的内容到一个新文件中的方法:SaveTemplate()。检查是否存在多sheet页情况的方法:checkAndAddSheet()。

ReportManager

       这个类提供了加载Xml配置文件的方法:loadXmlMapping()。4个存储Xml配置文件中信息的私有方法:List<GenericField> head(),Map<String,List<BodyField>> body(),List<GenericField> foot(),List<ImageField> image()。这4个方法以实体类作为存储的依据。处理外部系统数据流的方法:process(Map map)、processData(Stri
ng 
 jsonString)。 一个公开的接口:doReport(String excelURL,String xmlURL,String jsonString,String saveURL)和
一个专门用来处理 Json 字符串循环套嵌的静态类:NaturalDeserializer。

Entity 

       Entity提供了对应的实体。entity.BodyField:专门针对Xml配置文件的Body节点部分设计。entity.genericField
:针对Xml配置文件的Head节点、Foot节点设计。entity.ImageField:针对Xml配置文件的Image节点设计。以他们去存储各自对应的节点内容。

Config-reportMapping.xml

       整个程序最精彩的部分。在这里体现了模块化设计的思想、SOA的思想。在不改变程序源代码、不进行程序再次编译的基础上,通过配置外部文件去实现用户的功能。最大限度的实现了代码重用、提高系统运行效率。针对一些不入流的程序员将一个Team Center的报表程序写到了13000行+的代码量,到处出现重复的代码,让别人维护起来阵阵作呕,真心觉得他可以回家看孩子了,开发不适合他。
       在一个大型的系统中可能会出现要导出很多Excel文件的情况。在这种情况下你还要为每一个Excel文件的导出去写一个类吗?亲,累死你的。那么该如何解决这个问题呢?答案就是为每一个Excel模版配置一个Xml配置文件。有多少个Excel模版,就为他配多少个Xml配置文件,当然我们这里假定每一个Excel模版的格式都是不同的。
       我们将一个配置文件区分成这样的四个部分:头部-<Header>、身体-<Body name="Tap1" startRow="10" finishRow="22" sheetIndex="0">、脚部-<Footer>和图片部分-<Images>。在实际应用中主要存在这样两种特殊情况:1、Excel模版中存在多个<Body>属性,这种情况往往意味着您要在Excel中打印出多个Sheet页;2、Excel模版中要求写入多个图片,这种情况又分为:2.1、在第一个Sheet页中写入多个图片;2.2、有多个<Body>节点,要求在Excel中打印出多个Sheet页,每个Sheet页中写入一个图片。当然2.1和2.2这两种情况比较极端和少见了。针对这种极端情况,我的源代码中没有去实现,因为很少用上所以笔者偷懒了。如果您看懂了代码相信您自己动手更改完全没有问题。因为在我看来还是不太难的。
       情况2的多图片写入情况排除。在这个代码中只针对在Excel 第一个Sheet页写入一个图片的情况去讨论。程序的设计思路可以这样描述:从外部系统中接到一个输入流,和Xml配置文件中的定义内容做对比,找到Excel模版,写入。在Xml配置文件中

<Header> 节点下内容:
hf52v~)hx)0x5@s__l1ip4o.jpg
       <Header>节点下存在多个<Field>节点,每一个<Field>节点代表一个Excel中的单元格,其下的<name>节点代表外部系统中传入的Map流中Key值,这个Key值与这里的<name>节点属性做匹配,如果成功则在对应的单元格写入。<beginRow>节点和<beginCell>节点唯一确定了一个Excel模版的单元格。


<Body>节点下内容:
1.png
       name属性:表示这个<Body>节点的名称;startRow属性:表示这个<Body>节点的起始行; finishRow属性:表示这个<Body>节点的结束行;sheetIndex属性:表示这个<Body>节点要写在Excel模版中的第几个Sheet页中。默认是0,因为Excel中Sheet是从0开始算起的,这个要注意。由于Excel中的身体部分(Body)是作为循环去输出到Excel中的,所以这里的<beginCell>节点代表他的起始列。


<Footer>节点下内容:
2.png
 <Header> 节点 相同,不再熬述。


<Images>节点下内容:
3.png

这里面的内容您要自己尝试配置一下,同时看看代码中的注释就可以了解,这里不再针对他单独介绍。

源代码

ReportBulider.java

import java.awt.image.BufferedImage;
import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;

import javax.imageio.ImageIO;
import javax.imageio.stream.ImageInputStream;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFClientAnchor;
import org.apache.poi.hssf.usermodel.HSSFPatriarch;
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.poifs.filesystem.POIFSFileSystem;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;


public class ReportBuilder 
{
	//==============================================================================//
	private static Logger log = LoggerFactory.getLogger(ReportBuilder.class);
	FileOutputStream fileOutputStream = null;
	HSSFWorkbook workBook = null;
	HSSFSheet sheet = null;
	HSSFPatriarch patriarch = null;
	//==============================================================================//
	
	
	//==============================================================================//
	/**
	 * @用途:重载+1。加载一个已经存在的模板,将生成的内容保存到 workbook中
	 * @参数:String templateFile:指索要加载的模板的路径,如:"C:/Tamplates/texting-1.xls"
	 * @用法:templateFile: String templateFile_Name1 = "C:/Tamplates/texting-1.xls"               
	 * @author Yangcl
	 */
	public void loadTemplate(String templateURL)
	{
		if (templateURL == null || templateURL.trim().equals(""))
		{
			// 文件不能为空提示
			throw new RuntimeException("文件模板路径不能为空");
		}

		try
		{
			FileInputStream templateFile_Input = new FileInputStream(templateURL);			
			POIFSFileSystem fs = new POIFSFileSystem(templateFile_Input);
			workBook = new HSSFWorkbook(fs);
			sheet = workBook.getSheetAt(0);
		}
		catch (Exception e)
		{
			log.error("loadTemplate()方法异常,文件加载失败:", e.toString());
		}
	}
	public void loadTemplate(InputStream templateStream)
	{
		try
		{
			POIFSFileSystem fs = new POIFSFileSystem(templateStream);
			workBook = new HSSFWorkbook(fs);
			sheet = workBook.getSheetAt(0);
		}
		catch (Exception e)
		{
			log.error("loadTemplate()方法异常,文件加载失败:", e.toString());
		}
	}
	//==============================================================================//
	private void checkAndAddSheet(int sheetIndex)
	{
		if (sheetIndex + 1 > workBook.getNumberOfSheets())
		{
			for (int i = 0; i < sheetIndex + 1 - workBook.getNumberOfSheets(); i++)
			{
				sheet = workBook.createSheet();
			}
		}
		else
		{
			sheet = workBook.getSheetAt(sheetIndex);
		}
	}
	//==============================================================================//
	/**
	 * 写入非图片格式信息,重载+3
	 * 
	 * @描述:这是一个实体类,提供了相应的接口,用于操作Excel,在任意坐标处写入数据。
	 * @参数:String newContent:你要输入的内容 
	 * 		  int beginRow :行坐标,Excel从 0 算起 
	 * 		  int beginCol:列坐标,Excel从 0 算起         
	 * @author Yangcl
	 */
	public void writeInTemplate(String newContent, int beginRow, int beginCell)
	{
		sheet = workBook.getSheetAt(0);
		HSSFRow row = sheet.getRow(beginRow); 
		if (null == row)
		{
			row = sheet.createRow(beginRow);
		} 
		HSSFCell cell = row.getCell(beginCell); 
		if (null == cell)
		{
			cell = row.createCell(beginCell);
		} 
		cell.setCellType(HSSFCell.CELL_TYPE_STRING); 
		cell.setCellValue(newContent);
	}
	
	/**
	 * 写入非图片格式信息,针对Excel存在多个sheet的情况。
	 */
	public void writeInTemplate(int sheetIndex, String newContent,	int beginRow, int beginCell)
	{
		checkAndAddSheet(sheetIndex);
		HSSFRow row = sheet.getRow(beginRow);
		if (null == row)
		{ 
			row = sheet.createRow(beginRow);
		}
		HSSFCell cell = row.getCell(beginCell);
		if (null == cell)
		{ 
			cell = row.createCell(beginCell); 
		} 
		cell.setCellType(HSSFCell.CELL_TYPE_STRING); 
		cell.setCellValue(newContent);
	}
	//==============================================================================//
	/**
	 * 写入图片格式信息,重载+1
	 * 
	 * @param dx1:第一个cell开始的X坐           
	 * @param dy1:第一个cell开始的Y坐标            
	 * @param dx2:第二个cell开始的X坐标        
	 * @param dy2:第二个cell开始的Y坐标
	 * @param col1:图片的左上角放在第几个列cell (the column(o based); of the first cell)
	 * @param row1:图片的左上角放在第几个行cell (the row(o based); of the first cell)
	 * @param col2:图片的右下角放在第几个列cell (the column(o based); of the second cell)
	 * @param row2:图片的右下角放在第几个行cell (the row(o based); of the second cell)
	 *            
	 * @描述:这是一个实体类,提供了相应的接口,用于操作Excel,在任意坐标处写入数据。
	 * @参数: String imageFileURL:他接受一个外界传入的图片路径,图片以 *.jpeg 形式存在。
	 * @用法: ReportBuilder twi = new ReportBuilder(); 
	 *     	  String imageFileURL ="D:/workspace/Tamplates/1.jpeg"; 
	 *        twi.writeInTemplate(imageFileURL ,0,0, 0, 0, (short)6, 5, (short)8, 8);     
	 * @author Yangcl
	 */
	public void writeInTemplate(String imageFileURL, int dx1, int dy1, int dx2,int dy2, short col1, int row1, short col2, int row2)
	{
		BufferedImage bufferImage = null;

		// 写入图片格式信息
		try
		{
			ByteArrayOutputStream byteArrayOutputStream = new ByteArrayOutputStream();
			// 先把读入的图片放到第一个 ByteArrayOutputStream 中,用于产生ByteArray
			File fileImage = new File(imageFileURL);
			bufferImage = ImageIO.read(fileImage);
			ImageIO.write(bufferImage, "JPG", byteArrayOutputStream);
			System.out.println("ImageIO 写入完成");

			// 准备插入图片
			HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
			HSSFClientAnchor anchor = new HSSFClientAnchor(dx1, dy1, dx2, dy2,
					col1, row1, col2, row2);

			// 插入图片
			byte[] pictureData = byteArrayOutputStream.toByteArray();
			int pictureFormat = HSSFWorkbook.PICTURE_TYPE_JPEG;
			int pictureIndex = workBook.addPicture(pictureData, pictureFormat);
			patriarch.createPicture(anchor, pictureIndex);

		}
		catch (Exception e)
		{
			log.error("IO Erro:", e);
		}
		finally
		{
			if (fileOutputStream != null)
			{
				try
				{
					fileOutputStream.close();
				}
				catch (IOException io)
				{
					log.error(io.toString());
				}
			}
		}
	}
	/**
	 * 图片流写入
	 * @param imageInputStream
	 * @param dx1
	 * @param dy1
	 * @param dx2
	 * @param dy2
	 * @param col1
	 * @param row1
	 * @param col2
	 * @param row2
	 */
	public void writeInTemplate(ImageInputStream imageInputStream, int dx1,int dy1, int dx2, int dy2, short col1, int row1, short col2,	int row2)
	{
		BufferedImage bufferImage = null;
		// 写入图片格式信息
		try
		{
			ByteArrayOutputStream byteArrayOutputStream = new ByteArrayOutputStream();
			// 先把读入的图片放到一个 ByteArrayOutputStream 中,用于产生ByteArray
			bufferImage = ImageIO.read(imageInputStream);
			ImageIO.write(bufferImage, "JPG", byteArrayOutputStream);
			System.out.println("ImageIO 写入完成");

			// 准备插入图片
			HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
			HSSFClientAnchor anchor = new HSSFClientAnchor(dx1, dy1, dx2, dy2,
					col1, row1, col2, row2);

			// 插入图片
			byte[] pictureData = byteArrayOutputStream.toByteArray();
			int pictureFormat = HSSFWorkbook.PICTURE_TYPE_JPEG;
			int pictureIndex = workBook.addPicture(pictureData, pictureFormat);
			patriarch.createPicture(anchor, pictureIndex);
		}
		catch (Exception e)
		{
			log.error("IO Erro:", e);
		}
		finally
		{
			if (fileOutputStream != null)
			{
				try
				{
					fileOutputStream.close();
				}
				catch (IOException io)
				{
				}
			}
		}
	}
	//==============================================================================//
	/**
	 * 保存模板 
	 * @param templateFile
	 * @描述:这个方法用于保存workbook(工作薄)中的内容,并写入到一个Excel文件中
	 * @参数:String templateFile:取得已经保存的类模板 路径名称
	 * 
	 * @用法:templateFile:String templateFile_Name1 = "C:/Tamplates/texting-1.xls"
	 *        TemplateAdapter ta = new TemplateAdapter();
	 *        ta.SaveTemplate(templateFile_Name1);
	 */
	public void SaveTemplate(String templateFile)
	{
		try
		{
			// 建立输出流
			fileOutputStream = new FileOutputStream(templateFile);
			workBook.write(fileOutputStream);
		}
		catch (Exception e)
		{
			log.error("IO Erro", e);
		}
		finally
		{
			if (fileOutputStream != null)
			{
				try
				{
					fileOutputStream.close();
				}
				catch (IOException io)
				{
					log.error(io.toString());
				}
			}
		}
	}
	
}

ReportManager.java

package manager;

import java.io.File;
import java.lang.reflect.Type;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;

import org.dom4j.Document;
import org.dom4j.DocumentException;
import org.dom4j.Element;
import org.dom4j.Node;
import org.dom4j.io.SAXReader;

import com.google.gson.Gson;
import com.google.gson.GsonBuilder;
import com.google.gson.JsonArray;
import com.google.gson.JsonDeserializationContext;
import com.google.gson.JsonDeserializer;
import com.google.gson.JsonElement;
import com.google.gson.JsonObject;
import com.google.gson.JsonPrimitive;
import com.hongbo.antichem.ReportBuilder; 

import entity.BodyField;
import entity.GenericField;
import entity.ImageField;

public class ReportManager 
{
	private Document			document	= null;//org.dom4j.Document
	private ReportBuilder		builder; 
	private Element				bodyElement	= null;

	private List<GenericField>	heads		= null;
	private Map<String,List<BodyField>>		bodys		= null;
	private List<GenericField>	foots		= null;
	private List<ImageField>	image		= null;
	
	
	public ReportBuilder getBuilder()
	{
		return builder;
	}

	public void setBuilder(ReportBuilder builder)
	{
		this.builder = builder;
	}
	//==============================================================================//
	/**
	 * @描述:这个方法用于加载Xml配置文件,取得节点中的所有信息。
	 * @author Yangcl
	 */
	private void loadXmlMapping(String mappingURL)
	{
		File xmlConfigFile = new File(mappingURL);
		SAXReader saxReader = new SAXReader();
		try
		{
			document = saxReader.read(xmlConfigFile);//org.dom4j.Document
		}
		catch (DocumentException e)
		{ 
			e.printStackTrace();
		}

		Element rootElement = document.getRootElement();// 获取根节点:ReportMapping
		heads = head(rootElement);
		bodys = body(rootElement);
		foots = foot(rootElement);
		image = image(rootElement);
		
	}
	//==============================================================================//
	/**
	 * 分别遍历xml文件的head\body\foot\image 
	 */
	private List<GenericField> head(Element rootElement)
	{
		List<GenericField> headerList = new ArrayList<GenericField>(); 
		Element headerElement = rootElement.element("Header"); 
		for (Iterator iter = headerElement.elementIterator(); iter.hasNext();)
		{
			Element element = (Element) iter.next(); 
			// 取得Field节点下 <name>的值
			String xml_Name = element.elementTextTrim("name"); 
			String xml_Row = element.elementTextTrim("beginRow");
			String xml_Cell = element.elementTextTrim("beginCell");

			int intFieldRow = new Integer(xml_Row);
			int intFieldCell = new Integer(xml_Cell);

			// 放入实体类中
			GenericField gf = new GenericField();
			gf.setName(xml_Name); 
			gf.setBeginRow(intFieldRow);
			gf.setBeginCell(intFieldCell); 
			headerList.add(gf);
		}
		return headerList;
	}
	
	private Map<String,List<BodyField>> body(Element rootElement)
	{
		//存放Body节点。以其属性name为Key,节点List为Value
		Map<String,List<BodyField>> bodyMaps = new HashMap<String,List<BodyField>>();
		
		@SuppressWarnings("unchecked")
		List<Element> bodyEleList = rootElement.elements("Body");//<Body>节点list集合
		for(Iterator ite = bodyEleList.iterator();ite.hasNext();)//遍历其中的一个<Body>节点
		{	//<Body name="Tap1" startRow="10" finishRow="22" sheetIndex="2">
			Element element = (Element) ite.next();
			
			String bodyName = element.attribute("name").getValue();
			String startRow = element.attribute("startRow").getValue();
			String finishRow = element.attribute("finishRow").getValue();
			String sheetIndex = element.attribute("sheetIndex").getValue();
			String Key = bodyName + "/" + startRow + "/" + finishRow + "/" + sheetIndex;
			
			List<BodyField> bodylist = new LinkedList<BodyField>(); // 存储Body所有节点  
			for (Iterator iter = element.elementIterator(); iter.hasNext();)
			{
				Element ele = (Element) iter.next();
				String xml_Name = ele.elementTextTrim("name"); 
				String xml_Cell = ele.elementTextTrim("beginCell"); 
				
				int intFieldCell = new Integer(xml_Cell); 
				BodyField bf = new BodyField();			
				bf.setName(xml_Name); 
				bf.setBeginCell(intFieldCell);
				bodylist.add(bf); 
			}
			bodyMaps.put(Key, bodylist);
			
		} 
		return bodyMaps;
	}
	private List<BodyField> body1(Element rootElement)
	{
		List<BodyField> bodylist = new LinkedList<BodyField>();// 存储Body所有节点
		
		bodyElement = rootElement.element("Body"); 
		for (Iterator iter = bodyElement.elementIterator(); iter.hasNext();)
		{ 
			Element element = (Element) iter.next(); 
			String xml_Name = element.elementTextTrim("name"); 
			String xml_Cell = element.elementTextTrim("beginCell");//这个变量会用到
			
			int intFieldCell = new Integer(xml_Cell);
			
			BodyField bf = new BodyField();			
			bf.setName(xml_Name); 
			bf.setBeginCell(intFieldCell);
			bodylist.add(bf);
		} 
		return bodylist;
	}
	private List<GenericField> foot(Element rootElement)
	{
		List<GenericField> footlist = new LinkedList<GenericField>(); 
		Element footElement = rootElement.element("Footer");
		for (Iterator iter = footElement.elementIterator(); iter.hasNext();)
		{
			Element element = (Element) iter.next(); 
			String xml_Name = element.elementTextTrim("name"); 
			String xml_Row = element.elementTextTrim("beginRow");
			String xml_Cell = element.elementTextTrim("beginCell");

			// 转换为 int类型数据 
			int intFieldRow = new Integer(xml_Row);
			int intFieldCell = new Integer(xml_Cell);

			// 放入实体类中
			GenericField gf = new GenericField();
			gf.setName(xml_Name); 
			gf.setBeginRow(intFieldRow);
			gf.setBeginCell(intFieldCell);

			footlist.add(gf);
		}
		return footlist;
	}
	private List<ImageField> image(Element rootElement)
	{
		List<ImageField> imagelist = new LinkedList<ImageField>();
		Element imageElement = rootElement.element("Images");

		for (Iterator iter = imageElement.elementIterator(); iter.hasNext();)
		{
			Element element = (Element) iter.next();
			String xml_Name = element.elementTextTrim("name");
			//表示图片是以文本形式还是文件流形式写入
			String xml_ImageInputStream = element.elementTextTrim("imageInputStream");
			String xml_Dx1 = element.elementTextTrim("Dx1");
			String xml_Dy1 = element.elementTextTrim("Dy1");
			String xml_Dx2 = element.elementTextTrim("Dx2");
			String xml_Dy2 = element.elementTextTrim("Dy2");
			String xml_shortCol1 = element.elementTextTrim("shortCol1");
			String xml_row1 = element.elementTextTrim("row1");
			String xml_shortCol2 = element.elementTextTrim("shortCol2");
			String xml_row2 = element.elementTextTrim("row2");

			// 转换数据类型
			int intFieldDx1 = new Integer(xml_Dx1);
			int intFieldDy1 = new Integer(xml_Dy1);
			int intFieldDx2 = new Integer(xml_Dx2);
			int intFieldDy2 = new Integer(xml_Dy2);
			short intFieldCol1 = new Short(xml_shortCol1);
			int intFieldRow1 = new Integer(xml_row1);
			short intFieldCol2 = new Short(xml_shortCol2);
			int intFieldRow2 = new Integer(xml_row2);

			// 放入实体类中
			ImageField image = new ImageField();
			image.setName(xml_Name);
			image.setImageInputStream(xml_ImageInputStream);
			image.setDx1(intFieldDx1);
			image.setDy1(intFieldDy1);
			image.setDx2(intFieldDx2);
			image.setDy2(intFieldDy2);
			image.setShortCol1(intFieldCol1);
			image.setRow1(intFieldRow1);
			image.setShortCol2(intFieldCol2);
			image.setRow2(intFieldRow2);

			imagelist.add(image);
		}
		return imagelist;

	}
	//==============================================================================//
	
	private void process(Map map)
	{
		Iterator mapIter = map.entrySet().iterator();

		while (mapIter.hasNext())
		{
			Entry me = (Entry) mapIter.next();
			String name = (String) me.getKey(); // 取出外部map传入的 Key 值
			Object value = (Object) me.getValue();// 取出外部map传入的 value 值
//			System.out.println("Json Name = "+name);
			for (int i = 0; i < heads.size(); i++)
			{

				if (heads.get(i).getName().equals(name))
				{
					String values = (String) value;
					int beginRow = heads.get(i).getBeginRow();
					int beginCell = heads.get(i).getBeginCell();
					getBuilder().writeInTemplate(values, beginRow, beginCell);
					break;
				}
			}
			
			Iterator ite = bodys.entrySet().iterator();
			System.out.println(bodys.size());
			while(ite.hasNext())
			{
				Entry en = (Entry) ite.next();
				String Key = (String) en.getKey();//取出Body的属性名称 
				String [] s =Key.split("/");
				System.out.println(Key);//////////////////////////////////////
				
				String bodyName = s[0];
				String aa = s[1];
				String bb = s[2];
				String cc = s[3];
				int startRow = new Integer(aa); // 强制转化为 int
				int finishRow = new Integer(bb);
				int sheetIndex = new Integer(cc);
				
				List<BodyField> bodyListValue = (List<BodyField>) en.getValue();
				
				if(bodyName.equals(name))
				{
					int number = 1;//为Excel中的序号+1
					
					List<Map<String,String>> listValue = (List<Map<String,String>>) value;

					for (int a = 0; a < listValue.size(); a++)
					{
						Map<String, String> bodyMap = (Map<String, String>) listValue.get(a);
						Iterator bodyIter = bodyMap.entrySet().iterator();
						
						for(BodyField bodyField : bodyListValue)
						{
							if(bodyField.getName().equals("SerialNumber"))
							{
								String valueBody = Integer.toString(number);
								getBuilder().writeInTemplate(sheetIndex, valueBody, startRow - 1, 0);
							}
						}
						
						while (bodyIter.hasNext())
						{
							Entry meBody = (Entry) bodyIter.next();
							String nameBody = String.valueOf(meBody.getKey());
							String valueBody = String.valueOf(meBody.getValue());
							for (BodyField bodyField : bodyListValue)
							{
								if (bodyField.getName().equals(nameBody))
								{
									int beginCell = bodyField.getBeginCell();
									//getBuilder().writeInTemplate(valueBody, startRow - 1, beginCell);
									getBuilder().writeInTemplate(sheetIndex, valueBody, startRow - 1, beginCell);
								}
							} 
						}
						
//						if (startRow < finishRow)
//						{
//							startRow++;
//						}
						if(finishRow == 0)
						{
							startRow++;
							number++;
						}
						else if(finishRow > 0)
						{
							if(startRow < finishRow)
							{
								startRow++;
								number++;
							}
						}
						
						
						
					}
				}
				
			}
			for (int j = 0; j < foots.size(); j++)
			{
				if (foots.get(j).getName().equals(name))
				{
					String values = (String) value;
					int beginRow = foots.get(j).getBeginRow();
					int beginCell = foots.get(j).getBeginCell();
//					System.out.println("values:"+values+"\n");
//					System.out.println("beginRow:"+beginRow+"  beginCell:" + beginCell+"\n");
					
					getBuilder().writeInTemplate(values, beginRow, beginCell);
					break;
				}
	
			}
	
			for (int k = 0; k < image.size(); k++)
			{
				if (image.get(k).getName().equals(name))
				{
					String values = (String) value;
					int dx1 = image.get(k).getDx1();
					int dy1 = image.get(k).getDy1();
					int dx2 = image.get(k).getDx2();
					int dy2 = image.get(k).getDy2();
					short col1 = image.get(k).getShortCol1();
					int row1 = image.get(k).getRow1();
					short col2 = image.get(k).getShortCol2();
					int row2 = image.get(k).getRow2();
	
					getBuilder().writeInTemplate(values, dx1, dy1, dx2, dy2,col1, row1, col2, row2);
					break;
				}
			}
		}
	}
	
	private void processData(String jsonString)
	{
		GsonBuilder gbuilder = new GsonBuilder().setDateFormat("yyyy-MM-dd").registerTypeAdapter(Object.class, new NaturalDeserializer());
		Gson gson = gbuilder.create();
		Object natural = gson.fromJson(jsonString, Object.class);

		Map gsonMap = (Map) natural;
		// 解析并遍历Map
		process(gsonMap);

	}

	private static class NaturalDeserializer implements	JsonDeserializer<Object>
		{
			// 请参考:http://stackoverflow.com/questions/2779251/convert-json-to-hashmap-using-gson-in-java
			public Object deserialize(JsonElement json, Type typeOfT,
					JsonDeserializationContext context)
			{
				if (json.isJsonNull())
					return null;
				else if (json.isJsonPrimitive())
					return handlePrimitive(json.getAsJsonPrimitive());
				else if (json.isJsonArray())
					return handleArray(json.getAsJsonArray(), context);
				else
					return handleObject(json.getAsJsonObject(), context);
			}

			private Object handlePrimitive(JsonPrimitive json)
			{
				if (json.isBoolean())
					return json.getAsBoolean();
				else if (json.isString())
					return json.getAsString();
				else
				{
					BigDecimal bigDec = json.getAsBigDecimal();
					// Find out if it is an int type
					try
					{
						bigDec.toBigIntegerExact();
						try
						{
							return bigDec.intValueExact();
						}
						catch (ArithmeticException e)
						{

						}

						return bigDec.longValue();
					}
					catch (ArithmeticException e)
					{

					}
					// Just return it as a double
					return bigDec.doubleValue();
				}
			}

			private Object handleArray(JsonArray json,JsonDeserializationContext context)
			{
				Object[] array = new Object[json.size()];

				for (int i = 0; i < array.length; i++)
				{
					array[i] = context.deserialize(json.get(i), Object.class);
				}

				return array;
			}

			private Object handleObject(JsonObject json,JsonDeserializationContext context)
			{
				Map<String, Object> map = new HashMap<String, Object>();
				for (Entry<String, JsonElement> entry : json.entrySet())
				{
					map.put(entry.getKey(),
							context.deserialize(entry.getValue(), Object.class));
				}

				return map;
			}

		}
	
	public void doReport(String excelURL,String xmlURL,String jsonString,String saveURL)
	{
		ReportBuilder rb = new ReportBuilder(); 
		rb.loadTemplate(excelURL); 
		this.setBuilder(rb);
		this.loadXmlMapping(xmlURL); 
		this.processData(jsonString);
		rb.SaveTemplate(saveURL);
	}
	
}

实体类

public class BodyField 
{
	private String name;
	private int BeginCell;
	
	public int getBeginCell() 
	{
		return BeginCell;
	}

	public void setBeginCell(int beginCell) 
	{
		BeginCell = beginCell;
	}

	
	public String getName() 
	{
		return name;
	}

	public void setName(String name) 
	{
		this.name = name;
	}
 
}

ublic class GenericField 
{
	private String name;
	private int   beginRow;
	private int   beginCell;
	
	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
	public int getBeginRow() {
		return beginRow;
	}
	public void setBeginRow(int beginRow) {
		this.beginRow = beginRow;
	}
	public int getBeginCell() {
		return beginCell;
	}
	public void setBeginCell(int beginCell) {
		this.beginCell = beginCell;
	}
}

public class ImageField 
{
	private String   name;
	private String   imageInputStream;
	private int	Dx1;
	private int	Dy1;
	private int	Dx2;
	private int	Dy2;
	private int	row1;
	private int	row2;
	private	 short	shortCol1;
	private short	shortCol2;
	
	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
	public String getImageInputStream() {
		return imageInputStream;
	}
	public void setImageInputStream(String imageInputStream) {
		this.imageInputStream = imageInputStream;
	}
	public int getDx1() {
		return Dx1;
	}
	public void setDx1(int dx1) {
		Dx1 = dx1;
	}
	public int getDy1() {
		return Dy1;
	}
	public void setDy1(int dy1) {
		Dy1 = dy1;
	}
	public int getDx2() {
		return Dx2;
	}
	public void setDx2(int dx2) {
		Dx2 = dx2;
	}
	public int getDy2() {
		return Dy2;
	}
	public void setDy2(int dy2) {
		Dy2 = dy2;
	}
	public int getRow1() {
		return row1;
	}
	public void setRow1(int row1) {
		this.row1 = row1;
	}
	public int getRow2() {
		return row2;
	}
	public void setRow2(int row2) {
		this.row2 = row2;
	}
	public short getShortCol1() {
		return shortCol1;
	}
	public void setShortCol1(short shortCol1) {
		this.shortCol1 = shortCol1;
	}
	public short getShortCol2() {
		return shortCol2;
	}
	public void setShortCol2(short shortCol2) {
		this.shortCol2 = shortCol2;
	}	
}

测试类

package manager;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import com.google.gson.Gson;

/**
 * 这是一个单元测试类
 * @author user
 */
public class ActionTest 
{
	public static void main(String[] args) 
	{
		Map<String, Object> valueMap = new HashMap<String, Object>();
		valueMap.put("number", "100867721");
		valueMap.put("date", "2012/08/03");
		valueMap.put("storeNum", "99999999");
		valueMap.put("remarks", "中	华	人	民	共	和	国	万	岁	!!");
		valueMap.put("imageStream", "E:\\work\\workspace\\运行域\\Tamplates\\1.JPEG");
		valueMap.put("CustodyCaptain", "李军");
		valueMap.put("Storeman", "周勇潇");
		valueMap.put("BusinessAssistant", "李晨东");

		List<Map<String, String>> list1 = new ArrayList<Map<String, String>>();
		Map<String, String> bodyMap = new HashMap<String, String>();
		bodyMap.put("SerialNumber", "1");
		bodyMap.put("Name", "器材");
		bodyMap.put("BatchNumber", "ZZFH-0083299");
		bodyMap.put("Unit", "1");// 单位
		bodyMap.put("New", "1"); // 新品
		bodyMap.put("Prospect", "1"); // 堪品
		bodyMap.put("Defective", "1"); // 废品
		bodyMap.put("R-New", "1");
		bodyMap.put("R-Prospect", "1");
		bodyMap.put("R-Defective", "1");
		bodyMap.put("D-New", "1");// D :deducting,差异,扣除
		bodyMap.put("D-Prospect", "1");
		bodyMap.put("D-Defective", "1");
		bodyMap.put("remarks", "无");
		list1.add(bodyMap);
		Map<String, String> bodyMap1 = new HashMap<String, String>();
		bodyMap1.put("SerialNumber", "2");
		bodyMap1.put("Name", "器材");
		bodyMap1.put("BatchNumber", "2");
		bodyMap1.put("Unit", "2");// 单位
		bodyMap1.put("New", "2"); // 新品
		bodyMap1.put("Prospect", "2"); // 堪品
		bodyMap1.put("Defective", "2"); // 废品
		bodyMap1.put("R-New", "2");
		bodyMap1.put("R-Prospect", "2");
		bodyMap1.put("R-Defective", "2");
		bodyMap1.put("D-New", "2");// D :deducting,差异,扣除
		bodyMap1.put("D-Prospect", "2");
		bodyMap1.put("D-Defective", "2");
		bodyMap1.put("remarks", "无");
		list1.add(bodyMap1); 
		Map<String, String> bodyMap2 = new HashMap<String, String>();
		bodyMap2.put("SerialNumber", "3");
		bodyMap2.put("Name", "器材0000");
		bodyMap2.put("BatchNumber", "0");
		bodyMap2.put("Unit", "0");// 单位
		bodyMap2.put("New", "0"); // 新品
		bodyMap2.put("Prospect", "0"); // 堪品
		bodyMap2.put("Defective", "0"); // 废品
		bodyMap2.put("R-New", "0");
		bodyMap2.put("R-Prospect", "0");
		bodyMap2.put("R-Defective", "0");
		bodyMap2.put("D-New", "0");// D :deducting,差异,扣除
		bodyMap2.put("D-Prospect", "0");
		bodyMap2.put("D-Defective", "0");
		bodyMap2.put("remarks", "无000");
		list1.add(bodyMap2); 
		valueMap.put("Tap1", list1);
		 
		
		List<Map<String, String>> list2 = new ArrayList<Map<String, String>>();
		Map<String, String> bodyMap3 = new HashMap<String, String>();
		bodyMap3.put("SerialNumber", "3");
		bodyMap3.put("Name", "器材");
		bodyMap3.put("BatchNumber", "ZZFH-0083299");
		bodyMap3.put("Unit", "3");// 单位
		bodyMap3.put("New", "3"); // 新品
		bodyMap3.put("Prospect", "3"); // 堪品
		bodyMap3.put("Defective", "3"); // 废品
		bodyMap3.put("R-New", "3");
		bodyMap3.put("R-Prospect", "3");
		bodyMap3.put("R-Defective", "3");
		bodyMap3.put("D-New", "3");// D :deducting,差异,扣除
		bodyMap3.put("D-Prospect", "3");
		bodyMap3.put("D-Defective", "3");
		bodyMap3.put("remarks", "无");
		list2.add(bodyMap3);
		Map<String, String> bodyMap4 = new HashMap<String, String>();
		bodyMap4.put("SerialNumber", "4");
		bodyMap4.put("Name", "器材");
		bodyMap4.put("BatchNumber", "4");
		bodyMap4.put("Unit", "4");// 单位
		bodyMap4.put("New", "4"); // 新品
		bodyMap4.put("Prospect", "4"); // 堪品
		bodyMap4.put("Defective", "4"); // 废品
		bodyMap4.put("R-New", "4");
		bodyMap4.put("R-Prospect", "4");
		bodyMap4.put("R-Defective", "4");
		bodyMap4.put("D-New", "4");// D :deducting,差异,扣除
		bodyMap4.put("D-Prospect", "4");
		bodyMap4.put("D-Defective", "4");
		bodyMap4.put("remarks", "无");
		list2.add(bodyMap4);
		
		valueMap.put("Tap2", list2);
		
		Gson gson = new Gson();
		String jsonString = gson.toJson(valueMap); 
		System.out.println(jsonString);
		String excelURL = 	"E:\\work\\workspace\\运行域\\Tamplates\\TamplateTest-4.xls";
		String xmlURL = 	"E:\\work\\workspace\\运行域\\Tamplates\\config.xml";
		String saveURL = 	"E:\\work\\workspace\\运行域\\TamplatesUser\\test6.xls";
		ReportManager rm = new ReportManager();
		rm.doReport(excelURL, xmlURL, jsonString, saveURL);
	}
}

以下是依赖包
4.png
 


最新改进效果,解决多页面,多图片问题。


完整资源地址:http://download.csdn.net/detail/breatheryang/5294841










分享 举报

发表评论 评论 (4 个评论)

涂鸦板