Java 通过Poi api操作(read/write)Excel

系统管理员 2022-09-22 11:37 177阅读 0赞

依赖jar

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

我写的工具类

package com.plugins.core.util;
    
    import org.apache.poi.hssf.usermodel.HSSFWorkbook;
    import org.apache.poi.ss.usermodel.*;
    import org.slf4j.Logger;
    import org.slf4j.LoggerFactory;
    
    import java.io.*;
    import java.text.DecimalFormat;
    import java.util.ArrayList;
    import java.util.Iterator;
    import java.util.List;
    
    /** * 支持取.txt、.doc、.docx、.xls、.xlsx，5种格式文档的内容，以字符串的形式返回。 * {@link http://poi.apache.org} * * Created by simon on 16-6-28. */
    public class JavaExcelUtils { 
    
    // Excel格式如下示例:
    
    // systemFileType fileNo fileName responsibleDpt remark
    // 1 SC0001 test1.clxs 航海部 没有备注1
    // 1 SC0002 test2.clxs 航海部 没有备注2
    // 2 SC0003 test3.clxs 航海部 没有备注3
    
    
    
        private static final Logger logger = LoggerFactory.getLogger(JavaExcelUtils.class);
    
        //解析Excel文件
        public static List<List<Object>> parseExcel(InputStream input) throws Exception {
            final DecimalFormat decimalFormat = new DecimalFormat("#.####");
    
            final List<List<Object>> items = new ArrayList<List<Object>>();
    
            final Workbook wb =WorkbookFactory.create(input);
    
            final Sheet sheet = wb.getSheetAt(0);
            Iterator<Row> rowIterator = sheet.iterator();
            // skip the first row
            if (rowIterator.hasNext()) {
                rowIterator.next();
            }
            while (rowIterator.hasNext()) {
                Row row = rowIterator.next();
                List<Object> object=new ArrayList<Object>();
                items.add(object);
    
                int colIndex = 0;
                Iterator<Cell> cellIterator = row.cellIterator();
                while (cellIterator.hasNext()) {
                    Cell cell = cellIterator.next();
                    String value = getCellValueAsString(cell, decimalFormat);
                    switch(colIndex) {
                        case 0: //文件类型
                            object.add(value);
                            break;
                        case 1: //文件编号
                            object.add(value);
                            break;
                        case 2: //文件名称
                            object.add(value);
                            break;
                        case 3: responsibleDpt
                            object.add(value);
                            break;
                        default:
                            object.add(value);
                            break;
                    }
                    colIndex++;
                }
            }
            wb.close();
            return items;
        }
    
        public  static byte[] createExcel(List<List<Object>> lists) throws Exception {
            Workbook wb = new HSSFWorkbook();
            Sheet sheet = wb.createSheet("Sheet 1");
            // Create a row and put some cells in it. Rows are 0 based.
            int colIndex = 0;
            Row row = sheet.createRow(0);
            row.createCell(colIndex++).setCellValue("systemFileType");
            row.createCell(colIndex++).setCellValue("fileNo");
            row.createCell(colIndex++).setCellValue("fileName");
            row.createCell(colIndex++).setCellValue("responsibleDpt");
            row.createCell(colIndex++).setCellValue("remark");
    
            int rowIndex = 1;
            for (List<Object> item : lists) {
                colIndex = 0; // 重置列索引为0
                row = sheet.createRow(rowIndex);
                for(int i=0;i<item.size();i++){
                    row.createCell(colIndex++).setCellValue(item.get(i).toString());
                }
                rowIndex++;
            }
    
            ByteArrayOutputStream output = new ByteArrayOutputStream();
            wb.write(output);
            output.close();
            wb.close();
            return output.toByteArray();
        }
    
        // byte[] to file
        public static void getFile(byte[] bfile, String filePath,String fileName) {
            BufferedOutputStream bos = null;
            FileOutputStream fos = null;
            File file = null;
            try {
                File dir = new File(filePath);
                if(!dir.exists()&&dir.isDirectory()){
       //判断文件目录是否存在
                    dir.mkdirs();
                }
                file = new File(filePath+File.separator+fileName);
                fos = new FileOutputStream(file);
                bos = new BufferedOutputStream(fos);
                bos.write(bfile);
            } catch (Exception e) {
                e.printStackTrace();
            } finally {
                if (bos != null) {
                    try {
                        bos.close();
                    } catch (IOException e1) {
                        e1.printStackTrace();
                    }
                }
                if (fos != null) {
                    try {
                        fos.close();
                    } catch (IOException e1) {
                        e1.printStackTrace();
                    }
                }
            }
        }
    
    
        private static String getCellValueAsString(Cell cell, DecimalFormat decimalFormat) {
            switch (cell.getCellType()) {
                case Cell.CELL_TYPE_NUMERIC:
                    double numericCellValue = cell.getNumericCellValue();
                    return decimalFormat.format(numericCellValue);
                case Cell.CELL_TYPE_STRING:
                    return cell.getStringCellValue();
                case Cell.CELL_TYPE_BOOLEAN:
                    return String.valueOf(cell.getBooleanCellValue());
                default:
                    break;
            }
            return null;
        }
    }

我写的测试类

@Test
        public  void  test45() throws Exception {
            System.out.println("start......");
            File file=new File("/software/mine-workspace/idea/platform/data/filestorage/1/201606/2815/2348/5de92fb8-c6ce-4ed5-9e8f-b0da0816ce0e.xlsx");
            InputStream inputStream=new FileInputStream(file);
            List<List<Object>> lists= JavaExcelUtils.parseExcel(inputStream);
            for (List<Object> o:lists){
                for(int i=0;i<o.size();i++){
                    System.out.print(o.get(i)+"\t");
                }
                System.out.println();
            }
    
            System.out.println("write.....");
            byte[] bytes=JavaExcelUtils.createExcel(lists);
            JavaExcelUtils.getFile(bytes,"/home/simon/桌面/","data.xlsx");
            System.out.println("end..........");
        }

简单好用。。。。