diff --git a/ClosedXML/ClosedXML/ClosedXML_Sandbox/ClosedXML_Sandbox.csproj b/ClosedXML/ClosedXML/ClosedXML_Sandbox/ClosedXML_Sandbox.csproj index 3e4ab60..83f2958 100644 --- a/ClosedXML/ClosedXML/ClosedXML_Sandbox/ClosedXML_Sandbox.csproj +++ b/ClosedXML/ClosedXML/ClosedXML_Sandbox/ClosedXML_Sandbox.csproj @@ -120,7 +120,6 @@ - diff --git a/ClosedXML/ClosedXML/ClosedXML_Sandbox/Program.cs b/ClosedXML/ClosedXML/ClosedXML_Sandbox/Program.cs index 111f9ae..4d74ee3 100644 --- a/ClosedXML/ClosedXML/ClosedXML_Sandbox/Program.cs +++ b/ClosedXML/ClosedXML/ClosedXML_Sandbox/Program.cs @@ -6,191 +6,15 @@ using ClosedXML; using System.Drawing; using System.IO; -using DocumentFormat.OpenXml.Packaging; - namespace ClosedXML_Sandbox { class Program { - static void xMain(string[] args) - { - //var fileName = "DataValidation"; - var fileName = "Sandbox"; - //var fileName = "Issue_0000"; - //var wb = new XLWorkbook(String.Format(@"c:\Excel Files\ForTesting\{0}.xlsx", fileName)); - var wb = new XLWorkbook(); - var ws = wb.Worksheets.Add("SheetX"); - ws.Cell(1, 1).Value = "1 234"; - - //ws.Cell("A1").Value = "Category"; - //ws.Cell("A2").Value = "A"; - //ws.Cell("A3").Value = "B"; - //ws.Cell("B1").Value = "Value"; - //ws.Cell("B2").Value = 5; - //ws.Cell("B3").Value = 10; - - //ws.RangeUsed().CreateChart(4, 4, 22, 12); - - //workbook.Worksheet("CCR").Column("C").LastCellUsed().Address is: {C29} - //workbook.Worksheet("CCR").Column("B").LastCellUsed().Address is : {B25} - - //Now, when i use workbook.Worksheet("CCR").Range("B1:C34").RangeUsed(). - //The expect is B1:C29. - - wb.SaveAs(String.Format(@"c:\Excel Files\ForTesting\{0}_Saved.xlsx", fileName)); - - - //var start = DateTime.Now; - //var wb = new XLWorkbook(@"C:\Excel Files\ForTesting\Issue_0000.xlsx"); - //var end = DateTime.Now; - //Console.WriteLine(String.Format("Opened file in {0} seconds", (end - start).TotalSeconds)); - //var ws = wb.Worksheet(1); - //var cell = ws.Cell(100000, 13); - //Console.WriteLine(cell.GetString()); - Console.ReadKey(); - } - - - - static void CopyWorksheets(String source, XLWorkbook target) - { - var wb = new XLWorkbook(source); - foreach (var ws in wb.Worksheets) - { - ws.CopyTo(target, ws.Name); - } - } - static void Main(string[] args) { - FillStyles(); - List runningSave = new List(); - List runningLoad = new List(); - List runningSavedBack = new List(); + //Use this space to play with ClosedXML - var wb = new XLWorkbook(); - var startTotal = DateTime.Now; - var start = DateTime.Now; - foreach (var i in Enumerable.Range(1, 1)) - { - var ws = wb.Worksheets.Add("Sheet" + i); - foreach (var ro in Enumerable.Range(1, 10000)) - { - foreach (var co in Enumerable.Range(1, 20)) - { - //ws.Cell(ro, co).Style = GetRandomStyle(); - //if (rnd.Next(1, 5) == 1) - //ws.Cell(ro, co).FormulaA1 = ws.Cell(ro + 1, co + 1).Address.ToString() + " & \"-Copy\""; - //else - ws.Cell(ro, co).Value = GetRandomValue(); - } - //System.Threading.Thread.Sleep(10); - } - } - - var end = DateTime.Now; - Console.WriteLine(String.Format("Created file in {0} seconds", (end - start).TotalSeconds)); - - start = DateTime.Now; - wb.SaveAs(@"C:\Excel Files\ForTesting\Benchmark.xlsx"); - end = DateTime.Now; - var saved = (end - start).TotalSeconds; - runningSave.Add(saved); - Console.WriteLine("Saved in {0} secs.", saved); - - foreach (Int32 r in Enumerable.Range(1, 1)) - { - var start1 = DateTime.Now; - var wb1 = new XLWorkbook(@"C:\Excel Files\ForTesting\Benchmark.xlsx"); - var end1 = DateTime.Now; - var loaded = (end1 - start1).TotalSeconds; - runningLoad.Add(loaded); - Console.WriteLine("Loaded in {0} secs.", loaded); - - //var start2 = DateTime.Now; - ////wb1.SaveAs(@"C:\Excel Files\ForTesting\Benchmark_Saved.xlsx"); - //var end2 = DateTime.Now; - //var savedBack = (end2 - start2).TotalSeconds; - //runningSavedBack.Add(savedBack); - //Console.WriteLine("Saved back in {0} secs.", savedBack); - - var endTotal = DateTime.Now; - Console.WriteLine("It all took {0} secs.", (endTotal - startTotal).TotalSeconds); - } - Console.WriteLine("-------"); - Console.WriteLine("Avg Save time: {0}", runningSave.Average()); - Console.WriteLine("Avg Load time: {0}", runningLoad.Average()); - //Console.WriteLine("Avg Save Back time: {0}", runningSavedBack.Average()); - Console.ReadKey(); - } - - private static IXLStyle style1; - private static IXLStyle style2; - private static IXLStyle style3; - private static void FillStyles() - { - - style1 = XLWorkbook.DefaultStyle; - style1.Font.Bold = true; - style1.Fill.BackgroundColor = XLColor.Azure; - style1.Border.BottomBorder = XLBorderStyleValues.Medium; - style1.Alignment.Horizontal = XLAlignmentHorizontalValues.Center; - - style2 = XLWorkbook.DefaultStyle; - style2.Font.Italic = true; - style2.Fill.BackgroundColor = XLColor.Orange; - style2.Border.LeftBorder = XLBorderStyleValues.Medium; - style2.Alignment.Vertical = XLAlignmentVerticalValues.Center; - - style3 = XLWorkbook.DefaultStyle; - style3.Font.FontColor = XLColor.FromColor(Color.Red); - style3.Fill.PatternColor = XLColor.Blue; - style3.Fill.PatternType = XLFillPatternValues.DarkTrellis; - style3.Border.DiagonalBorder = XLBorderStyleValues.Dotted; - } - private static IXLStyle GetRandomStyle() - { - - var val = rnd.Next(1, 4); - if (val == 1) - { - return style1; - } - else if (val == 2) - { - return style2; - } - else - return style3; } - private static DateTime baseDate = DateTime.Now; - private static Random rnd = new Random(); - private static object GetRandomValue() - { - var val = rnd.Next(1, 7); - if (val == 1) - return Guid.NewGuid().ToString().Substring(1, 5); - else if (val == 2) - return true; - else if (val == 3) - return false; - else if (val == 4) - return DateTime.Now; - else if (val == 5) - return rnd.Next(1, 1000); - else - return (DateTime.Now - baseDate); - } - - - - class Person - { - public String Name { get; set; } - public Int32 Age { get; set; } - } - - // Save defaults to a .config file } } diff --git a/ClosedXML/ClosedXML/ClosedXML_Sandbox/XmlHelper.cs b/ClosedXML/ClosedXML/ClosedXML_Sandbox/XmlHelper.cs deleted file mode 100644 index d38395a..0000000 --- a/ClosedXML/ClosedXML/ClosedXML_Sandbox/XmlHelper.cs +++ /dev/null @@ -1,77 +0,0 @@ -using System; -using System.Collections.Generic; -using System.Linq; -using System.Text; -using System.Xml.Serialization; -using System.IO; -using System.Xml; - -namespace ClosedXML_Sandbox -{ - public class XmlHelper - { - //Creates an object from an XML string. - - /// - /// Creates an object from an XML string. - /// - /// The type of object to be constructed. - /// The XML string to convert to the object. - /// An object of type T. - public static T GetObjectFromXml(string xml) - { - T retVal; - Type objType = typeof(T); - var ser = new XmlSerializer(objType); - using (var stringReader = new StringReader(xml)) - { - retVal = (T)ser.Deserialize(stringReader); - stringReader.Close(); - } - return retVal; - } - - /// - /// Creates an XML string from an object. - /// - /// The type of object process. - /// The object from which to extract the XML. - /// An XML string. - public static String GetXmlFromObject(T obj, XmlSerializerNamespaces xmlSerializerNamespaces = null) - { - String retVal; - Type objType = typeof(T); - var ser = new XmlSerializer(objType); - //this will remove the namespace from the xml. Necessary per RealEC. - //XmlSerializerNamespaces xmlnsEmpty = new XmlSerializerNamespaces(); - //xmlnsEmpty.Add(String.Empty, String.Empty); - - using (var memStream = new MemoryStream()) - { - - ser.Serialize(memStream, obj, xmlSerializerNamespaces); - - retVal = Encoding.UTF8.GetString(memStream.GetBuffer()).Replace("\0", ""); - memStream.Close(); - } - - return retVal; - } - - public static String ConvertSpecialChars(String xmlData) - { - return - xmlData - .Replace("<", "<") - .Replace("#60;", "<") - .Replace(">", ">") - .Replace(">", ">") - .Replace(""", "\"") - .Replace("'", "\"") - .Replace("'", "'") - .Replace(""", "'") - .Replace("&", "&") - .Replace("#38;", "&"); - } - } -} diff --git a/ClosedXML/ClosedXML/ClosedXML_Sandbox_VB/Module1.vb b/ClosedXML/ClosedXML/ClosedXML_Sandbox_VB/Module1.vb index 173f612..1301cf1 100644 --- a/ClosedXML/ClosedXML/ClosedXML_Sandbox_VB/Module1.vb +++ b/ClosedXML/ClosedXML/ClosedXML_Sandbox_VB/Module1.vb @@ -3,192 +3,10 @@ Imports ClosedXML.Excel Imports System.IO Module Module1 - Sub Main1() - Dim counter As Integer = 0 - - Dim workbook As New XLWorkbook - Dim worksheet = workbook.Worksheets.Add("Sample Sheet") - - 'Row1 - worksheet.Cell(1, 1).Value = "Some Random Text" - - 'Row2 - For counter = 0 To 6 Step 1 - worksheet.Cell(2, (counter * 2) + 2).Value = Now.AddDays(counter).ToString("yyyy-MM-dd") - Next - - 'Row3 - worksheet.Cell(3, 1).Value = "val1" - worksheet.Cell(3, 2).Value = "val2" - worksheet.Cell(3, 3).Value = "val3" - - 'worksheet.PageSetup.PrintAreas.Clear() - - workbook.SaveAs("C:\Excel Files\ForTesting\Issue_5957_Saved.xlsx") - End Sub Sub Main() - Dim table = GetDataTable(17, 8280) - DataSetToClosedXML1(table, "Center") - Console.ReadKey() - End Sub - - Public Function GetDataTable(ByVal NumberOfColumns As Integer, ByVal NumberOfRows As Integer) - Dim table = New DataTable() - For co = 1 To NumberOfColumns - Dim coName = "Column" & co - Dim coType As Type - Dim val = co Mod 5 - Select Case val - Case 1 - coType = GetType(String) - Case 2 - coType = GetType(Boolean) - Case 3 - coType = GetType(Date) - Case 4 - coType = GetType(Integer) - Case Else - coType = GetType(TimeSpan) - End Select - - table.Columns.Add(coName, coType) - Next - Dim baseDate = Date.Now - Dim rnd = New Random() - For ro = 1 To NumberOfRows - Dim dr As DataRow = table.NewRow() - For co = 1 To NumberOfColumns - Dim coName As String = "Column" & co - Dim coValue As Object - Dim val = co Mod 5 - Select Case val - Case 1 - coValue = Guid.NewGuid().ToString().Substring(1, 5) - Case 2 - coValue = (ro Mod 2 = 0) - Case 3 - coValue = DateTime.Now - Case 4 - coValue = rnd.Next(1, 1000) - Case Else - coValue = (DateTime.Now - baseDate) - End Select - dr.Item(coName) = coValue - Next - table.Rows.Add(dr) - Next - - Return table - End Function - - Public Sub DataSetToClosedXML1(ByVal MyDataTable As DataTable, ByVal BodyAlignment As String) - - 'based on ClosedXML.dll downloaded from this website (free license: Version 0.39.0 12/30/2010) (ASPNET 3.5, not 4.0) - add to References - 'http://closedxml.codeplex.com/ - - 'requires DocumentFormat.OpenXML.dll - add to References - 'DLL can be obtained by downloading the full OpenXML SDK 2.0 package OR just the assembly containing the DLL - 'http://www.microsoft.com/downloads/en/details.aspx?FamilyID=c6e744e5-36e9-45f5-8d8c-331df206e0d0&DisplayLang=en - - 'OpenXML also requires Reference to the WindowsBase assembly (WindowsBase.dll) in order to use the System.IO.Packaging namespace. - - 'inputs: dataset; filename; Tab name; BodyAlignment = None (default), Left, Center, Right; optional SaveToDisk = Yes - 'code sample: DataSetToClosedXML(MyDS, "TestFile", "Test", "Left") - - - On Error GoTo ErrHandler - - - Dim wb As ClosedXML.Excel.XLWorkbook = New ClosedXML.Excel.XLWorkbook - - Dim ws As ClosedXML.Excel.IXLWorksheet = wb.Worksheets.Add("Sheet1") - - Dim column As DataColumn - Dim ColCount As Integer = MyDataTable.Columns.Count - Dim RowCount As Integer = MyDataTable.Rows.Count - Dim ColLetter As String - - For Each column In MyDataTable.Columns - - ws.Cell(1, MyDataTable.Columns.IndexOf(column) + 1).Value = column.ColumnName - - Next - - Dim contentRow As DataRow - Dim r, c As Integer - - For r = 0 To MyDataTable.Rows.Count - 1 - - contentRow = MyDataTable.Rows(r) - - For c = 0 To ColCount - 1 - - 'adjust for header in first row - ws.Cell(r + 2, c + 1).Value = contentRow(c) - - 'format for data type: - - Select Case MyDataTable.Columns(c).DataType.ToString - Case "System.Int16", "System.Int32", "System.Int64", "System.UInt16", "System.UInt32", "System.UInt64", "System.Byte", "System.SByte" - ws.Cell(r + 2, c + 1).Style.NumberFormat.NumberFormatId = 3 - Case "System.Single", "System.Double", "System.Decimal" - ws.Cell(r + 2, c + 1).Style.NumberFormat.NumberFormatId = 0 - Case "System.Boolean" - ws.Cell(r + 2, c + 1).Value = "'" & contentRow(c).ToString() - Case "System.DateTime" - ws.Cell(r + 2, c + 1).Style.NumberFormat.NumberFormatId = 14 - Case "System.String", "System.Char", "System.TimeSpan" - ws.Cell(r + 2, c + 1).Value = "'" & contentRow(c).ToString() - Case "System.Byte[]" - ws.Cell(r + 2, c + 1).DataType = ClosedXML.Excel.XLCellValues.Text - Case Else - ws.Cell(r + 2, c + 1).DataType = ClosedXML.Excel.XLCellValues.Text - End Select - - Next - - Next - - 'header: set to Bold - ws.Range(1, 1, 1, ColCount).Style.Font.Bold = True - - 'header column alignment (always centered) - ws.Range(1, 1, 1, ColCount).Style.Alignment.Horizontal = ClosedXML.Excel.XLAlignmentHorizontalValues.Center - - 'body column alignment - Select Case BodyAlignment - Case "None" - 'do nothing (default) - Case "Left" - ws.Range(2, 1, RowCount + 1, ColCount).Style.Alignment.Horizontal = ClosedXML.Excel.XLAlignmentHorizontalValues.Left - Case "Center" - ws.Range(2, 1, RowCount + 1, ColCount).Style.Alignment.Horizontal = ClosedXML.Excel.XLAlignmentHorizontalValues.Center - Case "Right" - ws.Range(2, 1, RowCount + 1, ColCount).Style.Alignment.Horizontal = ClosedXML.Excel.XLAlignmentHorizontalValues.Right - End Select - - 'auto-fit cols - 'ws.Columns(1, ColCount).AdjustToContents() - - 'View: freeze pane - freezes top row (headers) - ws.SheetView.FreezeRows(1) - - - 'save to disk - Dim startTime = DateTime.Now - wb.SaveAs("C:\Excel Files\ForTesting\Benchmark.xlsx") - Dim endTime = DateTime.Now - Console.WriteLine("Saved in {0} secs.", (endTime - startTime).TotalSeconds) - - Exit Sub - -ErrHandler: - 'this is a library Sub that displays a javascript alert - Console.WriteLine("Error: " & Err.Description) + 'Use this space to play with ClosedXML End Sub - - End Module