java之poi操作execel表

浅浅的花香味﹌ 2023-06-14 14:59 8阅读 0赞

## 首先引入pom ##

<dependency>
                <groupId>org.apache.poi</groupId>
                <artifactId>poi</artifactId>
                <version>4.0.1</version>
            </dependency>
            <dependency>
                <groupId>org.apache.poi</groupId>
                <artifactId>poi-ooxml</artifactId>
                <version>4.0.1</version>
            </dependency>
            <dependency>
                <groupId>org.apache.poi</groupId>
                <artifactId>poi-ooxml-schemas</artifactId>
                <version>4.0.1</version>
            </dependency>

## 写execel 和 读 execel 简单的例子 ##

import org.apache.poi.ss.usermodel.*;
    import org.apache.poi.xssf.usermodel.XSSFWorkbook;
    import org.junit.Test;
    
    import java.io.File;
    import java.io.FileOutputStream;
    import java.io.IOException;
    import java.text.SimpleDateFormat;
    
    public class DemoPoi { 
    	//cell转化任意类型
        private static Object getCellValue(Cell cell) { 
            Object o = null;
            //获得单元格的类型
            CellType cellType = cell.getCellType();
            switch (cellType){ 
                case STRING:
                    o=cell.getStringCellValue();//字符串数据
                    break;
                case NUMERIC: //数字类型 , 在excel中日期类型是数字类型
                    //判断是日期类型 还是数字类型
                    if(DateUtil.isCellDateFormatted(cell)){  //是不是日期类型
                        //是日期类型
                        SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd");
                        o = simpleDateFormat.format(cell.getDateCellValue());
                    }else{ 
                        //数字类型
                        o=cell.getNumericCellValue();
                    }
                    break;
                case BOOLEAN:
                    o=cell.getBooleanCellValue();//获得布尔类型数据
                    break;
                default:
                    break;
            }
    
            return o;
        }
    
    
        @Test
        public void ReadExecel() throws IOException { 
            Workbook workbook= new XSSFWorkbook("D:/demo.xlsx");
            //3.获得表
            Sheet sheet = workbook.getSheetAt(0);
            int rows = sheet.getLastRowNum();
            /*遍历行数*/
            for (int i = 0; i < rows; i++) { 
                short lastCellNum = sheet.getRow(i).getLastCellNum();
                for (int j = 0; j < lastCellNum ; j++) { 
                    Cell cell = sheet.getRow(i).getCell(j);
    
                    if (cell != null) { 
                        if (j == lastCellNum - 1) { 
                            Object cellValue = getCellValue(cell);
                            System.out.println(cellValue);
                        } else { 
                            Object cellValue = getCellValue(cell);
                            System.out.print(cellValue+" ");
                        }
                    }
    
    
    
                }
            }
        }
    
    
        /** * 写execel * 1.有excel的对象 workBook * 2.有sheet表 * 3.有行 * 4.有单元格(列) * 5.赋值数据 * 6.设置样式 * 7.下载(保存到本地) * @param args */
        public static void main(String[] args) throws IOException { 
            Workbook workbook = new XSSFWorkbook();
            Sheet sheet = workbook.createSheet();
            Row row = sheet.createRow(1);
            //row.setHeight((short) (50*20));
            //设置行高
            row.setHeightInPoints(50);
            Cell cell = row.createCell(1);
            //设置列宽
            sheet.setColumnWidth(1,20*256);
    
    
    
            cell.setCellValue("加油!大智");
    
            //设置样式
            CellStyle cellStyle = workbook.createCellStyle();
            //单元格周边加线条
            cellStyle.setBorderBottom(BorderStyle.THIN);
            cellStyle.setBorderLeft(BorderStyle.THIN);
            cellStyle.setBorderTop(BorderStyle.THIN);
            cellStyle.setBorderRight(BorderStyle.THIN);
            cell.setCellStyle(cellStyle);
    
            //设置上下,左右居中
            cellStyle.setAlignment(HorizontalAlignment.CENTER);
            cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
            cell.setCellStyle(cellStyle);
    
            //设置字体
            Font font = workbook.createFont();
            font.setBold(true);
            font.setFontHeightInPoints((short) 20);
            //设置颜色
            font.setColor((short) 3);
            font.setFontName("楷体");
    
            cellStyle.setFont(font);
            cell.setCellStyle(cellStyle);
    
            /*下载保存*/
            FileOutputStream fileOutputStream = new FileOutputStream(new File("D://demo2112.xlsx"));
            workbook.write(fileOutputStream);
            fileOutputStream.close();
        }
    }

