JAVA Excel导入导出

import net.sf.jxls.exception.ParsePropertyException;
import net.sf.jxls.transformer.XLSTransformer;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.InputStream;
import java.io.UnsupportedEncodingException;
import java.util.List;
import java.util.Map;public class ExcelTemplateUtils {
/**
*
* @param filename 导出的文件名
* @param dataMap List的集合
* @param model
* @param response
* @throws IOException
*/
@SuppressWarnings("rawtypes")
public void export(String filename, Map<String,List>dataMap, String model, HttpServletResponse response){
XLSTransformer transformer = new XLSTransformer();
HSSFWorkbook workbook;
InputStream in;
try {
//此方法需要类与模板放在同一目录下
in = Thread.currentThread().getContextClassLoader().getResourceAsStream(model);
// in = getClass().getResourceAsStream(model);
workbook = transformer.transformXLS(in, dataMap);
outExcel(workbook, response, filename);
} catch (Exception e) {
e.printStackTrace();
}
} protected void outExcel(HSSFWorkbook workbook, HttpServletResponse response,
String filename) throws UnsupportedEncodingException {
response.setContentType("application ns.ms-excel");
response.setHeader("Expires", "0");
response.setHeader("Cache-Control",
"must-revalidate, post-check=0, pre-check=0");
response.setHeader("Pragma", "public");
response.setHeader("Content-disposition", "attachment;filename="+new String(filename.getBytes("utf-8"),"iso-8859-1")+".xls"); try {
workbook.write(response.getOutputStream());
} catch (ParsePropertyException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
}
}

package com.glory.venus.common.utils;import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.util.ArrayList;
import java.util.List;import org.apache.poi.EncryptedDocumentException;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DateUtil;
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.ss.usermodel.WorkbookFactory;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;/**
* @author me
* @date 2017-06-08 16:29:25
*/
public class ExcelUtil { /**
* 生成2007及以后版本
* @param list 数据集合
* @param title 表头 表头名称的顺序要与list里的object属性的顺序对应
* @param sheetName sheet名称
* @return
*/
public static Workbook getXSSFWorkbook(List<Object> list,String[] title,String sheetName){
Workbook wb = new XSSFWorkbook();//2007及以后版本 .xslx
return createExcel(wb,list,title,sheetName,0,0);
} /**
* 2003及以前的版本
* @param list 数据集合
* @param title 表头 表头名称的顺序要与list里的object属性的顺序对应
* @param sheetName sheet名称
* @return
*/
public static Workbook getHSSFWorkbook(List<Object> list,String[] title,String sheetName){
Workbook wb = new HSSFWorkbook();//2003及以前的版本 .xsl
return createExcel(wb,list,title,sheetName,0,0);
} /**
* 生成Excel文件
* @param list 数据集合
* @param title 表头
* @param sheetName sheet名称
* @param rowNum 从第几行开始写入
* @param colNum 从第几列开始写入
* @return
*/
private static Workbook createExcel(Workbook wb,List list,String[] title,String sheetName,int rowNum,int colNum) {
if(sheetName == null || "".equals(sheetName)){
sheetName = "sheet1";
}
Sheet sheet = wb.createSheet(sheetName);
Row topRow = sheet.createRow(rowNum);
for (int i = 0; i < title.length; i++) {
sheet.autoSizeColumn(i,true);
topRow.createCell(i+colNum).setCellValue(title[i]);
}
if(list == null || list.size() == 0){
return wb;
}
Object[] objs = null;
for (int i = 0; i < list.size(); i++) {
rowNum++;
Row row = sheet.createRow(rowNum);
objs = getFieldValues(list.get(i));
for (int j = 0; j < objs.length; j++) {
String value = objs[j] == null ? "" : objs[j].toString();
row.createCell(j+colNum).setCellValue(value);
}
}
return wb;
} /**
* 获取对象属性的值
* @param o
* @return
*/
private static Object[] getFieldValues(Object o) {
Object[] value = null;
try {
Field[] fields = o.getClass().getDeclaredFields();
value = new Object[fields.length];
for (int i = 0; i < fields.length; i++) {
String firstLetter = fields[i].getName().substring(0, 1).toUpperCase();
String getter = "get" + firstLetter + fields[i].getName().substring(1);
Method method = o.getClass().getMethod(getter, new Class[] {});
value[i] = method.invoke(o, new Object[] {});
}
} catch (SecurityException e) {
e.printStackTrace();
} catch (NoSuchMethodException e) {
e.printStackTrace();
} catch (IllegalAccessException e) {
e.printStackTrace();
} catch (IllegalArgumentException e) {
e.printStackTrace();
} catch (InvocationTargetException e) {
e.printStackTrace();
} return value;
} /**
* 获取对象属性名称
* @param o
* @return
*/
private static String[] getFieldValue(Object o) {
Field[] fields = o.getClass().getDeclaredFields();
String[] value = new String[fields.length];
for (int i = 0; i < fields.length; i++) {
value[i] = fields[i].getName();
}
return value;
} /**
* 读取Excel
* @param is Excel的文件流
* @return
*/
public static ExcelCellBean readExcel(InputStream is){
Workbook workbook = null;
Sheet sheet = null;
try {
workbook = WorkbookFactory.create(is);
sheet = workbook.getSheetAt(0); //读取第几个工作表sheet
int rowNum = sheet.getLastRowNum();//有多少列
ExcelCellBean eb = new ExcelCellBean();
String[] title = new String[0];
String[] value = new String[0];
List<String[]> values = new ArrayList<String[]>();
int c = 0;//用去区分表头和数值
int colNum = 0;//总共有多少列
int firstCellNum = 0;//第几列开始读取
for(int k = 0;k <= rowNum;k++){
Row row = sheet.getRow(k);//从第几行开始读
if(row == null){//过滤空行
continue;
}
if(c == 0){
firstCellNum = row.getFirstCellNum();
colNum = row.getLastCellNum() - row.getFirstCellNum();
title = new String[colNum];
}else{
value = new String[colNum];
}
int count = 0;//计算放进数值里面的顺序
for (int i = firstCellNum; i < colNum+firstCellNum; i++) {
if(c == 0){
title[count] = getCellFormatValue(row.getCell(i));
}else{
value[count] = getCellFormatValue(row.getCell(i));
}
count++;
}
if(c > 0){
values.add(value);
}
c++;
}
eb.setTitle(title);
eb.setValues(values);
return eb;
} catch (IOException e) {
e.printStackTrace();
} catch (EncryptedDocumentException e) {
e.printStackTrace();
} catch (InvalidFormatException e) {
e.printStackTrace();
}
return null;
} /**
* 从cell获取值
* @param cell
* @return
*/
private static String getCellFormatValue(Cell cell) {
String cellvalue = "";
if(cell != null){
switch (cell.getCellTypeEnum()) {
case NUMERIC:
if(DateUtil.isCellDateFormatted(cell)){//日期类型
cellvalue = cell.getDateCellValue() + "";
}else{
cellvalue = String.valueOf(cell.getNumericCellValue());
}
break;
case BOOLEAN:
cellvalue = cell.getBooleanCellValue() + "";
break;
case FORMULA:
cellvalue = "公式";
break;
case ERROR:
cellvalue = cell.getErrorCellValue() + "";
break;
// case BLANK://空白
// case STRING:
default:
cellvalue = cell.getStringCellValue();
break;
}
}
return cellvalue;
}}

@Bean(name = "multipartResolver")
public CommonsMultipartResolver commonsMultipartResolver() {
logger.info("CommonsMultipartResolver");
return new CommonsMultipartResolver();
}

@RequestMapping(value = "/sport/export", method = RequestMethod.GET)
public void sportExport(HttpServletResponse response) throws IOException {
String[] titles = new String[]{"模板编码", "运动名称", "运动类型", "运动时间", "运动频率", "运动周期", "运动强度", "创建时间", "创建人"};
String sheetName = "运动模板";
List list = service.findAllTemplateSportExcel();
Workbook workbook = ExcelUtil.getXSSFWorkbook(list, titles, sheetName);
OutputStream output = response.getOutputStream();
response.reset();
response.setContentType("application/vnd.ms-excel");
response.setHeader("Content-disposition", "attachment;filename=" + java.net.URLEncoder.encode(sheetName + ".xls", "UTF-8"));
workbook.write(output);
output.close();
} @RequestMapping(value = "/sport/import", method = RequestMethod.POST)
public ResponseBean sportImport(HttpServletRequest request) throws IOException {
MultipartHttpServletRequest multipartRequest = (MultipartHttpServletRequest)request;
Long creator=1l;//// TODO: 2017-06-27 获取管理员ID
Iterator<String> iterator = multipartRequest.getFileNames();
while(iterator.hasNext()){
MultipartFile multipartFile = multipartRequest.getFile(iterator.next());
if(multipartFile != null){
ExcelCellBean bean=ExcelUtil.readExcel(multipartFile.getInputStream());
return service.importTemplateSport(creator,bean);
}
}
return new ResponseBean(-1,"获取上传文件失败");
}