Excel 檔案異常大 2007(xlsx) 存成2003(xls)

Excel xlsx 另存 xls 檔案異常的大

最有可能的原因可能是該檔案是由office2007以上的版本所建立的。但為了讓office2003可以讀取該檔,所以將它另存為xls(2003版本的檔案),但新版有許多新功能或樣式,然而舊版卻沒有。所以另存時會有一堆看不見的物件加到舊版格式的檔案中。因此新版檔案(xlsx)另存舊版檔案類型(xls)就變大很多很多。

解決辦法:利用 libreoffice 讀取被另存舊版的檔案,再做一次另存新檔的動作。但不變更目前的檔案格式。

libreoffice (xls) -> 另存 (xls) 檔案回復正常。

 

VBA 巨集取出 Excel 中的圖檔

出至:VBA – Extract Pictures from Excel

Last month, I have written Excel Automation Using VBScript. Today this post is to extract Pictures from Excel. Generally We can not use Export method for pictures. But we can use for Excel charts. I tried to extract our SVG chart image by macro and I succeed on it. I got help from Export pictures from Excel Below I’ve given the VBA Macro code.

Sub GetFirstPicture()

Dim sCurrPath As String
Dim aWorkSheet As Excel.Worksheet
Dim aShape As Excel.Shape
Dim aShapeChart As Excel.Shape
Dim aPicture As Variant
Dim aChart As Excel.Chart
Dim sCurrentSheet As String

Dim aImage As Variant
Dim iIndex As Integer
Dim iShapeCount As Integer

Dim MyChart As String, MyPicture As String
Dim PicWidth As Long, PicHeight As Long
Dim sChartJpg As String
Dim sChartGif As String
Dim sChartBmp As String

‘On Error GoTo ErrHandler
On Error Resume Next

Application.ScreenUpdating = False

sCurrPath = “D:\VB\MyTrials\ChartExpFromXL"
sChartJpg = “D:\VB\MyTrials\ChartExpFromXL.jpg"
sChartGif = “D:\VB\MyTrials\ChartExpFromXL.gif"
sChartBmp = “D:\VB\MyTrials\ChartExpFromXL.bmp"

Set aWorkSheet = ActiveWorkbook.ActiveSheet
sCurrentSheet = aWorkSheet.Name

‘MsgBox CStr(msoTrue) + " value for MsoTrue"
‘ MsoTrue equals to -1
MsgBox “Shapes count " + CStr(aWorkSheet.Shapes.Count)
For iIndex = 1 To aWorkSheet.Shapes.Count

Set aShape = aWorkSheet.Shapes(iIndex)
MyPicture = aShape.Name
MsgBox aShape.Name + " Name, " + Str(aShape.Type)
‘Picture 1 Name, 13
If Left(aShape.Name, 7) = “Picture" Then
With aShape
PicHeight = .Height
PicWidth = .Width
End With
‘Set aChart = aWorkSheet.ChartObjects(1)
Set aChart = ActiveWorkbook.Charts.Add
ActiveWorkbook.ActiveChart.Location Where:=xlLocationAsObject, Name:=sCurrentSheet
iShapeCount = aWorkSheet.Shapes.Count
Set aShapeChart = aWorkSheet.Shapes(iShapeCount)
MyChart = aShapeChart.Name ‘"Chart " & Str(aWorkSheet.Shapes.Count)

aShapeChart.Width = PicWidth
aShapeChart.Height = PicHeight

With aWorkSheet
aShape.Copy

With ActiveChart ‘aChart
.ChartArea.Select
.Paste
End With

.ChartObjects(1).Chart.Export Filename:=sChartJpg, FilterName:="jpg", Interactive:=True
.ChartObjects(1).Chart.Export Filename:=sChartGif
.ChartObjects(1).Chart.Export Filename:=sCurrPath & “.png"
‘Not working .ChartObjects(1).Chart.Export Filename:=sChartBmp, FilterName:="bmp"
aShapeChart.Cut
End With

Application.ScreenUpdating = True

MsgBox “Completed."
Exit Sub

End If

Next

MsgBox “Completed."
Exit Sub

ErrHandler:
MsgBox “Error # " & CStr(Err.Number) & " " & Err.Description & " " & Err.Source
Err.Clear ‘ Clear the error.
End Sub

Extract Images from an Excel Document

出至:http://stackoverflow.com/questions/5503015/extract-images-from-an-excel-document

First, use unoconv to convert the .xls to .pdf:

http://dag.wieers.com/home-made/unoconv/

On Ubuntu 10.10 command line:

sudo apt-get install unoconv
unoconv -f pdf file.xls
Then extract the images from the pdf using pdfimages (which seems to come bundled with Ubuntu):

http://en.wikipedia.org/wiki/Pdfimages

