StringBoot数据导入导出Excel详细(可以直接用)

最新推荐文章于 2023-06-07 16:54:53 发布

weixin_33882443

最新推荐文章于 2023-06-07 16:54:53 发布

阅读量324

点赞数

文章标签：数据库前端 json ViewUI

原文链接：http://www.cnblogs.com/ysySelf/p/11024133.html

版权

//Excel文本导入到本地数据库具体看下面代码

public class Response implements Serializable {//工具类
    private boolean success = true;
    private Object result = null;
    private String error = "";
}

public Response importExcel(HttpServletRequest request, @RequestParam("excelFile") MultipartFile excelFile) throws Exception {//excelFile 文件名称
    Response response = new Response();
    BaseUser baseUser = (BaseUser) request.getSession().getAttribute("user");
    List<List<List<String>>> data = ExcelUtil.readXlsxNotBlank(excelFile);//该工具类下面会帖出来
    Map<String, String> params = null;
    List<Map<String, String>> lists = new ArrayList<Map<String, String>>();
    String jsonString = null;

    Integer customer_cclstore_index = null;//店号
    String customer_ccl_store_code = null;

    Integer customer_ccl_store_index_name = null;//门店名称
    String customer_ccl_store_name = null;

    Integer customer_ccl_merchant_index_name = null;//商户名称
    String customer_ccl_merchant_name = null;

    Integer customer_ccl_store_index_province_name = null;//省
    String customer_ccl_store_province_name= null;

    Integer customer_ccl_store_index_city_name = null;//市
    String customer_ccl_store_city_name = null;

    Integer customer_ccl_store_index_address = null;//地址
    String customer_ccl_store_address = null;

    Integer customer_ccl_store_index_contacts = null;//联系人
    String customer_ccl_store_contacts = null;

    Integer customer_ccl_store_index_mobile = null;//联系电话
    String customer_ccl_store_mobile = null;

    Integer customer_ccl_store_index_email = null;//邮箱
    String customer_ccl_store_email = null;
    int count = 0;
    try{

        //开始遍历表格
        for (List<List<String>> result : data) {
            if (result.size() < 1) {
                continue;
            }
            for (int i = 0; i < result.size(); i++) {
                List<String> list = result.get(i);//获取到每一行
                if (i == 0 || i==1 ) {//这里的意思是只遍历标题和表头名称（如上表格），并且表头名称和我们定义的必须一致否则则报空指针异常
                    for (int j = 0; j < list.size(); j++) {//获取到每一行的每一个单元格的内容
                        if (list.get(j) != null && "店号".equals(list.get(j).toString())) {
                            customer_cclstore_index = j;
                        } else if (list.get(j) != null && "门店名称".equals(list.get(j).toString())) {
                            customer_ccl_store_index_name = j;
                        } else if (list.get(j) != null && "商户".equals(list.get(j).toString())) {
                            customer_ccl_merchant_index_name = j;
                        } else if (list.get(j) != null && "省".equals(list.get(j).toString())) {
                            customer_ccl_store_index_province_name = j;
                        } else if (list.get(j) != null && "市".equals(list.get(j).toString())) {
                            customer_ccl_store_index_city_name = j;
                        } else if (list.get(j) != null && "地址".equals(list.get(j).toString())) {
                            customer_ccl_store_index_address = j;
                        }else if (list.get(j) != null && "联系人".equals(list.get(j).toString())) {
                            customer_ccl_store_index_contacts = j;
                        }else if (list.get(j) != null && "联系电话".equals(list.get(j).toString())) {
                         customer_ccl_store_index_mobile = j;
                         }else if (list.get(j) != null && "邮箱".equals(list.get(j).toString())) {
                          customer_ccl_store_index_email = j;
                         }
                    }
                } else {
params = new HashMap<String, String>();

//获取到每一行的每一个单元格的内容，这里做了非空判断
customer_ccl_store_code = list.get(customer_cclstore_index) == null ? "" : list.get(customer_cclstore_index).toString();
customer_ccl_store_name = list.get(customer_ccl_store_index_name) == null ? "" : list.get(customer_ccl_store_index_name).toString();
customer_ccl_merchant_name = list.get(customer_ccl_merchant_index_name) == null ? "" : list.get(customer_ccl_merchant_index_name).toString();
customer_ccl_store_province_name = list.get(customer_ccl_store_index_province_name) == null ? "" : list.get(customer_ccl_store_index_province_name).toString();
customer_ccl_store_city_name = list.get(customer_ccl_store_index_city_name) == null ? "" : list.get(customer_ccl_store_index_city_name).toString();
customer_ccl_store_address = list.get(customer_ccl_store_index_address) == null ? "" : list.get(customer_ccl_store_index_address).toString();
customer_ccl_store_contacts = list.get(customer_ccl_store_index_contacts) == null ? "" : list.get(customer_ccl_store_index_contacts).toString();
customer_ccl_store_mobile = list.get(customer_ccl_store_index_mobile) == null ? "" : list.get(customer_ccl_store_index_mobile).toString();
customer_ccl_store_email = list.get(customer_ccl_store_index_email) == null ? "" : list.get(customer_ccl_store_index_email).toString();
params.put("customer_ccl_store_code", customer_ccl_store_code);
params.put("customer_ccl_store_name", customer_ccl_store_name);
params.put("customer_ccl_merchant_name", customer_ccl_merchant_name);
params.put("customer_ccl_store_province_name", customer_ccl_store_province_name);
params.put("customer_ccl_store_city_name", customer_ccl_store_city_name);
params.put("customer_ccl_store_address", customer_ccl_store_address);
params.put("customer_ccl_store_contacts", customer_ccl_store_contacts);
params.put("customer_ccl_store_mobile", customer_ccl_store_mobile);
params.put("customer_ccl_store_email", customer_ccl_store_email);
lists.add(params);//循环找到每一个值放到list集合
}
            }
        }
    }catch (Exception e){
e.getMessage ();
response.setError ( "文件格式错误！请重新选择！" );
response.setSuccess ( false );
return response;
}
jsonString = JSONArray.fromObject(lists).toString();

List<Map<String, String>> lists = JSONArray.fromObject(jsonString);
//具体业务代码存到数据库
}

