JAVA Excel导入导出

--------------------------------------------方式一（新）-----------------------------------------------------------------

这个导出excel是需要excel模板文件，这个方式的好处是可以自定义excel的表头，方式如下：

1.复制该工具类

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();  
            }  
        }  
}

2.自定义excel模板文件：（不能插入excel文件，所以截图了，如图所示）

3.导出请求代码：

@GetMapping("/export")
    public void excelTemplate(String startDate, String endDate, String email, HttpServletResponse response) {
        FinancialReq req = new FinancialReq();
        req.setEmail(email);
        req.setStartDate(startDate);
        req.setEndDate(endDate);
        List list  = adminFinancialService.findExcelList(req);
        Map<String, List> beanParams = new HashMap<String, List>();
        beanParams.put("financialSummary", list);
        ExcelTemplateUtils excel = new ExcelTemplateUtils();

        StringBuilder title = new StringBuilder();
        title.append("财务统计报表");
        title.append(DateUtils.formatDate(new Date(),"yyyyMMddhhmmss"));
        excel.export(title.toString(), beanParams, "template_financialSummary.xls", response);
    }

View Code

 

--------------------------------------------方式二（旧）----------------------------------------------------------------

导入架包：

gradle（

"org.apache.poi:poi-ooxml:3.16",

）

下面给出一个Excel导入导出的工具类:

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;
    }

}

备注;设计到文件的上传，需要的jar：

gradle(
"commons-fileupload:commons-fileupload:1.3.1",""
)
另外 还需要配置一个bean ：不然强转 request为MultipertRequest的时候  会出错

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

 后台 导入、导出Excel 请求 可参考一下代码：

 @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,"获取上传文件失败");
    }

 

转载于:https://www.cnblogs.com/dwb91/p/7085664.html