web开发之导入与导出excel

web开发之导入与导出excel

最近开发中的一个需求：需要从本地导入excel数据，根据筛选结果导出成excel到本地。今天把我的代码整理出来希望能给大家一些帮助。开发环境：ssm + maven；

1、导入：

jsp：准备一个model

<!-- 模态框（Modal） -->
<div id="importOrderExcelModal" class="modal hide" tabindex="-1" role="dialog" aria-labelledby="myModalLabel" aria-hidden="true">
    <div class="modal-dialog">
        <div class="modal-content">
            <div class="modal-header">
                <button id="closeModal" type="button" class="close" data-dismiss="modal" aria-hidden="true">
                    &times;
                </button>
                <h5 class="modal-title" id="myModalLabel">
                    请选择需要导入的Excel文件:
                </h5>
            </div>
            <div class="modal-body">
                <form id="importExcelForm" action="#" class="form-horizontal" method="post">
                    <div class="modal-body">

                        <div class="control-group">
                            <label class="control-label">Excel文件(订单数据)</label>
                            <div class="controls">
                                <input type="file" name="file" id="file" class="input-block-level">
                            </div>
                        </div>
                    </div>
                    <div class="modal-footer">
                        <span id="importStatus" style="margin-right:20px;"></span>
                        <button id="downloadOrderModel" class="btn btn-default">下载模板 </button>
                        <button id="importBtn" class="btn btn-primary">上传 </button>
                    </div>
                </form>
            </div>
        </div><!-- /.modal-content -->
    </div><!-- /.modal -->

js部分：提交表单到后台

$('#importExcelForm').ajaxSubmit({
                url : '${ctx}/customerOrder/importCustomerOder.action',
                type : "post",
                beforeSubmit : function(arr, $form, options) {
                    $("#importStatus").html("<font color='blue'>正在上传...");
                    $('#importBtn').attr("disabled", true);
                    $('#closeModal').attr("disabled", true);
                    $('#downloadOrderModel').attr("disabled", true);
                },
                success : function(result) {
                    if (result.success == true) {
                        $("#importStatus").html("<font color='green'>"+result.msg);
                        setTimeout(function () {
                            $("#importOrderExcelModal").modal("hide");
                            window.location.href="${ctx}/customerOrder/customerOrderList.action"

                        }, 1000);
                    } else {
                        $("#importStatus").html("<font color='red'>" + result.msg);
                        $('#importBtn').attr("disabled", false);
                        $('#closeModal').attr("disabled", false);
                        $('#downloadOrderModel').attr("disabled", false);
                    }
                },
                error : function() {
                    $("#importStatus").html("<font color='red'>服务器发生错误");
                    $('#importBtn').attr("disabled", false);
                    $('#closeModal').attr("disabled", false);
                    $('#downloadOrderModel').attr("disabled", false);
                }
            });

java代码：

controller

@Controller
@RequestMapping("/customerOrder")
public class CustomerOrderController {
    @RequestMapping(value = "/importCustomerOder.action")
    @ResponseBody
    public JsonResult importCustomerOder(HttpSession session,
                                         @RequestParam("file") MultipartFile file) {
        try {
            if (!file.isEmpty()) {
                User user = PrincipalUtil.getUser(session);
                InputStream inputStream = file.getInputStream();
                return customerService.importCustomerOrder(inputStream, user.getId());
            } else {
                return new JsonResult(false, "上传文件为空");
            }
        } catch (Exception e) {
            return new JsonResult(false, e.getMessage());
        }
    }
}

seviceImpl：读取excel，循环遍历行和列，存进List中。获取数据存储到数据库中;