//工具类

ExcelUtil.readXlsxNotBlank

   public static List<List<List<String>>> readXlsxNotBlank(MultipartFile excelFile) throws IOException {
        String originFileName = excelFile.getOriginalFilename();
        if(!originFileName.endsWith(".xlsx") || StringUtils.isEmpty(originFileName)){
            throw new NullPointerException("文件格式错误");
        }
        XSSFWorkbook xssfWorkbook = new XSSFWorkbook(excelFile.getInputStream());
        List<List<List<String>>> data = new ArrayList<>();
        //循环每一页，并处理当前页
        for(XSSFSheet xssfSheet : xssfWorkbook) {
            if(xssfSheet == null) {
                continue;
            }
            List<List<String>> result = new ArrayList<List<String>>();
            //处理当前页，循环读取每一行
            for(int rowNum = 0; rowNum <= xssfSheet.getLastRowNum(); rowNum++) {
                XSSFRow xssfRow = xssfSheet.getRow(rowNum);
                if (StringUtils.isEmpty(xssfRow)) {
                    continue;
                }
                int minColIx = xssfRow.getFirstCellNum();
                int maxColIx = xssfRow.getLastCellNum();
                List<String> rowList = new ArrayList<String>();
                // 遍历这行，获取处理每个cell元素
                int ia=minColIx;
                for(int colIx = minColIx; colIx < maxColIx; colIx++) {
                    XSSFCell cell = xssfRow.getCell(colIx);
                    if(cell != null){
                        cell.setCellType(XSSFCell.CELL_TYPE_STRING);
                    }
                    if (cell == null||cell.toString()==null||cell.toString().trim().length()==0)
                        ia++;
                    rowList.add(cell == null?null:cell.toString());
                }
                if(ia!=maxColIx)
                    result.add(rowList);
            }
            data.add(result);
        }
        return data;
    }

