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

2017-01-12 19:03:28来源:CSDN作者:lyj330852840人点击

自己项目中遇到了,写了挺久的,花了挺多时间的来找相关资料,这次来份详细的上传,包括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)@ResponseBodypublic 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";
}





最新文章

123

最新摄影

微信扫一扫

第七城市微信公众平台