java导入excel到数据库

最新推荐文章于 2024-03-26 18:11:05 发布

爱若手握流沙

最新推荐文章于 2024-03-26 18:11:05 发布

阅读量3.4k

点赞数 2

分类专栏： java基础

本文链接：https://blog.csdn.net/QMW19910301/article/details/53330895

版权

java基础专栏收录该内容

41 篇文章 0 订阅

订阅专栏

导读：

要想把excel中的数据通过java一次性导入到数据库，就要用java读取并解析excel，要读取并解析excel就要在导入执行时传进excel的存储路径，如下excelPath

    function  import(){
	document.MaterialPriceInfoForm.formAction.value="ImportExcel";
        document.MaterialPriceInfoForm.submit();
       }

       <input type="file" name="excelPath" id="importExcel">
       <input  type="submit"  value="导入" οnclick="import();">

java读取导入的Excel文件并解析的方法有两种：jxl和poi

一， jxl对Excel的读取和解析：

例一：

public class JxlReadExcel {  
    /** 
     * @param args 
     */  
    public static void main(String[] args) {  
        String filepath = "C:\\demo.xls";  
        try {  
            Workbook workbook = Workbook.getWorkbook(new File(filepath));  
            Sheet sheet = workbook.getSheet(0);  
            // j为行数，getCell("列号","行号")  
            for (int i = 1, j = sheet.getRows(); i < j; i++) {  
                Cell c1 = sheet.getCell(0, i);  
                String name = c1.getContents();  
                Cell c2 = sheet.getCell(1, i);  
                String score = c2.getContents();  
                System.out.println(" 姓名:" + name + ",成绩:" + score);  
            }  
        } catch (BiffException e) {  
            e.printStackTrace();  
        } catch (IOException e) {  
            e.printStackTrace();  
        }  
    }  
}

例二：

import java.io.File;
import jxl.*; 
public class Read_excel{
    public static void main(String[] args) {
        int i;
        Cell cell1,cell2,cell3;
        try { 
            Workbook  book= Workbook.getWorkbook(new File("t.xls"));    //t.xls为要读取的excel文件名 
            Sheet sheet=book.getSheet(0);    //获得第一个工作表对象(ecxel中sheet的编号从0开始,0,1,2,3,....)
            cell1=sheet.getCell(0,0);   //获取左上角的单元格
            System.out.println("标题："+cell1.getContents()); 
            i=1;
            while(true){  //获取每一行的单元格
                cell1=sheet.getCell(0,i);//（列，行）
                cell2=sheet.getCell(1,i);
                cell3=sheet.getCell(2,i);
                if("".equals(cell1.getContents())==true){    //如果读取的数据为空
                    break;
                    }
                System.out.println(cell1.getContents()+"\t"+cell2.getContents()+"\t"+cell3.getContents()); 
                i++;
            }
            book.close(); 
        }catch(Exception e){
        
        } 
    }
}

二，poi对Excel的读取和解析

例一：

public class PoiReadExcel {  
    public static void main(String[] args) {  
        try {  
            // HSSFWorkbook workbook=new  
            // HSSFWorkbook(Class.forName("ReadExcel").getResourceAsStream("c:\\demo.xls"));//  
            // 使用流的方式读文件  
            String filepath = "c:\\demo.xls";  
            POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(filepath));  
            HSSFWorkbook workbook = new HSSFWorkbook(fs);  
            HSSFSheet sheet = workbook.getSheetAt(0);  
            for (int i = 1, j = sheet.getLastRowNum(); i <= j; i++) {  
                HSSFCell c_name = sheet.getRow(i).getCell((short) 0);  
                String name = c_name.toString();  
                HSSFCell c_score = sheet.getRow(i).getCell((short) 1);  
                double score = c_score.getNumericCellValue();  
                System.out.println(" 姓名:" + name + ",成绩:" + score);  
            }  
        } catch (Exception e) {  
            e.printStackTrace();  
        }  
    }  
}

例二：

java代码：

public static void main(String[] args) throws Exception {
        InputStream is = new FileInputStream("d:/test.xls");
        HSSFWorkbook hssfWorkbook = new HSSFWorkbook(is);
        HSSFSheet sheetAt = hssfWorkbook.getSheetAt(0);
        
        HSSFRow title = sheetAt.getRow(0);
        
        HSSFCell cf = null;
        HSSFCell c = null;
        for (int rowNum = 1; rowNum <= sheetAt.getLastRowNum(); rowNum++) {
            HSSFRow r = sheetAt.getRow(rowNum);
            
            cf = title.getCell(0); //性别
            c = r.getCell(0);//男
            System.out.println(cf.getStringCellValue() + ":" + c.getStringCellValue());
            c = r.getCell(1);//女
            System.out.println(cf.getStringCellValue() + ":" + c.getStringCellValue());
            
            cf = title.getCell(2); //年龄
            c = r.getCell(2);//年轻
            System.out.println(cf.getStringCellValue() + ":" + c.getStringCellValue());
            c = r.getCell(3);//中年
            System.out.println(cf.getStringCellValue() + ":" + c.getStringCellValue());
            c = r.getCell(4);//老年
            System.out.println(cf.getStringCellValue() + ":" + c.getStringCellValue());
        }
    }

例三：一般遍历使用两种方式

1：得到总的行数和每行的列数，然后循环。

/**
 *1：得到总的行数和每行的列数，然后循环。
 */
