List of usage examples for org.apache.poi.ss.usermodel Sheet createRow
Row createRow(int rownum);
From source file:bloodbank.Simulation.java
public void simulate(Sheet sheet, int runs) { FES fes = new FES(); //Donor donor=new donor(); SimResults results = new SimResults(); //Queue queues[]; //queues = new Queue[8]; //Exception occurs if I use above code, I don't know why.... Queue queue0 = new Queue(); Queue queue1 = new Queue(); Queue queue2 = new Queue(); Queue queue3 = new Queue(); Queue queue4 = new Queue(); Queue queue5 = new Queue(); Queue queue6 = new Queue(); Queue queue7 = new Queue(); //0.reception 1.preinterview-plasma 2.preinterview-whole //3.predonation-plasma 4.predonation-whole //5. Disconnect (not distinguishing donation type) //6. Plasma connecting 7. Whole connecting Donor cDonor;//from w w w. j a v a 2 s. c o m int cID = 0;//current id int cType = 0;//current type int listID = 0;//ID in the list double OldhourBedOccPl = 0; double OldhourBedOccWh = 0; double OldhourQueue0 = 0; double OldhourQueueDocPl = 0; double OldhourQueueDocWh = 0; double OldsojournPreDonPl = 0; double OldsojournPreDonWh = 0; double OldsojournTotDonPl = 0; double OldsojournTotDonWh = 0; double OldhourQueuePreDonPl = 0; double OldhourQueuePreDonWh = 0; double OldhourQuestionnaireN = 0; double OldavailableNurse = 0; double OldPwaitforconnect = 0; double OldWwaitforconnect = 0; double Oldwaitfordisconnect = 0; //double number is the arbitrary number 35, (this was initially the plan as I constructed half hour statistics) double endtime = 0; double[] hourBedOccPl = new double[35]; // make array for hourBedOccPl double[] hourBedOccWh = new double[35]; // make array for hourBedOccWh double[] hourQueue0 = new double[35]; // make array for queue 0 double[] hourQueueDocPl = new double[35]; // make array for queue docpl double[] hourQueueDocWh = new double[35]; // make array for queue docwh double[] hourQueuePreDonPl = new double[35]; double[] hourQueuePreDonWh = new double[35]; double[] sojournPreDonPl = new double[35]; double[] sojournPreDonWh = new double[35]; double[] sojournTotDonPl = new double[35]; double[] sojournTotDonWh = new double[35]; double[] questionnaireN = new double[35]; double[] availableNurse = new double[35]; double[] Pwaitforconnect = new double[35]; double[] Wwaitforconnect = new double[35]; double[] waitfordisconnect = new double[35]; double t = 0; Event firstPlasma = new Event(0, Event.ARRIVAL, t, 0); fes.addEvent(firstPlasma); Event firsthour = new Event(0, Event.HOURSTAT, t + 60, 0); fes.addEvent(firsthour); results.registerNewDonor(listID++, Donor.PLASMA, t);//ID=0 double it = wholeInterarrivalDistribution[0].nextRandom(); Event firstWhole = new Event(1, Event.ARRIVAL, t + it, 0); fes.addEvent(firstWhole); results.registerNewDonor(listID++, Donor.WHOLE, it);// ID=1 while (1 == 1) { Event e = fes.nextEvent(); if (e == null) break; t = e.getTime();// tNurse=eNurse.getTime(); cID = e.getEventID(); cType = results.getTypeFromList(cID); if (fes.getSizeFES() == 1) { endtime = t; } if (e.getEventType() == e.HOURSTAT) { int hst = 0; hst = (int) Math.floor(t / 60); if (hst <= 14) { results.registerDonorNum(Event.REGISTRATION, t, queue0.getSize(), 0); results.registerDonorNum(Event.QUESTIONAIRE, t, results.questionnaireN, 0); results.registerDonorNum(Event.PREINTERVIEW, t, queue1.getSize(), 0); results.registerDonorNum(Event.PREINTERVIEW, t, queue2.getSize(), 1); results.registerDonorNum(Event.DOCINTERVIEW, t, 0, 0); results.registerDonorNum(Event.PREDONATION, t, queue3.getSize(), 0); results.registerDonorNum(Event.PREDONATION, t, queue4.getSize(), 1); results.registerDonorNum(Event.ENTERDONATIONROOM, t, results.plasmaAtDroom, 0); results.registerDonorNum(Event.ENTERDONATIONROOM, t, results.wholeAtDroom, 1); results.registerDonorNum(Event.NURSEFREE, t, 0, 0); results.registerDonorNum(Event.DISCONNECTION, t, queue5.getSize(), 0); results.registerDonorNum(Event.CONNECTION, t, queue6.getSize(), 0); results.registerDonorNum(Event.CONNECTION, t, queue7.getSize(), 1); hourBedOccPl[hst] = (results.getMeanDonorNum(Event.ENTERDONATIONROOM, 0) * hst - OldhourBedOccPl * (hst - 1)); hourBedOccWh[hst] = (results.getMeanDonorNum(Event.ENTERDONATIONROOM, 1) * hst - OldhourBedOccWh * (hst - 1)); hourQueue0[hst] = (results.getMeanDonorNum(Event.REGISTRATION, 0) * hst - OldhourQueue0 * (hst - 1)); hourQueueDocPl[hst] = (results.getMeanDonorNum(Event.PREINTERVIEW, 0) * hst - OldhourQueueDocPl * (hst - 1)); hourQueueDocWh[hst] = (results.getMeanDonorNum(Event.PREINTERVIEW, 1) * hst - OldhourQueueDocWh * (hst - 1)); hourQueuePreDonPl[hst] = (results.getMeanDonorNum(Event.PREDONATION, 0) * hst - OldhourQueuePreDonPl * (hst - 1)); hourQueuePreDonWh[hst] = (results.getMeanDonorNum(Event.PREDONATION, 1) * hst - OldhourQueuePreDonWh * (hst - 1)); questionnaireN[hst] = (results.getMeanDonorNum(Event.QUESTIONAIRE, 0) * hst - OldhourQuestionnaireN * (hst - 1)); availableNurse[hst] = (results.getMeanDonorNum(Event.NURSEFREE, 0) * hst - OldavailableNurse * (hst - 1)); Pwaitforconnect[hst] = (results.getMeanDonorNum(Event.CONNECTION, 0) * hst - OldPwaitforconnect * (hst - 1)); Wwaitforconnect[hst] = (results.getMeanDonorNum(Event.CONNECTION, 1) * hst - OldWwaitforconnect * (hst - 1)); waitfordisconnect[hst] = (results.getMeanDonorNum(Event.DISCONNECTION, 0) * hst - Oldwaitfordisconnect * (hst - 1)); sojournPreDonPl[hst] = (results.getMeanSojournTimePlasmaPreDonation() * hst - OldsojournPreDonPl * (hst - 1)); sojournTotDonPl[hst] = (results.getMeanSojournTimePlasmaDonation() * hst - OldsojournTotDonPl * (hst - 1)); sojournPreDonWh[hst] = (results.getMeanSojournTimeWholePreDonation() * hst - OldsojournPreDonWh * (hst - 1)); sojournTotDonWh[hst] = (results.getMeanSojournTimeWholeDonation() * hst - OldsojournTotDonWh * (hst - 1)); OldhourBedOccPl = results.getMeanDonorNum(Event.ENTERDONATIONROOM, 0); OldhourBedOccWh = results.getMeanDonorNum(Event.ENTERDONATIONROOM, 1); OldhourQueue0 = results.getMeanDonorNum(Event.REGISTRATION, 0); OldhourQueueDocPl = results.getMeanDonorNum(Event.PREINTERVIEW, 0); OldhourQueueDocWh = results.getMeanDonorNum(Event.PREINTERVIEW, 1); OldhourQueuePreDonPl = results.getMeanDonorNum(Event.PREDONATION, 0); OldhourQueuePreDonWh = results.getMeanDonorNum(Event.PREDONATION, 1); OldsojournPreDonPl = results.getMeanSojournTimePlasmaPreDonation(); OldsojournTotDonPl = results.getMeanSojournTimePlasmaDonation(); OldsojournPreDonWh = results.getMeanSojournTimeWholePreDonation(); OldsojournTotDonWh = results.getMeanSojournTimeWholeDonation(); OldhourQuestionnaireN = results.getMeanDonorNum(Event.QUESTIONAIRE, 0); OldavailableNurse = results.getMeanDonorNum(Event.NURSEFREE, 0); OldPwaitforconnect = results.getMeanDonorNum(Event.CONNECTION, 0); OldWwaitforconnect = results.getMeanDonorNum(Event.CONNECTION, 1); Oldwaitfordisconnect = results.getMeanDonorNum(Event.DISCONNECTION, 0); } if (hst <= 16) { Event hourstat = new Event(0, Event.HOURSTAT, t + 60, 0); fes.addEvent(hourstat); } if (hst == 15) { continue; } else continue; } if (e.getEventType() == e.ARRIVAL) { if (t >= 12 * 60) continue;//original setting:t>=12*60 if (cType == Donor.PLASMA) { if (t + 6 < 655) {//only 110 plasma donors results.registerNewDonor(listID, Donor.PLASMA, t + 6); Event nextPlasma = new Event(listID++, Event.ARRIVAL, t + 6, 0); fes.addEvent(nextPlasma); } Random rnd = new Random(); double s = rnd.nextDouble(); if (s < 0.15) { continue;//skip current iteration } cDonor = results.getDonorAt(cID); queue0.addDonor(cDonor); if (queue0.getSize() <= 1) { queue0.removeFirstDonor(); Event registration = new Event(cID, Event.REGISTRATION, t, 0); fes.addEvent(registration); } } else {//cType==WHOLE cDonor = results.getDonorAt(cID); queue0.addDonor(cDonor); if (queue0.getSize() <= 1) { Event registration = new Event(cID, Event.REGISTRATION, t, 0); fes.addEvent(registration); } int ti = 0;//time interval ti = (int) (Math.floor(t / 30)); double tt = wholeInterarrivalDistribution[ti].nextRandom(); if (t + tt > ((ti + 1) * 30)) { ti++; t = (Math.floor(t / 30) + 1) * 30; if (ti == 24) { tt = 1;//add 1 more minute to judge the end point } else { tt = wholeInterarrivalDistribution[ti].nextRandom(); } } results.registerNewDonor(listID, Donor.WHOLE, t + tt); Event nextWhole = new Event(listID++, Event.ARRIVAL, t + tt, 0); fes.addEvent(nextWhole); } } if (e.getEventType() == Event.REGISTRATION) { results.registerDonorNum(e.getEventType(), t, 1 + queue0.getSize(), 0); Event questionnaire = new Event(cID, Event.QUESTIONAIRE, t + procedureDistributions[0].nextRandom(), 0); // start questionnaire after reg.service complete fes.addEvent(questionnaire); } if (e.getEventType() == Event.QUESTIONAIRE) {//departure from register results.questionnaireN++; results.registerDonorNum(e.getEventType(), t, results.questionnaireN, 0); if (queue0.getSize() > 0) { Event registration = new Event(queue0.getFirstDonor().getIDdonor(), Event.REGISTRATION, t, 0); fes.addEvent(registration); queue0.removeFirstDonor(); } else {//no one waits for registration results.registerDonorNum(Event.REGISTRATION, t, 0, 0); } Event preinterview = new Event(cID, Event.PREINTERVIEW, t + procedureDistributions[1].nextRandom(), 0); fes.addEvent(preinterview); } if (e.getEventType() == Event.PREINTERVIEW) { results.questionnaireN--; results.registerDonorNum(Event.QUESTIONAIRE, t, results.questionnaireN, 0); //questionnaire-1donor //results.docinterN++; if (cType == Donor.PLASMA) { //results.registerDonorNum(Event.PREINTERVIEW, t, queue1.getSize(),0);//preinterview queue1.addDonor(results.getDonorAt(cID)); if (queue1.getSize() <= 1) { for (int i = 0; i < results.isdocbusy.length; i++) { if (results.isdocbusy[i] == 0) { Event docinterview = new Event(cID, Event.DOCINTERVIEW, t, i); fes.addEvent(docinterview); queue1.removeFirstDonor(); //results.registerDonorNum(Event.PREINTERVIEW, t, queue1.getSize(),0); break; } } } results.registerDonorNum(Event.PREINTERVIEW, t, queue1.getSize(), 0); } else {//whole donor //results.registerDonorNum(Event.PREINTERVIEW, t, queue2.getSize(),1); queue2.addDonor(results.getDonorAt(cID)); if (queue2.getSize() <= 1 && queue1.getSize() == 0) { for (int i = 0; i < results.isdocbusy.length; i++) { if (results.isdocbusy[i] == 0) { Event docinterview = new Event(cID, Event.DOCINTERVIEW, t, i); fes.addEvent(docinterview); queue2.removeFirstDonor(); //results.registerDonorNum(Event.PREINTERVIEW, t, queue2.getSize(),1); break; } } } results.registerDonorNum(Event.PREINTERVIEW, t, queue2.getSize(), 1); //newplace of registration } } if (e.getEventType() == Event.DOCINTERVIEW) { results.registerDonorNum(Event.DOCINTERVIEW, t, 0, 0); results.isdocbusy[e.getFlag()] = 1; double itt = procedureDistributions[2].nextRandom();//interview time Event predonation = new Event(cID, Event.PREDONATION, t + itt, e.getFlag());//i:doctor i fes.addEvent(predonation); } if (e.getEventType() == Event.PREDONATION) { //results.registerTempSojournTime(t-); results.registerDonorNum(Event.DOCINTERVIEW, t, 0, 0); results.isdocbusy[e.flag] = 0; if (queue1.getSize() > 0) {//plasma donor's waiting for interview Event docinterview = new Event(queue1.getFirstDonor().getIDdonor(), Event.DOCINTERVIEW, t, 0); fes.addEvent(docinterview); queue1.removeFirstDonor(); results.registerDonorNum(Event.PREINTERVIEW, t, queue1.getSize(), 0); } else if (queue2.getSize() > 0) {//check whole donor's waiting Event docinterview = new Event(queue2.getFirstDonor().getIDdonor(), Event.DOCINTERVIEW, t, 0); fes.addEvent(docinterview); queue2.removeFirstDonor(); results.registerDonorNum(Event.PREINTERVIEW, t, queue2.getSize(), 1); } Random rnd = new Random(); double s = rnd.nextDouble(); if (s < 0.05)//ineligible { continue;//skip current iteration } if (cType == Donor.PLASMA) { queue3.addDonor(results.getDonorAt(cID));//add plasma donor to pre-donation queue results.registerDonorNum(Event.PREDONATION, t, queue3.getSize(), 0);//register if (queue3.getSize() <= 1) {//also needs to check any bed and nurse available or not if (results.isBedAvailable(Donor.PLASMA) == 1) { queue3.removeFirstDonor(); Event donation = new Event(cID, Event.ENTERDONATIONROOM, t, 0); fes.addEvent(donation); } } } else { queue4.addDonor(results.getDonorAt(cID));//add whole donor to pre-donation queue results.registerDonorNum(Event.PREDONATION, t, queue4.getSize(), 1);//register if (queue4.getSize() <= 1) {//also needs to check any bed is available or not if (results.isBedAvailable(Donor.WHOLE) == 1) { queue4.removeFirstDonor(); Event donation = new Event(cID, Event.ENTERDONATIONROOM, t, 0); fes.addEvent(donation); } } } } if (e.getEventType() == Event.ENTERDONATIONROOM) { if (cType == Donor.PLASMA) { results.registerPlasmaPreDonationSojournTime(t - results.getDonorAt(cID).arrivalTime); results.registerDonorNum(Event.PREDONATION, t, queue3.getSize(), 0); results.plasmaAtDroom++; results.registerDonorNum(e.getEventType(), t, results.plasmaAtDroom, 0); results.registerDonorNum(Event.CONNECTION, t, queue6.getSize(), 0); } else { results.registerWholePreDonationSojournTime(t - results.getDonorAt(cID).arrivalTime); results.registerDonorNum(Event.PREDONATION, t, queue4.getSize(), 1); results.wholeAtDroom++; results.registerDonorNum(e.getEventType(), t, results.wholeAtDroom, 1); results.registerDonorNum(Event.CONNECTION, t, queue7.getSize(), 1); } results.registerPreDonationSojournTime(t - results.getDonorAt(cID).arrivalTime); //check any nurse is available results.registerBed(1, cType); int iflag = 0; int i = 0; do { if (results.isnursebusy[i] == 0) { //results.isnursebusy[i]=1; Event connection = new Event(cID, Event.CONNECTION, t, i);//i:nurse num i fes.addEvent(connection); iflag = 1; break; } i++; } while (i < results.isnursebusy.length); //if no nurse available, enter queue if (iflag == 0) { if (cType == Donor.PLASMA) { queue6.addDonor(results.getDonorAt(cID)); } else { queue7.addDonor(results.getDonorAt(cID)); } } } if (e.getEventType() == Event.CONNECTION) { results.registerDonorNum(Event.NURSEFREE, t, 0, 0); results.isnursebusy[e.getFlag()] = 1;//nursebusy0->1 double nt; nt = procedureDistributions[3].nextRandom();//nurse connection time //we can simply check three queues when this nurse finishes connection here, //but to simplify the code, we create event.NURSEFREE //Similarly, we can create event.DOCFREE, but we will only use it once, ...so ,.. Event nurseFree = new Event(0, Event.NURSEFREE, t + nt, e.getFlag()); fes.addEvent(nurseFree); if (cType == Donor.PLASMA) { results.registerDonorNum(Event.CONNECTION, t, queue6.getSize(), 0); Event predisconnect = new Event(cID, Event.DISCONNECTION, t + nt + procedureDistributions[5].nextRandom(), 0); fes.addEvent(predisconnect); } else { results.registerDonorNum(Event.CONNECTION, t, queue7.getSize(), 1); Event predisconnect = new Event(cID, Event.DISCONNECTION, t + nt + procedureDistributions[4].nextRandom(), 0); fes.addEvent(predisconnect); } } if (e.getEventType() == Event.PREDISCONNECT) { results.registerDonorNum(Event.DISCONNECTION, t, queue5.getSize(), 0); int iflag = 0; for (int i = 0; i <= results.isnursebusy.length; i++) {//check if any nurse is available if (results.isnursebusy[i] == 0) { //results.isnursebusy[i]=1; Event disconnection = new Event(cID, Event.DISCONNECTION, t, i);//i:nurse num i fes.addEvent(disconnection); iflag = 1; break; } } if (iflag == 0) {//no nurse is available at this time queue5.addDonor(results.getDonorAt(cID)); } } if (e.getEventType() == Event.DISCONNECTION) { results.registerDonorNum(Event.DISCONNECTION, t, queue5.getSize(), 0); results.registerDonorNum(Event.NURSEFREE, t, 0, 0); results.isnursebusy[e.getFlag()] = 1;//nursebusy0->1 double nt; nt = procedureDistributions[6].nextRandom();//nurse disconnection time Event nurseFree = new Event(0, Event.NURSEFREE, t + nt, e.getFlag()); fes.addEvent(nurseFree); //schedule recovery and leave Event leavedonationroom = new Event(cID, Event.LEAVEDONATIONROOM, t + nt + procedureDistributions[7].nextRandom(), 0); fes.addEvent(leavedonationroom); } if (e.getEventType() == Event.NURSEFREE) { results.registerDonorNum(Event.DISCONNECTION, t, queue5.getSize(), 0); results.registerDonorNum(Event.CONNECTION, t, queue6.getSize(), 0); results.registerDonorNum(Event.CONNECTION, t, queue7.getSize(), 1); //flag1->0 //check three queues. results.registerDonorNum(Event.NURSEFREE, t, 0, 0); results.isnursebusy[e.getFlag()] = 0; if (queue5.getSize() > 0) {//disconnection cDonor = queue5.getFirstDonor(); Event disconnection = new Event(cDonor.getIDdonor(), Event.DISCONNECTION, t, e.getFlag());//i:nurse num i fes.addEvent(disconnection); queue5.removeFirstDonor(); } else if (queue6.getSize() > 0) {//connect plasma cDonor = queue6.getFirstDonor(); Event connection = new Event(cDonor.getIDdonor(), Event.CONNECTION, t, e.getFlag());//i:nurse num i fes.addEvent(connection); queue6.removeFirstDonor(); } else if (queue7.getSize() > 0) {//connect whole cDonor = queue7.getFirstDonor(); Event connection = new Event(cDonor.getIDdonor(), Event.CONNECTION, t, e.getFlag());//i:nurse num i fes.addEvent(connection); queue7.removeFirstDonor(); } } if (e.getEventType() == Event.LEAVEDONATIONROOM) { if (cType == Donor.PLASMA) { results.registerPlasmaSojournTime(t - results.getDonorAt(cID).arrivalTime); results.plasmaAtDroom--; results.registerDonorNum(e.getEventType(), t, results.plasmaAtDroom, 0); } else { results.registerWholeSojournTime(t - results.getDonorAt(cID).arrivalTime); results.wholeAtDroom--; results.registerDonorNum(e.getEventType(), t, results.wholeAtDroom, 1); } results.registerSojournTime(t - results.getDonorAt(cID).arrivalTime); results.registerBed(0, cType);//1 bed becomes available if (cType == Donor.PLASMA && queue3.getSize() > 0) { //queue3.getSize()>0 //cType==Donor.PLASMA&&queue3.getSize()>0 Event donation = new Event(cID, Event.ENTERDONATIONROOM, t, 0); fes.addEvent(donation); queue3.removeFirstDonor(); results.registerDonorNum(Event.PREDONATION, t, queue3.getSize(), 0); } if (cType == Donor.WHOLE && queue4.getSize() > 0) {//queue4.getSize()>0 //cType==Donor.WHOLE&&queue4.getSize()>0 Event donation = new Event(cID, Event.ENTERDONATIONROOM, t, 0); fes.addEvent(donation); queue4.removeFirstDonor(); results.registerDonorNum(Event.PREDONATION, t, queue4.getSize(), 1); } } } Row row = sheet.createRow((short) runs); Cell cell = row.createCell(0); cell.setCellValue(endtime); cell = row.createCell(1); cell.setCellValue(results.getMeanSojournTimePlasmaPreDonation()); cell = row.createCell(2); cell.setCellValue(results.getMeanSojournTimeWholePreDonation()); cell = row.createCell(3); cell.setCellValue(results.getMeanSojournTimePlasmaDonation()); cell = row.createCell(4); cell.setCellValue(results.getMeanSojournTimeWholeDonation()); cell = row.createCell(5); cell.setCellValue(results.getMeanDonorNum(Event.REGISTRATION, 0));// cell = row.createCell(6); cell.setCellValue(results.getMeanDonorNum(Event.QUESTIONAIRE, 0)); cell = row.createCell(7); cell.setCellValue(results.getMeanDonorNum(Event.PREINTERVIEW, 0));//preinterview plasma cell = row.createCell(8); cell.setCellValue(results.getMeanDonorNum(Event.PREINTERVIEW, 1));//preinterview whole cell = row.createCell(9); cell.setCellValue(results.getMeanDonorNum(Event.DOCINTERVIEW, 0));//number of available doctors cell = row.createCell(10); cell.setCellValue(results.getMeanDonorNum(Event.PREDONATION, 0));// cell = row.createCell(11); cell.setCellValue(results.getMeanDonorNum(Event.PREDONATION, 1));// cell = row.createCell(12); cell.setCellValue(results.getMeanDonorNum(Event.ENTERDONATIONROOM, 0));//donor room cell = row.createCell(13); cell.setCellValue(results.getMeanDonorNum(Event.ENTERDONATIONROOM, 1));//donor room for (int i = 0; i < 16; i++) { /*cell=row.createCell(13+i);cell.setCellValue(Math.round(hourBedOccPl[i]* 10000.0) / 10000.0);//donor room Plasma cell=row.createCell(13+1*16+i);cell.setCellValue(Math.round(hourBedOccWh[i]* 10000.0) / 10000.0);//donor room Whole cell=row.createCell(13+2*16+i);cell.setCellValue(Math.round(hourQueue0[i]* 10000.0) / 10000.0);//Queue lenght reception cell=row.createCell(13+3*16+i);cell.setCellValue(Math.round(hourQueueDocPl[i]* 10000.0) / 10000.0);//Queue length doctor plasma cell=row.createCell(13+4*16+i);cell.setCellValue(Math.round(hourQueueDocWh[i]* 10000.0) / 10000.0);//Queue length doctor whole cell=row.createCell(13+5*16+i);cell.setCellValue(Math.round(sojournPreDonPl[i]* 10000.0) / 10000.0); cell=row.createCell(13+6*16+i);cell.setCellValue(Math.round(sojournTotDonPl[i]* 10000.0) / 10000.0); cell=row.createCell(13+7*16+i);cell.setCellValue(Math.round(sojournPreDonWh[i]* 10000.0) / 10000.0); cell=row.createCell(13+8*16+i);cell.setCellValue(Math.round(sojournTotDonWh[i]* 10000.0) / 10000.0); cell=row.createCell(13+9*16+i);cell.setCellValue(Math.round(hourQueuePreDonPl[i]* 10000.0) / 10000.0); cell=row.createCell(13+10*16+i);cell.setCellValue(Math.round(hourQueuePreDonWh[i]* 10000.0) / 10000.0); cell=row.createCell(13+11*16+i);cell.setCellValue(Math.round(questionnaireN[i]* 10000.0) / 10000.0);*/ cell = row.createCell(13 + 1 * 16 + i); cell.setCellValue(Math.round(availableNurse[i] * 10000.0) / 10000.0); cell = row.createCell(13 + 2 * 16 + i); cell.setCellValue(Math.round(Pwaitforconnect[i] * 10000.0) / 10000.0); cell = row.createCell(13 + 3 * 16 + i); cell.setCellValue(Math.round(Wwaitforconnect[i] * 10000.0) / 10000.0); cell = row.createCell(13 + 4 * 16 + i); cell.setCellValue(Math.round(waitfordisconnect[i] * 10000.0) / 10000.0); cell = row.createCell(13 + 5 * 16); cell.setCellValue(Math.round(results.donorList.size()) * 10000.0 / 10000.0); } //other measures can be added }
From source file:bloodbank.Simulation.java
/** * * @param args//w ww. j a v a 2s . co m * @throws IOException */ public static void main(String[] args) throws IOException { //from 8am to 20pm Random rng = new Random(); Distribution plasmaInter = new DiscreteUniformDistribution(6, 6, rng); Distribution[] wholeInter = new Distribution[24]; Distribution[] procedures = new Distribution[10]; constructDistribution(wholeInter, procedures, rng); Simulation sim = new Simulation(plasmaInter, wholeInter, procedures); // Create the sheet Workbook wb = new HSSFWorkbook(); CreationHelper createHelper = wb.getCreationHelper(); Sheet sheet = wb.createSheet("new sheet"); int runs = 1; Row row = sheet.createRow((short) 0); Cell cell = row.createCell(0); cell.setCellValue(createHelper.createRichTextString("Total running time")); cell = row.createCell(1); cell.setCellValue(createHelper.createRichTextString("P pre-donation sojourn time")); cell = row.createCell(2); cell.setCellValue(createHelper.createRichTextString("W pre-donation sojourn time")); cell = row.createCell(3); cell.setCellValue(createHelper.createRichTextString("P total sojourn time")); cell = row.createCell(4); cell.setCellValue(createHelper.createRichTextString("W total sojourn time")); cell = row.createCell(5); cell.setCellValue(createHelper.createRichTextString("Qmean # P&W at registration")); cell = row.createCell(6); cell.setCellValue(createHelper.createRichTextString("Qmean # P&W at questionnaire")); cell = row.createCell(7); cell.setCellValue(createHelper.createRichTextString("Qmean # P at (pre-)interview")); cell = row.createCell(8); cell.setCellValue(createHelper.createRichTextString("Qmean # W at (pre-)interview")); cell = row.createCell(9); cell.setCellValue(createHelper.createRichTextString("# of available doctors")); cell = row.createCell(10); cell.setCellValue(createHelper.createRichTextString("Qmean # P at pre-donation room")); cell = row.createCell(11); cell.setCellValue(createHelper.createRichTextString("Qmean # W at pre-donation room")); cell = row.createCell(12); cell.setCellValue(createHelper.createRichTextString("Qmean # P at donation room")); cell = row.createCell(13); cell.setCellValue(createHelper.createRichTextString("Qmean # W at donation room")); for (int i = 0; i < 16; i++) { /*cell=row.createCell(13+i);cell.setCellValue(createHelper.createRichTextString("BedOcc.Pl " + "hr " + (8 + i)));//donor room Plasma cell=row.createCell(13+1*16+i);cell.setCellValue(createHelper.createRichTextString("BedOcc.Wh " + "hr" + (8 + i)));//donor room Whole cell=row.createCell(13+2*16+i);cell.setCellValue(createHelper.createRichTextString("Queue0" + "hr" + (8 + i)));//Queue lenght reception cell=row.createCell(13+3*16+i);cell.setCellValue(createHelper.createRichTextString("QueueDocPl" + "hr" + (8 + i)));//Queue length doctor plasma cell=row.createCell(13+4*16+i);cell.setCellValue(createHelper.createRichTextString("QueueDocWh" + "hr" + (8 + i)));//Queue length doctor whole cell=row.createCell(13+5*16+i);cell.setCellValue(createHelper.createRichTextString("SJT Pl PreDon" + "hr" + (8 + i))); cell=row.createCell(13+6*16+i);cell.setCellValue(createHelper.createRichTextString("SJT Pl TotDon" + "hr" + (8 + i))); cell=row.createCell(13+7*16+i);cell.setCellValue(createHelper.createRichTextString("SJT Wh PreDon" + "hr" + (8 + i))); cell=row.createCell(13+8*16+i);cell.setCellValue(createHelper.createRichTextString("SJT Wh TotDon" + "hr" + (8 + i))); cell=row.createCell(13+9*16+i);cell.setCellValue(createHelper.createRichTextString("QueuePreDonPl" + "hr" + (8 + i))); cell=row.createCell(13+10*16+i);cell.setCellValue(createHelper.createRichTextString("QueuePreDonWh" + "hr" + (8 + i))); cell=row.createCell(13+11*16+i);cell.setCellValue(createHelper.createRichTextString("QuestionNaire" + "hr" + (8 + i))); */ cell = row.createCell(13 + 1 * 16 + i); cell.setCellValue(createHelper.createRichTextString("AvailableNurse" + "hr" + (8 + i))); cell = row.createCell(13 + 2 * 16 + i); cell.setCellValue(createHelper.createRichTextString("P Wait for connect" + "hr" + (8 + i))); cell = row.createCell(13 + 3 * 16 + i); cell.setCellValue(createHelper.createRichTextString("W Wait for connect" + "hr" + (8 + i))); cell = row.createCell(13 + 4 * 16 + i); cell.setCellValue(createHelper.createRichTextString("Wait for disconnect" + "hr" + (8 + i))); } //other measures can be added, see all measures in line 364-379, as well as variance while (runs <= 10000) {//runs=10000 costs 9 seconds sim.simulate(sheet, runs); runs++; } FileOutputStream fileOut = new FileOutputStream("correct.xls");//name of the excel file wb.write(fileOut); fileOut.close(); }
From source file:bouttime.fileinput.ExcelFileInputTest.java
License:Open Source License
private static void makeWrestler(Sheet s, int rownum, String fn, String ln, String c, String d, String wc, String aw, String tn, String l, String sn, String gn) { Row r = s.createRow(rownum); if (fn != null) { r.createCell(0).setCellValue(fn); }//from ww w . j ava 2 s. c o m if (ln != null) { r.createCell(1).setCellValue(ln); } if (c != null) { r.createCell(2).setCellValue(c); } if (d != null) { r.createCell(3).setCellValue(d); } if (wc != null) { r.createCell(4).setCellValue(wc); } if (l != null) { r.createCell(5).setCellValue(l); } if (tn != null) { r.createCell(6).setCellValue(tn); } if (aw != null) { r.createCell(7).setCellValue(aw); } if (sn != null) { r.createCell(8).setCellValue(sn); } if (gn != null) { r.createCell(9).setCellValue(gn); } }
From source file:br.com.algoritmo.compilacao.CompilaXlsx.java
License:Apache License
public static void main(String[] args) throws Exception { Workbook wb;/*w w w.j a v a 2s . c o m*/ Map<Integer, Object[]> data = new TreeMap<Integer, Object[]>(); data.put(0, new Object[] { 0, "Luiz Carlos Miyadaira Ribeiro Junior", "Base", "0468265522433921", "SOFTWARE", null, null, null }); data.put(1, new Object[] { 1, "Sergio Antnio Andrade de Freitas", "Destino 1", "0395549254894676", "SOFTWARE", null, null, null }); data.put(2, new Object[] { 2, "Andre Luiz Aquere de Cerqueira e Souza", "Destino 2", "8424412648258970", "CIVIL", null, null, null }); data.put(3, new Object[] { 3, "Edson Mintsu Hung Destino", "Destino 3", "6753551743147880", "ELETRNICA", null, null, null }); data.put(4, new Object[] { 4, "Edgard Costa Oliveira", "Destino 4", "1196380808351110", "SOFTWARE", null, null, null }); data.put(5, new Object[] { 5, "Edson Alves da Costa Jnior", "Destino 5", "2105379147123450", "SOFTWARE", null, null, null }); data.put(6, new Object[] { 6, "Andr Barros de Sales", "Destino 6", "7610669796869660", "SOFTWARE", null, null, null }); data.put(7, new Object[] { 7, "Giovanni Almeida dos Santos", "Destino 7", "0580891429319047", "SOFTWARE", null, null, null }); data.put(8, new Object[] { 8, "Cristiane Soares Ramos", "Destino 8", "9950213660160160", "SOFTWARE", null, null, null }); data.put(9, new Object[] { 9, "Fabricio Ataides Braz", "Destino 9", "1700216932505000", "SOFTWARE", null, null, null }); data.put(10, new Object[] { 10, "Alexandre Srgio de Arajo Bezerra", "Destino 10", "0255998976169051", "MEDICINA", null, null, null }); data.put(11, new Object[] { 11, "Eduardo Stockler Tognetti", "Destino 11", "2443108673822680", "ELTRICA", null, null, null }); data.put(12, new Object[] { 12, "Jan Mendona Correa", "Destino 12", "7844006017790570", "CINCIA DA COMPUTAO", null, null, null }); data.put(13, new Object[] { 13, "Rejane Maria da Costa Figueiredo", "Destino 13", "2187680174312042", "SOFTWARE", null, null, null }); data.put(14, new Object[] { 14, "Augusto Csar de Mendona Brasil", "Destino 14", "0571960641751286", "ENERGIA", null, null, null }); data.put(15, new Object[] { 15, "Fbio Macdo Mendes", "Destino 15", "8075435338067780", "F?SICA", null, null, null }); if (args.length > 0 && args[0].equals("-xls")) wb = new HSSFWorkbook(); else wb = new XSSFWorkbook(); Map<String, CellStyle> styles = createStyles(wb); Sheet aba1 = wb.createSheet("Percentual de similaridade 1"); PrintSetup printSetup = aba1.getPrintSetup(); printSetup.setLandscape(true); aba1.setFitToPage(true); aba1.setHorizontallyCenter(true); Sheet aba2 = wb.createSheet("Percentual de similaridade 2"); PrintSetup printSetup2 = aba2.getPrintSetup(); printSetup2.setLandscape(true); aba1.setFitToPage(true); aba1.setHorizontallyCenter(true); //title row Row titleRow = aba1.createRow(0); titleRow.setHeightInPoints(15); Cell titleCell = titleRow.createCell(0); titleCell.setCellValue( "Resultado da aplicao do algoritmo de clculo do percentual de similaridade entre os indivduos"); titleCell.setCellStyle(styles.get("title")); aba1.addMergedRegion(CellRangeAddress.valueOf("$A$1:$H$1")); //header row Row headerRow = aba1.createRow(1); headerRow.setHeightInPoints(15); Cell headerCell; for (int i = 1; i <= titles.length; i++) { headerCell = headerRow.createCell(i); headerCell.setCellValue(titles[i - 1]); headerCell.setCellStyle(styles.get("header")); } Row headerBase = aba1.createRow(2); headerBase.setHeightInPoints(15); Cell headerCellBase; for (int i = 1; i <= base.length; i++) { headerCellBase = headerBase.createCell(i); headerCellBase.setCellValue(base[i - 1]); headerCellBase.setCellStyle(styles.get("header1")); } Row headerDestino = aba1.createRow(4); headerDestino.setHeightInPoints(15); Cell headerCellDestino; for (int i = 1; i <= destino.length; i++) { headerCellDestino = headerDestino.createCell(i); headerCellDestino.setCellValue(destino[i - 1]); headerCellDestino.setCellStyle(styles.get("header1")); } /*int rownum = 2; for (int i = 0; i < 10; i++) { Row row = sheet.createRow(rownum++); for (int j = 0; j < titles.length; j++) { Cell cell = row.createCell(j); if(j == 9){ //the 10th cell contains sum over week days, e.g. SUM(C3:I3) String ref = "C" +rownum+ ":I" + rownum; cell.setCellFormula("SUM("+ref+")"); cell.setCellStyle(styles.get("formula")); } else if (j == 11){ cell.setCellFormula("J" +rownum+ "-K" + rownum); cell.setCellStyle(styles.get("formula")); } else { cell.setCellStyle(styles.get("cell")); } } } rownum = 3; for (int i = 0; i < 10; i++) { Row row = sheet.createRow(rownum++); for (int j = 0; j < titles1.length; j++) { Cell cell = row.createCell(j); if(j == 9){ //the 10th cell contains sum over week days, e.g. SUM(C3:I3) String ref = "C" +rownum+ ":I" + rownum; cell.setCellFormula("SUM("+ref+")"); cell.setCellStyle(styles.get("formula")); } else if (j == 11){ cell.setCellFormula("J" +rownum+ "-K" + rownum); cell.setCellStyle(styles.get("formula")); } else { cell.setCellStyle(styles.get("cell")); } } } */ //set sample data //Iterate over data and write to sheet Set<Integer> keyset = data.keySet(); int rownum = 0; for (Integer key : keyset) { Row row = aba1.createRow(3 + rownum++); Object[] objArr = data.get(key); int cellnum = 0; for (Object obj : objArr) { Cell cell = row.createCell(cellnum++); if (obj instanceof String) cell.setCellValue((String) obj); else if (obj instanceof Integer) cell.setCellValue((Integer) obj); } if (row.getRowNum() == 3) { rownum++; } } //finally set column widths, the width is measured in units of 1/256th of a character width aba1.setColumnWidth(0, 2 * 256); //2 characters wide aba1.setColumnWidth(1, 26 * 256); //26 characters wide aba1.setColumnWidth(2, 20 * 256); //20 characters wide aba1.setColumnWidth(3, 18 * 256); //18 characters wide aba1.setColumnWidth(4, 20 * 256); //20 characters wide for (int i = 5; i < 9; i++) { aba1.setColumnWidth(i, 15 * 256); //6 characters wide } // Write the output to a file String file = "Sada/Percentual de similaridade.xls"; if (wb instanceof XSSFWorkbook) file += "x"; FileOutputStream out = new FileOutputStream(file); wb.write(out); out.close(); }
From source file:br.com.gartech.nfse.integrador.util.ExcelHelper.java
public Workbook bindXml(Document document, Workbook workbook) throws XPathExpressionException { XPath xPath = XPathFactory.newInstance().newXPath(); NodeList cellValueList = (NodeList) xPath.evaluate("//cellValue", document, XPathConstants.NODESET); NodeList rowNodeList = (NodeList) xPath.evaluate("//row", document, XPathConstants.NODESET); Node rowsNode = (Node) xPath.evaluate("//rows", document, XPathConstants.NODE); Sheet sheet = workbook.getSheetAt(0); for (int i = 0; i < cellValueList.getLength(); i++) { Node cellValue = cellValueList.item(i); String cellName = cellValue.getAttributes().getNamedItem("ref").getTextContent(); String type = cellValue.getAttributes().getNamedItem("type").getTextContent(); String value = cellValue.getTextContent(); CellReference cellRef = new CellReference(cellName); Row row = sheet.getRow(cellRef.getRow()); Cell cell = row.getCell(cellRef.getCol()); if ("number".equals(type)) { double doubleValue = Double.valueOf(value); cell.setCellValue(doubleValue); } else if ("date".equals(type)) { Date dateValue = new Date(Long.valueOf(value)); cell.setCellValue(dateValue); } else if ("bool".equals(type)) { boolean boolValue = Boolean.valueOf(value); cell.setCellValue(boolValue); } else if ("formula".equals(type)) { cell.setCellFormula(value);//from w w w . j ava 2 s. c o m } else { cell.setCellValue(value); } } if (rowsNode != null && rowNodeList != null && rowNodeList.getLength() > 0) { CellReference startCellRef = new CellReference( rowsNode.getAttributes().getNamedItem("startRef").getTextContent()); CellReference endCellRef = new CellReference( rowsNode.getAttributes().getNamedItem("endRef").getTextContent()); int startRowIndex = startCellRef.getRow(); int startColIndex = startCellRef.getCol(); int endColIndex = endCellRef.getCol(); CellStyle[] cellStyles = new CellStyle[endColIndex + 1]; Row firstRow = sheet.getRow(startRowIndex); for (int i = startColIndex; i <= endColIndex; i++) { cellStyles[i] = firstRow.getCell(i).getCellStyle(); } for (int i = startRowIndex; i <= sheet.getLastRowNum(); i++) { Row templeteRow = sheet.getRow(i); if (templeteRow != null) { sheet.removeRow(templeteRow); } } int rowNodeIndex = 0; for (int i = startRowIndex; i < startRowIndex + rowNodeList.getLength(); i++) { Row row = sheet.createRow(i); int cellNodeIndex = 0; Node rowNode = rowNodeList.item(rowNodeIndex); NodeList rowValueNodeList = rowNode.getChildNodes(); ArrayList<Node> nodes = new ArrayList<Node>(); for (int idx = 0; idx < rowValueNodeList.getLength(); idx++) { Node currentNode = rowValueNodeList.item(idx); if (currentNode.getNodeType() == Node.ELEMENT_NODE) { nodes.add(currentNode); } } for (int j = startColIndex; j <= endColIndex; j++) { Cell cell = row.createCell(j); Node cellNode = nodes.get(cellNodeIndex); String type = cellNode.getAttributes().getNamedItem("type").getTextContent(); String value = cellNode.getTextContent(); CellStyle cellStyle = cellStyles[j]; cell.setCellStyle(cellStyle); if ("number".equals(type)) { double doubleValue = Double.valueOf(value); cell.setCellValue(doubleValue); } else if ("date".equals(type)) { Date dateValue = new Date(Long.valueOf(value)); cell.setCellValue(dateValue); } else if ("bool".equals(type)) { boolean boolValue = Boolean.valueOf(value); cell.setCellValue(boolValue); } else if ("formula".equals(type)) { cell.setCellType(HSSFCell.CELL_TYPE_FORMULA); cell.setCellFormula(value); } else if ("string".equals(type)) { if (value != null && value.length() > 0) { cell.setCellValue(value); } else { cell.setCellValue(""); } } else { cell.setCellValue(""); } cellNodeIndex++; } rowNodeIndex++; } } return workbook; }
From source file:br.com.itfox.test.Excel.java
public void gerarExcel(boolean simple) { try {/* ww w. ja v a 2s. c o m*/ String ini = "01/01/2015"; String fim = "22/06/2016"; String seg = "'21','22','23'"; String areaOper = "'47','24','23','29','4','18','5','48','10','31','43','35','36','7','33','45','3','32','9','39','13','38','16','44','30','15','2','17','12','6','42','41','34','40','1','19','14','26','22','51','46','49','27','25','8','50','52','28','11','20','37','21'"; BusinessDelegate bd = new BusinessDelegate(); String path = "/Users/belchiorpalma/Desktop/template/"; String pathTemplate = "/Users/belchiorpalma/NetBeansProjects/Quest_Iveco/src/br/com/itfox/generator/"; InputStream is = null; try { is = new FileInputStream(pathTemplate + "TemplateGic.xlsx"); } catch (FileNotFoundException ex) { // Logger.getLogger(Excel.class.getName()).log(Level.SEVERE, null, ex); ex.printStackTrace(); } //try(InputStream is = GeneratorObjectCollection.class.getResourceAsStream(pathTemplate+"TemplateGic.xlsx")) // { SimpleDateFormat sdf = new SimpleDateFormat("dd_M_yyyy_hh_mm_ss"); String date = sdf.format(new Date()); Workbook wb = new SXSSFWorkbook(100); // keep 100 rows in memory, exceeding rows will be flushed to disk Sheet sh = wb.createSheet(); ini = (Utils.dateFormat(ini)); fim = (Utils.dateFormat(fim)); List<Gic> gics = bd.selectGic(ini, fim, seg, areaOper); int i = 0; for (Gic g : gics) { Row row = sh.createRow(i); for (int cellnum = 0; cellnum < 153; cellnum++) { Cell cell = row.createCell(cellnum); cell.setCellValue(g.getC_nomeproprietario()); } i++; } /* for (int rownum = 0; rownum < 1000000; rownum++) { Row row = sh.createRow(rownum); for (int cellnum = 0; cellnum < 2; cellnum++) { Cell cell = row.createCell(cellnum); String address = new CellReference(cell).formatAsString(); cell.setCellValue(address); } }*/ FileOutputStream out; try { out = new FileOutputStream(path + "object_collection_output.xlsx"); wb.write(out); out.close(); } catch (FileNotFoundException ex) { //Logger.getLogger(Excel.class.getName()).log(Level.SEVERE, null, ex); ex.printStackTrace(); } } catch (IOException ex) { // Logger.getLogger(Excel.class.getName()).log(Level.SEVERE, null, ex); ex.printStackTrace(); } }
From source file:br.com.techne.gluonsoft.eowexport.builder.ExcelBuilder.java
License:Apache License
/** * mtodo cria bytes de documento Excel//from w w w . ja v a 2 s . com * @param titles * @param columnIndex * @param dataRows * @param locale * @return * @throws Exception */ public static byte[] createExcelBytes(String[] titles, String[] columnIndex, List<HashMap<String, Object>> dataRows, Locale locale) throws Exception { //Workbook wb = new HSSFWorkbook(); XSSFWorkbook wb = new XSSFWorkbook(); byte[] outBytes; try { HashMap<String, CellStyle> styles = createStyles(wb); Sheet sheet = wb.createSheet("Tab 1"); //turn off gridlines sheet.setDisplayGridlines(false); sheet.setPrintGridlines(false); sheet.setFitToPage(true); sheet.setHorizontallyCenter(true); PrintSetup printSetup = sheet.getPrintSetup(); printSetup.setLandscape(true); //the following three statements are required only for HSSF sheet.setAutobreaks(true); printSetup.setFitHeight((short) 1); printSetup.setFitWidth((short) 1); //the header row: centered text in 48pt font Row headerRow = sheet.createRow(0); headerRow.setHeightInPoints(12.75f); for (int indexColumn = 0; indexColumn < titles.length; indexColumn++) { Cell cell = headerRow.createCell(indexColumn); cell.setCellValue(titles[indexColumn]); if ((titles.length - 1) < indexColumn) { cell.setCellValue(""); } else cell.setCellValue(titles[indexColumn]); cell.setCellStyle(styles.get("header")); } //freeze the first row sheet.createFreezePane(0, 1); Row row; Cell cell; int rownum = 1;//devido constar titulo, comea do indice 1 ValueCellUtil vcutil = new ValueCellUtil(locale); for (int indexRow = 0; indexRow < dataRows.size(); indexRow++, rownum++) { row = sheet.createRow(rownum); HashMap<String, Object> dataRow = dataRows.get(indexRow); if (dataRow == null) continue; List<String> keysAttribs = null; if (columnIndex.length == 0) { keysAttribs = Arrays.asList(dataRow.keySet().toArray(new String[0])); Collections.reverse(keysAttribs); } else { keysAttribs = Arrays.asList(columnIndex); } int colCt = 0; for (String keyAttrib : keysAttribs) { cell = row.createCell(colCt); String styleName; cell.setCellValue(vcutil.parseValue(dataRow.get(keyAttrib)).toString()); //zebrando tabela if (indexRow % 2 == 0) { // even row styleName = "cell_normal_even"; } else { // odd row styleName = "cell_normal_odd"; } if (indexRow == 0) { //setando auto ajuste sheet.autoSizeColumn(colCt); } cell.setCellStyle(styles.get(styleName)); colCt++; } } sheet.setZoom(75); //75% scale // Write the output to a file // write for return byte[] ByteArrayOutputStream out = new ByteArrayOutputStream(); try { wb.write(out); outBytes = out.toByteArray(); } finally { out.close(); } } finally { wb.close(); } return outBytes; }
From source file:br.com.tecsinapse.dataio.util.WorkbookUtil.java
License:LGPL
public Workbook toWorkBook(Workbook wb, Table table) { List<List<TableCell>> matrix = table.getCells(); List<List<TableCell>> matrixFull = table.toTableCellMatrix(); replaceColorsPallete(table.getColorsReplaceMap(), wb); String sheetName = table.getTitle(); Sheet sheet = sheetName == null ? wb.createSheet() : wb.createSheet(sheetName); int titleRows = 0; int r = titleRows; int c = 0;// w w w. ja v a 2 s . com int maxColumns = -1; Map<Integer, Integer> defaultColumnWidth = new HashMap<>(); ExporterFormatter tableExporterFormatter = table.getExporterFormatter(); for (List<TableCell> row : matrix) { Row sheetRow = sheet.createRow(r); for (TableCell tableCell : row) { while (matrixFull.get(r - titleRows).get(c) == EmptyTableCell.EMPTY_CELL) { c++; if (c >= matrixFull.get(r - titleRows).size()) { c = 0; r++; } } Cell cell = sheetRow.createCell(c); if (c > maxColumns) { maxColumns = c; } if (tableCell.getRowspan() > 1 || tableCell.getColspan() > 1) { int rowStart = r; int rowEnd = rowStart + (tableCell.getRowspan() - 1); int colStart = c; int colEnd = colStart + (tableCell.getColspan() - 1); CellRangeAddress cellRange = new CellRangeAddress(rowStart, rowEnd, colStart, colEnd); sheet.addMergedRegion(cellRange); RegionUtil.setBorderTop(BorderStyle.THIN, cellRange, sheet); RegionUtil.setBorderRight(BorderStyle.THIN, cellRange, sheet); RegionUtil.setBorderBottom(BorderStyle.THIN, cellRange, sheet); RegionUtil.setBorderLeft(BorderStyle.THIN, cellRange, sheet); } else if (!table.isAutoSizeColumnSheet()) { Integer maxColumnWidth = defaultColumnWidth.get(c); if (maxColumnWidth == null) { defaultColumnWidth.put(c, tableCell.getDefaultColumnWidth()); } else { int defaultWidth = tableCell.getDefaultColumnWidth(); if (defaultWidth > maxColumnWidth) { defaultColumnWidth.put(c, defaultWidth); } } } String format = setConvertedValue(cell, tableCell, tableExporterFormatter); setCellStyle(cell, tableCell, wb, format); c++; } r++; c = 0; } if (table.isAutoSizeColumnSheet()) { for (int i = 0; i <= maxColumns; ++i) { if (sheet instanceof SXSSFSheet) { ((SXSSFSheet) sheet).trackColumnForAutoSizing(i); } sheet.autoSizeColumn(i, true); } } else { for (int i = 0; i <= maxColumns; ++i) { if (defaultColumnWidth.get(i) == null) { if (sheet instanceof SXSSFSheet) { ((SXSSFSheet) sheet).trackColumnForAutoSizing(i); } sheet.autoSizeColumn(i, true); } else { int width = table.getMinOrMaxOrActualCellWidth(defaultColumnWidth.get(i)); sheet.setColumnWidth(i, width); } } } return wb; }
From source file:br.com.tecsinapse.exporter.util.WorkbookUtil.java
License:LGPL
public Workbook toWorkBook(Workbook wb, Table table) { List<List<TableCell>> matrix = table.getCells(); List<List<TableCell>> matrixFull = table.toTableCellMatrix(); String sheetName = table.getTitle(); Sheet sheet = sheetName == null ? wb.createSheet() : wb.createSheet(sheetName); int titleRows = 0; int r = titleRows; int c = 0;// w ww.java 2 s . c o m int maxColumns = -1; Map<Integer, Integer> defaultColumnWidth = new HashMap<>(); ExporterFormatter tableExporterFormatter = table.getExporterFormatter(); for (List<TableCell> row : matrix) { Row sheetRow = sheet.createRow(r); for (TableCell tableCell : row) { while (matrixFull.get(r - titleRows).get(c) == EmptyTableCell.EMPTY_CELL) { c++; if (c >= matrixFull.get(r - titleRows).size()) { c = 0; r++; } } Cell cell = sheetRow.createCell(c); if (c > maxColumns) { maxColumns = c; } if (tableCell.getRowspan() > 1 || tableCell.getColspan() > 1) { int rowStart = r; int rowEnd = rowStart + (tableCell.getRowspan() - 1); int colStart = c; int colEnd = colStart + (tableCell.getColspan() - 1); CellRangeAddress cellRange = new CellRangeAddress(rowStart, rowEnd, colStart, colEnd); sheet.addMergedRegion(cellRange); RegionUtil.setBorderTop(1, cellRange, sheet, wb); RegionUtil.setBorderRight(1, cellRange, sheet, wb); RegionUtil.setBorderBottom(1, cellRange, sheet, wb); RegionUtil.setBorderLeft(1, cellRange, sheet, wb); } else if (!table.isAutoSizeColumnSheet()) { Integer maxColumnWidth = defaultColumnWidth.get(c); if (maxColumnWidth == null) { defaultColumnWidth.put(c, tableCell.getDefaultColumnWidth()); } else { int defaultWidth = tableCell.getDefaultColumnWidth(); if (defaultWidth > maxColumnWidth) { defaultColumnWidth.put(c, defaultWidth); } } } String format = setConvertedValue(cell, tableCell, tableExporterFormatter); setCellStyle(cell, tableCell, wb, format); c++; } r++; c = 0; } if (table.isAutoSizeColumnSheet()) { for (int i = 0; i <= maxColumns; ++i) { if (sheet instanceof SXSSFSheet) { ((SXSSFSheet) sheet).trackColumnForAutoSizing(i); } else { sheet.autoSizeColumn(i, true); } } } else { for (int i = 0; i <= maxColumns; ++i) { if (defaultColumnWidth.get(i) == null) { if (sheet instanceof SXSSFSheet) { ((SXSSFSheet) sheet).trackColumnForAutoSizing(i); } else { sheet.autoSizeColumn(i, true); } } else { sheet.setColumnWidth(i, defaultColumnWidth.get(i)); } } } return wb; }
From source file:br.ufal.cideei.util.count.SummaryBuilder.java
License:Open Source License
public static void buildSummary(String splShortName) throws InvalidFormatException, FileNotFoundException, IOException { // final String userHomeFolder = System.getProperty("user.home").substring(3); String userHomeFolder = "C:\\tst"; final String output = userHomeFolder + File.separator + "summ.xls"; File outputFile = new File(output); Workbook outputWorkbook;/*from ww w. j a v a2 s . c om*/ if (!outputFile.exists()) { outputFile.createNewFile(); outputWorkbook = new HSSFWorkbook(); } else { FileInputStream inputFileStream = new FileInputStream(outputFile); outputWorkbook = WorkbookFactory.create(inputFileStream); } { List<String> referencesForRDA3 = new ArrayList<String>(); List<String> referencesForUVA3 = new ArrayList<String>(); List<String> referencesForRDA2 = new ArrayList<String>(); List<String> referencesForUVA2 = new ArrayList<String>(); String fileName = "fs-" + splShortName + ".xls"; String filePath = userHomeFolder + File.separator; String fullFileName = filePath + File.separator + "fs-" + splShortName + ".xls"; Workbook workbook = WorkbookFactory.create(new FileInputStream(new File(fullFileName))); for (int i = 0; i < workbook.getNumberOfSheets(); i++) { Sheet sheet = workbook.getSheetAt(i); Row headerRow = sheet.getRow(0); for (Cell cell : headerRow) { String stringCellValue = cell.getStringCellValue(); if (stringCellValue.equals("rd")) { Row sumRow = sheet.getRow(sheet.getLastRowNum() - 1); Cell sumCell = sumRow.getCell(i); CellReference sumCellRef = new CellReference(sumCell); String cellRefForAnotherSheet = "\'" + filePath + "[" + fileName + "]Sheet" + i + "\'!" + sumCellRef.formatAsString(); referencesForRDA2.add(cellRefForAnotherSheet); } else if (stringCellValue.equals("uv")) { Row sumRow = sheet.getRow(sheet.getLastRowNum() - 1); Cell sumCell = sumRow.getCell(i); CellReference sumCellRef = new CellReference(sumCell); String cellRefForAnotherSheet = "\'" + filePath + "[" + fileName + "]Sheet" + i + "\'!" + sumCellRef.formatAsString(); referencesForUVA2.add(cellRefForAnotherSheet); } else if (stringCellValue.equals("rd (a3)")) { Row sumRow = sheet.getRow(sheet.getLastRowNum() - 1); Cell sumCell = sumRow.getCell(i); CellReference sumCellRef = new CellReference(sumCell); String cellRefForAnotherSheet = "\'" + filePath + "[" + fileName + "]Sheet" + i + "\'!" + sumCellRef.formatAsString(); referencesForRDA3.add(cellRefForAnotherSheet); } else if (stringCellValue.equals("uv (a3)")) { Row sumRow = sheet.getRow(sheet.getLastRowNum() - 1); Cell sumCell = sumRow.getCell(i); CellReference sumCellRef = new CellReference(sumCell); String cellRefForAnotherSheet = "\'" + filePath + "[" + fileName + "]Sheet" + i + "\'!" + sumCellRef.formatAsString(); referencesForUVA3.add(cellRefForAnotherSheet); } } } if (outputWorkbook.getSheet(splShortName) != null) { outputWorkbook.removeSheetAt(outputWorkbook.getSheetIndex(splShortName)); } Sheet outputSheet = outputWorkbook.createSheet(splShortName); Row RDA2Row = outputSheet.createRow(0); RDA2Row.createCell(0).setCellValue("RD A2"); for (int i = 0; i < referencesForRDA2.size(); i++) { Cell createdCell = RDA2Row.createCell(i + 1); System.out.println(referencesForRDA2.get(i)); createdCell.setCellType(Cell.CELL_TYPE_FORMULA); createdCell.setCellValue(referencesForRDA2.get(i)); } Row UVA2Row = outputSheet.createRow(1); UVA2Row.createCell(0).setCellValue("UV A2"); for (int i = 0; i < referencesForUVA2.size(); i++) { Cell createdCell = UVA2Row.createCell(i + 1); createdCell.setCellFormula(referencesForUVA2.get(i)); } Row RDA3Row = outputSheet.createRow(2); RDA3Row.createCell(0).setCellValue("RD A3"); for (int i = 0; i < referencesForRDA3.size(); i++) { Cell createdCell = RDA3Row.createCell(i + 1); createdCell.setCellFormula(referencesForRDA3.get(i)); } Row UVA3Row = outputSheet.createRow(3); UVA3Row.createCell(0).setCellValue("UV A3"); for (int i = 0; i < referencesForUVA3.size(); i++) { Cell createdCell = UVA3Row.createCell(i + 1); createdCell.setCellFormula(referencesForUVA3.get(i)); } } FileOutputStream fileOutputStream = new FileOutputStream(outputFile); outputWorkbook.write(fileOutputStream); fileOutputStream.close(); }