java解析Excel/导入信息到Excel

朱雀 2024-04-17 06:16 70阅读 0赞

###### 一、话题背景 ######

最近一直在负责公司某一个项目的Excel导入导出功能，前前后后改了更多需求，需求的改动当然带来功能的改动,功能的改动肯定会带来许多问题。所幸，这些问题都被我一一攻破了。  
正所谓前事不忘，后事之师。解决了一些难题以后，应该及时回头寻找得失、汲取教训、总结经验，只有这样，下一次遇到类似的问题的时候，方能成竹在胸，有“法”可依，而不至于手足无措，方寸大乱。

###### 二、功能需求 ######

1.  Excel模板下载
2.  从Excel模板文件导入信息
3.  将信息导出到Excel模板
    
     *  锁定导出Excel文件的某些列（设置密码），禁止用户擅自更改信息，破坏数据完整性（相当于Excel里面的： 审阅---->保护工作表）
     *  设置Excel模板样式(行高/列宽/字体大小等等)
     *  …

###### 三、依赖jar包 ######

poi：Apache的poi 3.7版本(请自行下载，我这里不能上传了，上传的时候提示:该资源已经存在)  
lang3：[点我跳转][Link 1]

###### 四、技术实现 ######

###### 4.1、模板下载方法 ######

4.1.2 控制器方法ModelAndView ：

/**
         * 下载房源批量导入模版
         */
        @RequestMapping("usersExportExcel")
        public ModelAndView dowloadModel(ModelMap map, HttpServletRequest request, HttpServletResponse response) throws Exception {
        
    
            DownloadExcelModelUtil excelModelUtil = new DownloadExcelModelUtil();
            ExcelFileName excelFileName = new ExcelFileName();
            //指定系统中已经存在的模板文件（下面文件名中的敏感信息已经被我xxx代替了，请修改为你的文件名）
            excelFileName.setModelFileName("xxxInfoModel.xls");
            //设置Excel文件名
            excelFileName.setOutFileName("xxx信息导入模版");
            //设置导出的Excel表格Sheet文件名
            excelFileName.setSheetName("xxx信息导入模版");
            return excelModelUtil.DowloadModel(map, request, response, excelFileName);
        }

4.1.2、excelModelUtil方法：

/**
     * FileName: utilForExportExcel
     * Author:   wxz
     * Date:     2019-04-22 15:23
     * Description: 模板下载工具
     * History:
     * <author>          <time>          <version>          <desc>
     * 作者姓名:wxz       修改时间:       版本号:           描述:
     */
    
    import com.gxhylc.utils.CommonUtils;
    import org.apache.poi.hssf.usermodel.HSSFSheet;
    import org.apache.poi.hssf.usermodel.HSSFWorkbook;
    import org.springframework.ui.ModelMap;
    import org.springframework.web.servlet.ModelAndView;
    import org.springframework.web.servlet.view.document.AbstractExcelView;
    
    import javax.servlet.http.HttpServletRequest;
    import javax.servlet.http.HttpServletResponse;
    import java.io.FileInputStream;
    import java.io.OutputStream;
    import java.text.SimpleDateFormat;
    import java.util.Date;
    import java.util.HashMap;
    import java.util.Map;
    
    /**
     * 〈一句话功能简述〉<br> 
     * 〈模板下载工具〉
     *
     * @author wxz
     * @create 2019-04-22
     * @since 1.0.0
     */
    public class DownloadExcelModelUtil {
        
    
        /**
         * 模板下载工具
         * @param map
         * @param request
         * @param response
         * @param excelFileName
         * @return
         * @throws Exception
         */
        public ModelAndView DowloadModel(ModelMap map, HttpServletRequest request, HttpServletResponse response ,ExcelFileName excelFileName)
                throws Exception {
        
            //建立输入流
            String root = request.getSession().getServletContext().getRealPath("/");
            //指定文件所在位置
            FileInputStream fileSrc = new FileInputStream(root + "s/assets/room/model/"+excelFileName.getModelFileName());
            //建立excel对象
            HSSFWorkbook workbook = new HSSFWorkbook(fileSrc);
            HSSFSheet sheet = workbook.createSheet(excelFileName.getSheetName());
            sheet.setDefaultColumnWidth(20);
            ViewExcel view = new ViewExcel();
            Map<String,Object> file = new HashMap<>();
            file.put("outFileName",excelFileName.getOutFileName());
            view.buildExcelDocument(file, workbook, request, response);
            //关闭输入输出流
            fileSrc.close();
            return new ModelAndView(view, map);
        }
    
        private class ViewExcel extends AbstractExcelView{
        
            @Override
            protected void buildExcelDocument (Map<String, Object> model, HSSFWorkbook workbook, HttpServletRequest request,
                                              HttpServletResponse response) throws Exception {
        
                SimpleDateFormat format = new SimpleDateFormat("yyyyMMddHHmm");
                String filename = model.get("outFileName") + format.format(new Date(System.currentTimeMillis())) + ".xls";
    
                filename = CommonUtils.encodeFilename(filename, request);
                response.setContentType("application/vnd.ms-excel");
                response.setHeader("Content-disposition", "attachment;filename=" + filename);
                OutputStream out = response.getOutputStream();
                workbook.write(out);
                out.flush();
                out.close();
            }
        }
    
    }

