ExcelUtil.java
1 package pers.kangxu.datautils.utils;
2
3 import java.io.File;
4 import java.io.FileInputStream;
5 import java.io.FileOutputStream;
6 import java.io.IOException;
7 import java.io.InputStream;
8 import java.util.ArrayList;
9 import java.util.HashMap;
10 import java.util.Iterator;
11 import java.util.List;
12 import java.util.Map;
13
14 import org.apache.poi.hssf.usermodel.HSSFCellStyle;
15 import org.apache.poi.hssf.usermodel.HSSFFont;
16 import org.apache.poi.hssf.usermodel.HSSFWorkbook;
17 import org.apache.poi.ss.usermodel.Cell;
18 import org.apache.poi.ss.usermodel.CellStyle;
19 import org.apache.poi.ss.usermodel.CellValue;
20 import org.apache.poi.ss.usermodel.Font;
21 import org.apache.poi.ss.usermodel.FormulaEvaluator;
22 import org.apache.poi.ss.usermodel.Row;
23 import org.apache.poi.ss.usermodel.Sheet;
24 import org.apache.poi.ss.usermodel.Workbook;
25 import org.apache.poi.ss.util.CellRangeAddress;
26
27 /**
28 *
29 * <b>
30 * excel 工具
31 * </b>
32 * @author kangxu
33 *
34 */
35 public class ExcelUtil {
36
37 /**
38 * 導出 excel
39 * @param filePath 文件全路徑
40 * @param sheetName sheet頁名稱
41 * @param sheetIndex 當前sheet下表 從0開始
42 * @param fileHeader 頭部
43 * @param datas 內容
44 */
45 public static void writeExcel(String filePath,String sheetName,
46 int sheetIndex,
47 String[] fileHeader,
48 List<String[]> datas){
49 // 創建工作簿
50 Workbook wb = new HSSFWorkbook();
51 // 創建工作表 sheet
52 Sheet s = wb.createSheet();
53
54 wb.setSheetName(sheetIndex, sheetName);
55
56 Row r = s.createRow(0);
57 Cell c = null;
58 Font font = null;
59 CellStyle styleHeader = null;
60 CellStyle styleContent = null;
61
62
63 //粗體
64 font = wb.createFont();
65 font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
66 // 設置頭樣式
67 styleHeader = wb.createCellStyle();
68 styleHeader.setFont(font);
69 styleHeader.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下邊框
70 styleHeader.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左邊框
71 styleHeader.setBorderTop(HSSFCellStyle.BORDER_THIN);//上邊框
72 styleHeader.setBorderRight(HSSFCellStyle.BORDER_THIN);//右邊框
73 // 設置內容樣式
74 styleContent = wb.createCellStyle();
75 styleContent.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下邊框
76 styleContent.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左邊框
77 styleContent.setBorderTop(HSSFCellStyle.BORDER_THIN);//上邊框
78 styleContent.setBorderRight(HSSFCellStyle.BORDER_THIN);//右邊框
79
80
81 //設置頭
82 for(int i=0;i<fileHeader.length;){
83 c = r.createCell(i);
84 c.setCellStyle(styleHeader);
85 c.setCellValue(fileHeader[i]);
86 i++;
87 }
88
89 //設置內容
90 for(int rownum=0;rownum<datas.size();){ // 行 row datas.size()
91 r = s.createRow(rownum+1); //創建行
92 for(int cellnum=0;cellnum<fileHeader.length;){
93 c = r.createCell(cellnum);
94
95 c.setCellValue(datas.get(rownum)[cellnum]);
96 c.setCellStyle(styleContent);
97 cellnum++;
98 }
99
100 rownum++;
101 }
102
103 FileOutputStream out = null;
104 try {
105 // 創建文件或者文件夾,將內容寫進去
106 if(FileUtil.createFile(new File(filePath))){
107 out = new FileOutputStream(filePath);
108 wb.write(out);
109 }
110
111 } catch (Exception e) {
112 e.printStackTrace();
113 }finally {
114 try {
115 // 關閉流
116 if(out != null){
117 out.flush();
118 out.close();
119 }
120 } catch (IOException e) {
121 e.printStackTrace();
122 }
123 }
124
125 }
126
127 /**
128 * 讀取 excel 文件內容
129 * @param filePath
130 * @param sheetIndex
131 */
132 public static List<Map<String,String>> readExcel(String filePath,int sheetIndex){
133 List<Map<String,String>> mapList = new ArrayList<Map<String,String>>();
134 // 頭
135 List<String> list = new ArrayList<String>();
136 //
137
138 int cnt = 0;
139 int idx = 0;
140
141 try {
142 InputStream input = new FileInputStream(filePath); //建立輸入流
143 Workbook wb = null;
144
145 wb = new HSSFWorkbook(input);
146
147 // 獲取sheet頁
148 Sheet sheet = wb.getSheetAt(sheetIndex);
149
150 Iterator<Row> rows = sheet.rowIterator();
151 while (rows.hasNext()) {
152 Row row = rows.next();
153 Iterator<Cell> cells = row.cellIterator();
154
155 Map<String,String> map = new HashMap<String,String>();
156
157 if(cnt == 0){ // 將頭放進list中
158 while (cells.hasNext()) {
159 Cell cell = cells.next();
160 if(isContainMergeCell(sheet)){
161 cancelMergeCell(sheet);
162 }
163 list.add(getStringCellValue(cell));
164 }
165 cnt ++;
166 continue;
167
168 }else {
169 while (cells.hasNext()) {
170 Cell cell = cells.next();
171 if(isContainMergeCell(sheet)){
172 cancelMergeCell(sheet);
173 }
174 // 區別相同的頭
175 list = ListUtil.changeSameVal(list);
176 map.put(list.get(idx++), getStringCellValue(cell));
177 }
178 }
179 idx = 0;
180 mapList.add(map);
181
182 }
183 return mapList;
184 } catch (IOException ex) {
185 ex.printStackTrace();
186 }
187 return null;
188
189 }
190
191 /**
192 * 合並單元格
193 * @param sheet 當前sheet頁
194 * @param firstRow 開始行
195 * @param lastRow 結束行
196 * @param firstCol 開始列
197 * @param lastCol 結束列
198 */
199 public static int mergeCell(Sheet sheet,int firstRow,int lastRow,int firstCol,int lastCol){
200 if(sheet == null){
201 return -1;
202 }
203 return sheet.addMergedRegion(new CellRangeAddress(firstRow, lastRow, firstCol, lastCol));
204 }
205
206
207 /**
208 * 取消合並單元格
209 * @param sheet
210 * @param idx
211 */
212 public static void cancelMergeCell(Sheet sheet){
213 int sheetMergeCount = sheet.getNumMergedRegions();
214 for(int idx = 0; idx < sheetMergeCount;){
215 CellRangeAddress range = sheet.getMergedRegion(idx);
216
217 String val = getMergeCellValue(sheet,range.getFirstRow(),range.getLastRow());
218 // 取消合並單元格
219 sheet.removeMergedRegion(idx);
220
221 for(int rownum=range.getFirstRow();rownum<range.getLastRow()+1;){
222 for(int cellnum=range.getFirstColumn();cellnum<range.getLastColumn()+1;){
223
224 sheet.getRow(rownum).getCell(cellnum).setCellValue(val);
225
226 cellnum ++;
227 }
228
229 rownum ++;
230 }
231
232 idx++;
233 }
234 }
235
236 /**
237 * 判斷指定單元格是否是合並單元格
238 * @param sheet 當前sheet頁
239 * @param firstRow 開始行
240 * @param lastRow 結束行
241 * @param firstCol 開始列
242 * @param lastCol 結束列
243 * @return
244 */
245 public static boolean isMergeCell(Sheet sheet,
246 int row ,int column){
247
248 int sheetMergeCount = sheet.getNumMergedRegions();
249 for(int i = 0; i < sheetMergeCount;){
250 CellRangeAddress range = sheet.getMergedRegion(i);
251
252 int firstColumn = range.getFirstColumn();
253 int lastColumn = range.getLastColumn();
254 int firstRow = range.getFirstRow();
255 int lastRow = range.getLastRow();
256 if(row >= firstRow && row <= lastRow){
257 if(column >= firstColumn && column <= lastColumn){
258 return true;
259 }
260 }
261
262 i++;
263 }
264 return false;
265 }
266
267 /**
268 * 判斷sheet頁中是否含有合並單元格
269 * @param sheet
270 * @return
271 */
272 public static boolean isContainMergeCell(Sheet sheet){
273 if(sheet == null){
274 return false;
275 }
276 return sheet.getNumMergedRegions()>0 ? true : false;
277 }
278
279 /**
280 * 獲取指定合並單元的值
281 * @param sheet
282 * @param row
283 * @param column
284 * @return
285 */
286 public static String getMergeCellValue(Sheet sheet,
287 int row ,int column){
288
289 int sheetMergeCount = sheet.getNumMergedRegions();
290 for(int i = 0; i < sheetMergeCount;){
291 CellRangeAddress range = sheet.getMergedRegion(i);
292
293 int firstColumn = range.getFirstColumn();
294 int lastColumn = range.getLastColumn();
295 int firstRow = range.getFirstRow();
296 int lastRow = range.getLastRow();
297 if(row >= firstRow && row <= lastRow){
298 if(column >= firstColumn && column <= lastColumn){
299 Row fRow = sheet.getRow(firstRow);
300 Cell fCell = fRow.getCell(firstColumn);
301
302 return getStringCellValue(fCell) ;
303 }
304 }
305
306 i++;
307 }
308
309 return null;
310 }
311
312 /**
313 * 獲取單元格的值
314 * @param cell
315 * @return
316 */
317 public static String getStringCellValue(Cell cell) {
318 String strCell = "";
319 if(cell==null) return strCell;
320 switch (cell.getCellType()) {
321 case Cell.CELL_TYPE_STRING:
322 strCell = cell.getRichStringCellValue().getString().trim();
323 break;
324 case Cell.CELL_TYPE_NUMERIC:
325 strCell = String.valueOf(cell.getNumericCellValue());
326 break;
327 case Cell.CELL_TYPE_BOOLEAN:
328 strCell = String.valueOf(cell.getBooleanCellValue());
329 break;
330 case Cell.CELL_TYPE_FORMULA:
331 FormulaEvaluator evaluator = cell.getSheet().getWorkbook().getCreationHelper().createFormulaEvaluator();
332 evaluator.evaluateFormulaCell(cell);
333 CellValue cellValue = evaluator.evaluate(cell);
334 strCell = String.valueOf(cellValue.getNumberValue()) ;
335 break;
336 default:
337 strCell = "";
338 }
339 return strCell;
340 }
341
342 }
調用方式如下
ExcelUtilTester.java
1 package pers.kangxu.datautils.test;
2
3 import java.util.ArrayList;
4 import java.util.List;
5
6 import pers.kangxu.datautils.utils.ExcelUtil;
7
8 public class ExcelUtilTester {
9
10 public static void main(String[] args) {
11 List<String[]> datas = new ArrayList<String[]>();
12 datas.add(new String[]{"狗熊","母","250"});
13 datas.add(new String[]{"豬糧","不明","251"});
14 //ExcelUtil.writeExcel("C:\\Users\\Administrator\\Desktop\\test\\test\\test.xls","sheet1",0, new String[]{"姓名","年齡","性別"}, datas);
15
16 System.out.println(ExcelUtil.readExcel("C:\\Users\\Administrator\\Desktop\\test\\test\\test.xls", 0));
17
18 }
19 }
