maven依賴
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>
<!--解析excel-->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>RELEASE</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>RELEASE</version>
</dependency>
<!-- file檔案轉化為mutifile -->
<dependency>
<groupId>org.apache.httpcomponents</groupId>
<artifactId>httpcore</artifactId>
<version>4.4.9</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-test</artifactId>
<version>5.1.6.RELEASE</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-web</artifactId>
<version>5.2.9.RELEASE</version>
</dependency>
</dependencies>
讀取Excel檔案
package com.example.test;
import org.apache.http.entity.ContentType;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.bouncycastle.util.test.Test;
import org.springframework.mock.web.MockMultipartFile;
import org.springframework.web.multipart.MultipartFile;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
public class ExcelReadTest {
/**
*将File檔案轉化為MultipartFile檔案
* @param url
* @return
* @throws IOException
*/
public MultipartFile fileToMultipartFile(String url) throws IOException {
File file = new File(url);
FileInputStream fileInputStream = new FileInputStream(file);
MultipartFile multipartFile = new MockMultipartFile(file.getName(), file.getName(),
ContentType.APPLICATION_OCTET_STREAM.toString(), fileInputStream);
return multipartFile;
}
/**
* 将檔案轉化為可操作的類型
* @param multipartFile
* @return
* @throws IOException
*/
public Workbook getWorkFile(MultipartFile multipartFile) throws IOException {
Workbook workbook = null;
//擷取檔案的類型
String type = multipartFile.getName().substring(multipartFile.getName().lastIndexOf(".")+1);
//擷取檔案位元組輸入流
InputStream in = multipartFile.getInputStream();//擷取檔案輸入流
if ("xls".equals(type)) {
workbook = new HSSFWorkbook(in);
} else if ("xlsx".equals(type)) {
workbook = new XSSFWorkbook(in);
}
return workbook;
}
/**
* 周遊讀取excel的每一個單元格
* @param workbook
*/
public void readFile(Workbook workbook) {
//周遊sheet
for (int numSheet = 0; numSheet < workbook.getNumberOfSheets(); numSheet++) {
//得到單個sheet
Sheet sheet = workbook.getSheetAt(numSheet);
if (sheet == null) {
continue;
}
//得到單個sheet的行數
int rowCount = sheet.getLastRowNum();
//從第二行開始，周遊Sheet的每一行(第一行一般是标題，是以不周遊)
for (int rowNum = 0; rowNum < rowCount; rowNum++) {
try {
//得到單行資料
Row row = sheet.getRow(rowNum);
if (row != null) {
int cellCount = row.getLastCellNum();
for (int cellNum = 0;cellNum<cellCount;cellNum++){
Cell cell = row.getCell(cellNum);
String cellValue = "";
if (cell!=null){
cell.setCellType(CellType.STRING);//提前設定String類型，防止數字後加.0
cellValue = cell.getStringCellValue();
//如果上面的setCellType(CellType.STRING)過期，可以先将Cell轉化為CellBase，然後再定義類型
// CellBase cellBase = (CellBase) cell;
// cellBase.setCellType(CellType.STRING);
// cellValue = cellBase.getStringCellValue();
}
System.out.print(cellValue + "\t");
}
System.out.println("");
}
} catch (Exception e) {
e.printStackTrace();
}
}
}
}
public void excelUtil(String url) throws IOException {
MultipartFile multipartFile = fileToMultipartFile(url);
Workbook workbook = getWorkFile(multipartFile);
readFile(workbook);
}
public static void main(String[] args) throws IOException {
// File file = new File("G:\\IDEAWorkspeace\\test\\excle\\src\\main\\resources\\excel\\試點團隊配置模闆.xls");
String file = "G:\\IDEAWorkspeace\\test\\excle\\src\\main\\resources\\excel\\試點團隊配置模闆.xls";
ExcelReadTest test = new ExcelReadTest();
test.excelUtil(file);
}
}
建立Excel檔案
package com.example.test;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.*;
public class ExcelWriteTest {
public void createExcel(){
//1、建立workbook
HSSFWorkbook wb = new HSSFWorkbook();
//2、建立sheet
HSSFSheet sheet = wb.createSheet("團隊資訊");
///3、建立第一行标題
HSSFRow row = sheet.createRow(0);
HSSFCellStyle style = wb.createCellStyle();
style.setAlignment(HorizontalAlignment.CENTER); //居中
List<String> title = new ArrayList<>();
title.add("團隊代碼");
title.add("團隊名稱");
//4、插入标題的值
for (int i =0; i < title.size(); i++){
Cell cell = row.createCell(i);
cell.setCellValue(title.get(i));
cell.setCellStyle(style);
}
// 5、建立單元格的值
List<Map> data = new ArrayList<>();
Map map = new HashMap();
map.put("code", 1);
map.put("name", "一團隊");
data.add(map);
//4、插入具體的單元格值
for (int i = 0; i < data.size(); i++) {
//從第二行開始插入資料
row = sheet.createRow(i+1);
// 擷取資料
Map cellData = data.get(i);
Set<String> set = cellData.keySet();
int j = 0;
for (String key : set){
row.createCell(j).setCellValue(String.valueOf(cellData.get(key)));
j++;
}
}
//6、将生成excel檔案儲存到指定路徑下
try {
File file = new File("G:\\IDEAWorkspeace\\test\\excle\\src\\main\\resources\\excel\\write.xls");
if (file.exists()){
file.delete();
}
FileOutputStream fout = new FileOutputStream(file);
wb.write(fout);
fout.close();
} catch (IOException e) {
e.printStackTrace();
}
System.out.println("檔案已成功建立");
}
public static void main(String[] args) {
ExcelWriteTest writeTest = new ExcelWriteTest();
writeTest.createExcel();
}
}
![Java小案例——随機數猜測随機數猜測[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)