备注：一下是我的文件放置位置，不知道如何寻找文件的童鞋请依样画葫芦  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM3ODQ0NDU0_size_16_color_FFFFFF_t_70]

4.1.3、excelModelUtil方法依赖的工具 CommonUtils：

public class CommonUtils {
        
         public static String encodeFilename(String filename, HttpServletRequest request) throws Exception
        {
            
    		String agent = request.getHeader("USER-AGENT");    
    		if ((agent != null) && (-1 != agent.indexOf("MSIE")))
    		{
            
    		      String newFileName = URLEncoder.encode(filename, "UTF-8");    
    		      //StringUtils工具在上面提供的lang3包里面,请自行下载.如果在使用的过程中有任何问题,欢迎留言
    		      newFileName = StringUtils.replace(newFileName, "+", "%20");    
    		      
    		      if (newFileName.length() > 150)
    		      {
            
    		    	  newFileName = new String(filename.getBytes("GB2312"), "ISO8859-1");    
    		    	  newFileName = StringUtils.replace(newFileName, " ", "%20");    
    		      }    
    		      
    		      return newFileName;    
    		}   
      
    		if ((agent != null) && (-1 != agent.indexOf("Mozilla")))    
    			return MimeUtility.encodeText(filename, "UTF-8", "B");    
    		
    		return filename;  
        }
        }

以上就是 模板下载的方法，只要前台访问这个方法，就会下载Excel文件

###### 4.2 从Excel模板中导入信息 ######

4.2.1 、前台方法

4.2.1.1、上传文件jq依赖:

4.2.1.2、模态框代码：

<%--导入房间model价格--%>
       <div class="modal fade" id="myModalPrice${item.jgId}" tabindex="-1" data-show="true" role="dialog"
              aria-labelledby="myModalLabel">
             <div class="modal-dialog" role="document">
                 <div class="modal-content">
                     <div class="modal-header">
                         <button type="button" class="close" data-dismiss="modal" aria-label="Close">
                             <span aria-hidden="true">×</span></button>
                         <h4 class="modal-title center" id="uploadRoomsPrice">房源价格导入</h4>
                     </div>
                     <div class="modal-body">
                         <div class="col-md-6">
                             <div class="col-md-6 fileinput-button">
    							<span class="fileinput-button">
    								<a class="btn green" href="">
    									<i class="fa fa-upload"></i> 从excel中导入房间价格
    								</a>
    									   
    								<input type="file" id="jfile2" class="jfile" name="file"
                                              data-url="xxx/usersExportExcel.do"
                                              multiple="" accept="application/vnd.ms-excel">
    							</span>
                              </div>
                     <div class="modal-footer">
                         <button type="button" class="btn btn-default" data-dismiss="modal">关闭</button>
                     </div>
                 </div>
             </div>
         </div>

