@Override
public void showImportObject(String filePath) throws Exception {
// TODO Auto-generated method stub
String path = filePath.replace("\\", "\\\\").replace("C:\\\\fakepath", "D:\\kjcoutput");
Workbook workbook = Workbook.getWorkbook(new File(path));
Sheet sheet = workbook.getSheet(0);
int rows = sheet.getRows();
for (int i = 1; i < rows; i++) {
Wqwqxkzxq1 wqwqxkzxq1 = new Wqwqxkzxq1();
wqwqxkzxq1.setXh(sheet.getCell(0, i).getContents());
wqwqxkzxq1.setZymc(sheet.getCell(1, i).getContents());
wqwqxkzxq1.setBz(sheet.getCell(2, i).getContents());
wqwqxkzxq1.setJlnf(sheet.getCell(3, i).getContents());
// xzxzgzb.setJlnf(sheet.getCell(13, i).getContents());
wqwqxkzxq1.setUsername(formListTemp.get(0).getUsername());
wqwqxkzxq1.setGxsj(new SimpleDateFormat("yyyy-MM-dd").format(new Date()));
wqwqxkzxq1.setSubmit(0);
// xzxzgzb.setUsername();
wqwqxkzxq1Dao.save(wqwqxkzxq1);
}
workbook.close();
}
public static void main(String g[]) {
DataDto ddto = new DataDto();
try {
ArrayList<String> executeTC = new ArrayList();
String execute = null;
String workingDir = System.getProperty("user.dir");
System.out.println("Current working directory : " + workingDir);
String exPath = workingDir + "\\TestScripts\\Data\\Execute_Script.xls";
File file = new File(exPath);
System.out.println(" " + file);
Workbook wb = Workbook.getWorkbook(file);
Sheet sheet = wb.getSheet(0);
int rows = sheet.getRows();
int column = sheet.getColumns();
System.out.println("Rows " + rows);
for (int i = 1; i < rows; i++) {
ddto.setTCID(sheet.getCell(0, i).getContents());
ddto.setEXECUTE(sheet.getCell(1, i).getContents());
if (ddto.getEXECUTE().equalsIgnoreCase("Y")) {
getExecute.add(ddto.getEXECUTE());
executeTCID.add(ddto.getTCID());
}
}
System.out.println("execute condition :" + executeTCID);
System.out.println(getExecute);
} catch (Exception e) {
e.printStackTrace();
}
}
/**
* @param rulename
* @param support 0: no 1: yes 2: all
* @return
*/
public LinkedList<String> getPCREbyRuleset(Workbook book, String rulename, int support) {
LinkedList<String> lpcre = new LinkedList<String>();
Sheet sheet = book.getSheet(rulename);
if (sheet == null) {
return null;
}
// begin to read
int index = 1;
while (index < sheet.getRows()) {
String pStatus = sheet.getCell(3, index).getContents();
String pcre = sheet.getCell(4, index).getContents();
if (support == 1) {
if (pStatus.compareToIgnoreCase("1") == 0) {
lpcre.add(pcre);
}
} else if (support == 0) {
if (pStatus.compareToIgnoreCase("0") == 0) {
lpcre.add(pcre);
}
} else {
lpcre.add(pcre);
}
index++;
}
return lpcre;
}
public Map<String, Semantic> loadAxiom() {
Map<String, Semantic> axiom = new HashMap<String, Semantic>();
Workbook readwb = null;
try {
readwb = Workbook.getWorkbook(new File("src/cn/edu/buaa/resources/Axiom.xls"));
Sheet readsheet = readwb.getSheet(0);
int rsRows = readsheet.getRows();
int i = 1;
while (i < rsRows) {
String name = readsheet.getCell(0, i).getContents().trim();
if (null == name || name.equals("")) continue;
List<Item> semanSet = new ArrayList<Item>();
Semantic seman = new Semantic(semanSet);
while (i < rsRows) {
String tmp = readsheet.getCell(0, i).getContents().trim();
if (null == tmp || tmp.equals("") || tmp.equals(name)) {
String premise = readsheet.getCell(1, i).getContents().trim();
String left = readsheet.getCell(2, i).getContents().trim();
String right = readsheet.getCell(3, i).getContents().trim();
if (null == premise || premise.equals("")) premise = null;
if (null == left || left.equals("")) left = null;
if (null == right || right.equals("")) right = null;
if (null != premise || null != left || null != right) {
Item item = new Item(premise, left, right);
semanSet.add(item);
}
i++;
} else {
break;
}
}
axiom.put(name, seman);
name = null;
}
} catch (BiffException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
} finally {
if (null != readwb) {
readwb.close();
}
}
// test axiom
for (String name : axiom.keySet()) {
System.out.println(name + ":");
List<Item> items = axiom.get(name).getSemanSet();
for (Item e : items) {
System.out.println(e.getPremise() + " -> " + e.getLeft() + " = " + e.getRight());
}
}
System.out.println("======================================");
return axiom;
}
@Test
public void verifyLogin() throws Exception {
fi = new FileInputStream("D:\\Workspace10AM\\Selenium_Autotest\\OR\\LoginOR.xls");
w = Workbook.getWorkbook(fi);
s = w.getSheet("Login");
driver.get("http://mail.in.com");
driver.findElement(By.id(s.getCell(0, 1).getContents())).sendKeys("jan30selenium");
driver.findElement(By.id(s.getCell(1, 1).getContents())).sendKeys("selenium");
driver.findElement(By.cssSelector(s.getCell(2, 1).getContents())).click();
Thread.sleep(3000);
}
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;
}
private void jButton7ActionPerformed(
java.awt.event.ActionEvent evt) { // GEN-FIRST:event_jButton7ActionPerformed
try {
if (lbl_ruta.getText().equals("")) {
JOptionPane.showMessageDialog(null, "Seleccione Archivo a importar");
return;
}
Workbook wb = Workbook.getWorkbook(arch);
Sheet sheet = wb.getSheet(0);
int rows = sheet.getRows();
String nrodoc = "";
DefaultListModel l = new DefaultListModel();
for (int i = 0; i < rows; i++) {
nrodoc = sheet.getCell(0, i).getContents().trim();
l.addElement(nrodoc);
}
jList2.setModel(l);
lbln_regcar.setText(l.size() + "");
} catch (Exception e) {
JOptionPane.showMessageDialog(null, e);
}
} // GEN-LAST:event_jButton7ActionPerformed
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;
}
}
@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());
}
@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 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;
}
/**
* 取得cell
*
* @param colNum 列号
* @param rowNum 行号
* @return
*/
public Cell getCell(int colNum, int rowNum) {
if (readOnlyWBook == true) {
return currentSheet.getCell(colNum, rowNum);
} else {
return wrCurrentSheet.getCell(colNum, rowNum);
}
}
public static void main(String args[]) {
try {
// 构建Workbook对象, 只读Workbook对象
// 直接从本地文件创建Workbook
// 从输入流创建Workbook
System.out.println("start load file-------------------------");
InputStream is = new FileInputStream("E:/account.xls"); // 创建输入
jxl.Workbook rwb = Workbook.getWorkbook(is);
Sheet rs = rwb.getSheet(0); // 读取第一个sheet
int colNum = rs.getColumns(); // 列数
int rowNum = rs.getRows(); // 行数
// System.out.println(colNum+" "+rowNum);
// 创建account bean
ApplicationContext ctx =
new ClassPathXmlApplicationContext(
new String[] {
"spring/applicationContext-model-account.xml", "spring/applicationContext.xml"
});
AccountService g = (AccountService) ctx.getBean("accountService");
for (int i = 1; i < rowNum; i++) {
String no = rs.getCell(0, i).getContents();
String pass = rs.getCell(1, i).getContents();
String name = rs.getCell(2, i).getContents();
String maj = rs.getCell(3, i).getContents();
String grade = rs.getCell(4, i).getContents();
String cla = rs.getCell(5, i).getContents();
String idn = rs.getCell(6, i).getContents();
String email = rs.getCell(7, i).getContents();
String phone = rs.getCell(8, i).getContents();
String status = rs.getCell(9, i).getContents();
String role = rs.getCell(10, i).getContents();
ModelAccount ma = new ModelAccount();
ma.setAccountNo(no);
ma.setAccountPassword(pass);
ma.setAccountRealName(name);
ma.setAccountMajority(maj);
ma.setAccountGrade(grade);
ma.setAccountClass(cla);
ma.setAccountIdentification(idn);
ma.setAccountEmail(email);
ma.setAccountMobilePhone(phone);
ma.setAccountStatus(0);
ModelRoles ac = new ModelRoles();
ac.setRole_id(role);
ma.setAccountRole(ac);
g.saveOrUpdate(ma);
// ma.setAccount_role(account_role)
// System.out.println(no);
}
} catch (Exception e) {
e.printStackTrace();
}
}
/**
* @see read excel by row,read sheet
* @param sheet
* @param numrow
* @return
*/
public static ArrayList readrow(Sheet 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;
}
/**
* 获取第一个第一行单元格内容非空的列号，因为第一行为参数key，不能为空
*
* @param mSheet
* @return
*/
private static int getColCount(Sheet mSheet) {
int count = 0;
int columns = mSheet.getColumns();
for (int i = 0; i < columns; i++) {
String content = mSheet.getCell(i, 0).getContents();
if (StringTools.isEmpty(content)) {
break;
}
++count;
}
return count;
}
/**
* 获取第一个第一列单元格内容非空的行号,因为第一列为caseId，不能为空
*
* @param mSheet
* @return
*/
private static int getRowCount(Sheet mSheet) {
int count = 0;
int row = mSheet.getRows();
for (int i = 0; i < row; i++) {
String content = mSheet.getCell(0, i).getContents();
if (StringTools.isEmpty(content)) {
break;
}
++count;
}
return count;
}
public Object getValueAt(int rowIndex, int columnIndex) {
if (columnIndex == 0) {
String value = getAnnotationMap().get(rowIndex);
if (value == null) {
return AnnotationCellEditor.NONE;
} else {
return value;
}
}
columnIndex--;
if (reductionSelection == null) {
return excelSheet.getCell(columnIndex, rowIndex).getContents();
} else {
return excelSheet
.getCell(
columnIndex + reductionSelection.getColumnIndexStart(),
rowIndex + reductionSelection.getRowIndexStart())
.getContents();
}
}
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();
}
/**
* 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;
}
public List<FreshCard> BeangetFromExcel() {
File operFile = new File(Constant.EXCBUILS, needFileName);
List<FreshCard> list = new ArrayList<FreshCard>();
try {
Workbook wb = Workbook.getWorkbook(operFile);
int sheets = wb.getSheets().length;
for (int i = 0; i < sheets; i++) {
Sheet sheet = wb.getSheet(i);
int k;
if (sheet != null) {
int col = sheet.getColumns();
int row = sheet.getRows();
for (int j = 0; j < row; j++) {
if (j > 0) {
FreshCard fd = new FreshCard();
for (k = 0; k < col; k++) {
String data = sheet.getCell(k, j).getContents();
if (k == 0) {
fd.setFreshTime(data);
} else if (k == 1) {
fd.setCardId(Integer.parseInt(data));
} else if (k == 2) {
fd.setName(data);
} else if (k == 3) {
fd.setDuty(data);
} else if (k == 4) {
fd.setNum(data);
} else if (k == 5) {
fd.setFreshCount(Integer.parseInt(data));
} else if (k == 6) {
fd.setState(data);
} else {
break;
}
}
list.add(fd);
}
}
}
}
} catch (BiffException | IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return list;
}
/**
* 得到需要爬取的百度百科词条
*
* @throws Exception
*/
public static ArrayList<String> getWord() throws Exception {
String excelPath = "imagedata/baike/HTML/C_termlist.xls";
Workbook wb = Workbook.getWorkbook(new File(excelPath));
Sheet sheet = wb.getSheet(0);
int row = sheet.getRows();
System.out.println("百科词条数：" + row);
ArrayList<String> failword = new ArrayList<String>();
for (int i = 0; i < row; i++) {
String word = sheet.getCell(1, i).getContents();
System.out.println("\n正在爬取: " + word);
int flag = crawlerImage(word);
if (flag == 0) {
failword.add(word);
}
}
return failword;
}
public ExcelInstance() {
// Initialize the template file
try {
template = Workbook.getWorkbook(new File(""));
templateSheet = template.getSheet(0);
} catch (IOException | BiffException e) {
JOptionPane.showMessageDialog(
null,
"Excel template file is either malformed or missing, please verify its integrity",
"Error",
JOptionPane.ERROR_MESSAGE);
}
// Init
String currentCell;
for (int i = 0; i < 100; i++) {
for (int j = 0; j < 100; j++) {
currentCell = templateSheet.getCell(i, j).getContents();
if (currentCell.matches("\\${\\d{1,}}")) { // TODO: Proper regex
}
}
}
}
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);
}
/**
* Gets the named cell from this workbook. If the name refers to a range of cells, then the cell
* on the top left is returned. If the name cannot be found, null is returned
*
* @param name the name of the cell/range to search for
* @return the cell in the top left of the range if found, NULL otherwise
*/
public Cell findCellByName(String name) {
NameRecord nr = (NameRecord) namedRecords.get(name);
if (nr == null) {
return null;
}
NameRecord.NameRange[] ranges = nr.getRanges();
// Go and retrieve the first cell in the first range
Sheet s = getSheet(getExternalSheetIndex(ranges[0].getExternalSheet()));
int col = ranges[0].getFirstColumn();
int row = ranges[0].getFirstRow();
// If the sheet boundaries fall short of the named cell, then return
// an empty cell to stop an exception being thrown
if (col > s.getColumns() || row > s.getRows()) {
return new EmptyCell(col, row);
}
Cell cell = s.getCell(col, row);
return cell;
}
/**
* 获取数据表中某个属性的信息增益
*
* @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;
}
public void Read() {
try {
Sheet sh = Functions.readExcel("capstone.xlt");
Sheet sh2 = Functions.readExcel("liste.xlt");
String word;
for (int k = 0; k < 81; k++) {
hash.put(sh2.getCell(1, k).getContents().toString(), 0);
}
for (int i = 3; i < 1457; i++) {
word =
sh.getCell(3, i).getContents().toString()
+ sh.getCell(3, i + 1).getContents().toString()
+ sh.getCell(3, i + 2).getContents().toString()
+ sh.getCell(3, i + 3).getContents().toString();
hash.put(word, hash.get(word) + 1);
}
String q_1 = null;
String q_2 = null;
String q_3 = null;
String q_4 = null;
for (int b = 0; b < 3; b++) {
q_1 = null;
if (b == 0) q_1 = "G";
else if (b == 1) q_1 = "Y";
else if (b == 2) q_1 = "K";
for (int c = 0; c < 3; c++) {
q_2 = null;
q_2 = q_1;
if (c == 0) q_2 = q_1 + "G";
else if (c == 1) q_2 = q_1 + "Y";
else if (c == 2) q_2 = q_1 + "K";
for (int d = 0; d < 3; d++) {
q_3 = null;
q_3 = q_2;
if (d == 0) q_3 = q_2 + "G";
else if (d == 1) q_3 = q_2 + "Y";
else if (d == 2) q_3 = q_2 + "K";
for (int e = 0; e < 3; e++) {
q_4 = null;
q_4 = q_3;
if (e == 0) q_4 = q_3 + "G";
else if (e == 1) q_4 = q_3 + "Y";
else if (e == 2) q_4 = q_3 + "K";
alist.put(
q_4,
(float) hash.get(q_4)
/ (hash.get(q_3 + "G") + hash.get(q_3 + "Y") + hash.get(q_3 + "K")));
}
}
}
}
for (int k = 0; k < 81; k++) {
System.out.println(
(sh2.getCell(1, k).getContents().toString()
+ " is :"
+ alist.get(sh2.getCell(1, k).getContents())));
Weather_GUI.txt.append(
(sh2.getCell(1, k).getContents().toString()
+ " is :"
+ alist.get(sh2.getCell(1, k).getContents()))
+ "\n");
}
} catch (BiffException | IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
private ActionForward impScore(
ActionMapping mapping,
ActionForm form,
HttpServletRequest request,
HttpServletResponse response) {
try {
request.setCharacterEncoding("utf-8");
response.setCharacterEncoding("utf-8");
String path = request.getContextPath();
String rootPath = request.getSession().getServletContext().getRealPath("/");
// 先建文件夹
String folderPath = rootPath + "export/";
String str_Path = folderPath.toString().replace("\\", "/");
// 上传文件
SmartUpload mySmartUpload = new SmartUpload();
// 设置允许上传的文件类型
// mySmartUpload.setAllowedFilesList("xls");
ServletConfig config = this.getServlet().getServletConfig();
mySmartUpload.initialize(config, request, response);
mySmartUpload.upload();
String CURDATE = GetRecentDate.getRecentDate("yyyyMMddHHmmssS");
String FILE_PATH = "";
for (int i = 0; i < mySmartUpload.getFiles().getCount(); i++) {
com.jspsmart.upload.File file = mySmartUpload.getFiles().getFile(i);
if (!file.getFileName().equals("")) {
FILE_PATH = str_Path + CURDATE + "_" + i + "." + file.getFileExt();
file.saveAs(FILE_PATH);
}
}
ScoreManagementDAO dao = new ScoreManagementDAO();
InputStream stream = new FileInputStream(FILE_PATH);
Workbook rwb = Workbook.getWorkbook(stream);
Sheet sheet = rwb.getSheet(0);
for (int i = 1; i < sheet.getRows(); i++) {
String SCORE_YEAR = sheet.getCell(0, i).getContents(); // 年份
if (SCORE_YEAR.equals("")) break;
String SEMESTER = sheet.getCell(1, i).getContents(); // 学期
String SCORE_GRADE = sheet.getCell(2, i).getContents(); // 班级
String SCORE_CLASS = sheet.getCell(3, i).getContents(); // 班级
String SCORE_NO = sheet.getCell(4, i).getContents(); // 学号
String STU_NAME = sheet.getCell(5, i).getContents(); // 姓名
String STU_SEX = sheet.getCell(6, i).getContents(); // 性别
String AAS = sheet.getCell(7, i).getContents(); // 文理科
String KEMU = sheet.getCell(8, i).getContents(); // 考试名称
String CHINESE = sheet.getCell(9, i).getContents(); // 语文
if (CHINESE.equals("")) CHINESE = "0";
String MATCH = sheet.getCell(10, i).getContents(); // 数学
if (MATCH.equals("")) MATCH = "0";
String ENGLISH = sheet.getCell(12, i).getContents(); // 英语
if (ENGLISH.equals("")) ENGLISH = "0";
String PHYSICAL = sheet.getCell(13, i).getContents(); // 物理
if (PHYSICAL.equals("")) PHYSICAL = "0";
String CHEMISTRY = sheet.getCell(14, i).getContents(); // 化学
if (CHEMISTRY.equals("")) CHEMISTRY = "0";
String BIOLOGICAL = sheet.getCell(15, i).getContents(); // 生物
if (BIOLOGICAL.equals("")) BIOLOGICAL = "0";
String HISTORY = sheet.getCell(16, i).getContents(); // 历史
if (HISTORY.equals("")) HISTORY = "0";
String POLITY = sheet.getCell(17, i).getContents(); // 政治
if (POLITY.equals("")) POLITY = "0";
String GEOGRAPHY = sheet.getCell(18, i).getContents(); // 地理
if (GEOGRAPHY.equals("")) GEOGRAPHY = "0";
float total =
Float.valueOf(CHINESE)
+ Float.valueOf(MATCH)
+ Float.valueOf(ENGLISH)
+ Float.valueOf(PHYSICAL)
+ Float.valueOf(CHEMISTRY)
+ Float.valueOf(BIOLOGICAL)
+ Float.valueOf(HISTORY)
+ Float.valueOf(POLITY)
+ Float.valueOf(GEOGRAPHY);
HashMap map = new HashMap();
map.put("SCORE_YEAR", SCORE_YEAR);
map.put("SEMESTER", SEMESTER);
map.put("SCORE_GRADE", SCORE_GRADE);
map.put("SCORE_CLASS", SCORE_CLASS);
map.put("SCORE_NO", SCORE_NO);
map.put("STU_NAME", STU_NAME);
map.put("STU_SEX", STU_SEX);
map.put("AAS", AAS);
map.put("KEMU", KEMU);
map.put("CHINESE", CHINESE);
map.put("MATCH", MATCH);
map.put("ENGLISH", ENGLISH);
map.put("PHYSICAL", PHYSICAL);
map.put("CHEMISTRY", CHEMISTRY);
map.put("BIOLOGICAL", BIOLOGICAL);
map.put("HISTORY", HISTORY);
map.put("POLITY", POLITY);
map.put("GEOGRAPHY", GEOGRAPHY);
map.put("TOTAL_SCORE", total);
dao.insertScore(map);
}
rwb.close();
stream.close();
response.getWriter().write("1");
response.getWriter().close();
} catch (Exception e) {
e.printStackTrace();
}
return null;
}
public void readData(ItemMgr itemMgr) throws SQLException {
Connection conn = OracleGate.getConnection();
itemMgr.getPnList().clear();
Workbook workbook = null;
try {
workbook = Workbook.getWorkbook(this.getCurrentFile());
Sheet sheet = workbook.getSheet(0); // the first sheet in excel file
int rows = sheet.getRows();
int columns = sheet.getColumns();
for (int i = 1; i < rows; i++) {
if (sheet.getCell(1, i).getContents().trim() == null
|| (sheet.getCell(1, i).getContents().trim()).equals("")) break;
Part part = new Part("");
if (columns > 1) {
part.setPnCode(sheet.getCell(1, i).getContents().trim());
}
if (columns > 2) {
part.setPnName(sheet.getCell(2, i).getContents().trim());
}
if (columns > 3) {
part.setPartFullName(sheet.getCell(3, i).getContents().trim());
}
if (columns > 4) {
part.setPartTemplateCode(sheet.getCell(4, i).getContents().trim());
}
if (columns > 5) {
part.setRecurType(sheet.getCell(5, i).getContents().trim());
}
if (columns > 6) {
part.setAccrued(sheet.getCell(6, i).getContents().trim().equals("Y"));
}
if (columns > 7) {
part.setCashAccount(this.findAccounting(conn, sheet.getCell(7, i).getContents().trim()));
}
if (columns > 8) {
part.setClaimAccount(this.findAccounting(conn, sheet.getCell(8, i).getContents().trim()));
}
if (columns > 9) {
part.setEntityAccount(
this.findAccounting(conn, sheet.getCell(9, i).getContents().trim()));
}
part.setPnRowNo(i);
itemMgr.getPnList().add(part);
}
} catch (BiffException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
} finally {
try {
OracleGate.freeConnection(conn);
workbook.close();
} catch (Exception e) {
}
}
}
/**
* 导入Excel格式评奖信息到数据库
*
* @param mapping
* @param form
* @param request
* @param response
* @return
*/
public ActionForward importToDatabase(
ActionMapping mapping,
ActionForm form,
HttpServletRequest request,
HttpServletResponse response) {
DynaActionForm fileForm = (DynaActionForm) form;
FormFile formFile = (FormFile) fileForm.get("objExcel");
try {
String jxjlx = request.getParameter("jxjlx");
InputStream excelInput = formFile.getInputStream();
Workbook workbook = Workbook.getWorkbook(excelInput);
Sheet sheet = workbook.getSheet(0);
int rows = sheet.getRows();
Xs0708 xs0708 = null;
List xs0708List = new ArrayList();
for (int i = 2; i < rows; i++) {
String xh = sheet.getCell(1, i).getContents().trim();
String nd = sheet.getCell(0, i).getContents().trim();
String jx = sheet.getCell(2, i).getContents().trim();
String yhzh = sheet.getCell(3, i).getContents().trim();
String jlje = sheet.getCell(4, i).getContents().trim();
String bz = sheet.getCell(5, i).getContents().trim();
Xs0101 xs0101 = this.getStzzServiceImpl().findXs0101ByXh(xh);
if (xs0101 == null) {
this.writeJsMessage(
response,
"alert('导入失败,第"
+ (i + 1)
+ "行学号:"
+ xh
+ ",在系统中不存在');window.parent.returnValue='ok';window.parent.close();");
return null;
}
xs0708 = new Xs0708();
xs0708.setXs0101(xs0101);
xs0708.setJlnd(nd);
xs0708.setJx(jx);
xs0708.setYhzh(yhzh);
xs0708.setJlje(new Long(jlje));
xs0708.setBz(bz);
xs0708.setJxjlx(jxjlx);
xs0708List.add(xs0708);
}
workbook.close();
this.getStzzServiceImpl().batchSaveXs0708(xs0708List, request);
this.writeJsMessage(
response,
"alert('数据导入成功!共导入"
+ xs0708List.size()
+ "条数据');window.parent.returnValue='ok';window.parent.close();");
} catch (NumberFormatException e) {
this.writeJsMessage(
response,
"alert('导入失败,\"奖励金额\"列出现非法数据');window.parent.returnValue='ok';window.parent.close();");
} catch (FileNotFoundException e) {
this.writeJsMessage(
response, "alert('找不到文件!');window.parent.returnValue='ok';window.parent.close();");
} catch (IOException e) {
this.writeJsMessage(
response, "alert('输入输出错误!');window.parent.returnValue='ok';window.parent.close();");
} catch (BiffException e) {
this.writeJsMessage(
response, "alert('模板格式错误!');window.parent.returnValue='ok';window.parent.close();");
} catch (Exception e) {
this.writeJsMessage(
response, "alert('导入失败,未知错误!');window.parent.returnValue='ok';window.parent.close();");
}
return null;
}
/**
* 读取奖惩管理EXCEL内容
*
* @param mapping
* @param form
* @param request
* @param response
* @return
*/
public ActionForward readJcglExcl(
ActionMapping mapping,
ActionForm form,
HttpServletRequest request,
HttpServletResponse response) {
DynaActionForm fileForm = (DynaActionForm) form;
FormFile formFile = (FormFile) fileForm.get("objExcel");
List list = new ArrayList();
try {
InputStream excelInput = formFile.getInputStream();
Workbook workbook = Workbook.getWorkbook(excelInput);
Sheet sheet = workbook.getSheet(0);
int rows = sheet.getRows();
Xs0709 xs0709 = null;
for (int i = 9; i < rows; i++) {
try {
String xh = sheet.getCell(0, i).getContents().trim();
String wjrq = sheet.getCell(2, i).getContents().trim();
String wjlb = sheet.getCell(3, i).getContents().trim();
String cfmc = sheet.getCell(4, i).getContents().trim();
String cfdw = sheet.getCell(5, i).getContents().trim();
String wjjj = sheet.getCell(6, i).getContents().trim();
String cfwh = sheet.getCell(7, i).getContents().trim();
String cfrq = sheet.getCell(8, i).getContents().trim();
xs0709 = new Xs0709();
Xs0101 xs0101 = this.getJeffglServiceImpl().findXs0101Byxh(xh);
if (xs0101 == null) {
this.writeJsMessage(
response,
"alert('导入失败，第"
+ i
+ "行，‘学号’列的值:"
+ xh
+ "，在系统中不存在，请确认后再导入');window.parent.returnValue='ok';window.parent.close();");
return null;
}
xs0709.setXs0101(xs0101);
if (wjrq != null && !wjrq.equals("")) {
xs0709.setWjrq(wjrq);
} else {
this.writeJsMessage(
response,
"alert('导入失败，第"
+ i
+ "行，‘违纪日期’列的值为空，请确认后再导入');window.parent.returnValue='ok';window.parent.close();");
return null;
}
ZzdWjlb zdwjlb = null;
if (wjlb != null && !wjlb.equals("")) {
zdwjlb =
(ZzdWjlb)
ZzdSingleton.getInstance(request.getSession().getServletContext())
.getZdByNameKey("ZzdWjlb", wjlb);
} else {
this.writeJsMessage(
response,
"alert('导入失败，第"
+ i
+ "行，‘违纪类别’列的值为空，请确认后再导入');window.parent.returnValue='ok';window.parent.close();");
return null;
}
if (zdwjlb == null) {
this.writeJsMessage(
response,
"alert('导入失败，第"
+ i
+ "行，‘违纪类别’列的值在系统中不存在，请确认后再导入');window.parent.returnValue='ok';window.parent.close();");
return null;
} else {
xs0709.setZzdWjlb(zdwjlb);
}
ZzdCfmc zdwjmc = null;
if (cfmc != null && !cfmc.equals("")) {
zdwjmc =
(ZzdCfmc)
ZzdSingleton.getInstance(request.getSession().getServletContext())
.getZdByNameKey("ZzdCfmc", cfmc);
} else {
this.writeJsMessage(
response,
"alert('导入失败，第"
+ i
+ "行，‘处分名称’列的值为空，请确认后再导入');window.parent.returnValue='ok';window.parent.close();");
return null;
}
if (zdwjmc == null) {
this.writeJsMessage(
response,
"alert('导入失败，第"
+ i
+ "行，‘处分名称’列的值在系统中不存在，请确认后再导入');window.parent.returnValue='ok';window.parent.close();");
return null;
} else {
xs0709.setZzdCfmc(zdwjmc);
}
if (cfdw != null && !cfdw.equals("")) {
xs0709.setGycfdw(cfdw);
}
if (wjjj != null && !wjjj.equals("")) {
xs0709.setWjjk(wjjj);
}
if (cfwh != null && !cfwh.equals("")) {
xs0709.setCfwh(cfwh);
}
if (cfrq != null && !cfrq.equals("")) {
xs0709.setCfrq(cfrq);
}
list.add(xs0709);
} catch (Exception e) {
e.printStackTrace();
return null;
}
}
workbook.close();
} catch (FileNotFoundException e) {
this.writeJsMessage(
response,
"alert('数据导入失败，请检查EXCEL文件是否存在!');window.parent.returnValue='ok';window.parent.close();");
e.printStackTrace();
return null;
} catch (IOException e) {
this.writeJsMessage(
response,
"alert('数据导入失败，请检查EXCEL格式是否符合要求!');window.parent.returnValue='ok';window.parent.close();");
e.printStackTrace();
return null;
} catch (BiffException e) {
e.printStackTrace();
this.writeJsMessage(
response,
"alert('数据导入失败，请检查EXCEL格式是否符合要求!');window.parent.returnValue='ok';window.parent.close();");
return null;
} catch (Exception e) {
e.printStackTrace();
this.writeJsMessage(
response,
"alert('数据导入失败，系统没有检测到的错误!');window.parent.returnValue='ok';window.parent.close();");
return null;
}
try {
if (list.size() != 0) {
Iterator it = list.iterator();
Xs0709 xs0709 = null;
while (it.hasNext()) {
xs0709 = (Xs0709) it.next();
this.getIWjglService().saveXs0709(xs0709, request);
}
}
this.writeJsMessage(
response, "alert('数据导入成功!');window.parent.returnValue='ok';window.parent.close();");
} catch (Exception e) {
this.writeJsMessage(
response,
"alert('数据导入失败，一般是由于数据不符合格式引起，请检查数据!');window.parent.returnValue='ok';window.parent.close();");
}
return null;
}
