用java代码遍历excel文件并回显
今天需要完成282个指标,分析后发现好多都是可复用的字段和方法,生成的dao类也是很多重复的代码,所以写下了简单的自动化遍历excel的test方法, excel业务逻辑如下,用了
HSSFSheet
类来实现,并复习了一遍
public static void main(String[] args) {
// TODO Auto-generated method stub
try {
FileInputStream fStream = new FileInputStream("E:/开发文档/中台迁移表数据/新增指标.xls");//修改
HSSFWorkbook book = new HSSFWorkbook(fStream);
//获得第一个工作表对象(ecxel中sheet的编号从0开始,0,1,2,3,....)
HSSFSheet sheet = book.getSheet("Sheet1");
StringBuilder sb = FuncUtils.getStringBuilder();
sb.setLength(0);
//遍历每一行内容
// 行数
sb.append("package org.gil.sydb.server.indicator.bond.zq.finanstatementnew.bank;").append("\r\n");
sb.append("\r\n");
sb.append("import com.google.common.collect.ImmutableList;").append("\r\n");
sb.append("import com.google.common.collect.ImmutableList;").append("\r\n");
sb.append("import org.gil.sydb.server.api.APIAnnotation.APIParam;").append("\r\n");
sb.append("import org.gil.sydb.server.api.APIAnnotation.ApiInfo;").append("\r\n");
sb.append("import org.gil.sydb.server.api.IAPI;").append("\r\n");
sb.append("import org.gil.sydb.server.cfidb.table.SecuMainAll;").append("\r\n");
sb.append("import org.gil.sydb.server.findb.table.FIN_OSDerivedIndex;").append("\r\n");
sb.append("import org.gil.sydb.server.findb.table.row.FIN_OSDerivedIndexRow;").append("\r\n");
sb.append("import org.gil.sydb.server.findb.table.FIN_OSDerivedTTMIndex;").append("\r\n");
sb.append("import org.gil.sydb.server.findb.table.row.FIN_OSDerivedTTMIndexRow;").append("\r\n");
sb.append("import org.gil.sydb.server.jydb.table.LC_QFinancialIndexNew;").append("\r\n");
sb.append("import org.gil.sydb.server.jydb.table.row.LC_QFinancialIndexNewRow;").append("\r\n");
sb.append("import org.gil.sydb.server.jydb.table.LC_BankLoan;").append("\r\n");
sb.append("import org.gil.sydb.server.jydb.table.row.LC_BankLoanRow;").append("\r\n");
sb.append("import org.gil.sydb.server.indicator.bond.zq.hsfinancanal.HSAnalysCommonsRows;").append("\r\n");
sb.append("import org.gil.sydb.server.indicator.util.Constant;").append("\r\n");
sb.append("import org.gil.sydb.server.jydb.table.Bond_Code;").append("\r\n");
sb.append("import org.gil.sydb.server.jydb.table.SecuMain;").append("\r\n");
sb.append("import org.gil.sydb.server.model.EFieldType;").append("\r\n");
sb.append("import org.gil.sydb.server.nfsdb.table.BaseCode;").append("\r\n");
sb.append("\r\n");
sb.append("import java.util.Arrays;").append("\r\n");
sb.append("import java.util.Date;").append("\r\n");
sb.append("import java.util.List;").append("\r\n");
sb.append("public class TestApi implements IAPI {").append("\r\n");
sb.append("\r\n");
sb.append(" @Override").append("\r\n");
sb.append(" public String getApiDescription() {").append("\r\n");
sb.append(" return \"债券数据浏览器->财务分析->新增指标\";").append("\r\n");
sb.append(" }").append("\r\n");
sb.append("\r\n");
sb.append(" @Override").append("\r\n");
sb.append(" public List<String> getDependTables() {").append("\r\n");
sb.append(" return ImmutableList.of(FIN_OSDerivedIndex.TABLENAME,FIN_OSDerivedTTMIndex.TABLENAME,LC_QFinancialIndexNew.TABLENAME,LC_BankLoan.TABLENAME).asList();").append("\r\n");
sb.append(" }").append("\r\n");
sb.append("\r\n");
int rowNumbers = sheet.getLastRowNum();
for (int t = 0; t < rowNumbers; t++) {
StringBuilder inputbuilder = new StringBuilder();
inputbuilder.setLength(0);
HSSFRow row = sheet.getRow(t);
sb.append(" @ApiInfo(Name=\"").append(row.getCell(1).toString()).append("\",Code={},ZhDescription=\"").append(row.getCell(0).toString()).append("\",ResultType=EFieldType.DOUBLEARRAY,ApiType=APIType.Bond)").append("\r\n");
if(row.getCell(4).toString().equals("InnerCode")) {
inputbuilder.append(",").append("int innerCode");
sb.append(" @APIParam(ZhName=\"内部编码\",EnName=\"InnerCode\",ParamType=EFieldType.INT,Required=true,Info=\"内部编码\")").append("\r\n");
}
if(row.getCell(7).toString().equals("ReportDate")) {
inputbuilder.append(",").append(" Date[] reportDate");
sb.append(" @APIParam(ZhName=\"报告期\",EnName=\"ReportDate\",ParamType=EFieldType.DATEARRAY,DefaultValue=Constant.RepDateDefValue,Info=\"报告期\")").append("\r\n");
}
if(row.getCell(7).toString().equals("TradingDate")) {
inputbuilder.append(",").append(" Date tradingDate");
sb.append(" @APIParam(ZhName=\"交易日期\",EnName=\"TradingDate\",ParamType=EFieldType.DATE,DefaultValue=Constant.TDateDefault,Info=\"交易日期\")").append("\r\n");
}
if(row.getCell(10).toString().equals("BaseDate")) {
inputbuilder.append(",").append(" int baseDate");
sb.append(" @APIParam(ZhName=\"TTM基准日\",EnName=\"BaseDate\",ParamType=EFieldType.INT,DefaultValue=\"0\",IsEnum=true,EnumValue=Constant.BaseDate)").append("\r\n");
}
if(row.getCell(10).toString().equals("Unit")) {
inputbuilder.append(",").append(" int unit");
sb.append(" @APIParam(ZhName=\"单位\",EnName=\"Unit\",ParamType=EFieldType.INT,DefaultValue=\"0\",IsEnum=true,EnumValue=Constant.Unit)").append("\r\n");
}
if(row.getCell(13).toString().equals("Unit")) {
inputbuilder.append(",").append(" int unit");
sb.append(" @APIParam(ZhName=\"单位\",EnName=\"Unit\",ParamType=EFieldType.INT,DefaultValue=\"0\",IsEnum=true,EnumValue=Constant.Unit)").append("\r\n");
}
System.out.println(inputbuilder);
System.out.println(row.getCell(1).toString());
sb.append(" public double[] get").append(row.getCell(1).toString()).append("(").append(inputbuilder.substring(1)).append(") {").append("\r\n");
String input = row.getCell(3).toString();
sb.append(" ").append(methodMap.get(String.valueOf(row.getCell(2).toString())));
if (String.valueOf(row.getCell(2).toString()).equals("FIN_OSDerivedIndex")||String.valueOf(row.getCell(2).toString()).equals("FIN_OSDerivedTTMIndex")) {//包含英文字符
sb.append(input).append("\"");
}else if (String.valueOf(row.getCell(2).toString()).equals("LC_QFinancialIndexNew")) {
sb.append(input);
} else{
BigDecimal bd = new BigDecimal(input);
String checkValue = bd.toPlainString();
sb.append(checkValue);
}
sb.append(");").append("\r\n");
sb.append(" }").append("\r\n");
sb.append("\r\n");
}
sb.append("}").append("\r\n\r\n");
// System.out.println(sb.toString());
FileWriter fwriter = null;
try {
// true表示不覆盖原来的内容，而是加到文件的后面。若要覆盖原来的内容，直接省略这个参数就好
fwriter = new FileWriter("E:/开发文档/TestApi.java", true);//修改
fwriter.write(sb.toString());
} catch (IOException ex) {
ex.printStackTrace();
} finally {
try {
fwriter.flush();
fwriter.close();
} catch (IOException ex) {
ex.printStackTrace();
}
}
} catch (Exception e) {
// TODO: handle exception
e.printStackTrace();
}
}
static Map<String, String> methodMap = new HashMap<>();
static {
methodMap.put("FIN_OSDerivedIndex", "return HSAnalysCommonsRows.getBasicEpsWithDeductionWithoutSharesValue(innerCode, reportDate, \"");
methodMap.put("FIN_OSDerivedTTMIndex", "return HSAnalysCommonsRows.getBasicEpsWithDeductionValueWithUnitTTM(innerCode, reportDate, unit, \"");
methodMap.put("LC_QFinancialIndexNew", "return HSAnalysCommonsRows.getSingleQuarter(innerCode, reportDate, LC_QFinancialIndexNewRow::get");
methodMap.put("LC_BankLoan", "return HSAnalysCommonsRows.getBankLoansWithUnit(innerCode, reportDate, unit, ");
}
}
浙公网安备 33010602011771号