jxl.jar 操作

/* 
 * Created on Aug 18, 2009 
 * 本例在实际项目中使用，基本的操作都可以满足，值得参考。
 * To change the template for this generated file go to 
 * Window>Preferences>Java>Code Generation>Code and Comments 
 */ 
package cn.kr.utils;  
 
import java.io.*;   
import java.util.List;

import cn.kr.base.Udownloadfile;

import jxl.*;   
import jxl.write.*;   
import jxl.write.Number;
 
/** 
 * @author Ken 
 * 
 * To change the template for this generated type comment go to 
 * Window>Preferences>Java>Code Generation>Code and Comments 
 */ 
public class CreateXLS {  
 /**
  * 访问统计
  * @param path
  * @param objectExcel
  * @return
  */
    public static void CreateView(String path, String fileName, List<Udownloadfile> objectExcel) {
       
     try {  
      //创建目录
      File file = new File(path);  
      if (!file.exists()) {
       file.mkdirs();
      }
      
            //open file.  
            WritableWorkbook book = Workbook.createWorkbook(new File(path+fileName));  
              
            //定义字体
            WritableFont font= new WritableFont(WritableFont.TIMES,16,WritableFont.BOLD);
            WritableCellFormat format = new WritableCellFormat(font);
            format.setAlignment(Alignment.CENTRE);
            //create Sheet named "Sheet_1". 0 means this is 1st page.  
            WritableSheet sheet = book.createSheet("访问统计", 0);
            Label labelTitle = new Label(0, 0, "访问统计", format);
            sheet.addCell(labelTitle);
            sheet.mergeCells(0,0,7,0);
            //define cell column and row in Label Constructor, and cell content write "test".  
            //cell is 1st-Column,1st-Row. value is "test".  
            Label labelFetcher = new Label(0, 1, "访问者");
            Label labelTime = new Label(1, 1, "访问时间");
            Label labelDownload = new Label(2, 1, "是否下载");       
            Label labelAuthor = new Label(3, 1, "作者");
            Label labelProduct = new Label(4, 1, "作品");
            Label labelItem = new Label(5, 1, "分集");
            Label labelProvider = new Label(6, 1, "提供者");
            Label labelWay = new Label(7, 1, "订阅形式");
            //add defined cell above to sheet instance.  
            sheet.addCell(labelFetcher);
            sheet.addCell(labelTime);
            sheet.addCell(labelDownload);
            sheet.addCell(labelAuthor);
            sheet.addCell(labelProduct);
            sheet.addCell(labelItem);
            sheet.addCell(labelProvider);
            sheet.addCell(labelWay);
              
            //create cell using add numeric. WARN:necessarily use integrated package-path, otherwise will be throws path-error.  
            //cell is 2nd-Column, 1st-Row. value is 789.123.  
            String fetcherValue = "";
            String timeValue = "";
            String downloadValue = "";
            String authorValue = "";
            String productValue = "";
            int itemValue = 0;
            String providerValue = "";
            String wayValue = "";
            //从第几行开始输入数据
            int i = 2;
            for (Udownloadfile download : objectExcel) {
             fetcherValue = download.getUuser().getMobileId();
                timeValue = KrDateUtil.ShortDate(download.getDownloadTime() );
                downloadValue = (download.getDownloaded() == 1 ? "是" : "否");
                authorValue = download.getArtistname();
                productValue = download.getProjectname();
                itemValue = download.getSetno() ;
                providerValue = download.getProvider();
                wayValue = (download.getOrderType()==1 ? "包月" : "计次");
       
       Label labelFetcherValue = new Label(0, i, fetcherValue);
                Label labelTimeValue = new Label(1, i, timeValue);
                Label labelDownloadValue = new Label(2, i, downloadValue);
                Label labelAuthorValue = new Label(3, i, authorValue);
                Label labelProductValue = new Label(4, i, productValue);
                Number numberItemValue = new Number(5, i, itemValue);
                Label labelProviderValue = new Label(6, i, providerValue);
                Label labelWayValue = new Label(7, i, wayValue);
                i++;
                sheet.addCell(labelFetcherValue);
                sheet.addCell(labelTimeValue);
                sheet.addCell(labelDownloadValue);
                sheet.addCell(labelAuthorValue);
                sheet.addCell(labelProductValue);
                sheet.addCell(numberItemValue);
                sheet.addCell(labelProviderValue);
                sheet.addCell(labelWayValue);
            }
            //jxl.write.Number number = new jxl.write.Number(0, 1, 1);
            //add defined cell above to sheet instance.  
            //sheet.addCell(number);  
              
            //add defined all cell above to case.  
            book.write();  
            //close file case.  
            book.close();
        } catch (Exception e) {  
            e.printStackTrace();  
        }
    }
 