//========================================================================================================导出

public Response exportList(HttpServletRequest request , HttpServletResponse responses ,String merchant_id){
        Response response = new Response();
        response = crmStore.exportList(merchant_id); //首先根据自己得需求查询出需要的数据（查询数据）
        ExportCrmDaos ex = new ExportCrmDaos();//工具类
        if(response.getResult () != null){
            //导出方法
           ex.exportExcel ( responses, (List<Map<String, Object>>) response.getResult() );
        }else{
            Response errResponse = new Response();
            errResponse.setSuccess(false);
            errResponse.setError("查询失败");
            responses.setContentType("application/json; charset=utf-8");
            PrintWriter out = null;
            try{
                out = responses.getWriter();
                out.println( JSONObject.fromObject(errResponse).toString());
            }catch(Exception e){
                log.error(e.getMessage());
            }finally {
                if(out!=null){
                    out.close();
                }
            }
        }
        return response;
    }

public Response exportList(String merchant_id){
        Response response = new Response ();
        List<Map<String,Object>> fault_list = new ArrayList<Map<String,Object>>();
        StringBuffer sql = new StringBuffer();
        List<Object> params_fault = new ArrayList<Object>();

        List<Map<String,Object>> findeListByName = jdbcTemplate.queryForList ( " select id,name as nameMerchant from customer_ccl_merchant where id= ?" ,new Object[]{merchant_id} );
        sql.append ( "select s.*,m.name as merchant_name from customer_ccl_store s join customer_ccl_merchant m on s.merchant_id = m.id where 1=1 \n" );
        if(merchant_id != null && !merchant_id.equals ( "" ) ){
            sql.append(" and m.name = ? ");
            params_fault.add(findeListByName.get ( 0 ).get ( "nameMerchant" ));
            System.err.println ( "####################################"+findeListByName.get ( 0 ).get ( "nameMerchant" ) );
            fault_list = jdbcTemplate.queryForList(sql.toString(),params_fault.toArray());
        }else{
            fault_list = jdbcTemplate.queryForList(sql.toString());

        }
        response.setResult(fault_list);
        return response;
    }

//工具类

public class ExportCrmDaos {

    Logger log = Logger.getLogger(this.getClass());

/* Integer no_index_ = 0;
    String no_title_ = "序号";*/

    Integer no_index = 0;
    String no_title = "店号"; //store_code

    Integer fault_no_index = 1;
    String fault_no_title = "门店名称";//store_name

    Integer merchant_index = 2;
    String merchant_title = "商户"; //merchant_name

    Integer store_index = 3;
    String store_title = "省";//province_name

    Integer province_index = 4;
    String province_title = "市";//city_name

    Integer city_index = 5;
    String city_title = "地址";//address

    Integer address_index = 6;
    String address_title = "联系人";//contacts

    Integer fault_describe_index = 7;
    String fault_describe_title = "联系电话";//mobile

    Integer model_index = 8;
    String model_title = "邮箱";//email

