本文整理汇总了C#中Worksheet.Descendants方法的典型用法代码示例。如果您正苦于以下问题：C# Worksheet.Descendants方法的具体用法？C# Worksheet.Descendants怎么用？C# Worksheet.Descendants使用的例子？那么恭喜您, 这里精选的方法代码示例或许可以为您提供帮助。您也可以进一步了解该方法所在类Worksheet的用法示例。
在下文中一共展示了Worksheet.Descendants方法的15个代码示例，这些例子默认根据受欢迎程度排序。您可以为喜欢或者感觉有用的代码点赞，您的评价将有助于系统推荐出更棒的C#代码示例。
示例1: GetObjects
public IEnumerable<ExpandoObject> GetObjects(Worksheet worksheet, ISheetDefinition sheetDefinition)
{
if (worksheet == null) throw new ArgumentNullException("worksheet");
if (sheetDefinition == null) throw new ArgumentNullException("sheetDefinition");
var rows = worksheet.Descendants<Row>().Skip(1);
return rows.Select(x => ParseFromRow(x, sheetDefinition));
}示例2: LoadClients
public static List<Client> LoadClients(Worksheet worksheet, SharedStringTable sharedString)
{
List<Client> result = new List<Client>();
IEnumerable<Row> dataRows =
from row in worksheet.Descendants<Row>()
where row.RowIndex > 1
select row;
foreach (Row row in dataRows)
{
IEnumerable<String> textValues =
from cell in row.Descendants<Cell>()
where cell.CellValue != null
select
(cell.DataType != null
&& cell.DataType.HasValue
&& cell.DataType == CellValues.SharedString
? sharedString.ChildElements[
int.Parse(cell.CellValue.InnerText)].InnerText
: cell.CellValue.InnerText)
;
if (textValues.Count() > 0)
{
var textArray = textValues.ToArray();
if (textArray.Length > 0)
{
Client client = new Client();
client.Name = textArray[0];
client.SortedName = client.Name.ToLower();
if (client.Name.ToLower().StartsWith("the "))
client.SortedName = client.Name.Substring(4).ToLower();
if (textArray.Length > 1)
client.Location = textArray[1];
result.Add(client);
}
}
else
{
break;
}
}
return result;
}示例3: LoadUIDesign
/// <summary>
/// Helper method for creating a list of ktExaminedGroup
/// from an Excel worksheet.
/// </summary>
public bool LoadUIDesign(Worksheet worksheet, SharedStringTable sharedString)
{
Result = new List<ktUIDesign>();
//Linq query to get the column headers on the sheet
Row columnRow =
(from row in worksheet.Descendants<Row>()
where row.RowIndex == 1
select row).First();
IEnumerable<String> headerValues =
from cell in columnRow.Descendants<Cell>()
where cell.CellValue != null
select
(cell.DataType != null
&& cell.DataType.HasValue
&& cell.DataType == CellValues.SharedString
? sharedString.ChildElements[
int.Parse(cell.CellValue.InnerText)].InnerText
: cell.CellValue.InnerText);
foreach (string header in headerValues)
{
ColumnNames.Add(header);
}
if (CheckColumnNames(ColumnNames))
{
//LINQ query to skip first row with column names.
IEnumerable<Row> dataRows =
from row in worksheet.Descendants<Row>()
where row.RowIndex > 1
select row;
if (CheckDataInSheets(dataRows))
{
foreach (Row row in dataRows)
{
//LINQ query to return the row's cell values.
//Where clause filters out any cells that do not contain a value.
//Select returns the value of a cell unless the cell contains
// a Shared String.
//If the cell contains a Shared String, its value will be a
// reference id which will be used to look up the value in the
// Shared String table.
//IEnumerable<String> textValues =
// from cell in row.Descendants<Cell>()
// where cell.CellValue != null
// select
// (cell.DataType != null
// && cell.DataType.HasValue
// && cell.DataType == CellValues.SharedString
// ? sharedString.ChildElements[
// int.Parse(cell.CellValue.InnerText)].InnerText
// : cell.CellValue.InnerText)
// ;
IEnumerable<String> textValues =
from cell in row.Descendants<Cell>()
where cell.CellValue != null
select
(cell.DataType != null
&& cell.DataType.HasValue
&& cell.DataType == CellValues.SharedString
? sharedString.ChildElements[
int.Parse(cell.CellValue.InnerText)].InnerText
: cell.CellValue.InnerText)
;
//Check to verify the row contained data.
if (textValues.Count() > 0)
{
//Create a ktExaminedGroup and add it to the list.
//var textArray = textValues.ToArray();
//ktUIDesign design = new ktUIDesign();
//design.DesignID = Int32.Parse(textArray[0]);
//design.DatabaseTableName = textArray[1];
//design.DatabaseFieldName = textArray[2];
//design.CodeTableName = textArray[3];
//design.ResxID = textArray[4];
//design.ReadOnlyPolicy = Int32.Parse(textArray[5]);
//design.InputDataType = Int32.Parse(textArray[6]);
//design.MortyParameter = Int32.Parse(textArray[7]);
//design.RequiredID = Int32.Parse(textArray[8]);
//design.GUIUnitShortName = textArray[9];
//design.DatabaseUnitName = textArray[10];
//design.LabkaUnitName = textArray[11];
//design.DatabaseToUIConversion = textArray[12];
//design.DefaultValue = textArray[13];
//design.NormalRangeMinimum = Int32.Parse(textArray[14]);
//design.NormalRangeMaximum = Int32.Parse(textArray[15]);
//design.CopyEncounter = Int32.Parse(textArray[16]);
//design.CopyEpisode = Int32.Parse(textArray[17]);
//design.DataQualityScore = Int32.Parse(textArray[18]);
//design.CopyFinalEncounter = Int32.Parse(textArray[19]);
//result.Add(design);
//.........这里部分代码省略.........
示例4: LoadQAGroups
/// <summary>
/// Helper method for creating a list of ktExaminedGroup
/// from an Excel worksheet.
/// </summary>
public void LoadQAGroups(Worksheet worksheet,
SharedStringTable sharedString)
{
//Initialize the ktExaminedGroup list.
List<QAGroup> result = new List<QAGroup>();
//LINQ query to skip first row with column names.
IEnumerable<Row> dataRows =
from row in worksheet.Descendants<Row>()
where row.RowIndex > 1
select row;
foreach (Row row in dataRows)
{
//LINQ query to return the row's cell values.
//Where clause filters out any cells that do not contain a value.
//Select returns the value of a cell unless the cell contains
// a Shared String.
//If the cell contains a Shared String, its value will be a
// reference id which will be used to look up the value in the
// Shared String table.
//IEnumerable<String> textValues =
// from cell in row.Descendants<Cell>()
// where cell.CellValue != null
// select
// (cell.DataType != null
// && cell.DataType.HasValue
// && cell.DataType == CellValues.SharedString
// ? sharedString.ChildElements[
// int.Parse(cell.CellValue.InnerText)].InnerText
// : cell.CellValue.InnerText)
// ;
IEnumerable<String> textValues =
from cell in row.Descendants<Cell>()
where cell.CellValue != null
select
(cell.DataType != null
&& cell.DataType.HasValue
&& cell.DataType == CellValues.SharedString
? sharedString.ChildElements[
int.Parse(cell.CellValue.InnerText)].InnerText
: cell.CellValue.InnerText)
;
//Check to verify the row contained data.
if (textValues.Count() > 0)
{
//Create a ktExaminedGroup and add it to the list.
var textArray = textValues.ToArray();
QAGroup qaGroup = new QAGroup();
qaGroup.TypeID = textArray[0];
qaGroup.Type = textArray[1];
result.Add(qaGroup);
}
else
{
//If no cells, then you have reached the end of the table.
break;
}
//Return populated list of ktExaminedGroup.
QAGroupsList = result;
}
}示例5: CreateSpreadsheetCellIfNotExist
// Given a Worksheet and a cell name, verifies that the specified cell exists.
// If it does not exist, creates a new cell.
private static void CreateSpreadsheetCellIfNotExist(Worksheet worksheet, string cellName)
{
string columnName = GetColumnName(cellName);
uint rowIndex = GetRowIndex(cellName);
IEnumerable<Row> rows = worksheet.Descendants<Row>().Where(r => r.RowIndex.Value == rowIndex);
// If the Worksheet does not contain the specified row, create the specified row.
// Create the specified cell in that row, and insert the row into the Worksheet.
if (rows.Count() == 0)
{
Row row = new Row() { RowIndex = new UInt32Value(rowIndex) };
Cell cell = new Cell() { CellReference = new StringValue(cellName) };
row.Append(cell);
worksheet.Descendants<SheetData>().First().Append(row);
worksheet.Save();
}
else
{
Row row = rows.First();
IEnumerable<Cell> cells = row.Elements<Cell>().Where(c => c.CellReference.Value == cellName);
// If the row does not contain the specified cell, create the specified cell.
if (cells.Count() == 0)
{
Cell cell = new Cell() { CellReference = new StringValue(cellName) };
row.Append(cell);
worksheet.Save();
}
}
}示例6: LoadktResources
/// <summary>
/// Helper method for creating a list of ktExaminedGroup
/// from an Excel worksheet.
/// </summary>
public bool LoadktResources(Worksheet worksheet,
SharedStringTable sharedString)
{
Result = new List<ktResources>();
//Linq query to get the column headers on the sheet
Row columnRow =
(from row in worksheet.Descendants<Row>()
where row.RowIndex == 1
select row).First();
IEnumerable<String> headerValues =
from cell in columnRow.Descendants<Cell>()
where cell.CellValue != null
select
(cell.DataType != null
&& cell.DataType.HasValue
&& cell.DataType == CellValues.SharedString
? sharedString.ChildElements[
int.Parse(cell.CellValue.InnerText)].InnerText
: cell.CellValue.InnerText);
foreach (string header in headerValues)
{
ColumnNames.Add(header);
}
if (CheckColumnNames(ColumnNames))
{
//LINQ query to skip first row with column names.
IEnumerable<Row> dataRows =
from row in worksheet.Descendants<Row>()
where row.RowIndex > 1
select row;
if (CheckDataInSheets(dataRows))
{
foreach (Row row in dataRows)
{
//LINQ query to return the row's cell values.
//Where clause filters out any cells that do not contain a value.
//Select returns the value of a cell unless the cell contains
// a Shared String.
//If the cell contains a Shared String, its value will be a
// reference id which will be used to look up the value in the
// Shared String table.
IEnumerable<String> textValues =
from cell in row.Descendants<Cell>()
where cell.CellValue != null
select
(cell.DataType != null
&& cell.DataType.HasValue
&& cell.DataType == CellValues.SharedString
? sharedString.ChildElements[
int.Parse(cell.CellValue.InnerText)].InnerText
: cell.CellValue.InnerText);
//Check to verify the row contained data.
if (textValues.Count() > 0)
{
var textArray = textValues.ToArray();
ktResources resource = new ktResources();
resource.ResourceID = textArray[0];
resource.ResourceTypeID = textArray[1];
resource.ResourceResxID = textArray[2];
Result.Add(resource);
}
else
{
//If no cells, then you have reached the end of the table.
break;
}
//Return populated list of ktExaminedGroup.
}
}
else
{
return false;
}
return true;
}
return false;
}示例7: LoadExaminedGroup
/// <summary>
/// Helper method for creating a list of ktExaminedGroup
/// from an Excel worksheet.
/// </summary>
public bool LoadExaminedGroup(Worksheet worksheet, SharedStringTable sharedString)
{
Result = new List<ktExaminedGroup>();
//Linq query to get the column headers on the sheet
Row columnRow =
(from row in worksheet.Descendants<Row>()
where row.RowIndex == 1
select row).First();
IEnumerable<String> headerValues =
from cell in columnRow.Descendants<Cell>()
where cell.CellValue != null
select
(cell.DataType != null
&& cell.DataType.HasValue
&& cell.DataType == CellValues.SharedString
? sharedString.ChildElements[
int.Parse(cell.CellValue.InnerText)].InnerText
: cell.CellValue.InnerText);
foreach (string header in headerValues)
{
ColumnNames.Add(header);
}
if (CheckColumnNames(ColumnNames))
{
//LINQ query to skip first row with column names.
IEnumerable<Row> dataRows =
from row in worksheet.Descendants<Row>()
where row.RowIndex > 1
select row;
if (CheckDataInSheets(dataRows))
{
foreach (Row row in dataRows)
{
//LINQ query to return the row's cell values.
//Where clause filters out any cells that do not contain a value.
//Select returns the value of a cell unless the cell contains
// a Shared String.
//If the cell contains a Shared String, its value will be a
// reference id which will be used to look up the value in the
// Shared String table.
IEnumerable<String> textValues =
from cell in row.Descendants<Cell>()
where cell.CellValue != null
select
(cell.DataType != null
&& cell.DataType.HasValue
&& cell.DataType == CellValues.SharedString
? sharedString.ChildElements[
int.Parse(cell.CellValue.InnerText)].InnerText
: cell.CellValue.InnerText);
//Check to verify the row contained data.
if (textValues.Count() > 0)
{
if (textValues.Count() == 8)
{
//Create a ktExaminedGroup and add it to the list.
var textArray = textValues.ToArray();
ktExaminedGroup examined = new ktExaminedGroup();
examined.ID = textArray[0];
examined.GroupIdentifier = textArray[1];
examined.GroupType = textArray[2];
examined.GroupExpendable = textArray[3];
examined.Name = textArray[4];
examined.Expanded = textArray[5];
examined.DataQualityScore = textArray[6];
examined.RequiredScore = textArray[7];
Result.Add(examined);
}
else
{
var textArray = textValues.ToArray();
ktExaminedGroup examined = new ktExaminedGroup();
examined.ID = textArray[0];
examined.GroupIdentifier = textArray[1];
examined.GroupType = textArray[2];
examined.GroupExpendable = textArray[3];
examined.Name = "";
examined.Expanded = textArray[4];
examined.DataQualityScore = textArray[5];
examined.RequiredScore = textArray[6];
Result.Add(examined);
}
}
else
{
//If no cells, then you have reached the end of the table.
break;
}
}
}
else
//.........这里部分代码省略.........
示例8: LoadListnews
private List<NewsEntity> LoadListnews(Worksheet worksheet, SharedStringTable sharedString)
{
//Initialize the customer list.
List<NewsEntity> result = new List<NewsEntity>();
//LINQ query to skip first row with column names.
IEnumerable<Row> dataRows = (from row in worksheet.Descendants<Row>()
where row.RowIndex > 1
select row);
foreach (Row row in dataRows)
{
//LINQ query to return the row's cell values.
//Where clause filters out any cells that do not contain a value.
//Select returns the value of a cell unless the cell contains
// a Shared String.
//If the cell contains a Shared String, its value will be a
// reference id which will be used to look up the value in the
// Shared String table.
IEnumerable<String> textValues = from cell in row.Descendants<Cell>()
select
(cell.DataType != null
&& cell.DataType.HasValue
&& cell.DataType == CellValues.SharedString
? sharedString.ChildElements[Utils.CIntDef(cell.CellValue.InnerText)].InnerText : cell.CellValue.InnerText);
//Check to verify the row contained data.
if (textValues.Count() > 0)
{
//Create a customer and add it to the list.
var textArray = textValues.ToArray();
string _title = Utils.CStrDef(textArray[0]);
string _masp = Utils.CStrDef(textArray[1]).Trim();
string _congnghe = Utils.CStrDef(textArray[2]);
string _weight = Utils.CStrDef(textArray[3]);
int _idhsx = getIdCate(Utils.CStrDef(textArray[4]));
int _idxuatxu = getIdCate(Utils.CStrDef(textArray[5]));
string _bhhanh = Utils.CStrDef(textArray[6]);
int _status = getIdStatus(Utils.CStrDef(textArray[7]));
int _vat = getIdVat(Utils.CStrDef(textArray[8]));
int _period = getIDperiod(Utils.CStrDef(textArray[10]));
decimal _price = Utils.CDecDef(Utils.CStrDef(textArray[12]));
decimal _pricePromos = Utils.CDecDef(Utils.CStrDef(textArray[13]));
decimal _pricegiovang = Utils.CDecDef(Utils.CStrDef(textArray[14]));
decimal _priceMuavao = Utils.CDecDef(Utils.CStrDef(textArray[15]));
int _idcat = getIdCate(Utils.CStrDef(textArray[16]));
NewsEntity newsenti = new NewsEntity();
newsenti._news_tile = _title;
newsenti._news_code = _masp;
newsenti._news_congnghe = _congnghe;
newsenti._news_weight = _weight;
newsenti._news_hangsx = _idhsx;
newsenti._news_xuatxu = _idxuatxu;
newsenti._news_baohanh = _bhhanh;
newsenti._news_status = _status;
newsenti._news_vat = _vat;
newsenti._news_period = _period;
newsenti._news_price = _price;
newsenti._news_priceMos = _pricePromos;
newsenti._news_priceGiovang = _pricegiovang;
newsenti._news_priceMuavao = _priceMuavao;
newsenti._catid = _idcat;
result.Add(newsenti);
}
else
{
//If no cells, then you have reached the end of the table.
break;
}
}
//Return populated list of customers.
return result;
}示例9: GetCellValue
public static string GetCellValue(this SpreadsheetDocument document, Worksheet worksheet, string addressName)
{
Cell theCell = worksheet.Descendants<Cell>().
Where(c => c.CellReference == addressName).FirstOrDefault();
// If the cell doesn't exist, return an empty string:
if (theCell == null)
return "";
return GetCellValue(document, theCell);
}示例10: LoadUIOrder
/// <summary>
/// Helper method for creating a list of ktExaminedGroup
/// from an Excel worksheet.
/// </summary>
public bool LoadUIOrder(Worksheet worksheet,
SharedStringTable sharedString)
{
Result = new List<ktUIOrder>();
//Linq query to get the column headers on the sheet
Row columnRow =
(from row in worksheet.Descendants<Row>()
where row.RowIndex == 1
select row).First();
IEnumerable<String> headerValues =
from cell in columnRow.Descendants<Cell>()
where cell.CellValue != null
select
(cell.DataType != null
&& cell.DataType.HasValue
&& cell.DataType == CellValues.SharedString
? sharedString.ChildElements[
int.Parse(cell.CellValue.InnerText)].InnerText
: cell.CellValue.InnerText);
foreach (string header in headerValues)
{
ColumnNames.Add(header);
}
if (CheckColumnNames(ColumnNames))
{
//LINQ query to skip first row with column names.
IEnumerable<Row> dataRows =
from row in worksheet.Descendants<Row>()
where row.RowIndex > 1
select row;
if (CheckDataInSheets(dataRows))
{
foreach (Row row in dataRows)
{
//LINQ query to return the row's cell values.
//Where clause filters out any cells that do not contain a value.
//Select returns the value of a cell unless the cell contains
// a Shared String.
//If the cell contains a Shared String, its value will be a
// reference id which will be used to look up the value in the
// Shared String table.
//IEnumerable<String> textValues =
// from cell in row.Descendants<Cell>()
// where cell.CellValue != null
// select
// (cell.DataType != null
// && cell.DataType.HasValue
// && cell.DataType == CellValues.SharedString
// ? sharedString.ChildElements[
// int.Parse(cell.CellValue.InnerText)].InnerText
// : cell.CellValue.InnerText)
// ;
IEnumerable<String> textValues =
from cell in row.Descendants<Cell>()
where cell.CellValue != null
select
(cell.DataType != null
&& cell.DataType.HasValue
&& cell.DataType == CellValues.SharedString
? sharedString.ChildElements[
int.Parse(cell.CellValue.InnerText)].InnerText
: cell.CellValue.InnerText)
;
//Check to verify the row contained data.
if (textValues.Count() > 0)
{
if (textValues.Count() == 5)
{
//Create a ktUIOrder and add it to the list.
var textArray = textValues.ToArray();
ktUIOrder order = new ktUIOrder();
order.DesignID = textArray[0];
order.GroupOrder = double.Parse(textArray[1], CultureInfo.InvariantCulture); // Convert.ToDouble(textArray[1]);
order.GroupTypeID = textArray[2];
order.PageTypeID = textArray[3];
order.IncludedTypeID = textArray[4];
Result.Add(order);
}
else
{
var textArray = textValues.ToArray();
ktUIOrder order = new ktUIOrder();
order.DesignID = textArray[0];
order.GroupOrder = double.Parse(textArray[1], CultureInfo.InvariantCulture); // Convert.ToDouble(textArray[1]);
order.GroupTypeID = textArray[2];
order.PageTypeID = "";
order.IncludedTypeID = textArray[4];
Result.Add(order);
}
//.........这里部分代码省略.........
示例11: ReadAQ1ExcelWorkSheet
private static void ReadAQ1ExcelWorkSheet(Worksheet sheet, int sheetNo, SharedStringTable sst, List<clsAQ1ReportDetailRow> detailRows)
{
Debug.WriteLine("ReadAQ1ExcelWorkSheet");
var cells = sheet.Descendants<Cell>();
var rows = sheet.Descendants<Row>();
Debug.WriteLine("Row Count = {0}", rows.LongCount());
Debug.WriteLine("Cell Count = {0}", cells.LongCount());
Debug.WriteLine("");
clsAQ1ReportHeaderRow headerRow = new clsAQ1ReportHeaderRow(sheetNo);
clsAQ1ReportDetailRow detailRow = null;
int rowCount = rows.Count();
RowTypeEnum rowType = RowTypeEnum.UNKNOWN_ROW_TYPE;
RowSubTypeEnum rowSubType = RowSubTypeEnum.UNKNOWN_ROW_SUB_TYPE;
foreach (Row row in rows)
{
SetRowTypeSubType(ref rowType, ref rowSubType, row, rowCount, sst);
Debug.WriteLine("\t" + "rowType: " + rowType.ToString() + ", rowSubType: " + rowSubType.ToString());
switch (rowType)
{
case RowTypeEnum.HeaderRow:
PopulateAQ1HeaderRow(sst, headerRow, rowSubType, row);
continue;
case RowTypeEnum.DetailRow:
if (rowSubType == RowSubTypeEnum.DetailRow1)
{
detailRow = new clsAQ1ReportDetailRow(headerRow, row.RowIndex);
detailRows.Add(detailRow);
}
PopulateAQ1DetailRow(sst, detailRow, rowSubType, row);
break;
case RowTypeEnum.FooterRow:
continue;
default:
throw new Exception("UNKNOWN Row Type");
}
}
}示例12: ToExcelSheet
public void ToExcelSheet(Worksheet worksheet)
{
if (worksheet == null) throw new ArgumentNullException("worksheet");
SheetData sheetData = worksheet.Descendants<SheetData>().First();
ToExcelSheet(sheetData);
}示例13: LoadOrders
/// <summary>
/// Helper method for creating a list of orders
/// from an Excel worksheet.
/// </summary>
public static List<Order> LoadOrders(Worksheet worksheet, SharedStringTable sharedString)
{
//Initialize order list.
List<Order> result = new List<Order>();
//LINQ query to skip first row with column names.
IEnumerable<Row> dataRows =
from row in worksheet.Descendants<Row>()
where row.RowIndex > 1
select row;
foreach (Row row in dataRows)
{
//LINQ query to return the row's cell values.
//Where clause filters out any cells that do not contain a value.
//Select returns cell's value unless the cell contains
// a shared string.
//If the cell contains a shared string its value will be a
// reference id which will be used to look up the value in the
// shared string table.
IEnumerable<String> textValues =
from cell in row.Descendants<Cell>()
where cell.CellValue != null
select (cell.DataType != null
&& cell.DataType.HasValue
&& cell.DataType == CellValues.SharedString ?
sharedString.ChildElements[int.Parse(cell.CellValue.InnerText)].InnerText : cell.CellValue.InnerText);
//Check to verify the row contains data.
if (textValues.Count() > 0)
{
//Create an Order and add it to the list.
var textArray = textValues.ToArray();
Order order = new Order();
order.PONumber = textArray[0];
order.Part = textArray[1].PadLeft(11, '0');
order.Area = textArray[2];
order.Qty = textArray[3];
order.Customer = textArray[4];
result.Add(order);
}
else
{
//If no cells, then you have reached the end of the table.
break;
}
}
//Return populated list of orders.
return result;
}示例14: ExtractRowsData
/// <summary>
/// Get the data using the first row as columns and the rest of the rows as data
/// </summary>
/// <param name="data"></param>
/// <param name="worksheet"></param>
/// <param name="ssTable"></param>
/// <param name="cellFormats"></param>
private static void ExtractRowsData(List<ExpandoObject> data, Worksheet worksheet, SharedStringTable ssTable, CellFormats cellFormats, bool hasColHeaders)
{
var columnHeaders = worksheet.Descendants<Row>().First().Descendants<Cell>().Select(c => Convert.ToString(ProcessCellValue(c, ssTable, cellFormats))).ToArray();
var columnHeadersCellReference = worksheet.Descendants<Row>().First().Descendants<Cell>().Select(c => c.CellReference.InnerText.Replace("1", string.Empty)).ToArray();
if (!hasColHeaders)
{
for (int i = 0; i < columnHeaders.Length; i++)
{
columnHeaders[i] = "Column" + i;
}
}
var spreadsheetData = from row in worksheet.Descendants<Row>()
where row.RowIndex > (hasColHeaders ? 1 : 0)
select row;
foreach (var dataRow in spreadsheetData)
{
dynamic row = new ExpandoObject();
Cell[] rowCells = dataRow.Descendants<Cell>().ToArray();
for (int i = 0; i < columnHeaders.Length; i++)
{
// Find and add the correct cell to the row object
Cell cell = dataRow.Descendants<Cell>().Where(c => c.CellReference == columnHeadersCellReference[i] + dataRow.RowIndex).FirstOrDefault();
if (cell != null)
((IDictionary<String, Object>)row).Add(new KeyValuePair<String, Object>(columnHeaders[i], ProcessCellValue(cell, ssTable, cellFormats)));
else
((IDictionary<String, Object>)row).Add(new KeyValuePair<String, Object>(columnHeaders[i], ""));
}
data.Add(row);
}
}示例15: MatchLayouts
/// <remarks>
/// An Excel SpreadSheet contains one or more Worksheets, each may or maynot contain data of interest.
/// - Excel is a terrible way to collect data from a large number of different sources in a consistent and reliable way.
/// - Be that as it may, Excel is favored by organizations that prefer manpower over automation when performing data processing tasks.
/// - Most state agencies are typical of this kind of organization.
/// - To top it off most of these agencies give little thought to gathering data in a consistent way. So we are likely to
/// recieve a dump of spreadsheets with a variety of inconsistencies.
///
/// As varied as these spreadsheets may be, a spreadsheet is expected to contain only a single type of related data set which is called a
/// DataSourceType in this application.
///
/// A DataSourceType describes how to process the worksheets in a spreadsheet. It indicates:
/// - the name of the file to save extracted data.
/// - a list of DataWorkSheets
/// - an indicator as to how to process the spreadsheet against the list of DataWorkSheets:
///
/// • MatchAllDataWorkSheets
/// - There must be a one to one correspondence between each DataWorkSheet and each SpreadSheet WorkSheet in order.
/// - The DataWorkSheet name must match the SpreadSheet WorkSheet name.
///
/// • MatchByClosestWorkSheetLayout
/// - Each SpreadSheet Worksheet will be matched against the closest DataWorkSheet/WorkSheetLayout
///
/// A DataWorkSheet has
/// - a Name to be used when processing the spreadsheet by MatchAllDataWorkSheets.
/// - a WorkSheetLayout
///
/// A WorkSheetLayout is
/// - a collection of field layout versions with additional information about how to determine where to look
/// for field cells on the spreadsheet.
/// - a collection of data columns with addition information about how to determine where to look
/// for the data column on the WorkSheet.
///
/// - Each data column in the collection has associated with it a list of column titles that should
/// map from the WorkSheet to the data column.
///
/// - The WorkSheetLayout also includes a collection of col layout versions. Each of these is a list of cells
/// that should be scraped for strings that are concatinated into a column title and the column to associate
/// the title with.
///
/// - For a given WorkSheet all layouts are processed
///
/// There is a layout of column title cells that will be scaped for column titles. Those titles
/// are then matched to a list of titles for a given data column. The assumption being that all titles to match
/// are unique across all data columns for a given WorkSheetLayout
/// </remarks>
public MatchData MatchLayouts(Worksheet ws, SheetLayout sheetLayout, SharedStringTablePart stringTable, CellFormats formats, FileInfo file)
{
MatchData md = new MatchData();
MatchData colMD = null;
MatchData cellMD = null;
// All cells in worksheet.
var tcs = ws.Descendants<Cell>();
switch (sheetLayout.wsLayout.layoutType)
{
case LayoutType.Both:
colMD = MatchColLayouts(md, tcs, sheetLayout, stringTable, formats, file);
cellMD = MatchCellLayouts(md, tcs, sheetLayout, stringTable, formats, file);
md.fldCellMap = cellMD.fldCellMap;
md.fldColMap = colMD.fldColMap;
md.isPass = md.fldColMap != null && md.fldCellMap != null;
break;
case LayoutType.CellOnly:
cellMD = MatchCellLayouts(md, tcs, sheetLayout, stringTable, formats, file);
md.fldCellMap = cellMD.fldCellMap;
md.isPass = md.fldCellMap != null;
break;
case LayoutType.ColumnOnly:
colMD = MatchColLayouts(md, tcs, sheetLayout, stringTable, formats, file);
md.fldColMap = colMD.fldColMap;
md.isPass = md.fldColMap != null;
break;
}
if (md.isPass)
{
if (sheetLayout.srcWorksheets == null)
sheetLayout.srcWorksheets = new List<Worksheet>();
sheetLayout.srcWorksheets.Add(ws);
}
return md;
}