Export super disgusting mass data to excel

It's speechless. Why do so many customers want to export data to excel? Tens of thousands or even hundreds of thousands of lines? I can't even read the exported data. It's really bad luck. Alas, let's get started.

 

The simple business function is to export the data in the database to an Excel file without any modification in the file, such as color or shading. Pure data files, the customer needs to view the file and perform operations such as filtering.

At first, we used poi to generate an Excel file and output it to the output stream, which allows users to download the file directly. Unfortunately, it was not a long time. Suddenly there were 50 thousand million data records, so I couldn't hold it anymore. about of the Data overflows, and I found a lot of articles on the Internet, which basically couldn't be done.

1. First of all, poi and jxl are the two major Development Kits which need to put the Excel file into the memory and write the files together. Therefore, all row and cell data before writing are in the memory and cannot be released, resulting in memory overflow. Because of the preceding reasons, multiple write operations won't work.

2. there is an article on the Internet "Solutions to the memory overflow caused by Excel export of Mass Data", which is to generate some small temporary files and then merge them into large files, in addition, some poi binary methods are used. However, after use, the data that can be processed is doubled, but it is still hard to escape the bad luck of overflow. The author of this article says that overflow can be avoided, but I cannot. I can't find the document for Binary writing.

I am helpless. I think of using ODBC to Operate Excel a long time ago. It's a good practice, without Overflow. Originally, some SQL statements are executed cyclically. If this overflows, you can only blame yourself for being too bad. As follows:

 

Class. forname ("Sun. JDBC. ODBC. jdbcodbcdriver ");
String url = "JDBC: ODBC: Driver = {Microsoft Excel Driver (*. xls)}; readonly = false; DBQ = D: // t.xls ";
Connection con = drivermanager. getconnection (URL );

 

After the Excel file is connected, insert directly. Readonly = false is required in the URL; otherwise, data cannot be written. As for sheet, you can use poi or jxl to establish it, or create table after database connection, which is convenient.

If there is a defect, it means it can only run on Windows and cannot process styles. The ultimate solution is the bin mode.

Another method is to store excel in XML, which is not supported by some EXCEL features, such as embedding images.

Record it here and forget it.