/**
* The BaseBean interface will be implemented by all the beans.
*/
public interface BaseBean {
}/**
* The EntityBean interface will be implements by all the bean which has the
* meanings.
*/
public interface EntityBean extends BaseBean {
public String getClsName();
public String getClsSimpleName();
public String getSheetName();
public String getTableName();
public void putDynamicInfo(String title, Object value);
public Map<String, Object> getDynamicInfo();
}/**
* The ExcelConstant class contains some constant variable.
*/
public final class ExcelConstant {
public static final String DYNAMIC = "DYNAMIC";
public static final String DYNAMIC_DATA = "DYNAMIC_DATA";
public static final String DYNAMIC_COLUMN = "DYNAMIC_COLUMN";
}
/**
* The TitleBean class contains all the title of
* the excel sheet or the special table.
*/
public class TitleBean implements BaseBean {
private LinkedList<String> titleList = new LinkedList<String>(); private String beanName; public TitleBean() {
} public void put(int index, String title) {
titleList.add(index, title);
} public void put(String title) {
titleList.add(title);
} public String get(int index) {
return titleList.get(index);
} public int size() {
return titleList.size();
} public LinkedList<String> getTitleList() {
return titleList;
} public void setTitleList(LinkedList<String> titleList) {
this.titleList = titleList;
} public String getBeanName() {
return beanName;
} public void setBeanName(String beanName) {
this.beanName = beanName;
} public boolean contains(Object obj) {
return titleList.contains(obj);
} public Iterator<String> iterator() {
return titleList.iterator();
} public void addFirst(String title) {
titleList.addFirst(title);
} public void remove(Object key) {
titleList.remove(key);
} public void remove(int index) {
titleList.remove(index);
} public String toString() {
StringBuffer title = new StringBuffer(); for(int i = 0; i < titleList.size(); i++) {
title.append(titleList.get(i) + "\t");
} return title.toString();
}
}
/**
* The PropertyUtil class store all the properties files info to memory.
*/
public class PropertyUtil {
private static Properties prop; /**
* @return which contains all the properties files's info.
*/
public static Properties getProperties() {
if(prop != null) {
return prop;
} prop = new Properties(); try {
prop.load(PropertyUtil.class
.getResourceAsStream("UserInfoBean.properties"));
}
catch(Exception e) {
e.printStackTrace();
} return prop;
} /**
* The method return the value by the key from properties file,
* if the value is null, return the key.
*
* @param key the special key.
* @return the value mapping the key.
*/
public static String getProperty(String key) {
String val = key == null ? null : getProperties().getProperty(key);
return val != null ? val : (key == null ? "NULL" : key);
} /**
* The method check if exist key value in properties file.
*
* @param key the special key.
* @return the value mapping the key.
*/
public static String getProperty2(String key) {
return getProperties().getProperty(key);
}
}
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.poifs.filesystem.OfficeXmlFileException;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;/**
* The ExcelEditor class provide some methods to read from excel file,
* or write into excel file.
*/
public class ExcelEditor {
public static Map<EntityBean, List<BaseBean>> readExcel(File excel,
EntityBean[] beans, List<String> importColsList) throws Exception {
return readExcel(excel, beans, importColsList, false);
} /**
* The method is used to read excel file.
* it can read one sheet or one more sheet,
* that rest with the bean's number.
*
* @param excel The file which read from.
* @param beans Which's construct as same as the excel sheet.
* @return sheetBean The map<sheet name, List<BaseBean>>;
* @throws IOException
*/
public static Map<EntityBean, List<BaseBean>> readExcel(File excel,
EntityBean[] beans, List<String> importColsList,
boolean allDynamic) throws Exception {
Map<EntityBean, List<BaseBean>> sheetBean =
new HashMap<EntityBean, List<BaseBean>>();
InputStream in = new FileInputStream(excel);
Workbook book = null; try {
book = new HSSFWorkbook(in);
}
catch(OutOfMemoryError e) {
String msg = "The file " + excel.getName()
+ " data is error, please delete invalid data!"; throw new Exception(msg);
}
catch(OfficeXmlFileException e) {
in = new FileInputStream(excel);
book = new XSSFWorkbook(in);
}
if(book == null || book.getNumberOfSheets() == 0) {
return sheetBean;
} // read more than one sheet.
for(int sheetNum = 0; sheetNum < book.getNumberOfSheets(); sheetNum++) {
Sheet sheet = book.getSheetAt(sheetNum); if(sheetNum > 0) {
in.close(); return sheetBean;
} if(sheet == null || sheet.getLastRowNum() == 0) {
throw new Exception("data.not.found");
} for(int i = 0; i < beans.length; i++) {
if(sheet.getSheetName() != "") {
sheetBean.put(beans[i],
readExcelSheet(sheet, beans[i], importColsList, allDynamic));
}
}
} in.close(); return sheetBean;
}
/**
* The method read excel sheet data into list.
*
* @param sheet excel sheet.
* @param bean which store the sheet data.
* @param prop resource obj.
* @param importColsList which contains which column should import.
* @return list contains sheet data.
* @throws Exception
*/
public static List<BaseBean> readExcelSheet(Sheet sheet,
EntityBean bean, List<String> importColsList,
boolean allDynamic) throws Exception {
List<BaseBean> beanList = new ArrayList<BaseBean>();
if(sheet == null || sheet.getLastRowNum() == 0) {
return beanList;
} try {
Class<?> cls = Class.forName(bean.getClsName());
TitleBean titleBean = new TitleBean();
boolean hasTitle = false;
int titleRow = 0;
Object stringType = msg(bean.getClsSimpleName() + "_" + "string");
List<String> stringTypeField =
stringType == null ? new ArrayList<String>() :
Arrays.asList(String.valueOf(stringType).split(",")); for(int rowNum = 0; rowNum <= sheet.getLastRowNum(); rowNum++) {
Row row = sheet.getRow(rowNum); if(row == null || row.getLastCellNum() == 0) {
continue;
} EntityBean obj = (EntityBean) cls.newInstance(); for(int cellNum = 0; cellNum < row.getLastCellNum(); cellNum++) {
Cell cell = row.getCell(cellNum); if(cell == null) {
continue;
} if(!hasTitle) {
String val = null; switch(cell.getCellType()) {
case HSSFCell.CELL_TYPE_STRING:
val = cell.getStringCellValue();
break;
case HSSFCell.CELL_TYPE_NUMERIC:
val = String.valueOf(cell.getNumericCellValue());
break;
default:
val = "";
break;
}
String title = PropertyUtil.getProperty2(
obj.getClsSimpleName() + "_" + val.trim()); // int lookMaterByBoard or lookBoardByMater,
// we should append other column except code.
// the other column maybe exist in the material or board object,
// so the value will be set in the set method,
// but we use dynamicInfo when output the other column,
// so the exsit column will not in the dynamicInfo,
// here we make all the other column dynamic.
if(allDynamic && cellNum != 0 && title!= null) {
title = null;
} // skip the invalid row.
if(cellNum == 0 && title == null) {
break;
}
// for dynamic title, such as month.
else if(title == null) {
title = ExcelConstant.DYNAMIC_COLUMN + val;
titleBean.put(title);
}
else {
if(importColsList != null && importColsList.contains(title)) {
titleBean.put(title);
}
else {
titleBean.put(title);
}
} if(cellNum == row.getLastCellNum() - 1) {
titleBean.setBeanName(obj.getClsSimpleName());
beanList.add(titleBean);
hasTitle = true;
titleRow = rowNum;
}
}
else {
String name = titleBean.get(cellNum);
if(name == null || "".equals(name.trim())) {
continue;
} int index = name.indexOf(ExcelConstant.DYNAMIC); if(index != -1) {
Method method = cls.getMethod("putDynamicInfo",
String.class, Object.class);
String dTitle = name; switch(cell.getCellType()) {
case HSSFCell.CELL_TYPE_STRING:
method.invoke(obj, new Object[] {dTitle,
cell.getStringCellValue().trim()});
break;
case HSSFCell.CELL_TYPE_NUMERIC:
if(HSSFDateUtil.isCellDateFormatted(cell)) {
Timestamp t = new Timestamp(
cell.getDateCellValue().getTime());
method.invoke(obj, new Object[] {dTitle, t});
}
else {
method.invoke(obj, new Object[] {dTitle,
cell.getNumericCellValue()});
} break;
default:
break;
} continue;
} Field field = null;
String fieldName = null;
String methodName = null;
try {
try {
field = cls.getDeclaredField(name);
}
catch(NoSuchFieldException e) {
field = cls.getField(name);
} fieldName = field.getName();
methodName = "set" + toFirstUpperCase(fieldName);
Method method = cls.getMethod(methodName, field.getType()); switch(cell.getCellType()) {
case HSSFCell.CELL_TYPE_STRING:
method.invoke(obj, new Object[] {cell
.getStringCellValue().trim()}); break;
case HSSFCell.CELL_TYPE_NUMERIC:
if(HSSFDateUtil.isCellDateFormatted(cell)) {
SimpleDateFormat format =
new SimpleDateFormat("yyyy-MM-dd");
String d = format.format(
new Date(cell.getDateCellValue().getTime()));
method.invoke(obj, new Object[] {d});
}
else {
Double d = cell.getNumericCellValue();
DecimalFormat df = new DecimalFormat();
df.applyPattern("0"); if(stringTypeField.contains(fieldName)) {
method.invoke(obj, new Object[] {String.valueOf(d)});
}
else {
method.invoke(obj, new Object[] {
d.intValue()});
}
} break;
default:
break;
}
}
catch(Exception e) {
e.printStackTrace();
}
}
} if(rowNum > titleRow
&& hasTitle
&& row.getCell(0) != null
&& row.getCell(0).getCellType() != Cell.CELL_TYPE_BLANK) {
beanList.add(obj);
}
} if(!hasTitle) {
throw new Exception("excel.title.error");
}
}
catch(Exception e) {
e.printStackTrace();
} return beanList;
}
* THe method write common data into excel.
*
* @param out output stream.
* @param beanList which contains common data.
* @throws Exception
*/
@SuppressWarnings("unchecked")
public static void writeExcel(OutputStream out, List<BaseBean> beanList)
throws Exception {
HSSFWorkbook book = new HSSFWorkbook();
HSSFDataFormat format = book.createDataFormat();
HSSFCellStyle textStyle = book.createCellStyle();
textStyle.setDataFormat(format.getFormat("@")); TitleBean title = null;
HSSFSheet sheet = null;
boolean isNewSheet = true; for(int i = 0; i < beanList.size(); i++) {
if(beanList.get(i) instanceof TitleBean) {
title = (TitleBean) beanList.get(i);
continue;
} EntityBean bean = (EntityBean) beanList.get(i); if(sheet != null && bean.getSheetName().equals(sheet.getSheetName())) {
isNewSheet = false;
}
else {
isNewSheet = true;
} if(isNewSheet) {
isNewSheet = false;
if(bean.getSheetName() == null) {
throw new Exception("sheet.name.is.null");
} sheet = book.createSheet(bean.getSheetName());
HSSFRow titleRow = sheet.createRow(sheet.getLastRowNum()); for(int j = 0; title != null && j < title.size(); j++) {
HSSFCell cell = titleRow.createCell(j);
String t = "";
int index = title.get(j).indexOf(ExcelConstant.DYNAMIC); if(index != -1) {
t = title.get(j).replace(ExcelConstant.DYNAMIC_COLUMN, "");
t = t.replace(ExcelConstant.DYNAMIC_DATA, "");
t = msg(t);
}
else {
t = msg(title.getBeanName() + "_"
+ title.get(j));
} cell.setCellValue(t);
sheet.setColumnWidth(j, 3000);
}
} if(sheet == null || bean == null) {
return;
} HSSFRow row = sheet.createRow(sheet.getLastRowNum() + 1); for(int k = 0; k < title.size(); k++) {
HSSFCell cell = row.createCell(k);
int index = title.get(k).indexOf(ExcelConstant.DYNAMIC); if(index != -1) {
Method method = bean.getClass().getMethod("getDynamicInfo");
Map<String, Object> dynamicInfo = (Map<String, Object>) method.invoke(bean);
String t = title.get(k).replace(ExcelConstant.DYNAMIC_DATA, "");
Object obj = dynamicInfo.get(t);
obj = obj == null ? "" : obj; try {
cell.setCellValue(Double.parseDouble(String.valueOf(obj)));
}
catch(NumberFormatException e) {
cell.setCellValue(String.valueOf(obj));
cell.setCellStyle(textStyle);
} continue;
}
String methodName = "get" + toFirstUpperCase(title.get(k)); try {
Method method = bean.getClass().getMethod(methodName);
Object obj = method.invoke(bean); if(obj instanceof String) {
if(methodName.indexOf("Code") != -1
|| "replacerMater".equals(title.get(k))) {
cell.setCellValue(String.valueOf(obj));
cell.setCellStyle(textStyle);
}
else {
try {
cell.setCellValue(Double.parseDouble(String.valueOf(obj)));
}
catch(NumberFormatException e) {
cell.setCellValue(String.valueOf(obj));
cell.setCellStyle(textStyle);
}
}
}
else if(obj instanceof Number) {
cell.setCellValue(Double.valueOf(String.valueOf(obj)));
}
else if(obj instanceof Date) {
HSSFCellStyle cs = book.createCellStyle();
cs.setDataFormat((short) 14);
cell.setCellStyle(cs);
cell.setCellValue(HSSFDateUtil.getExcelDate((Date) obj));
}
else if(obj instanceof Boolean) {
cell.setCellValue((Boolean) obj);
}
else if(obj == null) {
continue;
}
else {
throw new Exception("The " + obj + "'s type is error!");
}
}
catch(NoSuchMethodException e) {
throw new Exception("No such method: " + methodName);
}
}
} book.write(out);
} private static String toFirstUpperCase(String fieldName) {
if(fieldName == null || fieldName.length() == 0) {
return "";
}
else {
return fieldName.substring(0, 1).toUpperCase()
+ fieldName.substring(1);
}
} private static String msg(String key) {
return PropertyUtil.getProperty(key);
}
}
/**
* The UserInfoBean class contains all the user info.
*/
public class UserInfoBean implements EntityBean {
private String username; private String password; private String role; private String type; private String regTime; private Map<String, Object> dynamicInfo; public UserInfoBean() {
dynamicInfo = new LinkedHashMap<String, Object>();
} /**
* @return the password
*/
public String getPassword() {
return password;
} /**
* @param password the password to set
*/
public void setPassword(String password) {
this.password = password;
} /**
* @return the type
*/
public String getType() {
return type;
} /**
* @param type the type to set
*/
public void setType(String type) {
this.type = type;
} /**
* @return the regTime
*/
public String getRegTime() {
return regTime;
} /**
* @param regTime the regTime to set
*/
public void setRegTime(String regTime) {
this.regTime = regTime;
} /**
* @return the role
*/
public String getRole() {
return role;
} /**
* @param role the role to set
*/
public void setRole(String role) {
this.role = role;
} /**
* @return the username
*/
public String getUsername() {
return username;
} /**
* @param username the username to set
*/
public void setUsername(String username) {
this.username = username;
} public String getClsName() {
return getClass().getName();
} public String getClsSimpleName() {
return getClass().getSimpleName();
} public String getSheetName() {
return "userinfo";
} public String getTableName() {
return "USER_INFO";
} public void putDynamicInfo(String title, Object value) {
dynamicInfo.put(title, value);
} public Map<String, Object> getDynamicInfo() {
return dynamicInfo;
} public String toString() {
StringBuffer string = new StringBuffer();
string.append("username = " + getUsername());
string.append(", password = " + getPassword());
string.append(", role = " + getRole());
string.append(", purview = " + getType());
string.append(", regTime = " + getRegTime()); return string.toString();
}
}
#excel title to UserInfoBean property
UserInfoBean_用户名=username
UserInfoBean_密码=password
UserInfoBean_角色=role
UserInfoBean_权限=purview
UserInfoBean_注册日期=regTime#UserInfoBean property to excel title
UserInfoBean_username=用户名
UserInfoBean_password=密码
UserInfoBean_role=角色
UserInfoBean_purview=权限
UserInfoBean_regTime=注册日期
UserInfoBean.properties
#excel title to UserInfoBean property
UserInfoBean_\u7528\u6237\u540d=username
UserInfoBean_\u5bc6\u7801=password
UserInfoBean_\u89d2\u8272=role
UserInfoBean_\u6743\u9650=purview
UserInfoBean_\u6ce8\u518c\u65e5\u671f=regTime#UserInfoBean property to excel title
UserInfoBean_username=\u7528\u6237\u540d
UserInfoBean_password=\u5bc6\u7801
UserInfoBean_role=\u89d2\u8272
UserInfoBean_purview=\u6743\u9650
UserInfoBean_regTime=\u6ce8\u518c\u65e5\u671fuserinfo.xls
用户名 密码 角色
admin admin123 admin
test test other
import java.io.*;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;public class ExcelDemo {
/**
* @param args
*/
public static void main(String[] args) {
try {
UserInfoBean userBean = new UserInfoBean();
String fileName = "userinfo.xls";
String path = ExcelDemo.class.getResource(fileName).getFile();
File file = new File(path);
SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMdd");
List<BaseBean> userList = ExcelEditor.readExcel(file,
new EntityBean[] {userBean}, null).get(userBean);
for(int i = 0; i < userList.size(); i++) {
System.err.println(userList.get(i));
if(userList.get(i) instanceof TitleBean) {
((TitleBean) userList.get(i)).put("regTime");
} if(userList.get(i) instanceof UserInfoBean) {
((UserInfoBean) userList.get(i)).setRegTime(sdf.format(new Date()));
}
} path = path.replace(fileName, "userinfo2.xls");
System.err.println(path);
FileOutputStream out = new FileOutputStream(new File(path));
ExcelEditor.writeExcel(out, userList);
out.flush();
out.close();
}
catch(Exception e) {
e.printStackTrace();
}
}
}
完了，谢谢大家。这是一个读写Excel的例子，希望对大家有所帮助。
poi-3.6.jar
poi-3.6-dom4j-1.6.1.jar
poi-3.6-geronimo-stax-api_1.0_spec-1.0.jar
poi-3.6-xmlbeans-2.3.0.jar
poi-3.6-ooxml-20091214.jar
poi-3.6-ooxml-schemas-20091214.jar
在写Excel的时候，特别是碰到小计（合计） 这些问题，要灵活运用List Map这些已有的数据结构。
看一下这张图。。如果是这种结构的Excel（写出去），如果处理？ 首先，定义一个Bean,包含：部门，姓名，职位，住址 也就是所有的表头
二、查出数据，将所有部门加入Set<String)中。这样可以获得不重复的部门 三、定义一个Map<String,List<Bean>>的结构， 来保存管理数据。 string类型的key即是部门名，list<Bean>则是这个属于这个部门的Bean> 大家发现用这种结构去写Excel的时候时候，你只对Map进行迭代就好了。 首先，你在部门那一列要合并单元格，是不是？ 合并多少行列？ 你可以在map中，获取他的数据list.size() 这样就知道要合并多少行了吧 所以，扩展性也特别好。就算加再多部门，也不需要改程序。
当然，还有更复杂的Excel，大家可以采用上面这个思路，自己用更复杂的结构去存数据。。总之，灵活用好Set,List,Map，会让你在操作Excel中，得心应手。
* The UserInfoBean class contains all the user info.
*/
public class UserInfoBean implements EntityBean {
private String username; private String password; private String role; private String type; private String regTime; private Map<String, Object> dynamicInfo; public UserInfoBean() {
dynamicInfo = new LinkedHashMap<String, Object>();
} /**
* @return the password
*/
public String getPassword() {
return password;
} /**
* @param password the password to set
*/
public void setPassword(String password) {
this.password = password;
} /**
* @return the type
*/
public String getType() {
return type;
} /**
* @param type the type to set
*/
public void setType(String type) {
this.type = type;
} /**
* @return the regTime
*/
public String getRegTime() {
return regTime;
} /**
* @param regTime the regTime to set
*/
public void setRegTime(String regTime) {
this.regTime = regTime;
} /**
* @return the role
*/
public String getRole() {
return role;
} /**
* @param role the role to set
*/
public void setRole(String role) {
this.role = role;
} /**
* @return the username
*/
public String getUsername() {
return username;
} /**
* @param username the username to set
*/
public void setUsername(String username) {
this.username = username;
} public String getClsName() {
return getClass().getName();
} public String getClsSimpleName() {
return getClass().getSimpleName();
} public String getSheetName() {
return "userinfo";
} public String getTableName() {
return "USER_INFO";
} public void putDynamicInfo(String title, Object value) {
dynamicInfo.put(title, value);
} public Map<String, Object> getDynamicInfo() {
return dynamicInfo;
} public String toString() {
StringBuffer string = new StringBuffer();
string.append("username = " + getUsername());
string.append(", password = " + getPassword());
string.append(", role = " + getRole());
string.append(", purview = " + getType());
string.append(", regTime = " + getRegTime()); return string.toString();
}
}
else if(title == null) {
title = ExcelConstant.DYNAMIC_COLUMN + val;
titleBean.put(title);
}
else {
if(importColsList != null && importColsList.contains(title)) {
titleBean.put(title);
}
else {
titleBean.put(title);
}
}这一段 怎么回事
那个if else 里面做的事情一摸一样啊
这里当时是一个业务需要，这样写清晰，也可以放一起。
源码下载地址http://download.csdn.net/source/3561738