    public void exportExcel(HttpServletResponse httpResponse, List<Map<String,Object>> fault_list) {
       // System.err.println ( "共"+fault_list+"数据" );
        //设置下载信息
        String file_name = DateUtil.format(new Date ())+".xlsx";
        httpResponse.setContentType("application/force-download");
        httpResponse.addHeader("Content-Disposition", "attachment;fileName=" + file_name);
        //创建下载对象
        try{
            XSSFWorkbook workbook = new XSSFWorkbook();
            XSSFSheet sheet = workbook.createSheet();
            //创建标题合并单元格
            CellRangeAddress titleRegion = new CellRangeAddress(0, 0, 0, 9);
            sheet.addMergedRegion(titleRegion);
            Row titleRow = sheet.createRow(0);
            titleRow.createCell(0).setCellValue("门店记录");
            //创建表头
            Row lableRow = sheet.createRow(1);
           // lableRow.createCell(no_index_).setCellValue(no_title_);
            lableRow.createCell(no_index).setCellValue(no_title);
            lableRow.createCell(fault_no_index).setCellValue(fault_no_title);
            lableRow.createCell(merchant_index).setCellValue(merchant_title);
            lableRow.createCell(store_index).setCellValue(store_title);
            lableRow.createCell(province_index).setCellValue(province_title);
            lableRow.createCell(city_index).setCellValue(city_title);
            lableRow.createCell(address_index).setCellValue(address_title);
            lableRow.createCell(fault_describe_index).setCellValue(fault_describe_title);
            lableRow.createCell(model_index).setCellValue(model_title);
            //填写内容
            int now_row_index = 2;
            //int row_no = 1;
            for(Map<String,Object> fault_info : fault_list){
                Row dataRow = sheet.createRow(now_row_index);
              // dataRow.createCell(no_index_).setCellValue(row_no);merchant_name
                dataRow.createCell(no_index).setCellValue( ObjectUtils.isEmpty(fault_info.get("code"))?"":fault_info.get("code").toString());
                dataRow.createCell(fault_no_index).setCellValue( ObjectUtils.isEmpty(fault_info.get("name"))?"":fault_info.get("name").toString());                        dataRow.createCell(merchant_index).setCellValue(ObjectUtils.isEmpty(fault_info.get("merchant_name"))?"":fault_info.get("merchant_name").toString());
                dataRow.createCell(store_index).setCellValue(ObjectUtils.isEmpty(fault_info.get("province_name"))?"":fault_info.get("province_name").toString());
                dataRow.createCell(province_index).setCellValue(ObjectUtils.isEmpty(fault_info.get("city_name"))?"":fault_info.get("city_name").toString());
                dataRow.createCell(city_index).setCellValue(ObjectUtils.isEmpty(fault_info.get("address"))?"":fault_info.get("address").toString());
                dataRow.createCell(address_index).setCellValue(ObjectUtils.isEmpty(fault_info.get("contacts"))?"":fault_info.get("contacts").toString());
                dataRow.createCell(fault_describe_index).setCellValue(ObjectUtils.isEmpty(fault_info.get("mobile"))?"":fault_info.get("mobile").toString());
                dataRow.createCell(model_index).setCellValue(ObjectUtils.isEmpty(fault_info.get("email"))?"":fault_info.get("email").toString());
                now_row_index+=1;
                //row_no++;
            }
            httpResponse.setContentType("application/octet-stream;charset=UTF-8");
            httpResponse.setHeader("Content-Disposition", "attachment;filename="+ System.currentTimeMillis()+".xlsx");
            httpResponse.addHeader("Pargam", "no-cache");
            httpResponse.addHeader("Cache-Control", "no-cache");
            //输出
            OutputStream outputStream = httpResponse.getOutputStream();
            workbook.write(outputStream);
        }catch (Exception e1){
            e1.printStackTrace();
            Response errResponse = new Response();
            errResponse.setSuccess(false);
            errResponse.setError("查询失败");
            httpResponse.setContentType("application/json; charset=utf-8");
            PrintWriter out = null;
            try{
                out = httpResponse.getWriter();
                out.println( JSONObject.fromObject(errResponse).toString());
            }catch(Exception e2){
                log.error(e2.getMessage());
            }finally {
                if(out!=null){
                    out.close();
                }
            }
        }
    }
}

//*****************************************************************************************************************************************导出

导出对应的前端JS

