首先添加依賴。
<!--文件上傳組件-->
<dependency>
<groupId>commons-fileupload</groupId>
<artifactId>commons-fileupload</artifactId>
<version>1.3.1</version>
</dependency>
<dependency>
<groupId>commons-io</groupId>
<artifactId>commons-io</artifactId>
<version>2.5</version>
</dependency>
<!--讀取excel文件-->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.17</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.17</version>
</dependency>
然后寫數(shù)據(jù)庫對應的實體類
然后mapper層，有一個查詢方法辕万，有一個添加方法。
然后是mapper映射文件铝阐，業(yè)務層。這里就不列舉了脚翘。
重點說一下controller。
@Controller
public class SaleController {
@Autowired
? ? private SaleServiceImpltt;
//這一個方法是查詢出數(shù)據(jù)顯示到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++) {
//這個循環(huán)是這個表格有多少行绍哎，就循環(huán)多少次来农，每次循環(huán)都會獲得除了id那一列的每一列數(shù)據(jù)（id為遞增，不需要添加）崇堰，因為我的數(shù)據(jù)全都是int類型沃于，但是獲取的是double類型，所以我把它轉(zhuǎn)化為了int類型海诲。row.getcell()是獲取列,?two.getNumericCellValue()是獲取這個列的這個數(shù)據(jù),然后調(diào)用insert（）方法繁莹，最后插入進數(shù)據(jù)庫。
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("統(tǒng)計表");
createTitle(workbook,sheet);
//userService.getAll();
//而導出方法首先要查詢出來數(shù)據(jù)庫里面的數(shù)據(jù)饿肺，存到rows中蒋困，然后設置生成excel表的格式，把數(shù)據(jù)庫查詢出來的數(shù)據(jù)插入進去敬辣。
? ? ? 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"));
//新增數(shù)據(jù)行，并且設置單元格數(shù)據(jù)
? ? ? ? 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";
}
//創(chuàng)建表頭
? ? private void createTitle(HSSFWorkbook workbook,HSSFSheet sheet){
HSSFRow row = sheet.createRow(0);
//設置列寬零院，setColumnWidth的第二個參數(shù)要乘以256溉跃，這個參數(shù)的單位是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>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>Title</title>
</head>
<body>
<table border="1">
<tr>
<th>用戶名</th>
<th>價格</th>
<th>數(shù)量</th>
<th>總價</th>
<th>銷售者id</th>
<th>銷售產(chǎn)品id</th>
</tr>
<#list list as item>
? ? <tr >
<td >${item.id}</td>
<td >${item.price}</td>
<td >${item.quantity}</td>
<td >${item.totalPrice}</td>
<td >${item.userId}</td>
<td >${item.productId}</td>
</tr>
</table>
<button onclick="location.href='http://localhost:8080//excel/getUser';">導出</button>
<h1>這是用戶信息頁</h1>
//這個from表單就是選擇excel文件，上傳到excel/upload方法里面告抄。
<form action="excel/upload" method="post" enctype="multipart/form-data">
<input name="file" type="file" />
<input type="submit"/>
</form>
</body>
</html>
