private void MapDataPosix(SpreadsheetDocument doc, DataPosition dataPos, IEnumerable <Cell> header)
{
for (int i = 0; i < header.Count(); i++)
{
Cell celHeader = header.ElementAt(i);
var cellValue = doc.GetCellValue(celHeader);
dataPos.GetDataPositions(cellValue, celHeader.CellReference.Value);
}
}
ExtractFrontDocumentData(SpreadsheetDocument doc, IEnumerable <Cell> cells,
string courseName, uint rowIndex, DataPosition data)
{
return(await Task.Factory.StartNew(() =>
{
var documentFront = new DataAccess.Storage.Models.Document();
documentFront.StudentName = doc.GetCellValue(cells.FindCell(data.NameCol, rowIndex))?.Trim();
documentFront.StudentDocument = doc.GetCellValue(cells.FindCell(data.DocumentCol, rowIndex))?.Trim();
documentFront.Course = courseName;
//Find by previus header founded
documentFront.StartDate = doc.GetCellValue(cells.FindCell(data.StartDateCol, rowIndex))?.ParseDate();
documentFront.EndDate = doc.GetCellValue(cells.FindCell(data.EndDateCol, rowIndex))?.ParseDate();
documentFront.WorkLoad = doc.GetCellValue(cells.FindCell(data.WorkLoadCol, rowIndex));
//TODO: criar enum para status
documentFront.Status = "Aguardando processamento";
//TODO: colocar data por extenso
documentFront.DateOfIssue = DateTime.Now.ToString("dd/MM/yyyy") + ".";
return documentFront;
}));
}
private string FindCourseName(SpreadsheetDocument doc, IEnumerable <Cell> cellsTitle)
{
for (int i = 0; i < cellsTitle.Count(); i++)
{
Cell celCourseTitle = cellsTitle.ElementAt(i);
var cellValue = doc.GetCellValue(celCourseTitle);
if (!string.IsNullOrWhiteSpace(cellValue) && cellValue.StartsWith("Pós-Graduação"))
{
return(ExtractCourseName(cellValue));
}
}
return(string.Empty);
}
public static string GetCellValue(string fileName,
string sheetName,
string addressName)
{
string value = null;
// Open the spreadsheet document for read-only access.
using (SpreadsheetDocument document =
SpreadsheetDocument.Open(fileName, true))
{
value = document.GetCellValue(sheetName, addressName);
}
return(value);
}
private static List <ColumnData> GetColumnsEquivalences(this SpreadsheetDocument document, SheetData sheetData, ResultTable results)
{
var resultsCols = results.Columns.ToDictionary(c => c.Column.DisplayName);
var headerCells = sheetData.Descendants <Row>().FirstEx().Descendants <Cell>().ToList();
var templateCols = headerCells.ToDictionary(c => document.GetCellValue(c));
var rowDataCellTemplates = sheetData.Descendants <Row>()
.FirstEx(r => IsValidRowDataTemplate(r, headerCells))
.Descendants <Cell>().ToList();
var dic = templateCols.OuterJoinDictionaryCC(resultsCols, (name, cell, resultCol) =>
{
if (resultCol == null)
{
throw new ApplicationException(ExcelMessage.TheExcelTemplateHasAColumn0NotPresentInTheFindWindow.NiceToString().FormatWith(name));
}
if (cell != null)
{
return(new ColumnData
{
IsNew = false,
StyleIndex = rowDataCellTemplates[headerCells.IndexOf(cell)].StyleIndex,
Column = resultCol,
});
}
else
{
CellBuilder cb = PlainExcelGenerator.CellBuilder;
return(new ColumnData
{
IsNew = true,
StyleIndex = 0,
Column = resultCol,
});
}
});
return(dic.Values.ToList());
}
/// <summary>
/// Construct the list of header columns from the given row
/// </summary>
/// <param name="spreadsheetDocument">The spreadsheet</param>
/// <param name="headerRow">The row that contains the headers for the data</param>
/// <returns></returns>
private List <ColumnDefinition> BuildHeaders(SpreadsheetDocument spreadsheetDocument, Row headerRow)
{
var columns = new List <ColumnDefinition>();
var position = 0;
foreach (var unused in headerRow.Descendants <Cell>())
{
var columnName = spreadsheetDocument.GetCellValue(headerRow, position)
.FormatColumnName(_settings.HeaderRowSpaces);
// Find the column definition by name, if it exists
var existingColumn = columns.FirstOrDefault(n => n.Name == columnName);
// Column not found, add to the collection
if (existingColumn == null)
{
columns.Add(new ColumnDefinition
{
Name = columnName,
Positions = new List <int>()
{
position
}
});
// Add the de-duplicated columns to the DataTable
_data.Columns.Add(columnName);
}
else
{
// Add the current ordinal position to the
// matching column already added
existingColumn.Positions.Add(position);
}
position++;
}
return(columns);
}
public static WordParseBase GetWordParse(string excelResultFileFullName)
{
var originalExcelFileFullName = excelResultFileFullName;
//由于当前的EXCEL还在处于打开的状态，我们无法通过OPENXML打开这个EXCEL文件
//所以我们将EXCEL复制到临时文件夹下面
var filePathManager = IFilePathManagerProvider.PathProvider;
var excelDataFileFullName = Path.Combine(filePathManager.TemplateFilesPath, Guid.NewGuid().ToString() + ".xlsx");
File.Copy(excelResultFileFullName, excelDataFileFullName);
var productAliasName = ""; //ProductName将始终来自于第一个sheet表中的B2列
using (SpreadsheetDocument document =
SpreadsheetDocument.Open(excelDataFileFullName, false))
{
var addressName = "B2";
WorkbookPart wbPart = document.WorkbookPart;
//查找对应的sheet ,它的规范是 从记录 或者记录1中的B2中去获取
var sheets = wbPart.Workbook.Descendants <DocumentFormat.OpenXml.Spreadsheet.Sheet>().ToList();
var sheet = sheets.Where(t => t.Name == "记录").FirstOrDefault();
if (sheet == null)
{
sheet = sheets.Where(t => t.Name == "记录1").FirstOrDefault();
}
if (sheet == null)
{
throw new Exception("EXCEL中，找不到以`记录` 或者 `记录1`命名的sheet ");
}
productAliasName = document.GetCellValue(sheet.Name, addressName);
if (string.IsNullOrEmpty(productAliasName))
{
productAliasName = document.GetCellValue(sheet.Name, "C2");
}
}
var productName = ProductAliasName.GetProductName(productAliasName);
if (productName == "辐射温度计")
{
return(new RadiationThermomater.WordParser(originalExcelFileFullName, excelDataFileFullName, filePathManager));
}
else if (productName == "红外热像仪")
{
return(new ThermalImager.WordParse(originalExcelFileFullName, excelDataFileFullName, filePathManager));
}
else if (productName == "黑体辐射源")
{
return(new BlackBodySingleRadiationiation.WordParser(originalExcelFileFullName, excelDataFileFullName, filePathManager));
}
else if (productName == "黑体辐射源_")
{
return(new BlackBodyDoubleRadiation.WordParser(originalExcelFileFullName, excelDataFileFullName, filePathManager));
}
else if (productName == "面辐射源")
{
return(new BlackBodySurfaceRadiation.WordParser(originalExcelFileFullName, excelDataFileFullName, filePathManager));
}
throw new Exception("根据产品名称，找不到 WordParseBase (" + productName + ").");
}
public static List <T> ReadPlainExcel <T>(Stream stream, Func <string[], T> selector)
{
using (SpreadsheetDocument document = SpreadsheetDocument.Open(stream, false))
{
WorkbookPart workbookPart = document.WorkbookPart;
WorksheetPart worksheetPart = document.GetWorksheetPartById("rId1");
var data = worksheetPart.Worksheet.Descendants <SheetData>().Single();
return(data.Descendants <Row>().Skip(1).Select(r => selector(r.Descendants <Cell>().Select(c => document.GetCellValue(c)).ToArray())).ToList());
}
}
