public async Task DoConnect(string strSheetID, delOnFinishConnect OnFinishConnect, string strCredentialFilePath = "credentials.json")
{
List <SheetWrapper> listSheet = new List <SheetWrapper>();
_pService = null;
_eConnectedSheetType = ESpreadSheetType.GoogleSpreadSheet;
_strSheetID = strSheetID;
UserCredential credential;
Exception pException_OnError = null;
try
{
using (var stream =
new FileStream(strCredentialFilePath, FileMode.Open, FileAccess.Read))
{
string credPath = "token.json";
credential = GoogleWebAuthorizationBroker.AuthorizeAsync(
GoogleClientSecrets.Load(stream).Secrets,
Scopes,
"user",
_pTokenSource.Token,
new FileDataStore(credPath, true)).Result;
}
_pService = new SheetsService(new BaseClientService.Initializer()
{
HttpClientInitializer = credential,
ApplicationName = ApplicationName,
});
}
catch (Exception pException)
{
OnFinishConnect(strSheetID, "", _eConnectedSheetType, listSheet, pException);
return;
}
strFileName = "";
var pRequest_HandShake = _pService.Spreadsheets.Get(strSheetID);
try
{
var pResponse = pRequest_HandShake.ExecuteAsync();
await pResponse;
strFileName = pResponse.Result.Properties.Title;
for (int i = 0; i < pResponse.Result.Sheets.Count; i++)
{
listSheet.Add(new SheetWrapper(pResponse.Result.Sheets[i].Properties.Title));
}
}
catch (Exception pException)
{
pException_OnError = pException;
}
OnFinishConnect(strSheetID, strFileName, _eConnectedSheetType, listSheet, pException_OnError);
}
public async Task ISheetConnector_DoConnect_And_Parsing(string strSheetID, delOnFinishConnect OnFinishConnect)
{
_mapWorkSheet.Clear();
this.strSheetID = strSheetID;
_pService = null;
Exception pException_OnError = null;
try
{
UserCredential credential;
using (var stream =
new FileStream(_strCredentialFilePath, FileMode.Open, FileAccess.Read))
{
string credPath = "token.json";
credential = GoogleWebAuthorizationBroker.AuthorizeAsync(
GoogleClientSecrets.Load(stream).Secrets,
Scopes,
"user",
_pTokenSource.Token,
new FileDataStore(credPath, true)).Result;
}
_pService = new SheetsService(new BaseClientService.Initializer()
{
HttpClientInitializer = credential,
ApplicationName = ApplicationName,
});
}
catch (Exception pException)
{
OnFinishConnect(this, pException);
return;
}
strFileName = "";
var pRequest_HandShake = _pService.Spreadsheets.Get(strSheetID);
try
{
var pResponse = pRequest_HandShake.ExecuteAsync();
await pResponse;
strFileName = pResponse.Result.Properties.Title;
IList <Sheet> listSheet = pResponse.Result.Sheets;
_mapWorkSheet = listSheet.ToDictionary(p => p.Properties.SheetId.ToString(),
p => new SheetData(this, p.Properties.Title, p.Properties.SheetId.ToString()));
}
catch (Exception pException)
{
pException_OnError = pException;
}
OnFinishConnect(this, pException_OnError);
}
private async void Open_Excel(SynchronizationContext pSyncContext_Call, string strFileAbsolutePath_And_IncludeExtension, delOnFinishConnect OnFinishConnect)
{
List <SheetWrapper> listSheet = new List <SheetWrapper>();
_eConnectedSheetType = ESpreadSheetType.MSExcel;
_strSheetID = strFileAbsolutePath_And_IncludeExtension;
Exception pException_OnError = null;
foreach (DataTable pData in _mapWorkSheet.Values)
{
pData.Dispose();
}
_mapWorkSheet.Clear();
await Task.Run(() =>
{
string strAbsolutePath = DoMake_AbsolutePath(strFileAbsolutePath_And_IncludeExtension);
try
{
using (var stream = File.Open(strAbsolutePath, FileMode.Open, FileAccess.Read))
{
using (IExcelDataReader reader = ExcelReaderFactory.CreateReader(stream))
{
var DataSet = reader.AsDataSet();
foreach (DataTable pSheet in DataSet.Tables)
{
_mapWorkSheet.Add(pSheet.TableName, pSheet);
listSheet.Add(new SheetWrapper(pSheet.TableName));
}
}
}
}
catch (Exception pException)
{
pException_OnError = pException;
}
});
strFileName = Path.GetFileNameWithoutExtension(strFileAbsolutePath_And_IncludeExtension);
pSyncContext_Call.Send(new SendOrPostCallback(o =>
{
OnFinishConnect(strFileAbsolutePath_And_IncludeExtension, strFileName, _eConnectedSheetType, listSheet, pException_OnError);
}),
null);
}
public void DoOpen_Excel(string strFileAbsolutePath_And_IncludeExtension, delOnFinishConnect OnFinishConnect)
{
Open_Excel(SynchronizationContext.Current, strFileAbsolutePath_And_IncludeExtension, OnFinishConnect);
}
private async Task Open_Excel(SynchronizationContext pSyncContext_Call, string strFileAbsolutePath_And_IncludeExtension, delOnFinishConnect OnFinishConnect)
{
foreach (var pSet in _mapDataTable.Values)
{
pSet.Dispose();
}
_mapDataTable.Clear();
_mapWorkSheet.Clear();
strSheetID = strFileAbsolutePath_And_IncludeExtension;
Exception pException_OnError = null;
await Task.Run(() =>
{
string strAbsolutePath = DoConvert_AbsolutePath(strFileAbsolutePath_And_IncludeExtension);
try
{
using (var stream = File.Open(strAbsolutePath, FileMode.Open, FileAccess.Read))
{
using (IExcelDataReader reader = ExcelReaderFactory.CreateReader(stream))
{
var DataSet = reader.AsDataSet();
foreach (DataTable pSheet in DataSet.Tables)
{
string strTableName = pSheet.TableName;
_mapWorkSheet.Add(strTableName, new SheetData(this, strTableName));
_mapDataTable.Add(strTableName, pSheet);
}
}
}
}
catch (Exception pException)
{
pException_OnError = pException;
}
});
// strSheetID = Path.GetFileNameWithoutExtension(strFileAbsolutePath_And_IncludeExtension);
if (pSyncContext_Call == null)
{
OnFinishConnect(this, pException_OnError);
}
else
{
pSyncContext_Call.Send(o =>
{
OnFinishConnect(this, pException_OnError);
},
null);
}
}
public async Task ISheetConnector_DoConnect_And_Parsing(string strSheetID, delOnFinishConnect OnFinishConnect)
{
await Open_Excel(SynchronizationContext.Current, strSheetID, OnFinishConnect);
}