/*
* 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();
}
}
}