diff --git a/ClosedXML/ClosedXML/ClosedXML/ClosedXML.csproj b/ClosedXML/ClosedXML/ClosedXML/ClosedXML.csproj index 37f36e4..c696db7 100644 --- a/ClosedXML/ClosedXML/ClosedXML/ClosedXML.csproj +++ b/ClosedXML/ClosedXML/ClosedXML/ClosedXML.csproj @@ -50,6 +50,7 @@ + diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/IXLWorkbook.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/IXLWorkbook.cs index 62c6324..53cc4c2 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/IXLWorkbook.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/IXLWorkbook.cs @@ -12,6 +12,6 @@ String Name { get; } String FullName { get; } void SaveAs(String file, Boolean overwrite = false); - + void Load(String file); } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/PrintOptions/IXLPrintAreas.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/PrintOptions/IXLPrintAreas.cs index 1745068..2072f00 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/PrintOptions/IXLPrintAreas.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/PrintOptions/IXLPrintAreas.cs @@ -12,6 +12,5 @@ void Add(String rangeAddress); void Add(String firstCellAddress, String lastCellAddress); void Add(IXLAddress firstCellAddress, IXLAddress lastCellAddress); - void Add(IXLCell firstCell, IXLCell lastCell); } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/PrintOptions/XLPrintAreas.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/PrintOptions/XLPrintAreas.cs index a1bb144..5df4bbc 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/PrintOptions/XLPrintAreas.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/PrintOptions/XLPrintAreas.cs @@ -39,11 +39,6 @@ ranges.Add(worksheet.Range(firstCellAddress, lastCellAddress)); } - public void Add(IXLCell firstCell, IXLCell lastCell) - { - ranges.Add(worksheet.Range(firstCell, lastCell)); - } - public IEnumerator GetEnumerator() { return ranges.GetEnumerator(); diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRange.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRange.cs index 7f15257..077a0c1 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRange.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRange.cs @@ -103,10 +103,6 @@ return new XLRange(xlRangeParameters); } - public static IXLRange Range(this IXLRange range, IXLCell firstCell, IXLCell lastCell) - { - return range.Range(firstCell.Address, lastCell.Address); - } public static IEnumerable Cells(this IXLRange range) { diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/XLCell.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/XLCell.cs index a388442..0157c49 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/XLCell.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/XLCell.cs @@ -28,12 +28,42 @@ set { String val = value; - - if (!initialized) + Double dTest; + DateTime dtTest; + Boolean bTest; + if (initialized) { - Double dTest; - DateTime dtTest; - Boolean bTest; + if (dataType == XLCellValues.Boolean) + { + if (Boolean.TryParse(val, out bTest)) + val = bTest ? "1" : "0"; + else if (!(val == "1" || val == "0")) + throw new ArgumentException("'" + val + "' is not a Boolean type."); + } + else if (dataType == XLCellValues.DateTime) + { + if (DateTime.TryParse(val, out dtTest)) + { + + val = dtTest.ToOADate().ToString(); + } + else if (!Double.TryParse(val, out dTest)) + { + throw new ArgumentException("'" + val + "' is not a DateTime type."); + } + + if (Style.NumberFormat.Format == String.Empty) + Style.NumberFormat.NumberFormatId = 14; + } + else if (dataType == XLCellValues.Number) + { + if (!Double.TryParse(val, out dTest)) + throw new ArgumentException("'" + val + "' is not a Numeric type."); + + } + } + else + { if (val.Length > 0 && val.Substring(0, 1) == "'") { val = val.Substring(1, val.Length - 1); @@ -102,52 +132,54 @@ set { initialized = true; - if (value == XLCellValues.Boolean) + if (cellValue.Length > 0) { - cellValue = Boolean.Parse(cellValue) ? "1" : "0"; - } - else if (value == XLCellValues.DateTime) - { - DateTime dtTest; - Double dblTest; - if (DateTime.TryParse(cellValue, out dtTest)) + if (value == XLCellValues.Boolean) { - cellValue = dtTest.ToOADate().ToString(); + cellValue = Boolean.Parse(cellValue) ? "1" : "0"; } - else if (Double.TryParse(cellValue, out dblTest)) + else if (value == XLCellValues.DateTime) { - cellValue = dblTest.ToString(); + 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 (Style.NumberFormat.Format == String.Empty) + Style.NumberFormat.NumberFormatId = 14; + } + else if (value == XLCellValues.Number) + { + cellValue = Double.Parse(cellValue).ToString(); + if (Style.NumberFormat.Format == String.Empty) + Style.NumberFormat.NumberFormatId = 0; } 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 = 14; - } - else if (value == XLCellValues.Number) - { - cellValue = Double.Parse(cellValue).ToString(); - if (Style.NumberFormat.Format == String.Empty) - Style.NumberFormat.NumberFormatId = 0; - } - else - { - if (dataType == XLCellValues.Boolean) - { - cellValue = (cellValue == "0" ? false : true).ToString(); - } - else if (dataType == XLCellValues.Number) - { - cellValue = Double.Parse(cellValue).ToString(Style.NumberFormat.Format); - } - else if (dataType == XLCellValues.DateTime) - { - cellValue = DateTime.FromOADate(Double.Parse(cellValue)).ToString(Style.NumberFormat.Format); + if (dataType == XLCellValues.Boolean) + { + cellValue = (cellValue == "0" ? false : true).ToString(); + } + else if (dataType == XLCellValues.Number) + { + cellValue = Double.Parse(cellValue).ToString(Style.NumberFormat.Format); + } + else if (dataType == XLCellValues.DateTime) + { + cellValue = DateTime.FromOADate(Double.Parse(cellValue)).ToString(Style.NumberFormat.Format); + } } } - dataType = value; } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook.cs index cdbeeb3..f15bd13 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook.cs @@ -15,7 +15,15 @@ DefaultRowHeight = 15; DefaultColumnWidth = 9.140625; Worksheets = new XLWorksheets(); + + PopulateEnums(); } + + public XLWorkbook(String file) + { + Load(file); + } + #region IXLWorkbook Members public IXLWorksheets Worksheets { get; private set; } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Load.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Load.cs new file mode 100644 index 0000000..6a0cff1 --- /dev/null +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Load.cs @@ -0,0 +1,137 @@ +using DocumentFormat.OpenXml.Packaging; +using Ap = DocumentFormat.OpenXml.ExtendedProperties; +using Vt = DocumentFormat.OpenXml.VariantTypes; +using DocumentFormat.OpenXml; +using DocumentFormat.OpenXml.Spreadsheet; +using A = DocumentFormat.OpenXml.Drawing; +using Xdr = DocumentFormat.OpenXml.Drawing.Spreadsheet; +using C = DocumentFormat.OpenXml.Drawing.Charts; + +using System; +using System.Collections.Generic; +using System.Linq; +using System.Text; +using System.IO; + + + +namespace ClosedXML.Excel +{ + public partial class XLWorkbook + { + public void Load(String file) + { + + LoadSheets(file); + } + + private void LoadSheets(String fileName) + { + // Open file as read-only. + using (SpreadsheetDocument dSpreadsheet = SpreadsheetDocument.Open(fileName, false)) + { + SharedStringItem[] sharedStrings = null; + if (dSpreadsheet.WorkbookPart.GetPartsOfType().Count() > 0) + { + SharedStringTablePart shareStringPart = dSpreadsheet.WorkbookPart.GetPartsOfType().First(); + sharedStrings = shareStringPart.SharedStringTable.Elements().ToArray(); + } + + var workbookStylesPart = (WorkbookStylesPart)dSpreadsheet.WorkbookPart.WorkbookStylesPart; + var s = (Stylesheet)workbookStylesPart.Stylesheet; + var numberingFormats = (NumberingFormats)s.NumberingFormats; + Fills fills = (Fills)s.Fills; + + //return items[int.Parse(headCell.CellValue.Text)].InnerText; + + var sheets = dSpreadsheet.WorkbookPart.Workbook.Sheets; + + // For each sheet, display the sheet information. + foreach (var sheet in sheets) + { + var dSheet = ((Sheet)sheet); + WorksheetPart worksheetPart = (WorksheetPart)dSpreadsheet.WorkbookPart.GetPartById(dSheet.Id); + + + var sheetName = dSheet.Name; + + + var ws = Worksheets.Add(sheetName); + foreach (var cell in worksheetPart.Worksheet.Descendants()) + { + var dCell = (Cell)cell; + if (dCell.DataType != null) + { + var xlCell = ws.Cell(dCell.CellReference); + Int32 styleIndex = dCell.StyleIndex != null ? Int32.Parse(dCell.StyleIndex.InnerText) : -1; + if (styleIndex >= 0) + { + styleIndex = Int32.Parse(dCell.StyleIndex.InnerText); + var fillId = ((CellFormat)((CellFormats)s.CellFormats).ElementAt(styleIndex)).FillId.Value; + var fill = (Fill)fills.ElementAt(Int32.Parse(fillId.ToString())); + xlCell.Style.Fill.PatternType = fillPatternValues.Single(p => p.Value == fill.PatternFill.PatternType).Key; + xlCell.Style.Fill.PatternColor = System.Drawing.ColorTranslator.FromHtml("#" + fill.PatternFill.ForegroundColor.Rgb.Value); + xlCell.Style.Fill.PatternBackgroundColor = System.Drawing.ColorTranslator.FromHtml("#" + fill.PatternFill.BackgroundColor.Rgb.Value); + + var alignment = (Alignment)((CellFormat)((CellFormats)s.CellFormats).ElementAt(styleIndex)).Alignment; + xlCell.Style.Alignment.Horizontal = alignmentHorizontalValues.Single(a => a.Value == alignment.Horizontal).Key; + xlCell.Style.Alignment.Indent = Int32.Parse(alignment.Indent.ToString()); + xlCell.Style.Alignment.JustifyLastLine = alignment.JustifyLastLine; + xlCell.Style.Alignment.ReadingOrder = (XLAlignmentReadingOrderValues)Int32.Parse(alignment.ReadingOrder.ToString()); + xlCell.Style.Alignment.RelativeIndent = alignment.RelativeIndent; + xlCell.Style.Alignment.ShrinkToFit = alignment.ShrinkToFit; + xlCell.Style.Alignment.TextRotation = Int32.Parse(alignment.TextRotation.ToString()); + xlCell.Style.Alignment.Vertical = alignmentVerticalValues.Single(a => a.Value == alignment.Vertical).Key; + xlCell.Style.Alignment.WrapText = alignment.WrapText; + } + + if (dCell.DataType == CellValues.SharedString) + { + xlCell.DataType = XLCellValues.Text; + xlCell.Value = sharedStrings[Int32.Parse(dCell.CellValue.Text)].InnerText; + } + else if (dCell.DataType == CellValues.Date) + { + xlCell.DataType = XLCellValues.DateTime; + xlCell.Value = DateTime.FromOADate(Double.Parse(dCell.CellValue.Text)).ToString(); + } + else if (dCell.DataType == CellValues.Boolean) + { + xlCell.DataType = XLCellValues.Boolean; + xlCell.Value = (dCell.CellValue.Text == "1").ToString(); + } + else if (dCell.DataType == CellValues.Number) + { + xlCell.DataType = XLCellValues.Number; + xlCell.Value = dCell.CellValue.Text; + if (styleIndex >= 0) + { + var numberFormatId = ((CellFormat)((CellFormats)s.CellFormats).ElementAt(styleIndex)).NumberFormatId; + var numberFormatList = numberingFormats.Where(nf => ((NumberingFormat)nf).NumberFormatId.Value == numberFormatId); + var formatCode = String.Empty; + if (numberFormatList.Count() > 0) + { + NumberingFormat numberingFormat = (NumberingFormat)numberFormatList.First(); + formatCode = numberingFormat.FormatCode.Value; + } + if (formatCode.Length > 0) + xlCell.Style.NumberFormat.Format = formatCode; + else + xlCell.Style.NumberFormat.NumberFormatId = Int32.Parse(numberFormatId); + } + } + } + //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); + //} + } + } + } + } + + } +} \ No newline at end of file diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Save.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Save.cs index 866b795..f780a62 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Save.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Save.cs @@ -19,6 +19,16 @@ { public partial class XLWorkbook { + private List> fillPatternValues = new List>(); + private List> alignmentHorizontalValues = new List>(); + private List> alignmentVerticalValues = new List>(); + private void PopulateEnums() + { + PopulateFillPatternValues(); + PopulateAlignmentHorizontalValues(); + PopulateAlignmentVerticalValues(); + } + private enum RelType { General, Workbook, Worksheet } private class RelId { @@ -99,31 +109,27 @@ } } - private PatternValues GetPatternValue(XLFillPatternValues xlFillPatternValue) - { - switch (xlFillPatternValue) - { - case XLFillPatternValues.DarkDown: return PatternValues.DarkDown; - case XLFillPatternValues.DarkGray: return PatternValues.DarkGray; - case XLFillPatternValues.DarkGrid: return PatternValues.DarkGrid; - case XLFillPatternValues.DarkHorizontal: return PatternValues.DarkHorizontal; - case XLFillPatternValues.DarkTrellis: return PatternValues.DarkTrellis; - case XLFillPatternValues.DarkUp: return PatternValues.DarkUp; - case XLFillPatternValues.DarkVertical: return PatternValues.DarkVertical; - case XLFillPatternValues.Gray0625: return PatternValues.Gray0625; - case XLFillPatternValues.Gray125: return PatternValues.Gray125; - case XLFillPatternValues.LightDown: return PatternValues.LightDown; - case XLFillPatternValues.LightGray: return PatternValues.LightGray; - case XLFillPatternValues.LightGrid: return PatternValues.LightGrid; - case XLFillPatternValues.LightHorizontal: return PatternValues.LightHorizontal; - case XLFillPatternValues.LightTrellis: return PatternValues.LightTrellis; - case XLFillPatternValues.LightUp: return PatternValues.LightUp; - case XLFillPatternValues.LightVertical: return PatternValues.LightVertical; - case XLFillPatternValues.MediumGray: return PatternValues.MediumGray; - case XLFillPatternValues.None: return PatternValues.None; - case XLFillPatternValues.Solid: return PatternValues.Solid; - default: throw new NotImplementedException(); - } + private void PopulateFillPatternValues() + { + fillPatternValues.Add(new KeyValuePair(XLFillPatternValues.DarkDown, PatternValues.DarkDown)); + fillPatternValues.Add(new KeyValuePair(XLFillPatternValues.DarkGray, PatternValues.DarkGray)); + fillPatternValues.Add(new KeyValuePair(XLFillPatternValues.DarkGrid, PatternValues.DarkGrid)); + fillPatternValues.Add(new KeyValuePair(XLFillPatternValues.DarkHorizontal, PatternValues.DarkHorizontal)); + fillPatternValues.Add(new KeyValuePair(XLFillPatternValues.DarkTrellis, PatternValues.DarkTrellis)); + fillPatternValues.Add(new KeyValuePair(XLFillPatternValues.DarkUp, PatternValues.DarkUp)); + fillPatternValues.Add(new KeyValuePair(XLFillPatternValues.DarkVertical, PatternValues.DarkVertical)); + fillPatternValues.Add(new KeyValuePair(XLFillPatternValues.Gray0625, PatternValues.Gray0625)); + fillPatternValues.Add(new KeyValuePair(XLFillPatternValues.Gray125, PatternValues.Gray125)); + fillPatternValues.Add(new KeyValuePair(XLFillPatternValues.LightDown, PatternValues.LightDown)); + fillPatternValues.Add(new KeyValuePair(XLFillPatternValues.LightGray, PatternValues.LightGray)); + fillPatternValues.Add(new KeyValuePair(XLFillPatternValues.LightGrid, PatternValues.LightGrid)); + fillPatternValues.Add(new KeyValuePair(XLFillPatternValues.LightHorizontal, PatternValues.LightHorizontal)); + fillPatternValues.Add(new KeyValuePair(XLFillPatternValues.LightTrellis, PatternValues.LightTrellis)); + fillPatternValues.Add(new KeyValuePair(XLFillPatternValues.LightUp, PatternValues.LightUp)); + fillPatternValues.Add(new KeyValuePair(XLFillPatternValues.LightVertical, PatternValues.LightVertical)); + fillPatternValues.Add(new KeyValuePair(XLFillPatternValues.MediumGray, PatternValues.MediumGray)); + fillPatternValues.Add(new KeyValuePair(XLFillPatternValues.None, PatternValues.None)); + fillPatternValues.Add(new KeyValuePair(XLFillPatternValues.Solid, PatternValues.Solid)); } private BorderStyleValues GetBorderStyleValue(XLBorderStyleValues xlBorderStyleValue) @@ -148,33 +154,26 @@ } } - private HorizontalAlignmentValues GetHorizontalAlignmentValue(XLAlignmentHorizontalValues xlAlignmentHorizontalValue) + private void PopulateAlignmentHorizontalValues() { - switch (xlAlignmentHorizontalValue) - { - case XLAlignmentHorizontalValues.Center: return HorizontalAlignmentValues.Center; - case XLAlignmentHorizontalValues.CenterContinuous: return HorizontalAlignmentValues.CenterContinuous; - case XLAlignmentHorizontalValues.Distributed: return HorizontalAlignmentValues.Distributed; - case XLAlignmentHorizontalValues.Fill: return HorizontalAlignmentValues.Fill; - case XLAlignmentHorizontalValues.General: return HorizontalAlignmentValues.General; - case XLAlignmentHorizontalValues.Justify: return HorizontalAlignmentValues.Justify; - case XLAlignmentHorizontalValues.Left: return HorizontalAlignmentValues.Left; - case XLAlignmentHorizontalValues.Right: return HorizontalAlignmentValues.Right; - default: throw new NotImplementedException(); - } + alignmentHorizontalValues.Add(new KeyValuePair(XLAlignmentHorizontalValues.Center, HorizontalAlignmentValues.Center)); + alignmentHorizontalValues.Add(new KeyValuePair(XLAlignmentHorizontalValues.CenterContinuous, HorizontalAlignmentValues.CenterContinuous)); + alignmentHorizontalValues.Add(new KeyValuePair(XLAlignmentHorizontalValues.Distributed, HorizontalAlignmentValues.Distributed)); + alignmentHorizontalValues.Add(new KeyValuePair(XLAlignmentHorizontalValues.Fill, HorizontalAlignmentValues.Fill)); + alignmentHorizontalValues.Add(new KeyValuePair(XLAlignmentHorizontalValues.General, HorizontalAlignmentValues.General)); + alignmentHorizontalValues.Add(new KeyValuePair(XLAlignmentHorizontalValues.Justify, HorizontalAlignmentValues.Justify)); + alignmentHorizontalValues.Add(new KeyValuePair(XLAlignmentHorizontalValues.Left, HorizontalAlignmentValues.Left)); + alignmentHorizontalValues.Add(new KeyValuePair(XLAlignmentHorizontalValues.Right, HorizontalAlignmentValues.Right)); } - private VerticalAlignmentValues GetVerticalAlignmentValue(XLAlignmentVerticalValues xlAlignmentVerticalValue) + private void PopulateAlignmentVerticalValues() { - switch (xlAlignmentVerticalValue) - { - case XLAlignmentVerticalValues.Bottom: return VerticalAlignmentValues.Bottom; - case XLAlignmentVerticalValues.Center: return VerticalAlignmentValues.Center; - case XLAlignmentVerticalValues.Distributed: return VerticalAlignmentValues.Distributed; - case XLAlignmentVerticalValues.Justify: return VerticalAlignmentValues.Justify; - case XLAlignmentVerticalValues.Top: return VerticalAlignmentValues.Top; - default: throw new NotImplementedException(); - } + + alignmentVerticalValues.Add(new KeyValuePair(XLAlignmentVerticalValues.Bottom, VerticalAlignmentValues.Bottom)); + alignmentVerticalValues.Add(new KeyValuePair(XLAlignmentVerticalValues.Center, VerticalAlignmentValues.Center)); + alignmentVerticalValues.Add(new KeyValuePair(XLAlignmentVerticalValues.Distributed, VerticalAlignmentValues.Distributed)); + alignmentVerticalValues.Add(new KeyValuePair(XLAlignmentVerticalValues.Justify, VerticalAlignmentValues.Justify)); + alignmentVerticalValues.Add(new KeyValuePair(XLAlignmentVerticalValues.Top, VerticalAlignmentValues.Top)); } private PageOrderValues GetPageOrderValue(XLPageOrderValues pageOrderValue) @@ -603,7 +602,8 @@ foreach (var fillInfo in sharedFills.Values) { Fill fill = new Fill(); - PatternFill patternFill = new PatternFill() { PatternType = GetPatternValue(fillInfo.Fill.PatternType) }; + + PatternFill patternFill = new PatternFill() { PatternType = fillPatternValues.Single(p=>p.Key == fillInfo.Fill.PatternType).Value }; ForegroundColor foregroundColor = new ForegroundColor() { Rgb = fillInfo.Fill.PatternColor.ToHex() }; BackgroundColor backgroundColor = new BackgroundColor() { Rgb = fillInfo.Fill.PatternBackgroundColor.ToHex() }; @@ -661,7 +661,7 @@ var fontId = styleInfo.FontId; var fillId = styleInfo.FillId; var borderId = styleInfo.BorderId; - Boolean applyFill = GetPatternValue(styleInfo.Style.Fill.PatternType) == PatternValues.None; + Boolean applyFill = fillPatternValues.Single(p => p.Key == styleInfo.Style.Fill.PatternType).Value == PatternValues.None; IXLBorder opBorder = styleInfo.Style.Border; Boolean applyBorder = ( GetBorderStyleValue(opBorder.BottomBorder) != BorderStyleValues.None @@ -676,8 +676,8 @@ CellFormat cellFormat = new CellFormat() { NumberFormatId = (UInt32Value)(UInt32)numberFormatId, FontId = (UInt32Value)fontId, FillId = (UInt32Value)fillId, BorderId = (UInt32Value)borderId, FormatId = (UInt32Value)formatId, ApplyNumberFormat = false, ApplyFill = applyFill, ApplyBorder = applyBorder, ApplyAlignment = false, ApplyProtection = false }; Alignment alignment = new Alignment() { - Horizontal = GetHorizontalAlignmentValue(styleInfo.Style.Alignment.Horizontal), - Vertical = GetVerticalAlignmentValue(styleInfo.Style.Alignment.Vertical), + Horizontal = alignmentHorizontalValues.Single(a=>a.Key== styleInfo.Style.Alignment.Horizontal).Value, + Vertical = alignmentVerticalValues.Single(a=>a.Key == styleInfo.Style.Alignment.Vertical).Value, Indent = (UInt32)styleInfo.Style.Alignment.Indent, ReadingOrder = (UInt32)styleInfo.Style.Alignment.ReadingOrder, WrapText = styleInfo.Style.Alignment.WrapText, diff --git a/ClosedXML/ClosedXML/ClosedXML_Examples/ClosedXML_Examples.csproj b/ClosedXML/ClosedXML/ClosedXML_Examples/ClosedXML_Examples.csproj index 4ac7a71..e4c7663 100644 --- a/ClosedXML/ClosedXML/ClosedXML_Examples/ClosedXML_Examples.csproj +++ b/ClosedXML/ClosedXML/ClosedXML_Examples/ClosedXML_Examples.csproj @@ -51,6 +51,8 @@ + + diff --git a/ClosedXML/ClosedXML/ClosedXML_Examples/Creating/CreateFiles.cs b/ClosedXML/ClosedXML/ClosedXML_Examples/Creating/CreateFiles.cs new file mode 100644 index 0000000..285ec3a --- /dev/null +++ b/ClosedXML/ClosedXML/ClosedXML_Examples/Creating/CreateFiles.cs @@ -0,0 +1,40 @@ +using System; +using System.Collections.Generic; +using System.Linq; +using System.Text; +using ClosedXML_Examples.Styles; +using ClosedXML_Examples.Columns; +using ClosedXML_Examples.Rows; +using ClosedXML_Examples.Misc; +using ClosedXML_Examples.Ranges; +using ClosedXML_Examples.PageSetup; + +namespace ClosedXML_Examples +{ + public class CreateFiles + { + public static void CreateAllFiles() + { + new HelloWorld().Create(@"C:\Excel Files\Created\HelloWorld.xlsx"); + new BasicTable().Create(@"C:\Excel Files\Created\BasicTable.xlsx"); + new StyleExamples().Create(); + new ColumnSettings().Create(@"C:\Excel Files\Created\ColumnSettings.xlsx"); + new RowSettings().Create(@"C:\Excel Files\Created\RowSettings.xlsx"); + new MergeCells().Create(@"C:\Excel Files\Created\MergedCells.xlsx"); + new InsertRows().Create(@"C:\Excel Files\Created\InsertRows.xlsx"); + new InsertColumns().Create(@"C:\Excel Files\Created\InsertColumns.xlsx"); + new ColumnCollection().Create(@"C:\Excel Files\Created\ColumnCollection.xlsx"); + new DataTypes().Create(@"C:\Excel Files\Created\DataTypes.xlsx"); + new MultipleSheets().Create(@"C:\Excel Files\Created\MultipleSheets.xlsx"); + new RowCollection().Create(@"C:\Excel Files\Created\RowCollection.xlsx"); + new DefiningRanges().Create(@"C:\Excel Files\Created\DefiningRanges.xlsx"); + new ClearingRanges().Create(@"C:\Excel Files\Created\ClearingRanges.xlsx"); + new DeletingRanges().Create(@"C:\Excel Files\Created\DeletingRanges.xlsx"); + new Margins().Create(@"C:\Excel Files\Created\Margins.xlsx"); + new Page().Create(@"C:\Excel Files\Created\Page.xlsx"); + new HeaderFooters().Create(@"C:\Excel Files\Created\HeaderFooters.xlsx"); + new Sheets().Create(@"C:\Excel Files\Created\Sheets.xlsx"); + new SheetTab().Create(@"C:\Excel Files\Created\SheetTab.xlsx"); + } + } +} diff --git a/ClosedXML/ClosedXML/ClosedXML_Examples/Loading/LoadFiles.cs b/ClosedXML/ClosedXML/ClosedXML_Examples/Loading/LoadFiles.cs new file mode 100644 index 0000000..a8486aa --- /dev/null +++ b/ClosedXML/ClosedXML/ClosedXML_Examples/Loading/LoadFiles.cs @@ -0,0 +1,31 @@ +using System; +using System.Collections.Generic; +using System.Linq; +using System.Text; +using ClosedXML.Excel; + +namespace ClosedXML_Examples +{ + public class LoadFiles + { + public static void LoadAllFiles() + { + var forLoadingFolder = @"C:\Excel Files\ForLoading\"; + var forSavingFolder = @"C:\Excel Files\Modified\"; + + LoadAndSaveFile(forLoadingFolder + "HelloWorld.xlsx", forSavingFolder + "HelloWorld.xlsx"); + LoadAndSaveFile(forLoadingFolder + "DataTypes.xlsx", forSavingFolder + "DataTypes.xlsx"); + LoadAndSaveFile(forLoadingFolder + "MultipleSheets.xlsx", forSavingFolder + "MultipleSheets.xlsx"); + LoadAndSaveFile(forLoadingFolder + "styleNumberFormat.xlsx", forSavingFolder + "styleNumberFormat.xlsx"); + LoadAndSaveFile(forLoadingFolder + "styleFill.xlsx", forSavingFolder + "styleFill.xlsx"); + LoadAndSaveFile(forLoadingFolder + "styleAlignment.xlsx", forSavingFolder + "styleAlignment.xlsx"); + } + + private static void LoadAndSaveFile(String input, String output) + { + var wb = new XLWorkbook(); + wb.Load(input); + wb.SaveAs(output); + } + } +} diff --git a/ClosedXML/ClosedXML/ClosedXML_Examples/Program.cs b/ClosedXML/ClosedXML/ClosedXML_Examples/Program.cs index 0e152c4..2bf8280 100644 --- a/ClosedXML/ClosedXML/ClosedXML_Examples/Program.cs +++ b/ClosedXML/ClosedXML/ClosedXML_Examples/Program.cs @@ -15,26 +15,8 @@ { static void Main(string[] args) { - new HelloWorld().Create(@"c:\HelloWorld.xlsx"); - new BasicTable().Create(@"c:\BasicTable.xlsx"); - new StyleExamples().Create(); - new ColumnSettings().Create(@"c:\ColumnSettings.xlsx"); - new RowSettings().Create(@"c:\RowSettings.xlsx"); - new MergeCells().Create(@"c:\MergedCells.xlsx"); - new InsertRows().Create(@"c:\InsertRows.xlsx"); - new InsertColumns().Create(@"c:\InsertColumns.xlsx"); - new ColumnCollection().Create(@"c:\ColumnCollection.xlsx"); - new DataTypes().Create(@"c:\DataTypes.xlsx"); - new MultipleSheets().Create(@"c:\MultipleSheets.xlsx"); - new RowCollection().Create(@"c:\RowCollection.xlsx"); - new DefiningRanges().Create(@"c:\DefiningRanges.xlsx"); - new ClearingRanges().Create(@"c:\ClearingRanges.xlsx"); - new DeletingRanges().Create(@"c:\DeletingRanges.xlsx"); - new Margins().Create(@"c:\Margins.xlsx"); - new Page().Create(@"c:\Page.xlsx"); - new HeaderFooters().Create(@"c:\HeaderFooters.xlsx"); - new Sheets().Create(@"c:\Sheets.xlsx"); - new SheetTab().Create(@"c:\SheetTab.xlsx"); + //CreateFiles.CreateAllFiles(); + LoadFiles.LoadAllFiles(); } } } \ No newline at end of file diff --git a/ClosedXML/ClosedXML/ClosedXML_Examples/Ranges/DefiningRanges.cs b/ClosedXML/ClosedXML/ClosedXML_Examples/Ranges/DefiningRanges.cs index c20fb8c..01971a8 100644 --- a/ClosedXML/ClosedXML/ClosedXML_Examples/Ranges/DefiningRanges.cs +++ b/ClosedXML/ClosedXML/ClosedXML_Examples/Ranges/DefiningRanges.cs @@ -28,19 +28,14 @@ range2.Cell(1, 1).Value = "ws.Range(ws.Cell(2, 1).Address, ws.Cell(2, 2).Address).Merge()"; range2.Merge(); - // With two XLCells - var range3 = ws.Range(ws.Cell(3,1), ws.Cell(3,2)); - range3.Cell(1, 1).Value = "ws.Range(ws.Cell(3,1), ws.Cell(3,2)).Merge()"; - range3.Merge(); - // With two strings - var range4 = ws.Range("A4", "B4"); - range4.Cell(1, 1).Value = "ws.Range(\"A4\", \"B4\").Merge()"; + var range4 = ws.Range("A3", "B3"); + range4.Cell(1, 1).Value = "ws.Range(\"A3\", \"B3\").Merge()"; range4.Merge(); // With 4 points - var range5 = ws.Range(5, 1, 5, 2); - range5.Cell(1, 1).Value = "ws.Range(5, 1, 5, 2).Merge()"; + var range5 = ws.Range(4, 1, 4, 2); + range5.Cell(1, 1).Value = "ws.Range(4, 1, 4, 2).Merge()"; range5.Merge(); workbook.SaveAs(filePath); diff --git a/ClosedXML/ClosedXML/ClosedXML_Examples/StyleExamples.cs b/ClosedXML/ClosedXML/ClosedXML_Examples/StyleExamples.cs index 487607f..24dba30 100644 --- a/ClosedXML/ClosedXML/ClosedXML_Examples/StyleExamples.cs +++ b/ClosedXML/ClosedXML/ClosedXML_Examples/StyleExamples.cs @@ -59,11 +59,11 @@ // Public public void Create() { - new StyleFont().Create(@"c:\styleFont.xlsx"); - new StyleFill().Create(@"c:\styleFill.xlsx"); - new StyleBorder().Create(@"c:\styleBorder.xlsx"); - new StyleAlignment().Create(@"c:\styleAlignment.xlsx"); - new StyleNumberFormat().Create(@"c:\styleNumberFormat.xlsx"); + new StyleFont().Create(@"C:\Excel Files\Created\styleFont.xlsx"); + new StyleFill().Create(@"C:\Excel Files\Created\styleFill.xlsx"); + new StyleBorder().Create(@"C:\Excel Files\Created\styleBorder.xlsx"); + new StyleAlignment().Create(@"C:\Excel Files\Created\styleAlignment.xlsx"); + new StyleNumberFormat().Create(@"C:\Excel Files\Created\styleNumberFormat.xlsx"); } // Private diff --git a/ClosedXML/ClosedXML/ClosedXML_Sandbox/Program.cs b/ClosedXML/ClosedXML/ClosedXML_Sandbox/Program.cs index 4dd9c78..ae3ae4c 100644 --- a/ClosedXML/ClosedXML/ClosedXML_Sandbox/Program.cs +++ b/ClosedXML/ClosedXML/ClosedXML_Sandbox/Program.cs @@ -13,20 +13,15 @@ static void Main(string[] args) { var wb = new XLWorkbook(); - var ws = wb.Worksheets.Add("Print Areas"); - - // Column Collection examples - // Row Collection examples - // Sheets examples - // SheetTab examples - - // Add List Ranges(...) to IXLRandge - - //Apply a style to the entire sheet (not just the used cells) - - wb.SaveAs(@"c:\Sandbox.xlsx"); + wb.Load(@"c:\Initial.xlsx"); + wb.SaveAs(@"c:\Initial_Saved.xlsx"); //Console.ReadKey(); } + // Modify IXLRange to have the "IXLRanges Ranges(...)" methods + // Modify DefiningRanges example to show how to select multiple ranges + // Apply a style to the entire sheet (not just the used cells) + // Implement formulas + // Implement grouping of rows and columns } }