Dev Stack Source

Answer : To “reverse pivot”, “unpivot” or “flatten”: For Excel 2003: Activate any cell in your summary table and choose Data - PivotTable and PivotChart Report: For later versions access the Wizard with Alt + D , P . For Excel for Mac 2011, it's ⌘ + Alt + P (See here). Select Multiple consolidation ranges and click Next . In “Step 2a of 3”, choose I will create the page fields and click Next . In “Step 2b of 3” specify your summary table range in the Range field (A1:E5 for the sample data) and click Add , then Next . In “Step 3 of 3”, select a location for the pivot table (the existing sheet should serve, as the PT is only required temporarily): Click Finish to create the pivot table: Drill down (ie double-click) on the intersect of the Grand Totals (here Cell V7 or 7 ): The PT may now be deleted. The resulting Table may be converted to a conventional array of cells by selecting Table in the Quick Menu (right-click in the Table) and Convert to Range . There is a video ...

Answer : If you are wanting to just copy the whole column, you can simplify the code a lot by doing something like this: Sub CopyCol() Sheets("Sheet1").Columns(1).Copy Sheets("Sheet2").Columns(2).PasteSpecial xlPasteValues End Sub Or Sub CopyCol() Sheets("Sheet1").Columns("A").Copy Sheets("Sheet2").Columns("B").PasteSpecial xlPasteValues End Sub Or if you want to keep the loop Public Sub CopyrangeA() Dim firstrowDB As Long, lastrow As Long Dim arr1, arr2, i As Integer firstrowDB = 1 arr1 = Array("BJ", "BK") arr2 = Array("A", "B") For i = LBound(arr1) To UBound(arr1) Sheets("Sheet1").Columns(arr1(i)).Copy Sheets("Sheet2").Columns(arr2(i)).PasteSpecial xlPasteValues Next Application.CutCopyMode = False End Sub since you only want values copied, you can pass the values of arr1 directly to arr2 and avoid c...

Answer : from datetime import datetime excel_date = 42139 dt = datetime.fromordinal(datetime(1900, 1, 1).toordinal() + excel_date - 2) tt = dt.timetuple() print dt print tt As mentioned by J.F. Sebastian, this answer only works for any date after 1900/03/01 EDIT: (in answer to @R.K) If your excel_date is a float number, use this code: def floatHourToTime(fh): h, r = divmod(fh, 1) m, r = divmod(r*60, 1) return ( int(h), int(m), int(r*60), ) excel_date = 42139.23213 dt = datetime.fromordinal(datetime(1900, 1, 1).toordinal() + int(excel_date) - 2) hour, minute, second = floatHourToTime(excel_date % 1) dt = dt.replace(hour=hour, minute=minute, second=second) The module xlrd provides a function xldate_as_tuple to convert Excel's numerical date format to a tuple (year, month, day, hour, minute, nearest_second) . You can then use datetime.datetime to convert the tuple into a datetime -object. from datetime import datetime import xlrd excel_da...

Answer : I found a solution to my issue and after a request, will post it here to help others. Apologies if I missed any details, it's been a while since I worked on this solution. The first thing that is required is to install Openoffice.org on the server. I requested my hosting provider to install the open office RPM on my VPS. This can be done through WHM directly. Now that the server has the capability to handle MS Office files you are able to convert the files by executing command line instructions via PHP. To handle this, I found PyODConverter : https://github.com/mirkonasato/pyodconverter I created a directory on the server and placed the PyODConverter python file within it. I also created a plain text file above the web root (I named it "adocpdf"), with the following command line instructions in it: directory=$1 filename=$2 extension=$3 SERVICE='soffice' if [ "`ps ax|grep -v grep|grep -c $SERVICE`" -lt 1 ]; then unset DISPLAY /usr/bin/soffice...