Skip to content

Commit c7ec412

Browse files
houkohouko
committed
添加util方法
1 parent 6bae63f commit c7ec412

File tree

6 files changed

+554
-1
lines changed

6 files changed

+554
-1
lines changed

‎application/src/test/java/Test11.java‎

Lines changed: 44 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,44 @@
1+
import info.xiaomo.application.ApplicationMain;
2+
import info.xiaomo.application.dao.CountryDao;
3+
import info.xiaomo.application.model.CountryModel;
4+
import org.junit.Test;
5+
import org.junit.runner.RunWith;
6+
import org.springframework.beans.factory.annotation.Autowired;
7+
import org.springframework.boot.test.SpringApplicationConfiguration;
8+
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;
9+
import org.springframework.test.context.web.WebAppConfiguration;
10+
11+
import java.io.IOException;
12+
import java.util.Date;
13+
import java.util.List;
14+
15+
import static info.xiaomo.core.untils.ExcelUtil.getListData;
16+
17+
/**
18+
* @author 小莫 (https://xiaomo.info) (https://github.com/syoubaku)
19+
* @version : 2017/1/13 18:33
20+
*/
21+
@RunWith(SpringJUnit4ClassRunner.class) // SpringJUnit支持，由此引入Spring-Test框架支持！
22+
@SpringApplicationConfiguration(classes = ApplicationMain.class) // 指定我们SpringBoot工程的Application启动类
23+
@WebAppConfiguration // 由于是Web项目，Junit需要模拟ServletContext，因此我们需要给我们的测试类加上@WebAppConfiguration。
24+
public class Test11 {
25+
26+
@Autowired
27+
private
28+
CountryDao dao;
29+
30+
@Test
31+
public void test() throws IOException {
32+
List<List<String>> listData = getListData("E:\\thinkpage_cities.xls", 1);
33+
for (List<String> listDatum : listData) {
34+
CountryModel model = new CountryModel();
35+
model.setCityName(listDatum.get(0));
36+
model.setName(listDatum.get(1));
37+
model.setCreateTime(new Date());
38+
model.setUpdateTime(new Date());
39+
dao.save(model);
40+
}
41+
42+
}
43+
}
44+

‎core/pom.xml‎

Lines changed: 8 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -75,5 +75,13 @@
7575
<groupId>org.springframework.boot</groupId>
7676
<artifactId>spring-boot-starter-data-jpa</artifactId>
7777
</dependency>
78+
<dependency>
79+
<groupId>net.sourceforge.jexcelapi</groupId>
80+
<artifactId>jxl</artifactId>
81+
</dependency>
82+
<dependency>
83+
<groupId>org.apache.poi</groupId>
84+
<artifactId>poi</artifactId>
85+
</dependency>
7886
</dependencies>
7987
</project>

‎core/src/main/java/info/xiaomo/core/untils/ExcelUtil.java‎

