Java methods for manipulating excel
- 2020-04-01 04:01:26
- OfStack
This article illustrates a Java method for manipulating excel. Share with you for your reference. The details are as follows:
WritableWorkbook workbook = Workbook.createWorkbook(new File("d:\output.xls"));
WritableSheet sheet = workbook.createSheet(" Project presentations ", 0);
//style
WritableFont sonti18font = new WritableFont(WritableFont.createFont(" Song typeface "), 18,WritableFont.BOLD);
WritableFont sonti12font = new WritableFont(WritableFont.createFont(" Song typeface "), 12,WritableFont.BOLD);
WritableFont sonti12fontNoBold = new WritableFont(WritableFont.createFont(" Song typeface "), 12,WritableFont.NO_BOLD);
WritableCellFormat font18Subject = new WritableCellFormat (sonti18font);
WritableCellFormat font12Subject = new WritableCellFormat (sonti12font);
WritableCellFormat font12SubjectNoBold = new WritableCellFormat (sonti12fontNoBold);
font18Subject.setAlignment(jxl.format.Alignment.CENTRE);
font18Subject.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);
font18Subject.setBorder(jxl.format.Border.ALL,jxl.format.BorderLineStyle.THIN);
font12Subject.setAlignment(jxl.format.Alignment.LEFT);
font12Subject.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);
font12Subject.setBorder(jxl.format.Border.ALL,jxl.format.BorderLineStyle.THIN);
font12SubjectNoBold.setAlignment(jxl.format.Alignment.LEFT);
font12SubjectNoBold.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);
font12SubjectNoBold.setBorder(jxl.format.Border.ALL,jxl.format.BorderLineStyle.THIN);
//styleend
//Project profile information
sheet.addCell(new Label(0, 0, projReportParam.getProj_name() + " Project presentations ",font18Subject));
sheet.mergeCells(0, 0, 5, 0);
sheet.addCell(new Label(0,1," Item no. ",font12Subject));
sheet.addCell(new Label(1,1,projReportParam.getProj_code(),font12SubjectNoBold));
sheet.addCell(new Label(2,1," The project manager ",font12Subject));
sheet.addCell(new Label(3,1,projReportParam.getEmp_name(),font12SubjectNoBold));
sheet.addCell(new Label(4,1," Project department ",font12Subject));
sheet.addCell(new Label(5,1,projReportParam.getDept_name(),font12SubjectNoBold));
sheet.addCell(new Label(0,2," Planned cost ",font12Subject));
sheet.addCell(new Label(1,2,projReportParam.getPlan_cost(),font12SubjectNoBold));
sheet.addCell(new Label(2,2," Actual incurred cost ",font12Subject));
sheet.addCell(new Label(3,2,projReportParam.getActual_cost(),font12SubjectNoBold));
sheet.addCell(new Label(0,3," Project introduction ",font12Subject));
sheet.addCell(new Label(1,3,projReportParam.getProj_desc(),font12SubjectNoBold));
sheet.mergeCells(1, 3, 5, 3);
//Project profile informationend
//milestone
sheet.addCell(new Label(0,5," milestone ",font18Subject));
sheet.mergeCells(0, 5, 5, 5);
sheet.addCell(new Label(0,6," The name of the stage ",font12Subject));
sheet.addCell(new Label(1,6," The name of the task ",font12Subject));
sheet.addCell(new Label(2,6," The implementation of one ",font12Subject));
sheet.addCell(new Label(3,6," Schedule start and end times ",font12Subject));
sheet.addCell(new Label(4,6," Actual starting and ending time ",font12Subject));
sheet.addCell(new Label(5,6," state ",font12Subject));
int i = 7;//line
for(ProjReportParamMInfo projReportParamMInfo : projReportParam.getProjReportParamMInfoList()){
sheet.addCell(new Label(0,i,projReportParamMInfo.getPharse_name(),font12SubjectNoBold));
sheet.addCell(new Label(1,i,projReportParamMInfo.getTask_item(),font12SubjectNoBold));
sheet.addCell(new Label(2,i,projReportParamMInfo.getEmp_name(),font12SubjectNoBold));
sheet.addCell(new Label(3,i,Utils.formatDate(projReportParamMInfo.getTask_plan_sd()) + " / " + Utils.formatDate(projReportParamMInfo.getTask_plan_fd()),font12SubjectNoBold));
sheet.addCell(new Label(4,i,Utils.formatDate(projReportParamMInfo.getTask_actual_sd()) + " / " + Utils.formatDate(projReportParamMInfo.getTask_actual_fd()),font12SubjectNoBold));
sheet.addCell(new Label(5,i,projReportParamMInfo.getTask_state(),font12SubjectNoBold));
i++;
}
//milestoneend
//Project team members and stakeholders
int j = 1+i;//line
sheet.addCell(new Label(0,j," Project team members and stakeholders ",font18Subject));
sheet.mergeCells(0, j, 5, j);
sheet.addCell(new Label(0,j+1," The serial number ",font12Subject));
sheet.addCell(new Label(1,j+1," The name ",font12Subject));
sheet.addCell(new Label(2,j+1," role ",font12Subject));
sheet.addCell(new Label(3,j+1," Time to enter the project team ",font12Subject));
sheet.addCell(new Label(4,j+1," Time away from project team ",font12Subject));
sheet.addCell(new Label(5,j+1," The cumulative working hours ",font12Subject));
int m = 1;//The serial number
int n = j+2;//line
for(ProjReportParamHRInfo projReportParamHRInfo : projReportParam.getProjReportParamHRInfoList()){
sheet.addCell(new Label(0,n,m+"",font12SubjectNoBold));
sheet.addCell(new Label(1,n,projReportParamHRInfo.getEmp_name(),font12SubjectNoBold));
sheet.addCell(new Label(2,n,projReportParamHRInfo.getRole_name(),font12SubjectNoBold));
sheet.addCell(new Label(3,n,Utils.formatDate(projReportParamHRInfo.getHr_start_date()),font12SubjectNoBold));
sheet.addCell(new Label(4,n,Utils.formatDate(projReportParamHRInfo.getHr_release_date()),font12SubjectNoBold));
sheet.addCell(new Label(5,n,projReportParamHRInfo.getTotal_manhour(),font12SubjectNoBold));
m++;
n++;
}
//Project team members and stakeholdersend
//Work contents of this period
int k = n + 1;//line
sheet.addCell(new Label(0,k," Work contents of this period ("+startDate+" to "+endDate+")",font18Subject));
sheet.mergeCells(0, k, 6, k);
sheet.addCell(new Label(0,k+1," Task number ",font12Subject));
sheet.addCell(new Label(1,k+1," The name of the task ",font12Subject));
sheet.addCell(new Label(2,k+1," Schedule start and end times ",font12Subject));
sheet.addCell(new Label(3,k+1," Actual starting and ending time ",font12Subject));
sheet.addCell(new Label(4,k+1," Plan time ",font12Subject));
sheet.addCell(new Label(5,k+1," Actual working hours ",font12Subject));
sheet.addCell(new Label(6,k+1," The implementation of personnel ",font12Subject));
int p = k + 2;//line
for(ProjReportParamTaskInfo projReportParamTaskInfo : projReportParam.getThisProjReportParamTaskInfoList()){
sheet.addCell(new Label(0,p,projReportParamTaskInfo.getTask_code(),font12SubjectNoBold));
sheet.addCell(new Label(1,p,projReportParamTaskInfo.getTask_item(),font12SubjectNoBold));
sheet.addCell(new Label(2,p,Utils.formatDate(projReportParamTaskInfo.getTask_plan_sd()) + " / " + Utils.formatDate(projReportParamTaskInfo.getTask_plan_fd()),font12SubjectNoBold));
sheet.addCell(new Label(3,p,Utils.formatDate(projReportParamTaskInfo.getTask_actual_sd()) + " / " + Utils.formatDate(projReportParamTaskInfo.getTask_actual_fd()),font12SubjectNoBold));
sheet.addCell(new Label(4,p,projReportParamTaskInfo.getTask_plan_manhour(),font12SubjectNoBold));
sheet.addCell(new Label(5,p,projReportParamTaskInfo.getTask_actual_manhour(),font12SubjectNoBold));
sheet.addCell(new Label(6,p,projReportParamTaskInfo.getPlan_emp_name(),font12SubjectNoBold));
p++;
}
//Work contents of this periodend
//Work content for next week
int q = p + 1;//line
sheet.addCell(new Label(0,q," Work content for next week ",font18Subject));
sheet.mergeCells(0, q, 5, q);
sheet.addCell(new Label(0,q+1," Task number ",font12Subject));
sheet.addCell(new Label(1,q+1," The name of the task ",font12Subject));
sheet.addCell(new Label(2,q+1," Scheduled start time ",font12Subject));
sheet.addCell(new Label(3,q+1," Planned end time ",font12Subject));
sheet.addCell(new Label(4,q+1," Plan time ",font12Subject));
sheet.addCell(new Label(5,q+1," The implementation of personnel ",font12Subject));
int r = q + 2;
for(ProjReportParamTaskInfo projReportParamTaskInfo : projReportParam.getNextProjReportParamTaskInfoList()){
sheet.addCell(new Label(0,r,projReportParamTaskInfo.getTask_code(),font12SubjectNoBold));
sheet.addCell(new Label(1,r,projReportParamTaskInfo.getTask_item(),font12SubjectNoBold));
sheet.addCell(new Label(2,r,Utils.formatDate(projReportParamTaskInfo.getTask_plan_sd()),font12SubjectNoBold));
sheet.addCell(new Label(3,r,Utils.formatDate(projReportParamTaskInfo.getTask_plan_fd()),font12SubjectNoBold));
sheet.addCell(new Label(4,r,projReportParamTaskInfo.getTask_plan_manhour(),font12SubjectNoBold));
sheet.addCell(new Label(5,r,projReportParamTaskInfo.getPlan_emp_name(),font12SubjectNoBold));
r++;
}
//Work content for next weekend
//Project problems and solutions
int s = r + 1;//line
sheet.addCell(new Label(0,s," Project problems and solutions ",font18Subject));
sheet.mergeCells(0, s, 8, s);
sheet.addCell(new Label(0,s+1," Identify the date ",font12Subject));
sheet.addCell(new Label(1,s+1," Problem description ",font12Subject));
sheet.addCell(new Label(2,s+1," Subordinate to the risk ",font12Subject));
sheet.addCell(new Label(3,s+1," The solution ",font12Subject));
sheet.addCell(new Label(4,s+1," Solve people ",font12Subject));
sheet.addCell(new Label(5,s+1," Scheduled start time ",font12Subject));
sheet.addCell(new Label(6,s+1," Planned completion time ",font12Subject));
sheet.addCell(new Label(7,s+1," State of the problem ",font12Subject));
sheet.addCell(new Label(8,s+1," conclusion ",font12Subject));
int t = s + 2;//line
for(ProjReportParamRiskInfo projReportParamRiskInfo : projReportParam.getProjReportParamRiskInfoList()){
sheet.addCell(new Label(0,t,Utils.formatDate(projReportParamRiskInfo.getSts_date()),font12SubjectNoBold));
sheet.addCell(new Label(1,t,projReportParamRiskInfo.getProj_issue_item(),font12SubjectNoBold));
sheet.addCell(new Label(2,t,projReportParamRiskInfo.getProj_risk_item(),font12SubjectNoBold));
sheet.addCell(new Label(3,t,projReportParamRiskInfo.getIssue_resolve_method(),font12SubjectNoBold));
sheet.addCell(new Label(4,t,projReportParamRiskInfo.getIssue_resolve_emp(),font12SubjectNoBold));
sheet.addCell(new Label(5,t,Utils.formatDate(projReportParamRiskInfo.getIssue_plan_date()),font12SubjectNoBold));
sheet.addCell(new Label(6,t,Utils.formatDate(projReportParamRiskInfo.getIssue_actual_date()),font12SubjectNoBold));
sheet.addCell(new Label(7,t,projReportParamRiskInfo.getIssue_state(),font12SubjectNoBold));
sheet.addCell(new Label(8,t,projReportParamRiskInfo.getIssue_summarize(),font12SubjectNoBold));
t++;
}
//Project problems and solutionsend
workbook.write();
workbook.close();
//The program that generates the project brief
I hope this article has been helpful to your Java programming.