using Excel;
using Sirenix.OdinInspector;
using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Text;
using UnityEditor;
using UnityEngine;
//[CreateAssetMenu(fileName = "ExcelToCsvDataSetting")]
public class ExcelToCsvData : ScriptableObject
{
[Serializable]
public class ReadOne
{
public int startRow = 4;
public int keyRow = 0;
public string readPath;
public string writePath;
public string titleName;
public string sheetName = "Sheet1";
//[Button, HorizontalGroup("Group 1")]
//public void GetReadPath() => EditorUtility.RevealInFinder(readPath);
//[Button, HorizontalGroup("Group 1")]
//public void WritePath() => EditorUtility.RevealInFinder(writePath);
[Button, HorizontalGroup("Group 1")]
public void ReadExcel()
{
if (string.IsNullOrEmpty(readPath) || string.IsNullOrEmpty(writePath) || string.IsNullOrEmpty(titleName))
{
Debug.LogError(string.Format("读取路径:{0} ;写入路径:{1} ;数据类型名称:{2} ;其中一个为空", readPath, writePath,titleName));
return;
}
ReadExcel2CreateExporterConfig(readPath, writePath, titleName, sheetName, startRow, keyRow);
}
}
public List<ReadOne> readList;
[Button]
public void AllReadExcel()
{
if(readList != null)
{
Debug.Log("全部读取:" + readList.Count);
for (int i = 0; i < readList.Count; i++)
{
readList[i].ReadExcel();
}
AssetDatabase.Refresh();
}
}
public static void ReadExcel2CreateExporterConfig(string readPath, string writePath, string titleName, string sheetName,int startRow,int keyRow)
{
FileStream stream = null;
try
{
stream = File.Open(readPath, FileMode.Open, FileAccess.Read, FileShare.Read);
}
catch (System.Exception ex)
{
string err = string.Format("读取配置表[{0}]失败, 检查是否用Excel打开了这个配置表", readPath);
EditorUtility.DisplayDialog("", err, "失败");
Debug.LogError(err);
return;
}
string writePath1 = string.Format("{0}/{1}.csv", writePath, titleName);
IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);
DataSet result = excelReader.AsDataSet();
System.Data.DataTable wookSheet = result.Tables[sheetName];
if (wookSheet == null)
{
Debug.LogError("读取的数据为空:" + titleName);
return;
}
int columnCount = wookSheet.Columns.Count;
int endRow = wookSheet.Rows.Count;
StringBuilder builder = new StringBuilder();
for (int index = 0; index < columnCount; index++)
{
string keyText = wookSheet.Rows[keyRow][index].ToString();
if (!string.IsNullOrEmpty(keyText))
{
builder.Append(keyText);
if (index < columnCount - 1)
{
builder.Append(',');
}
}
}
builder.Append('\n');
ReadBuilder(startRow, endRow, columnCount, wookSheet, ref builder);
FileStream fileStream = new FileStream(writePath1, FileMode.Create, FileAccess.Write);
StreamWriter sr = new StreamWriter(fileStream);
sr.WriteLine(builder.ToString());
sr.Close();
fileStream.Close();
Debug.Log("成功读取文件:" + readPath);
}
private static void ReadBuilder(int startRow,int endRow,int columnCount,DataTable wookSheet,ref StringBuilder builder)
{
for (int row = startRow; row < endRow; row++)
{
for (int line = 0; line < columnCount; line++)
{
string value = wookSheet.Rows[row][line].ToString();
if (!string.IsNullOrEmpty(value))
{
builder.Append(value);
}
if (line < columnCount - 1)
{
builder.Append(',');
}
}
int nextRow = row + 1;
if(nextRow < endRow - 1)
{
string value = wookSheet.Rows[nextRow][0].ToString();
if (string.IsNullOrEmpty(value))
{
return;
}
}
if (row < endRow - 1)
{
builder.Append('\n');
}
}
}
}