Excel的导入导出，基本上是每个项目必备的功能。但是导入导出的实现还是有一些不同，下面我就分三种情况来说明，通过代码来体现有哪些不同：
1.常规导入导出xlsx
1.1 引入jar
<!--导入导出excel-->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>2.1.7</version>
</dependency>1.2 添加公共导入导出方法
这里需要添加几个处理导入导出必备类
EasyExcelUtils 公共类
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.support.ExcelTypeEnum;
import org.springframework.web.multipart.MultipartFile;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.net.URLEncoder;
import java.util.List;
/**
* @ClassName: EasyExcelUtils
* @Description: excel 导入导出公共类
* @Author: letisgo5
* @Date: 2022/06/21 14:34
*/
public class EasyExcelUtils {
/**
* 导出
* @param response
* @param excelName excel名称
* @param sheetName sheet名称
* @param clazz 导出标题类(必须添加导出注解)
* @param data 导出数据
* @throws Exception
*/
public static void exportData(HttpServletResponse response, String excelName, String sheetName, Class clazz, List data) throws Exception {
response.setContentType("application/vnd.ms-excel");
response.setCharacterEncoding("utf-8");
// 这里URLEncoder.encode可以防止中文乱码
excelName = URLEncoder.encode(excelName, "UTF-8").replaceAll("\\+", "%20");
String filename = new String(excelName.getBytes("UTF-8"), "ISO-8859-1");
response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + filename + ExcelTypeEnum.XLSX.getValue());
response.setHeader("filename", filename + ExcelTypeEnum.XLSX.getValue());
EasyExcel.write(response.getOutputStream(), clazz).sheet(sheetName).registerWriteHandler(new ExcelCellWriteHandler()).doWrite(data);
}
/**
* 导入
* @param excelFile 文件
* @param clazz 导出标题类
* @param esayExcelListener 解析文件对象
* @throws IOException
*/
public static void importData(MultipartFile excelFile, Class clazz, EsayExcelListener esayExcelListener) throws IOException {
EasyExcel.read(excelFile.getInputStream(), clazz, esayExcelListener).registerConverter(new ExcelStringNumberUtils()).sheet().headRowNumber(2).doRead(); //从第二行开始读取（第一行为标题）
}EsayExcel-导入/导出 监听类
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import java.util.ArrayList;
import java.util.List;
/**
* @ClassName: EsayExcelListener
* @Description: EsayExcel-导入/导出 监听类
* @Author: letisgo5
* @Date: 2022/06/21 14:53
*/
public class EsayExcelListener<T> extends AnalysisEventListener<T> {
// private static final int BATCH_COUNT = 3000;
//自定义用于暂时存储data。
//可以通过实例获取该值
List<T> dataList = new ArrayList<T>();
/**
* 通过 AnalysisContext 对象还获取当前 sheet，当前行等数据
* @param data
* @param analysisContext
*/
@Override
public void invoke(T data, AnalysisContext analysisContext) {
dataList.add(data);
// if (dataList.size() >= BATCH_COUNT) {
// }
}
/**
* 所有数据解析完成了 都会来调用
* @param analysisContext
*/
@Override
public void doAfterAllAnalysed(AnalysisContext analysisContext) {
//解析结束销毁不用的资源
//dataList.clear();
}
public List<T> getDataList() {
return dataList;
}
public void setDataList(List<T> dataList) {
this.dataList = dataList;
}EsayExcel传入String类型数字转换工具类
import com.alibaba.excel.converters.Converter;
import com.alibaba.excel.enums.CellDataTypeEnum;
import com.alibaba.excel.metadata.CellData;
import com.alibaba.excel.metadata.GlobalConfiguration;
import com.alibaba.excel.metadata.property.ExcelContentProperty;
import com.alibaba.excel.util.DateUtils;
import com.alibaba.excel.util.NumberUtils;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.util.NumberToTextConverter;
import java.math.BigDecimal;
/**
* @ClassName: ExcelStringNumberUtils
* @Description: EsayExcel传入String类型数字转换工具类
* @Author: letisgo5
* @Date: 2022/06/21 14:51
*/
public class ExcelStringNumberUtils implements Converter<String> {
@Override
public Class supportJavaTypeKey() {
return String.class;
}
@Override
public CellDataTypeEnum supportExcelTypeKey() {
return CellDataTypeEnum.NUMBER;
}
@Override
public String convertToJavaData(CellData cellData, ExcelContentProperty contentProperty,
GlobalConfiguration globalConfiguration) {
// If there are "DateTimeFormat", read as date
if (contentProperty != null && contentProperty.getDateTimeFormatProperty() != null) {
return com.alibaba.excel.util.DateUtils.format(
HSSFDateUtil.getJavaDate(cellData.getNumberValue().doubleValue(),
contentProperty.getDateTimeFormatProperty().getUse1904windowing(), null),
contentProperty.getDateTimeFormatProperty().getFormat());
}
// If there are "NumberFormat", read as number
if (contentProperty != null && contentProperty.getNumberFormatProperty() != null) {
return NumberUtils.format(cellData.getNumberValue().doubleValue(), contentProperty);
}
// Excel defines formatting
if (cellData.getDataFormat() != null) {
if (DateUtil.isADateFormat(cellData.getDataFormat(), cellData.getDataFormatString())) {
if (cellData.getDataFormatString().contains(":")) {
return DateUtils.format(HSSFDateUtil.getJavaDate(cellData.getNumberValue().doubleValue(),
globalConfiguration.getUse1904windowing(), null));
} else {
return DateUtils.format(HSSFDateUtil.getJavaDate(cellData.getNumberValue().doubleValue(),
globalConfiguration.getUse1904windowing(), null), "yyyy-MM-dd");
}
} else if (contentProperty == null) {
try {
// 百分比
if (cellData.getDataFormatString() != null && cellData.getDataFormatString().contains("%")) {
return new BigDecimal(cellData.getNumberValue().toString()).multiply(new BigDecimal(100)).stripTrailingZeros().toPlainString() + "%";
} else if (cellData.getDataFormatString() != null && cellData.getDataFormatString().equals("General")) {
//解决easyExcel 解析无 CLASS 对象时，Number to string 用String去接收数字，出现小数点等情况 方法一 会出现 数字位数失真的情况 ，即 excel 用公式计算数值后，只保留3位小数， 读取时 可能出现 直接去取保留前的N为小数的情况 建议使用方法二
// 方法一 NumberFormat numberFormat = NumberFormat.getInstance();
// numberFormat.setMaximumFractionDigits(20);
// numberFormat.setGroupingUsed(false);
// return numberFormat.format(cellData.getDoubleValue());
// 方法二
return NumberToTextConverter.toText(cellData.getNumberValue().doubleValue());
} else {
return NumberToTextConverter.toText(cellData.getNumberValue().doubleValue());
// return cellData.getDoubleValue().toString();
}
} catch (Exception e) {
// 建议 统一使用以下方法，可以解决数值格式问题
return NumberToTextConverter.toText(cellData.getNumberValue().doubleValue());
// return NumberUtils.format(cellData.getDoubleValue(), contentProperty);
}
} else {
return NumberToTextConverter.toText(cellData.getNumberValue().doubleValue());
// return NumberUtils.format(cellData.getDoubleValue(), contentProperty);
}
}
// Default conversion number
// NumberFormat numberFormat = NumberFormat.getInstance();
// numberFormat.setMaximumFractionDigits(20);
// numberFormat.setGroupingUsed(false);
// return numberFormat.format(cellData.getDoubleValue());
// 方法二
return NumberToTextConverter.toText(cellData.getNumberValue().doubleValue());
// return NumberUtils.format(cellData.getDoubleValue(), contentProperty);
}
@Override
public CellData convertToExcelData(String value, ExcelContentProperty contentProperty,
GlobalConfiguration globalConfiguration) {
return new CellData(Double.valueOf(value));
}ExcelCellWriteHandler 单元格处理器
import com.alibaba.excel.enums.CellDataTypeEnum;
import com.alibaba.excel.metadata.CellData;
import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.util.CollectionUtils;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.style.column.AbstractColumnWidthStyleStrategy;
import org.apache.poi.ss.usermodel.Cell;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
/**
* @ClassName: ExcelCellWriteHandler
* @Description: 单元格处理器
* @Author: letisgo5
* @Date: 2022/06/21 14:48
*/
public class ExcelCellWriteHandler extends AbstractColumnWidthStyleStrategy {
private Map<Integer, Map<Integer, Integer>> CACHE = new HashMap<Integer, Map<Integer, Integer>>();
@Override
protected void setColumnWidth(WriteSheetHolder writeSheetHolder, List<CellData> cellDataList, Cell cell, Head head, Integer integer, Boolean isHead) {
boolean needSetWidth = isHead || !CollectionUtils.isEmpty(cellDataList);
if (needSetWidth) {
Map<Integer, Integer> maxColumnWidthMap = CACHE.get(writeSheetHolder.getSheetNo());
if (maxColumnWidthMap == null) {
maxColumnWidthMap = new HashMap<Integer, Integer>();
CACHE.put(writeSheetHolder.getSheetNo(), maxColumnWidthMap);
}
Integer columnWidth = this.dataLength(cellDataList, cell, isHead);
if (columnWidth >= 0) {
if (columnWidth > 255) {
columnWidth = 255;
}
Integer maxColumnWidth = maxColumnWidthMap.get(cell.getColumnIndex());
if (maxColumnWidth == null || columnWidth > maxColumnWidth) {
maxColumnWidthMap.put(cell.getColumnIndex(), columnWidth);
writeSheetHolder.getSheet().setColumnWidth(cell.getColumnIndex(), columnWidth * 256);
}
}
}
}
private Integer dataLength(List<CellData> cellDataList, Cell cell, Boolean isHead) {
if (isHead) {
return cell.getStringCellValue().getBytes().length;
} else {
CellData cellData = cellDataList.get(0);
CellDataTypeEnum type = cellData.getType();
if (type == null) {
return -1;
} else {
switch (type) {
case STRING:
return cellData.getStringValue().getBytes().length;
case BOOLEAN:
return cellData.getBooleanValue().toString().getBytes().length;
case NUMBER:
return cellData.getNumberValue().toString().getBytes().length;
default:
return -1;
}
}
}
}1.3 代码调用
导出调用
EasyExcelUtils.exportWeb(response, excelName, sheetName, ExportDataVo.class, list);
导入调用
// ImportDataVo 表示需要导入的类，别忘记注解 @ExcelProperty(value = "序号", index = 0)
EsayExcelListener esayExcelListener = new EsayExcelListener();
EasyExcelUtils.importWeb(file, ImportDataVo.class, esayExcelListener);
List<ImportDataVo> list = esayExcelListener.getDataList();2.根据现有模板导入导出xlsx
根据导出模板导出public void patchExport(String tempName, String fileName, List dataLst, HttpSession session, HttpServletResponse response) {
BufferedOutputStream bos = null;
try {
//模板存储位置
String realPath = session.getServletContext().getRealPath("WEB-INF/template/export/"+ tempName);
response.setContentType("application/vnd.ms-excel");
response.setCharacterEncoding("utf-8");
//导出的文件名
String filename = URLEncoder.encode(fileName, "utf-8")+ ExcelTypeEnum.XLSX.getValue();
response.setHeader("Content-disposition", "attachment; filename=" + new String(filename.getBytes("UTF-8"), "ISO-8859-1"));
bos = new BufferedOutputStream(response.getOutputStream());
ExcelWriter excelWriter = EasyExcel.write(bos).withTemplate(realPath).build();
WriteSheet writeSheet = EasyExcel.writerSheet().build();
//list map 是查询并需导出的数据，并且里面的字段和excel需要导出的字段对应
if(CollectionUtils.isNotEmpty(dataLst)){
excelWriter.fill(dataLst, writeSheet);
// excelWriter.fill(dataMap, writeSheet);
}
excelWriter.finish();
bos.flush();
} catch (Exception e) {
response.reset();
response.setContentType("application/json");
response.setCharacterEncoding("utf-8");
log.error("patchExport error:[{}]", ExceptionUtils.getStackTrace(e));
} finally {
if (bos != null){
try {
bos.close();
} catch (IOException e) {
log.error("patchExport BufferedOutputStream close error:[{}]", ExceptionUtils.getStackTrace(e));
}
}
}
}根据导入模板导入
与常规导入无太大差别，主要是模板从第几行读取3.导入导出csv
导入导出csv
3.1 引入jar
<!-- 导入导出csv文件 -->
<dependency>
<groupId>org.apache.commons</groupId>
<artifactId>commons-csv</artifactId>
<version>1.8</version>
</dependency>
<dependency>
<groupId>commons-io</groupId>
<artifactId>commons-io</artifactId>
<version>2.8.0</version>
</dependency>3.2 实际应用
导出CSV
/**
* 写CSV并转换为字节流
* @param headers 表头
* @param cellList 表数据
* @return
*/
public static byte[] writeCsvAfterToBytes(String[] headers,List<Object[]> cellList) {
byte[] bytes = new byte[0];
ByteArrayOutputStream byteArrayOutputStream = new ByteArrayOutputStream();
OutputStreamWriter outputStreamWriter = new OutputStreamWriter(byteArrayOutputStream, Charset.forName("UTF-8"));
BufferedWriter bufferedWriter = new BufferedWriter(outputStreamWriter);
CSVPrinter csvPrinter = null;
try {
//创建csvPrinter并设置表格头
csvPrinter = new CSVPrinter(bufferedWriter, CSVFormat.DEFAULT.withHeader(headers));
//写数据
csvPrinter.printRecords(cellList);
csvPrinter.flush();
bytes = byteArrayOutputStream.toString("UTF-8").getBytes();
} catch (IOException e) {
log.error("writeCsv IOException:{}", e.getMessage(), e);
} finally {
try {
if (csvPrinter != null) {
csvPrinter.close();
}
if (bufferedWriter != null) {
bufferedWriter.close();
}
if (outputStreamWriter != null) {
outputStreamWriter.close();
}
if (byteArrayOutputStream != null) {
byteArrayOutputStream.close();
}
} catch (IOException e) {
log.error("iostream close IOException:{}", e.getMessage(), e);
}
}
return bytes;
}
/**
* 设置下载响应
* @param fileName
* @param bytes
* @param response
*/
public static void responseSetProperties(String fileName, byte[] bytes, HttpServletResponse response) {
try {
fileName = URLEncoder.encode(fileName, "UTF-8");
response.setContentType("application/csv");
response.setCharacterEncoding("UTF-8");
response.setHeader("Pragma", "public");
response.setHeader("Cache-Control", "max-age=30");
response.setHeader("Content-Disposition", "attachment; filename=" + fileName);
OutputStream outputStream = response.getOutputStream();
outputStream.write(bytes);
outputStream.flush();
} catch (IOException e) {
log.error("responseSetProperties error:{}", e.getMessage(), e);
}
}导入CSV
/**
* 导入CSV文件
* @param file 上传文件
* @return
*/
public static List<String> importCsv(MultipartFile file) {
List<String> dataList = new ArrayList<String>();
BufferedReader br = null;
try {
byte [] byteArr = file.getBytes();
InputStream inputStream = new ByteArrayInputStream(byteArr);
InputStreamReader inputStreamReader =new InputStreamReader(inputStream,"GBK");
br = new BufferedReader(inputStreamReader);
String line = "";
while ((line = br.readLine()) != null) {
dataList.add(line);
}
} catch (Exception e) {
log.error("importCsv Close error:{}", e.getMessage(), e);
} finally {
if (br != null) {
try {
br.close();
} catch (IOException e) {
log.error("importCsv BufferedReader Close error:{}", e.getMessage(), e);
}
}
}
return dataList;
}
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
