Java读取以.xlsx结尾的excel文件，并写出每张表对应的c#类、java类、保存数据的xml文件、读取xml文件的工具类

---

需要引入的jar包：

---

ReadXLSXFile2CSharp类：
package utils;

import java.io.File;
import java.io.FileFilter;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStreamWriter;
import java.io.PrintWriter;
import java.util.ArrayList;
import java.util.Scanner;

import org.apache.poi.ss.usermodel.FormulaEvaluator;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFFormulaEvaluator;
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.OutputFormat;
import org.dom4j.io.XMLWriter;

public class ReadXLSXFile2CSharp {

public static Scanner console = new Scanner(System.in);public static ArrayList<String> contents = new ArrayList<String>();public static String suffx = ".cs";public static String des_prefix = "//";public static String end_suffx = ";";public static File out_file_dir = new File("../out_CSharp_files");public static File out_xml_dir = new File("../out_Xml_files");public static File out_java_dir = new File("../out_Java_files");public static File out_read_dir = new File("../out_readXml_files");public static String[] types;public static String[] fileds;public static String[] des;public static FormulaEvaluator formulaEvaluator;public static XMLWriter xmlwriter;public static PrintWriter pw;public static PrintWriter printWriter;public static Element root;public static String file_name;public static File out_CSharp_file;public static File out_Read_file;public static XSSFWorkbook xssfWorkbook;public static Document document;public static String prefix = "readXml_";public static String[] titles = {"using System;","using System.Collections.Generic;","using System.Linq;","using System.Text;","using System.Collections.ObjectModel;",        "using System.IO;","using System.Xml;","using System.Text.RegularExpressions;","using System.Collections;"};// 输出格式public static OutputFormat outformat = new OutputFormat();public static void getValueFromXlsxFile(File file) {    document = DocumentHelper.createDocument();    file_name = file.getName().split("\\.")[0];    root = document.addElement(file_name);    out_CSharp_file = new File(out_file_dir, file_name + suffx);    out_Read_file = new File(out_read_dir, prefix+file_name + suffx);    String namespace = "namespace  ZXHD.Game.Data\n{";// the namespace    String class_line = "\tpublic class " + file_name + " {\n\n";// the class name    String read_class_line = "\tpublic class " +prefix+ file_name + " {\n\n";// the class name    try {        pw = null;        pw = new PrintWriter(new OutputStreamWriter(new FileOutputStream(out_CSharp_file), "utf-8"), true);        pw.println(namespace);        pw.write(class_line);        printWriter = null;        printWriter = new PrintWriter(new OutputStreamWriter(new FileOutputStream(out_Read_file), "utf-8"), true);        for(String title : titles){            printWriter.println(title);        }        printWriter.println(namespace);        printWriter.println(read_class_line);        xssfWorkbook = null;        xssfWorkbook = new XSSFWorkbook(new FileInputStream(file));        formulaEvaluator = null;        formulaEvaluator = new XSSFFormulaEvaluator(xssfWorkbook);        // sheet        for (int numSheet = 0; numSheet < xssfWorkbook.getNumberOfSheets(); numSheet++) {            XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(0);// We just need the first sheet here            if (xssfSheet == null) {                continue;            }            // Get each row of the current sheet            for (int rowNum = 0; rowNum <= xssfSheet.getLastRowNum(); rowNum++) {                Element row = null;                if (rowNum >= 5) {// The first five rows are table information descriptions                    row = root.addElement("Row");                }                XSSFRow xssfRow = xssfSheet.getRow(rowNum);// Get each row of records                if (xssfRow != null) {                    // String rowValue = "";//(To untangle or comment out simultaneously1) If the solution can affect the parsing performance, if the program runs incorrectly,it can be undone to check the progress                    // String split_str = " ";//(To untangle or comment out simultaneously2)                    if (rowNum == 0) {// rowNum: 0：中文描述，1：属性名，2：类型                        des = new String[xssfRow.getLastCellNum()];                    } else if (rowNum == 1) {                        fileds = new String[xssfRow.getLastCellNum()];                    } else if (rowNum == 2) {                        types = new String[xssfRow.getLastCellNum()];                    } else if (rowNum == 3) {// To get over                        String constru = "\t\tpublic " + file_name + " {}\n\n";                        pw.println(constru);                        for (int i = 0; i < fileds.length - 1; i++) {                            String out_print_filed;                            if ("list<int>".equals(types[i])) {                                out_print_filed = "\t\tprivate global::System.Collections.Generic.List<int>" + " _"                                        + fileds[i] + " = new global::System.Collections.Generic.List<int>()"                                        + end_suffx + des[i] + "\n";                            } else if ("int".equals(types[i])) {                                out_print_filed = "\t\tprivate " + types[i] + " _" + fileds[i] + " = default("                                        + types[i] + ")" + end_suffx + des[i] + "\n";                            } else {                                out_print_filed = "\t\tprivate " + types[i] + " _" + fileds[i] + " = \"\""                                        + end_suffx + des[i] + "\n";                            }                            pw.println(out_print_filed);                            String getMethod = "\t\tpublic " + types[i] + " " + fileds[i]                                    + "\n\t\t{\n\t\t\tget { return _" + fileds[i] + " " + end_suffx + " }"                                    + "\n\t\t\tset { _" + fileds[i] + " = value" + end_suffx + " }\n\t\t}\n";                            pw.println(getMethod);                        }                    }                    // Iterate through each cell in each row of data                    for (short cellNum = 0; cellNum < xssfRow.getLastCellNum(); cellNum++) {                        XSSFCell cell = xssfRow.getCell(cellNum);// Get each cell                        String cellValue = "";                        if (cell != null) {                            cellValue = getValue(cell);                            // rowValue = rowValue + split_str +                            // cellValue;//(To untangle or comment out simultaneously1)                            if (rowNum >= 5) {// write the data we need tothe XML file                                Element cell_xml = row.addElement("cell");                                Element data = cell_xml.addElement("data");                                data.setText(cellValue);                            }                        } else {                            continue;                        }                        if (rowNum == 0 && !("".equals(cellValue.trim()))) {// If you're  in line zero, you get a Chinese  description of the corresponding property                            des[cellNum] = des_prefix + cellValue;                        } else if (rowNum == 1 && !("".equals(cellValue.trim()))) {// If I were in the first row, I get the corresponding property                            fileds[cellNum] = cellValue;                        } else if (rowNum == 2 && !("".equals(cellValue.trim()))) {// If it's the second row, you get the type of the corresponding property                            types[cellNum] = cellValue;                        }                    }                    // System.out.println(rowValue);//(To untangle or comment out simultaneously1)                }            }        }        xmlwriter = new XMLWriter(new FileOutputStream(new File(out_xml_dir, file_name + ".xml")), outformat);        xmlwriter.write(root);        System.out.println("...................解析成功！");    } catch (FileNotFoundException e) {        e.printStackTrace();    } catch (IOException e) {        e.printStackTrace();    } finally {        try {            xmlwriter.close();        } catch (IOException e) {            e.printStackTrace();        }        pw.println("\t}\n}");        pw.close();        printWriter.println("\t}\n}");        printWriter.close();    }}// Convert data formatprivate static String getValue(XSSFCell xssfRow) {    if (xssfRow.getCellType() == xssfRow.CELL_TYPE_BOOLEAN) {        return String.valueOf(xssfRow.getBooleanCellValue());    } else if (xssfRow.getCellType() == xssfRow.CELL_TYPE_NUMERIC) {        return String.valueOf((int) xssfRow.getNumericCellValue());    } else if (xssfRow.getCellType() == xssfRow.CELL_TYPE_FORMULA) {        return PoiTest.getCellValueFormula(xssfRow, formulaEvaluator);    } else {        return String.valueOf(xssfRow.getStringCellValue());    }}public static File[] getAllFiles(String xlsx_dir) {    File dir = new File(xlsx_dir);    File[] files = null;    if (dir.exists() && dir.isDirectory()) {        files = dir.listFiles(new FileFilter() {            @Override            public boolean accept(File pathname) {                return pathname.getName().endsWith("xlsx");            }        });    }    return files;}public static boolean runStart(String dir_path) {    initOutFormat();    if (!out_file_dir.exists()) {        out_file_dir.mkdir();    }    if (!out_xml_dir.exists()) {        out_xml_dir.mkdir();    }    if (!out_java_dir.exists()) {        out_java_dir.mkdir();    }    if (!out_read_dir.exists()) {        out_read_dir.mkdir();    }    File[] files = getAllFiles(dir_path);    if (files.length == 0) {        return false;    }    for (File file : files) {        System.out.print("解析文件.......ToCSharp&Xml........：" + file.getName());        getValueFromXlsxFile(file);    }    return true;}public static boolean runTwo(String dir_path) {    initOutFormat();    if (!out_file_dir.exists()) {        out_file_dir.mkdir();    }    if (!out_xml_dir.exists()) {        out_xml_dir.mkdir();    }    if (!out_java_dir.exists()) {        out_java_dir.mkdir();    }    if (!out_read_dir.exists()) {        out_read_dir.mkdir();    }    File[] files = getAllFiles(dir_path);    if (files.length == 0) {        return false;    }    ReadXLSXFile.runOption(dir_path);    return true;}public static void initOutFormat() {    // Specifies the generation rules for XML files    outformat.setEncoding("UTF-8");    outformat.setNewlines(true);    outformat.setIndent(true);    outformat.setTrimText(true);}public static void begin(String dir_path) {    String regex = "^\\d{1}$";    boolean exit = false;    String[] tips = { "===========操作有误！===========","请按照操作编码选择操作：0: 【退出】 1:【将xlsx------> cs  、 xml 】     2:【将xlsx------> java】 " };    while (!exit) {        System.out.println(tips[1]);        String menu_choose = console.nextLine();        if (!menu_choose.matches(regex)) {            System.out.println(tips[0]);        } else {            switch (Integer.parseInt(menu_choose)) {            case 0:                exit = true;                break;            case 1:                optionOne(dir_path);                break;            case 2:                optionTwo(dir_path);                break;            }        }    }    System.out.println("退出成功!");}public static void optionOne(String dir_path) {    long start = System.currentTimeMillis();    if (runStart(dir_path)) {        System.out.println("***************解析完成！***************");        System.out.println("耗时：" + (System.currentTimeMillis() - start) + "毫秒");    } else {        System.out.println("**************文件夹为空！**************");    }}public static void optionTwo(String dir_path) {    long start = System.currentTimeMillis();    if (runTwo(dir_path)) {        System.out.println("***************解析完成！***************");        System.out.println("耗时：" + (System.currentTimeMillis() - start) + "毫秒");    } else {        System.out.println("**************文件夹为空！**************");    }}public static void main(String[] args) {    String dir_path = "/excel";// Store the directory of XLSX files    begin(dir_path);}

}