 /**
  * 下载概要
  * @param objectExcel
  * @return
  */
    public static void CreateSummery(String path, String fileName, List<Object[]> objectExcel) {
       
     try {  
      //创建目录
      File file = new File(path);  
      if (!file.exists()) {
       file.mkdirs();
      }
      
            //open file.  
            WritableWorkbook book = Workbook.createWorkbook(new File(path+fileName));  
              
            //定义字体
            WritableFont font= new WritableFont(WritableFont.TIMES,16,WritableFont.BOLD);
            WritableCellFormat format = new WritableCellFormat(font);
            format.setAlignment(Alignment.CENTRE);
            //create Sheet named "Sheet_1". 0 means this is 1st page.  
            WritableSheet sheet = book.createSheet("下载概要", 0);
            Label labelTitle = new Label(0, 0, "下载概要", format);
            sheet.addCell(labelTitle);
            sheet.mergeCells(0,0,4,0);
            //define cell column and row in Label Constructor, and cell content write "test".  
            //cell is 1st-Column,1st-Row. value is "test".  
            //作品名称 分集ID 作者名称 提供者名称 下载次数
            Label labelProduct = new Label(0, 1, "作品名称");
            Label labelItem = new Label(1, 1, "分集ID");
            Label labelAuthor = new Label(2, 1, "作者名称");
            Label labelProvider = new Label(3, 1, "提供者名称");
            Label labelTimes = new Label(4, 1, "下载次数");
            //add defined cell above to sheet instance.  
            sheet.addCell(labelAuthor);
            sheet.addCell(labelProduct);
            sheet.addCell(labelItem);
            sheet.addCell(labelProvider);
            sheet.addCell(labelTimes);
              
            //create cell using add numeric. WARN:necessarily use integrated package-path, otherwise will be throws path-error.  
            //cell is 2nd-Column, 1st-Row. value is 789.123.  
            String productValue = "";
            int itemValue = 0;
            String authorValue = "";
            String providerValue = "";
            int timesValue = 0;
            //从第几行开始输入数据
            int i = 2;
            SysOutLog.logInfo("objectExcel=="+objectExcel);
            for (Object[] download : objectExcel) {
             
             productValue = (String) download[1];
             itemValue = Integer.parseInt(download[2].toString());
             authorValue = (String) download[3];
             providerValue = (String) download[5];
             timesValue = Integer.parseInt(download[6].toString());
             
             Label labelProviderValue = new Label(0, i, productValue);
             Number numberItemValue = new Number(1, i, itemValue);
                Label labelAuthorValue = new Label(2, i, authorValue);
                Label labelProductValue = new Label(3, i, providerValue);
                Number numberTimesValue = new Number(4, i, timesValue);
                i++;
                sheet.addCell(labelProductValue);
                sheet.addCell(numberItemValue);
                sheet.addCell(labelAuthorValue);
                sheet.addCell(labelProviderValue);
                sheet.addCell(numberTimesValue);
            }
            //jxl.write.Number number = new jxl.write.Number(0, 1, 1);
            //add defined cell above to sheet instance.  
            //sheet.addCell(number);  
              
            //add defined all cell above to case.  
            book.write();  
            //close file case.  
            book.close();
        } catch (Exception e) {  
            e.printStackTrace();  
        }
    
    }  
   
