java 解析xls 文件 批量新增

import java.io.File;

import java.io.FileInputStream;

import java.io.IOException;

import java.io.InputStream;

import java.text.DateFormat;

import java.text.SimpleDateFormat;

import java.util.ArrayList;

import java.util.Date;

import java.util.List;

 

import org.apache.poi.hssf.usermodel.HSSFDateUtil;

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.XSSFCell;

import org.apache.poi.xssf.usermodel.XSSFWorkbook;

 

public class ExcelRead {

// 判断excel版本

static Workbook openWorkbook(InputStream in, String filename,

String fileFileName) throws IOException {

Workbook wb = null;

if (fileFileName.endsWith(".xlsx")) {

wb = new XSSFWorkbook(in);// Excel 2007

} else {

wb = (Workbook) new HSSFWorkbook(in);// Excel 2003

}

return wb;

}

 

public static List<String[]> getExcelData(String fileName,

String fileFileName) throws Exception {

InputStream in = new FileInputStream(fileName); // 创建输入流

Workbook wb = openWorkbook(in, fileName, fileFileName);// 获取Excel文件对象

Sheet sheet = wb.getSheetAt(0);// 获取文件的指定工作表m 默认的第一个

List<String[]> list = new ArrayList<String[]>();

Row row = null;

Cell cell = null;

int totalRows = sheet.getPhysicalNumberOfRows(); // 总行数

int totalCells = sheet.getRow(0).getPhysicalNumberOfCells();// 总列数

for (int i = 0; i < totalRows; i++) {

// 创建一个数组 用来存储每一列的值

String[] str = new String[totalRows];

row = sheet.getRow(i);

for (int j = 0; j < totalCells; j++) {

cell = (Cell) sheet.getCellComment(j, i);

cell = row.getCell(j);

System.out.println(j + "DDDDDDDDDD");

// str[j] = String.valueOf(cell);

}

// 把刚获取的列存入list

list.add(str);

 

}

for (int r = 0; r < totalRows; r++) {

row = sheet.getRow(r);

System.out.print("第" + r + "行");

for (int c = 0; c < totalCells; c++) {

cell = row.getCell(c);

String cellValue = "";

if (null != cell) {

// 以下是判断数据的类型

switch (cell.getCellType()) {

case XSSFCell.CELL_TYPE_NUMERIC: // 数字

cellValue = cell.getNumericCellValue() + "";

// 时间格式

// if(HSSFDateUtil.isCellDateFormatted(cell)){

// Date dd = cell.getDateCellValue();

// DateFormat df = new

// SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

// cellValue = df.format(dd);

// }

break;

case XSSFCell.CELL_TYPE_STRING: // 字符串

cellValue = cell.getStringCellValue();

break;

case XSSFCell.CELL_TYPE_BOOLEAN: // Boolean

cellValue = cell.getBooleanCellValue() + "";

break;

case XSSFCell.CELL_TYPE_FORMULA: // 公式

cellValue = cell.getCellFormula() + "";

break;

case XSSFCell.CELL_TYPE_BLANK: // 空值

cellValue = "";

break;

case XSSFCell.CELL_TYPE_ERROR: // 故障

cellValue = "非法字符";

break;

default:

cellValue = "未知类型";

break;

}

System.out.print("   " + cellValue + "\t");

 

}

}

System.out.println();

}

// 返回值集合

return list;

}

 

public static void main(String[] args) throws Exception {

String fileName = "C:/Users/liming.zhu/Desktop/工号.xlsx";//C:/Users/liming.zhu/Desktop/工号20150416.xlsx

ExcelRead upload = new ExcelRead();

upload.getExcelData(fileName, ".xlsx");

}

}