java集成poi框架

介紹 : Apache POI是Apache軟件基金會的開放源碼函式庫，POI提供API給Java程序對Microsoft Office格式檔案讀和寫的功能。

下面簡單介紹一下如何使用該框架：

一：導入依賴

 <!--        excel解析依賴--><dependency><groupId>org.apache.poi</groupId><artifactId>poi</artifactId><version>${org.poi-version}</version></dependency><dependency><groupId>org.apache.poi</groupId><artifactId>poi-ooxml</artifactId><version>${org.poi-version}</version></dependency><dependency><groupId>org.apache.poi</groupId><artifactId>poi-examples</artifactId><version>${org.poi-version}</version></dependency><dependency><groupId>org.apache.poi</groupId><artifactId>poi-excelant</artifactId><version>${org.poi-version}</version></dependency><!-- https://mvnrepository.com/artifact/com.monitorjbl/xlsx-streamer --><dependency><groupId>com.monitorjbl</groupId><artifactId>xlsx-streamer</artifactId><version>2.1.0</version></dependency><dependency><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-test</artifactId></dependency><!--阿里巴巴EasyExcel依賴--><dependency><groupId>com.alibaba</groupId><artifactId>easyexcel</artifactId><version>3.0.5</version></dependency><dependency><groupId>org.springframework.boot</groupId><artifactId>spring-boot-configuration-processor</artifactId><optional>true</optional></dependency><dependency><groupId>junit</groupId><artifactId>junit</artifactId><scope>test</scope></dependency><dependency><groupId>cn.hutool</groupId><artifactId>hutool-all</artifactId><version>${hutool.version}</version></dependency><!--        <dependency>--><!--            <groupId>org.projectlombok</groupId>--><!--            <artifactId>lombok</artifactId>--><!--        </dependency>--><dependency><groupId>org.projectlombok</groupId><artifactId>lombok</artifactId></dependency><dependency><groupId>com.hynnet</groupId><artifactId>jxl</artifactId><version>2.6.12.1</version></dependency><!-- https://mvnrepository.com/artifact/com.bulucat/BeautyEye --><dependency><groupId>com.bulucat</groupId><artifactId>BeautyEye</artifactId><version>1.0.0</version></dependency><dependency><groupId>com.alibaba</groupId><artifactId>druid-spring-boot-starter</artifactId><version>1.1.21</version></dependency><dependency><groupId>javax.annotation</groupId><artifactId>javax.annotation-api</artifactId><version>1.3.2</version></dependency>

二：創建實體類及dao層（查詢得到結果集封裝在list中即可，此處不做過多贅述）

