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 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

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: Excel

protect data validation to fail while copy and paste data

[Java] Write xls files

[Excel] Find the latest value

Excel 跳格計算

Categories