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; }