ClientServiceRequest <Spreadsheet> GenerateFilteredPullRequest(int sheetId, IList <SheetColumn> columnMapping)
{
var getRequest = new GetSpreadsheetByDataFilterRequest {
DataFilters = new List <DataFilter>()
};
foreach (var col in columnMapping)
{
getRequest.DataFilters.Add(new DataFilter
{
GridRange = new GridRange
{
SheetId = sheetId,
StartRowIndex = 1, // Ignore header
StartColumnIndex = col.ColumnIndex,
EndColumnIndex = col.ColumnIndex + 1
}
});
}
var request = SheetsService.Service.Spreadsheets.GetByDataFilter(getRequest, SpreadSheetId);
request.Fields = "sheets.properties.gridProperties.rowCount,sheets.data.rowData.values.formattedValue,sheets.data.rowData.values.note";
return(request);
}
private int GetSheetIdByNameClass(string nameClass)
{
GetSpreadsheetByDataFilterRequest requestBody = new GetSpreadsheetByDataFilterRequest();
requestBody.DataFilters = new List <DataFilter>();
requestBody.IncludeGridData = false;
SpreadsheetsResource.GetByDataFilterRequest request = Service.Spreadsheets.GetByDataFilter(requestBody, SpreadsheetId);
Spreadsheet response = request.Execute();
return((Int32)response.Sheets.Where(x => x.Properties.Title == nameClass).FirstOrDefault().Properties.SheetId);
}
public IList <ExcelRowDataModel> ReadAllData(BackgroundWorker worker = null, double maxProgress = 20)
{
var request = sheetsService.Spreadsheets.Get(spreadsheetId);
request.IncludeGridData = false;
var generalSpreadSheetInfo = request.Execute();
var result = new List <ExcelRowDataModel>();
var countRowsData = CountRowData(generalSpreadSheetInfo);
var fileProgressWeight = maxProgress / countRowsData;
var count = 1;
foreach (var sheet in generalSpreadSheetInfo.Sheets)
{
var dataFilterRequest = new GetSpreadsheetByDataFilterRequest
{
DataFilters = new List <DataFilter>(),
IncludeGridData = true
};
var dataFilter = new DataFilter
{
GridRange = new GridRange()
};
dataFilter.GridRange.SheetId = sheet.Properties.SheetId.Value;
dataFilter.GridRange.StartRowIndex = 0;
dataFilter.GridRange.EndRowIndex = 2999;
dataFilter.GridRange.StartColumnIndex = 0;
dataFilter.GridRange.EndColumnIndex = 29;
dataFilterRequest.DataFilters.Add(dataFilter);
GC.Collect();
var dataRequest = sheetsService.Spreadsheets.GetByDataFilter(dataFilterRequest, spreadsheetId);
var sheetData = dataRequest.Execute();
foreach (var row in sheetData.Sheets[0].Data[0].RowData)
{
if (row == null || row.Values == null || row.Values.Count == 0 || row.Values[0] == null ||
string.IsNullOrEmpty(row.Values[0].FormattedValue) || row.Values[0].FormattedValue == "Серийный номер")
{
worker?.ReportProgress((int)(fileProgressWeight * count));
count++;
continue;
}
result.Add(new ExcelRowDataModel(row));
worker?.ReportProgress((int)(fileProgressWeight * count));
count++;
}
}
GC.Collect();
return(result);
}
