-
Notifications
You must be signed in to change notification settings - Fork 0
Expand file tree
/
Copy pathCountChecks.java
More file actions
116 lines (85 loc) · 4.81 KB
/
CountChecks.java
File metadata and controls
116 lines (85 loc) · 4.81 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
import org.apache.poi.xssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
import java.io.*;
import java.awt.Color;
import org.apache.poi.xssf.model.StylesTable;
public class CountChecks {
public static void main(String args[]){
XSSFWorkbook report = null;
FileOutputStream newFile = null;
try{
//Read in excel file
report = new XSSFWorkbook(new FileInputStream("C:\\Users\\Ryon\\Desktop\\Business_CountReport_06.26.2015.xlsx"));
//Create the style for values that are found to be bad
Font badFont = report.createFont();
badFont.setColor(IndexedColors.RED.getIndex());
CellStyle badCountStyle = report.createCellStyle();
badCountStyle.setFont(badFont);
//Get the Total Count numberical value from the header for comparison.
int totalCount = Integer.parseInt(report.getSheet("Field_Count").getRow(2).getCell(0).getStringCellValue().replaceAll("[\\^A-Za-z:\\s,]", ""));
//-----------------------------------------------CHECK FIELD_COUNT TAB------------------------------------
XSSFSheet fieldCount = report.getSheet("Field_Count");
for(Row currentRow: fieldCount){
double currentCountValue;
double nextCountValue;
String nextFieldName;
int rowCount = fieldCount.getLastRowNum();
XSSFCell currentRowCountCell = (XSSFCell)currentRow.getCell(1);
XSSFCell nextRowCountCell;
String currentFieldName = currentRow.getCell(0).getStringCellValue();
if(currentRow.getRowNum() != rowCount)
nextFieldName = fieldCount.getRow(currentRow.getRowNum() + 1).getCell(0).getStringCellValue();
if(currentRow.getCell(1).getCellType() != Cell.CELL_TYPE_STRING && currentRow.getRowNum() != rowCount && fieldCount.getRow(currentRow.getRowNum() + 1).getCell(1).getCellType() != Cell.CELL_TYPE_STRING){
currentCountValue = currentRow.getCell(1).getNumericCellValue();
nextCountValue = fieldCount.getRow(currentRow.getRowNum() + 1).getCell(1).getNumericCellValue();
nextRowCountCell = (XSSFCell)fieldCount.getRow(currentRow.getRowNum() + 1).getCell(1);
if(currentFieldName.equals("CompanyName") && currentCountValue != totalCount)
currentRowCountCell.setCellStyle(badCountStyle);
if(currentFieldName.equalsIgnoreCase("Exchange") && currentCountValue != nextCountValue){
currentRowCountCell.setCellStyle(badCountStyle);
nextRowCountCell.setCellStyle(badCountStyle);
}
if(currentFieldName.equals("Year_First") && currentCountValue != totalCount)
currentRowCountCell.setCellStyle(badCountStyle);
if(currentFieldName.equals("Year_In_Business_Range") && currentCountValue != totalCount)
currentRowCountCell.setCellStyle(badCountStyle);
if(currentFieldName.equals("CreditCode") && currentCountValue != totalCount)
currentRowCountCell.setCellStyle(badCountStyle);
if(currentFieldName.equals("Credit_Capacity") && currentCountValue != totalCount)
currentRowCountCell.setCellStyle(badCountStyle);
if(currentFieldName.equals("Credit_Description") && currentCountValue != totalCount)
currentRowCountCell.setCellStyle(badCountStyle);
if((currentFieldName.equals("UpdateDate") || currentFieldName.equals("AddDate")) && currentCountValue != totalCount)
currentRowCountCell.setCellStyle(badCountStyle);
if(currentFieldName.equals("SIC01_Code") && currentCountValue != totalCount)
currentRowCountCell.setCellStyle(badCountStyle);
}//End outer if
else
continue;
}//End for
fieldCount = null;
//------------------------------------------------CHECK DETAIL_COUNT TAB-----------------------------------
XSSFSheet detailCount = report.getSheet("Detail_Count");
for(Row currentRow: detailCount){
double currentCountValue;
double nextCountValue;
String nextFieldName;
int rowCount = detailCount.getLastRowNum();
XSSFCell currentRowCountCell = (XSSFCell)currentRow.getCell(1);
XSSFCell nextRowCountCell;
String currentFieldName = currentRow.getCell(0).getStringCellValue();
if(currentRow.getRowNum() != rowCount)
nextFieldName = detailCount.getRow(currentRow.getRowNum() + 1).getCell(0).getStringCellValue();
if(currentRow.getCell(1).getCellType() != Cell.CELL_TYPE_STRING && currentRow.getRowNum() != rowCount && detailCount.getRow(currentRow.getRowNum() + 1).getCell(1).getCellType() != Cell.CELL_TYPE_STRING){
}//End outer if
}//End for
//-----------------------------------------------WRITE CHECKED REPORT--------------------------------------
newFile = new FileOutputStream(new File("C:\\Users\\Ryon\\Desktop\\Business_CountReport_06.26.2015_Checked.xlsx"));
report.write(newFile);
System.out.println("Written successfully");
}
catch(Exception e){
System.out.println(e.getStackTrace()[0].getLineNumber());
}
}//End main
}//End class