java操作excel文件

java通过poi读取.xls，官方下载地址：

https://poi.apache.org/download.html

 

直接下载地址：

https://mvnrepository.com/artifact/org.apache.poi/poi

 

update 2018/10/26,贴上java code

1 读取excel： 

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.json.JSONArray;
import org.json.JSONObject;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;

/**
 * Description: Excel操作
 */
public class ExcelUtil {

    private static final String EXCEL_XLS = "xls";
    private static final String EXCEL_XLSX = "xlsx";

    /**
     * 判断Excel的版本,获取Workbook
     *
     * @param in
     * @param file
     * @return
     * @throws IOException
     */
    public static Workbook getWorkbook(InputStream in, File file) throws IOException {
        Workbook wb = null;
        if (file.getName().endsWith(EXCEL_XLS)) {  //Excel 2003
            wb = new HSSFWorkbook(in);
        } else if (file.getName().endsWith(EXCEL_XLSX)) {  // Excel 2007/2010
            wb = new XSSFWorkbook(in);
        }
        return wb;
    }

    /**
     * 判断文件是否是excel
     *
     * @throws Exception
     */
    public static void checkExcelValid(File file) throws Exception {
        if (!file.exists()) {
            throw new Exception("文件不存在");
        }
        if (!(file.isFile() && (file.getName().endsWith(EXCEL_XLS) || file.getName().endsWith(EXCEL_XLSX)))) {
            throw new Exception("文件不是Excel");
        }
    }

    /**
     * 读取Excel测试，兼容 Excel 2003/2007/2010
     *
     * @throws Exception
     */
    public static void main(String[] args) throws Exception {
        int count = 0;
        try {
            // 同时支持Excel 2003、2007
            File excelFile = new File(args[0]); // 创建文件对象
            FileInputStream in = new FileInputStream(excelFile); // 文件流
            checkExcelValid(excelFile);
            Workbook workbook = getWorkbook(in, excelFile);
            //Workbook workbook = WorkbookFactory.create(is); // 这种方式 Excel2003/2007/2010都是可以处理的

//            int sheetCount = workbook.getNumberOfSheets(); // Sheet的数量
            /**
             * 设置当前excel中sheet的下标：0开始
             */
            Sheet sheet = workbook.getSheetAt(0);   // 遍历第一个Sheet

            //获取总行数
            System.out.println("当前sheet：1，总行数：" + sheet.getLastRowNum());

            JSONArray rootArray = new JSONArray();
            JSONObject levelObject = null;
            JSONArray tollgateArray = null;
            for (Row row : sheet) {
                    // 跳过第一的标题目录
//                    if(count < 1 ) {
//                        count++;
//                        continue;
//                    }

                    //获取总列数(空格的不计算)
//                    int columnTotalNum = row.getPhysicalNumberOfCells();
                    System.out.println("第 " + count + " 行:" + row.getCell(0));
                    if (count % 3 == 1) {
                        levelObject = new JSONObject();
                        rootArray.put(levelObject);
                        tollgateArray = new JSONArray();
                        levelObject.put("tollgate", tollgateArray);
                    }

                    JSONObject tollgateObject = new JSONObject();
                    tollgateArray.put(tollgateObject);
                    int end = row.getLastCellNum();
//                    具体解析excel内容
//                    for (int i = (count % 3 == 1 ? 1 : 2); i < end; i++) {
//                        Cell cell = row.getCell(i);
//                        switch (i) {
//                            case 0:
//                                break;
//                            case 1:
//                                String duration = (String) getValue(cell);
//                                levelObject.put("duration", //Integer.parseInt(duration.substring(0, duration.length() - 1)));
//                                break;
//                            case 2:
//                                tollgateObject.put("pic1", (String) getValue(cell));
//                                break;
//                            case 3:
//                                tollgateObject.put("pic2", (String) getValue(cell));
//                                break;
//                            case 4:
//                                String coordinates = (String) getValue(cell);
//                                coordinates = coordinates.replaceAll("X", "\"X\"");
//                                coordinates = coordinates.replaceAll("Y", "\"Y\"");
//                                coordinates = "[" + coordinates + "]";
//                                JSONArray pointsArray = new JSONArray(coordinates);
//                                tollgateObject.put("points", pointsArray);
//                                break;
//                            case 5:
//                                String touchEffectArea = (String) getValue(cell);
//                                String[] area = touchEffectArea.split("\\*");
//                                levelObject.put("effectWidth", //Integer.parseInt(area[0]));
//                                levelObject.put("effectHeight", //Integer.parseInt(area[1]));
//                                break;
//                        }
//                    }
                    count++;
            }
            System.out.println(rootArray.toString());
            writeFile(excelFile.getParent(), rootArray.toString());
        } catch (Exception e) {
            System.out.println("````````````` " + count);
            e.printStackTrace();
        }
    }
    