## 上传和下载execel ##

#### 1.上传execel ####

@RequestMapping(value = "import",name = "上传货物")
        public  String importExecel(String contractId,MultipartFile file) throws IOException { 
    //创建货物集合
            List<ContractProduct> list =new ArrayList<ContractProduct>();
            //获取数据
            if (file != null) { 
                //加载文件
                XSSFWorkbook workbook = new XSSFWorkbook(file.getInputStream());
                XSSFSheet sheet = workbook.getSheetAt(0);
                int lastRowNum = sheet.getLastRowNum();
                Row row = null;
                Cell cell = null;
                for (int i = 1; i <= lastRowNum; i++) { 
                    row = sheet.getRow(i);
                    short lastCellNum = row.getLastCellNum();
                    Object[] objects = new Object[lastCellNum];
                    for (int j = 0; j < lastCellNum; j++) { 
                        cell = row.getCell(j);
                        if (cell != null) { 
                      	  //调用上传工具类
                            Object cellValue = uploadFile.getCellValue(cell);
                            objects[j] = cellValue;
                        }
                    }
                    System.out.println(Arrays.toString(objects));
                    //调用自定义构造
                    ContractProduct contractProduct = new ContractProduct(objects, user.getCompanyId(), user.getCompanyName(), contractId);
                    list.add(contractProduct);
                    //循环结束
                }
                contractProductService.saveList(list);
            } else { 
                System.out.println("没有上传文件");
            }
            ///WEB-INF/pages/cargo/contractProduct/import.jsp
            return "redirect:/cargo/contractProduct/toImport.do?contractId="+contractId;
        }

**上传重点:**

//看构造
    ContractProduct contractProduct = new ContractProduct(objects, user.getCompanyId(), user.getCompanyName(), contractId);
    
    //就这
    public ContractProduct(Object []objs, String companyId, String companyName,String contractId) { 
    		this.factoryName = objs[1].toString();
    		this.productNo = objs[2].toString();
    		this.cnumber = ((Double) objs[3]).intValue();
    		this.packingUnit = objs[4].toString();
    		this.loadingRate = objs[5].toString();
    		this.boxNum = ((Double) objs[6]).intValue();
    		this.price = (Double) objs[7];			//单价
    		this.productRequest=objs[8].toString();
    		this.productDesc=objs[9].toString();
    		this.companyId = companyId;
    		this.companyName = companyName;
    		this.contractId = contractId;
    	}

**1.1 上传工具类 转换cell各种类型为object**

//调用上传工具类
    Object cellValue = uploadFile.getCellValue(cell);
    
    //详情
        public static Object getCellValue(Cell cell) { 
            Object o = null;
            //获得单元格的类型
            CellType cellType = cell.getCellType();
            switch (cellType){ 
                case STRING:
                    o=cell.getStringCellValue();//字符串数据
                    break;
                case NUMERIC: //数字类型 , 在excel中日期类型是数字类型
                    //判断是日期类型 还是数字类型
                    if(DateUtil.isCellDateFormatted(cell)){  //是不是日期类型
                        //是日期类型
                        SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd");
                        o = simpleDateFormat.format(cell.getDateCellValue());
                    }else{ 
                        //数字类型
                        o=cell.getNumericCellValue();
                    }
                    break;
                case BOOLEAN:
                    o=cell.getBooleanCellValue();//获得布尔类型数据
                    break;
                default:
                    break;
            }
    
            return o;
        }

#### 2.下载execel: ####

