Category Archives: Office

open external program from java for mac os x

Posted on 2017 年 03 月 24 日 by cowman.chiang

String[] cmds = {"open",  tempFile.getAbsolutePath(), "-a", "/Applications/Microsoft Office 2011/Microsoft Excel.app"};
Runtime.getRuntime().exec(cmds);

1 2	String[] cmds = {"open", tempFile.getAbsolutePath(), "-a", "/Applications/Microsoft Office 2011/Microsoft Excel.app"}; Runtime.getRuntime().exec(cmds);

Posted in Java, Mac, Office | Leave a comment

protect data validation to fail while copy and paste data

Posted on 2017 年 01 月 25 日 by cowman.chiang

Private Sub Worksheet_Change(ByVal Target As Range)
    'Does the validation range still have validation?
    Set range1 = Range("B2:B6500")
    If HasValidation(range1) Then
        Exit Sub
    Else
        Application.Undo
        MsgBox "您的操作將會被取消, " & vbCrLf & "請使用下拉選單進行選擇", vbCritical
    End If
End Sub

Private Function HasValidation(r) As Boolean
'   Returns True if every cell in Range r uses Data Validation
    On Error Resume Next
    x = r.Validation.Type
    If Err.Number = 0 Then HasValidation = True Else HasValidation = False
End Function

Private Sub Worksheet_Change(ByVal Target As Range)

'Does the validation range still have validation?

Set range1 = Range("B2:B6500")

If HasValidation(range1) Then

Exit Sub

Else

Application.Undo

MsgBox "您的操作將會被取消, " & vbCrLf & "請使用下拉選單進行選擇", vbCritical

End If

End Sub

Private Function HasValidation(r) As Boolean

' Returns True if every cell in Range r uses Data Validation

On Error Resume Next

x = r.Validation.Type

If Err.Number = 0 Then HasValidation = True Else HasValidation = False

End Function

source: Ensuring That Data Validation Is Not Deleted

Posted in Excel | Leave a comment

[Java] Write xls files

Posted on 2015 年 11 月 02 日 by cowman.chiang

public String createFile(Object object) {
    try {
        Excel excel = (Excel) object;
        HSSFWorkbook hssfWorkbook = new HSSFWorkbook();
        for (SheetContent sheetContent:excel.getSheets()) {
            HSSFSheet sheet = hssfWorkbook.createSheet(sheetContent.getName());

            List&lt;HSSFCellStyle&gt; formats = new ArrayList&lt;&gt;();
            /** format */
            for (String format:sheetContent.getFormats()) {
                HSSFCellStyle cs = hssfWorkbook.createCellStyle();
                HSSFDataFormat df = hssfWorkbook.createDataFormat();
                cs.setDataFormat(df.getFormat(format));
                formats.add(cs);
            }

            /** header */
            HSSFRow headerRow = sheet.createRow(0);
            for (int i = 0; i &lt; sheetContent.getHeaders().size(); i++) {
                HSSFCell cell = headerRow.createCell(i);
                cell.setCellType(HSSFCell.CELL_TYPE_STRING);
                cell.setCellValue(sheetContent.getHeaders().get(i));
            }

            /** content */
            for (int i = 0; i &lt; sheetContent.getRows().size(); i ++) {
                HSSFRow dataRow = sheet.createRow(i + 1);
                for (int j = 0; j &lt; sheetContent.getRows().get(i).getCells().size(); j++) {
                    HSSFCell cell = dataRow.createCell(j);
                    if (StringUtils.isNotEmpty(sheetContent.getFormats().get(j))) {
                        cell.setCellValue(Double.parseDouble(sheetContent.getRows().get(i).getCells().get(j)));
                        cell.setCellStyle(formats.get(j));
                    } else {
                        cell.setCellType(HSSFCell.CELL_TYPE_STRING);
                        cell.setCellValue(sheetContent.getRows().get(i).getCells().get(j));
                    }
                }
            }

            /** auto size */
            for (int i = 0; i &lt; sheet.getRow(0).getPhysicalNumberOfCells(); i++) {
                sheet.autoSizeColumn(i);
            }
        }

        FileOutputStream out = new FileOutputStream(new File("Excel.xls"));
        hssfWorkbook.write(out);
        out.close();

        return "ok";
    } catch (Exception e) {
        LOG.error(Constants.EXCEPTION_PREFIX, e);
        return null;
    }
}

public class Excel {
    List&lt;SheetContent&gt; sheets;

    public List&lt;SheetContent&gt; getSheets() {
        return sheets;
    }

