教你如何使用JAVA POI
一、導入jar包
所需jar包,在pom中添加如下坐標即可
<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>
註意:
操作Excel文件區分版本:
2003版本(包含2003)以前的擴展名為.xls需要用HSSFWorkbook類操作
2007版本(包含2007)以後的擴展名為.xlsx需要用XSSFWorkbook類操作
二、導出
2007版本(包含2007)以後的擴展名為.xlsx需要用XSSFWorkbook類操作
2003版本(包含2003)以前的擴展名為.xls需要用HSSFWorkbook類操作
和 07基本相似 就是把XSSFWorkbook換成HSSFWorkbook
後綴名改成 點xls
package com.zph.poi; import org.apache.poi.hssf.usermodel.HSSFCell; import org.apache.poi.hssf.usermodel.HSSFRow; import org.apache.poi.hssf.usermodel.HSSFSheet; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.xssf.usermodel.XSSFCell; import org.apache.poi.xssf.usermodel.XSSFRow; import org.apache.poi.xssf.usermodel.XSSFSheet; import org.apache.poi.xssf.usermodel.XSSFWorkbook; import org.springframework.core.io.ClassPathResource; import org.springframework.core.io.Resource; import org.springframework.stereotype.Service; import javax.servlet.ServletOutputStream; import javax.servlet.http.HttpServletRequest; import javax.servlet.http.HttpServletResponse; import java.io.File; import java.io.FileOutputStream; import java.io.IOException; import java.net.URI; import java.net.URLEncoder; @Service public class PoiServiceImpl { public void exportExcel2007() throws IOException { //創建工作簿 類似於創建Excel文件 XSSFWorkbook workbook=new XSSFWorkbook(); //創建 sheetname頁名 XSSFSheet sheet = workbook.createSheet("員工信息"); sheet.setColumnWidth(3,20*256);//給第3列設置為20個字的寬度 sheet.setColumnWidth(4,20*256);//給第4列設置為20個字的寬度 //創建一行,下標從0開始 XSSFRow row = sheet.createRow(0); //創建這行中的列,下標從0開始 (表頭) XSSFCell cell = row.createCell(0); // 給cell 0下表賦值 cell.setCellValue("姓名"); //創建這行中的列,並給該列直接賦值 row.createCell(1).setCellValue("年齡"); row.createCell(2).setCellValue("性別"); row.createCell(3).setCellValue("生日"); row.createCell(4).setCellValue("手機號"); // 設置表裡內容 row = sheet.createRow(1); row.createCell(0).setCellValue("T"); row.createCell(1).setCellValue("保密"); row.createCell(2).setCellValue("男"); row.createCell(3).setCellValue("保密"); row.createCell(4).setCellValue("12121212121"); row = sheet.createRow(2); row.createCell(0).setCellValue("T"); row.createCell(1).setCellValue("18"); row.createCell(2).setCellValue("女"); row.createCell(3).setCellValue("2000-01-01"); row.createCell(4).setCellValue("12121212122"); //設定 路徑 File file = new File("D:\\zph\\temp\\員工信息2007.xlsx"); FileOutputStream stream = new FileOutputStream(file); // 需要拋異常 workbook.write(stream); //關流 stream.close(); } public void exportExcel2003() throws IOException { //創建工作簿 類似於創建Excel文件 HSSFWorkbook workbook=new HSSFWorkbook(); //創建 sheetname頁名 HSSFSheet sheet = workbook.createSheet("員工信息"); //創建一行,下標從0開始 HSSFRow row = sheet.createRow(0); //創建這行中的列,下標從0開始 (表頭) HSSFCell cell = row.createCell(0); // 給cell 0下表賦值 cell.setCellValue("姓名"); //創建這行中的列,並給該列直接賦值 row.createCell(1).setCellValue("年齡"); row.createCell(2).setCellValue("性別"); row.createCell(3).setCellValue("生日"); row.createCell(4).setCellValue("手機號"); // 設置表裡內容 row = sheet.createRow(1); row.createCell(0).setCellValue("T"); row.createCell(1).setCellValue("保密"); row.createCell(2).setCellValue("男"); row.createCell(3).setCellValue("保密"); row.createCell(4).setCellValue("12121212121"); row = sheet.createRow(2); row.createCell(0).setCellValue("T"); row.createCell(1).setCellValue("18"); row.createCell(2).setCellValue("女"); row.createCell(3).setCellValue("2000-01-01"); row.createCell(4).setCellValue("12121212122"); //第一種導出 給定路徑 //1設定 路徑 創建文件讀進來在寫內容 File file = new File("D:\\zph\\temp\\員工信息2003.xls"); FileOutputStream stream = new FileOutputStream(file); // 需要拋異常 workbook.write(stream); //關流 stream.close(); } public void exportExcel2003(HttpServletRequest request, HttpServletResponse response) throws IOException { //第二種導出 從項目中獲取模板 //String realPath = request.getSession().getServletContext().getRealPath("/"); Resource resource = new ClassPathResource("templates/員工信息2003Tem.xls");//jar包獲取 //創建工作簿 類似於創建Excel文件 HSSFWorkbook workbookTem=new HSSFWorkbook(resource.getInputStream()); //創建 sheetname頁名 HSSFSheet sheetTem = workbookTem.getSheet("員工信息"); //HSSFSheet sheetTem = workbookTem.getSheetAt(0); HSSFRow rowTem = sheetTem.createRow(1); rowTem.createCell(0).setCellValue("xmtem"); rowTem.createCell(1).setCellValue("nltem"); rowTem.createCell(2).setCellValue("xbtem"); rowTem.createCell(3).setCellValue("srtem"); rowTem.createCell(4).setCellValue("sjhtem"); ServletOutputStream outputStream = response.getOutputStream(); response.reset(); String fileName = URLEncoder.encode("員工信息TemOut.xls", "utf-8"); response.setHeader("Content-disposition","attachment;filename="+fileName); response.setContentType("application/x-download;charset=UTF-8"); // 對響應客戶請求進行重新編碼11 //response.setCharacterEncoding("utf-8"); workbookTem.write(outputStream); outputStream.close(); } public String exportExcel2003(String s,HttpServletRequest request, HttpServletResponse response) throws IOException { //第三種直接導出 //創建工作簿 類似於創建Excel文件 HSSFWorkbook workbookTem=new HSSFWorkbook(); //創建 sheetname頁名 HSSFSheet sheet = workbookTem.createSheet("員工信息"); //創建一行,下標從0開始 HSSFRow row = sheet.createRow(0); //創建這行中的列,下標從0開始 (表頭) HSSFCell cell = row.createCell(0); // 給cell 0下表賦值 cell.setCellValue("姓名"); //創建這行中的列,並給該列直接賦值 row.createCell(1).setCellValue("年齡"); row.createCell(2).setCellValue("性別"); row.createCell(3).setCellValue("生日"); row.createCell(4).setCellValue("手機號"); // 設置表裡內容 row = sheet.createRow(1); row.createCell(0).setCellValue("T"); row.createCell(1).setCellValue("保密"); row.createCell(2).setCellValue("男"); row.createCell(3).setCellValue("保密"); row.createCell(4).setCellValue("12121212121"); row = sheet.createRow(2); row.createCell(0).setCellValue("T"); row.createCell(1).setCellValue("18"); row.createCell(2).setCellValue("女"); row.createCell(3).setCellValue("2000-01-01"); row.createCell(4).setCellValue("12121212122"); ServletOutputStream outputStream = response.getOutputStream(); response.reset(); String fileName = URLEncoder.encode("員工信息TemOut.xls", "utf-8"); response.setHeader("Content-disposition","attachment;filename="+fileName); //response.setContentType("application/x-download;charset=UTF-8"); response.setContentType("application/vnd.ms-excel"); //response.setContentType("application/msexcel"); // 對響應客戶請求進行重新編碼11 //response.setCharacterEncoding("utf-8"); workbookTem.write(outputStream); outputStream.close(); return s; } }
三、導出
@RequestMapping(value="/upload") public String uploadExcel(@RequestParam("fileData") MultipartFile file, HttpServletRequest request, HttpServletResponse response) throws Exception { InputStream in = file.getInputStream(); String s = poiService.uploadExcel(file, request, response); return s; } public String uploadExcel(MultipartFile file, HttpServletRequest request, HttpServletResponse response) throws Exception { InputStream in = file.getInputStream(); //D:\zph\temp // 多態 拋異常 //Workbook sheets = new XSSFWorkbook(stream); HSSFWorkbook sheets = new HSSFWorkbook(in); //獲取一個工作表(sheet頁),下標從0開始 HSSFSheet sheet = sheets.getSheetAt(0); for (int i = 1; i<=sheet.getLastRowNum() ; i++) { // 獲取行數 Row row = sheet.getRow(i); // 獲取單元格 取值 String value1 = row.getCell(0).getStringCellValue(); String value2 = row.getCell(1).getStringCellValue(); String value3 = row.getCell(2).getStringCellValue(); String value4 = row.getCell(3).getStringCellValue(); String value5= row.getCell(4).getStringCellValue(); System.out.println(value1); System.out.println(value2); System.out.println(value3); System.out.println(value4); System.out.println(value5); } //關流 sheets.close(); in.close(); return "hha"; }
postman 設置 post請求 請求頭 Content-Type multipart/form-data
到此這篇關於教你如何使用JAVA POI的文章就介紹到這瞭,更多相關JAVA POI內容請搜索WalkonNet以前的文章或繼續瀏覽下面的相關文章希望大傢以後多多支持WalkonNet!
推薦閱讀:
- Apache POI的基本使用詳解
- 基於Java實現Json文件轉換為Excel文件
- C#通過NPOI導入導出數據EXCEL
- Java實現超大Excel文件解析(XSSF,SXSSF,easyExcel)
- java導出excel 瀏覽器直接下載或者或以文件形式導出