【帶資訊的Excel模板下載】

最近在專案在做Excel下載與上傳，本篇部落格主要來進行總結，講知識分類歸倉。
程式碼:

public static void restExcel(Map<String,List<Map<String,Object>>> data,String fileName){

            int tableMarginRow= 0;
            String[] title = null;
            List<Map<String,Object>> tableData = null;

            HSSFWorkbook wb = new HSSFWorkbook();
            //資料樣式：
            HSSFCellStyle cellDataStyle = wb.createCellStyle();
            cellDataStyle = wb.createCellStyle();
            org.apache.poi.hssf.usermodel.HSSFFont cellDatafont = wb.createFont();
            cellDatafont.setFontHeight((short)200);
            cellDatafont.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
            cellDataStyle.setFont(cellDatafont);
            cellDataStyle.setBorderBottom((short)1);
            cellDataStyle.setBorderLeft((short)1);
            cellDataStyle.setBorderRight((short)1);
            cellDataStyle.setBorderTop((short)1);
            cellDataStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
            cellDataStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
            //新增一個檢測資訊表
            HSSFSheet sheet = wb.createSheet("檢測基本資訊表");
            sheet.setDefaultColumnWidth(20);
            sheet.setDefaultRowHeightInPoints(17);
            //建立一行
            HSSFRow row = sheet.createRow((int) 0);
            // 樣式
            HSSFCellStyle style = wb.createCellStyle();
            style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
            style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
            //字型：
            org.apache.poi.hssf.usermodel.HSSFFont font =  wb.createFont();
            //合併網格
            CellRangeAddress region = new CellRangeAddress(0, 0,0, 10);
            sheet.addMergedRegion(region);
            RegionUtil.setBorderBottom(1, region, sheet,wb); // 下邊框
            RegionUtil.setBottomBorderColor(HSSFColor.BLACK.index, region, sheet, wb);
            CellRangeAddress region1 = new CellRangeAddress(1,2,0,0);
            sheet.addMergedRegion(region1);
            RegionUtil.setBorderBottom(1, region1, sheet,wb); // 下邊框
            RegionUtil.setBottomBorderColor(HSSFColor.BLACK.index, region1, sheet, wb);

            region = new CellRangeAddress(1,2,1,1);
            sheet.addMergedRegion(region);
            region = new CellRangeAddress(1,2,2,2);
            sheet.addMergedRegion(region);
            region = new CellRangeAddress(1,2,3,3);
            sheet.addMergedRegion(region);
            region = new CellRangeAddress(1,2,4,4);
            sheet.addMergedRegion(region);
            region = new CellRangeAddress(1,1,5,10);
            sheet.addMergedRegion(region);

            //基本檢測資訊表 設定表頭
            HSSFCell cell = row.createCell(0);
            style.setFillBackgroundColor(HSSFColor.BLUE.index);
            cell.setCellValue("檢測基本資訊表");
            style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
            style.setFillForegroundColor((short)18);
            font.setFontHeight((short)250);
            font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
            style.setFont(font);
            cell.setCellStyle(style);


            style= wb.createCellStyle();
            font = wb.createFont();
            row = sheet.createRow(1);
            style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
            style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
            font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
            font.setFontHeight((short)250);
            style.setFont(font);

            title = new String[]{"開始日期","檢測人","檢測儀器","檢測背景","儀器序列","氣象五引數"};
            for( int j1=0;j1<6;j1++){
                cell = row.createCell(j1);
                cell.setCellValue(title[j1]);
                cell.setCellStyle(style);
            }

            title = new String[]{"溫度（°C）","溼度（%RH）","氣壓（hPa）","風向","風速（m/s）","備註"};
            row = sheet.createRow(2);
            for( int j1 =0;j1<11;j1++){
                cell = row.createCell(j1);
                cell.setCellStyle(style);
                if( j1>4){
                    cell.setCellValue(title[j1-5]);
                }
            }

            //插入資料
            tableData = data.get(testBaseInforTableName);
            font = wb.createFont();
            font.setFontHeight((short)150);
            style.setFont(font);
            for(Map<String,Object> columnData : tableData){
                row = sheet.createRow(tableMarginRow+3);
                for( int j1=0;j1<11;j1++){
                    cell = row.createCell(j1);
                    cell.setCellValue((String)columnData.get(testBaseInforTaleTitle[j1]));
                    cell.setCellStyle(cellDataStyle);
                }

            }

            //儀器校準資訊 設定表頭
            tableMarginRow = tableData.size()+4;
            region = new CellRangeAddress(tableMarginRow,tableMarginRow,0,11);
            sheet.addMergedRegion(region);
            //region = new CellRangeAddress(tableMarginRow+4,tableMarginRow+4,0,11);
            // sheet.addMergedRegion(region);
            row = sheet.createRow(tableMarginRow);
            cell = row.createCell(0);
            style = wb.createCellStyle();
            font = wb.createFont();
            font.setFontHeight((short)250);
            font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
            style.setFont(font);
            style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
            style.setFillForegroundColor((short)15);
            style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
            style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
            cell.setCellValue("儀器校準資訊");
            cell.setCellStyle(style);

            title = new String[]{"儀器序列","校準日期","校準人員","標準氣理論濃度","標準氣實際濃度",
                    "計數值","儀器校準值","響應時間","漂移校準時間","計數值","漂移校準PPM","備註"};
            tableMarginRow = tableMarginRow+1;
            row = sheet.createRow((int) tableMarginRow);
            style= wb.createCellStyle();
            font = wb.createFont();
            font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
            font.setFontHeight((short)180);
            style.setFont(font);
            style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
            style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
            for(int j1=0;j1<12;j1++){
                cell = row.createCell(j1);
                cell.setCellValue(title[j1]);
                cell.setCellStyle(style);
            }


            //新增資料
            tableData = data.get(instructionTestTableName);
            for(Map<String,Object> columnData : tableData){
                row = sheet.createRow(++tableMarginRow);
                for( int j1=0;j1<12;j1++){
                    cell = row.createCell(j1);
                    if( columnData.get(instructionTestTableTitle[j1])  instanceof Integer ){
                        cell.setCellValue(String.valueOf(columnData.get(instructionTestTableTitle[j1])));
                    }
                    else{
                        cell.setCellValue((String)columnData.get(instructionTestTableTitle[j1]));
                    }

                    cell.setCellStyle(cellDataStyle);
                }

            }
            //新增檢測資訊sheet
            sheet = wb.createSheet("檢測資訊表");
            sheet.setDefaultColumnWidth(20);
            sheet.setDefaultRowHeightInPoints(20);
            title= new  String[]{"裝置","區域","裝置","標籤號","擴充套件號","位置描述","元件型別","元件子型別","洩漏閾值","最小停留時間（秒）",
                    "結束時間","檢測儀器","檢測值","維修日期","維修人員","維修措施","複檢日期","複檢值","是否修復"};
            row = sheet.createRow(0);
            for( int j1 = 0;j1<title.length;j1++){
                cell = row.createCell(j1);
                cell.setCellStyle(style);
                cell.setCellValue(title[j1]);
            }

            //新增資料
            tableMarginRow = 0;
            tableData = data.get(testInforTableName);
            for(Map<String,Object> columnData : tableData){
                row = sheet.createRow(++tableMarginRow);
                for( int j1=0;j1<title.length;j1++){
                    cell = row.createCell(j1);
                    if(  columnData.get(copyInforTableTitle[j1]) != null ){
                        if( columnData.get(copyInforTableTitle[j1]) instanceof Integer ){
                            cell.setCellValue(String.valueOf(columnData.get(copyInforTableTitle[j1])));
                        }
                        else{
                            cell.setCellValue((String)columnData.get(copyInforTableTitle[j1]));
                        }
                    }
                    cell.setCellStyle(cellDataStyle);
                }

            }
            //寫出檔案
            FileOutputStream out = null ;
            try {
                out = new FileOutputStream(fileName);
                wb.write(out);
                out.close();
            } catch (Exception e) {
                e.printStackTrace();
            }

        }

感謝讀者的閱讀