function xxxx(){

location.href = 'storeContent/exportStoreModeAction.action?export_time_start='+export_time_start+'&export_time_end='+export_time_end;

}

假如我想导出一下的格式

数据一对多怎么导出？？

控制层： dao.exportExcel(httpResponse, (List<Map<String, Object>>) response.getResult());//调用导出工具类代码

Dao层查出来的数据

public Response getListForExport(String merchant_id, String create_time_start, String create_time_end) {
        Response response = new Response();
        List<Map<String,Object>> fault_list = new ArrayList<Map<String,Object>>();
        StringBuffer sql_fault = new StringBuffer();
        List<Object> params_fault = new ArrayList<Object>();
        sql_fault.append("SELECT f.*,m.`name` AS merchant_name,s.`name` AS store_name,s.province_name,city_name,address ");
        sql_fault.append("FROM hw_fault f ");
        sql_fault.append(" left JOIN customer_ccl_merchant m ON f.merchant_id = m.id ");
        sql_fault.append(" left JOIN customer_ccl_store s ON f.store_id = s.id ");
        sql_fault.append("WHERE 1 = 1 ");
        if(!StringUtils.isEmpty(merchant_id)){
            sql_fault.append(" and f.merchant_id = ? ");
            params_fault.add(merchant_id);
        }
        if(!StringUtils.isEmpty(create_time_start)){
            sql_fault.append(" and f.create_time >= ? ");
            params_fault.add(DateUtil.getDateFormat(create_time_start,"yyyy-MM-dd HH:mm").getTime());
        }
        if(!StringUtils.isEmpty(create_time_end)){
            sql_fault.append(" and f.create_time <= ? ");
            params_fault.add(DateUtil.getDateFormat(create_time_end,"yyyy-MM-dd HH:mm").getTime());
        }
        fault_list = jdbcTemplate.queryForList(sql_fault.toString(),params_fault.toArray());
        StringBuffer sql_worklist = new StringBuffer();
        sql_worklist.append("SELECT w.*,a.`name` AS aftersale_name ");
        sql_worklist.append("FROM hw_worklist w ");
        sql_worklist.append(" left JOIN customer_aftersale a ON w.aftersale_id = a.id ");
        sql_worklist.append("WHERE w.fault_id = ? ");
        for(Map<String,Object> fault_info : fault_list){
            List<Map<String,Object>> worklist_list = new ArrayList<Map<String,Object>>();
            worklist_list = jdbcTemplate.queryForList(sql_worklist.toString(),new Object[]{fault_info.get("id").toString()}); //一对多或者一对一
            fault_info.put("worklist_list",worklist_list);
        }
        response.setResult(fault_list);
        return response;
    }

工具类：

@Repository
public class ExportFaultDao {

    Logger log = Logger.getLogger(this.getClass());

    Integer no_index = 0;
    String no_title = "序号";

    Integer fault_no_index = 1;
    String fault_no_title = "故障号";

    Integer merchant_index = 2;
    String merchant_title = "所属商户";

    Integer store_index = 3;
    String store_title = "所属门店";

    Integer province_index = 4;
    String province_title = "省";

    Integer city_index = 5;
    String city_title = "市";

    Integer address_index = 6;
    String address_title = "地址";

    Integer fault_describe_index = 7;
    String fault_describe_title = "故障描述";

    Integer model_index = 8;
    String model_title = "型号";

    Integer fault_state_index =9;
    String fault_state_title= "当前状态";

    Integer create_time_index = 10;
    String create_time_title = "上报时间";

    /*Integer aftersale_name_index = 11;
    String aftersale_name_title ="指派工程师";*/

    Integer end_time_index = 11;
    String end_time_title ="解决时间";

    Integer workList_no_index = 12;
    String workList_no_title = "工单号";

    Integer aftersale_index = 13;
    String aftersale_title = "工程师";

    Integer aftersale_create_time_index = 14;
    String aftersale_create_time_title = "指派时间";

