pom.xml引入包:
<dependency>
<groupId>net.sourceforge.jexcelapi</groupId>
<artifactId>jxl</artifactId>
<version>2.6.12</version>
</dependency>
代码:
/**
* 导入Excel数据
*/
@PostMapping("import")
public AjaxResult importTradLine(@RequestParam(value = "file", required = false)MultipartFile file,@RequestParam String userId){
int b=0;
Date date = new Date();
DateFormat df = new SimpleDateFormat("hh:mm:ss");
SimpleDateFormat dfs = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
try {
// 1.获取用户上传的文件
Workbook workbook = Workbook.getWorkbook(file.getInputStream());
if (workbook == null) {
return error("导入Excel为空");
}
// 2.获取工作簿sheet
Sheet sheet = workbook.getSheet(0);
// 3.获取总行数
int rows = sheet.getRows();
System.out.println("rows:" + rows);
for (int i = 1; i < rows; i++) {
// Students students = new Students();
//Cell oCell= oFirstSheet.getCell(j,i);//需要注意的是这里的getCell方法的参数,第一个是指定第几列,第二个参数才是指定第几行
TradeLine tradeLine= new TradeLine();
String company2 = sheet.getCell(1, i).getContents();
String c1 =company2.replaceAll("\\s", "");
String c2 =company.replaceAll("\\s", "");
if (!c1.equals(c2)) {
continue;
}
tradeLine.setSupplier(c1);
tradeLine.setUnloadCompany(sheet.getCell(2, i).getContents());
tradeLine.setGoods(sheet.getCell(3, i).getContents());
tradeLine.setCarNumber(sheet.getCell(4, i).getContents());
tradeLine.setUnloadNumber(sheet.getCell(5, i).getContents());
tradeLine.setPrice(Double.parseDouble(sheet.getCell(6,i).getContents()));
tradeLine.setTon(Double.parseDouble(sheet.getCell(7, i).getContents()));
tradeLine.setAmounts(Double.parseDouble(sheet.getCell(8,i).getContents()));
String unloadTime=sheet.getCell(9, i).getContents();
String begin = unloadTime + " " + df.format(date);
Date d = dfs.parse(begin);
tradeLine.setUnloadDate(d);
tradeLine.setCreateDate(new Date());
// 4.添加到数据库中
int a =tradeLineService.insert(tradeLine);
b++;
}
// 5.关闭资源
workbook.close();
} catch (Exception e) {
e.printStackTrace();
}
System.out.println("共导入了" + b + "条");
return success("共成功执行了" + b + "条");
}
/**
* 下载导入数据模板
*/
@GetMapping("import/template")
public AjaxResult exportTemplate(HttpServletRequest request,HttpServletResponse response) throws Exception {
// 1.文件下载响应头
response.setContentType("application/msexcel");
response.addHeader("Content-Disposition", "attachment; filename=" + URLEncoder.encode("模板导出.xls", "UTF-8"));
response.setCharacterEncoding("utf-8");
// 2.响应到浏览器
WritableWorkbook workbook = Workbook.createWorkbook(response.getOutputStream());
/**
* 定义单元格样式
*/
WritableFont wf_title = new WritableFont(WritableFont.ARIAL, 12,
WritableFont.BOLD, false, UnderlineStyle.NO_UNDERLINE,
jxl.format.Colour.RED); // 定义格式 字体 下划线 斜体 粗体 颜色
WritableFont wf_head = new WritableFont(WritableFont.ARIAL, 12,
WritableFont.BOLD, false, UnderlineStyle.NO_UNDERLINE,
jxl.format.Colour.BLACK); // 定义格式 字体 下划线 斜体 粗体 颜色
WritableFont wf_table = new WritableFont(WritableFont.ARIAL, 8,
WritableFont.BOLD, false, UnderlineStyle.NO_UNDERLINE,
jxl.format.Colour.BLACK); // 定义格式 字体 下划线 斜体 粗体 颜色
WritableCellFormat wcf_title = new WritableCellFormat(wf_title); // 单元格定义
wcf_title.setBackground(jxl.format.Colour.BLACK); // 设置单元格的背景颜色
wcf_title.setAlignment(jxl.format.Alignment.CENTRE); // 设置对齐方式
WritableCellFormat wcf_head = new WritableCellFormat(wf_head);
wcf_head.setBackground(jxl.format.Colour.BLACK);
wcf_head.setAlignment(jxl.format.Alignment.CENTRE);
WritableCellFormat wcf_table = new WritableCellFormat(wf_table);
wcf_table.setBackground(jxl.format.Colour.BLACK);
wcf_table.setAlignment(jxl.format.Alignment.CENTRE);
// 创建工作簿sheet
WritableSheet sheet = workbook.createSheet("模板", 0);
// 3.设置column名
sheet.addCell(new Label(0, 0, "序号",wcf_head));
sheet.addCell(new Label(1, 0, "供应单位",wcf_head));
sheet.addCell(new Label(2, 0, "卸货厂家",wcf_head));
sheet.addCell(new Label(3, 0, "货品种类",wcf_head));
sheet.addCell(new Label(4, 0, "车牌号码",wcf_head));
sheet.addCell(new Label(5, 0, "卸货磅单",wcf_head));
sheet.addCell(new Label(6, 0, "货品单价(元/吨)",wcf_head));
sheet.addCell(new Label(7, 0, "卸货吨数",wcf_head));
sheet.addCell(new Label(8, 0, "总金额(元)",wcf_head));
sheet.addCell(new Label(9, 0, "卸货日期",wcf_head));
// 4.把核保的数据填充到工作簿中 service调用selectExport()查询数据库
System.out.println("开始导出...");
long s1 = System.currentTimeMillis();
// 5.写入数据
workbook.write();
// 6.关闭资源
workbook.close();
long s2 = System.currentTimeMillis();
long time = s2 - s1;
System.out.println("导出完成!消耗时间:" + time + "毫秒");
return null;
}
备注:如果是查询数据库数据,导出数据,在导出模板代码块中,加入个循环插入即可,不再重复写代码了。