Tag: Excel

Miscellaneous

~ mootavatar ~ Leave a comment

How to convert Epoch timestamp to Datetime in Excel

=TEXT((G2/1000 + (“1/1/1970”-“1/1/1900″+1)*86400) / 86400,”DD/MM/YYYY HH:MM:SS”)

Reference:
https://stackoverflow.com/questions/48484020/convert-epoch-time-to-readable-time-in-excel

How to convert Hex number to Decimal Number in Excel

=HEX2DEC(XX)

Reference:
https://www.extendoffice.com/documents/excel/2168-excel-convert-hex-to-decimal.html

 

Pentaho Data Integration Community Edition 8.1.0.0-365 Spoon.bat not running on Windows 10 64 Bit

I downloaded Pentaho DI CE 8.1.0.0-365 and when I ran spoon.bat, nothing was happening.

I then ran spoon.Debug.bat to see what is happening. The following error was seen in SpoonDebug.txt

“C:\Program Files (x86)\Java\jdk1.8.0_181\bin\java.exe” “-Xms1024m” “-Xmx2048m” “-XX:MaxPermSize=256m” “-Dhttps.protocols=TLSv1,TLSv1.1,TLSv1.2” “-Djava.library.path=libswt\win32” “-DKETTLE_HOME=” “-DKETTLE_REPOSITORY=” “-DKETTLE_USER=” “-DKETTLE_PASSWORD=” “-DKETTLE_PLUGIN_PACKAGES=” “-DKETTLE_LOG_SIZE_LIMIT=” “-DKETTLE_JNDI_ROOT=” -jar launcher\launcher.jar -lib ..\libswt\win32 /level:Debug
Error occurred during initialization of VM
Could not reserve enough space for 2097152KB object heap
Java HotSpot(TM) Client VM warning: ignoring option MaxPermSize=256m; support was removed in 8.0

I opened Spoon.bat and removed “-Xmx2048m” from this line below and saved.

if “%PENTAHO_DI_JAVA_OPTIONS%”==”” set PENTAHO_DI_JAVA_OPTIONS=”-Xms1024m” “-Xmx2048m” “-XX:MaxPermSize=256m”

Now Spoon.bat runs and opens up the IDE.

How to create a PDF file from an Excel Sheet (using C#)

~ mootavatar ~ 1 Comment

The task is to create a PDF file from each sheet in each Excel file in a folder using C#. Here is one way to do it.

Import the following libraries.

using Microsoft.Office.Interop.Excel;
using System.Runtime.InteropServices;
using Application = Microsoft.Office.Interop.Excel.Application;

Access the Excel files as follows:

var directoryinfo = new DirectoryInfo(folderPath);
var files = from f in directoryinfo.GetFiles() where f.Extension.ToLower().Contains("xls") || f.Extension.ToLower().Contains("xlsx") select f;
var fileInfosList = files.ToList();
Workbook workBook = null;
object misValue = System.Reflection.Missing.Value;
Application app = new Application();

Loop through each file, then loop through each sheet in each file.

foreach (FileInfo fileInfo in fileInfosList)
{
 try
 {
  workBook = app.Workbooks.Open(fileInfo.FullName, 0, true, 5, "", "", true, XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
  Sheets workSheets = workBook.Worksheets;
  string fileName = Path.GetFileNameWithoutExtension(fileInfo.FullName);
  string folderName = fileInfo.DirectoryName;

  foreach (Worksheet workSheet in workSheets)
  {
   workSheet.PageSetup.Orientation = XlPageOrientation.xlLandscape;
   workSheet.PageSetup.FitToPagesWide = 1;
   workSheet.PageSetup.Zoom = false;
   workSheet.PageSetup.LeftMargin = 10;
   workSheet.PageSetup.RightMargin = 10;
   workSheet.PageSetup.TopMargin = 10;
   workSheet.PageSetup.BottomMargin = 10;

   string sheetname = workSheet.Name;
if (folderName != null)
{
    string destinationPath = Path.Combine(folderName, fileName + "_" + sheetname + ".pdf");

    if (!File.Exists(destinationPath))
{
     workSheet.ExportAsFixedFormat(XlFixedFormatType.xlTypePDF, destinationPath);
    }
   }
 }
catch (Exception ex)
{
// Log the exception
workBook?.Close(false, misValue, misValue);
app.Quit();
}
app.Quit();
}

That should do it!

The magic method is “ExportAsFixedFormat(XlFixedFormatType.xlTypePDF, destinationPath)” that creates the PDF file.

Before running the code, you will have to “Autofit Column Width” in Excel for each sheet to make sure the content is not truncated in the PDF.