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