折腾日记——文件转换

我的项目用properties做配置文件，厂商提供的配置表是excel，我得一个个手敲，到不是很多，为了防止以后变多，或者改动， 写个程序来解析excel再写入properties。

/**
 * © 武汉善德科技有限公司
 */
package com.tuodier.test.test;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileWriter;
import java.io.IOException;
import java.io.InputStream;

import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

/**
 *
 * @author lidehu
 * @date 2018年12月7日下午2:42:14
 */
public class Excel2Properties {

	public static void main(String[] args) throws IOException {
		XSSFWorkbook xssfWorkbook;
		InputStream in = new FileInputStream(new File("F:/rhqx.xlsx"));
		FileWriter fileWriter = new FileWriter(new File("F:/microstrategy.properties"));
		try {
			xssfWorkbook = new XSSFWorkbook(in);
			for (int numSheet = 0; numSheet < xssfWorkbook.getNumberOfSheets(); numSheet++) {
				XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(numSheet);
				if(!"Sheet1".equals(xssfSheet.getSheetName()))
					continue;
				for (int rowNum = 1; rowNum <= xssfSheet.getLastRowNum(); rowNum++) {
					XSSFRow xssfRow = xssfSheet.getRow(rowNum);
					if (xssfRow != null) {
						fileWriter.write("##"+xssfRow.getCell(1).toString()+"\n");
						fileWriter.write("="+xssfRow.getCell(2).toString()+"/"+xssfRow.getCell(3).toString()+"\n");
						fileWriter.flush();
					}
				}
			}
		} catch (IOException e) {
			e.printStackTrace();
		} 
	}
	
	
}

 除了excel中的数据外，还有需要在数据库查的，这里就简单的用JDBC连接数据库。

 

 

解析excel放入xml中

/**
 * © 武汉善德科技有限公司
 */
package com.tuodier.test.test;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileWriter;
import java.io.IOException;
import java.io.InputStream;

import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.dom4j.Document;
import org.dom4j.DocumentHelper;
import org.dom4j.Element;
import org.dom4j.io.XMLWriter;

/**
 *
 * @author lidehu
 * @date 2018年12月9日下午6:48:09
 */
public class Excel2Xml {

	private static final String[][] DEPTS = {
				{"FXQ013"}, //研究处
				{"FXQ003"}, //收集处
				{}, //中心各处
				{"FXQ006"}, //协二
				{}, //反洗钱局
				{"FXQ017", "A1000111000029", "A1000150000243", "A1000113000944", "A1000114000147", "A1000115000136", 
				 "A1000123000732", "A1000122000364", "A1000133000037", "A1000135000128", "A1000134000115", "A1000112000032", 
				 "A1000141000266", "A1000143000104", "A1000136000067", "A1000145000714", "A1000146000208", "A1000153000931", 
				 "A1000152000120", "A1000154000072", "A1000162000058", "A1000163000174", "A1000121000387", "A1000164000011", 
				 "A1000165000529", "A1000135000092", "A1000133000227", "A1000144000028", "A1000137000373", "A1000121000096", 
				 "A1000132000099", "A1000137001134", "A1000142000089", "A1000144000055", "A1000151000028", "A1000161000727" }, //分支行
				{"FXQ004"}, //移送处
				{"FXQ007", "FXQ008", "FXQ009", "FXQ010"}, //各分析处
				{"FXQ014"}, //国际处
				{"FXQ001"}, //中心领导
				{"FXQ011"}, //信管处
				{"FXQ012"}  //方案处
			};
	
	public static void main(String[] args) throws IOException {
		InputStream in = new FileInputStream(new File("F:/rhqxV3.xlsx"));
		XMLWriter xmlWriter = null;
		try {
			XSSFWorkbook xssfWorkbook = new XSSFWorkbook(in);
			Document document = DocumentHelper.createDocument();
			Element root = document.addElement("root");
			for (int numSheet = 0; numSheet < xssfWorkbook.getNumberOfSheets(); numSheet++) {
				XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(numSheet);
				if(!"权限链接".equals(xssfSheet.getSheetName()))
					continue;
				for (int rowNum = 1; rowNum <= xssfSheet.getLastRowNum(); rowNum++) {
					XSSFRow xssfRow = xssfSheet.getRow(rowNum);
					if (xssfRow != null) {
						Element lable = root.addElement("lable");
						lable.addElement("name").setText(xssfCellToString(xssfRow.getCell(2)));
						lable.addElement("url").setText(xssfCellToString(xssfRow.getCell(3)));
						Element depts = lable.addElement("depts");
						Element menus = lable.addElement("menus");
						menus.addElement("menu").setText("gdbb");
//						menus.addElement("menu").setText(xssfCellToString(xssfRow.getCell(1)));//可以把excel的序列改成需要的菜单
						for(int cell=4; cell <=xssfRow.getLastCellNum(); cell++) {
							if(xssfRow.getCell(cell) != null && xssfRow.getCell(cell).toString().trim().equals("√")) {
								for(String deptCode : DEPTS[cell-4]) {
									depts.addElement("dept").setText(deptCode);
								}
							}
						}
					}
				}
			}
			xmlWriter = new XMLWriter(new FileWriter("F:/microstrategy.xml"));
			xmlWriter.write(document);
		} catch (IOException e) {
			e.printStackTrace();
		} finally {
			in.close();
			if(xmlWriter!=null)
				xmlWriter.close();
			System.out.println(">>>>>>>>>>>>>>>>>>>run finish<<<<<<<<<<<<<<<<<<<<");
		}
	}

	private static String xssfCellToString(XSSFCell xssfCell) {
		return xssfCell==null ? "null" : xssfCell.toString();
	}
	
}