java實現將資料庫資料轉化成excel表格顯示出來

import org.apache.log4j.LogManager;
import org.apache.log4j.Logger;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.CellRangeAddress;
import org.apache.poi.hssf.util.HSSFColor;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import java.io.FileOutputStream;
import java.util.List;

/**
 * ClassName:FactoryCreateTransactionController
 * Description:
 */
@RestController
@RequestMapping("/factory/create/transaction")
public class FactoryCreateTransactionController {
    private static final Logger logger = LogManager.getLogger(FactoryCreateTransactionController.class);

    @Autowired
    private FactoryCreateTransactionService factoryCreateTransactionService;


    // 呼叫資料庫資料生成excel表格
    @GetMapping("/Get/gasPrice")
    public String get() throws Exception {
        List<FactoryCreateTransaction> all = factoryCreateTransactionService.findAll();
/*        List listTimestamp = new ArrayList();
        List listGasPrice = new ArrayList();

        for(int i=0; i<all.size(); i++){
            String timestamp = all.get(i).getTimeStamp();
            String gasprice = all.get(i).getGasPrice();
            listTimestamp.add(timestamp);
            listGasPrice.add(gasprice);
            logger.info("timestamp ========== " + timestamp);
            logger.info("gasPrice ========== " + gasprice);
        }*/
        String sheetName = "gasPrice統計表單";
        String titleName = "gasPrice統計表";
        String fileName = "gasPriceAll";
        int columnNumber = 3;
        int[] columnWidth = { 10, 20, 30 };
        String[][] dataList = { {String.valueOf(all.get(0).getId()), all.get(0).getTimeStamp(), all.get(0).getGasPrice()},
                {String.valueOf(all.get(1).getId()), all.get(1).getTimeStamp(), all.get(1).getGasPrice()},
                {String.valueOf(all.get(2).getId()), all.get(2).getTimeStamp(), all.get(2).getGasPrice()},
                {String.valueOf(all.get(3).getId()), all.get(3).getTimeStamp(), all.get(3).getGasPrice()},
                {String.valueOf(all.get(4).getId()), all.get(4).getTimeStamp(), all.get(4).getGasPrice()},
                {String.valueOf(all.get(5).getId()), all.get(5).getTimeStamp(), all.get(5).getGasPrice()},
                {String.valueOf(all.get(6).getId()), all.get(6).getTimeStamp(), all.get(6).getGasPrice()},
                {String.valueOf(all.get(7).getId()), all.get(7).getTimeStamp(), all.get(7).getGasPrice()},
                {String.valueOf(all.get(8).getId()), all.get(8).getTimeStamp(), all.get(8).getGasPrice()},
                {String.valueOf(all.get(9).getId()), all.get(9).getTimeStamp(), all.get(9).getGasPrice()},
                {String.valueOf(all.get(10).getId()), all.get(10).getTimeStamp(), all.get(10).getGasPrice()},
                {String.valueOf(all.get(11).getId()), all.get(11).getTimeStamp(), all.get(11).getGasPrice()},
                {String.valueOf(all.get(12).getId()), all.get(12).getTimeStamp(), all.get(12).getGasPrice()},
                {String.valueOf(all.get(13).getId()), all.get(13).getTimeStamp(), all.get(13).getGasPrice()}
        };
        String[] columnName = { "id", "timeStamp", "gasPrice" };
        ExportNoResponse(sheetName, titleName, fileName,
                columnNumber, columnWidth, columnName, dataList);

        return "success";
    }

