Poi如何去读取excel文件

发布网友发布时间：2022-04-26 04:52

共2个回答

热心网友时间：2022-05-11 21:59

直接全部在action里面写的，这个就不多说了，直接上代码：

public String executeExcel() throws Exception{ String realPath = ServletActionContext.getServletContext().getRealPath("/fileupload");
System.out.println(fileFileName);
String filePath = "";
if(this.file!=null){
File saveFile = new File(new File(realPath),this.fileFileName);
filePath = realPath+"\\"+this.fileFileName;
System.out.println(filePath);
if(!saveFile.getParentFile().exists()){
saveFile.getParentFile().mkdirs(); }
FileUtils.copyFile(file, saveFile); }
this.exlToDB(filePath);
ActionContext.getContext().put("message","导入成功");
return "success"; } //读取excel2007，并把数据插入数据库
public void exlToDB(String filePath){ boolean flag = true;
AllKpi akpi = new AllKpi(); try { // 文件流指向excel文件
FileInputStream fin=new FileInputStream(filePath);
XSSFWorkbook workbook = new XSSFWorkbook(fin);// 创建工作薄
XSSFSheet sheet = workbook.getSheetAt(0);// 得到工作表
XSSFRow row = null;// 对应excel的行
XSSFCell cell = null;// 对应excel的列
int totalRow = sheet.getLastRowNum();// 得到excel的总记录条数
System.out.println(totalRow); // 以下的字段一一对应数据库表的字段
float idd = 0.0f;
String id = "";
String Name = "";
String DEPT_NAME = "";
String Weight = "";
<span></span>String ALGORITHM = "";
String text = " ";
//String sql = "insert into DSP_TJ_KPI values(DSP_TJ_KPI_SEQ.nextval,?,?,?,'无',?)";
for (int i = 1; i <= totalRow; i++) {
row = sheet.getRow(i);
//System.out.println(row.getCell(0).toString());
if(row.getCell(0) != null && !"".equals(row.getCell(0)) && row.getCell(1) != null && !"".equals(row.getCell(1)) && row.getCell(2) != null && !"".equals(row.getCell(2)) && row.getCell(3) != null && !"".equals(row.getCell(3))){
cell = row.getCell((short) 0);
Name = cell.toString();
System.out.println(Name);
cell = row.getCell((short) 1);
Weight = cell.toString();
System.out.println(Weight);
cell = row.getCell((short) 2);
DEPT_NAME = cell.toString();
System.out.println(DEPT_NAME);
cell = row.getCell((short) 3);
ALGORITHM = cell.toString();
System.out.println(ALGORITHM);
akpi.setAllkpiName(Name);
akpi.setAllkpiDeptName(DEPT_NAME);
akpi.setAllkpiWeight(Weight);
akpi.setAlgorithm(ALGORITHM);
akpi.setText(text);
allKpiService.addAllKpi(akpi); //以下注释代码为连接jdbc测试代码块
/*pst = con.prepareStatement(sql);
//pst.setString(1, student_id);
pst.setString
(1, DEPT_NAME);
pst.setString
(2, Name);
pst.setString
(3, Weight);
<span></span>pst.setString(4, ALGORITHM);
pst.execute();*/
System.out.println("preparestatement successful"); } }
/*pst.close();
con.close();*/
fin.close(); } catch (FileNotFoundException e)
{
flag = false;
e.printStackTrace();
}
catch (IOException ex)
{
flag = false;
ex.printStackTrace();
}

热心网友时间：2022-05-11 23:17

Poi如何去读取excel文件

主要是通过poi去去读取文件的每一行的。下载poi的jar文件是poi-bin-3.8-beta2-20110408.zip 那下边来分享一下代码吧：
package com.util;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
public class TestExcel {
//记录类的输出信息
static Log log = LogFactory.getLog(TestExcel.class);
//获取Excel文档的路径
public static String filePath = "D://excel.xls";
public static void main(String[] args) {
try {
// 创建对Excel工作簿文件
//HSSFWorkbook wookbook= new HSSFWorkbook(new FileInputStream(filePath));
// 在Excel文档中，第一张工作表的缺省索引是0，
// 其语句为：HSSFSheet sheet = workbook.getSheetAt(0);
// HSSFSheet sheet = wookbook.getSheet("Sheet1");
Workbook book = null;
try {
book = new XSSFWorkbook(filePath);
} catch (Exception ex) {
book = new HSSFWorkbook(new FileInputStream(filePath));
}

Sheet sheet = book.getSheet("Sheet1");
//获取到Excel文件中的所有行数
int rows = sheet.getPhysicalNumberOfRows();
//遍历行
for (int i = 0; i < rows; i++) {
// 读取左上端单元格
Row row = sheet.getRow(i);
// 行不为空
if (row != null) {
//获取到Excel文件中的所有的列
int cells = row.getPhysicalNumberOfCells();
String value = "";
//遍历列
for (int j = 0; j < cells; j++) {
//获取到列的值
Cell cell = row.getCell(j);
if (cell != null) {
switch (cell.getCellType()) {
case Cell.CELL_TYPE_FORMULA:
break;
case Cell.CELL_TYPE_NUMERIC:
value += cell.getNumericCellValue() + ",";
break;
case Cell.CELL_TYPE_STRING:
value += cell.getStringCellValue() + ",";
break;
default:
value += "0";
break;
}
}
}
// 将数据插入到mysql数据库中
String[] val = value.split(",");
for(int j=0;j<val.length;j++){
//每一行列数中的值遍历出来
System.out.println(val[j]);
}

}
}
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
}
}
?0?2
?0?2
?0?2
?0?2
?0?2

热心网友时间：2022-05-11 21:59

热心网友时间：2022-05-11 23:17

热心网友时间：2022-05-11 21:59

热心网友时间：2022-05-11 23:17