Maven坐标
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.15</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.15</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml-schemas</artifactId>
<version>3.15</version>
</dependency>
测试架构
不是标准的SpringBoot项目
标准导入
controller
/**
* poi 导入
*/
@PostMapping("poiExcelIn")
public ResponseEntity poiExcelIn(MultipartFile file) throws Exception {//不能加
if (ObjectUtils.isEmpty(file)) {
return ResponseEntity.status(500).body("未上传文件");
}
return testExcelService.poiExcelIn(file);
}
impl
/**
* poi导入
* @param file
* @return
*/
ResponseEntity poiExcelIn(MultipartFile file) throws IOException;
//----------------------------------
/**
* poi导入
* @param file
* @return
*/
@Override
public ResponseEntity poiExcelIn(MultipartFile file) throws IOException {
Workbook workbook = null;
String fileName = file.getOriginalFilename();
if (StringUtils.isBlank(fileName)) {
return ResponseEntity.status(500).body("文件名称不可为空！");
}
if (fileName.endsWith(".xls")) {
workbook = new HSSFWorkbook(file.getInputStream());
} else if (fileName.endsWith(".xlsx")) {
workbook = new XSSFWorkbook(file.getInputStream());
} else {
return ResponseEntity.status(500).body("该文件不是Excel文件");
}
Sheet sheet = workbook.getSheetAt(0);
int lastRowNum = sheet.getLastRowNum();
if (lastRowNum == 0) {
return ResponseEntity.status(500).body("文件内没有数据 请确认后重试");
}
ArrayList<TestExcel> testExcels = new ArrayList<>();
for (int i = 0; i < lastRowNum; i++) {
Row row = sheet.getRow(i + 2);
if (row!=null){
TestExcel testExcel = new TestExcel();
String xuehao = getCellValue(row.getCell(0));
testExcel.setXueHao(xuehao);
String name = getCellValue(row.getCell(1));
testExcel.setName(name);
String age = getCellValue(row.getCell(2));
testExcel.setAge(age);
String sex = getCellValue(row.getCell(3));
testExcel.setSex(sex);
testExcels.add(testExcel);
}
}
System.out.println(testExcels);
return null;
}
标准导出
controller
/**
* poi 导出
*/
@GetMapping("poiExcelOut")
public void poiExcelOut(HttpServletResponse response)throws Exception{
testExcelService.poiExcelOut(response);
}
impl
/**
* poi导出
* @param response
*/
void poiExcelOut(HttpServletResponse response) throws IOException;
//-------------------------------
/**
* poi导出excel
* @param response
*/
@Override
public void poiExcelOut(HttpServletResponse response) throws IOException {
//确定编码的格式
response.setCharacterEncoding("UTF-8");
//创建excel文件
XSSFWorkbook wb = new XSSFWorkbook();
//创建sheet页
XSSFSheet sheet = wb.createSheet("表");
// //创建标题行
// XSSFRow titleRow = sheet.createRow(0);
// titleRow.createCell(0).setCellValue("班号");
// titleRow.createCell(1).setCellValue("姓名");
// titleRow.createCell(2).setCellValue("年龄");
// titleRow.createCell(3).setCellValue("性别");
CellRangeAddress huhao = new CellRangeAddress(0, 1, 0, 0);
sheet.addMergedRegion(huhao);
Row rowtou = sheet.createRow(0);
Cell huhaocell = rowtou.createCell(0);
huhaocell.setCellValue("班号");
CellRangeAddress name = new CellRangeAddress(0, 1, 1, 1);
sheet.addMergedRegion(name);
Cell namecell = rowtou.createCell(1);
namecell.setCellValue("姓名");
CellRangeAddress idCard = new CellRangeAddress(0, 1, 2, 2);
sheet.addMergedRegion(idCard);
Cell idCardCell = rowtou.createCell(2);
idCardCell.setCellValue("年龄");
CellRangeAddress family = new CellRangeAddress(0, 1, 3, 3);
sheet.addMergedRegion(family);
Cell familyCell = rowtou.createCell(3);
familyCell.setCellValue("性别");
//查询所有数据
ArrayList<TestExcel> testExcels = new ArrayList<>();
testExcels.add(new TestExcel("张三1","18","男","1"));
testExcels.add(new TestExcel("张三2","18","男","1"));
testExcels.add(new TestExcel("张三3","18","男","1"));
testExcels.add(new TestExcel("李四1","18","男","2"));
testExcels.add(new TestExcel("李四2","18","男","2"));
testExcels.add(new TestExcel("李四3","18","男","2"));
if (CollectionUtils.isNotEmpty(testExcels)){
//遍历集合 创建单元格 并设置值
int num = 0;
for (int i = 0; i < testExcels.size(); i++) {
TestExcel testExcel = testExcels.get(i);
XSSFRow dataRow = sheet.createRow(num + 2);
dataRow.createCell(0).setCellValue(testExcel.getXueHao() == null ? "" : testExcel.getXueHao() );
dataRow.createCell(1).setCellValue(testExcel.getName() == null ? "" : testExcel.getName());
dataRow.createCell(2).setCellValue(testExcel.getAge() == null ? "" : testExcel.getAge());
dataRow.createCell(3).setCellValue(testExcel.getSex() == null ? "" : testExcel.getSex());
num++;
}
}
// 设置下载时客户端Excel的名称
response.setContentType("application/octet-stream;charset=utf-8");
response.setHeader("Content-Disposition", "attachment;filename="
//该版本为2007-的版本
+ new String("测试".getBytes("UTF-8"), "iso-8859-1")
+ ".xlsx");
OutputStream ouputStream = response.getOutputStream();
wb.write(ouputStream);
ouputStream.flush();
ouputStream.close();
}
poi导出 模板导出
controller
/**
* poi导出 模板导出
*/
@GetMapping("/poiExcelExportMo")
public void poiExcelExportMo(HttpServletResponse response) throws IOException {
testExcelService.poiExcelExportMo(response);
}
impl
/**
* poi模板导出
* @param response
*/
void poiExcelExportMo(HttpServletResponse response) throws IOException;
//---------------------------
/**
* poi模板导出
* @param response
*/
@Override
public void poiExcelExportMo(HttpServletResponse response) throws IOException {
//确定编码格式
response.setCharacterEncoding("UTF-8");
//创建一个 workbook， 对应一个 excel文件
// HSSFWorkbook -> xls
// XSSFWorkbook -> xlsx
InputStream stream = new ClassPathResource("templates/excel/test.xlsx").getStream();
XSSFWorkbook workbook = new XSSFWorkbook(stream);
//在 workbook 里添加一个 sheet页
XSSFSheet sheet = workbook.getSheetAt(0);
//查询所有数据
ArrayList<TestExcel> testExcels = new ArrayList<>();
testExcels.add(new TestExcel("张三1","18","男","1"));
testExcels.add(new TestExcel("张三2","18","男","1"));
testExcels.add(new TestExcel("张三3","18","男","1"));
testExcels.add(new TestExcel("李四1","18","男","2"));
testExcels.add(new TestExcel("李四2","18","男","2"));
testExcels.add(new TestExcel("李四3","18","男","2"));
ArrayList<GrandeClass> grandeClasses = new ArrayList<>();
Map<String, List<TestExcel>> collectionMap = testExcels.stream()
.collect(Collectors.groupingBy(TestExcel::getXueHao));
for (String key : collectionMap.keySet()) {
GrandeClass grandeClass = new GrandeClass();
grandeClass.setBanhao(key);
List<TestExcel> excels = collectionMap.get(key);
ArrayList<Student> students = new ArrayList<>();
for (TestExcel excel : excels) {
Student student = new Student();
student.setName(excel.getName());
student.setSex(excel.getSex());
student.setAge(excel.getAge());
students.add(student);
grandeClass.setSudents(students);
}
grandeClasses.add(grandeClass);
}
//起始行
int firstRow = 2;
//结束行
int lastRow;
for (GrandeClass grandeClass : grandeClasses) {
int size = grandeClass.getSudents().size();
lastRow=firstRow+size-1;
//----班号start
CellRangeAddress cra = new CellRangeAddress(firstRow,lastRow,0,0);
sheet.addMergedRegion(cra);
Row row1 = sheet.createRow(firstRow);
Cell cell = row1.createCell(0);
cell.setCellValue(grandeClass.getBanhao());
//----班号end
//----总合计start
int i = 0;
for (Student student : grandeClass.getSudents()) {
Row row = null;
if(i != 0){
row = sheet.createRow(firstRow + i);
}else{
row = row1;
}
row.createCell(1).setCellValue(student.getName());
row.createCell(2).setCellValue(student.getAge());
row.createCell(3).setCellValue(student.getSex());
i++;
}
//----总合计end
firstRow = lastRow+1;
}
//设置下载时客户端excel的名称
response.setContentType("application/octet-stream;charset=utf-8");
response.setHeader("Content-Disposition", "attachment; filename="
//该版本为2007的版本
+ new String("个体商户信息导出表".getBytes("UTF-8"), "iso-8859-1")
+ ".xlsx");
ServletOutputStream outputStream = response.getOutputStream();
workbook.write(outputStream);
outputStream.flush();
outputStream.close();
}
poi导入 (合并单元格)
controller
@PostMapping("/poiMergeUpload")
public ResponseEntity poiMergeUpload(@RequestParam("file") MultipartFile file) throws Exception {
if (file.isEmpty()) {
return ResponseEntity.ok("文件不存在");
}
String filename = file.getOriginalFilename();
String upperCase = filename.substring(filename.lastIndexOf(".") + 1).toUpperCase();
if (!"XLS".equals(upperCase) && !"XLSX".equals(upperCase)) {
return ResponseEntity.ok("文件格式不对");
} else {
return testExcelService.poiMergeUpload(file);
}
}
impl
/**
* poi合并单元格导入
* @param file
* @return
* @throws IOException
*/
ResponseEntity poiMergeUpload(MultipartFile file) throws IOException;
//-------------------------
/**
* poi合并单元格导入
* @param file
* @return
*/
@Override
public ResponseEntity poiMergeUpload(MultipartFile file) throws IOException {
Workbook workbook = null;
String fileName = file.getOriginalFilename();
if (StringUtils.isBlank(fileName)) {
return ResponseEntity.status(500).body("文件名称不可为空！");
}
if (fileName.endsWith(".xls")) {
workbook = new HSSFWorkbook(file.getInputStream());
} else if (fileName.endsWith(".xlsx")) {
workbook = new XSSFWorkbook(file.getInputStream());
} else {
return ResponseEntity.status(500).body("该文件不是Excel文件");
}
Sheet sheet = workbook.getSheetAt(0);
int rowCount = sheet.getLastRowNum();
if (rowCount == 0) {
return ResponseEntity.status(500).body("文件内没有数据 请确认后重试");
}
ArrayList<TestExcel> list = new ArrayList<>();
for (int i = 1; i < rowCount; i++) {
Row row ;
TestExcel importExcle = new TestExcel();
row = sheet.getRow(i);
importExcle.setXueHao(row.getCell(0)+"");
for (int j = 0; j < 4; j++) {
if(isMergedRegion(sheet,i,j)){
String mergedRegionValue = getMergedRegionValue(sheet, i, 0);
importExcle.setXueHao(mergedRegionValue);
}
}
importExcle.setName(row.getCell(1)+"");
importExcle.setAge(row.getCell(2)+"");
importExcle.setSex(row.getCell(3)+"");
list.add(importExcle);
}
for (TestExcel testExcel : list) {
System.out.println(testExcel);
}
return null;
}
poi导出 合并单元格
controller
/**
* poi导出 合并单元格
*/
@GetMapping("/poiExcelExport")
public void poiExcelExport(HttpServletResponse response) throws IOException {
testExcelService.poiExcelExport(response);
}
impl
/**
* poi合并单元格导出
* @param response
*/
void poiExcelExport(HttpServletResponse response) throws IOException;
//----------------------------------
/**
* poi合并单元格
* @param response
*/
@Override
public void poiExcelExport(HttpServletResponse response) throws IOException {
//确定编码的格式
response.setCharacterEncoding("UTF-8");
//查询所有数据
ArrayList<TestExcel> testExcels = new ArrayList<>();
testExcels.add(new TestExcel("张三1","18","男","1"));
testExcels.add(new TestExcel("张三2","18","男","1"));
testExcels.add(new TestExcel("张三3","18","男","1"));
testExcels.add(new TestExcel("李四1","18","男","2"));
testExcels.add(new TestExcel("李四2","18","男","2"));
testExcels.add(new TestExcel("李四3","18","男","2"));
ArrayList<GrandeClass> grandeClasses = new ArrayList<>();
Map<String, List<TestExcel>> collectionMap = testExcels.stream()
.collect(Collectors.groupingBy(TestExcel::getXueHao));
for (String key : collectionMap.keySet()) {
GrandeClass grandeClass = new GrandeClass();
grandeClass.setBanhao(key);
List<TestExcel> excels = collectionMap.get(key);
ArrayList<Student> students = new ArrayList<>();
for (TestExcel excel : excels) {
Student student = new Student();
student.setName(excel.getName());
student.setSex(excel.getSex());
student.setAge(excel.getAge());
students.add(student);
grandeClass.setSudents(students);
}
grandeClasses.add(grandeClass);
}
//创建excel文件
XSSFWorkbook wb = new XSSFWorkbook();
//创建sheet页
XSSFSheet sheet = wb.createSheet("表");
// //创建标题行
// XSSFRow titleRow = sheet.createRow(0);
// titleRow.createCell(0).setCellValue("班号");
// titleRow.createCell(1).setCellValue("姓名");
// titleRow.createCell(2).setCellValue("年龄");
// titleRow.createCell(3).setCellValue("性别");
CellRangeAddress huhao = new CellRangeAddress(0, 1, 0, 0);
sheet.addMergedRegion(huhao);
Row rowtou = sheet.createRow(0);
Cell huhaocell = rowtou.createCell(0);
huhaocell.setCellValue("班号");
CellRangeAddress name = new CellRangeAddress(0, 1, 1, 1);
sheet.addMergedRegion(name);
Cell namecell = rowtou.createCell(1);
namecell.setCellValue("姓名");
CellRangeAddress idCard = new CellRangeAddress(0, 1, 2, 2);
sheet.addMergedRegion(idCard);
Cell idCardCell = rowtou.createCell(2);
idCardCell.setCellValue("年龄");
CellRangeAddress family = new CellRangeAddress(0, 1, 3, 3);
sheet.addMergedRegion(family);
Cell familyCell = rowtou.createCell(3);
familyCell.setCellValue("性别");
//起始行
int firstRow = 2;
//结束行
int lastRow;
for (GrandeClass grandeClass : grandeClasses) {
int size = grandeClass.getSudents().size();
lastRow=firstRow+size-1;
//----班号start
CellRangeAddress cra = new CellRangeAddress(firstRow,lastRow,0,0);
sheet.addMergedRegion(cra);
Row row1 = sheet.createRow(firstRow);
Cell cell = row1.createCell(0);
cell.setCellValue(grandeClass.getBanhao());
//----班号end
//----总合计start
int i = 0;
for (Student student : grandeClass.getSudents()) {
Row row = null;
if(i != 0){
row = sheet.createRow(firstRow + i);
}else{
row = row1;
}
row.createCell(1).setCellValue(student.getName());
row.createCell(2).setCellValue(student.getAge());
row.createCell(3).setCellValue(student.getSex());
i++;
}
//----总合计end
firstRow = lastRow+1;
}
// 设置下载时客户端Excel的名称
response.setContentType("application/octet-stream;charset=utf-8");
response.setHeader("Content-Disposition", "attachment;filename="
//该版本为2007-的版本
+ new String("测试".getBytes("UTF-8"), "iso-8859-1")
+ ".xlsx");
OutputStream ouputStream = response.getOutputStream();
wb.write(ouputStream);
ouputStream.flush();
ouputStream.close();
}
测试数据
@Test
public void testshuju(){
ArrayList<TestExcel> testExcels = new ArrayList<>();
testExcels.add(new TestExcel("张三1","18","男","1"));
testExcels.add(new TestExcel("张三2","18","男","1"));
testExcels.add(new TestExcel("张三3","18","男","1"));
testExcels.add(new TestExcel("李四1","18","男","2"));
testExcels.add(new TestExcel("李四2","18","男","2"));
testExcels.add(new TestExcel("李四3","18","男","2"));
for (TestExcel testExcel : testExcels) {
System.out.println(testExcel);
}
ArrayList<GrandeClass> grandeClasses = new ArrayList<>();
Map<String, List<TestExcel>> collectionMap = testExcels.stream()
.collect(Collectors.groupingBy(TestExcel::getXueHao));
for (String key : collectionMap.keySet()) {
GrandeClass grandeClass = new GrandeClass();
grandeClass.setBanhao(key);
List<TestExcel> excels = collectionMap.get(key);
ArrayList<Student> students = new ArrayList<>();
for (TestExcel excel : excels) {
Student student = new Student();
student.setName(excel.getName());
student.setSex(excel.getSex());
student.setAge(excel.getAge());
students.add(student);
grandeClass.setSudents(students);
}
grandeClasses.add(grandeClass);
}
System.out.println(grandeClasses);
}
所需的工具方法
/**
* 判断指定的单元格是否是合并单元格
* @param sheet
* @param row 行下标
* @param column 列下标
* @return
*/
private boolean isMergedRegion(Sheet sheet,int row ,int column) {
int sheetMergeCount = sheet.getNumMergedRegions();
for (int i = 0; i < sheetMergeCount; i++) {
CellRangeAddress range = sheet.getMergedRegion(i);
int firstColumn = range.getFirstColumn();
int lastColumn = range.getLastColumn();
int firstRow = range.getFirstRow();
int lastRow = range.getLastRow();
if(row >= firstRow && row <= lastRow){
if(column >= firstColumn && column <= lastColumn){
return true;
}
}
}
return false;
}
/**
* 获取合并单元格的值
* @param sheet
* @param row
* @param column
* @return
*/
public String getMergedRegionValue(Sheet sheet ,int row , int column){
int sheetMergeCount = sheet.getNumMergedRegions();
for(int i = 0 ; i < sheetMergeCount ; i++){
CellRangeAddress ca = sheet.getMergedRegion(i);
int firstColumn = ca.getFirstColumn();
int lastColumn = ca.getLastColumn();
int firstRow = ca.getFirstRow();
int lastRow = ca.getLastRow();
if(row >= firstRow && row <= lastRow){
if(column >= firstColumn && column <= lastColumn){
Row fRow = sheet.getRow(firstRow);
Cell fCell = fRow.getCell(firstColumn);
return getCellValue(fCell) ;
}
}
}
return null ;
}
public String getCellValue(Cell cell) {
String value = "";
if (cell != null) {
// 以下是判断数据的类型
switch (cell.getCellType()) {
// 数字
case HSSFCell.CELL_TYPE_NUMERIC:
value = cell.getNumericCellValue() + "";
if (HSSFDateUtil.isCellDateFormatted(cell)) {
Date date = cell.getDateCellValue();
if (date != null) {
value = new SimpleDateFormat("yyyy-MM-dd").format(date);
} else {
value = "";
}
} else {
value = new DecimalFormat("0").format(cell.getNumericCellValue());
}
break;
// 字符串
case HSSFCell.CELL_TYPE_STRING:
value = cell.getStringCellValue();
break;
// Boolean
case HSSFCell.CELL_TYPE_BOOLEAN:
value = cell.getBooleanCellValue() + "";
break;
// 公式
case HSSFCell.CELL_TYPE_FORMULA:
value = cell.getCellFormula() + "";
break;
// 空值
case HSSFCell.CELL_TYPE_BLANK:
value = "";
break;
// 故障
case HSSFCell.CELL_TYPE_ERROR:
value = "非法字符";
break;
default:
value = "未知类型";
break;
}
}
return value.trim();
}
entity实体类
/**
* @author suizh
* @version 1.0
* @date 2021/9/20 22:43
*/
@Data
@AllArgsConstructor
@NoArgsConstructor
public class TestExcel {
private String name ;
private String age ;
private String sex ;
private String xueHao ;
}
bo
/**
* @author suizh
* @version 1.0
* @date 2021/9/21 0:37
*/
@Data
@AllArgsConstructor
@NoArgsConstructor
public class GrandeClass {
private String banhao;
private List<Student> sudents;
}
/**
* @author suizh
* @version 1.0
* @date 2021/9/21 0:37
*/
@Data
@AllArgsConstructor
@NoArgsConstructor
public class Student {
private String name ;
private String age ;
private String sex ;
}
1万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
