Импорт данных excel в mysql
Я пытаюсь импортировать данные excel в базу данных myql. но я получаю такую ошибку:"
org.apache.poi.poifs.filesystem.OfficeXmlFileException: The supplied data appears to be in the Office 2007+ XML. You are calling the part of POI that deals with OLE2 Office Documents. You need to call a different part of POI to process this data (eg XSSF instead of HSSF)"
Что я уже пробовал:
<html>
<head>
</head>
<body>
<form action ="Excel.jsp" method="post" >
<input type="file" name= file size="20" id="file" />
<input type="submit" value="Submit" />
</form>
</body>
</html>
Это мой код jsp:
<%@ page import ="java.util.Date" %>
<%@ page import ="java.io.*" %>
<%@ page import ="java.io.FileNotFoundException" %>
<%@ page import ="java.io.IOException" %>
<%@ page import ="java.util.Iterator" %>
<%@ page import ="java.util.ArrayList" %>
<%@ page import ="java.sql.*" %>
<%@ page import ="java.util.Iterator" %>
<%@ page import ="org.apache.poi.ss.usermodel.Cell" %>
<%@ page import ="org.apache.poi.ss.usermodel.Row" %>
<%@ page import ="org.apache.poi.xssf.usermodel.XSSFSheet" %>
<%@ page import ="org.apache.poi.xssf.usermodel.XSSFWorkbook" %>
<%@ page import ="org.apache.poi.poifs.filesystem.POIFSFileSystem" %>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
<title>Insert title here</title>
</head>
<body>
<%!
Connection con;
PreparedStatement ps=null;
Statement stmt= null;
public static ArrayList readExcelFile(String fileName)
{
/** --Define a ArrayList
--Holds ArrayList Of Cells
*/
ArrayList cellArrayLisstHolder = new ArrayList();
try
{
/** Creating Input Stream**/
//FileInputStream myInput = new FileInputStream(new File("C:/excel/f/testExcel.xlsx"));
FileInputStream file;
file = new FileInputStream(new File("C:/excel/f/testExcel.xls"));
/** Create a POIFSFileSystem object**/
POIFSFileSystem myFileSystem = new POIFSFileSystem(file);
/** Create a workbook using the File System**/
// HSSFWorkbook myWorkBook = new HSSFWorkbook(myFileSystem);
XSSFWorkbook workbook = new XSSFWorkbook(file);
/** Get the first sheet from workbook**/
// HSSFSheet mySheet = myWorkBook.getSheetAt(0);
XSSFSheet sheet = workbook.getSheetAt(0);
/** We now need something to iterate through the cells.**/
//Iterator rowIter = mySheet.rowIterator();
Iterator<Row> rowIterator = sheet.iterator();
/* while(rowIter.hasNext())
{
HSSFRow myRow = (HSSFRow) rowIter.next();
Iterator cellIter = myRow.cellIterator();
ArrayList cellStoreArrayList=new ArrayList();
while(cellIter.hasNext())
{
HSSFCell myCell = (HSSFCell) cellIter.next();
cellStoreArrayList.add(myCell);
}
cellArrayLisstHolder.add(cellStoreArrayList);
} */
while (rowIterator.hasNext())
{
Row row = rowIterator.next();
//For each row, iterate through all the columns
Iterator<Cell> cellIterator = row.cellIterator();
ArrayList cellStoreArrayList=new ArrayList();
while (cellIterator.hasNext())
{
Cell cell = cellIterator.next();
cellStoreArrayList.add(cell);
}
cellArrayLisstHolder.add(cellStoreArrayList);
}
}catch (Exception e)
{
e.printStackTrace();
}
return cellArrayLisstHolder;
}%>
<%
String file = request.getParameter("file");
String fileName=""+file+""; //testExcel.xls Excel File name
//Read an Excel File and Store in a ArrayList
ArrayList dataHolder=readExcelFile(fileName);
//Print the data read
//printCellDataToConsole(dataHolder);
try
{
Class.forName("com.mysql.jdbc.Driver");
con=DriverManager.getConnection("jdbc:mysql://localhost:3306/mytestbd","root","baseauto");
stmt =con.createStatement();
String query="insert into student values(?,?,?)";
ps=con.prepareStatement(query);
int count=0;
ArrayList cellStoreArrayList=null;
//For inserting into database
for(int i=1;i<dataHolder.size();i++)
{
cellStoreArrayList=(ArrayList)dataHolder.get(i);
ps.setString(1,((Cell)cellStoreArrayList.get(0)).toString());
ps.setString(2,((Cell)cellStoreArrayList.get(1)).toString());
ps.setString(3,((Cell)cellStoreArrayList.get(2)).toString());
count= ps.executeUpdate();
//out.print(((HSSFCell)cellStoreArrayList.get(2)).toString() + "\t");
}
//For checking data is inserted or not?
if(count>0)
{
%>
Following deatils from Excel file have been inserted in student table of database
<table>
<tr>
<th>Student's Name</th>
<th>Class</th>
<th>Age</th>
</tr>
<%
for (int j=1;j < dataHolder.size(); j++) {
cellStoreArrayList=(ArrayList)dataHolder.get(j);%>
<tr>
<td><%=((Cell)cellStoreArrayList.get(0)).toString() %></td>
<td><%=((Cell)cellStoreArrayList.get(1)).toString() %></td>
<td><%=((Cell)cellStoreArrayList.get(2)).toString() %></td>
</tr>
<%}
}
else
{%>
<center> Details have not been inserted!!!!!!!!!</center>
<% }
}catch(Exception e)
{}%>
</table>
</body>
</html>