首页 > Java开发 > WEB中以cvs格式导出数据

WEB中以cvs格式导出数据

实例为SSH项目,县公司导出售后报表的DEMO,先要导入jxl.jar:

Java代码  收藏代码
  1. import java.io.BufferedOutputStream;
  2. import java.io.File;
  3. import java.io.FileOutputStream;
  4. import java.lang.reflect.Field;
  5. import java.util.List;
  6. import jxl.Workbook;
  7. import jxl.format.Colour;
  8. import jxl.write.Label;
  9. import jxl.write.WritableCellFormat;
  10. import jxl.write.WritableSheet;
  11. import jxl.write.WritableWorkbook;
  12. /**
  13.  * 导出文件工具类
  14.  */
  15. public class ExportUtil {
  16.     /**
  17.      * 创建TXT或者CSV文件
  18.      *
  19.      * @param list 查询出的结果
  20.      * @param head 表头
  21.      * @param proerty 需要导出的列(与head对应)
  22.      * @param fileName 文件名
  23.      * @return
  24.      * @throws Exception
  25.      */
  26.     public static File createTxtFile(List list, List<String> head, List<String> proerty, String fileName) throws Exception {
  27.         File file = new File(fileName);
  28.         BufferedOutputStream out = new BufferedOutputStream(new FileOutputStream(file));
  29.         StringBuilder sb = new StringBuilder();
  30.         for (String str : head) {
  31.             sb.append("\t" + str + ",");
  32.         }
  33.         sb.append("\r\n");
  34.         Long row = 0L;
  35.         for (Object obj : list) {
  36.             Class className = obj.getClass();
  37.             // 反射所有字段
  38.             @SuppressWarnings("unused")
  39.             Field[] fields = className.getDeclaredFields();
  40.             for (String str : proerty) {
  41.                 // 若该字段是需要导出的字段则写入Excel
  42.                 Object o = ReflectUtils.getProertyValue(obj, str);
  43.                 String value = o == null ? "" : o.toString();
  44.                 // 设置cell的值
  45.                 sb.append("\t" + value + ",");
  46.             }
  47.             sb.append("\r\n");
  48.             if (row % 1000 == 0) {
  49.                 out.write(sb.toString().getBytes(LX100Constant.CHAR_SET));
  50.                 out.flush();
  51.                 sb = new StringBuilder();
  52.             }
  53.         }
  54.         out.write(sb.toString().getBytes(LX100Constant.CHAR_SET));
  55.         out.flush();
  56.         out.close();
  57.         return file;
  58.     }
  59.     /**
  60.      * 创建excel文件
  61.      *
  62.      * @param list
  63.      * @param head
  64.      * @param proerty
  65.      * @param fileName
  66.      * @return
  67.      * @throws Exception
  68.      */
  69.     public static File createExcel(List list, List<String> head, List<String> proerty, String fileName) throws Exception {
  70.         // 创建输出文件
  71.         File file = new File(fileName);
  72.         int line = 0;
  73.         int row = 0;
  74.         int sheetNum = 1;
  75.         // 表头格式
  76.         WritableCellFormat wcfF = new jxl.write.WritableCellFormat();
  77.         wcfF.setAlignment(jxl.format.Alignment.CENTRE);
  78.         wcfF.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);
  79.         @SuppressWarnings("unused")
  80.         Colour olour;
  81.         wcfF.setBackground(Colour.LIGHT_ORANGE);
  82.         WritableWorkbook wwb = Workbook.createWorkbook(file);
  83.         WritableSheet ws = wwb.createSheet("第" + sheetNum + "页", sheetNum);
  84.         // 设置冻结首行
  85.         ws.getSettings().setVerticalFreeze(1);
  86.         ws.getSettings().setFitWidth(100);
  87.         // 数据格式
  88.         WritableCellFormat dateDcfF = new jxl.write.WritableCellFormat();
  89.         dateDcfF.setWrap(true);
  90.         dateDcfF.setAlignment(jxl.format.Alignment.CENTRE);
  91.         dateDcfF.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);
  92.         // 控制列宽
  93.         ws.setColumnView(0, 10);
  94.         ws.setColumnView(1, 18);
  95.         ws.setColumnView(2, 18);
  96.         ws.setColumnView(3, 18);
  97.         ws.setColumnView(4, 18);
  98.         ws.setColumnView(5, 25);
  99.         ws.setColumnView(6, 18);
  100.         ws.setColumnView(7, 18);
  101.         ws.setColumnView(8, 18);
  102.         ws.setColumnView(9, 18);
  103.         ws.setColumnView(10, 18);
  104.         ws.setColumnView(11, 18);
  105.         ws.setColumnView(12, 18);
  106.         ws.setColumnView(13, 18);
  107.         ws.setColumnView(14, 18);
  108.         ws.setColumnView(15, 18);
  109.         sheetNum++;
  110.         // 写入表头
  111.         for (String str : head) {
  112.             Label label = new Label(line, 0, str, wcfF);
  113.             ws.addCell(label);
  114.             line++;
  115.         }
  116.         row++;
  117.         // 写入数据
  118.         for (Object obj : list) {
  119.             line = 0;
  120.             Class className = obj.getClass();
  121.             // 反射所有字段
  122.             Field[] fields = className.getDeclaredFields();
  123.             for (String str : proerty) {
  124.                 for (Field field : fields) {
  125.                     // 若该字段是需要导出的字段则写入Excel
  126.                     if (str.equals(field.getName())) {
  127.                         // 修改相应filed的权限
  128.                         boolean accessFlag = field.isAccessible();
  129.                         field.setAccessible(true);
  130.                         // 读取对象中相应的属性的值
  131.                         String value = field.get(obj).toString();
  132.                         if (str.equals("commendTime")) {
  133.                             value = field.get(obj).toString().substring(0, 19);
  134.                         }
  135.                         // 设置cell的值
  136.                         Label label = new Label(line, row, value, dateDcfF);
  137.                         ws.addCell(label);
  138.                         // 恢复相应field的权限
  139.                         field.setAccessible(accessFlag);
  140.                         line++;
  141.                     }
  142.                 }
  143.             }
  144.             row++;
  145.             // 行数超过10000行是数据放入下一个sheet
  146.             if (row % 10000 == 0) {
  147.                 // 设置标题格式
  148.                 line = 0;
  149.                 row = 0;
  150.                 ws = wwb.createSheet("第" + sheetNum + "页", sheetNum);
  151.                 // 设置冻结首行
  152.                 ws.getSettings().setVerticalFreeze(1);
  153.                 // 控制列宽
  154.                 ws.setColumnView(0, 10);
  155.                 ws.setColumnView(1, 18);
  156.                 ws.setColumnView(2, 18);
  157.                 ws.setColumnView(3, 18);
  158.                 ws.setColumnView(4, 18);
  159.                 sheetNum++;
  160.                 // 再次写入表头
  161.                 for (String str : head) {
  162.                     Label label = new Label(line, 0, str, wcfF);
  163.                     ws.addCell(label);
  164.                     line++;
  165.                 }
  166.                 row++;
  167.             }
  168.         }
  169.         // 写入数据并关闭文件
  170.         wwb.write();
  171.         wwb.close();
  172.         return file;
  173.     }
  174. }

