Java生成excel文件 JXL

要使用的jar包 jxl-2.5.7.jar

 

 

 

import java.io.File;
import java.io.IOException;
import java.net.URL;
import java.util.List;


import org.apache.commons.lang.StringUtils;
import org.apache.log4j.Logger;
import jxl.Workbook;
import jxl.format.UnderlineStyle;
import jxl.write.DateFormat;
import jxl.write.Label;
import jxl.write.NumberFormat;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;

public class JXLUtil {

    private final static Logger log = Logger.getLogger(JXLUtil.class);
    
    public final static  String[] proptitles = new String[]{"酒店ID","中文名称","所属品牌","中文地址","英文名称 ","英文地址","星级","钻级","省份","城市","邮编","纬度","经度","电话","传真","开业时间","装修时间","房间总数","总楼层","电梯数","激活状态","网站","是否接受外宾","酒店简介","商圈"};
    
  
    
    private static String path = null;
    
    static {
        URL url = Thread.currentThread().getContextClassLoader().getResource(""); 
        path = url.getPath();
        int index = path.lastIndexOf("WEB-INF/classes/");
        if(index>0){
            path = path.substring(0, index);
        }
        File file = new File(path +"props");
        if(!file.exists()){
            file.mkdirs();
        }
        
    }

    /**
     * 酒店基础信息生成excel文件
     * @param filename 文件名
     * @param sheetName  工作表名称
     * @param titles 列名
     * @param cards 数据
     * @return 
     */
    public static String export2TaPropExcel(String filename,String sheetName,String[] titles,List<TaProp> props){
        WritableWorkbook workbook = null;
        filename = filename+".xls";
        String pathfilename =  path + filename;
        String  exportpath=FogTaskConfig.getProperty("FogTask.TaPropsRoomTask.path");
        if(StringUtils.isNotEmpty(exportpath)){
        	pathfilename=exportpath;
        }else{
        	pathfilename=path;
        }
        try{
            File tempFile= new File(pathfilename);
            workbook = Workbook.createWorkbook(tempFile);   
            WritableSheet sheet = workbook.createSheet(sheetName, 0);
              
            //
            Label lable=null;   
            jxl.write.Number number=null;   
            jxl.write.DateTime date=null;   
            
            //format
            WritableFont titleFont = new WritableFont(WritableFont.ARIAL, 10, WritableFont.BOLD,
                    false, UnderlineStyle.NO_UNDERLINE, jxl.format.Colour.getInternalColour(9));
            WritableCellFormat titleFormat = new WritableCellFormat (titleFont);    
            titleFormat.setAlignment(jxl.format.Alignment.CENTRE);
            titleFormat.setBackground(jxl.format.Colour.getInternalColour(56));
            
            WritableFont detFont = new WritableFont(WritableFont.ARIAL, 10, WritableFont.NO_BOLD, 
                    false, UnderlineStyle.NO_UNDERLINE, jxl.format.Colour.BLACK);    
            WritableCellFormat contentFormat = new WritableCellFormat (detFont);    
            contentFormat.setAlignment(jxl.format.Alignment.CENTRE);
            
            DateFormat df=new DateFormat("yyyy-MM-dd");//用于日期的   
            WritableCellFormat dateFormat = new WritableCellFormat (detFont, df);    
            dateFormat.setAlignment(jxl.format.Alignment.CENTRE);
            
            NumberFormat nm = new NumberFormat("0");
            WritableCellFormat numberFormat = new WritableCellFormat (detFont, nm);    
            numberFormat.setAlignment(jxl.format.Alignment.CENTRE);
              
             //add Title   
             for (int i = 0; i < titles.length; i++) {
                 lable=new Label(i, 0, titles[i], titleFormat);   
                 sheet.addCell(lable);
                 sheet.setColumnView(i, 20);
             }
               
             //add detail
             for (int i = 0; i < props.size(); i++) {
                 int column=0;   
                 TaProp prop = props.get(i);
                 lable=new Label(column++, i+1, prop.getProp(), contentFormat);   
                 sheet.addCell(lable);  
                 lable=new Label(column++, i+1, prop.getProp_name_zh(), contentFormat);   
                 sheet.addCell(lable);  
                 lable = new Label(column++, i+1, prop.getOrgid(),contentFormat);
                 sheet.addCell(lable);  
                 lable = new Label(column++, i+1, prop.getAddress1_zh(),contentFormat);
                 sheet.addCell(lable);  
                 lable = new Label(column++, i+1, prop.getProp_name_en(),contentFormat);
                 sheet.addCell(lable); 
                 lable = new Label(column++, i+1, prop.getAddress1_en(),contentFormat);
                 sheet.addCell(lable); 
                 lable=new Label(column++, i+1, prop.getStar_rating(), contentFormat);   
                 sheet.addCell(lable);  
                 lable=new Label(column++, i+1, prop.getDiamond_rating(), contentFormat);   
                 sheet.addCell(lable);  
                 lable=new Label(column++, i+1, prop.getLname(), contentFormat);   
                 sheet.addCell(lable);  
                 lable=new Label(column++, i+1, prop.getName_zh(), contentFormat);   
                 sheet.addCell(lable); 
                 lable=new Label(column++, i+1, prop.getZip(), contentFormat);   
                 sheet.addCell(lable); 
                 lable=new Label(column++, i+1, prop.getLatitude(), contentFormat);   
                 sheet.addCell(lable); 
                 lable=new Label(column++, i+1, prop.getLongitude(), contentFormat);   
                 sheet.addCell(lable); 
                 lable=new Label(column++, i+1, prop.getPhone(), contentFormat);   
                 sheet.addCell(lable); 
                 lable=new Label(column++, i+1, prop.getFax(), contentFormat);   
                 sheet.addCell(lable); 
                 lable=new Label(column++, i+1, prop.getOpening_date(), contentFormat);   
                 sheet.addCell(lable); 
                 lable=new Label(column++, i+1, prop.getRenovated_date(), contentFormat);   
                 sheet.addCell(lable); 
                 lable=new Label(column++, i+1, prop.getTotal_rooms(), contentFormat);   
                 sheet.addCell(lable); 
                 lable=new Label(column++, i+1, prop.getTotal_floors(), contentFormat);   
                 sheet.addCell(lable); 
                 lable=new Label(column++, i+1, prop.getTotal_elevators(), contentFormat);   
                 sheet.addCell(lable); 
                 lable=new Label(column++, i+1, prop.getStatus(), contentFormat);   
                 sheet.addCell(lable); 
                 lable=new Label(column++, i+1, prop.getWebsite(), contentFormat);   
                 sheet.addCell(lable); 
                 lable=new Label(column++, i+1, prop.getIs_welcome_foreign(), contentFormat);   
                 sheet.addCell(lable); 
                 lable=new Label(column++, i+1, prop.getSimple_desc_zh(), contentFormat);   
                 sheet.addCell(lable); 
                 lable=new Label(column++, i+1, prop.getTradearea_zh(), contentFormat);   
                 sheet.addCell(lable); 
            }
               
        }catch (Exception e) {
            filename = null;
            log.error("writeCard error :"+e);
        }finally{
            if(workbook!=null){
                try {
                    workbook.write();
                    workbook.close();
                }catch (WriteException e) {
                    
                }catch (IOException e) {
                    
                }  
            }
            return filename;
        }
    }
    
}