package com.feiu2.client.util.excel;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
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.streaming.SXSSFCell;
import org.apache.poi.xssf.streaming.SXSSFRow;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import tk.mybatis.mapper.util.StringUtil;
import java.io.*;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.*;
/**
* @author LHZ<br>
* @createDate 2019/09/09 16:47 <br>
*/
public class ExcelUtil {
public static List<Map<String, Object>> readExcel(File file) throws IOException {
return readExcel(file, null);
}
public static List<Map<String, Object>> readExcel(File file, ExcelImpConfig config) throws IOException {
InputStream is = new FileInputStream(file);
Sheet sheet = getSheetFromInputStream(is, file.getName(), config);
if (sheet == null) {
return null;
}
return readSheet(sheet, config);
}
public static List<Map<String, Object>> readExcel(InputStream is, String fileName, ExcelImpConfig config) throws IOException {
Sheet sheet = getSheetFromInputStream(is, fileName, config);
if (sheet == null) {
return null;
}
return readSheet(sheet, config);
}
private static Sheet getSheetFromInputStream(InputStream is, String fileName, ExcelImpConfig config) throws IOException {
Workbook workbook = null;
String postfix = getPostfix(fileName);
if ("xls".equals(postfix)) {
workbook = new HSSFWorkbook(is);
} else if ("xlsx".equals(postfix)) {
workbook = new XSSFWorkbook(is);
}
if (workbook != null) {
if (config != null && !StringUtil.isEmpty(config.getSheetName())) {
return workbook.getSheet(config.getSheetName());
}
return workbook.getSheetAt(0);
}
return null;
}
private static List<Map<String, Object>> readSheet(Sheet sheet, ExcelImpConfig config) {
if (sheet == null) {
return null;
}
if (config == null) {
config = new ExcelImpConfig();
}
int dataRowStart = config.getDataRowStart();
int dataRowNum = config.getDataRowNum();
List<String> names = new ArrayList<>();
if (dataRowStart <= 0) {//自动
dataRowStart = 0;
}
if (names == null || names.size() == 0) {
if (config == null || config.getRead() == ExcelImpConfig.READ_METHOD_COL) {
names = readTitle(sheet.getRow(dataRowStart - 1));
}
}
int rowEnd = 0;
if (dataRowNum == 0) {
rowEnd = sheet.getLastRowNum() + 1;
} else {
if ((config != null && config.getRead() == ExcelImpConfig.READ_METHOD_ROW) || sheet.getLastRowNum() < (dataRowStart + dataRowNum)) {
rowEnd = dataRowStart + dataRowNum;
} else {
rowEnd = sheet.getLastRowNum() + 1;
}
}
Map<String, Object> nameMap = config.getNames();
List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
for (int rowNum = dataRowStart; rowNum < rowEnd; rowNum++) {
Row row = sheet.getRow(rowNum);
if (row != null) {
Map<String, Object> rowData = new LinkedHashMap<String, Object>();
if (config == null || config.getRead() == ExcelImpConfig.READ_METHOD_COL) {
for (int i = 0; i < names.size(); i++) {
Cell cell = row.getCell(i);
if (cell != null) {
Object value = getValue(cell);
if (value != null && !"".equals(value)) {
if (nameMap != null && nameMap.size() > 0) {
setValue(rowData, nameMap.get(names.get(i).trim()).toString(), value);
} else {
setValue(rowData, names.get(i), value);
}
} else {
rowData.put(names.get(i), "");
}
}else{
rowData.put(names.get(i), "");
}
}
} else {
for (int i = 0; i < row.getLastCellNum(); i = i + 2) {
Cell ncell = row.getCell(i);
Cell vcell = row.getCell(i + 1);
if (ncell != null && vcell != null) {
String name = getValue(ncell).toString();
Object value = getValue(vcell);
if (value != null && !"".equals(value)) {
if (nameMap != null && nameMap.size() > 0) {
setValue(rowData, nameMap.get(name).toString().trim(), value);
} else {
setValue(rowData, name, value);
}
} else {
rowData.put(names.get(i), "");
}
}
}
}
if (rowData.size() > 0) {
list.add(rowData);
}
}
}
return list;
}
private static List<String> readTitle(Row row) {
List<String> names = new ArrayList<>();
if (row != null) {
for (int i = 0; i < row.getLastCellNum(); i++) {
if (row.getCell(i) != null) {
names.add("" + ExcelUtil.getValue(row.getCell(i)));
}
}
}
return names;
}
private static void setValue(Map<String, Object> data, String name, Object value) {
String[] split = name.split("\\.");
String key = split[split.length - 1];
if (split.length > 1) {
for (int i = 0; i < split.length - 1; i++) {
Object object = data.get(split[i]);
if (object == null) {
object = new HashMap<>();
data.put(split[i], object);
}
data = (Map<String, Object>) object;
}
}
data.put(key, value);
}
public static Object getValue(Cell cell) {
if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) {
return String.valueOf(cell.getBooleanCellValue());
} else if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
if (HSSFDateUtil.isCellDateFormatted(cell)) {// 处理日期格式、时间格式
SimpleDateFormat sdf = null;
if (cell.getCellStyle().getDataFormat() == HSSFDataFormat
.getBuiltinFormat("h:mm")) {
sdf = new SimpleDateFormat("HH:mm");
} else {// 日期
sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
}
Date date = cell.getDateCellValue();
return sdf.format(date);
}
DecimalFormat df = new DecimalFormat("0");
String whatYourWant = df.format(cell.getNumericCellValue());
return whatYourWant;
} else {
return String.valueOf(cell.getStringCellValue());
}
}
public static String getPostfix(String path) {
if (path == null || "".equals(path.trim())) {
return "";
}
if (path.contains(".")) {
return path.substring(path.lastIndexOf(".") + 1, path.length());
}
return "";
}
/**
* 根据模板直接写入数据
*
* @param os
* @param dataList
* @param config
*/
public static void writeExcel(OutputStream os, List<Map<String, Object>> dataList, ExcelImpConfig config) throws IOException {
SXSSFWorkbook wb = new SXSSFWorkbook();//建立新HSSFWorkbook对象
SXSSFSheet sheet = wb.getSheet(config.getSheetName());
if (sheet == null) {
sheet = wb.createSheet(config.getSheetName());
}
int rowIndex = config.getDataRowStart();
Map<String, Object> titles = config.getNames();
//创建title
SXSSFRow rowTitle = sheet.createRow(rowIndex - 1);
int index = 0;
for (String t : titles.keySet()) {
SXSSFCell cell = rowTitle.createCell(index);
cell.setCellValue(t);
index++;
}
if (dataList != null && dataList.size() > 0) {
for (Map<String, Object> dataMap : dataList) {
SXSSFRow row = sheet.createRow(rowIndex);
Set<String> keySet = titles.keySet();
Iterator it = keySet.iterator();
for (int i = 0; i < titles.size(); i++) {
SXSSFCell cell = row.createCell(i);
String title = it.next().toString();
Object value = dataMap.get(title);
if (value instanceof Number) {
cell.setCellValue(((Number) value).doubleValue());
} else if (value instanceof String) {
cell.setCellValue((String) value);
} else if (value instanceof Boolean) {
cell.setCellValue((Boolean) value);
}
}
rowIndex++;
}
}
wb.write(os);
}
}
package com.feiu2.client.util.excel;
import java.util.Map;
/**
* @author LHZ<br>
* @createDate 2019/09/09 16:51 <br>
*/
public class ExcelImpConfig {
public static final int READ_METHOD_ROW = 0;
public static final int READ_METHOD_COL = 1;
/**
* 从第几行开始读取，1 就是第一行，对应的sheet 下标0
*/
private int dataRowStart = 1;
private int dataRowNum;
private String sheetName;
private int read = 1;
private Map<String, Object> names;
private String model;
private String isStatic;
public int getDataRowStart() {
return dataRowStart;
}
public void setDataRowStart(int dataRowStart) {
this.dataRowStart = dataRowStart;
}
public int getDataRowNum() {
return dataRowNum;
}
public void setDataRowNum(int dataRowNum) {
this.dataRowNum = dataRowNum;
}
public Map<String, Object> getNames() {
return names;
}
public void setNames(Map<String, Object> names) {
this.names = names;
}
public String getSheetName() {
return sheetName;
}
public void setSheetName(String sheetName) {
this.sheetName = sheetName;
}
public int getRead() {
return read;
}
public void setRead(int read) {
this.read = read;
}
public String getModel() {
return model;
}
public void setModel(String model) {
this.model = model;
}
public String getIsStatic() {
return isStatic;
}
public void setIsStatic(String isStatic) {
this.isStatic = isStatic;
}
}
<!--poi-->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.17</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.17</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.17</version>
</dependency>
网友评论