Shulou(Shulou.com)06/01 Report--

Today, I would like to share with you how Java to achieve Excel import and export operation of the relevant knowledge, detailed content, clear logic, I believe that most people are too aware of this knowledge, so share this article for your reference, I hope you will learn something after reading this article, let's take a look at it.

1. Functional Test 1.1 Test preparation

Before we do the test, we need to put [2. The four copies of the files in the environment preparation are in the project (for example, I put them here under the com.zyq.util.excel package).

1.2 data Import 1.2.1 Import parsing to JSON

For example, we have the following table:

Controller Code:

@ PostMapping ("/ import") public JSONArray importUser (@ RequestPart ("file") MultipartFile file) throws Exception {JSONArray array = ExcelUtils.readMultipartFile (file); System.out.println ("Import data is:" + array); return array;}

Test results:

1.2.2 Import resolves to objects (Foundation)

First, you need to create a Java entity object corresponding to the import table and mark it with the corresponding import annotation parsed by Excel. The value of the @ ExcelImport annotation is the header name.

Controller Code:

@ PostMapping ("/ import") public void importUser (@ RequestPart ("file") MultipartFile file) throws Exception {List users = ExcelUtils.readMultipartFile (file, User.class); for (User user: users) {System.out.println (user.toString ());}}

Test results:

1.2.3 Import resolves to objects (field automatic mapping)

For some enumerated data, usually when we import, the data in the table is the value, but when the data is saved, we often use keys, for example: we can use sex=1 to represent male, sex=2 to represent female, then we can also achieve automatic mapping of data when importing through configuration.

Then, we just need to change the type of the object sex field in the Java entity to the corresponding numeric type Integer, and then configure the kv attribute in the annotation (the attribute format is: key 1-value 1; key 2-value 2; key 3-value 3.)

The Cotroller code is slightly (identical to 1.2.2).

Test results: you can see that the automatic mapping has been successful.

1.2.4 Import resolves to object (get line number)

When we import page data, we may sometimes need to get the line number in order to track the imported data.

Then, we just need to add a rowNum field of type int to the corresponding entity.

The Cotroller code is slightly (identical to 1.2.2).

Test results:

1.2.5 Import resolves to objects (get raw data)

When importing page data, if there is an error in a row, we usually take out the original data and analyze why it causes the data error. So, we can add a rowData field of type String to the entity class.

The Cotroller code is slightly (identical to 1.2.2).

Test results:

1.2.6 Import resolves to objects (get error prompt)

When we import data, if a row of data exists, the field type is incorrect, and the length exceeds the maximum limit (see 1.2.7), required field verification (1.2.8), data uniqueness verification (1.2.9) and other errors, we can add a rowTips field of type String to the object and get the corresponding error message directly.

For example, we change the gender of Zhao Zilong in the table to F (F is not mapping data) and the gender of Big Joe to 28 (which cannot be converted to Integer type data).

The Cotroller code is slightly (identical to 1.2.2).

Test results: as you can see, we can get the corresponding error data prompt directly through rowTips.

1.2.7 Import resolves to objects (limits field length)

For example, if the length of our mobile phone is usually 11, we might as well limit the maximum length of the phone to 11 digits.

The corresponding approach is to set maxLength = 11 in the @ ExcelImport annotation.

For example, we set Zhuge Kongming's phone length to a string of more than 11 digits.

The Cotroller code is slightly (identical to 1.2.2).

Test results:

1.2.8 Import resolves to objects (required field validation)

When we do data import, we often have some required fields, such as the user's name and phone number.

So, all we need to do is add required = true to the @ ExcelImport annotation attribute.

We removed the phone number of Zhuge Kongming and the name of line 4 for testing.

The Cotroller code is slightly (identical to 1.2.2).

Test results:

1.2.9 Import parsing to objects (data uniqueness verification)

(1) single field uniqueness verification

When we import data, a certain field is unique. For example, if we assume that the name cannot be repeated here, we can add the unique = true attribute to the @ ExcelImport annotation of the corresponding field.

Here we build two pieces of data with the same name for testing.

The Cotroller code is slightly (identical to 1.2.2).

Test results:

(2) Multi-field uniqueness verification

If there is multi-field uniqueness verification in the imported data, just add required = true to the @ ExcelImport annotation attribute of each corresponding field.

For example, we verify the joint uniqueness of the name and phone fields (that is, there can be no data with the same name and phone, and a single field property is allowed repeatedly).

First of all, we import the data just (1).

Test results: you can see that although the name is the same, the phone is not the same, so there is no indication of a uniqueness verification error.

Now, we change the phone on the last line to the same number as line 1, so now there are two pieces of data that violate uniqueness.

Test results: as you can see, our joint uniqueness verification is in effect.

1.3 data Export 1.3.1 dynamic Export (Foundation)

This approach is very flexible, and the data in the table is completely customized.

Controller Code:

@ GetMapping ("/ export") public void export (HttpServletResponse response) {/ / header data List head = Arrays.asList ("name", "age", "gender", "avatar"); / / user 1 data List user1 = new ArrayList (); user1.add ("Zhuge Liang"); user1.add (60); user1.add ("male") User1.add ("https://profile.csdnimg.cn/A/7/3/3_sunnyzyq"); / / user 2 data List user2 = new ArrayList (); user2.add (" Big Joe "); user2.add (28); user2.add (" female "); user2.add (" https://profile.csdnimg.cn/6/1/9/0_m0_48717371"); / / aggregate data List sheetDataList = new ArrayList () SheetDataList.add (head); sheetDataList.add (user1); sheetDataList.add (user2); / / Export data ExcelUtils.export (response, user Table, sheetDataList);}

Code screenshot:

Since it is a get request, we can trigger the download by entering the request address directly on the browser.

When we open the download table, we can see that the data in the table is in the same order as our code.

1.3.2 dynamic export (export pictures)

If you need to display the corresponding picture link as a picture directly in your export, then you can also change the corresponding type to java.net.URL type. (note: there is exception handling during conversion. For convenience of demonstration, I will directly throw it here.)

Test results:

1.3.3 dynamic export (implement drop-down list)

When we do some data export, we may have to restrict the drop-down data of a row.

For example, when we download an import template, we can set the gender, city corresponding column to the drop-down selection.

Test results:

1.3.4 dynamic export (horizontal merge)

For example, if we merge the header horizontally, we only need to set the merged cell to ExcelUtils.COLUMN_MERGE.

Test results: you can see that the address of the header has been merged.

1.3.5 dynamic export (vertical merge)

In addition to horizontal merging, we can also merge vertically by setting the merged cells to ExcelUtils.ROW_MERGE.

Test results:

1.3.6 Export template (Foundation)

When we do data import, we often first provide a template for download, so that users know how to fill in the data when importing. In addition to using the dynamic export above, the export template also provides a more convenient way to write. All you need to do is create a class, and then mark the @ ExcelExport annotation class on the corresponding field.

Controller Code:

@ GetMapping ("/ export") public void export (HttpServletResponse response) {ExcelUtils.exportTemplate (response, "user Table", User.class);}

Code screenshot:

Test results:

1.3.7 Export template (with sample data)

When we download a template, we sometimes carry a piece of sample data to remind the user what the data format is, so we just need to configure it on the corresponding field.

Controller Code:

Test results:

1.3.8 Export by object (Foundation)

We can also export the data directly through the List object. First, you also need to set the export name on the field of the corresponding class.

Controller Code:

Test results:

1.3.9 Export by object (data mapping)

In the export of 1.3.8 above, we can see that the gender data are 1 and 2, which is not conducive to the user experience and should be converted to the corresponding Chinese. We can configure it on the field comments.

Controller code is slightly (exactly the same as 1.3.8)

Test results: you can see that 1 and 2 are shown as corresponding men and women

1.3.10 Export by object (adjust header order)

If you need to sort the header fields, there are two ways:

First: arrange the fields in the Java class in the order of the table

The second: in the @ ExcelExport annotation, specify the sort attribute. The lower the value, the higher the ranking.

Controller code is slightly (exactly the same as 1.3.8)

Test effect: you can see that the header order of the exported data is exactly the same as the order we specified.

two。 Environment preparation 2.1 Maven dependency

The encapsulation of this tool class mainly depends on Alibaba's JSON package and the table processing POI package, so we need to import the dependent packages of these two libraries, in addition, we also need the related packages for file uploading. after all, we upload the Excel file when we do Excel import on the browser page.

Org.apache.httpcomponents httpmime 4.5.7 com.alibaba fastjson 1.2.41 org.apache.poi poi-ooxml 3.162.2 Class File ExcelUtilspackage com.zyq.util.excel; import java.io.ByteArrayOutputStream;import java.io.File;import java.io.FileInputStream;import java.io.FileOutputStream;import java.io.IOException;import java.io.InputStream;import java.lang.reflect.Field;import java.math.BigDecimal;import java.math.RoundingMode;import java.net.URL Import java.text.NumberFormat;import java.text.SimpleDateFormat;import java.util.*;import java.util.Map.Entry; import javax.servlet.ServletOutputStream;import javax.servlet.http.HttpServletResponse; import com.zyq.entity.User;import org.apache.poi.hssf.usermodel.HSSFDataValidation;import org.apache.poi.hssf.usermodel.HSSFWorkbook;import org.apache.poi.poifs.filesystem.POIFSFileSystem;import org.apache.poi.ss.usermodel.Cell;import org.apache.poi.ss.usermodel.CellStyle Import org.apache.poi.ss.usermodel.CellType;import org.apache.poi.ss.usermodel.ClientAnchor.AnchorType;import org.apache.poi.ss.usermodel.DataValidation;import org.apache.poi.ss.usermodel.DataValidationConstraint;import org.apache.poi.ss.usermodel.DataValidationHelper;import org.apache.poi.ss.usermodel.Drawing;import org.apache.poi.ss.usermodel.FillPatternType;import org.apache.poi.ss.usermodel.HorizontalAlignment;import org.apache.poi.ss.usermodel.IndexedColors;import org.apache.poi.ss.usermodel.Row Import org.apache.poi.ss.usermodel.Sheet;import org.apache.poi.ss.usermodel.VerticalAlignment;import org.apache.poi.ss.usermodel.Workbook;import org.apache.poi.ss.util.CellRangeAddress;import org.apache.poi.ss.util.CellRangeAddressList;import org.apache.poi.xssf.streaming.SXSSFWorkbook;import org.apache.poi.xssf.usermodel.XSSFClientAnchor;import org.apache.poi.xssf.usermodel.XSSFWorkbook;import org.springframework.web.multipart.MultipartFile; import com.alibaba.fastjson.JSONArray Import com.alibaba.fastjson.JSONObject; / * Excel Import and Export tool Class * * @ author sunnyzyq * @ date 2021-12-17 * / public class ExcelUtils {private static final String XLSX = ".xlsx"; private static final String XLS = ".xls"; public static final String ROW_MERGE = "row_merge"; public static final String COLUMN_MERGE = "column_merge"; private static final String DATE_FORMAT = "yyyy-MM-dd HH:mm:ss" Private static final String ROW_NUM = "rowNum"; private static final String ROW_DATA = "rowData"; private static final String ROW_TIPS = "rowTips"; private static final int CELL_OTHER = 0; private static final int CELL_ROW_MERGE = 1; private static final int CELL_COLUMN_MERGE = 2; private static final int IMG_HEIGHT = 30; private static final int IMG_WIDTH = 30; private static final char LEAN_LINE ='/' Private static final int BYTES_DEFAULT_LENGTH = 10240; private static final NumberFormat NUMBER_FORMAT = NumberFormat.getNumberInstance (); public static List readFile (File file, Class clazz) throws Exception {JSONArray array = readFile (file); return getBeanList (array, clazz);} public static List readMultipartFile (MultipartFile mFile, Class clazz) throws Exception {JSONArray array = readMultipartFile (mFile); return getBeanList (array, clazz) } public static JSONArray readFile (File file) throws Exception {return readExcel (null, file);} public static JSONArray readMultipartFile (MultipartFile mFile) throws Exception {return readExcel (mFile, null);} private static List getBeanList (JSONArray array, Class clazz) throws Exception {List list = new ArrayList (); Map uniqueMap = new HashMap (16); for (int I = 0; I)

< array.size(); i++) { list.add(getBean(clazz, array.getJSONObject(i), uniqueMap)); } return list; } /** * 获取每个对象的数据 */ private static T getBean(Class c, JSONObject obj, Map uniqueMap) throws Exception { T t = c.newInstance(); Field[] fields = c.getDeclaredFields(); List errMsgList = new ArrayList(); boolean hasRowTipsField = false; StringBuilder uniqueBuilder = new StringBuilder(); int rowNum = 0; for (Field field : fields) { // 行号 if (field.getName().equals(ROW_NUM)) { rowNum = obj.getInteger(ROW_NUM); field.setAccessible(true); field.set(t, rowNum); continue; } // 是否需要设置异常信息 if (field.getName().equals(ROW_TIPS)) { hasRowTipsField = true; continue; } // 原始数据 if (field.getName().equals(ROW_DATA)) { field.setAccessible(true); field.set(t, obj.toString()); continue; } // 设置对应属性值 setFieldValue(t,field, obj, uniqueBuilder, errMsgList); } // 数据唯一性校验 if (uniqueBuilder.length() >

0) {if (uniqueMap.containsValue (uniqueBuilder.toString () {Set rowNumKeys = uniqueMap.keySet () For (Integer num: rowNumKeys) {if (uniqueMap.get (num) .equals (uniqueBuilder.toString () {errMsgList.add (String.format ("data uniqueness check failed, (% s) duplicates with line% s)", uniqueBuilder, num) } else {uniqueMap.put (rowNum, uniqueBuilder.toString ());} / / failure handling if (errMsgList.isEmpty () & &! hasRowTipsField) {return t;} StringBuilder sb = new StringBuilder (); int size = errMsgList.size () For (int I = 0; I

< size; i++) { if (i == size - 1) { sb.append(errMsgList.get(i)); } else { sb.append(errMsgList.get(i)).append(";"); } } // 设置错误信息 for (Field field : fields) { if (field.getName().equals(ROW_TIPS)) { field.setAccessible(true); field.set(t, sb.toString()); } } return t; } private static void setFieldValue(T t, Field field, JSONObject obj, StringBuilder uniqueBuilder, List errMsgList) { // 获取 ExcelImport 注解属性 ExcelImport annotation = field.getAnnotation(ExcelImport.class); if (annotation == null) { return; } String cname = annotation.value(); if (cname.trim().length() == 0) { return; } // 获取具体值 String val = null; if (obj.containsKey(cname)) { val = getString(obj.getString(cname)); } if (val == null) { return; } field.setAccessible(true); // 判断是否必填 boolean require = annotation.required(); if (require && val.isEmpty()) { errMsgList.add(String.format("[%s]不能为空", cname)); return; } // 数据唯一性获取 boolean unique = annotation.unique(); if (unique) { if (uniqueBuilder.length() >

0) {uniqueBuilder.append ("- -") .append (val);} else {uniqueBuilder.append (val);}} / / determine whether the maximum length is exceeded int maxLength = annotation.maxLength () If (maxLength > 0 & & val.length () > maxLength) {errMsgList.add ("[% s] length cannot exceed% s characters (current% s characters)", cname, maxLength, val.length ());} / / determine whether the current attribute has a mapping relationship LinkedHashMap kvMap = getKvMap (annotation.kv ()) If (! kvMap.isEmpty ()) {boolean isMatch = false; for (String key: kvMap.keySet ()) {if (kvMap.get (key) .equals (val)) {val = key; isMatch = true; break }} if (! isMatch) {errMsgList.add (String.format ("[% s] is incorrect (current value is% s)", cname, val); return;}} / / the rest is assigned String fieldClassName = field.getType (). GetSimpleName () Try {if ("String" .equalsIgnoreCase (fieldClassName)) {field.set (t, val);} else if ("boolean" .equalsIgnoreCase (fieldClassName)) {field.set (t, Boolean.valueOf (val)) } else if ("int" .equals IgnoreCase (fieldClassName) | | "Integer" .equals (fieldClassName)) {try {field.set (t, Integer.valueOf (val));} catch (NumberFormatException e) {errMsgList.add (the value format of String.format ("[% s] is incorrect (current value is% s)", cname, val) }} else if ("double" .equalsIgnoreCase (fieldClassName)) {field.set (t, Double.valueOf (val));} else if ("long" .equalsIgnoreCase (fieldClassName)) {field.set (t, Long.valueOf (val)) } else if ("BigDecimal" .equals IgnoreCase (fieldClassName)) {field.set (t, new BigDecimal (val));}} catch (Exception e) {e.printStackTrace ();}} private static JSONArray readExcel (MultipartFile mFile, File file) throws IOException {boolean fileNotExist = (file = = null | |! file.exists ()) If (mFile = = null & & fileNotExist) {return new JSONArray ();} / parse table data InputStream in; String fileName; if (mFile! = null) {/ / upload file parse in = mFile.getInputStream (); fileName = getString (mFile.getOriginalFilename ()) .toLowerCase () } else {/ / Local file parsing in = new FileInputStream (file); fileName = file.getName (). ToLowerCase ();} Workbook book; if (fileName.endsWith (XLSX)) {book = new XSSFWorkbook (in);} else if (fileName.endsWith (XLS)) {POIFSFileSystem poifsFileSystem = new POIFSFileSystem (in) Book = new HSSFWorkbook (poifsFileSystem);} else {return new JSONArray ();} JSONArray array = read (book); book.close (); in.close (); return array;} private static JSONArray read (Workbook book) {/ / get the first Sheet page of the Excel file Sheet sheet = book.getSheetAt (0) Return readSheet (sheet);} private static JSONArray readSheet (Sheet sheet) {/ / first row subscript int rowStart = sheet.getFirstRowNum (); / / trailing line subscript int rowEnd = sheet.getLastRowNum (); / / get header row Row headRow = sheet.getRow (rowStart); if (headRow = = null) {return new JSONArray () } int cellStart = headRow.getFirstCellNum (); int cellEnd = headRow.getLastCellNum (); Map keyMap = new HashMap (); for (int j = cellStart; j

< cellEnd; j++) { // 获取表头数据 String val = getCellValue(headRow.getCell(j)); if (val != null && val.trim().length() != 0) { keyMap.put(j, val); } } // 如果表头没有数据则不进行解析 if (keyMap.isEmpty()) { return (JSONArray) Collections.emptyList(); } // 获取每行JSON对象的值 JSONArray array = new JSONArray(); // 如果首行与尾行相同，表明只有一行，返回表头数据 if (rowStart == rowEnd) { JSONObject obj = new JSONObject(); // 添加行号 obj.put(ROW_NUM, 1); for (int i : keyMap.keySet()) { obj.put(keyMap.get(i), ""); } array.add(obj); return array; } for (int i = rowStart + 1; i 0) { array.add(obj); } } return array; } private static String getCellValue(Cell cell) { // 空白或空 if (cell == null || cell.getCellTypeEnum() == CellType.BLANK) { return ""; } // String类型 if (cell.getCellTypeEnum() == CellType.STRING) { String val = cell.getStringCellValue(); if (val == null || val.trim().length() == 0) { return ""; } return val.trim(); } // 数字类型 if (cell.getCellTypeEnum() == CellType.NUMERIC) { // 科学计数法类型 return NUMBER_FORMAT.format(cell.getNumericCellValue()) + ""; } // 布尔值类型 if (cell.getCellTypeEnum() == CellType.BOOLEAN) { return cell.getBooleanCellValue() + ""; } // 错误类型 return cell.getCellFormula(); } public static void exportTemplate(HttpServletResponse response, String fileName, Class clazz) { exportTemplate(response, fileName, fileName, clazz, false); } public static void exportTemplate(HttpServletResponse response, String fileName, String sheetName, Class clazz) { exportTemplate(response, fileName, sheetName, clazz, false); } public static void exportTemplate(HttpServletResponse response, String fileName, Class clazz, boolean isContainExample) { exportTemplate(response, fileName, fileName, clazz, isContainExample); } public static void exportTemplate(HttpServletResponse response, String fileName, String sheetName, Class clazz, boolean isContainExample) { // 获取表头字段 List headFieldList = getExcelClassFieldList(clazz); // 获取表头数据和示例数据 List sheetDataList = new ArrayList(); List headList = new ArrayList(); List exampleList = new ArrayList(); Map selectMap = new LinkedHashMap(); for (int i = 0; i < headFieldList.size(); i++) { ExcelClassField each = headFieldList.get(i); headList.add(each.getName()); exampleList.add(each.getExample()); LinkedHashMap kvMap = each.getKvMap(); if (kvMap != null && kvMap.size() >

< sheetDataList.size(); i++) { // 每个 Sheet 页中的行数据 Row row = sheet.createRow(i); List rowList = sheetDataList.get(i); for (int j = 0; j < rowList.size(); j++) { // 每个行数据中的单元格数据 Object o = rowList.get(j); int v = 0; if (o instanceof URL) { // 如果要导出图片的话, 链接需要传递 URL 对象 setCellPicture(book, row, patriarch, i, j, (URL) o); } else { Cell cell = row.createCell(j); if (i == 0) { // 第一行为表头行，采用灰色底背景 v = setCellValue(cell, o, headStyle); } else { // 其他行为数据行，默认白底色 v = setCellValue(cell, o, rowStyle); } } mergeArray[i][j] = v; } } // 合并单元格 mergeCells(sheet, mergeArray); // 设置下拉列表 setSelect(sheet, selectMap); // 写数据 if (response != null) { // 前端导出 try { write(response, book, fileName); } catch (IOException e) { e.printStackTrace(); } } else { // 本地导出 FileOutputStream fos; try { fos = new FileOutputStream(file); ByteArrayOutputStream ops = new ByteArrayOutputStream(); book.write(ops); fos.write(ops.toByteArray()); fos.close(); } catch (Exception e) { e.printStackTrace(); } } } /** * 合并当前Sheet页的单元格 * * @param sheet 当前 sheet 页 * @param mergeArray 合并单元格算法 */ private static void mergeCells(Sheet sheet, int[][] mergeArray) { // 横向合并 for (int x = 0; x < mergeArray.length; x++) { int[] arr = mergeArray[x]; boolean merge = false; int y1 = 0; int y2 = 0; for (int y = 0; y < arr.length; y++) { int value = arr[y]; if (value == CELL_COLUMN_MERGE) { if (!merge) { y1 = y; } y2 = y; merge = true; } else { merge = false; if (y1 >

0) {sheet.addMergedRegion (new CellRangeAddress (x, x, (y1-1), y2));} y1 = 0; y2 = 0 }} if (y1 > 0) {sheet.addMergedRegion (new CellRangeAddress (x, x, (y1-1), y2));}} / / vertically merge int xLen = mergeArray.length; int yLen = mergeArray [0] .length; for (int y = 0; y)

< yLen; y++) { boolean merge = false; int x1 = 0; int x2 = 0; for (int x = 0; x < xLen; x++) { int value = mergeArray[x][y]; if (value == CELL_ROW_MERGE) { if (!merge) { x1 = x; } x2 = x; merge = true; } else { merge = false; if (x1 >

0) {sheet.addMergedRegion (new CellRangeAddress ((x1-1), x2, y, y));} x1 = 0; x2 = 0 }} if (x1 > 0) {sheet.addMergedRegion (new CellRangeAddress ((x1-1), x2, y, y);} private static void write (HttpServletResponse response, SXSSFWorkbook book, String fileName) throws IOException {response.setContentType ("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"); response.setCharacterEncoding ("utf-8") String name= new String (fileName.getBytes ("GBK"), "ISO8859_1") + XLSX; response.addHeader ("Content-Disposition", "attachment;filename=" + name); ServletOutputStream out = response.getOutputStream (); book.write (out); out.flush (); out.close () } private static int setCellValue (Cell cell, Object o, CellStyle style) {/ / style cell.setCellStyle (style); / / if (o = = null) {cell.setCellType (CellType.STRING); cell.setCellValue (""); return CELL_OTHER } / / is the string if (o instanceof String) {String s = o.toString (); if (isNumeric (s)) {cell.setCellType (CellType.NUMERIC); cell.setCellValue (Double.parseDouble (s)); return CELL_OTHER } else {cell.setCellType (CellType.STRING); cell.setCellValue (s);} if (s.equals (ROW_MERGE)) {return CELL_ROW_MERGE;} else if (s.equals (COLUMN_MERGE)) {return CELL_COLUMN_MERGE } whether else {return CELL_OTHER;}} / / is a string if (o instanceof Integer | | o instanceof Long | | o instanceof Double | | o instanceof Float) {cell.setCellType (CellType.NUMERIC); cell.setCellValue (Double.parseDouble (o.toString (); return CELL_OTHER } / / whether it is Boolean if (o instanceof Boolean) {cell.setCellType (CellType.BOOLEAN); cell.setCellValue ((Boolean) o); return CELL_OTHER;} / / if it is BigDecimal, the default is 3 decimal places if (o instanceof BigDecimal) {cell.setCellType (CellType.NUMERIC) Cell.setCellValue ((BigDecimal) o) .setScale (3, RoundingMode.HALF_UP). DoubleValue (); return CELL_OTHER;} / / if it is Date data, display formatted data if (o instanceof Date) {cell.setCellType (CellType.STRING); cell.setCellValue (formatDate ((Date) o)); return CELL_OTHER } / / if it is other, the default string type is cell.setCellType (CellType.STRING); cell.setCellValue (o.toString ()); return CELL_OTHER;} private static void setCellPicture (SXSSFWorkbook wb, Row sr, Drawing patriarch, int x, int y, URL url) {/ / sets the width and height of the picture sr.setHeight ((short) (IMG_WIDTH * IMG_HEIGHT)) / / (streams can be automatically closed in jdk1.7 version try) try (InputStream is = url.openStream (); ByteArrayOutputStream outputStream = new ByteArrayOutputStream ()) {byte [] buff = new byte [bytes _ DEFAULT_LENGTH]; int rc; while ((rc = is.read (buff, 0, BYTES_DEFAULT_LENGTH)) > 0) {outputStream.write (buff, 0, rc) } / / set the picture position XSSFClientAnchor anchor = new XSSFClientAnchor (0,0,0,0, y, x, y + 1, x + 1); / / set this, the picture will automatically fill the cell anchor.setAnchorType (AnchorType.MOVE_AND_RESIZE) Patriarch.createPicture (anchor, wb.addPicture (outputStream.toByteArray (), HSSFWorkbook.PICTURE_TYPE_JPEG));} catch (Exception e) {e.printStackTrace ();}} private static String formatDate (Date date) {if (date = = null) {return ";} SimpleDateFormat format = new SimpleDateFormat (DATE_FORMAT); return format.format (date) } private static void setSelect (Sheet sheet, Map selectMap) {if (selectMap = = null | | selectMap.isEmpty ()) {return;} Set entrySet = selectMap.entrySet (); for (Entry entry: entrySet) {int y = entry.getKey (); List list = entry.getValue () If (list = = null | | list.isEmpty ()) {continue;} String [] arr = new String [list.size ()]; for (int I = 0; I

< list.size(); i++) { arr[i] = list.get(i); } DataValidationHelper helper = sheet.getDataValidationHelper(); CellRangeAddressList addressList = new CellRangeAddressList(1, 65000, y, y); DataValidationConstraint dvc = helper.createExplicitListConstraint(arr); DataValidation dv = helper.createValidation(dvc, addressList); if (dv instanceof HSSFDataValidation) { dv.setSuppressDropDownArrow(false); } else { dv.setSuppressDropDownArrow(true); dv.setShowErrorBox(true); } sheet.addValidationData(dv); } } private static boolean isNumeric(String str) { if ("0.0".equals(str)) { return true; } for (int i = str.length(); --i >

= 0;) {if (! Character.isDigit (str.charAt (I) {return false;}} return true;} private static String getString (String s) {if (s = = null) {return ";} if (s.isEmpty ()) {return s } return s.trim ();}} ExcelImportpackage com.zyq.util.excel; import java.lang.annotation.ElementType;import java.lang.annotation.Retention;import java.lang.annotation.RetentionPolicy;import java.lang.annotation.Target; / * * @ author sunnyzyq * @ date 2021-12-17 * / @ Target (ElementType.FIELD) @ Retention (RetentionPolicy.RUNTIME) public @ interface ExcelImport {/ * * Field name * / String value () / * * Export mapping in formats such as: 0-unknown; 1-male; 2-female * / String kv () default ""; / * * whether it is a required field (optional by default) * / boolean required () default false; / * * maximum length (default: 255L) * / int maxLength () default 255i / * * Import uniqueness verification (joint verification for multiple fields) * / boolean unique () default false;} ExcelExportpackage com.zyq.util.excel; import java.lang.annotation.ElementType;import java.lang.annotation.Retention;import java.lang.annotation.RetentionPolicy;import java.lang.annotation.Target / * * @ author sunnyzyq * @ date 2021-12-17 * / @ Target (ElementType.FIELD) @ Retention (RetentionPolicy.RUNTIME) public @ interface ExcelExport {/ * * Field name * / String value (); / * * the order of export: the smaller the number, the higher the number (default is exported in the order of Java fields) * / int sort () default 0; / * * Export mapping, format such as: 0-unknown 1-male; 2-female * / String kv () default ""; / * * Export template sample value (if there is a value, take this value directly without mapping) * / String example () default ";} ExcelClassFieldpackage com.zyq.util.excel; import java.util.LinkedHashMap; / * * @ author sunnyzyq * @ date 2021-12-17 * / public class ExcelClassField {/ * * Field name * / private String fieldName / * * header name * / private String name; / * * Mapping relationship * / private LinkedHashMap kvMap; / * * sample value * / private Object example; / * * sort * / private int sort; / * * whether it is an annotated field: 0-No, 1-Yes / private int hasAnnotation; public String getFieldName () {return fieldName } public void setFieldName (String fieldName) {this.fieldName = fieldName;} public String getName () {return name;} public void setName (String name) {this.name = name;} public LinkedHashMap getKvMap () {return kvMap;} public void setKvMap (LinkedHashMap kvMap) {this.kvMap = kvMap;} public Object getExample () {return example } public void setExample (Object example) {this.example = example;} public int getSort () {return sort;} public void setSort (int sort) {this.sort = sort;} public int getHasAnnotation () {return hasAnnotation;} public void setHasAnnotation (int hasAnnotation) {this.hasAnnotation = hasAnnotation }} these are all the contents of the article "how to implement Excel Import and Export by Java". Thank you for reading! I believe you will gain a lot after reading this article. The editor will update different knowledge for you every day. If you want to learn more knowledge, please pay attention to the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.