关于jxl导出excel文件的效率问题

Jianes 发布于 2017/12/01 09:07
阅读 366
收藏 0

最近做了一个导出excel的功能,用的是jxl工具导出。写了一个通用的工具方法、但是发现导出excel的时间太长,这样不符合要求。想问问有没有好点的优化..以下是工具方法:

package cn.delicacy.modules.finance.clientController;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import com.jfinal.plugin.activerecord.Record;
import cn.delicacy.base.BaseService;
import cn.delicacy.modules.common.annex.JDBC_SQL_SERVER;
import cn.delicacy.utils.DateUtils;
import jxl.Workbook;
import jxl.format.BorderLineStyle;
import jxl.format.VerticalAlignment;
import jxl.read.biff.BiffException;
import jxl.write.Label;
import jxl.write.WritableCellFormat;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;


/**
 * excel操作数据写入工具类
 * @author JC
 *
 */
public class ExcelUtil extends BaseService{
    
    public static ExcelUtil dao = new ExcelUtil();
    
    public List<Record> ExcelUnits(String user,String sub_id,String type){
        
        String[] parStrings = {user,sub_id,type};
        return BaseService.dao.getList("PRO_Excel_Unit_list", parStrings);
        
    }
    //读取数据库坐标和坐标对应参数
    public List<Record> UnitValue(String listCallName,String user,String cVenCode,String sub_id){
        
        String[] parStrings = {user,cVenCode,sub_id};
        return BaseService.dao.getList(listCallName, parStrings);
        
    }
    
    
    
    
    /**
     * 参数写入方法
     * @param listCallName 调用的存储过程名称
     * @param user    登陆账号
     * @param cVenCode 代理商编码
     * @param sub_id 模板id
     * @throws Exception 
     */
    public void writeValue(String listCallName,String user,String cVenCode,String sub_id,File file) throws Exception{
        
        query(sub_id, file);//先执行导出模板文件
        
//        List<Record> unit = ExcelUnits(user, sub_id, type);//抽取数据库模板坐标
        
        List<String[]> list = new ArrayList<String[]>();
        
        List<Record> unitValue = UnitValue(listCallName, user, cVenCode, sub_id);//抽取数据库模板坐标值的参数
        
        
        for (int i = 0; i < unitValue.size(); i++) {
            
            String zb = unitValue.get(i).get("excelUnit");//得到坐标
            String va = unitValue.get(i).get("exUnitValue");
            
            
            
            String[] map = {zb,va};
            
            list.add(map);

        }
        
        
        for (int i = 0; i < list.size(); i++) {
            
            String zb = list.get(i)[0];
            String va = list.get(i)[1];
//            String a  = value[0];
            String clos = ExcelCoord(zb);//最表进行x,y轴数字化坐标处理
            
            String y = clos.substring(0, clos.indexOf(","));//得到y轴坐标
            
            String x = clos.substring(clos.indexOf(",")+1);//得到x轴坐标
            
            int cloum = Integer.valueOf(y);//得到列
            
            int row = Integer.valueOf(x);//得到行
            
            ExcelOutToSQL(cloum, row, va, file);//写入导出对应的模板文件
        }
        
        
    }
    
