js java 导入 导出

一、导入
1、js

 /*导入*/
              upload.render({
                //允许上传的文件后缀
                elem: '#uploadEventFile'
                ,url: '/billopenb/importExcel'
                ,accept: 'file'
                ,exts: 'xlsx|xls'
                ,method: 'post'
                ,field: 'file'
                ,data: {
                  istax: function(){
                    var istax=$('input:radio[name="tax"]:checked').val();
                    if(istax=='含税'){
                      sfhs=1;
                    }else if(istax=='不含税'){
                      sfhs=0;
                    }
                    return sfhs;
                  }
                }
                ,before: function(){


                }
                ,headers:{"token":localStorage.token}
                ,done: function(res) {
                  if (res.success == true) {
                    console.log("导入成功");
                    //导入成功，先清本地数据
                    dataList[curId].splice(0,dataList[curId].length);
                    dataList[curId].push(JSON.parse(JSON.stringify(trData)));


                    //list=list.concat(res.body.vtBillopenb)
                    list = res.body.vtBillopenb;
                    // var list = res.body.vtBillopenb;
                    for (var i = 0;i<list.length;i++) {
                      list[i].rowno = i+1;
                      // list[i].taxrate = list[i].taxrate;
                    }
                    //如果子表>8行主表清单标识为1，否则清单标识为0
                    initForm(list);
                    dataList[curId] = list;
                    sessionStorage.setItem('spList',JSON.stringify(list));

                  }else{
                    layer.alert(res.msg);
                  }


                }
                ,error: function(res){
                  layer.msg('上传失败,请您检查该文件格式/模板是否正确。');
                }
              })

2、java
controller

@ResponseBody
    @RequestMapping(value = "billopenb/importExcel", method = RequestMethod.POST)
    public AjaxJson importExcel(@RequestParam("file") MultipartFile file,
            @ApiParam("含税标识 0否 1是") @RequestParam(required = true, value = "istax") String istax) throws Exception {

        List<Map<String, Object>> result = vtbillopenBService.readExcelFile(file, istax);
        return AjaxJson.ok("导入成功！").put("vtBillopenb", result);
    }

导入类

package com.vtax.utils.importUtils;

import java.io.IOException;
import java.io.InputStream;
import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.poi.hssf.usermodel.HSSFDateUtil;
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;
import org.springframework.web.multipart.MultipartFile;
/**
 * 
 * @ClassName:ReadExcel
 * @Description:
 * @author: jinpp
   @date: 2018年7月11日 下午2:33:10
 */
public class ReadExcel {
    // 总行数
    private int totalRows = 0;
    // 总条数
    private int totalCells = 0;
    // 错误信息接收器
    private String errorMsg;

    // 构造方法
    public ReadExcel() {
    }

    // 获取总行数
    public int getTotalRows() {
        return totalRows;
    }

    // 获取总列数
    public int getTotalCells() {
        return totalCells;
    }

    // 获取错误信息
    public String getErrorInfo() {
        return errorMsg;
    }