    public static void ExportNoResponse(String sheetName, String titleName,
                                        String fileName, int columnNumber, int[] columnWidth,
                                        String[] columnName, String[][] dataList) throws Exception {
        if (columnNumber == columnWidth.length&& columnWidth.length == columnName.length) {
            // 第一步，建立一個webbook，對應一個Excel檔案
            HSSFWorkbook wb = new HSSFWorkbook();
            // 第二步，在webbook中新增一個sheet,對應Excel檔案中的sheet
            HSSFSheet sheet = wb.createSheet(sheetName);
            // sheet.setDefaultColumnWidth(15); //統一設定列寬
            for (int i = 0; i < columnNumber; i++)
            {
                for (int j = 0; j <= i; j++)
                {
                    if (i == j)
                    {
                        sheet.setColumnWidth(i, columnWidth[j] * 256); // 單獨設定每列的寬
                    }
                }
            }
            // 建立第0行 也就是標題
            HSSFRow row1 = sheet.createRow((int) 0);
            row1.setHeightInPoints(50);// 裝置標題的高度
            // 第三步建立標題的單元格樣式style2以及字型樣式headerFont1
            HSSFCellStyle style2 = wb.createCellStyle();
            style2.setAlignment(HSSFCellStyle.ALIGN_CENTER);
            style2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
            style2.setFillForegroundColor(HSSFColor.LIGHT_TURQUOISE.index);
            style2.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
            HSSFFont headerFont1 = (HSSFFont) wb.createFont(); // 建立字型樣式
            headerFont1.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); // 字型加粗
            headerFont1.setFontName("黑體"); // 設定字型型別
            headerFont1.setFontHeightInPoints((short) 15); // 設定字型大小
            style2.setFont(headerFont1); // 為標題樣式設定字型樣式

            HSSFCell cell1 = row1.createCell(0);// 建立標題第一列
            sheet.addMergedRegion(new CellRangeAddress(0, 0, 0,
                    columnNumber - 1)); // 合併第0到第17列
            cell1.setCellValue(titleName); // 設定值標題
            cell1.setCellStyle(style2); // 設定標題樣式

            // 建立第1行 也就是表頭
            HSSFRow row = sheet.createRow((int) 1);
            row.setHeightInPoints(37);// 設定表頭高度

            // 第四步，建立表頭單元格樣式 以及表頭的字型樣式
            HSSFCellStyle style = wb.createCellStyle();
            style.setWrapText(true);// 設定自動換行
            style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
            style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); // 建立一個居中格式

            style.setBottomBorderColor(HSSFColor.BLACK.index);
            style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
            style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
            style.setBorderRight(HSSFCellStyle.BORDER_THIN);
            style.setBorderTop(HSSFCellStyle.BORDER_THIN);

            HSSFFont headerFont = (HSSFFont) wb.createFont(); // 建立字型樣式
            headerFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); // 字型加粗
            headerFont.setFontName("黑體"); // 設定字型型別
            headerFont.setFontHeightInPoints((short) 10); // 設定字型大小
            style.setFont(headerFont); // 為標題樣式設定字型樣式

            // 第四.一步，建立表頭的列
            for (int i = 0; i < columnNumber; i++)
            {
                HSSFCell cell = row.createCell(i);
                cell.setCellValue(columnName[i]);
                cell.setCellStyle(style);
            }

            // 第五步，建立單元格，並設定值
            for (int i = 0; i < dataList.length; i++)
            {
                row = sheet.createRow((int) i + 2);
                // 為資料內容設定特點新單元格樣式1 自動換行 上下居中
                HSSFCellStyle zidonghuanhang = wb.createCellStyle();
                zidonghuanhang.setWrapText(true);// 設定自動換行
                zidonghuanhang
                        .setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); // 建立一個居中格式

                // 設定邊框
                zidonghuanhang.setBottomBorderColor(HSSFColor.BLACK.index);
                zidonghuanhang.setBorderBottom(HSSFCellStyle.BORDER_THIN);
                zidonghuanhang.setBorderLeft(HSSFCellStyle.BORDER_THIN);
                zidonghuanhang.setBorderRight(HSSFCellStyle.BORDER_THIN);
                zidonghuanhang.setBorderTop(HSSFCellStyle.BORDER_THIN);

                // 為資料內容設定特點新單元格樣式2 自動換行 上下居中左右也居中
                HSSFCellStyle zidonghuanhang2 = wb.createCellStyle();
                zidonghuanhang2.setWrapText(true);// 設定自動換行
                zidonghuanhang2
                        .setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); // 建立一個上下居中格式
                zidonghuanhang2.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 左右居中

                // 設定邊框
                zidonghuanhang2.setBottomBorderColor(HSSFColor.BLACK.index);
                zidonghuanhang2.setBorderBottom(HSSFCellStyle.BORDER_THIN);
                zidonghuanhang2.setBorderLeft(HSSFCellStyle.BORDER_THIN);
                zidonghuanhang2.setBorderRight(HSSFCellStyle.BORDER_THIN);
                zidonghuanhang2.setBorderTop(HSSFCellStyle.BORDER_THIN);
                HSSFCell datacell = null;
                for (int j = 0; j < columnNumber; j++)
                {
                    datacell = row.createCell(j);
                    datacell.setCellValue(dataList[i][j]);
                    datacell.setCellStyle(zidonghuanhang2);
                }
            }

            // 第六步，將檔案存到指定位置
            try {
                FileOutputStream fout = new FileOutputStream("D://"+fileName+".xls");

                wb.write(fout);
                String str = "匯出" + fileName + "成功！";
                System.out.println(str);
                fout.close();
            } catch (Exception e) {
                e.printStackTrace();
                String str1 = "匯出" + fileName + "失敗！";
                System.out.println(str1);
            }
        } else {
            System.out.println("列數目長度名稱三個陣列長度要一致");
        }

    }
}