DYN-1819 ExportExcel Write as String Option (#11291)

StudioLE · SHKnudsen · web-flow · commit 5b9643f80f73 · 2020-12-11T16:40:29.000-05:00
* ExportExcel write as string option * add new excel node with writeAsString argument * update name * Revert "update name" This reverts commit 0ddd62d. * Update libraryItems.json * overload WriteToFile + add icons back for old excel node * make internal WriteData method * remove line * add test Co-authored-by: SHKnudsen <sylvesterknudsen@gmail.com>
diff --git a/src/Libraries/DSOffice/DSOfficeImages.resx b/src/Libraries/DSOffice/DSOfficeImages.resx
diff --git a/src/Libraries/DSOffice/Excel.cs b/src/Libraries/DSOffice/Excel.cs
@@ -275,28 +275,28 @@ public static WorkBook SaveAsExcelWorkbook(WorkBook workbook, string filename)
         [IsVisibleInDynamoLibrary(false)]
         public static object[][] ReadFromFile(FileInfo file, string sheetName, bool readAsStrings = false, bool showExcel = true)
         {
-        	object[][] data;
-        	
-        	if(!showExcel)
-        	{
-        		ExcelInterop.ShowOnStartup = false;
-        	}
-        	WorkBook wb = WorkBook.ReadExcelFile(file.FullName);
+            object[][] data;
+
+            if (!showExcel)
+            {
+                ExcelInterop.ShowOnStartup = false;
+            }
+            WorkBook wb = WorkBook.ReadExcelFile(file.FullName);
             WorkSheet ws = wb.GetWorksheetByName(sheetName);
             if (readAsStrings)
             {
-            	data = ws.GetData(true);
+                data = ws.GetData(true);
             }
             else
             {
-            	data = ws.Data;
+                data = ws.Data;
             }
-            if(!showExcel)
+            if (!showExcel)
             {
-            	wb.CloseHidden();
-            	ExcelInterop.ShowOnStartup = true;
+                wb.CloseHidden();
+                ExcelInterop.ShowOnStartup = true;
             }
-            
+
             return data;
         }
 
@@ -325,10 +325,15 @@ public static object[][] Read(string filePath, string sheetName)
         /// <search>office,excel,spreadsheet</search>
         [IsVisibleInDynamoLibrary(false)]
         public static object[][] WriteToFile(string filePath, string sheetName, int startRow, int startCol, object[][] data, bool overWrite = false)
+        {
+            return WriteData(filePath, sheetName, startRow, startCol, data, overWrite);
+        }
+
+        internal static object[][] WriteData(string filePath, string sheetName, int startRow, int startCol, object[][] data, bool overWrite = false, bool writeAsString = false)
         {
             WorkBook wb = new WorkBook(filePath);
             WorkSheet ws = new WorkSheet(wb, sheetName, overWrite);
-            ws = ws.WriteData(startRow, startCol, data);
+            ws = ws.WriteData(startRow, startCol, data, writeAsString);
             return ws.Data;
         }
     }
@@ -522,8 +527,9 @@ internal WorkSheet(Worksheet ws, WorkBook wb)
         /// <param name="startRow"></param>
         /// <param name="startColumn"></param>
         /// <param name="data"></param>
+        /// <param name="writeAsString"></param>
         /// <returns></returns>
-        internal WorkSheet WriteData(int startRow, int startColumn, object[][] data)
+        internal WorkSheet WriteData(int startRow, int startColumn, object[][] data, bool writeAsString = false)
         {
             startRow = Math.Max(0, startRow);
             startColumn = Math.Max(0, startColumn);
@@ -537,6 +543,8 @@ internal WorkSheet WriteData(int startRow, int startColumn, object[][] data)
             var c1 = (Range)ws.Cells[startRow + 1, startColumn + 1];
             var c2 = (Range)ws.Cells[startRow + numRows, startColumn + numColumns];
             var range = ws.Range[c1, c2];
+            if(writeAsString)
+                range.NumberFormat = "@";
             range.Value = rangeData;
 
             wb.Save();
@@ -833,9 +841,33 @@ public static object[][] ImportExcel(FileInfo file, string sheetName, bool readA
         /// <param name="overWrite"></param>
         /// <returns name="data">Data written to the spreadsheet.</returns>
         /// <search>office,excel,spreadsheet</search>
+        [Obsolete("Use ExportToExcel instead.")] 
         public static object[][] ExportExcel(string filePath, string sheetName, int startRow, int startCol, object[][] data, bool overWrite = false)
         {
-            return Excel.WriteToFile(filePath, sheetName, startRow, startCol, data, overWrite);
+            return Excel.WriteData(filePath, sheetName, startRow, startCol, data, overWrite);
+        }
+
+        /// <summary>
+        ///     Write data to a Microsoft Excel spreadsheet. Data is written by row
+        ///     with sublists to be written in successive rows. Rows and columns are
+        ///     zero-indexed; for example, the value in the data list at [0,0] will
+        ///     be written to cell A1. Null values and empty lists are written to Excel 
+        ///     as empty cells. This node requires Microsoft Excel to be installed. 
+        /// </summary>
+        /// <param name="filePath">File path to the Microsoft Excel spreadsheet.</param>
+        /// <param name="sheetName">Name of the worksheet to write data to.</param>
+        /// <param name="startRow">Start row for writing data. Enter 0 for Row 1, 1 for Row 2, etc.</param>
+        /// <param name="startCol">
+        ///     Start column for writing data. Enter 0 for Column A, 1 for Column B, etc.
+        /// </param>
+        /// <param name="data">Data to write to the spreadsheet.</param>
+        /// <param name="overWrite"></param>
+        /// <param name="writeAsString">Toggle to switch between writing Excel file as strings.</param>
+        /// <returns name="data">Data written to the spreadsheet.</returns>
+        /// <search>office,excel,spreadsheet</search>
+        public static object[][] ExportToExcel(string filePath, string sheetName, int startRow, int startCol, object[][] data, bool overWrite = false, bool writeAsString = false)
+        {
+            return Excel.WriteData(filePath, sheetName, startRow, startCol, data, overWrite, writeAsString);
         }
     }
 }
