Cabbage Java self study room covers core knowledge
Excel document export in Java mainly includes POI, JXL and direct IO stream. These three methods have different advantages and disadvantages respectively. The following is a brief introduction of them.
Commonly used formats
1. Excel2003 format
Excel2003 supports a maximum of 65536 rows and 256 columns per worksheet. The maximum number of worksheets that can be supported depends on CPU response speed and memory size. Note the limit on the number of rows and columns in Excel 2003 when you export data in the common export methods. POI supports this format only for HSSF packages. When the amount of exported data is greater than the maximum number of lines on a page (65536), it can be stored in pages.
Recommended export methods: 1) POI; 2) JXL.
The recommended export method supports Excel 2003, but the maximum amount of exported data is limited. The export of millions of large data cannot be realized.
2. Excel2007 format
Excel 2007 is an upgrade to Excel 2003, which supports up to 1,048,576 rows and 16,384 columns per worksheet. Note the limit on the number of rows and columns in Excel 2007. Only XSSF packages, including SXSSF extension packages, support this format, and only SXSSF supports big data.
Recommended export methods: 1) POI3.8 or later.
3. The CSV format
CSV is a common and relatively simple file format. It is widely used to transfer table data between programs.
It usually has the following characteristics:
- Plain text, which can be opened using Excel and a text editor;
- Each record is separated into fields by separators (typical separators are commas, semicolons, or tabs; Sometimes the delimiter may include optional Spaces);
- Common export methods do not cause memory overflow problems.
Common export methods are as follows: 1) Direct I/O stream.
Export plan
1. Use Apache POI SXSSFWorkbook to export Excel
Apache POI is a free open source cross-platform Java API written in Java. Apache POI provides API for Java programs to read and write Files in Microsoft Office format. The Apache POI component is a powerful API for Java operation of Microsoft Office Suite. Because the file structure of Office 2007 is completely different from that of 2003, POI has a different processing API. When the export format is Office 2003, POI calls the HSSF package. When the export format is Office 2007, the XSSF package is invoked. SXSSF package is an extension of XSSF on POI3.8 and is used to export large amounts of data, which effectively avoids memory overflow in practical applications.
This method supports not only the operation of Excel, but also the operation of files in other formats of Microsoft Office, such as Word, PowerPoint, etc., and supports the complex operation of cells. In addition, this method is far superior to JXL in the efficiency of reading, and far superior to JXL in the magnitude of data processing.
Recommended usage: 1 big data; 2. Complex cell requirements; 3) When reading data.
The exact package used to invoke the POI for the export function depends on your requirements.
Code implementation:
public void exportExcle(a) {
// Set the number of rows to be stored in memory, and the extra rows to be stored in hard disk
int cacheItems = 100;
Workbook wb = new SXSSFWorkbook(cacheItems);
Sheet sh = wb.createSheet();
// Query data
ResultSet rs = queryData();
int rownum = 0;
while(rs.next()){
Row row = sh.createRow(rownum);
Cell cell = row.createCell(0);
cell.setCellValue(rs.getString("c1"));
cell = row.createCell(1);
cell.setCellValue(rs.getString("c2")); ... cell = row.createCell(19);
cell.setCellValue(rs.getString("c20"));
rownum ++;
// Every time the number of rows reaches the set value, the data is flushed to the hard disk to clear the memory
if(rownum % cacheItems == 0){
((SXSSFSheet)sh).flushRows();
}
}
FileOutputStream out = new FileOutputStream("/excel_data.xlsx");
wb.write(out);
out.close();
}
Copy the code2. Use buffer streams to produce CSV files
A stream is an ordered collection of bytes with a starting point and an ending point. It is a general term or abstraction for data transmission. That is, data transmission between two devices is called a flow. The essence of a flow is data transmission. According to data transmission characteristics, a flow is abstracted into various classes to facilitate more intuitive data operations.
The POI and JXL mentioned above are actually IO stream encapsulation based tools. This approach uses the most primitive form of export work, and selecting the right stream can be very efficient. However, only text files, such as CSV and TXT, can be exported. The exported files are relatively large.
Recommended usage: 1) The exported file is a text file. 2) Don’t care if the exported file is too large.
Code implementation:
public void exportExcle(a) {
File csvFile = null;
BufferedWriter csvWriter = null;
csvFile = new File("/excel_data.csv");
File parent = csvFile.getParentFile();
if(parent ! =null && !parent.exists()) {
parent.mkdirs();
}
csvFile.createNewFile();
// utF-8 makes the delimiter "," correctly read
csvWriter = new BufferedWriter(new OutputStreamWriter(new FileOutputStream(
csvFile), "UTF-8"), 1024);
// Query data
ResultSet rs = queryData();
while(rs.next()){
StringBuffer sb = new StringBuffer();
sb.append("\" ").append(rs.getString("c1")).append("\";");
sb.append("\" ").append(rs.getString("c2")).append("\";"); ... sb.append("\" ").append(rs.getString("20")).append("\" ");
csvWriter.write(sb.toString());
csvWriter.newLine();
}
csvWriter.close();
}
Copy the code3. Export EXCEL in JXL mode
JXL is an open source Java Excel API project. It serves as a common support library for the Java Excel API because of its basic ability to create, read, and write spreadsheets. The basic features are as follows:
- Generating Excel files
- Import data from workbooks and spreadsheets
- Obtain the total number of rows and columns this method is very good for Chinese, there will be no garbled characters, support the common operation of cells, meet the general requirements, this method is better than POI in writing efficiency.
Note, however: JXL only supports XLS file format and handles a very limited amount of data.
Recommended usage: 1) The amount of data is not too large; 2) Simple cell requirements; 3) When data is written.
Code implementation:
public void exportExcle(a) {
WorkbookSettings wbSetting = new WorkbookSettings();
wbSetting.setUseTemporaryFileDuringWrite(true);
// Location of the temporary folder
wbSetting.setTemporaryFileDuringWriteDirectory(new File("/"));
WritableWorkbook book = jxl.Workbook.createWorkbook(new File("/excel_data.xls"),wbSetting);
// Set the style and font
WritableFont font1 = new WritableFont(WritableFont.createFont(Microsoft Yahei), 10 ,WritableFont.BOLD);
WritableFont font2 = new WritableFont(WritableFont.createFont(Microsoft Yahei), 9 ,WritableFont.NO_BOLD);
WritableCellFormat wcf = new WritableCellFormat(font1);
WritableCellFormat wcf2 = new WritableCellFormat(font2);
// Parallel center
wcf.setAlignment(Alignment.CENTRE);
// Vertical center
wcf.setVerticalAlignment(VerticalAlignment.CENTRE);
wcf2.setAlignment(Alignment.CENTRE);
wcf2.setVerticalAlignment(VerticalAlignment.CENTRE);
// Query data
ResultSet rs = queryData();
int rownum = 0;
// SHEET Indicates the page size. The maximum value is 65536
int sheetCount = 50000;
int sheetNum = 0;
WritableSheet sheet = null;
while(rs.next()){
if(rownum % sheetCount == 0){
sheetNum ++;
rownum = 0;
// Generate a worksheet named "first page" with the argument 0 indicating that this is the first page
sheet = book.createSheet( "The first"+ sheetNum +"Page" , sheetNum - 1);
}
// page set object
sheet.addCell(new Label( 0 , rownum, rs.getString("c1"), wcf2));
sheet.addCell(new Label( 1 , rownum, rs.getString("c2"), wcf2)); . sheet.addCell(new Label( 18 , rownum, rs.getString("c19"), wcf2));
sheet.addCell(new Label( 19 , rownum, rs.getString("c20"), wcf2));
// Set the line height
sheet.setRowView(rownum, 370);
rownum ++;
}
// Write data and close the file
book.write();
book.close();
}
Copy the codeProject summary
The third solution limits the maximum amount of data that can be exported. Therefore, this solution is not recommended. Scheme 1 and 2 have good support for the export of large amounts of data, which can be seen in the selection of a more suitable scheme according to the export format, storage space occupied and other factors.