Lines changed: 161 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,161 @@
1+
package info.xiaomo.core.untils;
2+
3+
import info.xiaomo.core.other.CountryModel;
4+
import org.apache.poi.hssf.usermodel.*;
5+
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
6+
7+
import java.io.*;
8+
import java.text.DecimalFormat;
9+
import java.text.SimpleDateFormat;
10+
import java.util.ArrayList;
11+
import java.util.Arrays;
12+
import java.util.Date;
13+
import java.util.List;
14+
15+
public class ExcelUtil {
16+
17+
public static void main(String[] args) throws Exception {
18+
List<List<String>> listData = getListData("E:\\thinkpage_cities.xls", 1);
19+
List<CountryModel> list = new ArrayList<>();
20+
for (List<String> listDatum : listData) {
21+
CountryModel model = new CountryModel();
22+
model.setCityName(listDatum.get(0));
23+
model.setCityNameEN(listDatum.get(1));
24+
list.add(model);
25+
}
26+
System.out.println(list);
27+
}
28+
29+
public static List<List<String>> getListData(String path, int ignoreRows) throws IOException {
30+
File file = new File(path);
31+
String[][] result = getData(file, ignoreRows);
32+
List<List<String>> lists = new ArrayList<>();
33+
for (String[] aResult : result) {
34+
List<String> list = new ArrayList<>();
35+
for (String anAResult : aResult) {
36+
if (!anAResult.equals("")) {
37+
list.add(anAResult);
38+
}
39+
}
40+
lists.add(list);
41+
}
42+
return lists;
43+
}
44+
45+
/**
46+
* 读取Excel的内容，第一维数组存储的是一行中格列的值，二维数组存储的是多少个行
47+
*
48+
* @param file 读取数据的源Excel
49+
* @param ignoreRows 读取数据忽略的行数，比喻行头不需要读入 忽略的行数为1
50+
* @return 读出的Excel中数据的内容
51+
* @throws FileNotFoundException
52+
* @throws IOException
53+
*/
54+
private static String[][] getData(File file, int ignoreRows)
55+
throws IOException {
56+
List<String[]> result = new ArrayList<>();
57+
int rowSize = 0;
58+
BufferedInputStream in = new BufferedInputStream(new FileInputStream(
59+
file));
60+
// 打开HSSFWorkbook
61+
POIFSFileSystem fs = new POIFSFileSystem(in);
62+
HSSFWorkbook wb = new HSSFWorkbook(fs);
63+
HSSFCell cell;
64+
for (int sheetIndex = 0; sheetIndex < wb.getNumberOfSheets(); sheetIndex++) {
65+
HSSFSheet st = wb.getSheetAt(sheetIndex);
66+
// 第一行为标题，不取
67+
for (int rowIndex = ignoreRows; rowIndex <= st.getLastRowNum(); rowIndex++) {
68+
HSSFRow row = st.getRow(rowIndex);
69+
if (row == null) {
70+
continue;
71+
}
72+
int tempRowSize = row.getLastCellNum() + 1;
73+
if (tempRowSize > rowSize) {
74+
rowSize = tempRowSize;
75+
}
76+
String[] values = new String[rowSize];
77+
Arrays.fill(values, "");
78+
boolean hasValue = false;
79+
for (short columnIndex = 0; columnIndex <= row.getLastCellNum(); columnIndex++) {
80+
String value = "";
81+
cell = row.getCell(columnIndex);
82+
if (cell != null) {
83+
switch (cell.getCellType()) {
84+
case HSSFCell.CELL_TYPE_STRING:
85+
value = cell.getStringCellValue();
86+
break;
87+
case HSSFCell.CELL_TYPE_NUMERIC:
88+
if (HSSFDateUtil.isCellDateFormatted(cell)) {
89+
Date date = cell.getDateCellValue();
90+
if (date != null) {
91+
value = new SimpleDateFormat("yyyy-MM-dd")
92+
.format(date);
93+
} else {
94+
value = "";
95+
}
96+
} else {
97+
value = new DecimalFormat("0").format(cell
98+
.getNumericCellValue());
99+
}
100+
break;
101+
case HSSFCell.CELL_TYPE_FORMULA:
102+
// 导入时如果为公式生成的数据则无值
103+
if (!cell.getStringCellValue().equals("")) {
104+
value = cell.getStringCellValue();
105+
} else {
106+
value = cell.getNumericCellValue() + "";
107+
}
108+
break;
109+
case HSSFCell.CELL_TYPE_BLANK:
110+
break;
111+
case HSSFCell.CELL_TYPE_ERROR:
112+
value = "";
113+
break;
114+
case HSSFCell.CELL_TYPE_BOOLEAN:
115+
value = (cell.getBooleanCellValue() ? "Y"
116+
: "N");
117+
break;
118+
default:
119+
value = "";
120+
}
121+
}
122+
if (columnIndex == 0 && value.trim().equals("")) {
123+
break;
124+
}
125+
values[columnIndex] = rightTrim(value);
126+
hasValue = true;
127+
}
128+
129+
if (hasValue) {
130+
result.add(values);
131+
}
132+
}
133+
}
134+
in.close();
135+
String[][] returnArray = new String[result.size()][rowSize];
136+
for (int i = 0; i < returnArray.length; i++) {
137+
returnArray[i] = result.get(i);
138+
}
139+
return returnArray;
140+
}
141+
142+
/**
143+
* 去掉字符串右边的空格
144+
*
145+
* @param str 要处理的字符串
146+
* @return 处理后的字符串
147+
*/
148+
public static String rightTrim(String str) {
149+
if (str == null) {
150+
return "";
151+
}
152+
int length = str.length();
153+
for (int i = length - 1; i >= 0; i--) {
154+
if (str.charAt(i) != 0x20) {
155+
break;
156+
}
157+
length--;
158+
}
159+
return str.substring(0, length);
160+
}
161+
}

0 commit comments

Comments
 (0)