SpringBoot导入导出Excel简单小例子

首先添加依赖。

<groupId>commons-fileupload</groupId>

<artifactId>commons-fileupload</artifactId>

</dependency>

<groupId>commons-io</groupId>

<artifactId>commons-io</artifactId>

</dependency>

<groupId>org.apache.poi</groupId>

</dependency>

<groupId>org.apache.poi</groupId>

<artifactId>poi-ooxml</artifactId>

</dependency>

然后写数据库对应的实体类

然后mapper层，有一个查询方法，有一个添加方法。

然后是mapper映射文件，业务层。这里就不列举了。

重点说一下controller。

@Controller

public class SaleController {

@Autowired

private SaleServiceImpltt;

//这一个方法是查询出数据显示到freemark页面

@RequestMapping("/index")

public String select(Model model, Integer id, Integer price, Integer quantity, Integer totalPrice,Integer userId, Integer productId){

List list=tt.select(id,price,quantity,totalPrice,userId,productId);

model.addAttribute("list",list);

return "index";

}

//这是个是导入文件

@RequestMapping("/excel/upload")

public String fileUpload(@RequestParam("file") MultipartFile file,Integer price,Integer quantity,Integer totalPrice,Integer userId,Integer productId)throws IOException {

long size=file.getSize();

HSSFWorkbook workbook =new HSSFWorkbook(new POIFSFileSystem(file.getInputStream()));

//获得有多少sheet

int numberOfSheets = workbook.getNumberOfSheets();

//默认只有一个sheet

HSSFSheet sheet = workbook.getSheetAt(0);

//获得sheet有多少行

int rows = sheet.getPhysicalNumberOfRows();

//遍历行

for (int j =0; j < rows; j++) {

if (j ==0) {

continue;//标题行(省略)

}

for (int k =0; k <1; k++) {

//这个循环是这个表格有多少行，就循环多少次，每次循环都会获得除了id那一列的每一列数据（id为递增，不需要添加），因为我的数据全都是int类型，但是获取的是double类型，所以我把它转化为了int类型。row.getcell()是获取列, two.getNumericCellValue()是获取这个列的这个数据,然后调用insert（）方法，最后插入进数据库。

HSSFRow row = sheet.getRow(j);

HSSFCell two = row.getCell(1);

HSSFCell three = row.getCell(2);

HSSFCell four = row.getCell(3);

HSSFCell five = row.getCell(4);

HSSFCell six = row.getCell(5);

double twoa = two.getNumericCellValue();

int twob = (int) twoa;

double threea = three.getNumericCellValue();

int threeb = (int) threea;

double foura = four.getNumericCellValue();

int fourb = (int) foura;

double fivea = five.getNumericCellValue();

int fiveb = (int) fivea;

double sixa = six.getNumericCellValue();

int sixb = (int) sixa;

tt.insert(twob, threeb, fourb, fiveb, sixb);

}

return "poi";

}

//生成user表excel

@GetMapping(value ="/excel/getUser")

@ResponseBody

public String getUser(HttpServletResponse response,Integer id,Integer price,Integer quantity,Integer totalPrice,Integer userId,Integer productId)throws Exception{

HSSFWorkbook workbook =new HSSFWorkbook();

HSSFSheet sheet = workbook.createSheet("统计表");

createTitle(workbook,sheet);

//userService.getAll();

//而导出方法首先要查询出来数据库里面的数据，存到rows中，然后设置生成excel表的格式，把数据库查询出来的数据插入进去。

List rows=tt.select(id,price,quantity,totalPrice,userId,productId);

System.out.print("aaaaaaaaaaaa"+rows);

HSSFCellStyle style = workbook.createCellStyle();

style.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy h:mm"));

//新增数据行，并且设置单元格数据

int rowNum=1;

for(Sale user:rows){

HSSFRow row = sheet.createRow(rowNum);

row.createCell(0).setCellValue(user.getId());

row.createCell(1).setCellValue(user.getPrice());

row.createCell(2).setCellValue(user.getProductId());

row.createCell(3).setCellValue(user.getQuantity());

row.createCell(4).setCellValue(user.getUserId());

rowNum++;

}

String fileName ="导出excel例子.xls";

//生成excel文件

buildExcelFile(fileName, workbook);

//浏览器下载excel

buildExcelDocument(fileName,workbook,response);

return "Poi";

}

//创建表头

private void createTitle(HSSFWorkbook workbook,HSSFSheet sheet){

HSSFRow row = sheet.createRow(0);

//设置列宽，setColumnWidth的第二个参数要乘以256，这个参数的单位是1/256个字符宽度

sheet.setColumnWidth(1,12*256);

sheet.setColumnWidth(3,17*256);

//设置为居中加粗

HSSFCellStyle style = workbook.createCellStyle();

HSSFFont font = workbook.createFont();

font.setBold(true);

//style.setAlignment(HSSFCellStyle.ALIGN_CENTER);

style.setFont(font);

HSSFCell cell;

cell = row.createCell(0);

cell.setCellValue("id");

cell.setCellStyle(style);

cell = row.createCell(1);

cell.setCellValue("price");

cell.setCellStyle(style);

cell = row.createCell(2);

cell.setCellValue("quantity");

cell.setCellStyle(style);

cell = row.createCell(3);

cell.setCellValue("totalprice");

cell = row.createCell(4);

cell.setCellValue("userId");

cell = row.createCell(5);

cell.setCellValue("productId");

cell.setCellStyle(style);

}

//生成excel文件

protected void buildExcelFile(String filename,HSSFWorkbook workbook)throws Exception{

FileOutputStream fos =new FileOutputStream(filename);

workbook.write(fos);

fos.flush();

fos.close();

}

//浏览器下载excel

protected void buildExcelDocument(String filename,HSSFWorkbook workbook,HttpServletResponse response)throws Exception{

response.setContentType("application/vnd.ms-excel");

response.setHeader("Content-Disposition","attachment;filename="+ URLEncoder.encode(filename,"utf-8"));

OutputStream outputStream = response.getOutputStream();

workbook.write(outputStream);

outputStream.flush();

outputStream.close();

}

然后最后写ftl页面

<!DOCTYPE html>

<head>

<title>Title</title>

</head>

<body>

<tr>

<th>销售者id</th>

<th>销售产品id</th>

</tr>

<#list list as item>

<tr >

<td >${item.price}</td>

<td >${item.quantity}</td>

<td >${item.totalPrice}</td>

<td >${item.userId}</td>

<td >${item.productId}</td>

</tr>

</table>

<h1>这是用户信息页</h1>

//这个from表单就是选择excel文件，上传到excel/upload方法里面。

</form>

</body>

</html>

SpringBoot导入导出Excel简单小例子

推荐阅读更多精彩内容