Java之使用POI教你玩轉(zhuǎn)Excel導(dǎo)入與導(dǎo)出

更新時(shí)間：2022年10月26日 10:06:18 作者：小馬同學(xué)

這篇文章主要介紹了Java之使用POI教你玩轉(zhuǎn)Excel導(dǎo)入與導(dǎo)出，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。如有錯(cuò)誤或未考慮完全的地方，望不吝賜教

在平時(shí)開(kāi)發(fā)項(xiàng)目時(shí)對(duì)數(shù)據(jù)的處理肯定是少不了的。對(duì)于數(shù)據(jù)處理也是很多程序員比較頭疼的問(wèn)題，就比如項(xiàng)目中的數(shù)據(jù)是如何添加進(jìn)去呢？一條一條的錄入？好像又有點(diǎn)不太友好，數(shù)據(jù)多了效率太低了，最關(guān)鍵的是甲方爸爸肯定不會(huì)滿意的。

這時(shí)我們可以使用POI來(lái)操作Excel表格，可以通過(guò)POI來(lái)把Excel中的數(shù)據(jù)批量導(dǎo)入到數(shù)據(jù)庫(kù)中，從而簡(jiǎn)化操作，提高效率。反之我們還可以通過(guò)POI把數(shù)據(jù)庫(kù)中的數(shù)據(jù)導(dǎo)出到Excel表格中。

POI 簡(jiǎn)介

Apache POI是Apache軟件基金會(huì)的開(kāi)放源碼函式庫(kù)，POI提供API給Java程序?qū)icrosoft Office格式檔案讀和寫(xiě)的功能。

POI依賴(lài)文件

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

POI常用類(lèi)

類(lèi)名	說(shuō)明
HSSFWorkbook	Excel的文件對(duì)象
HSSFSheet	Excel的表單
HSSFRow	Excel的行
HSSFCell	Excel的格子單元
HSSFFont	Excel字體
HSSFDataFormat	格子單元的日期格式
HSSFHeader	Excel文檔Sheet的頁(yè)眉
HSSFFooter	Excel文檔Sheet的頁(yè)腳
樣式：
HSSFCellStyle	cell樣式
輔助操作包括：
HSSFDateUtil	日期
HSSFPrintSetup	打印
HSSFErrorConstants	錯(cuò)誤信息表

數(shù)據(jù)導(dǎo)出

首先，理解一下一個(gè)Excel的文件的組織形式，一個(gè)Excel文件對(duì)應(yīng)于一個(gè)workbook(HSSFWorkbook)，一個(gè)workbook可以有多個(gè)sheet（HSSFSheet）表單組成，一個(gè)sheet是由多個(gè)row（HSSFRow）行組成，一個(gè)row是由多個(gè)cell（HSSFCell）單元格組成。

所以對(duì)應(yīng)的操作步驟如下：

用HSSFWorkbook打開(kāi)或者創(chuàng)建Excel文件對(duì)象。
用HSSFWorkbook對(duì)象返回或者創(chuàng)建Sheet對(duì)象。
用Sheet對(duì)象返回行對(duì)象，用行對(duì)象得到Cell對(duì)象。
對(duì)Cell對(duì)象讀寫(xiě)。

下面來(lái)看一下數(shù)據(jù)導(dǎo)出功能

前臺(tái)頁(yè)面只需要給出一個(gè)跳轉(zhuǎn)到后臺(tái)導(dǎo)出方法的鏈接即可。

<button class="layui-btn layui-btn-sm" onclick="window.location.href='staff/export'">導(dǎo)出</button>

后臺(tái)對(duì)應(yīng)導(dǎo)出的方法，代碼如下：

