左侧是所需的jar包。
1.生成XSSFWorkBook对象,参数为输入流对象 IS
InputStream is = new FileInputStream(dataPath);//dataPath为excel的路径,如"C:/Users/shen/Desktop/2.xlsx",注意是正斜杠
XSSFWorkbook xwb = new XSSFWorkbook(is);
2.利用XSSFWorkbook对象获取某一sheet。
XSSFSheet sheet = xwb.getSheetAt(0);//参数为工作簿的序号,从0开始
3.创建行对象XSSFROW。遍历工作簿的所有行。
XSSFRow rowData;
for (int i = sheet.getTopRow();i<sheet.getLastRow();i++) {
rowData = sheet.getRow(i);
for (int j=rowData.getFirstCellNum();j<rowData.getLastCellNum();j++) {
//打印单元格的值
System.out.print(rowData.getCell(j));
}
System.out.print();
}
package adruill;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Map;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
class TestFile {
public static void main(String[] args) throws IOException {
readExcelData("C:/Users/shen/Desktop/2.xlsx");
}
public static Map<String, ArrayList<String>> readExcelData(String dataPath) throws IOException {
InputStream is = new FileInputStream(dataPath);
@SuppressWarnings("resource")
XSSFWorkbook wb = new XSSFWorkbook(is);
XSSFSheet sheet = wb.getSheetAt(0); // 获取第一个sheet表
XSSFRow rowData;
Map<String, ArrayList<String>> resultData = new HashMap<String, ArrayList<String>>();
String trans_key = null;
String item;
for (int i = sheet.getTopRow(); i < sheet.getLastRowNum() + 1; i++) {
rowData = sheet.getRow(i);
if (i == sheet.getTopRow()) {
continue; //标题不读入
}
ArrayList<String> trans_item = new ArrayList<String>();
StringBuilder sbSql = new StringBuilder();
//拼接字符串,组成SQL
sbSql.append("insert into dvt050 (");
for (int j = rowData.getFirstCellNum(); j < rowData.getLastCellNum(); j++) {
System.out.println(rowData.getPhysicalNumberOfCells());
if (j != rowData.getLastCellNum()-1) {
sbSql.append(rowData.getCell(j) + ",");
} else {
sbSql.append(rowData.getCell(j));
}
}
sbSql.append(")");
System.out.println(sbSql.toString());
resultData.put(trans_key, trans_item);
}
is.close();
return resultData;
}
}