- ??作 ? ??????? 者：是江迪呀
- ??本文關(guān)鍵詞：
Java、Excel、導出、工具類、后端- ??每日?? 一言：有些事情不是對的才去堅持，而是堅持了它才是對的！
前言
我們在日常開發(fā)中，一定遇到過要將數(shù)據(jù)導出為Excel的需求，那么怎么做呢？在做之前，我們需要思考下Excel的組成。Excel是由四個元素組成的分別是：WorkBook(工作簿)、Sheet(工作表)、Row(行)、Cell(單元格)，其中包含關(guān)系是從左至右,，一個WorkBook可以包含多個Sheet，一個Sheet又是由多個Row組成，一個Row是由多個Cell組成。知道這些后那么我們就使用java來將數(shù)據(jù)以Excel的方式導出。讓我們一起來學習吧??！
一、引入Apache POI依賴
使用Java實現(xiàn)將數(shù)據(jù)以Excel的方式導出，需要依賴第三方的庫。我們需要再pom.xml中引入下面的依賴：
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>4.1.2</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>4.1.2</version>
</dependency>
二、用法&步驟
2.1 創(chuàng)建Excel的元素
（1）創(chuàng)建WokrBook
Workbook workbook = new XSSFWorkbook();
（2）創(chuàng)建Sheet
Sheet sheet = workbook.createSheet();
設置sheet的名稱
Sheet sheet = workbook.createSheet("sheet名稱");
（3）創(chuàng)建行Row
Row row = sheet.createRow(0);
（4）創(chuàng)建單元格Cell
Cell cell = row.createCell(0, CellType.STRING);
可以指定單元格的類型，支持的類型有下面7種：
_NONE(-1),
NUMERIC(0),
STRING(1),
//公式
FORMULA(2),
BLANK(3),
//布爾
BOOLEAN(4),
ERROR(5);
（5） 填充數(shù)據(jù)
cell.setCellValue("蘋果");
2.3 樣式和字體
如果我們需要導出的Excel美觀一些，如設置字體的樣式加粗、顏色、大小等等，就需要創(chuàng)建樣式和字體。
創(chuàng)建樣式：
CellStyle cellStyle = workbook.createCellStyle();
（1）左右垂直居中
//左右居中
excelTitleStyle.setAlignment(HorizontalAlignment.CENTER);
// 設置垂直居中
excelTitleStyle.setVerticalAlignment(VerticalAlignment.CENTER);
（2）字體加粗、顏色
創(chuàng)建加粗樣式并設置到CellStyle 中：
Font font = workbook.createFont();
//字體顏色為紅色
font.setColor(IndexedColors.RED.getIndex());
//字體加粗
font.setBold(true);
cellStyle.setFont(font);
指定Cell單元格使用該樣式：
cell.setCellStyle(style);
（3）調(diào)整列寬和高
Sheet sheet = workbook.createSheet();
//自動調(diào)整列的寬度來適應內(nèi)容
sheet.autoSizeColumn(int column);
// 設置列的寬度
sheet.setColumnWidth(2, 20 * 256);
autoSizeColumn()傳遞的參數(shù)就是要設置的列索引。setColumnWidth()第一個參數(shù)是要設置的列索引，第二參數(shù)是具體的寬度值，寬度 = 字符個數(shù) * 256（例如20個字符的寬度就是20 * 256）
（4）傾斜、下劃線
Font font = workbook.createFont();
font.setItalic(boolean italic); 設置傾斜
font.setUnderline(byte underline); 設置下劃線
2.4 進階用法
（1）合并單元格
Workbook workbook = new XSSFWorkbook();
Sheet sheet = workbook.createSheet(fileName);
sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 5));
-
CellRangeAddress()方法四個參數(shù)分別是fristRow：起始行、lastRow：結(jié)束行、fristCol：起始列、lastCol：結(jié)束列。
如果你想合并從第一行到第二行從一列到第十列的單元格（一共合并20格），那么就是CellRangeAddress(0,1,0,10)。
（2）字段必填
//創(chuàng)建數(shù)據(jù)驗證
DataValidationHelper dvHelper = sheet.getDataValidationHelper();
//創(chuàng)建要添加校驗的單元格對象
CellRangeAddressList addressList = new CellRangeAddressList(0, 0, 0, 10);
//創(chuàng)建必填校驗規(guī)則
DataValidationConstraint constraint = validationHelper.createCustomConstraint("NOT(ISBLANK(A1))");
//設置校驗
DataValidation validation = dvHelper.createValidation(constraint, addressList);
//校驗不通過 提示
validation.setShowErrorBox(true);
sheet.addValidationData(validation);
-
CellRangeAddressList()方法傳遞四個參數(shù)，分別是：fristRow：起始行、lastRow：結(jié)束行、fristCol：起始列、lastCol：結(jié)束列。CellRangeAddressList(0, 0, 0, 10)表示的就是給第一行從第一列開始到第十列一共十個單元格添加數(shù)據(jù)校驗。
（3）添加公式
- SUM：求和函數(shù)
//創(chuàng)建SUM公式
FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();
Cell sumCell = row.createCell(0);
sumCell.setCellFormula("SUM(A1:A10)");
//計算SUM公式結(jié)果
Cell sumResultCell = row.createCell(1);
sumResultCell.setCellValue(evaluator.evaluate(sumCell).getNumberValue());
- AVERAGE：平均數(shù)函數(shù)
//創(chuàng)建AVERAGE公式
FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();
Cell averageCell = row.createCell(0);
averageCell.setCellFormula("AVERAGE(A1:A10)");
//計算AVERAGE公式結(jié)果
Cell averageResultCell = row.createCell(1);
averageResultCell.setCellValue(evaluator.evaluate(averageCell).getNumberValue());
- COUNT：計數(shù)函數(shù)
//創(chuàng)建COUNT公式
FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();
Cell countCell = row.createCell(0);
countCell.setCellFormula("COUNT(A1:A10)");
//計算COUNT公式結(jié)果
Cell countResultCell = row.createCell(1);
countResultCell.setCellValue(evaluator.evaluate(countCell).getNumberValue());
- IF：條件函數(shù)
//創(chuàng)建IF公式
FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();
Cell ifCell = row.createCell(0);
ifCell.setCellFormula("IF(A1>B1,\"Yes\",\"No\")");
//計算IF公式結(jié)果
Cell ifResultCell = row.createCell(1);
ifResultCell.setCellValue(evaluator.evaluate(ifCell).getStringValue());
- CONCATENATE：連接函數(shù)
//創(chuàng)建CONCATENATE公式
FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();
Cell concatenateCell = row.createCell(0);
concatenateCell.setCellFormula("CONCATENATE(A1,\" \",B1)");
//計算CONCATENATE公式結(jié)果
Cell concatenateResultCell = row.createCell(1);
concatenateResultCell.setCellValue(evaluator.evaluate(concatenateCell).getStringValue());
（4）下拉選擇
//下拉值
private List<String> grade = Arrays.asList("高", "中", "低");
(此處省略n行代碼)
Sheet sheet = workbook.createSheet("sheet");
DataValidation dataValidation = this.addPullDownConstraint(i, sheet, grade );
sheet.addValidationData(dataValidation);
（5）設置單元格的數(shù)據(jù)類型
- 數(shù)字格式
// 設置單元格樣式 - 數(shù)字格式
CellStyle numberCellStyle = workbook.createCellStyle();
numberCellStyle.setDataFormat(workbook.createDataFormat().getFormat("#,##0.00"));
//指定單元格
Row row = sheet.createRow(0);
Cell cell = row.createCell(0);
cell.setCellStyle(numberCellStyle);
- 日期格式
// 設置單元格樣式 - 日期格式
CellStyle dateCellStyle = workbook.createCellStyle();
dateCellStyle.setDataFormat(workbook.createDataFormat().getFormat("yyyy-MM-dd"));
//指定單元格
Row row = sheet.createRow(0);
Cell cell = row.createCell(0);
cell.setCellStyle(dateCellStyle);
三、導出完整示例
下面的示例使用SpringBoot項目來演示：pom.xml依賴：
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
<version>2.7.5</version>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter</artifactId>
<version>2.7.5</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>4.1.2</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>4.1.2</version>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<version>1.16.16</version>
</dependency>
<!-- https://mvnrepository.com/artifact/com.alibaba.fastjson2/fastjson2 -->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>fastjson</artifactId>
<version>2.0.21</version>
</dependency>
<dependency>
<groupId>org.apache.commons</groupId>
<artifactId>commons-lang3</artifactId>
<version>3.12.0</version>
</dependency>
Controller層代碼：
package shijiangdiya.controller;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import shijiangdiya.utils.ExportUtils;
import javax.servlet.http.HttpServletResponse;
@RestController
@RequestMapping("/sync")
public class ExportController {
}
（1）代碼
@Autowired
private HttpServletResponse response;
@PostMapping("/export")
public void export() {
//模擬json數(shù)據(jù)
String data = "[{\n" +
" \"studentId\": \"20210101\",\n" +
" \"name\": \"Alice\",\n" +
" \"age\": 20,\n" +
" \"credit\": 80\n" +
" },\n" +
" {\n" +
" \"studentId\": \"20210102\",\n" +
" \"name\": \"Bob\",\n" +
" \"age\": 21,\n" +
" \"credit\": 85\n" +
" },\n" +
" {\n" +
" \"studentId\": \"20210103\",\n" +
" \"name\": \"Charlie\",\n" +
" \"age\": 22,\n" +
" \"credit\": 90\n" +
" },\n" +
" {\n" +
" \"studentId\": \"20210104\",\n" +
" \"name\": \"David\",\n" +
" \"age\": 20,\n" +
" \"credit\": 75\n" +
" },\n" +
" {\n" +
" \"studentId\": \"20210105\",\n" +
" \"name\": \"Emily\",\n" +
" \"age\": 21,\n" +
" \"credit\": 82\n" +
" },\n" +
" {\n" +
" \"studentId\": \"20210106\",\n" +
" \"name\": \"Frank\",\n" +
" \"age\": 22,\n" +
" \"credit\": 88\n" +
" },\n" +
" {\n" +
" \"studentId\": \"20210107\",\n" +
" \"name\": \"Grace\",\n" +
" \"age\": 20,\n" +
" \"credit\": 81\n" +
" },\n" +
" {\n" +
" \"studentId\": \"20210108\",\n" +
" \"name\": \"Henry\",\n" +
" \"age\": 21,\n" +
" \"credit\": 89\n" +
" },\n" +
" {\n" +
" \"studentId\": \"20210109\",\n" +
" \"name\": \"Isaac\",\n" +
" \"age\": 22,\n" +
" \"credit\": 92\n" +
" },\n" +
" {\n" +
" \"studentId\": \"20210110\",\n" +
" \"name\": \"John\",\n" +
" \"age\": 20,\n" +
" \"credit\": 78\n" +
" },\n" +
" {\n" +
" \"studentId\": \"20210111\",\n" +
" \"name\": \"Kelly\",\n" +
" \"age\": 21,\n" +
" \"credit\": 84\n" +
" },\n" +
" {\n" +
" \"studentId\": \"20210112\",\n" +
" \"name\": \"Linda\",\n" +
" \"age\": 22,\n" +
" \"credit\": 87\n" +
" },\n" +
" {\n" +
" \"studentId\": \"20210113\",\n" +
" \"name\": \"Mike\",\n" +
" \"age\": 20,\n" +
" \"credit\": 77\n" +
" },\n" +
" {\n" +
" \"studentId\": \"20210114\",\n" +
" \"name\": \"Nancy\",\n" +
" \"age\": 21,\n" +
" \"credit\": 83\n" +
" },\n" +
" {\n" +
" \"studentId\": \"20210115\",\n" +
" \"name\": \"Oscar\",\n" +
" \"age\": 22,\n" +
" \"credit\": 91\n" +
" },\n" +
" {\n" +
" \"studentId\": \"20210116\",\n" +
" \"name\": \"Paul\",\n" +
" \"age\": 20,\n" +
" \"credit\": 76\n" +
" },\n" +
" {\n" +
" \"studentId\": \"20210117\",\n" +
" \"name\": \"Queen\",\n" +
" \"age\": 21,\n" +
" \"credit\": 86\n" +
" },\n" +
" {\n" +
" \"studentId\": \"20210118\",\n" +
" \"name\": \"Rachel\",\n" +
" \"age\": 22,\n" +
" \"credit\": 94\n" +
" },\n" +
" {\n" +
" \"studentId\": \"20210119\",\n" +
" \"name\": \"Sarah\",\n" +
" \"age\": 20,\n" +
" \"credit\": 79\n" +
" },\n" +
" {\n" +
" \"studentId\": \"20210120\",\n" +
" \"name\": \"Tom\",\n" +
" \"age\": 21,\n" +
" \"credit\": 80\n" +
" }\n" +
"]\n";
ExportUtils.exportExcel("學生信息", data, Student.class, response);
}
（2）工具類
/**
* 數(shù)據(jù)導出
* @param fileName 導出excel名稱
* @param data 導出的數(shù)據(jù)
* @param c 導出數(shù)據(jù)的實體class
* @param response 響應
* @throws Exception
*/
public static void exportExcel(String fileName, String data, Class<?> c, HttpServletResponse response) throws Exception {
try {
// 創(chuàng)建表頭
// 創(chuàng)建工作薄
Workbook workbook = new XSSFWorkbook();
Sheet sheet = workbook.createSheet();
// 創(chuàng)建表頭行
Row rowHeader = sheet.createRow(0);
if (c == null) {
throw new RuntimeException("Class對象不能為空!");
}
Field[] declaredFields = c.getDeclaredFields();
List<String> headerList = new ArrayList<>();
if (declaredFields.length == 0) {
return;
}
for (int i = 0; i < declaredFields.length; i++) {
Cell cell = rowHeader.createCell(i, CellType.STRING);
String headerName = String.valueOf(declaredFields[i].getName());
cell.setCellValue(headerName);
headerList.add(i, headerName);
}
// 填充數(shù)據(jù)
List<?> objects = JSONObject.parseArray(data, c);
Object obj = c.newInstance();
if (!CollectionUtils.isEmpty(objects)) {
for (int o = 0; o < objects.size(); o++) {
Row rowData = sheet.createRow(o + 1);
for (int i = 0; i < headerList.size(); i++) {
Cell cell = rowData.createCell(i);
Field nameField = c.getDeclaredField(headerList.get(i));
nameField.setAccessible(true);
String value = String.valueOf(nameField.get(objects.get(o)));
cell.setCellValue(value);
}
}
}
response.setContentType("application/vnd.ms-excel");
String resultFileName = URLEncoder.encode(fileName, "UTF-8");
response.setHeader("Content-disposition", "attachment;filename=" + resultFileName + ";" + "filename*=utf-8''" + resultFileName);
workbook.write(response.getOutputStream());
workbook.close();
response.flushBuffer();
} catch (Exception e) {
throw new RuntimeException(e);
}
}
（2）結(jié)果
四、導入完整示例
（1）代碼
@PostMapping("/import")
public void importExcel(@RequestParam("excel") MultipartFile excel){
Workbook workbook = null;
try {
workbook = WorkbookFactory.create(excel.getInputStream());
Sheet sheet = workbook.getSheetAt(0);
List<Student> students = new ArrayList<>();
int i = 0;
for (Row row : sheet) {
Row row1 = sheet.getRow(i + 1);
if(row1 != null){
Student data = new Student();
data.setStudentId(Integer.parseInt(row1.getCell(0).getStringCellValue()));
data.setName(row1.getCell(1).getStringCellValue());
data.setAge(Integer.parseInt(row1.getCell(2).getStringCellValue()));
data.setCredit(Integer.parseInt(row1.getCell(3).getStringCellValue()));
students.add(data);
}
}
System.out.println(students);
workbook.close();
} catch (IOException e) {
throw new RuntimeException(e);
}
（2）工具類
/**
* 導入
* @param workbook 工作簿
* @param c 實體類
* @return 實體類集合
*/
public static <T> List<T> importExcel(Workbook workbook,Class<?> c){
List<T> dataList = new ArrayList<>();
try {
Sheet sheet = workbook.getSheetAt(0);
int i = 0;
T o = null;
for (Row row : sheet) {
Row row1 = sheet.getRow(i + 1);
if(row1 != null){
o = (T) c.newInstance();
Field[] declaredFields = c.getDeclaredFields();
for (int i1 = 0; i1 < declaredFields.length; i1++) {
String name = declaredFields[i1].getName();
Field declaredField1 = o.getClass().getDeclaredField(name);
declaredField1.setAccessible(true);
Cell cell = row1.getCell(i1);
String type = declaredFields[i1].getType().getName();
String value = String.valueOf(cell);
if(StringUtils.equals(type,"int") || StringUtils.equals(type,"Integer")){
declaredField1.set(o,Integer.parseInt(value));
} else if(StringUtils.equals(type,"java.lang.String") || StringUtils.equals(type,"char") || StringUtils.equals(type,"Character") ||
StringUtils.equals(type,"byte") || StringUtils.equals(type,"Byte")){
declaredField1.set(o,value);
} else if(StringUtils.equals(type,"boolean") || StringUtils.equals(type,"Boolean")){
declaredField1.set(o,Boolean.valueOf(value));
} else if(StringUtils.equals(type,"double") || StringUtils.equals(type,"Double")){
declaredField1.set(o,Double.valueOf(value));
} else if (StringUtils.equals(type,"long") || StringUtils.equals(type,"Long")) {
declaredField1.set(o,Long.valueOf(value));
} else if(StringUtils.equals(type,"short") || StringUtils.equals(type,"Short")){
declaredField1.set(o,Short.valueOf(value));
} else if(StringUtils.equals(type,"float") || StringUtils.equals(type,"Float")){
declaredField1.set(o,Float.valueOf(value));
}
}
}
dataList.add(o);
}
workbook.close();
return dataList;
}catch (Exception e){
e.printStackTrace();
}
return dataList;
}
注意：導入工具類僅限Java的八大基礎(chǔ)數(shù)據(jù)類型和String類型。如果還有其他類型需要自己擴展。文章來源：http://www.zghlxwxcb.cn/news/detail-459569.html
（3）結(jié)果
學生信息集合：
文章來源地址http://www.zghlxwxcb.cn/news/detail-459569.html
到了這里，關(guān)于使用Java導入、導出excel詳解（附有封裝好的工具類）的文章就介紹完了。如果您還想了解更多內(nèi)容，請在右上角搜索TOY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！
