poi导出：
pom文件：
<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>
接口：
@GetMapping("/poiExport")
public void poiExport(HttpServletResponse response, HttpServletRequest request);实现类：
@Override
public void poiExport(HttpServletResponse response, HttpServletRequest request) {
//poi导出其实和poi下载模板没有什么区别，多了插入数据而已
String [] excelHeader = {"原零件号","原零件名称","参考零件号","参考零件名称","长","宽"};
//数据可以从数据库读取
List<Object> list = new ArrayList<>();
Object[] obj1 = {"张三","173*****311","a1","A","IOS","是"};
Object[] obj2 = {"李四","138*****742","a2","B","安卓","否"};
list.add(obj1);
list.add(obj2);
FileExport.exportExcel(excelHeader, list, "下载模板", response, request);
}
工具类代码：
public class FileExport {
private static final Logger logger = LoggerFactory.getLogger(FileExport.class);
/** CSV文件列分隔符 */
private static final String CSV_COLUMN_SEPARATOR = ",";
private static final String CSV_COLUM_TABLE = "\t";
/** CSV文件列分隔符 */
private static final String CSV_RN = "\r\n";
/**
* 导出Excel文件
*
* @param excelHeader
* 导出文件中表格头
* @param list
* 导出的内容
* @param response
* HttpServletResponse对象，用来获得输出流向客户端写导出的文件
* @param sheetName
* Excel的sheet名称，加上时间戳作为导出文件的名称
*/
public static void exportExcel(String [] excelHeader, List<Object> list, String sheetName, HttpServletResponse response, HttpServletRequest request) {
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.createSheet(sheetName);
HSSFRow row = sheet.createRow((int) 0);
/******设置单元格是否显示网格线******/
sheet.setDisplayGridlines(true);
/******设置头单元格样式******/
HSSFCellStyle style = wb.createCellStyle();
style.setAlignment(HorizontalAlignment.CENTER);
Font fontHeader = wb.createFont();
fontHeader.setBold(true);
fontHeader.setFontHeight((short) 240);
style.setFont(fontHeader);
style.setBorderBottom(BorderStyle.THIN);
style.setBorderLeft(BorderStyle.THIN);
style.setBorderRight(BorderStyle.THIN);
style.setBorderTop(BorderStyle.THIN);
/******设置头内容******/
for (int i = 0; i < excelHeader.length; i++) {
HSSFCell cell = row.createCell(i);
//cell.setCellValue(" " +excelHeader[i] + " ");
cell.setCellValue(excelHeader[i]);
cell.setCellStyle(style);
}
/******设置内容单元格样式******/
HSSFCellStyle styleCell = wb.createCellStyle();
Font fontCell = wb.createFont();
//fontCell.setColor(HSSFColor.BLACK.index);
styleCell.setAlignment(HorizontalAlignment.CENTER);
styleCell.setFont(fontCell);
styleCell.setBorderBottom(BorderStyle.THIN);
styleCell.setBorderLeft(BorderStyle.THIN);
styleCell.setBorderRight(BorderStyle.THIN);
styleCell.setBorderTop(BorderStyle.THIN);
/******设置单元格内容******/
for (int i = 0; i < list.size(); i++) {
row = sheet.createRow(i + 1);
/******设置行高******/
row.setHeightInPoints(20);
Object[] obj = (Object[]) list.get(i);
for (int j = 0; j < excelHeader.length; j++) {
styleCell.setWrapText(false);
HSSFCell cell = row.createCell(j);
if (obj[j] != null){
cell.setCellValue(obj[j].toString());
}else{
cell.setCellValue("");
}
//if(obj[j].toString().length()>20)
// styleCell.setWrapText(true);
cell.setCellStyle(styleCell);
sheet.autoSizeColumn(j);
}
}
OutputStream ouputStream = null;
try {
String encoding = "UTF-8";
/** 获取浏览器相关的信息 */
String userAgent = request.getHeader("user-agent");
/** 判断是否为msie浏览器 */
/*if (userAgent.toLowerCase().indexOf("msie") != -1){
encoding = "gbk";
}*/
response.setCharacterEncoding(encoding);
response.setContentType("application/vnd.ms-excel");
//String fileName = sheetName;
SimpleDateFormat dateFormat = new SimpleDateFormat("yyyyMMddHHMMSS");
//fileName += (dateFormat.format(new Date())).toString()+".xls";
String fileName =(dateFormat.format(new Date())).toString()+".xls";
response.setHeader("Content-disposition", "attachment;filename=" + URLEncoder.encode(fileName, encoding));
ouputStream = response.getOutputStream();
wb.write(ouputStream);
ouputStream.flush();
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
if(ouputStream!=null) {
ouputStream.close();
}
} catch (IOException e) {
e.printStackTrace();
}
}
}
/**
* 导出CSV文件
* @param dataList 集合数据
* @param colNames 表头部数据
* @param mapKey 查找的对应数据
*/
public static boolean doExport(List<Map<String, Object>> dataList, String colNames, String mapKey, OutputStream os) {
try {
StringBuffer buf = new StringBuffer();
String[] colNamesArr = null;
String[] mapKeyArr = null;
colNamesArr = colNames.split(",");
mapKeyArr = mapKey.split(",");
/******完成数据csv文件的封装******/
/******输出列头******/
for (int i = 0; i < colNamesArr.length; i++) {
buf.append(colNamesArr[i]).append(CSV_COLUMN_SEPARATOR);
}
buf.append(CSV_RN);
if (null != dataList) {
/******输出数据******/
for (int i = 0; i < dataList.size(); i++) {
for (int j = 0; j < mapKeyArr.length; j++) {
buf.append(dataList.get(i).get(mapKeyArr[j])).append(CSV_COLUM_TABLE).append(CSV_COLUMN_SEPARATOR);
}
buf.append(CSV_RN);
}
}
/******写出响应******/
os.write(buf.toString().getBytes("GBK"));
os.flush();
return true;
} catch (Exception e) {
logger.error("doExport错误...", e);
}
return false;
}
/**
* 设置响应格式
* @param fileName
* @param response
* @throws UnsupportedEncodingException
*/
public static void responseSetProperties(String fileName, HttpServletResponse response) throws UnsupportedEncodingException {
/******设置文件后缀******/
SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMddHHmmss");
String fn = fileName + sdf.format(new Date()).toString() + ".csv";
/******读取字符编码******/
String utf = "UTF-8";
/******设置响应******/
response.setContentType("application/ms-txt.numberformat:@");
response.setCharacterEncoding(utf);
response.setHeader("Pragma", "public");
response.setHeader("Cache-Control", "max-age=30");
response.setHeader("Content-Disposition", "attachment; filename=" + URLEncoder.encode(fn, utf));
}
}项目运行，生成后的文件打开可以看到
poi导入：
我们把上面生成的excel导入进来
接口：
@RequestMapping(value = "/poiImport",method = RequestMethod.POST)
public void poiImport(HttpServletRequest request,@RequestParam("file") MultipartFile file) throws IOException;实现类：
@Override
public void poiImport(HttpServletRequest request,@RequestParam("file") MultipartFile file) throws IOException {
Map<String, Object> map = new HashMap<String, Object>();
// 判断文件是否为空
if (!StringUtils.isEmpty(file)) {
try {
List<RefPartDo> excelBeans = FileImport.readExcel(file, RefPartDo.class);
System.out.println(excelBeans.size());
for(RefPartDo ep : excelBeans){
System.out.println(ep.toString());
}
//........逻辑
} catch (Exception e) {
e.printStackTrace();
}
}
}实体类：
@Data
public class RefPartDo {
@ExcelColumn("原零件号")
private String partNo;
@ExcelColumn("原零件名称")
private String partName;
@ExcelColumn("参考零件号")
private String refPartNo;
@ExcelColumn("参考零件名称")
private String refPartName;;
@ExcelColumn("长")
private String length;
@ExcelColumn("宽")
private String width;
}
自定义注解：
@Target({ElementType.FIELD})
@Retention(RetentionPolicy.RUNTIME)
@Documented
public @interface ExcelColumn{
public String value() default "";
}工具类：
public class FileImport {
private static final DecimalFormat DECIMAL_FORMAT = new DecimalFormat("0");// 格式化 number为整
private static final DecimalFormat DECIMAL_FORMAT_PERCENT = new DecimalFormat("##.00%");//格式化分比格式，后面不足2位的用0补齐
// private static final DecimalFormat df_per_ = new DecimalFormat("0.00%");//格式化分比格式，后面不足2位的用0补齐,比如0.00,%0.01%
// private static final SimpleDateFormat sdf = new SimpleDateFormat("yyyy/MM/dd"); // 格式化日期字符串
private static final FastDateFormat FAST_DATE_FORMAT = FastDateFormat.getInstance("yyyy/MM/dd");
private static final DecimalFormat DECIMAL_FORMAT_NUMBER = new DecimalFormat("0.00E000"); //格式化科学计数器
private static final Pattern POINTS_PATTERN = Pattern.compile("0.0+_*[^/s]+"); //小数匹配
/**
* 对外提供读取excel 的方法
* @param file
* @return
* @throws IOException
*/
public static List<List<Object>> readExcel(MultipartFile file) throws IOException {
String extension = file.getOriginalFilename().substring(file.getOriginalFilename().lastIndexOf(".") + 1).toLowerCase();
if(Objects.equals("xls", extension) || Objects.equals("xlsx", extension)) {
return readExcel(file.getInputStream());
} else {
throw new IOException("不支持的文件类型");
}
}
/**
* 对外提供读取excel 的方法
* @param file
* @param cls
* @return
* @throws IOException
*/
public static <T> List<T> readExcel(MultipartFile file, Class<T> cls) throws IOException {
String extension = file.getOriginalFilename().substring(file.getOriginalFilename().lastIndexOf(".") + 1).toLowerCase();
if(Objects.equals("xls", extension) || Objects.equals("xlsx", extension)) {
return readExcel(file.getInputStream(), cls);
} else {
throw new IOException("不支持的文件类型");
}
}
/**
* 读取 office excel
*
* @param stream
* @return
* @throws IOException
*/
private static List<List<Object>> readExcel(InputStream inputStream) throws IOException {
List<List<Object>> list = new LinkedList<>();
Workbook workbook = null;
try {
workbook = WorkbookFactory.create(inputStream);
int sheetsNumber = workbook.getNumberOfSheets();
for (int n = 0; n < sheetsNumber; n++) {
Sheet sheet = workbook.getSheetAt(n);
Object value = null;
Row row = null;
Cell cell = null;
for (int i = sheet.getFirstRowNum() + 1; i <= sheet.getPhysicalNumberOfRows(); i++) { // 从第二行开始读取
row = sheet.getRow(i);
if (StringUtils.isEmpty(row)) {
continue;
}
List<Object> linked = new LinkedList<>();
for (int j = row.getFirstCellNum(); j <= row.getLastCellNum(); j++) {
cell = row.getCell(j);
if (StringUtils.isEmpty(cell)) {
continue;
}
value = getCellValue(cell);
linked.add(value);
}
list.add(linked);
}
}
} catch (Exception e) {
e.printStackTrace();
} finally {
IOUtils.closeQuietly(workbook);
IOUtils.closeQuietly(inputStream);
}
return list;
}
/**
* 获取excel数据 将之转换成bean
*
* @param path
* @param cls
* @param <T>
* @return
*/
private static <T> List<T> readExcel(InputStream inputStream, Class<T> cls) {
List<T> dataList = new LinkedList<T>();
Workbook workbook = null;
try {
workbook = WorkbookFactory.create(inputStream);
Map<String, List<Field>> classMap = new HashMap<String, List<Field>>();
Field[] fields = cls.getDeclaredFields();
for (Field field : fields) {
ExcelColumn annotation = field.getAnnotation(ExcelColumn.class);
if (annotation != null) {
String value = annotation.value();
if (!classMap.containsKey(value)) {
classMap.put(value, new ArrayList<Field>());
}
field.setAccessible(true);
classMap.get(value).add(field);
}
}
Map<Integer, List<Field>> reflectionMap = new HashMap<Integer, List<Field>>();
int sheetsNumber = workbook.getNumberOfSheets();
for (int n = 0; n < sheetsNumber; n++) {
Sheet sheet = workbook.getSheetAt(n);
for (int j = sheet.getRow(0).getFirstCellNum(); j < sheet.getRow(0).getLastCellNum(); j++) { //首行提取注解
Object cellValue = getCellValue(sheet.getRow(0).getCell(j));
if (classMap.containsKey(cellValue)) {
reflectionMap.put(j, classMap.get(cellValue));
}
}
Row row = null;
Cell cell = null;
for (int i = sheet.getFirstRowNum() + 1; i < sheet.getPhysicalNumberOfRows(); i++) {
row = sheet.getRow(i);
T t = cls.newInstance();
for (int j = row.getFirstCellNum(); j < row.getLastCellNum(); j++) {
cell = row.getCell(j);
if (reflectionMap.containsKey(j)) {
Object cellValue = getCellValue(cell);
List<Field> fieldList = reflectionMap.get(j);
for (Field field : fieldList) {
try {
field.set(t, cellValue);
/* if(cellValue instanceof String){
field.set(t, cellValue);
}
if(cellValue instanceof Double){
field.setDouble(t, (Double) cellValue);
}
if(cellValue instanceof Integer){
field.setInt(t, (Integer) cellValue);
}*/
} catch (Exception e) {
//logger.error()
}
}
}
}
dataList.add(t);
}
}
} catch (Exception e) {
dataList = null;
} finally {
IOUtils.closeQuietly(workbook);
IOUtils.closeQuietly(inputStream);
}
return dataList;
}
/**
* 获取excel 单元格数据
*
* @param cell
* @return
*/
private static Object getCellValue(Cell cell) {
Object value = null;
switch (cell.getCellTypeEnum()) {
case _NONE:
break;
case STRING:
value = cell.getStringCellValue();
break;
case NUMERIC:
if(DateUtil.isCellDateFormatted(cell)){ //日期
value = FAST_DATE_FORMAT.format(DateUtil.getJavaDate(cell.getNumericCellValue()));//统一转成 yyyy/MM/dd
} else if("@".equals(cell.getCellStyle().getDataFormatString())
|| "General".equals(cell.getCellStyle().getDataFormatString())
|| "0_ ".equals(cell.getCellStyle().getDataFormatString())){
//文本 or 常规 or 整型数值
//value = DECIMAL_FORMAT.format(cell.getNumericCellValue());
value = cell.getNumericCellValue();
} else if(POINTS_PATTERN.matcher(cell.getCellStyle().getDataFormatString()).matches()){ //正则匹配小数类型
value = cell.getNumericCellValue(); //直接显示
} else if("0.00E+00".equals(cell.getCellStyle().getDataFormatString())){//科学计数
value = cell.getNumericCellValue(); //待完善
value = DECIMAL_FORMAT_NUMBER.format(value);
} else if("0.00%".equals(cell.getCellStyle().getDataFormatString())){//百分比
value = cell.getNumericCellValue(); //待完善
value = DECIMAL_FORMAT_PERCENT.format(value);
} else if("# ?/?".equals(cell.getCellStyle().getDataFormatString())){//分数
value = cell.getNumericCellValue();
} else { //货币
value = cell.getNumericCellValue();
value = DecimalFormat.getCurrencyInstance().format(value);
}
break;
case BOOLEAN:
value = cell.getBooleanCellValue();
break;
case BLANK:
//value = ",";
break;
default:
value = cell.toString();
}
return value;
}
}postman导入文件：控制台可以看到
PS:问题
1.导入的时候，如果你把实体类字段类型改为非 String 类型，你会发现工具类解析不了，工具类相关代码也有注释，我有尝试去修改工具类，也不行。大佬看到的话可以指点一下
2. 如果你是自己定义的表格，后台如果解析不成功的话，可以看看列名是否有空格，有空格的话，是解析不了的。
参考帖子：https://blog.csdn.net/phil_jing/article/details/78307819
可能解决该问题的帖子：https://www.yuque.com/calmer/blog/java-poi#zg9evr