倾其所有，请教如何优化Excle导出

Question

相关技术：oracle数据库
JAVA 语言
JXL导出Excle目前客户有一张表：分类，城市，名称，日期，金额，数量，...
AA    北京   A1   200905  50   30
AA    北京   A2   200804  40   25
AA    上海   A1   200904  40   20
...
AF    北京   A3   200905  44   33
AF    北京   A4   200904  44   33
AF    上海   A5   200903  40   30
...说明：
各“名称”属于某个“分类”；
“金额”、“数量”等属于统计类型，除了这两个外，还有其它的统计类型。客户要求：统计“分类”下面，各个“城市”里，按统计类型排序，取出最新“日期”的前20个“名称”，并取出这些“名称”的所有“日期”的数据；并导出Excle文件。
表中目前有30多万条数据，需要导出7、8万条数据；单个Excle文件列表如下：分类，城市，名称，200804，200805，...200905按金额统计
AA    北京  A1     40       44         50
AA    北京  A2     35       33         40
...按数量统计
AA    北京  A1     28        26        30
AA    北京  A2     20        23        25
...说明：
这是“AA”分类下的，“北京”城市的数据；
在这个“分类”和“城市”下，如果“200905”属于最新日期的话，按这个日期排序，取出前20个“名称”；然后取出这些“名称”的所有日期的数据；
除了“数量”是按“数量”从大到小排列外，其它统计类型都按“金额”排序。
因为“日期”在数据库里是一个字段，而导出时需要进行行列互换。
目前解决方法：1、按统计类型取值
按“统计类型”取出所有“分类”和所有“城市”下的前二十条数据；
用oracle的分析函数：rank() over(partition by "分类",“城市”，“日期” order by "金额" desc)按“分类”和“城市”进行划分，并取出最新“日期”中，按“统计类型”(如“金额”)排列的前20条数据。然后另一部分，根据上面这些数据，取出其所有日期的数据，并进行行列互换。
最后把这两部分进行 left join 连接，并导出Excle文件。单个Excle文件列表如下：分类，城市，名称，200804，200805，...200905AA    北京  A1     40       44         50
AA    北京  A2     35       33         40
...AA    上海  A1     28        26        30
AA    上海  A2     20        23        25
...上面列表全部按“金额”统计，会列出所有相关的“城市”。
整个过程耗时6分钟。
但这种方式是，一种“统计类型”导入一个Excle文件。并不是客户要求的按“城市”划分。2、按“分类”、“城市”划分取值
这种方式是客户要求的方式。虽然除了“数量”外，其它统计类型都按“金额”排序。也就是说，在一个“城市”中，除了“数量”类型外，其它的统计类型所取出的“名称”是相同的。但是，要涉及到行列互换，同时要按客户要求的格式输出。所以还没有想到一次取出所有数据的办法。
所以现在采用一次取“分类”、“城市”下的一种“统计类型”下的20条数据；也就是一次取20条数据，在程序里按“分类”、“城市”、“统计类型”不断循环。
这样的话，要导出整个文件，需要到数据库里取几百次。整个导出过程耗时40分钟。目前的分数是我所有的可用分，希望大家能帮忙如何优化这个导出过程。

Answer 1

并取出这些“名称”的所有“日期”的数据
--------------------------------------------
那也就是说 Excel 的列是不定的？因为所有日期可能是 10 个月的，也可能是 100 个月的数据？

Answer 2

/**
 * 按城市分类；<br>
 * 单城市 ；<br>
 * 导出全部；
 * 
 */
public void exportAll() throws WriteException, BiffException, IOException,
SQLException {
Common cm = new Common();
ArrayList list = cm.getCategory();//取出所有分类
ArrayList list2 = cm.getCitys();  //取出所有城市
WritableWorkbook workbook = null;
WritableSheet sheet = null; int[] minDate = cm.getMinDate();//最小日期
int[] maxDate = cm.getMaxDate();//最大日期
ArrayList array = cm.getMonthes(minDate, maxDate);//所有月份 if (!list2.isEmpty()) {
for (int j = 0; j < list2.size(); j++) {
String[] citys = (String[]) list2.get(j); workbook = Workbook.createWorkbook(new File(
"d:\\exportFloder\\城市\\" + citys[1] + ".xls")); if (list != null && !list.isEmpty()) {
int firstSheet = 0;
for (int i = 0; i < list.size(); i++) {
String[] divs = (String[]) list.get(i); sheet = workbook.createSheet(divs[1], firstSheet);
cm.printTitle(sheet, array, "city");
this.exportData(minDate, maxDate, array, divs[0],
divs[1], Integer.parseInt(citys[0]), citys[1],
workbook, sheet); firstSheet++;
}
}
workbook.write();
workbook.close();

System.out.println(new Date());
}
} } /**
 * 单城市；<br>
 * 全部统计类型；
 * 
 */
