Excel数据导出与导入-优快云博客

本文链接：https://blog.youkuaiyun.com/weixin_43853787/article/details/87284015

|首先导入工具类

public class ExportExcel extends BaseJerseySupport {

public void exportExcelwithoutCellRangeAddress(HSSFWorkbook workbook, String sheet,String[] headers,
		Collection<T> dataset) {
	// exportExcel("导出EXCEL文档", headers, dataset, out, "yyyy-MM-dd");
	exportExcelmergewithoutCellRangeAddress(workbook,sheet, headers, dataset,"yyyy-MM-dd");

}

// 无合并类的方法
@SuppressWarnings("unchecked")
public void exportExcelmergewithoutCellRangeAddress(HSSFWorkbook workbook, String title,
		String[] headers, Collection<T> dataset, 
		String pattern) {
	

	// 生成一个表格
	HSSFSheet sheet = workbook.createSheet(title);
	
	// 设置表格默认列宽度为15个字节
	sheet.setDefaultColumnWidth((short) 15);
	// 生成一个样式
	HSSFCellStyle style = workbook.createCellStyle();
	// 设置这些样式
	style.setFillForegroundColor(HSSFColor.WHITE.index);
	style.setFillPattern(CellStyle.SOLID_FOREGROUND);
	style.setBorderBottom(CellStyle.BORDER_THIN);
	style.setBorderLeft(CellStyle.BORDER_THIN);
	style.setBorderRight(CellStyle.BORDER_THIN);
	style.setBorderTop(CellStyle.BORDER_THIN);
	style.setAlignment(CellStyle.ALIGN_CENTER);
	// 生成一个字体
	HSSFFont font = workbook.createFont();
	font.setColor(HSSFColor.BLACK.index);
	font.setFontHeightInPoints((short) 12);
	font.setBoldweight(Font.BOLDWEIGHT_BOLD);
	// 把字体应用到当前的样式
	style.setFont(font);
	// 生成并设置另一个样式
	HSSFCellStyle style2 = workbook.createCellStyle();
	// style2.setFillForegroundColor(HSSFColor.LIGHT_YELLOW.index);
	style2.setFillForegroundColor(HSSFColor.WHITE.index);
	style2.setFillPattern(CellStyle.SOLID_FOREGROUND);
	style2.setBorderBottom(CellStyle.BORDER_THIN);
	style2.setBorderLeft(CellStyle.BORDER_THIN);
	style2.setBorderRight(CellStyle.BORDER_THIN);
	style2.setBorderTop(CellStyle.BORDER_THIN);
	style2.setAlignment(CellStyle.ALIGN_CENTER);
	style2.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
	// 生成另一个字体
	HSSFFont font2 = workbook.createFont();
	// font2.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
	font2.setColor(HSSFColor.BLACK.index);
	// 把字体应用到当前的样式
	style2.setFont(font2);
	// 声明一个画图的顶级管理器
	HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
	// 定义注释的大小和位置,详见文档
	HSSFComment comment = patriarch.createComment(new HSSFClientAnchor(0,
			0, 0, 0, (short) 4, 2, (short) 6, 5));
	// 设置注释内容
	comment.setString(new HSSFRichTextString("可以在POI中添加注释！"));
	// 设置注释作者，当鼠标移动到单元格上是可以在状态栏中看到该内容.
	comment.setAuthor("longmai");

	HSSFFont font3 = workbook.createFont();
	font3.setColor(HSSFColor.BLACK.index);

	// 产生表格标题行
	HSSFRow row = sheet.createRow(0);
	for (short i = 0; i < headers.length; i++) {
		HSSFCell cell = row.createCell(i);
		cell.setCellStyle(style);
		HSSFRichTextString text = new HSSFRichTextString(headers[i]);
		cell.setCellValue(text);
	}

	// 遍历集合数据，产生数据行
	Iterator<T> it = dataset.iterator();
	int index = 0;
	while (it.hasNext()) {
		index++;
		row = sheet.createRow(index);
		T t = it.next();
		// 利用反射，根据javabean属性的先后顺序，动态调用getXxx()方法得到属性值
		Field[] fields = t.getClass().getDeclaredFields();
		// 插入序号

// HSSFCell celltitle = row.createCell(0);
// celltitle.setCellStyle(style2);
// HSSFRichTextString richNumber = new HSSFRichTextString(index + “”);
// richNumber.applyFont(font3);
// celltitle.setCellValue(richNumber);
// 第一个是序号第二个才是JAVA 反射对象的属性
for (short i = 0; i < fields.length; i++) {
HSSFCell cell = row.createCell(i);
cell.setCellStyle(style2);
Field field = fields[i];
String fieldName = field.getName();
String getMethodName = “get”
+ fieldName.substring(0, 1).toUpperCase()
+ fieldName.substring(1);
//

			try {
				Class tCls = t.getClass();
				Method getMethod = tCls.getMethod(getMethodName,
						new Class[] {});
				Object value = getMethod.invoke(t, new Object[] {});
				// 判断值的类型后进行强制类型转换
				String textValue = null;
				if (value instanceof Date) {
					Date date = (Date) value;
					SimpleDateFormat sdf = new SimpleDateFormat(pattern);
					textValue = sdf.format(date);
				} else {
					// 其它数据类型都当作字符串简单处理
					if (value != null) {
						textValue = value.toString();
					} else {
						textValue = " ";
					}
				}
				// 如果不是图片数据，就利用正则表达式判断textValue是否全部由数字组成
				if (textValue != null) {
					Pattern p = Pattern.compile("^//d+(//.//d+)?$");
					Matcher matcher = p.matcher(textValue);
					if (matcher.matches()) {
						// 是数字当作double处理
						cell.setCellValue(Double.parseDouble(textValue));
					} else {

						HSSFRichTextString richString = new HSSFRichTextString(
								textValue);
						richString.applyFont(font3);
						cell.setCellValue(richString);
					}
				}
			} catch (SecurityException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			} catch (NoSuchMethodException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			} catch (IllegalArgumentException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			} catch (IllegalAccessException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			} catch (InvocationTargetException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			} finally {
				// 清理资源

			}
		}

// if(merge){
// CellRangeAddress region1=new CellRangeAddress(0,0,0,12);
// sheet.addMergedRegion(region1);
// CellRangeAddress region2=new CellRangeAddress(index-1,index,3,3);
// sheet.addMergedRegion(region2);
// CellRangeAddress region3=new CellRangeAddress(index-1,index,2,2);
// sheet.addMergedRegion(region3);
// CellRangeAddress region4=new CellRangeAddress(index-1,index,1,1);
// sheet.addMergedRegion(region4);
// CellRangeAddress region0=new CellRangeAddress(index-1,index,0,0);
// sheet.addMergedRegion(region0);
// CellRangeAddress region10=new
// CellRangeAddress(index-1,index,10,10);
// sheet.addMergedRegion(region10);
// }
}
// try {
// workbook.write(out);
// out.close();
// } catch (IOException e) {
// // TODO Auto-generated catch block
// e.printStackTrace();
// }
}
public String Workbooks(HttpServletRequest request,HSSFWorkbook workbook,String filename){
String fileurlString = null;
OutputStream out = null;
try {
String Excelpath = request.getSession().getServletContext().getRealPath("/");
String filepathString = Excelpath + “attachment” + “/” + filename;
fileurlString = “/”+ “attachment” + “/” + filename;
System.out.println(filepathString);
File outFile = new File(filepathString);
if (!outFile.getParentFile().exists()) {
outFile.getParentFile().mkdirs();
}
out = new FileOutputStream(outFile);
try {
workbook.write(out);
out.close();
} catch (IOException e) {
e.printStackTrace();
sendResult(new JsonResult(true, “fail”, “错误文件下载失败！！”, null));
}

	}catch (Exception e) {
		e.printStackTrace();
		sendResult(new JsonResult(true, "fail", "错误文件下载失败！！", null));
	}
	return fileurlString;
	
}

}

