ReportAnywhere Excel文档导出程序

Yangcl 6年前

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> </span> 相同,不再熬述。
<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/5047499