准备工作：
导入POI对应的Jar包
1 <dependency>
2 <groupId>org.apache.poi</groupId>
3 <artifactId>poi-ooxml</artifactId>
4 <version>3.14-beta1</version>
5 </dependency>
6 <dependency>
7 <groupId>org.apache.poi</groupId>
8 <artifactId>poi-ooxml-schemas</artifactId>
9 <version>3.14-beta1</version>
10 </dependency>
11 <dependency>
12 <groupId>org.apache.poi</groupId>
13 <artifactId>poi</artifactId>
14 <version>3.14-beta1</version>
15 </dependency>
16 <dependency>
17 <groupId>org.apache.httpcomponents</groupId>
18 <artifactId>httpclient</artifactId>
19 <version>4.5.2</version>
20 </dependency>
创建一个ExcelBean实现对数据的封装
1 package com.website.system.utils;
2
3 import org.apache.poi.xssf.usermodel.XSSFCellStyle;
4
5 public class ExcelBean implements java.io.Serializable {
6 private String headTextName; //列头（标题）名
7 private String propertyName; //对应字段名
8 private Integer cols; //合并单元格数
9 private XSSFCellStyle cellStyle;
10 public ExcelBean(){
11 }
12 public ExcelBean(String headTextName, String propertyName){
13 this.headTextName = headTextName;
14 this.propertyName = propertyName;
15 }
16 public ExcelBean(String headTextName, String propertyName, Integer cols) {
17 super();
18 this.headTextName = headTextName;
19 this.propertyName = propertyName;
20 this.cols = cols;
21 }
22
23 public String getHeadTextName() {
24 return headTextName;
25 }
26
27 public void setHeadTextName(String headTextName) {
28 this.headTextName = headTextName;
29 }
30
31 public String getPropertyName() {
32 return propertyName;
33 }
34
35 public void setPropertyName(String propertyName) {
36 this.propertyName = propertyName;
37 }
38
39 public Integer getCols() {
40 return cols;
41 }
42
43 public void setCols(Integer cols) {
44 this.cols = cols;
45 }
46
47 public XSSFCellStyle getCellStyle() {
48 return cellStyle;
49 }
50
51 public void setCellStyle(XSSFCellStyle cellStyle) {
52 this.cellStyle = cellStyle;
53 }
54 }
创建一个Excel导入导出工具类ExcelUtil
1 package com.website.system.utils;
2 import org.apache.http.client.utils.DateUtils;
3 import org.apache.poi.hssf.usermodel.HSSFWorkbook;
4 import org.apache.poi.ss.usermodel.Cell;
5 import org.apache.poi.ss.usermodel.Row;
6 import org.apache.poi.ss.usermodel.Sheet;
7 import org.apache.poi.ss.usermodel.Workbook;
8 import org.apache.poi.ss.util.CellRangeAddress;
9 import org.apache.poi.xssf.usermodel.*;
10
11 import java.beans.IntrospectionException;
12 import java.beans.PropertyDescriptor;
13 import java.io.*;
14 import java.lang.reflect.InvocationTargetException;
15 import java.lang.reflect.Method;
16 import java.math.BigDecimal;
17 import java.text.DecimalFormat;
18 import java.text.NumberFormat;
19 import java.text.ParseException;
20 import java.text.SimpleDateFormat;
21 import java.util.ArrayList;
22 import java.util.Date;
23 import java.util.List;
24 import java.util.Map;
25
26 public class ExcelUtil {
27 private final static String excel2003L =".xls"; //2003- 版本的excel
28 private final static String excel2007U =".xlsx"; //2007+ 版本的excel
29 /**
30 * Excel导入
31 */
32 public static List<List<Object>> getBankListByExcel(InputStream in, String fileName) throws Exception{
33 List<List<Object>> list = null;
34 //创建Excel工作薄
35 Workbook work = getWorkbook(in,fileName);
36 if(null == work){
37 throw new Exception("创建Excel工作薄为空！");
38 }
39 Sheet sheet = null;
40 Row row = null;
41 Cell cell = null;
42 list = new ArrayList<List<Object>>();
43 //遍历Excel中所有的sheet
44 for (int i = 0; i < work.getNumberOfSheets(); i++) {
45 sheet = work.getSheetAt(i);
46 if(sheet==null){continue;}
47 //遍历当前sheet中的所有行
48 //包涵头部，所以要小于等于最后一列数,这里也可以在初始值加上头部行数，以便跳过头部
49 for (int j = sheet.getFirstRowNum(); j <= sheet.getLastRowNum(); j++) {
50 //读取一行
51 row = sheet.getRow(j);
52 //去掉空行和表头
53 if(row==null||row.getFirstCellNum()==j){continue;}
54 //遍历所有的列
55 List<Object> li = new ArrayList<Object>();
56 for (int y = row.getFirstCellNum(); y < row.getLastCellNum(); y++) {
57 cell = row.getCell(y);
58 li.add(getCellValue(cell));
59 }
60 list.add(li);
61 }
62 }
63 return list;
64 }
65
66 private static Workbook getWorkbook(InputStream inStr,String fileName) throws Exception {
67 Workbook wb = null;
68 String fileType = fileName.substring(fileName.lastIndexOf("."));
69 if(excel2003L.equals(fileType)){
70 wb = new HSSFWorkbook(inStr); //2003-
71 }else if(excel2007U.equals(fileType)){
72 wb = new XSSFWorkbook(inStr); //2007+
73 }else{
74 throw new Exception("解析的文件格式有误！");
75 }
76 return wb;
77 }
78 /**
79 * 描述：对表格中数值进行格式化
80 */
81 public static Object getCellValue(Cell cell){
82 Object value = null;
83 DecimalFormat df = new DecimalFormat("0"); //格式化字符类型的数字
84 SimpleDateFormat sdf = new SimpleDateFormat("yyy-MM-dd"); //日期格式化
85 DecimalFormat df2 = new DecimalFormat("0.00"); //格式化数字
86 switch (cell.getCellType()) {
87 case Cell.CELL_TYPE_STRING:
88 value = cell.getRichStringCellValue().getString();
89 break;
90 case Cell.CELL_TYPE_NUMERIC:
91 if("General".equals(cell.getCellStyle().getDataFormatString())){
92 value = df.format(cell.getNumericCellValue());
93 }else if("m/d/yy".equals(cell.getCellStyle().getDataFormatString())){
94 value = sdf.format(cell.getDateCellValue());
95 }else{
96 value = df2.format(cell.getNumericCellValue());
97 }
98 break;
99 case Cell.CELL_TYPE_BOOLEAN:
100 value = cell.getBooleanCellValue();
101 break;
102 case Cell.CELL_TYPE_BLANK:
103 value = "";
104 break;
105 default:
106 break;
107 }
108 return value;
109 }
110
111 /**
112 * 导入Excel表结束
113 * 导出Excel表开始
114 * @param sheetName 工作簿名称
115 * @param clazz 数据源model类型
116 * @param objs excel标题列以及对应model字段名
117 * @param map 标题列行数以及cell字体样式
118 */
119 public static XSSFWorkbook createExcelFile(Class clazz, List objs, Map<Integer, List<ExcelBean>> map, String sheetName) throws
120 IllegalArgumentException,IllegalAccessException, InvocationTargetException,
121 ClassNotFoundException, IntrospectionException, ParseException {
122 // 创建新的Excel工作簿
123 XSSFWorkbook workbook = new XSSFWorkbook();
124 // 在Excel工作簿中建一工作表，其名为缺省值, 也可以指定Sheet名称
125 XSSFSheet sheet = workbook.createSheet(sheetName);
126 // 以下为excel的字体样式以及excel的标题与内容的创建，下面会具体分析;
127 createFont(workbook); //字体样式
128 createTableHeader(sheet, map); //创建标题（头）
129 createTableRows(sheet, map, objs, clazz); //创建内容
130 return workbook;
131 }
132 private static XSSFCellStyle fontStyle;
133 private static XSSFCellStyle fontStyle2;
134 public static void createFont(XSSFWorkbook workbook) {
135 // 表头
136 fontStyle = workbook.createCellStyle();
137 XSSFFont font1 = workbook.createFont();
138 font1.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);
139 font1.setFontName("黑体");
140 font1.setFontHeightInPoints((short) 14);// 设置字体大小
141 fontStyle.setFont(font1);
142 fontStyle.setBorderBottom(XSSFCellStyle.BORDER_THIN); // 下边框
143 fontStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN);// 左边框
144 fontStyle.setBorderTop(XSSFCellStyle.BORDER_THIN);// 上边框
145 fontStyle.setBorderRight(XSSFCellStyle.BORDER_THIN);// 右边框
146 fontStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER); // 居中
147 // 内容
148 fontStyle2=workbook.createCellStyle();
149 XSSFFont font2 = workbook.createFont();
150 font2.setFontName("宋体");
151 font2.setFontHeightInPoints((short) 10);// 设置字体大小
152 fontStyle2.setFont(font2);
153 fontStyle2.setBorderBottom(XSSFCellStyle.BORDER_THIN); // 下边框
154 fontStyle2.setBorderLeft(XSSFCellStyle.BORDER_THIN);// 左边框
155 fontStyle2.setBorderTop(XSSFCellStyle.BORDER_THIN);// 上边框
156 fontStyle2.setBorderRight(XSSFCellStyle.BORDER_THIN);// 右边框
157 fontStyle2.setAlignment(XSSFCellStyle.ALIGN_CENTER); // 居中
158 }
159
160 /**
161 * 根据ExcelMapping 生成列头（多行列头）
162 *
163 * @param sheet 工作簿
164 * @param map 每行每个单元格对应的列头信息
165 */
166 public static final void createTableHeader(XSSFSheet sheet, Map<Integer, List<ExcelBean>> map) {
167 int startIndex=0;//cell起始位置
168 int endIndex=0;//cell终止位置
169 for (Map.Entry<Integer, List<ExcelBean>> entry : map.entrySet()) {
170 XSSFRow row = sheet.createRow(entry.getKey());
171 List<ExcelBean> excels = entry.getValue();
172 for (int x = 0; x < excels.size(); x++) {
173 //合并单元格
174 if(excels.get(x).getCols()>1){
175 if(x==0){
176 endIndex+=excels.get(x).getCols()-1;
177 CellRangeAddress range=new CellRangeAddress(0,0,startIndex,endIndex);
178 sheet.addMergedRegion(range);
179 startIndex+=excels.get(x).getCols();
180 }else{
181 endIndex+=excels.get(x).getCols();
182 CellRangeAddress range=new CellRangeAddress(0,0,startIndex,endIndex);
183 sheet.addMergedRegion(range);
184 startIndex+=excels.get(x).getCols();
185 }
186 XSSFCell cell = row.createCell(startIndex-excels.get(x).getCols());
187 cell.setCellValue(excels.get(x).getHeadTextName());// 设置内容
188 if (excels.get(x).getCellStyle() != null) {
189 cell.setCellStyle(excels.get(x).getCellStyle());// 设置格式
190 }
191 cell.setCellStyle(fontStyle);
192 }else{
193 XSSFCell cell = row.createCell(x);
194 cell.setCellValue(excels.get(x).getHeadTextName());// 设置内容
195 if (excels.get(x).getCellStyle() != null) {
196 cell.setCellStyle(excels.get(x).getCellStyle());// 设置格式
197 }
198 cell.setCellStyle(fontStyle);
199 }
200 }
201 }
202 }
203 public static void createTableRows(XSSFSheet sheet, Map<Integer, List<ExcelBean>> map, List objs, Class clazz)
204 throws IllegalArgumentException, IllegalAccessException, InvocationTargetException, IntrospectionException,
205 ClassNotFoundException, ParseException {
206 int rowindex = map.size();
207 int maxKey = 0;
208 List<ExcelBean> ems = new ArrayList<>();
209 for (Map.Entry<Integer, List<ExcelBean>> entry : map.entrySet()) {
210 if (entry.getKey() > maxKey) {
211 maxKey = entry.getKey();
212 }
213 }
214 ems = map.get(maxKey);
215 List<Integer> widths = new ArrayList<Integer>(ems.size());
216 for (Object obj : objs) {
217 XSSFRow row = sheet.createRow(rowindex);
218 for (int i = 0; i < ems.size(); i++) {
219 ExcelBean em = (ExcelBean) ems.get(i);
220 // 获得get方法
221 PropertyDescriptor pd = new PropertyDescriptor(em.getPropertyName(), clazz);
222 Method getMethod = pd.getReadMethod();
223 Object rtn = getMethod.invoke(obj);
224 String value = "";
225 // 如果是日期类型进行转换
226 if (rtn != null) {
227 if (rtn instanceof Date) {
228 value = DateUtils.formatDate((Date)rtn,"yyyy-MM-dd");
229 } else if(rtn instanceof BigDecimal){
230 NumberFormat nf = new DecimalFormat("#,##0.00");
231 value=nf.format((BigDecimal)rtn).toString();
232 } else if((rtn instanceof Integer) && (Integer.valueOf(rtn.toString())<0 )){
233 value="--";
234 }else {
235 value = rtn.toString();
236 }
237 }
238 XSSFCell cell = row.createCell(i);
239 cell.setCellValue(value);
240 cell.setCellType(XSSFCell.CELL_TYPE_STRING);
241 cell.setCellStyle(fontStyle2);
242 // 获得最大列宽
243 int width = value.getBytes().length * 300;
244 // 还未设置，设置当前
245 if (widths.size() <= i) {
246 widths.add(width);
247 continue;
248 }
249 // 比原来大，更新数据
250 if (width > widths.get(i)) {
251 widths.set(i, width);
252 }
253 }
254 rowindex++;
255 }
256 // 设置列宽
257 for (int index = 0; index < widths.size(); index++) {
258 Integer width = widths.get(index);
259 width = width < 2500 ? 2500 : width + 300;
260 width = width > 10000 ? 10000 + 300 : width + 300;
261 sheet.setColumnWidth(index, width);
262 }
263 }
264 }
实体类
1 package com.website.system.model;
2
3 public class Studentdb {
4 private Integer studentid;
5 private Integer classid;
6 private String name;
7 private Integer age;
8 private String city;
9 private Integer score;
10
11 public Integer getStudentid() {
12 return studentid;
13 }
14
15 public void setStudentid(Integer studentid) {
16 this.studentid = studentid;
17 }
18
19 public Integer getClassid() {
20 return classid;
21 }
22
23 public void setClassid(Integer classid) {
24 this.classid = classid;
25 }
26
27 public String getName() {
28 return name;
29 }
30
31 public void setName(String name) {
32 this.name = name;
33 }
34
35 public Integer getAge() {
36 return age;
37 }
38
39 public void setAge(Integer age) {
40 this.age = age;
41 }
42
43 public String getCity() {
44 return city;
45 }
46
47 public void setCity(String city) {
48 this.city = city;
49 }
50
51 public Integer getScore() {
52 return score;
53 }
54
55 public void setScore(Integer score) {
56 this.score = score;
57 }
58
59 @Override
60 public String toString() {
61 return "Studentdb{" +
62 "studentid=" + studentid +
63 ", classid=" + classid +
64 ", name='" + name + '\'' +
65 ", age=" + age +
66 ", city='" + city + '\'' +
67 ", score=" + score +
68 '}';
69 }
70 }
dao数据访问层
1 public interface StudentdbMapper {
2 boolean insertdb(List<Studentdb> list);
3 }
mapper sql语句
1 <mapper namespace="com.website.system.dao.StudentdbMapper">
2 <insert id="insertdb" parameterType="java.util.List">
3 insert into studentdb (studentid,classid, name,age, city, score)
4 values
5 <foreach collection="list" item="item" index="index" separator=",">
6 (#{item.studentid}, #{item.classid}, #{item.name},#{item.age}, #{item.city},
7 #{item.score})
8 </foreach>
9 </insert>
10 </mapper>
Service
1 public interface StudentdbService {
2 void insertdb(InputStream in, MultipartFile file) throws Exception;
3 //boolean insertdb(Studentdb studentdb);
4 }
ServiceImpl
1 @Service
2 public class StudentServiceImpl implements StudentdbService {
3 @Resource
4 private StudentdbMapper studentdbMapper;
5
6 /*@Override
7 public boolean insertdb(Studentdb studentdb) {
8 return studentdbMapper.insertdb(studentdb);
9 }*/
10
11 @Override
12 public void insertdb(InputStream in, MultipartFile file)throws Exception {
13 System.out.println(file.getOriginalFilename());
14 List<List<Object>> listob = ExcelUtil.getBankListByExcel(in,file.getOriginalFilename());
15 List<Studentdb> salaryList = new ArrayList<Studentdb>();
16 //遍历listob数据，把数据放到List中
17 for (int i = 0; i < listob.size(); i++) {
18 List<Object> ob = listob.get(i);
19 Studentdb studentdb = new Studentdb();
20 //设置编号
21 // studentdb.setSerial(SerialUtil.salarySerial());
22 //通过遍历实现把每一列封装成一个model中，再把所有的model用List集合装载
23 //studentdb.setAdminId(adminId);
24 int studentid=Integer.parseInt(String.valueOf(ob.get(0)));
25 int classid=Integer.parseInt(String.valueOf(ob.get(1)));
26 int age=Integer.parseInt(String.valueOf(ob.get(3)));
27 int score=Integer.parseInt(String.valueOf(ob.get(5)));
28 studentdb.setStudentid(studentid);
29 studentdb.setClassid(classid);
30 studentdb.setName(String.valueOf(ob.get(2)));
31 studentdb.setAge(age);
32 studentdb.setCity(String.valueOf(ob.get(4)));
33 studentdb.setScore(score);
34 /* salarymanage.setBankCard(String.valueOf(ob.get(6)));
35 salarymanage.setBank(String.valueOf(ob.get(7)));
36 //object类型转Double类型
37 salarymanage.setMoney(Double.parseDouble(ob.get(8).toString()));
38 salarymanage.setRemark(String.valueOf(ob.get(9)));
39 salarymanage.setSalaryDate(salaryDate);*/
40 salaryList.add(studentdb);
41 }
42 //批量插入
43 studentdbMapper.insertdb(salaryList);
44 //salarymanageDao.insertInfoBatch(salaryList);
45 }
46 }
controller
1 @Controller
2 public class StudentdbController {
3
4 @Resource
5 private StudentdbService StudentdbService;
6 @RequestMapping("/import")
7 public String impotr(HttpServletRequest request) throws Exception {
8 //获取上传的文件
9 MultipartHttpServletRequest multipart = (MultipartHttpServletRequest) request;
10 MultipartFile file = multipart.getFile("file");
11 String filename = request.getParameter("filename");
12 InputStream in = file.getInputStream();
13 System.out.println(filename);
14 //数据导入
15 StudentdbService.insertdb(in,file);
16 in.close();
17 return "true";
18 }
19
20 }
html页面和js
1 <script>
2 function uploadPic() {
3 var form = document.getElementById('upload'),
4 formData = new FormData(form);
5 $.ajax({
6 url:"/import",
7 type:"post",
8 data:formData,
9 processData:false,
10 contentType:false,
11 success:function(res){
12 if(res){
13 alert("上传成功！");
14 }
15 console.log(res);
16 $("#file").val("");
17 $(".showUrl").html(res);
18 $(".showPic").attr("src",res);
19 },
20 error:function(err){
21 alert("网络连接失败,稍后重试",err);
22 }
23
24 })
25
26 }
27 </script>
28 <body>
29 <form id="upload" enctype="multipart/form-data" method="post">
30 <input type="file" name="file" id="file"/>
31 <input type="button" value="提交" οnclick="uploadPic();"/>
32 <span class="showUrl"></span>
33 <img src="" class="showPic" alt="">
34 </form>
35 </body>
导出：
Excel导出Controller端实现
1 @RequestMapping("/export")
2 public @ResponseBody void export(HttpServletRequest request, HttpServletResponse response) throwsClassNotFoundException, IntrospectionException, IllegalAccessException, ParseException, InvocationTargetException {
3 String salaryDate = request.getParameter("salaryDate");//获取前端的查询条件
4 if(salaryDate!=""){
5 response.reset(); //清除buffer缓存
6 Map<String,Object> map=new HashMap<String,Object>();
7 // 指定下载的文件名，浏览器都会使用本地编码，即GBK，浏览器收到这个文件名后，用ISO-8859-1来解码，然后用GBK来显示
8 // 所以我们用GBK解码，ISO-8859-1来编码，在浏览器那边会反过来执行。
9 response.setHeader("Content-Disposition", "attachment;filename=" + new String(salaryDate.getBytes("GBK"),"ISO-8859-1"));
10 response.setContentType("application/vnd.ms-excel;charset=UTF-8");
11 response.setHeader("Pragma", "no-cache");
12 response.setHeader("Cache-Control", "no-cache");
13 response.setDateHeader("Expires", 0);
14 XSSFWorkbook workbook=null;
15 //导出Excel对象
16 workbook = salaryService.exportExcelInfo(salaryDate);
17 OutputStream output;
18 try {
19 output = response.getOutputStream();
20 BufferedOutputStream bufferedOutPut = new BufferedOutputStream(output);
21 bufferedOutPut.flush();
22 workbook.write(bufferedOutPut);
23 bufferedOutPut.close();
24 } catch (IOException e) {
25 e.printStackTrace();
26 }
27 }
28 }
Service层，这里是exportExcelInfo的实现方法
1 public XSSFWorkbook exportExcelInfo(String salaryDate) throws InvocationTargetException, ClassNotFoundException, IntrospectionException, ParseException, IllegalAccessException {
2 //根据条件查询数据，把数据装载到一个list中
3 List<Salarymanage> list = salarymanageDao.selectApartInfo(salaryDate);
4 for(int i=0;i<list.size();i++){
5 //查询财务名字
6 int adminId = list.get(i).getAdminId();
7 String adminName = salarymanageDao.selectAdminNameById(adminId);
8 list.get(i).setAdminName(adminName);
9 list.get(i).setId(i+1);
10 }
11 List<ExcelBean> excel=new ArrayList<>();
12 Map<Integer,List<ExcelBean>> map=new LinkedHashMap<>();
13 XSSFWorkbook xssfWorkbook=null;
14 //设置标题栏
15 excel.add(new ExcelBean("序号","id",0));
16 excel.add(new ExcelBean("厂名","company",0));
17 excel.add(new ExcelBean("工号","number",0));
18 excel.add(new ExcelBean("姓名","name",0));
19 excel.add(new ExcelBean("性别","sex",0));
20 excel.add(new ExcelBean("开户名","cardName",0));
21 excel.add(new ExcelBean("银行卡号","bankCard",0));
22 excel.add(new ExcelBean("开户行","bank",0));
23 excel.add(new ExcelBean("金额","money",0));
24 excel.add(new ExcelBean("备注","remark",0));
25 map.put(0, excel);
26 String sheetName = salaryDate + "月份收入";
27 //调用ExcelUtil的方法
28 xssfWorkbook = ExcelUtil.createExcelFile(Salarymanage.class, list, map, sheetName);
29 return xssfWorkbook;
30 }
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