public void exportData(int[] minDate, int[] maxDate, ArrayList array,
String divId, String div, int cityId, String city,
WritableWorkbook workbook, WritableSheet sheet) throws IOException,
WriteException, SQLException, BiffException { X = 0;
Y = 2; this.loadData(minDate, maxDate, array, "销售金额", cityId, divId, "val",
workbook, sheet, "count");
this.loadDataPercent(minDate, maxDate, array, "销售金额份额", cityId, divId,
"val", workbook, sheet, "count");
this.loadDataCount(minDate, maxDate, array, "销售数量", cityId, divId,
"volpack", workbook, sheet, "count");
this.loadDataPercent(minDate, maxDate, array, "销售包装数量份额", cityId,
divId, "volpack", workbook, sheet, "count");
this.loadDataAverage(minDate, maxDate, array, "平均价格", cityId, divId,
"", workbook, sheet, "count");
this.loadData(minDate, maxDate, array, "实际铺货率", cityId, divId,
"numdist", workbook, sheet, "");
this.loadData(minDate, maxDate, array, "加权铺货率", cityId, divId,
"wtddist", workbook, sheet, ""); this.loadData(minDate, maxDate, array, "实际缺货率", cityId, divId,
"out_stknumdist", workbook, sheet, "");
this.loadData(minDate, maxDate, array, "加权缺货率", cityId, divId,
"out_stkwtddist", workbook, sheet, "");
this.loadData(minDate, maxDate, array, "库存数量", cityId, divId,
"stkvolpack", workbook, sheet, "");
this.loadData(minDate, maxDate, array, "库存天数", cityId, divId,
"coverdays", workbook, sheet, "");
this.loadstorePercent(minDate, maxDate, array, "库存数量份额", cityId, divId,
"stkvolpack", workbook, sheet, "count");// System.out.println(new Date());
} /**
 * 单城市 ；<br>
 * 原始数据；
 */
public void loadData(int[] minDate, int[] maxDate, ArrayList array,
String title, int city_id, String div, String _char,
WritableWorkbook workbook, WritableSheet sheet, String count) {
try { dbo = new DBOperator();
int[] dataList = new int[array.size()]; // 样式；
WritableFont redFont = new WritableFont(WritableFont.ARIAL);
redFont.setColour(Colour.RED);
WritableCellFormat redFonts = new WritableCellFormat(redFont); String sql = "{call h_procedure_val(?,?,?,?,?,?,?,?) }";//oracle存储过程 Connection conn = dbo.conn; CallableStatement cste = conn.prepareCall(sql,
java.sql.ResultSet.TYPE_SCROLL_SENSITIVE,
java.sql.ResultSet.CONCUR_UPDATABLE); cste.setInt(1, minDate[0]);
cste.setInt(2, minDate[1]);
cste.setInt(3, maxDate[0]);
cste.setInt(4, maxDate[1]);
cste.setString(5, _char);
cste.setInt(6, city_id);
cste.setString(7, div);
cste.registerOutParameter(8, oracle.jdbc.OracleTypes.CURSOR);
cste.execute();
ResultSet rs = (ResultSet) cste.getObject(8); Label label10 = new Label(X, Y, title);
sheet.addCell(label10);
Y++; while (rs.next()) { Label label = new Label(X, Y, rs.getString("DIVNAME"));
sheet.addCell(label);
X++; Label label1 = new Label(X, Y, rs.getString("CITYNAME"));
sheet.addCell(label1);
X++; Label label2 = new Label(X, Y, rs.getString("ETITLE"));
sheet.addCell(label2);
X++; Label label3 = new Label(X, Y, rs.getString("CTITLE"));
sheet.addCell(label3);
X++; for (int i = 0; i < array.size(); i++) {
String chara = array.get(i).toString();
Number number = new Number(X, Y, rs.getInt(chara));
sheet.addCell(number); dataList[i] += rs.getInt(chara);
X++;
} X = 0;
Y++;
} if ("count".equals(count)) {
if (dataList != null) { // 合计
Label label0 = new Label(3, Y, "合计", redFonts);
sheet.addCell(label0);
X = 4;
for (int i = 0; i < array.size(); i++) { Number number = new Number(X, Y, dataList[i], redFonts);
sheet.addCell(number);
X++;
} X = 0;
Y++;
}
} Y++; } catch (Exception e) {
e.printStackTrace();
} finally {
dbo.close();
}
}

Answer 3

感觉你的程序里重复计算很多,oracle的内部缓存机制我不懂，你要是肯下狠心的话就把数据读到内存里，自己来操作，这样就没有重复计算了

Answer 4

代码太乱了，不止有 SQL 语句，还有存储过程，数据查询与写入 Excel 完全混杂在一起，实在是看不懂。