教你如何使用JAVA POI

所需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;@Servicepublic 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:zphtemp員工信息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:zphtemp員工信息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:zphtemp// 多態 拋異常//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內容請搜索好吧啦網以前的文章或繼續瀏覽下面的相關文章希望大家以后多多支持好吧啦網！