Java excel 读操作

最新推荐文章于 2024-11-22 16:40:03 发布

最新推荐文章于 2024-11-22 16:40:03 发布 · 119 阅读

文章标签：

#Excel #Java #Apache #SQL #J#

java代码示例专栏收录该内容

18 篇文章

订阅专栏

本文介绍了一个Java程序，该程序能够从指定的Excel文件中读取数据，并将这些数据转换为字符串格式后批量插入到数据库中。此过程涉及使用Apache POI库处理Excel文件，以及通过Java SQL API执行数据库操作。

package common.excelOprate;

import java.io.FileInputStream;
import java.sql.*;
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;
public class ReadExcel {

public static String fileToBeRead = "excel/我形我秀七彩铃音下载歌曲列表.xls"; //excel文件路径
public static String convertToString(HSSFCell cell) {
     String ret = "";

     if (cell != null)
     {
  int type = cell.getCellType();
  switch (type)
  {
  case HSSFCell.CELL_TYPE_BLANK:
  case HSSFCell.CELL_TYPE_BOOLEAN:
  case HSSFCell.CELL_TYPE_ERROR:
  case HSSFCell.CELL_TYPE_FORMULA:
      break;
  case HSSFCell.CELL_TYPE_NUMERIC:
      ret += (long)cell.getNumericCellValue();
      if (ret.indexOf(".")>=0){
         ret = ret.substring(0,ret.indexOf("."));
      }
      break;
  case HSSFCell.CELL_TYPE_STRING:
      ret = cell.getStringCellValue();
      break;
  }
     }
     return ret;

}

public void insertexceltodb() throws Exception{
   HSSFWorkbook workbook = new HSSFWorkbook(new FileInputStream(fileToBeRead));
      HSSFSheet sheet = workbook.getSheet("Sheet1");
    int rows = sheet.getPhysicalNumberOfRows();
    int records=2;
   for(int i=1;i<(rows/records+2);i++){
    for(int j=(((i-1)*records)+1);j<((i*records)+1);j++){
     if(j<rows){
    System.out.print("********************************"+j+"********************************");

            HSSFRow row = sheet.getRow(j);
              if(row!= null)
              {
             java.sql.Connection conn = null;
                 PreparedStatement prep = null;
                   String sql = "";
                    String f1 =ReadExcel.convertToString(((HSSFCell)row.getCell((short)0)));
                    String f2 = ReadExcel.convertToString(((HSSFCell)row.getCell((short)1)));
                    String f3 = ReadExcel.convertToString(((HSSFCell)row.getCell((short)2)));
                    String f4 = ReadExcel.convertToString(((HSSFCell)row.getCell((short)3)));
                    String f5 = ReadExcel.convertToString(((HSSFCell)row.getCell((short)4)));
                    String f6 =ReadExcel.convertToString(((HSSFCell)row.getCell((short)5)));
                    String f7 = ReadExcel.convertToString(((HSSFCell)row.getCell((short)6)));
                    String f8 = ReadExcel.convertToString(((HSSFCell)row.getCell((short)7)));
                    String f9 = ReadExcel.convertToString(((HSSFCell)row.getCell((short)8)));
              System.out.println(f1);
              System.out.println(f2);
              System.out.println(f3);
              System.out.println(f4);
              System.out.println(f5);
              System.out.println(f6);
              System.out.println(f7);
              System.out.println(f8);
              System.out.println(f9);
              }




     }
    }

   }




      }