@Test
public void testJExcel() throws Exception {
AbstractJExcelView excelView =
new UnixSafeAbstractJExcelView() {
@Override
protected void buildExcelDocument(
Map<String, Object> model,
WritableWorkbook wb,
HttpServletRequest request,
HttpServletResponse response)
throws Exception {
WritableSheet sheet = wb.createSheet("Test Sheet", 0);
// test all possible permutation of row or column not existing
sheet.addCell(new Label(2, 4, "Test Value"));
sheet.addCell(new Label(2, 3, "Test Value"));
sheet.addCell(new Label(3, 4, "Test Value"));
sheet.addCell(new Label(2, 4, "Test Value"));
}
};
excelView.render(new HashMap<String, Object>(), request, response);
Workbook wb = Workbook.getWorkbook(new ByteArrayInputStream(response.getContentAsByteArray()));
assertEquals("Test Sheet", wb.getSheet(0).getName());
Sheet sheet = wb.getSheet("Test Sheet");
Cell cell = sheet.getCell(2, 4);
assertEquals("Test Value", cell.getContents());
}
public static void addRowTop(WritableSheet ws) throws Exception {
WritableFont Bwf =
new WritableFont(
WritableFont.ARIAL, 16, WritableFont.BOLD, false); // 创建大字体：Arial,大小为18号,粗体，非斜体
Bwf.setColour(jxl.format.Colour.BLACK); // //字体颜色为红色
// 创建单元格格式：
jxl.write.WritableCellFormat CwcfF = new jxl.write.WritableCellFormat(Bwf);
CwcfF.setAlignment(jxl.write.Alignment.CENTRE); // 设置水平对齐为居中对齐
CwcfF.setVerticalAlignment(VerticalAlignment.CENTRE); // 设置垂直对齐为居中对齐
CwcfF.setBorder(jxl.format.Border.TOP, BorderLineStyle.MEDIUM); // 设置顶部边框线为实线(默认是黑色－－也可以设置其他颜色)
CwcfF.setBorder(jxl.format.Border.RIGHT, BorderLineStyle.MEDIUM); // 设置右边框线为实线
CwcfF.setBorder(jxl.format.Border.BOTTOM, BorderLineStyle.MEDIUM); // 设置顶部框线为实线
List<Label> labelList = new ArrayList<Label>();
labelList.add(new Label(0, 0, "商品名称", CwcfF)); //
labelList.add(new Label(1, 0, "单位", CwcfF)); //
labelList.add(new Label(2, 0, "销售数量", CwcfF)); //
labelList.add(new Label(3, 0, "销售成本", CwcfF));
labelList.add(new Label(4, 0, "销售金额", CwcfF));
labelList.add(new Label(5, 0, "利润", CwcfF));
labelList.add(new Label(6, 0, "利润率", CwcfF));
for (int j = 0; j < labelList.size(); j++) {
ws.addCell(labelList.get(j));
}
for (int i = 0; i < ws.getColumns(); i++) {
Cell cell = ws.getCell(i, 0);
ws.setColumnView(i, cell.getContents().length() * 4);
}
// ws.setRowView(0, 80*4);
}
public static List<String[]> readExcel(Context ctx, String fileName, Object targetSheet) {
List<String[]> rowList = new ArrayList<String[]>();
try {
InputStream mInputStream = ctx.getResources().getAssets().open(fileName);
Workbook wb = Workbook.getWorkbook(mInputStream);
Sheet mSheet = null;
if (targetSheet instanceof Integer) {
mSheet = wb.getSheet((Integer) targetSheet);
} else {
mSheet = wb.getSheet((String) targetSheet);
}
int row = getRowCount(mSheet);
int columns = getColCount(mSheet);
Log.d("W", "Total Row: " + row + ", Total Columns: " + columns);
String[] colArray = new String[columns];
for (int i = 1; i < row; i++) {
for (int j = 0; j < columns; j++) {
Cell temp = mSheet.getCell(j, i);
String content = temp.getContents();
Log.d("W", j + " ," + i + " ," + content);
colArray[j] = content;
}
rowList.add(colArray);
}
wb.close();
mInputStream.close();
} catch (BiffException e) {
e.printStackTrace();
} catch (IndexOutOfBoundsException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
return rowList;
}
@Test
public void testJExcelWithTemplateAndLanguage() throws Exception {
request.setAttribute(
DispatcherServlet.LOCALE_RESOLVER_ATTRIBUTE, newDummyLocaleResolver("de", ""));
AbstractJExcelView excelView =
new UnixSafeAbstractJExcelView() {
@Override
protected void buildExcelDocument(
Map<String, Object> model,
WritableWorkbook wb,
HttpServletRequest request,
HttpServletResponse response)
throws Exception {
WritableSheet sheet = wb.getSheet("Sheet1");
// test all possible permutation of row or column not existing
sheet.addCell(new Label(2, 4, "Test Value"));
sheet.addCell(new Label(2, 3, "Test Value"));
sheet.addCell(new Label(3, 4, "Test Value"));
sheet.addCell(new Label(2, 4, "Test Value"));
}
};
excelView.setApplicationContext(webAppCtx);
excelView.setUrl("template");
excelView.render(new HashMap<String, Object>(), request, response);
Workbook wb = Workbook.getWorkbook(new ByteArrayInputStream(response.getContentAsByteArray()));
Sheet sheet = wb.getSheet("Sheet1");
Cell cell = sheet.getCell(0, 0);
assertEquals("Test Template auf Deutsch", cell.getContents());
}
public ArrayList<String> getCitiesFromExcel() {
ArrayList<String> cities = new ArrayList<String>();
try {
Workbook workbook =
Workbook.getWorkbook(this.getClass().getResourceAsStream("/resource/cities.xls"));
int totalsheet = workbook.getNumberOfSheets();
for (int i = 0; i < totalsheet; i++) {
Sheet sheet = workbook.getSheet(i);
int rows = sheet.getRows();
for (int j = 1; j < rows; j++) {
Cell cell1 = sheet.getCell(0, j);
cities.add(cell1.getContents());
}
}
return cities;
} catch (IOException e) {
e.printStackTrace();
return null;
} catch (BiffException e) {
e.printStackTrace();
return null;
}
}
/**
* Get the cell value. Compensates for empty cells and cells that contain errors.
*
* @param C Cell
* @return
*/
private static String getCellValue(Cell C) {
CellType type = C.getType();
if (type == CellType.EMPTY || type == CellType.ERROR || type == CellType.FORMULA_ERROR) {
return "";
} else {
return C.getContents();
}
}
/**
* @param sheet
* @param numrow
* @return
*/
public static ArrayList readrow(WritableSheet sheet, int numrow) {
ArrayList contentlist = new ArrayList();
for (int numcol = 0; numcol < sheet.getColumns(); numcol++) {
Cell cell = sheet.getCell(numcol, numrow);
String content = cell.getContents();
contentlist.add(content);
}
return contentlist;
}
public int getCcIndexForCell(Cell cell, ArrayList<ConnexComposant> ccs) {
for (int i = 0; i < ccs.size(); i++) {
if (cell.getRow() >= ccs.get(i).getStartY()
&& cell.getRow() <= ccs.get(i).getEndY()
&& cell.getColumn() >= ccs.get(i).getStartX()
&& cell.getColumn() <= ccs.get(i).getEndX()) {
return i;
}
}
return -1;
}
/**
* Determine if a cell row is blank.
*
* @param Cells Row of cells
* @return
*/
private static boolean isBlankRow(Cell[] Cells) {
if (Cells == null || Cells.length == 0) {
return true;
}
// if first cell is blank, row is considered blank
Cell c = Cells[0];
CellType type = c.getType();
if (type == CellType.EMPTY) {
return true;
}
return false;
}
public static int nextFreeColNumber(WritableSheet sheet) {
if (sheet == null) return 0;
int colCount = sheet.getColumns();
Cell[] cells;
int i = 0;
for (; i < colCount; i++) {
cells = sheet.getColumn(i);
for (Cell cell : cells) {
if (cell.getContents() == null || cell.getContents().isEmpty()) return i;
}
}
return i;
}
/**
* 取得公式内容
*
* @param colNum
* @param rowNum
* @return
* @throws FormulaException
*/
public String getFormula(int colNum, int rowNum) throws FormulaException {
Cell cell = getCell(colNum, rowNum);
FormulaCell nfc = (FormulaCell) cell;
// 如果为公式类型
if (cell.getType() == CellType.NUMBER_FORMULA
|| cell.getType() == CellType.STRING_FORMULA
|| cell.getType() == CellType.BOOLEAN_FORMULA
|| cell.getType() == CellType.DATE_FORMULA
|| cell.getType() == CellType.FORMULA_ERROR) {
return nfc.getFormula();
} else {
logger.error("cell is not Formula");
throw new BaseException("cell is not Formula");
}
}
public static List<Map<String, String>> getXlsUserInfoData(String path) {
try {
File xlsFile = new File(path);
Workbook book = Workbook.getWorkbook(xlsFile);
Sheet[] sheet = book.getSheets();
if (sheet.length > 0) {
Sheet page = sheet[0];
int columns = page.getColumns();
System.out.println(columns);
int rows = page.getRows();
System.out.println(page.getName());
if (rows < 2) return null;
int columnsCount = 0;
List<String> titleList = new ArrayList<String>();
for (int i = 0; i < columns; i++) {
// 列 行
Cell cell = page.getCell(i, 0);
String key = cell.getContents();
if (key == null || key.equals("")) {
continue;
}
columnsCount++;
logger.debug(key);
titleList.add(key);
}
columns = columnsCount;
List<Map<String, String>> dataList = new ArrayList<Map<String, String>>();
for (int i = 1; i < rows; i++) {
logger.info("ii:" + i);
Map<String, String> dataMap = new HashMap<String, String>();
for (int j = 0; j < columns; j++) {
String key = titleList.get(j);
String value = page.getCell(j, i).getContents();
dataMap.put(key, value);
logger.debug(key + " " + value);
}
dataList.add(dataMap);
}
logger.debug("xls length:" + dataList.size());
return dataList;
}
} catch (Exception ex) {
ex.printStackTrace();
}
return null;
}
public void loadExcelData2DB(String excelPath, String sheetName) throws Exception {
Workbook book = Workbook.getWorkbook(new File(excelPath));
Sheet st = book.getSheet(sheetName);
int totalColumns = 220;
int totalRows = st.getRows();
// 1. get columns name
String[] columnNames = new String[totalColumns];
for (int i = 0; i < totalColumns; i++) {
columnNames[i] = st.getCell(i, 2).getContents();
}
String value = null;
for (int row = 3; row < totalRows; row++) {
JewelryEntity entity = new JewelryEntity();
int col;
for (col = 0; col < totalColumns; col++) {
Cell cell = st.getCell(col, row);
if (CellType.EMPTY == cell.getType()) {
if (0 == col) {
break;
} else {
value = null;
}
} else {
value = cell.getContents();
if (0 == col && value != null && value.trim().length() < 1) {
break;
}
}
this.setValueByJaveReflect(entity, columnNames[col], value);
}
if (0 != col) {
System.out.println(entity);
this.amazonJewelryDao.saveOrUpdate(entity);
}
}
book.close();
}
/** NOTE: Copied from InvestigationExcelReader */
private void writeSheetToFile(Sheet sheet, File file) throws FileNotFoundException {
List<String> headers = new ArrayList<String>();
Cell[] headerCells = sheet.getRow(0); // assume headers are on first
// line
ArrayList<Integer> namelessHeaderLocations = new ArrayList<Integer>(); // allow
// for
// empty
// columns,
// also
// column
// order
// does
// not
// matter
for (int i = 0; i < headerCells.length; i++) {
if (!headerCells[i].getContents().equals("")) {
headers.add(headerCells[i].getContents());
} else {
headers.add("nameless" + i);
namelessHeaderLocations.add(i);
}
}
PrintWriter pw = new PrintWriter(file);
CsvWriter cw = new CsvWriter(pw, headers);
cw.setMissingValue("");
cw.writeHeader();
for (int rowIndex = 1; rowIndex < sheet.getRows(); rowIndex++) {
Tuple t = new SimpleTuple();
int colIndex = 0;
for (Cell c : sheet.getRow(rowIndex)) {
if (!namelessHeaderLocations.contains(colIndex)) {
t.set(headers.get(colIndex), c.getContents());
}
colIndex++;
}
cw.writeRow(t);
}
cw.close();
}
/**
* Récupère la liste des étudiants dans un xls
*
* @param uri : l'uri du fichier xls
* @return la liste remplie
* @author Foulon Alain
*/
public static GestionEtudiants getValsFromXls(String uri) throws BiffException, IOException {
GestionEtudiants liste = new GestionEtudiants();
try {
// Récupère le fichier
File fich = new File(uri);
WorkbookSettings workSet = new WorkbookSettings(); // config de fichier .xls
workSet.setLocale(new Locale("fr", "FR")); // vars locales
Workbook workbook =
Workbook.getWorkbook(fich, workSet); // ouvre un fichier existant en fct des settings
Sheet feuille = workbook.getSheet(0); // ouvre la première feuille du fichier
Cell cellNom = feuille.findCell("NOM"); // trouve la cellule "NOM" dans la feuille
// soit : REGEX sur "nom, Nom, noms, NOM, NOMS..."
// soit : fct qui teste les possibilités; (note: la CELL est null si non trouvée)
int colNom = cellNom.getColumn(); // colonne noms
int colPrenom = colNom + 1; // colonne prénoms
int firstRow = cellNom.getRow() + 1; // premiere ligne
int lastRow = feuille.getRows(); // derniere ligne
// boucle de remplissage
for (int i = firstRow, j = 0; i < lastRow; i++) {
// je récupère les infors
String nom = feuille.getCell(colNom, i).getContents();
String prenom = feuille.getCell(colPrenom, i).getContents();
// j'ajoute l'étudiant dans la liste
j++;
liste.add(new Etudiant(j, nom, prenom));
}
} catch (IOException ioe) {
System.out.println("Erreur : " + ioe); // Erreur lors de l'ouverture du fichier Excel
}
// je retourne la liste finie
return liste;
}
private boolean writeSheetToFile(Sheet sheet, File file) throws FileNotFoundException {
List<String> headers = new ArrayList<String>();
Cell[] headerCells = sheet.getRow(0); // assume headers are on first line
if (headerCells.length == 0) {
return false;
}
ArrayList<Integer> namelessHeaderLocations =
new ArrayList<Integer>(); // allow for empty columns, also column order does not matter
for (int i = 0; i < headerCells.length; i++) {
if (!headerCells[i].getContents().equals("")) {
headers.add(headerCells[i].getContents());
} else {
headers.add("nameless" + i);
namelessHeaderLocations.add(i);
}
}
PrintWriter pw = new PrintWriter(file);
CsvWriter cw = new CsvWriter(pw, headers);
cw.setMissingValue("");
cw.writeHeader();
for (int rowIndex = 1; rowIndex < sheet.getRows(); rowIndex++) {
Tuple t = new SimpleTuple();
int colIndex = 0;
for (Cell c : sheet.getRow(rowIndex)) {
if (!namelessHeaderLocations.contains(colIndex)
&& colIndex < headers.size()
&& c.getContents() != null) {
t.set(headers.get(colIndex), c.getContents());
}
colIndex++;
}
cw.writeRow(t);
}
cw.close();
return true;
}
public String[][] getTableArray() throws Exception {
String[][] tabArray = null;
Workbook workbook = Workbook.getWorkbook(new File(inputFile));
Sheet sheet = workbook.getSheet(sheetName);
int startRow, startCol, endRow, endCol, ci, cj;
Cell tableStart = sheet.findCell(tableName);
startRow = tableStart.getRow();
startCol = tableStart.getColumn();
Cell tableEnd = sheet.findCell(tableName, startCol + 1, startRow + 1, 400, 64000, false);
// Cell tableEnd= sheet.findCell(tableName, startCol+1,startRow+1, 100, 64000, false);
endRow = tableEnd.getRow();
endCol = tableEnd.getColumn();
System.out.println(
"startRow="
+ startRow
+ ", endRow="
+ endRow
+ ", "
+ "startCol="
+ startCol
+ ", endCol="
+ endCol);
tabArray = new String[endRow - startRow - 1][endCol - startCol - 1];
ci = 0;
for (int i = startRow + 1; i < endRow; i++, ci++) {
cj = 0;
for (int j = startCol + 1; j < endCol; j++, cj++) {
tabArray[ci][cj] = sheet.getCell(j, i).getContents();
}
}
return (tabArray);
}
@Test
public void setUp() throws Exception {
try {
// ---------------------Maximize Window----------------
driver.manage().window().maximize();
// ---------------------Open URL----------------
System.setProperty(
"webdriver.ie.driver", "C:\\Users\\nitish_ravindran\\Desktop\\IEDriverServer.exe");
/*WebDriver driver;
driver=new InternetExplorerDriver();*/
driver.get("http://*****:*****@href='/SBCS-WEB/AdvancedSearch.do?moduleSelected=Scenarios']")).getAttribute("title").contains("Advanced Search ");
}
});
//-------------------------Logout-----------------------------
WebElement logoutLinkImg=driver.findElement(By.cssSelector("a[href='/SBCS-WEB/logout.do']"));
logoutLinkImg.click();*/
} // -------------------------End of Try block-----------------------------
// -------------------------Catch Blocks-----------------------------
catch (NoSuchElementException e) {
System.out.println("-----------Element not found " + e);
}
/*catch(AssertionFailedError e)
{
System.out.println("------------Assertion failed " + e);
}*/
/*catch(StaleElementReferenceException e)
{
System.out.println("-----------Stale Element" + e);
}*/
catch (Exception e) {
e.printStackTrace();
} finally {
// driver.quit();
}
}
/**
* Check all the merged cells for borders. If the merge record has borders, then we need to rejig
* the cell formats to take account of this. This is called by the write method of the
* WritableWorkbookImpl, so that any new XFRecords that are created may be written out with the
* others
*/
void checkMergedBorders() {
Range[] mcells = mergedCells.getMergedCells();
ArrayList borderFormats = new ArrayList();
for (int mci = 0; mci < mcells.length; mci++) {
Range range = mcells[mci];
Cell topLeft = range.getTopLeft();
XFRecord tlformat = (XFRecord) topLeft.getCellFormat();
if (tlformat != null && tlformat.hasBorders() == true && !tlformat.isRead()) {
try {
CellXFRecord cf1 = new CellXFRecord(tlformat);
Cell bottomRight = range.getBottomRight();
cf1.setBorder(Border.ALL, BorderLineStyle.NONE, Colour.BLACK);
cf1.setBorder(
Border.LEFT,
tlformat.getBorderLine(Border.LEFT),
tlformat.getBorderColour(Border.LEFT));
cf1.setBorder(
Border.TOP, tlformat.getBorderLine(Border.TOP), tlformat.getBorderColour(Border.TOP));
if (topLeft.getRow() == bottomRight.getRow()) {
cf1.setBorder(
Border.BOTTOM,
tlformat.getBorderLine(Border.BOTTOM),
tlformat.getBorderColour(Border.BOTTOM));
}
if (topLeft.getColumn() == bottomRight.getColumn()) {
cf1.setBorder(
Border.RIGHT,
tlformat.getBorderLine(Border.RIGHT),
tlformat.getBorderColour(Border.RIGHT));
}
int index = borderFormats.indexOf(cf1);
if (index != -1) {
cf1 = (CellXFRecord) borderFormats.get(index);
} else {
borderFormats.add(cf1);
}
((WritableCell) topLeft).setCellFormat(cf1);
// Handle the bottom left corner
if (bottomRight.getRow() > topLeft.getRow()) {
// Handle the corner cell
if (bottomRight.getColumn() != topLeft.getColumn()) {
CellXFRecord cf2 = new CellXFRecord(tlformat);
cf2.setBorder(Border.ALL, BorderLineStyle.NONE, Colour.BLACK);
cf2.setBorder(
Border.LEFT,
tlformat.getBorderLine(Border.LEFT),
tlformat.getBorderColour(Border.LEFT));
cf2.setBorder(
Border.BOTTOM,
tlformat.getBorderLine(Border.BOTTOM),
tlformat.getBorderColour(Border.BOTTOM));
index = borderFormats.indexOf(cf2);
if (index != -1) {
cf2 = (CellXFRecord) borderFormats.get(index);
} else {
borderFormats.add(cf2);
}
sheet.addCell(new Blank(topLeft.getColumn(), bottomRight.getRow(), cf2));
}
// Handle the cells down the left hand side (and along the
// right too, if necessary)
for (int i = topLeft.getRow() + 1; i < bottomRight.getRow(); i++) {
CellXFRecord cf3 = new CellXFRecord(tlformat);
cf3.setBorder(Border.ALL, BorderLineStyle.NONE, Colour.BLACK);
cf3.setBorder(
Border.LEFT,
tlformat.getBorderLine(Border.LEFT),
tlformat.getBorderColour(Border.LEFT));
if (topLeft.getColumn() == bottomRight.getColumn()) {
cf3.setBorder(
Border.RIGHT,
tlformat.getBorderLine(Border.RIGHT),
tlformat.getBorderColour(Border.RIGHT));
}
index = borderFormats.indexOf(cf3);
if (index != -1) {
cf3 = (CellXFRecord) borderFormats.get(index);
} else {
borderFormats.add(cf3);
}
sheet.addCell(new Blank(topLeft.getColumn(), i, cf3));
}
}
// Handle the top right corner
if (bottomRight.getColumn() > topLeft.getColumn()) {
if (bottomRight.getRow() != topLeft.getRow()) {
// Handle the corner cell
CellXFRecord cf6 = new CellXFRecord(tlformat);
cf6.setBorder(Border.ALL, BorderLineStyle.NONE, Colour.BLACK);
cf6.setBorder(
Border.RIGHT,
tlformat.getBorderLine(Border.RIGHT),
tlformat.getBorderColour(Border.RIGHT));
cf6.setBorder(
Border.TOP,
tlformat.getBorderLine(Border.TOP),
tlformat.getBorderColour(Border.TOP));
index = borderFormats.indexOf(cf6);
if (index != -1) {
cf6 = (CellXFRecord) borderFormats.get(index);
} else {
borderFormats.add(cf6);
}
sheet.addCell(new Blank(bottomRight.getColumn(), topLeft.getRow(), cf6));
}
// Handle the cells along the right
for (int i = topLeft.getRow() + 1; i < bottomRight.getRow(); i++) {
CellXFRecord cf7 = new CellXFRecord(tlformat);
cf7.setBorder(Border.ALL, BorderLineStyle.NONE, Colour.BLACK);
cf7.setBorder(
Border.RIGHT,
tlformat.getBorderLine(Border.RIGHT),
tlformat.getBorderColour(Border.RIGHT));
index = borderFormats.indexOf(cf7);
if (index != -1) {
cf7 = (CellXFRecord) borderFormats.get(index);
} else {
borderFormats.add(cf7);
}
sheet.addCell(new Blank(bottomRight.getColumn(), i, cf7));
}
// Handle the cells along the top, and along the bottom too
for (int i = topLeft.getColumn() + 1; i < bottomRight.getColumn(); i++) {
CellXFRecord cf8 = new CellXFRecord(tlformat);
cf8.setBorder(Border.ALL, BorderLineStyle.NONE, Colour.BLACK);
cf8.setBorder(
Border.TOP,
tlformat.getBorderLine(Border.TOP),
tlformat.getBorderColour(Border.TOP));
if (topLeft.getRow() == bottomRight.getRow()) {
cf8.setBorder(
Border.BOTTOM,
tlformat.getBorderLine(Border.BOTTOM),
tlformat.getBorderColour(Border.BOTTOM));
}
index = borderFormats.indexOf(cf8);
if (index != -1) {
cf8 = (CellXFRecord) borderFormats.get(index);
} else {
borderFormats.add(cf8);
}
sheet.addCell(new Blank(i, topLeft.getRow(), cf8));
}
}
// Handle the bottom right corner
if (bottomRight.getColumn() > topLeft.getColumn()
|| bottomRight.getRow() > topLeft.getRow()) {
// Handle the corner cell
CellXFRecord cf4 = new CellXFRecord(tlformat);
cf4.setBorder(Border.ALL, BorderLineStyle.NONE, Colour.BLACK);
cf4.setBorder(
Border.RIGHT,
tlformat.getBorderLine(Border.RIGHT),
tlformat.getBorderColour(Border.RIGHT));
cf4.setBorder(
Border.BOTTOM,
tlformat.getBorderLine(Border.BOTTOM),
tlformat.getBorderColour(Border.BOTTOM));
if (bottomRight.getRow() == topLeft.getRow()) {
cf4.setBorder(
Border.TOP,
tlformat.getBorderLine(Border.TOP),
tlformat.getBorderColour(Border.TOP));
}
if (bottomRight.getColumn() == topLeft.getColumn()) {
cf4.setBorder(
Border.LEFT,
tlformat.getBorderLine(Border.LEFT),
tlformat.getBorderColour(Border.LEFT));
}
index = borderFormats.indexOf(cf4);
if (index != -1) {
cf4 = (CellXFRecord) borderFormats.get(index);
} else {
borderFormats.add(cf4);
}
sheet.addCell(new Blank(bottomRight.getColumn(), bottomRight.getRow(), cf4));
// Handle the cells along the bottom (and along the top
// as well, if appropriate)
for (int i = topLeft.getColumn() + 1; i < bottomRight.getColumn(); i++) {
CellXFRecord cf5 = new CellXFRecord(tlformat);
cf5.setBorder(Border.ALL, BorderLineStyle.NONE, Colour.BLACK);
cf5.setBorder(
Border.BOTTOM,
tlformat.getBorderLine(Border.BOTTOM),
tlformat.getBorderColour(Border.BOTTOM));
if (topLeft.getRow() == bottomRight.getRow()) {
cf5.setBorder(
Border.TOP,
tlformat.getBorderLine(Border.TOP),
tlformat.getBorderColour(Border.TOP));
}
index = borderFormats.indexOf(cf5);
if (index != -1) {
cf5 = (CellXFRecord) borderFormats.get(index);
} else {
borderFormats.add(cf5);
}
sheet.addCell(new Blank(i, bottomRight.getRow(), cf5));
}
}
} catch (WriteException e) {
// just log e.toString(), not the whole stack trace
logger.warn(e.toString());
}
}
}
}
/**
* DOC xqliu Comment method "importToStucture".
*
* @param importObject
* @param selectionFolder
* @param type
* @param skip
* @param rename
* @param importItemName
* @return
*/
public static List<ReturnCode> importToStucture(
ImportObject importObject,
IFolder selectionFolder,
ExpressionType type,
boolean skip,
boolean rename,
String importItemName) {
List<ReturnCode> importEvent = new ArrayList<ReturnCode>();
// MOD qiongli 2012-12-20 TDQ-5899(issue 2),should get all patterns from Pattern folder.
Set<String> names =
PatternUtilities.getNestFolderPatternNames(
new HashSet<String>(), ResourceManager.getPatternFolder());
File importFile = importObject.getObjFile();
String fileExtName = getFileExtName(importFile);
if ("csv".equalsIgnoreCase(fileExtName)) { // $NON-NLS-1$
try {
CsvReader reader = new CsvReader(new FileReader(importFile), CURRENT_SEPARATOR);
reader.setEscapeMode(CsvReader.ESCAPE_MODE_DOUBLED);
reader.setTextQualifier(TEXT_QUAL);
reader.setUseTextQualifier(USE_TEXT_QUAL);
reader.readHeaders();
while (reader.readRecord()) {
String name = reader.get(PatternToExcelEnum.Label.getLiteral());
if (names.contains(name)) {
if (skip) {
importEvent.add(
new ReturnCode(
DefaultMessagesImpl.getString("ImportFactory.patternInported", name),
false)); //$NON-NLS-1$
continue;
}
if (rename) {
name = name + "(" + new Date() + ")"; // $NON-NLS-1$ //$NON-NLS-2$
}
}
PatternParameters patternParameters = new ImportFactory().new PatternParameters();
patternParameters.name = name;
patternParameters.auther = reader.get(PatternToExcelEnum.Author.getLiteral());
patternParameters.description = reader.get(PatternToExcelEnum.Description.getLiteral());
patternParameters.purpose = reader.get(PatternToExcelEnum.Purpose.getLiteral());
patternParameters.relativePath = reader.get(PatternToExcelEnum.RelativePath.getLiteral());
for (PatternLanguageType languagetype : PatternLanguageType.values()) {
String cellStr = reader.get(languagetype.getExcelEnum().getLiteral());
if (cellStr != null && !cellStr.equals("")) { // $NON-NLS-1$
patternParameters.regex.put(languagetype.getLiteral(), cellStr);
}
}
try {
TypedReturnCode<Object> create =
createAndStorePattern(patternParameters, selectionFolder, type);
if (create.isOk()) {
names.add(name);
importEvent.add(
new ReturnCode(
DefaultMessagesImpl.getString(
"ImportFactory.importPattern",
name, //$NON-NLS-1$
selectionFolder.getProjectRelativePath().toString()),
true));
} else {
throw new TalendInternalPersistenceException(create.getMessage());
}
} catch (Exception e) {
importEvent.add(
new ReturnCode(
DefaultMessagesImpl.getString("ImportFactory.SaveFailed", name),
false)); //$NON-NLS-1$
}
}
reader.close();
} catch (Exception e) {
log.error(e, e);
importEvent.add(
new ReturnCode(
DefaultMessagesImpl.getString("ImportFactory.importFailed"), false)); // $NON-NLS-1$
}
}
if ("xls".equalsIgnoreCase(fileExtName)) { // $NON-NLS-1$
Map<Integer, PatternLanguageType> expressionMap = new HashMap<Integer, PatternLanguageType>();
try {
WorkbookSettings settings = new WorkbookSettings();
settings.setEncoding("UTF-8"); // $NON-NLS-1$
Workbook rwb = Workbook.getWorkbook(importFile, settings);
Sheet[] sheets = rwb.getSheets();
for (Sheet sheet : sheets) {
Cell[] headerRow = sheet.getRow(0);
for (Cell cell : headerRow) {
for (PatternLanguageType languageType : PatternLanguageType.values()) {
if (cell.getContents().equals(languageType.getExcelEnum().getLiteral())) {
expressionMap.put(cell.getColumn(), languageType);
}
}
}
for (int i = 1; i < sheet.getRows(); i++) {
Cell[] row = sheet.getRow(i);
Cell cell = row[0];
if (CellType.LABEL.equals(cell.getType())) {
String contents = cell.getContents();
if (names.contains(contents)) {
if (skip) {
importEvent.add(
new ReturnCode(
DefaultMessagesImpl.getString(
"ImportFactory.patternInported", //$NON-NLS-1$
contents),
false));
continue;
}
if (rename) {
contents = contents + "(" + new Date() + ")"; // $NON-NLS-1$ //$NON-NLS-2$
}
}
PatternParameters patternParameters = new ImportFactory().new PatternParameters();
patternParameters.name = contents;
patternParameters.auther = row[6].getContents();
patternParameters.description = row[2].getContents();
patternParameters.purpose = row[1].getContents();
patternParameters.status = DevelopmentStatus.DRAFT.getLiteral();
for (int columnIndex : expressionMap.keySet()) {
String rowContent = row[columnIndex].getContents();
if (!rowContent.equals("")) { // $NON-NLS-1$
patternParameters.regex.put(
expressionMap.get(columnIndex).getLiteral(), rowContent);
}
}
try {
TypedReturnCode<Object> create =
createAndStorePattern(patternParameters, selectionFolder, type);
if (create.isOk()) {
names.add(contents);
importEvent.add(
new ReturnCode(
DefaultMessagesImpl.getString(
"ImportFactory.importPattern",
((TDQItem) create.getObject())
.getProperty()
.getDisplayName(), //$NON-NLS-1$
selectionFolder.getProjectRelativePath().toString()),
true));
} else {
throw new TalendInternalPersistenceException(create.getMessage());
}
} catch (Exception e) {
importEvent.add(
new ReturnCode(
DefaultMessagesImpl.getString("ImportFactory.SaveFailed", contents),
false)); //$NON-NLS-1$
}
}
}
}
rwb.close();
} catch (BiffException e) {
log.error(e, e);
importEvent.add(
new ReturnCode(
DefaultMessagesImpl.getString("ImportFactory.importFailed"), false)); // $NON-NLS-1$
} catch (IOException e) {
log.error(e, e);
importEvent.add(
new ReturnCode(
DefaultMessagesImpl.getString("ImportFactory.importFailed"), false)); // $NON-NLS-1$
}
}
importObject.copyJarFiles();
// ADD xqliu 2012-04-27 TDQ-5149
checkImportEvent(importItemName, importEvent);
// ~ TDQ-5149
return importEvent;
}
/**
* DOC xqliu Comment method "importIndicatorToStucture".
*
* @param importObject
* @param selectionFolder
* @param skip
* @param rename
* @param importItemName
* @return
*/
public static List<ReturnCode> importIndicatorToStucture(
ImportObject importObject,
IFolder selectionFolder,
boolean skip,
boolean rename,
String importItemName) {
List<ReturnCode> information = new ArrayList<ReturnCode>();
Set<String> names = UDIHelper.getAllIndicatorNames(selectionFolder);
File importFile = importObject.getObjFile();
String fileExtName = getFileExtName(importFile);
if ("csv".equalsIgnoreCase(fileExtName)) { // $NON-NLS-1$
String name = PluginConstant.EMPTY_STRING;
try {
CsvReader reader = new CsvReader(new FileReader(importFile), CURRENT_SEPARATOR);
// MOD zshen EscapeMode default is CsvReader.ESCAPE_MODE_DOUBLED
reader.setTextQualifier(TEXT_QUAL);
reader.setUseTextQualifier(USE_TEXT_QUAL);
reader.readHeaders();
java.text.SimpleDateFormat simpleDateFormat =
new java.text.SimpleDateFormat("yyyyMMddHHmmssSSS"); // $NON-NLS-1$
while (reader.readRecord()) {
name = reader.get(PatternToExcelEnum.Label.getLiteral());
if (names.contains(name)) {
if (skip) {
information.add(
new ReturnCode(
DefaultMessagesImpl.getString("ImportFactory.Imported", name),
false)); //$NON-NLS-1$
continue;
}
if (rename) {
name =
name
+ "("
+ simpleDateFormat.format(new Date())
+ Math.random()
+ ")"; //$NON-NLS-1$ //$NON-NLS-2$
}
}
UDIParameters udiParameters = new ImportFactory().new UDIParameters();
udiParameters.name = name;
udiParameters.auther = reader.get(PatternToExcelEnum.Author.getLiteral());
udiParameters.description = reader.get(PatternToExcelEnum.Description.getLiteral());
udiParameters.purpose = reader.get(PatternToExcelEnum.Purpose.getLiteral());
udiParameters.relativePath = reader.get(PatternToExcelEnum.RelativePath.getLiteral());
udiParameters.category = reader.get(PatternToExcelEnum.Category.getLiteral());
udiParameters.javaClassName = reader.get(PatternToExcelEnum.JavaClassName.getLiteral());
udiParameters.javaJarPath = reader.get(PatternToExcelEnum.JavaJarPath.getLiteral());
String[] headers = reader.getHeaders();
String[] columnsValue = reader.getValues();
HashMap<String, String> record = new HashMap<String, String>();
for (int i = 0; i < headers.length; i++) {
if (columnsValue[i] != null && columnsValue[i].length() > 0) {
record.put(headers[i], columnsValue[i]);
}
}
for (PatternLanguageType languagetype : PatternLanguageType.values()) {
String cellStr = record.get(languagetype.getExcelEnum().getLiteral());
if (cellStr != null && !cellStr.equals("\"\"")) { // $NON-NLS-1$
udiParameters.regex.put(languagetype.getLiteral(), trimQuote(cellStr));
}
}
udiParameters.setParaMap(buildIndDefPara(record));
TypedReturnCode<Object> create = createAndStoreUDI(udiParameters, selectionFolder);
if (create.isOk()) {
names.add(name);
// add the suscess message to display.
information.add(
new ReturnCode(
DefaultMessagesImpl.getString(
"ImportFactory.importedSucess" //$NON-NLS-1$
,
((TDQItem) create.getObject()).getProperty().getDisplayName(),
selectionFolder.getProjectRelativePath().toString()),
true));
} else {
throw new TalendInternalPersistenceException(create.getMessage());
}
}
reader.close();
} catch (Exception e) {
log.error(e, e);
information.add(
new ReturnCode(
DefaultMessagesImpl.getString("ImportFactory.importedFailed", name),
false)); //$NON-NLS-1$
}
}
if ("xls".equalsIgnoreCase(fileExtName)) { // $NON-NLS-1$
Map<Integer, PatternLanguageType> expressionMap = new HashMap<Integer, PatternLanguageType>();
String contents = PluginConstant.EMPTY_STRING;
try {
WorkbookSettings settings = new WorkbookSettings();
settings.setEncoding("UTF-8"); // $NON-NLS-1$
Workbook rwb = Workbook.getWorkbook(importFile, settings);
Sheet[] sheets = rwb.getSheets();
for (Sheet sheet : sheets) {
Cell[] headerRow = sheet.getRow(0);
for (Cell cell : headerRow) {
for (PatternLanguageType languageType : PatternLanguageType.values()) {
if (cell.getContents().equals(languageType.getExcelEnum().getLiteral())) {
expressionMap.put(cell.getColumn(), languageType);
}
}
}
for (int i = 1; i < sheet.getRows(); i++) {
Cell[] row = sheet.getRow(i);
Cell cell = row[0];
if (CellType.LABEL.equals(cell.getType())) {
contents = cell.getContents();
if (names.contains(contents)) {
if (skip) {
continue;
}
if (rename) {
contents = contents + "(" + new Date() + ")"; // $NON-NLS-1$ //$NON-NLS-2$
}
}
UDIParameters udiParameters = new ImportFactory().new UDIParameters();
udiParameters.name = contents;
udiParameters.auther = row[6].getContents();
udiParameters.description = row[2].getContents();
udiParameters.purpose = row[1].getContents();
udiParameters.status = DevelopmentStatus.DRAFT.getLiteral();
udiParameters.category = row[16].getContents();
for (int columnIndex : expressionMap.keySet()) {
String rowContent = row[columnIndex].getContents();
if (!rowContent.equals("")) { // $NON-NLS-1$
udiParameters.regex.put(expressionMap.get(columnIndex).getLiteral(), rowContent);
}
}
createAndStoreUDI(udiParameters, selectionFolder);
names.add(contents);
information.add(
new ReturnCode(
DefaultMessagesImpl.getString(
"ImportFactory.importedSucess" //$NON-NLS-1$
,
contents),
true));
}
}
}
rwb.close();
} catch (Exception e) {
log.error(e, e);
information.add(
new ReturnCode(
DefaultMessagesImpl.getString("ImportFactory.importedFailed", contents),
false)); //$NON-NLS-1$
}
}
// MOD qiongli 2011-11-28 TDQ-4038.consider to import the definition file.
if (FactoriesUtil.DEFINITION.equalsIgnoreCase(fileExtName)) {
String propFilePath =
importFile
.getPath()
.replaceFirst(
PluginConstant.DOT_STRING + fileExtName,
PluginConstant.DOT_STRING + FactoriesUtil.PROPERTIES_EXTENSION);
File propFile = new File(propFilePath);
// just import the definition file which have the realted Property file.
if (!propFile.exists()) {
return information;
}
String name = importFile.getName();
try {
if (names.contains(name)) {
if (skip) {
information.add(
new ReturnCode(
DefaultMessagesImpl.getString("ImportFactory.Imported", name),
false)); //$NON-NLS-1$
return information;
}
if (rename) {
name = name + "(" + new Date() + Math.random() + ")"; // $NON-NLS-1$ //$NON-NLS-2$
}
}
IFile elementFile = selectionFolder.getFile(name);
if (!elementFile.exists()) {
elementFile.create(new FileInputStream(importFile), false, null);
ModelElement modelElement = ModelElementFileFactory.getModelElement(elementFile);
if (modelElement != null) {
ElementWriterFactory.getInstance()
.createIndicatorDefinitionWriter()
.create(modelElement, selectionFolder);
DefinitionHandler.getInstance().reloadIndicatorsDefinitions();
names.add(name);
information.add(
new ReturnCode(
DefaultMessagesImpl.getString(
"ImportFactory.importedSucess" //$NON-NLS-1$
,
name),
true));
}
}
} catch (Exception e) {
log.error(e);
information.add(
new ReturnCode(
DefaultMessagesImpl.getString("ImportFactory.importedFailed", name),
false)); //$NON-NLS-1$
}
}
importObject.copyJarFiles();
// ADD xqliu 2012-04-27 TDQ-5149
checkImportEvent(importItemName, information);
// ~ TDQ-5149
return information;
}
private boolean isCellVoid(Cell cell) {
if (cell == null || cell.equals("") || cell.getType().equals(CellType.EMPTY)) return true;
else return false;
}
public int ImportDefalutXls(Context mContext) {
try {
String xlsType = "";
InputStream is = null;
String downloadXlsPath = getUpdateXlsPath(mContext) + File.separator + "address.xls";
File downloadXlsFile = new File(downloadXlsPath);
if (downloadXlsFile.exists()) {
LogUtils.i(XlsUtils.class.getName(), "downloadXlsPath:" + downloadXlsPath);
is = new FileInputStream(downloadXlsFile);
xlsType = "xls";
} else {
AssetManager assets = mContext.getAssets();
String[] files = assets.list("");
for (String fileName : files) {
if (fileName.equals(ConfigUtils.DEFAULT_XLS_NAME)) {
xlsType = "xls";
is = assets.open(fileName);
LogUtils.i(XlsUtils.class.getName(), "assets file");
break;
} else if (fileName.equals(ConfigUtils.DEFAULT_XLSX_NAME)) {
xlsType = "xlsx";
is = assets.open(fileName);
break;
} else {
return -1;
}
}
}
if (xlsType.equals("xls")) {
Workbook wb = null;
wb = Workbook.getWorkbook(is);
Sheet sht =
wb.getSheet(XResources.getString(mContext, ConfigUtils.SHEET_NAME)); // name or number
int coloums = sht.getColumns();
int rows = sht.getRows();
Cell currentCell = null;
String addressname = XResources.getString(mContext, ConfigUtils.ADDRESS_USERNAME);
String jobname = XResources.getString(mContext, ConfigUtils.ADDRESS_JOB);
String mobilephonename = XResources.getString(mContext, ConfigUtils.ADDRESS_MOBILEPHONE);
String workphonename = XResources.getString(mContext, ConfigUtils.ADDRESS_WORKPHONE);
LogUtils.i(XlsUtils.class.getName(), "************************************");
LogUtils.i(XlsUtils.class.getName(), String.format("row[%d],columns[%d]", rows, coloums));
// delete address db
DBUtils.getInstances().deleteAll(mContext, AddressModel.TableName);
for (int i = 1; i < rows; i++) {
LogUtils.i(XlsUtils.class.getName(), "####################");
ContactsBean tmp = new ContactsBean();
for (int j = 0; j < coloums; j++) {
Cell topCell = sht.getCell(j, 0);
String topName = topCell.getContents();
currentCell = sht.getCell(j, i);
// LogUtils.i(XlsUtils.class.getName(),topName);
if (topName.equals(addressname)) {
tmp.setName(currentCell.getContents());
LogUtils.i(
XlsUtils.class.getName(), String.format("%s:%s", addressname, tmp.getName()));
} else if (topName.equals(jobname)) {
tmp.setJob(currentCell.getContents());
LogUtils.i(XlsUtils.class.getName(), String.format("%s:%s", jobname, tmp.getJob()));
} else if (topName.equals(mobilephonename)) {
tmp.setMobilephone(currentCell.getContents());
LogUtils.i(
XlsUtils.class.getName(),
String.format("%s:%s", mobilephonename, tmp.getMobilephone()));
} else if (topName.equals(workphonename)) {
tmp.setWorkphone(currentCell.getContents());
LogUtils.i(
XlsUtils.class.getName(),
String.format("%s:%s", workphonename, tmp.getWorkphone()));
} else {
}
}
// insert to db
DBUtils.getInstances().InsertAddress(mContext, tmp);
LogUtils.i(XlsUtils.class.getName(), "####################");
}
LogUtils.i(XlsUtils.class.getName(), "************************************");
} else {
return -2;
}
return 1;
} catch (Exception e) {
LogUtils.error(e);
}
return 0;
}
public static void main(String[] args) throws Exception {
System.out.println(
"====================================学术论文Excel整理begin================================================");
System.out.println("========================获取文件夹中的文件begin============");
String dirPath = "E:/wuyu/学术论文";
ReadDirFiles readDirFiles = new ReadDirFiles();
ArrayList filePathList = readDirFiles.getFileList(dirPath);
System.out.println(
"========================获取文件夹中的文件end============文件个数：" + filePathList.size());
// 写入的Excel
WritableWorkbook book = null;
WritableSheet sheet = null;
String path = "E:\\wuyu\\学术论文2011.xls";
book = Workbook.createWorkbook(new File(path));
sheet = book.createSheet("KYLW", 0);
int rowIndex = 1;
String filePath = "";
String filePath2 = "";
for (int filePathIndex = 0; filePathIndex < filePathList.size(); filePathIndex++) {
filePath = (String) filePathList.get(filePathIndex);
filePath2 = filePath.replaceAll("\\\\", "\\/");
InputStream is = new FileInputStream(filePath2); // File is = new File("D:/test2011.xls");
Workbook rwb = Workbook.getWorkbook(is);
Sheet rs = (Sheet) rwb.getSheet(0);
int columnNum = rs.getColumns();
int rowNum = rs.getRows();
System.out.println(
"第" + filePathIndex + "个 Excel：" + filePath2 + ".中行列为：" + rowNum + ", " + columnNum);
// columnNum = 45; //学术论文中的特定值
Cell cell = null;
String contentTemp = "";
for (int i = 6; i <= rowNum - 1; i++) {
if (rs.getCell(19, i).getContents().trim().equals("2011")
|| rs.getCell(19, i).getContents().trim().equals("2011年")
|| (rs.getCell(19, i).getContents().length() >= 4
&& rs.getCell(19, i).getContents().substring(0, 3).equals("2011"))) {
for (int j = 0; j <= columnNum - 1; j++) {
cell = rs.getCell(j, i);
contentTemp = cell.getContents();
if (j == 0 && contentTemp.equals("") && contentTemp.trim().equals("")) {
break; // 如果第一列数据为空，说明数据不存在。“论文”中名称是必须的
}
// System.out.print(j+","+i+":"+contentTemp+" ");
// 写入Excel
Label label = new Label(j, rowIndex, contentTemp);
sheet.addCell(label);
}
rowIndex++;
}
}
rwb.close();
}
book.write();
book.close();
System.out.println("学术论文写入结束");
}
/** 解析碎片与分面之间的关系，填写assemble表（表格变化） （针对二叉树Binary_tree） */
public void assemble(String keyword) throws Exception {
try {
// 解析数据，数据加到数据库里面
String catalog = KeywordCatalogDesign.GetKeywordCatalog(keyword);
String path = catalog + keyword + "-tag_changed.xls";
File file = new File(path);
if (!file.exists()) {
System.out.println(path + " 不存在，请重新生成解析表格...");
} else {
System.out.println("\n开始对应： " + path);
Workbook book = Workbook.getWorkbook(file);
Sheet sheet = book.getSheet(0);
int row = sheet.getRows();
String[] facetname = {
"definition",
"feature",
"implementation",
"example",
"operation",
"application",
"method",
"type",
"relevant",
"history",
"description",
"purpose",
"explanation",
"storage",
"simulator"
};
for (int i = 0; i < row; i++) {
Cell cell0 = sheet.getCell(0, i);
Cell cell12 = sheet.getCell(12, i);
if (cell12.getContents().equals("1")) {
String fragmentid = cell0.getContents().replaceAll("\\+", "\\_");
int facetid;
Cell cell13 = sheet.getCell(13, i);
Cell cell14 = sheet.getCell(14, i);
Cell cell15 = sheet.getCell(15, i);
Cell cell16 = sheet.getCell(16, i);
String[] tag = {
cell14.getContents(), cell15.getContents(), cell16.getContents(), cell13.getContents()
};
for (int m = 0; m < tag.length; m++) {
for (int n = 0; n < facetname.length; n++) {
if (tag[m].equals(facetname[n])) {
facetid = n + 1;
sql = "replace into assemble values (?, ?, ?, ?, ?)";
Object[] answerobject = new Object[] {52, facetid, 1, fragmentid, " "};
mysqlCon.doSql(sql, answerobject);
// i = mysqlCon.getUpdateCount();
// if (i != -1) {
// System.out.println("数据插入成功！");
// } else {
// System.out.println("数据插入失败！");
// }
// 关闭链接
mysqlCon.getClose();
System.out.println(fragmentid + " " + facetid + " 52");
}
}
}
}
}
}
} catch (Exception ex) {
System.out.println("Error : " + ex.toString());
}
}
/**
* 获取数据表中某个属性的信息增益
*
* @param pt 需要计算信息增益的属性
* @param t 判断属性，也即属性pt分类时的依据属性。
* @return 属性pt的信息增益
*/
public String[] getInformationGain(String pt, String t) {
String[] gain_and_support = new String[3];
String gain = null;
int cla = 0; // 代表分类数目
BufferedWriter writer = null;
int part = 0;
int target = 0;
String[] attr = PublicData.getAttr();
for (int p = 0; p < attr.length; p++) {
if (attr[p].equals(pt)) {
part = p;
System.out.println("分类属性是：" + attr[p]);
}
if (attr[p].equals(t)) {
target = p;
System.out.println("所选属性是：" + attr[p]);
}
}
/** 所有参与计算的变量必须都放到方法内部来，避免下次计算时重复 */
float yes_to_attr = 0; // 属于当前类的数目
float no_to_attr = 0;
BigDecimal info = new BigDecimal(0); // 计算元组分类所需的期望信息
BigDecimal infoD = new BigDecimal(0); // 存储当前列属性的信息增益
float sum = 0; // 存储总行数
java.text.DecimalFormat DataFormat =
(java.text.DecimalFormat) java.text.DecimalFormat.getInstance();
// 为了得到整数相处保留三位小数的结果。注意，经过这样的处理后，计算得到的结果是String类型
DataFormat.applyPattern("##.###"); // 为了得到整数相处保留三位小数的结果
value = new int[50][2];
// 以下是用来读取Excel文件中的单元值
try {
Workbook book = Workbook.getWorkbook(new File(PublicData.getFile()));
Sheet sheet = book.getSheet(0);
for (int i = 1; i < sheet.getRows(); i++) // 默认第一行为属性辨别行，不参与计算
{
Cell cell = sheet.getCell(target, i); // 获取当前属性所在列
Cell cell2 = sheet.getCell(part, i); // 获取结果列，即训练元组最终分类类别那一列
String c2 = cell2.getContents().replaceAll("\"", "").trim();
String result = cell.getContents().replaceAll("\"", " ").trim();
if (list.contains(result) == false && result != null) // 将所选属性加入到list中
{
list.add(result);
cla = list.indexOf(result);
} else if (list.contains(result) == true)
cla = list.indexOf(result); // 以当前属性的类别（String类型）所在list中的索引为value的第一维
if (c2.equals("y") || c2.equals("yes")) // 以二维数组value的第二维的第一列来存储属于最终类的总数
{
value[cla][0]++;
yes_to_attr++; // 全部数据中符合条件占比
} else // if(c2.equals("n")||c2.equals("no"))
{
value[cla][1]++; // 第二列来存储不属于最终列的总数
no_to_attr++; // 全部数据中不符合数量
}
}
// writer.close();
book.close();
} catch (Exception e) {
e.printStackTrace();
}
// 以下是用来计算信息增益的程序段
System.out.println("属性长度" + list.size());
sum = yes_to_attr + no_to_attr;
BigDecimal ysum = mlog.getDivide(yes_to_attr, sum);
BigDecimal nsum = mlog.getDivide(no_to_attr, sum);
info = (nsum.multiply(mlog.getLog(nsum))).abs().add((ysum.multiply(mlog.getLog(ysum))).abs());
String[] at = new String[list.size()];
System.out.println("属性长度-------------" + list.size());
for (int p = 0; p < list.size(); p++) {
if (!(list.get(p).equals("") || list.get(p).equals(null))) {
at[p] = list.get(p);
}
int a = value[p][0];
int b = value[p][1];
System.out.println("|" + list.get(p) + "| " + a + " | " + b + " |");
int c = a + b;
BigDecimal asum = mlog.getDivide(a, c);
BigDecimal bsum = mlog.getDivide(b, c); // asum是肯定总量，bsum是否定总量
BigDecimal csum = mlog.getDivide(c, sum);
if (a == 0 && b != 0)
infoD = infoD.add((csum.multiply(bsum.multiply(mlog.getLog(bsum)))).abs());
else if (b == 0 && a != 0)
infoD = infoD.add((csum.multiply(asum.multiply(mlog.getLog(asum)))).abs());
else if (a != 0 && b != 0)
infoD =
infoD.add(
((csum.multiply(asum.multiply(mlog.getLog(asum)))).abs())
.add((csum.multiply(bsum.multiply(mlog.getLog(bsum)))).abs()));
else if (a == 0 && b == 0) infoD = infoD.add(new BigDecimal(0));
}
for (int i = 0; i < at.length; i++) {
if (i == 0 && at[i] != null) {
attr_part = at[i];
} else {
if (at[i] != null) {
attr_part = attr_part + "," + at[i];
}
}
}
System.out.println("attr_part长度是：" + attr_part.length());
try {
String f = HEAD + t + LAST;
System.out.println("正在尝试写入文件-------" + f);
BufferedWriter w = new BufferedWriter(new FileWriter(new File(f)));
w.append(attr_part.trim()); // 去掉双引号
w.flush();
w.close();
} catch (IOException e) {
e.printStackTrace();
}
attr_part = "";
gain = info.subtract(infoD).toString();
System.out.println("当前属性信息增益：" + infoD);
list.clear();
// 清空list表，以便下次存储时不会由于前面的插入元素受干扰
gain_and_support[0] = gain;
gain_and_support[1] = yes_to_attr + "";
gain_and_support[2] = no_to_attr - 1 + "";
return gain_and_support;
}
/**
* @param is 要导入Excel的输入流
* @param sheetName 导入的工作表名称
* @param entityClass List中对象的类型（Excel中的每一行都要转化为该类型的对象）
* @param fieldMap 类的英文属性和Excel中的中文列名的对应关系 例：{id=编号}
* @param uniqueFields 指定业务主键组合（即复合主键），这些列的组合不能重复
* @return List
* @throws ExcelException @Description 将Excel转化成实体对象List
*/
public static <T> List<T> excelToList(
InputStream is,
String sheetName,
Class<T> entityClass,
LinkedHashMap<String, String> fieldMap,
String[] uniqueFields)
throws ExcelException {
// 定义要返回的list
List<T> resultList = new ArrayList<T>();
try {
// 根据excel数据源创建WorkBook
Workbook wb = Workbook.getWorkbook(is);
// 获取工作表
Sheet sheet = wb.getSheet(sheetName);
// 获取工作表的有效行数
int realRows = 0;
for (int i = 0; i < sheet.getRows(); i++) {
int nullCols = 0;
for (int j = 0; j < sheet.getColumns(); j++) {
Cell CurrentCell = sheet.getCell(j, i);
if (CurrentCell == null || "".equals(CurrentCell.getContents().toString())) {
nullCols++;
}
}
if (nullCols == sheet.getColumns()) {
break;
} else {
realRows++;
}
}
// 如果Excel中没有任何数据则提示错误信息
if (realRows <= 1) {
throw new ExcelException("Excel文件中没有任何数据");
}
Cell[] firstRow = sheet.getRow(0);
String[] excelFieldNames = new String[firstRow.length];
// 获取Excel的列名
for (int i = 0; i < firstRow.length; i++) {
excelFieldNames[i] = firstRow[i].getContents().toString().trim();
}
// 判断需要的字段在Excel中是否都存在
boolean isExist = true;
List<String> excelFieldList = Arrays.asList(excelFieldNames);
for (String cnName : fieldMap.values()) {
if (!excelFieldList.contains(cnName)) {
isExist = false;
break;
}
}
// 如果有列名不存在或不匹配，则抛出异常并提示错误
if (!isExist) {
throw new ExcelException("Excel中缺少必要的字段，或字段名称有误");
}
// 将列名和列号放入Map中，这样通过列名就可以拿到列号
LinkedHashMap<String, Integer> colMap = new LinkedHashMap<String, Integer>();
for (int i = 0; i < excelFieldNames.length; i++) {
colMap.put(excelFieldNames[i], firstRow[i].getColumn());
}
// 判断是否有重复行
// 1.获取uniqueFields指定的列
Cell[][] uniqueCells = new Cell[uniqueFields.length][];
for (int i = 0; i < uniqueFields.length; i++) {
int col = colMap.get(uniqueFields[i]);
uniqueCells[i] = sheet.getColumn(col);
}
// 2.从指定列中寻找重复行
for (int i = 1; i < realRows; i++) {
int nullCols = 0;
int length = uniqueFields.length;
for (int j = 0; j < length; j++) {
Cell currentCell = uniqueCells[j][i];
String currentContent = currentCell.getContents().toString().trim();
Cell sameCell =
sheet.findCell(
currentContent,
currentCell.getColumn(),
currentCell.getRow() + 1,
currentCell.getColumn(),
uniqueCells[j][realRows - 1].getRow(),
true);
if (sameCell != null) {
nullCols++;
}
}
// 复合主键，意味着这些列的组合不能重复，
// 只有当所有的列都有重复的时候，才被认为是有重复行
if (nullCols == length) {
throw new Exception("Excel中有重复行，请检查");
}
}
// 将sheet转换为list
for (int i = 1; i < realRows; i++) {
// 新建要转换的对象
T entity = entityClass.newInstance();
// 给对象中的字段赋值
for (Map.Entry<String, String> entry : fieldMap.entrySet()) {
// 获取英文字段名
String enNormalName = entry.getKey();
// 获取中文字段名
String cnNormalName = entry.getValue();
// 根据中文字段名获取列号
int col = colMap.get(cnNormalName);
// 获取当前单元格中的内容
String content = sheet.getCell(col, i).getContents().toString().trim();
// 给对象赋值
setFieldValueByName(enNormalName, content, entity);
}
resultList.add(entity);
}
} catch (Exception e) {
e.printStackTrace();
// 如果是ExcelException,则直接抛出
if (e instanceof ExcelException) {
throw (ExcelException) e;
} else {
// 否则将其包装成ExcelException,再将其抛出
throw new ExcelException("导入ExceL失败");
}
}
return resultList;
}