/**
* Description: Get the excel data of a transaction a few days ago
*
* @param file
* @return
*/
private Map<String,List<SpotDailyDealImportVO>> SpotDailyDealLoadExcelData(MultipartFile file){
Map<String,List<SpotDailyDealImportVO>> map = new HashMap<>();
try {
//Get all sheet pages List<ReadSheet> readSheets = (());
List<ReadSheet> arrayList = ().collect(
// Put the collection into treeSet first and then convert them into list collectingAndThen(
toCollection(() -> new TreeSet<>((o -> ())))
, ArrayList::new));
if (() < ()){
throw new BusinessException("The same unit name exists!");
}
if ((readSheets)){
//Read data int i = 0;//Read from the first page for (ReadSheet readSheet : readSheets){
((),(
(()), (), i ,
, ));
i++;
}
}else throw new BusinessException("Cannot get sheet page information!");
} catch (IOException e) {
("error",e);
throw new BusinessException("The upload file format is incorrect" + ());
}
if (() == 0){
throw new BusinessException("Data exception, no data!");
}
return map;
}
Tools
/*
* Project Name: fsc
* File Name:
* Class Name: ExcelImportHelper
*
* Copyright 2018 Hengtian Software Inc
*
* Licensed under the Hengtiansoft
*
*
*
* Unless required by applicable law or agreed to in writing, software
* distributed under the License is distributed on an "AS IS" BASIS,
* WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or
* implied.
* See the License for the specific language governing permissions and
* limitations under the License.
*/
package ;
import ;
import ;
import ;
import ;
import ;
import ;
import ;
import .slf4j.Slf4j;
import ;
import ;
import ;
import ;
import ;
/**
* Description: Excel tool class
*
* @author qianqianzhu1, weiyangzhu
* @since 29.09.2018
*/
@Slf4j
public final class ExcelImportHelper {
public static final String OFFICE_EXCEL_XLS = ".xls";
public static final String OFFICE_EXCEL_XLSX = ".xlsx";
public static final String OFFICE_EXCEL_XLSM = ".xlsm";
private ExcelImportHelper() {
}
/**
* Obtain Excel file type according to file extension
*
* @param excelName file name, including extension
* @return Excel type
*/
public static ExcelType getExcelTypeByName(String excelName) {
if (excelName == null) {
throw new IllegalArgumentException("Excel name is null.");
}
if ((OFFICE_EXCEL_XLS)) {
return ;
} else if ((OFFICE_EXCEL_XLSX)) {
return ;
} else if ((OFFICE_EXCEL_XLSM)) {
return ;
} else {
throw new IllegalArgumentException("Invalid excel name: " + excelName);
}
}
/**
* Read all the 0th Sheet in the specified Excel file into memory, skip the title line
*
* @param excelFile Excel file
* @param titleLine Number of title lines that need to be skipped
* @param clazz Data class description
* @param <T> Data Type
* @return List of data read from Excel
* @throws IOException IO Exception
*/
public static <T> List<T> loadExcelDataAtSheet0Fully(File excelFile, Integer titleLine, Class<T> clazz) throws IOException {
if (excelFile == null) {
throw new IllegalArgumentException("Excel file is null.");
}
ExcelType excelType = getExcelTypeByName(());
return loadExcelDataAtSheet0Fully(excelType, new FileInputStream(excelFile), titleLine, clazz);
}
/**
* Read all the 0th Sheet in the Excel file in the specified input stream into memory, skip the title line
*
* @param excelType Excel file type
* @param inputStream Excel file input stream
* @param titleLine Number of title lines that need to be skipped
* @param clazz Data class description
* @param <T> Data Type
* @return List of data read from Excel
* @throws IOException IO Exception
*/
public static <T> List<T> loadExcelDataAtSheet0Fully(ExcelType excelType, InputStream inputStream, Integer titleLine, Class<T> clazz) throws IOException {
return loadExcelDataFully(excelType, inputStream, 0, true, titleLine, clazz);
}
/**
* Read all data in the Excel file in the specified input stream into memory
*
* @param excelType Excel file type
* @param inputStream Excel file input stream
* @param sheetIndex The sheet index number that needs to be read, starting from 0, -1 means reading all sheets
* @param skipFirstRow true means skipping the title line
* @param titleLine Number of title lines that need to be skipped
* @param clazz Data class description
* @param <T> Data Type
* @return List of data read from Excel
* @throws IOException IO Exception
*/
public static <T> List<T> loadExcelDataFully(ExcelType excelType, InputStream inputStream, int sheetIndex,
boolean skipFirstRow, Integer titleLine, Class<T> clazz) throws IOException {
return loadExcelDataFully(excelType, inputStream, sheetIndex, skipFirstRow ? titleLine : 0, clazz);
}
/**
* Read all data in the Excel file in the specified input stream into memory
*
* @param excelType Excel file type
* @param inputStream Excel file input stream
* @param sheetIndex The sheet index number that needs to be read, starting from 0, -1 means reading all sheets
* @param dataStartRow The row number (including the row) that starts reading the data, starting from 0
* @param clazz Data class description
* @param <T> Data Type
* @return List of data read from Excel
* @throws IOException IO Exception
*/
public static <T> List<T> loadExcelDataFully(ExcelType excelType, InputStream inputStream, int sheetIndex,
int dataStartRow, Class<T> clazz) throws IOException {
FullyReadExcelDataHandler<T> dataHandler = new FullyReadExcelDataHandler<>();
loadExcelData(excelType, inputStream, sheetIndex, dataStartRow, clazz, dataHandler);
if (()) {
String errorMsg = "Reading excel occurs error";
if (() != null) {
errorMsg = errorMsg + " at sheet: " + ();
}
if (() != -1) {
errorMsg = errorMsg + " row: " + ();
}
if (() != -1) {
errorMsg = errorMsg + " col: " + ();
}
throw new IOException(errorMsg, ());
}
return ();
}
/**
* Use the read processor to process Excel files in the input stream, read in as a file stream, which may take up more memory
*
* @param excelType Excel type
* @param inputStream input stream
* @param sheetIndex The sheet index number that needs to be read, starting from 0, -1 means reading all sheets
* @param dataStartRow The row number (including the row) that starts reading the data, starting from 0
* @param clazz Data class description
* @param dataHandler Excel data processor
* @param <T> Data Type
*/
public static <T> void loadExcelData(ExcelType excelType, InputStream inputStream, int sheetIndex,
int dataStartRow, Class<T> clazz, ExcelDataHandler<T> dataHandler) {
switch (excelType) {
case XLS:
(inputStream, sheetIndex, dataStartRow, clazz, dataHandler);
break;
case XLSX:
case XLSM:
(inputStream, sheetIndex, dataStartRow, clazz, dataHandler);
break;
default:
throw new IllegalArgumentException("Invalid excel type: " + excelType);
}
}
/**
* Using the read processor to process Excel files in the input stream. Reading in the file will save some memory and the probability of OOM is smaller.
*
* @param excelType Excel type
* @param file Excel file
* @param sheetIndex The sheet index number that needs to be read, starting from 0, -1 means reading all sheets
* @param dataStartRow The row number (including the row) that starts reading the data, starting from 0
* @param clazz Data class description
* @param dataHandler Excel data processor
* @param <T> Data Type
*/
public static <T> void loadExcelData(ExcelType excelType, File file, int sheetIndex,
int dataStartRow, Class<T> clazz, ExcelDataHandler<T> dataHandler) {
switch (excelType) {
case XLS:
(file, sheetIndex, dataStartRow, clazz, dataHandler);
break;
case XLSX:
(file, sheetIndex, dataStartRow, clazz, dataHandler);
break;
default:
throw new IllegalArgumentException("Invalid excel type: " + excelType);
}
}
/**
* Get all sheets of excel file
* @date 2022/11/25 17:13
* @param inputStream file stream
* @return <>
*/
public static List<ReadSheet> listSheet(InputStream inputStream){
if(inputStream == null){
throw new RuntimeException("inputStream is null");
}
ExcelReader build = (inputStream).build();
List<ReadSheet> readSheets = ().sheetList();
((readSheets));
return readSheets;
}
}