一、介紹
利用java實現文件的導入導出數據庫，目前在大部分系統中是比較常見的功能了，今天寫個小demo來理解其原理，沒接觸過的同學也可以看看參考下。
目前我所接觸過的導入導出技術主要有POI和iReport，poi主要作為一些數據批量導入數據庫，iReport做報表導出。另外還有jxl類似poi的方式，不過貌似很久沒跟新了，2007之后的office好像也不支持，這里就不說了。
二、POI使用詳解
2.1 什么是Apache POI？
Apache POI是Apache軟件基金會的開放源碼函式庫，POI提供API給Java程序對Microsoft Office格式檔案讀和寫的功能。
2.2 POI的jar包導入
本次講解使用maven工程，jar包版本使用poi-3.14和poi-ooxml-3.14。目前最新的版本是3.16。因為3.15以后相關api有更新，部分操作可能不一樣，大家注意下。
|
1
2
3
4
5
6
7
8
9
10
11
|
<!-- poi的包 3.15版本后單元格類型獲取方式有調整 --><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> |
2.3 POI的API講解
2.3.1 結構
- HSSF － 提供讀寫Microsoft Excel格式檔案的功能。
- XSSF － 提供讀寫Microsoft Excel OOXML格式檔案的功能。
- HWPF － 提供讀寫Microsoft Word格式檔案的功能。
- HSLF － 提供讀寫Microsoft PowerPoint格式檔案的功能。
- HDGF － 提供讀寫Microsoft Visio格式檔案的功能。
2.3.2 對象
本文主要介紹HSSF和XSSF兩種組件，簡單的講HSSF用來操作Office 2007版本前excel.xls文件，XSSF用來操作Office 2007版本后的excel.xlsx文件，注意二者的后綴是不一樣的。
HSSF在org.apache.poi.hssf.usermodel包中。它實現了Workbook 接口，用于Excel文件中的.xls格式
常用組件：
HSSFWorkbook excel的文檔對象
HSSFSheet excel的表單
HSSFRow excel的行
HSSFCell excel的格子單元
HSSFFont excel字體
HSSFDataFormat 日期格式
HSSFHeader sheet頭
HSSFFooter sheet尾（只有打印的時候才能看到效果）
樣式：
HSSFCellStyle cell樣式
輔助操作包括：
HSSFDateUtil 日期
HSSFPrintSetup 打印
HSSFErrorConstants 錯誤信息表
XSSF在org.apache.xssf.usemodel包，并實現Workbook接口，用于Excel文件中的.xlsx格式
常用組件：
XSSFWorkbook excel的文檔對象
XSSFSheet excel的表單
XSSFRow excel的行
XSSFCell excel的格子單元
XSSFFont excel字體
XSSFDataFormat 日期格式
和HSSF類似;
2.3.3 兩個組件共同的字段類型描述
其實兩個組件就是針對excel的兩種格式，大部分的操作都是相同的。
|
1
2
3
4
5
6
7
|
單元格類型 描述CELL_TYPE_BLANK 代表空白單元格CELL_TYPE_BOOLEAN 代表布爾單元（true或false）CELL_TYPE_ERROR 表示在單元的誤差值CELL_TYPE_FORMULA 表示一個單元格公式的結果CELL_TYPE_NUMERIC 表示對一個單元的數字數據CELL_TYPE_STRING 表示對一個單元串（文本） |
2.3.4 操作步驟
以HSSF為例，XSSF操作相同。
首先，理解一下一個Excel的文件的組織形式，一個Excel文件對應于一個workbook(HSSFWorkbook)，一個workbook可以有多個sheet（HSSFSheet）組成，一個sheet是由多個row（HSSFRow）組成，一個row是由多個cell（HSSFCell）組成。
1、用HSSFWorkbook打開或者創建“Excel文件對象”
2、用HSSFWorkbook對象返回或者創建Sheet對象
3、用Sheet對象返回行對象，用行對象得到Cell對象
4、對Cell對象讀寫。
三、代碼操作
3.1 效果圖
慣例，貼代碼前先看效果圖
Excel文件兩種格式各一個：
代碼結構：
導入后：（我導入了兩遍，沒做校驗）
導出效果：
3.2 代碼詳解
這里我以Spring+SpringMVC+Mybatis為基礎
Controller:
|
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
|
package com.allan.controller;import java.util.List;import javax.servlet.http.HttpServletResponse;import org.apache.poi.ss.formula.functions.Mode;import org.springframework.beans.factory.annotation.Autowired;import org.springframework.stereotype.Controller;import org.springframework.ui.Model;import org.springframework.web.bind.annotation.RequestMapping;import org.springframework.web.bind.annotation.RequestMethod;import org.springframework.web.bind.annotation.RequestParam;import org.springframework.web.bind.annotation.ResponseBody;import org.springframework.web.multipart.MultipartFile;import org.springframework.web.servlet.ModelAndView;import com.allan.pojo.Student;import com.allan.service.StudentService;/** * * @author 小賣鋪的老爺爺 * */@Controllerpublic class StudentController { @Autowired private StudentService studentService; /** * 批量導入表單數據 * * @param request * @param myfile * @return */ @RequestMapping(value="/importExcel",method=RequestMethod.POST) public String importExcel(@RequestParam("myfile") MultipartFile myFile) { ModelAndView modelAndView = new ModelAndView(); try { Integer num = studentService.importExcel(myFile); } catch (Exception e) { modelAndView.addObject("msg", e.getMessage()); return "index"; } modelAndView.addObject("msg", "數據導入成功"); return "index"; } @RequestMapping(value="/exportExcel",method=RequestMethod.GET) public void exportExcel(HttpServletResponse response) { try { studentService.exportExcel(response); } catch (Exception e) { e.printStackTrace(); } } } |
Service
|
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
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
|
package com.allan.service.impl;import java.io.OutputStream;import java.text.DecimalFormat;import java.text.SimpleDateFormat;import java.util.Date;import java.util.List;import javax.servlet.http.HttpServletResponse;import org.apache.commons.lang.StringUtils;import org.apache.poi.hssf.usermodel.HSSFCell;import org.apache.poi.hssf.usermodel.HSSFCellStyle;import org.apache.poi.hssf.usermodel.HSSFDateUtil;import org.apache.poi.hssf.usermodel.HSSFRow;import org.apache.poi.hssf.usermodel.HSSFSheet;import org.apache.poi.hssf.usermodel.HSSFWorkbook;import org.apache.poi.ss.usermodel.Cell;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.xssf.usermodel.XSSFWorkbook;import org.springframework.beans.factory.annotation.Autowired;import org.springframework.stereotype.Service;import org.springframework.web.multipart.MultipartFile;import com.allan.mapper.StudentMapper;import com.allan.pojo.Student;import com.allan.service.StudentService;/** * * @author 小賣鋪的老爺爺 * */@Servicepublic class StudentServiceImpl implements StudentService{ private final static String XLS = "xls"; private final static String XLSX = "xlsx"; @Autowired private StudentMapper studentMapper; /** * 導入Excel，兼容xls和xlsx */ @SuppressWarnings("resource") public Integer importExcel(MultipartFile myFile) throws Exception { // 1、用HSSFWorkbook打開或者創建“Excel文件對象” // // 2、用HSSFWorkbook對象返回或者創建Sheet對象 // // 3、用Sheet對象返回行對象，用行對象得到Cell對象 // // 4、對Cell對象讀寫。 //獲得文件名 Workbook workbook = null ; String fileName = myFile.getOriginalFilename(); if(fileName.endsWith(XLS)){ //2003 workbook = new HSSFWorkbook(myFile.getInputStream()); }else if(fileName.endsWith(XLSX)){ //2007 workbook = new XSSFWorkbook(myFile.getInputStream()); }else{ throw new Exception("文件不是Excel文件"); } Sheet sheet = workbook.getSheet("Sheet1"); int rows = sheet.getLastRowNum();// 指的行數，一共有多少行+ if(rows==0){ throw new Exception("請填寫數據"); } for (int i = 1; i <= rows+1; i++) { // 讀取左上端單元格 Row row = sheet.getRow(i); // 行不為空 if (row != null) { // **讀取cell** Student student = new Student(); //姓名 String name = getCellValue(row.getCell(0)); student.setName(name); //班級 String classes = getCellValue(row.getCell(1)); student.setClasses(classes); //分數 String scoreString = getCellValue(row.getCell(2)); if (!StringUtils.isEmpty(scoreString)) { Integer score = Integer.parseInt(scoreString); student.setScore(score); } //考試時間 SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");//小寫的mm表示的是分鐘 String dateString = getCellValue(row.getCell(3)); if (!StringUtils.isEmpty(dateString)) { Date date=sdf.parse(dateString); student.setTime(date); } studentMapper.insert(student); } } return rows-1; } /** * 獲得Cell內容 * * @param cell * @return */ public String getCellValue(Cell cell) { String value = ""; if (cell != null) { // 以下是判斷數據的類型 switch (cell.getCellType()) { case HSSFCell.CELL_TYPE_NUMERIC: // 數字 value = cell.getNumericCellValue() + ""; if (HSSFDateUtil.isCellDateFormatted(cell)) { Date date = cell.getDateCellValue(); if (date != null) { value = new SimpleDateFormat("yyyy-MM-dd").format(date); } else { value = ""; } } else { value = new DecimalFormat("0").format(cell.getNumericCellValue()); } break; case HSSFCell.CELL_TYPE_STRING: // 字符串 value = cell.getStringCellValue(); break; case HSSFCell.CELL_TYPE_BOOLEAN: // Boolean value = cell.getBooleanCellValue() + ""; break; case HSSFCell.CELL_TYPE_FORMULA: // 公式 value = cell.getCellFormula() + ""; break; case HSSFCell.CELL_TYPE_BLANK: // 空值 value = ""; break; case HSSFCell.CELL_TYPE_ERROR: // 故障 value = "非法字符"; break; default: value = "未知類型"; break; } } return value.trim(); } /** * 導出excel文件 */ public void exportExcel(HttpServletResponse response) throws Exception { // 第一步，創建一個webbook，對應一個Excel文件 HSSFWorkbook wb = new HSSFWorkbook(); // 第二步，在webbook中添加一個sheet,對應Excel文件中的sheet HSSFSheet sheet = wb.createSheet("Sheet1"); // 第三步，在sheet中添加表頭第0行,注意老版本poi對Excel的行數列數有限制short HSSFRow row = sheet.createRow(0); // 第四步，創建單元格，并設置值表頭 設置表頭居中 HSSFCellStyle style = wb.createCellStyle(); style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 創建一個居中格式 HSSFCell cell = row.createCell(0); cell.setCellValue("姓名"); cell.setCellStyle(style); cell = row.createCell(1); cell.setCellValue("班級"); cell.setCellStyle(style); cell = row.createCell(2); cell.setCellValue("分數"); cell.setCellStyle(style); cell = row.createCell(3); cell.setCellValue("時間"); cell.setCellStyle(style); // 第五步，寫入實體數據 實際應用中這些數據從數據庫得到， List<Student> list = studentMapper.selectAll(); for (int i = 0; i < list.size(); i++){ row = sheet.createRow(i + 1); Student stu = list.get(i); // 第四步，創建單元格，并設置值 row.createCell(0).setCellValue(stu.getName()); row.createCell(1).setCellValue(stu.getClasses()); row.createCell(2).setCellValue(stu.getScore()); cell = row.createCell(3); cell.setCellValue(new SimpleDateFormat("yyyy-MM-dd").format(stu.getTime())); } //第六步,輸出Excel文件 OutputStream output=response.getOutputStream(); response.reset(); long filename = System.currentTimeMillis(); SimpleDateFormat df = new SimpleDateFormat("yyyyMMddHHmmss");//設置日期格式 String fileName = df.format(new Date());// new Date()為獲取當前系統時間 response.setHeader("Content-disposition", "attachment; filename="+fileName+".xls"); response.setContentType("application/msexcel"); wb.write(output); output.close(); } } |
3.3 導出文件api補充
大家可以看到上面service的代碼只是最基本的導出。
在實際應用中導出的Excel文件往往需要閱讀和打印的，這就需要對輸出的Excel文檔進行排版和樣式的設置，主要操作有合并單元格、設置單元格樣式、設置字體樣式等。
3.3.1 單元格合并
使用HSSFSheet的addMergedRegion()方法
|
1
|
public int addMergedRegion(CellRangeAddress region) |
參數CellRangeAddress 表示合并的區域，構造方法如下：依次表示起始行，截至行，起始列， 截至列
|
1
|
CellRangeAddress(int firstRow, int lastRow, int firstCol, int lastCol) |
3.3.2 設置單元格的行高和列寬
|
1
2
3
4
|
HSSFSheet sheet=wb.createSheet();sheet.setDefaultRowHeightInPoints(10);//設置缺省列高sheet.setDefaultColumnWidth(20);//設置缺省列寬//設置指定列的列寬，256 * 50這種寫法是因為width參數單位是單個字符的256分之一sheet.setColumnWidth(cell.getColumnIndex(), 256 * 50); |
3.3.3 設置單元格樣式
1、創建HSSFCellStyle
|
1
|
HSSFCellStyle cellStyle=wkb.createCellStyle() |
2、設置樣式
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
|
// 設置單元格的橫向和縱向對齊方式，具體參數就不列了，參考HSSFCellStyle cellStyle.setAlignment(HSSFCellStyle.ALIGN_JUSTIFY); cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); /* 設置單元格的填充方式，以及前景顏色和背景顏色 三點注意： 1.如果需要前景顏色或背景顏色，一定要指定填充方式，兩者順序無所謂； 2.如果同時存在前景顏色和背景顏色，前景顏色的設置要寫在前面； 3.前景顏色不是字體顏色。 */ //設置填充方式(填充圖案) cellStyle.setFillPattern(HSSFCellStyle.DIAMONDS); //設置前景色 cellStyle.setFillForegroundColor(HSSFColor.RED.index); //設置背景顏色 cellStyle.setFillBackgroundColor(HSSFColor.LIGHT_YELLOW.index); // 設置單元格底部的邊框及其樣式和顏色 // 這里僅設置了底邊邊框，左邊框、右邊框和頂邊框同理可設 cellStyle.setBorderBottom(HSSFCellStyle.BORDER_SLANTED_DASH_DOT); cellStyle.setBottomBorderColor(HSSFColor.DARK_RED.index); //設置日期型數據的顯示樣式 cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy h:mm")); |
3、將樣式應用于單元格
|
1
2
3
|
cell.setCellStyle(cellStyle);//將樣式應用到行，但有些樣式只對單元格起作用row.setRowStyle(cellStyle); |
3.3.4設置字體樣式
1、創建HSSFFont對象（調用HSSFWorkbook 的createFont方法）
|
1
2
3
|
HSSFWorkbook wb=new HSSFWorkbook();HSSFFont fontStyle=wb.createFont();HSSFWorkbook wb=new HSSFWorkbook (); |
2、設置字體各種樣式
|
1
2
3
4
5
6
7
8
9
10
11
12
|
//設置字體樣式 fontStyle.setFontName("宋體"); //設置字體高度 fontStyle.setFontHeightInPoints((short)20); //設置字體顏色 font.setColor(HSSFColor.BLUE.index); //設置粗體 fontStyle.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); //設置斜體 font.setItalic(true); //設置下劃線 font.setUnderline(HSSFFont.U_SINGLE); |
3、將字體設置到單元格樣式
|
1
2
3
4
5
|
//字體也是單元格格式的一部分，所以從屬于HSSFCellStyle// 將字體對象賦值給單元格樣式對象cellStyle.setFont(font);// 將單元格樣式應用于單元格cell.setCellStyle(cellStyle); |
大家可以看出用poi導出文件還是比較麻煩的，等下次在為大家介紹下irport的方法。
導出的api基本上就是這些，最后也希望上文對大家能有所幫助。
總結
以上就是這篇文章的全部內容了，希望本文的內容對大家的學習或者工作能帶來一定的幫助，如果有疑問大家可以留言交流，謝謝大家對服務器之家的支持。
原文鏈接：http://www.cnblogs.com/allanzhang/p/6938889.html
