java解析Excel/導(dǎo)入信息到Excel方式
更新時(shí)間：2023年11月02日 16:34:43 作者：寫(xiě)完bug就找女朋友
這篇文章主要介紹了java解析Excel/導(dǎo)入信息到Excel方式,具有很好的參考價(jià)值,希望對(duì)大家有所幫助,如有錯(cuò)誤或未考慮完全的地方,望不吝賜教
一、話題背景
最近一直在負(fù)責(zé)公司某一個(gè)項(xiàng)目的Excel導(dǎo)入導(dǎo)出功能，前前后后改了更多需求，需求的改動(dòng)當(dāng)然帶來(lái)功能的改動(dòng),功能的改動(dòng)肯定會(huì)帶來(lái)許多問(wèn)題。所幸，這些問(wèn)題都被我一一攻破了。
正所謂前事不忘，后事之師。解決了一些難題以后，應(yīng)該及時(shí)回頭尋找得失、汲取教訓(xùn)、總結(jié)經(jīng)驗(yàn)，只有這樣，下一次遇到類似的問(wèn)題的時(shí)候，方能成竹在胸，有“法”可依，而不至于手足無(wú)措，方寸大亂。
二、功能需求
Excel模板下載
從Excel模板文件導(dǎo)入信息
將信息導(dǎo)出到Excel模板
- 鎖定導(dǎo)出Excel文件的某些列（設(shè)置密碼），禁止用戶擅自更改信息，破壞數(shù)據(jù)完整性（相當(dāng)于Excel里面的： 審閱---->保護(hù)工作表）
- 設(shè)置Excel模板樣式(行高/列寬/字體大小等等)
- …
三、依賴jar包
poi：Apache的poi 3.7版本(請(qǐng)自行下載，我這里不能上傳了，上傳的時(shí)候提示:該資源已經(jīng)存在)
lang3：點(diǎn)我跳轉(zhuǎn)
四、技術(shù)實(shí)現(xiàn)
4.1 模板下載方法
- 4.1.2 控制器方法ModelAndView ：
/**
* 下載房源批量導(dǎo)入模版
*/
@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代替了，請(qǐng)修改為你的文件名）
excelFileName.setModelFileName("xxxInfoModel.xls");
//設(shè)置Excel文件名
excelFileName.setOutFileName("xxx信息導(dǎo)入模版");
//設(shè)置導(dǎo)出的Excel表格Sheet文件名
excelFileName.setSheetName("xxx信息導(dǎo)入模版");
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 修改時(shí)間: 版本號(hào): 描述:
*/
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;
/**
* 〈一句話功能簡(jiǎn)述〉<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對(duì)象
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();
}
}
}
備注：一下是我的文件放置位置，不知道如何尋找文件的童鞋請(qǐng)依樣畫(huà)葫蘆
- 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包里面,請(qǐng)自行下載.如果在使用的過(guò)程中有任何問(wèn)題,歡迎留言
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;
}
}
以上就是 模板下載的方法，只要前臺(tái)訪問(wèn)這個(gè)方法，就會(huì)下載Excel文件
4.2 從Excel模板中導(dǎo)入信息
- 4.2.1 、前臺(tái)方法
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)框代碼：
<%--導(dǎo)入房間model價(jià)格--%>
<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">房源價(jià)格導(dǎo)入</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中導(dǎo)入房間價(jià)格
</a>
<!---這個(gè)自定義屬性data-url就是你訪問(wèn)控制器下載模板文件的地址,我這里已經(jīng)修改了關(guān)鍵信息,請(qǐng)你修改為你自己的地址-->
<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 () {
//開(kāi)啟控件文件上傳功能
$('.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 后臺(tái)解析上傳的Excel文件
4.2.2.1、解析Excel控制器：
/**
* 實(shí)現(xiàn)批量導(dǎo)入房源信息
*/
@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("機(jī)構(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");
//這個(gè)就是把時(shí)間戳經(jīng)過(guò)處理得到期望格式的時(shí)間
String batch_sign = format.format(System.currentTimeMillis());
//機(jī)構(gòu)id不為空才能導(dǎo)入
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ù)，存入實(shí)體屬性
room.setName();
}
//取出map里面的數(shù)據(jù)
temp_obj = replaceBlank(map.get("號(hào)碼"));
if ("".equals(temp_obj)) {
continue;
}
else {
//得到數(shù)據(jù)，存入實(shí)體屬性
room.setNo();
}
//.....
//調(diào)用保存方法
//清除result
result.clear();
result = null;
in.close();
response.put("result", "導(dǎo)入成功，共導(dǎo)入 " + count + " 條數(shù)據(jù)，請(qǐng)刷新頁(yè)面進(jìn)行查看！");
}
} catch (Exception e) {
e.printStackTrace();
in.close();
response = responeError(count);
}
}else {
logger.info("party_id為空");
response.put("result", "發(fā)生異常，導(dǎo)入數(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文件中導(dǎo)入信息算是結(jié)束了。
4.3 將數(shù)據(jù)導(dǎo)出到Excel
- 4.3.1、導(dǎo)出主入口方法
/**
* 導(dǎo)出信息到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工具類(設(shè)置表格列寬、保護(hù)Excel表格等等)
/**
* FileName: ExportToExcelUtil
* Author: wxz
* Date: 2019-05-21 16:50
* Description: 將信息導(dǎo)出到Excel工具
* History:
* <author> <time> <version> <desc>
* 作者姓名:wxz 修改時(shí)間: 版本號(hào): 描述:
*/
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;
/**
* 〈一句話功能簡(jiǎn)述〉<br>
* 〈將信息導(dǎo)出到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 {
//設(shè)置導(dǎo)出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();
//設(shè)置導(dǎo)出Excel文件表格Sheet名
Sheet sheet = wb.createSheet("xxxx信息表");
//設(shè)置保護(hù)Excel全局，設(shè)置Excel編輯密碼為123456
sheet.protectSheet("123456");
//設(shè)置Sheet表格標(biāo)題
List<String> titleList = this.getExcelTitle();
//寫(xiě)入標(biāo)題
Row r = sheet.createRow(0);
for (int i = 0; i < titleList.size(); i++) {
r.createCell(i).setCellValue(titleList.get(i));
}
//獲取當(dāng)前單元格的樣式對(duì)象
HSSFCellStyle alterableStyle = (HSSFCellStyle) wb.createCellStyle();
//獲取當(dāng)前單元格的樣式對(duì)象
HSSFCellStyle nolockedStyle = (HSSFCellStyle) wb.createCellStyle();
//設(shè)定單元格為鎖定狀態(tài)
alterableStyle.setLocked(true);
//設(shè)定單元格為非鎖定狀態(tài)
nolockedStyle.setLocked(false);
//向Excel寫(xiě)入數(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());
//設(shè)置當(dāng)前行不保護(hù),如果還有某一行不想設(shè)置保護(hù),繼續(xù)如下調(diào)用該方法
row.createCell((short) 3).setCellStyle(nolockedStyle);
....
row.createCell((short) 17).setCellValue("");
//設(shè)置當(dāng)前行不保護(hù)
row.createCell((short) 17).setCellStyle(nolockedStyle);
row.createCell((short) 18).setCellValue("");
//設(shè)置當(dāng)前行不保護(hù)
row.createCell((short) 18).setCellStyle(nolockedStyle);
}
//調(diào)整列寬 9: 從這第九列開(kāi)始 ,到最后一列結(jié)束。如果想設(shè)置行高，請(qǐng)調(diào)用 setColumnHidden() 方法
for (int i = 9; i < 19; i++) {
if (i < 16) {
//第一個(gè)參數(shù)為第幾列,第二個(gè)值為寬度值
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("序號(hào)");
titleList.add("名字");
titleList.add("號(hào)碼");
titleList.add("xxx");
//你的實(shí)體有多少個(gè)屬性,就在這里寫(xiě)入屬性名
....
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);
// 自動(dòng)換行
normalStyle.setWrapText(true);
return normalStyle;
}
}
將到這里，關(guān)于java解析Excel文件,包括導(dǎo)出數(shù)據(jù)到Excel、從Excel文件中導(dǎo)入數(shù)據(jù)、設(shè)置表格保護(hù)等等算是告一段落了，后面有啥再補(bǔ)充。
總結(jié)
以上為個(gè)人經(jīng)驗(yàn)，希望能給大家一個(gè)參考，也希望大家多多支持腳本之家。
相關(guān)文章
Java中存在一個(gè)日歷表示法的類庫(kù),LocalDate類,如果只想要指定的年月日就可以用這個(gè)LocalDate類,下面這篇文章主要給大家介紹了關(guān)于JAVA中日期LocalDate類詳細(xì)用法講解的相關(guān)資料,需要的朋友可以參考下2024-01-01
Spring中的ApplicationContext與BeanFactory詳解
這篇文章主要介紹了Spring中的ApplicationContext與BeanFactory詳解,Spring的IoC容器就是一個(gè)實(shí)現(xiàn)了BeanFactory接口的可實(shí)例化類,事實(shí)上, Spring提供了兩種不同的容器,一種是最基本的BeanFactory,另一種是擴(kuò)展的ApplicationContext,需要的朋友可以參考下2024-01-01
解決grails服務(wù)端口沖突的辦法(grails修改端口號(hào))
grails中默認(rèn)的服務(wù)端口為8080，當(dāng)本機(jī)中需要同時(shí)啟動(dòng)兩個(gè)不同的項(xiàng)目時(shí)，就會(huì)造成端口沖突,下面給出解決方法2013-12-12
java中使用Files.readLines()處理文本中行數(shù)據(jù)方式
這篇文章主要介紹了java中使用Files.readLines()處理文本中行數(shù)據(jù)方式，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。如有錯(cuò)誤或未考慮完全的地方，望不吝賜教2021-12-12
java對(duì)接微信小程序詳細(xì)流程(登錄&獲取用戶信息)
這篇文章主要給大家介紹了關(guān)于java對(duì)接微信小程序(登錄&獲取用戶信息)的相關(guān)資料,我們?cè)陂_(kāi)發(fā)微信小程序時(shí)經(jīng)常需要獲取用戶微信用戶名以及頭像信息,微信提供了專門的接口API用于返回這些信息,需要的朋友可以參考下2023-08-08
微服務(wù)中使用Maven BOM來(lái)管理你的版本依賴詳解
這篇文章主要介紹了微服務(wù)中使用Maven BOM來(lái)管理你的版本依賴,文中通過(guò)示例代碼介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，需要的朋友們下面隨著小編來(lái)一起學(xué)習(xí)學(xué)習(xí)吧2019-12-12
Springboot內(nèi)部提供的事務(wù)管理器是根據(jù)autoconfigure來(lái)進(jìn)行決定的。接下來(lái)通過(guò)本文給大家介紹SpringBoot的事務(wù)管理相關(guān)知識(shí)，感興趣的朋友一起學(xué)習(xí)吧2017-04-04
springboot如何使用yml文件方式配置shardingsphere
這篇文章主要介紹了springboot如何使用yml文件方式配置shardingsphere問(wèn)題,具有很好的參考價(jià)值,希望對(duì)大家有所幫助,如有錯(cuò)誤或未考慮完全的地方,望不吝賜教2023-09-09
Spring應(yīng)用中使用acutator/refresh刷新屬性不生效的問(wèn)題分析及解決
在Spring應(yīng)用收到/actuator/refresh的POST請(qǐng)求后,標(biāo)注了@RefreshScope以及@ConfiguratioinProperties的bean會(huì)被Spring容器重新加載,但是,在實(shí)際應(yīng)用中,并沒(méi)有按照預(yù)期被Spring容器加載,本文將討論導(dǎo)致這種未按預(yù)期刷新的一種原因,感興趣的朋友可以參考下2024-01-01
Java8新特性之JavaFX 8_動(dòng)力節(jié)點(diǎn)Java學(xué)院整理
這篇文章主要介紹了Java8新特性之JavaFX 8的相關(guān)知識(shí)，非常不錯(cuò)，具有參考借鑒價(jià)值，需要的朋友參考下吧2017-06-06