public void Get_Excel_Address_From_String(string address, string expectedColumn, long expectedRow)
{
var excellAddress = new ExcelCellAddress(address);
Assert.Equal(ExcelCellAddress.GetColumnLetter(excellAddress.Column), expectedColumn);
Assert.Equal(excellAddress.Row, expectedRow);
}
private DataSet ParseExcel(ExcelPackage package)
{
if (package == null)
{
return(null);
}
try
{
var result = new DataSet();
foreach (var sheet in package.Workbook.Worksheets)
{
if ((sheet?.Dimension?.End?.Row ?? 0) <= 0)
{
continue;
}
if ((sheet?.Dimension?.End?.Column ?? 0) <= 0)
{
continue;
}
var table = new DataTable {
TableName = sheet.Name
};
var totalRows = Math.Min(sheet.Dimension.End.Row, MAX_PREVIEW_ROWS);
var totalColumns = Math.Min(sheet.Dimension.End.Column, Constants.MAX_COLUMNS);
ColumnsNames.Clear();
for (var columnIndex = sheet.Dimension.Start.Column; columnIndex <= totalColumns; columnIndex++)
{
var columnName = ExcelCellAddress.GetColumnLetter(columnIndex);
table.Columns.Add(columnName);
ColumnsNames.Add(columnName);
}
for (var rowIndex = sheet.Dimension.Start.Row; rowIndex <= totalRows; rowIndex++)
{
var row = table.Rows.Add();
for (var columnIndex = sheet.Dimension.Start.Column; columnIndex <= totalColumns; columnIndex++)
{
var columnName = ExcelCellAddress.GetColumnLetter(columnIndex);
row[columnName] = sheet.Cells[columnName + rowIndex].Value;
}
}
result.Tables.Add(table);
}
return(result);
}
catch (Exception ex)
{
ColumnsNames.Clear();
_eventAggregator.PublishOnUIThread(ex);
return(null);
}
}
/// <summary>
/// Disable cell error checking for text cells containing numbers.
/// Do not call more than once per worksheet.
/// </summary>
/// <param name="ws">Worksheet to operate upon</param>
public static void DisableCellWarnings(ExcelWorksheet ws)
{
var xdoc = ws.WorksheetXml;
var ns = xdoc.ChildNodes[1].NamespaceURI;
// Many of the text identifiers are numerical in nature. Excel does not like numerical text data. It thinks
// it needs to be converted and puts a tiny green triangle in the corner of the offending cells. When one
// hovers over it, the tooltip says 'The number in this cell is formatted as text...'. Very annoying.
// NPPlus does not support disabling the cell warning, so we have to modify the low-level worksheet xml document.
// See Hint: http://stackoverflow.com/questions/11858109/using-epplus-excel-how-to-ignore-excel-error-checking-or-remove-green-tag-on-t
var node = xdoc.ChildNodes[1].AppendChild(xdoc.CreateElement("ignoredErrors", ns)).AppendChild(xdoc.CreateElement("ignoredError", ns));
node.Attributes.Append(xdoc.CreateAttribute("sqref")).Value = "A:" + ExcelCellAddress.GetColumnLetter(ws.Dimension.End.Column);
node.Attributes.Append(xdoc.CreateAttribute("numberStoredAsText")).Value = "1";
}
private void SetCellValue(ExcelTerm excelTerm, ExcelRangeBase cell, int columnIndex)
{
var columnLetter = ExcelCellAddress.GetColumnLetter(columnIndex);
if (columnLetter == _providerSettings.SourceColumn)
{
excelTerm.Source = cell.Text;
excelTerm.SourceCulture = _providerSettings.SourceLanguage;
}
if (columnLetter == _providerSettings.TargetColumn.ToUpper())
{
excelTerm.Target = cell.Text;
excelTerm.TargetCulture = _providerSettings.TargetLanguage;
}
if (columnLetter == _providerSettings.ApprovedColumn?.ToUpper())
{
excelTerm.Approved = cell.Text;
}
}
