以下程式碼都是自身寫出來實際驗證過的.跟類似百度知道, CSDN 那種渣渣文是不同的.
匯入:
所需 jar包
<!--apache 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>複製程式碼import java.io.IOException;
import java.io.InputStream;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.util.StringUtil;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.*;
public class ExcelReader {
private POIFSFileSystem fs;
private HSSFWorkbook wb;
private HSSFSheet sheet;
private HSSFRow row;
private XSSFWorkbook xFwb;
private XSSFSheet xFSheet;
private XSSFRow xFRow;
private int sheetSize = 0;
public void setSheetSize(int sheetSize) {
this.sheetSize = sheetSize;
}
/**
* 自我關閉輸入流
*
* @param excelPath
* @param inputStream
* WARNING:inputStream can not be repeatable!!!
* @return
* @throws IOException
*/
public LinkedList<String> readExcelTitleSafeLy(String excelPath, InputStream inputStream) throws IOException {
LinkedList<String> title = new LinkedList<>();
try {
title = readExcelTitle(excelPath, inputStream);
} finally {
IOUtils.closeQuietly(inputStream);
}
return title;
}
/**
* 自我關閉輸入流
*
* @param excelPath
* @param inputStream
* WARNING:inputStream can not be repeatable!!!
* @return
* @throws IOException
*/
public Map<Integer, LinkedList<String>> readExcelContentSafeLy(String excelPath, InputStream inputStream) throws IOException {
Map<Integer, LinkedList<String>> content = new LinkedHashMap<>();
try {
content = readExcelContent(excelPath, inputStream);
} finally {
IOUtils.closeQuietly(inputStream);
}
return content;
}
/**
* 讀取 xls Excel表格表頭的內容
*
* @param excelPath
* @param inputStream
* WARNING:inputStream can not be repeatable!!! AND this method can
* not be close stream;
* @return String 表頭內容的陣列
*/
public LinkedList<String> readExcelTitle(String excelPath, InputStream inputStream) throws IOException {
boolean isXLS = setPublicAndJudge(excelPath, inputStream);
LinkedList<String> title = new LinkedList<>();
// 標題總列數
int colNum;
String cellFormatValue;
if (isXLS) {
colNum = row.getPhysicalNumberOfCells();
for (int i = 0; i < colNum; i++) {
cellFormatValue = getCellFormatValue(row.getCell(i));
// 如果是空表頭就直接 空+ index
if (StringUtils.isEmpty(cellFormatValue)) {
title.add("空" + i);
} else {
title.add(cellFormatValue);
}
}
} else {
// 標題總列數
colNum = xFRow.getPhysicalNumberOfCells();
for (int i = 0; i < colNum; i++) {
cellFormatValue = getCellFormatValue(xFRow.getCell((short) i));
// 如果是空表頭就直接 空+ index
if (StringUtils.isEmpty(cellFormatValue)) {
title.add("空" + i);
} else {
title.add(cellFormatValue);
}
}
}
return title;
}
private boolean setPublicAndJudge(String excelPath, InputStream inputStream) throws IOException {
boolean isXLS = false;
if (StringUtils.endsWithIgnoreCase(excelPath, "xls")) {
fs = new POIFSFileSystem(inputStream);
wb = new HSSFWorkbook(fs);
sheet = wb.getSheetAt(sheetSize);
// 得到表頭
row = sheet.getRow(0);
isXLS = true;
} else if (StringUtils.endsWithIgnoreCase(excelPath, "xlsx")) {
xFwb = new XSSFWorkbook(inputStream);
xFSheet = xFwb.getSheetAt(0);
// 得到表頭
xFRow = xFSheet.getRow(0);
} else {
throw new RuntimeException("未能識別的字尾!");
}
return isXLS;
}
/**
* 讀取 Excel 的實際資料,從第二行開始
*
* @param excelPath
* @param inputStream
* WARNING:inputStream can not be repeatable!!!
* @return
* @throws IOException
*/
@SuppressWarnings("deprecation")
public Map<Integer, LinkedList<String>> readExcelContent(String excelPath, InputStream inputStream)
throws IOException {
boolean isXLS = setPublicAndJudge(excelPath, inputStream);
// 得到總行數
int rowNum;
// 總列數
int colNum;
// data
LinkedList<String> rowCells;
// 第幾行, data
Map<Integer, LinkedList<String>> content = new LinkedHashMap<>();
if (isXLS) {
// 得到總行數
rowNum = sheet.getLastRowNum();
// 得到總列數
colNum = row.getPhysicalNumberOfCells();
// 正文內容應該從第二行開始,第一行為表頭的標題
for (int i = 1; i <= rowNum; i++) {
rowCells = new LinkedList<>();
row = sheet.getRow(i);
int j = 0;
while (j < colNum) {
if (Objects.nonNull(xFRow)
&& StringUtils.isNotBlank(getCellFormatValue(row.getCell((short) j)).trim())) {
rowCells.add(getCellFormatValue(row.getCell((short) j)).trim());
} else {
rowCells.add(StringUtils.EMPTY);
}
j++;
}
// 如果第一列是空的就不加入
if (StringUtils.isEmpty(rowCells.getFirst())) {
continue;
}
content.put(i - 1, rowCells);
}
} else {
// 得到總行數
rowNum = xFSheet.getLastRowNum();
// 總列數
colNum = xFRow.getPhysicalNumberOfCells();
// 正文內容應該從第二行開始,第一行為表頭的標題
for (int i = 1; i <= rowNum; i++) {
rowCells = new LinkedList<>();
xFRow = xFSheet.getRow(i);
int j = 0;
while (j < colNum) {
if (Objects.nonNull(xFRow)
&& StringUtils.isNotBlank((getCellFormatValue(xFRow.getCell((short) j)).trim()))) {
rowCells.add(getCellFormatValue(xFRow.getCell((short) j)).trim());
} else {
rowCells.add(StringUtils.EMPTY);
}
j++;
}
// 如果第一列是空的就不加入
if (StringUtils.isEmpty(rowCells.getFirst())) {
continue;
}
content.put(i - 1, rowCells);
}
}
return content;
}
/**
* 根據HSSFCell型別設定資料 處理2003的xls Excel
*
* @param cell
* @return
*/
private String getCellFormatValue(HSSFCell cell) {
if (Objects.isNull(cell)) {
return StringUtils.EMPTY;
}
String cellValue;
// 判斷當前Cell的Type
switch (cell.getCellType()) {
// 如果當前Cell的Type為NUMERIC
case HSSFCell.CELL_TYPE_NUMERIC:
cellValue = handleNUMERICCellValue(cell);
break;
case HSSFCell.CELL_TYPE_FORMULA: {
cellValue = handleDate(cell);
break;
}
// 如果當前Cell的Type為STRIN
case HSSFCell.CELL_TYPE_STRING:
// 取得當前的Cell字串
cellValue = cell.getRichStringCellValue().getString();
break;
// 預設的Cell值
default:
cellValue = StringUtils.EMPTY;
}
return cellValue;
}
private String handleNUMERICCellValue(Cell cell) {
String cellValue;
// 當前值
Object inputVal;
if (HSSFDateUtil.isCellDateFormatted(cell)) {
SimpleDateFormat sdf;
if (cell.getCellStyle().getDataFormat() == HSSFDataFormat.getBuiltinFormat("h:mm")) {
sdf = new SimpleDateFormat("HH:mm");
} else {// 日期
sdf = new SimpleDateFormat("yyyy-MM-dd");
}
Date date = cell.getDateCellValue();
cellValue = sdf.format(date);
} else {
double doubleVal = cell.getNumericCellValue();
long longVal = Math.round(cell.getNumericCellValue());
if (Double.parseDouble(longVal + ".0") == doubleVal) {
inputVal = longVal;
} else {
inputVal = doubleVal;
}
cellValue = String.valueOf(inputVal);
}
return cellValue;
}
private String handleDate(Cell cell) {
String cellValue;
// 判斷當前的cell是否為Date
if (HSSFDateUtil.isCellDateFormatted(cell)) {
SimpleDateFormat sdf;
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();
cellValue = sdf.format(date);
}
// 如果是純數字
else {
DecimalFormat df = new DecimalFormat("#");
// 取得當前Cell的數值
cellValue = String.valueOf(df.format(cell.getNumericCellValue()));
}
return cellValue;
}
/**
* 根據XSSFCell型別設定資料
*
* @param cell
* @return
*/
private String getCellFormatValue(XSSFCell cell) {
if (Objects.isNull(cell)) {
return StringUtils.EMPTY;
}
String cellValue;
// 判斷當前Cell的Type
switch (cell.getCellType()) {
// 如果當前Cell的Type為NUMERIC
case XSSFCell.CELL_TYPE_NUMERIC:
cellValue = handleNUMERICCellValue(cell);
break;
// 公式型
case XSSFCell.CELL_TYPE_FORMULA: {
cellValue = handleDate(cell);
break;
}
// 如果當前Cell的Type為STRIN
case HSSFCell.CELL_TYPE_STRING:
// 取得當前的Cell字串
cellValue = cell.getRichStringCellValue().getString();
break;
// 預設的Cell值
default:
cellValue = StringUtils.EMPTY; } return cellValue; }}
複製程式碼匯出:
動態匯出(支援自定義表頭,內容)
所需 jar包:
core:
<dependency>
<groupId>org.apache.commons</groupId>
<artifactId>commons-csv</artifactId>
<version>1.5</version>
</dependency>
other:
<dependency>
<groupId>javax.servlet</groupId>
<artifactId>javax.servlet-api</artifactId>
<version>3.1.0</version>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<version>1.16.20</version>
<scope>provided</scope>
</dependency>
複製程式碼import com.google.common.base.Charsets;
import lombok.Data;
import org.apache.commons.collections4.CollectionUtils;
import org.apache.commons.csv.CSVFormat;
import org.apache.commons.csv.CSVPrinter;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.util.IOUtils;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.OutputStream;
import java.io.OutputStreamWriter;
import java.io.UnsupportedEncodingException;
import java.net.URLEncoder;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.LinkedList;
import java.util.List;
/**
* support Dynamic Head content
* 此種匯出方式不建議大量資料使用,因為是先把資料放置記憶體,最後集中處理的!!!
* @author Forest10
* @date 2018/3/28 10:57
*/
@Data
public class DynamicExcelWriter {
/***
* 匯出的檔名字
*/
private String fileName;
/****should be LinkedList****/
private LinkedList<String> excelHeadList;
/****should be LinkedList****/
private LinkedList<LinkedList<String>> excelContentList;
public DynamicExcelWriter(LinkedList<String> excelHeadList, LinkedList<LinkedList<String>> excelContentList) {
this.excelHeadList = excelHeadList;
this.excelContentList = excelContentList;
}
/***匯出表頭**/
private void exportHead(CSVPrinter csvPrinter) throws IOException {
//Assert.check(CollectionUtils.isNotEmpty(excelHeadList), "excel表頭不能為空!");
csvPrinter.printRecord(CollectionUtils.isEmpty(excelHeadList) ? new LinkedList<>() : excelHeadList);
csvPrinter.flush();
}
/***匯出內容**/
private void exportContent(CSVPrinter csvPrinter) throws IOException {
//Assert.check(CollectionUtils.isNotEmpty(excelHeadList), "excel內容不能為空!");
if (CollectionUtils.isEmpty(excelContentList)) {
excelContentList = new LinkedList<>();
}
for (List<String> list : excelContentList) {
csvPrinter.printRecord(CollectionUtils.isEmpty(list) ? new LinkedList<>() : list);
//flush 每行
csvPrinter.flush();
}
//flush所有行
csvPrinter.flush();
}
/**
* 匯出Excel
*
* @param response
* @throws IOException
*/
public void doExport(HttpServletResponse response) throws IOException {
OutputStream os = null;
CSVPrinter csvPrinter = null;
try {
os = response.getOutputStream();
csvPrinter = new CSVPrinter(new OutputStreamWriter(os, Charsets.UTF_8), CSVFormat.EXCEL);
//設定下載頭(csv)
responseSetProperties(response);
//設定 BOM頭
os.write(new byte[]{(byte) 0xEF, (byte) 0xBB, (byte) 0xBF});
//匯出 excel表頭
exportHead(csvPrinter);
//匯出實際資料
exportContent(csvPrinter);
} finally {
IOUtils.closeQuietly(os);
IOUtils.closeQuietly(csvPrinter);
}
}
private void responseSetProperties(HttpServletResponse response) throws UnsupportedEncodingException {
// 設定檔案字尾
SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMddHHmmss");
String fn = StringUtils.isBlank(fileName) ? StringUtils.EMPTY : fileName + sdf.format(new Date()) + ".csv";
// 讀取字元編碼
String utf = "UTF-8";
// 設定響應
response.setContentType("text/csv;charset=utf-8");
response.setCharacterEncoding(utf);
response.setHeader("Pragma", "public");
response.setHeader("Cache-Control", "max-age=30");
response.setHeader("Content-Disposition", "attachment; filename=" + URLEncoder.encode(fn, utf));
}
}複製程式碼