1. Foreword
- Initial: The reason is that the data needs to be imported into the database in the form of an Excel table, but because the previous people used the ExcelUtil tool class, in line with the principle of less changes, they did not use the POI method to operate the Excel table, but used The original tool class to operate
- Overall function: Receive Excel files from the front end. Since ExcelUtil only supports xls format, if it is in xlsx format, you need to use e-iceblue to convert the format, and then take out the data in the Excel file and import it into the database
- Disadvantages: A redundant intermediate file will be generated during the format conversion process using e-iceblue, and I have tried many methods but cannot delete it; the conversion process takes a certain amount of time; pom.xml needs to be updated, and it is easy to report errors
- Subsequent optimization: try to use POI to operate Excel table import; batch insert, don’t insert one by one
2. Steps
package com.ev_image.gdwebgl.tools;
import jxl. Cell;
import jxl.Sheet;
import jxl. Workbook;
import jxl.write.Label;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import javax.servlet.http.HttpServletResponse;
import java.io.InputStream;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.util.*;
/**
* excel tools
*
* @since 0.1.0
*/
public class ExcelUti {<!-- -->
/**
* @param listMap multiple data sources
* @param out export stream
* @MethodName : listToExcel
* @Description : Export Excel (can be exported to the local file system or to the browser, the size of the worksheet can be customized)
*/
public static <T> void listMapToExcel(
Map<Map<String,List>,LinkedHashMap> listMap,
OutputStream out
) throws Exception {<!-- -->
if (listMap == null || listMap. size() == 0) {<!-- -->
return;
// throw new Exception("There is no data in the data source");
}
//Create a workbook and send it to the place specified by OutputStream
WritableWorkbook wwb;
try {<!-- -->
wwb = Workbook. createWorkbook(out);
for (Map.Entry<Map<String,List>, LinkedHashMap> entry : listMap.entrySet()) {<!-- -->
System.out.println("Key = " + entry.getKey() + ", Value = " + entry.getValue());
Map<String,List> obj=entry. getKey();
LinkedHashMap fieldMap = entry. getValue();
for (Map.Entry<String, List> item : obj.entrySet())
{<!-- -->
List list=item. getValue();
String sheetName = item. getKey();
// WritableSheet sheet = wwb. createSheet(sheetName, 0);
// fillSheet(sheet, list, fieldMap, 0, list. size() - 1);
double sheetNum = Math.ceil(list.size() / new Integer(65535).doubleValue());
//2. Create the corresponding worksheet and fill it with data
for (int i = 0; i < sheetNum; i ++ ) {<!-- -->
//If there is only one worksheet
if (1 == sheetNum) {<!-- -->
WritableSheet sheet = wwb. createSheet(sheetName, i);
fillSheet(sheet, list, fieldMap, 0, list. size() - 1);
//If there are multiple worksheets
} else {<!-- -->
WritableSheet sheet = wwb.createSheet(sheetName + (i + 1), i);
// get start index and end index
int firstIndex = i * 65535;
int lastIndex = (i + 1) * 65535 - 1 > list.size() - 1 ? list.size() - 1 : (i + 1) * 65535 - 1;
// populate the worksheet
fillSheet(sheet, list, fieldMap, firstIndex, lastIndex);
}
}
}
}
wwb.write();
wwb. close();
} catch (Exception e) {<!-- -->
e.printStackTrace();
//If it is Exception, throw it directly
if (e instanceof Exception) {<!-- -->
throw (Exception) e;
//Otherwise wrap other exceptions into Exception and throw
} else {<!-- -->
throw new Exception("Failed to export Excel");
}
}
}
/**
* @param list data source
* Correspondence between the English attributes of the @param fieldMap class and the Chinese column names in Excel
* If what is needed is the attribute of the reference object, the English attribute uses a format similar to the EL expression
* For example: all the students are stored in the list, and there is a college attribute in the student, and we need the name of the college, we can write it like this
* fieldMap.put("college.collegeName","College Name")
* @param sheetName the name of the worksheet
* @param sheetSize the maximum number of records in each worksheet
* @param out export stream
* @MethodName : listToExcel
* @Description : Export Excel (can be exported to the local file system or to the browser, the size of the worksheet can be customized)
*/
public static <T> void listToExcel(
List<T> list,
LinkedHashMap<String, String> fieldMap,
String sheetName,
int sheetSize,
OutputStream out
) throws Exception {<!-- -->
if (list == null || list. size() == 0) {<!-- -->
return;
// throw new Exception("There is no data in the data source");
}
if (sheetSize > 65535 || sheetSize < 1) {<!-- -->
sheetSize = 65535;
}
//Create a workbook and send it to the place specified by OutputStream
WritableWorkbook wwb;
try {<!-- -->
wwb = Workbook. createWorkbook(out);
//Because 2003 Excel can have a maximum of 65536 records in a worksheet, 65535 records are left after removing the column header
//So if there are too many records, they need to be placed in multiple worksheets, which is actually a paging process
//1. Calculate how many worksheets there are in total
double sheetNum = Math.ceil(list.size() / new Integer(sheetSize).doubleValue());
//2. Create the corresponding worksheet and fill it with data
for (int i = 0; i < sheetNum; i ++ ) {<!-- -->
//If there is only one worksheet
if (1 == sheetNum) {<!-- -->
WritableSheet sheet = wwb. createSheet(sheetName, i);
fillSheet(sheet, list, fieldMap, 0, list. size() - 1);
//If there are multiple worksheets
} else {<!-- -->
WritableSheet sheet = wwb.createSheet(sheetName + (i + 1), i);
// get start index and end index
int firstIndex = i * sheetSize;
int lastIndex = (i + 1) * sheetSize - 1 > list.size() - 1 ? list.size() - 1 : (i + 1) * sheetSize - 1;
// populate the worksheet
fillSheet(sheet, list, fieldMap, firstIndex, lastIndex);
}
}
if (sheetNum == 0) {<!-- -->
WritableSheet sheet = wwb. createSheet(sheetName, 0);
fillSheet(sheet, list, fieldMap, 0, list. size() - 1);
}
wwb.write();
wwb. close();
} catch (Exception e) {<!-- -->
e.printStackTrace();
//If it is Exception, throw it directly
if (e instanceof Exception) {<!-- -->
throw (Exception) e;
//Otherwise wrap other exceptions into Exception and throw
} else {<!-- -->
throw new Exception("Failed to export Excel");
}
}
}
/**
* @param list data source
* Correspondence between the English attributes of the @param fieldMap class and the Chinese column names in Excel
* @param out export stream
* @throws Exception
* @MethodName : listToExcel
* @Description : Export Excel (can be exported to the local file system or to the browser, the worksheet size is the maximum supported by 2003)
*/
public static <T> void listToExcel(
List<T> list,
LinkedHashMap<String, String> fieldMap,
String sheetName,
OutputStream out
) throws Exception {<!-- -->
listToExcel(list, fieldMap, sheetName, 65535, out);
}
/**
* @param list data source
* Correspondence between the English attributes of the @param fieldMap class and the Chinese column names in Excel
* @param sheetSize the maximum number of records in each worksheet
* @param response Use response to export to browser
* @throws Exception
* @MethodName : listToExcel
* @Description : Export Excel (export to browser, you can customize the size of the worksheet)
*/
public static <T> void listToExcel(
List<T> list,
LinkedHashMap<String, String> fieldMap,
String sheetName,
int sheetSize,
HttpServletResponse response
) throws Exception {<!-- -->
//Set the default file name to the current time: year, month, day, hour, minute, second
String fileName = new SimpleDateFormat("yyyyMMddhhmmss").format(new Date()).toString();
//Set response header information
response. reset();
response.setContentType("application/vnd.ms-excel"); //Change to output excel file
response.setHeader("Content-disposition", "attachment; filename=" + fileName + ".xls");
//Create workbook and send to browser
try {<!-- -->
OutputStream out = response. getOutputStream();
listToExcel(list, fieldMap, sheetName, sheetSize, out);
} catch (Exception e) {<!-- -->
e.printStackTrace();
//If it is Exception, throw it directly
if (e instanceof Exception) {<!-- -->
throw (Exception) e;
//Otherwise wrap other exceptions into Exception and throw
} else {<!-- -->
throw new Exception("Failed to export Excel");
}
}
}
/**
* @param list data source
* Correspondence between the English attributes of the @param fieldMap class and the Chinese column names in Excel
* @param response Use response to export to browser
* @throws Exception
* @MethodName : listToExcel
* @Description : Export Excel (export to browser, the size of the worksheet is the maximum supported by 2003)
*/
public static <T> void listToExcel(
List<T> list,
LinkedHashMap<String, String> fieldMap,
String sheetName,
HttpServletResponse response
) throws Exception {<!-- -->
listToExcel(list, fieldMap, sheetName, 65535, response);
}
/**
* @param in : carrying the input stream of Excel
* @param entityClass: the type of object in the List (each row in Excel must be converted to an object of this type)
* @param fieldMap: Map of the correspondence between the Chinese column headers in Excel and the English attributes of the class
* @param uniqueFields: Specify the combination of business primary keys (ie composite primary keys), the combination of these columns cannot be repeated
* @return: List
* @throws Exception
* @MethodName : excelToList
* @Description : Convert Excel to List
*/
public static <T> List<T> excelToList(
InputStream in,
String sheetName,
Class<T> entityClass,
LinkedHashMap<String, String> fieldMap,
String[] uniqueFields
) throws Exception {<!-- -->
//Define the list to return
List<T> resultList = new ArrayList<T>();
try {<!-- -->
//Create a WorkBook based on the Excel data source
Workbook wb = Workbook. getWorkbook(in);
// get worksheet
Sheet sheet = wb. getSheet(sheetName);
// Sheet sheet = wb. getSheet(0);
//Get the effective number of rows in the worksheet
int realRows = 0;
for (int i = 0; i < sheet. getRows(); i ++ ) {<!-- -->
int nullCols = 0;
for (int j = 0; j < sheet. getColumns(); j ++ ) {<!-- -->
Cell currentCell = sheet. getCell(j, i);
if (currentCell == null || "".equals(currentCell.getContents().toString())) {<!-- -->
nullCols++;
}
}
if (nullCols == sheet.getColumns()) {<!-- -->
break;
} else {<!-- -->
realRows++;
}
}
//If there is no data in Excel, an error will be prompted
if (realRows <= 1) {<!-- -->
throw new Exception("There is no data in the Excel file");
}
Cell[] firstRow = sheet. getRow(0);
String[] excelFieldNames = new String[firstRow. length];
//Get the column names in Excel
for (int i = 0; i < firstRow. length; i ++ ) {<!-- -->
excelFieldNames[i] = firstRow[i].getContents().toString().trim();
}
//Determine whether the required fields exist in Excel
boolean isExist = true;
List<String> excelFieldList = Arrays. asList(excelFieldNames);
for (String cnName : fieldMap.keySet()) {<!-- -->
if (!excelFieldList.contains(cnName)) {<!-- -->
isExist = false;
break;
}
}
//If a column name does not exist, an exception will be thrown, prompting an error
if (!isExist) {<!-- -->
throw new Exception("A necessary field is missing in Excel, or the field name is wrong");
}
//Put the column name and column number into the Map, so that the column number can be obtained through the column name
LinkedHashMap<String, Integer> colMap = new LinkedHashMap<String, Integer>();
for (int i = 0; i < excelFieldNames. length; i ++ ) {<!-- -->
colMap.put(excelFieldNames[i], firstRow[i].getColumn());
}
// Check if there are duplicate rows
//1. Get the column specified by uniqueFields
Cell[][] uniqueCells = new Cell[uniqueFields. length][];
for (int i = 0; i < uniqueFields. length; i ++ ) {<!-- -->
int col = colMap.get(uniqueFields[i]);
uniqueCells[i] = sheet. getColumn(col);
}
//2. Find duplicate rows from the specified column
for (int i = 1; i < realRows; i ++ ) {<!-- -->
int nullCols = 0;
for (int j = 0; j < uniqueFields. length; j ++ ) {<!-- -->
String currentContent = uniqueCells[j][i].getContents();
Cell sameCell = sheet. findCell(currentContent,
uniqueCells[j][i].getColumn(),
uniqueCells[j][i].getRow() + 1,
uniqueCells[j][i].getColumn(),
uniqueCells[j][realRows - 1]. getRow(),
true);
if (sameCell != null) {<!-- -->
nullCols++;
}
}
if (nullCols == uniqueFields. length) {<!-- -->
throw new Exception("There are duplicate rows in Excel, please check");
}
}
//Convert sheet to list
for (int i = 1; i < realRows; i ++ ) {<!-- -->
// Create new object to convert
T entity = entityClass. newInstance();
// Assign values to the fields in the object
for (Map.Entry<String, String> entry : fieldMap.entrySet()) {<!-- -->
//Get the Chinese field name
String cnNormalName = entry. getKey();
//Get the English field name
String enNormalName = entry. getValue();
//Get the column number according to the Chinese field name
int col = colMap.get(cnNormalName);
// Get the content of the current cell
String content = sheet. getCell(col, i). getContents(). toString(). trim();
//Assign value to object
setFieldValueByName(enNormalName, content, entity);
}
resultList. add(entity);
}
} catch (Exception e) {<!-- -->
e.printStackTrace();
//If it is Exception, throw it directly
if (e instanceof Exception) {<!-- -->
throw (Exception) e;
//Otherwise wrap other exceptions into Exception and throw
} else {<!-- -->
e.printStackTrace();
throw new Exception("Failed to import Excel");
}
}
return resultList;
}
/*<-------------------------Auxiliary private method--------------- ----------------------------->*/
/**
* @param fieldName field name
* @param o object
* @return field value
* @MethodName : getFieldValueByName
* @Description : Get the field value according to the field name
*/
private static Object getFieldValueByName(String fieldName, Object o) throws Exception {<!-- -->
Object value = null;
Field field = getFieldByName(fieldName, o.getClass());
if (field != null) {<!-- -->
field.setAccessible(true);
value = field. get(o);
} else {<!-- -->
throw new Exception(o.getClass().getSimpleName() + "Class does not exist field name " + fieldName);
}
return value;
}
/**
* @param fieldName field name
* @param clazz the class containing the field
* @return field
* @MethodName : getFieldByName
* @Description : Get the field according to the field name
*/
private static Field getFieldByName(String fieldName, Class<?> clazz) {<!-- -->
// Get all the fields of this class
Field[] selfFields = clazz. getDeclaredFields();
//If the field exists in this class, return
for (Field field : selfFields) {<!-- -->
if (field. getName(). equals(fieldName)) {<!-- -->
return field;
}
}
//Otherwise, check whether this field exists in the parent class, and return if so
Class<?> superClazz = clazz. getSuperclass();
if (superClazz != null & amp; & amp; superClazz != Object.class) {<!-- -->
return getFieldByName(fieldName, superClazz);
}
//If there is neither this class nor the parent class, return empty
return null;
}
/**
* @param fieldNameSequence attribute name with path or simple attribute name
* @param o object
* @return attribute value
* @throws Exception
* @MethodName : getFieldValueByNameSequence
* @Description :
* Get the attribute value according to the attribute name with path or without path
* It accepts simple attribute names, such as userName, etc., and accepts attribute names with paths, such as student.department.name, etc.
*/
private static Object getFieldValueByNameSequence(String fieldNameSequence, Object o) throws Exception {<!-- -->
Object value = null;
//Split fieldNameSequence
String[] attributes = fieldNameSequence. split("\.");
if (attributes. length == 1) {<!-- -->
value = getFieldValueByName(fieldNameSequence, o);
} else {<!-- -->
/ / Get the attribute object according to the attribute name
Object fieldObj = getFieldValueByName(attributes[0], o);
String subFieldNameSequence = fieldNameSequence. substring(fieldNameSequence. indexOf(".") + 1);
value = getFieldValueByNameSequence(subFieldNameSequence, fieldObj);
}
return value;
}
/**
* @param fieldName field name
* @param fieldValue field value
* @param o object
* @MethodName : setFieldValueByName
* @Description : Assign values to the fields of the object according to the field names
*/
private static void setFieldValueByName(String fieldName, Object fieldValue, Object o) throws Exception {<!-- -->
Field field = getFieldByName(fieldName, o.getClass());
if (field != null) {<!-- -->
field.setAccessible(true);
// get field type
Class<?> fieldType = field. getType();
//Assign a value to the field according to the field type
if (String. class == fieldType) {<!-- -->
field.set(o, String.valueOf(fieldValue));
} else if ((Integer. TYPE == fieldType)
|| (Integer. class == fieldType)) {<!-- -->
field.set(o, Integer.parseInt(fieldValue.toString()));
} else if ((Long. TYPE == fieldType)
|| (Long. class == fieldType)) {<!-- -->
field.set(o, Long.valueOf(fieldValue.toString()));
} else if ((Float. TYPE == fieldType)
|| (Float. class == fieldType)) {<!-- -->
field.set(o, Float.valueOf(fieldValue.toString()));
} else if ((Short. TYPE == fieldType)
|| (Short. class == fieldType)) {<!-- -->
field.set(o, Short.valueOf(fieldValue.toString()));
} else if ((Double. TYPE == fieldType)
|| (Double. class == fieldType)) {<!-- -->
field.set(o, Double.valueOf(fieldValue.toString()));
} else if (Character. TYPE == fieldType) {<!-- -->
if ((fieldValue != null) & amp; & amp; (fieldValue.toString().length() > 0)) {<!-- -->
field.set(o, Character.valueOf(fieldValue.toString().charAt(0)));
}
} else if (BigDecimal. class == fieldType) {<!-- -->
field.set(o, BigDecimal.valueOf(Double.parseDouble(fieldValue.toString())));
} else if (Date. class == fieldType) {<!-- -->
field.set(o, new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").parse(fieldValue.toString()));
} else {<!-- -->
field.set(o, fieldValue);
}
} else {<!-- -->
throw new Exception(o.getClass().getSimpleName() + "Class does not exist field name " + fieldName);
}
}
/**
* @param ws
* @MethodName : setColumnAutoSize
* @Description : Set the automatic column width and the first row of the worksheet to be bold
*/
private static void setColumnAutoSize(WritableSheet ws, int extraWith) {<!-- -->
// Get the width of the widest cell in this column
for (int i = 0; i < ws.getColumns(); i ++ ) {<!-- -->
int colWith = 0;
for (int j = 0; j < ws. getRows(); j ++ ) {<!-- -->
String content = ws.getCell(i, j).getContents().toString();
int cellWith = content. length();
if (colWith < cellWith) {<!-- -->
colWith = cellWith;
}
}
//Set the width of the cell to the widest width + extra width
ws.setColumnView(i, colWith + extraWith);
}
}
/**
* @param sheet worksheet
* @param list data source
* @param fieldMap Map of the corresponding relationship between Chinese and English fields
* @param firstIndex start index
* @param lastIndex end index
* @MethodName : fillSheet
* @Description : Fill the worksheet with data
*/
private static <T> void fillSheet(
WritableSheet sheet,
List<T> list,
LinkedHashMap<String, String> fieldMap,
int firstIndex,
int lastIndex
) throws Exception {<!-- -->
//Define an array for storing English field names and Chinese field names
String[] enFields = new String[fieldMap. size()];
String[] cnFields = new String[fieldMap. size()];
// fill the array
int count = 0;
for (Map.Entry<String, String> entry : fieldMap.entrySet()) {<!-- -->
enFields[count] = entry. getKey();
cnFields[count] = entry. getValue();
count + + ;
}
// fill header
for (int i = 0; i < cnFields. length; i ++ ) {<!-- -->
Label label = new Label(i, 0, cnFields[i]);
sheet. addCell(label);
}
if (list != null & amp; & amp; list. size() != 0) {<!-- -->
// Fill content
int rowNo = 1;
for (int index = firstIndex; index <= lastIndex; index ++ ) {<!-- -->
// get a single object
T item = list. get(index);
for (int i = 0; i < enFields. length; i ++ ) {<!-- -->
Object objValue = getFieldValueByNameSequence(enFields[i], item);
String fieldValue = objValue == null ? "" : objValue.toString();
Label label = new Label(i, rowNo, fieldValue);
sheet. addCell(label);
}
rowNo++;
}
}
//Set automatic column width
setColumnAutoSize(sheet, 5);
}
}
- Import e-iceblue in pom.xml
<dependencies>
<dependency>
<groupId>e-iceblue</groupId>
<artifactId>spire.xls</artifactId>
<version>12.9.1</version>
</dependency>
</dependencies>
<repositories>
<repository>
<id>com.e-iceblue</id>
<name>e-iceblue</name>
<url>https://repo.e-iceblue.cn/repository/maven-public/</url>
</repository>
</repositories>
- If the configured warehouse does not work, find the following location in the maven setting.xml file, and add !com.e-iceblue after the * of the mirrorOf location
<mirror>
<id>aliyunmaven</id>
<mirrorOf>*,!com.e-iceblue</mirrorOf>
<name>Alibaba Cloud Public Warehouse</name>
<url>https://maven.aliyun.com/repository/public</url>
</mirror>
@Override
public Object insertCableLineInfoByExcel(MultipartFile file){<!-- -->
try{<!-- -->
//Initialize the required parameters
InputStream inputStream=file. getInputStream();
//If the suffix is xlsx, you need to convert to xls first
if (file.getOriginalFilename().endsWith("xlsx")){<!-- -->
Workbook workbook=new Workbook();
workbook.loadFromHtml(inputStream);
workbook.saveToFile(file.getOriginalFilename().substring(0,file.getOriginalFilename().indexOf(".")) + ".xls", ExcelVersion.Version97to2003);
File fileTemp=new File(file.getOriginalFilename().substring(0,file.getOriginalFilename().indexOf(".")) + ".xls");
inputStream=new FileInputStream(fileTemp);
//Delete temporary excel
// FileUtil.del(fileTemp.getPath());
}
//sheet name
String sheetname="Sheet1";
EvCablelineinfor evCablelineinfor=new EvCablelineinfor();
// Correspondence
LinkedHashMap<String, String> fieldMap = new LinkedHashMap<>();
fieldMap.put("Number", "id");
fieldMap.put("Line ID", "loopid");
fieldMap.put("Line name", "loopname");
fieldMap.put("cable name", "cablelinename");
fieldMap.put("start device", "startdevice");
fieldMap.put("end device", "enddevice");
fieldMap.put("cable length", "cablelength");
fieldMap.put("arc type", "arctype");
fieldMap.put("running status", "runstatus");
fieldMap.put("cover type", "covertype");
//primary key
String[] uniqueFields=new String[1];
uniqueFields[0]="Number";
//Convert to List
List list = ExcelUti.excelToList(inputStream,sheetname,evCablelineinfor.getClass(),fieldMap,uniqueFields);
System.out.println(list.size());
int isSuccess=0;
//insert
for(int i=0;i<list. size();i ++ ){<!-- -->
isSuccess=evCablelineinforMapper.insertSelective((EvCablelineinfor) list.get(i));
}
if(isSuccess<1){<!-- -->
return ResultUtil.error("excel table import failed");
}
return ResultUtil.success("excel table imported successfully");
} catch (Exception e){<!-- -->
return ResultUtil.error("Excel table format error");
}
}
Reference
- https://blog.csdn.net/LabDNirvana/article/details/104497709