POJO实体类

Java代码  收藏代码
  1. /**
  2.  * 售后信息
  3.  */
  4. public class FreeCallInfo {
  5.     //商品ID
  6.     private Long goodsId;
  7.     // 机型
  8.     private String goodsName;
  9.     //县公司Id
  10.     private Long cityId;
  11.     // 县公司名称
  12.     private String cityName;
  13.     // 分公司Id
  14.     private Long countyId;
  15.     / 分公司名称
  16.     private String countyName;
  17.     // 数量
  18.     private Long maintainNumber;
  19.     // 修复方式
  20.     private String maintainType;
  21.     //维修方式
  22.     private Long auditIsMaintain;
  23.     //get、set
  24. }

查找数据的方法:

Java代码  收藏代码
  1. public List<FreeCallInfo> query(Long cityId, Long countyId, Long mainType, String keyWord, String startTime, String endTime) {
  2.         Object[] objs = null;
  3.         StringBuilder sb = new StringBuilder(
  4.             " select gi.goods_name as goodsName, " +
  5.             "case" +
  6.              " when fm.audit_is_machine = 0 then" +
  7.              "  '维修'" +
  8.              " when fm.audit_is_machine = 1 then " +
  9.              "  '换机'" +
  10.              " when fm.audit_is_machine = 2 then " +
  11.              "  '丢失'" +
  12.            " end as maintainType," +
  13.                     "count(fm.goods_id) as maintainNumber" +
  14.               " from erp_freecall_monitor fm" +
  15.               " left join erp_goods_info gi" +
  16.               "   on fm.goods_id = gi.id" +
  17.               " left join erp_organisation oo" +
  18.               "   on oo.id = fm.org_id" +
  19.               " left join erp_dim_county dc" +
  20.               "   on oo.county_id = dc.county_id" +
  21.               " left join erp_dim_city cc" +
  22.               "   on cc.city_id = dc.city_id " +
  23.              " where fm.record_status = 2 ");
  24.         if (mainType == 3) {
  25.             objs = new Object[] { cityId, countyId, keyWord, startTime, endTime };
  26.             sb.append("and oo.city_id = ? and oo.county_id = ? and gi.goods_name like ? ");
  27.         } else {
  28.             objs = new Object[] { cityId, countyId, mainType, keyWord, startTime, endTime };
  29.             sb.append("and oo.city_id = ? and oo.county_id = ? and fm.audit_is_machine = ? and gi.goods_name like ? ");
  30.         }
  31.         sb.append("and to_char(fm.maintain_time, 'yyyy-MM-dd') >= ? " +
  32.           "and to_char(fm.maintain_time, 'yyyy-MM-dd') <= ? " );
  33.         sb.append("group by gi.goods_name, fm.audit_is_machine");
  34.         List list = (List) this.getJdbcTemplate().query(sb.toString(), objs,new RowMapperResultSetExtractor(new RowMapper() {
  35.           public Object mapRow(ResultSet res, int arg1) throws SQLException {
  36.             FreeCallInfo fc = new FreeCallInfo();
  37.             fc.setGoodsName(res.getString("goodsName"));
  38.             fc.setMaintainType(res.getString("maintainType"));
  39.             fc.setMaintainNumber(res.getLong("maintainNumber"));
  40.             return fc;
  41.           }
  42.         }));
  43.         return list;
  44.     }

