static void Main(string[] args)
{
if (args != null)
{
if (args[0] == "ReadLine")
{
Console.ForegroundColor = ConsoleColor.Red;
Console.WriteLine("---------若有修改以上xlsx文件,需通知服务端进行处理。若无修改，请忽略---------");
Console.ForegroundColor = ConsoleColor.White;
Console.WriteLine("按任意键继续");
Console.ReadLine();
Console.ResetColor(); //将控制台的前景色和背景色设为默认值
}
else
{
string fileName = Path.GetFileNameWithoutExtension(args[0]); //返回不带扩展名的文件名
string savepath = args[1];
string savefile = args[2];
Program p = new Program();
XlsxReader xr = new XlsxReader();
string errorString;
DataSet dataset = xr.ReadXlsxFile(args[0], out errorString);
p.ExportToTxt(dataset, savepath + @"\" + fileName + savefile);
Console.ForegroundColor = ConsoleColor.Green;
Console.WriteLine("服务端文件{0}{1}导出完毕,存放目录为：{2}", fileName, savefile, savepath);
Console.ResetColor(); //将控制台的前景色和背景色设为默认值
}
}
}
public async Task AddGtfs(string dirName, IProgress <ProgressData> progress = null)
{
DirectoryInfo dInfo = new DirectoryInfo(dirName);
HashSet <string> filenames =
new HashSet <string>(dInfo.GetFiles("*.png")
.Select(fInfo => fInfo.Name));
string filenameXlsxName = dirName + "/filenames.xlsx";
XlsxReader xlsx = new XlsxReader(filenameXlsxName);
IEnumerable <Record> records = (await Task.Run(() => xlsx.GetRows("XXI", "filenames")))
.Where(record => filenames.Contains((string)record[1]));
int total = records.Count();
int count = 0;
foreach (Record record in records)
{
string entryName = (string)record[0];
string pngName = dInfo.FullName + '\\' + (string)record[1];
count++;
progress?.Report(new ProgressData {
count = count, total = total, filename = entryName
});
if (!HasFile(entryName))
{
ZipArchiveEntry entry = zipArchive.CreateEntry(entryName);
using Stream entryStream = entry.Open();
await GTF.WriteGTF(entryStream, new System.Drawing.Bitmap(pngName), (int)record[2]);
_entries.Add(new PatchZipEntry(entry));
}
}
}
/// <summary>
/// Loads a workbook from a file
/// </summary>
/// <param name="filename">Filename of the workbook</param>
/// <param name="options">Import options to override the data types of columns. These options can be used to cope with wrong interpreted data, caused by irregular styles</param>
/// <returns>Workbook object</returns>
/// <exception cref="Exceptions.IOException">Throws IOException in case of an error</exception>
public static Workbook Load(String filename, ImportOptions options = null)
{
XlsxReader r = new XlsxReader(filename, options);
r.Read();
return(r.GetWorkbook());
}
/// <summary>
/// Loads a workbook from a stream asynchronously
/// </summary>
/// <param name="stream">Stream containing the workbook</param>
/// /// <param name="options">Import options to override the data types of columns. These options can be used to cope with wrong interpreted data, caused by irregular styles</param>
/// <returns>Workbook object</returns>
/// <exception cref="Exceptions.IOException">Throws IOException in case of an error</exception>
public static async Task <Workbook> LoadAsync(Stream stream, ImportOptions options = null)
{
XlsxReader r = new XlsxReader(stream, options);
await r.ReadAsync();
return(r.GetWorkbook());
}
/// <summary>
/// Loads a workbook from a stream
/// </summary>
/// <param name="stream">Stream containing the workbook</param>
/// /// <param name="options">Import options to override the data types of columns. These options can be used to cope with wrong interpreted data, caused by irregular styles</param>
/// <returns>Workbook object</returns>
/// <exception cref="Exceptions.IOException">Throws IOException in case of an error</exception>
public static Workbook Load(Stream stream, ImportOptions options = null)
{
XlsxReader r = new XlsxReader(stream, options);
r.Read();
return(r.GetWorkbook());
}
public void ReadXlsxTest()
{
XlsxReader xlsxReader = new XlsxReader();
string xlsxPath = @".\Assets\ImportData.xlsx";
ReadXlsxOptions[] readXlsxOptions = new[]
{
new ReadXlsxOptions {
SheetName = "data",
HeaderRowIndex = 1
}
};
IReadOnlyList <SheetInfo> results = xlsxReader.ReadXlsx(xlsxPath, readXlsxOptions);
Assert.IsNotNull(results);
Assert.IsTrue(results.Any());
Assert.IsTrue(results[0].Name == "data");
Assert.IsTrue(results[0].Columns[1] == "first_name");
Assert.IsTrue(results[0].GetCell("B8").Value == "Alejandra");
Assert.IsTrue(results[0].AllRows[0][3].Value == "email");
Assert.IsTrue(results[0].AllRows[2]["country"].Value == "China");
Assert.IsTrue(results[0].Rows[0][3].Value == "*****@*****.**");
Assert.IsTrue((results[0].Rows[0] as dynamic).email == "*****@*****.**");
}
/// <summary>
/// Loads a workbook from a file
/// </summary>
/// <param name="filename">Filename of the workbook</param>
/// <returns>Workbook object</returns>
/// <exception cref="Exceptions.IOException">Throws IOException in case of an error</exception>
public static Workbook Load(String filename)
{
XlsxReader r = new XlsxReader(filename);
r.Read();
return(r.GetWorkbook());
}
/// <summary>
/// Loads a workbook from a stream
/// </summary>
/// <param name="stream">Stream containing the workbook</param>
/// <returns>Workbook object</returns>
/// <exception cref="Exceptions.IOException">Throws IOException in case of an error</exception>
public static Workbook Load(Stream stream)
{
XlsxReader r = new XlsxReader(stream);
r.Read();
return(r.GetWorkbook());
}
public static void WriteFile(Stream stream, XlsxReader xlsx)
{
List <Record> list = xlsx.GetRows("iiiiiIII", "skillBoard").ToList();
List <Record> stringList = xlsx.GetRows("IIX", "skillBoardStrings").ToList();
Binary binary = new Binary(stream, true);
List <byte> strBuf = new List <byte>();
foreach (Record record in stringList)
{
record[0] = strBuf.Count / 2;
var buf = ImasEncoding.Custom.GetBytes((string)record[2]);
strBuf.AddRange(buf);
strBuf.Add(0);
strBuf.Add(0);
}
int recordCount = list.Count;
binary.WriteInt32(recordCount);
int strBufLen = strBuf.Count / 2;
binary.WriteInt32(strBufLen);
stream.Write(strBuf.ToArray());
for (int i = 0; i < recordCount; i++)
{
list[i][2] = stringList[(int)list[i][5]][0];
list[i][3] = stringList[(int)list[i][6]][0];
list[i][4] = stringList[(int)list[i][7]][0];
list[i].Serialise(stream);
}
}
public static async Task <Workbook> LoadAsync(Stream stream)
{
XlsxReader r = new XlsxReader(stream);
await r.ReadAsync();
return(r.GetWorkbook());
}
private void OnLoaded()
{
#region Ładowanie Plików
List <Record> data = XlsxReader.ReadXlsx("..\\..\\..\\Resources\\weatherAUS.xlsx");
List <LinguisticVariable> variables = LinguisticVariableSerializer.Deserialize("..\\..\\..\\Resources\\linguisticVariables.xml");
List <LinguisticVariable> quantifiers = LinguisticVariableSerializer.Deserialize("..\\..\\..\\Resources\\linguisticQuantifiers.xml");
List <LinguisticVariable> qualificators = LinguisticVariableSerializer.Deserialize("..\\..\\..\\Resources\\linguisticQualificators.xml");
#endregion
var groups = variables.GroupBy(variable => variable.MemberToExtract).ToList();
List <FuzzySet> sets = new List <FuzzySet>();
foreach (var group in groups)
{
var movedGroupings = new List <Tuple <string, List <LinguisticVariable> > >
{
new Tuple <string, List <LinguisticVariable> >(group.Key, group.ToList())
};
movedGroupings.AddRange(groups.Where(linguisticVariables => linguisticVariables != group).Take(2)
.Select(g => new Tuple <string, List <LinguisticVariable> >(g.Key, g.ToList())));
sets = sets.Concat(GetSets(data, movedGroupings)).ToList();
}
var i = 0;
List <double> qualities = new List <double>();
foreach (var fuzzySet in sets)
{
Process(fuzzySet);
fuzzySet.Qualificator = new FuzzySet(data, qualificators[i++]);
if (i == qualificators.Count)
{
i = 0;
}
Process(fuzzySet);
void Process(FuzzySet set)
{
SummarizationResult summarizationResult = new SummarizationResult();
string bestResult = "";
double quality = 0;
foreach (var quantifier in quantifiers)
{
double degreeOfTruth = set.DegreeOfTruth(quantifier);
var summarization = $"{quantifier.Name} wpisów wskazuje {set} [Jakość: {degreeOfTruth:N3}]";
if (degreeOfTruth > quality)
{
bestResult = summarization;
quality = degreeOfTruth;
}
summarizationResult.AllSummarizations.Add(summarization);
}
qualities.Add(quality);
summarizationResult.BestSummarization = bestResult;
Summarizations.Add(summarizationResult);
}
}
Results.ItemsSource = Summarizations;
}
private bool _TryAppendCellToDown(XlsxReader reader)
{
var fields = _schema.fieldsForBuilder;
var bW = _schema.blockWidth;
var r = reader.cellRef.row - _rowRef.row;
var c = reader.cellRef.col - _rowRef.col;
if (r < 0 || c < 0 || c >= bW)
{
return(false);
}
while (r >= _schema.nextBlockOffset)
{
var row = _rowRef.row + _schema.nextBlockOffset;
_rowRef = new CellRef(row, _rowRef.col);
_fieldOrder = 0;
var isFullRow = System.Array.TrueForAll(
_rows[_rowIndex], x => x != null
);
if (!isFullRow)
{
tryAppendCell = _DoNotAppendCell;
return(false);
}
_rows.Add(new string[_schema.fieldCount]);
_rowIndex = _rows.Count - 1;
r -= _schema.nextBlockOffset;
}
do
{
if (_fieldOrder >= fields.Count)
{
return(false);
}
var field = fields[_fieldOrder];
if (field.rowOffset > r)
{
return(false);
}
if (field.rowOffset < r)
{
++_fieldOrder; continue;
}
if (field.colOffset > c)
{
return(false);
}
if (field.colOffset < c)
{
++_fieldOrder; continue;
}
var fi = fields[_fieldOrder].index;
var v = reader.cellValue;
_rows[_rowIndex][fi] = v;
++_fieldOrder;
} while (false);
return(true);
}
public void PastblWriteTest(string binName, string xlsxName, string alfFileName)
{
using XlsxReader xlsx = new XlsxReader(xlsxName);
List<string> strings = xlsx.GetRows("XX", "pastbl")
.Where(record => (string)record[0] == alfFileName)
.Select(record => (string)record[1])
.ToList();
using FileStream stream = new FileStream(binName, FileMode.Create, FileAccess.Write);
Pastbl.WriteFile(stream, strings);
}
public static void WriteFile(Stream stream, XlsxReader xlsx)
{
List <Record> mails = new List <Record>();
foreach (string sheetName in allMailSheetNames)
{
mails.AddRange(xlsx.GetRows(mailFormat, sheetName));
}
mails.Sort((record1, record2) => ((short)record1[0]).CompareTo((short)record2[0]));
Record.WriteRecords(stream, mails);
}
public static Dictionary <string, string> ParseXlsxConfigFile(string filePath, string sheetName, out string errorString)
{
if (!File.Exists(filePath))
{
errorString = string.Format("错误：TxtConfig文件不存在，输入路径为{0}", filePath);
return(null);
}
Dictionary <string, string> result = new Dictionary <string, string>();
DataSet ds = XlsxReader.ReadXlsxFile(filePath, out errorString);
if (!string.IsNullOrEmpty(errorString))
{
return(null);
}
DataTable dt = ds.Tables[sheetName + "$"];
if (dt == null)
{
errorString = "国际表内不含制定名称的工作表:" + sheetName;
return(null);
}
if (dt.Rows.Count <= AppValues.DATA_FIELD_LANG_DATA_START_INDEX)
{
errorString = "表格格式不符合要求，必须在表格首行声明字段名";
return(null);
}
if (dt.Columns.Count < 2)
{
errorString = "表格中至少需要配置2个字段";
return(null);
}
const int KEY_COLUMN_IDX = 0;
const int VALUE_COLUMN_IDX = 1;
for (int row = AppValues.DATA_FIELD_LANG_DATA_START_INDEX; row < dt.Rows.Count; ++row)
{
string inputKey = dt.Rows[row][KEY_COLUMN_IDX].ToString().Trim();
if (string.IsNullOrEmpty(inputKey))
{
continue;
}
string inputVal = dt.Rows[row][VALUE_COLUMN_IDX].ToString().Trim();
result.Add(inputKey, inputVal);
}
errorString = null;
return(result);
}
public void ReadRow(XlsxReader xlsx, Row row)
{
file = xlsx.GetString(row, "A");
messageID = xlsx.GetInt(row, "B");
flag1 = xlsx.GetBool(row, "C") ? (byte)1 : (byte)0;
flag2 = xlsx.GetBool(row, "D") ? (byte)1 : (byte)0;
name_raw = xlsx.GetString(row, "E");
message_raw = xlsx.GetString(row, "F");
name = xlsx.GetString(row, "G");
message = xlsx.GetString(row, "H");
}
public PspStand(string xlsxFilePath, string worksheetName, float plotAreaInAcres)
{
this.IngrowthByYear = new SortedDictionary <int, List <PspTreeMeasurementSeries> >();
this.MeasurementsByTag = new SortedList <int, PspTreeMeasurementSeries>();
this.MeasurementYears = new HashSet <int>();
this.plotAreaInAcres = plotAreaInAcres;
this.plotCount = 0;
this.yearOfMostRecentIngrowthAdded = Int32.MinValue;
XlsxReader reader = new XlsxReader();
XlsxReader.ReadWorksheet(xlsxFilePath, worksheetName, this.ParseRow);
}
public void WriteRecordTest(string xlsxName, string sheetName, string format)
{
using XlsxReader xlsx = new XlsxReader(xlsxName);
var records = xlsx.GetRows(format, sheetName);
using (FileStream fileStream = new FileStream("temp.bin", FileMode.Create, FileAccess.Write))
{
foreach (Record record in records)
{
record.Serialise(fileStream);
}
}
File.Delete("temp.bin");
}
public void AddParameterFiles(string xlsxName, IProgress <string> progress = null)
{
using XlsxReader xlsx = new XlsxReader(xlsxName);
AddRecordFormatFiles(xlsx, progress);
AddStageInfoFiles(xlsx, progress);
AddFanLetter(xlsx, progress);
AddPastblFiles(xlsx, progress);
AddSongInfo(xlsx, progress);
AddSkillBoard(xlsx, progress);
AddJaJp(xlsx, progress);
AddIdolMail(xlsx, progress);
AddCatalogFiles(xlsx, progress);
}
private void AddStageInfoFiles(XlsxReader xlsx, IProgress <string> progress)
{
progress?.Report(string.Format("Adding stageInfo"));
IEnumerable <Record> records = xlsx.GetRows(StageInfo.format, StageInfo.sheetName);
foreach (Record record in records)
{
ZipArchiveEntry entry = zipArchive.CreateEntry(StageInfo.parFilePath + (string)record[0]);
using Stream entryStream = entry.Open();
record.Serialise(entryStream);
_entries.Add(new PatchZipEntry(entry));
}
}
private void AddSongInfo(XlsxReader xlsx, IProgress <string> progress)
{
const string songInfoName = "songinfo/songResource.bin";
if (!HasFile(songInfoName) && xlsx.Sheets.Descendants <Sheet>().Any(sheet => sheet.Name == "songInfo"))
{
progress?.Report(string.Format("Adding {0}", songInfoName));
ZipArchiveEntry entry = zipArchive.CreateEntry(songInfoName);
using Stream entryStream = entry.Open();
SongInfo.WriteFile(entryStream, xlsx);
_entries.Add(new PatchZipEntry(entry));
}
}
public static void WriteFile(Stream stream, XlsxReader xlsxReader)
{
IEnumerable <Record> records = xlsxReader.GetRows(format, "songInfo");
int recCount = records.Count();
Binary binary = new Binary(stream, true);
binary.WriteUInt64(0x534F4E47494E464F);
binary.WriteInt32(recCount);
foreach (Record record in records)
{
record.Serialise(stream);
}
}
private void AddFanLetter(XlsxReader xlsx, IProgress <string> progress)
{
const string fanLetterFilename = "parameter/fanLetterInfo.bin";
if (!HasFile(fanLetterFilename) && FanLetterInfo.sheetNames.All(sheetName =>
xlsx.Sheets.Descendants <Sheet>().Any(sheet => sheet.Name == sheetName)))
{
progress?.Report(string.Format("Adding {0}", fanLetterFilename));
ZipArchiveEntry entry = zipArchive.CreateEntry(fanLetterFilename);
using Stream entryStream = entry.Open();
FanLetterInfo.WriteFile(entryStream, xlsx);
_entries.Add(new PatchZipEntry(entry));
}
}
private void AddIdolMail(XlsxReader xlsx, IProgress <string> progress)
{
const string idolMailName = "parameter/mail_idol_par/_dlc01_mail_idol.bin";
if (!HasFile(idolMailName) &&
IdolMail.allMailSheetNames.Any(sheetName =>
xlsx.Sheets.Descendants <Sheet>().Any(sheet => sheet.Name == sheetName)))
{
progress?.Report(string.Format("Adding {0}", idolMailName));
ZipArchiveEntry entry = zipArchive.CreateEntry(idolMailName);
using Stream entryStream = entry.Open();
IdolMail.WriteFile(entryStream, xlsx);
_entries.Add(new PatchZipEntry(entry));
}
}
private void AddJaJp(XlsxReader xlsx, IProgress <string> progress)
{
const string jaJpName = "text/im2nx_text.ja_jp";
if (!HasFile(jaJpName) &&
xlsx.Sheets.Descendants <Sheet>().Any(sheet => sheet.Name == "jaJp") &&
xlsx.Sheets.Descendants <Sheet>().Any(sheet => sheet.Name == "jaJpStrings"))
{
progress?.Report(string.Format("Adding {0}", jaJpName));
ZipArchiveEntry entry = zipArchive.CreateEntry(jaJpName);
using Stream entryStream = entry.Open();
JaJpText.WriteFile(entryStream, xlsx);
_entries.Add(new PatchZipEntry(entry));
}
}
private void AddSkillBoard(XlsxReader xlsx, IProgress <string> progress)
{
const string skillBoardName = "ui/menu/skillBoard/skillBoard.info";
if (!HasFile(skillBoardName) &&
xlsx.Sheets.Descendants <Sheet>().Any(sheet => sheet.Name == "skillBoard") &&
xlsx.Sheets.Descendants <Sheet>().Any(sheet => sheet.Name == "skillBoardStrings"))
{
progress?.Report(string.Format("Adding {0}", skillBoardName));
ZipArchiveEntry entry = zipArchive.CreateEntry(skillBoardName);
using Stream entryStream = entry.Open();
SkillBoard.WriteFile(entryStream, xlsx);
_entries.Add(new PatchZipEntry(entry));
}
}
private ImportResult ReadXlsx(string filePath, int sheetIndex = 0, int maxRows = 0, bool headerRow = true)
{
var result = new ImportResult();
IList <string[]> lstValues = new List <string[]>();
try
{
var reader = new XlsxReader(filePath)
{
CurrentSheet = sheetIndex
};
reader.ReadRecord();
result.ColumnCount = reader.ColumnCount;
if (headerRow && reader.RecordCount > 0)
{
result.Columns = reader.Values;
for (int i = 1; i < reader.RecordCount && (maxRows <= 0 || i - 1 < maxRows); i++)
{
reader.ReadRecord();
lstValues.Add(reader.Values);
}
}
else if (!headerRow)
{
for (int i = 0; i < reader.RecordCount && (maxRows <= 0 || i < maxRows); i++)
{
bool record = reader.ReadRecord();
lstValues.Add(reader.Values);
}
}
result.StatusMessage = string.Format(
"Operation completed on {0} record(s). {1} Columns Detected",
reader.RecordCount,
reader.ColumnCount);
}
catch (Exception ex)
{
result.StatusMessage = string.Format("Exception thrown: {0}", ex.Message);
}
result.Values = lstValues;
return(result);
}
private void AddRecordFormatFiles(XlsxReader xlsx, IProgress <string> progress)
{
foreach (RecordFormat format in RecordFormat.formats)
{
if (!HasFile(format.fileName))
{
progress?.Report(string.Format("Adding {0}", format.fileName));
IEnumerable <Record> records = xlsx.GetRows(format.format, format.sheetName);
if (records.Any())
{
ZipArchiveEntry entry = zipArchive.CreateEntry(format.fileName);
using Stream entryStream = entry.Open();
Record.WriteRecords(entryStream, records);
_entries.Add(new PatchZipEntry(entry));
}
}
}
}
void Start()
{
if (brain.brainType != BrainType.Show)
{
this.enabled = false;
}
else
{
List <Vector3> path = new List <Vector3>();
path = XlsxReader.ReadExcelPath("path");
Vector3[] path_array = path.ToArray();
lastPos = transform.position;
lastRotZ = 0;
DrawLine(path_array);
transform.DOPath(path_array, 10f); // Start animation
}
}
/// <summary>
/// ボタン〔データ復旧〕押下処理
/// </summary>
private async void RestoreCommandExecute()
{
string restorePathAndFileName = this.RestorePathAndFileName.Value;
var metroDialogSettings = new MetroDialogSettings()
{
AffirmativeButtonText = "はい",
NegativeButtonText = "いいえ",
AnimateHide = true,
AnimateShow = true,
ColorScheme = MetroDialogColorScheme.Theme,
};
var diagResult = await this.MainWindow.ShowMessageAsync("データ復旧の確認", "現在のデータを全て消去し、バックアップファイル " + Path.GetFileName(restorePathAndFileName) + " で上書きしますがよろしいですか？", MessageDialogStyle.AffirmativeAndNegative, metroDialogSettings);
if (diagResult != MessageDialogResult.Affirmative)
{
return;
}
// 復旧用データを取得
DataSet restoreSheets = XlsxReader.GetXLSheets(restorePathAndFileName);
// 復旧用データを検証
var checkRestoreSheetsResult = FileViewRestoreValidator.CheckRestoreSheets(restoreSheets);
if (checkRestoreSheetsResult.Count > 0)
{
await this.MainWindow.ShowMessageAsync("データ復旧処理を中止しました", "バックアップファイルの形式が正しくありません。");
return;
}
// 各テーブルのリストア処理
FileViewRestoreLogic restoreLogic = new FileViewRestoreLogic(_dbAccessor_);
restoreLogic.RestoreTables(restoreSheets);
await this.MainWindow.ShowMessageAsync("データ復旧処理が完了しました", "バックアップファイル " + Path.GetFileName(restorePathAndFileName) + " からデータを復旧しました。");
this.RestorePathAndFileName.Value = string.Empty;
}
/// <summary>
/// Reading XLSX File
/// </summary>
/// <param name="path">FilePath</param>
/// <returns>List<String[]></returns>
public List<string[]> ParseXLSXFile(string path)
{
List<string[]> parsedData = new List<string[]>();
try
{
XlsxReader reader;
reader = new XlsxReader(path);
foreach (string[] arr in reader)
{
parsedData.Add(arr);
}
return parsedData;
}
catch
{
return parsedData;
}
}
