// 输出
private void output(List <PoiInfo> poiList)
{
foreach (PoiInfo poi in poiList)
{
getNum++;
// 输出到 Excel
if (sheet != null)
{
XlsUtil.AddBaiduContent(sheet, poi, getNum);
}
// 输出到 DataGridView
if (dataGridView != null)
{
DataGridViewRow row = new DataGridViewRow();
int index = dataGridView.Rows.Add(row);
// 编号
dataGridView.Rows[index].Cells[0].Value = getNum;
// 标题
dataGridView.Rows[index].Cells[1].Value = poi.Name;
// 坐标
dataGridView.Rows[index].Cells[2].Value = string.Format("{0}, {1}", poi.Lng, poi.Lat);
// 地址
dataGridView.Rows[index].Cells[3].Value = poi.Address;
// 定位到当前行，以触发刷新事件
dataGridView.CurrentCell = dataGridView.Rows[index].Cells[0];
}
}
}
public static XlsInput Load(string xlsFilePath)
{
if (!File.Exists(xlsFilePath))
{
throw new FileNotFoundException();
}
List <XlsWorksheetInfo> worksheets = new();
XlsUtil.WithWorkbook(xlsFilePath, workbook => {
var sheets = workbook.ReadWorksheetNames();
var numSheets = sheets.Count;
if (numSheets == 0)
{
throw new Exception("This workbook does not contain any sheets.");
}
for (var i = 0; i < numSheets; i++)
{
worksheets.Add(XlsWorksheetInfo.Load(workbook, i));
}
});
return(new() {
FilePath = xlsFilePath,
Worksheets = worksheets
});
}
public override object ConvertTo(ITypeDescriptorContext context, CultureInfo culture, object value, Type destinationType)
{
var columnIndex = XlsUtil.ColumnRefToIndex(value); // zero-based
return(columnIndex.HasValue && columnIndex >= 0 && columnIndex < 16384
? XlsUtil.GetColumnRefString(value)
: "");
}
public async Task LoadColumns()
{
await _columnsLoadControl.DoLoad(async() => {
var sheetIndex = _sheetMeta.Index;
var range = new ColumnHeaderRange(_sheetOptions);
var columnNames = await Task.Run(() => XlsUtil.ReadColumnNames(
_filePath, sheetIndex, range.FirstRowNumber, range.FirstColumnLetter, range.LastColumnLetter,
range.HeaderRow));
_columnsControl.SetSourceColumns(columnNames);
_columnsControl.SetTargetToNewTable();
});
}
public override IEnumerable <object[]> Execute(object[] hiddenValues)
{
var filePath = ArgUtil.GetStrArg(hiddenValues[0], "file-path", Name);
if (!File.Exists(filePath))
{
throw new Exception($"{Name.ToUpper()}: File not found.");
}
try {
return(XlsUtil.ReadWorksheetNames(filePath).Select((name, i) => new object[] {
filePath, i + 1, name
}).ToList());
} catch (Exception ex) {
throw new Exception($"{Name.ToUpper()}: {ex.GetExceptionMessage()}");
}
}
private void Import()
{
XlsUtil.WithWorkbook(_filePath, workbook => {
var sheet = workbook.GetSheetAt(GetSheetIndex(workbook));
var rows = XlsUtil.ReadSheet(sheet, _firstRowIndex, _lastRowIndex, _firstColumnIndex, _lastColumnIndex);
SqlUtil.Import(
srcColNames: XlsUtil.ReadColumnNames(rows, _headerRow),
dataRows: rows.Skip(_headerRow ? 1 : 0),
importTable: _stmt.ImportTable,
temporaryTable: _temporaryTable,
truncateExistingTable: _truncateExistingTable,
ifConversionFails: _ifConversionFails,
notebook: _notebook,
runner: _runner,
env: _env);
});
}
private void Import()
{
XlsUtil.WithWorkbook(_filePath, workbook => {
workbook.SeekToWorksheet(GetSheetIndex(workbook));
var rows = workbook.ReadSheet(_firstRowIndex, _lastRowIndex, _firstColumnIndex, _lastColumnIndex);
SqlUtil.Import(
srcColNames: XlsUtil.ReadColumnNames(rows, _headerRow),
dataRows: rows.Skip(_headerRow ? 1 : 0),
importTable: _stmt.ImportTable,
temporaryTable: _temporaryTable,
truncateExistingTable: _truncateExistingTable,
stopAtFirstBlankRow: _stopAtFirstBlankRow,
ifConversionFails: _ifConversionFails,
blankValuesMethod: _blankValuesMethod,
notebook: _notebook,
runner: _runner,
env: _env);
});
}
private async void SetCellRangeLnk_LinkClicked(object sender, LinkLabelLinkClickedEventArgs e)
{
var range = _grid.CurrentWorksheet.SelectionRange;
if (range.IsEmpty)
{
return;
}
_setCellRangeLnk.Enabled = false;
_sheetOptions.FirstRowNumber = range.Row + 1;
_sheetOptions.LastRowNumber = range.EndRow + 1;
if (_sheetOptions.LastRowNumber == MAX_SHEET_ROWS)
{
_sheetOptions.LastRowNumber = null;
}
_sheetOptions.FirstColumnLetter = XlsUtil.GetColumnRefString(range.Col + 1);
_sheetOptions.LastColumnLetter = XlsUtil.GetColumnRefString(range.EndCol + 1);
_propGrid.Refresh();
await LoadColumns();
_setCellRangeLnk.Enabled = true;
}
public Dictionary <string, string> GetSkillData(string skillId)
{
if (!(skillSheet != null && boxesSheet != null && skillId != null && skillId != ""))
{
return(null);
}
string trimmedSid = skillId.Trim();
IRow skillRow = XlsUtil.findRow(skillSheet, trimmedSid);
if (skillRow == null)
{
return(null);
}
IRow skillRefRow = skillSheet.GetRow(2);
IRow boxesRefRow = boxesSheet.GetRow(2);
Dictionary <string, string> skillData = XlsUtil.getValueDict(skillRow, skillRefRow);
return(skillData);
}
// 点击开始按钮
private void buttonStart_Click(object sender, EventArgs e)
{
// 初始化
dataGridView1.Rows.Clear();
toolStripStatusLabelResult.Text = "";
// 关键字
string keyWord = getKeyWord();
if (string.IsNullOrEmpty(keyWord))
{
MessageBox.Show("请输入自定义关键字");
return;
}
// excel导出路径
IWorkbook workbook = null;
ISheet sheet = null;
if (checkBoxExport.Checked)
{
if (string.IsNullOrEmpty(textBoxSave.Text.Trim()))
{
MessageBox.Show("请选择数据导出路径");
return;
}
// 创建 excel
workbook = new XSSFWorkbook();
sheet = workbook.CreateSheet("poi");
XlsUtil.AddBaiduHeader(sheet);
}
// 开始请求
toolStripStatusLabelStatus.Text = "请求开始……";
// 获取总数
handler = new PlaceAPIHandler(comboBoxCity.SelectedItem as Region, keyWord, dataGridView1, sheet);
int totalNum = handler.GetTotalNum((comboBoxProvince.SelectedItem as Region).Name);
if (totalNum < 0) // 返回错误
{
string errMsg = PlaceAPIHandler.GetErrMsg(totalNum);
MessageBox.Show(errMsg);
toolStripStatusLabelStatus.Text = "就绪";
toolStripStatusLabelResult.Text = errMsg;
return;
}
else if (totalNum == 0)
{
string msg = "没有搜索结果";
MessageBox.Show(msg);
toolStripStatusLabelStatus.Text = "就绪";
toolStripStatusLabelResult.Text = msg;
return;
}
else // 有结果，开始请求
{
int rowNum = 1;
int colNum = 1;
if (totalNum > 400 && checkBoxBlock.Checked) // 分块获取
{
rowNum = Convert.ToInt32(comboBoxBlockRow.SelectedItem);
colNum = Convert.ToInt32(comboBoxBlockCol.SelectedItem);
if (rowNum * colNum > 150) // 可能超过配额
{
if (MessageBox.Show("请求可能超过配额，是否继续？", "", MessageBoxButtons.OKCancel)
== DialogResult.Cancel) // 取消请求
{
toolStripStatusLabelStatus.Text = "就绪";
return;
}
}
}
// 请求
requestData(rowNum, colNum);
// 保存 excel
if (workbook != null)
{
FileStream fs = File.Create(textBoxSave.Text);
workbook.Write(fs);
workbook.Close();
fs.Close();
}
}
}
private ImportXlsStmtRunner(INotebook notebook, ScriptEnv env, ScriptRunner runner, Ast.ImportXlsStmt stmt)
{
_notebook = notebook;
_env = env;
_runner = runner;
_stmt = stmt;
_filePath = _runner.EvaluateExpr <string>(_stmt.FilenameExpr, _env);
if (!File.Exists(_filePath))
{
throw new Exception($"The specified XLS/XLSX file was not found: \"{_filePath}\"");
}
if (_stmt.WhichSheetExpr != null)
{
_whichSheet = _runner.EvaluateExpr(_stmt.WhichSheetExpr, _env);
}
int?index;
foreach (var option in _stmt.OptionsList.GetOptionKeys())
{
switch (option)
{
case "FIRST_ROW":
_firstRowIndex = _stmt.OptionsList.GetOptionInt(option, _runner, _env, 1, minValue: 1) - 1;
break;
case "LAST_ROW":
var lastRowNum = _stmt.OptionsList.GetOptionInt(option, _runner, _env, 0, minValue: 0);
if (lastRowNum == 0)
{
_lastRowIndex = null;
}
else
{
_lastRowIndex = lastRowNum - 1;
}
break;
case "FIRST_COLUMN":
index = XlsUtil.ColumnRefToIndex(_stmt.OptionsList.GetOption <object>(option, _runner, _env, null));
if (index.HasValue)
{
_firstColumnIndex = index.Value;
}
else
{
throw new Exception($"The {option} option must be a valid column number or string.");
}
break;
case "LAST_COLUMN":
var lastColumnValue = _stmt.OptionsList.GetOption <object>(option, _runner, _env, null);
if (lastColumnValue.Equals(0))
{
_lastColumnIndex = null;
break;
}
index = XlsUtil.ColumnRefToIndex(lastColumnValue);
if (index.HasValue)
{
_lastColumnIndex = index.Value;
}
else
{
throw new Exception($"The {option} option must be a valid column number or string.");
}
break;
case "HEADER_ROW":
_headerRow = _stmt.OptionsList.GetOptionBool(option, _runner, _env, true);
break;
case "TRUNCATE_EXISTING_TABLE":
_truncateExistingTable = _stmt.OptionsList.GetOptionBool(option, _runner, _env, false);
break;
case "TEMPORARY_TABLE":
_temporaryTable = _stmt.OptionsList.GetOptionBool(option, _runner, _env, false);
break;
case "IF_CONVERSION_FAILS":
_ifConversionFails = (IfConversionFails)_stmt.OptionsList.GetOptionLong(
option, _runner, _env, 1, minValue: 1, maxValue: 3);
break;
default:
throw new Exception($"\"{option}\" is not a recognized option name.");
}
}
}
public async Task LoadGrid()
{
await _gridLoadControl.DoLoad(async() => {
var data = await Task.Run(() =>
XlsUtil.ReadWorksheet(_filePath, _sheetMeta.Index, lastRowIndex: MAX_SHEET_ROWS - 1));
// user might have closed the window while we were loading
if (_grid.IsDisposed)
{
return;
}
// reogrid doesn't seem to properly support newlines in quoted CSV strings but we're just using it for
// preview purposes, so we'll just remove newlines. it's fine here. it's important that reogrid's
// row numbers match what we think the row numbers are
foreach (var row in data)
{
for (var j = 0; j < row.Length; j++)
{
if (row[j] is string s && s.Contains('\n'))
{
row[j] = s.Replace("\r", "").Replace("\n", " ");
}
}
}
var columnCount = Math.Max(1, data.Max(x => x.Length));
// the fastest way to get the data into the grid control seems to be via CSV
using (var memoryStream = new MemoryStream()) {
await Task.Run(() => {
var streamWriter = new StreamWriter(
memoryStream, Encoding.Default, STREAM_BUFFER_SIZE, leaveOpen: true);
using (streamWriter) {
CsvUtil.WriteCsv(data, streamWriter);
}
memoryStream.Position = 0;
});
// user might have closed the window while we were loading
if (_grid.IsDisposed)
{
return;
}
_grid.Load(memoryStream, FileFormat.CSV);
}
var w = _grid.CurrentWorksheet;
w.SetRows(data.Count);
w.SetCols(columnCount);
for (var i = 0; i < columnCount; i++)
{
w.AutoFitColumnWidth(i);
var width = Math.Min(MAX_SHEET_COLUMN_WIDTH, w.GetColumnWidth(i) + 5);
w.SetColumnsWidth(i, 1, (ushort)width);
}
w.SetSettings(WorksheetSettings.Edit_Readonly, true);
_setCellRangeLnk.Enabled = true;
});
}
public void TryShowSkill(string skillId)
{
//var rigidBody = gameObject.GetComponent<Rigidbody> ();
//rigidBody.velocity = testVelocity;
if (!(skillSheet != null && boxesSheet != null && skillId != null && skillId != ""))
{
return;
}
string trimmedSid = skillId.Trim();
IRow skillRow = XlsUtil.findRow(skillSheet, trimmedSid);
if (skillRow == null)
{
return;
}
IRow skillRefRow = skillSheet.GetRow(2);
IRow boxesRefRow = boxesSheet.GetRow(2);
Dictionary <string, string> skillData = XlsUtil.getValueDict(skillRow, skillRefRow);
List <string> boxIdList = new List <string> ();
for (int i = 1; i <= 10; ++i)
{
string key = "box" + i.ToString();
if (skillData.ContainsKey(key))
{
boxIdList.Add(skillData [key]);
}
}
List <Dictionary <string, string> > boxDataDict = new List <Dictionary <string, string> >();
foreach (string boxId in boxIdList)
{
IRow boxRow = XlsUtil.findRow(boxesSheet, boxId);
if (boxRow != null)
{
Dictionary <string, string> boxData = XlsUtil.getValueDict(boxRow, boxesRefRow);
boxDataDict.Add(boxData);
}
}
// clear colliders for simulate hit pause
clearColliders();
foreach (var boxData in boxDataDict)
{
if (boxData.ContainsKey("shake_tm"))
{
delayList.Add(Single.Parse(boxData["shake_tm"]) / 24);
}
else
{
delayList.Add(0.0f);
}
}
// play sounds
List <KeyValuePair <string, int> > sounds = new List <KeyValuePair <string, int> >();
if (skillData.ContainsKey("sound1"))
{
string sounds_str = skillData ["sound1"];
string frames_str = "";
if (skillData.ContainsKey("sound_frame1"))
{
frames_str = skillData ["sound_frame1"];
}
string[] all_sounds = sounds_str.Split(',');
string[] all_frames = frames_str.Split(',');
for (int i = 0; i < all_sounds.Length; ++i)
{
string sound = all_sounds[i].Trim();
int frame = 0;
if (all_frames.Length > i)
{
frame = Convert.ToInt32(all_frames [i]);
}
sounds.Add(new KeyValuePair <string, int>(sound, frame));
}
}
if (gtc != null)
{
gtc.destroy();
gtc = null;
}
if (audioGtc != null)
{
audioGtc.destroy();
audioGtc = null;
}
_sounds = sounds;
_skillData = skillData;
_boxDataDict = boxDataDict;
var config = LBootEditor.GameConfig.Get();
animLen = config.getClipLength(this.animator.runtimeAnimatorController.name, skillData["anim_type"]);
// StartCoroutine ("loadSounds");
loadSounds();
}
public void TryShowSkill()
{
//var rigidBody = gameObject.GetComponent<Rigidbody> ();
//rigidBody.velocity = testVelocity;
if (!(skillWorkBook != null && boxesWorkBook != null && skillId != null && skillId != ""))
{
return;
}
ISheet skillSheet = skillWorkBook.GetSheetAt(1);
ISheet boxesSheet = boxesWorkBook.GetSheetAt(1);
string trimmedSid = skillId.Trim();
IRow skillRow = XlsUtil.findRow(skillSheet, trimmedSid);
if (skillRow == null)
{
return;
}
IRow skillRefRow = skillSheet.GetRow(2);
IRow boxesRefRow = boxesSheet.GetRow(2);
Dictionary <string, string> skillData = XlsUtil.getValueDict(skillRow, skillRefRow);
List <string> boxIdList = new List <string> ();
for (int i = 1; i <= 10; ++i)
{
string key = "box" + i.ToString();
if (skillData.ContainsKey(key))
{
boxIdList.Add(skillData [key]);
}
}
List <Dictionary <string, string> > boxDataDict = new List <Dictionary <string, string> >();
foreach (string boxId in boxIdList)
{
IRow boxRow = XlsUtil.findRow(boxesSheet, boxId);
if (boxRow != null)
{
Dictionary <string, string> boxData = XlsUtil.getValueDict(boxRow, boxesRefRow);
boxDataDict.Add(boxData);
}
}
// clear colliders for simulate hit pause
clearColliders();
foreach (var boxData in boxDataDict)
{
if (boxData.ContainsKey("shake_tm"))
{
delayList.Add(Single.Parse(boxData["shake_tm"]) / 24);
}
else
{
delayList.Add(0.0f);
}
}
if (gtc != null)
{
gtc.destroy();
gtc = null;
}
this.time = 0;
animTimeVal = 0;
preATV = 0;
preState = skillData["anim_type"];
gameObject.transform.position = Vector3.zero;
this.animator.Play(preState, 0, 0);
this.animator.enabled = true;
this.animator.speed = 1.0f;
DestroyEfx();
if (skillData.ContainsKey("self_eff"))
{
effect_nf = MakeEfx(skillData["self_eff"]);
}
if (skillData.ContainsKey("self_eff_follow"))
{
effect_f = MakeEfx(skillData["self_eff_follow"]);
}
if (effect_f != null)
{
effect_f.transform.SetParent(gameObject.transform, true);
effect_f.name = skillData["self_eff_follow"] + "efx";
}
if (effect_nf != null)
{
effect_nf.transform.position = gameObject.transform.position;
effect_nf.name = skillData["self_eff"] + "efx";
}
if (skillData["is_jump_skill"] == "是")
{
applyJumpSkillMove(boxDataDict);
}
else
{
applyNonJumpSkillMove(boxDataDict);
}
}
public override object ConvertFrom(ITypeDescriptorContext context, CultureInfo culture, object value)
{
var str = XlsUtil.GetColumnRefString(value);
return((str?.Any() ?? false) ? str : null);
}
public static XlsWorksheetInfo Load(XlsUtil.IWorkbook workbook, int worksheetIndex)
{
workbook.SeekToWorksheet(worksheetIndex);
var fullCount = workbook.GetRowCount();
workbook.SeekToWorksheet(worksheetIndex);
var data = workbook.ReadSheet(maxRows: 1000);
DataTable dataTable = new();
int numColumns;
if (data.Count > 0)
{
numColumns = data.Max(x => x.Length);
for (var columnIndex = 0; columnIndex < numColumns; columnIndex++)
{
var columnLetter = XlsUtil.ConvertNumToColString(columnIndex);
dataTable.Columns.Add(columnLetter);
}
}
else
{
numColumns = 1;
dataTable.Columns.Add("A");
}
dataTable.BeginLoadData();
foreach (var row in data)
{
// Format numbers.
for (var i = 0; i < row.Length; i++)
{
if (row[i] is int intValue)
{
row[i] = $"{intValue:#,##0}";
}
else if (row[i] is long longValue)
{
row[i] = $"{longValue:#,##0}";
}
else if (row[i] is double doubleValue)
{
row[i] = $"{doubleValue:#,##0.####}";
}
}
if (row.Length == numColumns)
{
dataTable.LoadDataRow(row, true);
}
else
{
var extendedRow = new object[numColumns];
Array.Copy(row, extendedRow, row.Length);
dataTable.LoadDataRow(extendedRow, true);
}
}
dataTable.EndLoadData();
return(new() {
Index = worksheetIndex,
Name = workbook.GetSheetName(worksheetIndex),
DataTable = dataTable,
FullCount = fullCount,
});
}