1, Table structure design, import and export templates.
Note: the structure of blogger tree is 8-level structure, which is divided into two tables due to business relationship. This table is divided into four levels, and the structure is as follows:
Building unit floor room
Excel The table item name field is obtained from the first table and can be ignored.
The table only intercepts a few tree structure related fields, and the business fields can be added according to the needs.
Export Excel style as follows.
The import Excel template style is as follows:
2, Recursive query tree structure
1. The idea is as follows
- Because there are many levels of blogger structure, it is divided into two levels, so the sub table can be understood as the detail table. The tree structure data of the detail table corresponds to the item id of the main table.
- Input parameter: according to the input parameter, the project id obtains all child data sets (including buildings, units, floors and rooms) under the project first.
- The returned entity must contain the child List field and the custom method to get all leaf nodes
private List<****> children = new ArrayList<SysDeptInfoTreeVo>();
public int getLeftNum(){
int count = 0 ;
if (children.isEmpty()){
return 1;
}else{
for (**** s : children){
count+= s.getLeftNum();
}
}
return count;
}
2. Code example
//Obtain all data under this item according to the item id
List<SysDeptInfoTreeVo> sysDeptInfoTreeVoList = deptInfoMapper.selectListByDeptId(sysDeptInfoDto.getDeptId());
//Get parent node
List<SysDeptInfoTreeVo> collect = sysDeptInfoTreeVoList.stream().filter(sysDeptInfoTreeVo -> sysDeptInfoTreeVo.getParentId() == 0).map(s -> {
s.setChildren(getChildren(s, sysDeptInfoTreeVoList));
return s;
}).sorted(Comparator.comparing(SysDeptInfoTreeVo::getId)).collect(Collectors.toList());
- Code interpretation:
- First obtain all data sets under this item from the sub table, that is, the detail table.
- Filter and obtain the first level node. The first level node has no parent node. After obtaining each first level node, set the child node and call the recursive method.
/**
* Recursive query child node
* @param sysDeptInfoTreeVo Root node
* @param sysDeptInfos All nodes
* @return Root node information
*/
public static List<SysDeptInfoTreeVo> getChildren(SysDeptInfoTreeVo sysDeptInfoTreeVo, List<SysDeptInfoTreeVo> sysDeptInfos) {
List<SysDeptInfoTreeVo> children = sysDeptInfos.stream().filter(s -> sysDeptInfoTreeVo.getId().equals(s.getParentId())).map(sysDeptInfo -> {
System.out.println("'sysDeptInfo' = " + sysDeptInfo.toString());
sysDeptInfo.setChildren(getChildren(sysDeptInfo,sysDeptInfos));
return sysDeptInfo;
}).sorted(Comparator.comparing(SysDeptInfoTreeVo::getId)).collect(Collectors.toList());
return children;
}
2, New tree structure
The idea is as follows
-
The new parameter entry entity must have parentId and parent node id
-
Add and verify according to specific business
-
To add a new node, you need to judge whether it is the first level node,
For the first level node, the parent node id is 0, the ancestor list is 0, and the level is 1
Otherwise, query the parent node first according to the parent node id, obtain the id from the parent node as the parentId, the parent node grandparent list + parent node id as the grandparent list parameter, and the level is the parent node level + 1 -
The new operation can be processed hierarchically without sample code
III Tree structure modification.
The idea is as follows
- The modification operation needs to be modified according to the actual business. The blogger modification has no modification level operation, so it is only a direct update after verification.
4, Recursive deletion tree structure
1. The idea is as follows
- Input parameter: the id of a root node. As long as you get all the descendant data of this root node, you can delete them all.
2. Code example
//Get this item by id
SysDeptInfo sysDeptInfo = deptInfoMapper.selectById(id);
Assert.isNull(sysDeptInfo,"No item information found");
//Gets the collection of children of this project
List<SysDeptInfo> sysDeptInfoList = deptInfoMapper.selectList(new QueryWrapper<SysDeptInfo>().eq("dept_id",sysDeptInfo.getDeptId()));
//list of all descendants under this item
List<SysDeptInfo> list = new ArrayList();
list.add(sysDeptInfo);
//Call recursive method
list = getChildrenList(sysDeptInfo, sysDeptInfoList,list);
- Code interpretation:
- First, obtain this piece of data according to the id of a root node, and then obtain the data set of each level under the same project as the root node according to the item id of this data. This set must contain the descendant data under this piece of data.
- Create and return a list, which contains all data sets to be deleted and this node.
- Call recursive method
/**
* Recursive query child node
* @param sysDeptInfo Root node
* @param sysDeptInfoList list of all data under the same project as the root node
* @param list Return to list
* @return sysDeptInfoList list collection of all child nodes
*/
public static List<SysDeptInfo> getChildrenList(SysDeptInfo sysDeptInfo, List<SysDeptInfo> sysDeptInfoList,List<SysDeptInfo> list) {
List<SysDeptInfo> resList = sysDeptInfoList.stream().filter(s -> sysDeptInfo.getId().equals(s.getParentId())).map(s -> {
list.add(s);
List<SysDeptInfo> childrenList = getChildrenList(s, sysDeptInfoList, list);
return s;
}).collect(Collectors.toList());
return list;
}
- After obtaining all the list sets to be deleted, delete them from the table.
Refer to the link below for Excel import and export, and modify and improve it as required: Example of Excel import and export completed by Java POI
The reference blogger poi version is lower, if 4.0 is used The version of the initial poi needs to be modified is inconsistent, and the problem of some methods of modification has been corrected in the following code.
5, Tree structure recursive export Excel
1. The idea is as follows
- The cell merging operation has not been carried out for exporting Excel. If the boss writes it well, if you don't mind, share it in the comment area. Thank you.
- Input parameter: result Vo of recursive query tree structure
2. Code example
- Code interpretation:
- Just focus on the methods exportTaskSumPoi and ExcelChildrenNew.
- exportTaskSumPoi method starts from the third row to create rows and cells. Purpose: because the blogger structure is divided into primary and secondary tables, and the leaf node of the primary table is the item of the secondary table. For example, the leaf of the primary table is accurate to love apartment, then the first level node of the secondary table starts from the building, that is, the first building of love apartment, but the blogger adds a field item id to specify these buildings and cells, Floor, room belongs to a project, for example:
- The id of love apartment in the main table is 105
- Add Dept to the sub table_ The ID field is used to specify which item it belongs to
- Return to the third row and start to create rows and cells. The purpose is to obtain the item information from the main table and put the item information in the first cell of the third row. The focus is the following recursive method ExcelChildrenNew
import java.io.IOException;
import java.io.OutputStream;
import java.text.SimpleDateFormat;
import java.util.List;
import com.baomidou.mybatisplus.core.toolkit.CollectionUtils;
import com.zensun.system.domain.vo.SysDeptInfoTreeVo;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.stereotype.Component;
/**
* @program: family-file-pc-b
* @description: Excel Tree structure export Util
* @author: LYK
* @create: 2021-05-20 08:37
**/
@Component
public class ExcelExportUtil {
private int cols = 9;//Table column in excel
private String sheetTitle = "Project room information";
public void setCols(int cols) {
this.cols = cols;
}
public void setSheetTitle(String sheetTitle) {
this.sheetTitle = sheetTitle;
}
/**
* POI : Export data and store it in Excel
*
* @param os
* Output stream (action: OutputStream os = response.getOutputStream();)
* @param sysDeptInfoTreeVo
* Data to export (root data)
*/
public void exportTaskSumPoi(OutputStream os, SysDeptInfoTreeVo sysDeptInfoTreeVo) {
//Take out the data source;
String headTitle = sheetTitle;//title;
SimpleDateFormat sdf = new SimpleDateFormat("yyyy year MM month dd day");
try {
// Create Excel Workbook
Workbook book = null;
try {
book = new XSSFWorkbook();//excell2007
} catch (Exception ex) {
book = new HSSFWorkbook();//excell2003
}
// Create a worksheet in the Excel workbook;
Sheet sheet = book.createSheet(sheetTitle);
//Set title and header;
createTitle(book, sheet, headTitle);
createHead(book, sheet);
int startCellCount = -1 ; //Control which cell starts the field
int endCellCount = 0;
int rowCount = 2 ; //Controls the creation of line segments
//Create a line from the third line --- project information
Row row = sheet.createRow(rowCount);
//Create a cell -- item information
Cell cell0 = row.createCell(0);
cell0.setCellValue(sysDeptInfoTreeVo.getDeptName()==null?"null":sysDeptInfoTreeVo.getDeptName());
// Get building information
ExcelChildrenNew(row,sysDeptInfoTreeVo, sheet, startCellCount,endCellCount,rowCount);
// Write data and save the corresponding Excel workbook to disk
book.write(os);
book.close();
os.close();
} catch (IOException e) {
e.printStackTrace();
}
}
/**
* Recursively obtain children and set rows and columns for children
*
* @param sheet
*/
private void ExcelChildrenNew(Row row,SysDeptInfoTreeVo sysDeptInfoTreeVo, Sheet sheet, int startCellCount,int endCellCount,int rowCount) {
Row newRow = row;
//Each sub level of each + 2 purpose occupies 2 cells.
startCellCount= startCellCount+2;
endCellCount= endCellCount+2;
List<SysDeptInfoTreeVo> children = sysDeptInfoTreeVo.getChildren();
if (CollectionUtils.isNotEmpty(children)){
for (int i=0; i<children.size(); i++){
if (i==0){
//This indicates that the first child node should be in the same row as the parent node in Excel
Cell cell0 = newRow.createCell(startCellCount);
Cell cell1 = newRow.createCell(endCellCount);
cell0.setCellValue(children.get(i).getDeptName()==null?"null":children.get(i).getDeptName());
cell1.setCellValue(children.get(i).getRoomTypeName()==null?"null":children.get(i).getRoomTypeName());
}else{
//Here, the number of leaf nodes of all parent nodes is obtained as the number of rows
for (int j = 1 ; j<= i ; j++){
rowCount += children.get(j-1).getLeftNum();
}
newRow = sheet.createRow(rowCount);
Cell cell0 = newRow.createCell(startCellCount);
Cell cell1 = newRow.createCell(endCellCount);
cell0.setCellValue(children.get(i).getDeptName()==null?"null":children.get(i).getDeptName());
cell1.setCellValue(children.get(i).getRoomTypeName()==null?"null":children.get(i).getRoomTypeName());
}
ExcelChildren(newRow,children.get(i),sheet,startCellCount,endCellCount,rowCount);
}
}
}
/**
* Set title for excel
*
* @param sheet
*/
public void createTitle(Workbook book, Sheet sheet, String headTitle) {
CellStyle style = createTitleStyle(book);
Row row = sheet.createRow(0);// Create the first row and set the title of the table;
row.setHeightInPoints(36);//Set the height of the row to 34 points
Cell cell = row.createCell(0);
cell.setCellValue(headTitle);
cell.setCellStyle(style);
sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, cols-1));//The first row spans all columns of the table;
}
/**
* Sets the header of the exported table
*
* @param book
* @param sheet
*/
private void createHead(Workbook book, Sheet sheet) {
// Format cells (text)
// Second row header row
String title = "";
CellStyle style = createHeadStyle(book);
Row row = sheet.createRow(1);// Create first row
row.setHeightInPoints(22);//Set the height of the row to 20 points
for (int j = 0; j < cols; j++) {
Cell cell = row.createCell(j);
cell.setCellType(CellType.STRING);
if (j == 0) {
title = "Project name";
sheet.setColumnWidth(j, title.getBytes().length * 2 * 256);
}
if (j == 1) {
title = "Building";
sheet.setColumnWidth(j, title.getBytes().length * 2 * 256);
}
if (j == 2) {
title = "Format";
}
if (j == 3) {
title = "unit";
sheet.setColumnWidth(j, title.getBytes().length * 2 * 256);
}
if (j == 4) {
title = "Format";
}
if (j == 5) {
title = "floor";
}
if (j == 6) {
title = "Format";
}
if (j == 7) {
title = "room";
}
if (j == 8) {
title = "Format";
}
cell.setCellValue(title);
cell.setCellStyle(style);
}
}
/**
* Create a title style
* @param book
* @return
*/
public CellStyle createTitleStyle(Workbook book) {
CellStyle cellStyle = book.createCellStyle();
cellStyle.setBorderTop(BorderStyle.THIN);//border-top
cellStyle.setBorderBottom(BorderStyle.THIN);//bottom
cellStyle.setBorderLeft(BorderStyle.THIN);//border-left
cellStyle.setBorderRight(BorderStyle.THIN);//border-right
cellStyle.setAlignment(HorizontalAlignment.CENTER);//horizontally
cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);//Vertical center
Font font = book.createFont();
font.setFontHeightInPoints((short) 20); // font size
font.setFontName("Song style");
cellStyle.setFont(font);
return cellStyle;
}
/**
* Create header style
* @param book
* @return
*/
public CellStyle createHeadStyle(Workbook book) {
CellStyle cellStyle = book.createCellStyle();
cellStyle.setBorderTop(BorderStyle.THIN);//border-top
cellStyle.setBorderBottom(BorderStyle.THIN);//bottom
cellStyle.setBorderLeft(BorderStyle.THIN);//border-left
cellStyle.setBorderRight(BorderStyle.THIN);//border-right
cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND); // Fill cell
cellStyle.setFillForegroundColor(HSSFColor.HSSFColorPredefined.LIGHT_TURQUOISE.getIndex()); // Fill in light blue
cellStyle.setAlignment(HorizontalAlignment.CENTER);//horizontally
cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);//Vertical center
Font font = book.createFont();
font.setFontHeightInPoints((short) 11); // font size
font.setFontName("Blackbody");
cellStyle.setFont(font);
return cellStyle;
}
/**
* Create cell styles for the first 8 columns of the content section
* @param book
* @return
*/
public CellStyle createContentStyle(Workbook book) {
CellStyle cellStyle = book.createCellStyle();
cellStyle.setBorderTop(BorderStyle.THIN);//border-top
cellStyle.setBorderBottom(BorderStyle.THIN);//bottom
cellStyle.setBorderLeft(BorderStyle.THIN);//border-left
cellStyle.setBorderRight(BorderStyle.THIN);//border-right
cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND); // Fill cell
cellStyle.setFillForegroundColor(HSSFColor.HSSFColorPredefined.LIGHT_TURQUOISE.getIndex()); // Fill in light blue
cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);//Vertical center
cellStyle.setWrapText(true);//Auto wrap
Font font = book.createFont();
font.setFontHeightInPoints((short) 11); // font size
font.setFontName("Song style");
cellStyle.setFont(font);
return cellStyle;
}
/**
* Create cell styles for other parts of the content
* @param book
* @return
*/
public CellStyle createOtherStyle(Workbook book) {
CellStyle cellStyle = book.createCellStyle();
cellStyle.setBorderTop(BorderStyle.THIN);//border-top
cellStyle.setBorderBottom(BorderStyle.THIN);//bottom
cellStyle.setBorderLeft(BorderStyle.THIN);//border-left
cellStyle.setBorderRight(BorderStyle.THIN);//border-right
cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);//Vertical center
cellStyle.setWrapText(true);//Auto wrap
Font font = book.createFont();
font.setFontHeightInPoints((short) 11); // font size
font.setFontName("Song style");
cellStyle.setFont(font);
return cellStyle;
}
/**
* Set the annotation style in the string;
* @param book
* @param str Incoming pending string;
* @return
*/
public RichTextString noteFontStyle(Workbook book, String str) {
//Define font
Font hFont = book.createFont();
hFont.setFontHeightInPoints((short) 10);//font size
hFont.setFontName("Regular script");
RichTextString richString = null;
try {//excel 2007
richString = new XSSFRichTextString(str);//excel 2007
} catch (Exception e) {
richString = new HSSFRichTextString(str);//excel 2003
}
if (str.indexOf("(") == -1) {
return richString;
}
//Set the font style to the string;
richString.applyFont(str.indexOf("("), str.indexOf(")"), hFont);
return richString;
}
/**
* Set date format;
* @param book
* @return
*/
public CellStyle setDateStyle(Workbook book) {
CellStyle style = book.createCellStyle();
try {//excel 2007
DataFormat format = book.createDataFormat();
style.setDataFormat(format.getFormat("yyyy year MM month dd day"));
} catch (Exception e) {//excel 2003
style.setDataFormat(HSSFDataFormat.getBuiltinFormat("yyyy year MM month dd day"));
}
return style;
}
}
6, Import tree structure Excel
1. The idea is as follows
- The import method tool class can be used directly without any modification. The return value is list < string [] > corresponding to the data in each row of Excel.
2. Code example
- Import tool class ExcelImportUtil: just call the readerExcel method.
- Interpretation of participation:
- InputStream inIo : file.getInputStream()
- String sheetName: the table name in the first row of Excel
- int minColumns: total number of columns
import java.io.IOException;
import java.io.InputStream;
import java.io.PrintStream;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import javax.xml.parsers.ParserConfigurationException;
import javax.xml.parsers.SAXParser;
import javax.xml.parsers.SAXParserFactory;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.openxml4j.exceptions.OpenXML4JException;
import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.ss.usermodel.BuiltinFormats;
import org.apache.poi.ss.usermodel.DataFormatter;
import org.apache.poi.xssf.eventusermodel.ReadOnlySharedStringsTable;
import org.apache.poi.xssf.eventusermodel.XSSFReader;
import org.apache.poi.xssf.model.StylesTable;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;
import org.springframework.stereotype.Component;
import org.xml.sax.Attributes;
import org.xml.sax.InputSource;
import org.xml.sax.SAXException;
import org.xml.sax.XMLReader;
import org.xml.sax.helpers.DefaultHandler;
/**
* @description: ExcelImportUtil
* @author: LYK
* @create: 2021-05-21 08:09
**/
public class ExcelImportUtil {
//Styles existing in excll;
enum xssfDataType {
BOOL, ERROR, FORMULA, INLINESTR, SSTINDEX, NUMBER;
}
/**
*/
class MyXSSFSheetHandler extends DefaultHandler {
/**
* Table style
*/
private StylesTable stylesTable;
/**
* Unique string in table
*/
private ReadOnlySharedStringsTable sharedStringsTable;
/**
* data output
*/
private final PrintStream output;
/**
* Maximum number of columns
*/
private final int minColumnCount;
// Set when you see the v start element
private boolean vIsOpen;
// Set when the cell start element appears;
//Used when you see the cell close element.
private xssfDataType nextDataType;
// Used to format numeric cell values
private short formatIndex;
private String formatString;
private final DataFormatter formatter;
private int thisColumn = -1;
// Print to the last column of the output stream
private int lastColumnNumber = -1;
// Collect the roles you see
private StringBuffer value;
private String[] record;
private List<String[]> rows = new ArrayList<String[]>();
private boolean isCellNull = false;
/**
* Objects required for analysis.
* @param styles
* @param strings Shared string
* @param cols Minimum number of columns to display
* @param target
*/
public MyXSSFSheetHandler(StylesTable styles, ReadOnlySharedStringsTable strings, int cols, PrintStream target) {
this.stylesTable = styles;
this.sharedStringsTable = strings;
this.minColumnCount = cols;
this.output = target;
this.value = new StringBuffer();
this.nextDataType = xssfDataType.NUMBER;
this.formatter = new DataFormatter();
record = new String[this.minColumnCount];
rows.clear();// The row set is emptied every time it is read
}
/*
* The first execution method is used to set the number type of the cell (such as date, number, string, etc.);
*/
public void startElement(String uri, String localName, String name, Attributes attributes) throws SAXException {
if ("inlineStr".equals(name) || "v".equals(name)) {
vIsOpen = true;
// Clear contents cache
value.setLength(0);
}
// c => cell
else if ("c".equals(name)) {
// Get the cell reference
String r = attributes.getValue("r");
int firstDigit = -1;
for (int c = 0; c < r.length(); ++c) {
if (Character.isDigit(r.charAt(c))) {
firstDigit = c;
break;
}
}
thisColumn = nameToColumn(r.substring(0, firstDigit));
// Set up defaults.
this.nextDataType = xssfDataType.NUMBER;
this.formatIndex = -1;
this.formatString = null;
String cellType = attributes.getValue("t");
String cellStyleStr = attributes.getValue("s");
if ("b".equals(cellType))
nextDataType = xssfDataType.BOOL;
else if ("e".equals(cellType))
nextDataType = xssfDataType.ERROR;
else if ("inlineStr".equals(cellType))
nextDataType = xssfDataType.INLINESTR;
else if ("s".equals(cellType))
nextDataType = xssfDataType.SSTINDEX;
else if ("str".equals(cellType))
nextDataType = xssfDataType.FORMULA;
else if (cellStyleStr != null) {
// It's a number, but almost certainly one
// with a special style or format
int styleIndex = Integer.parseInt(cellStyleStr);
XSSFCellStyle style = stylesTable.getStyleAt(styleIndex);
this.formatIndex = style.getDataFormat();
this.formatString = style.getDataFormatString();
if (this.formatString == null)
this.formatString = BuiltinFormats.getBuiltinFormat(this.formatIndex);
}
}
}
/*
* The last execution method;
*/
public void endElement(String uri, String localName, String name) throws SAXException {
String thisStr = null;
// v => contents of a cell
if ("v".equals(name)) {
// Process the value content as needed.
// At this point, the characters() method may be called multiple times
switch (nextDataType) {
case BOOL:
char first = value.charAt(0);
thisStr = first == '0' ? "FALSE" : "TRUE";
break;
case ERROR:
thisStr = "\"ERROR:" + value.toString() + '"';
break;
case FORMULA:
// A formula could result in a string value,
// so always add double-quote characters.
thisStr = '"' + value.toString() + '"';
break;
case INLINESTR:
// TODO: have seen an example of this, so it's untested.
XSSFRichTextString rtsi = new XSSFRichTextString(value.toString());
thisStr = '"' + rtsi.toString() + '"';
break;
//character string
case SSTINDEX:
String sstIndex = value.toString();
try {
int idx = Integer.parseInt(sstIndex);
//Get content value according to idx index value
XSSFRichTextString rtss = new XSSFRichTextString(sharedStringsTable.getEntryAt(idx));
thisStr = rtss.toString();
} catch (NumberFormatException ex) {
output.println("Failed to parse SST index '" + sstIndex + "': " + ex.toString());
}
break;
case NUMBER:
String n = value.toString();
// Determine whether it is a date format
if (HSSFDateUtil.isADateFormat(this.formatIndex, n)) {
Double d = Double.parseDouble(n);
Date date = HSSFDateUtil.getJavaDate(d);
thisStr = formateDateToString(date);
} else if (this.formatString != null)
thisStr = formatter.formatRawCellContents(Double.parseDouble(n), this.formatIndex,
this.formatString);
else
thisStr = n;
break;
default:
thisStr = "(TODO: Unexpected type: " + nextDataType + ")";
break;
}
// Output after we've seen the string contents
// Emit commas for any fields that were missing on this row
if (lastColumnNumber == -1) {
lastColumnNumber = 0;
}
//Judge whether the cell value is empty
if (thisStr == null || "".equals(isCellNull)) {
isCellNull = true;// Sets whether the cell is null
}
record[thisColumn] = thisStr;
// Update column
if (thisColumn > -1)
lastColumnNumber = thisColumn;
} else if ("row".equals(name)) {
// Print out any missing commas if needed
if (minColumns > 0) {
// Columns are 0 based
if (lastColumnNumber == -1) {
lastColumnNumber = 0;
}
boolean recordflag = record[0] != null;
for (int i = 1; i < minColumns; i++) {
recordflag = recordflag || (record[i] != null);
}
if (isCellNull == false && recordflag)// Judge whether there is a blank line
{
rows.add(record.clone());
isCellNull = false;
for (int i = 0; i < record.length; i++) {
record[i] = null;
}
}
}
lastColumnNumber = -1;
}
}
public List<String[]> getRows() {
return rows;
}
public void setRows(List<String[]> rows) {
this.rows = rows;
}
/**
* Capture characters only when the appropriate element is opened. At first it was just "v"; Expand to inlinestr
*/
public void characters(char[] ch, int start, int length) throws SAXException {
if (vIsOpen)
value.append(ch, start, length);
}
/**
* Convert Excel column names (such as "C") to zero based indexes
*
* @param name
* @return The index corresponding to the specified name
*/
private int nameToColumn(String name) {
int column = -1;
for (int i = 0; i < name.length(); ++i) {
int c = name.charAt(i);
column = (column + 1) * 26 + c - 'A';
}
return column;
}
private String formateDateToString(Date date) {
SimpleDateFormat sdf = new SimpleDateFormat("yyyy/MM/dd day");//format date
return sdf.format(date);
}
}
private OPCPackage xlsxPackage;
private int minColumns;
private PrintStream output;
private String sheetName;
/**
* Create a new xlsx - > CSV converter
*
* @param pkg
* XLSX package to process
* @param output
* Print stream to output csv to
* @param minColumns
* The minimum number of columns to output, or - 1 indicates no minimum value
*/
public ExcelImportUtil(OPCPackage pkg, PrintStream output, String sheetName, int minColumns) {
this.xlsxPackage = pkg;
this.output = output;
this.minColumns = minColumns;
this.sheetName = sheetName;
}
/**
*Parses and displays the contents of a worksheet using the specified style and shared string table.
*
* @param styles
* @param strings
* @param sheetInputStream
*/
public List<String[]> processSheet(StylesTable styles, ReadOnlySharedStringsTable strings,
InputStream sheetInputStream) throws IOException, ParserConfigurationException, SAXException {
InputSource sheetSource = new InputSource(sheetInputStream);
SAXParserFactory saxFactory = SAXParserFactory.newInstance();
SAXParser saxParser = saxFactory.newSAXParser();
XMLReader sheetParser = saxParser.getXMLReader();
MyXSSFSheetHandler handler = new MyXSSFSheetHandler(styles, strings, this.minColumns, this.output);
sheetParser.setContentHandler(handler);
//Parse each record in excel. In this process, the three functions of startElement(), characters(), and endElement() will be executed in turn
sheetParser.parse(sheetSource);
return handler.getRows();
}
/**
* Initializing this handler will
*
* @throws IOException
* @throws OpenXML4JException
* @throws ParserConfigurationException
* @throws SAXException
*/
public List<String[]> process() throws IOException, OpenXML4JException, ParserConfigurationException, SAXException {
ReadOnlySharedStringsTable strings = new ReadOnlySharedStringsTable(this.xlsxPackage);
XSSFReader xssfReader = new XSSFReader(this.xlsxPackage);
List<String[]> list = null;
StylesTable styles = xssfReader.getStylesTable();
XSSFReader.SheetIterator iter = (XSSFReader.SheetIterator) xssfReader.getSheetsData();
int index = 0;
while (iter.hasNext()) {
InputStream stream = iter.next();
String sheetNameTemp = iter.getSheetName();
if (this.sheetName.equals(sheetNameTemp)) {
list = processSheet(styles, strings, stream);
stream.close();
++index;
}
}
return list;
}
/**
* Read Excel
*
* @param inIo
* File stream
* @param sheetName
* sheet name
* @param minColumns
* Total number of columns
* @return
* @throws SAXException
* @throws ParserConfigurationException
* @throws OpenXML4JException
* @throws IOException
*/
public static List<String[]> readerExcel(InputStream inIo, String sheetName, int minColumns) throws IOException,
OpenXML4JException, ParserConfigurationException, SAXException {
OPCPackage p = OPCPackage.open(inIo);
ExcelImportUtil xlsx2csv = new ExcelImportUtil(p, System.out, sheetName, minColumns);
List<String[]> list = xlsx2csv.process();
p.close();
return list;
}
}
- Import method:
- Code study:
- sysDeptInfoMap function: because Excel is read line by line, save is performed line by line
- Take the following Excel as an example:
- In the first line, save the "one building" entity in map key LD
- In the second line, save the "two buildings" entity in the map key as LD, overwrite the first line LD, save the "two buildings one unit" entity as DY and the "one unit 1F" entity as LC, because the insertion operation child needs the parent entity to obtain the parent id and ancestor list. At this time, the parent entity can be obtained from the map according to the corresponding key.
- The third row also performs the save map operation, so the data in this map will always have parent data.
- The basic format of Excel is consistent, and the rules of their respective formats can be found out for analysis and storage. The following is only an analytical idea.
@Override
public void importExcel(MultipartFile file, HttpServletRequest req, HttpServletResponse resp)throws IOException, OpenXML4JException, ParserConfigurationException, SAXException {
List<String[]> strings = ExcelImportUtil.readerExcel(file.getInputStream(), "Project room information", 9);
//id of the project to which the imported project room information belongs
Integer deptId = 0;
//This map collection is used to hold parent objects.
Map<String,SysDeptInfo> sysDeptInfoMap = new HashMap<>();
for (int i = 0; i < strings.size() ; i++) {
if (i>1){
//Convert array to collection
List<String> list = Arrays.asList(strings.get(i));
if (StringUtils.isNotBlank(list.get(0))){
// When the item id is inserted, it indicates that it is not convenient to check the item id
}
if (StringUtils.isNotBlank(list.get(1)) && StringUtils.isNotBlank(list.get(2))){
//Building operation
//Insert building information
deptInfoMapper.insert(sysDeptInfo);
//Deposit the building in Maop
sysDeptInfoMap.put("LD",LD);
}
if (StringUtils.isNotBlank(list.get(3))){
//Get parent building information
SysDeptInfo father = sysDeptInfoMap.get("LD");
//unit
SysDeptInfo sysDeptInfo = new SysDeptInfo();
sysDeptInfo.setParentId(father.getId());
sysDeptInfo.setAncestors(father.getAncestors() + "," + sysDeptInfo.getParentId());
sysDeptInfo.setLevel(father.getLevel() + 1);
//Insert unit information
deptInfoMapper.insert(sysDeptInfo);
//Save cells in Map
sysDeptInfoMap.put("DY",sysDeptInfo);
}
if (StringUtils.isNotBlank(list.get(5))){
//floor
//Get parent information
SysDeptInfo father = sysDeptInfoMap.get("DY");
//floor
SysDeptInfo sysDeptInfo = new SysDeptInfo();
sysDeptInfo.setParentId(father.getId());
sysDeptInfo.setAncestors(father.getAncestors() + "," + sysDeptInfo.getParentId());
//Insert floor information
deptInfoMapper.insert(sysDeptInfo);
//Save floors to Map
sysDeptInfoMap.put("LC",sysDeptInfo);
}
if (StringUtils.isNotBlank(list.get(7)) && StringUtils.isNotBlank(list.get(8))){
//room
//Get parent information
SysDeptInfo father = sysDeptInfoMap.get("LC");
//room
SysDeptInfo sysDeptInfo = new SysDeptInfo();
sysDeptInfo.setParentId(father.getId());
sysDeptInfo.setAncestors(father.getAncestors() + "," + sysDeptInfo.getParentId());
sysDeptInfo.setLevel(father.getLevel() + 1);
//Insert room information
deptInfoMapper.insert(sysDeptInfo);
}
}
}
sysDeptInfoMap.clear();
}