public class CommonConstant {
public static String SCIENTIFIC_NOTATION = "[+-]*\\d+\\.?\\d*[Ee]*[+-]*\\d+";
public static String NUMBER = "^[-+]?(([0-9]+)([.]([0-9]+))?|([.]([0-9]+))?)$";
}
工具类
package com.example.rbacdemo.util;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.math.BigDecimal;
import java.text.DateFormat;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.regex.Pattern;
import static com.example.rbacdemo.constant.CommonConstant.NUMBER;
import static com.example.rbacdemo.constant.CommonConstant.SCIENTIFIC_NOTATION;
/**
* Excel import、export
*/
public class ExcelUtil {
public static List<Map<String, String>> readExcelForStream(InputStream inputStream, Integer sheetIndex) throws Exception {
List<Map<String, String>> dataList = new ArrayList<>();
XSSFWorkbook wb = new XSSFWorkbook(inputStream);
Sheet sheet = wb.getSheetAt(sheetIndex);
int maxRownum = sheet.getPhysicalNumberOfRows();
Row firstRow = sheet.getRow(0);
int maxColnum = firstRow.getPhysicalNumberOfCells();
String[] columns = new String[maxColnum];
for (int i = 0; i < maxRownum; i++) {
Map<String, String> map = new HashMap<>(100);
map = new LinkedHashMap<>();
firstRow = sheet.getRow(i);
if (firstRow != null) {
String cellData;
for (int j = 0; j < maxColnum; j++) {
cellData = getCellValue(firstRow.getCell(j));
if (StringUtils.isNotBlank(cellData)) {
//考虑科学计数法问题
Pattern pattern = Pattern.compile(SCIENTIFIC_NOTATION);
boolean isNumber = pattern.matcher(cellData).matches();
if (isNumber) {
BigDecimal cellValue_ = new BigDecimal(cellData);
cellData = cellValue_.toPlainString();
if (cellData.length() > 11) {
cellData = cellValue_.toString();
}
}
Pattern patternNum = Pattern.compile(NUMBER);
boolean isNum = patternNum.matcher(cellData).matches();
//非数字去除空格
if (!isNum) {
cellData = cellData.trim();
}
} else {
if (cellData != null) {
cellData = null;
}
}
if (i == 0) {
columns[j] = cellData;
map.put(columns[j], cellData);
} else {
map.put(columns[j], cellData);
}
}
} else {
continue;
}
dataList.add(map);
}
return dataList;
}
public static List<Map<String, String>> readExcel(String filePath, Integer sheetIndex) {
List<Map<String, String>> dataList = new ArrayList<>();
Workbook wb = ExcelUtil.createWorkBook(filePath);
if (wb != null) {
Sheet sheet = wb.getSheetAt(sheetIndex);
int maxRownum = sheet.getPhysicalNumberOfRows();
Row firstRow = sheet.getRow(0);
int maxColnum = firstRow.getPhysicalNumberOfCells();
for (int i = 0; i < maxRownum; i++) {
Map<String, String> map = new HashMap<>(100);
if (i > 0) {
map = new LinkedHashMap<>();
firstRow = sheet.getRow(i);
}
if (firstRow != null) {
String cellData = null;
for (int j = 0; j < maxColnum; j++) {
cellData = getCellValue(firstRow.getCell(j));
if (i == 0) {
//columns[j] = cellData;
} else {
map.put(String.valueOf(j), cellData);
}
}
} else {
break;
}
if (i > 0) {
dataList.add(map);
}
}
}
return dataList;
}
private static Workbook createWorkBook(String filePath) {
Workbook wb = null;
if (filePath == null) {
return null;
}
String extString = filePath.substring(filePath.lastIndexOf("."));
InputStream is = null;
try {
is = new FileInputStream(filePath);
if (".xls".equals(extString)) {
return new HSSFWorkbook(is);
} else if (".xlsx".equals(extString)) {
return new XSSFWorkbook(is);
} else {
return null;
}
} catch (IOException e) {
e.printStackTrace();
} finally {
try {
if (is != null) {
is.close();
}
} catch (IOException e) {
e.printStackTrace();
}
}
return null;
}
public static String getCellValue(Cell cell) {
String cellValue = "";
if (null != cell) {
//以下是判断数据类型
switch (cell.getCellType()) {
case _NONE:
break;
case NUMERIC:
if (DateUtil.isCellDateFormatted(cell)) { //判断是否为日期类型
Date date = cell.getDateCellValue();
DateFormat formater = new SimpleDateFormat("yyyy-MM-dd");
cellValue = formater.format(date);
} else {
DecimalFormat df = new DecimalFormat("####.####");
cellValue = df.format(cell.getNumericCellValue());
}
break;
case STRING:
cellValue = cell.getStringCellValue();
break;
case BOOLEAN:
cellValue = cell.getBooleanCellValue() + "";
break;
case FORMULA:
break;
case BLANK: //空值
cellValue = "";
case ERROR:
cellValue = "非法字符";
break;
}
}
return cellValue;
}
private static Object washWord(Object obj) {
String words = "#N/A;NA";
if (obj != null) {
String str = obj.toString();
if (words.contains(str)) {
return "0";
} else {
if (str.startsWith("$") || str.endsWith("$")) {
str = str.replace("$", "").trim();
}
}
return str;
}
return "";
}
/**
* 用户信息导出类
*
* @param response 响应
* @param fileName 文件名
* @param columnList 每列的标题名
* @param dataList 导出的数据
*/
public static void export(HttpServletResponse response, String fileName, List<String> columnList, List<List<Object>> dataList) {
//声明输出流
OutputStream os = null;
//设置响应头
setResponseHeader(response, fileName);
try {
//获取输出流
os = response.getOutputStream();
//内存中保留1000条数据,以免内存溢出,其余写入硬盘
try (SXSSFWorkbook wb = new SXSSFWorkbook(1000)) {
//获取该工作区的第一个sheet
Sheet sheet1 = wb.createSheet("sheet1");
int excelRow = 0;
//创建标题行
Row titleRow = sheet1.createRow(excelRow++);
for (int i = 0; i < columnList.size(); i++) {
//创建该行下的每一列,并写入标题数据
Cell cell = titleRow.createCell(i);
cell.setCellValue(columnList.get(i));
}
//设置内容行
if (dataList != null && dataList.size() > 0) {
//外层for循环创建行
for (int i = 0; i < dataList.size(); i++) {
Row dataRow = sheet1.createRow(excelRow++);
//内层for循环创建每行对应的列,并赋值
for (int j = 0; j < dataList.get(0).size(); j++) {
Cell cell = dataRow.createCell(j);
Object v = dataList.get(i).get(j);
if (v instanceof String) {
cell.setCellValue(v.toString());
} else if (v.toString().matches(NUMBER)) {
cell.setCellValue(Double.parseDouble(v.toString()));
}
}
}
}
//将整理好的excel数据写入流中
wb.write(os);
}
} catch (IOException e) {
e.printStackTrace();
} finally {
try {
// 关闭输出流
if (os != null) {
os.close();
}
} catch (IOException e) {
e.printStackTrace();
}
}
}
/*
设置浏览器下载响应头
*/
private static void setResponseHeader(HttpServletResponse response, String fileName) {
try {
try {
fileName = new String(fileName.getBytes(), "ISO8859-1");
} catch (UnsupportedEncodingException e) {
e.printStackTrace();
}
response.setContentType("application/octet-stream;charset=UTF-8");
response.setHeader("Content-Disposition", "attachment;filename=" + fileName);
response.addHeader("Pargam", "no-cache");
response.addHeader("Cache-Control", "no-cache");
} catch (Exception ex) {
ex.printStackTrace();
}
}
}