Back on the command line:

pdfimages file.pdf fileimage
And done! All of the images in the .xls are now in separate files in the directory. This could be done very easily on most Linux systems using your language of choice. In python, for example:

import subprocess
subprocess.call([‘unoconv’,’-f’,’pdf’,’file.xls’])
subprocess.call([‘pdfimages’,’file.pdf’,’fileimage’])

I would love to hear a simpler solution if somebody has one.
******************************************************************************************

If a excel file is a compressed file.(xlsx)

$ unzip file.xlsx

in xl/media/ are all pictures

Python Excel Mini Cookbook

From: Python Excel Mini Cookbook

Python Excel Mini Cookbook
Posted on October 5, 2009
To get you started, I’ve illustrated a number of common tasks you can do with Python and Excel. Each program below is a self contained example, just copy it, paste it and run it. A few things to note:

These examples were tested in Excel 2007, they should work fine in earlier versions as well after changing the extension of the file within the wb.SaveAs() statement from .xlsx to .xls
If you’re new to this, I recommend typing these examples by hand into IDLE, IPython or the Python interpreter, then watching the effect in Excel as you enter the commands. To make Excel visible add the line excel.Visible = True after the excel =win32.gencache.EnsureDispatch(‘Excel.Application’) line in the script
These are simple examples with no error checking. Make sure the output files doesn’t exist before running the script. If the script crashes, it may leave a copy of Excel running in the background. Open the Windows Task Manager and kill the background Excel process to recover.
These examples contain no optimization. You typically wouldn’t use a for loop to iterate through data in individual cells, it’s provided here for illustration only.
Open Excel, Add a Workbook

The following script simply invokes Excel, adds a workbook and saves the empty workbook.

#
# Add a workbook and save (Excel 2007)
# For older versions of excel, use the .xls file extension
#
import win32com.client as win32
excel = win32.gencache.EnsureDispatch(‘Excel.Application’)
wb = excel.Workbooks.Add()
wb.SaveAs(‘add_a_workbook.xlsx’)
excel.Application.Quit()
Open an Existing Workbook

This script opens an existing workbook and displays it (note the statement excel.Visible =True). The file workbook1.xlsx must already exist in your “My Documents” directory. You can also open spreadsheet files by specifying the full path to the file as shown below. Using r’in the statement r’C:\myfiles\excel\workbook2.xlsx’ automatically escapes the backslash characters and makes the file name a bit more concise.

#
# Open an existing workbook
#
import win32com.client as win32
excel = win32.gencache.EnsureDispatch(‘Excel.Application’)
wb = excel.Workbooks.Open(‘workbook1.xlsx’)
# Alternately, specify the full path to the workbook
# wb = excel.Workbooks.Open(r’C:\myfiles\excel\workbook2.xlsx’)
excel.Visible = True
Add a Worksheet

This script creates a new workbook with three sheets, adds a fourth worksheet and names it MyNewSheet.

#
# Add a workbook, add a worksheet,
# name it ‘MyNewSheet’ and save
#
import win32com.client as win32
excel = win32.gencache.EnsureDispatch(‘Excel.Application’)
wb = excel.Workbooks.Add()
ws = wb.Worksheets.Add()
ws.Name = “MyNewSheet"
wb.SaveAs(‘add_a_worksheet.xlsx’)
excel.Application.Quit()
Ranges and Offsets

This script illustrates different techniques for addressing cells by using the Cells() and Range()operators. Individual cells can be addressed using Cells(row,column), where row is the row number, column is the column number, both start from 1. Groups of cells can be addressed using Range(), where the argument in the parenthesis can be a single cell denoted by its textual name (eg “A2″), a group noted by a textual name with a colon (eg “A3:B4″) or a group denoted with two Cells() identifiers (eg ws.Cells(1,1),ws.Cells(2,2)). The Offsetmethod provides a way to address a cell based on a reference to another cell.

#
# Using ranges and offsets
#
import win32com.client as win32
excel = win32.gencache.EnsureDispatch(‘Excel.Application’)
wb = excel.Workbooks.Add()
ws = wb.Worksheets(“Sheet1″)
ws.Cells(1,1).Value = “Cell A1″
ws.Cells(1,1).Offset(2,4).Value = “Cell D2″
ws.Range(“A2″).Value = “Cell A2″
ws.Range(“A3:B4″).Value = “A3:B4″
ws.Range(“A6:B7,A9:B10″).Value = “A6:B7,A9:B10″
wb.SaveAs(‘ranges_and_offsets.xlsx’)
excel.Application.Quit()
Autofill Cell Contents

This script uses Excel’s autofill capability to examine data in cells A1 and A2, then autofill the remaining column of cells through A10.

