diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/XLCell.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/XLCell.cs index 1666c10..439a407 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/XLCell.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/XLCell.cs @@ -11,6 +11,7 @@ { internal class XLCell : IXLCell { + public static readonly DateTime baseDate = new DateTime(1899, 12, 30); XLWorksheet worksheet; public XLCell(IXLAddress address, IXLStyle defaultStyle, XLWorksheet worksheet) { @@ -54,8 +55,16 @@ } else if (dataType == XLCellValues.DateTime || IsDateFormat()) { - String format = GetFormat(); - return DateTime.FromOADate(Double.Parse(cellValue)).ToString(format); + TimeSpan timeSpan; + if (TimeSpan.TryParse(cellValue, out timeSpan)) + { + return timeSpan.ToString(); + } + else + { + String format = GetFormat(); + return DateTime.FromOADate(Double.Parse(cellValue)).ToString(format); + } } else if (dataType == XLCellValues.Number) { @@ -297,7 +306,6 @@ { if (DateTime.TryParse(val, out dtTest)) { - val = dtTest.ToOADate().ToString(); } else if (!Double.TryParse(val, out dTest)) @@ -306,13 +314,28 @@ } if (Style.NumberFormat.Format == String.Empty && Style.NumberFormat.NumberFormatId == 0) - Style.NumberFormat.NumberFormatId = 14; + { + if (dtTest.Date == dtTest) + Style.NumberFormat.NumberFormatId = 14; + else + Style.NumberFormat.NumberFormatId = 22; + } + } else if (dataType == XLCellValues.Number) { - if (!Double.TryParse(val, out dTest)) - throw new ArgumentException("'" + val + "' is not a Numeric type."); - + if (value is TimeSpan) + { + var timeSpan = (TimeSpan)value; + val = baseDate.Add(timeSpan).ToOADate().ToString(); + if (Style.NumberFormat.Format == String.Empty && Style.NumberFormat.NumberFormatId == 0) + Style.NumberFormat.NumberFormatId = 46; + } + else + { + if (!Double.TryParse(val, out dTest)) + throw new ArgumentException("'" + val + "' is not a Numeric type."); + } } } else @@ -322,6 +345,13 @@ val = val.Substring(1, val.Length - 1); dataType = XLCellValues.Text; } + else if (value is TimeSpan) + { + var timeSpan = (TimeSpan)value; + val = baseDate.Add(timeSpan).ToOADate().ToString(); + dataType = XLCellValues.Number; + Style.NumberFormat.NumberFormatId = 46; + } else if (Double.TryParse(val, out dTest)) { dataType = XLCellValues.Number; @@ -329,7 +359,12 @@ else if (DateTime.TryParse(val, out dtTest)) { dataType = XLCellValues.DateTime; - Style.NumberFormat.NumberFormatId = 14; + + if (dtTest.Date == dtTest) + Style.NumberFormat.NumberFormatId = 14; + else + Style.NumberFormat.NumberFormatId = 22; + val = dtTest.ToOADate().ToString(); } else if (Boolean.TryParse(val, out bTest)) @@ -386,6 +421,7 @@ initialized = true; if (cellValue.Length > 0) { + TimeSpan tsTest; if (value == XLCellValues.Boolean) { Boolean bTest; @@ -394,45 +430,73 @@ else cellValue = cellValue == "0" || String.IsNullOrEmpty(cellValue) ? "0" : "1"; } - else if (value == XLCellValues.DateTime) + else if (value == XLCellValues.DateTime && !TimeSpan.TryParse(cellValue, out tsTest)) { DateTime dtTest; Double dblTest; - if (DateTime.TryParse(cellValue, out dtTest)) - { - cellValue = dtTest.ToOADate().ToString(); - } - else if (Double.TryParse(cellValue, out dblTest)) - { - cellValue = dblTest.ToString(); - } - else - { - throw new ArgumentException("Cannot set data type to DateTime because '" + cellValue + "' is not recognized as a date."); - } + if (DateTime.TryParse(cellValue, out dtTest)) + { + cellValue = dtTest.ToOADate().ToString(); + } + else if (Double.TryParse(cellValue, out dblTest)) + { + cellValue = dblTest.ToString(); + } + else + { + throw new ArgumentException("Cannot set data type to DateTime because '" + cellValue + "' is not recognized as a date."); + } - if (Style.NumberFormat.Format == String.Empty && Style.NumberFormat.NumberFormatId == 0) - Style.NumberFormat.NumberFormatId = 14; + if (Style.NumberFormat.Format == String.Empty && Style.NumberFormat.NumberFormatId == 0) + if (((Int32)Double.Parse(cellValue)).ToString() == cellValue) + Style.NumberFormat.NumberFormatId = 14; + else + Style.NumberFormat.NumberFormatId = 22; } else if (value == XLCellValues.Number) { - cellValue = Double.Parse(cellValue).ToString(); - //if (Style.NumberFormat.Format == String.Empty ) - // Style.NumberFormat.NumberFormatId = 0; + if (TimeSpan.TryParse(cellValue, out tsTest)) + { + cellValue = baseDate.Add(tsTest).ToOADate().ToString(); + if (Style.NumberFormat.Format == String.Empty && Style.NumberFormat.NumberFormatId == 0) + Style.NumberFormat.NumberFormatId = 46; + } + else + { + cellValue = Double.Parse(cellValue).ToString(); + } } else { + var formatCodes = GetFormatCodes(); if (dataType == XLCellValues.Boolean) { cellValue = (cellValue != "0").ToString(); } else if (dataType == XLCellValues.Number) { - cellValue = Double.Parse(cellValue).ToString(Style.NumberFormat.Format); + if (Style.NumberFormat.NumberFormatId == 46) + { + cellValue = (DateTime.FromOADate(Double.Parse(cellValue)) - baseDate).ToString(); + } + else + { + String format; + if (Style.NumberFormat.NumberFormatId > 0) + format = formatCodes[Style.NumberFormat.NumberFormatId]; + else + format = Style.NumberFormat.Format; + cellValue = Double.Parse(cellValue).ToString(format); + } } else if (dataType == XLCellValues.DateTime) { - cellValue = DateTime.FromOADate(Double.Parse(cellValue)).ToString(Style.NumberFormat.Format); + String format; + if (Style.NumberFormat.NumberFormatId > 0) + format = formatCodes[Style.NumberFormat.NumberFormatId]; + else + format = Style.NumberFormat.Format; + cellValue = DateTime.FromOADate(Double.Parse(cellValue)).ToString(format); } } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook.cs index 3f720c0..078f984 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook.cs @@ -12,6 +12,8 @@ public enum XLReferenceStyle { R1C1, A1, Default }; public partial class XLWorkbook { + private enum XLLoadSource { New, File, Stream }; + private XLLoadSource loadSource = XLLoadSource.New; public XLWorkbook() { DefaultRowHeight = 15; @@ -32,10 +34,18 @@ private String originalFile; public XLWorkbook(String file): this() { + loadSource = XLLoadSource.File; originalFile = file; Load(file); } + public XLWorkbook(Stream stream) + : this() + { + loadSource = XLLoadSource.Stream; + Load(stream); + } + #region IXLWorkbook Members public IXLWorksheets Worksheets { get; private set; } @@ -53,10 +63,12 @@ public void Save() { - if (originalFile == null) - throw new Exception("This is a new file, please use one of the following methods: SaveAs, MergeInto, or SaveChangesTo"); - - MergeInto(originalFile); + if (loadSource == XLLoadSource.New) + throw new Exception("This is a new file, please use one of the SaveAs method."); + else if (loadSource == XLLoadSource.Stream) + throw new Exception("The file was loaded from a stream, please use one of the SaveAs method."); + else + CreatePackage(originalFile); } public void SaveAs(String file) @@ -69,15 +81,9 @@ CreatePackage(file); } - public void MergeInto(String file) + public void SaveAs(Stream stream) { - CreatePackage(file); - } - - public void SaveChangesTo(String file) - { - if (File.Exists(file)) File.Delete(file); - CreatePackage(file); + CreatePackage(stream); } public IXLStyle Style { get; set; } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Load.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Load.cs index 4c571c3..c7ceb19 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Load.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Load.cs @@ -21,403 +21,416 @@ { private void Load(String file) { - LoadSheets(file); } - + private void Load(Stream stream) + { + LoadSheets(stream); + } private void LoadSheets(String fileName) { - // Open file as read-only. using (SpreadsheetDocument dSpreadsheet = SpreadsheetDocument.Open(fileName, false)) { - SetProperties(dSpreadsheet); - SharedStringItem[] sharedStrings = null; - if (dSpreadsheet.WorkbookPart.GetPartsOfType().Count() > 0) + LoadSpreadsheetDocument(dSpreadsheet); + } + } + private void LoadSheets(Stream stream) + { + using (SpreadsheetDocument dSpreadsheet = SpreadsheetDocument.Open(stream, false)) + { + LoadSpreadsheetDocument(dSpreadsheet); + } + } + private void LoadSpreadsheetDocument(SpreadsheetDocument dSpreadsheet) + { + SetProperties(dSpreadsheet); + SharedStringItem[] sharedStrings = null; + if (dSpreadsheet.WorkbookPart.GetPartsOfType().Count() > 0) + { + SharedStringTablePart shareStringPart = dSpreadsheet.WorkbookPart.GetPartsOfType().First(); + sharedStrings = shareStringPart.SharedStringTable.Elements().ToArray(); + } + + var referenceMode = dSpreadsheet.WorkbookPart.Workbook.CalculationProperties.ReferenceMode; + if (referenceMode != null) + { + ReferenceStyle = referenceModeValues.Single(p => p.Value == referenceMode.Value).Key; + } + + var calculateMode = dSpreadsheet.WorkbookPart.Workbook.CalculationProperties.CalculationMode; + if (calculateMode != null) + { + CalculateMode = calculateModeValues.Single(p => p.Value == calculateMode.Value).Key; + } + + if (dSpreadsheet.ExtendedFilePropertiesPart.Properties.Elements().Count() > 0) + Properties.Company = dSpreadsheet.ExtendedFilePropertiesPart.Properties.GetFirstChild().Text; + + if (dSpreadsheet.ExtendedFilePropertiesPart.Properties.Elements().Count() > 0) + Properties.Manager = dSpreadsheet.ExtendedFilePropertiesPart.Properties.GetFirstChild().Text; + + + var workbookStylesPart = (WorkbookStylesPart)dSpreadsheet.WorkbookPart.WorkbookStylesPart; + var s = (Stylesheet)workbookStylesPart.Stylesheet; + var numberingFormats = (NumberingFormats)s.NumberingFormats; + Fills fills = (Fills)s.Fills; + Borders borders = (Borders)s.Borders; + Fonts fonts = (Fonts)s.Fonts; + + var sheets = dSpreadsheet.WorkbookPart.Workbook.Sheets; + + foreach (var sheet in sheets) + { + var sharedFormulas = new Dictionary(); + + Sheet dSheet = ((Sheet)sheet); + WorksheetPart worksheetPart = (WorksheetPart)dSpreadsheet.WorkbookPart.GetPartById(dSheet.Id); + + var sheetName = dSheet.Name; + + var ws = (XLWorksheet)Worksheets.Add(sheetName); + + var sheetFormatProperties = (SheetFormatProperties)worksheetPart.Worksheet.Descendants().First(); + if (sheetFormatProperties.DefaultRowHeight != null) + ws.RowHeight = sheetFormatProperties.DefaultRowHeight; + + if (sheetFormatProperties.DefaultColumnWidth != null) + ws.ColumnWidth = sheetFormatProperties.DefaultColumnWidth; + + foreach (var mCell in worksheetPart.Worksheet.Descendants()) { - SharedStringTablePart shareStringPart = dSpreadsheet.WorkbookPart.GetPartsOfType().First(); - sharedStrings = shareStringPart.SharedStringTable.Elements().ToArray(); + var mergeCell = (MergeCell)mCell; + ws.Range(mergeCell.Reference).Merge(); } - var referenceMode = dSpreadsheet.WorkbookPart.Workbook.CalculationProperties.ReferenceMode; - if (referenceMode != null) + Column wsDefaultColumn = null; + var defaultColumns = worksheetPart.Worksheet.Descendants().Where(c => c.Max == XLWorksheet.MaxNumberOfColumns); + if (defaultColumns.Count() > 0) + wsDefaultColumn = defaultColumns.Single(); + + if (wsDefaultColumn != null && wsDefaultColumn.Width != null) ws.ColumnWidth = wsDefaultColumn.Width; + + Int32 styleIndexDefault = wsDefaultColumn != null && wsDefaultColumn.Style != null ? Int32.Parse(wsDefaultColumn.Style.InnerText) : -1; + if (styleIndexDefault >= 0) { - ReferenceStyle = referenceModeValues.Single(p => p.Value == referenceMode.Value).Key; + ApplyStyle(ws, styleIndexDefault, s, fills, borders, fonts, numberingFormats); } - var calculateMode = dSpreadsheet.WorkbookPart.Workbook.CalculationProperties.CalculationMode; - if (calculateMode != null) + foreach (var col in worksheetPart.Worksheet.Descendants()) { - CalculateMode = calculateModeValues.Single(p => p.Value == calculateMode.Value).Key; - } - - if (dSpreadsheet.ExtendedFilePropertiesPart.Properties.Elements().Count() > 0) - Properties.Company = dSpreadsheet.ExtendedFilePropertiesPart.Properties.GetFirstChild().Text; - - if (dSpreadsheet.ExtendedFilePropertiesPart.Properties.Elements().Count() > 0) - Properties.Manager = dSpreadsheet.ExtendedFilePropertiesPart.Properties.GetFirstChild().Text; - - - var workbookStylesPart = (WorkbookStylesPart)dSpreadsheet.WorkbookPart.WorkbookStylesPart; - var s = (Stylesheet)workbookStylesPart.Stylesheet; - var numberingFormats = (NumberingFormats)s.NumberingFormats; - Fills fills = (Fills)s.Fills; - Borders borders = (Borders)s.Borders; - Fonts fonts = (Fonts)s.Fonts; - - var sheets = dSpreadsheet.WorkbookPart.Workbook.Sheets; - - foreach (var sheet in sheets) - { - var sharedFormulas = new Dictionary(); - - Sheet dSheet = ((Sheet)sheet); - WorksheetPart worksheetPart = (WorksheetPart)dSpreadsheet.WorkbookPart.GetPartById(dSheet.Id); - - var sheetName = dSheet.Name; - - var ws = (XLWorksheet)Worksheets.Add(sheetName); - - var sheetFormatProperties = (SheetFormatProperties)worksheetPart.Worksheet.Descendants().First(); - if (sheetFormatProperties.DefaultRowHeight != null) - ws.RowHeight = sheetFormatProperties.DefaultRowHeight; - - if (sheetFormatProperties.DefaultColumnWidth != null) - ws.ColumnWidth = sheetFormatProperties.DefaultColumnWidth; - - foreach (var mCell in worksheetPart.Worksheet.Descendants()) + IXLStylized toApply; + if (col.Max != XLWorksheet.MaxNumberOfColumns) { - var mergeCell = (MergeCell)mCell; - ws.Range(mergeCell.Reference).Merge(); - } - - Column wsDefaultColumn = null; - var defaultColumns = worksheetPart.Worksheet.Descendants().Where(c => c.Max == XLWorksheet.MaxNumberOfColumns); - if (defaultColumns.Count() > 0) - wsDefaultColumn = defaultColumns.Single(); - - if (wsDefaultColumn != null && wsDefaultColumn.Width != null) ws.ColumnWidth = wsDefaultColumn.Width; - - Int32 styleIndexDefault = wsDefaultColumn != null && wsDefaultColumn.Style != null ? Int32.Parse(wsDefaultColumn.Style.InnerText) : -1; - if (styleIndexDefault >= 0) - { - ApplyStyle(ws, styleIndexDefault, s, fills, borders, fonts, numberingFormats); - } - - foreach (var col in worksheetPart.Worksheet.Descendants()) - { - IXLStylized toApply; - if (col.Max != XLWorksheet.MaxNumberOfColumns) - { - toApply = ws.Columns(col.Min, col.Max); - var xlColumns = (XLColumns)toApply; - if (col.Width != null) - xlColumns.Width = col.Width; - else - xlColumns.Width = ws.ColumnWidth; - - if (col.Hidden != null && col.Hidden) - xlColumns.Hide(); - - if (col.Collapsed != null && col.Collapsed) - xlColumns.Collapse(); - - if (col.OutlineLevel != null) - xlColumns.ForEach(c=> c.OutlineLevel = col.OutlineLevel); - - Int32 styleIndex = col.Style != null ? Int32.Parse(col.Style.InnerText) : -1; - if (styleIndex > 0) - { - ApplyStyle(toApply, styleIndex, s, fills, borders, fonts, numberingFormats); - } - else - { - toApply.Style = DefaultStyle; - } - } - } - - foreach (var row in worksheetPart.Worksheet.Descendants().Where(r=>r.CustomFormat != null && r.CustomFormat).Select(r=>r)) - { - //var dRow = (Column)col; - var xlRow = ws.Row((Int32)row.RowIndex.Value); - if (row.Height != null) - xlRow.Height = row.Height; + toApply = ws.Columns(col.Min, col.Max); + var xlColumns = (XLColumns)toApply; + if (col.Width != null) + xlColumns.Width = col.Width; else - xlRow.Height = ws.RowHeight; + xlColumns.Width = ws.ColumnWidth; - if (row.Hidden != null && row.Hidden) - xlRow.Hide(); + if (col.Hidden != null && col.Hidden) + xlColumns.Hide(); - if (row.Collapsed != null && row.Collapsed) - xlRow.Collapse(); + if (col.Collapsed != null && col.Collapsed) + xlColumns.Collapse(); - if (row.OutlineLevel != null) - xlRow.OutlineLevel = row.OutlineLevel; + if (col.OutlineLevel != null) + xlColumns.ForEach(c => c.OutlineLevel = col.OutlineLevel); - Int32 styleIndex = row.StyleIndex != null ? Int32.Parse(row.StyleIndex.InnerText) : -1; + Int32 styleIndex = col.Style != null ? Int32.Parse(col.Style.InnerText) : -1; if (styleIndex > 0) { - ApplyStyle(xlRow, styleIndex, s, fills, borders, fonts, numberingFormats); + ApplyStyle(toApply, styleIndex, s, fills, borders, fonts, numberingFormats); } else { - xlRow.Style = DefaultStyle; - } - } - - foreach (var cell in worksheetPart.Worksheet.Descendants() - .Where(c=>c.CellFormula != null && c.CellFormula.SharedIndex != null && c.CellFormula.Reference != null) - .Select(c=>c)) - { - sharedFormulas.Add(cell.CellFormula.SharedIndex.Value, cell.CellFormula); - } - foreach (var cell in worksheetPart.Worksheet.Descendants()) - { - var dCell = (Cell)cell; - Int32 styleIndex = dCell.StyleIndex != null ? Int32.Parse(dCell.StyleIndex.InnerText) : 0; - var xlCell = ws.CellFast(dCell.CellReference); - if (styleIndex > 0) - { - //styleIndex = Int32.Parse(dCell.StyleIndex.InnerText); - ApplyStyle(xlCell, styleIndex, s, fills, borders, fonts, numberingFormats); - } - else - { - xlCell.Style = DefaultStyle; - } - - if(dCell.CellFormula != null) - { - if (dCell.CellFormula.SharedIndex != null) - xlCell.FormulaA1 = sharedFormulas[dCell.CellFormula.SharedIndex.Value].Text; - else - xlCell.FormulaA1 = dCell.CellFormula.Text; - } - else if (dCell.DataType != null) - { - if (dCell.DataType == CellValues.SharedString) - { - xlCell.DataType = XLCellValues.Text; - if (dCell.CellValue != null) - { - if (!String.IsNullOrWhiteSpace(dCell.CellValue.Text)) - xlCell.Value = sharedStrings[Int32.Parse(dCell.CellValue.Text)].InnerText; - else - xlCell.Value = dCell.CellValue.Text; - } - else - { - xlCell.Value = String.Empty; - } - } - else if (dCell.DataType == CellValues.Date) - { - xlCell.DataType = XLCellValues.DateTime; - xlCell.Value = DateTime.FromOADate(Double.Parse(dCell.CellValue.Text)); - } - else if (dCell.DataType == CellValues.Boolean) - { - xlCell.DataType = XLCellValues.Boolean; - xlCell.Value = (dCell.CellValue.Text == "1"); - } - else if (dCell.DataType == CellValues.Number) - { - xlCell.DataType = XLCellValues.Number; - xlCell.Value = dCell.CellValue.Text; - } - } - else if (dCell.CellValue != null) - { - //var styleIndex = Int32.Parse(dCell.StyleIndex.InnerText); - var numberFormatId = ((CellFormat)((CellFormats)s.CellFormats).ElementAt(styleIndex)).NumberFormatId; //. [styleIndex].NumberFormatId; - ws.Cell(dCell.CellReference).Value = dCell.CellValue.Text; - ws.Cell(dCell.CellReference).Style.NumberFormat.NumberFormatId = Int32.Parse(numberFormatId); - } - } - - var printOptionsQuery = worksheetPart.Worksheet.Descendants(); - if (printOptionsQuery.Count() > 1) - { - var printOptions = (PrintOptions)printOptionsQuery.First(); - if (printOptions.GridLines != null) - ws.PageSetup.ShowGridlines = printOptions.GridLines; - if (printOptions.HorizontalCentered != null) - ws.PageSetup.CenterHorizontally = printOptions.HorizontalCentered; - if (printOptions.VerticalCentered != null) - ws.PageSetup.CenterVertically = printOptions.VerticalCentered; - if (printOptions.Headings != null) - ws.PageSetup.ShowRowAndColumnHeadings = printOptions.Headings; - } - - var pageMarginsQuery = worksheetPart.Worksheet.Descendants(); - if (pageMarginsQuery.Count() > 0) - { - var pageMargins = (PageMargins)pageMarginsQuery.First(); - if (pageMargins.Bottom != null) - ws.PageSetup.Margins.Bottom = pageMargins.Bottom; - if (pageMargins.Footer != null) - ws.PageSetup.Margins.Footer = pageMargins.Footer; - if (pageMargins.Header != null) - ws.PageSetup.Margins.Header = pageMargins.Header; - if (pageMargins.Left != null) - ws.PageSetup.Margins.Left = pageMargins.Left; - if (pageMargins.Right != null) - ws.PageSetup.Margins.Right = pageMargins.Right; - if (pageMargins.Top != null) - ws.PageSetup.Margins.Top = pageMargins.Top; - } - - var pageSetupQuery = worksheetPart.Worksheet.Descendants(); - if (pageSetupQuery.Count() > 0) - { - var pageSetup = (PageSetup)pageSetupQuery.First(); - if (pageSetup.PaperSize !=null) - ws.PageSetup.PaperSize = (XLPaperSize)Int32.Parse(pageSetup.PaperSize.InnerText); - if (pageSetup.Scale != null) - { - ws.PageSetup.Scale = Int32.Parse(pageSetup.Scale.InnerText); - } - else - { - if (pageSetup.FitToWidth != null) - ws.PageSetup.PagesWide = Int32.Parse(pageSetup.FitToWidth.InnerText); - if (pageSetup.FitToHeight != null) - ws.PageSetup.PagesTall = Int32.Parse(pageSetup.FitToHeight.InnerText); - } - if (pageSetup.PageOrder != null) - ws.PageSetup.PageOrder = pageOrderValues.Single(p => p.Value == pageSetup.PageOrder).Key; - if (pageSetup.Orientation != null) - ws.PageSetup.PageOrientation = pageOrientationValues.Single(p => p.Value == pageSetup.Orientation).Key; - if (pageSetup.BlackAndWhite != null) - ws.PageSetup.BlackAndWhite = pageSetup.BlackAndWhite; - if (pageSetup.Draft != null) - ws.PageSetup.DraftQuality = pageSetup.Draft; - if (pageSetup.CellComments != null) - ws.PageSetup.ShowComments = showCommentsValues.Single(sc => sc.Value == pageSetup.CellComments).Key; - if (pageSetup.Errors != null) - ws.PageSetup.PrintErrorValue = printErrorValues.Single(p => p.Value == pageSetup.Errors).Key; - if (pageSetup.HorizontalDpi != null) ws.PageSetup.HorizontalDpi = Int32.Parse(pageSetup.HorizontalDpi.InnerText); - if (pageSetup.VerticalDpi != null) ws.PageSetup.VerticalDpi = Int32.Parse(pageSetup.VerticalDpi.InnerText); - if (pageSetup.FirstPageNumber != null) ws.PageSetup.FirstPageNumber = Int32.Parse(pageSetup.FirstPageNumber.InnerText); - } - - var headerFooters = worksheetPart.Worksheet.Descendants(); - if (headerFooters.Count() > 0) - { - var headerFooter = (HeaderFooter)headerFooters.First(); - if (headerFooter.AlignWithMargins != null) - ws.PageSetup.AlignHFWithMargins = headerFooter.AlignWithMargins; - - // Footers - var xlFooter = (XLHeaderFooter)ws.PageSetup.Footer; - var evenFooter = (EvenFooter)headerFooter.EvenFooter; - if (evenFooter != null) - xlFooter.SetInnerText(XLHFOccurrence.EvenPages, evenFooter.Text); - var oddFooter = (OddFooter)headerFooter.OddFooter; - if (oddFooter != null) - xlFooter.SetInnerText(XLHFOccurrence.OddPages, oddFooter.Text); - var firstFooter = (FirstFooter)headerFooter.FirstFooter; - if (firstFooter != null) - xlFooter.SetInnerText(XLHFOccurrence.FirstPage, firstFooter.Text); - // Headers - var xlHeader = (XLHeaderFooter)ws.PageSetup.Header; - var evenHeader = (EvenHeader)headerFooter.EvenHeader; - if (evenHeader != null) - xlHeader.SetInnerText(XLHFOccurrence.EvenPages, evenHeader.Text); - var oddHeader = (OddHeader)headerFooter.OddHeader; - if (oddHeader != null) - xlHeader.SetInnerText(XLHFOccurrence.OddPages, oddHeader.Text); - var firstHeader = (FirstHeader)headerFooter.FirstHeader; - if (firstHeader != null) - xlHeader.SetInnerText(XLHFOccurrence.FirstPage, firstHeader.Text); - } - - var sheetProperties = worksheetPart.Worksheet.Descendants(); - if (sheetProperties.Count() > 0) - { - var sheetProperty = (SheetProperties)sheetProperties.First(); - if (sheetProperty.OutlineProperties != null) - { - if (sheetProperty.OutlineProperties.SummaryBelow != null) - { - ws.Outline.SummaryVLocation = sheetProperty.OutlineProperties.SummaryBelow ? - XLOutlineSummaryVLocation.Bottom : XLOutlineSummaryVLocation.Top; - } - - if (sheetProperty.OutlineProperties.SummaryRight != null) - { - ws.Outline.SummaryHLocation = sheetProperty.OutlineProperties.SummaryRight ? - XLOutlineSummaryHLocation.Right : XLOutlineSummaryHLocation.Left; - } - } - } - - var rowBreaksList = worksheetPart.Worksheet.Descendants(); - if (rowBreaksList.Count() > 0) - { - var rowBreaks = (RowBreaks)rowBreaksList.First(); - foreach (var rowBreak in rowBreaks.Descendants()) - { - ws.PageSetup.RowBreaks.Add(Int32.Parse(rowBreak.Id.InnerText)); - } - } - - var columnBreaksList = worksheetPart.Worksheet.Descendants(); - if (columnBreaksList.Count() > 0) - { - var columnBreaks = (ColumnBreaks)columnBreaksList.First(); - foreach (var columnBreak in columnBreaks.Descendants()) - { - if (columnBreak.Id != null) - ws.PageSetup.ColumnBreaks.Add(Int32.Parse(columnBreak.Id.InnerText)); + toApply.Style = DefaultStyle; } } } - var workbook = (Workbook)dSpreadsheet.WorkbookPart.Workbook; - foreach (var definedName in workbook.Descendants()) + foreach (var row in worksheetPart.Worksheet.Descendants().Where(r => r.CustomFormat != null && r.CustomFormat).Select(r => r)) { - var name = definedName.Name; - if (name == "_xlnm.Print_Area") - { - foreach (var area in definedName.Text.Split(',')) - { - var sections = area.Split('!'); - var sheetName = sections[0].Replace("\'", ""); - var sheetArea = sections[1]; - Worksheets.Worksheet(sheetName).PageSetup.PrintAreas.Add(sheetArea); - } - } - else if (name == "_xlnm.Print_Titles") - { - var areas = definedName.Text.Split(','); + //var dRow = (Column)col; + var xlRow = ws.Row((Int32)row.RowIndex.Value); + if (row.Height != null) + xlRow.Height = row.Height; + else + xlRow.Height = ws.RowHeight; - var colSections = areas[0].Split('!'); - var sheetNameCol = colSections[0].Replace("\'", ""); - var sheetAreaCol = colSections[1]; - Worksheets.Worksheet(sheetNameCol).PageSetup.SetColumnsToRepeatAtLeft(sheetAreaCol); + if (row.Hidden != null && row.Hidden) + xlRow.Hide(); - var rowSections = areas[1].Split('!'); - var sheetNameRow = rowSections[0].Replace("\'", ""); - var sheetAreaRow = rowSections[1]; - Worksheets.Worksheet(sheetNameRow).PageSetup.SetRowsToRepeatAtTop(sheetAreaRow); + if (row.Collapsed != null && row.Collapsed) + xlRow.Collapse(); + + if (row.OutlineLevel != null) + xlRow.OutlineLevel = row.OutlineLevel; + + Int32 styleIndex = row.StyleIndex != null ? Int32.Parse(row.StyleIndex.InnerText) : -1; + if (styleIndex > 0) + { + ApplyStyle(xlRow, styleIndex, s, fills, borders, fonts, numberingFormats); } else { - var localSheetId = definedName.LocalSheetId; - var comment = definedName.Comment; - var text = definedName.Text; - if (localSheetId == null) - { - NamedRanges.Add(name, text, comment); - } + xlRow.Style = DefaultStyle; + } + } + + foreach (var cell in worksheetPart.Worksheet.Descendants() + .Where(c => c.CellFormula != null && c.CellFormula.SharedIndex != null && c.CellFormula.Reference != null) + .Select(c => c)) + { + sharedFormulas.Add(cell.CellFormula.SharedIndex.Value, cell.CellFormula); + } + foreach (var cell in worksheetPart.Worksheet.Descendants()) + { + var dCell = (Cell)cell; + Int32 styleIndex = dCell.StyleIndex != null ? Int32.Parse(dCell.StyleIndex.InnerText) : 0; + var xlCell = ws.CellFast(dCell.CellReference); + if (styleIndex > 0) + { + //styleIndex = Int32.Parse(dCell.StyleIndex.InnerText); + ApplyStyle(xlCell, styleIndex, s, fills, borders, fonts, numberingFormats); + } + else + { + xlCell.Style = DefaultStyle; + } + + if (dCell.CellFormula != null) + { + if (dCell.CellFormula.SharedIndex != null) + xlCell.FormulaA1 = sharedFormulas[dCell.CellFormula.SharedIndex.Value].Text; else + xlCell.FormulaA1 = dCell.CellFormula.Text; + } + else if (dCell.DataType != null) + { + if (dCell.DataType == CellValues.SharedString) { - Worksheets.Worksheet(Int32.Parse(localSheetId)).NamedRanges.Add(name, text, comment); + xlCell.DataType = XLCellValues.Text; + if (dCell.CellValue != null) + { + if (!String.IsNullOrWhiteSpace(dCell.CellValue.Text)) + xlCell.Value = sharedStrings[Int32.Parse(dCell.CellValue.Text)].InnerText; + else + xlCell.Value = dCell.CellValue.Text; + } + else + { + xlCell.Value = String.Empty; + } + } + else if (dCell.DataType == CellValues.Date) + { + xlCell.DataType = XLCellValues.DateTime; + xlCell.Value = DateTime.FromOADate(Double.Parse(dCell.CellValue.Text)); + } + else if (dCell.DataType == CellValues.Boolean) + { + xlCell.DataType = XLCellValues.Boolean; + xlCell.Value = (dCell.CellValue.Text == "1"); + } + else if (dCell.DataType == CellValues.Number) + { + xlCell.DataType = XLCellValues.Number; + xlCell.Value = dCell.CellValue.Text; + } + } + else if (dCell.CellValue != null) + { + //var styleIndex = Int32.Parse(dCell.StyleIndex.InnerText); + var numberFormatId = ((CellFormat)((CellFormats)s.CellFormats).ElementAt(styleIndex)).NumberFormatId; //. [styleIndex].NumberFormatId; + ws.Cell(dCell.CellReference).Value = dCell.CellValue.Text; + ws.Cell(dCell.CellReference).Style.NumberFormat.NumberFormatId = Int32.Parse(numberFormatId); + } + } + + var printOptionsQuery = worksheetPart.Worksheet.Descendants(); + if (printOptionsQuery.Count() > 1) + { + var printOptions = (PrintOptions)printOptionsQuery.First(); + if (printOptions.GridLines != null) + ws.PageSetup.ShowGridlines = printOptions.GridLines; + if (printOptions.HorizontalCentered != null) + ws.PageSetup.CenterHorizontally = printOptions.HorizontalCentered; + if (printOptions.VerticalCentered != null) + ws.PageSetup.CenterVertically = printOptions.VerticalCentered; + if (printOptions.Headings != null) + ws.PageSetup.ShowRowAndColumnHeadings = printOptions.Headings; + } + + var pageMarginsQuery = worksheetPart.Worksheet.Descendants(); + if (pageMarginsQuery.Count() > 0) + { + var pageMargins = (PageMargins)pageMarginsQuery.First(); + if (pageMargins.Bottom != null) + ws.PageSetup.Margins.Bottom = pageMargins.Bottom; + if (pageMargins.Footer != null) + ws.PageSetup.Margins.Footer = pageMargins.Footer; + if (pageMargins.Header != null) + ws.PageSetup.Margins.Header = pageMargins.Header; + if (pageMargins.Left != null) + ws.PageSetup.Margins.Left = pageMargins.Left; + if (pageMargins.Right != null) + ws.PageSetup.Margins.Right = pageMargins.Right; + if (pageMargins.Top != null) + ws.PageSetup.Margins.Top = pageMargins.Top; + } + + var pageSetupQuery = worksheetPart.Worksheet.Descendants(); + if (pageSetupQuery.Count() > 0) + { + var pageSetup = (PageSetup)pageSetupQuery.First(); + if (pageSetup.PaperSize != null) + ws.PageSetup.PaperSize = (XLPaperSize)Int32.Parse(pageSetup.PaperSize.InnerText); + if (pageSetup.Scale != null) + { + ws.PageSetup.Scale = Int32.Parse(pageSetup.Scale.InnerText); + } + else + { + if (pageSetup.FitToWidth != null) + ws.PageSetup.PagesWide = Int32.Parse(pageSetup.FitToWidth.InnerText); + if (pageSetup.FitToHeight != null) + ws.PageSetup.PagesTall = Int32.Parse(pageSetup.FitToHeight.InnerText); + } + if (pageSetup.PageOrder != null) + ws.PageSetup.PageOrder = pageOrderValues.Single(p => p.Value == pageSetup.PageOrder).Key; + if (pageSetup.Orientation != null) + ws.PageSetup.PageOrientation = pageOrientationValues.Single(p => p.Value == pageSetup.Orientation).Key; + if (pageSetup.BlackAndWhite != null) + ws.PageSetup.BlackAndWhite = pageSetup.BlackAndWhite; + if (pageSetup.Draft != null) + ws.PageSetup.DraftQuality = pageSetup.Draft; + if (pageSetup.CellComments != null) + ws.PageSetup.ShowComments = showCommentsValues.Single(sc => sc.Value == pageSetup.CellComments).Key; + if (pageSetup.Errors != null) + ws.PageSetup.PrintErrorValue = printErrorValues.Single(p => p.Value == pageSetup.Errors).Key; + if (pageSetup.HorizontalDpi != null) ws.PageSetup.HorizontalDpi = Int32.Parse(pageSetup.HorizontalDpi.InnerText); + if (pageSetup.VerticalDpi != null) ws.PageSetup.VerticalDpi = Int32.Parse(pageSetup.VerticalDpi.InnerText); + if (pageSetup.FirstPageNumber != null) ws.PageSetup.FirstPageNumber = Int32.Parse(pageSetup.FirstPageNumber.InnerText); + } + + var headerFooters = worksheetPart.Worksheet.Descendants(); + if (headerFooters.Count() > 0) + { + var headerFooter = (HeaderFooter)headerFooters.First(); + if (headerFooter.AlignWithMargins != null) + ws.PageSetup.AlignHFWithMargins = headerFooter.AlignWithMargins; + + // Footers + var xlFooter = (XLHeaderFooter)ws.PageSetup.Footer; + var evenFooter = (EvenFooter)headerFooter.EvenFooter; + if (evenFooter != null) + xlFooter.SetInnerText(XLHFOccurrence.EvenPages, evenFooter.Text); + var oddFooter = (OddFooter)headerFooter.OddFooter; + if (oddFooter != null) + xlFooter.SetInnerText(XLHFOccurrence.OddPages, oddFooter.Text); + var firstFooter = (FirstFooter)headerFooter.FirstFooter; + if (firstFooter != null) + xlFooter.SetInnerText(XLHFOccurrence.FirstPage, firstFooter.Text); + // Headers + var xlHeader = (XLHeaderFooter)ws.PageSetup.Header; + var evenHeader = (EvenHeader)headerFooter.EvenHeader; + if (evenHeader != null) + xlHeader.SetInnerText(XLHFOccurrence.EvenPages, evenHeader.Text); + var oddHeader = (OddHeader)headerFooter.OddHeader; + if (oddHeader != null) + xlHeader.SetInnerText(XLHFOccurrence.OddPages, oddHeader.Text); + var firstHeader = (FirstHeader)headerFooter.FirstHeader; + if (firstHeader != null) + xlHeader.SetInnerText(XLHFOccurrence.FirstPage, firstHeader.Text); + } + + var sheetProperties = worksheetPart.Worksheet.Descendants(); + if (sheetProperties.Count() > 0) + { + var sheetProperty = (SheetProperties)sheetProperties.First(); + if (sheetProperty.OutlineProperties != null) + { + if (sheetProperty.OutlineProperties.SummaryBelow != null) + { + ws.Outline.SummaryVLocation = sheetProperty.OutlineProperties.SummaryBelow ? + XLOutlineSummaryVLocation.Bottom : XLOutlineSummaryVLocation.Top; + } + + if (sheetProperty.OutlineProperties.SummaryRight != null) + { + ws.Outline.SummaryHLocation = sheetProperty.OutlineProperties.SummaryRight ? + XLOutlineSummaryHLocation.Right : XLOutlineSummaryHLocation.Left; } } } + + var rowBreaksList = worksheetPart.Worksheet.Descendants(); + if (rowBreaksList.Count() > 0) + { + var rowBreaks = (RowBreaks)rowBreaksList.First(); + foreach (var rowBreak in rowBreaks.Descendants()) + { + ws.PageSetup.RowBreaks.Add(Int32.Parse(rowBreak.Id.InnerText)); + } + } + + var columnBreaksList = worksheetPart.Worksheet.Descendants(); + if (columnBreaksList.Count() > 0) + { + var columnBreaks = (ColumnBreaks)columnBreaksList.First(); + foreach (var columnBreak in columnBreaks.Descendants()) + { + if (columnBreak.Id != null) + ws.PageSetup.ColumnBreaks.Add(Int32.Parse(columnBreak.Id.InnerText)); + } + } } + + var workbook = (Workbook)dSpreadsheet.WorkbookPart.Workbook; + foreach (var definedName in workbook.Descendants()) + { + var name = definedName.Name; + if (name == "_xlnm.Print_Area") + { + foreach (var area in definedName.Text.Split(',')) + { + var sections = area.Split('!'); + var sheetName = sections[0].Replace("\'", ""); + var sheetArea = sections[1]; + Worksheets.Worksheet(sheetName).PageSetup.PrintAreas.Add(sheetArea); + } + } + else if (name == "_xlnm.Print_Titles") + { + var areas = definedName.Text.Split(','); + + var colSections = areas[0].Split('!'); + var sheetNameCol = colSections[0].Replace("\'", ""); + var sheetAreaCol = colSections[1]; + Worksheets.Worksheet(sheetNameCol).PageSetup.SetColumnsToRepeatAtLeft(sheetAreaCol); + + var rowSections = areas[1].Split('!'); + var sheetNameRow = rowSections[0].Replace("\'", ""); + var sheetAreaRow = rowSections[1]; + Worksheets.Worksheet(sheetNameRow).PageSetup.SetRowsToRepeatAtTop(sheetAreaRow); + } + else + { + var localSheetId = definedName.LocalSheetId; + var comment = definedName.Comment; + var text = definedName.Text; + if (localSheetId == null) + { + NamedRanges.Add(name, text, comment); + } + else + { + Worksheets.Worksheet(Int32.Parse(localSheetId)).NamedRanges.Add(name, text, comment); + } + } + } + } private void SetProperties(SpreadsheetDocument dSpreadsheet) diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Save.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Save.cs index ece4108..d4c0116 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Save.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Save.cs @@ -240,7 +240,7 @@ alignmentReadingOrderValues.Add(new KeyValuePair(XLAlignmentReadingOrderValues.LeftToRight, 1)); alignmentReadingOrderValues.Add(new KeyValuePair(XLAlignmentReadingOrderValues.RightToLeft, 2)); } - // Creates a SpreadsheetDocument. + private void CreatePackage(String filePath) { SpreadsheetDocument package; @@ -255,6 +255,15 @@ } } + private void CreatePackage(Stream stream) + { + SpreadsheetDocument package = SpreadsheetDocument.Create(stream, SpreadsheetDocumentType.Workbook); + using (package) + { + CreateParts(package); + } + } + // Adds child parts and generates content of the specified part. private RelIdGenerator relId; private void CreateParts(SpreadsheetDocument document) @@ -1640,7 +1649,15 @@ } else if (dataType == XLCellValues.DateTime || dataType == XLCellValues.Number) { - cellValue.Text = Double.Parse(opCell.Value.InnerText).ToString(CultureInfo.InvariantCulture); + TimeSpan timeSpan; + if (TimeSpan.TryParse(opCell.Value.InnerText, out timeSpan)) + { + cellValue.Text = XLCell.baseDate.Add(timeSpan).ToOADate().ToString(CultureInfo.InvariantCulture); + } + else + { + cellValue.Text = Double.Parse(opCell.Value.InnerText).ToString(CultureInfo.InvariantCulture); + } cell.CellValue = cellValue; } else diff --git a/ClosedXML/ClosedXML/ClosedXML_Examples/Misc/DataTypes.cs b/ClosedXML/ClosedXML/ClosedXML_Examples/Misc/DataTypes.cs index 5dbd6ee..f65a94e 100644 --- a/ClosedXML/ClosedXML/ClosedXML_Examples/Misc/DataTypes.cs +++ b/ClosedXML/ClosedXML/ClosedXML_Examples/Misc/DataTypes.cs @@ -58,12 +58,18 @@ ws.Cell(++ro, co).Value = "Plain Date:"; ws.Cell(ro, co + 1).Value = new DateTime(2010, 9, 2); + ws.Cell(++ro, co).Value = "Plain DateTime:"; + ws.Cell(ro, co + 1).Value = new DateTime(2010, 9, 2, 13, 45, 22); + ws.Cell(++ro, co).Value = "Plain Boolean:"; ws.Cell(ro, co + 1).Value = true; ws.Cell(++ro, co).Value = "Plain Number:"; ws.Cell(ro, co + 1).Value = 123.45; + ws.Cell(++ro, co).Value = "TimeSpan:"; + ws.Cell(ro, co + 1).Value = new TimeSpan(33, 45, 22); + ro++; ws.Cell(++ro, co).Value = "Explicit Text:"; @@ -72,12 +78,18 @@ ws.Cell(++ro, co).Value = "Date as Text:"; ws.Cell(ro, co + 1).Value = "'" + new DateTime(2010, 9, 2).ToString(); + ws.Cell(++ro, co).Value = "DateTime as Text:"; + ws.Cell(ro, co + 1).Value = "'" + new DateTime(2010, 9, 2, 13, 45, 22).ToString(); + ws.Cell(++ro, co).Value = "Boolean as Text:"; ws.Cell(ro, co + 1).Value = "'" + true.ToString(); ws.Cell(++ro, co).Value = "Number as Text:"; ws.Cell(ro, co + 1).Value = "'123.45"; + ws.Cell(++ro, co).Value = "TimeSpan as Text:"; + ws.Cell(ro, co + 1).Value = "'" + new TimeSpan(33, 45, 22).ToString(); + ro++; ws.Cell(++ro, co).Value = "Changing Data Types:"; @@ -88,6 +100,10 @@ ws.Cell(ro, co + 1).Value = new DateTime(2010, 9, 2); ws.Cell(ro, co + 1).DataType = XLCellValues.Text; + ws.Cell(++ro, co).Value = "DateTime to Text:"; + ws.Cell(ro, co + 1).Value = new DateTime(2010, 9, 2, 13, 45, 22); + ws.Cell(ro, co + 1).DataType = XLCellValues.Text; + ws.Cell(++ro, co).Value = "Boolean to Text:"; ws.Cell(ro, co + 1).Value = true; ws.Cell(ro, co + 1).DataType = XLCellValues.Text; @@ -96,10 +112,18 @@ ws.Cell(ro, co + 1).Value = 123.45; ws.Cell(ro, co + 1).DataType = XLCellValues.Text; + ws.Cell(++ro, co).Value = "TimeSpan to Text:"; + ws.Cell(ro, co + 1).Value = new TimeSpan(33, 45, 22); + ws.Cell(ro, co + 1).DataType = XLCellValues.Text; + ws.Cell(++ro, co).Value = "Text to Date:"; ws.Cell(ro, co + 1).Value = "'" + new DateTime(2010, 9, 2).ToString(); ws.Cell(ro, co + 1).DataType = XLCellValues.DateTime; + ws.Cell(++ro, co).Value = "Text to DateTime:"; + ws.Cell(ro, co + 1).Value = "'" + new DateTime(2010, 9, 2, 13, 45, 22).ToString(); + ws.Cell(ro, co + 1).DataType = XLCellValues.DateTime; + ws.Cell(++ro, co).Value = "Text to Boolean:"; ws.Cell(ro, co + 1).Value = "'" + true.ToString(); ws.Cell(ro, co + 1).DataType = XLCellValues.Boolean; @@ -108,6 +132,10 @@ ws.Cell(ro, co + 1).Value = "'123.45"; ws.Cell(ro, co + 1).DataType = XLCellValues.Number; + ws.Cell(++ro, co).Value = "Text to TimeSpan:"; + ws.Cell(ro, co + 1).Value = "'" + new TimeSpan(33, 45, 22).ToString(); + ws.Cell(ro, co + 1).DataType = XLCellValues.Number; + ro++; ws.Cell(++ro, co).Value = "Formatted Date to Text:";