讀取得到的資料格式為Map<String, List<List>>
List為某一行的資料集
List<List> 為目前sheet工作簿的資料集
Map<String, List<List>> 為所有工作簿的資料集，Map的鍵為工作簿的sheet的名稱
依賴包：
<!-- https://mvnrepository.com/artifact/org.apache.poi/poi -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>4.0.1</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>4.0.1</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml-schemas</artifactId>
<version>4.0.1</version>
</dependency>
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.Date;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
/**
* description: excel工具類
*
* @author sanchar
* @date 12/10/2018 17:10
* lastUpdateBy: sanchar
* lastUpdateDate: 12/10/2018
*/
public class ExcelUtils {
private static final String EXCEL_XLS = ".xls";
private static final String EXCEL_XLSX = ".xlsx";
/**
* description: 讀取excel檔案,預設檔案格式為 .xlsx
*
* @param inputStream excel檔案輸入流
* @return 以sheet頁簽名為鍵，頁簽的資料為值，資料以行為list存入map，以列為list存入行list
*/
public static Map<String, List<List<Object>>> readExcel(InputStream inputStream) {
return readExcel(EXCEL_XLSX, inputStream);
}
/**
* description: 讀取excel檔案
*
* @param fileType 檔案格式
* @param inputStream excel檔案輸入流
* @return 以sheet頁簽名為鍵，頁簽的資料為值，資料以行為list存入map，以列為list存入行list
*/
public static Map<String, List<List<Object>>> readExcel(String fileType, InputStream inputStream) {
return readExcel(fileType, inputStream, 1, (short) 1);
}
/**
* description: 讀取excel檔案
*
* @param fileType 檔案格式
* @param inputStream excel檔案輸入流
* @param startRow 開始讀取的第幾行
* @param startColumn 開始讀取的第幾列
* @return 以sheet頁簽名為鍵，頁簽的資料為值，資料以行為list存入map，以列為list存入行list
*/
public static Map<String, List<List<Object>>> readExcel(String fileType, InputStream inputStream,
int startRow, short startColumn) {
Map<String, List<List<Object>>> dataMap = new LinkedHashMap<>();
int rowSize = 0;
Workbook wb;
Sheet st;
Row row;
Cell cell;
try {
if (EXCEL_XLS.equalsIgnoreCase(fileType)) {
wb = new HSSFWorkbook(inputStream);
} else if (EXCEL_XLSX.equalsIgnoreCase(fileType)) {
wb = new XSSFWorkbook(inputStream);
} else {
return dataMap;
}
for (int sheetIndex = 0; sheetIndex < wb.getNumberOfSheets(); sheetIndex++) {
List<List<Object>> outerList = new ArrayList<>();
st = wb.getSheetAt(sheetIndex);
String sheetName = st.getSheetName();
for (int rowIndex = startRow - 1; rowIndex <= st.getLastRowNum(); rowIndex++) {
List<Object> innerList = new ArrayList();
row = st.getRow(rowIndex);
if (row == null) {
continue;
}
int tempRowSize = row.getLastCellNum() + 1;
if (tempRowSize > rowSize) {
rowSize = tempRowSize;
}
boolean hasValue = false;
for (short columnIndex = (short) (startColumn - 1); columnIndex < row.getLastCellNum(); columnIndex++) {
cell = row.getCell(columnIndex);
Object value = getFormatValue(cell);
if (columnIndex == startColumn && value == null) {
break;
}
innerList.add(value);
hasValue = true;
}
if (hasValue) {
outerList.add(innerList);
}
}
dataMap.put(sheetName, outerList);
}
inputStream.close();
} catch (Exception e) {
e.printStackTrace();
}
return dataMap;
}
/**
* description: 寫excel檔案
*
* @param dataMap 寫入的資料
* @return 檔案的輸出流
*/
public static Workbook writeExcel(Map<String, List<List<Object>>> dataMap) {
return writeExcel(dataMap, EXCEL_XLSX);
}
/**
* description: 寫excel檔案
*
* @param dataMap 寫入的資料
* @param startSheet 從第幾個sheet開始寫入
* @param startRow 從第幾行開始寫入
* @param startColumn 從第幾列開始寫入
* @return 檔案的輸出流
*/
public static Workbook writeExcel(Map<String, List<List<Object>>> dataMap, int startSheet, int startRow, short startColumn) {
return writeExcel(dataMap, EXCEL_XLSX, startSheet, startRow, startColumn);
}
/**
* description: 寫excel檔案
*
* @param dataMap 寫入的資料
* @param fileType 檔案類型
* @return 檔案的輸出流
*/
public static Workbook writeExcel(Map<String, List<List<Object>>> dataMap, String fileType) {
return writeExcel(dataMap, fileType, 1, 1, (short) 1);
}
/**
* description: 寫excel檔案
*
* @param dataMap 寫入的資料
* @param fileType 檔案類型
* @param startSheet 從第幾個sheet開始寫入
* @param startRow 從第幾行開始寫入
* @param startColumn 從第幾列開始寫入
* @return 檔案的輸出流
*/
public static Workbook writeExcel(Map<String, List<List<Object>>> dataMap, String fileType,
int startSheet, int startRow, short startColumn) {
Workbook wb = null;
Sheet st;
try {
if (EXCEL_XLS.endsWith(fileType)) {
wb = new HSSFWorkbook();
} else if (EXCEL_XLSX.endsWith(fileType)) {
wb = new XSSFWorkbook();
} else {
wb = new XSSFWorkbook();
}
int emptySheet = startSheet;
while (startSheet > 1) {
wb.createSheet("Sheet" + (emptySheet - startSheet + 1));
startSheet--;
}
for (String name : dataMap.keySet()) {
st = wb.createSheet(name);
List<List<Object>> dataList = dataMap.get(name);
for (int i = 0; i < dataList.size(); i++) {
Row row = st.createRow(i + startRow - 1);
List<Object> valueList = dataList.get(i);
for (int j = 0; j < valueList.size(); j++) {
// 自适應寬度
st.autoSizeColumn(j + startColumn - 1, true);
Cell cell = row.createCell(j + startColumn - 1);
Object value = valueList.get(j);
if (value instanceof Integer) {
cell.setCellValue((Integer) value);
} else if (value instanceof String) {
cell.setCellValue((String) value);
} else if (value instanceof Double) {
cell.setCellValue((Double) value);
} else if (value instanceof Float) {
cell.setCellValue((Float) value);
} else if (value instanceof Long) {
cell.setCellValue((Long) value);
} else if (value instanceof Boolean) {
cell.setCellValue((Boolean) value);
} else if (value instanceof Date) {
cell.setCellValue((Date) value);
}
}
}
}
} catch (Exception e) {
e.printStackTrace();
}
return wb;
}
/**
* description: 擷取資料
*
* @param cell 單元格
* @return 單元格的資料
*/
private static Object getFormatValue(Cell cell) {
Object value = null;
if (cell != null) {
switch (cell.getCellType()) {
case STRING:
value = rightTrim(cell.getStringCellValue());
break;
case NUMERIC:
if (HSSFDateUtil.isCellDateFormatted(cell)) {
value = cell.getDateCellValue();
} else {
value = cell.getNumericCellValue();
}
break;
case FORMULA:
// 導入時如果為公式生成的資料則無值
if (!cell.getStringCellValue().equals("")) {
value = cell.getStringCellValue();
} else {
value = cell.getNumericCellValue() + "";
}
break;
case BLANK:
break;
case ERROR:
value = null;
break;
case BOOLEAN:
value = cell.getBooleanCellValue();
break;
default:
value = null;
}
}
return value;
}
/**
* 去掉字元串右邊的空格
*
* @param str 要處理的字元串
* @return 處理後的字元串
*/
public static String rightTrim(String str) {
if (str == null) {
return "";
}
int length = str.length();
for (int i = length - 1; i >= 0; i--) {
if (str.charAt(i) != 0x20) {
break;
}
length--;
}
return str.substring(0, length);
}
public static void main(String[] args) throws IOException {
File file1 = new File("C:\\Users\\97773\\Desktop\\test.xlsx");
Map<String, List<List<Object>>> dataMap = readExcel(file1.getName().substring(file1.getName().lastIndexOf(".")),
new FileInputStream(file1.getAbsolutePath()), 2, (short) 2);
File file = new File("C:\\Users\\97773\\Desktop\\test2.xlsx");
if (!file.exists()) {
file.createNewFile();
}
OutputStream outputStream = new FileOutputStream(file);
writeExcel(dataMap, 1, 2, (short) 2).write(outputStream);
outputStream.close();
System.out.println(dataMap);
}
}
![Java小案例——随機數猜測随機數猜測[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)