[代碼] [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)

總結

以上是生活随笔為你收集整理的oracle erp 报表开发手册,处置OracleERP导出的报表文件的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。