| |
|-package com.lm.tjhky.emp.common.excel;

import java.io.File;
import java.io.FileOutputStream;
import java.io.InputStream;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Date;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map.Entry;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.write.Label;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;

/**

@author : WH
@group : tgb8
@Date : 2014-1-2 下午9:13:21
@Comments : 导入导出Excel工具类
@Version : 1.0.0
*/

public class ExcelUtil {

/**
 * @MethodName  : listToExcel
 * @Description : 导出Excel（可以导出到本地文件系统，也可以导出到浏览器，可自定义工作表大小）
 * @param list      数据源
 * @param fieldMap      类的英文属性和Excel中的中文列名的对应关系
 * 如果需要的是引用对象的属性，则英文属性使用类似于EL表达式的格式
 * 如：list中存放的都是student，student中又有college属性，而我们需要学院名称，则可以这样写
 * fieldMap.put("college.collegeName","学院名称")
 * @param sheetName 工作表的名称
 * @param sheetSize 每个工作表中记录的最大个数
 * @param out       导出流
 * @throws ExcelException
 */ 
public static <T>  void   listToExcel ( 
        List<T> list , 
        LinkedHashMap<String,String> fieldMap, 
        String sheetName, 
        int sheetSize, 
        OutputStream out 
        ) throws ExcelException{ 
       
       
    if(list.size()==0 || list==null){ 
        throw new ExcelException("数据源中没有任何数据"); 
    } 
       
    if(sheetSize>65535 || sheetSize<1){ 
        sheetSize=65535; 
    } 
       
    //创建工作簿并发送到OutputStream指定的地方 
    WritableWorkbook wwb; 
    try { 
        wwb = Workbook.createWorkbook(out); 
           
        //因为2003的Excel一个工作表最多可以有65536条记录，除去列头剩下65535条 
        //所以如果记录太多，需要放到多个工作表中，其实就是个分页的过程 
        //1.计算一共有多少个工作表 
        double sheetNum=Math.ceil(list.size()/new Integer(sheetSize).doubleValue()); 
           
        //2.创建相应的工作表，并向其中填充数据 
        for(int i=0; i<sheetNum; i++){ 
            //如果只有一个工作表的情况 
            if(1==sheetNum){ 
                WritableSheet sheet=wwb.createSheet(sheetName, i); 
                fillSheet(sheet, list, fieldMap, 0, list.size()-1); 
               
            //有多个工作表的情况 
            }else{ 
                WritableSheet sheet=wwb.createSheet(sheetName+(i+1), i); 
                   
                //获取开始索引和结束索引 
                int firstIndex=i*sheetSize; 
                int lastIndex=(i+1)*sheetSize-1>list.size()-1 ? list.size()-1 : (i+1)*sheetSize-1; 
                //填充工作表 
                fillSheet(sheet, list, fieldMap, firstIndex, lastIndex); 
            } 
        } 
           
        wwb.write(); 
        wwb.close(); 
       
    }catch (Exception e) { 
        e.printStackTrace(); 
        //如果是ExcelException，则直接抛出 
        if(e instanceof ExcelException){ 
            throw (ExcelException)e; 
           
        //否则将其它异常包装成ExcelException再抛出 
        }else{ 
            throw new ExcelException("导出Excel失败"); 
        } 
    } finally {
	} 
           
} 
   
/**
 * @MethodName  : listToExcel
 * @Description : 导出Excel（可以导出到本地文件系统，也可以导出到浏览器，工作表大小为2003支持的最大值）
 * @param list      数据源
 * @param fieldMap      类的英文属性和Excel中的中文列名的对应关系
 * @param out       导出流
 * @throws ExcelException
 */ 
public static  <T>  void   listToExcel ( 
        List<T> list , 
        LinkedHashMap<String,String> fieldMap, 
        String sheetName, 
        OutputStream out 
        ) throws ExcelException{ 
       
    listToExcel(list, fieldMap, sheetName, 65535, out); 
       
} 
   
   
/**
 * @MethodName  : listToExcel
 * @Description : 导出Excel（导出到浏览器，可以自定义工作表的大小）
 * @param list      数据源
 * @param fieldMap      类的英文属性和Excel中的中文列名的对应关系
 * @param sheetSize    每个工作表中记录的最大个数
 * @param response  使用response可以导出到浏览器
 * @throws ExcelException
 */ 
public static  <T>  void   listToExcel ( 
        List<T> list , 
        LinkedHashMap<String,String> fieldMap, 
        String sheetName, 
        int sheetSize, 
        HttpServletResponse response  
        ) throws ExcelException{ 
       
    //设置默认文件名为当前时间：年月日时分秒 
    String fileName=new SimpleDateFormat("yyyyMMddhhmmss").format(new Date()).toString(); 
       
    //设置response头信息 
    response.reset();           
    response.setContentType("application/vnd.ms-excel");        //改成输出excel文件 
    response.setHeader("Content-disposition","attachment; filename="+fileName+".xls" ); 

    //创建工作簿并发送到浏览器 
    try { 
           
        OutputStream out=response.getOutputStream(); 
        listToExcel(list, fieldMap, sheetName, sheetSize,out ); 
           
    } catch (Exception e) { 
        e.printStackTrace(); 
           
        //如果是ExcelException，则直接抛出 
        if(e instanceof ExcelException){ 
            throw (ExcelException)e; 
           
        //否则将其它异常包装成ExcelException再抛出 
        }else{ 
            throw new ExcelException("导出Excel失败"); 
        } 
    } 
} 
/**
 * @MethodName  : listToExcel
 * @Description : 导出Excel（导出到浏览器，可以自定义工作表的大小）
 * @param list      数据源
 * @param fieldMap      类的英文属性和Excel中的中文列名的对应关系
 * @param sheetSize    每个工作表中记录的最大个数
 * @param response  使用response可以导出到浏览器
 * @return 
 * @throws ExcelException
 */ 
public static  <T>  String   listToExcel ( 
        List<T> list , 
        LinkedHashMap<String,String> fieldMap, 
        String sheetName, 
        int sheetSize, 
        HttpServletRequest request  
        ) throws ExcelException{ 
       
    //设置默认文件名为当前时间：年月日时分秒 
    String fileName=new SimpleDateFormat("yyyyMMddhhmmss").format(new Date()).toString()+".xls"; 
       
    String Excelpath = request.getSession().getServletContext().getRealPath("/");
    String filepathString = Excelpath  + "attachment" + "/"	+ fileName;
    String fileurlString = "/" + "attachment" + "/" + fileName;
	File outFile = new File(filepathString);
	if (!outFile.getParentFile().exists()) {
		outFile.getParentFile().mkdirs();
	}

    //创建工作簿并发送到浏览器 
    try { 
           
        OutputStream out= new FileOutputStream(outFile);
        listToExcel(list, fieldMap, sheetName, sheetSize,out ); 
           
    } catch (Exception e) { 
        e.printStackTrace(); 
           
        //如果是ExcelException，则直接抛出 
        if(e instanceof ExcelException){ 
            throw (ExcelException)e; 
           
        //否则将其它异常包装成ExcelException再抛出 
        }else{ 
            throw new ExcelException("导出Excel失败"); 
        } 
    } 
    return fileurlString;
}   
   
/**
 * @MethodName  : listToExcel
 * @Description : 导出Excel（导出到浏览器，工作表的大小是2003支持的最大值）
 * @param list      数据源
 * @param fieldMap      类的英文属性和Excel中的中文列名的对应关系
 * @param response  使用response可以导出到浏览器
 * @throws ExcelException
 */ 
public static <T>  void   listToExcel ( 
        List<T> list , 
        LinkedHashMap<String,String> fieldMap, 
        String sheetName, 
        HttpServletResponse response  
        ) throws ExcelException{ 
       
    listToExcel(list, fieldMap, sheetName, 65535, response); 
} 
   
/**
 * @MethodName          : excelToList
 * @Description             : 将Excel转化为List
 * @param in                    ：承载着Excel的输入流
 * @param sheetIndex        ：要导入的工作表序号
 * @param entityClass       ：List中对象的类型（Excel中的每一行都要转化为该类型的对象）
 * @param fieldMap          ：Excel中的中文列头和类的英文属性的对应关系Map
 * @param uniqueFields  ：指定业务主键组合（即复合主键），这些列的组合不能重复
 * @return                      ：List
 * @throws ExcelException
 */ 
public static <T>  List<T>  excelToList( 
        InputStream in, 
        String sheetName, 
        Class<T> entityClass, 
        LinkedHashMap<String, String> fieldMap, 
        String[] uniqueFields 
        ) throws ExcelException{ 
       
    //定义要返回的list 
    List<T> resultList=new ArrayList<T>(); 
       
    try { 
           
        //根据Excel数据源创建WorkBook 
        Workbook wb=Workbook.getWorkbook(in); 
        //获取工作表 
        Sheet sheet=wb.getSheet(sheetName); 
           
        //获取工作表的有效行数 
        int realRows=0; 
        for(int i=0;i<sheet.getRows();i++){ 
               
            int nullCols=0; 
            for(int j=0;j<sheet.getColumns();j++){ 
                Cell currentCell=sheet.getCell(j,i); 
                if(currentCell==null || "".equals(currentCell.getContents().toString())){ 
                    nullCols++; 
                } 
            } 
               
            if(nullCols==sheet.getColumns()){ 
                break; 
            }else{ 
                realRows++; 
            } 
        } 
           
           
        //如果Excel中没有数据则提示错误 
        if(realRows<=1){ 
            throw new ExcelException("Excel文件中没有任何数据"); 
        } 
           
           
        Cell[] firstRow=sheet.getRow(0); 

        String[] excelFieldNames=new String[firstRow.length]; 
           
        //获取Excel中的列名 
        for(int i=0;i<firstRow.length;i++){ 
            excelFieldNames[i]=firstRow[i].getContents().toString().trim(); 
        } 
           
        //判断需要的字段在Excel中是否都存在 
        boolean isExist=true; 
        List<String> excelFieldList=Arrays.asList(excelFieldNames); 
        for(String cnName : fieldMap.keySet()){ 
            if(!excelFieldList.contains(cnName)){ 
                isExist=false; 
                break; 
            } 
        } 
           
        //如果有列名不存在，则抛出异常，提示错误 
        if(!isExist){ 
            throw new ExcelException("Excel中缺少必要的字段，或字段名称有误"); 
        } 
           
           
        //将列名和列号放入Map中,这样通过列名就可以拿到列号 
        LinkedHashMap<String, Integer> colMap=new LinkedHashMap<String, Integer>(); 
        for(int i=0;i<excelFieldNames.length;i++){ 
            colMap.put(excelFieldNames[i], firstRow[i].getColumn()); 
        }  
           
           
           
        //判断是否有重复行 
        //1.获取uniqueFields指定的列 
        Cell[][] uniqueCells=new Cell[uniqueFields.length][]; 
        for(int i=0;i<uniqueFields.length;i++){ 
            int col=colMap.get(uniqueFields[i]); 
            uniqueCells[i]=sheet.getColumn(col); 
        } 
           
        //2.从指定列中寻找重复行

// for(int i=1;i<realRows;i++){
// int nullCols=0;
// for(int j=0;j<uniqueFields.length;j++){
// String currentContent=uniqueCells[j][i].getContents();
// Cell sameCell=sheet.findCell(currentContent,
// uniqueCells[j][i].getColumn(),
// uniqueCells[j][i].getRow()+1,
// uniqueCells[j][i].getColumn(),
// uniqueCells[j][realRows-1].getRow(),
// true);
//
// if(sameCell!=null){
// nullCols++;
// }
// }
//
// if(nullCols==uniqueFields.length){
// throw new ExcelException(“Excel中有重复行，请检查”);
// }
// }

        //将sheet转换为list 
        for(int i=1;i<realRows;i++){ 
            //新建要转换的对象 
            T entity=entityClass.newInstance(); 
               
            //给对象中的字段赋值 
            for(Entry<String, String> entry : fieldMap.entrySet()){ 
                //获取中文字段名 
                String cnNormalName=entry.getKey(); 
                //获取英文字段名 
                String enNormalName=entry.getValue(); 
                //根据中文字段名获取列号 
                int col=colMap.get(cnNormalName); 
                   
                //获取当前单元格中的内容 
                String content=sheet.getCell(col, i).getContents().toString().trim(); 
                   
                //给对象赋值 
                setFieldValueByName(enNormalName, content, entity); 
            } 
               
            resultList.add(entity); 
        } 
    } catch(Exception e){ 
        e.printStackTrace(); 
        //如果是ExcelException，则直接抛出 
        if(e instanceof ExcelException){ 
            throw (ExcelException)e; 
           
        //否则将其它异常包装成ExcelException再抛出 
        }else{ 
            e.printStackTrace(); 
            throw new ExcelException("导入Excel失败"); 
        } 
    } 
    return resultList; 
} 
   
   
   
   
   
/*<-------------------------辅助的私有方法----------------------------------------------->*/ 
/**
 * @MethodName  : getFieldValueByName
 * @Description : 根据字段名获取字段值
 * @param fieldName 字段名
 * @param o 对象
 * @return  字段值
 */ 
private static  Object getFieldValueByName(String fieldName, Object o) throws Exception{ 
       
    Object value=null; 
    Field field=getFieldByName(fieldName, o.getClass()); 
       
    if(field !=null){ 
        field.setAccessible(true); 
        value=field.get(o); 
    }else{ 
        throw new ExcelException(o.getClass().getSimpleName() + "类不存在字段名 "+fieldName); 
    } 
       
    return value; 
} 
   
/**
 * @MethodName  : getFieldByName
 * @Description : 根据字段名获取字段
 * @param fieldName 字段名
 * @param clazz 包含该字段的类
 * @return 字段
 */ 
private static Field getFieldByName(String fieldName, Class<?>  clazz){ 
    //拿到本类的所有字段 
    Field[] selfFields=clazz.getDeclaredFields(); 
       
    //如果本类中存在该字段，则返回 
    for(Field field : selfFields){ 
        if(field.getName().equals(fieldName)){ 
            return field; 
        } 
    } 
       
    //否则，查看父类中是否存在此字段，如果有则返回 
    Class<?> superClazz=clazz.getSuperclass(); 
    if(superClazz!=null  &&  superClazz !=Object.class){ 
        return getFieldByName(fieldName, superClazz); 
    } 
       
    //如果本类和父类都没有，则返回空 
    return null; 
} 
   
   
   
/**
 * @MethodName  : getFieldValueByNameSequence
 * @Description : 
 * 根据带路径或不带路径的属性名获取属性值
 * 即接受简单属性名，如userName等，又接受带路径的属性名，如student.department.name等
 * 
 * @param fieldNameSequence  带路径的属性名或简单属性名
 * @param o 对象
 * @return  属性值
 * @throws Exception
 */ 
private static  Object getFieldValueByNameSequence(String fieldNameSequence, Object o) throws Exception{ 
       
    Object value=null; 
       
    //将fieldNameSequence进行拆分 
    String[] attributes=fieldNameSequence.split("\\."); 
    if(attributes.length==1){ 
        value=getFieldValueByName(fieldNameSequence, o); 
    }else{ 
        //根据属性名获取属性对象 
        Object fieldObj=getFieldValueByName(attributes[0], o); 
        String subFieldNameSequence=fieldNameSequence.substring(fieldNameSequence.indexOf(".")+1); 
        value=getFieldValueByNameSequence(subFieldNameSequence, fieldObj); 
    } 
    return value;  
       
}  
   
   
/**
 * @MethodName  : setFieldValueByName
 * @Description : 根据字段名给对象的字段赋值
 * @param fieldName  字段名
 * @param fieldValue    字段值
 * @param o 对象
 */ 
private static void setFieldValueByName(String fieldName,Object fieldValue,Object o) throws Exception{ 
       
        Field field=getFieldByName(fieldName, o.getClass()); 
        if(field!=null){ 
            field.setAccessible(true); 
            //获取字段类型 
            Class<?> fieldType = field.getType();   
               
            //根据字段类型给字段赋值 
            if (String.class == fieldType) {   
                field.set(o, String.valueOf(fieldValue));   
            } else if ((Integer.TYPE == fieldType)   
                    || (Integer.class == fieldType)) {   
                field.set(o, Integer.parseInt(fieldValue.toString()));   
            } else if ((Long.TYPE == fieldType)   
                    || (Long.class == fieldType)) {   
                field.set(o, Long.valueOf(fieldValue.toString()));   
            } else if ((Float.TYPE == fieldType)   
                    || (Float.class == fieldType)) {   
                field.set(o, Float.valueOf(fieldValue.toString()));   
            } else if ((Short.TYPE == fieldType)   
                    || (Short.class == fieldType)) {   
                field.set(o, Short.valueOf(fieldValue.toString()));   
            } else if ((Double.TYPE == fieldType)   
                    || (Double.class == fieldType)) {   
                field.set(o, Double.valueOf(fieldValue.toString()));   
            } else if (Character.TYPE == fieldType) {   
                if ((fieldValue!= null) && (fieldValue.toString().length() > 0)) {   
                    field.set(o, Character   
                            .valueOf(fieldValue.toString().charAt(0)));   
                }   
            }else if(Date.class==fieldType){ 
                field.set(o, new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").parse(fieldValue.toString())); 
            }else{ 
                field.set(o, fieldValue); 
            } 
        }else{ 
            throw new ExcelException(o.getClass().getSimpleName() + "类不存在字段名 "+fieldName); 
        } 
} 
   
   
/**
 * @MethodName  : setColumnAutoSize
 * @Description : 设置工作表自动列宽和首行加粗
 * @param ws
 */ 
private static void setColumnAutoSize(WritableSheet ws,int extraWith){ 
    //获取本列的最宽单元格的宽度 
    for(int i=0;i<ws.getColumns();i++){ 
        int colWith=0; 
        for(int j=0;j<ws.getRows();j++){ 
            String content=ws.getCell(i,j).getContents().toString(); 
            int cellWith=content.length(); 
            if(colWith<cellWith){ 
                colWith=cellWith; 
            } 
        } 
        //设置单元格的宽度为最宽宽度+额外宽度 
        ws.setColumnView(i, colWith+extraWith); 
    } 
       
} 
   
/**
 * @MethodName  : fillSheet
 * @Description : 向工作表中填充数据
 * @param sheet     工作表 
 * @param list  数据源
 * @param fieldMap 中英文字段对应关系的Map
 * @param firstIndex    开始索引
 * @param lastIndex 结束索引
 */ 
private static <T> void fillSheet( 
        WritableSheet sheet, 
        List<T> list, 
        LinkedHashMap<String,String> fieldMap, 
        int firstIndex, 
        int lastIndex 
        )throws Exception{ 
       
    //定义存放英文字段名和中文字段名的数组 
    String[] enFields=new String[fieldMap.size()]; 
    String[] cnFields=new String[fieldMap.size()]; 
       
    //填充数组 
    int count=0; 
    for(Entry<String,String> entry:fieldMap.entrySet()){ 
        enFields[count]=entry.getKey(); 
        cnFields[count]=entry.getValue(); 
        count++; 
    } 
    //填充表头 
    for(int i=0;i<cnFields.length;i++){ 
        Label label=new Label(i,0,cnFields[i]); 
        sheet.addCell(label); 
    } 
       
    //填充内容 
    int rowNo=1; 
    for(int index=firstIndex;index<=lastIndex;index++){ 
        //获取单个对象 
        T item=list.get(index); 
        for(int i=0;i<enFields.length;i++){ 
            Object objValue=getFieldValueByNameSequence(enFields[i], item); 
            String fieldValue=objValue==null ? "" : objValue.toString(); 
            Label label =new Label(i,rowNo,fieldValue); 
            sheet.addCell(label); 
        } 
           
        rowNo++; 
    } 
       
    //设置自动列宽 
    setColumnAutoSize(sheet, 5); 
}
/**
 * @MethodName  : listToExcel
 * @Description : 导出Excel（导出到浏览器，工作表的大小是2003支持的最大值）
 * @param list      数据源
 * @param fieldMap      类的英文属性和Excel中的中文列名的对应关系
 * @param response  使用response可以导出到浏览器
 * @return 
 * @throws ExcelException
 */ 
public static <T>  String   listToExcel ( 
        List<T> list , 
        LinkedHashMap<String,String> fieldMap, 
        String sheetName, 
        HttpServletRequest request  
        ) throws ExcelException{ 
       
    return listToExcel(list, fieldMap, sheetName, 65535, request); 
}

}

-|–|

package com.lm.tjhky.emp.common.excel;

import java.net.URLEncoder;

import com.lm.tjhky.emp.common.base.Config;
public class Filepath {
public static String savePath(String rootpath,String filename) throws Exception
{
String url = “”;

	String filePath = rootpath+Config.ATTACHMENTFOLDER + "/";

	String outFileName = filename;

	url = filePath + URLEncoder.encode(outFileName, "UTF-8");
	
	return url;
}

}

package com.lm.tjhky.emp.common.excel;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;

import com.lm.tjhky.emp.common.base.Config;

public class InputStreamToFile {
/**
* 通过文件输入流转换成临时文件保存在项目中
* @param ins 文件输入流
* @param filepath 临时文件名称
* @return
*/
public static File getFileByInputStream(InputStream ins,String filepname){
OutputStream os = null;
File file = new File(Config.BASEPATH+"/attachment/"+filepname+System.currentTimeMillis()+".xls");
try {
os = new FileOutputStream(file);

		int bytesRead = 0;
		byte[] buffer = new byte[8192];
		while ((bytesRead = ins.read(buffer, 0, 8192)) != -1) {
			os.write(buffer, 0, bytesRead);
		}
	} catch (Exception e) {
		e.printStackTrace();
	}finally {
		try {
			os.close();
		} catch (IOException e) {
			e.printStackTrace();
		}
		try {
			ins.close();
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
	}
	return file;
}

}

| | |
@POST
@Path(“uploadIndustrialsource”)
@Consumes(MediaType.MULTIPART_FORM_DATA)
// @Produces(MediaType.APPLICATION_JSON)
public void uploadIndustrialSource(@FormDataParam(“industrialsourceaddfile”) InputStream fileInputStream) {

	if (CommonUtil.isEmpty(currentBackUser())) {
		sendResult(new JsonResult(true, "success", "请重新登录！" , null));
		return;
	}
	String filename = "工业过程源_"+currentBackUser().getUsername();
	
	File file = InputStreamToFile.getFileByInputStream(fileInputStream, filename);
	if (CommonUtil.isEmpty(file)) {
		sendResult(new JsonResult(true, "success", "文件上传失败，请重新上传！" , null));
		return;
	}
	System.out.println(file);
	
	//插入数据集
	List<HkyIndustrialsource> insertIndustrialsource = new LinkedList<HkyIndustrialsource>();
	//错误数据集
	List<HkyIndustrialsource> errerIndustrialsource = new LinkedList<HkyIndustrialsource>();
	int totalCount = 0;
	try {
		
		Workbook workbook = Workbook.getWorkbook(file);
		Sheet sheet = workbook.getSheet(0);
		totalCount = sheet.getRows() - 1;
		int row = 1;
		while (row < sheet.getRows()) {
			try {
				Cell[] cellset = sheet.getRow(row++);
				//序号(必填)
				String orderNumber = cellset[0].getContents();
				//统计年份(必填)
				String statisticalYear = cellset[1].getContents();
				//企业名称(必填)
				String enterpriseName = cellset[2].getContents();
				//区县(必填)
				String district = cellset[3].getContents();
				//地址
				String address = cellset[4].getContents();
				//中心经度(必填)
				String longitude = cellset[5].getContents();
				//中心纬度(必填)
				String latitude = cellset[6].getContents();
				//组织机构代码
				String organizationCode = cellset[7].getContents();
				//统一社会信用代码
				String unifiedsocialcreditCode = cellset[8].getContents();
				//源分类一级(必填)
				String sourceClassification1 = cellset[9].getContents();
				//行业类别(必填)
				String industryCategory = cellset[10].getContents();
				//行业代码(必填)
				String industryCode = cellset[11].getContents();
				//原辅材料名称(必填)
				String materialsName = cellset[12].getContents();
				 //原辅材料单位(必填)
				String materialsUnit = cellset[13].getContents();
				 //原辅材料使用量(必填)
				String materialsConsumption = cellset[14].getContents();
				 //产品名称(必填)
				String productName = cellset[15].getContents();
				//产品单位(必填)
				String productUnit = cellset[16].getContents();
				//产品产量(必填)
				String productOutput = cellset[17].getContents();
				//产污工艺名称
				String pollutionName = cellset[18].getContents();
				 //溶剂类型(必填)
				String solventType = cellset[19].getContents();
				 //源分类编码SCCs(必填)
				String sccs = cellset[20].getContents();
				//脱硫工艺(必填)(必填)
				String desulfurizationProcess = cellset[21].getContents();
				//综合脱硫效率(%)
				String desulfurizationEfficiency = cellset[22].getContents();
				//脱硝工艺(必填)
				String denitrationProcess = cellset[23].getContents();
				//综合脱硝效率(%)
				String denitrationEfficiency = cellset[24].getContents();
				//除尘工艺(必填)
				String dustremovalProcess = cellset[25].getContents();
				// PM2.5-10综合除尘效率(%)
				String pm2510Dustremoval = cellset[26].getContents();
				//PM2.5综合除尘效率(%)
				String pm25Dustremoval = cellset[27].getContents();
				//VOC治理工艺
				String vocTechnology = cellset[28].getContents();
				 //VOC综合去除效率
				String vocRemoval = cellset[29].getContents();
				//排气筒编号
				String exhaustNumber = cellset[30].getContents();
				 //排气筒高度(M)
				String exhaustHeight = cellset[31].getContents();
				//排气筒出口内径(M)
				String exhaustBore = cellset[32].getContents();
				 //烟气温度(°C)
				String fluegasTemperature = cellset[33].getContents();
			    //烟气出口流速(m/s)
				String fluegasStrength = cellset[34].getContents();
				
				//SO2排放量(吨)(必填)
				String so2 = cellset[35].getContents();
				//nox排放量(吨)(必填)
				String nox = cellset[36].getContents();
				//pm10排放量(吨)(必填)
				String pm10 = cellset[37].getContents();
				//pm25排放量(吨)(必填)
				String pm25 = cellset[38].getContents();
				//co排放量(吨)(必填)
				String co = cellset[39].getContents();
				//vocs排放量(吨)(必填)
				String vocs = cellset[40].getContents();
				//nh3排放量(吨)(必填)
				String nh3 = cellset[41].getContents();
				//oc排放量(吨)
				String oc = cellset[42].getContents();
				//bc排放量(吨)
				String bc = cellset[43].getContents();
				//co2排放量(吨)
				String co2 = cellset[44].getContents();
				//坐标类型(必填)
				String coordinateType = cellset[45].getContents();
				//更新日期(必填)
				String updateDate = cellset[46].getContents();
				//环统SO2排放量(吨)
				String so2Rs = cellset[47].getContents();
				//环统NOX排放量(吨)
				String noxRs = cellset[48].getContents();
				//环统烟粉尘排放量(吨)
				String smokedustRs = cellset[49].getContents();
				//环统VOCs排放量(吨)
				String vocsRs = cellset[50].getContents();
				//污普SO2排放量(吨)
				String so2Pp = cellset[51].getContents();
				//污普NOX排放量(吨)
				String noxPp = cellset[52].getContents();
				//污普烟粉尘排放量(吨)
				String smokedustPp = cellset[53].getContents();
				//污普VOCs排放量(吨)
				String vocsPp = cellset[54].getContents();
				//排污许可证编号
				String dischargeLicense = cellset[55].getContents();
				//备注
				String remarks = cellset[56].getContents();
				
				HkyIndustrialsource industrial = new HkyIndustrialsource();
				industrial.setOrderNumber(orderNumber);
				industrial.setStatisticalYear(statisticalYear);
				industrial.setEnterpriseName(enterpriseName);
				industrial.setDistrict(district);
				industrial.setAddress(address);
				industrial.setLongitude(longitude);
				industrial.setLatitude(latitude);
				industrial.setOrganizationCode(organizationCode);
				industrial.setUnifiedsocialcreditCode(unifiedsocialcreditCode);
				industrial.setSourceClassification1(sourceClassification1);
				industrial.setIndustryCategory(industryCategory);
				industrial.setIndustryCode(industryCode);
				industrial.setMaterialsName(materialsName);
				industrial.setMaterialsUnit(materialsUnit);
				industrial.setMaterialsConsumption(materialsConsumption);
				industrial.setProductName(productName);
				industrial.setProductUnit(productUnit);
				industrial.setProductOutput(productOutput);
				industrial.setPollutionName(pollutionName);
				industrial.setSolventType(solventType);
				industrial.setSccs(sccs);
				industrial.setDesulfurizationProcess(desulfurizationProcess);
				industrial.setDesulfurizationEfficiency(desulfurizationEfficiency);
				industrial.setDenitrationEfficiency(denitrationEfficiency);
				industrial.setDenitrationProcess(denitrationProcess);
				industrial.setDustremovalProcess(dustremovalProcess);
				industrial.setDustremovalProcess(dustremovalProcess);
				industrial.setPm2510Dustremoval(pm2510Dustremoval);
				industrial.setPm25Dustremoval(pm25Dustremoval);
				industrial.setVocTechnology(vocTechnology);
				industrial.setVocRemoval(vocRemoval);
				industrial.setExhaustNumber(exhaustNumber);
				industrial.setExhaustHeight(exhaustHeight);
				industrial.setExhaustBore(exhaustBore);
				industrial.setFluegasTemperature(fluegasTemperature);
				industrial.setFluegasStrength(fluegasStrength);
				industrial.setSo2(so2);
				industrial.setNox(nox);
				industrial.setPm10(pm10);
				industrial.setPm25(pm25);
				industrial.setCo(co);
				industrial.setVocs(vocs);
				industrial.setNh3(nh3);
				industrial.setOc(oc);
				industrial.setBc(bc);
				industrial.setCo2(co2);
				industrial.setCoordinateType(coordinateType);
				industrial.setUpdateDate(updateDate);
				industrial.setSo2Rs(so2Rs);
				industrial.setNoxRs(noxRs);
				industrial.setSmokedustRs(smokedustRs);
				industrial.setVocsRs(vocsRs);
				industrial.setSo2Pp(so2Pp);
				industrial.setNoxPp(noxPp);
				industrial.setSmokedustPp(smokedustPp);
				industrial.setVocsPp(vocsPp);
				industrial.setDischargeLicense(dischargeLicense);
				industrial.setRemarks(remarks);
				
				
				if (CommonUtil.isEmpty(orderNumber) || 
						CommonUtil.isEmpty(statisticalYear) || 
						CommonUtil.isEmpty(enterpriseName) || 
						CommonUtil.isEmpty(district) ||
						CommonUtil.isEmpty(longitude) || 
						CommonUtil.isEmpty(latitude) || 
						CommonUtil.isEmpty(sourceClassification1) ||
						CommonUtil.isEmpty(industryCategory) || 
						CommonUtil.isEmpty(industryCode) ||
						CommonUtil.isEmpty(materialsName) || 
						CommonUtil.isEmpty(materialsUnit) || 
						CommonUtil.isEmpty(materialsConsumption) || 
						CommonUtil.isEmpty(productName) ||
						CommonUtil.isEmpty(productUnit) || 
						CommonUtil.isEmpty(productOutput) ||
						CommonUtil.isEmpty(solventType) ||
						CommonUtil.isEmpty(sccs) || 
						CommonUtil.isEmpty(desulfurizationProcess) ||
						CommonUtil.isEmpty(denitrationProcess) ||
						CommonUtil.isEmpty(dustremovalProcess) ||
						CommonUtil.isEmpty(so2) || 
						CommonUtil.isEmpty(nox) || 
						CommonUtil.isEmpty(pm10) || 
						CommonUtil.isEmpty(pm25) || 
						CommonUtil.isEmpty(co) || 
						CommonUtil.isEmpty(vocs) || 
						CommonUtil.isEmpty(nh3) || 
						CommonUtil.isEmpty(coordinateType) || 
						CommonUtil.isEmpty(updateDate)) {
					
					errerIndustrialsource.add(industrial);
				}
				if (errerIndustrialsource.size() == 0) {
					insertIndustrialsource.add(industrial);
				}
				
				
			} catch (Exception e) {
				workbook.close();
				sendResult(new JsonResult(true, "fail", "文件解析异常！请检查文件格式是否正确！1", null));
				return;
			}
		}
		
	} catch (Exception e) {
		e.printStackTrace();
		sendResult(new JsonResult(true, "fail", "文件解析异常！请检查文件格式是否正确！2", null));
		return;
	}
	if (errerIndustrialsource.size() == 0 && insertIndustrialsource.size() != 0) {
		totalCount= industrialsourceServer.insertSelective(insertIndustrialsource);
		if (totalCount != 0 ) {
			sendResult(new JsonResult(true, "success", "文件上传成功！共"+totalCount+"条数据" , null));
			return;
		}else {
			sendResult(new JsonResult(true, "fail", "文件解析异常！请重新上传！", null));
			return;
		}
	}else if(errerIndustrialsource.size() != 0){
		HSSFWorkbook workbook1 = new HSSFWorkbook();
		ExportExcel<HkyIndustrialsource> tex  = new ExportExcel<HkyIndustrialsource>(); 
		String[] headers = { "序号（必填）","统计年份（必填）","企业名称（必填）",
				"区县（必填）","地址（必填）","中心经度（必填）",
				"中心纬度（必填）","组织机构代码","统一社会信用代码",
				"源分类一级(必填)","行业类别(必填)","行业代码(必填)","原辅材料名称(必填)",
				"原辅材料单位(必填)","原辅材料使用量(必填)","产品名称(必填)","产品单位(必填)","产品产量(必填)",
				"产污工艺名称","溶剂类型(必填)","源分类编码SCCs(必填)","脱硫工艺(必填)",
				"综合脱硫效率(%)","脱硝工艺(必填)","综合脱硝效率(%)","除尘工艺(必填)","PM2.5-10综合除尘效率(%)",
				"PM2.5综合除尘效率(%)","VOC治理工艺","VOC综合去除效率",
				"排气筒编号","排气筒高度(M)","排气筒出口内径(M)","烟气温度(°C)","烟气出口流速(m/s)","SO2排放量(吨)(必填)",
				"NOX排放量(吨)(必填)",
				"PM10排放量(吨)(必填)","PM25排放量(吨)(必填)","CO排放量(吨)(必填)","VOCs排放量(吨)(必填)","NH3排放量(吨)(必填)",
				"OC排放量(吨)","BC排放量(吨)","坐标类型(必填)","更新日期（必填）","环统SO2排放量(吨)",
				"环统NOX排放量(吨)","环统烟粉尘排放量(吨)","环统VOCs排放量(吨)" ,
				"污普SO2排放量(吨)","污普NOX排放量(吨)","污普烟粉尘排放量(吨)","污普VOCs排放量(吨)","排污许可证编号","备注"};
		String sheet1 = "Sheet1";
		tex.exportExcelwithoutCellRangeAddress(workbook1,sheet1,headers, errerIndustrialsource);
		ExportExcel<HkyIndustrialsource> exportExcel = new ExportExcel<HkyIndustrialsource>();
		String fileurlString = exportExcel.Workbooks(request,workbook1,"固定燃烧源工业过程源_错误数据表.xls");
		System.out.println("url:"+fileurlString);
		if (errerIndustrialsource.size() != 0) {
			sendResult(new JsonResult(false, "fail", "上传失败！错误数据   "+errerIndustrialsource.size()+"  条！", fileurlString));
			return;
		}else {
			sendResult(new JsonResult(true, "fail", "文件解析异常！请检查文件格式是否正确！3", null));
			return;
		}
	}else {
		sendResult(new JsonResult(true, "fail", "文件解析异常！请检查文件格式是否正确！", null));
		return;
	}
}

前台页面

上传文件

<form method="post" id="uploadfileform" class="form-horizontal"
	onsubmit="javascript:return false;" enctype="multipart/form-data" >
		<!-- 添加元素 -->
			<div class="form-group">
				<label for="uploadfile" class="col-md-2 control-label input_on " >
					<em class="required_em">*</em> 上传文件：
				</label>
				<div class="col-md-8">
					 <input id="electricboileradd_file" type="file" name="electricboileraddfile" >
				</div>
			</div>
	  <div class="form_buttons">
		<input class="form_submit button" id="save" type="submit" value="提交" />
	</div>
</form>

简单的导入功能