    /**
    * 将读取内容写入json文件
    */
    private static void writeFile(String directory, String stream) throws Exception
    {
        FileOutputStream outputStream = null;
        try {
            System.out.println(directory + File.separator + "configs.json");
            outputStream = new FileOutputStream(directory + File.separator + "configs.json");
            byte[] streamByte = stream.getBytes();
            outputStream.write(streamByte, 0, streamByte.length);
        } catch (Exception e) {
            throw e;
        } finally {
            if (null != outputStream)
            {
                try {
                    outputStream.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
    }
    
    /**
    * 读取单元格内容
    */
    private static Object getValue(Cell cell) {
        Object obj = null;
        switch (cell.getCellType()) {
            case BOOLEAN:
                obj = cell.getBooleanCellValue();
                break;
            case ERROR:
                obj = cell.getErrorCellValue();
                break;
            case NUMERIC:
                obj = cell.getNumericCellValue();
                break;
            case STRING:
                obj = cell.getStringCellValue();
                break;
            default:
                break;
        }
        return obj;
    }
}

 

 

2.将内容写入到excel文件

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.json.JSONArray;
import org.json.JSONObject;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;

/**
 * Created by Administrator on 2018/10/25.
 * 此类用途：随机打乱excel行 创建excel文件并写入
 */
public class ExcelRandom {
    private static final String EXCEL_XLS = "xls";
    private static final String EXCEL_XLSX = "xlsx";
    public static void main(String[] args) throws Exception {
        int count = 0;
        try {
            // 同时支持Excel 2003、2007
            File excelFile = new File(args[0]); // 创建文件对象
            checkExcelValid(excelFile);
            Workbook workbook = getWorkbook(new FileInputStream(excelFile), excelFile);
            //Workbook workbook = WorkbookFactory.create(is); // 这种方式 Excel2003/2007/2010都是可以处理的
            /**
             * 设置当前excel中sheet的下标：0开始
             */
            for (int sheetIndex = 0, sheetCount = workbook.getNumberOfSheets(); sheetIndex < sheetCount; sheetIndex++) {
            Sheet sheet = workbook.getSheetAt(sheetIndex);   // 遍历第i个Sheet
            //获取总行数
            System.out.println("当前sheet：" + sheetIndex + "，总行数：" + sheet.getLastRowNum());
            JSONArray rootArray = new JSONArray();
            CellStyle style = null;
            for (Row row : sheet) {
                JSONArray rowArray = new JSONArray();
//                StringBuffer buffer = new StringBuffer();
                    for (int i = 1, size = row.getLastCellNum(); i < size; i++) {
                        Cell cell = row.getCell(i);
                        if (null != cell) {
                            //获取单元格显示格式
                            if (null == style && ((sheet.getLastRowNum() >= 4 && row.getRowNum() >= 2)))
                                style = cell.getCellStyle();
                            String text = (String) getValue(cell);
//                            buffer.append(text);
                            rowArray.put(i, text);
                        } else {
//                            System.out.println("============cell == null, columnIndex = " + (i + 1) + ", totalColumn = " + size);
                        }
                    }
//                    System.out.println(buffer);
                    rootArray.put(rowArray);
            }
//            System.out.println(rootArray.toString());
            writeFile(excelFile.getName(), excelFile.getParent(), rootArray, sheet.getSheetName(), style);
        }
        } catch (Exception e) {
            System.out.println("error " + (count + 1) + "   " + e.toString());
        }
    }

    /**
     * 判断Excel的版本,获取Workbook
     *
     * @param in
     * @param file
     * @return
     * @throws IOException
     */
    public static Workbook getWorkbook(InputStream in, File file) throws IOException {
        Workbook wb = null;
        if (file.getName().endsWith(EXCEL_XLS)) {  //Excel 2003
            wb = new HSSFWorkbook(in);
        } else if (file.getName().endsWith(EXCEL_XLSX)) {  // Excel 2007/2010
            wb = new XSSFWorkbook(in);
        }
        return wb;
    }

    /**
     * 判断文件是否是excel
     *
     * @throws Exception
     */
    public static void checkExcelValid(File file) throws Exception {
        if (!file.exists()) {
            throw new Exception("文件不存在");
        }
        if (!(file.isFile() && (file.getName().endsWith(EXCEL_XLS) || file.getName().endsWith(EXCEL_XLSX)))) {
            throw new Exception("文件不是Excel");
        }
    }

    private static Object getValue(Cell cell) {
        Object obj = null;
        switch (cell.getCellType()) {
            case BOOLEAN:
                obj = cell.getBooleanCellValue();
                break;
            case ERROR:
                obj = cell.getErrorCellValue();
                break;
            case NUMERIC:
                obj = cell.getNumericCellValue();
                break;
            case STRING:
                obj = cell.getStringCellValue();
                break;
            default:
                break;
        }
        return obj;
    }

    private static void writeFile(String sourceName, String directory, JSONArray array, String sheetName, CellStyle style) throws Exception {
        if (null != array && array.length() > 0) {
            int point = sourceName.lastIndexOf(".");
            File excelFile = new File(directory + File.separator + sourceName.substring(0, point) + "_生成结果" + sourceName.substring(point)); // 创建文件对象
            if (excelFile.exists())
                excelFile.delete();
            Workbook workbook;
            if (sourceName.endsWith(EXCEL_XLS)) {
                workbook = new HSSFWorkbook();
            } else if(sourceName.endsWith(EXCEL_XLSX)) {
                workbook = new XSSFWorkbook();
            } else {
                throw new Exception("文件格式不正确");
            }
            //创建sheet对象，此处主要作用是创建excel空白文件，其它方法如：File.createNewFile()是无法创建能够识别的正确excel文件
            OutputStream outputStream = new FileOutputStream(excelFile);
            workbook.write(outputStream);
            outputStream.flush();
            outputStream.close();
            //随机排序行
            JSONArray resultArray = new JSONArray();
            int index = 0;
            for (int i = 0, length = array.length(); i < length; i++)
            {
                int indexRandom = (int) (Math.random() * array.length());
                resultArray.put(index++, array.get(indexRandom));
                array.remove(indexRandom);
            }
            
            //将数据写入到excel文档中
            Sheet sheet = workbook.createSheet(sheetName);//创建sheet
            CellStyle cellStyle = workbook.createCellStyle();//CellStyle只能创建，无法复用其它workbook的style对象
            cellStyle.cloneStyleFrom(style);
            if (null == cellStyle.getAlignment())//手动设置单元格的水平对齐方式
                cellStyle.setAlignment(HorizontalAlignment.LEFT);
            if (null == cellStyle.getVerticalAlignment())//手动设置单元格的垂直对齐方式
                cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
            //开始逐行逐个单元格写入数据
            for (int i = 0, size = resultArray.length(); i < size; i++) {
                Row row = sheet.createRow(i);
                JSONArray rowJson = resultArray.getJSONArray(i);
                System.out.println("第 " + i + " 行：" + rowJson.toString());
                if (null != rowJson && rowJson.length() > 0) {
                    for (int j = 0, length = rowJson.length(); j < length; j++) {
                        Cell cell = row.createCell(j);
                        if (!rowJson.isNull(j) && rowJson.get(j) != JSONObject.NULL) {
                            cell.setCellStyle(cellStyle);
                            cell.setCellValue((String) rowJson.get(j));
                        }
                    }
                }
            }
            
            //将生成的sheet写入到创建的excel文件中
            OutputStream outputStream1 = new FileOutputStream(excelFile);
            workbook.write(outputStream1);
            outputStream1.close();
        }
    }
}

 

     以上为通过poi jar系列包读取和写入excel示例。仅供交流所用

     poi完整系列jar资源下载地址：https://download.csdn.net/download/xianglongjifei/10691018