三：在serviceImpl中處理結果集

 @AutowiredDbToExcelDao dbToExcelDao;@Overridepublic List<String[]> getAllCity(QueryDto dto) {List<CityInfo> cityInfoList = dbToExcelDao.getAllCity(dto);List<String[]> result=new ArrayList<>();for (CityInfo cityInfo : cityInfoList) {String[] cityInfoArr = new String[7];//將查詢到的結果集轉換成字符串存入字符串數組中cityInfoArr[0] = xxx;cityInfoArr[1] = xxx;cityInfoArr[2] = xxx;cityInfoArr[3] = xxx;cityInfoArr[4] = xxx;//將該字符串數組存入list中result.add(cityInfoArr);}return result;}

四：在controller中調用service對象使用poi工具類導出表格。

import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.UUID;/***** 文件導入導出工具類***/
public class ExcelUtils {/*** 導出excel文件的方法** @param titles 輸出到excel的表頭* @param datas  數據庫的表中的數據 集合對象* @param out excel(File) 輸出到指定的路徑 (OutPutStream)*/public static void export(List<String[]> titles, List<List<String[]>> datas, OutputStream out) {// 創建工作表HSSFWorkbook work = new HSSFWorkbook();for (int k = 0;k<datas.size();k++){String sheetname = "";// 創建sheet表if(k == 0){if(datas.size() > 1){sheetname = "xxx";//sheet名}else {sheetname = "xxx";//sheet名}}else if(k == 1){sheetname = "xxx";//sheet名}else if(k == 2){sheetname = "xxx";//sheet名}HSSFSheet sheet = work.createSheet(sheetname);// 創建表頭【表頭數據來源】Row headRow = sheet.createRow(0);// 循環輸出標題for (int i = 0; i < titles.get(k).length; i++) {// 設置字體HSSFCellStyle redStyle = work.createCellStyle();
//           CellStyle redStyle = work.createCellStyle();HSSFFont redFont = work.createFont();//字體顏色redFont.setColor((short) 9);//設置字體大小redFont.setFontHeightInPoints((short) 10);//字體樣式redFont.setFontName("Microsoft YaHei");//加粗redFont.setBold(true);redStyle.setFont(redFont);//設置居中redStyle.setAlignment(HorizontalAlignment.CENTER);redStyle.setVerticalAlignment(VerticalAlignment.CENTER);//背景色redStyle.setFillForegroundColor(IndexedColors.BLACK.getIndex());redStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);// 上薄邊框redStyle.setBorderTop(BorderStyle.THIN);// 下薄邊框redStyle.setBorderBottom(BorderStyle.THIN);// 左薄邊框redStyle.setBorderLeft(BorderStyle.THIN);// 右薄邊框redStyle.setBorderRight(BorderStyle.THIN);// 下邊框：黑色redStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex());// 右邊框：黑色redStyle.setRightBorderColor(IndexedColors.BLACK.getIndex());// 左邊框：黑色redStyle.setLeftBorderColor(IndexedColors.BLACK.getIndex());// 上邊框：黑色redStyle.setTopBorderColor(IndexedColors.BLACK.getIndex());//創建cell,嵌入樣式Cell cell = headRow.createCell(i);cell.setCellStyle(redStyle);cell.setCellValue(titles.get(k)[i]); // 設置cell中的內容}// 表體內容// 設置表體內容 嵌套循環for (int row = 0; row < datas.get(k).size(); row++) {// 創建行HSSFRow sheetRow = sheet.createRow(row + 1);// 循環 創建列for (int c = 0; c < datas.get(k).get(row).length; c++) {sheet.autoSizeColumn(c, true);// 設置字體HSSFCellStyle redStyle = work.createCellStyle();
//           CellStyle redStyle = work.createCellStyle();HSSFFont redFont = work.createFont();//字體顏色redFont.setColor((short) 8);//設置字體大小redFont.setFontHeightInPoints((short) 10);//字體樣式redFont.setFontName("等線");//加粗
//                redFont.setBold(true);redStyle.setFont(redFont);//設置居中redStyle.setAlignment(HorizontalAlignment.CENTER);redStyle.setVerticalAlignment(VerticalAlignment.CENTER);//背景色
//                redStyle.setFillForegroundColor(IndexedColors.BLACK.getIndex());
//                redStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);// 上薄邊框redStyle.setBorderTop(BorderStyle.THIN);// 下薄邊框
//                    if(row == datas.size()){redStyle.setBorderBottom(BorderStyle.THIN);
//                    }// 左薄邊框redStyle.setBorderLeft(BorderStyle.THIN);// 右薄邊框
//                    if(c == datas.get(k).get(row).length-1){redStyle.setBorderRight(BorderStyle.THIN);
//                    }// 下邊框：黑色redStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex());// 右邊框：黑色redStyle.setRightBorderColor(IndexedColors.BLACK.getIndex());// 左邊框：黑色redStyle.setLeftBorderColor(IndexedColors.BLACK.getIndex());// 上邊框：黑色redStyle.setTopBorderColor(IndexedColors.BLACK.getIndex());HSSFCell sheetCell = sheetRow.createCell(c);sheetCell.setCellStyle(redStyle);// 設置內容sheetCell.setCellValue(datas.get(k).get(row)[c]);}}}// 創建完成后，內存創建的Excel輸出到文件中try {work.write(out);out.flush();out.close();} catch (IOException e) {e.printStackTrace();}}/***  導入excel文件的方法**  suffix: 傳入excel的后綴名*      excel  2003版的后綴為(xls) HSSFWorkbook  2007版的后綴為(xlsx)XSSFWorkbook*  fis :   通過輸入流來讀取excel的內容*  startRow: 讀取內容的起始行**  @return 返回導入的數據    List<String[]>**/public static List<String[]> importData(MultipartFile file, int startrow) {List<String[]> datas = new ArrayList<String[]>();// 創建表格對象Workbook work = null;try {//獲取文件流對象InputStream fis = file.getInputStream();//獲取文件的后綴String suffix = file.getOriginalFilename().substring(file.getOriginalFilename().lastIndexOf("."));// 判斷后綴名if (suffix.equals(".xls")) {work = new HSSFWorkbook(fis); // 2003的工作表對象} else if (suffix.equals(".xlsx")) { //work = new XSSFWorkbook(fis); // 2007的工作表對象} else {return null;}} catch (IOException e) {e.printStackTrace();}// 獲取sheet表格Sheet sheet = work.getSheetAt(0);if (sheet == null) { // 代表一個sheet表格也沒有return null;}// 獲取一共多少行int rownum = sheet.getLastRowNum();if (rownum == 0 || rownum+1 == startrow) {return null;}for (int i = startrow; i <= rownum; i++) {// 獲取行Row row = sheet.getRow(i);// 獲取列short first = row.getFirstCellNum(); //起始列的下標short num = row.getLastCellNum();    //終止列的下標String[] cols = new String[num];    //存放當前行所有的列內容for (int j = first; j < num; j++) {// 處理列對象Cell cell = row.getCell(j);
//                if(j == 10){
//                    String[] dates = row.getCell(7).toString().split("-");
//                    String year = dates[2];
//                    String  month = dates[1].substring(0,dates[1].length()-1);
//                    //獲取年份差
//                    int yx = Integer.parseInt(row.getCell(1).toString().substring(0,4) )- Integer.parseInt(year);
//                    //獲取月份差
//                    int mx = Integer.parseInt(row.getCell(1).toString().substring(4,6)) - Integer.parseInt(month);
//                    cols[j] = yx*12+mx+1 + "";
//                    continue;
//                }//獲取指定列的內容， 都轉換為String類型cols[j] = parseCell(cell);}datas.add(cols);  //列處理完成以后，把該行所有列添加集合中}return datas;}// 轉換類型private static String parseCell(Cell cell) {String cellValue = "";if(cell == null){return null;}//判斷如果是String類型 則去除空格if (cell.getCellTypeEnum() == CellType.STRING) {cellValue = cell.getStringCellValue().trim();} else if (cell.getCellTypeEnum() == CellType.BOOLEAN) { //如果是boolean類型則獲取boolean類型的值cellValue = String.valueOf(cell.getBooleanCellValue());} else if (cell.getCellTypeEnum() == CellType.NUMERIC) { //如果是數字類型， 則判斷是日期類型還是普通數字if (HSSFDateUtil.isCellDateFormatted(cell)) { // 判斷日期類型double d = cell.getNumericCellValue();Date date = HSSFDateUtil.getJavaDate(d);cellValue = new SimpleDateFormat("yyyy-MM-dd").format(date);} else { // 否cellValue = new DecimalFormat("#.######").format(cell.getNumericCellValue());}} else {cellValue = "";}return cellValue;}//圖片處理public static List<String> savePic(InputStream fis) throws Exception {HSSFWorkbook workbook = (HSSFWorkbook) WorkbookFactory.create(fis);List<HSSFPictureData> pictures = workbook.getAllPictures();HSSFSheet sheet = workbook.getSheetAt(0);List<String> picList = new ArrayList<>();for (HSSFShape shape : sheet.getDrawingPatriarch().getChildren()) {HSSFClientAnchor anchor = (HSSFClientAnchor) shape.getAnchor();if (shape instanceof HSSFPicture) {HSSFPicture pic = (HSSFPicture) shape;int row = anchor.getRow1();int pictureIndex = pic.getPictureIndex() - 1;HSSFPictureData picData = pictures.get(pictureIndex);//獲取文件后綴String ext = picData.suggestFileExtension();//獲取圖片內容byte[] data = picData.getData();//通過文件流將圖片寫入到指定的位置String filename = UUID.randomUUID().toString().replace("-", "");FileOutputStream out = new FileOutputStream("D:\\upload\\"+ filename +"."+ext);out.write(data);out.close();picList.add(filename+"."+ext);}}return picList;}
}

    @AutowiredDbToExcelService dbToExcelService;//導出結果表@RequestMapping("export")public void exportFileReconnect(HttpServletResponse res,QueryDto querydto) throws IOException {//定義添加標題String[] title1={"xx","xx","xx","xx","xx"};List<String[]> title = new ArrayList<>();title.add(title1);//定義添加內容,也可以存入多個list，作一個表中的不同sheetList<String[]> list1 = dbToExcelService.getAllReconnect(querydto);List<List<String[]>> list = new LinkedList<>();list.add(list1);//設置響應頭，告知瀏覽器是下載操作res.setHeader("Content-Disposition","attachment;filename=futongtongji.xls");//設置MIME類型res.setContentType("application/vnd.ms-excel");//這里要傳遞的字節流數據,excel為字節流文件ExcelUtils.export(title,list,res.getOutputStream());}@AutowiredExcelToDbService excelToDbService;//文件導入數據庫@RequestMapping("import")@ResponseBodypublic ResultData importFile(MultipartFile file) throws Exception {//獲取excel的數據List<String[]> list = ExcelUtils.importData(file, 1);//獲取excel中圖片的數據List<String> strings = ExcelUtils.savePic(file.getInputStream());for (int i = 0; i < list.size(); i++) {//將圖片數據渲染到excel表格中list.get(i)[1]= strings.get(i);}//上傳數據就是一個增加的過程ResultData resultData = excelToDbService.insertAll(list);return resultData;}