课程建设类教学成果奖网站,wordpress 评论回复插件,如何开 网站建设公司,绍兴网站建设服务介绍 : Apache POI是Apache软件基金会的开放源码函式库#xff0c;POI提供API给Java程序对Microsoft Office格式档案读和写的功能。
下面简单介绍一下如何使用该框架#xff1a;
一#xff1a;导入依赖 !-- excel解析依赖--dependencygroupIdPOI提供API给Java程序对Microsoft Office格式档案读和写的功能。
下面简单介绍一下如何使用该框架
一导入依赖 !-- excel解析依赖--dependencygroupIdorg.apache.poi/groupIdartifactIdpoi/artifactIdversion${org.poi-version}/version/dependencydependencygroupIdorg.apache.poi/groupIdartifactIdpoi-ooxml/artifactIdversion${org.poi-version}/version/dependencydependencygroupIdorg.apache.poi/groupIdartifactIdpoi-examples/artifactIdversion${org.poi-version}/version/dependencydependencygroupIdorg.apache.poi/groupIdartifactIdpoi-excelant/artifactIdversion${org.poi-version}/version/dependency!-- https://mvnrepository.com/artifact/com.monitorjbl/xlsx-streamer --dependencygroupIdcom.monitorjbl/groupIdartifactIdxlsx-streamer/artifactIdversion2.1.0/version/dependencydependencygroupIdorg.springframework.boot/groupIdartifactIdspring-boot-starter-test/artifactId/dependency!--阿里巴巴EasyExcel依赖--dependencygroupIdcom.alibaba/groupIdartifactIdeasyexcel/artifactIdversion3.0.5/version/dependencydependencygroupIdorg.springframework.boot/groupIdartifactIdspring-boot-configuration-processor/artifactIdoptionaltrue/optional/dependencydependencygroupIdjunit/groupIdartifactIdjunit/artifactIdscopetest/scope/dependencydependencygroupIdcn.hutool/groupIdartifactIdhutool-all/artifactIdversion${hutool.version}/version/dependency!-- dependency--!-- groupIdorg.projectlombok/groupId--!-- artifactIdlombok/artifactId--!-- /dependency--dependencygroupIdorg.projectlombok/groupIdartifactIdlombok/artifactId/dependencydependencygroupIdcom.hynnet/groupIdartifactIdjxl/artifactIdversion2.6.12.1/version/dependency!-- https://mvnrepository.com/artifact/com.bulucat/BeautyEye --dependencygroupIdcom.bulucat/groupIdartifactIdBeautyEye/artifactIdversion1.0.0/version/dependencydependencygroupIdcom.alibaba/groupIdartifactIddruid-spring-boot-starter/artifactIdversion1.1.21/version/dependencydependencygroupIdjavax.annotation/groupIdartifactIdjavax.annotation-api/artifactIdversion1.3.2/version/dependency
二创建实体类及dao层查询得到结果集封装在list中即可此处不做过多赘述
三在serviceImpl中处理结果集 AutowiredDbToExcelDao dbToExcelDao;Overridepublic ListString[] getAllCity(QueryDto dto) {ListCityInfo cityInfoList dbToExcelDao.getAllCity(dto);ListString[] resultnew 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(ListString[] titles, ListListString[] datas, OutputStream out) {// 创建工作表HSSFWorkbook work new HSSFWorkbook();for (int k 0;kdatas.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 返回导入的数据 ListString[]**/public static ListString[] importData(MultipartFile file, int startrow) {ListString[] datas new ArrayListString[]();// 创建表格对象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 || rownum1 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*12mx1 ;
// 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 ListString savePic(InputStream fis) throws Exception {HSSFWorkbook workbook (HSSFWorkbook) WorkbookFactory.create(fis);ListHSSFPictureData pictures workbook.getAllPictures();HSSFSheet sheet workbook.getSheetAt(0);ListString 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};ListString[] title new ArrayList();title.add(title1);//定义添加内容,也可以存入多个list作一个表中的不同sheetListString[] list1 dbToExcelService.getAllReconnect(querydto);ListListString[] list new LinkedList();list.add(list1);//设置响应头告知浏览器是下载操作res.setHeader(Content-Disposition,attachment;filenamefutongtongji.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的数据ListString[] list ExcelUtils.importData(file, 1);//获取excel中图片的数据ListString 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;}
