Java poi 读写excel工具类

依赖

1
2
3
4
5
6
7
8
9
10
<dependency>
	<groupId>org.apache.poi</groupId>
	<artifactId>poi</artifactId>
	<version>3.14</version>
</dependency>
<dependency>
	<groupId>org.apache.poi</groupId>
	<artifactId>poi-ooxml</artifactId>
	<version>3.14</version>
</dependency>

WorkbookUtils.java

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
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.DataFormatter;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.FormulaEvaluator;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.WorkbookUtil;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.tomcat.util.http.fileupload.IOUtils;

public class WorkbookUtils extends WorkbookUtil {
	// 读取excel文件
	public static Workbook readExcel(String filePath) {
		Workbook wb = null;
		if (filePath == null) {
			return null;
		}
		String extString = filePath.substring(filePath.lastIndexOf("."));
		InputStream is = null;
		try {
			is = new FileInputStream(filePath);
			if (".xls".equals(extString)) {
				return wb = new HSSFWorkbook(is);
			} else if (".xlsx".equals(extString)) {
				return wb = new XSSFWorkbook(is);
			} else {
				return wb = null;
			}

		} catch (FileNotFoundException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		} finally {
			IOUtils.closeQuietly(is);
		}
		return wb;
	}

	//读取单元格
	public static Object getCellFormatValue(Cell cell, FormulaEvaluator formulaEvaluator) {
		if (cell == null) {
			return null;
		}
		Object cellValue = null;
		// 判断cell类型
		int cellType = cell.getCellType();
		if (cellType == Cell.CELL_TYPE_FORMULA) {
			cellType = formulaEvaluator.evaluateFormulaCell(cell);
		}
		switch (cellType) {
		case Cell.CELL_TYPE_STRING:
			cellValue = cell.getRichStringCellValue().getString();
			break;
		case Cell.CELL_TYPE_NUMERIC:
			if (DateUtil.isCellDateFormatted(cell)) {// 判断cell是否为日期格式
				cellValue = cell.getDateCellValue();
				break;
			}
			DataFormatter dataFormatter = new DataFormatter();
			cellValue = dataFormatter.formatCellValue(cell, formulaEvaluator);
			break;
		case Cell.CELL_TYPE_BOOLEAN:
			cellValue = cell.getBooleanCellValue();
			break;
		default:
			cellValue = "";
		}
		return cellValue;
	}

	// 设置报表头样式
	public static CellStyle createHeadSytle(Workbook workbook) {
		CellStyle style1 = workbook.createCellStyle();// cell样式
		// 设置单元格背景色，设置单元格背景色以下两句必须同时设置
		style1.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);// 设置填充样式
		style1.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);// 设置填充色
		// 设置单元格上、下、左、右的边框线
		style1.setBorderBottom(HSSFCellStyle.BORDER_THIN);
		style1.setBorderLeft(HSSFCellStyle.BORDER_THIN);
		style1.setBorderRight(HSSFCellStyle.BORDER_THIN);
		style1.setBorderTop(HSSFCellStyle.BORDER_THIN);
		Font font1 = workbook.createFont();// 创建一个字体对象
		font1.setBoldweight((short) 10);// 设置字体的宽度
		font1.setFontHeightInPoints((short) 10);// 设置字体的高度
		font1.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);// 粗体显示
		style1.setFont(font1);// 设置style1的字体
		// style1.setWrapText(true);// 设置自动换行
		style1.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 设置单元格字体显示居中（左右方向）
		style1.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 设置单元格字体显示居中(上下方向)
		return style1;
	}

	// 设置报表体样式
	public static CellStyle createCellStyle(Workbook wb) {
		// 设置style1的样式，此样式运用在第二行
		CellStyle style1 = wb.createCellStyle();// cell样式
		// 设置单元格上、下、左、右的边框线
		style1.setBorderBottom(HSSFCellStyle.BORDER_THIN);
		style1.setBorderLeft(HSSFCellStyle.BORDER_THIN);
		style1.setBorderRight(HSSFCellStyle.BORDER_THIN);
		style1.setBorderTop(HSSFCellStyle.BORDER_THIN);
		// style1.setWrapText(true);// 设置自动换行
		style1.setAlignment(HSSFCellStyle.ALIGN_LEFT);// 设置单元格字体显示居中（左右方向）
		style1.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 设置单元格字体显示居中(上下方向)
		return style1;
	}
}