package com.golden.test;   
import java.io.File;   
import java.io.FileInputStream;   
import org.apache.poi.hssf.usermodel.HSSFCell;   
import org.apache.poi.hssf.usermodel.HSSFRow;   
import org.apache.poi.hssf.usermodel.HSSFSheet;   
import org.apache.poi.hssf.usermodel.HSSFWorkbook;   
/**  
* @author qimingwei
*/  
public class PoiReadXls2 {   
    public static void main(String[] args) {   
        File f = new File("c:\\a.xls");   
        try {   
            FileInputStream is = new FileInputStream(f);   
            HSSFWorkbook wbs = new HSSFWorkbook(is);   
            HSSFSheet childSheet = wbs.getSheetAt(0);   
            // System.out.println(childSheet.getPhysicalNumberOfRows());   
            System.out.println("有行数" + childSheet.getLastRowNum());   
            for (int j = 0; j < childSheet.getLastRowNum(); j++) {   
                HSSFRow row = childSheet.getRow(j);   
                // System.out.println(row.getPhysicalNumberOfCells());   
                // System.out.println("有列数" + row.getLastCellNum());   
                if (null != row) {   
                    for (int k = 0; k < row.getLastCellNum(); k++) {   

                        HSSFCell cell = row.getCell(k);   
                        if (null != cell) {   
                            switch (cell.getCellType()) {   
                            case HSSFCell.CELL_TYPE_NUMERIC: // 数字   
                                System.out.print(cell.getNumericCellValue()   
                                        + "   ");   
                                break;   
                            case HSSFCell.CELL_TYPE_STRING: // 字符串   
                                System.out.print(cell.getStringCellValue()   
                                        + "   ");   
                                break;   
                            case HSSFCell.CELL_TYPE_BOOLEAN: // Boolean   
                                System.out.println(cell.getBooleanCellValue()   
                                        + "   ");   
                                break;   
                            case HSSFCell.CELL_TYPE_FORMULA: // 公式   
                                System.out.print(cell.getCellFormula() + "   ");   
                                break;   
                            case HSSFCell.CELL_TYPE_BLANK: // 空值   
                                System.out.println(" ");   
                                break;   
                            case HSSFCell.CELL_TYPE_ERROR: // 故障   
                                System.out.println(" ");   
                                break;   
                            default:   
                                System.out.print("未知类型   ");   
                                break;   
                            }   
                        } else {   
                            System.out.print("-   ");   
                        }   
                    }   
                }   
                System.out.println();   
            }   
        } catch (Exception e) {   
            e.printStackTrace();   
        }   
    }   
}

2：使用迭代

package com.golden.test;   
import java.io.File;   
import java.io.FileInputStream;   
import java.io.IOException;   
import java.io.InputStream;   
import java.util.Iterator;   
import org.apache.poi.hssf.usermodel.HSSFCell;   
import org.apache.poi.hssf.usermodel.HSSFRow;   
import org.apache.poi.hssf.usermodel.HSSFSheet;   
import org.apache.poi.hssf.usermodel.HSSFWorkbook;   
import org.apache.poi.poifs.filesystem.POIFSFileSystem;   
/**  
* @author qimingwei  
*/   
public class PoiReadXls {   
        @SuppressWarnings( { "unchecked", "deprecation" })   
        public static void main(String[] args) {   
                File f = new File("c:\\a.xls");   
                try {   
                        InputStream input = new FileInputStream(f);   
                        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();   
                                // System.out.print("行：" + row.getRowNum() + " ");   
                                Iterator cells = row.cellIterator();   
                                while (cells.hasNext()) {   
                                        HSSFCell cell = (HSSFCell) cells.next();   
                                        // System.out.println("列：" + cell.getCellNum());   
                                        switch (cell.getCellType()) {   
                                        case HSSFCell.CELL_TYPE_NUMERIC: // 数字   
                                                System.out.print(cell.getNumericCellValue() + "   ");   
                                                break;   
                                        case HSSFCell.CELL_TYPE_STRING: // 字符串   
                                                System.out.print(cell.getStringCellValue() + "   ");   
                                                break;   
                                        case HSSFCell.CELL_TYPE_BOOLEAN: // Boolean   
                                                System.out.println(cell.getBooleanCellValue() + "   ");   
                                                break;   
                                        case HSSFCell.CELL_TYPE_FORMULA: // 公式   
                                                System.out.print(cell.getCellFormula() + "   ");   
                                                break;   
                                        case HSSFCell.CELL_TYPE_BLANK: // 空值   
                                                System.out.println(" ");   
                                                break;   
                                        case HSSFCell.CELL_TYPE_ERROR: // 故障   
                                                System.out.println(" ");   
                                                break;   
                                        default:   
                                                System.out.print("未知类型   ");   
                                                break;   
                                        }   
                                }   
                                System.out.println();   
                        }   
                } catch (IOException ex) {   
                        ex.printStackTrace();   
                }   
        }   
}

爱若手握流沙

关注

2
点赞
踩
10

收藏

觉得还不错? 一键收藏
0
评论
java导入excel到数据库

导读：要想把excel中的数据通过java一次性导入到数据库，就要用java读取并解析excel，要读取并解析excel就要在导入执行时传进excel的存储路径，如下excelPath function import(){ document.MaterialPriceInfoForm.formAction.value="ImportExcel";
复制链接

扫一扫