package com.shooin.config;
import com.shooin.common.BizException;
import com.shooin.model.vo.ImportTagVo;
import io.swagger.annotations.ApiModelProperty;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
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 org.springframework.mock.web.MockMultipartFile;
import org.springframework.web.multipart.MultipartFile;
import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.GregorianCalendar;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import static com.shooin.common.ResultEnum.ERROR_FILE_TYPE__NOT_SUPPORTED;
/**
* ExcelHandle
*
* @author gongweixin
* @date 2021/3/15
*/
@Slf4j
public class ExcelHandle {
private static final int SECONDS_PER_MINUTE = 60;
private static final int MINUTES_PER_HOUR = 60;
private static final int HOURS_PER_DAY = 24;
private static final int SECONDS_PER_DAY = (HOURS_PER_DAY * MINUTES_PER_HOUR * SECONDS_PER_MINUTE);
private static final long DAY_MILLISECONDS = SECONDS_PER_DAY * 1000L;
/**
* Excel表格導出
* @param response HttpServletResponse對象
* @param excelData Excel表格的資料，封裝為List<List<String>>
* @param sheetName sheet的名字
* @param fileName 導出Excel的檔案名
* @param columnWidth Excel表格的寬度，建議為15
* @throws IOException 抛IO異常
*/
public static void exportExcel(HttpServletResponse response,
List<List<String>> excelData,
String sheetName,
String fileName,
int columnWidth) throws IOException {
//聲明一個工作簿
HSSFWorkbook workbook = new HSSFWorkbook();
//生成一個表格，設定表格名稱
HSSFSheet sheet = workbook.createSheet(sheetName);
//設定表格列寬度
sheet.setDefaultColumnWidth(columnWidth);
//寫入List<List<String>>中的資料
int rowIndex = 0;
for(List<String> data : excelData){
//建立一個row行，然後自增1
HSSFRow row = sheet.createRow(rowIndex++);
//周遊添加本行資料
for (int i = 0; i < data.size(); i++) {
//建立一個單元格
HSSFCell cell = row.createCell(i);
//建立一個内容對象
HSSFRichTextString text = new HSSFRichTextString(data.get(i));
//将内容對象的文字内容寫入到單元格中
cell.setCellValue(text);
}
}
//準備将Excel的輸出流通過response輸出到頁面下載下傳
//八進制輸出流
response.setContentType("application/octet-stream");
//設定導出Excel的名稱
response.setHeader("Content-disposition", "attachment;filename=" + fileName);
//重新整理緩沖
response.flushBuffer();
//測試寫入本地檔案
//workbook.write(new File("C:\\Users\\Administrator\\Desktop\\excel測試\\fileName.xlsx"));
//workbook将Excel寫入到response的輸出流中，供頁面下載下傳該Excel檔案
workbook.write(response.getOutputStream());
//關閉workbook
workbook.close();
}
/**
* 将excel檔案資料轉換為實體類資料清單（隻轉換第一頁）
* @param file
* @param clazz
* @param <T>
* @return
* @throws IOException
*/
public static <T> List<T> readExcelFileToDTO(MultipartFile file, Class<T> clazz) throws IOException {
return readExcelFileToDTO(file, clazz, 0);
}
/**
* 将excel檔案轉換為實體類
* @param file
* @param clazz
* @param sheetId
* @param <T>
* @return
* @throws IOException
*/
public static <T> List<T> readExcelFileToDTO(MultipartFile file, Class<T> clazz, Integer sheetId) throws IOException {
//将檔案轉成workbook類型
Workbook workbook = buildWorkbook(file);
//第一個表
return readSheetToDTO(workbook.getSheetAt(sheetId), clazz);
}
/**
* 将sheet頁資料轉換實體類資料清單
* @param sheet
* @param clazz
* @param <T>
* @return
* @throws IOException
*/
public static <T> List<T> readSheetToDTO(Sheet sheet, Class<T> clazz) throws IOException {
List<T> result = new ArrayList<>();
List<Map<String, String>> sheetValue = changeSheetToMapList(sheet);
for (Map<String, String> valueMap : sheetValue) {
T dto = buildDTOByClass(clazz, valueMap);
if (dto != null) {
result.add(dto);
}
}
return result;
}
/**
* 檔案資料流建構Workbook
* @param file
* @return
* @throws IOException
*/
public static Workbook buildWorkbook(MultipartFile file) throws IOException {
String filename = file.getOriginalFilename();
if (filename.endsWith(".xls")) {
return new HSSFWorkbook(file.getInputStream());
} else if(filename.endsWith(".xlsx")){
return new XSSFWorkbook(file.getInputStream());
} else {
throw new BizException(ERROR_FILE_TYPE__NOT_SUPPORTED);
}
}
/**
* 表頭處理
* @param sheet
* @return
*/
private static List<Map<String, String>> changeSheetToMapList(Sheet sheet) {
List<Map<String, String>> result = new ArrayList<>();
int rowNumber = sheet.getPhysicalNumberOfRows();
String[] titles = getSheetRowValues(sheet.getRow(0)); // 第一行作為表頭
for (int i = 1; i < rowNumber; i++) {
String[] values = getSheetRowValues(sheet.getRow(i));
Map<String, String> valueMap = new HashMap<>();
for (int j = 0; j < titles.length; j++) {
valueMap.put(titles[j], values[j]);
}
result.add(valueMap);
}
return result;
}
/**
* 建立資料模型
* @param clazz
* @param valueMap
* @param <T>
* @return
*/
private static <T> T buildDTOByClass(Class<T> clazz, Map<String, String> valueMap) {
try {
T dto = clazz.newInstance();
for (Field field : clazz.getDeclaredFields()) {
ApiModelProperty desc = field.getAnnotation(ApiModelProperty.class);
String value = valueMap.get(desc.value());
if (StringUtils.isNotEmpty(value)) {
if(field.getType().getName().equalsIgnoreCase("java.lang.Integer")) {
Method method = clazz.getMethod(getSetMethodName(field.getName()), field.getType());
Integer age = Integer.parseInt(value.split("\\.")[0]);
method.invoke(dto, age);
}else if(field.getType().getName().equalsIgnoreCase("java.util.Date")) {
Method method = clazz.getMethod(getSetMethodName(field.getName()), field.getType());
Date date = excelDateToJava(Double.parseDouble(value));
method.invoke(dto, date);
}
else{
Method method = clazz.getMethod(getSetMethodName(field.getName()), field.getType());
method.invoke(dto, value);
}
}
}
return dto ;
} catch (Exception e) {
log.error("資料類型轉換失敗！ e = {}",e.getStackTrace());
throw new BizException("資料類型轉換失敗！");
}
}
private static String getSetMethodName(String name) {
String firstChar = name.substring(0, 1);
return "set" + firstChar.toUpperCase() + name.substring(1);
}
private static String[] getSheetRowValues(Row row) {
if (row == null) {
return new String[]{};
} else {
int cellNumber = row.getLastCellNum();
List<String> cellValueList = new ArrayList<>();
for (int i = 0; i < cellNumber; i++) {
cellValueList.add(getValueOnCell(row.getCell(i)));
}
return cellValueList.toArray(new String[0]);
}
}
private static String getValueOnCell(Cell cell) {
if (cell == null) {
return "";
}
switch (cell.getCellTypeEnum()) {
case STRING: return cell.getStringCellValue();
case NUMERIC: return String.valueOf(cell.getNumericCellValue());
case BOOLEAN: return cell.getBooleanCellValue() ? "true" : "false";
case FORMULA:
try {
return cell.getStringCellValue();
} catch (Exception e) {
return String.valueOf(cell.getNumericCellValue());
}
default: return "";
}
}
/**
* 将excel裡面的數字時間轉化為準确的時間格式！
* @param date
* @return
*/
public static Date excelDateToJava(double date){
int wholeDays = (int)Math.floor(date);
int millisecondsInDay = (int)((date - wholeDays) * DAY_MILLISECONDS + 0.5);
Calendar calendar = new GregorianCalendar(); // using default time-zone
setCalendar(calendar, wholeDays, millisecondsInDay, false);
return calendar.getTime();
}
public static void setCalendar(Calendar calendar, int wholeDays,
int millisecondsInDay, boolean use1904windowing) {
int startYear = 1900;
int dayAdjust = -1;
if (use1904windowing) {
startYear = 1904;
dayAdjust = 1;
}
else if (wholeDays < 61) {
dayAdjust = 0;
}
calendar.set(startYear,0, wholeDays + dayAdjust, 0, 0, 0);
calendar.set(GregorianCalendar.MILLISECOND, millisecondsInDay);
}
public static void main(String[] args) {
try {
File pdf = new File("C:\\Users\\Administrator\\Desktop\\excel測試\\标簽導入模闆.xlsx");
FileInputStream fileInputStream = new FileInputStream(pdf);
MultipartFile multipartFile = new MockMultipartFile(pdf.getName(),pdf.getName(),
"multipart/form-data",fileInputStream);
List<ImportTagVo> test = readExcelFileToDTO(multipartFile, ImportTagVo.class);
System.out.println(test.toString());
} catch (FileNotFoundException e) {
e.printStackTrace();
}catch (IOException e) {
e.printStackTrace();
}
}
}
![Java小案例——随機數猜測随機數猜測[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)