//====================================
//描 述 : Excel => C#
//作 者 :
//创建时间 :2018/10/12 11:11:47
//版 本 :
// ===============================================
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using System;
using System.Collections;
using System.Collections.Generic;
using System.IO;
using System.Text;
using UnityEditor;
using UnityEngine;
public class ConvertExcelToCode : Editor
{
[MenuItem("Test/Excel => Code")]
public static void TestCreatCode()
{
string path = "C:/Users/Administrator/Desktop/Test.xls";
IWorkbook excelFile = ReadExcel(path);
string context = GetCodeContext(GetCodeStrs(excelFile), excelFile);
SaveCode(context);
}
private static IWorkbook ReadExcel(string path) {
FileStream stream = File.Open(path, FileMode.Open, FileAccess.Read, FileShare.ReadWrite);
HSSFWorkbook hSSFWorkbook = new HSSFWorkbook(stream);
stream.Close();
return hSSFWorkbook;
}
private static Dictionary<int, List<string>> GetCodeStrs(IWorkbook book) {
Dictionary<int, List<string>> strDict = new Dictionary<int, List<string>>();
for (int i = 0; i < book.NumberOfSheets; i++){
ISheet sheet = book.GetSheetAt(i);
for (int j = sheet.FirstRowNum; j < 3; j++){
IRow row = sheet.GetRow(j);
for (int k = row.FirstCellNum; k < row.LastCellNum; k++)
{
ICell cell = row.GetCell(k);
string str = GetCellVelue(cell);
if (strDict.ContainsKey(k))
{
strDict[k].Add(str);
}
else {
List<string> strs = new List<string>();
strs.Add(str);
strDict.Add(k, strs);
}
}
}
}
return strDict;
}
private static string GetCellVelue(ICell cell) {
if (cell == null || cell.CellType == CellType.BLANK){
return "";
}
if (cell.CellType == CellType.NUMERIC){
if (HSSFDateUtil.IsCellDateFormatted(cell))//日期类型
{
if (cell.DateCellValue.Year < 2000)
{
return cell.DateCellValue.ToString("HH:mm:ss");
}
else
{
return cell.DateCellValue.ToString("yyyy/MM/dd HH:mm:ss");
}
}
else
{
return cell.NumericCellValue.ToString();
}
}
else if (cell.CellType == CellType.BOOLEAN){
return cell.BooleanCellValue.ToString();
}
else{
return cell.StringCellValue;
}
}
private static string GetCodeContext(Dictionary<int, List<string>> strDict, IWorkbook book) {
StringBuilder sb = new StringBuilder();
sb.AppendLine(GetDefultCodeStrs());
sb.AppendLine(GetCodeClassName(book) + "{");
foreach (var item in strDict) {
sb.AppendLine(PackField(item.Value[0], item.Value[2], item.Value[1]));
}
sb.Append("}");
return sb.ToString();
}
private static string PackField(string chsTypeName, string typeName, string name){
StringBuilder sb = new StringBuilder();
sb.AppendLine(" /// <summary>");
sb.AppendLine(" /// " + chsTypeName);
sb.AppendLine(" /// <summary>");
sb.AppendLine(" public " + GetTypeName(typeName) + " " + name + ";");
return sb.ToString();
}
/// <summary>
/// 这个方法点不好,需要优化
/// </summary>
/// <param name="typeName"></param>
/// <returns></returns>
private static string GetTypeName(string typeName) {
typeName = typeName.ToLower();
if (typeName.Contains("int"))
return "int";
return typeName;
}
private static string GetDefultCodeStrs() {
StringBuilder sb = new StringBuilder();
sb.AppendLine("/// Auto generated code,don't change manual!!");
sb.AppendLine("using System;");
sb.AppendLine("using System.Collections.Generic;\r\nusing System.IO;");
return sb.ToString();
}
private static string GetCodeClassName(IWorkbook book) {
StringBuilder sb = new StringBuilder();
sb.AppendLine("public class " + book.GetSheetAt(0).SheetName);
return sb.ToString();
}
private static void SaveCode(string str)
{
string path = "C:/Users/Administrator/Desktop/Test.cs";
if (File.Exists(path)) File.Delete(path);
using (FileStream aFile = new FileStream(path, FileMode.OpenOrCreate))
{
using (StreamWriter sw = new StreamWriter(aFile, Encoding.UTF8))
{
sw.WriteLine(str);
}
}
}
}