1.导入相关的依赖
2.创建ExportExcel类
package www.gaozening.club.langfangdachenggaozhuanggerenxinxiguanlizhongxin.Export;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiOperation;
import lombok.SneakyThrows;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.DateUtil;
import org.springframework.stereotype.Controller;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.OutputStream;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.springframework.web.bind.annotation.RequestMapping;
import static com.sun.xml.internal.ws.spi.db.BindingContextFactory.LOGGER;
@Controller
@Api(description = "导出Excel")
public class ExportExcel {
/**
* 导出模板
*
* @param response
* @param request
* @throws IOException
*/
@RequestMapping(value = "/download", produces = "text/html;charset=UTF-8")
public void download(HttpServletResponse response, HttpServletRequest request) {
//创建HSSFWorkbook对象
try (HSSFWorkbook wb = new HSSFWorkbook()) {
LOGGER.info("进入导出模板方法");
//创建HSSFSheet对象
HSSFSheet sheet = wb.createSheet("导出模板");
//在sheet里创建第一行，参数为行索引(excel的行)，可以是0～65535之间的任何一个
HSSFRow row1 = sheet.createRow(0);
//创建单元格（excel的单元格，参数为列索引，可以是0～255之间的任何一个
HSSFCell cell = row1.createCell(0);
//合并单元格
sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 11));
//向合并单元格中输入字段
cell.setCellValue("第一小区户口");
//在sheet里创建第二行(下标为1)
HSSFRow row2 = sheet.createRow(1);
//创建单元格并设置单元格内容
row2.createCell(0).setCellValue("ID");
row2.createCell(1).setCellValue("户号");
row2.createCell(2).setCellValue("与户主关系");
row2.createCell(3).setCellValue("姓名");
row2.createCell(4).setCellValue("性别");
row2.createCell(5).setCellValue("年龄");
row2.createCell(6).setCellValue("出生年月");
row2.createCell(7).setCellValue("身份证");
row2.createCell(8).setCellValue("手机号");
row2.createCell(9).setCellValue("民族");
row2.createCell(10).setCellValue("派出所");
row2.createCell(11).setCellValue("村居委会");
//输出Excel文件
OutputStream output = response.getOutputStream();
response.reset();
//filename=后面跟文件名
response.setHeader("Content-disposition", "attachment; filename=Community.xlsx");
response.setContentType("application/vnd.ms-excel;charset=utf-8");
LOGGER.info("即将输出文件流");
wb.write(output);
output.flush();
output.close();
} catch (Exception e) {
// LOGGER.error("导出统计模板生成excel异常", e);
} finally {
Object RequestContextLocal = null;
// RequestContextLocal.bindUserOperation("下载模板", "客户支付流程跟踪导入和统计菜单");
}
LOGGER.info("方法运行结束");
}
}
3.工具类ExcelUtils
package www.gaozening.club.langfangdachenggaozhuanggerenxinxiguanlizhongxin.Utils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;
import www.gaozening.club.langfangdachenggaozhuanggerenxinxiguanlizhongxin.enity.Community;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;
public class ExcelUtils {
//总行数
private static int totalRows = 12;
//总条数
private static int totalCells = 12;
//错误信息接收器
private static String errorMsg;
/**
* 验证EXCEL文件
*
* @param filePath
* @return
*/
public static boolean validateExcel(String filePath) {
if (filePath == null || !(isExcel2003(filePath) || isExcel2007(filePath))) {
errorMsg = "文件名不是excel格式";
return false;
}
return true;
}
/*
excel2003版本的excel文件是.xls格式，excel2007及以上版本的excel的是.xlsx格式。
*/
// @描述：是否是2003的excel，返回true是2003
public static boolean isExcel2003(String filePath) {
return filePath.matches("^.+\\.(?i)(xls)$");
}
//@描述：是否是2007的excel，返回true是2007
public static boolean isExcel2007(String filePath) {
return filePath.matches("^.+\\.(?i)(xlsx)$");
}
/**
* 读取Excel里面客户的信息
* @param wb
* @return
*/
private static List
//默认会跳过第一行标题
// 得到第一个shell
Sheet sheet = wb.getSheetAt(0);
// 得到Excel的行数
totalRows = sheet.getPhysicalNumberOfRows();
// 得到Excel的列数(前提是有行数)
if (totalRows > 1 && sheet.getRow(0) != null) {
totalCells = sheet.getRow(0).getPhysicalNumberOfCells();
}
List
// 循环Excel行数
for (int r = 1; r < totalRows; r++) {
Row row = sheet.getRow(r);
if (row == null) {
continue;
}
Community community = new Community();
// 循环Excel的列
for (int c = 0; c < totalCells ; c++) {
Cell cell = row.getCell(c);
if (null != cell) {
if (c == 0) { //第一列
//如果是纯数字,将单元格类型转为String
if (cell.getCellTypeEnum() == CellType.NUMERIC) {
double numericCellValue = cell.getNumericCellValue();
community.setID((int)numericCellValue);
}
} else if (c == 1) {
if (cell.getCellTypeEnum() == CellType.NUMERIC) {
cell.setCellType(CellType.STRING);
}
community.setNumber(cell.getStringCellValue());
} else if (c == 2) {
if (cell.getCellTypeEnum() == CellType.STRING) {
String stringCellValue = cell.getStringCellValue();
community.setRelationship(stringCellValue);
}
} else if (c == 3) {
if (cell.getCellTypeEnum() == CellType.STRING) {
String stringCellValue = cell.getStringCellValue();
community.setName(stringCellValue);
}
} else if (c == 4) {
if (cell.getCellTypeEnum() == CellType.STRING) {
String stringCellValue = cell.getStringCellValue();
community.setGender(stringCellValue);
}
} else if (c == 5) {
if (cell.getCellTypeEnum() == CellType.STRING) {
// double numericCellValue = cell.getNumericCellValue();
// community.setAge(String.valueOf((int)numericCellValue));
String numericCellValue = cell.getStringCellValue();
community.setAge(numericCellValue);
}
if (cell.getCellTypeEnum() == CellType.NUMERIC) {
// double numericCellValue = cell.getNumericCellValue();
// community.setAge(String.valueOf((int)numericCellValue));
double numericCellValue = cell.getNumericCellValue();
community.setAge(String.valueOf((int)numericCellValue));
}
if (cell.getCellTypeEnum() == CellType.FORMULA) {
// double numericCellValue = cell.getNumericCellValue();
// community.setAge(String.valueOf((int)numericCellValue));
double numericCellValue = cell.getNumericCellValue();
community.setAge(String.valueOf((int)numericCellValue));
}
} else if (c == 6) {
if (cell.getCellTypeEnum() == CellType.STRING) {
String numericCellValue = cell.getStringCellValue();
community.setCard(numericCellValue);
}
} else if (c == 7) {
if (cell.getCellTypeEnum() == CellType.NUMERIC) {
cell.setCellType(CellType.STRING);
} String numericCellValue = cell.getStringCellValue();
community.setDate(numericCellValue);
} else if (c == 8) {
if (cell.getCellTypeEnum() == CellType.NUMERIC) {
cell.setCellType(CellType.STRING);
}
String numericCellValue = cell.getStringCellValue();
community.setMobile(numericCellValue);
} else if (c == 9) {
if (cell.getCellTypeEnum() == CellType.STRING) {
String stringCellValue = cell.getStringCellValue();
community.setNationality(stringCellValue);
}
} else if (c == 10) {
if (cell.getCellTypeEnum() == CellType.STRING) {
String stringCellValue = cell.getStringCellValue();
community.setStation(stringCellValue);
}
} else if (c == 11) {
if (cell.getCellTypeEnum() == CellType.STRING) {
String stringCellValue = cell.getStringCellValue();
community.setCommittee(stringCellValue);
}
//score属性是int数据类型，转换成int
// manychoice.setScore(Integer.valueOf(cell.getStringCellValue()));
}
}
}
//将excel解析出来的数据赋值给对象添加到list中
manychoiceList.add(community);
}
return manychoiceList;
}
/**
* 根据excel里面的内容读取客户信息
* @param is 输入流
* @param isExcel2003 excel是2003还是2007版本
* @return
* @throws IOException
*/
public static List
try{
Workbook wb = null;
if (isExcel2003) {
// 当excel是2003时,创建excel2003
wb = new HSSFWorkbook(is);
} else {
// 当excel是2007时,创建excel2007
wb = new XSSFWorkbook(is);
}
// 读取Excel里面客户的信息
List
return communityList;
} catch (IOException e) {
e.printStackTrace();
}
return null;
}
/**
* 读EXCEL文件，获取信息集合
* @return
*/
public static List
String files = file.getOriginalFilename();//获取文件名
try {
if (!validateExcel(files)) {// 验证文件名是否合格
return null;
}
boolean isExcel2003 = true;// 根据文件名判断文件是2003版本还是2007版本
if (isExcel2007(files)) {
isExcel2003 = false;
}
List
return communityList;
} catch (Exception e) {
e.printStackTrace();
}
return null;
}
}
4.实体类
package cn.java999.uploadandparseexcel.entity;
import com.baomidou.mybatisplus.annotation.TableId;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import org.springframework.stereotype.Component;
@Data
@AllArgsConstructor
@NoArgsConstructor
@Component
public class Community {
@TableId
private Integer ID;
private String number;
private String relationship;
private String name;
private String gender;
private String age;
private String Date;
private String card;
private String Mobile;
private String nationality;
private String station;
private String committee;
}
5.mapper
package cn.java999.uploadandparseexcel.mapper;
import cn.java999.uploadandparseexcel.entity.Community;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import org.apache.ibatis.annotations.Mapper;
@Mapper
public interface Communitymapper extends BaseMapper
}
6.导出流程
7.开始导出下载
8.下载完成
9.导出效果
总结：到这里我们用java生成Excel表格并导出就完成了，请各位老铁浏览时点个赞并收藏以免用到时找不到，如有不足的地方请评论在下方
相关阅读
您阅读本篇文章共花了：
发表评论