diff --git a/src/LibraryViewExtension/web/library/layoutSpecs.json b/src/LibraryViewExtension/web/library/layoutSpecs.json
@@ -1032,7 +1032,7 @@
                   "path": "DSOffice.DSOffice.Data.ImportExcel"
                 },
                 {
-                  "path": "DSOffice.DSOffice.Data.ExportExcel"
+                  "path": "DSOffice.DSOffice.Data.ExportToExcel"
                 },
                 {
                   "path": "DSOffice.DSOffice.Data.ImportCSV"
diff --git a/src/LibraryViewExtensionMSWebBrowser/web/library/layoutSpecs.json b/src/LibraryViewExtensionMSWebBrowser/web/library/layoutSpecs.json
@@ -1028,7 +1028,7 @@
                   "path": "DSOffice.DSOffice.Data.ImportExcel"
                 },
                 {
-                  "path": "DSOffice.DSOffice.Data.ExportExcel"
+                  "path": "DSOffice.DSOffice.Data.ExportToExcel"
                 },
                 {
                   "path": "DSOffice.DSOffice.Data.ImportCSV"
diff --git a/test/Libraries/DynamoMSOfficeTests/ExcelTests.cs b/test/Libraries/DynamoMSOfficeTests/ExcelTests.cs
@@ -1180,6 +1180,40 @@ public void TestOverwriteToSingleSheetExcel()
             Assert.IsTrue(wb.WorkSheets.Length == 1);
         }
 
+
+        [Test, Category("ExcelTest")]
+        public void CanExportToExcelAsString()
+        {
+            // Arrange
+            string openPath = Path.Combine(TestDirectory, @"core\excel\ExportToExcelAsString.dyn");
+            ViewModel.OpenCommand.Execute(openPath);
+
+            var filename = ViewModel.Model.CurrentWorkspace.FirstNodeFromWorkspace<Filename>();
+
+            // Act
+            // remap the filename as Excel requires an absolute path
+            filename.Value = filename.HintPath.Replace(@"..\..\..\test", TestDirectory);
+
+            ViewModel.HomeSpace.Run();
+
+            // Codeblock that holds the data that will be exported (value = 12)
+            var dataCodeBlock = ViewModel.Model.CurrentWorkspace.NodeFromWorkspace("8070c45660c349d5bbc143febd22cfcf") as CodeBlockNodeModel;           
+            var wb = Excel.ReadExcelFile(filename.Value);
+
+            // Running the graph exports the value 12 to excel, 
+            // getting that value here so we can check that the value has been converted to a string.
+            var excelExportDataType = wb.WorkSheets
+                .FirstOrDefault()
+                .Data
+                .FirstOrDefault()
+                .FirstOrDefault()
+                .GetType();
+
+            // Assert
+            Assert.AreEqual(typeof(Int64), dataCodeBlock.CachedValue.Data.GetType());
+            Assert.AreEqual(typeof(string), excelExportDataType);
+        }
+
         #endregion
 
         #region Saving
diff --git a/test/ViewExtensionLibraryTests/resources/libraryItems.json b/test/ViewExtensionLibraryTests/resources/libraryItems.json
@@ -5649,9 +5649,9 @@
       "keywords": ""
     },
     {
-      "fullyQualifiedName": "DSOffice.Data.ExportExcel",
-      "iconUrl": "https://localhost/icons/DSOffice.Data.ExportExcel.Small?path=DSOffice.dll",
-      "contextData": "DSOffice.Data.ExportExcel@string,string,int,int,var[][],bool",
+      "fullyQualifiedName": "DSOffice.Data.ExportToExcel",
+      "iconUrl": "https://localhost/icons/DSOffice.Data.ExportToExcel.Small?path=DSOffice.dll",
+      "contextData": "DSOffice.Data.ExportToExcel@string,string,int,int,var[][],bool,bool",
       "parameters": null,
       "itemType": "action",
       "keywords": ""
diff --git a/test/core/excel/ExportToExcelAsString.dyn b/test/core/excel/ExportToExcelAsString.dyn

Original file line number	Diff line number	Diff line change
`@@ -1032,7 +1032,7 @@`
`1032`	`1032`	`"path": "DSOffice.DSOffice.Data.ImportExcel"`
`1033`	`1033`	`},`
`1034`	`1034`	`{`
`1035`		`- "path": "DSOffice.DSOffice.Data.ExportExcel"`
	`1035`	`+ "path": "DSOffice.DSOffice.Data.ExportToExcel"`
`1036`	`1036`	`},`
`1037`	`1037`	`{`
`1038`	`1038`	`"path": "DSOffice.DSOffice.Data.ImportCSV"`
Original file line number	Diff line number	Diff line change
`@@ -1028,7 +1028,7 @@`
`1028`	`1028`	`"path": "DSOffice.DSOffice.Data.ImportExcel"`
`1029`	`1029`	`},`
`1030`	`1030`	`{`
`1031`		`- "path": "DSOffice.DSOffice.Data.ExportExcel"`
	`1031`	`+ "path": "DSOffice.DSOffice.Data.ExportToExcel"`
`1032`	`1032`	`},`
`1033`	`1033`	`{`
`1034`	`1034`	`"path": "DSOffice.DSOffice.Data.ImportCSV"`