/**
* @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;
}
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
@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());
}
@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 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();
}
}
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;
}
/**
* 读Excel
*
* @param pathname
*/
public static LinkedList<HashMap<String, String>> readExcel(String pathname) {
LinkedList<HashMap<String, String>> list = new LinkedList<HashMap<String, String>>();
File file = new File(pathname);
String uri = "f:\\xml\\map\\edge.xml";
Workbook wb = null;
try {
wb = Workbook.getWorkbook(file);
Sheet sheet = wb.getSheet(0);
int rows = sheet.getRows();
// ThreadPoolExecutorTest threadPool = new
// ThreadPoolExecutorTest(queue);
for (int i = 0; i < rows; i++) {
HashMap<String, String> map = new HashMap<String, String>();
// System.out.println("第"+(i+1)+"条数据正在执行");
Cell[] cols = sheet.getRow(i);
String sender = cols[0].getContents();
String nodeID = cols[1].getContents();
String wayID = cols[2].getContents();
String depart = cols[3].getContents();
String edgeID = SaxService.getWayIdFromNodeXML(uri, nodeID, wayID);
map.put("sender", sender);
map.put("nodeID", nodeID);
map.put("wayID", wayID);
map.put("edgeID", edgeID);
map.put("depart", depart);
list.add(map);
}
} catch (Exception e) {
e.printStackTrace();
}
return list;
}
private List<String> getInvestigationNameFromExcel(File excelFile) throws Exception {
File tmpInvestigation =
new File(System.getProperty("java.io.tmpdir") + File.separator + "tmpInvestigation.txt");
if (tmpInvestigation.exists()) {
boolean deleteSuccess = tmpInvestigation.delete();
if (!deleteSuccess) {
throw new Exception("Deletion of tmp file 'tmpInvestigation.txt' failed, cannot proceed.");
}
}
boolean createSuccess = tmpInvestigation.createNewFile();
if (!createSuccess) {
throw new Exception("Creation of tmp file 'tmpInvestigation.txt' failed, cannot proceed.");
}
Workbook workbook = Workbook.getWorkbook(excelFile);
for (Sheet sheet : workbook.getSheets()) {
if (sheet.getName().toLowerCase().equals("investigation")) {
writeSheetToFile(sheet, tmpInvestigation);
}
}
List<String> names = XgapCommonImport.getInvestigationNameFromFile(tmpInvestigation);
return names;
}
public static List<ConsoleVo> readExcel(String fileName) {
List<ConsoleVo> list = new ArrayList<ConsoleVo>();
try {
Workbook book = Workbook.getWorkbook(new File(fileName));
Sheet[] sheets = book.getSheets();
for (Sheet s : sheets) {
for (int i = 1; i < s.getRows(); i++) {
ConsoleVo m = new ConsoleVo();
Cell[] cells = s.getRow(i);
if (cells != null) {
System.out.println(cells.length);
m.setLoginName(StringUtil.getNotNullValueString(cells[0].getContents()));
m.setUserName(StringUtil.getNotNullValueString(cells[1].getContents()));
m.setDeptId(StringUtil.getNotNullValueString(cells[2].getContents()));
m.setOrders(StringUtil.getNotNullValueString(cells[3].getContents()));
list.add(m);
} else {
System.out.print("excel格式错误导入数据失败！");
return null;
}
}
}
book.close();
return list;
} catch (Exception e) {
e.printStackTrace();
}
return null;
}
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;
}
}
public ExcelDataProvider(String classname, String methodname) {
try {
int dotNum = classname.indexOf(".");
if (dotNum > 0) {
classname = classname.substring(classname.lastIndexOf(".") + 1, classname.length());
}
String path = "data/" + classname + ".xls";
InputStream inputStream = new FileInputStream(path);
book = Workbook.getWorkbook(inputStream);
// 方法名即为sheet名
sheet = book.getSheet(methodname);
// sheet = book.getSheet(0);
rowNum = sheet.getRows();
Cell[] cell = sheet.getRow(0);
columnNum = cell.length;
columnnName = new String[cell.length];
for (int i = 0; i < cell.length; i++) {
columnnName[i] = cell[i].getContents().toString();
}
this.currentRowNo++;
} catch (Exception e) {
e.printStackTrace();
Assert.fail("unable to read Excel data");
}
}
/**
* @throws IOException
* @throws FileNotFoundException
* @throws BiffException
* @see
*/
public int getmaxrownumber(String filename, String sheetname)
throws BiffException, FileNotFoundException, IOException {
int rownumber = 0;
Workbook wb = Workbook.getWorkbook(new FileInputStream(filename));
Sheet sheet = wb.getSheet(sheetname);
rownumber = sheet.getRows();
return rownumber;
}
/**
* @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;
}
@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);
}
/**
* 获取第一个第一行单元格内容非空的列号，因为第一行为参数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;
}
/** Writes out the workbook data as XML, without formatting information */
private void writeXML() throws IOException {
try {
OutputStreamWriter osw = new OutputStreamWriter(out, encoding);
BufferedWriter bw = new BufferedWriter(osw);
bw.write("<?xml version=\"1.0\" ?>");
bw.newLine();
bw.write("<!DOCTYPE workbook SYSTEM \"workbook.dtd\">");
bw.newLine();
bw.newLine();
bw.write("<workbook>");
bw.newLine();
for (int sheet = 0; sheet < workbook.getNumberOfSheets(); sheet++) {
Sheet s = workbook.getSheet(sheet);
bw.write(" <sheet>");
bw.newLine();
bw.write(" <name><![CDATA[" + s.getName() + "]]></name>");
bw.newLine();
Cell[] row = null;
for (int i = 0; i < s.getRows(); i++) {
bw.write(" <row number=\"" + i + "\">");
bw.newLine();
row = s.getRow(i);
for (int j = 0; j < row.length; j++) {
if (row[j].getType() != CellType.EMPTY) {
bw.write(" <col number=\"" + j + "\">");
bw.write("<![CDATA[" + row[j].getContents() + "]]>");
bw.write("</col>");
bw.newLine();
}
}
bw.write(" </row>");
bw.newLine();
}
bw.write(" </sheet>");
bw.newLine();
}
bw.write("</workbook>");
bw.newLine();
bw.flush();
bw.close();
} catch (UnsupportedEncodingException e) {
System.err.println(e.toString());
}
}
/**
* Constructor
*
* @param w The workbook to interrogate
* @param out The output stream to which the CSV values are written
* @param encoding The encoding used by the output stream. Null or unrecognized values cause the
* encoding to default to UTF8
* @param hide Suppresses hidden cells
* @exception java.io.IOException
*/
public CSV(Workbook w, OutputStream out, String encoding, boolean hide) throws IOException {
if (encoding == null || !encoding.equals("UnicodeBig")) {
encoding = "UTF8";
}
try {
OutputStreamWriter osw = new OutputStreamWriter(out, encoding);
BufferedWriter bw = new BufferedWriter(osw);
for (int sheet = 0; sheet < w.getNumberOfSheets(); sheet++) {
Sheet s = w.getSheet(sheet);
if (!(hide && s.getSettings().isHidden())) {
bw.write("*** " + s.getName() + " ****");
bw.newLine();
Cell[] row = null;
for (int i = 0; i < s.getRows(); i++) {
row = s.getRow(i);
if (row.length > 0) {
if (!(hide && row[0].isHidden())) {
bw.write(row[0].getContents());
// Java 1.4 code to handle embedded commas
// bw.write("\"" +
// row[0].getContents().replaceAll("\"","\"\"")
// + "\"");
}
for (int j = 1; j < row.length; j++) {
bw.write(',');
if (!(hide && row[j].isHidden())) {
bw.write(row[j].getContents());
// Java 1.4 code to handle embedded quotes
// bw.write("\"" +
// row[j].getContents().replaceAll("\"","\"\"")
// + "\"");
}
}
}
bw.newLine();
}
}
}
bw.flush();
bw.close();
} catch (UnsupportedEncodingException e) {
System.err.println(e.toString());
}
}
/**
* 取得当前sheet的sheet名
*
* @return
*/
public String getSheetName() {
if (readOnlyWBook == true) {
return currentSheet.getName();
} else {
return wrCurrentSheet.getName();
}
}
/**
* 取得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);
}
}
/**
* 取得一行的cell
*
* @param rowNum 行号
* @return
*/
public Cell[] getRowCells(int rowNum) {
if (readOnlyWBook == true) {
return currentSheet.getRow(rowNum);
} else {
return wrCurrentSheet.getRow(rowNum);
}
}
/**
* 取得一列的cell
*
* @param rowNum 行号
* @return 一列的cell
*/
public Cell[] getColCells(int colNum) {
if (readOnlyWBook == true) {
return currentSheet.getColumn(colNum);
} else {
return wrCurrentSheet.getColumn(colNum);
}
}
/**
* 读取Excel数据
*
* @param pBegin 从第几行开始读数据<br>
* <b>注意下标索引从0开始的哦!
* @return 以List<BaseDTO>形式返回数据
* @throws BiffException
* @throws IOException
*/
public List read(int pBegin) throws BiffException, IOException {
List list = new ArrayList();
Workbook workbook = Workbook.getWorkbook(getIs());
Sheet sheet = workbook.getSheet(0);
int rows = sheet.getRows();
for (int i = pBegin; i < rows; i++) {
Dto rowDto = new BaseDto();
Cell[] cells = sheet.getRow(i);
for (int j = 0; j < cells.length; j++) {
String key = getMetaData().trim().split(",")[j];
if (G4Utils.isNotEmpty(key)) rowDto.put(key, cells[j].getContents());
}
list.add(rowDto);
}
return list;
}
/**
* 取得列数
*
* @return 列数
*/
public int getCols() {
if (readOnlyWBook == true) {
return currentSheet.getColumns();
} else {
return wrCurrentSheet.getColumns();
}
}
public int getRowCount() {
if (reductionSelection == null) {
return excelSheet.getRows();
} else {
return reductionSelection.getRowIndexEnd() - reductionSelection.getRowIndexStart() + 1;
}
}
public Object[] next() {
Cell[] c = sheet.getRow(this.currentRowNo);
Map<String, String> data = new HashMap<String, String>();
// List<String> list = new ArrayList<String>();
for (int i = 0; i < this.columnNum; i++) {
String temp = "";
try {
temp = c[i].getContents().toString();
} catch (ArrayIndexOutOfBoundsException ex) {
temp = "";
}
// if(temp != null&& !temp.equals(""))
// list.add(temp);
data.put(this.columnnName[i], temp);
}
Object object[] = new Object[1];
object[0] = data;
this.currentRowNo++;
return object;
}
/**
* 得到需要爬取的百度百科词条
*
* @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;
}
private void initialise() {
Workbook workbook = WorkbookSingleton.getWorkbook(workbookFileName);
Sheet currentSheet = workbook.getSheet(ColumnIndexes.FAILURECLASS__SHEETNO);
Cell[] row;
for (int i = 1; i < currentSheet.getRows(); i++) {
row = currentSheet.getRow(i);
if (row.length > 0) {
createFailure(
Integer.parseInt(row[ColumnIndexes.FAILURECLASS_FAILURECLASS_COLNO].getContents()),
row[ColumnIndexes.FAILURECLASS_DESCRIPTION_COLNO].getContents());
}
}
}
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;
}
/** @param args */
public static void main(String[] args) {
// TODO Auto-generated method stub
try {
Workbook book = Workbook.getWorkbook(new File("测试.xls"));
Sheet sheet = book.getSheet(0);
int x = sheet.getRows();
int y = sheet.getColumns();
System.out.println("表格的列数为：" + x);
System.out.println("表格的行数为：" + y);
} catch (BiffException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
