java实现excel下载和上传的工具类详解编程语言

java实现excel下载和上传的工具类

网上搜索了很多工具类的写法，大多都是poi的应用，

需要用到的poi的maven包

<!– 上传excel –>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>3.16</version>
        </dependency>
        <!– 上传excel –>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.16</version>
        </dependency>

其中有一篇写的比较精炼和容易理解，但是却又几处错误无法直接使用，现将修改后的代码粘贴如下：

package com.estun.util; 
import java.io.IOException; 
import java.io.InputStream; 
import java.io.OutputStream; 
import java.lang.reflect.Field; 
import java.lang.reflect.Method; 
import java.util.ArrayList; 
import java.util.List; 
import javax.servlet.http.HttpServletRequest; 
import org.apache.log4j.Logger; 
import org.apache.poi.hssf.usermodel.HSSFCell; 
import org.apache.poi.hssf.usermodel.HSSFCellStyle; 
import org.apache.poi.hssf.usermodel.HSSFFont; 
import org.apache.poi.hssf.usermodel.HSSFRichTextString; 
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.hssf.util.HSSFColor; 
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; 
/** 
* excel的上传与下载的工具类 
* @author gaozhen 
* @time 2017年10月30日下午3:02:58 
*/ 
public class ExcelUtil { 
private static final Logger logger = Logger.getLogger(ExcelUtil.class); 
/**  
* @Title: createWorkbook  
* @Description: 判断excel文件后缀名，生成不同的workbook  
* @param @param is 
* @param @param excelFileName 
* @param @return 
* @param @throws IOException 
* @return Workbook 
* @throws  
*/ 
public Workbook createWorkbook(InputStream is,String excelFileName) throws IOException{ 
if (excelFileName.endsWith(".xls")) { 
return new HSSFWorkbook(is); 
}else if (excelFileName.endsWith(".xlsx")) { 
return new XSSFWorkbook(is); 
} 
return null; 
} 
/**  
* @Title: getSheet  
* @Description: 根据sheet索引号获取对应的sheet 
* @param @param workbook 
* @param @param sheetIndex 
* @param @return 
* @return Sheet 
* @throws  
*/ 
public Sheet getSheet(Workbook workbook,int sheetIndex){ 
return workbook.getSheetAt(0);         
} 
/**  
* @Title: importDataFromExcel  
* @Description: 将sheet中的数据保存到list中， 
* 1、调用此方法时，vo的属性个数必须和excel文件每行数据的列数相同且一一对应，vo的所有属性都为String 
* 2、在action调用此方法时，需声明  
*     private File excelFile;上传的文件 
*     private String excelFileName;原始文件的文件名 
* 3、页面的file控件name需对应File的文件名 
* @param @param vo javaBean 
* @param @param is 输入流 
* @param @param excelFileName 
* @param @return 
* @return List<Object> 
* @throws  
*/ 
public List<Object> importDataFromExcel(Object vo,InputStream is,String excelFileName){ 
List<Object> list = new ArrayList<Object>(); 
try { 
//创建工作簿 
Workbook workbook = this.createWorkbook(is, excelFileName); 
//创建工作表sheet 
Sheet sheet = this.getSheet(workbook, 0); 
//获取sheet中数据的行数 
int rows = sheet.getPhysicalNumberOfRows(); 
//获取表头单元格个数 
int cells = sheet.getRow(0).getPhysicalNumberOfCells(); 
//利用反射，给JavaBean的属性进行赋值 
Field[] fields = vo.getClass().getDeclaredFields(); 
for (int i = 1; i < rows; i++) {//第一行为标题栏，从第二行开始取数据 
Row row = sheet.getRow(i); 
int index = 0; 
while (index < cells) { 
Cell cell = row.getCell(index); 
if (null == cell) { 
cell = row.createCell(index); 
} 
cell.setCellType(Cell.CELL_TYPE_STRING); 
String value = null == cell.getStringCellValue()?"":cell.getStringCellValue(); 
Field field = fields[index]; 
String fieldName = field.getName(); 
String methodName = "set"+fieldName.substring(0,1).toUpperCase()+fieldName.substring(1); 
Method setMethod = vo.getClass().getMethod(methodName, new Class[]{String.class}); 
setMethod.invoke(vo, new Object[]{value}); 
index++; 
} 
if (isHasValues(vo)) {//判断对象属性是否有值 
list.add(vo); 
//vo=new Student(); 
//vo.getClass().newInstance(); 
vo=vo.getClass().getConstructor(new Class[]{}).newInstance(new Object[]{});//重新创建一个vo对象 
} 
} 
} catch (Exception e) { 
e.printStackTrace(); 
logger.error(e); 
}finally{ 
try { 
is.close();//关闭流 
} catch (Exception e2) { 
logger.error(e2); 
} 
} 
return list; 
} 
/**  
* @Title: isHasValues  
* @Description: 判断一个对象所有属性是否有值，如果一个属性有值(分空)，则返回true 
* @param @param object 
* @param @return 
* @return boolean 
* @throws  
*/ 
public boolean isHasValues(Object object){ 
Field[] fields = object.getClass().getDeclaredFields(); 
boolean flag = false; 
for (int i = 0; i < fields.length; i++) { 
String fieldName = fields[i].getName(); 
String methodName = "get"+fieldName.substring(0, 1).toUpperCase()+fieldName.substring(1); 
Method getMethod; 
try { 
getMethod = object.getClass().getMethod(methodName); 
Object obj = getMethod.invoke(object); 
if (null != obj && !"".equals(obj)) { 
flag = true; 
break; 
} 
} catch (Exception e) { 
logger.error(e); 
} 
} 
return flag; 
} 
public <T> void exportDataToExcel(List<T> list,String[] headers,String title,OutputStream os){ 
HSSFWorkbook workbook = new HSSFWorkbook(); 
//生成一个表格 
HSSFSheet sheet = workbook.createSheet(title); 
//设置表格默认列宽15个字节 
sheet.setDefaultColumnWidth(15); 
//生成一个样式 
HSSFCellStyle style = this.getCellStyle(workbook); 
//生成一个字体 
HSSFFont font = this.getFont(workbook); 
//把字体应用到当前样式 
style.setFont(font); 
//生成表格标题 
HSSFRow row = sheet.createRow(0); 
row.setHeight((short)300); 
HSSFCell cell = null; 
for (int i = 0; i < headers.length; i++) {         
cell = row.createCell(i); 
cell.setCellStyle(style); 
HSSFRichTextString text = new HSSFRichTextString(headers[i]); 
cell.setCellValue(text); 
} 
//将数据放入sheet中 
for (int i = 0; i < list.size(); i++) { 
row = sheet.createRow(i+1); 
T t = list.get(i); 
//利用反射，根据JavaBean属性的先后顺序，动态调用get方法得到属性的值 
Field[] fields = t.getClass().getFields(); 
try { 
for (int j = 0; j < fields.length; j++) { 
cell = row.createCell(j); 
Field field = fields[j]; 
String fieldName = field.getName(); 
String methodName = "get"+fieldName.substring(0, 1).toUpperCase()+fieldName.substring(1); 
Method getMethod = t.getClass().getMethod(methodName,new Class[]{}); 
Object value = getMethod.invoke(t, new Object[]{}); 
if(null == value) 
value =""; 
cell.setCellValue(value.toString()); 
} 
} catch (Exception e) { 
logger.error(e); 
} 
} 
try { 
workbook.write(os); 
} catch (Exception e) { 
logger.error(e); 
}finally{ 
try { 
os.flush(); 
os.close(); 
} catch (IOException e) { 
logger.error(e); 
} 
} 
} 
/**  
* @Title: getCellStyle  
* @Description: 获取单元格格式 
* @param @param workbook 
* @param @return 
* @return HSSFCellStyle 
* @throws  
*/ 
public HSSFCellStyle getCellStyle(HSSFWorkbook workbook){ 
HSSFCellStyle style = workbook.createCellStyle(); 
style.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index); 
style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); 
style.setBorderBottom(HSSFCellStyle.BORDER_THIN); 
style.setBorderTop(HSSFCellStyle.BORDER_THIN); 
style.setLeftBorderColor(HSSFCellStyle.BORDER_THIN); 
style.setRightBorderColor(HSSFCellStyle.BORDER_THIN); 
style.setAlignment(HSSFCellStyle.ALIGN_CENTER); 
return style; 
} 
/**  
* @Title: getFont  
* @Description: 生成字体样式 
* @param @param workbook 
* @param @return 
* @return HSSFFont 
* @throws  
*/ 
public HSSFFont getFont(HSSFWorkbook workbook){ 
HSSFFont font = workbook.createFont(); 
font.setColor(HSSFColor.WHITE.index); 
font.setFontHeightInPoints((short)12); 
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); 
return font; 
} 
public boolean isIE(HttpServletRequest request){ 
return request.getHeader("USER-AGENT").toLowerCase().indexOf("msie")>0?true:false;     
} 
}