    public void writeListValue(String listCallName,String user,String cCouCode,String sub_id,File file) throws Exception{
        
        if (file.isFile()) {
            
            List<Record> list = UnitValue(listCallName, user, cCouCode, sub_id);
            
            for (int i = 0; i < list.size(); i++) {
                
                String excelUnit = list.get(i).get("excelUnit");
                String exUnitValue = list.get(i).get("exUnitValue");
                
                String clos = ExcelCoord(excelUnit);//最表进行x,y轴数字化坐标处理
                
                String y = clos.substring(0, clos.indexOf(","));//得到y轴坐标
                
                String x = clos.substring(clos.indexOf(",")+1);//得到x轴坐标
                
                int cloum = Integer.valueOf(y);//得到列
                
                int row = Integer.valueOf(x);//得到行
                
                ExcelOutToSQL(cloum, row, exUnitValue, file);//写入导出对应的模板文件
                
                
            }
            
            
            
            
            
            
        }else{
            
            query(sub_id, file);//先执行导出模板文件
            List<Record> list = UnitValue(listCallName, user, cCouCode, sub_id);
            
            for (int i = 0; i < list.size(); i++) {
                
                String excelUnit = list.get(i).get("excelUnit");
                String exUnitValue = list.get(i).get("exUnitValue");
                
                String clos = ExcelCoord(excelUnit);//最表进行x,y轴数字化坐标处理
                
                String y = clos.substring(0, clos.indexOf(","));//得到y轴坐标
                
                String x = clos.substring(clos.indexOf(",")+1);//得到x轴坐标
                
                int cloum = Integer.valueOf(y);//得到列
                
                int row = Integer.valueOf(x);//得到行
                
                ExcelOutToSQL(cloum, row, exUnitValue, file);//写入导出对应的模板文件
                
                
            }
            
        }
        
        
        
        
    }
    
    
    
    
    /**
     * 获取excel坐标方法
     * @param coord excel坐标,例如:"B5"
     * @return  返回数字坐标
     */
    static public String ExcelCoord(String coord) {
        
        
        Map<Integer, String> map = new HashMap<Integer, String>();//定义表头列对应的坐标标识
        
        map.put(0, "A");
        map.put(1, "B");
        map.put(2, "C");
        map.put(3, "D");
        map.put(4, "E");
        map.put(5, "F");
        map.put(6, "G");
        map.put(7, "H");
        map.put(8, "I");
        map.put(9, "J");
        map.put(10, "K");
        map.put(11, "L");
        map.put(12, "M");
        map.put(13, "N");
        map.put(14, "O");
        map.put(15, "P");
        map.put(16, "Q");
        map.put(17, "R");
        map.put(18, "S");
        map.put(19, "T");
        map.put(20, "U");
        map.put(21, "V");
        map.put(22, "W");
        map.put(23, "X");
        map.put(24, "Y");
        map.put(25, "Z");
        
        
        List<Integer> list = new ArrayList<Integer>();
        
        StringBuffer sb = new StringBuffer();
        
        String row =coord.substring(1);//截取excel行坐标标识
        int X = Integer.valueOf(row)-1;
        
        String clu = coord.substring(0, 1);//截取excel列坐标标识
        
        /**
         * 依据给出的excel坐标,换取对应的数字坐标。例如:"B5"对应的坐标为(1,4)--excel的数字坐标从0,0开始数起
         */
        
        for (int i = 0; i < map.size(); i++) {
            
            Object obj = map.get(i);//获取列对应的字母
            
            //匹配字母得到对应的数字坐标
            if (obj.toString().equals(clu)) {
                
                sb.append(i);
                sb.append(",");
                sb.append(X);

                break;

            }
            
            
        }
        
        return sb.toString();
    
    }
    
    
    