@RequestMapping(value = "printExcel",name = "打印出货表")
        public  String printExcel(String  inputDate) throws IOException { 
            //获取合同数据
            List<ContractProductVo> list=contractService.findByShipTime(inputDate,user.getCompanyId());
            System.out.println(list);
            //初始化值
            Row row = null;
            Cell cell = null;
    
            //创建工作簿
            Workbook  workbook = new SXSSFWorkbook();
            //创建表
            Sheet sheet = workbook.createSheet();
            //创建大标题
            row = sheet.createRow(0);
           //创建列
            cell= row.createCell(1);
            //合并单元格 从0行开始0行结束 从第一列 到 第8列结束
            sheet.addMergedRegion(new CellRangeAddress(0,0,1,8));
    
            //2019年2月 设置标题
            cell.setCellValue(inputDate.replace("-","年")+"月出货表");
            //设置样式
            CellStyle cellStyle = bigTitle(workbook);
            cell.setCellStyle(cellStyle);
            //设置宽高
            sheet.setDefaultRowHeightInPoints(36);
            sheet.setDefaultRowHeight((short)(36*20));
            sheet.setColumnWidth(1, 26 * 256);
            sheet.setColumnWidth(2, 11 * 256);
            sheet.setColumnWidth(3, 30 * 256);
            sheet.setColumnWidth(4, 12 * 256);
            sheet.setColumnWidth(5, 15 * 256);
            sheet.setColumnWidth(6, 15 * 256);
            sheet.setColumnWidth(7, 11 * 256);
            sheet.setColumnWidth(8, 11 * 256);
            sheet.setColumnWidth(9, 11 * 256);
            //第二行 小标题 给每列赋值
            Object[] objects = { "","客人", "订单号", "货号", "数量", "工厂", "工厂交期", "船期", "贸易条款"};
            row = sheet.createRow(1);
            CellStyle title = title(workbook);
            for (int i = 0; i <= 8 ; i++) { 
                cell = row.createCell(i);
                cell.setCellValue((String)objects[i]);
                cell.setCellStyle(title);
            }
    
            //循环数据 遍历输出
            for (int i = 1; i < list.size(); i++) { 
                //创建第三行 数据开始的第一行
                row = sheet.createRow(i+1);
                //获取第一个数据对象
                ContractProductVo contractProductVo = list.get(i-1);
                //一行 8个单元格赋值
                for (int j = 1; j <= 8 ; j++) { 
                    cell = row.createCell(j);
                    switch (j) { 
                        case 1:
                            cell.setCellValue(contractProductVo.getCustomName());
                            break;
                        case 2:
                            cell.setCellValue(contractProductVo.getContractNo());
                            break;
                        case 3:
                            cell.setCellValue(contractProductVo.getProductNo());
                            break;
                        case 4:
                            cell.setCellValue(contractProductVo.getCnumber());
                            break;
                        case 5:
                            cell.setCellValue(contractProductVo.getFactoryName());
                            break;
                        case 6:
                            cell.setCellValue(contractProductVo.getDeliveryPeriod());
                            break;
                        case 7:
                            cell.setCellValue(contractProductVo.getShipTime());
                            break;
                        case 8:
                            cell.setCellValue(contractProductVo.getTradeTerms());
                            break;
                            default:
                                break;
                    }
                }
            }
            //文件写回 浏览器
            DownloadUtil downloadUtil = new DownloadUtil();
            ByteArrayOutputStream byteArrayOutputStream = new ByteArrayOutputStream();
            workbook.write(byteArrayOutputStream);
            downloadUtil.download(byteArrayOutputStream,response,"出货表.xlsx");
            return "cargo/print/contract-print";
        }

**下载的工具类:**

