Hi everyone...
I'm trying to change and modify a Excel file which is received as Blob variable from database.
I wrote a PL/SQL Procedure which get the Excel file and convert it to blob; after that send it as the parameter of a Java source called TestPrintExcel
CREATE OR REPLACE DIRECTORY VMI_TEST_DIR AS '/afc/opt/dba/test_utl_dir';
GRANT READ, WRITE ON DIRECTORY SYS.VMI_TEST_DIR TO SYSTEM;
declare
bfi bfile := bfilename('VMI_TEST_DIR','TestFzglist.xls');
bin blob;
cout clob;
begin
dbms_lob.open(bfi);
dbms_lob.createtemporary(bin, true);
dbms_lob.loadfromfile(bin, bfi, dbms_lob.getlength(bfi));
dbms_lob.close(bfi);
dbms_lob.createtemporary(cout, true);
dbms_java.set_output(100000);
TestPrintExcel(bin);
dbms_output.put_line('done!');
end;
With the following Java procedure I can read the content of the Excel file and write it to database perfectly.
But the question is,
how can i save the excel file with the same way, if i would change or insert any cell? I mean with the same way that to save as blob stream.
Because i get permission error when i use another methods like FileOutputStream, FileInputStream etc.
That's way i want to resolve this problem with the blob-stream method.
Can anybody tell me how i can do that or what a method i have to use?
Thanks.
create or replace and compile java source named xltest as
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.util.CellReference;
import org.apache.poi.ss.usermodel.DataFormatter;
import org.apache.poi.ss.usermodel.FormulaEvaluator;
import java.util.*;
import java.sql.*;
import oracle.sql.*;
import oracle.jdbc.driver.*;
public class XLTEST {
public static void printx( oracle.sql.BLOB xblin ) throws SQLException {
Connection Conn = null;
try {
Conn = DriverManager.getConnection( "jdbc:default:connection:" );
Workbook wb = WorkbookFactory.create( xblin.getBinaryStream() ); // "/afc/opt/dba/test_utl_dir/TstFzglist.xls"
for ( int sheetNum = 0; sheetNum < wb.getNumberOfSheets(); sheetNum++ ) { //NumberOfSheets
String TableName = new String();
switch( sheetNum )
{
case 0:
TableName = "freeimportxls_s1"; break;
case 1:
TableName = "FreeImportXLS_S2"; break;
case 2:
TableName = "FreeImportXLS_S3"; break;
default:
break;
}
Sheet sheet1 = wb.getSheetAt(sheetNum);
java.util.Iterator<Row> ri = sheet1.rowIterator();
while ( ri.hasNext() ) {
Row row = ri.next();
java.util.Iterator<Cell> ci = row.cellIterator();
while ( ci.hasNext() ) {
Cell cell = ci.next();
CellReference cellRef = new CellReference( row.getRowNum(), cell.getColumnIndex() );
switch( cell.getCellType() ) {
case Cell.CELL_TYPE_STRING:
Values += "'" + cell.getRichStringCellValue().getString() + "'";
break;
case Cell.CELL_TYPE_NUMERIC:
Values += "'" + cell.getNumericCellValue() + "'";
break;
case Cell.CELL_TYPE_BOOLEAN:
Values += "'" + cell.getBooleanCellValue() + "'";
break;
case Cell.CELL_TYPE_FORMULA:
Values += "'" + cell.getCellFormula() + "'";
break;
default:
Values += "''";
}
int Counter1 = cell.getColumnIndex() + 1;
int Counter2 = row.getLastCellNum();
if ( Counter1==Counter2 ) {
Values += ")";
else {
Values += "," ;
}
} System.out.println ( Values );
if ( ( sheetNum >= 0 ) && ( sheetNum <=2 ) ) {
String sqlUpdate = "INSERT INTO " + TableName + " VALUES " + Values;
PreparedStatement pstmt = Conn.prepareStatement( sqlUpdate );
pstmt.executeUpdate();
pstmt.close();
}
}
} Conn.close();
} catch ( java.sql.SQLException e ) {
System.err.println( e.getMessage() );
e.printStackTrace( System.err );
Conn.close();}
}
};