oracle erp 报表开发手册,处置OracleERP导出的报表文件

[代码] [Java]代码 package k.finance;

import java.io.File;

import java.io.IOException;

import java.util.ArrayList;

import java.util.HashMap;

import java.util.List;

import java.util.Map;

import j http://www.starkp.com/linked/20130228.do; xl.Sheet;

import jxl.Workbook;

import jxl.read.biff.BiffException;

import jxl.write.Label;

import jxl.write.WritableSheet;

import jxl.write.WritableWorkbook;

import jxl.write.WriteException;

import org.apache.commons.lang3.StringUtils;

public class GetContentFromXls {

/**

* @param args

*/

public static void main(String[] args) {

String file = "d://cuc.xls";

GetContentFromXls.get(file);

}

public static void get(String file){

Workbook workbook = null;

try {

workbook = Workbook.getWorkbook(new File(file));

Sheet sheet = workbook.getSheet(0);

//取得文件中一共有多少行

int totalRows = sheet.getRows();

System.out.println("总行数：" totalRows);

//数据处置结果list

List> okList = new ArrayList>();

List> groupList = getGroupList(sheet, totalRows);

//得到每组的行数据

for(Map groupMap : groupList){

int sIdx = groupMap.get("s");

int eIdx = groupMap.get("e");

//取得项目编号，在每组的榜首行。用. split 之后 第5位是项目编号

String okProjCode = StringUtils.split(getCellValue2Trim(sheet, sIdx),".")[5];

//System.out.println("项目编号str：" okProjCode);

//取得门类，在每组的第二行。用. split 之后 第3位是门类

String okCostType = StringUtils.split(getCellValue2Trim(sheet, sIdx 1),".")[3];

//System.out.println("门类str：" okCostType);

//取得没项目的明细中，每组本月算计的开端、完毕索引

List> byhjGroupIdxList = getSumIdxGroup(sheet, sIdx, eIdx);

for(Map idxMap : byhjGroupIdxList){

Map okMap = new HashMap();

okMap.put("proj_code", okProjCode);

okMap.put("cost_type", okCostType);

String colYMStr = getCellValue2Trim(sheet, idxMap.get("s"));

String colMSumStr = getCellValue2Trim(sheet, idxMap.get("e"));

//System.out.println("年月列str : " colYMStr);

//System.out.println("月算计列str : " colMSumStr);

//取得你年月，取colYMStr前8位后trim

String[] ymVal = StringUtils.split(StringUtils.trimToEmpty(StringUtils.substring(colYMStr, 0, 8)),"-");

String okYear = ymVal[0];

String okMonth = ymVal[1];

okMap.put("year", okYear);

okMap.put("month", okMonth);

//System.out.println("年:" okYear " - 月:" okMonth);

//处置月算计列，按空格split

String[] sumVal = StringUtils.split(colMSumStr);

String okJie = sumVal[1];

String okDai = sumVal[2];

String okYe = sumVal[4];

okMap.put("jie", okJie);

okMap.put("dai", okDai);

okMap.put("ye", okYe);

//System.out.println("借：" okJie " - 贷:" okDai " - 余额：" okYe);

okList.add(okMap);

}

}

//System.out.println(okList);

//write to xls

toXls(okList);

} catch (BiffException | IOException | WriteException e) {

e.printStackTrace();

}finally{

workbook.close();

}

}

private static void toXls(final List> okList) throws WriteException, IOException {

WritableWorkbook workbook = null;

try {

workbook = Workbook.createWorkbook(new File("d://output" System.currentTimeMillis() ".xls"));

WritableSheet sheet = workbook.createSheet("sheet1", 0);

//写表头

sheet.addCell(new Label(1, 0, "项目编号"));

sheet.addCell(new Label(2, 0, "本钱类型"));

sheet.addCell(new Label(3, 0, "年"));

sheet.addCell(new Label(4, 0, "月"));

sheet.addCell(new Label(5, 0, "借"));

sheet.addCell(new Label(6, 0, "贷"));

sheet.addCell(new Label(7, 0, "本月余额"));

//写表格内容

for(int i=0;i map = okList.get(i);

sheet.addCell(new Label(1, i 1, map.get("proj_code")));

sheet.addCell(new Label(2, i 1, map.get("cost_type")));

sheet.addCell(new Label(3, i 1, map.get("year")));

sheet.addCell(new Label(4, i 1, map.get("month")));

sheet.addCell(new Label(5, i 1, map.get("jie")));

sheet.addCell(new Label(6, i 1, map.get("dai")));

sheet.addCell(new Label(7, i 1, map.get("ye")));

}

workbook.write();

} finally{

workbook.close();

}

}

private static List> getSumIdxGroup(Sheet sheet, int sIdx, int eIdx) {

//算计组明细的开端索引

int sumGroupStartIdx = sIdx 4;

//将包括“本月算计”的行打包,开端索引 4，去掉头部信息。并将包括本月算计的索引保存起来

List byhjIdxList = new ArrayList();

for(int i=sumGroupStartIdx;i<=eIdx;i ){

String byhjValStr = getCellValue2Trim(sheet, i);

//System.out.println(byhjValStr);

if(StringUtils.contains(byhjValStr, "本月算计")){

//System.out.println("本月算计所内行：A" i);

byhjIdxList.add(i);

}

}

//System.out.println(byhjIdxList);

//处置包括本月算计的组开端索引和组完毕索引

List> byhjGroupIdxList = new ArrayList>();

for(int i=0;i map = new HashMap();

map.put("s", sumGroupStartIdx);

map.put("e", e);

if(i 1 < byhjIdxList.size()){

if(StringUtils.contains(getCellValue2Trim(sheet,e 1),"本年累计")){

sumGroupStartIdx = e 2;

}else{

sumGroupStartIdx = e 1;

}

}

byhjGroupIdxList.add(map);

}

//System.out.println(byhjGroupIdxList);

return byhjGroupIdxList;

}

private static List> getGroupList(Sheet sheet, int totalRows) {

//containKEMUList寄存一切包括【科 目：】的行号

List containKEMUList = new ArrayList();

//取得文件中有多少组项目

for(int i=1;i<=totalRows;i ){

//取得col-A中的内容并去掉空格

String aVal = getCellValue2Trim(sheet,i);

//将col-A中包括【科 目：】字样的行提取出来

if(StringUtils.isNotBlank(aVal)