Java实现数据库中查询出的数据转存成excel表

看了很多文章乱糟糟的，自己写了个简单暴力的一眼就懂，没有那么多花里胡哨，表格样式可以通过代码定义，我嫌麻烦

注意日期格式如果是以String类型的方式存到数据库的导出时要转换一次，直接导出格式不对

因为导出excel表格用的是get方式传参，所以如果需要对导出的数据用中文模糊查询，此时 用get传参会出现中文乱码

解决办法：

前端对需要传的中文参数进行一次编码 URLEncoder.encode(传参,“utf-8”);

后台需要再次解码：URLDecoder.decode(接收的参数,“utf-8”);

@RequestMapping(value = "outPutExcel", method = RequestMethod.GET)
@ResponseBody
public void outPutExcel( HttpServletResponse response,String officeid,
String sonid,String nameorphone,String beginTime, String endTime,String option) {
        String nString = "";
        try {
            if (nameorphone != null && nameorphone != "") {
            //对前端传的参数解码
                 nString = URLDecoder.decode(nameorphone,"UTF-8");
            }
        } catch (UnsupportedEncodingException e2) {
            // TODO Auto-generated catch block
            e2.printStackTrace();
        }
        response.reset();
        //设置浏览器下载的格式，并以当前时间的毫秒数命名
        response.setHeader("Content-Disposition", "attachment;Filename=" + System.currentTimeMillis() + ".xls");
        response.setContentType("application/msexcel");
        List<PurchaseSum> list = purchaseService.selectPCSum(officeid, sonid, nString, beginTime, endTime, option);
        if (list == null && list.isEmpty()) {
            throw new NullPointerException("导出数据源为空");
        }
        HSSFWorkbook wb = new HSSFWorkbook();
        HSSFSheet sheet = wb.createSheet("sheet0");
        HSSFRow rows;
        HSSFCell cells;
        //设置表格第一行的列名
        // 获得表格第一行
        rows = sheet.createRow(0);
        // 根据需要给第一行每一列设置标题
        cells = rows.createCell(0);
        cells.setCellValue("客户姓名");

        cells = rows.createCell(1);
        cells.setCellValue("客户电话");

        cells = rows.createCell(2);
        cells.setCellValue("下单日期");

        cells = rows.createCell(3);
        cells.setCellValue("订单号");

        cells = rows.createCell(4);
        cells.setCellValue("所属分公司");

        cells = rows.createCell(5);
        cells.setCellValue("签单人");

        cells = rows.createCell(6);
        cells.setCellValue("品名");

        cells = rows.createCell(7);
        cells.setCellValue("型号");

        cells = rows.createCell(8);
        cells.setCellValue("颜色");

        cells = rows.createCell(9);
        cells.setCellValue("尺寸");

        cells = rows.createCell(10);
        cells.setCellValue("材质");

        cells = rows.createCell(11);
        cells.setCellValue("已采购数量(件)");

        cells = rows.createCell(12);
        cells.setCellValue("采购单价");

        cells = rows.createCell(13);
        cells.setCellValue("采购总价");

        cells = rows.createCell(14);
        cells.setCellValue("已出库(件)");
        //循环数据库查出来的数据集，对应每一列赋值
        //此处list.size()本不应该-1，因为同事在list集合里追加了另一条数据，导致报错故将其去除
        for (int i = 0; i < list.size()-1; i++) {
            rows = sheet.createRow(i + 1);

            cells = rows.createCell(0);
            cells.setCellValue(list.get(i).getCustomerName());

            cells = rows.createCell(1);
            cells.setCellValue(list.get(i).getPhone());
            //对日期格式进行转换
            cells = rows.createCell(2);
            String dateString  = list.get(i).getPlaceOrderTime().toString();
            Date date = null;
            try {
                date = new SimpleDateFormat("EEE MMM dd HH:mm:ss Z yyyy", Locale.UK).parse(dateString);
            } catch (ParseException e1) {
                // TODO Auto-generated catch block
                e1.printStackTrace();
            }
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
            cells.setCellValue(sdf.format(date));

            cells = rows.createCell(3);
            cells.setCellValue(list.get(i).getOrderNumber());

            cells = rows.createCell(4);
            cells.setCellValue(list.get(i).getOfficeName());

            cells = rows.createCell(5);
            cells.setCellValue(list.get(i).getUsername());

            cells = rows.createCell(6);
            cells.setCellValue(list.get(i).getProductName());

            cells = rows.createCell(7);
            cells.setCellValue(list.get(i).getType());

            cells = rows.createCell(8);
            cells.setCellValue(list.get(i).getColor());

            cells = rows.createCell(9);
            cells.setCellValue(list.get(i).getSize());

            cells = rows.createCell(10);
            cells.setCellValue(list.get(i).getTexture());

            cells = rows.createCell(11);
            cells.setCellValue(list.get(i).getPurchasedNumber());

            cells = rows.createCell(12);
            cells.setCellValue(list.get(i).getPurchaseprice());

            cells = rows.createCell(13);
            cells.setCellValue(list.get(i).getPurchasePriceSun());

            cells = rows.createCell(14);
            cells.setCellValue(list.get(i).getOutlibraryNumber());

        }
        try {
            OutputStream oStream = response.getOutputStream();
            wb.write(oStream);
            oStream.flush();
        } catch (FileNotFoundException e1) {
            // TODO Auto-generated catch block
            e1.printStackTrace();
        } catch (IOException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }

    }

Atas ialah kandungan terperinci Java实现数据库中查询出的数据转存成excel表. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!