@Service
public class CustomerServiceImpl implements CustomerService {
    @Override
    public JsonResult importCustomerOrder(InputStream inputStream, Integer createUserId) throws IOException {
        if(!inputStream.markSupported()) {
            inputStream = new PushbackInputStream(inputStream, 8);
        }
        if(POIFSFileSystem.hasPOIFSHeader(inputStream)) {//2003
            return new JsonResult(false, "请导入2007版Excel格式");
        }
        if(POIXMLDocument.hasOOXMLHeader(inputStream)) {//2007
            //创建Excel工作簿
            XSSFWorkbook book = new XSSFWorkbook(inputStream);
            //得到第一个工作表格
            XSSFSheet sheet = book.getSheetAt(0);
            //得到第一个工作表格的总行数
            int rowCount = sheet.getPhysicalNumberOfRows();
            int index = 10; //列数

            List<OrderExcel> orderExcelList = new ArrayList<>();
            //循环取出Excel中的内容
            for (int i = 1; i < rowCount; i++) {
                XSSFRow cells = sheet.getRow(i);
                boolean valueNotNull = false;
                OrderExcel orderExcel = new OrderExcel();
                for(int j = 0; j < index; j++){
                    String value = "";
                    if(cells.getPhysicalNumberOfCells() > j){
                        try {
                            value = cells.getCell(j).getStringCellValue();
                        } catch (Exception e) {
                            value = cells.getCell(j).getNumericCellValue() + "";
                        }
                    }
                    if(StringUtils.isNotBlank(value)){
                        valueNotNull = true;
                        value = value.trim();
                        if(j==0){//对应数据存储到对象中。
                            orderExcel.setOrderNo(value);
                        } else if(j==1){
                            orderExcel.setName(value);
                        } else if(j==2){
                            orderExcel.setMobile(value);
                        } else if(j==3){
                            orderExcel.setSendCity(value);
                        } else if(j==4){
                            orderExcel.setAddress(value);
                        } else if(j==5){
                            orderExcel.setBrandName(value);
                        } else if(j==6){
                            orderExcel.setMallName(value);
                        } else if(j==7){
                            orderExcel.setSendTime(value);
                        } else if(j==8){
                            orderExcel.setPayTime(value);
                        } else if(j==9){
                            orderExcel.setTotalAmount(value);
                        }
                    }
                }
            //excel表中的数据放入集合orderExcelList，对数据进行保存的方法
            return processOrder(orderExcelList, createUserId);
        }

        return new JsonResult(false, "导入文件不是excel格式");
    }

    private JsonResult processOrder(List<OrderExcel> orderExcelList, Integer createUserId){
       //存储到数据库中;
    }
}

2、导出：

业务需求：根据条件获取数据库中对象集合，循环载入表格中。

controller

/**
 *
 * @param session
 * @param response
 * @param orderFilter 前台的筛选条件存入实体类中
 */
@RequestMapping(value = "/exportCustomerOrder.action")
public void exportCustomerOrder(HttpSession session, HttpServletResponse response, OrderFilter orderFilter) {
    User user = PrincipalUtil.getUser(session);
    if (!user.getRole().equals(Constants.ROLE_SUPER_ADMIN)){
        String mallId = user.getMallId() + "";
        orderFilter.setMallId(mallId);
    }
    //存在乱码，转码传参
    String name = orderFilter.getName();
    String brandName = orderFilter.getBrandName();
    String address = orderFilter.getAddress();
    try {
        if (StringUtils.isNotBlank(name)) {
            String decodeName = new String(orderFilter.getName().getBytes("ISO-8859-1"),"UTF-8");
            orderFilter.setName(decodeName);
        }
        if (StringUtils.isNotBlank(brandName)) {
            String decodeBrandName = new String(orderFilter.getBrandName().getBytes("ISO-8859-1"),"UTF-8");
            orderFilter.setBrandName(decodeBrandName);
        }
        if (StringUtils.isNotBlank(address)) {
            String decodeAddress = new String(orderFilter.getAddress().getBytes("ISO-8859-1"),"UTF-8");
            orderFilter.setAddress(decodeAddress);
        }
    } catch (UnsupportedEncodingException e) {
        e.printStackTrace();
    }

    List<LinkedHashMap<String,String>> exportOrderList = new ArrayList<LinkedHashMap<String, String>>();
    List<CustomerOrderEx> filterNormalCustomerOrderList = customerService.getFilterNormalCustomerOrderList(orderFilter);
    LinkedHashMap<String, String> exportHashMap = null;
    for (CustomerOrderEx customerOrderEx : filterNormalCustomerOrderList){
        exportHashMap = new LinkedHashMap<>();
        exportHashMap.put("商场名称", customerOrderEx.getMallName());
        exportHashMap.put("顾客名称", customerOrderEx.getName());
        exportHashMap.put("手机号", customerOrderEx.getMobile());
        exportHashMap.put("一级品类", customerOrderEx.getCategoryName());
        exportHashMap.put("品牌", customerOrderEx.getBrandName());
        exportHashMap.put("送货城市", customerOrderEx.getProvinceName()+customerOrderEx.getCityName()+customerOrderEx.getDistrictName());
        exportHashMap.put("送货地址", customerOrderEx.getAddress());
        if (customerOrderEx.getSendTime() != null){
            exportHashMap.put("预计送货时间", DateUtil.formatDate(customerOrderEx.getSendTime()));
        }else {
            exportHashMap.put("预计送货时间", "");
        }
        if (customerOrderEx.getPayTime() != null){
            exportHashMap.put("最后一次付款时间", DateUtil.formatDate(customerOrderEx.getPayTime()));
        }else {
            exportHashMap.put("最后一次付款时间", "");
        }
        exportHashMap.put("实收金额", customerOrderEx.getTotalAmount());
        exportOrderList.add(exportHashMap);
    }
    String sheetName = "订单导出";
    String fileName = sheetName;
    fileService.exportExcel2007File(response, fileName, sheetName, exportOrderList);
}

serviceImpl：

@Service
public class FileServiceImpl implements FileService {
    @Override
    public JsonResult exportExcel2007File(HttpServletResponse servletResponse, String exportFileName, String sheetName, List<LinkedHashMap<String,String>> exportContentList) {
        servletResponse.reset();
        servletResponse.setHeader("Charset", "UTF-8");
        servletResponse.setHeader("Content-Type", "application/force-download");
        servletResponse.setHeader("Content-Type", "application/vnd.ms-excel");
        try {
            servletResponse.setHeader("Content-disposition",
                    "attachment; filename="+ URLEncoder.encode(exportFileName, "utf8") + ".xls");
        } catch (UnsupportedEncodingException e1) {
            // TODO Auto-generated catch block
            e1.printStackTrace();
        }
        servletResponse.setContentType("application/msexcel");

        HSSFWorkbook xssf_w_book = new HSSFWorkbook();
        HSSFSheet xssf_w_sheet= xssf_w_book.createSheet(sheetName);
        HSSFRow xssf_w_row = null;// 创建一行
        HSSFCell xssf_w_cell = null;// 创建每个单元格
        xssf_w_sheet.createFreezePane( 0, 1, 0, 1 );  //冻结窗口
        HSSFCellStyle head_cellStyle = xssf_w_book.createCellStyle();// 创建一个单元格样式
        HSSFFont head_font = xssf_w_book.createFont();
        head_font.setFontName("宋体");// 设置头部字体为宋体
        head_font.setBoldweight(Font.BOLDWEIGHT_BOLD); // 粗体
        head_font.setFontHeightInPoints((short) 12);
        head_cellStyle.setFont(head_font);// 单元格样式使用字体
//		head_cellStyle.setVerticalAlignment(org.apache.poi.ss.usermodel.VerticalAlignment.CENTER);
//		head_cellStyle.setAlignment(HorizontalAlignment.LEFT);
        head_cellStyle.setWrapText(true);
        head_cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        head_cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        head_cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
        head_cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);

