/// <summary>Call this method to get the sheet data</summary>
/// <remarks>Convert the TextAsset from json file to class SheetInfo</remarks>
/// <param name="textAsset">the json file you want to convert</param>
public static SheetInfo GetSheet(TextAsset textAsset)
{
string content = textAsset.text;
SheetInfo sheet = JsonUtility.FromJson <SheetInfo> (content);
return(sheet);
}
public SheetInfo loadExcelData(string filePath)
{
//文件名为空
if (string.IsNullOrEmpty(filePath))
{
throw new Exception("文件名为空");
}
string filename = System.IO.Path.GetFileName(filePath);
string[] parts = filename.Split("-_.".ToCharArray());
//格式定义：I,C,CX
if (parts.Length != 4 || !new string[] { "I", "C", "CX" }.Contains(parts[2]))
{
throw new Exception("文件名格式错误");
}
SheetInfo result = new SheetInfo();
ExcelTable exceltable = new ExcelTable();
exceltable.CurrentFile = parts[0];
exceltable.Signal = parts[1];
result.ExcelTableData = exceltable;
result.Tag = parts[2].Equals("I");
readExcelData(filePath, result, parts[2]);
return(result);
}
//Parse sheet IDs from the HTML view.
List <SheetInfo> GetSheetIDs(string URL, List <SheetInfo> sheetList)
{
string output = GetWebpage(URL);
MatchCollection matches = Regex.Matches(output, ";gid=(?<sheetID>[0-9]+)\">(?<sheetTitle>[^<]+)</");
foreach (Match mat in matches)
{
int sheetID = int.Parse(mat.Groups["sheetID"].Value);
string sheetTitle = mat.Groups["sheetTitle"].Value;
bool present = false;
foreach (SheetInfo info in sheetList)
{
if (info.ID == sheetID)
{
present = true;
break;
}
}
if (!present)
{
SheetInfo inf = new SheetInfo();
inf.ID = sheetID;
inf.title = sheetTitle;
sheetList.Add(inf);
}
}
return(sheetList);
}
/// <summary>
/// Constructor
/// </summary>
/// <param name="excelFile">The sheet to read.</param>
/// <param name="settings">Processing settings.</param>
internal ExcelFileReader(Stream excelFile, DataFileReaderSettings settings)
{
if (excelFile == null)
{
throw new ArgumentNullException("excelFile");
}
if (settings == null)
{
throw new ArgumentNullException("settings");
}
_stream = excelFile;
_settings = settings;
_document = SpreadsheetDocument.Open(_stream, false);
_sheetId = _settings.SheetId;
if (_sheetId == null)
{
SheetInfo firstSheet = GetSheets( )?.FirstOrDefault( );
if (firstSheet != null)
{
_sheetId = firstSheet.SheetId;
}
}
}
private void GenerateColumns(DataGrid dataGrid, SheetInfo sheetInfo)
{
dataGrid.Columns.Clear();
int i = 0;
#region Dynamic Columns
foreach (var m_Column in sheetInfo.Columns)
{
if (m_Column.Index > 0)
{
string m_FieldName = CustomEntity.GetPropertyName(i);
string m_BackgroundFieldName = CustomEntity.GetBackgroundFieldName(i);
//Use FColumn -> CellTemplate
DataGridTemplateColumn m_TemplateColumn = new DataGridTemplateColumn()
{
Header = m_Column.Header,
IsReadOnly = false,
};
m_TemplateColumn.CellTemplate = GetDataTemplate(m_FieldName, m_BackgroundFieldName);
dataGrid.Columns.Add(m_TemplateColumn);
i++;
}
}
#endregion
}
private void PrintRightBtn_Click(object sender, RoutedEventArgs e)
{
CollectionViewSource cvs = FindResource("ViewSource") as CollectionViewSource;
if (cvs == null)
{
return;
}
if (cvs.View.CurrentItem == null)
{
return;
}
SheetInfo sheetInfo = cvs.View.CurrentItem as SheetInfo;
string msg = "";
if (PrintDeal.CreateInstance().PrintSheetRightSheet(sheetInfo, CurProcedureInfoList.ToList(), CurBOPInfoList.ToList(), ref msg))
{
MessageBox.Show("打印完成。");
}
else
{
if (!string.IsNullOrEmpty(msg))
{
MessageBox.Show(msg);
}
}
}
public bool PrintDebitNote(SheetInfo sheetInfo, List <SheetBOPInfo> sheetBOPList, ref string msg)
{
if (sheetInfo == null)
{
return(false);
}
try
{
if (!xlsAccess.LoadXlsTemplate(ResourceMap.AppFolderPathHashtable[AppFolderPath.Templates] as string + ResourceMap.TemplatesNameHashtable[TemplatesName.DebitNote] as string))
{
return(false);
}
if (xlsAccess.GetSheet((ResourceMap.TemplatesNameHashtable[TemplatesName.DebitNote] as string).Split('.')[0]))
{
xlsAccess.SetCellValue(2, 0, "TO:" + (sheetInfo.Customer == null ? "" : sheetInfo.Customer));
xlsAccess.SetCellValue(2, 3, "DATE:" + DateTime.Now.ToString("yyyy-MM-dd"));
xlsAccess.SetCellValue(4, 0, sheetInfo.BSType == null ? "" : sheetInfo.BSType);
xlsAccess.SetCellValue(4, 1, "RMB");
xlsAccess.SetCellValue(4, 2, sheetInfo.Price);
if (sheetBOPList.Count > 0)
{
List <SheetBOPInfo> tempList = sheetBOPList.Where(info => info.CanRecycle == true).ToList();
if (tempList.Count > 0)
{
xlsAccess.CopyRow(5, 4, tempList.Count);
for (int index = 0; index < tempList.Count; index++)
{
xlsAccess.SetCellValue(index + 5, 0, tempList[index].BOPReason);
xlsAccess.SetCellValue(index + 5, 1, "RMB");
xlsAccess.SetCellValue(index + 5, 2, tempList[index].Payment);
}
xlsAccess.SetCellFormula(5 + tempList.Count, 2, "sum(C5:C" + (5 + tempList.Count) + ")");
}
else
{
xlsAccess.SetCellValue(5, 2, sheetInfo.Price);
}
}
else
{
xlsAccess.SetCellValue(5, 2, sheetInfo.Price);
}
return(SaveWithDialog());
//xlsAccess.SaveAs(ResourceMap.AppFolderPathHashtable[AppFolderPath.Outputs] as string + sheetInfo.Customer + "_DebitNote.xls");
}
else
{
return(false);
}
}
catch (Exception ex)
{
msg = ex.Message + ex.StackTrace.ToString();
return(false);
}
}
private void DeleteBtn_Click(object sender, RoutedEventArgs e)
{
if (SheetDataGrid.SelectedItem == null)
{
return;
}
MessageBoxResult mr = MessageBox.Show("删除该订单后不可恢复，确认删除？", "删除订单", MessageBoxButton.OKCancel, MessageBoxImage.Question);
if (mr == MessageBoxResult.OK)
{
FuncPasswordWindow funcPDWindows = new FuncPasswordWindow(dataManager.CurUserName);
funcPDWindows.Owner = App.Current.MainWindow;
if (funcPDWindows.ShowDialog() == true)
{
SheetInfo info = SheetDataGrid.SelectedItem as SheetInfo;
if (SheetDeal.DeleteSheetInfo(info.ID))
{
int index = dataManager.AllSheetList.ToList().FindIndex(sheet => sheet.ID == info.ID);
if (index > -1)
{
dataManager.AllSheetList.RemoveAt(index);
}
}
}
}
}
List <SheetInfo> GetSpreadSheetIDs(string gDocsUrl)
{
System.Text.RegularExpressions.Match match = Regex.Match(gDocsUrl, @"(/d/)(.+)/");
string gDocskey = match.Groups[2].Value;
string URL = "https://spreadsheets.google.com/spreadsheet/pub?key=" + gDocskey;
List <SheetInfo> res = new List <SheetInfo>();
res = GetSheetIDs(URL, res);
//Also fetch the ID of the default sheet.
if (res.Count > 0)
{
GetSheetIDs(URL + "&gid=" + res[0].ID, res);
}
if (res.Count == 0)
{
Debug.LogWarning("No sheets found, or your spreadsheet has only 1 sheet. We are assuming that the first sheet has ID '0'. (You can fix this by simply adding a second sheet as this will allow ID lookup via HTML output)");
SheetInfo info = new SheetInfo();
info.ID = 0;
info.title = "Sheet1";
res.Add(info);
}
return(res);
}
//Parse sheet IDs from the HTML view.
List <SheetInfo> GetSheetIDs(string URL, List <SheetInfo> sheetList)
{
string output = GetWebpage(URL);
string pattern = @"switchToSheet..([0-9]+)....\s?>(.[^<]+)<";
MatchCollection matches = Regex.Matches(output, pattern);
foreach (System.Text.RegularExpressions.Match mat in matches)
{
int sheetID = int.Parse(mat.Groups[1].Value);
string sheetTitle = mat.Groups[2].Value;
bool present = false;
foreach (SheetInfo info in sheetList)
{
if (info.ID == sheetID)
{
present = true;
break;
}
}
if (!present)
{
SheetInfo inf = new SheetInfo();
inf.ID = sheetID;
inf.title = sheetTitle;
sheetList.Add(inf);
}
}
return(sheetList);
}
/// <summary>
/// 插入EntityInfo数据
/// </summary>
/// <param name="sheetInfo"></param>
/// <param name="KeyMap"></param>
/// <param name="command"></param>
private void InsertInfoTable(SheetInfo sheetInfo, Dictionary <string, int> KeyMap, MySqlTransaction tran, string timeStamp)
{
if (sheetInfo.InfoRows == null || sheetInfo.InfoRows.Count == 0)
{
throw new Exception("无效的数据");
}
List <InfoEntityData> rows = sheetInfo.InfoRows;
string signal = sheetInfo.ExcelTableData.Signal;
foreach (InfoEntityData one in rows)
{
string sql = string.Format("insert into EntityTable(ExcelSignal,EntityName,Version) values ('{0}','{1}','{2}')", signal, one.EntityName, timeStamp);
//插入Entity
new MySqlCommand(sql, tran.Connection, tran).ExecuteNonQuery();
CEntityName entity = selectEntity(one.EntityName, tran.Connection);
Dictionary <string, string> data = one.Data;
StringBuilder buider = null;
if (data != null && data.Count != 0)
{
buider = new StringBuilder("insert into InfoTable(Key_ID,Entity_ID,Value,Version) values");
}
foreach (string s in data.Keys)
{
buider.AppendFormat(" ({0},{1},'{2}','{3}'),", KeyMap[s], entity.Id, data[s], timeStamp);
}
buider.Remove(buider.Length - 1, 1);
new MySqlCommand(buider.ToString(), tran.Connection, tran).ExecuteNonQuery();
}
}
internal async Task <Election> GetElection(SheetInfo settingsSheet, SheetInfo votersSheet, List <SheetInfo> raceSheets, string timezone)
{
var ranges = new List <string>();
ranges.Add($"{settingsSheet.Title}!A:B");
ranges.Add($"{votersSheet.Title}!A:A");
foreach (var race in raceSheets)
{
ranges.Add($"{race.Title}!1:1");
}
var request = _service.Spreadsheets.Values.BatchGet(_spreadsheetId);
request.Ranges = new Repeatable <string>(ranges);
var response = await request.ExecuteAsync().ConfigureAwait(false);
var sheets = response.ValueRanges.ToArray();
var election = new Election
{
Settings = ParseSettings(sheets[0], timezone),
AuthorizedVoters = ParseVoters(sheets[1]),
Races = ParseRaces(raceSheets, sheets.Slice(2))
};
return(election);
}
internal IEnumerator OpenSheetTask(Asset.SubTaskHandle sth)
{
var signal = sth.Signal;
if (CancellableSignal.IsCancelled(signal))
{
yield break;
}
var sheetInfos = new SheetInfo[]
{
//this.CreateSheetInfo<long, GameConfigData >(this.OnOpenSheets_GameConfigData ),
//this.CreateSheetInfo<long, RegionData >(this.OnOpenSheets_RegionData ),
//this.CreateSheetInfo<long, ComposerData >(this.OnOpenSheets_ComposerData ),
//this.CreateSheetInfo<long, MusicData >(this.OnOpenSheets_MusicData ),
//this.CreateSheetInfo<long, BgmData >(this.OnOpenSheets_BgmData ),
//this.CreateSheetInfo<long, MatineeData >(this.OnOpenSheets_MatineeData ),
//this.CreateSheetInfo<long, StarData >(this.OnOpenSheets_StarData ),
//this.CreateSheetInfo<long, JudgementData >(this.OnOpenSheets_JudgementData ),
//this.CreateSheetInfo<long, Concours4KeyData >(this.OnOpenSheets_Concours4KeyData ),
//this.CreateSheetInfo<long, Concours6KeyData >(this.OnOpenSheets_Concours6KeyData ),
//this.CreateSheetInfo<long, PianoData >(this.OnOpenSheets_PianoData ),
this.CreateSheetInfo <long, UnitData>(this.OnOpenSheets_UnitData),
};
var progressStride = 1f / sheetInfos.Length;
var progressStrideHalf = progressStride * 0.5f;
var progress = 0f;
for (int n = 0, cnt = sheetInfos.Length; n < cnt; ++n)
{
var si = sheetInfos[n];
var source = Resources.Load <TextAsset>(string.Format("Data/{0}", si.sheetname));
if (!source)
{
Debug.LogError(string.Format("SERVICEMODEL:SHEET_NOT_FOUND_ERROR:{0}", si.sheetname));
}
sth.SetProgress(progress + progressStrideHalf);
yield return(null);
if (CancellableSignal.IsCancelled(signal))
{
yield break;
}
si.parseFunc(si, source);
sth.SetProgress(progress += progressStride);
yield return(null);
if (CancellableSignal.IsCancelled(signal))
{
yield break;
}
}
sth.Complete();
}
SheetInfo CreateSheetInfo <K, V>(BuildFunc buildFunc, ParseFunc parseFunc = null) where V : AssetData <K>
{
var si = new SheetInfo();
si.sheetname = typeof(V).Name;
si.buildFunc = buildFunc;
si.parseFunc = null != parseFunc ? parseFunc : this.ParseFromCsv <K, V>;
return(si);
}
private IList <CustomEntity> ReadData(string fileName, SheetInfo sheetInfo)
{
IList <CustomEntity> m_CustomEntities = new List <CustomEntity>();
ExcelReaderWriter m_ExcelReaderWriter = new ExcelReaderWriter(fileName, sheetInfo.Index);
try
{
m_ExcelReaderWriter.OpenWorkbook();
object[,] m_Values = (object[, ])m_ExcelReaderWriter.ActiveWorksheet.UsedRange.Value2;
if (m_Values != null)
{
if (sheetInfo.BeginRowIndex == 0)
{
sheetInfo.BeginRowIndex = 0;
}
if (sheetInfo.EndRowIndex == 0)
{
sheetInfo.EndRowIndex = (int)m_Values.GetLongLength(0);
}
if (m_Values != null)
{
for (int m_RowIndex = sheetInfo.BeginRowIndex; m_RowIndex <= sheetInfo.EndRowIndex; m_RowIndex++)
{
CustomEntity m_CustomEntity = new CustomEntity();
//Dynamic value excel
foreach (var m_ColumnInfo in sheetInfo.Columns)
{
if (m_ColumnInfo.Index != 0)
{
//Range a = (Range)m_ExcelReaderWriter.ActiveWorksheet.UsedRange[m_RowIndex, m_ColumnInfo.Index];
//var x = a.Text;
var x = m_Values[m_RowIndex, m_ColumnInfo.Index];
string m_ValueString = string.Format("{0}", x);// m_Values[m_RowIndex, m_ColumnInfo.Index]);
m_CustomEntity.Properties.Add(m_ValueString);
}
}
m_CustomEntities.Add(m_CustomEntity);
}
}
}
}
catch (Exception ex)
{
Debug.WriteLine(ex.Message);
}
finally
{
m_ExcelReaderWriter.CloseWorkbook();
}
return(m_CustomEntities);
}
private void OperationTypeList_SelectionChanged(object sender, SelectionChangedEventArgs e)
{
if (OperationTypeList.SelectedItem == null)
{
return;
}
switch ((OperationType)(((DictionaryEntry)(OperationTypeList.SelectedValue)).Key))
{
case OperationType.Scan:
if (SheetDataGrid.SelectedValue != null)
{
SheetDetailGrid.SetBinding(Grid.DataContextProperty, new Binding("SelectedValue")
{
Source = SheetDataGrid
});
}
UpdateProcefureInfo();
break;
case OperationType.Edit:
sheetInfoForEdit = AlgorithmClass.DeepClone <SheetInfo>(SheetDataGrid.SelectedItem as SheetInfo);
SheetDetailGrid.DataContext = sheetInfoForEdit;
procedureInfoListForEdit = AlgorithmClass.DeepClone <ObservableCollection <ProcedureInfo> >(CurProcedureInfoList);
ProcedureDataGrid.ItemsSource = procedureInfoListForEdit;
bopInfoListForEdit = AlgorithmClass.DeepClone <ObservableCollection <SheetBOPInfo> >(CurBOPInfoList);
BOPDataGrid.ItemsSource = bopInfoListForEdit;
if (procedureInfoListForEdit.Count == CurSelectionProcedureInfoList.Count)
{
AddRowBtn.IsEnabled = false;
}
else
{
AddRowBtn.IsEnabled = true;
}
break;
case OperationType.New:
BSTypeTextBlock.Text = "";
sheetInfoForNew = new SheetInfo();
SheetDetailGrid.DataContext = sheetInfoForNew;
procedureInfoListForNew = new ObservableCollection <ProcedureInfo>();
ProcedureDataGrid.ItemsSource = procedureInfoListForNew;
AddRowBtn.IsEnabled = true;
bopInfoListForNew = new ObservableCollection <SheetBOPInfo>();
BOPDataGrid.ItemsSource = bopInfoListForNew;
break;
}
}
public async Task <ActionResult> GetListJsonStudentCode(string StudentCode)
{
DetailsListParam detailsListParam = new DetailsListParam();
OperatorInfo operatorInfo = await Operator.Instance.Current();
if (!operatorInfo.RoleIds.Contains(GlobalContext.SystemConfig.RoleId))//不是超级管理员
{
detailsListParam.SysDepartmentId = operatorInfo.DepartmentId;
}
detailsListParam.StudentCode = StudentCode;
TData <List <DetailsEntity> > details = await detailsBLL.GetList(detailsListParam);
if (details.Tag == 1 && details.Result.Any())
{
var infos = new List <SheetInfo>();
var ids = details.Result.Select(x => x.ChargeSheetId).Distinct().ToList();
foreach (var id in ids)
{
TData <SheetEntity> obj = await sheetBLL.GetEntity(long.Parse(id.ToString()));
if (obj.Tag == 1 && obj.Result != null)
{
SheetInfo info = obj.Result.MapTo <SheetEntity, SheetInfo>();
var mxs = details.Result.Where(x => x.ChargeSheetId == id).ToList();
info.details = mxs;
info.SumMoney = mxs.Sum(x => x.Money);
info.YesMoney = 0;
//收款记录
RecordListParam recordListParam = new RecordListParam();
if (!operatorInfo.RoleIds.Contains(GlobalContext.SystemConfig.RoleId))//不是超级管理员
{
recordListParam.SysDepartmentId = operatorInfo.DepartmentId;
}
recordListParam.StudentCode = StudentCode;
recordListParam.ChargeSheetId = id;
recordListParam.Status = 1;
TData <List <RecordEntity> > record = await recordBLL.GetList(recordListParam);
if (record.Tag == 1 && record.Result != null)
{
info.YesMoney = record.Result.Sum(x => x.Money);
}
info.NoMoney = info.SumMoney - info.YesMoney;
infos.Add(info);
}
}
return(Json(infos));
}
return(Json(new List <SheetInfo>()));
}
/// <summary>
/// Get WorkSheet By Sheet Info
/// </summary>
/// <param name="excelPackage"></param>
/// <param name="sheetInfo"></param>
/// <returns></returns>
public ExcelWorksheet GetWorkSheet(ExcelPackage excelPackage, SheetInfo sheetInfo)
{
ExcelWorksheet excelWorksheet = null;
if (sheetInfo != null)
{
excelWorksheet = sheetInfo.SheetIndex > 0 ? excelPackage.Workbook.Worksheets[sheetInfo.SheetIndex] : excelPackage.Workbook.Worksheets[sheetInfo.SheetName];
}
return(excelWorksheet);
}
private bool CheckSheetIsValid(SheetInfo sheetInfo)
{
if (string.IsNullOrEmpty(sheetInfo.Accepter) || string.IsNullOrEmpty(sheetInfo.BSType) || string.IsNullOrEmpty(sheetInfo.Contacter) ||
string.IsNullOrEmpty(sheetInfo.Customer) || string.IsNullOrEmpty(sheetInfo.PhoneNumber) || sheetInfo.StartDate == null)
{
return(false);
}
else
{
return(true);
}
}
void SetUIAndGetSheet(string sheetName)
{
data = SourceLoader.GetSheetData(sheetName);
sheet = SourceLoader.GetSheet(data.SheetInfo);
aFx.clip = data.Music;
cover.texture = data.Cover;
queueNotes = new Queue <NoteInfo> (sheet.Notes);
for (int i = 0; i < noteObjectPool.Capacity; i++)
{
SetNote( );
}
}
public void ClassGetSheet()
{
var excel = Excel;
ISheet sheet = excel.GetSheetAt(2);
Assert.AreEqual(5, SheetInfo.GetColumnCount(sheet));
Assert.AreEqual(11, SheetInfo.GetRowCount(sheet));
SheetInfo.TryGetSheetInfo(sheet, opt, out SheetInfo sheetInfo);
var x = ClassSheet.GetOrNull(sheetInfo);
Assert.AreEqual(4, x.Contents.Length);
}
public void ConstGetSheet()
{
var excel = Excel;
ISheet sheet = excel.GetSheetAt(1);
Assert.AreEqual(6, SheetInfo.GetColumnCount(sheet));
Assert.AreEqual(9, SheetInfo.GetRowCount(sheet));
SheetInfo.TryGetSheetInfo(sheet, opt, out SheetInfo sheetInfo);
var x = ConstSheet.GetOrNull(sheetInfo);
Assert.AreEqual(6, x.Contents.Length);
}
List <SheetInfo> GetSpreadSheetIDs(string gDocsUrl)
{
List <SheetInfo> res = new List <SheetInfo>();
if (!gDocsUrl.Contains("output="))
{
//2014 format
string output = GetWebpage(gDocsUrl);
var document = new HtmlAgilityPack.HtmlDocument();
document.LoadHtml(output);
List <string> titles = GetSheetTitles(document);
int i = 0;
foreach (string titl in titles)
{
SheetInfo inf = new SheetInfo();
inf.ID = i;
inf.title = titl;
res.Add(inf);
i++;
}
}
else
{
//2013 format
Match match = Regex.Match(gDocsUrl, "key=(?<gDocsKey>[^&#]+)");
string gDocskey = match.Groups["gDocsKey"].Value;
string URL = "https://spreadsheets.google.com/spreadsheet/pub?key=" + gDocskey;
res = GetSheetIDs(URL, res);
//Also fetch the ID of the default sheet.
if (res.Count > 0)
{
GetSheetIDs(URL + "&gid=" + res[0].ID, res);
}
}
if (res.Count == 0)
{
Debug.LogWarning("No sheets found, or your spreadsheet has only 1 sheet. We are assuming that the first sheet has ID '0'. (You can fix this by simply adding a second sheet as this will allow ID lookup via HTML output)");
SheetInfo info = new SheetInfo();
info.ID = 0;
info.title = "Sheet1";
res.Add(info);
}
return(res);
}
private static List <SheetInfo> GetSheetInfos(ExcelWorksheets excelWorksheets)
{
var sheetInfos = new List <SheetInfo>();
foreach (var excelWorksheet in excelWorksheets)
{
var sheetInfo = new SheetInfo();
sheetInfo.SheetName = excelWorksheet.Name;
sheetInfo.RowCount = excelWorksheet.Dimension?.End.Row ?? 0;
sheetInfo.ColCount = excelWorksheet.Dimension?.End.Column ?? 0;
sheetInfos.Add(sheetInfo);
}
return(sheetInfos);
}
/// <summary>
/// 插入一个SheetInfo，只插入以前表中不存在的数据
/// </summary>
/// <param name="sheetInfo"></param>
/// <returns></returns>
public void InsertSheetInfo(SheetInfo sheetInfo)
{
if (sheetInfo == null)
{
throw new Exception("无效的插入数据");
}
if (sheetInfo.Tag)
{
InsertInfoData(sheetInfo);
}
else
{
InsertDrawData(sheetInfo);
}
}
//GameConfigData OnOpenSheets_GameConfigData(SheetInfo si, object data)
//{
// var row = (CsvRow)data;
// var assetModel = new GameConfigData();
// var counter = 0;
// counter = this.OnOpenSheets_IAssetable(row, assetModel, long.Parse, counter);
// ++counter;// NOTE: Comment
// assetModel.integerValue = this.ParseNullable(row[counter++], int.Parse);
// assetModel.dobuleValue = this.ParseNullable(row[counter++], double.Parse);
// assetModel.stringValue = row[counter++];
// if ("" == assetModel.stringValue)
// assetModel.stringValue = null;
// return assetModel;
//}
//RegionData OnOpenSheets_RegionData(SheetInfo si, object data)
//{
// var row = (CsvRow)data;
// var assetModel = new RegionData();
// var counter = 0;
// counter = this.OnOpenSheets_IAssetable(row, assetModel, long.Parse, counter);
// assetModel.name = row[counter++];
// return assetModel;
//}
//ComposerData OnOpenSheets_ComposerData(SheetInfo si, object data)
//{
// var row = (CsvRow)data;
// var assetModel = new ComposerData();
// var counter = 0;
// counter = this.OnOpenSheets_IAssetable(row, assetModel, long.Parse, counter);
// assetModel.name = row[counter++];
// assetModel.era = short.Parse(row[counter++]);
// assetModel.order = short.Parse(row[counter++]);
// assetModel.saveSlot = byte.Parse(row[counter++]);
// return assetModel;
//}
//MusicData OnOpenSheets_MusicData(SheetInfo si, object data)
//{
// var row = (CsvRow)data;
// var assetModel = new MusicData();
// var counter = 0;
// counter = this.OnOpenSheets_IAssetable(row, assetModel, long.Parse, counter);
// assetModel.title = row[counter++];
// assetModel.composer = long.Parse(row[counter++]);
// assetModel.diff4k1 = byte.Parse(row[counter++]);
// assetModel.diff4k2 = byte.Parse(row[counter++]);
// assetModel.diff4k3 = byte.Parse(row[counter++]);
// assetModel.diff6k1 = byte.Parse(row[counter++]);
// assetModel.diff6k2 = byte.Parse(row[counter++]);
// assetModel.diff6k3 = byte.Parse(row[counter++]);
// assetModel.diff2p1 = byte.Parse(row[counter++]);
// assetModel.diff2p2 = byte.Parse(row[counter++]);
// assetModel.diff2p3 = byte.Parse(row[counter++]);
// assetModel.ensembleOrder = int.Parse(row[counter++]);
// assetModel.hidden = "1" == row[counter++];
// return assetModel;
//}
//BgmData OnOpenSheets_BgmData(SheetInfo si, object data)
//{
// var row = (CsvRow)data;
// var assetModel = new BgmData();
// var counter = 0;
// counter = this.OnOpenSheets_IAssetable(row, assetModel, long.Parse, counter);
// assetModel.bgmStart = float.Parse(row[counter++]);
// assetModel.bgmEnd = float.Parse(row[counter++]);
// assetModel.bgmFI = float.Parse(row[counter++]);
// assetModel.bgmFO = float.Parse(row[counter++]);
// assetModel.bgmNextTerm = float.Parse(row[counter++]);
// assetModel.previewStart = float.Parse(row[counter++]);
// assetModel.previewEnd = float.Parse(row[counter++]);
// assetModel.previewFI = float.Parse(row[counter++]);
// assetModel.previewFO = float.Parse(row[counter++]);
// assetModel.previewNextTerm = float.Parse(row[counter++]);
// assetModel.outgame = "1" == row[counter++];
// return assetModel;
//}
//MatineeData OnOpenSheets_MatineeData(SheetInfo si, object data)
//{
// var row = (CsvRow)data;
// var assetModel = new MatineeData();
// var counter = 0;
// counter = this.OnOpenSheets_IAssetable(row, assetModel, long.Parse, counter);
// assetModel.stage4k1 = short.Parse(row[counter++]);
// assetModel.order4k1 = short.Parse(row[counter++]);
// assetModel.stage4k2 = short.Parse(row[counter++]);
// assetModel.order4k2 = short.Parse(row[counter++]);
// assetModel.stage4k3 = short.Parse(row[counter++]);
// assetModel.order4k3 = short.Parse(row[counter++]);
// assetModel.stage6k1 = short.Parse(row[counter++]);
// assetModel.order6k1 = short.Parse(row[counter++]);
// assetModel.stage6k2 = short.Parse(row[counter++]);
// assetModel.order6k2 = short.Parse(row[counter++]);
// assetModel.stage6k3 = short.Parse(row[counter++]);
// assetModel.order6k3 = short.Parse(row[counter++]);
// return assetModel;
//}
//StarData OnOpenSheets_StarData(SheetInfo si, object data)
//{
// var row = (CsvRow)data;
// var assetModel = new StarData();
// var counter = 0;
// counter = this.OnOpenSheets_IAssetable(row, assetModel, long.Parse, counter);
// assetModel.class1 = double.Parse(row[counter++]);
// assetModel.class2 = double.Parse(row[counter++]);
// assetModel.class3 = double.Parse(row[counter++]);
// return assetModel;
//}
//JudgementData OnOpenSheets_JudgementData(SheetInfo si, object data)
//{
// var row = (CsvRow)data;
// var assetModel = new JudgementData();
// var counter = 0;
// counter = this.OnOpenSheets_IAssetable(row, assetModel, long.Parse, counter);
// assetModel.tier = (JudgementType)int.Parse(row[counter++]);
// assetModel.groupKey = row[counter++];
// assetModel.ratio = double.Parse(row[counter++]);
// assetModel.life = double.Parse(row[counter++]);
// assetModel.score = int.Parse(row[counter++]);
// assetModel.difficultBonus = int.Parse(row[counter++]);
// assetModel.noteF = double.Parse(row[counter++]);
// assetModel.noteR = double.Parse(row[counter++]);
// assetModel.longF = double.Parse(row[counter++]);
// assetModel.longR = double.Parse(row[counter++]);
// return assetModel;
//}
//int OnOpenSheets_ConcoursData(CsvRow row, ConcoursData assetModel, int counter)
//{
// counter = this.OnOpenSheets_IAssetable(row, assetModel, long.Parse, counter);
// assetModel.name = row[counter++];
// assetModel.music1 = long.Parse(row[counter++]);
// assetModel.difficultType1 = (DifficultType)int.Parse(row[counter++]);
// assetModel.hidden1 = "1" == row[counter++];
// assetModel.music2 = long.Parse(row[counter++]);
// assetModel.difficultType2 = (DifficultType)int.Parse(row[counter++]);
// assetModel.hidden2 = "1" == row[counter++];
// assetModel.music3 = long.Parse(row[counter++]);
// assetModel.difficultType3 = (DifficultType)int.Parse(row[counter++]);
// assetModel.hidden3 = "1" == row[counter++];
// assetModel.music4 = long.Parse(row[counter++]);
// assetModel.difficultType4 = (DifficultType)int.Parse(row[counter++]);
// assetModel.hidden4 = "1" == row[counter++];
// assetModel.judgeGroupKey = row[counter++];
// assetModel.missionActive = (ActiveMissionType)int.Parse(row[counter++]);
// assetModel.missionValue = uint.Parse(row[counter++]);
// assetModel.missionMiss = byte.Parse(row[counter++]);
// assetModel.missionSpeed = byte.Parse(row[counter++]);
// assetModel.missionFade = (NoteVisibleType)int.Parse(row[counter++]);
// assetModel.missionSequence = (SequenceType)int.Parse(row[counter++]);
// {
// var valueL = 0L;
// if (long.TryParse(row[counter++], out valueL))
// assetModel.rewardPiano = valueL;
// else
// assetModel.rewardPiano = null;
// }
// {
// var valueL = 0L;
// if (long.TryParse(row[counter++], out valueL))
// assetModel.rewardMusic = valueL;
// else
// assetModel.rewardMusic = null;
// }
// return counter;
//}
//Concours4KeyData OnOpenSheets_Concours4KeyData(SheetInfo si, object data)
//{
// var row = (CsvRow)data;
// var assetModel = new Concours4KeyData();
// var counter = 0;
// counter = this.OnOpenSheets_ConcoursData(row, assetModel, counter);
// return assetModel;
//}
//Concours6KeyData OnOpenSheets_Concours6KeyData(SheetInfo si, object data)
//{
// var row = (CsvRow)data;
// var assetModel = new Concours6KeyData();
// var counter = 0;
// counter = this.OnOpenSheets_ConcoursData(row, assetModel, counter);
// return assetModel;
//}
//PianoData OnOpenSheets_PianoData(SheetInfo si, object data)
//{
// var row = (CsvRow)data;
// var assetModel = new PianoData();
// var counter = 0;
// counter = this.OnOpenSheets_IAssetable(row, assetModel, long.Parse, counter);
// assetModel.name = row[counter++];
// assetModel.keyColorCode = row[counter++];
// assetModel.access = int.Parse(row[counter++]);
// assetModel.order = int.Parse(row[counter++]);
// return assetModel;
//}
UnitData OnOpenSheets_UnitData(SheetInfo si, object data)
{
var row = (CsvRow)data;
var assetModel = new UnitData();
var counter = 0;
counter = this.OnOpenSheets_IAssetable(row, assetModel, long.Parse, counter);
assetModel.name = row[counter++];
assetModel.hp = long.Parse(row[counter++]);
assetModel.damage = long.Parse(row[counter++]);
assetModel.moveSpeed = long.Parse(row[counter++]);
assetModel.range = long.Parse(row[counter++]);
assetModel.attackSpeed = float.Parse(row[counter++]);
assetModel.knockbackPower = long.Parse(row[counter++]);
return(assetModel);
}
/// <summary>
/// 更新Sheet信息
/// </summary>
public static int UpdateSheetInfo(SheetInfo sheetInfo)
{
ISqlMapper mapper = GetSqlMapper.GetMapper();
int result = 0;
try
{
mapper.BeginTransaction();
result = mapper.Update("UpdateSheetInfo", sheetInfo);
mapper.CommitTransaction();
}
catch (Exception ex)
{
applog.InfoFormat("Access.UpdateSheetInfo() Error:{0}", ex.Message);
mapper.RollBackTransaction();
}
return(result);
}
/// <summary>
/// 插入Sheet信息
/// </summary>
/// <returns>最新插入插入的ID</returns>
public static int InsertSheet(SheetInfo sheetInfo)
{
ISqlMapper mapper = GetSqlMapper.GetMapper();
int id = 0;
try
{
mapper.BeginTransaction();
id = (int)(mapper.Insert("InsertSheet", sheetInfo));
mapper.CommitTransaction();
}
catch (Exception ex)
{
applog.InfoFormat("Access.InsertSheet() Error:{0}", ex.Message);
mapper.RollBackTransaction();
}
return(id);
}
/// <summary>
/// 插入DrawDataTable
/// </summary>
/// <param name="sheetInfo"></param>
/// <param name="command"></param>
private void InsertDrawDataTable(SheetInfo sheetInfo, MySqlTransaction tran, string timeStamp)
{
List <DrawEntityData> rows = sheetInfo.DrawRows;
string signal = sheetInfo.ExcelTableData.Signal;
foreach (DrawEntityData one in rows)
{
CEntityName entity = selectEntity(one.EntityName, tran.Connection);
DateTime? maxDate = null;
if (entity == null)
{
string sql = string.Format("insert into EntityTable(ExcelSignal,EntityName,Version) values ('{0}','{1}','{2}')", signal, one.EntityName, timeStamp);
//插入Entity
new MySqlCommand(sql, tran.Connection, tran).ExecuteNonQuery();
entity = selectEntity(one.EntityName, tran.Connection);
}
else
{
string sql = string.Format("select Max(Date) from DrawTable where Entity_ID={0}", entity.Id);
maxDate = Convert.ToDateTime(new MySqlCommand(sql, tran.Connection).ExecuteScalar());
}
List <DrawData> data = one.Data;
StringBuilder buider = null;
if (data != null && data.Count != 0)
{
buider = new StringBuilder("insert into DrawTable(Entity_ID,Date,EntityMaxValue,EntityMidValue,EntityMinValue,Detail,Version) values");
}
bool hasValue = false;
foreach (DrawData p in data)
{
if (maxDate == null || p.Date > maxDate)
{
hasValue = true;
buider.AppendFormat(" ({0},'{1}','{2}','{3}','{4}','{5}','{6}'),", entity.Id, p.Date, p.MaxValue, p.MidValue, p.MinValue, p.Detail, timeStamp);
}
}
if (hasValue)
{
buider.Remove(buider.Length - 1, 1);
new MySqlCommand(buider.ToString(), tran.Connection, tran).ExecuteNonQuery();
}
}
}
void ParseFromCsv <K, V>(SheetInfo si, TextAsset source) where V : AssetData <K>
{
using (var reader = new CsvFileReader(new MemoryStream(source.bytes)))
{
// NOTE: HEADER
reader.ReadLine();
var list = new List <V>(512);
var row = new CsvRow();
while (reader.ReadRow(row))
{
var elem = (V)si.buildFunc(si, row);
list.Add(elem);
}
#if LOG_ASSET
Debug.Log(string.Format("SERVICEMODEL:SHEET:{0}:{1}", si.sheetname, ConvertString.Execute(list)));
#endif// LOG_ASSET
MVC.Model <K, V> .Open(ServiceModelManager.GetAssetCode <K, V>, list);
}
}
List<SheetInfo> GetSpreadSheetIDs(string gDocsUrl)
{
Match match = Regex.Match(gDocsUrl, "key=(?<gDocsKey>[^&#]+)");
string gDocskey = match.Groups["gDocsKey"].Value;
string URL = "https://spreadsheets.google.com/spreadsheet/pub?key=" + gDocskey;
List<SheetInfo> res = new List<SheetInfo>();
res = GetSheetIDs(URL, res);
//Also fetch the ID of the default sheet.
if (res.Count > 0)
GetSheetIDs(URL + "&gid=" + res[0].ID, res);
if (res.Count == 0)
{
Debug.LogWarning("No sheets found, or your spreadsheet has only 1 sheet. We are assuming that the first sheet has ID '0'. (You can fix this by simply adding a second sheet as this will allow ID lookup via HTML output)");
SheetInfo info = new SheetInfo();
info.ID = 0;
info.title ="Sheet1";
res.Add(info);
}
return res;
}
//Parse sheet IDs from the HTML view.
List<SheetInfo> GetSheetIDs(string URL, List<SheetInfo> sheetList)
{
string output = GetWebpage(URL);
MatchCollection matches = Regex.Matches(output, ";gid=(?<sheetID>[0-9]+)\">(?<sheetTitle>[^<]+)</");
foreach (Match mat in matches)
{
int sheetID = int.Parse(mat.Groups["sheetID"].Value);
string sheetTitle = mat.Groups["sheetTitle"].Value;
bool present = false;
foreach (SheetInfo info in sheetList)
{
if (info.ID == sheetID)
{
present = true;
break;
}
}
if (!present)
{
SheetInfo inf = new SheetInfo();
inf.ID = sheetID;
inf.title = sheetTitle;
sheetList.Add(inf);
}
}
return sheetList;
}