    Integer aftersale_acceptance_time_index = 15;
    String aftersale_acceptance_time_title = "受理时间";

    Integer aftersale_service_start_time_index = 16;
    String aftersale_service_start_time_title = "上门时间";

    Integer aftersale_service_end_time_index = 17;
    String aftersale_service_end_time_title = "完成时间";

    Integer aftersale_end_type_index = 18;
    String aftersale_end_type_title = "服务结果";

    Integer aftersale_fault_type_index = 19;
    String aftersale_fault_type_title = "故障类型";

    Integer aftersale_measures_index = 20;
    String aftersale_measures_title = "解决措施";

    Integer aftersale_sparepart_index = 21;
    String aftersale_sparepart_title = "备件";

    public void exportExcel(HttpServletResponse httpResponse, List<Map<String,Object>> fault_list) {//List<Map<String,Object>> fault_list 查出来的参数
        //设置下载信息
        String file_name = DateUtil.format(new Date())+".xlsx";
        httpResponse.setContentType("application/force-download");
        httpResponse.addHeader("Content-Disposition", "attachment;fileName=" + file_name);
        //创建下载对象
        try{
            XSSFWorkbook workbook = new XSSFWorkbook();
            XSSFSheet sheet = workbook.createSheet();
            //创建标题合并单元格
            CellRangeAddress titleRegion = new CellRangeAddress(0, 0, 0, 19);//合并表头，这四个参数分别表示：开始行数，最后行数，开始列数，结束列数
            sheet.addMergedRegion(titleRegion);
            Row titleRow = sheet.createRow(0);
            titleRow.createCell(0).setCellValue("创捷故障报修登记表");//表头的名字
            //创建表头标题
            Row lableRow = sheet.createRow(1);
            lableRow.createCell(no_index).setCellValue(no_title);
            lableRow.createCell(fault_no_index).setCellValue(fault_no_title);
            lableRow.createCell(merchant_index).setCellValue(merchant_title);
            lableRow.createCell(store_index).setCellValue(store_title);
            lableRow.createCell(province_index).setCellValue(province_title);
            lableRow.createCell(city_index).setCellValue(city_title);
            lableRow.createCell(address_index).setCellValue(address_title);
            lableRow.createCell(fault_describe_index).setCellValue(fault_describe_title);
            lableRow.createCell(model_index).setCellValue(model_title);
            lableRow.createCell(fault_state_index).setCellValue(fault_state_title);//
            lableRow.createCell(create_time_index).setCellValue(create_time_title);//
            lableRow.createCell(end_time_index).setCellValue(end_time_title);
            lableRow.createCell(workList_no_index).setCellValue(workList_no_title);
            lableRow.createCell(aftersale_index).setCellValue(aftersale_title);
            lableRow.createCell(aftersale_create_time_index).setCellValue(aftersale_create_time_title);
            lableRow.createCell(aftersale_acceptance_time_index).setCellValue(aftersale_acceptance_time_title);
            lableRow.createCell(aftersale_service_start_time_index).setCellValue(aftersale_service_start_time_title);
            lableRow.createCell(aftersale_service_end_time_index).setCellValue(aftersale_service_end_time_title);
            lableRow.createCell(aftersale_end_type_index).setCellValue(aftersale_end_type_title);
            lableRow.createCell(aftersale_fault_type_index).setCellValue(aftersale_fault_type_title);
            lableRow.createCell(aftersale_measures_index).setCellValue(aftersale_measures_title);
            lableRow.createCell(aftersale_sparepart_index).setCellValue(aftersale_sparepart_title);
            //填写内容
            int now_row_index = 2;
            int row_no = 1;
            for(Map<String,Object> fault_info : fault_list){
                List<Map<String,Object>> worklist_list = (List<Map<String, Object>>) fault_info.get("worklist_list");
                //检查是否需要合并单元格
                if(worklist_list.size()>1){
                    for(int i = 0;i<12;i++){
                        CellRangeAddress dataRegion = new CellRangeAddress(now_row_index, now_row_index+worklist_list.size()-1, i, i);
                        sheet.addMergedRegion(dataRegion);
                    }
                }

        //给每一个单元格赋值，循环一次赋值一行
                Row dataRow = sheet.createRow(now_row_index);
                dataRow.createCell(no_index).setCellValue(row_no);
                dataRow.createCell(fault_no_index).setCellValue(ObjectUtils.isEmpty(fault_info.get("fault_no"))?"":fault_info.get("fault_no").toString());
                 dataRow.createCell(merchant_index).setCellValue(ObjectUtils.isEmpty(fault_info.get("merchant_name"))?"":fault_info.get("merchant_name").toString());
                dataRow.createCell(store_index).setCellValue(ObjectUtils.isEmpty(fault_info.get("store_name"))?"":fault_info.get("store_name").toString());
                dataRow.createCell(province_index).setCellValue(ObjectUtils.isEmpty(fault_info.get("province_name"))?"":fault_info.get("province_name").toString());
                dataRow.createCell(city_index).setCellValue(ObjectUtils.isEmpty(fault_info.get("city_name"))?"":fault_info.get("city_name").toString());
                dataRow.createCell(address_index).setCellValue(ObjectUtils.isEmpty(fault_info.get("address"))?"":fault_info.get("address").toString());
                dataRow.createCell(fault_describe_index).setCellValue(ObjectUtils.isEmpty(fault_info.get("fault_describe"))?"":fault_info.get("fault_describe").toString());
                dataRow.createCell(model_index).setCellValue(ObjectUtils.isEmpty(fault_info.get("model"))?"":fault_info.get("model").toString());
                String type_fault_state = fault_info.get("fault_state").toString ();
                String typeStrings ;
                if(!ObjectUtils.isEmpty ( type_fault_state )){
                    if(type_fault_state.equals ( "0" )){
                        typeStrings = "未指派";
                    }else if(type_fault_state.equals ( "1" )){
                        typeStrings = "未受理";
                    }else if(type_fault_state.equals ( "2" )){
                        typeStrings = "已受理";
                    }else if(type_fault_state.equals ( "3" )){
                        typeStrings = "未解决";
                    }else if(type_fault_state.equals ( "4" )){
                        typeStrings = "已解决";
                    }else{
                        typeStrings = "不详";
                    }
                }else{
                    typeStrings = "";
                }
                dataRow.createCell(fault_state_index).setCellValue(typeStrings);
                dataRow.createCell(create_time_index).setCellValue(DateUtil.getDate(new Date(Long.parseLong(fault_info.get("create_time").toString())),"yyyy-MM-dd HH:mm:ss"));
                String times = String.valueOf ( fault_info.get("end_time") );
                if(times == null || times.equals ( "" ) || times.equals ( "null" )){
                   // dataRow.createCell(end_time_index).setCellValue(times+"");//解决时间
                }else{
                    Date date = new Date ( );
                    date.setTime ( Long.valueOf ( times ) );
                    SimpleDateFormat SDF = new SimpleDateFormat ( "yyyy-MM-dd HH:mm:ss" );
                    dataRow.createCell(end_time_index).setCellValue(SDF.format ( date ));//解决时间
                }

//以上先查询出上面表格红色的字体，下面在查询黄色的内容也就是一对一或者一对多并且合并单元格
                for(int i = 0;i<worklist_list.size();i++){
                    Map<String,Object> worklist_info = worklist_list.get(i);
                    Row subDataRow;
                    if(i==0){
                        subDataRow = dataRow;
                    }else{
                        subDataRow = sheet.createRow(now_row_index+i);
                    }
                    subDataRow.createCell(workList_no_index).setCellValue(ObjectUtils.isEmpty(worklist_info.get("workList_no"))?"":worklist_info.get("workList_no").toString());
                    subDataRow.createCell(aftersale_index).setCellValue(ObjectUtils.isEmpty(worklist_info.get("aftersale_name"))?"":worklist_info.get("aftersale_name").toString());
                    subDataRow.createCell(aftersale_create_time_index).setCellValue(DateUtil.getDate(new Date(Long.parseLong(worklist_info.get("create_time").toString())),"yyyy-MM-dd HH:mm:ss"));
                    subDataRow.createCell(aftersale_acceptance_time_index).setCellValue(ObjectUtils.isEmpty(worklist_info.get("acceptance_time"))?"":DateUtil.getDate(new Date(Long.parseLong(worklist_info.get("acceptance_time").toString())),"yyyy-MM-dd HH:mm:ss"));
                    subDataRow.createCell(aftersale_service_start_time_index).setCellValue(ObjectUtils.isEmpty(worklist_info.get("service_start_time"))?"":DateUtil.getDate(new Date(Long.parseLong(worklist_info.get("service_start_time").toString())),"yyyy-MM-dd HH:mm:ss"));
                    subDataRow.createCell(aftersale_service_end_time_index).setCellValue(ObjectUtils.isEmpty(worklist_info.get("service_end_time"))?"":DateUtil.getDate(new Date(Long.parseLong(worklist_info.get("service_end_time").toString())),"yyyy-MM-dd HH:mm:ss"));
                    subDataRow.createCell(aftersale_end_type_index).setCellValue(ObjectUtils.isEmpty(worklist_info.get("end_type"))?"":(worklist_info.get("end_type").toString().equals("1")?"已解决":"未解决"));
                    subDataRow.createCell(aftersale_fault_type_index).setCellValue(worklist_info.get("fault_type")==null?"":worklist_info.get("fault_type").toString());
                    subDataRow.createCell(aftersale_measures_index).setCellValue(worklist_info.get("measures")==null?"":worklist_info.get("measures").toString());
                    subDataRow.createCell(aftersale_sparepart_index).setCellValue(worklist_info.get("sparepart")==null?"":worklist_info.get("sparepart").toString());
                }
                if(worklist_list.size() == 0){//这个判断必须有如果一对一则不需要合并单元格再当前表格行数+1即可
                    now_row_index +=1;
                }else{
                    now_row_index+=worklist_list.size();//如以上表格内容是1对多必须加上查询出来的多个条数的内容，如当前是52行查询出了3条数据（一对多），则下次循环必须是+3行而不是+1
                }
                row_no++;
            }
            httpResponse.setContentType("application/octet-stream;charset=UTF-8");
            httpResponse.setHeader("Content-Disposition", "attachment;filename="+ System.currentTimeMillis()+".xlsx");
            httpResponse.addHeader("Pargam", "no-cache");
            httpResponse.addHeader("Cache-Control", "no-cache");
            //输出
            OutputStream outputStream = httpResponse.getOutputStream();
            workbook.write(outputStream);
        }catch (Exception e1){
            e1.printStackTrace();
            Response errResponse = new Response();
            errResponse.setSuccess(false);
            errResponse.setError("查询失败");
            httpResponse.setContentType("application/json; charset=utf-8");
            PrintWriter out = null;
            try{
                out = httpResponse.getWriter();
                out.println(JSONObject.fromObject(errResponse).toString());
            }catch(Exception e2){
                log.error(e2.getMessage());
            }finally {
                if(out!=null){
                    out.close();
                }
            }
        }
    }
}

转载于:https://www.cnblogs.com/ysySelf/p/11024133.html

weixin_33882443

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
StringBoot数据导入导出Excel详细(可以直接用)

//Excel文本导入到本地数据库具体看下面代码public class Response implements Serializable {//工具类 private boolean success = true; private Object result = null; private String error = "";}public Resp...
复制链接

扫一扫