Am trying to insert am excel table into oracle database and when i try i get issuesmy table to the database doesnt get formatted correct
public class excelDatabaseFile {
// String for sql method
static String s = "INSERT INTO myTableValues VALUES(?,?,?,?,?,?,?,?)";
// excel
private static Workbook workbook;
//sheet inside excel
private static org.apache.poi.ss.usermodel.Sheet sheet;
// formating hte data(String,integer so on
private static DataFormatter dataFormatter;
//For performance reasons, this class keeps a cache of all previously calculated
// intermediate cell values.
private static FormulaEvaluator evaluator;
public static void main(String[] args) throws Exception{
// loading/registering drivers
Class.forName("Driver");
//getting connection
Connection con = DriverManager.getConnection(
"url","user","password");
try {
// file location
File file = new File(
"C:\\Users\\C819794\\Documents\\myFile\\myExcelTable.xlsx");
// reading file data
FileInputStream fis = new FileInputStream(file);
// creating a statement that provides a methods to execute queries with the //
//database
PreparedStatement preparedStatement = con.prepareStatement(s);
//creating a workbook for decting excel
workbook = WorkbookFactory.create(fis);
// getting the sheet of excel (page one)
sheet = workbook.getSheetAt(0);
//FORMATING THE DATA IN EXCEL TO STRING
dataFormatter = new DataFormatter();
// It is intended for compilation of algebraic expressions involving functions.
evaluator = workbook.getCreationHelper().createFormulaEvaluator();
// array of keys for hashmap-my collection to store the data
ArrayList<HashMap<String, String>> list = new ArrayList<HashMap<String, String>>();
//looping: first block is less than sheet last row get next row
for (int i = 1; i <= sheet.getLastRowNum(); i++)
{
// row is first sheet get first row
Row row = sheet.getRow(i);
//if row is not = to unknown/nothing
if (row != null) {
// create a hashmap to store data
HashMap<String, String> map = new HashMap<String, String>();
// x = first cell in excel sheet; if first cell is less and equil to last row get next row
for (int x = row.getFirstCellNum(); x <= row.getLastCellNum(); x++)
{
// Cell is called v and = to row get the cells
Cell v = row.getCell(x);
// myValue = converting what inside the cells to a string
String myValue = dataFormatter.formatCellValue(v);
// first row = to first sheet get the rows
Row rowfirst = sheet.getRow(0);
//Cell called headcell = first row of sheet get whats in side the cell
Cell headcell = rowfirst.getCell(x);
// formatting the map to put the string cell in a storage
map.put(dataFormatter.formatCellValue(headcell), myValue);
// extends the Statement This object can then be used to efficiently execute this statement //multiple //times.
preparedStatement.setString(1, myValue);
preparedStatement.setString(2, myValue);
preparedStatement.setString(3, myValue);
preparedStatement.setString(4, myValue);
preparedStatement.setString(5, myValue);
preparedStatement.setString(6, myValue);
preparedStatement.setString(7, myValue);
preparedStatement.setString(8, myValue);
preparedStatement.executeQuery();
}
//adding map to list
list.add(map);
}
}
// closing file input stream
fis.close();
// closing connection
con.close();
}
catch(Exception e)
{
System.out.println(e);
//exits current program by terminating running Java virtual machine.
System.exit(1);
}
}
}
// [1]: https://i.stack.imgur.com/MZJL3.png
0 Answer(s)