public void executeJob(Connection con) throws Exception {
// input ����
String fileName = "./기초자료/기초자료.xls";
String sheetName = "GHOST_TABLES";
FileInputStream fis = null;
HSSFWorkbook wb = null;
HSSFSheet sheet = null;
// output ����
String deleteSQL = "delete from ghost_tables";
String insertSQL = "insert into ghost_tables values(?,?,?)";
PreparedStatement pstmt = null;
Statement stmt = null;
try {
// Input �ڷ� �غ�
fis = new FileInputStream(fileName);
wb = new HSSFWorkbook(fis);
sheet = wb.getSheet(sheetName);
// �����ڷ� All ����
stmt = con.createStatement();
stmt.execute(deleteSQL);
// Insert�� SQL Statement ����
pstmt = con.prepareStatement(insertSQL);
HSSFRow row = null;
Bean bean = null;
for (int i = 1; i < sheet.getPhysicalNumberOfRows(); i++) {
row = sheet.getRow(i);
readCount++;
if (row == null) continue;
bean = getBean(row);
pstmt.setString(1, bean.tableName);
pstmt.setString(2, bean.tableDesc);
pstmt.setString(3, bean.shortName);
pstmt.executeUpdate();
writeCount++;
System.out.printf("[%d][%s][%s][%s]\n", i, bean.tableName, bean.tableDesc, bean.shortName);
}
} finally {
if (wb != null) wb.close();
if (fis != null) fis.close();
JDBCUtil.close(stmt);
JDBCUtil.close(pstmt);
}
}
public void testFormulaGeneral() throws IOException {
// perhaps this testcase belongs on TestHSSFName
HSSFWorkbook wb = new HSSFWorkbook();
HSSFName name = wb.createName();
wb.createSheet("Sheet1");
name.setNameName("test");
name.setRefersToFormula("Sheet1!A1+Sheet1!A2");
assertEquals("Sheet1!A1+Sheet1!A2", name.getRefersToFormula());
name.setRefersToFormula("5*6");
assertEquals("5*6", name.getRefersToFormula());
wb.close();
}
public void testDgRecordNumShapes() throws IOException {
HSSFWorkbook wb = new HSSFWorkbook();
try {
HSSFSheet sheet = wb.createSheet();
HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
EscherAggregate aggregate = HSSFTestHelper.getEscherAggregate(patriarch);
EscherDgRecord dgRecord = (EscherDgRecord) aggregate.getEscherRecord(0).getChild(0);
assertEquals(dgRecord.getNumShapes(), 1);
} finally {
wb.close();
}
}
public void testFormulaRelAbs_bug46174() throws IOException {
// perhaps this testcase belongs on TestHSSFName
HSSFWorkbook wb = new HSSFWorkbook();
HSSFName name = wb.createName();
wb.createSheet("Sheet1");
name.setNameName("test");
name.setRefersToFormula("Sheet1!$B$3");
if (name.getRefersToFormula().equals("Sheet1!B3")) {
fail("Identified bug 46174");
}
assertEquals("Sheet1!$B$3", name.getRefersToFormula());
wb.close();
}
public void testBug45312() throws Exception {
HSSFWorkbook wb = new HSSFWorkbook();
try {
HSSFSheet sheet = wb.createSheet();
HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
{
HSSFClientAnchor a1 = new HSSFClientAnchor();
a1.setAnchor((short) 1, 1, 0, 0, (short) 1, 1, 512, 100);
HSSFSimpleShape shape1 = patriarch.createSimpleShape(a1);
shape1.setShapeType(HSSFSimpleShape.OBJECT_TYPE_LINE);
}
{
HSSFClientAnchor a1 = new HSSFClientAnchor();
a1.setAnchor((short) 1, 1, 512, 0, (short) 1, 1, 1024, 100);
HSSFSimpleShape shape1 = patriarch.createSimpleShape(a1);
shape1.setFlipVertical(true);
shape1.setShapeType(HSSFSimpleShape.OBJECT_TYPE_LINE);
}
{
HSSFClientAnchor a1 = new HSSFClientAnchor();
a1.setAnchor((short) 2, 2, 0, 0, (short) 2, 2, 512, 100);
HSSFSimpleShape shape1 = patriarch.createSimpleShape(a1);
shape1.setShapeType(HSSFSimpleShape.OBJECT_TYPE_LINE);
}
{
HSSFClientAnchor a1 = new HSSFClientAnchor();
a1.setAnchor((short) 2, 2, 0, 100, (short) 2, 2, 512, 200);
HSSFSimpleShape shape1 = patriarch.createSimpleShape(a1);
shape1.setFlipHorizontal(true);
shape1.setShapeType(HSSFSimpleShape.OBJECT_TYPE_LINE);
}
/*OutputStream stream = new FileOutputStream("/tmp/45312.xls");
try {
wb.write(stream);
} finally {
stream.close();
}*/
checkWorkbookBack(wb);
} finally {
wb.close();
}
}
public void testOpt() throws Exception {
HSSFWorkbook wb = new HSSFWorkbook();
try {
// create a sheet with a text box
HSSFSheet sheet = wb.createSheet();
HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
HSSFTextbox textbox = patriarch.createTextbox(new HSSFClientAnchor());
EscherOptRecord opt1 = HSSFTestHelper.getOptRecord(textbox);
EscherOptRecord opt2 =
HSSFTestHelper.getEscherContainer(textbox).getChildById(EscherOptRecord.RECORD_ID);
assertSame(opt1, opt2);
} finally {
wb.close();
}
}
public void testShapeContainerImplementsIterable() throws IOException {
HSSFWorkbook wb = new HSSFWorkbook();
try {
HSSFSheet sheet = wb.createSheet();
HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
patriarch.createSimpleShape(new HSSFClientAnchor());
patriarch.createSimpleShape(new HSSFClientAnchor());
int i = 2;
for (HSSFShape shape : patriarch) {
i--;
}
assertEquals(i, 0);
} finally {
wb.close();
}
}
public String getZhFrom4GYsb() throws IOException {
// 预算表中 第三行 B列的名称为： 单项工程名称:SXZH001TL新建、共址2G、共址其他运营商的(F)（D)宏站基站
// path1: 预算表 path2 3g4g基础信息
String results = "";
String result2 = "";
FileInputStream fise = new FileInputStream(excelPath);
HSSFWorkbook wb = new HSSFWorkbook(fise);
HSSFWorkbook wb2 = new HSSFWorkbook(new FileInputStream(excelPath2));
HSSFFormulaEvaluator e = new HSSFFormulaEvaluator(wb);
HSSFFormulaEvaluator e2 = new HSSFFormulaEvaluator(wb2);
// TODO: 将此处的文件名替换为从参数读取的文件名
String[] strArray = new String[2];
String[] paths = this.excelPath.split("\\\\");
strArray[0] = paths[paths.length - 1];
// System.out.println(strArray[0]);
String[] paths1 = this.excelPath2.split("\\\\");
// strArray[1] = paths1[paths1.length-1];
// System.out.println(strArray[1]);
HSSFFormulaEvaluator[] evals = new HSSFFormulaEvaluator[2];
evals[0] = e;
evals[1] = e2;
Sheet sheet = wb.getSheetAt(7);
Row r = null;
r = sheet.getRow(2);
Cell cell2 = r.getCell(1);
Cell cell8 = r.getCell(8);
// System.out.println(cell8.toString());
String[] path1 = cell8.toString().split("\'");
String path12 = path1[3];
String[] path2 = path12.split("]");
String path21 = path2[0];
String path3 = path21.substring(1);
strArray[1] = path3;
// System.out.println(path3);
HSSFFormulaEvaluator.setupEnvironment(strArray, evals);
if (cell2.getCellType() == HSSFCell.CELL_TYPE_FORMULA) {
results = e.evaluate(cell2).getStringValue();
// results = cell2.getStringCellValue();
} else if (cell2.getCellType() == HSSFCell.CELL_TYPE_STRING) {
results = cell2.toString();
}
System.out.println(result2);
// TL TLD TLFD SXZH017TL 前面有7位 TL- conditions
// TL-1、TL-2、TL-3 TLD-1 TLD-2 TLD-3
if (results.contains("TLFD")) {
int end = results.indexOf("TLFD");
end = end + 4;
int begin = end - 12;
result2 = results.substring(begin + 1, end);
} else if (results.contains("TL-")) {
int end = results.indexOf("TL-");
int count = 11;
boolean flag = true;
end = end + 3;
System.out.println(results.substring(end - 1, end));
for (int k = 1; k < 5; k++) {
end = end + 1;
System.out.println(results.substring(end - 1, end));
flag = isNumeric(results.substring(end - 1, end));
if (flag) {
count++;
} else {
break;
}
}
int begin = end - count;
result2 = results.substring(begin + 1, end);
} else if (results.contains("TLD-")) {
int end = results.indexOf("TLD-");
int count = 12;
boolean flag = true;
end = end + 4;
System.out.println(results.substring(end - 1, end));
for (int k = 1; k < 5; k++) {
end = end + 1;
System.out.println(results.substring(end - 1, end));
flag = isNumeric(results.substring(end - 1, end));
if (flag) {
count++;
} else {
break;
}
}
int begin = end - count;
result2 = results.substring(begin + 1, end);
} else if (results.contains("TLD")) {
int end = results.indexOf("TLD");
end = end + 3;
int begin = end - 11;
result2 = results.substring(begin + 1, end);
} else if (results.contains("TL")) {
// System.out.println(results+"!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!");
int end = results.indexOf("TL");
// System.out.println(end);
end = end + 2;
// System.out.println(results.substring(end-1, end));
int begin = end - 10;
result2 = results.substring(begin + 1, end);
}
wb2.close();
wb.close();
fise.close();
return result2;
}
@Override
public List<Artifact> getArtifactList(Project project, File spreadsheet) throws IOException {
xlsFile = spreadsheet;
BufferedInputStream bis = null;
HSSFWorkbook workBook = null;
List<Artifact> artifacts = new ArrayList<Artifact>();
try {
bis = new BufferedInputStream(new FileInputStream(xlsFile));
workBook = new HSSFWorkbook(bis);
HSSFSheet sheet = workBook.getSheetAt(0);
Map<ColumnType, Integer> columnHeaders = getColumnInfo(sheet);
Integer typeColumn = columnHeaders.get(ColumnType.TYPE_COLUMN);
Integer classifierColumn = columnHeaders.get(ColumnType.CLASSIFIER_COLUMN);
Integer digestColumn = columnHeaders.get(ColumnType.DIGEST_COLUMN);
String groupId = "";
String artifactId = "";
String type = JAR;
String version = "";
String classifier = "";
String digest = "";
for (int i = sheet.getFirstRowNum() + 1; i <= sheet.getLastRowNum(); ++i) {
HSSFRow row = sheet.getRow(i);
if (row != null) {
HSSFCell cell = row.getCell(columnHeaders.get(ColumnType.GROUP_COLUMN).intValue());
if (cell != null) {
String gId = cell.getStringCellValue().trim();
if (!gId.isEmpty()) {
groupId = gId;
}
}
cell = row.getCell(columnHeaders.get(ColumnType.ARTIFACT_COLUMN).intValue());
if (cell != null) {
String aId = cell.getStringCellValue().trim();
if (!aId.isEmpty()) {
artifactId = aId;
}
}
cell = row.getCell(columnHeaders.get(ColumnType.VERSION_COLUMN).intValue());
if (cell != null) {
String v;
if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
v = String.valueOf(cell.getNumericCellValue());
} else {
v = cell.getStringCellValue().trim();
}
if (!v.isEmpty()) {
version = v;
}
}
cell = (typeColumn != null) ? row.getCell(typeColumn.intValue()) : null;
if (cell != null) {
type = cell.getStringCellValue().trim();
}
cell = (classifierColumn != null) ? row.getCell(classifierColumn.intValue()) : null;
if (cell != null) {
classifier = cell.getStringCellValue().trim();
}
cell = (digestColumn != null) ? row.getCell(digestColumn.intValue()) : null;
if (cell != null) {
digest = cell.getStringCellValue().trim();
}
if (groupId.isEmpty() || artifactId.isEmpty() || version.isEmpty()) {
if (groupId.isEmpty() || version.isEmpty()) {
project.log(
"Row "
+ row.getRowNum()
+ ": Invalid artifact specified: [groupId: "
+ groupId
+ ", artifactId: "
+ artifactId
+ ", classifier: "
+ classifier
+ ", version: "
+ version
+ ", digest: "
+ digest
+ "]");
}
} else {
artifacts.add(new Artifact(groupId, artifactId, type, classifier, version, digest));
}
}
artifactId = "";
classifier = "";
digest = "";
type = JAR;
}
project.log(sheet.getLastRowNum() + " rows read from " + xlsFile, Project.MSG_VERBOSE);
} finally {
if (workBook != null) {
workBook.close();
}
Closer.close(bis);
}
return artifacts;
}
public static void writeExcelFile(String fileLocation) {
try {
FileOutputStream fileOut = new FileOutputStream(fileLocation);
HSSFWorkbook workbook = new HSSFWorkbook();
Font bold = workbook.createFont(); // Create font
bold.setBoldweight(Font.BOLDWEIGHT_BOLD); // Make font bold
CellStyle correctCell = workbook.createCellStyle();
correctCell.setFillForegroundColor(HSSFColor.GREEN.index);
correctCell.setFillBackgroundColor(HSSFColor.GREEN.index);
correctCell.setFillPattern(CellStyle.SOLID_FOREGROUND);
CellStyle incorrectCell = workbook.createCellStyle();
incorrectCell.setFillForegroundColor(HSSFColor.RED.index);
incorrectCell.setFillBackgroundColor(HSSFColor.RED.index);
incorrectCell.setFillPattern(CellStyle.SOLID_FOREGROUND);
CellStyle classificationCells = workbook.createCellStyle();
classificationCells.setFillForegroundColor(HSSFColor.YELLOW.index);
classificationCells.setFillBackgroundColor(HSSFColor.YELLOW.index);
classificationCells.setFillPattern(CellStyle.SOLID_FOREGROUND);
CellStyle attributeNameCells = workbook.createCellStyle();
attributeNameCells.setFont(bold);
CellStyle classificationAttributeCell = workbook.createCellStyle();
classificationAttributeCell.setFillForegroundColor(HSSFColor.YELLOW.index);
classificationAttributeCell.setFillBackgroundColor(HSSFColor.YELLOW.index);
classificationAttributeCell.setFillPattern(CellStyle.SOLID_FOREGROUND);
classificationAttributeCell.setFont(bold);
Sheet worksheet = workbook.createSheet("Results");
Row currRow = worksheet.createRow(0);
for (int attribute = 0; attribute < metadataLL.size() + 1; attribute++) {
Cell currCell = currRow.createCell(attribute);
if (attribute < metadataLL.size()) {
currCell.setCellValue(metadataLL.get(attribute)[0]);
if (metadataLL.get(attribute)[2].compareTo("classifier") == 0) {
currCell.setCellStyle(classificationAttributeCell);
} else {
currCell.setCellStyle(attributeNameCells);
}
} else {
currCell.setCellValue("Guessed Classification");
currCell.setCellStyle(attributeNameCells);
}
}
int correct = 0;
int incorrect = 0;
for (int node = 0; node < testDataLL.size(); node++) {
currRow = worksheet.createRow(node + 1); // Offset by one since first row is header data
int classifierCompleted = 0; // Necessary for if data does not end in classifier
for (int attribute = 0;
attribute < metadataLL.size() + 2;
attribute++) // +1 for the row for guessed data +1 for the row that contains
{
Cell currCell = currRow.createCell(attribute);
if (attribute < metadataLL.size()) // Print testingData
{
if (metadataLL.get(attribute)[2].compareTo("classifier") == 0) {
currCell.setCellValue(actualClassifications.get(node));
currCell.setCellStyle(classificationCells);
classifierCompleted++;
} else {
currCell.setCellValue(testDataLL.get(node)[attribute - classifierCompleted]);
}
} else if (attribute == metadataLL.size()) // Print guessed classification
{
currCell.setCellValue(guessedClassifications.get(node));
if (guessedClassifications.get(node).compareTo(actualClassifications.get(node)) == 0) {
currCell.setCellStyle(correctCell);
correct++;
} else {
currCell.setCellStyle(incorrectCell);
incorrect++;
}
if (node == testDataLL.size() - 1) // If this is the last loop
{
double precentRight = (double) correct / (correct + incorrect);
currRow = worksheet.createRow(node + 2);
currCell = currRow.createCell(attribute);
currCell.setCellValue(precentRight);
if (precentRight > .90) {
correctCell.setDataFormat(workbook.createDataFormat().getFormat("0.000%"));
currCell.setCellStyle(correctCell);
} else {
incorrectCell.setDataFormat(workbook.createDataFormat().getFormat("0.000%"));
currCell.setCellStyle(incorrectCell);
}
}
} else if (attribute
== metadataLL.size() + 1) // Print potential bad training data if the flag is true
{
if (unseenDataFlag.get(node)) {
currCell.setCellValue(
"This node an attribute value not in the training set, classifier selected is based on most frequent classifier. If laplacian smoothing is 1 or more this likely wont happen"); // TODO make this a bit shorter
}
}
}
}
worksheet = workbook.createSheet("Training Data");
currRow = worksheet.createRow(0);
for (int attribute = 0; attribute < metadataLL.size(); attribute++) {
Cell currCell = currRow.createCell(attribute);
currCell.setCellValue(metadataLL.get(attribute)[0]);
currCell.setCellStyle(attributeNameCells);
}
for (int node = 0; node < trainingDataLL.size(); node++) {
currRow = worksheet.createRow(node + 1); // Offset by one since first row is header data
int classifierCompleted = 0; // Necessary for if data does not end in classifier
for (int attribute = 0; attribute < metadataLL.size(); attribute++) {
Cell currCell = currRow.createCell(attribute);
if (metadataLL.get(attribute)[2].compareTo("classifier") == 0) {
currCell.setCellValue(knownClassifications.get(node));
classifierCompleted++;
} else {
currCell.setCellValue(trainingDataLL.get(node)[attribute - classifierCompleted]);
}
}
}
worksheet = workbook.createSheet("Likelihood");
currRow = worksheet.createRow(0);
int largestAttributeSize = 0;
for (int attribute = 0; attribute < classifier.size(); attribute++) {
if (classifier.get(attribute).size() > largestAttributeSize) {
largestAttributeSize = classifier.get(attribute).size();
}
}
// Label attributes along the top
for (int i = 0; i < metadataLL.size(); i++) {
if (i == 0) {
Cell currCell = currRow.createCell(i);
// currCell.setCellValue("Attributes");
currCell.setCellStyle(attributeNameCells);
} else {
Cell currCell = currRow.createCell(i);
currCell.setCellValue(
metadataLL
.get(i - 1)[0]); // -1 since the first cell does not contain a attribute name
currCell.setCellStyle(attributeNameCells);
}
}
// List possible classifications on the side and classification likelihoods at the end
for (int i = 0;
i
< (largestAttributeSize * (classificationTypes.size() + 1)
+ classificationTypes.size()
+ 1);
i++) // +1 since the first row of each stride lists each attributes string of what
// occurrence the likelihoods are displaying
{ // +classificationTypes.size() so we can list the classification types likelihood at the end
currRow = worksheet.createRow(i + 1); // +1 since first row is attribute names
Cell currCell = currRow.createCell(0);
int currentClassificationType =
i
% (classificationTypes.size()
+ 1); // +1 since the first row of each stride lists each attributes string of
// what occurrence the likelihoods are displaying
// List the classification type of each row along the side
if (i
< largestAttributeSize
* (classificationTypes.size()
+ 1)) // +1 since the first row of each stride lists each attributes string of
// what occurrence the likelihoods are displaying
{
for (int j = 0;
j < classificationTypes.size() + 1;
j++) // +1 since the first row of each stride lists each attributes string of what
// occurrence the likelihoods are displaying
{
if (currentClassificationType == 0) {
// Do nothing for now may have it say something later
} else if (currentClassificationType == j) {
currCell.setCellValue(
classificationTypes.get(
j - 1)); // -1 since the first cell does not contain a classification type
}
}
} else // List the likelihood of each classification at the end
{
for (int j = 0;
j < classificationTypes.size() + 1;
j++) // +1 since the first row of each stride lists each attributes string of what
// occurrence the likelihoods are displaying
{
if (currentClassificationType == 0) {
// Do nothing for now may have it say value later
} else if (currentClassificationType == j) {
currCell.setCellValue(
"Likelihood of: "
+ classificationTypes.get(j - 1)
+ " is "
+ classificationLikelihood.get(
j - 1)); // -1 since the first cell does not contain a classification type
}
}
}
currCell.setCellStyle(attributeNameCells);
}
// List the data
for (int attribute = 0; attribute < classifier.size(); attribute++) {
for (int occurrences = 0; occurrences < classifier.get(attribute).size(); occurrences++) {
for (int classification = 0;
classification < classifier.get(attribute).get(occurrences).length;
classification++) {
currRow =
worksheet.getRow(
(occurrences * classifier.get(attribute).get(occurrences).length
+ classification)
+ 1); // +1 since first row is attribute names
Cell currCell =
currRow.createCell(
(attribute) + 1); // TODO figure out why this errors out at i:0 j:4 k:0
// largestAttributeSize:105 on kidney dataset
currCell.setCellValue(classifier.get(attribute).get(occurrences)[classification]);
}
}
}
workbook.write(fileOut);
workbook.close();
workbook.close();
} catch (FileNotFoundException e) {
System.out.println("Error file not found");
e.printStackTrace();
System.exit(0);
} catch (IOException e) {
System.out.println("Unable to output file, is the output destination writelocked?");
e.printStackTrace();
System.exit(0);
}
}
public void testCorrectOrderInOptRecord() throws IOException {
HSSFWorkbook wb = new HSSFWorkbook();
try {
HSSFSheet sheet = wb.createSheet();
HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
HSSFTextbox textbox = patriarch.createTextbox(new HSSFClientAnchor());
EscherOptRecord opt = HSSFTestHelper.getOptRecord(textbox);
String opt1Str = opt.toXml();
textbox.setFillColor(textbox.getFillColor());
EscherContainerRecord container = HSSFTestHelper.getEscherContainer(textbox);
EscherOptRecord optRecord = container.getChildById(EscherOptRecord.RECORD_ID);
assertEquals(opt1Str, optRecord.toXml());
textbox.setLineStyle(textbox.getLineStyle());
assertEquals(opt1Str, optRecord.toXml());
textbox.setLineWidth(textbox.getLineWidth());
assertEquals(opt1Str, optRecord.toXml());
textbox.setLineStyleColor(textbox.getLineStyleColor());
assertEquals(opt1Str, optRecord.toXml());
} finally {
wb.close();
}
}
