ExcelUtil.java
5.63 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
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
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
package com.uccc.number.util;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.Cell;
import java.text.DecimalFormat;
public class ExcelUtil {
/**
* 设置单元格文字样式
*
* @param wb
* @param font
* @return
*/
public static HSSFCellStyle operateCellStyle(HSSFWorkbook wb, HSSFFont font) {
HSSFCellStyle cellStyle = wb.createCellStyle();
cellStyle.setFont(font);
return cellStyle;
}
/**
* 设置字体的样式,字体的大小
*
* @param wb
* @param type
* @param point
* @return
*/
public static HSSFFont operateFont(HSSFWorkbook wb, String type, short point, Boolean bool) {
HSSFFont font = wb.createFont();
if (bool == true) {
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); // 加粗
}
font.setFontName(type); // 设置字体
font.setFontHeightInPoints(point);// 设置字体大小
return font;
}
/**
* 对某一行依次添加source的内容
*
* @param isChange 是否换行
* @param sheet
* @param rowCount
* @param source
*/
public static void operateRow(HSSFSheet sheet, int rowCount, HSSFCellStyle style, Boolean isChange,
Object... source) {
HSSFRow row = sheet.createRow(rowCount);
for (int i = 0; i < source.length; i++) {
HSSFCell cell = row.createCell(i);
if (isChange == true) {
style.setWrapText(true);
}
cell.setCellStyle(style);
// 默认设置为String
cell.setCellType(HSSFCell.CELL_TYPE_STRING);
cell.setCellValue(source[i] == null ? "" : source[i] + "");
}
}
/**
* 对某一行依次添加source的内容
*
* @param sheet
* @param rowCount
* @param source
*/
public static void operateRow(HSSFSheet sheet, int rowCount, HSSFCellStyle style,
Object... source) {
HSSFRow row = sheet.createRow(rowCount);
for (int i = 0; i < source.length; i++) {
HSSFCell cell = row.createCell(i);
cell.setCellStyle(style);
// 默认设置为String
cell.setCellValue(source[i] == null ? "" : source[i] + "");
}
}
/**
* 从第n列开始,依次对表格的宽度赋值
*
* @param sheet
* @param startColumn
*/
public static void operateColumn(HSSFSheet sheet, int startColumn, int... width) {
for (int i = 0; i < width.length; i++) {
sheet.setColumnWidth(i + startColumn, width[i] * 256); // 设置列宽，20个字符宽
}
}
/**
* 创建钉铛业务标准的excel
*
* @return
*/
public static Excel createStandardExcel() {
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFFont headerFont = workbook.createFont();
headerFont.setFontName("宋体");
headerFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
headerFont.setFontHeightInPoints((short) 12);
HSSFCellStyle headerCellStyle = workbook.createCellStyle();
headerCellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
headerCellStyle.setFont(headerFont);
HSSFFont textfont = workbook.createFont();
textfont.setFontHeightInPoints((short) 11);
HSSFCellStyle textCellStyle = workbook.createCellStyle();
textCellStyle.setFont(textfont);
textCellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
Excel excel = new ExcelUtil().new Excel();
excel.headerHSSFCellStyle = headerCellStyle;
excel.textHSSFCellStyle = textCellStyle;
excel.hssfWorkbook = workbook;
return excel;
}
/**
* Description:获取单元格数据
* int CELL_TYPE_NUMERIC = 0;
* int CELL_TYPE_STRING = 1;
* int CELL_TYPE_FORMULA = 2;
* int CELL_TYPE_BLANK = 3;
* int CELL_TYPE_BOOLEAN = 4;
* int CELL_TYPE_ERROR = 5;
*/
public static String getFromCell(Cell cell) {
if (cell == null) {
return "";
}
if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
DecimalFormat decimalFormat = new DecimalFormat("0");
return decimalFormat.format(cell.getNumericCellValue());
}
if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
return String.valueOf(cell.getStringCellValue());
}
if (cell.getCellType() == Cell.CELL_TYPE_FORMULA) {
return String.valueOf(cell.getCellFormula());
}
if (cell.getCellType() == Cell.CELL_TYPE_BLANK) {
return "";
}
if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) {
return String.valueOf(cell.getBooleanCellValue());
}
return "";
}
public class Excel {
HSSFWorkbook hssfWorkbook;
HSSFCellStyle headerHSSFCellStyle;
HSSFCellStyle textHSSFCellStyle;
public HSSFWorkbook getHssfWorkbook() {
return hssfWorkbook;
}
public void setHssfWorkbook(HSSFWorkbook hssfWorkbook) {
this.hssfWorkbook = hssfWorkbook;
}
public HSSFCellStyle getHeaderHSSFCellStyle() {
return headerHSSFCellStyle;
}
public void setHeaderHSSFCellStyle(HSSFCellStyle headerHSSFCellStyle) {
this.headerHSSFCellStyle = headerHSSFCellStyle;
}
public HSSFCellStyle getTextHSSFCellStyle() {
return textHSSFCellStyle;
}
public void setTextHSSFCellStyle(HSSFCellStyle textHSSFCellStyle) {
this.textHSSFCellStyle = textHSSFCellStyle;
}
}
}