#
# Autofill cell contents
#
import win32com.client as win32
excel = win32.gencache.EnsureDispatch(‘Excel.Application’)
wb = excel.Workbooks.Add()
ws = wb.Worksheets(“Sheet1″)
ws.Range(“A1″).Value = 1
ws.Range(“A2″).Value = 2
ws.Range(“A1:A2″).AutoFill(ws.Range(“A1:A10″),win32.constants.xlFillDefault)
wb.SaveAs(‘autofill_cells.xlsx’)
excel.Application.Quit()
Cell Color

This script illustrates adding an interior color to the cell using Interior.ColorIndex. Column A, rows 1 through 20 are filled with a number and assigned that ColorIndex.

#
# Add an interior color to cells
#
import win32com.client as win32
excel = win32.gencache.EnsureDispatch(‘Excel.Application’)
wb = excel.Workbooks.Add()
ws = wb.Worksheets(“Sheet1″)
for i in range (1,21):
ws.Cells(i,1).Value = i
ws.Cells(i,1).Interior.ColorIndex = i
wb.SaveAs(‘cell_color.xlsx’)
excel.Application.Quit()
Column Formatting

This script creates two columns of data, one narrow and one wide, then formats the column width with the ColumnWidth property. You can also use the Columns.AutoFit() function to autofit all columns in the spreadsheet.