    public Excel setSheets(List&lt;SheetContent&gt; sheets) {
        this.sheets = sheets;
        return this;
    }

    public Excel addSheets(SheetContent sheet) {
        if (this.sheets == null) {
            this.sheets = new ArrayList&lt;&gt;();
        }
        this.sheets.add(sheet);
        return this;
    }
}

public class SheetContent {
    private String name;
    private List&lt;String&gt; formats;
    private List&lt;String&gt; headers;
    private List&lt;Row&gt; rows;

    public String getName() {
        return name;
    }

    public SheetContent setName(String name) {
        this.name = name;
        return this;
    }


    public List&lt;String&gt; getFormats() {
        return formats;
    }

    public SheetContent setFormats(List&lt;String&gt; formats) {
        this.formats = formats;
        return this;
    }

    public List&lt;String&gt; getHeaders() {
        return headers;
    }

    public SheetContent setHeaders(List&lt;String&gt; headers) {
        this.headers = headers;
        return this;
    }

    public List&lt;Row&gt; getRows() {
        return rows;
    }

    public SheetContent setRows(List&lt;Row&gt; rows) {
        this.rows = rows;
        return this;
    }
}

public class Row {
    private List&lt;String&gt; cells;

    public List&lt;String&gt; getCells() {
        return cells;
    }

    public Row setCells(List&lt;String&gt; cells) {
        this.cells = cells;
        return this;
    }
}

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

