要实现的效果如图所示：excel.xlsx
1.查询数据库表信息sql：
select
( @i := @i + 1 ) as '序号',-- 序号
TABLE_NAME as '表名',-- 表名
IFNULL( table_comment, table_name ) AS '注释' -- 表注释
FROM
information_schema.TABLES,
( SELECt @i := 0 ) as i
WHERe
TABLE_SCHEMA = 'nonauto_quotation'
and (TABLE_NAME like 'nbz%' or table_name like 't_%')
ORDER BY table_name;
2.查询表字段结构信息sql：
select table_name, COLUMN_NAME 字段名, -- 数据类型包括类型和长度 DATA_TYPE 字段类型, IS_NULLABLE 是否非空, if(extra = 'auto_increment','是','') AS 是否自增, ifnull(column_default,'') AS 默认值, COLUMN_COMMENT 注释, if(column_key = 'pri','PK','') AS 是否主键 from INFORMATION_SCHEMA.columns where -- pc560为数据库名称，到时候只需要修改成你要导出表结构的数据库即可 table_schema ='nonauto_quotation' and (table_name like 'nbz%' or table_name like 't_%');
3.pom中配置引用
com.alanpoi alanpoi-analysis 1.3.6 org.apache.poi poi-ooxml 4.1.2 org.apache.poi poi-ooxml-schemas 4.1.2 org.projectlombok lombok
注意：普通导出多sheet页用左边1.3.4版本即可
4.导出工具类
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;
}
}
5.实体层：
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 lombok.Data;
@ExcelSheet(name = "测试1", backColor = AlanColor.ROSE, font = "宋体", fontSize = 10,rowHeight=22)
@Data
public class TableVO {
//表名
@ExcelColumn(name = "表名",isExist=false)
private String tableName;
//字段名
@ExcelColumn(name = "字段名",width = 18)
private String columnName;
//字段类型
@ExcelColumn(name = "字段类型",width = 12)
private String dataType;
//是否非空
@ExcelColumn(name = "是否非空",width = 12)
private String isNullAble;
//是否自增
@ExcelColumn(name = "是否自增",width = 12)
private String autoIncrement;
//默认值
@ExcelColumn(name = "默认值",width = 12)
private String columnDefault;
//注释
@ExcelColumn(name = "注释",width = 50)
private String columnComment;
//是否主键
@ExcelColumn(name = "是否主键", width = 12)
private String columnKey;
}
6.控制请求层
package cn.com.sinosafe.core.quotation.controller;
import cn.com.sinosafe.core.quotation.service.quotation.QuotationDataService;
import cn.com.sinosafe.core.quotation.utils.TableVO;
import com.alanpoi.analysis.common.ExportMultipleSheetParam;
import com.alanpoi.analysis.common.utils.ExcelExportUtil;
import org.apache.poi.ss.usermodel.Workbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.util.*;
import java.util.stream.Collectors;
@CrossOrigin
@RestController
public class QueryController {
private final static Logger logger = LoggerFactory.getLogger(QueryController.class);
@Autowired
QuotationDataService quotationDataService;
@GetMapping("/testExcel")
public void testExcel() throws IOException {
//查询这个表结构信息
List tableList = quotationDataService.queryTables();
//tableList.stream().sorted(Comparator.comparing(TableVO::getTableName)).collect(Collectors.toList());
//需要分类不同的表，然后put到map
//TODO 处理第一个sheet，目录，也可以不处理，导出来之后，手动处理第一个目录
//这个循环就已经把后面所有的表信息赋值到excel
ExportMultipleSheetParam param = new ExportMultipleSheetParam();
Map> map2 = tableList.stream().collect(Collectors.groupingBy(item -> item.getTableName()));
//获取map键集合，new实体
Iterator iter = map2.keySet().iterator();
int i = 0;
while (iter.hasNext()) {
String key = iter.next();
List value = map2.get(key);
Map, Collection> map = new HashMap<>();
TableVO vo = new TableVO();
param.put(i++, value.stream().findFirst().get().getTableName(), vo.getClass(), value);
}
//调用获取workbook对象；也可以直接调用exportByMultiSheet方法导出到浏览器
Workbook workbook = ExcelExportUtil.getByMultiSheet(param);
OutputStream fileOs = new FileOutputStream("C:\work\excel.xlsx");
workbook.write(fileOs);
fileOs.flush();
fileOs.close();
workbook.close();
}
}
7.Postman测试：
http://localhost:8568/testExcel