4.2.1.3、上传Excel文件的jq代码:

$(function () {
        
            //开启控件文件上传功能
            $('.jfile').each(function () {
        
                $(this).fileupload({
        
                    progress: function (e, data) {
        
                        $('#myModal${item.jgId}').modal('hide');
                        var tip = result.result.result;
                        alert(tip);
                    },
                    done: function (e, result) {
        
                        $('#myModal${item.jgId}').modal('hide');
                        var tip = result.result.result;
                        alert(tip);
                    }
                });
            });
        });

4.2.1.4、效果图:

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM3ODQ0NDU0_size_16_color_FFFFFF_t_70 1]

4.2.2 后台解析上传的Excel文件

4.2.2.1、解析Excel控制器:

/**
         * 实现批量导入房源信息
         */
        @RequestMapping("usersExportExcel")
        @ResponseBody
        public Map importExcel(@RequestParam(value = "file", required = false) MultipartFile dataFile,
                               @RequestParam Map<String, Object> parameterMap, RedirectAttributes redirectAttributes) throws Exception {
        
            Map response = new HashMap();
            List<Map<String, String>> result = null;
            String uploadFileFileName = dataFile.getOriginalFilename();
    
            String party_id = parameterMap.get("party_id") + "";
            logger.info("机构id为:" + party_id);
    
            String type = uploadFileFileName.substring(uploadFileFileName.lastIndexOf("."));
            InputStream in = dataFile.getInputStream();
    
            Long count = 0L;
            String temp_obj = null;
    
            SimpleDateFormat format = new SimpleDateFormat("yyyyMMddHHmmss");
            //这个就是把时间戳经过处理得到期望格式的时间
            String batch_sign = format.format(System.currentTimeMillis());
                  //机构id不为空才能导入
            if (!"".equals(party_id)) {
        
                try {
        
                    result = getResult(type, in);
                    TXtJg item = this.gettXtJg(party_id);
                    Room room;
                    for (Map<String, String> map : result) {
        
                        if (map.isEmpty()) {
        
                            continue;
                        }
                        System.out.println("解析表格得到的数据：" + map);
    
    					//取出map里面的数据
                        temp_obj = replaceBlank(map.get("名字"));
                        if ("".equals(temp_obj)) {
        
                            continue;
                        }
                        else {
        
                           //得到数据，存入实体属性
                           room.setName();
                        }
    					//取出map里面的数据
                        temp_obj = replaceBlank(map.get("号码"));
                        if ("".equals(temp_obj)) {
        
                            continue;
                        }
                        else {
        
                           //得到数据，存入实体属性
                           room.setNo();
                        }
                        //.....
                        //调用保存方法
    
    					//清除result
                        result.clear();
    	                result = null;
    	                in.close();
    	                response.put("result", "导入成功，共导入 " + count + " 条数据，请刷新页面进行查看！");
                         }
                      } catch (Exception e) {
        
    	                e.printStackTrace();
    	                in.close();
    	                response = responeError(count);
    				  }
    		 }else {
        
    		            logger.info("party_id为空");
    		            response.put("result", "发生异常，导入数据失败");
    				}
    		      messageHelper.addFlashMessage(redirectAttributes, "core.success.save", "succese");
    		        return response;
    			}

4.2.2.2、解析Excel方法

/**
         * 根据传入的文件后缀返回解析结果
         * @param type
         * @param in
         * @return
         */
        private List<Map<String, String>> getResult(String type, InputStream in) {
        
            List<Map<String, String>> result = new ArrayList<>();
            String xls = ".xls";
            String xlsx = ".xlsx";
            try {
        
                if (xls.equals(type)) {
        
                    result = new CommonUtilsTwoLine().readExcelXLS(in);
                }
                else if (xlsx.equals(type)) {
        
                    result = new CommonUtilsTwoLine().readExcelXLSX(in);
                }
            }
            catch (Exception e) {
        
                e.printStackTrace();
            }
            return result;
        }