---

PoiTest类:
package utils;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.text.SimpleDateFormat;

import org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.FormulaEvaluator;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFFormulaEvaluator;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class PoiTest {

public static void main(String[] args) {    String path = "/excel/ItemInfo.xlsx";    File file = new File(path);    InputStream in = null;    Workbook workbook = null;    FormulaEvaluator formulaEvaluator = null;    try {        in = new FileInputStream(file);        if (file.getName().endsWith("xlsx")) {            workbook = new XSSFWorkbook(in);            formulaEvaluator = new XSSFFormulaEvaluator((XSSFWorkbook) workbook);        } else {            workbook = new HSSFWorkbook(in);            formulaEvaluator = new HSSFFormulaEvaluator((HSSFWorkbook) workbook);        }        Sheet sheet = workbook.getSheetAt(0);        for (Row row : sheet) {            for (Cell cell : row) {                //结果比较                System.out.println(getCellValue(cell) + "**" + getCellValueFormula(cell, formulaEvaluator));            }        }    } catch (IOException e) {        e.printStackTrace();    } finally {        if (in != null) {            try {                in.close();            } catch (IOException e) {                e.printStackTrace();            }        }    }}//未处理公式public static String getCellValue(Cell cell) {    if (cell == null) {        return null;    }    switch (cell.getCellType()) {        case Cell.CELL_TYPE_STRING:            return cell.getRichStringCellValue().getString().trim();        case Cell.CELL_TYPE_NUMERIC:            if (DateUtil.isCellDateFormatted(cell)) {                SimpleDateFormat sdf = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");//非线程安全                return sdf.format(cell.getDateCellValue());            } else {                return String.valueOf(cell.getNumericCellValue());            }        case Cell.CELL_TYPE_BOOLEAN:            return String.valueOf(cell.getBooleanCellValue());        case Cell.CELL_TYPE_FORMULA:            return cell.getCellFormula();        default:            return null;    }}//处理公式public static String getCellValueFormula(Cell cell, FormulaEvaluator formulaEvaluator) {    if (cell == null || formulaEvaluator == null) {        return null;    }    if (cell.getCellType() == Cell.CELL_TYPE_FORMULA) {        return String.valueOf(formulaEvaluator.evaluate(cell).getNumberValue());    }    return getCellValue(cell);}

}

---

ReadXLSXFile类：
package utils;

import java.io.File;
import java.io.FileFilter;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStreamWriter;
import java.io.PrintWriter;
import java.util.ArrayList;

import org.apache.poi.ss.usermodel.FormulaEvaluator;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFFormulaEvaluator;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class ReadXLSXFile {

public static ArrayList<String> contents = new ArrayList<String>();public static String suffx = ".java";public static String des_prefix = "//";public static String end_suffx = ";";public static File out_file_dir = new File("../out_java_files");public static String[] types ;public static String[] fileds;public static String[] des;public static FormulaEvaluator formulaEvaluator;public static void getValueFromXlsxFile(File file)  {    String file_name = file.getName().split("\\.")[0];    File out_java_file = new File(out_file_dir, file_name+suffx);    if (!out_java_file.exists()) {        try {            out_java_file.createNewFile();        } catch (IOException e) {            e.printStackTrace();        }    }    String first_line = "public class " + file_name +" {\n\n";//类名    PrintWriter pw=null;    InputStream is = null;    try {    pw = new PrintWriter(new OutputStreamWriter(new FileOutputStream(out_java_file),"utf-8"),true);     pw.write(first_line);        is = new FileInputStream(file);        XSSFWorkbook xssfWorkbook = new XSSFWorkbook(is);        formulaEvaluator = null;        formulaEvaluator = new XSSFFormulaEvaluator(xssfWorkbook);        // 获取每一个工作薄        for (int numSheet = 0; numSheet < xssfWorkbook.getNumberOfSheets(); numSheet++) {            XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(0);            if (xssfSheet == null) {                continue;            }            // 获取当前工作薄的每一行            for (int rowNum = 0; rowNum <= xssfSheet.getLastRowNum(); rowNum++) {//抛弃前五行，前五行为描述                XSSFRow xssfRow = xssfSheet.getRow(rowNum);//获取每一行记录                if (xssfRow != null) {//rowNum:   0：中文描述，1：属性名，2：类型

// String rowValue = “”;
// String split_str =” “;
if(rowNum == 0){
des = new String[xssfRow.getLastCellNum()];
}else if (rowNum == 1) {
fileds = new String[xssfRow.getLastCellNum()];
}else if (rowNum == 2) {
types = new String[xssfRow.getLastCellNum()];
}else if(rowNum == 3 ){//获取完毕
String constru = “\tpublic “+file_name+” {}\n\n”;
pw.println(constru);
for(int i = 0;i