I reprinted an article about the basic operation of Excel before, and the page views of this article soared rapidly, so that it ranked the third in my blog’s popular articles. However, the reprinted article is not practical and its explanation is not very clear, so I plan to take a rest today. Write an article about SpringBoot through WorkBook fast implementation of Excel import, export, data verification article, is also easy to refer to in the future.
1. Introduce dependencies
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>4.0. 0</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>4.0. 0</version>
</dependency>
Copy the code
2, Excel type enumeration
/ * * *@Description: Excel type enumeration *@Author: zhangzhixiang *@CreateDate: 2018/08/31 13:59:48 *@Version: 1.0 * /
public enum ExcelTypeEnum {
/** ** report backup import */
REPORT_TYPE(BatchImportConsts.EXCEL_REPORT_MODEL_TYPE, new BatchImportStruBO(BatchImportConsts.excel_report_titles, new ReportRecordCheck(), SpringHelper.getBeanByClass(ReportInsertServiceImpl.class))),
/** * import */
CLUE_TYPE(BatchImportConsts.EXCEL_CLUE_MODEL_TYPE, new BatchImportStruBO(BatchImportConsts.excel_clue_titles, new ClueRecordCheck(), SpringHelper.getBeanByClass(ClueInsertServiceImpl.class)));
@Setter
@Getter
private String type;
@Setter
@Getter
private BatchImportStruBO struBO;
ExcelTypeEnum(String type, BatchImportStruBO struBO) {
this.type = type;
this.struBO = struBO;
}
/** * Check whether the enumeration corresponding to key exists. If so, return this enumeration. Otherwise null * * is returned@param key
* @return* /
public static ExcelTypeEnum containKey(String key) {
ExcelTypeEnum type = null;
switch (key) {
case "report":
type = ExcelTypeEnum.REPORT_TYPE;
break;
case "clue":
type = ExcelTypeEnum.CLUE_TYPE;
break;
default:
type = null;
break;
}
returntype; }}Copy the code
3. Excel import related configuration
/** * Excel import related configuration **@author zhangzhixiang
* @data2018/09/18 11:48:59 * /
public class BatchImportConsts {
/** * Excel import mode */
public static String EXCEL_CLUE_MODEL_TYPE = "clue";
public static String EXCEL_REPORT_MODEL_TYPE = "report";
/** * Import Excel title mapping information */
public static List<BaseColumn> excel_report_titles;
/** * import Excel title mapping information */
public static List<BaseColumn> excel_clue_titles;
/** * import Excel title */
public static final String EXCEL_REPORT_RECORD_COLUMN_PROVINCE = "Save";
public static final String EXCEL_REPORT_RECORD_COLUMN_CITY = "The city";
public static final String EXCEL_REPORT_RECORD_COLUMN_AREA = "Area";
public static final String EXCEL_REPORT_RECORD_COLUMN_CUSTOMER_SOURCE = "Customer Source (Name of unit)";
public static final String EXCEL_REPORT_RECORD_COLUMN_ITEM = "Project Ownership";
public static final String EXCEL_REPORT_RECORD_COLUMN_DATA_CONTENT_DESC = "Data Content Description";
public static final String EXCEL_REPORT_RECORD_COLUMN_DATA_NUM = "Data quantity";
public static final String EXCEL_REPORT_RECORD_COLUMN_DATA_PURPOSE = "Data Usage";
/** * clues import Excel title */
public static final String EXCEL_CLUE_RECORD_COLUMN_CLUE_SOURCE = "Source of information.";
public static final String EXCEL_CLUE_RECORD_COLUMN_ITEM = "Attribution item";
public static final String EXCEL_CLUE_RECORD_COLUMN_TAG = Cue tag;
public static final String EXCEL_CLUE_RECORD_COLUMN_NAME = "Clue name";
public static final String EXCEL_CLUE_RECORD_COLUMN_DESC = "Clue content";
public static final String EXCEL_CLUE_RECORD_COLUMN_CLUE_REMARK = "Cue notes";
public static final String EXCEL_CLUE_RECORD_COLUMN_PERSON_NUM = "The number";
public static final String EXCEL_CLUE_RECORD_COLUMN_PERSON_NAME = "Name";
public static final String EXCEL_CLUE_RECORD_COLUMN_ID_CARD = "Id Number";
public static final String EXCEL_CLUE_RECORD_COLUMN_IPHONE = "Mobile phone Number";
public static final String EXCEL_CLUE_RECORD_COLUMN_SEX = "Gender";
public static final String EXCEL_CLUE_RECORD_COLUMN_NATION = "Ethnic";
/** * Report backup configuration (header + Mandatory) */
static {
excel_report_titles = new ArrayList<>();
excel_report_titles.add(new BaseColumn(EXCEL_REPORT_RECORD_COLUMN_PROVINCE, BaseColumn.TRUE));
excel_report_titles.add(new BaseColumn(EXCEL_REPORT_RECORD_COLUMN_CITY, BaseColumn.FALSE));
excel_report_titles.add(new BaseColumn(EXCEL_REPORT_RECORD_COLUMN_AREA, BaseColumn.FALSE));
excel_report_titles.add(new BaseColumn(EXCEL_REPORT_RECORD_COLUMN_CUSTOMER_SOURCE, BaseColumn.TRUE));
excel_report_titles.add(new BaseColumn(EXCEL_REPORT_RECORD_COLUMN_ITEM, BaseColumn.TRUE));
excel_report_titles.add(new BaseColumn(EXCEL_REPORT_RECORD_COLUMN_DATA_CONTENT_DESC, BaseColumn.TRUE));
excel_report_titles.add(new BaseColumn(EXCEL_REPORT_RECORD_COLUMN_DATA_NUM, BaseColumn.FALSE));
excel_report_titles.add(new BaseColumn(EXCEL_REPORT_RECORD_COLUMN_DATA_PURPOSE, BaseColumn.FALSE));
}
/** * Clue configuration (header + mandatory) */
static {
excel_clue_titles = new ArrayList<>();
excel_clue_titles.add(new BaseColumn(EXCEL_CLUE_RECORD_COLUMN_CLUE_SOURCE, BaseColumn.FALSE));
excel_clue_titles.add(new BaseColumn(EXCEL_CLUE_RECORD_COLUMN_ITEM, BaseColumn.FALSE));
excel_clue_titles.add(new BaseColumn(EXCEL_CLUE_RECORD_COLUMN_TAG, BaseColumn.FALSE));
excel_clue_titles.add(new BaseColumn(EXCEL_CLUE_RECORD_COLUMN_NAME, BaseColumn.FALSE));
excel_clue_titles.add(new BaseColumn(EXCEL_CLUE_RECORD_COLUMN_DESC, BaseColumn.FALSE));
excel_clue_titles.add(new BaseColumn(EXCEL_CLUE_RECORD_COLUMN_CLUE_REMARK, BaseColumn.FALSE));
excel_clue_titles.add(new BaseColumn(EXCEL_CLUE_RECORD_COLUMN_PERSON_NUM, BaseColumn.FALSE));
excel_clue_titles.add(new BaseColumn(EXCEL_CLUE_RECORD_COLUMN_PERSON_NAME, BaseColumn.FALSE));
excel_clue_titles.add(new BaseColumn(EXCEL_CLUE_RECORD_COLUMN_ID_CARD, BaseColumn.TRUE));
excel_clue_titles.add(new BaseColumn(EXCEL_CLUE_RECORD_COLUMN_IPHONE, BaseColumn.FALSE));
excel_clue_titles.add(new BaseColumn(EXCEL_CLUE_RECORD_COLUMN_SEX, BaseColumn.FALSE));
excel_clue_titles.add(newBaseColumn(EXCEL_CLUE_RECORD_COLUMN_NATION, BaseColumn.FALSE)); }}Copy the code
4. Batch import Excel data structure
/ * * *@Description: Batch import Excel data structure information *@Author: zhangzhixiang *@CreateDate: 2018/09/05 19:54:32 *@Version: 1.0 * /
@Data
public class BatchImportStruBO {
/** ** ** */
private List<BaseColumn> importTitles;
/** * record validator */
private BaseRecordCheck recordCheck;
/** * enter the table processor */
private BaseInsertService insertHandle;
public BatchImportStruBO(List<BaseColumn> importTitles, BaseRecordCheck recordCheck, BaseInsertService insertHandle) {
this.importTitles = importTitles;
this.recordCheck = recordCheck;
this.insertHandle = insertHandle; }}Copy the code
5. Spring help classes
/ * * *@Description: Spring help class *@Author: zhangzhixaing *@CreateDate: 2018/08/31 16:39:45 *@Version: 1.0 * /
@Component
public class SpringHelper implements ApplicationContextAware {
private static ApplicationContext applicationContext = null;
@Override
public void setApplicationContext(ApplicationContext applicationContext) throws BeansException {
if(SpringHelper.applicationContext == null) { SpringHelper.applicationContext = applicationContext; }}/** * Get the corresponding bean */ in the configuration file based on the id of a bean
public static Object getBean(String beanId) throws BeansException {
if(applicationContext.containsBean(beanId)) {
applicationContext.getBean(beanId);
}
return null;
}
/** * get the corresponding bean */ in the configuration file based on the type of a bean
public static <T> T getBeanByClass(Class<T> requiredType) throws BeansException {
return applicationContext.getBean(requiredType);
}
/** * Returns true if the BeanFactory contains a definition of a bean that matches the given name, otherwise false */
public static boolean containsBean(String name) {
return applicationContext.containsBean(name);
}
/** * get the Spring container */
public static ApplicationContext getApplicationContext(a) {
returnSpringHelper.applicationContext; }}Copy the code
6, String help class
/ * * *@Description: String help class *@Author: zhangzhixaing *@CreateDate: 2018/08/31 16:39:45 *@Version: 1.0 * /
public class StringHelper extends StringUtils {
public StringHelper(a) {}
/** * Check whether the value is null */
public static boolean isBlankAnyWay(String str) {
return isBlank(str) || "null".equalsIgnoreCase(str);
}
/** * check whether the value is not null */
public static boolean isNotBlankAnyWay(String str) {
return! isBlankAnyWay(str); }/** * converts null to null */
public static String nullToEmpty(String str) {
return Strings.nullToEmpty(str);
}
/**
* 将空转为null
*/
public static String emptyToNumm(String str) {
return Strings.emptyToNull(str);
}
/** * left fill */
public static String padStart(String str, int minLength, char padChar) {
return Strings.padStart(str, minLength, padChar);
}
/** * right fill */
public static String padEnd(String str, int minLength, char padChar) {
returnStrings.padEnd(str, minLength, padChar); }}Copy the code
7. Excel cells
/ * * *@Description: Excel cell entity *@Author: zhangzhixaing *@CreateDate: 2018/08/31 16:39:45 *@Version: 1.0 * /
@Data
public class BaseColumn {
/** * constant definition */
public static final Boolean TRUE = true;
public static final Boolean FALSE = false;
/** * The corresponding field name in Excel */
protected String excelName;
/** * Excel field value */
protected String excelValue;
/** * Specifies whether */ is mandatory
protected Boolean isRequired;
/** * Indicates whether the verification succeeds. The default value is true */
protected boolean checkIsSuccess = true;
/** * Verification result */
protected String checkMessage;
public BaseColumn(a) {
super(a); }public BaseColumn(String excelName, Boolean isRequired) {
super(a);this.excelName = excelName;
this.isRequired = isRequired;
}
public BaseColumn(String excelName) {
super(a);this.excelName = excelName; }}Copy the code
8. Line check interface
/ * * *@Description: line check interface *@Author: zhangzhixiang *@CreateDate: 2018/08/31 21:56:32 *@Version: 1.0 * /
public interface BaseRecordCheck {
/** * record verification **@param cells
* @return
* @throws Exception
*/
List<BaseColumn> valueCheck(List<BaseColumn> cells) throws Exception;
}
Copy the code
9, the realization of clue line verification
/ * * *@Description: Clue line check *@Author: zhangzhixiang *@CreateDate: 2018/08/31 12:59:48 *@Version: 1.0 * /
public class ClueRecordCheck implements BaseRecordCheck {
@Override
public List<BaseColumn> valueCheck(List<BaseColumn> cells) throws Exception {
// Field verification
for (BaseColumn column : cells) {
switch (column.getExcelName()) {
case BatchImportConsts.EXCEL_CLUE_RECORD_COLUMN_NAME:
new BaseCheck().valueCheck(column);
break;
case BatchImportConsts.EXCEL_CLUE_RECORD_COLUMN_ID_CARD:
new ClueIdcardCheck().valueCheck(column);
break;
case BatchImportConsts.EXCEL_CLUE_RECORD_COLUMN_IPHONE:
new CluePhoneCheck().valueCheck(column);
break;
case BatchImportCONsts.EXCEL_CLUE_RECORD_COLUMN_SEX:
new ClueSexCheck().valueCheck(column);
break;
default:
break; }}returncells; }}Copy the code
10. Column check interface
/ * * *@Description: column verification interface *@Author: zhangzhixiang *@CreateDate: 2018/08/31 21:56:34 *@Version: 1.0 * /
public interface BaseColumnCheck {
/** * Verify the field value **@param cell
* @return
* @throws Exception
*/
BaseColumn valueCheck(BaseColumn cell) throws Exception;
/** ** *@param cells
* @return
* @throws Exception
*/
List<BaseColumn> valueCheck(List<BaseColumn> cells) throws Exception;
}
Copy the code
11. Verification of clue column (basic non-null verification)
/ * * *@Description: Basic non-null check *@Author: zhangzhixiang *@CreateDate: 2018/08/31 21:59:43 *@Version: 1.0 * /
public class BaseCheck implements BaseColumnCheck {
private static final String EMPTY_MESSAGE = "Cannot be empty;"
@Override
public BaseColumn valueCheck(BaseColumn cell) throws Exception {
if (StringUtils.isBlank(cell.getExcelValue())) {
cell.setCheckIsSuccess(false);
cell.setCheckMessage((cell.getCheckMessage() == null ? "" : cell.getCheckMessage()) + cell.getExcelName() + EMPTY_MESSAGE);
} else {
cell.setCheckIsSuccess(true);
}
return cell;
}
@Override
public List<BaseColumn> valueCheck(List<BaseColumn> cells) throws Exception {
return null; }}Copy the code
12. Verification of clue column (VERIFICATION of ID number)
/ * * *@Description: ID number verification *@Author: zhangzhixiang *@CreateDate: 2018/08/31 21:59:43 *@Version: 1.0 * /
public class ClueIdcardCheck implements BaseColumnCheck {
private static final String EMPTY_MESSAGE = "Cannot be empty;";
private static final String WRONG_MESSAGE = "Wrong format;";
@Override
public BaseColumn valueCheck(BaseColumn cell) throws Exception {
if(cell.getIsRequired() || (! cell.getIsRequired() && StringHelper.isNotBlankAnyWay(cell.getExcelValue()))) { cell.setCheckIsSuccess(false);
cell.setCheckMessage((cell.getCheckMessage() == null ? "" : cell.getCheckMessage()) + cell.getExcelName() + EMPTY_MESSAGE);
} else {
// Verify the validity of the id number
String reg = "^\\d{15}$|^\\d{17}[0-9Xx]$";
if(! cell.getExcelValue().matches(reg)) { cell.setCheckIsSuccess(false);
cell.setCheckMessage((cell.getCheckMessage() == null ? "": cell.getCheckMessage()) + cell.getExcelName() + WRONG_MESSAGE); }}return cell;
}
@Override
public List<BaseColumn> valueCheck(List<BaseColumn> cells) throws Exception {
return null; }}Copy the code
13. Verification of clue column (verification of mobile phone number)
/ * * *@Description: Mobile phone number verification *@Author: zhangzhixiang *@CreateDate: 2018/08/31 21:59:43 *@Version: 1.0 * /
public class CluePhoneCheck implements BaseColumnCheck {
private static final String EMPTY_MESSAGE = "Cannot be empty;"
private static final String WRONG_MESSAGE = "Wrong format;"
private Pattern p = Pattern.compile("^ ([0-9] (13) | (15 [^ 4 \ \ D]) | (18 [0, 5-9])) \ \ D {8} $");
@Override
public BaseColumn valueCheck(BaseColumn cell) throws Exception {
if(cell.getRequired() || (! cell.getRequired() && StringHelper,isNotBlankAnyWay(cell.getExcelValue()))) { cell.setCheckIsSuccess(false);
cell.setCheckMessage((cell.getCheckMessage() == null ? "" : cell.getCheckMessage()) + cell.getExcelName() + EMPTY_MESSAGE);
} else {
// Verify the validity of the mobile phone number
Matcher m = p.matcher(cell.getExcelValue());
if(! m.matches()) { cell.setCheckIsSuccess(false);
cell.setCheckMessage((cell.getCheckMessage() == null ? "": cell.getCheckMessage()) + cell.getExcelName() + WRONG_MESSAGE); }}return cell;
}
@Override
public List<BaseColumn> valueCheck(List<BaseColumn> cells) throws Exception {
return null; }}Copy the code
14. Realization of clue column verification (gender verification)
/ * * *@Description: Gender check *@Author: zhangzhixiang *@CreateDate: 2018/08/31 21:59:43 *@Version: 1.0 * /
public class ClueSexCheck implements BaseColumnCheck {
private static final String EMPTY_MESSAGE = "Cannot be empty;"
private static final String WRONG_MESSAGE = "Wrong format;"
private String man = "Male";
private String women = "Female";
@Override
public BaseColumn valueCheck(BaseColumn cell) throws Exception {
if(cell.getRequired() || (! cell.getRequired() && StringHelper,isNotBlankAnyWay(cell.getExcelValue()))) { cell.setCheckIsSuccess(false);
cell.setCheckMessage((cell.getCheckMessage() == null ? "" : cell.getCheckMessage()) + cell.getExcelName() + EMPTY_MESSAGE);
} else {
// Verify the validity of the mobile phone number
Matcher m = p.matcher(cell.getExcelValue());
if(! m.matches()) {if(! (man.equals(cell.getExcelValue()) || women.equals(cell.getExcelValue()))) { cell.setCheckIsSuccess(false);
cell.setCheckMessage((cell.getCheckMessage == null ? "": cell.getCheckMessage()) + cell.getExcelName() + WRONG_MESSAGE); }}}return cell;
}
@Override
public List<BaseColumn> valueCheck(List<BaseColumn> cells) throws Exception {
return null; }}Copy the code
15. Excel library interface
/ * * *@Description: Excel library interface *@Author: zhangzhixiang *@CreateDate: 2018/08/31 21:59:43 *@Version: 1.0 * /
public interface BaseInsertService {
/** * Excel database **@paramRows Excle data *@param isSuccess
* @author zhangzhixiang
* @data2018/09/19 18:56:43 * /
void insertDB(List<List<BaseColumn>> rows, Boolean isSuccess) throws Exception;
}
Copy the code
16. Implementation of Excel library entry (Clue)
/ * * *@Description: Excel cue entry interface *@Author: zhangzhixiang *@CreateDate: 2018/08/31 21:59:43 *@Version: 1.0 * /
@Service
public class ClueInsertServiceImpl implements BaseInsertService {
@Autowired
public ClueInfoDAO clueInfoDAO;
@Override
@Transactional(rollbackFor = Exception.class)
public void insertDB(List<List<BaseColumn>> rows, Boolean isSuccess) throws Exception {
// cue custom entry code}}Copy the code
17. Excel Public Service Interface (OSS)
/ * * *@Description: Excel Service Interface (OSS) *@Author: zhangzhixiang *@CreateDate: 2018/08/31 21:59:43 *@Version: 1.0 * /
public interface ExcelOperateService {
/** * Excel file import *@paramFileName indicates the original fileName *@paramThe code file is uniquely marked with *@paramModel Excel type identifier *@returnAnalytical results@throws Exception
*/
ExcelParseResultBO excelImport(String code, String model) throws Exception;
/** * Excel file export **@param response
* @paramRows needs to export data *@paramFileName fileName *@paramSheetName sheet name *@return void
* @author zxzhang
* @date2019/10/10 * /
void excelExport(HttpServletResponse response, List<List<String>> rows, String fileName, String sheetName) throws Exception;
}
Copy the code
18. Excel Public Service Implementation (OSS)
/ * * *@Description: Excel Public Service Implementation (OSS) *@Author: zhangzhixiang *@CreateDate: 2018/08/31 21:59:43 *@Version: 1.0 * /
@Service
public class ExcelOperateServiceImpl implements ExcelOperateService {
private static final Logger logger = LoggerFactory.getLogger(ExcelOperateServiceImpl.class);
private static final String SERVICE_ERROR_MESSAGE = "Batch import failed";
/** * Excel file import **@paramThe code OSS file is uniquely marked *@paramModel Business type (clue, report) *@returnAnalytical results@throws Exception
*/
@Override
public ExcelOperateBO excelImport(String code, String model) throws Exception {
FileClient client = ClientFactory.createClientByType(BootstrapConsts.file_client_type);
ExcelOperateBO resultBO = null;
// get Excel data
List<List<String>> excelDatas = null;
excelDatas = ExcelRead.getSheetDataWithTitle(client.getFileStream(code), code, null);
String message;
if(null == excelDatas || excelDatas.size() < SimpleConst.TWO) {
message = "File has no valid data";
resultBO = new ExcelOperateBO();
resultBO.setFileOK(false);
resultBO.setMessage(message);
return resultBO;
}
logger.info("Number of records:" + excelDatas.size());
//2
resultBO = excelTitleCheck(excelDatas.get(0), model);
if(! resultBO.getFileOK()) {return resultBO;
}
// bind each column in Excel to the title name
List<List<BaseColumn>> excelRows = null;
excelRows = getExcelData(excelDatas, model);
if(null == excelRows || excelRows.size() == 0) {
resultBO = new ExcelOperateBO();
resultBO.setTotalNum(0);
resultBO.setSuccessNum(0);
resultBO.setFailNum(0);
resultBO.setFileOK(true);
return resultBO;
}
//4. Excel data verification
List<List<BaseColumn>> successRows = new ArrayList<>();
List<List<BaseColumn>> failRows = new ArrayList<>();
rowsCheck(excelRows, model, successRows, failRows);
/ / 5, put in storage
if(successRows.size() > 0) {
insertdb(successRows, model, true);
}
if(failRows.size() > 0) {
insertdb(failRows, model, false);
}
return resultBO;
}
/** * Excel file export **@param response
* @paramRows exports file contents (including titles) *@paramFileName Specifies the name of the export file *@paramSheetName Sheet name *@returnAnalytical results@throws Exception
*/
@Override
public void excelExport(HttpServletResponse response, List<List<String>> rows, String fileName, String sheetName) throws Exception {
ExcelWrite.exportExcel(response, rows, fileName, sheetName);
}
/** * data is stored **@paramRows data to be written *@paramModel Business type (clue, report) *@paramIsSuccess Is successful */
private void insertdb(List<List<BaseColumn>> rows, String model, Boolean isSuccess) throws Exception {
BatchImportStruBO struBO = ExcelTypeEnum.containKey(model).getStruBO();
BaseInsertService insertHandle = struBO.getInsertHandle();
insertHandle.insertDB(rows, isSuccess);
}
/** * Excek data check **@paramExcelRows Data to be checked *@paramModel Business type (clue, report) *@paramSuccessRows Verifies successful data *@paramFailRows Fails to verify data */
private void rowsCheck(List<List<BaseColumn>> excelRows, String model, List<List<BaseColumn>> successRows, List<List<BaseColumn>> failRows) throws Exception {
BaseRecordCheck recordCheck = ExcelTypeEnum.containKey(model).getStruBO().getRecordCheck();
List<BaseColumn> checkedRow = null;
boolean isSuccessCheck = true;
for (List<BaseColumn> rowData : excelRows) {
checkedRow = recordCheck.valueCheck(rowData);
if(checkedRow == null || checkedRow.size() == 0) {
continue;
}
for (BaseColumn cell : checkedRow) {
if(! cell.isCheckIsSuccess()) { isSuccessCheck = cell.isCheckIsSuccess();break; }}if(! isSuccessCheck) { failRows.add(checkedRow); }else {
successRows.add(checkedRow);
}
isSuccessCheck = true; }}/** * Get valid Excel data **@paramExcel Data *@return
* @throws Exception
*/
private List<List<BaseColumn>> getExcelData(List<List<String>> excelDatas, String model) throws Exception {
BatchImportStruBO batchImportStruBO = ExcelTypeEnum.containKey(model).getStruBO();
List<BaseColumn> titles = batchImportStruBO.getImportTitles();
excelDatas.remove(0);
for(int i = 0; i<excelDatas.size(); i++) {
List<String> row = valueTrim(excelDatas.get(i));
excelDatas.set(i, row);
}
List<List<BaseColumn>> excelRows = excelDataTidy(titles, excelDatas);
if (null == excelRows) {
throw new Exception("ExcelDatas data to excelRows data error");
}
return excelRows;
}
/** * Combine the title with the specific data **@paramTitles information *@paramRowList records data where the field index of the record is consistent with the corresponding title index *@return* /
private List<List<BaseColumn>> excelDataTidy(List<BaseColumn> titles, List<List<String>> rowList) {
List<List<BaseColumn>> datas = new ArrayList<>();
List<BaseColumn> rowData = null;
BaseColumn cellData = null;
List<String> excelRow = null;
for(int i = 0; i < rowList.size(); i++) {
rowData = new ArrayList<>();
excelRow = rowList.get(i);
for(int j = 0; j < excelRow.size(); j++) {
String excelCellValue = excelRow.get(j);
String title = null;
if(j < titles.size()) {
title = titles.get(j).getExcelName();
} else {
break;
}
cellData = new BaseColumn();
cellData.setExcelName(title);
cellData.setIsRequired(titles.get(j).getIsRequired());
cellData.setExcelValue(excelCellValue);
rowData.add(cellData);
}
datas.add(rowData);
}
return datas;
}
/** * verify that the title is compliant **@paramTitles information *@paramModel Business type (clue, report) *@return* /
private ExcelOperateBO excelTitleCheck(List<String> titles, String model) throws Exception {
BatchImportStruBO batchImportStruBO = ExcelTypeEnum.containKey(model).getStruBO();
String message = "Parsing title error";
titles = valueTrim(titles);
List<BaseColumn> orderTitles = batchImportStruBO.getImportTitles();
boolean titleCheck = titleCheck(titles, orderTitles);
if(! titleCheck) {throw new Exception(message);
}
ExcelOperateBO resultBO= new ExcelOperateBO();
resultBO.setFileOK(true);
return resultBO;
}
/** * Verify that the title in Excel contains the required title **@paramTitles information *@paramTitle information requested by orderTitles *@return* /
private boolean titleCheck(List<String> titles, List<BaseColumn> orderTitles) {
if (null == titles || titles.size() == 0) {
return false;
}
if(null == orderTitles || orderTitles.size() == 0) {
return false;
}
List<String> names = new ArrayList<>();
for (BaseColumn column : orderTitles) {
names.add(column.getExcelName());
}
for (String name : names) {
if(! titles.contains(name)) {return false; }}return true;
}
/** ** removes invalid Spaces **@paramValues to the blank string *@return* /
private List<String> valueTrim(List<String> values) {
if(null == values || values.size() == 0) {
return values;
}
for(int i=0; i < values.size(); i++) {
if(StringUtils.isBlank(values.get(i))) {
values.set(i, null);
continue;
} else{ values.set(i, values.get(i).trim()); }}returnvalues; }}Copy the code
19. Excel basic operations
/ * * *@Description: Excel basic operation *@Author: zhangzhixiang *@CreateDate: 2018/08/31 21:59:43 *@Version: 1.0 * /
public class ExcelBaseOperate {
private static final Logger logger = LoggerFactory.getLogger(ExcelBaseOperate.class);
private static final String SUFFIX_XLS = "xls";
private static final String SUFFIX_XLSX = "xlsx";
/** * Get the Excel operation class **@param inputStream
* @param name
* @return
* @throws Exception
*/
public static Workbook getWorkbook(InputStream inputStream, String name) throws Exception {
Workbook wb = null;
if (name.endsWith(SUFFIX_XLSX)) {
wb = new XSSFWorkbook(OPCPackage.open(inputStream));
} else if (name.endsWith(SUFFIX_XLS)) {
wb = WorkbookFactory.create(inputStream);
} else {
String errorMessage = "Unrecognized file type. File name:" + name;
logger.error(errorMessage);
throw new Exception(errorMessage);
}
returnwb; }}Copy the code
20. Excel reading operation
/ * * *@Description: Excel read operation *@Author: zhangzhixiang *@CreateDate: 2018/08/31 21:59:43 *@Version: 1.0 * /
public class ExcelRead {
private static final Logger logger = LoggerFactory.getLogger(ExcelRead.class);
private static SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
/** * gets the sheet page title **@paramInputStream Excel file stream *@paramExcelName Name of the Excel file *@paramSheetName Sheet Page name. If you do not enter it, the first sheet * is taken by default@return* /
public static List<String> getSheetTitle(InputStream inputStream, String excelName, String sheetName) throws Exception {
Workbook workbook = ExcelBaseOperate.getWorkbook(inputStream, excelName);
// If sheetname is not entered, the default is the first sheet
if (StringUtils.isBlank(sheetName)) {
sheetName = workbook.getSheetAt(0).getSheetName();
}
List<List<String>> sheetData = parseSheet(workbook, sheetName, 0.0);
if(null == sheetData || sheetData.size() == 0) {
return null;
}
return sheetData.get(0);
}
/** * Get the titled sheet page data **@paramInputStream Excel file stream *@paramExcelName Name of the Excel file *@paramSheetName Sheet Page name. If you do not enter it, the first sheet * is taken by default@return* /
public static List<List<String>> getSheetDataWithTitle(InputStream inputStream, String excelName, String sheetName) throws Exception {
Workbook workbook = ExcelBaseOperate.getWorkbook(inputStream, excelName);
// If sheetname is not entered, the default is the first sheet
if (StringUtils.isBlank(sheetName)) {
sheetName = workbook.getSheetAt(0).getSheetName();
}
List<List<String>> sheetData = parseSheet(workbook, sheetName, 0, workbook.getSheet(sheetName).getPhysicalNumberOfRows());
return sheetData;
}
/** * Parse sheet data **@paramWb Excel file *@paramSheetName Sheet Page name *@paramStartIndex Specifies the starting row for parsing, starting from 0 and containing the row *@paramThe end row of endIndex parsing, starting at 0, contains the row *@returnList: row; Layer 2 list: cell */
private static List<List<String>> parseSheet(Workbook wb, String sheetName, int startIndex, int endIndex) throws Exception {
String message = null;
Sheet sheet = wb.getSheet(sheetName);
if(null == sheet) {
message = sheetName + ", this sheet does not exist";
throw new Exception(message);
}
int lastRowNum = sheet.getPhysicalNumberOfRows();
if(startIndex < 0 || endIndex > lastRowNum || startIndex > endIndex) {
message = "Entered parse row scope error. StartIndex." + startIndex + ", endIndex:" + endIndex;
throw new Exception(message);
}
List<List<String>> sheetData = new ArrayList<>();
int cellCount = -1;
//row list
for (int i = startIndex; i < endIndex; i++) {
Row row = sheet.getRow(i);
if(null == row) {
continue;
}
List<String> rowData = new ArrayList<>();
cellCount = row.getLastCellNum();
//cell list
for(int j = 0; j < cellCount; j++) {
Cell cell = row.getCell(j);
if(null == cell) {
rowData.add(null);
continue;
}
rowData.add(getCellValue(cell));
}
sheetData.add(rowData);
}
return sheetData;
}
/** * Get the cell value **@param cell
* @return* /
private static String getCellValue(Cell cell) {
CellType type = cell.getCellTypeEnum();
String value = null;
if(CellType.NUMERIC.equals(type)) {
if(DateUtil.isCellDateFormatted(cell)) {
Date date = cell.getDateCellValue();
value = sdf.format(date);
} else{ cell.setCellType(CellType.STRING); value = String.valueOf(cell.getStringCellValue()); }}else if(CellType.BOOLEAN.equals(type)) {
value = String.valueOf(cell.getBooleanCellValue());
} else if(CellType.BLANK.equals(type)) {
value = null;
} else {
value = String.valueOf(cell.getStringCellValue());
}
returnvalue; }}Copy the code
21. Excel write operation
/ * * *@Description: Excel read operation *@Author: zhangzhixiang *@CreateDate: 2018/08/31 21:59:43 *@Version: 1.0 * /
public class ExcelWrite {
private static final Logger logger = LoggerFactory.getLogger(ExcelWrite.class);
public static final String SUFFIX = "xlsx";
/** * Export Excel **@param
* @return void
* @author zhangzhixiang
* @data2018/09/10 13:59:43 * /
public static void exportExcel(HttpServletResponse response, List<List<String>> rows, String fileName, String sheetName) {
Workbook workbook = createWb();
try(OutputStream out = response.getOutputStream()) {
Sheet sheet = workbook.createSheet(sheetName);
sheetAppendData(rows, sheet);
response.setContentType("application/ms-excel; charset=UTF-8");
response.setHeader("Content-Disposition"."attachment; filename="
.concat(String.valueOf(URLEncoder.encode(fileName, "UTF-8"))));
workbook.write(out);
} catch(Exception e) {
logger.error("ExcelWrite--exportExcel throw Exception.fileName:{}", fileName, e); }}/** * Generate Excel file streams **@param rows
* @param name
* @return java.io.ByteArrayOutputStream
* @author zhangzhixiang
* @date2018/09/10 20:54:46 * /
public static ByteArrayOutputStream writeData(List<List<String>> rows, String name) {
ByteArrayOutputStream out = new ByteArrayOutputStream();
Workbook workbook = createWb();
Sheet sheet= workbook.createSheet(name);
sheetAppendData(rows, sheet);
try {
workbook.write(out);
out.flush();
return out;
} catch(Exception e) {
logger.error("ExcelWrite-->writeData throw Exception.name:{}.", name, e);
return null; }}/** * sheet page padding data **@param rows
* @param sheet
* @return org.apache.poi.ss.usermodel.Sheet
* @author zhangzhixiang
* @date2018/09/10 10:59:48 * /
public static Sheet sheetAppendData(List<List<String>> rows, Sheet sheet) {
int lastRowNum = sheet.getPhysicalNumberOfRows();
if(null == rows || rows.size() == 0) {
return sheet;
}
Row row = null;
for (List<String> rowData : rows) {
row = sheet.createRow(lastRowNum);
rowCreateData(rowData, row);
lastRowNum = lastRowNum + 1;
}
return sheet;
}
/** * generates row **@param datas
* @param row
* @return org.apache.poi.ss.usermodel.Row
* @author zhangzhixiang
* @date2018/09/10 20:35:46 * /
private static Row rowCreateData(List<String> datas, Row row) {
Cell cell = null;
for(int i = 0; i < datas.size(); i++) {
cell = row.createCell(i);
cell.setCellType(CellType.STRING);
cell.setCellValue(datas.get(i));
}
return row;
}
/** * Create workbook **@param
* @return org.apache.poi.ss.usermodel.Workbook
* @author zhangzhixiang
* @date2018/09/10 20:35:42 * /
public static Workbook createWb(a) {
Workbook wb = null;
if(SimpleConst.EXCEL_EXT.equals(SUFFIX)) {
wb = new XSSFWorkbook();
} else {
wb = new HSSFWorkbook();
}
returnwb; }}Copy the code
22. Constant class definitions
/ * * *@Description: Simple on definition *@Author: zhangzhixiang *@CreateDate: 2018/08/31 11:34:56 *@Version: 1.0 * /
public class SimpleConsts {
public static final String EXCEL_EXT = "xlsx";
}
Copy the code
23, ExcelOperateBO
/ * * *@Description: Excel operation result *@Author: zhangzhixiang *@CreateDate: 2018/09/05 19:54:32 *@Version: 1.0 * /
@Data
public class ExcelOperateBO {
/** * Whether the file succeeded */
private Boolean fileOK;
/** * Failure cause */
private String message;
/** * total number of rows */
private Integer totalNum;
/** * number of successful rows */
private Integer successNum;
/** * Number of failed rows */
private Integer failNum;
}
Copy the code
24, the controller layer
/ * * *@Description: Public service interface - Controller *@Author: zhangzhixiang *@CreateDate: 2018/08/31 11:34:56 *@Version: 1.0 * /
@RestController
@RequestMapping("/api/ops/common")
public class CommonController extends ExceptionHandlerAdvice {
private final Logger logger = LoggerFactory.getLogger(this.getClass());
@Autowired
private ExcelOperateService excelOperateService;
@RequestMapping(value = "/excelImport", method = RequestMethod.POST)
public ResponseResult excelImport(@RequestBody ExcelImportBO excelImportBO) throw Exception {
Boolean flag = paramCheckModule(excelImportBO.getModule());
if(! flag) {return new ResponseResult().setSuccess(false).setMessage(ResultCode.ERROR_PARAM_CHECK.getMessage()).setCode(ResultCode.ERROR_PARAM_CHECK.getCode());
}
ExcelOperateResultBO data = excelOperateService.excelImport(excelImportBO.getCode());
return new ResponseResult().setSuccess(true).setData(data).setMessage(ResultCode.SUCCESS.getMessage);
}
@RequestMapping(value = "/excelExport", method = RequestMethod.GET)
public void excelExport(@RequestBody List<List<String>> rows, String fileName, String sheetName, HttpServletResponse response) throw Exception {
excelOperateService.excelExport(response, rows, fileName, sheetName);
}
private boolean paramCheckModule(String module) throws Exception {
ExcelTypeEnum type = ExcelTypeEnum.containKey(module);
if(null == type) {
logger.error("********************No specified module was found.module:{}**********************".module);
return false;
}
return true; }}Copy the code
The whole article is completely pure hand, if you feel helpful, remember to pay attention to praise yo ~~