import org.apache.commons.io.FileUtils;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.springframework.web.multipart.MultipartFile;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.lang.reflect.Field;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;
/**
* Excel数据导入导出帮助类
*/
public class ExcelHelper {
public ExcelHelper() {}
public static String transferTo(MultipartFile fileField, String path, String fileName) throws Exception {
File targetFile = new File(path, fileName);
if (!targetFile.getParentFile().exists()) {
targetFile.getParentFile().mkdirs();
}
targetFile.deleteOnExit();
targetFile.createNewFile();
try {
fileField.transferTo(targetFile);
} catch (Exception var5) {
var5.printStackTrace();
}
return targetFile.getPath();
}
/**
* 导入导出时的公用流操作
*/
public static void commonStreamOperation(HttpServletRequest request,
HttpServletResponse response, File file) {
try {
FileInputStream fis = new FileInputStream(file);
BufferedInputStream bis = new BufferedInputStream(fis);
OutputStream os = response.getOutputStream();
BufferedOutputStream bos = new BufferedOutputStream(os);
response.setHeader("Content-disposition", "attachment;filename=" + new String(file.getName().getBytes("gb2312"), "ISO8859-1"));
int bytesRead = 0;
byte[] buffer = new byte[1024];
while ((bytesRead = bis.read(buffer)) != -1) {
bos.write(buffer, 0, bytesRead);
}
bos.close();
bis.close();
os.flush();
os.close();
fis.close();
} catch (Exception e) {
e.printStackTrace();
}
}
/**
* 导入导出时的公用流操作
*/
public static void commonStreamOperation(HttpServletRequest request,
HttpServletResponse response, InputStream fis, String fileName) {
try {
BufferedInputStream bis = new BufferedInputStream(fis);
OutputStream os = response.getOutputStream();
BufferedOutputStream bos = new BufferedOutputStream(os);
response.setHeader("Content-disposition", "attachment;filename=" + new String(fileName.getBytes("gb2312"), "ISO8859-1"));
int bytesRead = 0;
byte[] buffer = new byte[1024];
while ((bytesRead = bis.read(buffer)) != -1) {
bos.write(buffer, 0, bytesRead);
}
bos.close();
bis.close();
os.flush();
os.close();
fis.close();
} catch (Exception e) {
e.printStackTrace();
}
}
/**
* 校验表头格式
*
* @param row 表头所在的行
* @param headers 需要将表头对应的值放入 list数组
* @throws Exception
*/
private static void validateHeader(XSSFRow row, List<String> headers) throws IllegalAccessException {
if (headers.size() == row.getLastCellNum()) {
for (Cell cell : row) {
int columnIndex = cell.getColumnIndex();
String cellValue = cell.getStringCellValue();
if (!headers.get(columnIndex).contains(cellValue)) {
throw new IllegalAccessException("传入表格列名不正确!");
}
}
} else {
throw new IllegalAccessException("传入表格列名不正确!");
}
}
public static String getCellValue(Cell cell) {
String cellValue = "";
if (cell == null) {
return cellValue;
}
// 判断数据的类型
switch (cell.getCellType()) {
case Cell.CELL_TYPE_NUMERIC: // 数字
cellValue = String.valueOf(cell.getNumericCellValue());
break;
case Cell.CELL_TYPE_STRING: // 字符串
cellValue = String.valueOf(cell.getStringCellValue());
break;
case Cell.CELL_TYPE_BOOLEAN: // Boolean
cellValue = String.valueOf(cell.getBooleanCellValue());
break;
case Cell.CELL_TYPE_FORMULA: // 公式
cellValue = String.valueOf(cell.getCellFormula());
break;
case Cell.CELL_TYPE_BLANK: // 空值
cellValue = null;
break;
case Cell.CELL_TYPE_ERROR: // 故障
cellValue = "非法字符";
break;
default:
cellValue = "未知类型";
break;
}
return cellValue;
}
}
