Hello,
I've download poi.apache.org and used the loadjava utility to upload my java code into the Oracle database.
After this I wrote a simple java program to convert xls files:
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.usermodel.DataFormatter;
import org.apache.poi.ss.usermodel.FormulaEvaluator;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileWriter;
import java.io.BufferedWriter;
import java.io.IOException;
import java.io.FileNotFoundException;
import java.util.ArrayList;
/**
* Demonstrate one way to convert an Excel spreadsheet into a CSV file.
This
* class makes the following assumptions;
*
* 1. Where the Excel workbook contains more that one worksheet then a
single
* CSV file will contain the data from all of the worksheets.
* 2. The data matrix contained in the CSV file will be square. This means
that
* the number of elements in each row of the CSV file will match the
number
* of cells in the longest row found in the Excel workbook. Any short
rows
* will be 'padded' with empty elements - an empty elements is
represented in
* the CSV file in this way ,,.
* 3. Empty elements will represent missing cells.
* 4. A row consisting of empty elements will be used to represent an empty
row
* in the Excel workbook.
*
* @author Mark B
* @version 1.00 9th April 2010
*/
public class ToCSV {
private Workbook workbook = null;
private ArrayList<ArrayList> csvData = null;
private int maxRowWidth = 0;
private DataFormatter formatter = null;
private FormulaEvaluator evaluator = null;
/**
* Open an Excel workbook readt for conversion.
*
* @param filename An instance of the String class that encapsulates
the
* path to and name of a valid Excel workbook. Note
that
the
* workbook can be either a binary (.xls) or
SpreadsheetML
* (.xlsx) file.
*
* @throws java.io.FileNotFoundException Thrown if the file cannot be
located.
* @throws java.io.IOException Thrown if a problem occurs in the file
system.
* @throws org.apache.poi.openxml4j.exceptions.InvalidFormatException
Thrown
* if invalid xml is found whilst parsing an input SpreadsheetML
file.
*/
public void openWorkbook(String filename) throws
FileNotFoundException,
IOException,
InvalidFormatException {
File file = null;
FileInputStream fis = null;
try {
file = new File(filename);
fis = new FileInputStream(file);
this.workbook = WorkbookFactory.create(fis);
this.evaluator =
this.workbook.getCreationHelper().createFormulaEvaluator();
this.formatter = new DataFormatter();
}
finally {
if(fis != null) {
fis.close();
}
}
}
/**
* Called to convert the contents of the currently opened workbook
into
* a CSV file.
*/
public void convertToCSV() {
Sheet sheet = null;
Row row = null;
int lastRowNum = 0;
this.csvData = new ArrayList<ArrayList>();
// Discover how many sheets there are in the workbook....
int numSheets = this.workbook.getNumberOfSheets();
// and then iterate through them.
for(int i = 0; i < numSheets; i++) {
// Get a reference to a sheet and check to see if it contains
// any rows.
sheet = this.workbook.getSheetAt(i);
if(sheet.getPhysicalNumberOfRows() > 0) {
// Note down the index number of the bottom-most row and
// then iterate through all of the rows on the sheet
// starting
// from the very first row - number 1 - even if it is
// missing.
// Recover a reference to the row and then call another
// method
// which will strip the data from the cells and build
// lines
// for inclusion in the resylting CSV file.
lastRowNum = sheet.getLastRowNum();
for(int j = 0; j <= lastRowNum; j++) {
row = sheet.getRow(j);
this.rowToCSV(row);
}
}
}
}
/**
* Called to actually save the data recovered from the Excel workbook
* as a CSV file.
*
* @param filename An instance of the String class that encapsulates
the
* path to and name of the CSV file.
* @param separator An instance of the String class that encapsulates
the
* character or character that ought to be used to
delimit
* elements on the lines of the CSV file.
* @throws java.io.FileNotFoundException Thrown if the file cannot be
found.
* @throws java.io.IOException Thrown to indicate and error occurred
in
the
* underylying file system.
*/
public void saveCSVFile(String filename, String separator)
throws
FileNotFoundException,
IOException {
File file = null;
FileWriter fw = null;
BufferedWriter bw = null;
ArrayList<String>line = null;
StringBuffer buffer = null;
String csvLineElement = null;
try {
// Open a writer onto the CSV file.
file = new File(filename);
fw = new FileWriter(file);
bw = new BufferedWriter(fw);
// Step through the elements of the ArrayList that was used to
// hold
// all of the data recovered from the Excel workbooks' sheets,
//
// and cells.
for(int i = 0; i < this.csvData.size(); i++) {
buffer = new StringBuffer();
// Get an element from the ArrayList that contains the
// the workbook. This element will itself be an ArrayList
// containing Strings and each String will hold the data
// recovered
// from a single cell. The for() loop is used to recover
// elements
// from this 'row' ArrayList one at a time and to write
// the
// Strings
// away to a StringBuffer thus assembling a single line
// for
// inclusion
// in the CSV file. If a row was empty or if it was short,
// then
// the ArrayList that contains it's data will also be
// shorter than
// some of the others. Therefore, it is necessary to check
// within
// the for loop to ensure that the ArrayList contains data
// to be
// processed. If it does, then an element will be
// recovered
// and
// appended to the StringBuffer.
line = this.csvData.get(i);
for(int j = 0; j < this.maxRowWidth; j++) {
if(line.size() > j) {
csvLineElement = line.get(j);
if(csvLineElement != null) {
buffer.append(csvLineElement);
}
}
if(j < (this.maxRowWidth - 1)) {
buffer.append(separator);
}
}
// Once the line is built, write it away to the CSV file.
bw.write(buffer.toString().trim());
// Condition the inclusion of new line characters so as to
// avoid an additional, superfluous, new line at the end
// of
// the file.
if(i < (this.csvData.size() - 1)) {
bw.newLine();
}
}
}
finally {
if(bw != null) {
bw.flush();
bw.close();
}
}
}
/**
* Called to convert a row of cells into a line of data that can later
be
* output to the CSV file.
*
* Note that no tests have yet been conducted with blank cells or
those
* containing formulae. Such may require latereations to the way this
code
* works.
*
* @param row An instance of either the HSSFRow or XSSFRo classes that
* encapsulates information about a row of cells recovered
from
* an Excel workbook.
*/
private void rowToCSV(Row row) {
Cell cell = null;
int lastCellNum = 0;
ArrayList<String> csvLine = new ArrayList<String>();
// Check to ensure that a row was recovered from the sheet as it
// is
// possible that one or more rows between other populated rows
// could
// be
// missing - blank. If the row does contain cells then...
if(row != null) {
// Get the index for the right most cell on the row and then
// step along the row from left to right recovering the
// contents
// of each cell, converting that into a formatted String and
// then storing the String into the csvLine ArrayList.
lastCellNum = row.getLastCellNum();
for(int i = 0; i <= lastCellNum; i++) {
cell = row.getCell(i);
if(cell == null) {
csvLine.add("");
}
else {
if(cell.getCellType() != Cell.CELL_TYPE_FORMULA) {
csvLine.add(this.formatter.formatCellValue(cell));
}
else {
csvLine.add(this.formatter.formatCellValue(cell,
this.evaluator));
}
}
}
// Make a note of the index number of the right most cell.
// This
// value
// will later be used to ensure that the matrix of data in the
// CSV file
// is square.
if(lastCellNum > this.maxRowWidth) {
this.maxRowWidth = lastCellNum;
}
}
this.csvData.add(csvLine);
}
/**
* The main() method contains code that demonstrates how to use the
class.
* @param args
*/
public static void main(String[] args) {
try {
ToCSV converter = new ToCSV();
converter.openWorkbook(args[0]);
converter.convertToCSV();
converter.saveCSVFile(args[1], ";");
}
catch(Exception ex) {
System.out.println("Caught an: " + ex.getClass().getName());
System.out.println("Message: " + ex.getMessage());
System.out.println("Stacktrace follows:.....");
ex.printStackTrace(System.out);
}
}
}
After this I wrote a wrapper pl/sql procedure:
CREATE OR REPLACE PROCEDURE ToCSV(agr1 in varchar2, arg2 in varchar2) AS LANGUAGE JAVA NAME 'ToCSV(arg1 java.lang.String arg2 java.lang.String)';
This also works.
The question is:
Can you use BFILE to connect to my java program? In Oracle 10G Express there is no jvm and no native java support.
Thank you.
Kind regards,
Toon Schilder
TS Creations