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 format
private 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
相關文章
- JAVA 讀取xml檔案JavaXML
- Java讀取Json檔案工具類JavaJSON
- Java系列：讀取XML檔案JavaXML
- Java 通過Xml匯出Excel檔案，Java Excel 匯出工具類，Java匯出Excel工具類JavaXMLExcel
- C#讀取Xml檔案C#XML
- list集合、txt檔案對比的工具類和檔案讀寫工具類
- XMl 檔案屬性的讀取XML
- Java讀取多層級xml檔案JavaXML
- 讀取xml檔案 解析雙層xmlXML
- 用JDOM讀取XML檔案XML
- java mysql儲存和讀取檔案JavaMySql
- 通用的config檔案讀取類
- 簡單讀取XML檔案中的值XML
- Pandas之EXCEL資料讀取/儲存/檔案分割/檔案合併Excel
- 使用C#讀寫xml檔案C#XML
- C#讀寫xml檔案應用案例C#XML
- python讀取兩個excel資料檔案輸出整理好以後的excel資料檔案PythonExcel
- SaxReader讀取，更新xml檔案XML
- NSXMLDocument讀取遠端XML檔案XML
- Java 讀取檔案Java
- Java中的獲取檔案的物理絕對路徑，和讀取檔案Java
- 讀寫iOS XML檔案iOSXML
- Java屬性檔案的讀取Java
- 解析xlsx檔案---Java讀取Excel2007JavaExcel
- 基於 DOM 的 XML 檔案解析類XML
- Go讀取yaml檔案到struct類GoYAMLStruct
- 讀取檔案流並寫入檔案流
- 類檔案結構_class類檔案的的結構
- QDomDocument 讀取和編輯xml檔案XML
- python讀取檔案——python讀取和儲存mat檔案Python
- 自己動手寫類似酷狗播放器(5)_檔案的儲存和讀取播放器
- java讀取properties檔案Java
- SQL Server中讀取XML檔案的簡單做法SQLServerXML
- Java讀取本地檔案內容並輸出Java
- Rss-Rome 閱讀器讀取XML檔案XML
- vbs類生成xml檔案(轉)XML
- jquery實現的讀取並解析xml檔案程式碼例項jQueryXML
- java讀取大檔案並處理Java