Apache POI is a Java API for Microsoft Documents processing. It provides pure Java libraries for reading and writing files in Microsoft Office formats, such as Word, PowerPoint and Excel. The code examples in this post are based on Apache POI v5.0.0.
Integer spacingBefore; // 1/20th point paragraph.setSpacingBefore(spacingBefore); Integer spacingBeforeLines; // 1/100th line paragraph.setSpacingBeforeLines(spacingBeforeLines); Integer spacingAfter; // 1/20th point paragraph.setSpacingAfter(spacingAfter); Integer spacingAfterLines; // 1/100th line paragraph.setSpacingAfterLines(spacingAfterLines); Integer spacingBetween; // 1 line or 1 point, It depends on what LineSpacingRule used paragraph.setSpacingBetween(spacingBetween, LineSpacingRule.AUTO); paragraph.setSpacingBetween(spacingBetween, LineSpacingRule.EXACT);
// align CellUtil.setAlignment(cell, HorizontalAlignment.LEFT); CellUtil.setVerticalAlignment(cell, VerticalAlignment.TOP); // wrap text CellUtil.setCellStyleProperty(cell, CellUtil.WRAP_TEXT, true); // font CellUtil.setFont(cell, font);
Insert Images
InputStreaminputStream=newFileInputStream("C:\\Users\\Taogen\\Desktop\\demo.png"); byte[] bytes = IOUtils.toByteArray(inputStream); intpictureIdx= workbook.addPicture(bytes, Workbook.PICTURE_TYPE_PNG); inputStream.close(); //Returns an object that handles instantiating concrete classes CreationHelperhelper= workbook.getCreationHelper(); //Creates the top-level drawing patriarch. Drawingdrawing= sheet.createDrawingPatriarch(); //Create an anchor that is attached to the worksheet ClientAnchoranchor= helper.createClientAnchor(); //set top-left corner for the image anchor.setCol1(1); anchor.setRow1(2); //Creates a picture Picturepicture= drawing.createPicture(anchor, pictureIdx); //Reset the image size doublescale=0.2; picture.resize(scale); // or picture.resize() original size
In the SQL world, order is not an inherent property of a set of data. Thus, you get no guarantees from your RDBMS that your data will come back in a certain order – or even in a consistent order – unless you query your data with an ORDER BY clause.
If an <order by clause> is not specified, then the ordering of the rows is implementation-dependent.
Default Orders
MySQL Server v5.6, InnoDB
If select fields only in unique key/index, the default order is ordered by unique key/index.
if select fields only in primary key, the default order is ordered by unique key/index or primary key
if select fields contain a field that is not in the primary key, unique key, and index, the default order is ordered by the primary key.
Suggestions
Do not depend on order when ORDER BY is missing.
Always specify ORDER BY if you want a particular order.
If to exported data is not large and the exporting process can be finished in a few seconds, we can just use the synchronized exporting. Send an export request, and then download the organized data file.
Asynchronized Export
If to exported data is too large, exporting will cost a lot of time. So we need to use the asynchronized exporting. Send an export request, view the schedule of the export, wait for the handling of exporting files to be finished, and download the organized data file.
Ways of Export implementations
Write data into Java servlet response output stream.
Write data into file and store in user temporary directory user.dir, and return download file URI. Delete file when download is finished.
Upload data to OSS, and return download file URL.
The Limit of Max Size of Exported Data
Sometimes we need consider to setting the max size of data for exporting.
Fetch External Data
Fetch data from relational databases
Fetch static files with HTTP URLs
Build Exported Files
Exported File Types
Office Excel
Office Doc
Zip
Optimization
Database
SQL optimization. 1) Only query required columns. 2) Add index for query.
Cache
Cache rarely modified database data to Redis or memory.
Network IO
Fetch Database data
Fetch rows with multiple threads.
Each time to fetch a limited number of rows. It depends on the data size of a row. E.g. for the small size of a row, you can fetch 500 rows at a time.
Fetch Static Files by URL
Fetch files with multiple threads. E.g. 20 threads.
Caching files in the temporary directory.
Using blocking NIO, non-blocking NIO, or NIO2.
Compression
Compress images.
Compress text files.
Compress binary files.
Disk IO
When using traditional blocking IO, reading and writing files should use buffered Input/output wrapper class (E.g. BufferedInputStream) or read from/write to direct buffering array.
// buffered Input/output wrapper class FileInputStreamfis=newFileInputStream(filepath); BufferedInputStreambis=newBufferedInputStream(fis);
// direct buffering array FileInputStreamfis=newFileInputStream(filepath); byte buf[] = newbyte[2048]; int len; while ((len = fis.read(buf)) != -1) {}
(Optional) Using blocking NIO, non-blocking NIO, or NIO2. The Java NIO package offers the possibility to transfer bytes between two Channels without buffering them into the application memory. In single thread environment, traditional IO is better. NIO is used not because it’s faster but because it has better scalability especially there are amounts of clients.
Using List<Integer> ids to receive a form parameter of HTTP request. The size of the parameter ids is over 255.
Error Info
org.springframework.beans.InvalidPropertyException: Invalid property 'ids[256]' of bean class [com.fantasi.manage.console.web.modules.recovery.dto.RecoveryGroupDto]: Invalid list index in property path 'ids[256]'; nested exception is java.lang.IndexOutOfBoundsException: Index: 256, Size: 256.
Solutions
To override init Binder method and configure the limit size which needs so that you can pass those many objects to your controller classes.
SHA256 is difficult to handle than MD5 because of its size.
SHA256 is less secure than MD5
MD5 result in an output of 128 bits whereas SHA256 result output of 256 bits.
Concluding all points, it will be better to use MDA5 if you want to secure your files otherwise you can use SHA256.
Symmetric Encryption Algorithms
DES
DES (data encryption standard, 1976)
DES is Not Secure.
3DES
3DES is Not Secure.
AES
AES (advanced encryption system, 2001)
The AES algorithm has six modes of operation:
ECB (Electronic Code Book) Not Recommend
CBC (Cipher Block Chaining)
CFB (Cipher FeedBack)
OFB (Output FeedBack)
CTR (Counter)
GCM (Galois/Counter Mode)
Don’t use AES Electronic codebook (ECB) Mode. The AES ECB mode, or AES/ECB/PKCS5Padding (in Java) is not semantically secure.
AES encryption best practice: Don’t reuse IV with the same key.
IV
The IV (initial value or initial vector), it is random bytes, typically 12 bytes or 16 bytes. In Java, we can use SecureRandom to generate the random IV.
The AES secret key that derived from a given password. In Java, we can use the SecretKeyFactory and PBKDF2WithHmacSHA256 to generate an AES key from a given password.
// where type = 1 or type = 2 wrapper.and(wrapper -> wrapper.eq("type", 1) .or().eq("type", 2))
// where status = 0 and (type = 1 or (type > 10 and type < 20)) wrapper.eq("status", 0) .and(wrapper -> wrapper.eq("type", 1) .or(wrapper2 -> wrapper2.gt("type", 10) .lt("type", 20));
Pass com.baomidou.mybatisplus.extension.plugins.pagination.Page object as a parameter.
Using the IPage class as the return type.
Don’t need to add limit start, size in mapper XML. The SQL is query all rows. But MyBatis Plus automatically add limit at the end of SQL. If you want to query all, update to List<MyResult> queryAll(@Param("param") MyParam param);
Caused by: java.lang.IllegalArgumentException: Could not resolve placeholder 'spring.datasource.druid.initialSize' in value "${spring.datasource.druid.initialSize}" at org.springframework.util.PropertyPlaceholderHelper.parseStringValue(PropertyPlaceholderHelper.java:178) ... 97 common frames omitted
Solutions
1. Check that the property you need exists in a configuration file. Find out in which configuration file your injected property is.
2. Check whether the property spring.profiles.active value is correct in application.yml. Make sure the active profile is in your project.
3. Check whether your injected property exists in application.yml (or application.properties) and the active profile application-xxx.yml (or application-xxx.properties).
Reasons
The value of the property spring.profiles.active in my application.yml is spring.profiles.active=pro, but I don’t have the application-pro.yml file. The property’s value spring.profiles.active=pro should update to prod.