java解析Excel/導入信息到Excel方式

更新時間：2023年11月02日 16:34:43 作者：寫完bug就找女朋友

這篇文章主要介紹了java解析Excel/導入信息到Excel方式,具有很好的參考價值,希望對大家有所幫助,如有錯誤或未考慮完全的地方,望不吝賜教

一、話題背景

最近一直在負責公司某一個項目的Excel導入導出功能，前前后后改了更多需求，需求的改動當然帶來功能的改動,功能的改動肯定會帶來許多問題。所幸，這些問題都被我一一攻破了。

正所謂前事不忘，后事之師。解決了一些難題以后，應該及時回頭尋找得失、汲取教訓、總結(jié)經(jīng)驗，只有這樣，下一次遇到類似的問題的時候，方能成竹在胸，有“法”可依，而不至于手足無措，方寸大亂。

二、功能需求

Excel模板下載

從Excel模板文件導入信息

將信息導出到Excel模板

鎖定導出Excel文件的某些列（設置密碼），禁止用戶擅自更改信息，破壞數(shù)據(jù)完整性（相當于Excel里面的：審閱---->保護工作表）
設置Excel模板樣式(行高/列寬/字體大小等等)
…

三、依賴jar包

poi：Apache的poi 3.7版本(請自行下載，我這里不能上傳了，上傳的時候提示:該資源已經(jīng)存在)

lang3：點我跳轉(zhuǎn)

四、技術(shù)實現(xiàn)

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();
        //指定系統(tǒng)中已經(jīng)存在的模板文件（下面文件名中的敏感信息已經(jīng)被我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);
        //關(guān)閉輸入輸出流
        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();
        }
    }

}

備注：一下是我的文件放置位置，不知道如何尋找文件的童鞋請依樣畫葫蘆

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依賴：

<script src="${ctx}/s/assets/plugins/chineserp-jquery-master/src/jquery.regionpicker.js"></script>
<link rel="stylesheet"
      href="${ctx}/s/assets/plugins/chineserp-jquery-master/dist/jquery.chineserp.css" rel="external nofollow" 
      media="screen">
<link rel="stylesheet" href="${ctx}/s/assets/plugins/jquery-file-upload/css/jquery.fileupload-ui.css" rel="external nofollow" >

<script src="${ctx}/s/assets/plugins/jquery-file-upload/js/jquery.fileupload.js" language="javascript"
        type="text/javascript"></script>

4.2.1.2、模態(tài)框代碼：

<%--導入房間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">&times;</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>
									   <!---這個自定義屬性data-url就是你訪問控制器下載模板文件的地址,我這里已經(jīng)修改了關(guān)鍵信息,請你修改為你自己的地址-->
								<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">關(guān)閉</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、效果圖：

4.2.2 后臺解析上傳的Excel文件

4.2.2.1、解析Excel控制器：

        /**
     * 實現(xiàn)批量導入房源信息
     */
    @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("機構(gòu)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");
        //這個就是把時間戳經(jīng)過處理得到期望格式的時間
        String batch_sign = format.format(System.currentTimeMillis());
              //機構(gòu)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("解析表格得到的數(shù)據(jù)：" + map);

					//取出map里面的數(shù)據(jù)
                    temp_obj = replaceBlank(map.get("名字"));
                    if ("".equals(temp_obj)) {
                        continue;
                    }
                    else {
                       //得到數(shù)據(jù)，存入實體屬性
                       room.setName();
                    }
					//取出map里面的數(shù)據(jù)
                    temp_obj = replaceBlank(map.get("號碼"));
                    if ("".equals(temp_obj)) {
                        continue;
                    }
                    else {
                       //得到數(shù)據(jù)，存入實體屬性
                       room.setNo();
                    }
                    //.....
                    //調(diào)用保存方法

					//清除result
                    result.clear();
	                result = null;
	                in.close();
	                response.put("result", "導入成功，共導入 " + count + " 條數(shù)據(jù)，請刷新頁面進行查看！");
                     }
                  } catch (Exception e) {
	                e.printStackTrace();
	                in.close();
	                response = responeError(count);
				  }
		 }else {
		            logger.info("party_id為空");
		            response.put("result", "發(fā)生異常，導入數(shù)據(jù)失敗");
				}
		      messageHelper.addFlashMessage(redirectAttributes, "core.success.save", "succese");
		        return response;
			}

4.2.2.2、解析Excel方法

       
    /**
     * 根據(jù)傳入的文件后綴返回解析結(jié)果
     * @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文件中導入信息算是結(jié)束了。

4.3 將數(shù)據(jù)導出到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();

            //設定單元格為鎖定狀態(tài)
            alterableStyle.setLocked(true);
            //設定單元格為非鎖定狀態(tài)
            nolockedStyle.setLocked(false);


            //向Excel寫入數(shù)據(jù)
            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());
                
                //設置當前行不保護,如果還有某一行不想設置保護,繼續(xù)如下調(diào)用該方法
                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);
            }

            //調(diào)整列寬 9: 從這第九列開始 ,到最后一列結(jié)束。如果想設置行高，請調(diào)用 setColumnHidden() 方法
            for (int i = 9; i < 19; i++) {
                if (i < 16) {
                    //第一個參數(shù)為第幾列,第二個值為寬度值
                    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;
    }
}

將到這里，關(guān)于java解析Excel文件,包括導出數(shù)據(jù)到Excel、從Excel文件中導入數(shù)據(jù)、設置表格保護等等算是告一段落了，后面有啥再補充。