如何将excel数据上传到mysql表中

I am developing web application with database in that application user can upload .csv file to database it works some time it has problem with data upload. so i need to know how can i upload excel file into Mysql table below code

我正在开发一个带有数据库的web应用程序，在这个应用程序中，用户可以将。csv文件上传到数据库中。所以我需要知道如何上传excel文件到Mysql表下面的代码

 conv = new Connectivity();
                               con = conv.setConnection();
                               st = con.createStatement();
                               query = "LOAD DATA LOCAL INFILE \"" + filename2 + "\" INTO TABLE " + tablename + " FIELDS TERMINATED BY ',' IGNORE 1 LINES";

                               st.executeUpdate(query);
                               PrintWriter obj1 = response.getWriter();
                               obj1.println("Row (1) inserted");

                               HttpSession sval = request.getSession();
                               sval.setAttribute("UpdatedCorret", "yes");

2 个解决方案

#1

This is demo code for using Poi API to parse excel file...

这是使用Poi API解析excel文件的演示代码……

File source = jFileChooser.getSelectedFile();

InputStream input = new BufferedInputStream(new FileInputStream(
                source.getCanonicalPath()));

POIFSFileSystem fs = new POIFSFileSystem(input);
HSSFWorkbook wb = new HSSFWorkbook(fs);
HSSFSheet sheet = wb.getSheetAt(0);
Iterator<?> rows = sheet.rowIterator();
while (rows.hasNext()) {
    HSSFRow row = (HSSFRow) rows.next();
        Iterator<?> cells = row.cellIterator();
        while (cells.hasNext()) {
            HSSFCell cell = (HSSFCell) cells.next();
            try {
                System.out.print(new BigDecimal(cell
                    .getNumericCellValue()).toPlainString());
            } catch (IllegalStateException ex) {
                System.out.print(cell.getStringCellValue());
            } catch (Exception ex) {
                ex.printStackTrace();
        }
    }
}

Hope it works for you...

希望对你有用……

#2

Try Excel2MySQL. It has a simple user interface for manual imports and it also includes a command line interface for automating uploads and can read any excel file directly. No need to export as a csv file. The Excel sheetname determines the destination MySQL table name. The table can be automatically created or simply appended to. All fields can also be automatically optimized to the proper MySQL type. The program is free to try and I am it's author.

Excel2MySQL试试。它有一个用于手动导入的简单用户界面，还包括一个用于自动上传的命令行界面，可以直接读取任何excel文件。不需要导出为csv文件。Excel sheetname确定目标MySQL表名。可以自动创建该表，也可以简单地添加到该表中。所有字段也可以自动优化为适当的MySQL类型。这个节目是免费的，我是它的作者。

-h, --help, display help message and exit
-f file, Excel file to convert (include full path) (REQUIRED)
-s sheet, Excel sheet to convert (omit to convert all)
-z host, mysql server hostname or IP address (omit will default to localhost)
-d database, mysql database name (REQUIRED)
-u user, mysql user name (REQUIRED)
-p password, mysql password
-x port, mysql port number (omit will default to 3306)
-r, replace existing table (omit to append to existing table)
-n, first row is not a header (omit to indicate first row contains header)
-a, allow spaces in table & field names (omit to change spaces to underscores)
-m, use myisam database storage engine (omit to use innodb)
-k, keep blank rows & columns (omit to remove)
-c, Unmerge merged cells
-v, define all fields as varchar type on tables (omit to optimize field types)

Here is an example command line to upload an excel file with sheet name of "address":

下面是一个示例命令行，用于上传一个名为“address”的excel文件:

excel2mysqlCLI.exe -f "c:\my excel.xls" -d mydb -u myid -p mypass -s address

#1