要实现的效果如图所示：excel.xlsx
1.pom中配置引用
com.alanpoi alanpoi-analysis 1.3.4 javax.servlet servlet-api org.apache.poi poi-ooxml 4.1.2 org.apache.poi poi-ooxml-schemas 4.1.2
2.导出工具类
package cn.com.sinosafe.core.quotation.utils;
import com.sinosoft.sysframework.common.util.StringUtils;
import com.sinosoft.sysframework.exception.BusinessException;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.springframework.web.multipart.MultipartFile;
import java.io.*;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Date;
public class ExcelUtils {
public static String[][] getData(File file) throws FileNotFoundException,
IOException {
return getData(file, 1);
}
public static String[][] getData(File file, int ignoreRows)
throws FileNotFoundException, IOException {
ArrayList result = new ArrayList();
int rowSize = 0;
BufferedInputStream in = new BufferedInputStream(new FileInputStream(
file));
// 打开HSSFWorkbook
POIFSFileSystem fs = new POIFSFileSystem(in);
HSSFWorkbook wb = null;
try{
wb = new HSSFWorkbook(fs);
}catch (Exception e) {
throw new BusinessException("获取Excel数据失败,在保存Excel之前请注意将焦点选中在没有下拉选项的列!",true);
}
HSSFCell cell = null;
SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");
StringBuilder value = new StringBuilder();
for (int sheetIndex = 0; sheetIndex < wb.getNumberOfSheets(); sheetIndex++) {
HSSFSheet st = wb.getSheetAt(sheetIndex);
// 第一行为标题，不取
for (int rowIndex = ignoreRows; rowIndex <= st.getLastRowNum(); rowIndex++) {
HSSFRow row = st.getRow(rowIndex);
if (row == null) {
continue;
}
int tempRowSize = row.getLastCellNum() + 1;
if (tempRowSize > rowSize) {
rowSize = tempRowSize;
}
String[] values = new String[rowSize];
Arrays.fill(values, "");
boolean hasValue = false;
for (short columnIndex = 0; columnIndex <= row.getLastCellNum(); columnIndex++) {
value.setLength(0);
cell = row.getCell(columnIndex);
if (cell != null) {
switch (cell.getCellType()) {
case STRING:
value.append(cell.getStringCellValue());
break;
case NUMERIC:
if (HSSFDateUtil.isCellDateFormatted(cell)) {
Date date = cell.getDateCellValue();
if (date != null) {
value.append(dateFormat.format(date));
}
} else {
double d = cell.getNumericCellValue();
if(d != ((int)d)){
value.append(d);
}else{
value.append((int)d);
}
}
break;
case FORMULA:
// 导入时如果为公式生成的数据则无值
try {
cell.setCellType(CellType.STRING);
} catch (Exception e) {
}
if (cell.getStringCellValue().equals("")) {
value.append("");
} else {
value.append(cell.getStringCellValue());
}
break;
case BLANK:
break;
case ERROR:
break;
case BOOLEAN:
value
.append((cell.getBooleanCellValue() == true ? "Y"
: "N"));
break;
default:
value.setLength(0);
}
}
String s = value.toString().trim();
if (columnIndex == 0 && s.equals("")) {//若第一列为空，则向后判断5列 若都为空则不导入该行数据
if (row.getCell(new Short("1").shortValue()) == null
&& row.getCell(new Short("2").shortValue()) == null
&& row.getCell(new Short("3").shortValue()) == null
&& row.getCell(new Short("4").shortValue()) == null
&& row.getCell(new Short("5").shortValue()) == null) {
break;
}
}
values[columnIndex] = StringUtils.rightTrim(value
.toString());
hasValue = true;
//到每行的第五列的时候开始判断前5列是否都为空,若都为空则不导入该行数据 (该方法需要与以上方法同时使用)
if(columnIndex == 4){
if("".equals(values[0]) && "".equals(values[1])
&& "".equals(values[2]) && "".equals(values[3])
&& "".equals(values[4])){
hasValue = false;
break;
}
}
}
if (hasValue) {
result.add(values);
}
}
}
in.close();
String[][] returnArray = new String[result.size()][rowSize];
for (int i = 0; i < returnArray.length; i++) {
returnArray[i] = (String[]) result.get(i);
}
return returnArray;
}
public static String[][] getData(File file, int ignoreRows, int index)
throws FileNotFoundException, IOException {
ArrayList result = new ArrayList();
int rowSize = 0;
BufferedInputStream in = new BufferedInputStream(new FileInputStream(
file));
// 打开HSSFWorkbook
POIFSFileSystem fs = new POIFSFileSystem(in);
HSSFWorkbook wb = new HSSFWorkbook(fs);
HSSFCell cell = null;
HSSFSheet st = wb.getSheetAt(index - 1);
SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");
DecimalFormat decimalFormat = new DecimalFormat("0.00");
// 第一行为标题，不取
for (int rowIndex = ignoreRows; rowIndex <= st.getLastRowNum(); rowIndex++) {
HSSFRow row = st.getRow(rowIndex);
if (row == null) {
continue;
}
int tempRowSize = row.getLastCellNum() + 1;
if (tempRowSize > rowSize) {
rowSize = tempRowSize;
}
String[] values = new String[rowSize];
Arrays.fill(values, "");
boolean hasValue = false;
StringBuilder value = new StringBuilder();
for (short columnIndex = 0; columnIndex <= row.getLastCellNum(); columnIndex++) {
value.setLength(0);
cell = row.getCell(columnIndex);
if (cell != null) {
switch (cell.getCellType()) {
case STRING:
value.append(cell.getStringCellValue());
break;
case NUMERIC:
if (HSSFDateUtil.isCellDateFormatted(cell)) {
Date date = cell.getDateCellValue();
if (date != null) {
value.append(dateFormat.format(date));
}
} else {
value.append(decimalFormat.format(cell
.getNumericCellValue()));
}
break;
case FORMULA:
// 导入时如果为公式生成的数据则无值
if (cell.getStringCellValue().equals("")) {
value.append(cell.getNumericCellValue());
} else {
value.append(cell.getStringCellValue());
}
break;
case BLANK:
break;
case ERROR:
break;
case BOOLEAN:
value.append(cell.getBooleanCellValue() == true ? "Y"
: "N");
break;
default:
value.setLength(0);
}
}
if (columnIndex == 0 && value.toString().trim().equals("")) {
break;
}
values[columnIndex] = StringUtils.rightTrim(value.toString());
hasValue = true;
}
if (hasValue) {
result.add(values);
}
}
in.close();
String[][] returnArray = new String[result.size()][rowSize];
for (int i = 0; i < returnArray.length; i++) {
returnArray[i] = (String[]) result.get(i);
}
return returnArray;
}
public static File multipartFileToFile(File file, MultipartFile multipartFile){
OutputStream out = null;
try{
//获取文件流，以文件流的方式输出到新文件
out = new FileOutputStream(file);
byte[] ss = multipartFile.getBytes();
for(int i = 0; i < ss.length; i++){
out.write(ss[i]);
}
}catch(IOException e){
e.printStackTrace();
}finally {
if (out != null){
try {
out.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
return file;
}
}
3.实体层：
package cn.com.sinosafe.core.quotation.utils;
import com.alanpoi.analysis.common.enums.AlanColor;
import com.alanpoi.analysis.excel.annotation.ExcelColumn;
import com.alanpoi.analysis.excel.annotation.ExcelSheet;
import com.alanpoi.common.annotation.DateFormat;
import com.alanpoi.common.annotation.NumFormat;
import lombok.Data;
import lombok.Getter;
import lombok.Setter;
import java.math.BigDecimal;
import java.util.Date;
@ExcelSheet(name = "测试1", backColor = AlanColor.GREEN, font = "宋体", fontSize = 25)
@Data
public class ExportVO1 {
private String sheetName;
@ExcelColumn(name = "名称", width = 32, link = "${url}")
private String name;
@ExcelColumn(name = "值")
private BigDecimal value;
@ExcelColumn(name = "金额")
@NumFormat(value = "0000.00##")
private BigDecimal amount;
@ExcelColumn(name = "时间格式化")
@DateFormat(value = "yyyy-MM-dd hh:mm:ss")
private Date dateTime;
@DateFormat
@ExcelColumn(name = "日期格式化")
private java.sql.Date date;
@ExcelColumn(isExist = false)
private String url;
}
package cn.com.sinosafe.core.quotation.utils;
import com.alanpoi.analysis.common.enums.AlanColor;
import com.alanpoi.analysis.excel.annotation.ExcelColumn;
import com.alanpoi.analysis.excel.annotation.ExcelSheet;
import com.alanpoi.common.annotation.DateFormat;
import com.alanpoi.common.annotation.NumFormat;
import lombok.Data;
import java.math.BigDecimal;
import java.util.Date;
@ExcelSheet(name = "测试2", backColor = AlanColor.GOLD, font = "宋体", fontSize = 25,index = 1)
@Data
public class ExportVO2 {
@ExcelColumn(name = "名称", width = 32, link = "${url}")
private String name;
@ExcelColumn(name = "值")
private String value;
@ExcelColumn(name = "金额")
@NumFormat(value = "0000.00##")
private BigDecimal amount;
@ExcelColumn(name = "时间格式化")
@DateFormat(value = "yyyy-MM-dd hh:mm:ss")
private Date dateTime;
@DateFormat
@ExcelColumn(name = "日期格式化")
private java.sql.Date date;
@ExcelColumn(isExist = false)
private String url;
}
4.控制层
@GetMapping("/testExcel")
public void testExcel() throws IOException {
List list = new ArrayList<>();
List list2 = new ArrayList<>();
for (int i = 0; i < 500; i++) {
ExportVO1 exportVO = new ExportVO1();
exportVO.setName("name" + i);
exportVO.setValue(new BigDecimal(123.11 + i * 0.09));
exportVO.setAmount(new BigDecimal(6666.666 + i * 10));
exportVO.setDate(new java.sql.Date(132324343 + i * 100));
exportVO.setDateTime(new java.util.Date());
exportVO.setUrl("http://www.baidu.com");
list.add(exportVO);
ExportVO2 export2VO = new ExportVO2();
export2VO.setName("name" + i);
export2VO.setValue("value" + i);
export2VO.setAmount(new BigDecimal(6666.666 + i * 10));
export2VO.setDate(new java.sql.Date(132324343 + i * 100));
export2VO.setDateTime(new java.util.Date());
export2VO.setUrl("http://www.baidu.com");
list2.add(export2VO);
}
Map, Collection> map = new HashMap<>();
map.put(ExportVO1.class, list);
map.put(ExportVO2.class, list2);
//调用获取workbook对象；也可以直接调用exportByMultiSheet方法导出到浏览器
Workbook workbook = ExcelExportUtil.getWorkbookByMultiSheet(map);
OutputStream fileOs=new FileOutputStream("C:\work\excel.xlsx");
workbook.write(fileOs);
fileOs.flush();
fileOs.close();
workbook.close();
}
5.Postman测试：
http://localhost:8568/testExcel
引用：
1.你不能不知道的INFORMATION_SCHEMA:
https://blog.csdn.net/duleilewuhen/article/details/123749352
2.poi 颜色对照表
https://blog.csdn.net/xcc_2269861428/article/details/89208981
