Category Archives: Excel

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 … Continue reading →

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<HSSFCellStyle> formats = new ArrayList<>(); /** format */ for (String format:sheetContent.getFormats()) { HSSFCellStyle cs = … Continue reading →

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 The common method is using the upper mathematical formulas. But if we adjust the columns, we may get the … Continue reading →

Posted in Excel | 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