精华内容
下载资源
问答
  • SpringBoot 导出Excel

    千次阅读 2019-01-12 05:01:42
    Springboot导出Excel -- poi 方式引入依赖创建表头与设置样式获取Excel数据 引入依赖 <dependency> <groupId>org.apache.poi</groupId> <artifactId>...

    引入依赖

     	<dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>3.14</version>
        </dependency>
    

    创建表头与设置样式

     /**
         * 订单导出Excel
         * 创建表头
         */
        private void createTitle(HSSFWorkbook workbook, HSSFSheet sheet){
            HSSFRow row = sheet.createRow(0);
            //设置列宽,setColumnWidth的第二个参数要乘以256,这个参数的单位是1/256个字符宽度
            sheet.setColumnWidth(2, 12*256);
            sheet.setColumnWidth(3, 17*256);
            //设置为居中加粗
            HSSFCellStyle style = workbook.createCellStyle();
            HSSFFont font = workbook.createFont();
            font.setBold(true);
            style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
            style.setFont(font);
    
            HSSFCell cell;
            cell = row.createCell(0);
            cell.setCellValue("id");
            cell.setCellStyle(style);
    
            cell = row.createCell(1);
            cell.setCellValue("用户ID");
            cell.setCellStyle(style);
    
            cell = row.createCell(2);
            cell.setCellValue("订单编号");
            cell.setCellStyle(style);
    
            cell = row.createCell(3);
            cell.setCellValue("应收金额");
            cell.setCellStyle(style);
    
            cell = row.createCell(4);
            cell.setCellValue("实际金额");
            cell.setCellStyle(style);
    
    
            cell = row.createCell(5);
            cell.setCellValue("付款方式");
            cell.setCellStyle(style);
    
    
            cell = row.createCell(6);
            cell.setCellValue("订单状态");
            cell.setCellStyle(style);
    
    
            cell = row.createCell(7);
            cell.setCellValue("是否提醒");
            cell.setCellStyle(style);
    
    
            cell = row.createCell(8);
            cell.setCellValue("下单时间");
            cell.setCellStyle(style);
    
    
            cell = row.createCell(9);
            cell.setCellValue("更新时间");
            cell.setCellStyle(style);
    
            cell = row.createCell(10);
            cell.setCellValue("付款时间");
            cell.setCellStyle(style);
    
    
            cell = row.createCell(11);
            cell.setCellValue("发货时间");
            cell.setCellStyle(style);
    
    
            cell = row.createCell(12);
            cell.setCellValue("收货时间");
            cell.setCellStyle(style);
    
    
            cell = row.createCell(13);
            cell.setCellValue("交易完成时间");
            cell.setCellStyle(style);
    
    
            cell = row.createCell(14);
            cell.setCellValue("交易关闭时间");
            cell.setCellStyle(style);
    
    
            cell = row.createCell(15);
            cell.setCellValue("物流名称");
            cell.setCellStyle(style);
    
    
            cell = row.createCell(16);
            cell.setCellValue("物流编号");
            cell.setCellStyle(style);
    
            cell = row.createCell(17);
            cell.setCellValue("运费");
            cell.setCellStyle(style);
    
            cell = row.createCell(18);
            cell.setCellValue("收货方式");
            cell.setCellStyle(style);
    
            cell = row.createCell(19);
            cell.setCellValue("是否发放积分");
            cell.setCellStyle(style);
    
            cell = row.createCell(20);
            cell.setCellValue("退货截至时间");
            cell.setCellStyle(style);
    
        }
    

    获取Excel数据

     /**
         * 获取excel数据
         */
        @GetMapping("/getExcel")
        public void getExcel(HttpServletResponse response) throws IOException {
            HSSFWorkbook workbook = new HSSFWorkbook();
            HSSFSheet sheet = workbook.createSheet("订单表");
            createTitle(workbook,sheet);
            List<OrderInfo> orderInfos;
    
    		---------------------分割线-----------------------------------
    
    				此处请自行插入需查询的业务逻辑数据
    
    		---------------------------------------------------------------       
    
           
            //设置日期格式
            HSSFCellStyle style=workbook.createCellStyle();
            style.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy h:mm"));
            SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
            //新增数据行,并且设置单元格数据
            int rowNum = 1;
            for (OrderInfo oderInfo: orderInfos) {
                HSSFRow row = sheet.createRow(rowNum);
                row.createCell(0).setCellValue(oderInfo.getId());
                row.createCell(1).setCellValue(oderInfo.getUserId());
                if (oderInfo.getCode()!=null)
                    row.createCell(2).setCellValue(oderInfo.getCode());
                if (oderInfo.getShouldPayment()!=null)
                    row.createCell(3).setCellValue(oderInfo.getShouldPayment());
                if (oderInfo.getActualPayment()!=null)
                    row.createCell(4).setCellValue(oderInfo.getActualPayment());
                if (oderInfo.getStatus()!=null)
                     row.createCell(5).setCellValue(oderInfo.getStatus());
                if (oderInfo.getIsRemind()!=null)
                     row.createCell(6).setCellValue(oderInfo.getIsRemind());
                if (oderInfo.getCreateTime()!=null)
                     row.createCell(7).setCellValue(dateFormat.format(oderInfo.getCreateTime()));
                if (oderInfo.getUpdateTime()!=null)
                     row.createCell(8).setCellValue(dateFormat.format(oderInfo.getUpdateTime()));
                if (oderInfo.getPaymentTime()!=null)
                     row.createCell(9).setCellValue(dateFormat.format(oderInfo.getPaymentTime()));
                if (oderInfo.getConsignTime()!=null)
                     row.createCell(10).setCellValue(dateFormat.format(oderInfo.getConsignTime()));
                if (oderInfo.getReceiverTime()!=null)
                    row.createCell(11).setCellValue(dateFormat.format(oderInfo.getReceiverTime()));
                if (oderInfo.getEndTime()!=null)
                    row.createCell(12).setCellValue(dateFormat.format(oderInfo.getEndTime()));
                if (oderInfo.getCloseTime()!=null)
                    row.createCell(13).setCellValue(dateFormat.format(oderInfo.getCloseTime()));
                if (oderInfo.getShippingName()!=null)
                    row.createCell(14).setCellValue(oderInfo.getShippingName());
                if (oderInfo.getShippingCode()!=null)
                     row.createCell(15).setCellValue(oderInfo.getShippingCode());
                if (oderInfo.getShippingMoney()!=null)
                    row.createCell(16).setCellValue(oderInfo.getShippingMoney());
                if (oderInfo.getReceiverWay()!=null)
                row.createCell(17).setCellValue(oderInfo.getReceiverWay());
                if (oderInfo.getPaymentStatus()!=null)
                row.createCell(18).setCellValue(oderInfo.getPaymentStatus());
                if (oderInfo.getCutoff_time()!=null)
                    row.createCell(19).setCellValue(dateFormat.format(oderInfo.getCutoff_time()));
                rowNum++;
            }
    
    
    //      拼装表名称
            String fileName = "订单表.xlsx";
            String dateTime = dateFormat.format(new Date());
            String blobName =  dateTime + "/" + UUID.randomUUID().toString().replaceAll("-", "") + "/" + fileName;
            response.setContentType("application/octet-stream");
            response.setHeader("Content-disposition", "attachment;filename=" + blobName);
            response.flushBuffer();
            workbook.write(response.getOutputStream());
            workbook.close();
        }
    

    本人98年菜鸟一枚,希望大神多多指点

    展开全文
  • SpringBoot导出excel

    2020-03-22 16:57:16
    SpringBoot导出excel 引入maven依赖: <!-- POI --> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi</artifactId> <version>3.9</version> &...

    SpringBoot导出excel

    引入maven依赖:

    <!-- https://mvnrepository.com/artifact/org.apache.poi/poi -->
    <dependency>
    	<groupId>org.apache.poi</groupId>
    	<artifactId>poi</artifactId>
    	<version>4.1.0</version>
    </dependency>
    
    <!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml -->
    <dependency>
    	<groupId>org.apache.poi</groupId>
    	<artifactId>poi-ooxml</artifactId>
    	<version>4.1.0</version>
    </dependency>
    

    工具类:

    import org.apache.poi.hssf.usermodel.HSSFCell;
    import org.apache.poi.hssf.usermodel.HSSFCellStyle;
    import org.apache.poi.hssf.usermodel.HSSFFont;
    import org.apache.poi.hssf.usermodel.HSSFRow;
    import org.apache.poi.hssf.usermodel.HSSFSheet;
    import org.apache.poi.hssf.usermodel.HSSFWorkbook;
    import org.apache.poi.ss.usermodel.HorizontalAlignment;
    import org.apache.poi.ss.usermodel.VerticalAlignment;
    import org.apache.poi.ss.util.CellRangeAddress;
    
    import java.text.SimpleDateFormat;
    import java.util.Date;
    
    public class ExcelOutUtil {
        /**
         * 导出Excel
         * @param sheetName
         *        sheet名称
         * @param title
         *        标题
         * @param values
         *        内容
         * @param wb
         *        HSSFWorkbook对象
         * @param head
         *        标题头
         * @return
         */
        public static HSSFWorkbook getHSSFWorkbook(String sheetName,
                                                   String[] title, String[][] values, HSSFWorkbook wb,String head) {
            // 第一步,创建一个HSSFWorkbook,对应一个Excel文件
            if (wb == null) {
                wb = new HSSFWorkbook();
            }
            // 第二步,在workbook中添加一个sheet,对应Excel文件中的sheet
            HSSFSheet sheet = wb.createSheet(sheetName);
            // 第三步,在sheet中添加表头第1行
            HSSFRow row = sheet.createRow(1);
    
            // 第四步,设置样式
            HSSFCellStyle cellStyle = wb.createCellStyle();
            HSSFFont font = wb.createFont();
            font.setFontHeightInPoints((short)11);
            cellStyle.setFont(font);
            cellStyle.setAlignment(HorizontalAlignment.CENTER);//居中
            cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中
            cellStyle.setWrapText(true);//自动换行
    
            // 创建总标题头
            HSSFRow rowHead = sheet.createRow(0);
            HSSFCell cellRowHead = rowHead.createCell(0);
            cellRowHead.setCellValue(head);
            cellRowHead.setCellStyle(cellStyle);
            rowHead.setHeightInPoints(36);
            CellRangeAddress cra = new CellRangeAddress(0,0,0,6);//合并单元格
            sheet.addMergedRegion(cra);//引用设计样式
    
            // 声明列对象
            HSSFRow row1=sheet.createRow(1);
            HSSFCell cell = row1.createCell(1);
            // 创建标题
            for (int i = 0; i < title.length; i++) {
                cell = row.createCell(i);
                cell.setCellValue(title[i]);
                cell.setCellStyle(cellStyle);
            }
    
            // 创建内容
            for (int i = 0; i < values.length; i++) {
                row = sheet.createRow(i + 2);
                row.setHeightInPoints(20);//设置行高
                int width=10;//设置宽度
                sheet.setColumnWidth(0,256*width+384);
                sheet.setColumnWidth(1,256*width+384);
                sheet.setColumnWidth(2,256*width+384);
                sheet.setColumnWidth(3,256*width+384);
                sheet.setColumnWidth(4,256*width+384);
                sheet.setColumnWidth(5,256*width+384);
                sheet.setColumnWidth(6,256*width+484);
                for (int j = 0; j < values[i].length; j++) {
                    // 将内容按顺序赋给对应的列对象
                    row.createCell(j).setCellValue(values[i][j]);
                    row.setRowStyle(cellStyle);
                }
            }
            return wb;
        }
    
        // 根据参数返回一个二维数组
        public static String[][] getContent(int length) {
            return new String[length][];
        }
    
        /**
         * 用于时间命名
         * @return
         */
        public String getDataTime(){
            Date date = new Date();
            SimpleDateFormat dateFormat= new SimpleDateFormat("yyyyMMddhhmmss");
            String dataTime = dateFormat.format(date);
            return dataTime;
        }
    
        /**
         * 格式化列的类型
         * @param cell 列
         * @return 统一返回字符串
         */
    	/*public static String formatCell(HSSFCell cell) {
    		if (cell == null) {
    			return "";
    		} else {
    			//return String.valueOf(cell.getRichStringCellValue()) 返回字符串
    			if (cell.getCellType() == HSSFCell.CELL_TYPE_BOOLEAN) {//布尔型
    				return cell.getBooleanCellValue() ? "true" : "false";
    			} else if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {//数值型
    				cell.setCellType(cell.CELL_TYPE_STRING);//数值转为字符串类型
    				return cell.getStringCellValue();
    			}else if(cell.getCellType() == HSSFCell.CELL_TYPE_FORMULA){//公式型
    				return cell.getCellFormula();
    			}else if(cell.getCellType() == HSSFCell.CELL_TYPE_STRING){//字符串
    				return cell.getStringCellValue();
    			}else {
    				return "";
    			}
    		}
    	}*/
    }
    

    cotroller类:

    import java.io.IOException;
    import java.io.OutputStream;
    import java.io.UnsupportedEncodingException;
    import java.util.List;
    import java.util.Map;
    
    import javax.servlet.http.HttpServletRequest;
    import javax.servlet.http.HttpServletResponse;
    
    import org.apache.poi.hssf.usermodel.HSSFWorkbook;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.web.bind.annotation.CrossOrigin;
    import org.springframework.web.bind.annotation.GetMapping;
    import org.springframework.web.bind.annotation.RestController;
    
    import com.project.main.excel.out.entity.WorkersVO;
    import com.project.main.excel.out.service.WorkersOutService;
    import com.project.main.excel.out.utils.ExcelOutUtil;
    import com.project.main.utils.R;
    
    @RestController
    @CrossOrigin
    public class WorkersOutController {
    	
    	@Autowired
    	private WorkersOutService workersService;
    	
    	@GetMapping("/excelDownload")
        public R excelDownload(HttpServletRequest request, HttpServletResponse response){
            //Map<String, Object> map = getQueryMap();
            List<WorkersVO> workersList = workersService.getWorkersToExcelOut();//要导出的数据集合
            //excel标题
            String[] title = {"工号","姓名","年龄","性别","居住地址","职位","入职日期"};
            // excel文件名
            ExcelOutUtil eu = new ExcelOutUtil();
            String dataName = eu.getDataTime();
            String fileName = dataName+"员工信息表.xls";
            String[][] content = ExcelOutUtil.getContent(workersList.size());
            String sheetName = "信息";
            String head = "员工信息表";
            for (int i = 0; i <workersList.size(); i++) {
                content[i] = new String[title.length];
                WorkersVO workersVO = workersList.get(i);
                content[i][0] = workersVO.getCardNum();
                content[i][1] = workersVO.getWorkerName();
                content[i][2] = workersVO.getAge();
                content[i][3] = workersVO.getSex();
                content[i][4] = workersVO.getAddress();
                content[i][5] = workersVO.getPosition();
                content[i][6] = workersVO.getWorkDate();
               
            }
            HSSFWorkbook hwb = ExcelOutUtil.getHSSFWorkbook(sheetName, title, content,
                    null, head);
            try {
                this.setResponseHeader(response, fileName);
                OutputStream os = response.getOutputStream();
                hwb.write(os);
                os.flush();
                os.close();
                return null;
            } catch (IOException e) {
                e.printStackTrace();
            }
            return R.ok("导出成功...");
        }
    
        private Map<String, Object> getQueryMap() {
    		// TODO Auto-generated method stub
    		return null;
    	}
    
    	// 发送响应流方法
        public void setResponseHeader(HttpServletResponse response, String fileName) {
            try {
                try {
                    fileName = new String(fileName.getBytes(),"ISO-8859-1");
                } catch (UnsupportedEncodingException e) {
                    e.printStackTrace();
                }
                response.setContentType("application/octet-stream;charset=ISO8859-1");
                response.setHeader("Content-Disposition", "attachment;filename="
                        + fileName);
                response.addHeader("Pargam", "no-cache");
                response.addHeader("Cache-Control", "no-cache");
            } catch (Exception ex) {
                //log.info("导出excel功能,发送响应流方法异常!");
                ex.printStackTrace();
            }
        }
    }
    

    此导出excel适用于简易型导出,无过多的样式修饰
    导出样例
    在这里插入图片描述

    更多开源开发软件,可在微信公众号搜索"小逸分享"前去提取哦!

    展开全文
  • springboot导出excel

    2018-04-04 14:53:49
    使用springboot开发,将excel功能嵌入到里面,在线导出excel
  • SpringBoot导出Excel

    2019-07-10 16:09:29
    页面样式,这里结合通过选择下拉框进行导出: <div class="panel-body"> <div class="form-inline"> <label>选择小区:</label> <div class="form-group"> ...

    页面样式,这里结合通过选择下拉框进行导出:

    <div class="panel-body">
                <div class="form-inline">
                    <label>选择小区:</label>
                    <div class="form-group">
                        <select id="areas" class="selectpicker" multiple data-live-search="true" data-live-search-placeholder="Search" data-actions-box="true" data-style="btn-default btn-sm" data-size="5">
                            <option th:each="haAreas : ${haAreasList}"
                                    th:value="${haAreas.areaId}" th:text="${haAreas.areaNo}+':'+${haAreas.haName}"></option>
                        </select>
                    </div>
                    <button type="button" id="templateExport" class="btn btn-default btn-sm btnMargin">导出</button>
                </div>
            </div>

    当点击时传值跳转方法,不要用ajax跳转,无反应:

    $("#templateExport").click(function(){
                var areaId = $("#areas").val();
                var loadLayerIndex = "";
                if($("#areas").val() == null){
                    window.open(get_excel_model);
                    return false;
                }
                window.location.href="/room/CustomerExport?areaId="+areaId;
            });

    下面创建一个导出Excel工具类:

    package com.ktamr.common.utils.export;
    
    import org.apache.poi.hssf.usermodel.*;
    import org.apache.poi.ss.usermodel.HorizontalAlignment;
    
    public class ExcelUtil {
        /**
         * 导出Excel
         * @param sheetName sheet名称
         * @param title 标题
         * @param values 内容
         * @param wb HSSFWorkbook对象
         * @return
         */
        public static HSSFWorkbook getHSSFWorkbook(String sheetName, String []title, String [][]values, HSSFWorkbook wb){
    
            // 第一步,创建一个HSSFWorkbook,对应一个Excel文件
            if(wb == null){
                wb = new HSSFWorkbook();
            }
    
            // 第二步,在workbook中添加一个sheet,对应Excel文件中的sheet
            HSSFSheet sheet = wb.createSheet(sheetName);
    
            // 第三步,在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制
            HSSFRow row = sheet.createRow(0);
    
            // 第四步,创建单元格,并设置值表头 设置表头居中
            HSSFCellStyle style = wb.createCellStyle();
            style.setAlignment(HorizontalAlignment.CENTER); // 创建一个居中格式
    
            //声明列对象
            HSSFCell cell = null;
    
            //创建标题
            for(int i=0;i<title.length;i++){
                cell = row.createCell(i);
                cell.setCellValue(title[i]);
                cell.setCellStyle(style);
            }
    
            //创建内容
            for(int i=0;i<values.length;i++){
                row = sheet.createRow(i + 1);
                for(int j=0;j<values[i].length;j++){
                    //将内容按顺序赋给对应的列对象
                    row.createCell(j).setCellValue(values[i][j]);
                }
            }
            return wb;
        }
    }
    

    Collectorller控制器代码:

    //发送响应流方法
        public void setResponseHeader(HttpServletResponse response, String fileName) {
            try {
                try {
                    fileName = new String(fileName.getBytes(), "UTF-8");
                } catch (UnsupportedEncodingException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                }
                response.setContentType("application/octet-stream;charset=UTF-8");
                response.setHeader("Content-Disposition", "attachment;filename=" + fileName);
                response.addHeader("Pargam", "no-cache");
                response.addHeader("Cache-Control", "no-cache");
            } catch (Exception ex) {
                ex.printStackTrace();
            }
        }
    
        @RequestMapping("/CustomerExport")
        @ResponseBody
        public void customerExport(HttpServletResponse response, Integer areaId) throws Exception {
            List<HaRoom> rooms = haRoomService.customExport(areaId);
    
            //excel标题
            String[] title = {"1", "2", "3", "4", "5", "6", "7", "8", "9", "10", "11", "12", "13", "14", "15", "16", "17", "18", "19", "20"};
    
            //excel文件名
            String fileName = "kt-userTable" + System.currentTimeMillis() + ".xls";
    
            //sheet名
            String sheetName = "sheet1";
    
            String[][] content = new String[rooms.size()][];
            for (int i = 0; i < rooms.size(); i++) {
                content[i] = new String[title.length];
                HaRoom obj = rooms.get(i);
                //捕捉异常,如果值为空赋空值
                try {
                    content[i][0] = obj.getHaCustom().getCustNo(); 
                } catch (Exception e) {
                    e.printStackTrace();
                    content[i][0] = "";
                }
                try {
                    content[i][1] = obj.getHaCustom().getName();
                } catch (Exception e) {
                    e.printStackTrace();
                    content[i][1] = "";
                }
                content[i][2] = obj.getHaArea().getHaName(); 
                content[i][3] = obj.getHaBuilding().getName(); 
                content[i][4] = obj.getName(); 
                try {
                    content[i][5] = String.valueOf(obj.getHaMeter().getMeterNumber()); 
                } catch (Exception e) {
                    e.printStackTrace();
                    content[i][5] = "";
                }
                try {
                    content[i][6] = obj.getHaMeter().getMeterChannel().toString(); 
                } catch (Exception e) {
                    e.printStackTrace();
                    content[i][6] = "";
                }
                try {
                    content[i][7] = obj.getHaMeter().getMeterSequence().toString(); 
                } catch (Exception e) {
                    e.printStackTrace();
                    content[i][7] = "";
                }
                try {
                    content[i][8] = String.valueOf(obj.getHaMeter().getVendorId()); 
                } catch (Exception e) {
                    e.printStackTrace();
                    content[i][8] = "";
                }
                try {
                    content[i][9] = obj.getHaCentor().getCentorNo(); 
                } catch (Exception e) {
                    e.printStackTrace();
                    content[i][9] = "";
                }
                try {
                    content[i][10] = obj.getHaCollector().getNconf().toString(); 
                } catch (Exception e) {
                    e.printStackTrace();
                    content[i][10] = "";
                }
                try {
                    content[i][11] = obj.getHaMetertype().getName(); 
                } catch (Exception e) {
                    e.printStackTrace();
                    content[i][11] = "";
                }
                try {
                    content[i][12] = String.valueOf(obj.getHaMeter().getIsBranch()); 
                } catch (Exception e) {
                    e.printStackTrace();
                    content[i][12] = "";
                }
                try {
                    content[i][13] = String.valueOf(obj.getHaMeter().getGnumber()); 
                } catch (Exception e) {
                    e.printStackTrace();
                    content[i][13] = "";
                }
                try {
                    content[i][14] = String.valueOf(obj.getHaMeter().getStartTime()); 
                } catch (Exception e) {
                    e.printStackTrace();
                    content[i][14] = "";
                }
    
                try {
                    content[i][15] = obj.getHaPricestandard().getName();
                } catch (Exception e) {
                    e.printStackTrace();
                    content[i][15] = "";
                }
                try {
                    content[i][16] = String.valueOf(obj.getHaMeter().getRate());
                } catch (Exception e) {
                    e.printStackTrace();
                    content[i][16] = "";
                }
                try {
                    content[i][17] = obj.getHaCustom().getSex();
                } catch (Exception e) {
                    e.printStackTrace();
                    content[i][17] = "";
                }
                try {
                    content[i][18] = obj.getHaCustom().getMobil();
                } catch (Exception e) {
                    e.printStackTrace();
                    content[i][18] = "";
                }
                try {
                    content[i][19] = String.valueOf(obj.getHaCustom().getBalance());
                } catch (Exception e) {
                    e.printStackTrace();
                    content[i][19] = "";
                }
            }
    
            //创建HSSFWorkbook
            HSSFWorkbook wb = ExcelUtil.getHSSFWorkbook(sheetName, title, content, null);
    
            //响应到客户端
            try {
                this.setResponseHeader(response, fileName);
                OutputStream os = response.getOutputStream();
                wb.write(os);
                os.flush();
                os.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }

    导出Excel效果图:

     

    展开全文
  • SpringBoot导出Excel模板

    2020-10-23 14:15:21
    文章目录SpringBoot导出Excel模板pom.xml依赖Excel模板文件Controller测试 SpringBoot导出Excel模板 共同探讨,向各位大佬学习 走向CEO,迎娶白富美 pom.xml依赖 <!--excel--> <dependency> <...

    SpringBoot导出Excel模板

    共同探讨,向各位大佬学习
    走向CEO,迎娶白富美

    pom.xml依赖

    <!--excel-->
            <dependency>
                <groupId>org.apache.poi</groupId>
                <artifactId>poi-ooxml</artifactId>
                <version>3.11</version>
            </dependency>
            <dependency>
                <groupId>org.apache.poi</groupId>
                <artifactId>poi</artifactId>
                <version>3.11</version>
            </dependency>
    

    Excel模板文件

    在项目resources包下创建templates,将准备好的模板文件放在templates包下。
    excel模板文件
    在这里插入图片描述

    Controller

        @GetMapping("/report")
        public void report(HttpServletResponse response){
            try {
                //从数据库获取数据
                List<User> userList = userMapper.selectList(null);
                //获取Excel模板文件绝对磁盘路径
                String path = ResourceUtils.getFile(ResourceUtils.CLASSPATH_URL_PREFIX + "templates/user.xlsx").getPath();
                //基于POI在内存中创建一个Excel文件
                XSSFWorkbook excel= new XSSFWorkbook(new FileInputStream(new File(path)));
                //获取第一个工作表
                XSSFSheet sheet = excel.getSheetAt(0);
                //设置行变量
                int firstNum = 0;
                //遍历集合
                for (User user : userList) {
                    //从第二行开始创建新行
                    XSSFRow row = sheet.createRow(++firstNum);
                    //创建新列,并设值
                    row.createCell(0).setCellValue(user.getName());
                    row.createCell(1).setCellValue(user.getPassword());
                    row.createCell(2).setCellValue(user.getAge());
                    row.createCell(3).setCellValue(user.getSex());
                }
                //创建输出流,用于从服务器写数据到客户端浏览器
                ServletOutputStream out = response.getOutputStream();
                response.setContentType("application/vnd.ms-excel");
                response.setHeader("content-Disposition", "attachment;filename=user.xlsx");
                excel.write(out);
                //关闭流
                out.flush();
                out.close();
                excel.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    

    测试

    在这里插入图片描述
    ———————————————————————————————————————
    在这里插入图片描述

    展开全文
  • springboot 导出excel

    2019-07-06 12:11:00
    依赖 <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-ooxml</artifactId> <version>3.17</version>...
  • SpringBoot导出Excel表格

    2020-06-09 09:31:06
    SpringBoot导出excel表格 第一步 导入依赖 <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi</artifactId> <version>3.8</version> </...
  • Springboot导出excel文件并下载 文章目录Springboot导出excel文件并下载前言一、引入Maven包?二、数据库查询数据2.1 实体类2.2 mapper三、导出代码四、 补充4.1 问题4.2 方案4.3 建议 前言 在项目中,我们难免...
  • SpringBoot 导出Excel功能

    2021-05-20 13:41:12
    SpringBoot 导出Excel功能 前言:话不多说,直接上代码。 一、pom.xml依赖 <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-ooxml</artifactId> <version&...
  • Springboot导出excel,合并单元格示例

    千次阅读 2019-01-21 13:26:34
    原文链接:Springboot导出excel,合并单元格示例 更多文章,欢迎访问:Java知音,一个专注于技术分享的网站 以下用一个示例来说明springboot如何导出数据到excel。 首先引入Maven依赖: &lt;dependency&gt; ...
  • SpringBoot导出excel数据报错Could not find acceptable representation 今天在实现后台导出数据时,导出数据一直报错: org.springframework.web.HttpMediaTypeNotAcceptableException: Could not find acceptable ...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 4,856
精华内容 1,942
关键字:

springboot导出excel

spring 订阅