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();
}
}
/**
* 取得列数
*
* @return 列数
*/
public int getCols() {
if (readOnlyWBook == true) {
return currentSheet.getColumns();
} else {
return wrCurrentSheet.getColumns();
}
}
/**
* @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;
}
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();
}
}
public int getColumnCount() {
if (reductionSelection == null) {
return excelSheet.getColumns() + 1;
} else {
return reductionSelection.getColumnIndexEnd()
- reductionSelection.getColumnIndexStart()
+ 1
+ 1;
}
}
/**
* 获取第一个第一行单元格内容非空的列号，因为第一行为参数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;
}
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 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();
}
}
/**
* 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 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;
}
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) {
}
}
}
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 String readFile() throws IOException {
File inputWorkbook = new File(archivo);
Workbook w;
try {
String msg = "";
w = Workbook.getWorkbook(inputWorkbook);
// Get the first sheet
Sheet sheet = w.getSheet(0);
// int defaultRows = sheet.getRows();
// Si la cantidad de columnas es 4
if (sheet.getColumns() >= 4 && sheet.getRows() > 1) {
// Valido que las cabeceras tengan el formato correcto
if (!sheet.getCell(0, 0).getContents().equals("ORIGEN")
|| !sheet.getCell(1, 0).getContents().equals("DESTINO")
|| !sheet.getCell(2, 0).getContents().equals("PRODUCTO")
|| !sheet.getCell(3, 0).getContents().equals("PIEZAS")) {
msg = Msg.translate(getCtx(), "Column Names");
return msg;
}
// El vector que almacena la estructura de traspasos
Vector<Movimiento> movimientos = new Vector<Movimiento>();
Iterator<Movimiento> iterator_movimientos = null;
Movimiento movimiento_temporal = null, movimiento_nuevo = null;
for (int i = 1;
i < sheet.getRows() && getStore(sheet.getCell(0, i).getContents()) != null;
i++) {
// Capturo el origen
MWarehouse origen = getStore(sheet.getCell(0, i).getContents());
MWarehouse destino = getStore(sheet.getCell(1, i).getContents());
String isStore = "N";
// Aca debo bucar los values de los m_warehouse que son cd
String sql =
"SELECT XX_IsStore FROM M_Warehouse WHERE M_Warehouse_ID in ( "
+ origen.getM_Warehouse_ID()
+ ","
+ destino.getM_Warehouse_ID()
+ ")";
PreparedStatement pstmt = null;
ResultSet rs = null;
try {
pstmt = DB.prepareStatement(sql, null);
rs = pstmt.executeQuery();
while (rs.next()) {
if (rs.getString(1).equals("Y")) isStore = rs.getString(1);
}
} catch (Exception a) {
log.log(Level.SEVERE, sql, a);
} finally {
DB.closeResultSet(rs);
DB.closeStatement(pstmt);
}
if (origen == null || isStore.equals("Y"))
return Msg.translate(getCtx(), "Cell A Error")
+ (i + 1)
+ " "
+ sheet.getCell(0, i).getContents();
// Capturo el destino
if (destino == null || isStore.equals("Y"))
return Msg.translate(getCtx(), "Cell B Error")
+ (i + 1)
+ " "
+ sheet.getCell(1, i).getContents();
// Capturo el producto
String p_id = sheet.getCell(2, i).getContents();
MProduct producto = getProduct(p_id);
if (producto == null) {
return Msg.translate(getCtx(), "Cell C Error")
+ (i + 1)
+ " "
+ sheet.getCell(2, i).getContents();
}
// Capturo las piezas
Double piezas = null;
try {
if (sheet.getCell(3, i) != null)
piezas = Double.parseDouble(sheet.getCell(3, i).getContents());
} catch (NumberFormatException e) {
return Msg.translate(getCtx(), "Cell D Error")
+ (i + 1)
+ " "
+ sheet.getCell(3, i).getContents();
}
if (piezas == null) {
return Msg.translate(getCtx(), "Cell D Error")
+ (i + 1)
+ " "
+ sheet.getCell(3, i).getContents();
}
// Agregar cada fila al vector de traspasos
iterator_movimientos = movimientos.iterator();
boolean encontrado = false;
while (iterator_movimientos.hasNext()) {
movimiento_temporal = iterator_movimientos.next();
// Si un traspaso anterior va a la misma tienda, departamento, etc
if (movimiento_temporal.origen == origen.get_ID()) {
if (movimiento_temporal.destino == destino.get_ID()) {
if (movimiento_temporal.departamento == producto.getXX_VMR_Department_ID()) {
// Entonces agregar algo al objeto traspaso
movimiento_temporal.agregarCantidades(producto.get_ID(), piezas);
encontrado = true;
break;
}
}
}
}
// Si no se encontró en el vector de traspasos agregarlo
if (!encontrado) {
movimiento_nuevo =
new Movimiento(
origen.get_ID(), destino.get_ID(), producto.getXX_VMR_Department_ID());
movimiento_nuevo.agregarCantidades(producto.get_ID(), piezas);
movimientos.add(movimiento_nuevo);
}
}
// Una vez leido todo el archivo se procede a crear los traspasos
// System.out.println(traspasos);
return procesarMovimientos(movimientos).toString();
} else {
return Msg.translate(getCtx(), "4 Columns");
}
} catch (BiffException e) {
log.log(Level.SEVERE, e.getMessage());
}
return "";
}
@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();
}
}
/**
* Processes requests for both HTTP <code>GET</code> and <code>POST</code> methods.
*
* @param request servlet request
* @param response servlet response
* @throws ServletException if a servlet-specific error occurs
* @throws IOException if an I/O error occurs
*/
protected void processRequest(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException, BiffException {
response.setContentType("text/html;charset=UTF-8");
PrintWriter out = response.getWriter();
HttpSession sesion = ((HttpServletRequest) request).getSession();
RequestDispatcher dispatcher = null;
// variables
String action = "",
user = "",
password = "",
file = "",
nombres = "",
apellidos = "",
estado = "";
String idperfil_str = "", idusuario_str = "", email = "", telefono = "", direccion = "";
int idperfil = 0, idusuario = 0, modificaciones = 0;
// interfaces y objetos
// usuarioInterface U = new usuarioService();
User Us = new User();
userHelper userhelp = new userHelper();
try {
action = request.getParameter("action");
} catch (Exception e) {
action = "";
}
if (action.equals("loginAutenticar")) {
user = request.getParameter("user");
password = request.getParameter("passw");
try {
Us = userhelp.existeUser(user, password);
} catch (Exception e) {
Us = null;
}
try {
if (Us != null) {
// pe= userhelp.getPerfil(Us.getIdPerfil());
sesion.setAttribute("IdUsuarioSesion", Us.getId());
sesion.setAttribute("nickUsuarioSesion", Us.getUsername());
sesion.setAttribute("nombreUsuarioSesion", Us.getForenames());
sesion.setAttribute("apellidosUsuarioSesion", Us.getSurnames());
// #################################################################
// Actualiza ultimo acceso que tiene el usuario al sistema
// modificaciones= userhelp.editUser(Us, Us.getId());
// #################################################################
dispatcher = request.getRequestDispatcher("client.jsp");
dispatcher.forward(request, response);
} else {
// Guargar log en Bitácora
dispatcher =
request.getRequestDispatcher(
"index.jsp?resp=Usuario y/o " + "contraseña incorrectos");
dispatcher.forward(request, response);
}
// dispatcher.forward(request, response);
} catch (Exception e) {
}
} else if (action.equals("saliraplicacion")) {
try {
sesion.setAttribute("IdUsuarioSesion", null);
sesion.setAttribute("nickUsuarioSesion", null);
sesion.setAttribute("nombreUsuarioSesion", null);
sesion.setAttribute("apellidosUsuarioSesion", null);
sesion.setAttribute("IdPerfilSesion", null);
sesion.invalidate();
response.sendRedirect(response.encodeRedirectURL("index.jsp"));
} catch (Exception e) {
out.print("error");
}
} else if (action.equalsIgnoreCase("cargarCliente")) {
try {
file = request.getParameter("fileField");
System.out.println("este es:" + file);
Workbook workbook =
Workbook.getWorkbook(new File(file)); // Pasamos el excel que vamos a leer
Sheet sheet = workbook.getSheet(0); // Seleccionamos la hoja que vamos a leer
String nombre;
for (int fila = 1; fila < sheet.getRows(); fila++) { // recorremos las filas
for (int columna = 0;
columna < sheet.getColumns();
columna++) { // recorremos las columnas
nombre = sheet.getCell(columna, fila).getContents(); // setear la celda leida a nombre
System.out.print(nombre + ""); // imprimir nombre
}
}
dispatcher = request.getRequestDispatcher("client.jsp");
dispatcher.forward(request, response);
// dispatcher.forward(request, response);
} catch (Exception e) {
}
} else if (action.equalsIgnoreCase("getusuario")) {
Us = new User();
idusuario_str = request.getParameter("iduser");
idusuario = idusuario_str.equals("") ? 0 : Integer.parseInt(idusuario_str);
Us = userhelp.getUserByID(idusuario);
String resp = "", separador = "%";
// resp =
// Us.getUsuario()+separador+Us.getNombres()+separador+Us.getApellidos()+separador+Us.getEmail()+separador+
//
// Us.getIdPerfil()+separador+idusuario+separador+Us.getContrasena()+separador+Us.getTelefono()+separador+Us.getDireccion();
// Us.setFechaCreacion(diaHoraActual);
// Us.setFechaModificacion(diaHoraActual);
out.write(resp);
} else if (action.equalsIgnoreCase("getDataUser")) {
Us = new User();
idusuario_str = request.getParameter("iduser");
idusuario = idusuario_str.equals("") ? 0 : Integer.parseInt(idusuario_str);
Us = userhelp.getUserByID(idusuario);
String resp = "", separador = "%";
// resp =
// Us.getUsuario()+separador+Us.getNombres()+separador+Us.getApellidos()+separador+Us.getEmail()+separador+
//
// Us.getIdPerfil()+separador+idusuario+separador+Us.getContrasena()+separador+Us.getTelefono()+separador+Us.getDireccion();
// Us.setFechaCreacion(diaHoraActual);
// Us.setFechaModificacion(diaHoraActual);
out.write(resp);
}
}
public void write2Excel(List<JewelryEntity> list, String excelPath) throws Exception {
if (list == null && list.isEmpty()) {
this.log.warn("Product list is empty !");
return;
}
// 1. copy excel head from template
File templateFile = new ClassPathResource("template/JewelryTemplate.xls").getFile();
String templateFilePath = templateFile.getAbsolutePath();
Workbook sourceBook = Workbook.getWorkbook(new File(templateFilePath));
WritableWorkbook targetBoook = Workbook.createWorkbook(new File(excelPath), sourceBook);
WritableSheet sheet1 = targetBoook.getSheet(0);
sheet1.getSettings().setHorizontalFreeze(1); // 设置列冻结
sheet1.getSettings().setVerticalFreeze(3); // 设置行冻结前2行
// 2. get columns name
Sheet st = sourceBook.getSheet(0);
int totalColumns = st.getColumns(); // for version it's 220
String[] columnNames = new String[totalColumns];
for (int i = 0; i < totalColumns; i++) {
columnNames[i] = st.getCell(i, 2).getContents();
}
// 3. write data to excel
for (int row = 0; row < list.size(); row++) {
JewelryEntity ent = list.get(row);
this.log.info(row + "------------------------");
for (int col = 0; col < totalColumns; col++) {
Object columnValue = this.getValueByJaveReflect(ent, columnNames[col]);
if (columnValue == null) {
if ("generic_keywords".equals(columnNames[col])) {
String keywords =
ent.getGenericKeywords1()
+ " "
+ ent.getGenericKeywords2()
+ " "
+ ent.getGenericKeywords3()
+ " "
+ ent.getGenericKeywords4()
+ " "
+ ent.getGenericKeywords5()
+ " "
+ ent.getNewKeywords();
int lenght = keywords.length() > 1000 ? 999 : keywords.length() - 1;
sheet1.addCell(new Label(col, row + 3, keywords.substring(0, lenght)));
} else {
sheet1.addCell(new Label(col, row + 3, null));
}
} else {
if (ParentChild.parent.toString().equals(ent.getParentChild())) {
// some column for parent is no needed
if (this.omitColumnNames4Parent.contains(columnNames[col])) {
sheet1.addCell(new Label(col, row + 3, null));
} else {
sheet1.addCell(new Label(col, row + 3, columnValue.toString()));
}
} else {
sheet1.addCell(new Label(col, row + 3, columnValue.toString()));
}
}
}
}
targetBoook.write();
targetBoook.close();
sourceBook.close();
}
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("学术论文写入结束");
}
