Java之使用POI教你玩轉(zhuǎn)Excel導(dǎo)入與導(dǎo)出
更新時間：2022年10月26日 10:06:18 作者：小馬 同學(xué)
這篇文章主要介紹了Java之使用POI教你玩轉(zhuǎn)Excel導(dǎo)入與導(dǎo)出，具有很好的參考價值，希望對大家有所幫助。如有錯誤或未考慮完全的地方，望不吝賜教
在平時開發(fā)項目時對數(shù)據(jù)的處理肯定是少不了的。對于數(shù)據(jù)處理也是很多程序員比較頭疼的問題，就比如項目中的數(shù)據(jù)是如何添加進(jìn)去呢？一條一條的錄入？好像又有點(diǎn)不太友好，數(shù)據(jù)多了效率太低了，最關(guān)鍵的是甲方爸爸肯定不會滿意的。
這時我們可以使用POI來操作Excel表格，可以通過POI來把Excel中的數(shù)據(jù)批量導(dǎo)入到數(shù)據(jù)庫中，從而簡化操作，提高效率。反之我們還可以通過POI把數(shù)據(jù)庫中的數(shù)據(jù)導(dǎo)出到Excel表格中。
POI 簡介
Apache POI是Apache軟件基金會的開放源碼函式庫，POI提供API給Java程序?qū)icrosoft Office格式檔案讀和寫的功能。
POI依賴文件
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.17</version>
</dependency>POI常用類
| 類名 | 說明 |
|---|---|
| HSSFWorkbook | Excel的文件對象 |
| HSSFSheet | Excel的表單 |
| HSSFRow | Excel的行 |
| HSSFCell | Excel的格子單元 |
| HSSFFont | Excel字體 |
| HSSFDataFormat | 格子單元的日期格式 |
| HSSFHeader | Excel文檔Sheet的頁眉 |
| HSSFFooter | Excel文檔Sheet的頁腳 |
| 樣式： | |
| HSSFCellStyle | cell樣式 |
| 輔助操作包括： | |
| HSSFDateUtil | 日期 |
| HSSFPrintSetup | 打印 |
| HSSFErrorConstants | 錯誤信息表 |
數(shù)據(jù)導(dǎo)出
首先，理解一下一個Excel的文件的組織形式，一個Excel文件對應(yīng)于一個workbook(HSSFWorkbook)，一個workbook可以有多個sheet（HSSFSheet）表單組成，一個sheet是由多個row（HSSFRow）行組成，一個row是由多個cell（HSSFCell）單元格組成。
所以對應(yīng)的操作步驟如下：
- 用HSSFWorkbook打開或者創(chuàng)建Excel文件對象。
- 用HSSFWorkbook對象返回或者創(chuàng)建Sheet對象。
- 用Sheet對象返回行對象，用行對象得到Cell對象。
- 對Cell對象讀寫。
下面來看一下數(shù)據(jù)導(dǎo)出功能
前臺頁面只需要給出一個跳轉(zhuǎn)到后臺導(dǎo)出方法的鏈接即可。
<button class="layui-btn layui-btn-sm" onclick="window.location.href='staff/export'">導(dǎo)出</button>
后臺對應(yīng)導(dǎo)出的方法，代碼如下：
@RequestMapping(value = "/export")
@ResponseBody
public void export(HttpServletRequest request, HttpServletResponse response) throws Exception {
//查詢數(shù)據(jù)庫中的數(shù)據(jù)
List<Staff> staffList = staffService.findAll();
//創(chuàng)建excel的文檔對象
HSSFWorkbook workbook = new HSSFWorkbook();
//創(chuàng)建員工信息工作薄，表單
HSSFSheet sheet = workbook.createSheet("員工信息");
//在sheet里創(chuàng)建第一行，參數(shù)為行索引(excel的行)，從0開始
HSSFRow row1 = sheet.createRow(0);
//創(chuàng)建單元格excel的單元格，參數(shù)為列索引，從0開始
HSSFCell cell = row1.createCell(0);
//設(shè)置單元格內(nèi)容，標(biāo)題第一行（可以不設(shè)置，根據(jù)項目需求）
cell.setCellValue("員工信息");
//合并單元格CellRangeAddress構(gòu)造參數(shù)依次表示起始行，截至行，起始列， 截至列，一行標(biāo)題合并單元格
sheet.addMergedRegion(new CellRangeAddress(0,0,0,3));
//設(shè)置列名（每列的小標(biāo)題）
String[] fieldnames = {"姓名", "部門", "年齡", "工號"};
//在sheet里創(chuàng)建第二行，第一行設(shè)置了標(biāo)題
HSSFRow row2 = sheet.createRow(1);
for(int i = 0; i < fieldnames.length; i++) {
row2.createCell(i).setCellValue(fieldnames[i]); //設(shè)置列名
}
//填充導(dǎo)出的數(shù)據(jù)到Excel中
for(int i = 0; i < staffList.size(); i++){
HSSFRow rows = sheet.createRow(i + 2);
HSSFCell cellName = rows.createCell(0);
cellName.setCellValue(staffList.get(i).getName());
HSSFCell cell1Dept = rows.createCell(1);
cell1Dept.setCellValue(staffList.get(i).getDept());
HSSFCell cell1Age = rows.createCell(2);
cell1Age.setCellValue(staffList.get(i).getAge());
HSSFCell cell1Number = rows.createCell(3);
cell1Number.setCellValue(staffList.get(i).getNumber());
}
//輸出Excel文件
OutputStream output=response.getOutputStream();
response.reset();
//filename可以設(shè)置Excel文件的名稱
response.setHeader("Content-disposition", "attachment; filename=staff.xls");
response.setContentType("application/msexcel");
workbook.write(output);
output.close();
}
注釋怎么清楚，就不用多解釋了吧，(#^ . ^#)。
導(dǎo)出結(jié)果如下：
設(shè)置樣式
上面導(dǎo)出的內(nèi)容大家也看到了，就是最原始的樣式。其實在導(dǎo)出時我們也可以對其Excel設(shè)置相應(yīng)的樣式。
1、合并單元格
使用HSSFSheet類中的addMergedRegion(CellRangeAddress region)方法，上面導(dǎo)出也用到過。
參數(shù)CellRangeAddress表示合并的區(qū)域，方法如下：
public CellRangeAddress(int firstRow, int lastRow, int firstCol, int lastCol)
其中參數(shù)依次表示起始行，截至行，起始列， 截至列。有興趣的小伙伴可以點(diǎn)進(jìn)封裝類中看一下。
2、設(shè)置單元格的大小
HSSFSheet sheet = workbook.createSheet("員工信息");// 創(chuàng)建工作表(Sheet)
HSSFRow row = sheet.createRow(1);
HSSFCell cell = row.createCell(1);
cell.setCellValue("張三");
sheet.setColumnWidth(1, 256 * 50); //設(shè)置第一列的寬度，為50個字符寬度
row.setHeightInPoints(50); //設(shè)置一行的高度setColumnWidth方法和setHeightInPoints方法適合這是部分的樣式，如果需要設(shè)置全部樣式，可以使用HSSFSheet.setDefaultColumnWidth和HSSFSheet.setDefaultRowHeightInPoints方法設(shè)置默認(rèn)的列寬和行高。
3、設(shè)置單元格樣式
單元格樣式是通過HSSFCellStyle類來設(shè)置的，所以我們需要先得到HSSFCellStyle 類
HSSFCellStyle style = workbook.createCellStyle()
3.1、設(shè)置水平對齊方式
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);//水平居中
水平對齊相關(guān)參數(shù)
- 如果是左側(cè)對齊就是 HSSFCellStyle.ALIGN_FILL。
- 如果是居中對齊就是 HSSFCellStyle.ALIGN_CENTER。
- 如果是右側(cè)對齊就是 HSSFCellStyle.ALIGN_RIGHT。
- 如果是跨列舉中就是 HSSFCellStyle.ALIGN_CENTER_SELECTION。
- 如果是兩端對齊就是 HSSFCellStyle.ALIGN_JUSTIFY。
3.2、設(shè)置垂直對齊方式
style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//垂直居中
垂直對齊相關(guān)參數(shù)
- 如果是靠上就是 HSSFCellStyle.VERTICAL_TOP。
- 如果是居中就是 HSSFCellStyle.VERTICAL_CENTER。
- 如果是靠下就是 HSSFCellStyle.VERTICAL_BOTTOM。
- 如果是兩端對齊就是 HSSFCellStyle.VERTICAL_JUSTIFY。
3.3、設(shè)置邊框
style.setBorderTop(HSSFCellStyle.BORDER_DOTTED);//上邊框 style.setBorderBottom(HSSFCellStyle.BORDER_THICK);//下邊框 style.setBorderLeft(HSSFCellStyle.BORDER_DOUBLE);//左邊框 style.setBorderRight(HSSFCellStyle.BORDER_SLANTED_DASH_DOT);//右邊框 style.setTopBorderColor(HSSFColor.RED.index);//上邊框顏色 style.setBottomBorderColor(HSSFColor.BLUE.index);//下邊框顏色 style.setLeftBorderColor(HSSFColor.GREEN.index);//左邊框顏色 style.setRightBorderColor(HSSFColor.PINK.index);//右邊框顏色
3.4、應(yīng)用樣式
//用在某一個單元格中 cell.setCellStyle(cellStyle); //用在一行中，設(shè)置一行的樣式 row.setRowStyle(cellStyle);
4、設(shè)置字體樣式
字體樣式是通過HSSFFont類來設(shè)置的，所以我們需要先得到HSSFFont類
HSSFFont font = workbook.createFont()
設(shè)置樣式
font.setFontName("華文行楷");//設(shè)置字體名稱
font.setFontHeightInPoints((short)28);//設(shè)置字號
font.setColor(HSSFColor.RED.index);//設(shè)置字體顏色
font.setUnderline(FontFormatting.U_SINGLE);//設(shè)置下劃線
font.setTypeOffset(FontFormatting.SS_SUPER);//設(shè)置上標(biāo)下標(biāo)
font.setStrikeout(true);//設(shè)置刪除線下劃線選項值：
- 單下劃線 FontFormatting.U_SINGLE
- 雙下劃線 FontFormatting.U_DOUBLE
- 會計用單下劃線 FontFormatting.U_SINGLE_ACCOUNTING
- 會計用雙下劃線 FontFormatting.U_DOUBLE_ACCOUNTING
- 無下劃線 FontFormatting.U_NONE
上標(biāo)下標(biāo)選項值：
- 上標(biāo) FontFormatting.SS_SUPER
- 下標(biāo) FontFormatting.SS_SUB
- 普通，默認(rèn)值 FontFormatting.SS_NONE
應(yīng)用樣式
//字體也是單元格格式的一部分，所以從屬于HSSFCellStyle // 將字體對象賦值給單元格樣式對象 style.setFont(font); // 將單元格樣式應(yīng)用于單元格 cell.setCellStyle(cellStyle);
數(shù)據(jù)導(dǎo)入
導(dǎo)入數(shù)據(jù)時，頁面需要給出一個選擇文件的標(biāo)簽，頁面就不多說了，只要有一個上傳的標(biāo)簽即可。
這是用layui寫的一個簡單的頁面，代碼如下：
<style type="text/css">
#updateFile{
margin: 20px 0px 20px 50px;
}
</style>
<form id="importForm" method="post" class="layui-form" >
<div class="layui-form-item" style="margin: 0px;">
<button type="button" name="file" class="layui-btn layui-btn-normal" id="updateFile">選擇文件</button>
</div>
<div class="layui-form-item">
<div class="layui-input-block" style="margin-left: 50px;">
<input class="layui-btn layui-btn-normal" id="upload" value=" 導(dǎo) 入 " />
<a href="staff/exportmoban" rel="external nofollow" >下載模板</a>
</div>
</div>
</form>
<script type="text/javascript">
layui.use(['form', 'upload'], function(){
var upload = layui.upload;
upload.render({
elem: '#updateFile',
url: 'staff/excelimport',
auto: false,
accept: 'file',
exts: 'xls|excel|xlsx',
bindAction: '#upload',
done: function(res){ //導(dǎo)出成功后回調(diào)
}
});
});
</script>其中下載模板和導(dǎo)出數(shù)據(jù)基本一樣，只需要創(chuàng)建一個Excel給出一條樣式數(shù)據(jù)即可，代碼如下：
@RequestMapping(value = "exportmoban")
@ResponseBody
public void exportmoban(HttpServletRequest request, HttpServletResponse response) throws Exception{
//創(chuàng)建excel的文檔對象
HSSFWorkbook workbook = new HSSFWorkbook();
//創(chuàng)建員工信息工作薄，表單
HSSFSheet sheet = workbook.createSheet("員工信息");
//在sheet里創(chuàng)建第一行，參數(shù)為行索引(excel的行)，從0開始
HSSFRow row1 = sheet.createRow(0);
//創(chuàng)建單元格excel的單元格，參數(shù)為列索引，從0開始
HSSFCell cell = row1.createCell(0);
//設(shè)置單元格內(nèi)容，標(biāo)題第一行（可以不設(shè)置，根據(jù)項目需求）
cell.setCellValue("員工信息");
//合并單元格CellRangeAddress構(gòu)造參數(shù)依次表示起始行，截至行，起始列， 截至列，一行標(biāo)題合并單元格
sheet.addMergedRegion(new CellRangeAddress(0,0,0,3));
//設(shè)置列名（每列的小標(biāo)題）
String[] fieldnames = {"姓名", "部門", "年齡", "工號"};
//在sheet里創(chuàng)建第二行，第一行設(shè)置了標(biāo)題
HSSFRow row2 = sheet.createRow(1);
for(int i = 0; i < fieldnames.length; i++) {
row2.createCell(i).setCellValue(fieldnames[i]); //設(shè)置列名
}
//模板數(shù)據(jù)
HSSFRow row3 = sheet.createRow(2);
row3.createCell(0).setCellValue("王五");
row3.createCell(1).setCellValue("軟件部");
row3.createCell(2).setCellValue(“18“);
row3.createCell(3).setCellValue("003");
//輸出Excel文件
OutputStream output=response.getOutputStream();
response.reset();
//filename可以設(shè)置Excel文件的名稱
response.setHeader("Content-disposition", "attachment; filename=staff.xls");
response.setContentType("application/msexcel");
workbook.write(output);
output.close();
}模板下載如圖。
導(dǎo)入數(shù)據(jù)，我們只需要選擇一個有數(shù)據(jù)的Excel表格（數(shù)據(jù)格式需要和模板中格式一樣），點(diǎn)擊導(dǎo)入即可。
后臺對應(yīng)導(dǎo)入數(shù)據(jù)的excelimport方法，代碼如下：
@RequestMapping(value = "/excelimport")
@ResponseBody
public boolean excelimport(MultipartFile file) {
// 實例化工具類
ImportExcel excelReader = new ImportExcel();
try {
InputStream is = file.getInputStream();
// 導(dǎo)入excel
excelReader.readExcelContent(is);
Map<Integer, String> map = excelReader.readExcelContent(is);
//遍歷數(shù)據(jù)保存
//因為第一行和第二行是標(biāo)題，所以從2開始
for (int i = 2; i <= map.size() + 1; i++) {
String[] scoreArray = map.get(i).split("-");
if (scoreArray.length > 0) {
String name = scoreArray[0];
String dept = scoreArray[1];
Integer age = Integer.parseInt(scoreArray[2]);
String number = scoreArray[3];
Staff staff = new Staff();
staff.setName(name);
staff.setDept(dept);
staff.setAge(age);
staff.setNumber(number);
//保存
staffService.save(staff);
}
}
return true;
} catch (Exception e) {
e.printStackTrace();
return false;
}
}注： 如果對數(shù)據(jù)需要做一些效驗的話可以放在for循環(huán)中處理，這里只是寫一個小案例，沒有添加任何效驗，根據(jù)項目需求自己添加。
其中ImportExcel類是一個處理導(dǎo)入的Excel數(shù)據(jù)的封裝類。代碼如下：
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.HashMap;
import java.util.Map;
public class ImportExcel {
private POIFSFileSystem fs;
private HSSFWorkbook wb;
private HSSFSheet sheet;
private HSSFRow row;
/**
* 讀取Excel表格表頭的內(nèi)容
* @param is
* @return String 表頭內(nèi)容的數(shù)組
*/
public String[] readExcelTitle(InputStream is) {
try {
fs = new POIFSFileSystem(is);
wb = new HSSFWorkbook(fs);
} catch (IOException e) {
e.printStackTrace();
}
sheet = wb.getSheetAt(0);
//得到首行的row
row = sheet.getRow(0);
// 標(biāo)題總列數(shù)
int colNum = row.getPhysicalNumberOfCells();
String[] title = new String[colNum];
for (int i = 0; i < colNum; i++) {
title[i] = getCellFormatValue(row.getCell((short) i));
}
return title;
}
/**
* 讀取Excel數(shù)據(jù)內(nèi)容
* @param is
* @return Map 包含單元格數(shù)據(jù)內(nèi)容的Map對象
*/
public Map<Integer, String> readExcelContent(InputStream is) {
Map<Integer, String> content = new HashMap<Integer, String>();
String str = "";
try {
fs = new POIFSFileSystem(is);
wb = new HSSFWorkbook(fs);
} catch (IOException e) {
//e.printStackTrace();
}
sheet = wb.getSheetAt(0);
// 得到總行數(shù)
int rowNum = sheet.getLastRowNum();
//由于第0行和第一行已經(jīng)合并了 在這里索引從2開始
row = sheet.getRow(2);
int colNum = row.getPhysicalNumberOfCells();
// 正文內(nèi)容應(yīng)該從第二行開始,第一行為表頭的標(biāo)題
for (int i = 2; i <= rowNum; i++) {
row = sheet.getRow(i);
int j = 0;
while (j < colNum) {
str += getCellFormatValue(row.getCell((short) j)).trim() + "-";
j++;
}
content.put(i, str);
str = "";
}
return content;
}
/**
* 獲取單元格數(shù)據(jù)內(nèi)容為字符串類型的數(shù)據(jù)
*
* @param cell Excel單元格
* @return String 單元格數(shù)據(jù)內(nèi)容
*/
private String getStringCellValue(HSSFCell cell) {
String strCell = "";
switch (cell.getCellType()) {
case HSSFCell.CELL_TYPE_STRING:
strCell = cell.getStringCellValue();
break;
case HSSFCell.CELL_TYPE_NUMERIC:
strCell = String.valueOf(cell.getNumericCellValue());
break;
case HSSFCell.CELL_TYPE_BOOLEAN:
strCell = String.valueOf(cell.getBooleanCellValue());
break;
case HSSFCell.CELL_TYPE_BLANK:
strCell = "";
break;
default:
strCell = "";
break;
}
if (strCell.equals("") || strCell == null) {
return "";
}
if (cell == null) {
return "";
}
return strCell;
}
/**
* 獲取單元格數(shù)據(jù)內(nèi)容為日期類型的數(shù)據(jù)
*
* @param cell
* Excel單元格
* @return String 單元格數(shù)據(jù)內(nèi)容
*/
private String getDateCellValue(HSSFCell cell) {
String result = "";
try {
int cellType = cell.getCellType();
if (cellType == HSSFCell.CELL_TYPE_NUMERIC) {
Date date = cell.getDateCellValue();
result = (date.getYear() + 1900) + "-" + (date.getMonth() + 1)
+ "-" + date.getDate();
} else if (cellType == HSSFCell.CELL_TYPE_STRING) {
String date = getStringCellValue(cell);
result = date.replaceAll("[年月]", "-").replace("日", "").trim();
} else if (cellType == HSSFCell.CELL_TYPE_BLANK) {
result = "";
}
} catch (Exception e) {
System.out.println("日期格式不正確!");
e.printStackTrace();
}
return result;
}
/**
* 根據(jù)HSSFCell類型設(shè)置數(shù)據(jù)
* @param cell
* @return
*/
private String getCellFormatValue(HSSFCell cell) {
String cellvalue = "";
if (cell != null) {
// 判斷當(dāng)前Cell的Type
switch (cell.getCellType()) {
// 如果當(dāng)前Cell的Type為NUMERIC
case HSSFCell.CELL_TYPE_NUMERIC:
case HSSFCell.CELL_TYPE_FORMULA: {
// 判斷當(dāng)前的cell是否為Date
if (HSSFDateUtil.isCellDateFormatted(cell)) {
Date date = cell.getDateCellValue();
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
cellvalue = sdf.format(date);
}
// 如果是純數(shù)字
else {
// 取得當(dāng)前Cell的數(shù)值
cellvalue = String.valueOf(cell.getNumericCellValue());
}
break;
}
// 如果當(dāng)前Cell的Type為STRIN
case HSSFCell.CELL_TYPE_STRING:
// 取得當(dāng)前的Cell字符串
cellvalue = cell.getRichStringCellValue().getString();
break;
// 默認(rèn)的Cell值
default:
cellvalue = " ";
}
} else {
cellvalue = "";
}
return cellvalue;
}
}
導(dǎo)入的數(shù)據(jù)：
以上為個人經(jīng)驗，希望能給大家一個參考，也希望大家多多支持腳本之家。
相關(guān)文章
一篇文章帶你了解java Object根類中關(guān)于toString,equals的方法
這篇文章主要介紹了Object類toString()和equals()方法使用解析,文中通過示例代碼介紹的非常詳細(xì)，對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價值,需要的朋友可以參考下2021-09-09
今天小編就為大家分享一篇關(guān)于Java中的Map允許有重復(fù)元素嗎?，小編覺得內(nèi)容挺不錯的，現(xiàn)在分享給大家，具有很好的參考價值，需要的朋友一起跟隨小編來看看吧2019-02-02
Apache DolphinScheduler完全設(shè)置東八區(qū)時區(qū)
這篇文章主要為大家介紹了Apache DolphinScheduler完全設(shè)置東八區(qū)配置詳解,有需要的朋友可以借鑒參考下,希望能夠有所幫助,祝大家多多進(jìn)步,早日升職加薪2023-11-11
Java語言基于無向有權(quán)圖實現(xiàn)克魯斯卡爾算法代碼示例
這篇文章主要介紹了Java語言基于無向有權(quán)圖實現(xiàn)克魯斯卡爾算法代碼示例，具有一定參考價值，需要的朋友可以了解下。2017-11-11
java中double強(qiáng)制轉(zhuǎn)換int引發(fā)的OOM問題記錄
這篇文章主要介紹了java中double強(qiáng)制轉(zhuǎn)換int引發(fā)的OOM問題記錄,本文給大家分享問題排查過程,感興趣的朋友跟隨小編一起看看吧2024-10-10
Spring?Security中如何獲取AuthenticationManager對象
有時需要使用AuthenticationManager（以下簡稱Manager）對象，可是這個對象不是Bean，沒有直接保存在Spring的Bean庫中，那么如何獲取Spring Security中的這個對象呢,需要的朋友可以參考下2022-11-11
SpringBoot 異步線程間數(shù)據(jù)傳遞的實現(xiàn)
本文主要介紹了SpringBoot 異步線程間數(shù)據(jù)傳遞的實現(xiàn),包括異步線程的基本概念、數(shù)據(jù)傳遞的方式、具體實現(xiàn)方式等,具有一定的參考價值,感興趣的可以了解一下2024-03-03
這篇文章主要介紹了并發(fā)編程模式之ThreadLocal源碼和圖文解讀,具有很好的參考價值,希望對大家有所幫助,如有錯誤或未考慮完全的地方,望不吝賜教2024-08-08
Java wait和notifyAll實現(xiàn)簡單的阻塞隊列
這篇文章主要介紹了Java wait和notifyAll實現(xiàn)簡單的阻塞隊列,文中通過示例代碼介紹的非常詳細(xì)，對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價值,需要的朋友可以參考下2019-10-10
Springmvc自定義類型轉(zhuǎn)換器實現(xiàn)步驟
這篇文章主要介紹了Springmvc自定義類型轉(zhuǎn)換器實現(xiàn)步驟,文中通過示例代碼介紹的非常詳細(xì)，對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價值,需要的朋友可以參考下2020-08-08