    /**
     * 下载详细
  * @param path
  * @param objectExcel
  * @return
  */
    public static void CreateDetail(String path, String fileName, List<Udownloadfile> objectExcel) {
       
     try {  
      //创建目录
      File file = new File(path);  
      if (!file.exists()) {
       file.mkdirs();
      }
      
            //open file.  
            WritableWorkbook book = Workbook.createWorkbook(new File(path+fileName));  
              
            //定义字体
            WritableFont font= new WritableFont(WritableFont.TIMES,16,WritableFont.BOLD);
            WritableCellFormat format = new WritableCellFormat(font);
            format.setAlignment(Alignment.CENTRE);
            //create Sheet named "Sheet_1". 0 means this is 1st page.  
            WritableSheet sheet = book.createSheet("下载详细", 0);
            Label labelTitle = new Label(0, 0, "下载详细", format);
            sheet.addCell(labelTitle);
            sheet.mergeCells(0,0,7,0);
            //define cell column and row in Label Constructor, and cell content write "test".  
            //cell is 1st-Column,1st-Row. value is "test".  
            //下载者 下载时间 作者 作品 分集 实际价格 提供者 订阅形式
            Label labelFetcher = new Label(0, 1, "下载者");
            Label labelTime = new Label(1, 1, "下载时间");
            Label labelAuthor = new Label(2, 1, "作者");
            Label labelProduct = new Label(3, 1, "作品");
            Label labelItem = new Label(4, 1, "分集");
            Label labelPrice = new Label(5, 1, "实际价格");
            Label labelProvider = new Label(6, 1, "提供者");
            Label labelWay = new Label(7, 1, "订阅形式");
            //add defined cell above to sheet instance.  
            sheet.addCell(labelFetcher);
            sheet.addCell(labelTime);
            sheet.addCell(labelAuthor);
            sheet.addCell(labelProduct);
            sheet.addCell(labelItem);
            sheet.addCell(labelPrice);
            sheet.addCell(labelProvider);
            sheet.addCell(labelWay);
              
            //create cell using add numeric. WARN:necessarily use integrated package-path, otherwise will be throws path-error.  
            //cell is 2nd-Column, 1st-Row. value is 789.123.  
            String fetcherValue = "";
            String timeValue = "";
            String authorValue = "";
            String productValue = "";
            int itemValue = 0;
            float priceValue = 0;
            String providerValue = "";
            String wayValue = "";
            //从第几行开始输入数据
            int i = 2;
            for (Udownloadfile download : objectExcel) {
             
             fetcherValue = download.getUuser().getMobileId();
             timeValue = KrDateUtil.ShortDate(download.getDownloadTime() );
             authorValue = download.getArtistname();
             productValue = download.getProjectname();
             itemValue = download.getSetno() ;
             priceValue = download.getRealPrice();
             providerValue = download.getProvider();
             wayValue = (download.getOrderType() == 1 ? "包月" : "计次");
             
       Label labelFetcherValue = new Label(0, i, fetcherValue);
                Label labelTimeValue = new Label(1, i, timeValue);
                Label labelAuthorValue = new Label(2, i, authorValue);
                Label labelProductValue = new Label(3, i, productValue);
                Number numberItemValue = new Number(4, i, itemValue);
                Number numberDownloadValue = new Number(5, i, priceValue);
                Label labelProviderValue = new Label(6, i, providerValue);
                Label labelWayValue = new Label(7, i, wayValue);
                i++;
                sheet.addCell(labelFetcherValue);
                sheet.addCell(labelTimeValue);
                sheet.addCell(labelAuthorValue);
                sheet.addCell(labelProductValue);
                sheet.addCell(numberItemValue);
                sheet.addCell(numberDownloadValue);
                sheet.addCell(labelProviderValue);
                sheet.addCell(labelWayValue);
            }
            //jxl.write.Number number = new jxl.write.Number(0, 1, 1);
            //add defined cell above to sheet instance.  
            //sheet.addCell(number);  
              
            //add defined all cell above to case.  
            book.write();  
            //close file case.  
            book.close();
        } catch (Exception e) {  
            e.printStackTrace();  
        }
    }
}