ajax(FormData)的poi下载excel模板与excel上传解析(maven项目与非maven项目)(poi-ooxml与poi3.9

自己项目中遇到了,写了挺久的,花了挺多时间的来找相关资料,这次来份详细的上传,包括poi-ooxml(maven项目)

下载都一样没有区别

excel 下载模板,其实预设内容  (这个下载没写好,用户无法选定保存路径) :

前台不写了就是一个普通的请求

@RequestMapping(value = "downloadTemp", method = RequestMethod.POST)
public void downloadTemp(HttpServletResponse response) {
    response.setContentType("application/x-download");
    response.setHeader("Pragma", "public");
    response.setHeader("Cache-Control", "max-age=0");
    response.setHeader("Content-Disposition", "attachment; filename=Temp.xlsx");//以上都是设置响应,以下是设置模板
    Workbook wb;
    Row row;
    Cell cell;
    Sheet sheet;
    try {
        wb = new XSSFWorkbook();
        sheet = wb.createSheet();
        row = sheet.createRow(0);
        cell = row.createCell(0);
        cell.setCellValue("订单号");
        cell = row.createCell(1);
        cell.setCellValue("物流单号");
        sheet.autoSizeColumn(1, true);
        wb.write(response.getOutputStream());//响应
        LOG.info("下载发货excel模板成功");
    } catch (Exception e) {
        LOG.error("下载发货excel模板出错", e);
    }

之前做过普通项目的下载,现在做的是ajax的maven项目

上传poi-ooxml :   (以下代码做过一些修改,删除了与我项目有关的操作都很简单的)

前台:

html:

<form class="hidden" id="uploadTemp" method="post" enctype="multipart/form-data">
    <input id="uploadTemp_data" name="uploadFile" type="file" class="hidden">

	//这里还可以在携带其他的表单参数
</form>

js(ajax):

var $uploadTemp_input = $("#uploadTemp_data"); 

//判断选择文件是否已选择
var file = $uploadTemp_input.get(0).files[0];	/获取到选择的文件
if (!file || file == '') {
    butil.alert("操作失败", "还未选择文件", "error");
    console.log("文件类型错误");
    return;
}
//判断选择文件类型
var fileName = file.name;
var index = fileName.lastIndexOf('.');
var fix = fileName.substring(index + 1);
if ((fix != "xls" && fix != "xlsx")) {
    butil.alert("操作失败", "文件类型选择错误", "error");
    console.log("文件类型错误");
    return;
}
            var formData = new FormData($("#uploadTemp").get(0));	//获取到form表单然后ajax发送请求
            window.setTimeout(function () {
                $.ajax({
                    url: $rootScope.serverUrls.orderUrl + "/order/uploadTemp",
                    type: 'post',
                    data: formData,
                    async: false,
                    cache: false,
                    contentType: false,
                    processData: false,
                    success: function (record) {
 
                        console.info(record);
                    },
                    error: function (data) {

                        console.info(data);
                    }
                });
            }, 100);

        }
    }
});

后台服务器(ajax的)

@RequestMapping(value = "uploadTemp", method = RequestMethod.POST)
@ResponseBody
public OrderSeedRecord uploadTemp(HttpServletRequest request, MultipartFile uploadFile) {
   
    if (request.getContentType().indexOf("multipart/form-data") >= 0) {
        InputStream in = null;
        try {
            in = uploadFile.getInputStream();
            Workbook workbook = WorkbookFactory.create(in);
            Sheet sheet = workbook.getSheetAt(0);   //暂时只做第一个sheet的读取
            int lastNum = sheet.getLastRowNum();    //最后一行的index
            Row row;
            for (int i = 1; i <= lastNum; i++) {        //从excel取数据
                row = sheet.getRow(i);
       
                    String orderNo = ExcelPoiFormat.getCellValue(row.getCell(0));  //第一列
                    String logisticsNo = ExcelPoiFormat.getCellValue(row.getCell(1));//第二列
                                
                }
}

            LOG.info("excel转换数据完成:{}");
        } catch (Exception e) {
            LOG.error("excel转换成数据,发生错误", e);
        } finally {
            if (in != null) {
                try {
                    in.close();
                } catch (IOException e) {
                    LOG.error("关闭流发生错误", e);
                }
            }
        }
    }
    return record;
}

后台解析时,格式问题:

public class ExcelPoiFormat {

   private static DecimalFormat df = new DecimalFormat("0");

    public static String getCellValue(Cell cell) {

        String value = "";
        if (cell != null) {
            int cellType = cell.getCellType();
            switch (cellType) {
                case 0:            //表示数值
                    value =  df.format(cell.getNumericCellValue());
                    break;
                case 1:            //表示字符串
                    value = cell.getStringCellValue();
                    break;
                case 3:            //表示空白
                    value = "";
                    break;
                case 4:            //表示boolean
                    value = cell.getBooleanCellValue() + "";
                    break;
                case 5:            //表示错误
                   // value = cell.getErrorCellValue() + "";
                    value = ""; //用空串
                    break;
                default:        //表示其他
                    value = "";
                    break;
            }
        } else {
            value = "";
        }
        return value.trim();
    }
}

poi3.9的 这个做不是ajax的

前台就是一个表单提交

@RequestMapping("/addServiceAccountMany")
public String insertServiceAccountMany(HttpServletRequest request,
@RequestParam("upLoadFile") CommonsMultipartFile upLoadFile) {
List<ServiceAccountBean> list = new ArrayList<ServiceAccountBean>();

int j = 1;


// 文件输入流
InputStream fis = null;


// 取得客户端上传的数据类型
String contentType = request.getContentType();


// 第几个sheet
int number = 1;


// 判断内容类型
if (contentType.indexOf("multipart/form-data") >= 0) {
try {


fis = upLoadFile.getFileItem().getInputStream(); // 获得请求来的流


Workbook book = WorkbookFactory.create(fis);// 通过流获得WorkBook


Sheet sheet = book.getSheetAt(number - 1);// 获得Sheet,起始位置为0


int lastRowNum = sheet.getLastRowNum(); // _execl表格中的最后一行


Row row = null;
for (int i = 1; i <= lastRowNum; i++) {
row = sheet.getRow(i);// 获得每一行,通过循环变成一个一个的对象,最后放入集合中
if (row != null) {
//这里与上面操作一样的,无非就是取第几行第几列的值

}


}
}
} catch (IOException e1) {
e1.printStackTrace();
} catch (InvalidFormatException e) {
e.printStackTrace();
}
}

return "xxxxx";
}