        // 获取标题
        LinkedHashMap<String, String> responseTitleHashMap = new LinkedHashMap<String, String>();
        if(exportContentList !=null && exportContentList.size() > 0){
            responseTitleHashMap = exportContentList.get(0);
        }
        int i = 0; // 行数
        int titleRows = 0; // 标题占据的行数
        xssf_w_row = xssf_w_sheet.createRow(0 + titleRows);// 第一行写入标题行
        xssf_w_row.setHeight((short)(2*256));

        for (Map.Entry entry : responseTitleHashMap.entrySet()) {
            Object key = entry.getKey(); // 标题
            xssf_w_cell = xssf_w_row.createCell((short) i);
            HSSFDataFormat head_format = xssf_w_book.createDataFormat();
            head_cellStyle.setDataFormat(head_format.getFormat("@"));
            xssf_w_cell.setCellType(HSSFCell.CELL_TYPE_STRING);
            HSSFRichTextString xssfString = new HSSFRichTextString(
                    key.toString());
            xssf_w_cell.setCellValue(xssfString);
            xssf_w_cell.setCellStyle(head_cellStyle);
            xssf_w_sheet.autoSizeColumn((short) i);
            titleRows++;
            i++;
        }

        HSSFCellStyle cellStyle_CN = xssf_w_book.createCellStyle();// 创建数据单元格样式(数据库数据样式)

        cellStyle_CN.setBorderBottom(HSSFCellStyle.BORDER_NONE);
        cellStyle_CN.setBorderLeft(HSSFCellStyle.BORDER_NONE);
        cellStyle_CN.setBorderRight(HSSFCellStyle.BORDER_NONE);
        cellStyle_CN.setBorderTop(HSSFCellStyle.BORDER_NONE);

        // 获取内容
        int j = 0; // 行数
        for (LinkedHashMap<String, String> responseMap : exportContentList) {
            int z = 0; // 列数
            j++; // 数据从第二行开始
            xssf_w_row = xssf_w_sheet.createRow(j);
            xssf_w_row.setHeight((short)(1.5*256));
            for (Map.Entry entry : responseMap.entrySet()) {
                Object value = entry.getValue(); // 数据
                String content = (value == null?"":value.toString());
                content = content.replace("null","");
                content = content.replace("\n","");
                xssf_w_cell = xssf_w_row.createCell((short) z);
                HSSFRichTextString xssfString = new HSSFRichTextString(
                        String.valueOf(content));
                xssf_w_cell.setCellStyle(cellStyle_CN);
                xssf_w_cell.setCellValue(xssfString);

                z++;
            }
        }
        try {
            OutputStream os = servletResponse.getOutputStream();
            xssf_w_book.write(os);
            os.flush();
            os.close();
        } catch (Exception e) {
            e.printStackTrace();
            return new JsonResult(false, e.getMessage());
        }
        return new JsonResult(true);
    }
}