4.2.2.3、CommonUtilsTwoLine工具文件

public class CommonUtilsTwoLine {
        
            public List<Map<String,String>> readExcelXLS(InputStream in) throws Exception
        {
        
            POIFSFileSystem poiFileSystem = new POIFSFileSystem(in);
    
            HSSFWorkbook workbook= new HSSFWorkbook(poiFileSystem);
    
            return processWorkXLS(workbook);
        }
    
    
        public List<Map<String,String>> readExcelXLSX(InputStream in) throws Exception
        {
        
            XSSFWorkbook workbook = new XSSFWorkbook(in);
    
            return processWorkXLSX(workbook);
        }
    }

到这里从Excel文件中导入信息算是结束了。

###### 4.3 将数据导出到Excel ######

4.3.1、导出主入口方法

/**
         * 导出信息到Excel文件
         */
        @RequestMapping("downloadRoomPriceModel")
        public void downloadRoomPriceModel(@RequestParam Map<String, Object> parameterMap, ModelMap map, HttpServletRequest request, HttpServletResponse response)
                throws Exception {
        
            String party_id = parameterMap.get("party_id") + "";
            if (!"".equals(party_id)) {
        
                String hql = "from Room where delete_flag = '1' and sale_state = '-2' and party_id = ?";
                List<Room> room = roomManager.find(hql, party_id);
                new ExportToExcelUtil().exportToExcel(room, request, response);
            }
        }

4.3.2、ExportToExcelUtil工具类(设置表格列宽、保护Excel表格等等)