@RequestMapping(value = "/export")
@ResponseBody
public void export(HttpServletRequest request, HttpServletResponse response) throws Exception {
    //查詢數(shù)據(jù)庫(kù)中的數(shù)據(jù)
    List<Staff> staffList = staffService.findAll();
    //創(chuàng)建excel的文檔對(duì)象
    HSSFWorkbook workbook = new HSSFWorkbook();
    //創(chuàng)建員工信息工作薄，表單
    HSSFSheet sheet = workbook.createSheet("員工信息");
    //在sheet里創(chuàng)建第一行，參數(shù)為行索引(excel的行)，從0開(kāi)始
    HSSFRow row1 = sheet.createRow(0);
    //創(chuàng)建單元格excel的單元格，參數(shù)為列索引，從0開(kāi)始
    HSSFCell cell = row1.createCell(0);
    //設(shè)置單元格內(nèi)容，標(biāo)題第一行（可以不設(shè)置，根據(jù)項(xiàng)目需求）
    cell.setCellValue("員工信息");
    //合并單元格CellRangeAddress構(gòu)造參數(shù)依次表示起始行，截至行，起始列， 截至列，一行標(biāo)題合并單元格
    sheet.addMergedRegion(new CellRangeAddress(0,0,0,3));
    //設(shè)置列名（每列的小標(biāo)題）
    String[] fieldnames = {"姓名", "部門(mén)", "年齡", "工號(hào)"};
    //在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文件的名稱(chēng)
    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)容大家也看到了，就是最原始的樣式。其實(shí)在導(dǎo)出時(shí)我們也可以對(duì)其Excel設(shè)置相應(yīng)的樣式。

1、合并單元格

使用HSSFSheet類(lèi)中的addMergedRegion(CellRangeAddress region)方法，上面導(dǎo)出也用到過(guò)。

參數(shù)CellRangeAddress表示合并的區(qū)域，方法如下：

public CellRangeAddress(int firstRow, int lastRow, int firstCol, int lastCol)

其中參數(shù)依次表示起始行，截至行，起始列，截至列。有興趣的小伙伴可以點(diǎn)進(jìn)封裝類(lèi)中看一下。

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個(gè)字符寬度
row.setHeightInPoints(50);	//設(shè)置一行的高度

setColumnWidth方法和setHeightInPoints方法適合這是部分的樣式，如果需要設(shè)置全部樣式，可以使用HSSFSheet.setDefaultColumnWidth和HSSFSheet.setDefaultRowHeightInPoints方法設(shè)置默認(rèn)的列寬和行高。

3、設(shè)置單元格樣式

單元格樣式是通過(guò)HSSFCellStyle類(lèi)來(lái)設(shè)置的，所以我們需要先得到HSSFCellStyle 類(lèi)

 HSSFCellStyle style = workbook.createCellStyle()

3.1、設(shè)置水平對(duì)齊方式

style.setAlignment(HSSFCellStyle.ALIGN_CENTER);//水平居中

水平對(duì)齊相關(guān)參數(shù)

如果是左側(cè)對(duì)齊就是 HSSFCellStyle.ALIGN_FILL。
如果是居中對(duì)齊就是 HSSFCellStyle.ALIGN_CENTER。
如果是右側(cè)對(duì)齊就是 HSSFCellStyle.ALIGN_RIGHT。
如果是跨列舉中就是 HSSFCellStyle.ALIGN_CENTER_SELECTION。
如果是兩端對(duì)齊就是 HSSFCellStyle.ALIGN_JUSTIFY。

3.2、設(shè)置垂直對(duì)齊方式

style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//垂直居中

垂直對(duì)齊相關(guān)參數(shù)

如果是靠上就是 HSSFCellStyle.VERTICAL_TOP。
如果是居中就是 HSSFCellStyle.VERTICAL_CENTER。
如果是靠下就是 HSSFCellStyle.VERTICAL_BOTTOM。
如果是兩端對(duì)齊就是 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)用樣式

//用在某一個(gè)單元格中
cell.setCellStyle(cellStyle);
//用在一行中，設(shè)置一行的樣式
row.setRowStyle(cellStyle);

4、設(shè)置字體樣式

字體樣式是通過(guò)HSSFFont類(lèi)來(lái)設(shè)置的，所以我們需要先得到HSSFFont類(lèi)

HSSFFont font = workbook.createFont()

設(shè)置樣式

font.setFontName("華文行楷");//設(shè)置字體名稱(chēng)
font.setFontHeightInPoints((short)28);//設(shè)置字號(hào)
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è)置刪除線

下劃線選項(xiàng)值：

單下劃線 FontFormatting.U_SINGLE
雙下劃線 FontFormatting.U_DOUBLE
會(huì)計(jì)用單下劃線 FontFormatting.U_SINGLE_ACCOUNTING
會(huì)計(jì)用雙下劃線 FontFormatting.U_DOUBLE_ACCOUNTING
無(wú)下劃線 FontFormatting.U_NONE

上標(biāo)下標(biāo)選項(xiàng)值：

