一、实现导出功能
1.1 前端代码
<a class="btn ibtn_export" type="button" href="javascript:void(0)" onclick='vehiclemanageList.download();'>
导出
</a>
js
var vehiclemanageList = window.vehicleManageList || {};
vehiclemanageList.download = function(){
window.location.href="http://localhost:8071/oc-back/vehiclemanage/export.do";
}
1.2 后端导出部分代码
@RequestMapping("/export")
public void exportExcel(String startTime,String endTime,HttpServletResponse response) throws IOException {
List<VehicleManage> list = vehicleManageService.findAll();
//设置导出excel文件名称
String fileName = "vehicleManage";
//创建Excel工作薄对象
HSSFWorkbook workbook = new HSSFWorkbook();
//创建工作表对象
HSSFSheet sheet = workbook.createSheet();
//设置表的最后一列第8列的宽度//设置表头字体样式
//sheet.setColumnWidth(7,500);
//设置表的列宽
for (int i = 0; i < 8; i++) {
sheet.setColumnWidth(i,256 * 20);
}
HSSFFont font = workbook.createFont();
font.setFontName("宋体");
//设置字号
font.setFontHeightInPoints((short)10);
//设置加粗
font.setBoldweight((short) 20);
//设置列字体，这里暂不设置
HSSFCellStyle cellStyle = workbook.createCellStyle();
//水平居中
cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
//垂直居中
cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
//创建第一行，第一行为每一列的标题
HSSFRow row0 = sheet.createRow(0);
HSSFCell cell_ = row0.createCell(0);
cell_.setCellValue("车辆管理");
cell_.setCellStyle(cellStyle);
row0.setHeight((short) (256 * 2));
CellRangeAddress cellRangeAddress = new CellRangeAddress(0, 0, 0, 7);
sheet.addMergedRegion(cellRangeAddress);
//创建第二行，第二行为每一列的标题
HSSFRow row1 = sheet.createRow(1);
row1.setHeight((short) (256 * 2));
String[] cellTitle={"车牌号","车架号","颜色","品牌","姓名","手机号","保险过期时间","创建日期"};
for(int i=0;i<cellTitle.length;i++) {
//依次设置列标题
HSSFCell cell = row1.createCell(i);
cell.setCellValue(cellTitle[i]);
cell.setCellStyle(cellStyle);
}
//表格中数据总共8列，循环新建一行，然后把对象中的值依次写入到这一行中的每一列
for(int i=0;i<list.size();i++) {
//创建新的一行
HSSFRow row = sheet.createRow(i+2);
VehicleManage vehicleManage = list.get(i);
//设置行高
row.setHeight((short) (256 * 2));
//依次为当前行的每一列添加数据
HSSFCell cell0 = row.createCell(0);
cell0.setCellValue(vehicleManage.getLicensePlate());
cell0.setCellStyle(cellStyle);
HSSFCell cell1 = row.createCell(1);
cell1.setCellValue(vehicleManage.getFrameNumber());
cell1.setCellStyle(cellStyle);
HSSFCell cell2 = row.createCell(2);
cell2.setCellValue(vehicleManage.getColor());
cell2.setCellStyle(cellStyle);
HSSFCell cell3 = row.createCell(3);
cell3.setCellValue(vehicleManage.getBrand());
cell3.setCellStyle(cellStyle);
HSSFCell cell4 = row.createCell(4);
cell4.setCellValue(vehicleManage.getName());
cell4.setCellStyle(cellStyle);
HSSFCell cell5 = row.createCell(5);
cell5.setCellValue(vehicleManage.getPhoneNumber());
cell5.setCellStyle(cellStyle);
HSSFCell cell6 = row.createCell(6);
SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");
cell6.setCellValue(format.format(vehicleManage.getInsuranceExpireTime()));
cell6.setCellStyle(cellStyle);
HSSFCell cell7 = row.createCell(7);
cell7.setCellValue(format.format(vehicleManage.getCreateAt()));
cell7.setCellStyle(cellStyle);
}
OutputStream os = response.getOutputStream();
response.reset(); //重置输出流
//设置输出头文件
//.xls是excel文件的后缀
response.setHeader("Content-disposition", "attachment; filename=" + new String(fileName.getBytes("GB2312"), "8859_1") + ".xls");
//定义输出头类型
response.setContentType("application/msexcel");
workbook.write(os);
os.close();
System.out.println("导出完成=============");
}
二、实现导入功能
2.1 excel文件的上传
需要借助jar包：commons-fileupload-1.2.1.jar以及commons-io-1.3.2.jar
2.2 前端代码
<!--中间甚至可以写其他的东西，比如跨div。超级灵活--->
<input type="file" class="form-control" id="uploadFileInput" />
<a class="btn btn-success btn-large" href="javascript:void(0)" id="uploadFileBtn" onclick="vehiclemanageList.upload()">上传文件</a>
js
vehiclemanageList.upload = function () {
var pic = $("#uploadFileInput")[0].files[0];
var fd = new FormData();
fd.append('file', pic);//这里挂载的是一个文件
$.ajax({
url: "upload.do",
type: "post",
// Form数据
data: fd,
cache: false,
contentType: false,
processData: false,
success: function (data) {
$("#uploadFileInput").val("");
}
});
}
2.3 后端代码
@RequestMapping("upload")
public String upload(@RequestParam(value="file",required=true) MultipartFile uploadFile, HttpServletResponse response)throws Exception{
String suffix = uploadFile.getOriginalFilename().split("\\.")[1];
int startRow = 2;
InputStream inputStream = uploadFile.getInputStream();
// 1.定义excel对象变量
Workbook workbook = null;
// 2.判断后缀 决定如何创建具体的对象
if ("xls".equals(suffix)) {
// 2003
workbook = new HSSFWorkbook(inputStream);
} else if ("xlsx".equals(suffix)) {
// 2007
workbook = new XSSFWorkbook(inputStream);
} else {
return null;
}
// 获取工作表 excel分为若干个表
Sheet sheet = workbook.getSheetAt(0);
if (sheet == null) {
return null;
}
// 获取表格中最后一行的行号
int lastRowNum = sheet.getLastRowNum();
// 最后一行的行号大于startRow
if (lastRowNum <= startRow) {
return null;
}
List<String[]> result = new ArrayList<String[]>();
// 循环读取
Row row = null;
Cell cell = null;
List<VehicleManage> list = new ArrayList<>();
for (int rowNum = startRow; rowNum <= lastRowNum; rowNum++) {
row = sheet.getRow(rowNum);
// 获取当前行的第一列和最后一列的标记
short firstCellNum = row.getFirstCellNum();
short lastCellNum = row.getLastCellNum();
//用来解析单元格中日期类型的数据
SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");
if (firstCellNum != lastCellNum && lastCellNum != 0) {
//构建一个VehicleManage对象用来封装excel表中的一行数据
VehicleManage vehicleManage = new VehicleManage();
//用来判断数据是否都为空，当count为8时说明当前行的数据都为空，无需添加到数据库
int count = 0;
//用来判断这一行数据是否存在唯一性冲突,若发生了冲突，无需添加到数据库，则直接跳出当前循环，进入下一行数据的循环
boolean flag = false;
for (int cellNum = firstCellNum; cellNum < lastCellNum; cellNum++) {
if (flag) {
break;
}
cell = row.getCell(cellNum);
switch (cellNum) {
case VehicleConstant.CELL_NUMBER_0 : {
if (cell == null) {
count ++;
continue;
}
String licensePlate = parseCell(cell);
if (!vehicleManageService.isLicensePlateUnique(licensePlate, null)) {
flag = true;
break;
}
vehicleManage.setLicensePlate(licensePlate);
break;
}
case VehicleConstant.CELL_NUMBER_1 : {
if (cell == null) {
count ++;
continue;
}
String frameNumber = parseCell(cell);
if (!vehicleManageService.isFrameNumberUnique(frameNumber, null)) {
flag = true;
break;
}
vehicleManage.setFrameNumber(frameNumber);
break;
}
case VehicleConstant.CELL_NUMBER_2 : {
if (cell == null) {
count ++;
continue;
}
String color = parseCell(cell);
vehicleManage.setColor(color);
break;
}
case VehicleConstant.CELL_NUMBER_3 : {
if (cell == null) {
count ++;
continue;
}
String brand = parseCell(cell);
vehicleManage.setBrand(brand);
break;
}
case VehicleConstant.CELL_NUMBER_4 : {
if (cell == null) {
count ++;
continue;
}
String name = parseCell(cell);
vehicleManage.setName(name);
break;
}
case VehicleConstant.CELL_NUMBER_5 : {
if (cell == null) {
count ++;
continue;
}
String phoneNumber = parseCell(cell);
vehicleManage.setPhoneNumber(phoneNumber);
break;
}
case VehicleConstant.CELL_NUMBER_6 : {
if (cell == null) {
count ++;
continue;
}
String expireTime = parseCell(cell);
Date insuranceExpireTime = format.parse(expireTime);
vehicleManage.setInsuranceExpireTime(insuranceExpireTime);
break;
}
case VehicleConstant.CELL_NUMBER_7 : {
if (cell == null) {
count ++;
continue;
}
String createTime = parseCell(cell);
Date createDate = format.parse(createTime);
vehicleManage.setCreateAt(createDate);
break;
}
}
}
if (flag == true) {
continue;
}
if (count == 8) {
continue;
}
list.add(vehicleManage);
}
}
for (VehicleManage vehicleManage : list) {
System.out.println(vehicleManage);
vehicleManageService.save(vehicleManage);
}
//return JsonResultFactory.createSimpleJsonResult(true, "文件导入成功");
return "redirect:list.do";
}
/*@SuppressWarnings("resource")
public static List<String[]> getParseExcel(InputStream inputStream, String suffix, int startRow)
throws IOException {
// 1.定义excel对象变量
Workbook workbook = null;
// 2.判断后缀 决定如何创建具体的对象
if ("xls".equals(suffix)) {
// 2003
workbook = new HSSFWorkbook(inputStream);
} else if ("xlsx".equals(suffix)) {
// 2007
workbook = new XSSFWorkbook(inputStream);
} else {
return null;
}
// 获取工作表 excel分为若干个表
Sheet sheet = workbook.getSheetAt(0);
if (sheet == null) {
return null;
}
// 获取表格中最后一行的行号
int lastRowNum = sheet.getLastRowNum();
// 最后一行的行号大于startRow
if (lastRowNum <= startRow) {
return null;
}
List<String[]> result = new ArrayList<String[]>();
// 循环读取
Row row = null;
Cell cell = null;
for (int rowNum = startRow; rowNum <= lastRowNum; rowNum++) {
row = sheet.getRow(rowNum);
// 获取当前行的第一列和最后一列的标记
short firstCellNum = row.getFirstCellNum();
short lastCellNum = row.getLastCellNum();
if (firstCellNum != lastCellNum && lastCellNum != 0) {
String[] rowArray = new String[lastCellNum];
//VehicleManage vehicleManage = new VehicleManage();
for (int cellNum = firstCellNum; cellNum < lastCellNum; cellNum++) {
cell = row.getCell(cellNum);
// 判断单元格是否有数据
if (cell == null) {
rowArray[cellNum] = null;
} else {
rowArray[cellNum] = parseCell(cell);
}
}
result.add(rowArray);
}
}
return result;
}*/
/**
* 解析单元格
*
* @param cell 需要解析的单元格数据
* @return 返回字符串
*/
@SuppressWarnings("deprecation")
private static String parseCell(Cell cell) {
String cellStr = null;
// 判断单元格的类型
switch (cell.getCellType()) {
// 字符串类型单元格
case Cell.CELL_TYPE_STRING:
cellStr = cell.getStringCellValue();
break;
// 空数据 标准数据
case Cell.CELL_TYPE_BLANK:
cellStr = "";
break;
// 数学类型 时间,日期,数字
case Cell.CELL_TYPE_NUMERIC:
// 判断日期类型
if (HSSFDateUtil.isCellDateFormatted(cell)) {
// 判断具体类型 是日期还是时间
SimpleDateFormat sdf = null;
if (cell.getCellStyle().getDataFormat() == HSSFDataFormat.getBuiltinFormat("h:mm")) {
// 这是时间
sdf = new SimpleDateFormat("HH:MM");
} else {
// 日期
sdf = new SimpleDateFormat("yyyy-MM-dd");
}
Date temp = cell.getDateCellValue();
cellStr = sdf.format(temp);
} else {
// 是数字
double temp = cell.getNumericCellValue();
// 数学格式化工具
DecimalFormat format = new DecimalFormat();
String formatString = cell.getCellStyle().getDataFormatString();
if ("General".equals(formatString)) {
// 定义格式化的正则 定义
format.applyPattern("#");
}
cellStr = format.format(temp);
}
break;
default:
cellStr = "";
}
return cellStr;
}
793
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