/**
     * FileName: ExportToExcelUtil
     * Author:   wxz
     * Date:     2019-05-21 16:50
     * Description: 将信息导出到Excel工具
     * History:
     * <author>          <time>          <version>          <desc>
     * 作者姓名:wxz       修改时间:       版本号:           描述:
     */
    
    
    import com.gxhylc.xxx;
    import org.apache.poi.hssf.usermodel.HSSFCellStyle;
    import org.apache.poi.hssf.usermodel.HSSFFont;
    import org.apache.poi.hssf.usermodel.HSSFWorkbook;
    import org.apache.poi.hssf.util.HSSFColor;
    import org.apache.poi.ss.usermodel.CellStyle;
    import org.apache.poi.ss.usermodel.Font;
    import org.apache.poi.ss.usermodel.Row;
    import org.apache.poi.ss.usermodel.Sheet;
    import org.apache.poi.xssf.streaming.SXSSFWorkbook;
    
    import javax.servlet.http.HttpServletRequest;
    import javax.servlet.http.HttpServletResponse;
    import java.util.ArrayList;
    import java.util.List;
    
    /**
     * 〈一句话功能简述〉<br>
     * 〈将信息导出到Excel工具〉
     *
     * @author wxz
     * @create 2019-05-21
     * @since 1.0.0
     */
    public class ExportToExcelUtil {
        
    
        public void exportToExcel(List<Room> returnList, HttpServletRequest request, HttpServletResponse response) throws Exception {
        
            request.setCharacterEncoding("GB2312");
            response.reset();
            try {
        
                 //设置导出Excel文件名
                String reportName = "xxx信息表";
    
                String utf8Name = java.net.URLEncoder.encode(reportName, "UTF-8");
                response.setHeader("Content-disposition", "attachment; filename=" + utf8Name + ".xls");
                response.setContentType("application/vnd.ms-excel");
                response.setBufferSize(1024 * 1024);
                HSSFWorkbook wb = new HSSFWorkbook();
                //设置导出Excel文件表格Sheet名
                Sheet sheet = wb.createSheet("xxxx信息表");
    
                //设置保护Excel全局，设置Excel编辑密码为123456
                sheet.protectSheet("123456");
                //设置Sheet表格标题
                List<String> titleList = this.getExcelTitle();
    
                //写入标题
                Row r = sheet.createRow(0);
                for (int i = 0; i < titleList.size(); i++) {
        
                    r.createCell(i).setCellValue(titleList.get(i));
                }
    
                //获取当前单元格的样式对象
                HSSFCellStyle alterableStyle = (HSSFCellStyle) wb.createCellStyle();
    			 //获取当前单元格的样式对象
                HSSFCellStyle nolockedStyle = (HSSFCellStyle) wb.createCellStyle();
    
                //设定单元格为锁定状态
                alterableStyle.setLocked(true);
                //设定单元格为非锁定状态
                nolockedStyle.setLocked(false);
    
    
                //向Excel写入数据
                for (int i = 0; i < returnList.size(); i++) {
        
                    if (returnList.size() <= 0) {
        
                        return;
                    }
                    Row row = sheet.createRow((i + 1));
                    Room room = returnList.get(i);
                    row.createCell((short) 0).setCellValue(room.getId());
                    if (room.getName() != null) {
        
                        String name= room.getName();
                        if (name!= null) {
        
                            row.createCell((short) 1).setCellValue(name);
                        }
                        else {
        
                            row.createCell((short) 1).setCellValue("");
                        }
                    }
                    row.createCell((short) 2).setCellValue(room.getNo());
                    row.createCell((short) 3).setCellValue(room.getSize());
                    
                    //设置当前行不保护,如果还有某一行不想设置保护,继续如下调用该方法
                    row.createCell((short) 3).setCellStyle(nolockedStyle);
    
    				....
    				
                    row.createCell((short) 17).setCellValue("");
                    //设置当前行不保护
                    row.createCell((short) 17).setCellStyle(nolockedStyle);
    
                    row.createCell((short) 18).setCellValue("");
                    //设置当前行不保护
                    row.createCell((short) 18).setCellStyle(nolockedStyle);
                }
    
                //调整列宽 9: 从这第九列开始 ,到最后一列结束。如果想设置行高，请调用 setColumnHidden() 方法
                for (int i = 9; i < 19; i++) {
        
                    if (i < 16) {
        
                        //第一个参数为第几列,第二个值为宽度值
                        sheet.setColumnWidth(i, 5000);
                    }
                    else {
        
                        sheet.setColumnWidth(i, 4000);
                    }
                }
    
                wb.write(response.getOutputStream());
                response.getOutputStream().flush();
                response.getOutputStream().close();
            }
            catch (Exception e) {
        
                e.printStackTrace();
            }
        }
    
        private final List<String> getExcelTitle() {
        
            List<String> titleList = new ArrayList<>();
            titleList.add("序号");
            titleList.add("名字");
            titleList.add("号码");
            titleList.add("xxx");
            //你的实体有多少个属性,就在这里写入属性名
            ....
            return titleList;
        }
    
    
        private final CellStyle cellBasicPropert(SXSSFWorkbook workbook) {
        
    
            // 表头格式
            CellStyle normalStyle = workbook.createCellStyle();
            normalStyle.setAlignment(CellStyle.ALIGN_CENTER);
            normalStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
            normalStyle.setFillForegroundColor(HSSFColor.LIGHT_TURQUOISE.index);
            Font font = workbook.createFont();
            //宽度
            font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
            font.setFontHeight((short) 250);
            // 加粗
            font.setBoldweight(Font.BOLDWEIGHT_NORMAL);
    
            normalStyle.setFont(font);
            // 自动换行
            normalStyle.setWrapText(true);
            return normalStyle;
        }
    }

将到这里，关于java解析Excel文件,包括导出数据到Excel、从Excel文件中导入数据、设置表格保护等等算是告一段落了，后面有啥再补充。有任何疑问欢迎留言或者邮箱联系，企鹅邮箱：my52hz@qq.com

[Link 1]: https://mvnrepository.com/artifact/org.apache.commons/lang3/3.1.0
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM3ODQ0NDU0_size_16_color_FFFFFF_t_70]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/15/ce0c20c422054417b466d6e3f8f99017.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM3ODQ0NDU0_size_16_color_FFFFFF_t_70 1]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/15/9ef2b90d29544388b934f8963e6bbc2d.png