package com.czh.common.utils;
    
    import javax.servlet.ServletOutputStream;
    import javax.servlet.http.HttpServletResponse;
    import java.io.ByteArrayOutputStream;
    import java.io.File;
    import java.io.FileInputStream;
    import java.io.IOException;
    
    public class DownloadUtil { 
    	
    	/** * @param filePath 要下载的文件路径 * @param returnName 返回的文件名 * @param response HttpServletResponse * @param delFlag 是否删除文件 */
    	protected void download(String filePath,String returnName,HttpServletResponse response,boolean delFlag){ 
    		this.prototypeDownload(new File(filePath), returnName, response, delFlag);
    	}
    
    
    	/** * @param file 要下载的文件 * @param returnName 返回的文件名 * @param response HttpServletResponse * @param delFlag 是否删除文件 */
    	protected void download(File file,String returnName,HttpServletResponse response,boolean delFlag){ 
    		this.prototypeDownload(file, returnName, response, delFlag);
    	}
    	
    	/** * @param file 要下载的文件 * @param returnName 返回的文件名 * @param response HttpServletResponse * @param delFlag 是否删除文件 */
    	public void prototypeDownload(File file,String returnName,HttpServletResponse response,boolean delFlag){ 
    		// 下载文件
    		FileInputStream inputStream = null;
    		ServletOutputStream outputStream = null;
    		try { 
    			if(!file.exists()) return;
    			response.reset();
    			//设置响应类型 PDF文件为"application/pdf"，WORD文件为："application/msword"， EXCEL文件为："application/vnd.ms-excel"。 
    			response.setContentType("application/octet-stream;charset=utf-8");
    
    			//设置响应的文件名称,并转换成中文编码
    			//returnName = URLEncoder.encode(returnName,"UTF-8");
    			returnName = response.encodeURL(new String(returnName.getBytes(),"iso8859-1"));	//保存的文件名,必须和页面编码一致,否则乱码
    			
    			//attachment作为附件下载；inline客户端机器有安装匹配程序，则直接打开；注意改变配置，清除缓存，否则可能不能看到效果
    			response.addHeader("Content-Disposition",   "attachment;filename="+returnName);  
    			
    			//将文件读入响应流
    			inputStream = new FileInputStream(file);
    			outputStream = response.getOutputStream();
    			int length = 1024;
    			int readLength=0;
    			byte buf[] = new byte[1024];
    			readLength = inputStream.read(buf, 0, length);
    			while (readLength != -1) { 
    				outputStream.write(buf, 0, readLength);
    				readLength = inputStream.read(buf, 0, length);
    			}
    		} catch (Exception e) { 
    			e.printStackTrace();
    		} finally { 
    			try { 
    				outputStream.flush();
    			} catch (IOException e) { 
    				e.printStackTrace();
    			}
    			try { 
    				outputStream.close();
    			} catch (IOException e) { 
    				e.printStackTrace();
    			}
    			try { 
    				inputStream.close();
    			} catch (IOException e) { 
    				e.printStackTrace();
    			}
    			//删除原文件
    			
    			if(delFlag) { 				
    				file.delete();
    			}
    		}
    	}
    
    	/** * @param byteArrayOutputStream 将文件内容写入ByteArrayOutputStream * @param response HttpServletResponse 写入response * @param returnName 返回的文件名 */
    	public void download(ByteArrayOutputStream byteArrayOutputStream, HttpServletResponse response, String returnName) throws IOException{ 
    		response.setContentType("application/octet-stream;charset=utf-8");
    		//保存的文件名,必须和页面编码一致,否则乱码
    		returnName = response.encodeURL(new String(returnName.getBytes(),"iso8859-1"));
    		System.out.println("returnName : "+returnName);
    
    		response.addHeader("Content-Disposition",   "attachment;filename=" + returnName);  
    		response.setContentLength(byteArrayOutputStream.size());
    		
    		ServletOutputStream outputstream = response.getOutputStream();	//取得输出流
    		byteArrayOutputStream.writeTo(outputstream);					//写到输出流
    		byteArrayOutputStream.close();									//关闭
    		outputstream.flush();											//刷数据
    	}
    }

## 模板打印execel ##