页面的导出按钮:

Java代码  收藏代码
  1. <input type="button" value="导出" <s:if test="pagination.total == 0">disabled="disabled"</s:if>
  2.                                     onclick="window.location='<%=basePath%>afterSalesAction!countyExportMaintainInfo.action?mainType=${mainType}&keyWord=${keyWord}&startTime=${startTime}&endTime=${endTime}'" />

导出cvs的action:

Java代码  收藏代码
  1. /**
  2.      * 县公司导出售后维修报表
  3.      */
  4.     public String countyExportMaintainInfo() {
  5.         try {
  6.             freeCallInfolList = freeCallMonitorService.query(user.getOrganisation().getCityId(), user.getOrganisation().getCountyId(), Long.valueOf(mainType), keyWord, startTime, endTime);
  7.             String path = this.getServletContext().getRealPath("/");
  8.             fileName = new String("县公司售后维修报表统计.CSV".getBytes(LX100Constant.CHAR_SET), "ISO8859_1");
  9.             tmpFileName = "temp.csv";
  10.             List<String> head = new ArrayList<String>();
  11.             head.add("机型");
  12.             head.add("维修方式");
  13.             head.add("数量");
  14.             List<String> proerty = new ArrayList<String>();
  15.             proerty.add("goodsName");
  16.             proerty.add("maintainType");
  17.             proerty.add("maintainNumber");
  18.             File file = ExportUtil.createTxtFile(freeCallInfolList, head, proerty, path + tmpFileName);
  19.             inputStream = new FileInputStream(file);
  20.             log.info("用户导出县公司售后维修报表成功");
  21.         } catch (Exception e) {
  22.             log.error("用户导出县公司售后维修报表异常,创建文件异常{}", new Object[] { e });
  23.         }
  24.         return "export_success";
  25.     }

最后还要在sturts.xml中配置:

Java代码  收藏代码
  1. <result name="export_success" type="stream">
  2.             <param name="inputName">inputStream</param>
  3.             <param name="contentType">application/octet-stream</param>
  4.             <param name="contentDisposition">attachment;filename="${fileName}"</param>
  5.             <param name="bufferSize">500000</param>
  6.         </result>

本文固定链接: http://www.devba.com/index.php/archives/269.html | 开发吧

WEB中以cvs格式导出数据:目前有1 条留言

  1. 沙发
    无聊哥:

    好耶!

    2013-04-24 下午 12:24