===maven文件===
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.17-beta1</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml-schemas -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml-schemas</artifactId>
<version>3.17-beta1</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.apache.poi/poi-scratchpad -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-scratchpad</artifactId>
<version>3.17-beta1</version>
===实体类===
package cn.stylefeng.guns.modular.system.model;
import com.baomidou.mybatisplus.enums.IdType;
import com.baomidou.mybatisplus.annotations.TableId;
import com.baomidou.mybatisplus.annotations.TableField;
import com.baomidou.mybatisplus.activerecord.Model;
import com.baomidou.mybatisplus.annotations.TableName;
import java.io.Serializable;
/**
* <p>
*
* </p>
*
* @author yzj
* @since 2020-11-27
*/
@TableName("teaching_qualification")
public class TeachingQualification extends Model<TeachingQualification> {
private static final long serialVersionUID = 1L;
/**
* 编号
*/
@TableId(value = "id", type = IdType.AUTO)
private Integer id;
/**
* 报名号
*/
@TableField("reg_num")
private String regNum;
/**
* 档案号
*/
private String filenum;
/**
* 姓名
*/
private String name;
/**
* 性别
*/
private String sex;
/**
* 证件类型
*/
@TableField("certificate_type")
private String certificateType;
/**
* 证件号码
*/
@TableField("id_num")
private String idNum;
/**
* 考试合格证明编号
*/
@TableField("examination_qualified_num")
private String examinationQualifiedNum;
/**
* 出生日期
*/
private String birthdate;
/**
* 民族
*/
private String nation;
/**
* 申请资格种类
*/
@TableField("eligibility_type")
private String eligibilityType;
/**
* 任教学科
*/
@TableField("teaching_subjects")
private String teachingSubjects;
/**
* 教师资格证书号码
*/
@TableField("teacher_qualification")
private String teacherQualification;
/**
* 确认点
*/
private String affirm;
/**
* 认定机构
*/
@TableField("certification_body")
private String certificationBody;
/**
* 政治面貌
*/
@TableField("politics_status")
private String politicsStatus;
/**
* 普通话水平
*/
@TableField("mandarin_level")
private String mandarinLevel;
/**
* 普通话证书编号
*/
@TableField("putonghua_num")
private String putonghuaNum;
/**
* 普通话发证单位
*/
@TableField("putonghua_certificate_issuing_unit")
private String putonghuaCertificateIssuingUnit;
/**
* 是否在校生
*/
@TableField("is_school")
private String isSchool;
/**
* 毕业时间
*/
@TableField("graduation_date")
private String graduationDate;
/**
* 最高学历
*/
@TableField("highest_education")
private String highestEducation;
/**
* 最高学位
*/
@TableField("highest_degree")
private String highestDegree;
/**
* 所学专业
*/
private String major;
/**
* 专业类别
*/
@TableField("speciality_classification")
private String specialityClassification;
/**
* 学习形式
*/
@TableField("study_mode")
private String studyMode;
/**
* 工作单位
*/
@TableField("work_unit")
private String workUnit;
/**
* 现从事职业
*/
@TableField("present_occupation")
private String presentOccupation;
/**
* 专业技术职务
*/
@TableField("title_technical_post")
private String titleTechnicalPost;
/**
* 通讯地址
*/
@TableField("mailing_address")
private String mailingAddress;
/**
* 毕业学校
*/
@TableField("school_graduation")
private String schoolGraduation;
/**
* 通讯地的邮编
*/
@TableField("place_communication")
private String placeCommunication;
/**
* 手机号码
*/
private String phone;
/**
* 网报时间
*/
@TableField("net_time")
private String netTime;
/**
* 考试类型
*/
@TableField("exam_type")
private String examType;
/**
* 最后修改时间
*/
@TableField("last_time")
private String lastTime;
/**
* 是否参加能力测试
*/
@TableField("is_ability_test")
private String isAbilityTest;
/**
* 认定状态
*/
@TableField("identity_status")
private String identityStatus;
/**
* 普通话是否核验
*/
@TableField("is_pth_verification")
private String isPthVerification;
/**
* 学籍是否核验
*/
@TableField("is_roll")
private String isRoll;
/**
* 学历是否核验
*/
@TableField("is_education")
private String isEducation;
/**
* 毕业证书编号
*/
@TableField("graduate_num")
private String graduateNum;
/**
* 认定申请所在地
*/
@TableField("location_application")
private String locationApplication;
/**
* 证书获取方式
*/
@TableField("certificate_acquisition_mode")
private String certificateAcquisitionMode;
/**
* 邮寄地址
*/
@TableField("mail_address")
private String mailAddress;
/**
* 邮编
*/
private String postcode;
/**
* 联系人
*/
private String linkman;
/**
* 联系电话
*/
private String linkphone;
/**
* 数据标识：已填报/未填报
*/
private String dataFlag;
/**
* 许可决定日期
*/
@TableField("xk_jdrq")
private String xkJdrq;
/**
* 有效期自
*/
private String yxqstart;
/**
* 有效期至
*/
private String yxqend;
public String getXkJdrq() {
return xkJdrq;
}
public void setXkJdrq(String xkJdrq) {
this.xkJdrq = xkJdrq;
}
public String getYxqstart() {
return yxqstart;
}
public void setYxqstart(String yxqstart) {
this.yxqstart = yxqstart;
}
public String getYxqend() {
return yxqend;
}
public void setYxqend(String yxqend) {
this.yxqend = yxqend;
}
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getRegNum() {
return regNum;
}
public void setRegNum(String regNum) {
this.regNum = regNum;
}
public String getFilenum() {
return filenum;
}
public void setFilenum(String filenum) {
this.filenum = filenum;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getSex() {
return sex;
}
public void setSex(String sex) {
this.sex = sex;
}
public String getCertificateType() {
return certificateType;
}
public void setCertificateType(String certificateType) {
this.certificateType = certificateType;
}
public String getIdNum() {
return idNum;
}
public void setIdNum(String idNum) {
this.idNum = idNum;
}
public String getExaminationQualifiedNum() {
return examinationQualifiedNum;
}
public void setExaminationQualifiedNum(String examinationQualifiedNum) {
this.examinationQualifiedNum = examinationQualifiedNum;
}
public String getBirthdate() {
return birthdate;
}
public void setBirthdate(String birthdate) {
this.birthdate = birthdate;
}
public String getNation() {
return nation;
}
public void setNation(String nation) {
this.nation = nation;
}
public String getEligibilityType() {
return eligibilityType;
}
public void setEligibilityType(String eligibilityType) {
this.eligibilityType = eligibilityType;
}
public String getTeachingSubjects() {
return teachingSubjects;
}
public void setTeachingSubjects(String teachingSubjects) {
this.teachingSubjects = teachingSubjects;
}
public String getTeacherQualification() {
return teacherQualification;
}
public void setTeacherQualification(String teacherQualification) {
this.teacherQualification = teacherQualification;
}
public String getAffirm() {
return affirm;
}
public void setAffirm(String affirm) {
this.affirm = affirm;
}
public String getCertificationBody() {
return certificationBody;
}
public void setCertificationBody(String certificationBody) {
this.certificationBody = certificationBody;
}
public String getPoliticsStatus() {
return politicsStatus;
}
public void setPoliticsStatus(String politicsStatus) {
this.politicsStatus = politicsStatus;
}
public String getMandarinLevel() {
return mandarinLevel;
}
public void setMandarinLevel(String mandarinLevel) {
this.mandarinLevel = mandarinLevel;
}
public String getPutonghuaNum() {
return putonghuaNum;
}
public void setPutonghuaNum(String putonghuaNum) {
this.putonghuaNum = putonghuaNum;
}
public String getPutonghuaCertificateIssuingUnit() {
return putonghuaCertificateIssuingUnit;
}
public void setPutonghuaCertificateIssuingUnit(String putonghuaCertificateIssuingUnit) {
this.putonghuaCertificateIssuingUnit = putonghuaCertificateIssuingUnit;
}
public String getIsSchool() {
return isSchool;
}
public void setIsSchool(String isSchool) {
this.isSchool = isSchool;
}
public String getGraduationDate() {
return graduationDate;
}
public void setGraduationDate(String graduationDate) {
this.graduationDate = graduationDate;
}
public String getHighestEducation() {
return highestEducation;
}
public void setHighestEducation(String highestEducation) {
this.highestEducation = highestEducation;
}
public String getHighestDegree() {
return highestDegree;
}
public void setHighestDegree(String highestDegree) {
this.highestDegree = highestDegree;
}
public String getMajor() {
return major;
}
public void setMajor(String major) {
this.major = major;
}
public String getSpecialityClassification() {
return specialityClassification;
}
public void setSpecialityClassification(String specialityClassification) {
this.specialityClassification = specialityClassification;
}
public String getStudyMode() {
return studyMode;
}
public void setStudyMode(String studyMode) {
this.studyMode = studyMode;
}
public String getWorkUnit() {
return workUnit;
}
public void setWorkUnit(String workUnit) {
this.workUnit = workUnit;
}
public String getPresentOccupation() {
return presentOccupation;
}
public void setPresentOccupation(String presentOccupation) {
this.presentOccupation = presentOccupation;
}
public String getTitleTechnicalPost() {
return titleTechnicalPost;
}
public void setTitleTechnicalPost(String titleTechnicalPost) {
this.titleTechnicalPost = titleTechnicalPost;
}
public String getMailingAddress() {
return mailingAddress;
}
public void setMailingAddress(String mailingAddress) {
this.mailingAddress = mailingAddress;
}
public String getSchoolGraduation() {
return schoolGraduation;
}
public void setSchoolGraduation(String schoolGraduation) {
this.schoolGraduation = schoolGraduation;
}
public String getPlaceCommunication() {
return placeCommunication;
}
public void setPlaceCommunication(String placeCommunication) {
this.placeCommunication = placeCommunication;
}
public String getPhone() {
return phone;
}
public void setPhone(String phone) {
this.phone = phone;
}
public String getNetTime() {
return netTime;
}
public void setNetTime(String netTime) {
this.netTime = netTime;
}
public String getExamType() {
return examType;
}
public void setExamType(String examType) {
this.examType = examType;
}
public String getLastTime() {
return lastTime;
}
public void setLastTime(String lastTime) {
this.lastTime = lastTime;
}
public String getIsAbilityTest() {
return isAbilityTest;
}
public void setIsAbilityTest(String isAbilityTest) {
this.isAbilityTest = isAbilityTest;
}
public String getIdentityStatus() {
return identityStatus;
}
public void setIdentityStatus(String identityStatus) {
this.identityStatus = identityStatus;
}
public String getIsPthVerification() {
return isPthVerification;
}
public void setIsPthVerification(String isPthVerification) {
this.isPthVerification = isPthVerification;
}
public String getIsRoll() {
return isRoll;
}
public void setIsRoll(String isRoll) {
this.isRoll = isRoll;
}
public String getIsEducation() {
return isEducation;
}
public void setIsEducation(String isEducation) {
this.isEducation = isEducation;
}
public String getGraduateNum() {
return graduateNum;
}
public void setGraduateNum(String graduateNum) {
this.graduateNum = graduateNum;
}
public String getLocationApplication() {
return locationApplication;
}
public void setLocationApplication(String locationApplication) {
this.locationApplication = locationApplication;
}
public String getCertificateAcquisitionMode() {
return certificateAcquisitionMode;
}
public void setCertificateAcquisitionMode(String certificateAcquisitionMode) {
this.certificateAcquisitionMode = certificateAcquisitionMode;
}
public String getMailAddress() {
return mailAddress;
}
public void setMailAddress(String mailAddress) {
this.mailAddress = mailAddress;
}
public String getPostcode() {
return postcode;
}
public void setPostcode(String postcode) {
this.postcode = postcode;
}
public String getLinkman() {
return linkman;
}
public void setLinkman(String linkman) {
this.linkman = linkman;
}
public String getLinkphone() {
return linkphone;
}
public void setLinkphone(String linkphone) {
this.linkphone = linkphone;
}
public String getDataFlag() {
return dataFlag;
}
public void setDataFlag(String dataFlag) {
this.dataFlag = dataFlag;
}
@Override
protected Serializable pkVal() {
return this.id;
}
@Override
public String toString() {
return "TeachingQualification{" +
", id=" + id +
", regNum=" + regNum +
", filenum=" + filenum +
", name=" + name +
", sex=" + sex +
", certificateType=" + certificateType +
", idNum=" + idNum +
", examinationQualifiedNum=" + examinationQualifiedNum +
", birthdate=" + birthdate +
", nation=" + nation +
", eligibilityType=" + eligibilityType +
", teachingSubjects=" + teachingSubjects +
", teacherQualification=" + teacherQualification +
", affirm=" + affirm +
", certificationBody=" + certificationBody +
", politicsStatus=" + politicsStatus +
", mandarinLevel=" + mandarinLevel +
", putonghuaNum=" + putonghuaNum +
", putonghuaCertificateIssuingUnit=" + putonghuaCertificateIssuingUnit +
", isSchool=" + isSchool +
", graduationDate=" + graduationDate +
", highestEducation=" + highestEducation +
", highestDegree=" + highestDegree +
", major=" + major +
", specialityClassification=" + specialityClassification +
", studyMode=" + studyMode +
", workUnit=" + workUnit +
", presentOccupation=" + presentOccupation +
", titleTechnicalPost=" + titleTechnicalPost +
", mailingAddress=" + mailingAddress +
", schoolGraduation=" + schoolGraduation +
", placeCommunication=" + placeCommunication +
", phone=" + phone +
", netTime=" + netTime +
", examType=" + examType +
", lastTime=" + lastTime +
", isAbilityTest=" + isAbilityTest +
", identityStatus=" + identityStatus +
", isPthVerification=" + isPthVerification +
", isRoll=" + isRoll +
", isEducation=" + isEducation +
", graduateNum=" + graduateNum +
", locationApplication=" + locationApplication +
", certificateAcquisitionMode=" + certificateAcquisitionMode +
", mailAddress=" + mailAddress +
", postcode=" + postcode +
", linkman=" + linkman +
", linkphone=" + linkphone +
"}";
}
}
===service===
//excel导入
Map saveDataFromExcel(HttpServletRequest request, MultipartFile multipartFile)throws Exception;
===事务层===
import cn.stylefeng.guns.modular.system.model.Yqdwz;
import cn.stylefeng.guns.modular.system.dao.YqdwzMapper;
import cn.stylefeng.guns.modular.system.service.IYqdwzService;
import com.baomidou.mybatisplus.service.impl.ServiceImpl;
import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import javax.servlet.http.HttpServletRequest;
import org.apache.commons.collections.map.HashedMap;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.multipart.MultipartFile;
@Override
public Map saveDataFromExcel(HttpServletRequest request, MultipartFile multipartFile){
Map<String, Object> mapt=new HashedMap();//初始化
mapt.put("报名号", "regNum");
mapt.put("档案号", "filenum");
mapt.put("姓名", "name");
mapt.put("性别", "sex");
mapt.put("证件类型", "certificateType");
mapt.put("证件号码", "idNum");
mapt.put("考试合格证明编号", "examinationQualifiedNum");
mapt.put("出生日期", "birthdate");
mapt.put("民族", "nation");
mapt.put("申请资格种类", "eligibilityType");
mapt.put("任教学科", "teachingSubjects");
mapt.put("教师资格证书号码", "teacherQualification");
mapt.put("确认点", "affirm");
mapt.put("认定机构", "certificationBody");
mapt.put("政治面貌", "politicsStatus");
mapt.put("普通话水平", "mandarinLevel");
mapt.put("普通话证书编号", "putonghuaNum");
mapt.put("普通话发证单位", "putonghuaCertificateIssuingUnit");
mapt.put("是否在校生", "isSchool");
mapt.put("毕业时间", "graduationDate");
mapt.put("最高学历", "highestEducation");
mapt.put("最高学位", "highestDegree");
mapt.put("所学专业", "major");
mapt.put("专业类别", "specialityClassification");
mapt.put("学习形式", "studyMode");
mapt.put("工作单位", "workUnit");
mapt.put("现从事职业", "presentOccupation");
mapt.put("专业技术职务", "titleTechnicalPost");
mapt.put("通讯地址", "mailingAddress");
mapt.put("毕业学校", "schoolGraduation");
mapt.put("通讯地的邮编", "placeCommunication");
mapt.put("手机号码", "phone");
mapt.put("网报时间", "netTime");
mapt.put("考试类型", "examType");
mapt.put("最后修改时间", "lastTime");
mapt.put("是否参加能力测试", "isAbilityTest");
mapt.put("认定状态", "identityStatus");
mapt.put("普通话是否核验", "isPthVerification");
mapt.put("学籍是否核验", "isRoll");
mapt.put("学历是否核验", "isEducation");
mapt.put("毕业证书编号", "graduateNum");
mapt.put("认定申请所在地", "locationApplication");
mapt.put("证书获取方式", "certificateAcquisitionMode");
mapt.put("邮寄地址", "mailAddress");
mapt.put("邮编", "postcode");
mapt.put("联系人", "linkman");
mapt.put("联系电话", "linkphone");
mapt.put("许可决定日期", "xkJdrq");
mapt.put("有效期自", "yxqstart");
mapt.put("有效期至", "yxqend");
Long start = System.currentTimeMillis();
Map<String, Object> result = new HashMap<>();
StringBuilder errorMsg = new StringBuilder();
// 新增设备基本信息列表
ArrayList<TeachingQualification> workBasedInformationList = new ArrayList<TeachingQualification>();
HSSFWorkbook workbook;
// XSSFWorkbook workbook;
Sheet sheet;
try {
InputStream inputStream = multipartFile.getInputStream();
String fileName = multipartFile.getOriginalFilename();
if (fileName.endsWith(".xlsx") || fileName.endsWith(".xls")) {
workbook = new HSSFWorkbook(inputStream);
} else {
result.put("error", 1);
result.put("msg", "这不是有效的xlsx文件导入模板,请将后缀改为.xlsx");
return result;
}
} catch (IOException e) {
e.printStackTrace();
result.put("error", 1);
result.put("msg", "这不是有效的导入模板");
return result;
}
// 读取第一个sheet
sheet = workbook.getSheetAt(0);
int usedRowsCount = sheet.getLastRowNum();// 行标(比行数小1)
if (usedRowsCount < 2) {
result.put("error", 1);
result.put("msg", "没有可导入的数据");
return result;
}
// 获取模板里面的字段列表(默认在第一行)
Row row = sheet.getRow(0);
int usedCellsCount = row.getLastCellNum();// 列数(比列标大1)
ArrayList<String> templateFieldList = new ArrayList<>();
for (int i = 0; i < usedCellsCount; i++) {
Cell cell = row.getCell(i);
cell.setCellType(CellType.STRING);
//修改入对应字段
templateFieldList.add((String) mapt.get(cell.getStringCellValue().trim()));
System.out.println("==="+(String) mapt.get(cell.getStringCellValue().trim()));
}
// 第2行开始才是用户填的数据
for (int j = 1; j <= usedRowsCount; j++) {
TeachingQualification wrkBasedInformation = new TeachingQualification();
Row dataRow = sheet.getRow(j);
// 序号不需要读取
for (int k = 0; k < usedCellsCount; k++) {
Cell cell = dataRow.getCell(k);
String fieldName = templateFieldList.get(k);
// System.out.println("K========="+fieldName);
if (cell != null) {
try {
Field field = wrkBasedInformation.getClass().getDeclaredField(fieldName);
field.setAccessible(true);
String fieldType = wrkBasedInformation.getClass().getDeclaredField(fieldName).getType()
.toString();
if ("class java.util.Date".equals(fieldType)) {
Date cellValueDate = cell.getDateCellValue();
field.set(wrkBasedInformation, cellValueDate);
} else {
cell.setCellType(CellType.STRING);
String cellValue = cell.getStringCellValue();
field.set(wrkBasedInformation, cellValue);
}
} catch (IllegalAccessException | NoSuchFieldException e) {
e.printStackTrace();
}
}
}
workBasedInformationList.add(wrkBasedInformation);
// System.out.println("wrkBasedInformation============"+wrkBasedInformation.toString());
}
// 循环插入数据
for (TeachingQualification tPersonInfo : workBasedInformationList) {
tPersonInfo.setDataFlag("未填报");
this.insert(tPersonInfo);
}
return result;
}
===导出=== 使用HXXF最佳
@RequestMapping("/exportdc2/{idstr}")
public void exportdc2(HttpServletResponse response,HttpServletRequest request, @PathVariable String idstr)
throws IOException {
System.out.println("----------"+idstr);
SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");//设置日期格式
// 声明String数组，并初始化元素（表头名称）
//第一行表头字段，合并单元格时字段跨几列就将该字段重复几次
String[] excelHeader0 = { "序号", "行政相对人信息","行政相对人信息","行政相对人信息","行政相对人信息","行政相对人信息","行政相对人信息","行政相对人信息","行政相对人信息",
"案件信息","案件信息","案件信息","案件信息","案件信息","案件信息","案件信息","案件信息","案件信息","案件信息","案件信息","案件信息","案件信息"};
// “0,2,0,0” ===> “起始行，截止行，起始列，截止列”
String[] headnum0 = { "0,3,0,0", "0,0,1,8", "0,0,9,21"};
//第二行表头字段，其中的空的双引号是为了补全表格边框
String[] excelHeader1 = { "","行政相对人名称", "行政相对人类别", "法人及其他组织个体工商户","法人及其他组织个体工商户","法人及其他组织个体工商户","法人及其他组织个体工商户", "自然人" ,"自然人",
"案件名称", "决定书(通知书)名称", "决定书(通知书)文号", "许可证书名称", "许可编号",
"许可决定日期", "许可类别", "处理结果","许可内容","职权来源","委托单位","备注","决定书" };
// 合并单元格
String[] headnum1 = { "1,3,1,1", "1,3,2,2", "1,1,3,6", "1,1,7,8", "1,3,9,9", "1,3,10,10",
"1,3,11,11","1,3,12,12","1,3,13,13","1,3,14,14","1,3,15,15","1,3,16,16","1,3,17,17",
"1,3,18,18","1,3,19,19","1,3,20,20","1,3,21,21"};
//第三行表头字段
String[] excelHeader2 = { "","","","统一社会信用代码", "法定代表人", "法定代表人件类型", "法定代表人证件号码", "自然人证件类型", "自然人证件号码"};
String[] headnum2 = { "2,3,3,3", "2,3,4,4", "2,3,5,5", "2,3,6,6", "2,3,7,7", "2,3,8,8"};
String strListid = idstr.substring(0,idstr.length() -1);
XSSFWorkbook workbook = new XSSFWorkbook();
XSSFSheet sheet = workbook.createSheet("信息表");
// new Date()为获取当前系统时间
String fileName = "行政许可案件-省公示"+df.format(new Date());//设置要导出的文件的名字
//新增数据行，并且设置单元格数据
int rowNum = 4;
String[] listdc=strListid.split(",");
// 生成表格的第一行
// 第一行表头
XSSFRow row = sheet.createRow(0);
for (int i = 0; i < excelHeader0.length; i++) {
XSSFCell cell = row.createCell(i);
cell.setCellValue(excelHeader0[i]);
sheet.autoSizeColumn(i, true);// 根据字段长度自动调整列的宽度
// System.out.println(excelHeader0[i]);
if (i >= 0 && i <= 21) {
for (int j = 0; j < excelHeader0.length; j++) {
// 从第j列开始填充
cell = row.createCell(j);
// 填充excelHeader1[j]第j个元素
cell.setCellValue(excelHeader0[j]);
// sheet.autoSizeColumn(j, true);// 根据字段长度自动调整列的宽度
}
}
}
// 动态合并单元格
for (int i = 0; i < headnum0.length; i++) {
sheet.autoSizeColumn(i, true);// 根据字段长度自动调整列的宽度
String[] temp = headnum0[i].split(",");
Integer startrow = Integer.parseInt(temp[0]);
Integer overrow = Integer.parseInt(temp[1]);
Integer startcol = Integer.parseInt(temp[2]);
Integer overcol = Integer.parseInt(temp[3]);
sheet.addMergedRegion(new CellRangeAddress(startrow, overrow, startcol, overcol));
}
// 第二行表头
row = sheet.createRow(1);
for (int i = 0; i < excelHeader1.length - 1; i++) {
XSSFCell cell = row.createCell(i + 1);
cell.setCellValue(excelHeader1[i]);
sheet.autoSizeColumn(i, true);// 根据字段长度自动调整列的宽度
if (i >= 1 && i <= 21) {
for (int j = 0; j < excelHeader1.length; j++) {
// 从第j+1列开始填充
cell = row.createCell(j);
// 填充excelHeader1[j]第j个元素
cell.setCellValue(excelHeader1[j]);
// sheet.autoSizeColumn(j, true);// 根据字段长度自动调整列的宽度
}
}
}
// 动态合并单元格
for (int i = 0; i < headnum1.length; i++) {
sheet.autoSizeColumn(i, true);// 根据字段长度自动调整列的宽度
String[] temp = headnum1[i].split(",");
Integer startrow = Integer.parseInt(temp[0]);
Integer overrow = Integer.parseInt(temp[1]);
Integer startcol = Integer.parseInt(temp[2]);
Integer overcol = Integer.parseInt(temp[3]);
sheet.addMergedRegion(new CellRangeAddress(startrow, overrow, startcol, overcol));
}
// 第三行表头
row = sheet.createRow(2);
for (int i = 0; i < excelHeader2.length; i++) {
XSSFCell cell = row.createCell(i + 2);
cell.setCellValue(excelHeader2[i]);
// System.out.println(excelHeader2[i]);
sheet.autoSizeColumn(i, true);// 自动调整宽度
if (i > 3 && i <= 8) {
for (int j = 0; j < excelHeader2.length; j++) {
// 从第j+2列开始填充
cell = row.createCell(j);
// 填充excelHeader1[j]第j个元素
cell.setCellValue(excelHeader2[j]);
// sheet.autoSizeColumn(j, true);// 根据字段长度自动调整列的宽度
}
}
}
// 动态合并单元格
for (int i = 0; i < headnum2.length; i++) {
sheet.autoSizeColumn(i, true);// 根据字段长度自动调整列的宽度
String[] temp = headnum2[i].split(",");
Integer startrow = Integer.parseInt(temp[0]);
Integer overrow = Integer.parseInt(temp[1]);
Integer startcol = Integer.parseInt(temp[2]);
Integer overcol = Integer.parseInt(temp[3]);
sheet.addMergedRegion(new CellRangeAddress(startrow, overrow, startcol, overcol));
}
List<Map<String, Object>> list = this.teachingQualificationService.zdylist(listdc);
// //在表中存放查询到的数据放入对应的列
for (int i = 0 ;i<list.size();i++) {
XSSFRow row1 = sheet.createRow(rowNum);
//
// //自定义对齐各个数据
//编号
row1.createCell(0).setCellValue(String.valueOf(i+1));
//行政相对人名称
row1.createCell(1).setCellValue(String.valueOf(list.get(i).get("name")));
//行政相对人类别
row1.createCell(2).setCellValue("自然人");
//统一社会信用代码
row1.createCell(3).setCellValue("");
//法定代表人
row1.createCell(4).setCellValue("");
//法定代表人证件类型
row1.createCell(5).setCellValue("");
//法定代表人证件号码
row1.createCell(6).setCellValue("");
//自然人证件类型
if("身份证".equals(String.valueOf(list.get(i).get("certificate_type")))) {
row1.createCell(7).setCellValue("居民身份证");
}else {
row1.createCell(7).setCellValue(String.valueOf(list.get(i).get("certificate_type")));
}
//自然人证件号码
row1.createCell(8).setCellValue(String.valueOf(list.get(i).get("id_num")));
//案件名称
row1.createCell(9).setCellValue("初中、小学、幼儿园教师资格认定");
//决定书(通知书)名称
row1.createCell(10).setCellValue("教师资格证书");
//决定书(通知书)文号
row1.createCell(11).setCellValue(String.valueOf(list.get(i).get("teacher_qualification")));
//许可证书名称
row1.createCell(12).setCellValue("教师资格证书");
//许可编号
row1.createCell(13).setCellValue("");
//许可决定日期
row1.createCell(14).setCellValue("2020/10/31");
//许可类别
row1.createCell(15).setCellValue("普通");
//处理结果
row1.createCell(16).setCellValue("予以许可");
//许可内容
if("幼儿园".equals(String.valueOf(list.get(i).get("teaching_subjects")))) {
row1.createCell(17).setCellValue("根据《中华人民共和国教师法》及《教师资格条例》的规定认定"+String.valueOf(list.get(i).get("name"))+"具备"+String.valueOf(list.get(i).get("eligibility_type")));
}else {
row1.createCell(17).setCellValue("根据《中华人民共和国教师法》及《教师资格条例》的规定认定"+String.valueOf(list.get(i).get("name"))+"具备"+String.valueOf(list.get(i).get("eligibility_type")).substring(0,String.valueOf(list.get(i).get("eligibility_type")).length() -4)+String.valueOf(list.get(i).get("teaching_subjects"))+"教师资格");
}
//职权来源
row1.createCell(18).setCellValue("依职权");
//委托单位
row1.createCell(19).setCellValue("深圳市南山区教育局");
//备注
row1.createCell(20).setCellValue("");
//决定书
row1.createCell(21).setCellValue("");
rowNum++;
}
//设置单元格大小
for(int i = 0 ; i<=21 ;i++) {
sheet.setColumnWidth(i, 25 * 256);
}
response.setContentType("application/octet-stream");
response.setCharacterEncoding("utf-8");
response.setHeader("Content-disposition", "attachment;filename=" + new String(fileName.getBytes("gbk"), "iso8859-1")+".xlsx");
response.flushBuffer();
workbook.write(response.getOutputStream());
}
本文档介绍了如何在项目中使用Maven引入Apache POI库处理Excel文件，包括 poi-ooxml、poi-ooxml-schemas 和 poi-scratchpad 的依赖。同时展示了TeachingQualification实体类的定义及其与Service层的交互，重点在于Excel数据的导入与导出功能，包括数据校验和文件格式转换。
扫一扫
2136
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
