Properties、xls相互转换

一、 Xlsx 转换成 Properties 文件

package com.test;

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.*;
import java.util.*;

public class XlsxToProperties {
    public static void main(String[] args) {
        String inputFilePath =MergePropertiesToXlsx.inputFilePath;
        String outputFolderPath = "C:\\Users\\liyuying\\git\\gencode\\configs";
        Map<Integer,String> map = new HashMap<Integer,String>();
        try (Workbook workbook = new XSSFWorkbook(new FileInputStream(inputFilePath))) {
            int sheetCount = workbook.getNumberOfSheets();
            for (int sheetIndex = 0; sheetIndex < sheetCount; sheetIndex++) {
                Sheet sheet = workbook.getSheetAt(sheetIndex);
                Properties properties = new Properties();
                // Read column data and populate properties
                int rowCount = sheet.getLastRowNum() + 1;
                for (int rowNum = 0; rowNum < rowCount; rowNum++) {
                    Row row = sheet.getRow(rowNum);
                    if(rowNum==0) {
                    	 for (int i = 0; i < row.getLastCellNum(); i++) {
                    		 map.put(i,row.getCell(i).toString());
     					}
                    	continue;
                    }                    
					for (int i = 0; i < row.getLastCellNum(); i++) {
						if (row.getCell(i) != null) {
							if (row.getCell(i).getCellType() == CellType.NUMERIC) {
								if (row.getCell(i) != null) {
									Double value = row.getCell(i).getNumericCellValue();
									if (value != null) {
										properties.put(map.get(i), (value).intValue() + "");
										continue;
									}
								} else {
									System.out.println("row.getCell(i)==null");
									properties.put(map.get(i), "");
									continue;
								}
							} else {
								properties.put(map.get(i), row.getCell(i).toString());
								continue;
							}
						}
						System.out.println(map.get(i) + "---row.getCell(i)==null 1");
						properties.put(map.get(i), "");
					}
                    // Save properties to output file
                    String outputFilePath = outputFolderPath + File.separator + properties.getProperty("FileName");
                    try (OutputStream output = new FileOutputStream(outputFilePath)) {
                        properties.store(output, "Generated from XLSX");
                    }
                    System.out.println("Properties file has been generated: " + outputFilePath);
                }
            }
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
}

二、多个Properties 转 Xlsx

package com.test;

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.*;
import java.util.*;

public class MergePropertiesToXlsx {
	
	public static  String[] COLUMN_NAME = { "FileName", "STAFF_ROOT_URL", "NFS_SERVER", "KC_PROXY", "KC_DB_SCHEMA", "APP_NAME_SIMPLE", "KC_DB_URL" "DOMAIN_CALL","KC_DB_ADMIN_USERNAME","KC_DB_ADMIN_PASSWORD","ILS_ROOT_URL","IP_ADDRESS" };
	
    public static String inputFilePath = "Yaml配置1.xlsx";

	public static void main(String[] args) {
		String outputFilePath = inputFilePath;
		String path = "C:\\Users\\liyuying\\git\\gencode\\configs\\";
		List<String> propertyFilePaths = Arrays.asList("dev-a2.conf",  
				"sit-a2.conf",  
				"uat-a2.conf",  
				"ff-a2.conf",  
				"train-a2.conf",  
				"sandbox-a2.conf"  );


		try (Workbook workbook = new XSSFWorkbook()) {
			Sheet sheet = workbook.createSheet("Properties");
			Row row1 = sheet.getRow(0);
			if (row1 == null) {
				row1 = sheet.createRow(0);
			}
			for (int i = 0; i < COLUMN_NAME.length; i++) {
				Cell cell1 = row1.createCell(i);
				cell1.setCellValue(COLUMN_NAME[i]);
			}
			int rowNum = 1;
			for (String propertyFilePath : propertyFilePaths) {
				System.out.println("------------"+rowNum +":"+ propertyFilePath);
				Properties curProperties = new Properties();
				curProperties.put("FileName",propertyFilePath);
				try {
					InputStream input = new FileInputStream(path + propertyFilePath);
					curProperties.load(input);
					Row row = sheet.createRow(rowNum);
					rowNum ++;
					for (int i = 0; i < COLUMN_NAME.length; i++) {
						Cell cell = row.getCell(i);
						if (cell == null) {
							cell = row.createCell(i);
						}
						if (curProperties.get(COLUMN_NAME[i]) != null) {
							System.err.println(curProperties.get(COLUMN_NAME[i]).toString());
							cell.setCellValue(curProperties.get(COLUMN_NAME[i]).toString());
						} else {
							cell.setCellValue("");
						}
					}
					input.close();
					curProperties = null;
				} catch (Exception e) {
					e.printStackTrace();
				}

			}

			// Auto-size columns
			for (int i = 0; i < COLUMN_NAME.length; i++) {
				sheet.autoSizeColumn(i);
			}

			// Save the workbook to the output file
			try (OutputStream output = new FileOutputStream(outputFilePath)) {
				workbook.write(output);
			}

			System.out.println("Merged properties have been saved to: " + outputFilePath);
		} catch (IOException e) {
			e.printStackTrace();
		}
	}
}