import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.lang.reflect.Method;
import java.text.DecimalFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;
import javax.servlet.http.HttpServletResponse;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
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.XSSFWorkbook;
public class ExcelUtil {
/** Excel 文件要存放的位置,假定在D盘下 */
public static String outputFile = "D:\\test1.xls";
public static String fileToBeRead="D:\\test.xls";
/**
*
* 描述:导出excel文件
* @param list:实体类的集合
* @param name:导出excel文件的文件名
* @param columnName:要导出的列名(开头第一个字母大写)
* @param columnTitle 列名对应的标题
* @param ClassName 列中包含的引用对象的类名(前后需加逗号分隔,要求是需要取的值必须是该引用对象的Name属性且名字应与该对象属性名字一致,如要取Department dept对象的deptName属性,则该参数为',Department,')
*/
public static void Export(HttpServletResponse response,List<?> list, String name, String[] columnName, String[] columnTitle,String ClassName){
OutputStream os = null;
//输出到输出流,让用户下载
try {
response.setHeader("Content-disposition", "attachment; filename="
+ new String(name.getBytes("GB2312"), "ISO8859-1")
+ ".xls");
response.setContentType("application/msexcel");
os = response.getOutputStream();
} catch (IOException e1) {
e1.printStackTrace();
}
//输出到指定文件
/*OutputStream os = null;
try {
os = new FileOutputStream(new File("d:/"+name+".xls"));
} catch (FileNotFoundException e1) {
e1.printStackTrace();
}*/
Class<?> bean = list.get(0).getClass();
//新建excel
HSSFWorkbook workbook = new HSSFWorkbook();
//得到实体类的名字,作为工作表的名字
String bean_name = bean.getName();
bean_name = bean_name.substring(bean_name.lastIndexOf(".")+1);
//第一张工作表
HSSFSheet sheet = workbook.createSheet(bean_name);
//设置字体样式
HSSFFont font = workbook.createFont();
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
HSSFCellStyle cellStyle= workbook.createCellStyle();
cellStyle.setFont(font);
//设置Excel表头,如果标题为null则用列名代替标题
if(columnTitle==null){
columnTitle = columnName;
}
HSSFRow firstrow = sheet.createRow(0); // 下标为0的行开始
for (int i = 0; i < columnTitle.length; i++) {
HSSFCell title = firstrow.createCell(i);
title.setCellStyle(cellStyle);
title.setCellValue(columnTitle[i]);
}
String value;
SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");
try{
for(int t = 0;t < list.size();t++){
Object b = list.get(t);
HSSFRow cotent = sheet.createRow(t + 1);
for(int i=0;i<columnName.length;i++){
Method method = bean.getMethod("get"+columnName[i]);
//判断是否是日期属性
Object o = method.invoke(b);
if(o!=null){
if(o instanceof Date){
value = format.format(format.parse(o.toString()));
}else{
if(ClassName !=null && !"".equals(ClassName) && ClassName.indexOf(","+o.getClass().getSimpleName()+",") != -1){
value = o.getClass().getMethod("get"+columnName[i]+"Name").invoke(o).toString();
}else{
value = o.toString();
}
}
}else{
value="";
}
HSSFCell contentDetail = cotent.createCell(i);
contentDetail.setCellValue(value);
}
}
workbook.write(os);
os.flush();
os.close();
}catch(Exception e){
e.printStackTrace();
}
}
/**
*
* 描述:导入excel文件
* @param i_class:导入的文件中存放实体类的class
* @param file:需要导入的文件
* @param map:导入的模板(excel里的表头和实体类里的列对应)
* @param isExcel2003:判断excel文件是否为2003版本
* @return 返回值若为String则表示有错误,若为集合则为将excel中数据填充后的实体类集合
*/
public static Object Import(Class<?> i_class,Map<String,String> map, File file,boolean isExcel2003){
//存储实体的集合
List<Object> list = new ArrayList<Object>();
//错误数
int wrong=0;
//标题数组
String[] attrs = null;
String[] attrsChildren = null;
//错误消息
StringBuffer message = new StringBuffer();
SimpleDateFormat format = new SimpleDateFormat("yyyy-M-d");
SimpleDateFormat format2 = new SimpleDateFormat("yyyy/M/d");
DecimalFormat df = new DecimalFormat("0");
try {
//根据文件得到workbook对象
Workbook workbook = null;
if(isExcel2003){
//2003
workbook = new HSSFWorkbook(new FileInputStream(file));
}else{
//2007
workbook = new XSSFWorkbook(new FileInputStream(file));
}
//得到sheet工作表的数量
int sheetNumber = workbook.getNumberOfSheets();
//存储各个工作表标题的集合
List<String[]> ColNames=new ArrayList<String[]>();
//验证列名
StringBuffer wrongCols = new StringBuffer("出错列名为:");
int size = 0;
Sheet sheet = workbook.getSheetAt(size);
String content;
while(sheet != null){
Row row = sheet.getRow(0);
if(row != null){
int rowNum = (int)row.getLastCellNum();
attrs = new String[rowNum];
int isNull = 0;
for(int i = 0;i < rowNum;i++){
Cell cell = row.getCell(i);
content = cell.getStringCellValue();
if(content != null && !"".equals(content)){
isNull++;
if(!map.containsKey(content)){
wrongCols.append(content+",");
}else{
attrs[i]=map.get(content);
}
}
}
attrsChildren = new String[isNull];
for(int t = 0;t < isNull;t++){
attrsChildren[t] = attrs[t];
}
attrs = attrsChildren;
ColNames.add(attrs);
}
size++;
if(size >= (sheetNumber - 1)){
break;
}
sheet = workbook.getSheetAt(size);
}
if(!wrongCols.toString().equals("出错列名为:")){
return wrongCols.subSequence(0, wrongCols.length() - 1);
}
//验证数据类型
String MethodName;
Method method;
size = 0;
sheet = workbook.getSheetAt(size);
String oldContent;
Date dateValue;
while(sheet != null){
oldContent = message.toString();
int rowNum = 1;
Row row = sheet.getRow(rowNum++);
if(row == null){
size++;
if(size >= (sheetNumber - 1)){
break;
}
sheet = workbook.getSheetAt(size);
continue;
}
attrs = ColNames.get(size);
Cell cell = null;
while(row != null && row.getLastCellNum() > 0){
for(int i=0;i<attrs.length;i++){
dateValue = null;
cell = row.getCell(i);
String contentDetail = "";
if (null != cell){
// 以下是判断数据的类型
switch (cell.getCellType()){
case HSSFCell.CELL_TYPE_NUMERIC: // 数字
//判断是否是日期类型
if(HSSFDateUtil.isCellDateFormatted(cell)){
dateValue = cell.getDateCellValue();
}else{
contentDetail = cell.getNumericCellValue() + "";
}
break;
case HSSFCell.CELL_TYPE_STRING: // 字符串
contentDetail = cell.getStringCellValue();
break;
case HSSFCell.CELL_TYPE_BOOLEAN: // Boolean
contentDetail = cell.getBooleanCellValue() + "";
break;
case HSSFCell.CELL_TYPE_FORMULA: // 公式
contentDetail = cell.getCellFormula() + "";
break;
case HSSFCell.CELL_TYPE_BLANK: // 空值
contentDetail = "";
break;
case HSSFCell.CELL_TYPE_ERROR: // 故障
contentDetail = "";
break;
default:
contentDetail = "";
break;
}
}
if(i > (row.getLastCellNum() - 1) || contentDetail == null || "".equals(contentDetail)){
size++;
if(size >= (sheetNumber - 1)){
break;
}
sheet = workbook.getSheetAt(size);
continue;
}
MethodName = "set"+attrs[i];
//Integer类型
try{
method = i_class.getMethod(MethodName,Integer.class);
Integer.parseInt(contentDetail);
continue;
}catch(NoSuchMethodException e){
}catch(Exception e){
wrong++;
if(message.indexOf("第" + row.getRowNum() + "张工作表:") == -1){
message.append("第" + row.getRowNum() + "张工作表:");
}
message.append(attrs[i] + "列,"+ rowNum + "行;");
continue;
}
//Double类型
try{
method = i_class.getMethod(MethodName, Double.class);
Double.parseDouble(contentDetail);
continue;
}catch(NoSuchMethodException e){
}catch(Exception e1){
wrong++;
if(message.indexOf("第" + row.getRowNum() + "张工作表:") == -1){
message.append("第" + row.getRowNum() + "张工作表:");
}
message.append(attrs[i] + "列," + rowNum + "行;");
continue;
}
//Date类型
try{
method = i_class.getMethod(MethodName, Date.class);
if(dateValue == null){
try{
format.parse(contentDetail);
}catch(ParseException e){
format2.parse(contentDetail);
}
}
continue;
}catch(NoSuchMethodException e){
}catch(Exception e2){
wrong++;
if(message.indexOf("第" + row.getRowNum() + "张工作表:") == -1){
message.append("第" + row.getRowNum() + "张工作表:");
}
message.append(attrs[i] + "列," + rowNum + "行;");
continue;
}
//String类型
try{
method = i_class.getMethod(MethodName, String.class);
}catch(NoSuchMethodException e){
wrong++;
if(message.indexOf("第" + row.getRowNum() + "张工作表:") == -1){
message.append("第" + row.getRowNum() + "张工作表:");
}
message.append(attrs[i] + "列," + rowNum + "行;");
}
}
row = sheet.getRow(rowNum++);
}
if(!message.toString().equals(oldContent)){
message.append("<br />");
}
size++;
if(size >= (sheetNumber - 1)){
break;
}
sheet = workbook.getSheetAt(size);
}
if(wrong>0){
return "数据出错总数为:" + wrong + "<br />分别为:" + message.toString();
}
size = 0;
sheet = workbook.getSheetAt(size);
Cell cell = null;
while(sheet != null){
if(size >= ColNames.size()){
break;
}
attrs = ColNames.get(size);
int rowNum = 1;
Row row = sheet.getRow(rowNum++);
while(row != null && row.getLastCellNum() > 0){
//此处可增加判断行为空行是跳过的代码
Object o = i_class.newInstance();
for(int i=0;i<attrs.length;i++){
dateValue = null;
cell = row.getCell(i);
String contentDetail = "";
if (null != cell){
// 以下是判断数据的类型
switch (cell.getCellType()){
case HSSFCell.CELL_TYPE_NUMERIC: // 数字
//判断是否是日期类型,若是则转为字符串,因为有可能数据类型为String
if(HSSFDateUtil.isCellDateFormatted(cell)){
dateValue = cell.getDateCellValue();
contentDetail = format.format(dateValue);
}else{
contentDetail = df.format(cell.getNumericCellValue());
}
break;
case HSSFCell.CELL_TYPE_STRING: // 字符串
contentDetail = cell.getStringCellValue();
break;
case HSSFCell.CELL_TYPE_BOOLEAN: // Boolean
contentDetail = cell.getBooleanCellValue() + "";
break;
case HSSFCell.CELL_TYPE_FORMULA: // 公式
contentDetail = cell.getCellFormula() + "";
break;
case HSSFCell.CELL_TYPE_BLANK: // 空值
contentDetail = "";
break;
case HSSFCell.CELL_TYPE_ERROR: // 故障
contentDetail = "";
break;
default:
contentDetail = "";
break;
}
}
if(i > (row.getLastCellNum() - 1) || contentDetail == null || "".equals(contentDetail)){
continue;
}
MethodName = "set"+attrs[i];
//得到该属性对应的set方法,参数有可能是Integer、Double、Date、String
try{
method = i_class.getMethod(MethodName,Integer.class);
method.invoke(o, Integer.parseInt(contentDetail));
}catch(Exception e){
try{
method = i_class.getMethod(MethodName, Double.class);
method.invoke(o, Double.parseDouble(contentDetail));
}catch(Exception e1){
try{
method = i_class.getMethod(MethodName, Date.class);
if(dateValue != null){
method.invoke(o, dateValue);
}else{
try{
method.invoke(o, format.parse(contentDetail));
}catch(ParseException pe){
method.invoke(o, format2.parse(contentDetail));
}
}
}catch(Exception e2){
method = i_class.getMethod(MethodName, String.class);
method.invoke(o, contentDetail);
}
}
}
}
list.add(o);
row = sheet.getRow(rowNum++);
}
size++;
if(size >= (sheetNumber - 1)){
break;
}
sheet = workbook.getSheetAt(size);
}
} catch (Exception e) {
e.printStackTrace();
}
return list;
}
public static void main(String argv[]) {
/*List<Level> l_list = new ArrayList<Level>();
for(int i=1;i<=10;i++){
Level level = new Level(i,"LevelName" + i,"LevelDesc" + i,i);
l_list.add(level);
}
String[] columnName = {"LevelId","LevelName","LevelDesc","LevelOrder"};
String[] columnTitle = {"级别ID","级别名称","级别描述","级别重要顺序"};
Export(null,l_list,"POI",columnName,columnTitle,null);
Map<String,String> map = new HashMap<String,String>();
map.put("级别ID", "LevelId");
map.put("级别名称", "LevelName");
map.put("级别描述", "LevelDesc");
map.put("级别重要顺序", "LevelOrder");
Object o = Import(Level.class,map,new File("d:/POI.xls"),true);
if(o instanceof String){
System.out.println(o.toString());
}else{
List<Level> test = (List<Level>)o;
for(Level l : test){
System.out.println(l.getLevelName());
}
}*/
}
}
用POI实现的导入导出excel的公用类,根据反射实现,导入的方法由于判断有效性略麻烦。。。