    /**
     * 导出excel文件并写入数据
     * @param clo 列
     * @param row 行
     * @param value 写入指定坐标的参数
     * @param file 写入指定excel文件路径
     */
    public void ExcelOutToSQL(int clo,int row,Object value,File file) {
        
        try {
            
            Workbook workbook = Workbook.getWorkbook(file);//获取模板文件
            

            WritableWorkbook wb = workbook.createWorkbook(file, workbook);//创建写入模板
            
            WritableSheet sheet = wb.getSheet(0);//获取第一表格
            
            WritableCellFormat format = new WritableCellFormat();
            format.setVerticalAlignment(VerticalAlignment.CENTRE);
            //设置顶部边框线为实线(默认是黑色--也可以设置其他颜色)
            format.setBorder(jxl.format.Border.TOP, BorderLineStyle.MEDIUM);
            format.setBorder(jxl.format.Border.RIGHT, BorderLineStyle.MEDIUM);
            
            
            sheet.addCell(new Label(clo, row, ConvertToStr(value)));//写入对应的单元格
            
            wb.write();//对单元格写入数据
            wb.close();//关闭流、释放内存
//            workbook.close();
            
        } catch (BiffException | IOException | WriteException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        
        
    }
    
    
    
    /**
     * 获取数据库文件读写方法。
     * @param excelNum 文件编号
     * @param query 执行查询语句、指定查询条件能提高查询效率。例如:select excelFile from EX_ExcelTemplate where excelNum = "1"
     * @param file 生成文件路径
     * @throws Exception
     */
    public void query(String excelNum,File file) throws Exception {
        
        JDBC_SQL_SERVER jdbc = new JDBC_SQL_SERVER();//获取数据库连接
        
        Connection conn = jdbc.getConnection();
       
        try {
            // 用conn创建Statement对象类实例
            Statement sql_statement = conn.createStatement();

            // 执行查询,用ResultSet类的对象,返回查询的结果
            String query = "select excelFile from EX_ExcelTemplate where excelNum = '"+excelNum+"'";
            
            ResultSet result = sql_statement.executeQuery(query);
            
            System.out.println(result);

            // 处理结果
            while (result.next()) {
                
                InputStream excel = result.getBinaryStream("excelFile");//定义输入流得到数据库中的文件
                
                String ex = excel.toString();//转换为字符串
                
                long length = ex.length();//得到字符串长度
                
                int read = 0;//定义读写头,冲0开始读写
                
                byte[] ba = new byte[(int)length/7];//得到需要读写的字节长度
  
                
                //判断该路径下时候已存在已生成的文件,如果没有就生成
                if (!file.exists()) {
                    
                    file.createNewFile();
                    
                }
                
                
                FileOutputStream out = new FileOutputStream(file, true);//定义数据输出流,写入数据
                
                boolean go = true;
                
                
                while (go) {
                
                    //如果存在需要生成的数据,写入文件
                    while((read = excel.read(ba))!=-1) {
                        out.write(ba);    
                        
                    }
                    
                    go=false;
                    
                }
                
                //-----------刷新缓冲区、关闭流、节约内存开销--------------
                out.flush();//刷新缓冲区
                out.close();//关闭流
                excel.close();//关闭流
                
                
            }
            
            
           
        } catch (Exception e) {
            e.printStackTrace();
        } finally{
            
            conn.close();//关闭数据库连接..节约资源
            
        }
  
        
    }
    
    
    /**
     * 处理各种数据并全部转换为字符串类型(String)
     * @param value
     * @return
     */
    static public String ConvertToStr(Object value) {
        
        if(value instanceof Boolean){
          if ((Boolean)value) {
              return "是";
              
            }else {
                return "否";
            }
      }else if(value instanceof Date){
          
          
          if (DateUtils.getDate((Date)value)==null) {
            return "";
        }else {
             return DateUtils.getDate((Date)value);
        }
          
      }else if(value instanceof Double){
          
          Double dou = (Double)value;
          
          if (dou==null) {
            return "";
        }else {
            return String.valueOf(dou);
        }  
         
      }else if(value instanceof BigDecimal){
          
         Double big = Double.valueOf(value.toString());
         
         if (big==null) {
            return "";
        }else {
            return String.valueOf(big);
        }
      }else if(value instanceof Float
              ||value instanceof Long
              ||value instanceof Integer){
          
          Double all = Double.valueOf(value.toString());
          
          if (all==null) {
            return "";
        }else {
            return String.valueOf(all);
        }
      }else{
          
          if (value==null) {
            return "";
        }else {
            return String.valueOf(value);
        }
         
      }
    
    }
    

}

加载中
0
尚浩宇
尚浩宇

不要在线导出,加入后台任务。比如创建一个线程单独去跑导出,导出完成后发送邮件告知用户下载地址。

尚浩宇
尚浩宇
不用那么麻烦,就一个线程就好了,后台自己慢慢写,只要保证完事了发个邮件就好了
Jianes
Jianes
我是否应该创建两个线程、一个负责读、一个负责写。读和写是否应该做同步。
0
Jianes
Jianes

问题已经解决、读取模板参数后,在循环类给单元格设好参数sheet.addCell(new Label(cloum, row, exUnitValue)); 。然后在循环外直接调用写入方法wb.write(); wb.close();//关闭流、释放内存就可以了。测试了个大概178条数据库记录用时2秒。(红色部分就改动的代码)

/**
     * 参数写入方法
     * @param listCallName 调用的存储过程名称
     * @param user    登陆账号
     * @param cVenCode 代理商编码
     * @param sub_id 模板id
     * @throws Exception 
     */
    public void writeValue(String listCallName,String user,String cVenCode,String sub_id,File file) throws Exception{
        
        query(sub_id, file);//先执行导出模板文件
        
//        List<Record> unit = ExcelUnits(user, sub_id, type);//抽取数据库模板坐标
    
        
        List<Record> unitValue = UnitValue(listCallName, user, cVenCode, sub_id);//抽取数据库模板坐标值的参数
        
        Workbook workbook = Workbook.getWorkbook(file);//获取模板文件
        

        WritableWorkbook wb = workbook.createWorkbook(file, workbook);//创建写入模板
        
        WritableSheet sheet = wb.getSheet(0);//获取第一表格

        
        
        for (int i = 0; i < unitValue.size(); i++) {
            
            String zb = unitValue.get(i).get("excelUnit");//得到坐标
            String va = unitValue.get(i).get("exUnitValue");

            
            String clos = ExcelCoord(zb);//最表进行x,y轴数字化坐标处理
            
            String y = clos.substring(0, clos.indexOf(","));//得到y轴坐标
            
            String x = clos.substring(clos.indexOf(",")+1);//得到x轴坐标
            
            int cloum = Integer.valueOf(y);//得到列
            
            int row = Integer.valueOf(x);//得到行
            
            sheet.addCell(new Label(cloum, row, va));//把需要写入数据的单元格设入参数
    
        }
        
        wb.write();//对单元格写入数据
        wb.close();//关闭流、释放内存

        
        
    }

 

返回顶部
顶部