Just recently, the company has to write a reconciliation function. The background uses java to get reconciliation information from the bank. The data is of json type, and then it is written into excel and sent to the server in the card center. A lot of codes are found on the Internet, and then they are integrated and corrected. The codes are as follows.

  1 import java.io.File;   
  2 import jxl.*;   
  3 import jxl.write.*;  
  4 import jxl.write.biff.RowsExceededException;  
  5 import java.sql.*;  
  6 import java.util.*; 
  7 
  8 
  9 public class DBtoExcel {
 10 
 11 
 12 /** 
 13      * Export Excel table 
 14      * @param rs Database result set 
 15      * @param filePath Path to save, fileName.xls 
 16      * @param sheetName Workbook name. Currently, only one Excel workbook can be exported 
 17      * @param columnName Column name, type Vector 
 18      */  
 19     public void WriteExcel(ResultSet rs, String filePath, String sheetName, Vector columnName) {  
 20         WritableWorkbook workbook = null;  
 21         WritableSheet sheet = null;  
 22           
 23           
 24         int rowNum = 1; // Write from first line  
 25         try {  
 26             workbook = Workbook.createWorkbook(new File(filePath)); // Establish Excel file  
 27             sheet = workbook.createSheet(sheetName, 0); // Create name sheetName Workbook    
 28               
 29             this.writeCol(sheet, columnName, 0); // First write the column name  
 30             // Write result set to  
 31             while(rs.next()) {  
 32                 Vector col = new Vector(); // To save a row of data  
 33                   
 34                 for(int i = 1; i <= columnName.size(); i++) { // Save a line in col in  
 35                     col.add(rs.getString(i));  
 36                 }  
 37                 // Write in Excel  
 38                 this.writeCol(sheet, col, rowNum++);  
 39             }  
 40               
 41         }catch(Exception e) {  
 42             e.printStackTrace();  
 43         }  
 44         finally {  
 45             try {  
 46                 // Close  
 47                 workbook.write();  
 48                 workbook.close();  
 49                 rs.close();  
 50             }catch(Exception e) {  
 51                 e.printStackTrace();  
 52             }  
 53         }  
 54     }
 55     /*** 
 56      * Write array to Workbook  
 57      * @param sheet Workbook to write 
 58      * @param col Array of data to write 
 59      * @param rowNum Which line to write 
 60      * @throws WriteException  
 61      * @throws RowsExceededException  
 62      */  
 63     private void writeCol(WritableSheet sheet, Vector col, int rowNum) throws RowsExceededException, WriteException {  
 64         int size = col.size(); // Get collection size  
 65           
 66         for(int i = 0; i < size; i++) { // Write to each column  
 67             Label label = new Label(i, rowNum, (String) col.get(i));   
 68             sheet.addCell(label);  
 69         }  
 70     }
 71     
 72     public static void main(String[] args) throws SQLException{
 73 
 74         String DRIVER = "com.mysql.jdbc.Driver";  
 75         String URL = "jdbc:mysql://localhost:3306/car";//Set the path and user name password according to your own database
 76         String USERNAME = "root";  
 77         String USERPASSWORD = "root";  
 78           
 79         String sql = "SELECT * FROM car";
 80 //           + "(SELECT SUBSTRING_INDEX(NAME, '/', -2) nn ,accesstime FROM accessinfo WHERE accesstime BETWEEN'2018-03-31'  AND  '2018-04-01') tt "
 81 //           + "GROUP BY nn ORDER BY times DESC;"; // Write according to your own requirements sql Sentence  
 82         Vector columnName = new Vector(); // Column names   
 83         columnName.add("Name of web page");  
 84         columnName.add("Number of visits");    
 85 //         String[]  S={"A","a","B","b","C","c","D","d","E","e"};
 86 //         List<String> list1 = Arrays.asList(S);
 87         
 88         // Connect to database  
 89         ResultSet rs = null; 
 90         try {
 91         Class.forName(DRIVER);
 92         Connection conn = DriverManager.getConnection(URL,USERNAME,USERPASSWORD);  
 93                 PreparedStatement ps = conn.prepareStatement(sql);  
 94                 rs = ps.executeQuery();
 95      // The path and Workbook name of the exported file can be modified according to your own needs
 96         new DBtoExcel().WriteExcel(rs, "D:/day03.xls", "Access record", columnName);
 97 //      
 98 } catch (ClassNotFoundException e) {
 99 // TODO Auto-generated catch block
100 e.printStackTrace();
101 }                 
102 }
103 }

The above is found on the Internet. Read data from the database and write it into excel. Because it's different from my environment, I need to modify it slightly, and write the data in Json format into excel

package com.fuyin.until;
import java.io.File;
import jxl.*;
import jxl.write.*;
import jxl.write.biff.RowsExceededException;
import java.sql.*;
import java.util.*;

import com.fuyin.entity.BhyfDownload;

public class JavatoExcel {

/**
* Export Excel table
* @param rs Database result set
* @param filePath Path to save, fileName.xls
* @param sheetName Workbook name. Currently, only one Excel workbook can be exported
* @param columnName Column name, type Vector
*/
public void WriteExcel(List<BhyfDownload> list,HashMap<String, Integer> map ,String filePath, String sheetName, Vector columnName) {
WritableWorkbook workbook = null;
WritableSheet sheet = null;
Vector alltitle = null;
Vector all = null;
try {
workbook = Workbook.createWorkbook(new File(filePath)); // Create Excel file
sheet = workbook.createSheet(sheetName, 0); // Create a workbook named sheetName
alltitle=new Vector(); //Total heading
alltitle.add("Total number of records");
alltitle.add("Number of successful records");
alltitle.add("Total amount of success");
this.writeCol(sheet, alltitle, 0); // First write the column name
all=new Vector(); //Total
all.add(map.get("allnumber")+"");
all.add(map.get("number")+"");
all.add(map.get("money")+"");
this.writeCol(sheet, all, 1); // Write content to
int rowNum = 5; // Write from line 5

this.writeCol(sheet, columnName, 4); // First write the column name
// Write result set to
for(int a=0;a<list.size();a++) {
Vector col = new Vector(); // To save a row of data
// Save a line in col
col.add(list.get(a).getPlat_transno());
col.add(list.get(a).getPartnerNo());
col.add(list.get(a).getTotal_fee());
col.add(list.get(a).getOut_trade_no());
col.add(list.get(a).getCurrencyType());
col.add(list.get(a).getTrade_state());
col.add(list.get(a).getTrans_type());
col.add(list.get(a).getPlatCharge());
col.add(list.get(a).getSubPlatCharge());
col.add(list.get(a).getTime_start());
// Write to Excel
this.writeCol(sheet, col, rowNum++);
}

}catch(Exception e) {
e.printStackTrace();
}
finally {
try {
// Close
workbook.write();
workbook.close();
}catch(Exception e) {
e.printStackTrace();
}
}
}
/***
* Write array to Workbook
* @param sheet Workbook to write
* @param col Array of data to write
* @param rowNum Which line to write
* @throws WriteException
* @throws RowsExceededException
*/
private void writeCol(WritableSheet sheet, Vector col, int rowNum) throws RowsExceededException, WriteException {
int size = col.size(); // Get collection size

for(int i = 0; i < size; i++) { // Write to each column
Label label = new Label(i, rowNum, (String) col.get(i));
sheet.addCell(label);
}
}

}

}

Import the data from the fifth line, because the data is known, so for convenience, it is all write dead. Need a jxl.jar package Baidu can find