简单的实体类格式(使用模板进行行列的填充)
/***
* @param index
* 从第Excel第几行开始设置值的索引
* @param fieldNames
* 实体类属性数组
* @param dataset
* 数据源
* @param sheet
* sheet对象
*/
@SuppressWarnings({ "unchecked", "rawtypes" })
public <T> void createSheetRowsByList(int index, String[] fieldNames,
Collection dataset, HSSFSheet sheet, HSSFCellStyle boderStyle) {
// 遍历集合数据，产生数据行
Iterator<T> it = dataset.iterator();
T t;
Pattern p = Pattern.compile("^//d+(//.//d+)?$");
Matcher matcher;
String fieldName; // 遍历实体类字段的名称
String getMethodName; // 通过反射拼接字段的get方法名
Cell cell; // 要填充的单元格
Class tCls;
Method getMethod;
Object value;
String textValue;
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
try {
while (it.hasNext()) {
index++;
Row row = sheet.createRow(index);
t = (T) it.next();
// 利用反射，根据JavaBean属性的先后顺序，动态调用getXxx()方法得到属性值
for (int i = 0; i < fieldNames.length; i++) {
cell = row.createCell(i);
cell.setCellStyle(boderStyle);
fieldName = fieldNames[i];
getMethodName = "get"
+ fieldName.substring(0, 1).toUpperCase()
+ fieldName.substring(1);
tCls = t.getClass();
getMethod = tCls.getMethod(getMethodName, new Class[] {});
value = getMethod.invoke(t, new Object[] {});
// 判断值的类型后进行强制类型转换
textValue = null;
if (value instanceof Integer) {
cell.setCellValue((Integer) value);
} else if (value instanceof Float) {
textValue = String.valueOf((Float) value);
cell.setCellValue(textValue);
} else if (value instanceof Double) {
textValue = String.valueOf((Double) value);
cell.setCellValue(textValue);
} else if (value instanceof Long) {
cell.setCellValue((Long) value);
}
if (value instanceof Boolean) {
textValue = "是";
if (!(Boolean) value) {
textValue = "否";
}
} else if (value instanceof Date) {
textValue = sdf.format((Date) value);
} else {
// 其它数据类型都当作字符串简单处理
if (value != null) {
textValue = value.toString();
}
}
if (textValue != null) {
matcher = p.matcher(textValue);
if (matcher.matches()) {
// 是数字当作double处理
cell.setCellValue(Double.parseDouble(textValue));
} else {
cell.setCellValue(textValue);
}
}
} // end for
}// end while
} catch (Exception e) {
e.printStackTrace();
} finally {
// 清理资源
}
}
简单的键值对格式(使用模板进行行列的填充)
private<T> void createSheetRowsByMap( int index ,String[] fieldNames, Collection dataset , XSSFSheet sheet){
// 遍历集合数据，产生数据行
Iterator<HashMap<String, Object>> it = dataset.iterator();
RichTextString richString;
Pattern p = Pattern.compile("^//d+(//.//d+)?$");
Matcher matcher;
String fieldName;
Cell cell;
Object value;
String textValue;
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
while (it.hasNext()) {
index++;
HashMap<String, Object> map = it.next();
Row row = sheet.createRow(index);
for (int i = 0; i < fieldNames.length; i++) {
cell = row.createCell(i);
fieldName = fieldNames[i];
try {
value = map.get(fieldName);
// 判断值的类型后进行强制类型转换
textValue = null;
if (value instanceof Integer) {
cell.setCellValue((Integer) value);
} else if (value instanceof Float) {
textValue = String.valueOf((Float) value);
cell.setCellValue(textValue);
} else if (value instanceof Double) {
textValue = String.valueOf((Double) value);
cell.setCellValue(textValue);
} else if (value instanceof Long) {
cell.setCellValue((Long) value);
}
if (value instanceof Boolean) {
textValue = "是";
if (!(Boolean) value) {
textValue = "否";
}
} else if (value instanceof Date) {
textValue = sdf.format((Date) value);
} else {
// 其它数据类型都当作字符串简单处理
if (value != null) {
textValue = value.toString();
}
}
if (textValue != null) {
matcher = p.matcher(textValue);
if (matcher.matches()) {
// 是数字当作double处理
cell.setCellValue(Double.parseDouble(textValue));
} else {
richString = new XSSFRichTextString(textValue);
cell.setCellValue(richString);
}
}
} catch (SecurityException e) {
e.printStackTrace();
} catch (IllegalArgumentException e) {
e.printStackTrace();
} finally {
// 清理资源
}
}
}
}
每个单元格带样式的
@SuppressWarnings({ "unchecked", "rawtypes", "unused" })
private static <T> void setSheetRows(String version,String[] fieldNames,int index, Collection dataset ,Sheet sheet,CellStyle style){
// 遍历集合数据，产生数据行
Iterator<HashMap<String, Object>> it = dataset.iterator();
RichTextString richString;
Pattern p = Pattern.compile("^//d+(//.//d+)?$");
Matcher matcher;
String fieldName;
Cell cell;
Object value;
String textValue;
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
DecimalFormat df = new DecimalFormat("##.##");
while (it.hasNext()) {
index++;
Row row = sheet.createRow(index);
HashMap<String, Object> map = it.next();
for (int i = 0; i < fieldNames.length; i++) {
cell = row.createCell(i);
cell.setCellStyle(style);
//field = fields[i];
fieldName = fieldNames[i];
try {
// 判断值的类型后进行强制类型转换
value = map.get(fieldName);
textValue = null;
if (value instanceof Integer) {
cell.setCellValue((Integer) value);
} else if (value instanceof Float) {
cell.setCellValue(df.format((Float) value));
} else if (value instanceof Double) {
cell.setCellValue(df.format((Double) value));
} else if (value instanceof Long) {
cell.setCellValue((Long) value);
}else if (value instanceof BigDecimal) {
cell.setCellValue(((BigDecimal) value).doubleValue());
} else if (value instanceof Boolean) {
textValue = "是";
if (!(Boolean) value) {
textValue = "否";
}
} else if (value instanceof Date) {
textValue = sdf.format((Date) value);
} else {
// 其它数据类型都当作字符串简单处理
if (value != null) {
textValue = value.toString();
}
}
if (textValue != null) {
matcher = p.matcher(textValue);
if (matcher.matches()) {
// 是数字当作double处理
cell.setCellValue(Double.parseDouble(textValue));
} else {
if(StringUtils.isBlank(version) || EXCEl_FILE_2007.equals(version.trim())){
richString = new XSSFRichTextString(textValue);
}else{
richString = new HSSFRichTextString(textValue);
}
cell.setCellValue(richString);
}
}
} catch (SecurityException e) {
e.printStackTrace();
} catch (IllegalArgumentException e) {
e.printStackTrace();
} finally {
// 清理资源
}
//sheet.autoSizeColumn((short)i);
}
}
}
