数据库导入导出表头
之前的工具类GenerateExcelToFile新增两个导出这种二级表头方法
package com.njry.utils;
import cn.hutool.core.util.IdUtil;
import com.njry.config.FileProperties;
import com.njry.exception.BadRequestException;
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.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.util.IOUtils;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
public class GenerateExcelToFile {
public static final String SYS_TEM_DIR = System.getProperty("java.io.tmpdir") + File.separator;
/**
* 根据导出表在后面添加每个原子的基本值和目标值
* @param list 数据库查询出来的信息
* @param atomMbListAllString 后面动态添加的原子(要动态生成二级表头) 原子id,模版名称,维度名称三个用下划线分割,在导入的时候取数据用 例如:AT10681_0627-2_测试,AT10600_水电费debug_按开户厅
* @param tableHead 基本表头 例如list 三个写死的表头:组织ID,组织名称,组织等级
* @param type 导出的excel类型 1 xlsx 2 xls
* @param response
* @throws IOException
*/
public static void dataToExcel(List<Map<String, Object>> list, List<String> atomMbListAllString, List<String> tableHead, int type, HttpServletResponse response) throws IOException{
String tempPath = SYS_TEM_DIR + IdUtil.fastSimpleUUID() + ".xlsx";
File file = new File(tempPath);
Workbook wb = null;
if(type == 1){
wb = new XSSFWorkbook();
}
if(type == 2){
wb = new HSSFWorkbook();
}
Workbook exportWorkbook = exportMultiExcel(wb, list, atomMbListAllString, tableHead, type);
// 临时文件 写出流
FileOutputStream outStream = new FileOutputStream(file);
// 写入Workbook到文件
exportWorkbook.write(outStream);
// 也可以通过流获取大小
// long size = outStream.getChannel().size();
// 强制刷新文件流,确保所有数据都被写入到文件中
outStream.flush();
outStream.close();
//response为HttpServletResponse对象
response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=utf-8");
//test.xls是弹出下载对话框的文件名,不能为中文,中文请自行编码
response.setHeader("Content-Disposition", "attachment;filename=file.xlsx");
ServletOutputStream out = response.getOutputStream();
FileInputStream fileInputStream = new FileInputStream(file);
IOUtils.copy(fileInputStream,out);
file.deleteOnExit();
fileInputStream.close();
}
/**
*
* @param wb 操作的Workbook
* @param list 数据库查询出来的信息
* @param atomMbListAllString 后面动态添加的原子(要动态生成二级表头)
* @param tableHead 基本表头
* @param type 导出的excel类型 1 xlsx 2 xls
* @return
*/
public static Workbook exportMultiExcel(Workbook wb,List<Map<String, Object>> list, List<String> atomMbListAllString, List<String> tableHead, int type){
HSSFWorkbook HSSwb = null;
XSSFWorkbook XSSwb = null;
if(type == 1){
XSSwb = (XSSFWorkbook)wb;
}
if(type == 2){
HSSwb = (HSSFWorkbook)wb;
}
// 创建样式
CellStyle style = wb.createCellStyle();
style.setAlignment(HorizontalAlignment.CENTER); // 水平居中
style.setVerticalAlignment(VerticalAlignment.CENTER); // 垂直居中
if(type == 1){
XSSFSheet sheet = XSSwb.createSheet();
// 处理sheet的表头
// XSSFRow row = sheet.createRow((short) 0);(表头两行)
// 合并第一行和第二行的第一个单元格
// int startrow = 0;
// int endrow = 1;
// int startcell = 1;
// int endcell = 1;
// sheet.addMergedRegion(new CellRangeAddress(startrow, endrow, startcell, endcell));
// 处理表头(合并第一行和第二行)
XSSFRow row = sheet.createRow((short) 0);
XSSFRow row1 = sheet.createRow((short) 1);
int size = tableHead.size();//固定表头长度
for (int i = 0; i < size; i++) {
int startrow = 0;
int endrow = 1;
int startcell = i;
int endcell = i;
// 在合并单元格第一个写内容
XSSFCell cell = row.createCell(i);
cell.setCellValue(tableHead.get(i));
sheet.addMergedRegion(new CellRangeAddress(startrow, endrow, startcell, endcell));
cell.setCellStyle(style);
}
int asyncSize = atomMbListAllString.size() * 2;//动态表头长度
for (int j = size; j < asyncSize+size; j += 2) {
int startrow = 0;
int endrow = 0;
int startcell = j;
int endcell = j + 1;
// 在合并单元格第一个写内容
XSSFCell cell = row.createCell(j);
int circulationNum = (j - size) / 2;//循环次数
cell.setCellValue(atomMbListAllString.get(circulationNum));
// 合并第二行写固定的基本值和目标值
XSSFCell cellMerge1 = row1.createCell(j);
cellMerge1.setCellValue("基本值");
XSSFCell cellMerge2 = row1.createCell(j+1);
cellMerge2.setCellValue("目标值");
sheet.addMergedRegion(new CellRangeAddress(startrow, endrow, startcell, endcell));
cellMerge1.setCellStyle(style);
cellMerge2.setCellStyle(style);
cell.setCellStyle(style);
}
// 写入数据(固定表头下面内容)
for (int m = 0; m < list.size(); m++) {
// 已经添加的两个行
XSSFRow rowTemp = sheet.createRow(m + 2);
// 每一行数据的map,map对应的key就是固定表头
Map<String, Object> stringObjectMap = list.get(m);
Cell dataCell = null;
for (int n = 0; n < tableHead.size(); n++) {
dataCell = rowTemp.createCell(n);
dataCell.setCellValue(StringUtils.notEmpty(stringObjectMap.get(tableHead.get(n))));
}
}
}
if(type == 2){
HSSFSheet sheet = HSSwb.createSheet();
// 处理sheet的表头
// XSSFRow row = sheet.createRow((short) 0);(表头两行)
// 合并第一行和第二行的第一个单元格
// int startrow = 0;
// int endrow = 1;
// int startcell = 1;
// int endcell = 1;
// sheet.addMergedRegion(new CellRangeAddress(startrow, endrow, startcell, endcell));
// 处理表头(合并第一行和第二行)
HSSFRow row = sheet.createRow((short) 0);
HSSFRow row1 = sheet.createRow((short) 1);
int size = tableHead.size();//固定表头长度
for (int i = 0; i < size; i++) {
int startrow = 0;
int endrow = 1;
int startcell = i;
int endcell = i;
// 在合并单元格第一个写内容
HSSFCell cell = row.createCell(i);
cell.setCellValue(tableHead.get(i));
sheet.addMergedRegion(new CellRangeAddress(startrow, endrow, startcell, endcell));
cell.setCellStyle(style);
}
int asyncSize = atomMbListAllString.size() * 2;//动态表头长度
for (int j = size; j < asyncSize+size; j += 2) {
int startrow = 0;
int endrow = 1;
int startcell = j;
int endcell = j + 1;
// 在合并单元格第一个写内容
HSSFCell cell = row.createCell(j);
int circulationNum = (j - size) / 2;//循环次数
cell.setCellValue(atomMbListAllString.get(circulationNum));
// 合并第二行写固定的基本值和目标值
HSSFCell cellMerge1 = row1.createCell(j);
cellMerge1.setCellValue("基本值");
HSSFCell cellMerge2 = row1.createCell(j+1);
cellMerge2.setCellValue("目标值");
sheet.addMergedRegion(new CellRangeAddress(startrow, endrow, startcell, endcell));
cellMerge1.setCellStyle(style);
cellMerge2.setCellStyle(style);
cell.setCellStyle(style);
}
// 写入数据(固定表头下面内容)
for (int m = 0; m < list.size(); m++) {
// 已经添加的两个行
HSSFRow rowTemp = sheet.createRow(m + 2);
// 每一行数据的map,map对应的key就是固定表头
Map<String, Object> stringObjectMap = list.get(m);
Cell dataCell = null;
for (int n = 0; n < tableHead.size(); n++) {
dataCell = rowTemp.createCell(n);
dataCell.setCellValue(StringUtils.notEmpty(stringObjectMap.get(tableHead.get(n))));
}
}
}
return wb;
}
/**
*
* @param list 数据库查询出来的信息
* @param tableHead 数据库的表头(简单表头)
* @param dirPath 保存的文件路径
* @param fileSuffix 生成的excel格式后缀
* @param separator 生成的excel带水印格要多加一层文件路径
* @throws IOException
*/
public static Map<String, Object> dataToExcel(List<Map<String, Object>> list,List<String> tableHead,String dirPath, String fileSuffix, String separator) throws IOException {
Map<String, Object> resultMap = new HashMap<>();
Workbook wb = null;
FileInputStream tempInput = null;
String watermarkPath = dirPath + "watermarkPath" + separator;
int type = 3;//默认用type区分一个sheet有多少行
if("xlsx".equals(fileSuffix)){
wb = new XSSFWorkbook();
type = 1;
}
if("xls".equals(fileSuffix)){
wb = new HSSFWorkbook();
type = 2;
}
Workbook exportWorkbook = export(wb, list, tableHead, type);
String attachmentId = IdUtil.simpleUUID();
String fileSize = "";
String to_file_name = attachmentId + "." + fileSuffix; // 结果文件名称
// 判断保存的文件路径是否存在,不存在就创建
File outFileExist = new File(dirPath);
if(!outFileExist.exists()){
outFileExist.mkdirs();
}
File outFile = new File(dirPath, to_file_name);
try {
FileOutputStream outStream = new FileOutputStream(outFile);
// 写入Workbook到文件
exportWorkbook.write(outStream);
// 也可以通过流获取大小
// long size = outStream.getChannel().size();
// 强制刷新文件流,确保所有数据都被写入到文件中
outStream.flush();
// 获取文件对象
File outputFile = new File(dirPath, to_file_name);
long length = outputFile.length();
fileSize = length + " bytes";
outStream.close();
} catch (Exception e) {
throw new BadRequestException("导出结果文件异常:" + e);
}
// 将有数据的excel的文件再加水印
File tempFile = new File(dirPath, to_file_name);
tempInput = new FileInputStream(tempFile);
XSSFWorkbook xSSFWorkbook = new XSSFWorkbook(tempInput);
// 将导出的数据加水印放到另一个文件watermarkPath里面
File watermarkFileExist = new File(watermarkPath, to_file_name);
PoiSecurity.addWatermarkToXlsx(new String[]{"test"},xSSFWorkbook,watermarkFileExist);
xSSFWorkbook.close();
tempInput.close();
// 终止后删除临时文件
tempFile.deleteOnExit();
// 处理文件信息返回
resultMap.put("attachmentId",attachmentId);
resultMap.put("fileName",to_file_name);
resultMap.put("fileType","excel");
resultMap.put("fileExtension",fileSuffix);
resultMap.put("fileSize",fileSize);
resultMap.put("attachmentPath",dirPath);
return resultMap;
}
/**
*
* @param wb 操作的Workbook
* @param list 数据库查询出来的信息
* @param tableHead 数据库的表头(简单表头)
* @param type excel类型 xlsx 1 xls 2 -------区分sheet最大告诉
* @return
*/
public static Workbook export(Workbook wb,List<Map<String, Object>> list, List<String> tableHead,int type) {
HSSFWorkbook HSSwb = null;
XSSFWorkbook XSSwb = null;
// 不定义sheet名字,自生成
// Excel 2003及更早的版本中,行数上限是65,536行
// 2007开始,行数上限增加到了1,048,576行
int maxRow = 49999;//去除一个表头行
if(type == 1){
maxRow = 1048575;//去除一个表头行
XSSwb = (XSSFWorkbook)wb;
}
if(type == 2){
maxRow = 65535;//去除一个表头行
HSSwb = (HSSFWorkbook)wb;
}
maxRow = 49999;//去除一个表头行(无论啥格式默认都是50000一个sheet)
// 处理数据需要多少个sheet
int size = list.size();
int result = size / maxRow + 1;
if(result == 0){
result = 1;
}
// 循环sheet
for (int i = 0; i < result; i++) {
if(type == 1){
XSSFSheet sheet = XSSwb.createSheet();
// 处理每个sheet的表头
XSSFRow row = sheet.createRow((short) 0);
Cell cell = null;
for (int j = 0; j < tableHead.size(); j++) {
cell = row.createCell(j);
// cell.setCellStyle(headStyle);
cell.setCellValue(tableHead.get(j));
}
// 写入数据
for (int n = 0 + maxRow * i; n < maxRow * (i + 1); n++) {
// 判断数据list的大小是否大于要创建的行
if(size - 1 >= n ){//下面list.get(n)就取不到数据,不应该继续创建行 size 14 get(n)时候 n只能到13
row = sheet.createRow(n % maxRow + 1);
Cell dataCell = null;
for (int m = 0; m < tableHead.size(); m++) {
dataCell = row.createCell(m);
dataCell.setCellValue(StringUtils.notEmpty(list.get(n).get(tableHead.get(m))));
}
}
}
}
if(type == 2){
HSSFSheet sheet = HSSwb.createSheet();
// 处理每个sheet的表头
HSSFRow row = sheet.createRow((short) 0);
Cell cell = null;
for (int j = 0; j < tableHead.size(); j++) {
cell = row.createCell(j);
// cell.setCellStyle(headStyle);
cell.setCellValue(tableHead.get(j));
}
// 写入数据
for (int n = 0 + maxRow * i; n < maxRow * (i + 1); n++) {
// 判断数据list的大小是否大于要创建的行
if(size - 1 >= n ){//下面list.get(n)就取不到数据,不应该继续创建行 size 14 get(n)时候 n只能到13
row = sheet.createRow(n % maxRow + 1);
Cell dataCell = null;
for (int m = 0; m < tableHead.size(); m++) {
dataCell = row.createCell(m);
dataCell.setCellValue(StringUtils.notEmpty(list.get(n).get(tableHead.get(m))));
}
}
}
}
}
return wb;
}
}
处理之前导出的模板,把用户输入的基本值和目标值再放回数据库里
解析excel保存到表里(贴一个实现层代码)
@Override
@Transactional(rollbackFor = Exception.class)
public void excelImportBatch(MultipartFile file,Long sceneId,String month) throws Exception {
// 保存在 T_ATOM_MB_CFG_ITEM 但是需要目标值,
// 前端传了场景id,以及excel里面原子id_模式名称_维度名称 有atomId,模式名称,维度名称
// 因此要根据atomId,模式名称,维度名称以及场景id 获取目标id
String extension = file.getOriginalFilename().substring(file.getOriginalFilename().lastIndexOf(".") + 1)
.toLowerCase(); // 获取输入流
try (java.io.InputStream fis = file.getInputStream();
Workbook workbook = "xlsx".equals(extension) ? new XSSFWorkbook(fis) : new HSSFWorkbook(fis);){
Sheet sheet = workbook.getSheetAt(0);
//获取Sheet中的合并单元格信息(为下文HeaderCell下属性判断使用)
HeaderRegion[] headerRegions = new HeaderRegion[sheet.getNumMergedRegions()];
for(int k = 0; k < sheet.getNumMergedRegions(); k++)
{
HeaderRegion headerRegion = null;
CellRangeAddress region = sheet.getMergedRegion(k);
headerRegion = new HeaderRegion();
int firstRow = region.getFirstRow();
int lastRow = region.getLastRow();
int firstColumn = region.getFirstColumn();
int lastColumn = region.getLastColumn();
headerRegion.setTargetRowFrom(firstRow);
headerRegion.setTargetRowTo(lastRow);
headerRegion.setTargetColumnFrom(firstColumn);
headerRegion.setTargetColumnTo(lastColumn);
Cell cell = sheet.getRow(firstRow).getCell(firstColumn);
headerRegion.setText(cell.getStringCellValue());
headerRegion.setColLength(1 + (lastColumn - firstColumn));
headerRegion.setRowLength(1 + (lastRow - firstRow));
headerRegions[k] = headerRegion;
}
// 找到不是基础表头的合并单元格(前三列是基础数据表头合并单元格,要获取后面动态生成的合并单元格)
int size = 0;//后面动态生成的个数
int sizeLength = 0;//动态占用列数
int baseSize = 0;//基础表头
Map<String,List<AtomMbCfgItem>> map = new LinkedHashMap<>();//key放动态生成的目标id(一个映射表)
Map<Integer,String> cellMap = new LinkedHashMap<>();//key列对应的目标id(一个映射表,方便下面循环excel的列时候找到所属的目标id)
for(HeaderRegion item : headerRegions){
// 同一列,基础表头
if(item.getTargetColumnFrom() == item.getTargetColumnTo()){
baseSize++;
}else{
size++;
sizeLength += item.getColLength();
int targetColumnFrom = item.getTargetColumnFrom();
int targetColumnTo = item.getTargetColumnTo();
String text = item.getText();
String[] nameText = text.split("_");
String atomId = nameText[0];//atomId
String modeName = nameText[1];//模式名称
String dimenName = nameText[2];//维度名称
// T_ATOM_MB_CFG表里的mb_id 是number
List<AtomMbCfg> atomMbCfgs = atomMbCfgMapper.queryMBId(sceneId, atomId, modeName, dimenName);
if(atomMbCfgs == null || atomMbCfgs.size() == 0){
throw new BadRequestException("导入的目标不对,可能不是当前场景的模版");
}
AtomMbCfg atomMbCfg = atomMbCfgs.get(0);
String mbId = atomMbCfg.getMbId();
cellMap.put(targetColumnFrom,mbId);
cellMap.put(targetColumnTo,mbId);
List<AtomMbCfgItem> temp = new ArrayList<>();
map.put(mbId,temp);
}
}
String orgid=null;
String orgName="";
int orglevel= -1;
String orglevelName="";
int baseval= 0;
int chalval= 0;
// 必输验证提示信息
StringBuffer error = new StringBuffer();
// 遍历数据行(这里知道是从第三行是实际数据行)
for (int i = 2; i <= sheet.getLastRowNum(); i++){
int n = i + 1;//实际行数(显示报错用的)
StringBuffer err = new StringBuffer();
Row row = sheet.getRow(i);
if (row == null)
continue;
orgid = row.getCell(0).toString().trim();
if(orgid.length() == 0){
err.append("\r\n第[" + n + "]行"+ 1 +"列没有值!");
}
orgName = row.getCell(1).toString().trim();//获取也存入表里
if(orgName.length() == 0){
err.append("\r\n第[" + n + "]行"+ 2 +"列没有值!");
}
orglevelName = row.getCell(2).toString().trim();
if(orglevelName.length() == 0){
err.append("\r\n第[" + n + "]行"+ 3 +"列没有值!");
}
// 不循环数据固定表头,直接上面写死
for (int j = 3; j < baseSize+sizeLength; j += 2) {
String mbId = cellMap.get(j);//获取动态表头的目标id
List<AtomMbCfgItem> atomMbCfgItems = map.get(mbId);//当前目标id的列表
AtomMbCfgItem atomMbCfgitem = new AtomMbCfgItem();
String basevalStr = row.getCell(j).toString().trim();
if(basevalStr.endsWith(".0")){
basevalStr = basevalStr.substring(0, basevalStr.length() - 2);
baseval = Integer.parseInt(basevalStr);
}
String chalvalStr = row.getCell(j+1).toString().trim();
if(chalvalStr.endsWith(".0")){
chalvalStr = chalvalStr.substring(0, chalvalStr.length() - 2);
chalval = Integer.parseInt(chalvalStr);
}
// baseval = Integer.parseInt(basevalStr);
// chalval = Integer.parseInt(chalvalStr);
if(baseval < 0){
err.append("\r\n第[" + n + "]行"+ j+1 +"列数据为负数!");
}
if(chalval < 0){
err.append("\r\n第[" + n + "]行"+ j+2 +"列为负数!");
}
// 为啥还要获取一遍,不用excel里面的值 根据组织名称和组织等级获取组织id并不唯一
// select * from t_report_org_template_1
// where org_name='党政军组' and
// org_level= 3
// orgid= atomMbCfgMapper.selectOrgIdByName(orgName,orglevelName,atomMbCfgcx.getAtomId());
atomMbCfgitem.setMbId(mbId);
atomMbCfgitem.setMonth(month);
if(orgid!=null && !orgid.isEmpty()){
atomMbCfgitem.setOrgId(orgid);
}
// 在t_atom_template_level只有原子是tempalte_id 是1的才能获取到组织层级 先写死1 查询组织层级
// List<Integer> integers = atomMbCfgMapper.selectOrgLevel(orglevelName, atomMbCfgcx.getAtomId());
//
// if(integers.size()>0){
// orglevel = integers.get(0);
// atomMbCfgitem.setOrgLevel(orglevel);
// }else{
// err.append("\r\n第[" + n + "]无对应组织level,请检查!");
// }
// 根据组织名称和 先写死的模版id 是1 查找组织等级
Integer integer = atomMbCfgMapper.selectOrgLevelByorglevelNameTemplateId(orglevelName, "1");
atomMbCfgitem.setOrgLevel(integer);
atomMbCfgitem.setBaseVal(baseval);
atomMbCfgitem.setChalVal(chalval);
atomMbCfgitem.setCreateBy(SecurityUtils.getCurrentUsername());
atomMbCfgitem.setCreateTime(new Date());
if ("".equals(err.toString())){
atomMbCfgItems.add(atomMbCfgitem);
}else{
error.append(err);
error.append("\r\n请修改!");
throw new BadRequestException(error.toString());
}
}
}
for (String key : map.keySet()) {
List<AtomMbCfgItem> atomMbCfgItems = map.get(key);
System.out.println(key + " :" + atomMbCfgItems);
atomMbCfgMapper.saveItam(atomMbCfgItems);
}
// 文件流关闭
fis.close();
}
}