#
# Set column widths
#
import win32com.client as win32
excel = win32.gencache.EnsureDispatch(‘Excel.Application’)
wb = excel.Workbooks.Add()
ws = wb.Worksheets(“Sheet1″)
ws.Range(“A1:A10″).Value = “A"
ws.Range(“B1:B10″).Value = “This is a very long line of text"
ws.Columns(1).ColumnWidth = 1
ws.Range(“B:B").ColumnWidth = 27
# Alternately, you can autofit all columns in the worksheet
# ws.Columns.AutoFit()
wb.SaveAs(‘column_widths.xlsx’)
excel.Application.Quit()
Copying Data from Worksheet to Worksheet

This script uses the FillAcrossSheets() method to copy data from one location to all other worksheets in the workbook. Specifically, the data in the range A1:J10 is copied from Sheet1 to sheets Sheet2 and Sheet3.

#
# Copy data and formatting from a range of one worksheet
# to all other worksheets in a workbook
#
import win32com.client as win32
excel = win32.gencache.EnsureDispatch(‘Excel.Application’)
wb = excel.Workbooks.Add()
ws = wb.Worksheets(“Sheet1″)
ws.Range(“A1:J10″).Formula = “=row()*column()"
wb.Worksheets.FillAcrossSheets(wb.Worksheets(“Sheet1″).Range(“A1:J10″))
wb.SaveAs(‘copy_worksheet_to_worksheet.xlsx’)
excel.Application.Quit()
Format Worksheet Cells

This script creates two columns of data, then formats the font type and font size used in the worksheet. Five different fonts and sizes are used, the numbers are formatted using a monetary format.

#
# Format cell font name and size, format numbers in monetary format
#
import win32com.client as win32
excel = win32.gencache.EnsureDispatch(‘Excel.Application’)
wb = excel.Workbooks.Add()
ws = wb.Worksheets(“Sheet1″)

for i,font in enumerate([“Arial","Courier New","Garamond","Georgia","Verdana"]):
ws.Range(ws.Cells(i+1,1),ws.Cells(i+1,2)).Value = [font,i+i]
ws.Range(ws.Cells(i+1,1),ws.Cells(i+1,2)).Font.Name = font
ws.Range(ws.Cells(i+1,1),ws.Cells(i+1,2)).Font.Size = 12+i

ws.Range(“A1:A5″).HorizontalAlignment = win32.constants.xlRight
ws.Range(“B1:B5″).NumberFormat = “$###,##0.00″
ws.Columns.AutoFit()
wb.SaveAs(‘format_cells.xlsx’)
excel.Application.Quit()
Setting Row Height

This script illustrates row height. Similar to column height, row height can be set with the RowHeight method. You can also useAutoFit() to automatically adjust the row height based on cell contents.

#
# Set row heights and align text within the cell
#
import win32com.client as win32
excel = win32.gencache.EnsureDispatch(‘Excel.Application’)
wb = excel.Workbooks.Add()
ws = wb.Worksheets(“Sheet1″)
ws.Range(“A1:A2″).Value = “1 line"
ws.Range(“B1:B2″).Value = “Two\nlines"
ws.Range(“C1:C2″).Value = “Three\nlines\nhere"
ws.Range(“D1:D2″).Value = “This\nis\nfour\nlines"
ws.Rows(1).RowHeight = 60
ws.Range(“2:2″).RowHeight = 120
ws.Rows(1).VerticalAlignment = win32.constants.xlCenter
ws.Range(“2:2″).VerticalAlignment = win32.constants.xlCenter

# Alternately, you can autofit all rows in the worksheet
# ws.Rows.AutoFit()

wb.SaveAs(‘row_height.xlsx’)
excel.Application.Quit()

 

Prerequisites

Python (refer to http://www.python.org)

Win32 Python module (refer to http://sourceforge.net/projects/pywin32)

Microsoft Excel (refer to http://office.microsoft.com/excel)

Source Files and Scripts

Source for the program and data text file are available athttp://github.com/pythonexcels/examples

That’s all for now, thanks — Dan

如何使用 VLOOKUP 或 HLOOKUP 尋找完全相同的值

出至:http://support.microsoft.com/kb/181213/zh-tw

這些函數的語法定義如下。

VLOOKUP 函數

=VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)

其中:
Argument 引數的定義
———————————————————————

Lookup_value 在陣列第一欄中所要尋找的值。

Table_array 查閱資料中的資訊表格。

col_index 相符數值應該傳回的 table_array
應傳回相符的值。

Range_lookup 無論您想要尋找完全相符或近似的相符值,
其為指定的邏輯值。
如果是 TRUE 或是省略,就會傳回近似的相符值;
也就是說,如果沒有找到完全相符的值,
就會傳回下一個小於 Lookup_value 的最大值
。如果是 FALSE,VLOOKUP 則會尋找完全相符值。如果
找不到完全相同的值,就會傳回 #N/A 錯誤值。

注意 如果 Range_lookup 是 TRUE 或是省略 (近似的相符值),在 Table_array 第一欄中所要尋找的值必須以遞增順序排序。如果 Range_lookup 是 FALSE (完全相符值),Table_array 就不需要排序。
使用 FALSE 做為 Range_lookup 引數的範例

下列清單包含一些水果及個別的顏色。請注意第一欄未排序:
A1:水果 B1:色彩
答 2:奇異果 B2:Green
答 3:香蕉 B3:Yellow
答 4:葡萄 B4:紫
答 5:蘋果 B5:紅色

下列公式尋找相對應於蘋果的顏色 (紅色)。您可以在工作表上的任何儲存格中輸入公式:
=VLOOKUP(“Apple",A2:B5,2,FALSE)

請注意,如果您將 range_lookup 引數變更為 TRUE,Excel 會傳回 #N/A 錯誤,因為第一欄未排序。

HLOOKUP 函數

=HLOOKUP(lookup_value,table_array,row_index_num,range_lookup)

其中:
Argument 引數的定義
———————————————————————

Lookup_value 在陣列第一欄中所要尋找的值。

Table_array 查閱資料中的資訊表格。

col_index 相符數值應該傳回的 table_array
應傳回相符的值。

Range_lookup 無論您想要尋找完全相符或近似的相符值,
其為指定的邏輯值。
如果是 TRUE 或是省略,就會傳回近似的相符值;
也就是說,如果沒有找到完全相符的值,
就會傳回下一個小於 Lookup_value 的最大值
。如果是 FALSE,VLOOKUP 則會尋找完全相符值。
找不到完全相同的值,就會傳回 #N/A 錯誤值

注意 如果 Range_lookup 是 TRUE 或是省略 (近似的相符值),在 Table_array 第一欄中所要尋找的值必須以遞增順序排序。如果 Range_lookup 是 FALSE (完全相符值),Table_array 就不需要排序。
使用 FALSE 做為 Range_lookup 引數的範例

下列清單包含一些水果及個別的顏色。請注意第一欄未排序:
A1:水果 B1:色彩
答 2:奇異果 B2:Green
答 3:香蕉 B3:Yellow
答 4:葡萄 B4:紫
答 5:蘋果 B5:紅色

下列公式會尋找「顏色」欄,並且傳回標題「黃色」的第三個項目。您可以在工作表上的任何儲存格中輸入公式:
=HLOOKUP(“Color",A1:B5,3,FALSE)

請注意,如果您將 range_lookup 引數變更為 TRUE,Excel 會傳回 #N/A 錯誤,因為第一欄未排序。

一次刪除 excel 裏所有的文字方塊包含文字藝術師

轉貼:http://tw.myblog.yahoo.com/jw!3QH6dXaFGRvLjngfyDp82rOE/article?mid=176

要刪除 EXCEL 工作表裹的文字方塊(數量很多時,又不知在那),
可依下列方式一次通通刪除:

1. 打開 EXCEL 檔,選好要刪除文字方塊的那個工作表。

2. 按 F5,出現一個視窗,按左下的 特殊 按鈕。

3. 又出一現個 特殊目標 的視窗,然後選擇 物件 ,就會將所有的 文字方塊 都選起來了。

4. 按 刪除 就全部將文字方塊 都刪除掉了。