操作 POI 从数据库导出数据到excel表格

最新推荐文章于 2025-06-11 13:40:00 发布
xiarinanfeng123
最新推荐文章于 2025-06-11 13:40:00 发布
阅读量2.2k
点赞数 1
CC 4.0 BY-SA版权
分类专栏： java 文章标签：数据库 poi excel
本文链接：https://blog.youkuaiyun.com/xiarinanfeng123/article/details/54603905
java 专栏收录该内容
16 篇文章
订阅专栏
本文介绍了一个使用Java实现的从数据库获取客户信息并导出为Excel文件的示例。该示例展示了如何创建Excel工作簿、设置字体样式、创建单元格样式等，并详细解释了如何填充数据及导出的过程。
摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >
这里先将要导出的数据封装到对象容器list中,得到users
if (users.size() > 0) {
            // 创建Excel的工作书册 Workbook,对应到一个excel文档
            HSSFWorkbook wb = new HSSFWorkbook();
            // 创建Excel的工作sheet,对应到一个excel文档的tab
            HSSFSheet sheet = wb.createSheet("客户列表");
            // 设置excel每列宽度
            sheet.setColumnWidth(0, 4000);
            sheet.setColumnWidth(1, 4000);
            sheet.setColumnWidth(2, 4000);
            sheet.setColumnWidth(3, 4000);
            sheet.setColumnWidth(4, 4000);
            sheet.setColumnWidth(5, 4000);
            sheet.setColumnWidth(6, 4000);
            sheet.setColumnWidth(7, 4000);
            sheet.setColumnWidth(8, 4000);
            sheet.setColumnWidth(9, 4000);
            sheet.setColumnWidth(10, 4000);
            sheet.setColumnWidth(11, 4000);
            sheet.setColumnWidth(12, 4000);
            sheet.setColumnWidth(13, 4000);
            sheet.setColumnWidth(14, 4000);
            sheet.setColumnWidth(15, 4000);

            // 创建字体样式
            HSSFFont font = wb.createFont();
            font.setFontName("Verdana");
            font.setBoldweight((short) 100);
            font.setFontHeight((short) 300);
            font.setColor(HSSFColor.BLUE.index);
            // 创建单元格样式
            HSSFCellStyle style = wb.createCellStyle();
            style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
            style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
            style.setFillForegroundColor(HSSFColor.LIGHT_TURQUOISE.index);
            style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
            // 设置边框
            style.setBottomBorderColor(HSSFColor.RED.index);
            style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
            style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
            style.setBorderRight(HSSFCellStyle.BORDER_THIN);
            style.setBorderTop(HSSFCellStyle.BORDER_THIN);
            style.setFont(font);// 设置字体
            // 创建Excel的sheet的一行
            HSSFRow row = sheet.createRow(0);
            row.setHeight((short) 500);// 设定行的高度
            // 创建一个Excel的单元格
            HSSFCell cell = row.createCell(0);
            // 合并单元格(startRow，endRow，startColumn，endColumn)
            sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 11));
            // 给Excel的单元格设置样式和赋值
            cell.setCellStyle(style);
            String title = "用户列表";
            cell.setCellValue(title );
            // 设置单元格内容格式时间
            HSSFCellStyle style1 = wb.createCellStyle();
            style1.setDataFormat(HSSFDataFormat.getBuiltinFormat("yyyy-mm-dd"));
            style1.setWrapText(true);// 自动换行
            style1.setAlignment(HSSFCellStyle.ALIGN_CENTER);
            HSSFCellStyle style2 = wb.createCellStyle();
            style2.setAlignment(HSSFCellStyle.ALIGN_CENTER);
            row = sheet.createRow(1);// 创建Excel的sheet的数据标题行
            cell = row.createCell(0);
            cell.setCellStyle(style2);
            cell.setCellValue("客户编号");
            cell = row.createCell(1);
            cell.setCellStyle(style2);
            cell.setCellValue("客户名称");
            cell = row.createCell(2);
            cell.setCellStyle(style2);
            cell.setCellValue("客户类型");
            cell = row.createCell(3);
            cell.setCellStyle(style2);
            cell.setCellValue("联系人");
            cell = row.createCell(4);
            cell.setCellStyle(style2);
            cell.setCellValue("联系电话");
            cell = row.createCell(5);
            cell.setCellStyle(style2);
            cell.setCellValue("收货时间");
            cell = row.createCell(6);
            cell.setCellStyle(style2);
            cell.setCellValue("履约地点");
            cell = row.createCell(7);
            cell.setCellStyle(style2);
            cell.setCellValue("地点名称");
            cell = row.createCell(8);
            cell.setCellStyle(style2);
            cell.setCellValue("对标门店价格");
            cell = row.createCell(9);
            cell.setCellStyle(style2);
            cell.setCellValue("对标价格门店名称");
            cell = row.createCell(10);
            cell.setCellStyle(style2);
            cell.setCellValue("更新人");
            cell = row.createCell(11);
            cell.setCellStyle(style2);
            cell.setCellValue("更新时间");
            cell = row.createCell(12);
            cell.setCellStyle(style2);
            cell.setCellValue("创建时间");
            cell = row.createCell(13);
            cell.setCellStyle(style2);
            cell.setCellValue("审核状态");
            cell = row.createCell(14);
            cell.setCellStyle(style2);
            cell.setCellValue("审核人员");
            cell = row.createCell(15);
            cell.setCellStyle(style2);
            cell.setCellValue("审核时间");
            for (int j = 0; j <= users.size()-1 ; j++) {
                User user = users.get(j);
                String a = "未审核";
                if(user.getUserInfo().getCheck_status()!=null){
                    if(user.getUserInfo().getCheck_status()==0)
                    { a = "未审核";}
                    if(user.getUserInfo().getCheck_status()==1)
                    { a = "审核";}
                }
                int i = 0;
                row = sheet.createRow(j+2);
                cell = row.createCell(i);
                cell.setCellStyle(style2);
                cell.setCellValue(user.getUser_contract_no());

                cell = row.createCell(++i);
                cell.setCellStyle(style2);
                cell.setCellValue(user.getUserInfo().getCust_name());

                cell = row.createCell(++i);
                cell.setCellStyle(style2);
                cell.setCellValue(user.getUserInfo().getCust_group());

                cell = row.createCell(++i);
                cell.setCellStyle(style2);
                cell.setCellValue(user.getUserInfo().getCust_con());

                cell = row.createCell(++i);
                cell.setCellStyle(style2);
                cell.setCellValue(user.getUserInfo().getCon_tel());

                cell = row.createCell(++i);
                cell.setCellStyle(style2);
                String json=user.getReceive_time();
                StringBuilder builder=new StringBuilder();
                if(StringUtils.isNotBlank(json)){
                    Map<String,String> map=Json.fromJson(Map.class,json);
                    for(Map.Entry<String,String> entity:map.entrySet()){
                        builder.append(entity.getKey()+param1+entity.getValue());
                    }
                }
                style2.setWrapText(true);//先设置为自动换行
                cell.setCellStyle(style2);
                cell.setCellValue(new HSSFRichTextString(builder.toString()));

                cell = row.createCell(++i);
                cell.setCellStyle(style2);
                cell.setCellValue(user.getUserInfo().getContract_store_no());

                cell = row.createCell(++i);
                cell.setCellStyle(style2);
                cell.setCellValue(user.getAddress());

                cell = row.createCell(++i);
                cell.setCellStyle(style2);
                cell.setCellValue(user.getUserInfo().getTarget_store_no());

                cell = row.createCell(++i);
                cell.setCellStyle(style2);
                cell.setCellValue(user.getUserInfo().getShop_name());

                cell = row.createCell(++i);
                cell.setCellStyle(style2);
                cell.setCellValue(user.getUserInfo().getUpdate_user());

                cell = row.createCell(++i);
                cell.setCellStyle(style2);
                cell.setCellValue(user.getUserInfo().getUpdate_time());

                cell = row.createCell(++i);
                cell.setCellStyle(style2);
                cell.setCellValue(user.getUserInfo().getAddtime());

                cell = row.createCell(++i);
                cell.setCellStyle(style2);
                cell.setCellValue(a);

                cell = row.createCell(++i);
                cell.setCellStyle(style2);
                cell.setCellValue(user.getUserInfo().getCheck_user());

                cell = row.createCell(++i);
                cell.setCellStyle(style2);
                cell.setCellValue(user.getUserInfo().getCheck_time());
            }

            SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMddHHmmss");
            String excel_name = sdf.format(new Date());
            try {
                response.setContentType("application/x-download");
                response.addHeader("Content-Disposition",
                        "attachment;filename=" + excel_name + ".xls");
                OutputStream os = response.getOutputStream();
                wb.write(os);
                os.close();
            } catch (FileNotFoundException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            } catch (IOException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }