home
***
CD-ROM
|
disk
|
FTP
|
other
***
search
/
Cricao de Sites - 650 Layouts Prontos
/
WebMasters.iso
/
Servidores
/
bb98.exe
/
SOexcel.java
< prev
next >
Wrap
Text File
|
2002-11-09
|
8KB
|
281 lines
//
// The contents of this file are subject to the BadBlue End User License
// Agreement (the "EULA"); you may not use this file except in
// compliance with the EULA. You may obtain a copy of the EULA at
// http://badblue.com/down.htm .
//
// Software distributed under the EULA is distributed on an "AS IS" basis,
// WITHOUT WARRANTY OF ANY KIND, either express or implied. See the EULA
// for the specific language governing rights and limitations under the
// EULA.
//
// The Initial Developer of this code under the EULA is Working Resources,
// Inc., Atlanta, GA UNITED STATES. All Rights Reserved.
//
//
package ShareOffice;
//
import java.net.*;
import java.io.*;
import ShareOffice.HTTPGet;
import ShareOffice.SOinit;
//
public class SOexcel extends SOinit {
//
public SOexcel() {
}
// Retrieve Excel data over specified range and fill in
// array with values.
// Inputs:
// sAddr: address of BadBlue server (e.g., "127.0.0.1:8080")
// sPath: path of shared file in EXT.INI file (e.g., "path3")
// sFile: name of Excel file to examine (e.g., "invoice.xls")
// nSheet: sheet number (e.g., 1)
// sCellStart: starting cell of area to retrieve (e.g., "A1")
// sCellEnd: ending cell of area to retrieve (e.g., "G99")
// sUser: (optional) user-name to get access to file
// sPassword: (optional) password to get access to file
// Outputs:
// sData[][]: two-dimensional array returned with data
// (if error occurs, array will be 1x1 and contain an
// error message)
//
public String[][] GetExcelData(
String sAddr,
String sPath,
String sFile,
int nSheet,
String sCellStart,
String sCellEnd,
String sUser,
String sPassword
) {
String[][] aasSheet;
String sError = "";
try {
// General setup.
//
int nX, nY;
int nDX = 25;
int nDY = 50;
int[] iaStart = Cell2RowCol(sCellStart);
int[] iaEnd = Cell2RowCol(sCellEnd);
int i, j, nCursor, nCursor2, nTemp;
String sTemp, sVal;
aasSheet = new String[iaEnd[1]-iaStart[1]+nDY][iaEnd[0]-iaStart[0]+nDX];
// Debugging info.
//
// System.out.println("DY: "+Integer.toString(iaEnd[1]-iaStart[1]+1) + " " +
// "DX: "+Integer.toString(iaEnd[0]-iaStart[0]+1) + "\r\n");
// System.out.println("iaS" + Integer.toString(iaStart[0]) + " " + Integer.toString(iaStart[1]) + "\r\n");
// System.out.println("iaE" + Integer.toString(iaEnd[0]) + " " + Integer.toString(iaEnd[1]) + "\r\n");
// Loop through by chunks until done.
//
for (nY = iaStart[1]; nY <= iaEnd[1]; nY += nDY) {
for (nX = iaStart[0]; nX <= iaEnd[0]; nX += nDX) {
// Construct the URL and read it.
//
String sURL =
"http://"+sAddr+"/ext.dll?MfcISAPICommand=LoadPage&"+
"page=xls.htx&a0=/get/"+sPath+"/"+URLEncoder.encode(sFile, "UTF-8")+
"&a1="+Integer.toString(nSheet)+"&a2=_&"+
"a3="+Integer.toString(nX)+"&a4="+Integer.toString(nY)+
"&a5="+Integer.toString(nDX)+"&a6="+Integer.toString(nDY)+
"&a7=2&a8=100%25";
HTTPGet h = new HTTPGet();
String sPage;
if (sUser.length() > 0) {
sPage = h.Read(sURL, sUser, sPassword);
} else {
sPage = h.Read(sURL);
}
// System.out.println("---\r\n"+sPage+"\r\n---\r\n");
// Rip through multiple rows of data...
//
for (i = 0; i < nDY && (nY + i) <= iaEnd[1]; i++) {
for (j = 0; j < nDX && (nX + j) <= iaEnd[0]; j++) {
//
sTemp = "<TD class=fXL";
if ((nCursor = sPage.indexOf(sTemp)) < 0) {
sError = "Invalid template file (2)";
break;
}
nCursor += sTemp.length();
if ((nCursor = sPage.indexOf(">", nCursor)) < 0) {
sError = "Invalid template file (3)";
break;
}
nCursor++;
//
if (nCursor + 255 > (nCursor2 = sPage.length())) {
nCursor2--;
} else {
nCursor2 = nCursor + 255;
}
sVal = sPage.substring(nCursor, nCursor2);
if ((nCursor2 = sVal.indexOf("<")) >= 0) {
sVal = sVal.substring(0, nCursor2);
}
//
sPage = sPage.substring(nCursor + nCursor2);
nTemp = nY + i;
aasSheet[nTemp][nX + j] = sVal;
//
}
if (sError.length() > 0) {
break;
}
sTemp = "</TR";
if ((nCursor = sPage.indexOf(sTemp)) < 0) {
sError = "Invalid template file (6)";
break;
}
sPage = sPage.substring(nCursor + sTemp.length());
}
if (sError.length() > 0) {
break;
}
}
if (sError.length() > 0) {
break;
}
}
if (sError.length() > 0) {
aasSheet = new String[1][1];
aasSheet[0][0] = "Error: " + sError;
}
return (aasSheet);
// ...end SP.
//
} catch (StringIndexOutOfBoundsException e) {
aasSheet = new String[1][1];
aasSheet[0][0] = "Error: string out of bounds: " + e.getMessage();
return (aasSheet);
// ...end SP.
//
} catch (Exception e) {
aasSheet = new String[1][1];
aasSheet[0][0] = "Error: no data available: " + e.getMessage();
return (aasSheet);
}
}
//
// Update a cell in an Excel shared workbook
// Inputs:
// sAddr: address of BadBlue server (e.g., "127.0.0.1:8080")
// sPath: path of shared file in EXT.INI file (e.g., "path3")
// sFile: name of Excel file to examine (e.g., "invoice.xls")
// nSheet: sheet number (e.g., 1)
// sUpdateCell: cell to update (e.g., "A1")
// sUpdateValue: new value for update cell
// sUpdateType: data type of cell being updated (default is "S")
// I2 = short-int I4 = long int R4 = real R8 = float
// C = currency D = date B = boolean E = empty
// S = string
// sUser: (optional) user-name to get access to file
// sPassword: (optional) password to get access to file
// Outputs:
// errmsg: empty if no error occurred, otherwise error message
//
public String UpdateExcelData(
String sAddr,
String sPath,
String sFile,
int nSheet,
String sUpdateCell,
String sUpdateValue,
String sUpdateType,
String sUser,
String sPassword
) {
String sError = "";
try {
// Set defaults.
//
if (sUpdateType.length() == 0) {
sUpdateType = "s";
}
int nX = 0; int nY = 0;
int nDX = 9; int nDY = 9;
// Construct the URL and read it.
//
String sURL =
"http://"+sAddr+"/ext.dll?MfcISAPICommand=LoadPage&"+
"page=xls.htx&a0=/get/"+sPath+"/"+URLEncoder.encode(sFile, "UTF-8")+
"&a1="+Integer.toString(nSheet)+"&a2=Update&"+
"a3="+Integer.toString(nX)+"&a4="+Integer.toString(nY)+
"&a5="+Integer.toString(nDX)+"&a6="+Integer.toString(nDY)+
"&a7=2&a8=100%25&a10="+sUpdateCell+
"&a9="+URLEncoder.encode(sUpdateValue, "UTF-8")+"/"+sUpdateType;
HTTPGet h = new HTTPGet();
String sPage;
// System.out.println(sURL+"\r\n.........\r\n\r\n");
if (sUser.length() > 0) {
sPage = h.Read(sURL, sUser, sPassword);
} else {
sPage = h.Read(sURL);
}
// System.out.println(sPage+"\r\n.........\r\n\r\n");
return (sError);
// ...end SP.
//
} catch (StringIndexOutOfBoundsException e) {
sError = "Error: string out of bounds: " + e.getMessage();
return (sError);
// ...end SP.
//
} catch (Exception e) {
sError = "Error: no data available: " + e.getMessage();
return (sError);
}
}
// Convert Excel coordinate (e.g., "C6") into numeric X,Y (e.g., 2,5)
// Inputs:
// sCell: cell desigation (e.g., "C6")
// Outputs:
// int[0]: X-coordinate (column, e.g., 2) using zero-based counting
// int[1]: Y-coordinate (row, e.g., 5) using zero-based counting
//
public int[] Cell2RowCol(String sCell) {
int iCoords[] = new int[2];
int nX = 0;
int nY = 0;
sCell.toUpperCase();
char c = sCell.charAt(0);
nX = (int) c - 65;
sCell = sCell.substring(1);
c = sCell.charAt(0);
if ((int) c > 64) {
nX *= 26;
nX += ((int) c - 65);
sCell = sCell.substring(1);
}
nY = Integer.parseInt(sCell) - 1;
iCoords[0] = nX;
iCoords[1] = nY;
return (iCoords);
}
// Private members.
//
}
// <EOF>
//