Excel与多国语言的xml文件转换工具
前言
前两天加6国语言,整整加了一天,气不过,索性写了个xml的工具,是用java写的exe,看上去还行,周末放上去好了
本来想做一个gif展示效果的,但是最近忙得很,没心思,还是直接开源代码吧.
布局界面 StartExchange
public class StartExchange extends JFrame {
private JPanel contentPane;
private JTextField numA;
private JTextField numB;
private JTextField numResult;
private String showPath;
private JLabel showDesc;
/**
* Launch the application.
*/
public static void main(String[] args) {
EventQueue.invokeLater(new Runnable() {
public void run() {
try {
StartExchange frame = new StartExchange();
frame.setVisible(true);
} catch (Exception e) {
e.printStackTrace();
}
}
});
}
/**
* Create the frame.
*/
public StartExchange() {
setTitle("Excel和Xml相互转换");
setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
setBounds(500, 400, 500, 400); // x 坐标 ---y坐标---长 --- 宽
contentPane = new JPanel();
contentPane.setBorder(new EmptyBorder(5, 5, 5, 5));
setContentPane(contentPane);
contentPane.setLayout(null);
JLabel lbla = new JLabel("路径:");
lbla.setBounds(40, 30, 50, 20);
contentPane.add(lbla);
numA = new JTextField();
numA.setBounds(80, 30, 270, 20);
contentPane.add(numA);
numA.setColumns(10);
JButton buttonPath = new JButton("打开");
buttonPath.addActionListener(new ActionListener() {
public void actionPerformed(ActionEvent arg0) {
showJFile();
}
});
buttonPath.setBounds(360, 30, 70, 20);
contentPane.add(buttonPath);
JButton buttonXml = new JButton("XmlToExcel");
buttonXml.addActionListener(new ActionListener() {
public void actionPerformed(ActionEvent arg0) {
String path = "文件路径不能为空";
if (null == showPath || showPath.length() == 0) {
showDesc.setText(path);
return;
}
path = "无法解析Xml,请检查strings文件路径";
File file = new File(showPath);
if (!file.isDirectory() || !file.exists()) {
showDesc.setText(path);
return;
}
try {
boolean isTrue = ReadXml2WriteExcel.parseXmlList(showPath);
if(isTrue) {
path = "Excel文件已生成, 路径是: " + ReadXml2WriteExcel.writeExcelList();
}
showDesc.setText(path);
} catch (FileNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
});
buttonXml.setBounds(80, 120, 110, 20);
contentPane.add(buttonXml);
JButton buttonExcel = new JButton("ExcelToXml");
buttonExcel.addActionListener(new ActionListener() {
public void actionPerformed(ActionEvent arg0) {
String path = "文件路径不能为空";
if (null == showPath || showPath.length() == 0) {
showDesc.setText(path);
return;
}
path = "无法解析excel,请检查excel文件路径";
File file = new File(showPath);
if (file.isDirectory() || !file.exists()) {
showDesc.setText(path);
return;
}
try {
boolean isTrue = ReadExcel2WriteXml.readExcel(showPath);
if(isTrue)
path = "Xml文件夹已生成, 路径是: " + ReadExcel2WriteXml.writeFile();
showDesc.setText(path);
} catch (FileNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
});
buttonExcel.setBounds(200, 120, 110, 20);
contentPane.add(buttonExcel);
JButton buttonJson = new JButton("ExcelToJson");
buttonJson.addActionListener(new ActionListener() {
public void actionPerformed(ActionEvent arg0) {
String path = "文件路径不能为空";
if (null == showPath || showPath.length() == 0) {
showDesc.setText(path);
return;
}
path = "无法解析excel,请检查excel文件路径";
File file = new File(showPath);
if (file.isDirectory() || !file.exists()) {
showDesc.setText(path);
return;
}
try {
boolean isTrue = ReadExcel2WriteJson.readExcel(showPath);
if(isTrue)
path = "json文件夹已生成, 路径是: " + ReadExcel2WriteJson.writeFile();
showDesc.setText(path);
} catch (FileNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
});
buttonJson.setBounds(320, 120, 110, 20);
contentPane.add(buttonJson);
showDesc = new JLabel("");
showDesc.setBounds(80, 180, 400, 100);
contentPane.add(showDesc);
setResizable(false);
}
protected void showJFile() {
showDesc.setText("");
String pathStr = null != showPath && showPath.length() > 0 ? showPath : PublicConstant.baseXmlPath;
JFileChooser chooser = new JFileChooser(new File(pathStr));
chooser.setFileSelectionMode(JFileChooser.FILES_AND_DIRECTORIES);
int returnval = chooser.showDialog(this, "选择文件夹");
if (returnval == JFileChooser.APPROVE_OPTION) {
showPath = chooser.getSelectedFile().getPath();
numA.setText(showPath);
}
}
public int addNum(int a, int b) {
return a + b;
}
}
Xml 转 Excel ReadXml2WriteExcel
public class ReadXml2WriteExcel {
static Map<Integer, List<StringBean>> allDataMaps = new LinkedHashMap();
static List<LanguageBean> lanList = new ArrayList();
static String xmlPath;
static int allTypeNum;
static int startNum = -1;
public static String writeExcelList() {
try {
HSSFWorkbook wb = new HSSFWorkbook();
// 创建表头等
HSSFSheet sheet = wb.createSheet(PublicConstant.EXCEL_SHEET_NAME);
HSSFRow row = sheet.createRow(0);
HSSFCell cell;
xmlPath += File.separator;
System.out.println("45---xmlPath : " + xmlPath);
FileOutputStream output = new FileOutputStream(xmlPath + PublicConstant.excelName);
for (int i = 0; i < allTypeNum + 1; i++) {
cell = row.createCell(i);
cell.setCellValue(i == 0 ? PublicConstant.KEY : PublicConstant.fileList.get(i - 1));
}
Iterator<Entry<Integer, List<StringBean>>> iterator = allDataMaps.entrySet().iterator();
System.out.println("54---allDataMaps : " + allDataMaps.size() + ",startNum: " + startNum);
if (lanList.size() > 0)
lanList.clear();
while (iterator.hasNext()) {
Map.Entry<Integer, List<StringBean>> entry = iterator.next();
System.out.println("54---allDataMaps : " + allDataMaps.size() + ",startNum: " + startNum +",entry.getKey() : "+ entry.getKey() );
if (entry.getKey() < startNum)
continue;
if (entry.getKey() == startNum) {
for (StringBean bean : entry.getValue()) { // 这是key 对应strings里的name
lanList.add(new LanguageBean(bean.getName(), bean.getText(), 0));
}
} else {
LanguageBean lanBean;
boolean isChange = false;
for (int i = 0; i < lanList.size(); i++) {
lanBean = lanList.get(i);
for (StringBean bean : entry.getValue()) {
if (bean.getName().equals(lanBean.key)) {
isChange = true;
lanBean.valuesArray.add(bean.getText());
}
}
if(!isChange) lanBean.valuesArray.add("");
lanList.set(i, lanBean);
isChange = false;
}
}
}
if (lanList.size() > 0)
writeExcelN(sheet, wb, output);
wb.close();
output.close();
System.out.println("成功创建excel文件");
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
}
return xmlPath + PublicConstant.excelName;
}
private static void writeExcelN(HSSFSheet sheet, HSSFWorkbook wb, FileOutputStream output) throws Exception {
// TODO Auto-generated method stub
HSSFRow row;
HSSFCell cell;
LanguageBean lanBean;
System.out.println("lanList2: " + lanList.size());
for (int i = 0; i < lanList.size(); i++) {
lanBean = lanList.get(i);
row = sheet.createRow(i + 1);
row.createCell(PublicConstant.key_index).setCellValue(lanBean.key); // 对应的key
// System.out.println("110---lanBean: "+ lanBean.toString());
for (int j = 0; j < lanBean.valuesArray.size(); j++) {
// System.out.println("111-values: " + lanBean.valuesArray.size() + ",key: " + (j));
setCell(row, lanBean.valuesArray.get(j), j + 1);
}
}
wb.write(output);
output.flush();
}
public static void setCell(HSSFRow row, String values, int key) {
// System.out.println("111-values: " + values + ",key: " + key);
if (!stringIsNull(values))
row.createCell(key).setCellValue(values);
}
public static boolean stringIsNull(String result) {
return null == result || result.length() <= 0;
}
public static boolean parseXmlList(String xmlPathN) throws Exception {
xmlPath = xmlPathN;
System.out.println("xmlPath : " + xmlPath);
// 遍历xml的子
File file = new File(xmlPath);
if (file.isDirectory()) {
File[] files = file.listFiles();
String endPathName[];
List<StringBean> beanList;
String path;
if (PublicConstant.fileList.size() > 0)
PublicConstant.fileList.clear();
if (allDataMaps.size() > 0)
allDataMaps.clear();
for (int i = 0; i < files.length; i++) {
if (files[i].isDirectory()) {
beanList = new ArrayList();
path = files[i].getAbsolutePath() + File.separator + "strings.xml";
if (!new File(path).exists())
continue;
endPathName = files[i].getAbsolutePath().split("\\\\");
PublicConstant.fileList.add(endPathName[endPathName.length - 1]);
beanList = parseXml(beanList, path);
System.out.println("beanList:" + beanList.size());
allDataMaps.put(i, beanList);
if (startNum == -1)
startNum = i;
// System.out.println("文件夹:" + files[i].getAbsolutePath() + ",文件: " + path +
// ",beanList: " + beanList.size());
}
}
allTypeNum = PublicConstant.fileList.size();
System.out.println("PublicConstant.fileStrName:" + allTypeNum);
}
return allDataMaps.size() > 0;
}
public static List<StringBean> parseXml(List<StringBean> beanList, String path) throws Exception {
if (beanList.size() > 0)
beanList.clear();
SAXReader reader = new SAXReader();
File file = new File(path);
Document document = reader.read(file);
List<Element> childElements = document.getRootElement().elements();
for (Element child : childElements) {
beanList.add(new StringBean(child.attributeValue("name"), child.getText()));
}
return beanList;
}
}
Excel 转 Xml ReadExcel2WriteXml
public class ReadExcel2WriteXml {
static String[] fileName = null;
static String key;
static String excelPathN;
static int rowNum = 18;
static Map<Integer, List<StringBean>> allDataMaps = new LinkedHashMap();
public static boolean readExcel(String excelPath) throws FileNotFoundException, IOException {
excelPathN = excelPath;
if(allDataMaps.size() > 0) allDataMaps.clear();
// 创建对Excel工作簿文件的引用
HSSFWorkbook workbook = new HSSFWorkbook(new FileInputStream(excelPath));
// 创建对工作表的引用。
// 本例是按名引用（让我们假定那张表有着缺省名"Sheet1"）
HSSFSheet sheet = workbook.getSheet(PublicConstant.EXCEL_SHEET_NAME);
int num = sheet.getPhysicalNumberOfRows();
HSSFRow row;
HSSFCell cell;
String content;
rowNum = sheet.getRow(0).getPhysicalNumberOfCells() - 1; //多国语言的列数 不包括key
fileName = new String[rowNum];
List<StringBean> valuesList;
for (int i = 0; i < num; i++) {
row = sheet.getRow(i); //
for (int j = 0; j < rowNum + 1; j++) {
cell = row.getCell(j); // 第2列的内容
if(null != cell) {
cell.setCellType(CellType.STRING);
}
content = null != cell ? cell.getStringCellValue() : "";
if (j == 0) { // 第一行
key = content;
}
if (i > 0) {
if (content == "")
continue;
if(j - 1 >= 0) {
valuesList = new ArrayList();
valuesList.add(new StringBean(key, content));
if(allDataMaps.containsKey(j - 1)) {
List<StringBean> insertList = allDataMaps.get(j - 1);
insertList.addAll(valuesList);
allDataMaps.put(j - 1, insertList);
}else {
allDataMaps.put(j - 1, valuesList);
}
}
} else if (j > 0) {
fileName[j - 1] = content;
// System.out.println("content : " + cell.getStringCellValue());
}
}
}
return allDataMaps.size() > 0;
}
public static String writeFile() throws Exception {
Iterator<Entry<Integer, List<StringBean>>> iterator = allDataMaps.entrySet().iterator();
while (iterator.hasNext()) {
Map.Entry<Integer, List<StringBean>> entry = iterator.next();
createXml(entry.getValue(),fileName[entry.getKey()], returnResPath());
}
return returnResPath().toString();
}
private static StringBuilder returnResPath() {
String baseXmlPathArray[] = excelPathN.split("\\\\");
StringBuilder baseXmlPath = new StringBuilder();
for (int i = 0; i < baseXmlPathArray.length - 1; i++) {
baseXmlPath.append(baseXmlPathArray[i]).append(File.separator);
}
baseXmlPath.append("res").append(File.separator);
return baseXmlPath;
}
public static void createXml(List<StringBean> beanList, String name, StringBuilder baseXmlPath) {
// System.out.println("name : " + name +",size: "+ beanList.size());
if (beanList.size() <= 0) {
return;
}
baseXmlPath.append(name);
System.out.println("baseXmlPath: " + baseXmlPath.toString() + ",name: " + name);
File directory = new File(baseXmlPath.toString());
if (!directory.exists())
directory.mkdirs();
Document doc = (Document) DocumentHelper.createDocument();
Element resource = doc.addElement("resources");
Element string;
for (StringBean bean : beanList) {
string = resource.addElement("string");
string.addAttribute("name", bean.getName());
if (null != bean.getText() && "" != bean.getText())
string.setText(bean.getText());
}
// 实例化输出格式对象
OutputFormat format = OutputFormat.createPrettyPrint();
// 设置输出编码
format.setEncoding("UTF-8");
// 创建需要写入的File对象
baseXmlPath.append(File.separator).append("strings.xml");
File file = new File(baseXmlPath.toString());
// 生成XMLWriter对象，构造函数中的参数为需要输出的文件流和格式
XMLWriter writer;
try {
writer = new XMLWriter(new FileOutputStream(file), format);
writer.write(doc); // 开始写入，write方法中包含上面创建的Document对象
writer.close();
} catch (UnsupportedEncodingException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (FileNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
结尾
github地址
如果有不对的地方欢迎留言指正,不胜感激
6174
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
