Excelインポートデータ--解析(2003と2007の互換性)
7137 ワード
Excelはjavaで解析し,最初は難しくなかったが,2003バージョンと2007以上のバージョンでの互換解析は困難であった.長いことやってやっとできた.次は私が修正したコードです.
package com.test;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DataFormatter;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import com.pojo.VHrUser;
/**
* Excel
*/
public class UserImport {
private static final int version2003 = 2003;
private static final int version2007 = 2007;
private static int version = version2003;
private static Workbook wb = null;
private static Sheet sheet=null;
private static Cell cell = null;
private static org.apache.poi.ss.usermodel.Row row=null;
public List readExcel(String excelFilePath) throws IOException {
if (excelFilePath.endsWith(".xls"))
version = version2003;
else if (excelFilePath.endsWith(".xlsx"))
version = version2007;
List list = new ArrayList();
InputStream stream=null;
if (version == version2003) {
stream = new FileInputStream(excelFilePath);
wb = (Workbook) new HSSFWorkbook(stream);
} else if (version == version2007) {
wb = (Workbook) new XSSFWorkbook(excelFilePath);
}
sheet = wb.getSheetAt(0);
// ( 0 , ),
int count_row=sheet.getLastRowNum(),count_cell=sheet.getRow(0).getPhysicalNumberOfCells();
String[][] str=new String[count_row][count_cell];
for (int i = 0; i < count_row; i++) {
for (int j = 0; j < count_cell; j++) {
row=sheet.getRow(i+1);
cell=((org.apache.poi.ss.usermodel.Row) row).getCell(j);
int type = cell.getCellType(); //
String k="";
switch (type) { //
case Cell.CELL_TYPE_BLANK:
k = "";
break;
case Cell.CELL_TYPE_BOOLEAN:
k = cell.getBooleanCellValue() + "";
break;
case Cell.CELL_TYPE_ERROR:
k = cell.getErrorCellValue() + "";
break;
case Cell.CELL_TYPE_FORMULA:
k = cell.getCellFormula();
break;
case Cell.CELL_TYPE_NUMERIC:
if(DateUtil.isCellDateFormatted(cell)){
k = new DataFormatter().formatRawCellContents(cell.getNumericCellValue(), 0, "yyyy-mm-dd");//
}else{
k=cell.getNumericCellValue()+"";
}
break;
case Cell.CELL_TYPE_STRING:
k = cell.getStringCellValue();
break;
default:
break;
}
str[i][j]=k;
}
}
cell=null;
row=null;
sheet=null;
wb=null;
for (int k = 0; k < str.length; k++) {
String[] temp_str=str[k];
VHrUser vUser=new VHrUser();
for (int s = 0; s < temp_str.length; s++) {
vUser.setUsername(temp_str[s]);
vUser.setPassword(temp_str[s+1]);
vUser.setDate(temp_str[s+2]);
list.add(vUser);
break;
}
}
return list;
}
public static void main(String[] args){
UserImport i=new UserImport();
try {
List<VHrUser> vuser_list = i.readExcel("d:\\excel.xlsx");
for (VHrUser user : vuser_list) {
System.out.println(" =="+user.getUsername());
}
} catch (IOException e) {
e.printStackTrace();
}
System.exit(0);
}
}
package com.pojo;
public class VHrUser {
private String username;
private String password;
private String date;
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
public String getDate() {
return date;
}
public void setDate(String date) {
this.date = date;
}
}