I am trying to create an excel file of 2007. Scenario for Creation of Excel from other file.
Input file :- InputFile.xlsx with One Sheet
Calculation File :- Model.xlsx with four sheets
Description for Calculation file :- 1) First Sheet is input file 2) Second sheet is Output 3) Third and Fourth Sheet is calculation sheets.
in Java using Apache POI I am reading Excel file. in one function I am reading file,takes input files InputFiles.xlsx
in second function I am merging two sheets in model.xlsx file. i.e.in the first sheet of Model.xlsx merging first sheet of InputFile.xlsx
In third function I am reading output, that affect after writing in model.xlsx and reading it.
Merging function as follows
public void writeXLSXFile(String modelid, String inputfile) throws SQLException, ClassNotFoundException
{
try
{
String realPath = context.getRealPath("/");
String DestPath = "";
InputStream ExcelFileToRead = new FileInputStream(inputfile);
XSSFWorkbook wbip = new XSSFWorkbook(ExcelFileToRead);
DestPath = realPath + "WEB-INF/classes/uploadfiles/Model.xlsx";
NPOIFSFileSystem fs = new NPOIFSFileSystem(new File(DestPath));
EncryptionInfo info = new EncryptionInfo(fs);
Decryptor d = Decryptor.getInstance(info);
XSSFWorkbook wb = null;
try
{
if (d.verifyPassword("MUSDCM"))
{
wb = new XSSFWorkbook(d.getDataStream(fs));
} else
{
System.out.println("Wrong Password");
}
} catch (GeneralSecurityException ex)
{
}
XSSFSheet sheet = wb.getSheetAt(0);
wb.removeSheetAt(0);
wb.removeSheetAt(3);
wb.createSheet("2.2 Parameter Estimates");
wb.createSheet("Raw Macro Data");
wb.setSheetOrder("DCM_Implementation_v09.csv", 1);
wb.setSheetOrder("Excel Model_IG", 2);
wb.setSheetOrder("Excel Model_HY", 3);
wb.setSheetOrder("Raw Macro Data", 4);
wb.setSheetOrder("2.2 Parameter Estimates", 0);
sheet = wb.getSheetAt(0);
XSSFRow row = sheet.createRow(1);
XSSFCell cell = row.createCell(1);
cell.setCellValue("Investment Grade Bond Issuance Volume Model");
cell = row.createCell(4);
cell.setCellValue("High Yield Bond Issuance Volume Model");
row = sheet.createRow(3);
cell = row.createCell(1);
cell.setCellValue("Independent Variables");
cell = row.createCell(2);
cell.setCellValue("Coefficients");
cell = row.createCell(4);
cell.setCellValue("Independent Variables");
cell = row.createCell(5);
cell.setCellValue("Coefficients");
int rowNumber = 4;
Class.forName("com.mysql.jdbc.Driver");
Connection con = getConnection();
Statement st = con.createStatement();
ResultSet rs = st.executeQuery("SELECT s.volumename,s.id,s.parameter,s.value,m.model_name FROM singlevalue_table s inner join model_inventory m on s.modelid =m.modelid and s.modelid=" + modelid);
ArrayList InBond = new ArrayList();
ArrayList HnBond = new ArrayList();
while (rs.next())
{
if ("Investment Grade Bond Issuance Volume Model".equals(rs.getString("s.volumename")))
{
InBond.add(rs.getString("s.parameter"));
InBond.add(rs.getString("s.value"));
} else if ("High Yield Bond Issuance Volume Model".equals(rs.getString("s.volumename")))
{
HnBond.add(rs.getString("s.parameter"));
HnBond.add(rs.getString("s.value"));
}
}
int maxcount = ((InBond.size() >= HnBond.size()) ? InBond.size() : HnBond.size());
int count1 = 0;
int count2 = 0;
for (int j = 0; j < maxcount / 2; j++)
{
row = sheet.createRow(j + 4);
if (j < (InBond.size() / 2))
{
cell = row.createCell(1);
cell.setCellValue((String) InBond.get(count1));
cell = row.createCell(2);
cell.setCellValue((String) InBond.get(count1 + 1));
count1 += 2;
}
if (j < (HnBond.size() / 2))
{
cell = row.createCell(4);
cell.setCellValue((String) HnBond.get(count2));
cell = row.createCell(5);
cell.setCellValue((String) HnBond.get(count2 + 1));
count2 += 2;
}
}
wb = CopySheet(wbip, wb);
try
{
wb.setForceFormulaRecalculation(true);
FileOutputStream fileOut = new FileOutputStream(DestPath);
wb.write(fileOut);
wbip.close();
wb.close();
fs.close();
fileOut.close();
} catch (IOException e)
{
e.printStackTrace();
}
//Add password protection and encrypt the file
fs = null;
info = null;
fs = new NPOIFSFileSystem();
info = new EncryptionInfo(fs, EncryptionMode.binaryRC4);
Encryptor enc = info.getEncryptor();
enc.confirmPassword("MUSDCM");
OPCPackage opc = OPCPackage.open(new File(DestPath), PackageAccess.READ_WRITE);
OutputStream os = enc.getDataStream(fs);
opc.save(os);
opc.close();
FileOutputStream fos = new FileOutputStream(DestPath);
fs.writeFilesystem(fos);
fos.close();
fs.close();
ExcelFileToRead.close();
System.out.println("File created!!");
} catch (Exception ex)
{
Logger.getLogger(resource.class.getName()).log(Level.SEVERE, null, ex);
}
}
Reading output from Model.xlsx function as follows
public Response readXLSXFileWithSecondSheet(@QueryParam("modelid") String modelid) throws IOException, GeneralSecurityException
{
try
{
String realPath = context.getRealPath("/");
String DestPath = realPath + "WEB-INF/classes/uploadfiles/DCM_Implementation_v10.xlsx";
NPOIFSFileSystem fs = new NPOIFSFileSystem(new File(DestPath));
EncryptionInfo info = new EncryptionInfo(fs);
Decryptor d = Decryptor.getInstance(info);
XSSFWorkbook wb = null;
if (d.verifyPassword("MUSDCM"))
{
wb = new XSSFWorkbook(d.getDataStream(fs));
wb.setForceFormulaRecalculation(true);
} else
{
System.out.println("Wrong Password");
}
XSSFSheet sheet = (XSSFSheet) wb.getSheetAt(1);
XSSFRow row;
XSSFCell cell;
ArrayList Heading = new ArrayList();
ArrayList<graphdata> Basemap = new ArrayList<graphdata>();
ArrayList<graphdata> Adversemap = new ArrayList<graphdata>();
ArrayList<graphdata> SeverelyAdversemap = new ArrayList<graphdata>();
ArrayList<graphdata> BHCBasemap = new ArrayList<graphdata>();
ArrayList<graphdata> BHCSeverelyAdversemap = new ArrayList<graphdata>();
String ModelName = "Investment Grade Bond Issuance Volume Model";
Iterator rows = sheet.rowIterator();
while (rows.hasNext())
{
row = (XSSFRow) rows.next();
Iterator cells = row.cellIterator();
//int columnNo = 1;
int CCARCount = 0;
graphdata g1 = new graphdata();
graphdata g2 = new graphdata();
graphdata g3 = new graphdata();
graphdata g4 = new graphdata();
graphdata g5 = new graphdata();
while (cells.hasNext())
{
cell = (XSSFCell) cells.next();
int ActulaColINdex = cell.getColumnIndex();
if (cell.getCellType() == XSSFCell.CELL_TYPE_BLANK)
{
continue;
}
if (cell.getCellType() == XSSFCell.CELL_TYPE_STRING)
{
if (((String) cell.getRichStringCellValue().getString()).equals(ModelName) && cell.getColumnIndex() == 1)
{
ModelName = (String) cell.getRichStringCellValue().getString();
continue;
}
if (((String) cell.getRichStringCellValue().getString()).equals("High Yield Bond Issuance Volume Model") && cell.getColumnIndex() == 1)
{
ModelName = (String) cell.getRichStringCellValue().getString();
continue;
}
String FindCCAR = (String) cell.getRichStringCellValue().getString();
if (FindCCAR.contains("CCAR"))
{
continue;
}
if (FindCCAR.contains("CCAR"))
{
switch (CCARCount)
{
case 0:
Heading.add(FindCCAR);
break;
case 1:
Heading.add(FindCCAR);
break;
case 2:
Heading.add(FindCCAR);
break;
case 3:
Heading.add(FindCCAR);
break;
case 4:
Heading.add(FindCCAR);
break;
}
CCARCount++;
}
if (FindCCAR.contains("Date") || FindCCAR.contains("Model") || FindCCAR.contains("Diff"))
{
continue;
}
if (ActulaColINdex <= 5 && ActulaColINdex >= 1) //Base
{
if (ActulaColINdex == 1)
{
g1.setVolumeName(ModelName);
g1.setModelName("Base");
g1.setYear(cell.getRichStringCellValue().toString());
}
//System.out.print(cell.getRichStringCellValue() + " ");
}
if (ActulaColINdex <= 11 && ActulaColINdex >= 7) //Adverse
{
if (ActulaColINdex == 7)
{
g2.setVolumeName(ModelName);
g2.setModelName("Adverse");
g2.setYear(cell.getRichStringCellValue().toString());
}
//System.out.print(cell.getRichStringCellValue() + " ");
}
if (ActulaColINdex <= 17 && ActulaColINdex >= 13) //Severly Adverse
{
if (ActulaColINdex == 13)
{
g3.setVolumeName(ModelName);
g3.setModelName("Severely Adverse");
g3.setYear(cell.getRichStringCellValue().toString());
}
//System.out.print(cell.getRichStringCellValue() + " ");
}
if (ActulaColINdex <= 23 && ActulaColINdex >= 19) //BHC Base
{
if (ActulaColINdex == 19)
{
g4.setVolumeName(ModelName);
g4.setModelName("BHC Base");
g4.setYear(cell.getRichStringCellValue().toString());
}
//System.out.print(cell.getRichStringCellValue() + " ");
}
if (ActulaColINdex <= 30 && ActulaColINdex >= 26) //BHC Severly Adverse
{
if (ActulaColINdex == 26)
{
g5.setVolumeName(ModelName);
g5.setModelName("BHC Severely Adverse");
g5.setYear(cell.getRichStringCellValue().toString());
}
//System.out.print(cell.getRichStringCellValue() + " ");
}
} else if (cell.getCellType() == XSSFCell.CELL_TYPE_NUMERIC)
{
if (ActulaColINdex <= 5 && ActulaColINdex >= 1) //Base
{
if (ActulaColINdex == 2)
{
g1.setSasmodel(cell.getNumericCellValue());
}
}
if (ActulaColINdex <= 11 && ActulaColINdex >= 7) //Adverse
{
if (ActulaColINdex == 8)
{
g2.setSasmodel(cell.getNumericCellValue());
}
}
if (ActulaColINdex <= 17 && ActulaColINdex >= 13) //Severly Adverse
{
if (ActulaColINdex == 14)
{
g3.setSasmodel(cell.getNumericCellValue());
}
}
if (ActulaColINdex <= 23 && ActulaColINdex >= 19) //BHC Base
{
if (ActulaColINdex == 20)
{
g4.setSasmodel(cell.getNumericCellValue());
}
}
if (ActulaColINdex <= 30 && ActulaColINdex >= 26) //BHC Severly Advrse
{
if (ActulaColINdex == 27)
{
g5.setSasmodel(cell.getNumericCellValue());
}
}
} else if (cell.getCellType() == XSSFCell.CELL_TYPE_FORMULA)
{
switch (cell.getCachedFormulaResultType())
{
case Cell.CELL_TYPE_NUMERIC:
{
if (ActulaColINdex <= 5 && ActulaColINdex >= 1) //Base
{
if (ActulaColINdex == 3)
{
g1.setXxcelmodel(cell.getNumericCellValue());
}
if (ActulaColINdex == 4)
{
g1.setDiff(cell.getNumericCellValue());
}
if (ActulaColINdex == 5)
{
g1.setDiffper(cell.getNumericCellValue());
Basemap.add(g1);
}
}
if (ActulaColINdex <= 11 && ActulaColINdex >= 7) //Adverse
{
if (ActulaColINdex == 9)
{
g2.setXxcelmodel(cell.getNumericCellValue());
}
if (ActulaColINdex == 10)
{
g2.setDiff(cell.getNumericCellValue());
}
if (ActulaColINdex == 11)
{
g2.setDiffper(cell.getNumericCellValue());
Adversemap.add(g2);
}
}
if (ActulaColINdex <= 17 && ActulaColINdex >= 13) //SeverlyAdverse
{
if (ActulaColINdex == 15)
{
g3.setXxcelmodel(cell.getNumericCellValue());
}
if (ActulaColINdex == 16)
{
g3.setDiff(cell.getNumericCellValue());
}
if (ActulaColINdex == 17)
{
g3.setDiffper(cell.getNumericCellValue());
SeverelyAdversemap.add(g3);
}
}
if (ActulaColINdex <= 23 && ActulaColINdex >= 19)//BHC Base
{
if (ActulaColINdex == 21)
{
g4.setXxcelmodel(cell.getNumericCellValue());
}
if (ActulaColINdex == 22)
{
g4.setDiff(cell.getNumericCellValue());
}
if (ActulaColINdex == 23)
{
g4.setDiffper(cell.getNumericCellValue());
BHCBasemap.add(g4);
}
}
if (ActulaColINdex <= 30 && ActulaColINdex >= 26)//BHC Severly Adverse
{
if (ActulaColINdex == 28)
{
g5.setXxcelmodel(cell.getNumericCellValue());
}
if (ActulaColINdex == 29)
{
g5.setDiff(cell.getNumericCellValue());
}
if (ActulaColINdex == 30)
{
g5.setDiffper(cell.getNumericCellValue());
BHCSeverelyAdversemap.add(g5);
}
}
}
break;
case Cell.CELL_TYPE_STRING:
{
if (ActulaColINdex <= 5 && ActulaColINdex >= 1)//Base
{
if (ActulaColINdex == 3)
{
g1.setXxcelmodel(cell.getNumericCellValue());
}
if (ActulaColINdex == 4)
{
g1.setDiff(cell.getNumericCellValue());
}
if (ActulaColINdex == 5)
{
g1.setDiffper(cell.getNumericCellValue());
Basemap.add(g1);
}
}
if (ActulaColINdex <= 11 && ActulaColINdex >= 7) //Adverse
{
if (ActulaColINdex == 9)
{
g2.setXxcelmodel(cell.getNumericCellValue());
}
if (ActulaColINdex == 10)
{
g2.setDiff(cell.getNumericCellValue());
}
if (ActulaColINdex == 11)
{
g2.setDiffper(cell.getNumericCellValue());
Adversemap.add(g2);
}
}
if (ActulaColINdex <= 17 && ActulaColINdex >= 13) //SeverlyAdverse
{
if (ActulaColINdex == 15)
{
g3.setXxcelmodel(cell.getNumericCellValue());
}
if (ActulaColINdex == 16)
{
g3.setDiff(cell.getNumericCellValue());
}
if (ActulaColINdex == 17)
{
g3.setDiffper(cell.getNumericCellValue());
SeverelyAdversemap.add(g3);
}
ActulaColINdex++;
}
if (ActulaColINdex <= 23 && ActulaColINdex >= 19)//BHC Base
{
if (ActulaColINdex == 21)
{
g4.setXxcelmodel(cell.getNumericCellValue());
}
if (ActulaColINdex == 22)
{
g4.setDiff(cell.getNumericCellValue());
}
if (ActulaColINdex == 23)
{
g4.setDiffper(cell.getNumericCellValue());
BHCBasemap.add(g4);
}
}
if (ActulaColINdex <= 30 && ActulaColINdex >= 26)//BHC Severly Adverse
{
if (ActulaColINdex == 28)
{
g5.setXxcelmodel(cell.getNumericCellValue());
}
if (ActulaColINdex == 29)
{
g5.setDiff(cell.getNumericCellValue());
}
if (ActulaColINdex == 30)
{
g5.setDiffper(cell.getNumericCellValue());
BHCSeverelyAdversemap.add(g5);
}
}
}
break;
}
} else
{
//U Can Handel Boolean, Formula, Errors
}
}
System.out.println();
}
wb.close();
fs.close();
System.out.println("-----------------------Base----------------------");
Class.forName("com.mysql.jdbc.Driver");
Connection con;
con = getConnection();
Statement st = con.createStatement();
String sql1 = "delete from outputtable where modelid=" + modelid;
int flag1 = st.executeUpdate(sql1);
for (int i = 0; i < Basemap.size(); i++)
{
graphdata g = (graphdata) Basemap.get(i);
String sql = "INSERT INTO outputtable (VolumeName,ModelName,year,sasmodel,excelmodel,diff,diffper,modelid)"
+ " VALUES ('" + g.getVolumeName() + "','" + g.getModelName() + "','" + g.getYear() + "', " + g.getSasmodel() + ", " + g.getXxcelmodel() + ", " + g.getDiff() + ", " + g.getDiffper() + ", " + modelid + ")";
st.executeUpdate(sql);
System.out.println(g.getVolumeName() + "" + g.getYear() + "" + g.getModelName() + "" + g.getSasmodel() + "" + g.getXxcelmodel());
}
System.out.println("-----------------------Advrse----------------------");
for (int i = 0; i < Adversemap.size(); i++)
{
graphdata g = (graphdata) Adversemap.get(i);
String sql = "INSERT INTO outputtable (`outputtable`.`VolumeName`,`outputtable`.`ModelName`,`outputtable`.`year`,`outputtable`.`sasmodel`,`outputtable`.`excelmodel`,`outputtable`.`diff`,`outputtable`.`diffper`,`outputtable`.`modelid`)"
+ " VALUES ('" + g.getVolumeName() + "','" + g.getModelName() + "','" + g.getYear() + "', " + g.getSasmodel() + ", " + g.getXxcelmodel() + ", " + g.getDiff() + ", " + g.getDiffper() + ", " + modelid + ")";
st.executeUpdate(sql);
System.out.println(g.getVolumeName() + "" + g.getYear() + "" + g.getModelName() + "" + g.getSasmodel() + "" + g.getXxcelmodel());
}
System.out.println("-----------------------Severly Advers----------------------");
for (int i = 0; i < SeverelyAdversemap.size(); i++)
{
graphdata g = (graphdata) SeverelyAdversemap.get(i);
String sql = "INSERT INTO outputtable (`outputtable`.`VolumeName`,`outputtable`.`ModelName`,`outputtable`.`year`,`outputtable`.`sasmodel`,`outputtable`.`excelmodel`,`outputtable`.`diff`,`outputtable`.`diffper`,`outputtable`.`modelid`)"
+ " VALUES ('" + g.getVolumeName() + "','" + g.getModelName() + "','" + g.getYear() + "', " + g.getSasmodel() + ", " + g.getXxcelmodel() + ", " + g.getDiff() + ", " + g.getDiffper() + ", " + modelid + ")";
st.executeUpdate(sql);
System.out.println(g.getVolumeName() + "" + g.getYear() + "" + g.getModelName() + "" + g.getSasmodel() + "" + g.getXxcelmodel());
}
System.out.println("-----------------------BHC Base----------------------");
for (int i = 0; i < BHCBasemap.size(); i++)
{
graphdata g = (graphdata) BHCBasemap.get(i);
String sql = "INSERT INTO outputtable (`outputtable`.`VolumeName`,`outputtable`.`ModelName`,`outputtable`.`year`,`outputtable`.`sasmodel`,`outputtable`.`excelmodel`,`outputtable`.`diff`,`outputtable`.`diffper`,`outputtable`.`modelid`)"
+ " VALUES ('" + g.getVolumeName() + "','" + g.getModelName() + "','" + g.getYear() + "', " + g.getSasmodel() + ", " + g.getXxcelmodel() + ", " + g.getDiff() + ", " + g.getDiffper() + ", " + modelid + ")";
st.executeUpdate(sql);
System.out.println(g.getVolumeName() + "" + g.getYear() + "" + g.getModelName() + "" + g.getSasmodel() + "" + g.getXxcelmodel());
}
System.out.println("-----------------------BHC Severly Adve----------------------");
for (int i = 0; i < BHCSeverelyAdversemap.size(); i++)
{
graphdata g = (graphdata) BHCSeverelyAdversemap.get(i);
String sql = "INSERT INTO outputtable (`outputtable`.`VolumeName`,`outputtable`.`ModelName`,`outputtable`.`year`,`outputtable`.`sasmodel`,`outputtable`.`excelmodel`,`outputtable`.`diff`,`outputtable`.`diffper`,`outputtable`.`modelid`)"
+ " VALUES ('" + g.getVolumeName() + "','" + g.getModelName() + "','" + g.getYear() + "', " + g.getSasmodel() + ", " + g.getXxcelmodel() + ", " + g.getDiff() + ", " + g.getDiffper() + ", " + modelid + ")";
st.executeUpdate(sql);
System.out.println(g.getVolumeName() + "" + g.getYear() + "" + g.getModelName() + "" + g.getSasmodel() + "" + g.getXxcelmodel());
}
} catch (Exception e)
{
return Response.status(401)
.header("Access-Control-Allow-Origin", "*")
.header("Access-Control-Allow-Headers", "origin, content-type, accept, authorization")
.header("Access-Control-Allow-Credentials", "true")
.header("Access-Control-Allow-Methods", "GET, POST, PUT, DELETE, OPTIONS, HEAD")
.header("Access-Control-Max-Age", "1209600")
.entity("[{\"Success\":\"Error\"}]")
.build();
}
return Response.status(200)
.header("Access-Control-Allow-Origin", "*")
.header("Access-Control-Allow-Headers", "origin, content-type, accept, authorization")
.header("Access-Control-Allow-Credentials", "true")
.header("Access-Control-Allow-Methods", "GET, POST, PUT, DELETE, OPTIONS, HEAD")
.header("Access-Control-Max-Age", "1209600")
.entity("[{\"Success\":\"Yes\"}]")
.build();
}
Problem is :- While reading second sheet using apache poi in java, it read previous data not the updated data after calculation.even if Model.xlsx contain updated data java reads only previous data.This is very strange find me any solution.
Kindly please assist me.
Thanks.
Model.xlsxfile from the first function after modifying it. – user2004685