public String createFile(Object object) {

try {

Excel excel = (Excel) object;

HSSFWorkbook hssfWorkbook = new HSSFWorkbook();

for (SheetContent sheetContent:excel.getSheets()) {

HSSFSheet sheet = hssfWorkbook.createSheet(sheetContent.getName());

List<HSSFCellStyle> formats = new ArrayList<>();

/** format */

for (String format:sheetContent.getFormats()) {

HSSFCellStyle cs = hssfWorkbook.createCellStyle();

HSSFDataFormat df = hssfWorkbook.createDataFormat();

cs.setDataFormat(df.getFormat(format));

formats.add(cs);

}

/** header */

HSSFRow headerRow = sheet.createRow(0);

for (int i = 0; i < sheetContent.getHeaders().size(); i++) {

HSSFCell cell = headerRow.createCell(i);

cell.setCellType(HSSFCell.CELL_TYPE_STRING);

cell.setCellValue(sheetContent.getHeaders().get(i));

}

/** content */

for (int i = 0; i < sheetContent.getRows().size(); i ++) {

HSSFRow dataRow = sheet.createRow(i + 1);

for (int j = 0; j < sheetContent.getRows().get(i).getCells().size(); j++) {

HSSFCell cell = dataRow.createCell(j);

if (StringUtils.isNotEmpty(sheetContent.getFormats().get(j))) {

cell.setCellValue(Double.parseDouble(sheetContent.getRows().get(i).getCells().get(j)));

cell.setCellStyle(formats.get(j));

} else {

cell.setCellType(HSSFCell.CELL_TYPE_STRING);

cell.setCellValue(sheetContent.getRows().get(i).getCells().get(j));

}

/** auto size */

for (int i = 0; i < sheet.getRow(0).getPhysicalNumberOfCells(); i++) {

sheet.autoSizeColumn(i);

}

FileOutputStream out = new FileOutputStream(new File("Excel.xls"));

hssfWorkbook.write(out);

out.close();

return "ok";

} catch (Exception e) {

LOG.error(Constants.EXCEPTION_PREFIX, e);

return null;

}

public class Excel {

List<SheetContent> sheets;

public List<SheetContent> getSheets() {

return sheets;

}

public Excel setSheets(List<SheetContent> sheets) {

this.sheets = sheets;

return this;

}

public Excel addSheets(SheetContent sheet) {

if (this.sheets == null) {

this.sheets = new ArrayList<>();

}

this.sheets.add(sheet);

return this;

}

public class SheetContent {

private String name;

private List<String> formats;

private List<String> headers;

private List<Row> rows;

public String getName() {

return name;

}

public SheetContent setName(String name) {

this.name = name;

return this;

}

public List<String> getFormats() {

return formats;

}

public SheetContent setFormats(List<String> formats) {

this.formats = formats;

return this;

}

public List<String> getHeaders() {

return headers;

}

public SheetContent setHeaders(List<String> headers) {

this.headers = headers;

return this;

}

public List<Row> getRows() {

return rows;

}

public SheetContent setRows(List<Row> rows) {

this.rows = rows;

return this;

}

public class Row {

private List<String> cells;

public List<String> getCells() {

return cells;

}

public Row setCells(List<String> cells) {

this.cells = cells;

return this;

}

Posted in Excel, Java | Leave a comment

[Excel] Find the latest value

Posted on 2015 年 03 月 04 日 by cowman.chiang

If we want use excel to get the value F.

F = A * B * C * D * E

1 2	F = A * B * C * D * E

The common method is using the upper mathematical formulas. But if we adjust the columns, we may get the wrong value because there are some exceptions in the reference … Continue reading →

Posted in Excel | Leave a comment

物件名稱包含變數 + 應用紀錄

Posted on 2013 年 05 月 15 日 by cowman.chiang

Dim s1, s2 As Integer
Sub OnSlideShowPageChange()
    s1 = 1
    s2 = 1
    With ActivePresentation.Slides(SlideShowWindows(1).View.Slide.SlideIndex)
        If .Shapes.Title.TextFrame.TextRange.Text = "Windows 安裝Splunk" Then
            allhide
            selectphoto (s1)
        ElseIf .Shapes.Title.TextFrame.TextRange.Text = "Windows 設定 Forward" Then
            allhide_1
            selectphoto_1 (s2)
        End If
    End With
End Sub
Function allhide()
    With ActivePresentation.Slides(SlideShowWindows(1).View.Slide.SlideIndex)
        If .Shapes.Title.TextFrame.TextRange.Text = "Windows 安裝Splunk" Then
            For i = 1 To 24 Step 1
                .Shapes("windows_splunk_" & Format(i, "00")).Visible = msoFalse
                'format設定2位數，不足2位數補0
            Next i
        End If
    End With
End Function
Function selectphoto(num As Integer)
    With ActivePresentation.Slides(SlideShowWindows(1).View.Slide.SlideIndex)
        If .Shapes.Title.TextFrame.TextRange.Text = "Windows 安裝Splunk" Then
            .Shapes("windows_splunk_" & Format(num, "00")).Visible = msoTrue
            .Shapes("windows_splunk_" & Format(num, "00")).ZOrder msoBringToFront
        End If
    End With
End Function
Sub photo_back()
    With ActivePresentation.Slides(SlideShowWindows(1).View.Slide.SlideIndex)
        If .Shapes.Title.TextFrame.TextRange.Text = "Windows 安裝Splunk" Then
            s1 = s1 - 1
            If s1 = 0 Then s1 = 24
            allhide
            selectphoto (s1)
        End If
    End With
End Sub
Sub photo_next()
    With ActivePresentation.Slides(SlideShowWindows(1).View.Slide.SlideIndex)
        If .Shapes.Title.TextFrame.TextRange.Text = "Windows 安裝Splunk" Then
            s1 = s1 + 1
            If s1 = 25 Then s1 = 1
            allhide
            selectphoto (s1)
        End If
    End With
End Sub
Function allhide_1()
    With ActivePresentation.Slides(SlideShowWindows(1).View.Slide.SlideIndex)
        If .Shapes.Title.TextFrame.TextRange.Text = "Windows 設定 Forward" Then
            For i = 1 To 6 Step 1
                .Shapes("windows_forward_" & Format(i, "00")).Visible = msoFalse
            Next i
        End If
    End With
End Function
Function selectphoto_1(num As Integer)
    With ActivePresentation.Slides(SlideShowWindows(1).View.Slide.SlideIndex)
        If .Shapes.Title.TextFrame.TextRange.Text = "Windows 設定 Forward" Then
            .Shapes("windows_forward_" & Format(num, "00")).Visible = msoTrue
            .Shapes("windows_forward_" & Format(num, "00")).ZOrder msoBringToFront
        End If
    End With
End Function
Sub photo_back_1()
    With ActivePresentation.Slides(SlideShowWindows(1).View.Slide.SlideIndex)
        If .Shapes.Title.TextFrame.TextRange.Text = "Windows 設定 Forward" Then
            s2 = s2 - 1
            If s2 = 0 Then s2 = 6
            allhide_1
            selectphoto_1 (s2)
        End If
    End With
End Sub
Sub photo_next_1()
    With ActivePresentation.Slides(SlideShowWindows(1).View.Slide.SlideIndex)
        If .Shapes.Title.TextFrame.TextRange.Text = "Windows 設定 Forward" Then
            s2 = s2 + 1
            If s2 = 6 Then s2 = 1
            allhide_1
            selectphoto_1 (s2)
        End If
    End With
End Sub

Dim s1, s2 As Integer

Sub OnSlideShowPageChange()

s1 = 1

s2 = 1

With ActivePresentation.Slides(SlideShowWindows(1).View.Slide.SlideIndex)

If .Shapes.Title.TextFrame.TextRange.Text = "Windows 安裝Splunk" Then

allhide

selectphoto (s1)

ElseIf .Shapes.Title.TextFrame.TextRange.Text = "Windows 設定 Forward" Then

allhide_1

selectphoto_1 (s2)

End If

End With

End Sub

Function allhide()

With ActivePresentation.Slides(SlideShowWindows(1).View.Slide.SlideIndex)

If .Shapes.Title.TextFrame.TextRange.Text = "Windows 安裝Splunk" Then

For i = 1 To 24 Step 1

.Shapes("windows_splunk_" & Format(i, "00")).Visible = msoFalse

'format設定2位數，不足2位數補0

Next i

End If

End With

End Function

Function selectphoto(num As Integer)

With ActivePresentation.Slides(SlideShowWindows(1).View.Slide.SlideIndex)

If .Shapes.Title.TextFrame.TextRange.Text = "Windows 安裝Splunk" Then

.Shapes("windows_splunk_" & Format(num, "00")).Visible = msoTrue

.Shapes("windows_splunk_" & Format(num, "00")).ZOrder msoBringToFront

End If

End With

End Function

Sub photo_back()

With ActivePresentation.Slides(SlideShowWindows(1).View.Slide.SlideIndex)

If .Shapes.Title.TextFrame.TextRange.Text = "Windows 安裝Splunk" Then

s1 = s1 - 1

If s1 = 0 Then s1 = 24

allhide

selectphoto (s1)

End If

End With

End Sub

Sub photo_next()

With ActivePresentation.Slides(SlideShowWindows(1).View.Slide.SlideIndex)

If .Shapes.Title.TextFrame.TextRange.Text = "Windows 安裝Splunk" Then

s1 = s1 + 1

If s1 = 25 Then s1 = 1

allhide

selectphoto (s1)

End If

End With

End Sub

Function allhide_1()

With ActivePresentation.Slides(SlideShowWindows(1).View.Slide.SlideIndex)

If .Shapes.Title.TextFrame.TextRange.Text = "Windows 設定 Forward" Then

For i = 1 To 6 Step 1

.Shapes("windows_forward_" & Format(i, "00")).Visible = msoFalse

Next i

End If

End With

End Function

Function selectphoto_1(num As Integer)

With ActivePresentation.Slides(SlideShowWindows(1).View.Slide.SlideIndex)

If .Shapes.Title.TextFrame.TextRange.Text = "Windows 設定 Forward" Then

.Shapes("windows_forward_" & Format(num, "00")).Visible = msoTrue

.Shapes("windows_forward_" & Format(num, "00")).ZOrder msoBringToFront

End If

End With

End Function

Sub photo_back_1()

With ActivePresentation.Slides(SlideShowWindows(1).View.Slide.SlideIndex)

If .Shapes.Title.TextFrame.TextRange.Text = "Windows 設定 Forward" Then

s2 = s2 - 1

If s2 = 0 Then s2 = 6

allhide_1

selectphoto_1 (s2)

End If

End With

End Sub

Sub photo_next_1()

With ActivePresentation.Slides(SlideShowWindows(1).View.Slide.SlideIndex)

If .Shapes.Title.TextFrame.TextRange.Text = "Windows 設定 Forward" Then

s2 = s2 + 1

If s2 = 6 Then s2 = 1

allhide_1

selectphoto_1 (s2)

End If

End With

End Sub

Posted in PowerPoint | Leave a comment

設定元件Visible屬性

Posted on 2013 年 02 月 08 日 by cowman.chiang

Sub Picture25_visible()
    With ActivePresentation.Slides(SlideShowWindows(1).View.Slide.SlideIndex)
        If .Shapes.Title.TextFrame.TextRange.Text = "Splunk 優點" Then
            .Shapes("圖片 25").Visible = -1
            .Shapes("圖片 25").ZOrder msoBringToFront
            End If
    End With
End Sub
Sub Picture25_hide()
    With ActivePresentation.Slides(SlideShowWindows(1).View.Slide.SlideIndex)
        If .Shapes.Title.TextFrame.TextRange.Text = "Splunk 優點" Then
            .Shapes("圖片 25").Visible = msoFalse
        End If
    End With
End Sub

Sub Picture25_visible()

With ActivePresentation.Slides(SlideShowWindows(1).View.Slide.SlideIndex)

If .Shapes.Title.TextFrame.TextRange.Text = "Splunk 優點" Then

.Shapes("圖片 25").Visible = -1

.Shapes("圖片 25").ZOrder msoBringToFront

End If

End With

End Sub

Sub Picture25_hide()

With ActivePresentation.Slides(SlideShowWindows(1).View.Slide.SlideIndex)

If .Shapes.Title.TextFrame.TextRange.Text = "Splunk 優點" Then

.Shapes("圖片 25").Visible = msoFalse

End If

End With

End Sub

記得當visible=msoTure (-1) 時要一併移到前面否則會看不出效果

Posted in Office, PowerPoint | Leave a comment

當投影片切換時 (VBA)

Posted on 2013 年 02 月 08 日 by cowman.chiang

Sub OnSlideShowPageChange()
    MsgBox ActivePresentation.Slides(ActiveWindow.Selection.SlideRange.SlideNumber).Shapes.Title.TextFrame.TextRange.Text
End Sub

Sub OnSlideShowPageChange()

MsgBox ActivePresentation.Slides(ActiveWindow.Selection.SlideRange.SlideNumber).Shapes.Title.TextFrame.TextRange.Text

End Sub

Posted in Office, PowerPoint | Leave a comment

Powerpoint VBA: 取得當前Slide編號

Posted on 2013 年 02 月 08 日 by cowman.chiang

主要有兩種 1.

ActiveWindow.Selection.SlideRange.SlideNumber

1	ActiveWindow.Selection.SlideRange.SlideNumber

此處主要是在一般狀態下 (非簡報播放) 適合用來Debug 2.

SlideShowWindows(1).View.Slide.SlideIndex

1	SlideShowWindows(1).View.Slide.SlideIndex

此處主要是當簡報播放情況下 ( 所以一般Debug模式會出錯 ) 以下為實際應用

Sub Picture76_4()
    With ActivePresentation.Slides(SlideShowWindows(1).View.Slide.SlideIndex)
        If .Shapes.Title.TextFrame.TextRange.Text = "Splunk for Unix and Linux" Then
            .Shapes("Picture 4").ZOrder msoBringToFront
        End If
    End With
End Sub
Sub Picture76_5()
    With ActivePresentation.Slides(ActiveWindow.Selection.SlideRange.SlideNumber)
        MsgBox .Shapes.Title.TextFrame.TextRange.Text
        If .Shapes.Title.TextFrame.TextRange.Text = "Splunk for Unix and Linux" Then
            .Shapes("Picture 5").ZOrder msoBringToFront
        End If
    End With
End Sub

Sub Picture76_4()

With ActivePresentation.Slides(SlideShowWindows(1).View.Slide.SlideIndex)

If .Shapes.Title.TextFrame.TextRange.Text = "Splunk for Unix and Linux" Then

.Shapes("Picture 4").ZOrder msoBringToFront

End If

End With

End Sub

Sub Picture76_5()

With ActivePresentation.Slides(ActiveWindow.Selection.SlideRange.SlideNumber)

MsgBox .Shapes.Title.TextFrame.TextRange.Text

If .Shapes.Title.TextFrame.TextRange.Text = "Splunk for Unix and Linux" Then

.Shapes("Picture 5").ZOrder msoBringToFront

End If

End With

End Sub

Posted in Office, PowerPoint | Leave a comment

PowerPoint巨集，將物件移到最前面

Posted on 2013 年 01 月 31 日 by cowman.chiang

查詢物件名稱

Sub Who_AM_I()
    With ActiveWindow.Selection.ShapeRange(1)
        MsgBox .Name
    End With
End Sub

Sub Who_AM_I()

With ActiveWindow.Selection.ShapeRange(1)

MsgBox .Name

End With

End Sub

將物件移到最前面

Sub Bringtofront()
    ActivePresentation.Slides(第幾張投影片).Shapes("物件名稱").ZOrder msoBringToFront
End Sub

Sub Bringtofront()

ActivePresentation.Slides(第幾張投影片).Shapes("物件名稱").ZOrder msoBringToFront

End Sub

Posted in Office, PowerPoint | Leave a comment

Excel 跳格計算

Posted on 2012 年 01 月 11 日 by cowman.chiang

這是怪獸兩個多禮拜以前問的… 當時在首都上就沒辦法幫他找了剛剛才想到有這個問題…Orz 真的老了網路上找了一下相關的問題使用下列就可以解決了 =SUM(IF(MOD(ROW(A1:A65535),5)=2,A1:B65535)) 按下ALT+SHIFT+ENTER變成陣列運算其中主要是用MOD 5餘2的儲存格來計算後面的A1:B65535主要就是要計算的範圍

Posted in Excel | Leave a comment

Category Archives: Office

Categories