public void ShouldGetAllTypesOfValues()
{
using (var rs = GetType().Assembly.GetManifestResourceStream("CcExcel.Test.Resources.AllTypes.xlsx"))
using (var doc = SpreadsheetDocument.Open(rs, false))
{
var sd = SpreadsheetHelper.GetSheetData(doc, "Sheet1");
var b = BaseAZ.Parse("B");
Assert.AreEqual("general", SpreadsheetHelper.GetValue(doc, sd, null, b, 2));
Assert.AreEqual("12.4568", SpreadsheetHelper.GetValue(doc, sd, null, b, 3));
Assert.AreEqual("45.25", SpreadsheetHelper.GetValue(doc, sd, null, b, 4));
Assert.AreEqual("18.56", SpreadsheetHelper.GetValue(doc, sd, null, b, 5));
Assert.AreEqual("32408", SpreadsheetHelper.GetValue(doc, sd, null, b, 6));
Assert.AreEqual("42952", SpreadsheetHelper.GetValue(doc, sd, null, b, 7));
Assert.AreEqual("0.49", SpreadsheetHelper.GetValue(doc, sd, null, b, 8));
Assert.AreEqual("0.1845", SpreadsheetHelper.GetValue(doc, sd, null, b, 9));
Assert.AreEqual("0.2", SpreadsheetHelper.GetValue(doc, sd, null, b, 10));
Assert.AreEqual("10500000", SpreadsheetHelper.GetValue(doc, sd, null, b, 11));
Assert.AreEqual("text1", SpreadsheetHelper.GetValue(doc, sd, null, b, 12));
Assert.AreEqual("text2", SpreadsheetHelper.GetValue(doc, sd, null, b, 13));
Assert.AreEqual("text1", SpreadsheetHelper.GetValue(doc, sd, null, b, 14));
Assert.AreEqual("text2", SpreadsheetHelper.GetValue(doc, sd, null, b, 15));
Assert.AreEqual("a", SpreadsheetHelper.GetValue(doc, sd, null, b, 16));
Assert.AreEqual("1", SpreadsheetHelper.GetValue(doc, sd, null, b, 17));
Assert.IsNull(SpreadsheetHelper.GetValue(doc, sd, null, b, 18));
}
}
public void ShouldSetCellStyles()
{
using (var rs = GetType().Assembly.GetManifestResourceStream("CcExcel.Test.Resources.Styles.xlsx"))
using (var ms = new MemoryStream())
{
rs.CopyTo(ms);
ms.Position = 0;
using (var excel = new Excel(ms, ExcelMode.Open))
{
var style = excel["Sheet1"].Styles["B", 2];
for (var line = 3; line <= 6; line++)
{
excel["Sheet1"].Styles["B", line] = style;
}
excel.Save();
}
using (var excel = new Excel(ms, ExcelMode.OpenReadOnly))
{
var sheet = SpreadsheetHelper.GetSheetData(excel.OpenXmlDocument, "Sheet1");
var b = BaseAZ.Parse("B");
Assert.AreEqual("3", SpreadsheetHelper.GetCell(sheet, b, 2).StyleIndex.InnerText);
Assert.AreEqual("3", SpreadsheetHelper.GetCell(sheet, b, 3).StyleIndex.InnerText);
Assert.AreEqual("3", SpreadsheetHelper.GetCell(sheet, b, 4).StyleIndex.InnerText);
Assert.AreEqual("3", SpreadsheetHelper.GetCell(sheet, b, 5).StyleIndex.InnerText);
Assert.AreEqual("3", SpreadsheetHelper.GetCell(sheet, b, 6).StyleIndex.InnerText);
}
DumpGeneratedExcelFiles.Dump(ms);
}
}
public void ShouldInsertInSharedStringTable()
{
using (var rs = GetType().Assembly.GetManifestResourceStream("CcExcel.Test.Resources.AllTypes.xlsx"))
using (var ms = new MemoryStream())
{
rs.CopyTo(ms);
var doc = SpreadsheetDocument.Open(ms, true);
var newId = SpreadsheetHelper.InsertInSharedString(doc, "new value");
Assert.AreEqual(4, newId);
var sheetData = SpreadsheetHelper.GetSheetData(doc, "Sheet1");
var cell = SpreadsheetHelper.GetCell(sheetData, BaseAZ.Parse("B"), 12);
cell.CellValue = new Spreadsheet.CellValue("4");
doc.Save();
Assert.AreEqual("4", cell.InnerText);
DumpGeneratedExcelFiles.Dump(ms);
}
}
public static Cell GetCell(SheetData sheetData, BaseAZ column, uint line, bool createIfDoesntExists = false)
{
var row = GetRow(sheetData, line, createIfDoesntExists);
if (row == null)
{
return(null);
}
var cellReference = column.ToString() + line;
var cell = row.Elements <Cell>().FirstOrDefault(c => c.CellReference == cellReference);
if (cell != null)
{
return(cell);
}
else if (!createIfDoesntExists)
{
return(null);
}
cell = new Cell {
CellReference = cellReference
};
// Se nao existir outras celulas pode inserir em qualquer lugar.
if (!row.Elements <Cell>().Any())
{
row.AppendChild(cell);
return(cell);
}
// Caso existam outras celular precisa inserir na posicao correta.
var cells = row.Elements <Cell>()
.Select(s => new
{
Ref = CellReference.Parse(s.CellReference),
Cell = s
})
.OrderBy(o => o.Ref.Column)
.ToList();
var before = cells.LastOrDefault(w => w.Ref.Column < column);
if (before != null) // Existem linhas anteriores a que sera inserida.
{
row.InsertAfter(cell, before.Cell);
}
else // Nao existem nenhuma linha anterior a que sera inserida.
{
var after = cells.First(f => f.Ref.Column > column);
// Insere antes do primeiro.
row.InsertBefore(cell, after.Cell);
}
return(cell);
}
public string GetValue(BaseAZ column, uint line)
{
var cell = GetCell(column.ToString() + line);
if (cell?.DataType?.Value == CellValues.SharedString)
{
return(_stringTable?.SharedStringTable.ElementAt(int.Parse(cell.InnerText)).InnerText);
}
return(cell?.InnerText);
}
public void ShouldGetCell()
{
using (var rs = GetType().Assembly.GetManifestResourceStream("CcExcel.Test.Resources.AllTypes.xlsx"))
using (var doc = SpreadsheetDocument.Open(rs, false))
{
var sheetData = SpreadsheetHelper.GetSheetData(doc, "Sheet1");
var cell = SpreadsheetHelper.GetCell(sheetData, BaseAZ.Parse("B"), 2);
Assert.IsNotNull(cell);
}
}
public void ShouldCreateSheetCells()
{
using (var ms = new MemoryStream())
{
using (var doc = SpreadsheetDocument.Create(ms, SpreadsheetDocumentType.Workbook, true))
{
var sheetData = SpreadsheetHelper.GetSheetData(doc, "Sheet3", createIfDoesntExists: true);
Action <string, uint, int> setCell = (column, line, value) =>
{
var azColumn = BaseAZ.Parse(column);
var valueStr = value.ToString();
SpreadsheetHelper.GetCell(sheetData, azColumn, line, createIfDoesntExists: true).CellValue
= new DocumentFormat.OpenXml.Spreadsheet.CellValue(valueStr);
};
setCell("C", 3, 5);
setCell("C", 1, 8);
setCell("C", 5, 2);
setCell("A", 3, 4);
setCell("A", 1, 7);
setCell("A", 5, 1);
setCell("E", 3, 6);
setCell("E", 1, 9);
setCell("E", 5, 3);
doc.Save();
doc.Dispose();
}
using (var doc = SpreadsheetDocument.Open(ms, true))
{
var sheetData = SpreadsheetHelper.GetSheetData(doc, "Sheet3");
Assert.AreEqual("1", SpreadsheetHelper.GetCell(sheetData, BaseAZ.Parse("A"), 5).CellValue.InnerText);
Assert.AreEqual("2", SpreadsheetHelper.GetCell(sheetData, BaseAZ.Parse("C"), 5).CellValue.InnerText);
Assert.AreEqual("3", SpreadsheetHelper.GetCell(sheetData, BaseAZ.Parse("E"), 5).CellValue.InnerText);
Assert.AreEqual("4", SpreadsheetHelper.GetCell(sheetData, BaseAZ.Parse("A"), 3).CellValue.InnerText);
Assert.AreEqual("5", SpreadsheetHelper.GetCell(sheetData, BaseAZ.Parse("C"), 3).CellValue.InnerText);
Assert.AreEqual("6", SpreadsheetHelper.GetCell(sheetData, BaseAZ.Parse("E"), 3).CellValue.InnerText);
Assert.AreEqual("7", SpreadsheetHelper.GetCell(sheetData, BaseAZ.Parse("A"), 1).CellValue.InnerText);
Assert.AreEqual("8", SpreadsheetHelper.GetCell(sheetData, BaseAZ.Parse("C"), 1).CellValue.InnerText);
Assert.AreEqual("9", SpreadsheetHelper.GetCell(sheetData, BaseAZ.Parse("E"), 1).CellValue.InnerText);
doc.Dispose();
}
DumpGeneratedExcelFiles.Dump(ms);
}
}
public static CellReference Parse(string cellReference)
{
var match = Regex.Match(cellReference, @"^(?<column>[A-Za-z]+)(?<line>[1-9]\d*)$");
if (!match.Success)
{
throw new ArgumentException(Texts.TheParameterCellReferenceWasNotInACorrectFormat, nameof(cellReference));
}
var column = BaseAZ.Parse(match.Groups["column"].Value);
var line = uint.Parse(match.Groups["line"].Value);
return(new CellReference(column, line));
}
public void ShouldSetCells()
{
using (var ms = new MemoryStream())
{
using (var doc = SpreadsheetDocument.Create(ms, SpreadsheetDocumentType.Workbook, true))
{
var sheet = SpreadsheetHelper.GetSheetData(doc, "Sheet3", createIfDoesntExists: true);
SpreadsheetHelper.SetValue(doc, null, "A", CellValues.SharedString, sheet, BaseAZ.Parse("A"), 5);
SpreadsheetHelper.SetValue(doc, null, "B", CellValues.SharedString, sheet, BaseAZ.Parse("C"), 5);
SpreadsheetHelper.SetValue(doc, null, "C", CellValues.SharedString, sheet, BaseAZ.Parse("E"), 5);
SpreadsheetHelper.SetValue(doc, null, "125", null, sheet, BaseAZ.Parse("A"), 3);
SpreadsheetHelper.SetValue(doc, null, "458", CellValues.SharedString, sheet, BaseAZ.Parse("C"), 3);
SpreadsheetHelper.SetValue(doc, null, "4.586", CellValues.SharedString, sheet, BaseAZ.Parse("E"), 3);
SpreadsheetHelper.SetValue(doc, null, "1", CellValues.Boolean, sheet, BaseAZ.Parse("A"), 1);
SpreadsheetHelper.SetValue(doc, null, "0", CellValues.Boolean, sheet, BaseAZ.Parse("C"), 1);
SpreadsheetHelper.SetValue(doc, null, "info", CellValues.SharedString, sheet, BaseAZ.Parse("E"), 1);
doc.Save();
doc.Dispose();
}
using (var doc = SpreadsheetDocument.Open(ms, true))
{
var sheetData = SpreadsheetHelper.GetSheetData(doc, "Sheet3");
Assert.AreEqual("A", SpreadsheetHelper.GetValue(doc, sheetData, null, BaseAZ.Parse("A"), 5));
Assert.AreEqual("B", SpreadsheetHelper.GetValue(doc, sheetData, null, BaseAZ.Parse("C"), 5));
Assert.AreEqual("C", SpreadsheetHelper.GetValue(doc, sheetData, null, BaseAZ.Parse("E"), 5));
Assert.AreEqual("125", SpreadsheetHelper.GetValue(doc, sheetData, null, BaseAZ.Parse("A"), 3));
Assert.AreEqual("458", SpreadsheetHelper.GetValue(doc, sheetData, null, BaseAZ.Parse("C"), 3));
Assert.AreEqual("4.586", SpreadsheetHelper.GetValue(doc, sheetData, null, BaseAZ.Parse("E"), 3));
Assert.AreEqual("1", SpreadsheetHelper.GetValue(doc, sheetData, null, BaseAZ.Parse("A"), 1));
Assert.AreEqual("0", SpreadsheetHelper.GetValue(doc, sheetData, null, BaseAZ.Parse("C"), 1));
Assert.AreEqual("info", SpreadsheetHelper.GetValue(doc, sheetData, null, BaseAZ.Parse("E"), 1));
}
DumpGeneratedExcelFiles.Dump(ms);
}
}
private static void WriteSheet(Dictionary <string, Bucket> diffs, Func <Bucket, object> func, string sheetName, string filePath)
{
using (FileStream datedFile = new FileStream(filePath, FileMode.Open))
{
Excel datedExcel = new Excel(datedFile);
Sheet datedSheet = datedExcel.GetSheet(sheetName);
uint line = 0;
CellStyle textStyle = datedSheet.GetCellStyle("A", line + 1);
CellStyle dateStyle = datedSheet.GetCellStyle("B", line + 1);
CellStyle numberStyle = datedSheet.GetCellStyle("B", line + 2);
datedSheet.ClearAllSheetDataBelow(line++);
BaseAZ column = BaseAZ.Parse("A");
datedSheet.SetCell(column++, line, textStyle, "Reason");
DateTime[] dates = reasonLookup[reasonStrings[0]].Keys.OrderByDescending(x => x).ToArray();
foreach (DateTime date in dates)
{
datedSheet.SetCell(column++, line, dateStyle, ToExcelDateValue(date));
}
line++;
foreach (string reasonString in reasonStrings)
{
column = BaseAZ.Parse("A");
datedSheet.SetCell(column++, line, textStyle, GetReasonString(diffs, func, reasonString));
foreach (DateTime date in dates)
{
datedSheet.SetCell(column++, line, numberStyle, func(reasonLookup[reasonString][date]));
}
line++;
}
datedExcel.Save();
}
}
public T GetValue <T>(BaseAZ column, uint line)
{
var strValue = GetValue(column, line);
if (string.IsNullOrEmpty(strValue))
{
return(default(T));
}
if (typeof(T) == typeof(string))
{
return((T)(object)strValue);
}
var enUs = new CultureInfo("en-US");
var style = NumberStyles.Any;
var aliasType = GetAlias(typeof(T));
switch (aliasType)
{
case "bool":
case "bool?":
if (int.TryParse(strValue, style, enUs, out var boolValue))
{
return((T)(object)(boolValue == 1));
}
break;
case "char":
case "char?":
return((T)(object)strValue?.First());
case "short":
case "short?":
if (short.TryParse(strValue, style, enUs, out var shortValue))
{
return((T)(object)shortValue);
}
break;
case "int":
case "int?":
if (int.TryParse(strValue, style, enUs, out var intValue))
{
return((T)(object)intValue);
}
break;
case "long":
case "long?":
if (long.TryParse(strValue, style, enUs, out var longValue))
{
return((T)(object)longValue);
}
break;
case "float":
case "float?":
if (float.TryParse(strValue, style, enUs, out var floatValue))
{
return((T)(object)floatValue);
}
break;
case "double":
case "double?":
if (double.TryParse(strValue, style, enUs, out var doubleValue))
{
return((T)(object)doubleValue);
}
break;
case "decimal":
case "decimal?":
if (decimal.TryParse(strValue, style, enUs, out var decimalValue))
{
return((T)(object)decimalValue);
}
break;
case "DateTime":
case "DateTime?":
if (double.TryParse(strValue, style, enUs, out var dateTimeValue))
{
return((T)(object)DateTime.FromOADate(dateTimeValue));
}
break;
case "TimeSpan":
case "TimeSpan?":
if (double.TryParse(strValue, style, enUs, out var timeSpanValue))
{
var dateTime = DateTime.FromOADate(timeSpanValue);
return((T)(object)(dateTime - dateTime.Date));
}
break;
default:
throw new NotSupportedException($"GetValue has no support to {typeof(T)}.");
}
return((T)(object)null);
}
public T GetValue <T>(string column, uint line)
{
return(GetValue <T>(BaseAZ.Parse(column), line));
}
public string GetValue(string column, uint line)
{
return(GetValue(BaseAZ.Parse(column), line));
}
public CellReference(BaseAZ column, uint line)
{
_column = column;
_line = line;
}
