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

来源：互联网发布：淘宝发展的起源编辑：程序博客网时间：2024/06/05 08:30

依赖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..........");    }

简单好用。。。。

0 0