上標(biāo) FontFormatting.SS_SUPER
下標(biāo) FontFormatting.SS_SUB
普通，默認(rèn)值 FontFormatting.SS_NONE

應(yīng)用樣式

//字體也是單元格格式的一部分，所以從屬于HSSFCellStyle
// 將字體對(duì)象賦值給單元格樣式對(duì)象
style.setFont(font);
// 將單元格樣式應(yīng)用于單元格
cell.setCellStyle(cellStyle);

數(shù)據(jù)導(dǎo)入

導(dǎo)入數(shù)據(jù)時(shí)，頁(yè)面需要給出一個(gè)選擇文件的標(biāo)簽，頁(yè)面就不多說(shuō)了，只要有一個(gè)上傳的標(biāo)簽即可。

這是用layui寫(xiě)的一個(gè)簡(jiǎn)單的頁(yè)面，代碼如下：

<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ù)基本一樣，只需要?jiǎng)?chuàng)建一個(gè)Excel給出一條樣式數(shù)據(jù)即可，代碼如下：

@RequestMapping(value = "exportmoban")
@ResponseBody
public void exportmoban(HttpServletRequest request, HttpServletResponse response) throws Exception{
    //創(chuàng)建excel的文檔對(duì)象
    HSSFWorkbook workbook = new HSSFWorkbook();
    //創(chuàng)建員工信息工作薄，表單
    HSSFSheet sheet = workbook.createSheet("員工信息");
    //在sheet里創(chuàng)建第一行，參數(shù)為行索引(excel的行)，從0開(kāi)始
    HSSFRow row1 = sheet.createRow(0);
    //創(chuàng)建單元格excel的單元格，參數(shù)為列索引，從0開(kāi)始
    HSSFCell cell = row1.createCell(0);
    //設(shè)置單元格內(nèi)容，標(biāo)題第一行（可以不設(shè)置，根據(jù)項(xiàng)目需求）
    cell.setCellValue("員工信息");
    //合并單元格CellRangeAddress構(gòu)造參數(shù)依次表示起始行，截至行，起始列， 截至列，一行標(biāo)題合并單元格
    sheet.addMergedRegion(new CellRangeAddress(0,0,0,3));
    //設(shè)置列名（每列的小標(biāo)題）
    String[] fieldnames = {"姓名", "部門(mén)", "年齡", "工號(hào)"};
    //在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文件的名稱(chēng)
    response.setHeader("Content-disposition", "attachment; filename=staff.xls");
    response.setContentType("application/msexcel");
    workbook.write(output);
    output.close();
}

模板下載如圖。

導(dǎo)入數(shù)據(jù)，我們只需要選擇一個(gè)有數(shù)據(jù)的Excel表格（數(shù)據(jù)格式需要和模板中格式一樣），點(diǎn)擊導(dǎo)入即可。

后臺(tái)對(duì)應(yīng)導(dǎo)入數(shù)據(jù)的excelimport方法，代碼如下：

@RequestMapping(value = "/excelimport")
@ResponseBody
public boolean excelimport(MultipartFile file) {
    // 實(shí)例化工具類(lèi)
    ImportExcel excelReader = new ImportExcel();
    try {
        InputStream is = file.getInputStream();
        // 導(dǎo)入excel
        excelReader.readExcelContent(is);
        Map<Integer, String> map = excelReader.readExcelContent(is);
        //遍歷數(shù)據(jù)保存
        //因?yàn)榈谝恍泻偷诙惺菢?biāo)題，所以從2開(kāi)始
        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;
    }
}

注：如果對(duì)數(shù)據(jù)需要做一些效驗(yàn)的話可以放在for循環(huán)中處理，這里只是寫(xiě)一個(gè)小案例，沒(méi)有添加任何效驗(yàn)，根據(jù)項(xiàng)目需求自己添加。

其中ImportExcel類(lèi)是一個(gè)處理導(dǎo)入的Excel數(shù)據(jù)的封裝類(lèi)。代碼如下：

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對(duì)象
	 */
	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開(kāi)始
		row = sheet.getRow(2);
		int colNum = row.getPhysicalNumberOfCells();
		// 正文內(nèi)容應(yīng)該從第二行開(kāi)始,第一行為表頭的標(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)容為字符串類(lè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)容為日期類(lè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類(lèi)型設(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ù)：