@RequestMapping(value = "printExcel",name = "打印出货表")
       public  String printExcel(String  inputDate) throws IOException { 
           //获取合同数据
           List<ContractProductVo> list=contractService.findByShipTime(inputDate,user.getCompanyId());
           System.out.println(list);
           //初始化值
           Row row = null;
           Cell cell = null;
           //读取模板数据
           InputStream is = session.getServletContext().getResourceAsStream("/make/xlsprint/tOUTPRODUCT.xlsx");
           //创建workbook
           XSSFWorkbook workbook = new XSSFWorkbook(is);
           XSSFSheet sheet = workbook.getSheetAt(0);
           row = sheet.getRow(0);
           cell= row.getCell(1);
           //大标题
           cell.setCellValue( inputDate.replace("-","年")+"月出货表");
            //小标题已写好
           //开始写数据
           //获取样式
           row = sheet.getRow(2);
           short lastCellNum = row.getLastCellNum();
           //存储每个样式
           CellStyle[] cellStyles = new CellStyle[lastCellNum];
           for (int i = 1; i < lastCellNum; i++) { 
               cell = row.getCell(i);
               CellStyle cellStyle = cell.getCellStyle();
               cellStyles[i]=cellStyle;
           }
    
           //循环数据 遍历输出
           for (int i = 1; i < list.size(); i++) { 
               //创建第三行 数据开始的第一行
               row = sheet.createRow(i+1);
               //获取第一个数据对象
               ContractProductVo contractProductVo = list.get(i-1);
               //一行 8个单元格赋值
               for (int j = 1; j <= 8 ; j++) { 
                   cell = row.createCell(j);
                   switch (j) { 
                       case 1:
                           cell.setCellValue(contractProductVo.getCustomName());
                           cell.setCellStyle(cellStyles[1]);
                           break;
                       case 2:
                           cell.setCellValue(contractProductVo.getContractNo());
                           cell.setCellStyle(cellStyles[2]);
                           break;
                       case 3:
                           cell.setCellValue(contractProductVo.getProductNo());
                           cell.setCellStyle(cellStyles[3]);
                           break;
                       case 4:
                           cell.setCellValue(contractProductVo.getCnumber());
                           cell.setCellStyle(cellStyles[4]);
                           break;
                       case 5:
                           cell.setCellValue(contractProductVo.getFactoryName());
                           cell.setCellStyle(cellStyles[5]);
                           break;
                       case 6:
                           SimpleDateFormat simpleDateFormat1 = new SimpleDateFormat("yyyy-MM-dd");
                           String format1 = simpleDateFormat1.format(contractProductVo.getDeliveryPeriod());
                           cell.setCellValue(format1);
                           cell.setCellStyle(cellStyles[6]);
                           break;
                       case 7:
                           SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd");
                           String format = simpleDateFormat.format(contractProductVo.getShipTime());
                           System.out.println(format);
                           cell.setCellValue(format);
                           cell.setCellStyle(cellStyles[7]);
                           break;
                       case 8:
                           cell.setCellValue(contractProductVo.getTradeTerms());
                           cell.setCellStyle(cellStyles[8]);
                           break;
                       default:
                           break;
                   }
               }
           }
    
    
    
           //文件写回 浏览器
           DownloadUtil downloadUtil = new DownloadUtil();
           ByteArrayOutputStream byteArrayOutputStream = new ByteArrayOutputStream();
           workbook.write(byteArrayOutputStream);
           downloadUtil.download(byteArrayOutputStream,response,"出货表.xlsx");
           return "cargo/print/contract-print";
       }

## 一些简单的样式 ##

//大标题的样式
        public CellStyle bigTitle(Workbook wb){ 
            CellStyle style = wb.createCellStyle();
            Font font = wb.createFont();
            font.setFontName("宋体");
            font.setFontHeightInPoints((short)16);
            font.setBold(true);//字体加粗
            style.setFont(font);
            style.setAlignment(HorizontalAlignment.CENTER);				//横向居中
            style.setVerticalAlignment(VerticalAlignment.CENTER);		//纵向居中
            return style;
        }
    
        //小标题的样式
        public CellStyle title(Workbook wb){ 
            CellStyle style = wb.createCellStyle();
            Font font = wb.createFont();
            font.setFontName("黑体");
            font.setFontHeightInPoints((short)12);
            style.setFont(font);
            style.setAlignment(HorizontalAlignment.CENTER);				//横向居中
            style.setVerticalAlignment(VerticalAlignment.CENTER);		//纵向居中
            style.setBorderTop(BorderStyle.THIN);						//上细线
            style.setBorderBottom(BorderStyle.THIN);					//下细线
            style.setBorderLeft(BorderStyle.THIN);						//左细线
            style.setBorderRight(BorderStyle.THIN);						//右细线
            return style;
        }
    
        //文字样式
        public CellStyle text(Workbook wb){ 
            CellStyle style = wb.createCellStyle();
            Font font = wb.createFont();
            font.setFontName("Times New Roman");
            font.setFontHeightInPoints((short)10);
    
            style.setFont(font);
    
            style.setAlignment(HorizontalAlignment.LEFT);				//横向居左
            style.setVerticalAlignment(VerticalAlignment.CENTER);		//纵向居中
            style.setBorderTop(BorderStyle.THIN);						//上细线
            style.setBorderBottom(BorderStyle.THIN);					//下细线
            style.setBorderLeft(BorderStyle.THIN);						//左细线
            style.setBorderRight(BorderStyle.THIN);						//右细线
    
            return style;
        }

## 测试例子: ##

CellStyle cellStyle = bigTitle(workbook);
            cell.setCellStyle(cellStyle);