做个记录 以防 用的时候一顿找. 借鉴各家所长 改了改.
里面的数据格式需要自己修改
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>4.1.0</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>4.1.0</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml-schemas</artifactId>
<version>4.1.0</version>
</dependency>
1.读取 xls、xlsx、csv 文件,可以 是 文件、绝对路径、流的方式直接读取
/**
* @ClassName ExcelReader
* @Description
* @Author lipeng
* @Date 2019/11/193:04 下午
* @Version 1.0
*/
import com.kt.ranger.platform.utils.ConstantUtils;
import org.apache.commons.collections.CollectionUtils;
import org.apache.commons.lang.ArrayUtils;
import org.apache.commons.lang.time.DateFormatUtils;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.thrift.TException;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import java.io.*;
import java.text.NumberFormat;
import java.util.*;
/**
* @ClassName 读取 exlce｜csv
* @Description
* @Author lipeng
* @Date 2019/11/1912:24 下午
* @Version 1.0
*/
public class ReaderUtils {
private static final Logger LOGGER = LoggerFactory.getLogger(ReaderUtils.class);
private Workbook workBook;
private Map<Sheet, String[]> sheetHeaders;
// CSV文件编码
public String ENCODE = "UTF-8";
public String FILE_FORMAT = null;
private FileInputStream fis = null;
private InputStreamReader isw = null;
private BufferedReader br = null;
public ReaderUtils(String filePath, String format) {
this(new File(filePath),format);
}
public ReaderUtils(File file, String format) {
// 解决版本问题，HSSFWorkbook是97-03版本的xls版本，XSSFWorkbook是07版本的xlsx
try {
if(!StringUtil.isNullOrEmpty(format)){
ENCODE = format;
}
if(!file.getName().contains(".")){
return;
}
FILE_FORMAT = file.getName().substring(file.getName().lastIndexOf("."), file.getName().length());
if (FILE_FORMAT.equals(ConstantUtils.CSV_D)) {
fis = new FileInputStream(file);
isw = new InputStreamReader(fis, ENCODE);
br = new BufferedReader(isw);
} else if (FILE_FORMAT.equals(ConstantUtils.XLSX_D)) {
workBook = new XSSFWorkbook(new FileInputStream(file));
} else if (FILE_FORMAT.equals(ConstantUtils.XLS_D)) {
workBook = new HSSFWorkbook(new FileInputStream(file));
} else {
return;
}
} catch (Exception e) {
LOGGER.error("Excel格式不正确", e);
throw new RuntimeException(e);
}
}
public ReaderUtils(InputStream inputStream, String format) {
// 解决版本问题，HSSFWorkbook是97-03版本的xls版本，XSSFWorkbook是07版本的xlsx
try {
if (format.equals(".csv")) {
isw = new InputStreamReader(inputStream, ENCODE);
br = new BufferedReader(isw);
} else if (format.equals(".xlsx")) {
workBook = new XSSFWorkbook(inputStream);
} else if (format.equals(".xls")) {
workBook = new HSSFWorkbook(inputStream);
}
} catch (Exception e) {
LOGGER.error("Excel格式不正确", e);
throw new RuntimeException(e);
}
}
/**
* 初始化sheet和表头信息，默认以每个sheet的第一行作为表头
*/
private void initDefaultSheetHeaders() {
sheetHeaders = new LinkedHashMap<>();
if (workBook == null) {
return;
}
int numberOfSheets = workBook.getNumberOfSheets();
for (int i = 0; i < numberOfSheets; i++) {
Sheet sheet = workBook.getSheetAt(i);
String sheetName = workBook.getSheetName(i);
LOGGER.debug("sheetName[{}]: {}", i, sheetName);
// 默认以第一行作为表头
Row row = sheet.getRow(0);
if (row == null) {
sheetHeaders.put(sheet, new String[]{});
continue;
}
String[] headers = new String[]{};
short lastCellNum = row.getLastCellNum();
for (int j = 0; j < lastCellNum; j++) {
String cellValue = getCellStringValue(row.getCell(j));
headers = (String[]) ArrayUtils.add(headers, cellValue);
}
sheetHeaders.put(sheet, headers);
}
}
// 查询全部 从第几条开始
public List<Map<String, Object>> readAllExcel() {
return readAllExcel(0);
}
// 查询全部 从第几条开始
public List<Map<String, Object>> readAllExcel(int start) {
List<Map<String, Object>> result = new LinkedList<>();
int numberOfSheets = workBook.getNumberOfSheets();
for (int i = 0; i < numberOfSheets; i++) {
List<Map<String, Object>> datas = readAllExcel(i, start);
if (CollectionUtils.isNotEmpty(datas)) {
result.addAll(datas);
}
}
return result;
}
public List<Map<String, Object>> readAllExcel(int sheetIndex, int start) {
if (sheetHeaders == null) {
initDefaultSheetHeaders();
}
Sheet sheet = workBook.getSheetAt(sheetIndex);
return readSheetDatas(sheetIndex, sheetHeaders.get(sheet), start);
}
public List<Map<String, Object>> readSheetDatas(int sheetIndex, String[] headers) {
return readSheetDatas(sheetIndex, headers, 0);
}
/**
* 读取指定sheet数据
*
* @param sheetIndex sheet的下标
* @param headers 表头
* @param startRow 起始行数
* @return
*/
public List<Map<String, Object>> readSheetDatas(int sheetIndex, String[] headers, int startRow) {
List<Map<String, Object>> result = new LinkedList<>();
if (ArrayUtils.isEmpty(headers)) {
return result;
}
Sheet sheet = workBook.getSheetAt(sheetIndex);
if (sheet == null) {
return result;
}
int lastRowNum = sheet.getLastRowNum();
for (int i = 0; i <= lastRowNum; i++) {
Row row = sheet.getRow(i);
if (row == null) {
continue;
}
Map<String, Object> rowMap = new LinkedHashMap<>();
result.add(rowMap);
for (int j = 0, length_1 = headers.length; j < length_1; j++) {
String cellValue = getCellStringValue(row.getCell(j));
String header = headers[j];
rowMap.put(header, cellValue);
}
}
return result;
}
/**
* POI3.15之后的读取方法(建议用这个)
*
* @param cell
* @return
*/
private String getCellStringValue(Cell cell) {
if (cell == null) {
return "";
}
String cellValue = null;
if (cell.getCellTypeEnum() == CellType.NUMERIC) {
if (HSSFDateUtil.isCellDateFormatted(cell)) {
cellValue = DateFormatUtils.format(cell.getDateCellValue(), "yyyy-MM-dd");
} else {
NumberFormat nf = NumberFormat.getInstance();
cellValue = String.valueOf(nf.format(cell.getNumericCellValue())).replace(",", "");
}
} else if (cell.getCellTypeEnum() == CellType.STRING) {
cellValue = cell.getStringCellValue();
} else if (cell.getCellTypeEnum() == CellType.BOOLEAN) {
cellValue = String.valueOf(cell.getBooleanCellValue());
} else if (cell.getCellTypeEnum() == CellType.ERROR) {
cellValue = "错误类型";
} else {
cellValue = "";
}
return cellValue;
}
// ==========以下是公开方法=============================
/**
* 从CSV文件流中读取一个CSV行。
*
* @throws Exception
*/
public List<Map<String, Object>> readAllCSV() throws Exception {
return readAllCSV(0);
}
public List<Map<String, Object>> readAllCSV(int start) throws Exception {
List<Map<String, Object>> list = new ArrayList<>();
// 获取标题
List<String> title = getTitle(br.readLine(), list);
String lineData = null;
try {
while ((lineData = br.readLine()) != null) {
Map<String, Object> map = getMap(lineData, title);
list.add(map);
}
} catch (Exception ex) {
LOGGER.error(ex.getMessage());
}
return list;
}
//获取头部
private List<String> getTitle(String string, List<Map<String, Object>> title) throws TException {
List<String> arrayList = fromCSVLinetoArray(string);
Map map = getMap(string, arrayList);
title.add(map);
return arrayList;
}
private Map<String, Object> getMap(String string, List<String> listTitle) throws TException {
Map<String, Object> map = new HashMap<>();
try {
ArrayList arrayList = fromCSVLinetoArray(string);
for (int i = 0; i < arrayList.size(); i++) {
map.put(listTitle.get(i), arrayList.get(i));
}
} catch (Exception ex) {
LOGGER.error(ex.getMessage());
}
return map;
}
/**
* 把CSV文件的一行转换成字符串数组。指定数组长度，不够长度的部分设置为null。
*/
public static String[] fromCSVLine(String source, int size) {
ArrayList tmpArray = fromCSVLinetoArray(source);
if (size < tmpArray.size()) {
size = tmpArray.size();
}
String[] rtnArray = new String[size];
tmpArray.toArray(rtnArray);
return rtnArray;
}
/**
* 把CSV文件的一行转换成字符串数组。不指定数组长度。
*/
public static ArrayList fromCSVLinetoArray(String source) {
if (source == null || source.length() == 0) {
return new ArrayList();
}
int currentPosition = 0;
int maxPosition = source.length();
int nextComma = 0;
ArrayList rtnArray = new ArrayList();
while (currentPosition < maxPosition) {
nextComma = nextComma(source, currentPosition);
rtnArray.add(nextToken(source, currentPosition, nextComma));
currentPosition = nextComma + 1;
if (currentPosition == maxPosition) {
rtnArray.add("");
}
}
return rtnArray;
}
/**
* 把字符串类型的数组转换成一个CSV行。（输出CSV文件的时候用）
*/
public static String toCSVLine(String[] strArray) {
if (strArray == null) {
return "";
}
StringBuffer cvsLine = new StringBuffer();
for (int idx = 0; idx < strArray.length; idx++) {
String item = addQuote(strArray[idx]);
cvsLine.append(item);
if (strArray.length - 1 != idx) {
cvsLine.append(',');
}
}
return cvsLine.toString();
}
/**
* 字符串类型的List转换成一个CSV行。（输出CSV文件的时候用）
*/
public static String toCSVLine(ArrayList strArrList) {
if (strArrList == null) {
return "";
}
String[] strArray = new String[strArrList.size()];
for (int idx = 0; idx < strArrList.size(); idx++) {
strArray[idx] = (String) strArrList.get(idx);
}
return toCSVLine(strArray);
}
// 执行方法
public List<Map<String, Object>> actingLoad() throws Exception {
if(StringUtil.isNullOrEmpty(FILE_FORMAT)){
return null;
}
if (FILE_FORMAT.equals(ConstantUtils.CSV_D)) {
return readAllCSV();
} else if (FILE_FORMAT.equals(ConstantUtils.XLSX_D) || FILE_FORMAT.equals(ConstantUtils.XLS_D)) {
return readAllExcel(1);
} else {
return null;
}
}
// ==========以下是内部使用的方法=============================
/**
* 计算指定文字的个数。
*
* @param str 文字列
* @param c 文字
* @param start 开始位置
* @return 个数
*/
private int countChar(String str, char c, int start) {
int i = 0;
int index = str.indexOf(c, start);
return index == -1 ? i : countChar(str, c, index + 1) + 1;
}
/**
* 查询下一个逗号的位置。
*
* @param source 文字列
* @param st 检索开始位置
* @return 下一个逗号的位置。
*/
private static int nextComma(String source, int st) {
int maxPosition = source.length();
boolean inquote = false;
while (st < maxPosition) {
char ch = source.charAt(st);
if (!inquote && ch == ',') {
break;
} else if ('"' == ch) {
inquote = !inquote;
}
st++;
}
return st;
}
/**
* 取得下一个字符串
*/
private static String nextToken(String source, int st, int nextComma) {
StringBuffer strb = new StringBuffer();
int next = st;
while (next < nextComma) {
char ch = source.charAt(next++);
if (ch == '"') {
if ((st + 1 < next && next < nextComma) && (source.charAt(next) == '"')) {
strb.append(ch);
next++;
}
} else {
strb.append(ch);
}
}
return strb.toString();
}
/**
* 在字符串的外侧加双引号。如果该字符串的内部有双引号的话，把"转换成""。
*
* @param item 字符串
* @return 处理过的字符串
*/
private static String addQuote(String item) {
if (item == null || item.length() == 0) {
return "\"\"";
}
StringBuffer sb = new StringBuffer();
sb.append('"');
for (int idx = 0; idx < item.length(); idx++) {
char ch = item.charAt(idx);
if ('"' == ch) {
sb.append("\"\"");
} else {
sb.append(ch);
}
}
sb.append('"');
return sb.toString();
}
}
2.导出 xls、xlsx、csv 按照需求 自己改,这个 是本需求所需要
import com.kt.ranger.platform.entitylogic.inte.entity.AttrEntityLabel;
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.SXSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
/**
* @ClassName 导出 exlce｜csv
* @Description
* @Author lipeng
* @Date 2019/11/195:24 下午
* @Version 1.0
*/
public class ExportUtils {
private final static Logger logger = LoggerFactory.getLogger(ExportUtils.class);
/**
* CSV文件列分隔符
*/
private final String CSV_COLUMN_SEPARATOR = ",";
/**
* CSV文件列分隔符
*/
private final String CSV_RN = "\r\n";
// CSV文件编码
public String ENCODE = "UTF-8";
//表头
private List<Object> titles = null;
//表头arrt
private List<String> titleArrts = null;
private static ExportUtils instance = null;
private ExportUtils() {
}
public static ExportUtils getInstance() {
if (instance == null) {
synchronized (ExportUtils.class) {
if (instance == null) {
try {
instance = new ExportUtils();
} catch (Exception e) {
logger.error("", e);
}
}
}
}
return instance;
}
//导出 .csv 格式
public void exportCsv(HttpServletResponse response, HttpServletRequest request, List list, List<AttrEntityLabel> labels, String fontFormat) {
OutputStream outputStream = null;
//初始化表头数据
getTitle(list, labels);
// 导出文件路径
String downloadFilePath = request.getSession().getServletContext().getRealPath("");
// 导出文件名称
String fileName = "download";
// 导出CSV文件
File csvFile = createCSVFile(titles, list, downloadFilePath, fileName, fontFormat);
try {
// 取得文件名。
String filename = csvFile.getName();
// 以流的形式下载文件。
FileInputStream inputStream = new FileInputStream(csvFile);
// 设置response的Header
outputStream = response.getOutputStream();
String userAgent = request.getHeader("User-Agent");
// // 针对IE或者以IE为内核的浏览器：
if (userAgent.contains("MSIE") || userAgent.contains("Trident")) {
filename = java.net.URLEncoder.encode(filename, ENCODE);
} else {
// 非IE浏览器的处理：
filename = new String(filename.getBytes(ENCODE), "ISO-8859-1");
}
response.reset();
response.setContentType("application/octet-stream; charset="+ENCODE);
response.setHeader("Content-Encoding", ENCODE);
response.setHeader("Content-Disposition", "attachment;filename=" + filename);
int content = 0;
while ((content = inputStream.read()) != -1) {
outputStream.write(content);
}
inputStream.close();
outputStream.close();
outputStream.flush();
} catch (IOException ex) {
ex.printStackTrace();
}
}
//导出excel
public void exportExcel(HttpServletResponse response, HttpServletRequest request, List list, List<AttrEntityLabel> labels, String fileFormat) {
FileOutputStream fileOutputStream = null;
OutputStream outputStream = null;
Workbook workbook = null;
File exFile = null;
String str = null;
//初始化表头数据
getTitle(list, labels);
// 导出文件路径
String downloadFilePath = request.getSession().getServletContext().getRealPath("");
// 导出文件名称
String fileName = "download";
try {
if (fileFormat.equals("xls")) {
// 创建一个文件 .xls文件
workbook = new HSSFWorkbook();
str = ".xls";
} else if (fileFormat.equals("xlsx")) {
// 创建一个文件 .xlsx文件
workbook = new SXSSFWorkbook();
str = ".xlsx";
} else {
logger.error("文件类型错误!");
}
exFile = new File(downloadFilePath + File.separator + fileName + str);
File parent = exFile.getParentFile();
if (parent != null && !parent.exists()) {
parent.mkdirs();
}
// 创建一个工作表sheet
Sheet sheet = workbook.createSheet();
// 创建第一行
Row row = sheet.createRow(0);
// 创建一个单元格
creatHeadExcelLin(titles, row);
//设置单元格数据
creatExcelDate(list, sheet);
// 取得文件名。
String filename = exFile.getName();
String userAgent = request.getHeader("User-Agent");
if (userAgent.contains("MSIE") || userAgent.contains("Trident")) {
filename = java.net.URLEncoder.encode(filename, "UTF-8");
} else {
// 非IE浏览器的处理：
filename = new String(filename.getBytes("UTF-8"), "ISO-8859-1");
}
response.reset();
response.setHeader("Content-Encoding", "UTF-8");
if (str.equals(".xls")) {
// 创建一个文件 .xls文件
response.setContentType("application/vnd.ms-excel");
response.addHeader("Content-Disposition", "attachment;filename=" + filename);
} else if (str.equals(".xlsx")) {
// 创建一个文件 .xlsx文件
response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
response.addHeader("Content-Disposition", "attachment;filename=" + filename);
} else {
logger.error("文件类型错误!");
}
fileOutputStream = new FileOutputStream(filename);
workbook.write(fileOutputStream);
fileOutputStream.flush();
fileOutputStream.close();
workbook.close();
FileInputStream inputStream = new FileInputStream(filename);
outputStream = response.getOutputStream();
int content = 0;
while ((content = inputStream.read()) != -1) {
outputStream.write(content);
}
inputStream.close();
outputStream.close();
outputStream.flush();
response.flushBuffer();
} catch (Exception e) {
e.printStackTrace();
}
}
//创建excel表头
private void creatHeadExcelLin(List<Object> list, Row row) {
// 创建一个单元格
Cell cell = null;
// 创建表头
for (int i = 0; i < list.size(); i++) {
cell = row.createCell(i);
cell.setCellValue(String.valueOf(titles.get(i)));
}
}
//创建excel表数据
private void creatExcelDate(List list, Sheet sheet) {
for (int i = 0; i < list.size(); i++) {
Map maps = (Map) list.get(i);
// 创建第i行
Row nextRow = sheet.createRow(i + 1);
for (int j = 0; j < titles.size(); j++) {
// 参数代表第几列
Cell cell2 = nextRow.createCell(j);
cell2.setCellValue(String.valueOf(maps.get(titleArrts.get(j))));
}
}
}
/**
* CSV文件生成方法
*
* @param head
* @param outPutPath
* @param filename
* @return
*/
private File createCSVFile(List<Object> head, List list, String outPutPath, String filename, String fontFormat) {
File csvFile = null;
BufferedWriter csvWtriter = null;
try {
csvFile = new File(outPutPath + File.separator + filename + ".csv");
File parent = csvFile.getParentFile();
if (parent != null && !parent.exists()) {
parent.mkdirs();
}
// GB2312使正确读取分隔符","
csvWtriter = new BufferedWriter(new OutputStreamWriter(new FileOutputStream(csvFile), fontFormat), 1024);
// 写入文件头部
writeHeadRow(head, csvWtriter);
// 写入文件内容
for (Object row : list) {
writeRow((Map) row, csvWtriter);
}
csvWtriter.flush();
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
csvWtriter.close();
} catch (IOException e) {
e.printStackTrace();
}
}
return csvFile;
}
/**
* 写一行数据方法
*
* @param row
* @param csvWriter
* @throws IOException
*/
private void writeRow(Map row, BufferedWriter csvWriter) throws IOException {
// 写入文件头部
String rowStr = null;
for (int j = 0; j < titles.size(); j++) {
StringBuffer buf = new StringBuffer();
if ((titles.size() - j) != 1) {
rowStr = buf.append("\"").append(row.get(titleArrts.get(j))).append("\",").toString().trim();
} else {
rowStr = buf.append("\"").append(row.get(titleArrts.get(j))).append("\"").toString().trim();
}
csvWriter.write(rowStr);
}
csvWriter.newLine();
}
/**
* 写头数据方法
*
* @param row
* @param csvWriter
* @throws IOException
*/
private void writeHeadRow(List<Object> row, BufferedWriter csvWriter) throws IOException {
// 写入文件头部
String rowStr = null;
for (int j = 0; j < row.size(); j++) {
StringBuffer buf = new StringBuffer();
if ((titles.size() - j) != 1) {
rowStr = buf.append("\"").append(row.get(j)).append("\",").toString().trim();
} else {
rowStr = buf.append("\"").append(row.get(j)).append("\"").toString().trim();
}
csvWriter.write(rowStr);
}
csvWriter.newLine();
}
//获取表头集合
public void getTitle(List list, List<AttrEntityLabel> labels) {
Object str = null;
titles = new ArrayList<>();
titleArrts = new ArrayList<>();
if (list != null && list.size() > 0 && labels != null) {
Map<String, String> map = (HashMap<String, String>) list.get(0);
for (AttrEntityLabel ael : labels) {
//判断是否有查询的字段
str = map.get(ael.getAttrName());
if (str != null) {
titles.add(ael.getAttrLabel());
titleArrts.add(ael.getAttrName());
}
}
}
}
}
381
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