    /**
     * 读EXCEL文件，获取信息集合
     * 
     * @param fielName
     * @return
     */
    public List<Map<String, Object>> getExcelInfo(MultipartFile mFile,String[] fields) {
        String fileName = mFile.getOriginalFilename();// 获取文件名

        try {
            if (!validateExcel(fileName)) {// 验证文件名是否合格
                return null;
            }
            boolean isExcel2003 = true;// 根据文件名判断文件是2003版本还是2007版本
            if (isExcel2007(fileName)) {
                isExcel2003 = false;
            }
            return createExcel(mFile.getInputStream(), isExcel2003,fields);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }

    /**
     * 根据excel里面的内容读取客户信息
     * 
     * @param is      输入流
     * @param isExcel2003   excel是2003还是2007版本
     * @return
     * @throws IOException
     */
    public List<Map<String, Object>> createExcel(InputStream is, boolean isExcel2003,String[] fields) {
        try {
            Workbook wb = null;
            if (isExcel2003) {// 当excel是2003时,创建excel2003
                wb = new HSSFWorkbook(is);
            } else {// 当excel是2007时,创建excel2007
                wb = new XSSFWorkbook(is);
            }
            return readExcelValue(wb,fields);// 读取Excel里面客户的信息
        } catch (IOException e) {
            e.printStackTrace();
        }
        return null;
    }

    /**
     * 读取Excel里面客户的信息
     * 
     * @param wb
     * @return
     */
    private List<Map<String, Object>> readExcelValue(Workbook wb,String[] fields) {
        // 得到第一个shell
        Sheet sheet = wb.getSheetAt(0);
        // 得到Excel的行数
        this.totalRows = sheet.getPhysicalNumberOfRows();
        // 得到Excel的列数(前提是有行数)
        if (totalRows > 1 && sheet.getRow(0) != null) {
            this.totalCells = sheet.getRow(0).getPhysicalNumberOfCells();
        }
        List<Map<String, Object>> userList = new ArrayList<Map<String, Object>>();

        // 循环Excel行数
        for (int r = 3; r < totalRows; r++) {
            Row row = sheet.getRow(r);

            if (row == null) {
                continue;
            }
            // 循环Excel的列
            Map<String, Object> map = new HashMap<String, Object>();
            for (int c = 0; c < this.totalCells; c++) {
                Cell cell = row.getCell(c);
                if (null != cell) {
                    row.getCell(c).setCellType(Cell.CELL_TYPE_STRING);
                    map.put(fields[c], getValue(row.getCell(c)));
                   }
            }
            // 添加到list
              userList.add(map);

                   }
        return userList;
    }
    // 判断单元格的类型
    public static String getValue(Cell cell) {
        String value = "";
        if (null == cell) {
            return value;
        }
        switch (cell.getCellType()) {
        // 数值型
        case Cell.CELL_TYPE_NUMERIC:
            if (HSSFDateUtil.isCellDateFormatted(cell)) {
                // 如果是date类型则 ，获取该cell的date值
                Date date = HSSFDateUtil.getJavaDate(cell.getNumericCellValue());
                SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");
                value = format.format(date);
            } else {// 纯数字
                BigDecimal big = new BigDecimal(String.valueOf(cell.getNumericCellValue()));
                // 科学计数法
                value = big.toPlainString();
                // 解决1234.0 去掉后面的.0
                if (null != value && !"".equals(value.trim())) {
                    String[] item = value.split("[.]");
                    if (1 < item.length && "0".equals(item[1])) {
                        value = item[0];
                    }
                }
            }
            break;
        // 字符串类型
        case Cell.CELL_TYPE_STRING:
            value = cell.getStringCellValue();
            break;
        // 布尔类型
        case Cell.CELL_TYPE_BOOLEAN:
            value = " " + cell.getBooleanCellValue();
            break;
        default:
            value = cell.getStringCellValue();
        }
        //TODO 重复且 可能报错吧 建议注释掉  by wangd
        /*if ("null".endsWith(value.trim())) {
            value = "";
        }*/
        return value == null ? "" : value.trim();
    }
    /**
     * 验证EXCEL文件
     * 
     * @param filePath
     * @return
     */
    public boolean validateExcel(String filePath) {
        if (filePath == null || !(isExcel2003(filePath) || isExcel2007(filePath))) {
            errorMsg = "文件名不是excel格式";
            return false;
        }
        return true;
    }

    // @描述：是否是2003的excel，返回true是2003
    public static boolean isExcel2003(String filePath) {
        return filePath.matches("^.+\\.(?i)(xls)$");
    }

    // @描述：是否是2007的excel，返回true是2007
    public static boolean isExcel2007(String filePath) {
        return filePath.matches("^.+\\.(?i)(xlsx)$");
    }

}

二、导出本地模板

<div class="importDiv">
        <button type="button" class="layui-btn layui-btn-sm" id="downloadEventTemplate" onclick="downloadTemplate()">
            <i class="layui-icon">&#xe67c;</i>导出Excel模板
        </button>
    </div>

  //下载Excel导入模板
  window.downloadTemplate=function () {
    var istaxfalg=$('input:radio[name="tax"]:checked').val();
    var istax='';
    if(istaxfalg=='含税'){
      istax=1;
    }else if(istaxfalg=='不含税'){
      istax=0;
    }
    location.href="/billopenb/exportExcelTemplate?istax="+istax;

  }

//导出excel模板
    @ResponseBody
    @RequestMapping(value = "billopenb/exportExcelTemplate", method = RequestMethod.GET)
    public void exportExcelTemplate(HttpServletRequest request,HttpServletResponse response,@ApiParam("含税标识 0否 1是") @RequestParam(required=true,value="istax") String istax) throws Exception {
        String filename="不含税发票开具商品行导入模板.xlsx";
        if(istax.equals("1")) {
            filename="含税发票开具商品行导入模板.xlsx";
        }
        vtbillopenBService.exportExcelFile(request,response,filename);
    }

@Override
    public void exportExcelFile(HttpServletRequest request, HttpServletResponse response, String filename)
            throws Exception {
        // 判断文件后缀名是xls，还是xlsx
        // 如果是xls，使用HSSFWorkbook；如果是xlsx，使用XSSFWorkbook
        XSSFWorkbook book = new XSSFWorkbook(ClassUtils.getDefaultClassLoader().getResourceAsStream(filename));
        // 清空response
        response.reset();
        // 设置response的Header
        response.setCharacterEncoding("UTF-8");
        // ①指定响应数据的内容类型
        response.setContentType("application/vnd.ms-excel;charset=utf-8");
        // ②指定响应数据的文件名
        response.setHeader("Content-Disposition", "Attachment;Filename=" + URLEncoder.encode(filename, "utf-8"));
        book.write(response.getOutputStream());
        book.close();
    }