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 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 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 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>(string column, uint line)
{
return(GetValue <T>(BaseAZ.Parse(column), line));
}
public string GetValue(string column, uint line)
{
return(GetValue(BaseAZ.Parse(column), line));
}