使用java进行excel读取和写入

灰太狼 2022-05-28 11:05 321阅读 0赞

：添加处理excel的依赖jar包

<dependency>
                <groupId>org.apache.poi</groupId>
                <artifactId>poi</artifactId>
                <version>${poi.version}</version>
            </dependency>
            <dependency>
                <groupId>org.apache.poi</groupId>
                <artifactId>poi-ooxml</artifactId>
                <version>3.14</version>
            </dependency>
            
            <dependency>
                <groupId>net.sourceforge.jexcelapi</groupId>
                <artifactId>jxl</artifactId>
                <version>2.6.10</version>
            </dependency>

2：向excel中写入内容的类

WriteExcel.java

package com.li.controller;
    import java.io.File;
    import java.io.FileInputStream;
    import java.io.FileOutputStream;
    import java.io.IOException;
    import java.io.OutputStream;
    import java.util.ArrayList;
    import java.util.HashMap;
    import java.util.List;
    import java.util.Map;
    
    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;
    
    public class WriteExcel {
        private static final String EXCEL_XLS = "xls";
        private static final String EXCEL_XLSX = "xlsx";
        
        public static void main(String[] args) {
            
            Map<String, String> dataMap=new HashMap<String, String>();
            dataMap.put("BankName", "BankName");
            dataMap.put("Addr", "Addr");
            dataMap.put("Phone", "Phone");
            List<Map> list=new ArrayList<Map>();
            list.add(dataMap);
            writeExcel(list, 3, "D:/writeExcel.xlsx");
            
        }
    
        public static void writeExcel(List<Map> dataList, int cloumnCount,String finalXlsxPath){
            OutputStream out = null;
            try {
                // 获取总列数
                int columnNumCount = cloumnCount;
                // 读取Excel文档
                File finalXlsxFile = new File(finalXlsxPath);
                Workbook workBook = getWorkbok(finalXlsxFile);
                // sheet 对应一个工作页
                Sheet sheet = workBook.getSheetAt(0);
                /**
                 * 删除原有数据，除了属性列
                 */
                int rowNumber = sheet.getLastRowNum();    // 第一行从0开始算
                System.out.println("原始数据总行数，除属性列：" + rowNumber);
                for (int i = 1; i <= rowNumber; i++) {
                    Row row = sheet.getRow(i);
                    sheet.removeRow(row);
                }
                // 创建文件输出流，输出电子表格：这个必须有，否则你在sheet上做的任何操作都不会有效
                out =  new FileOutputStream(finalXlsxPath);
                workBook.write(out);
                /**
                 * 往Excel中写新数据
                 */
                for (int j = 0; j < dataList.size(); j++) {
                    // 创建一行：从第二行开始，跳过属性列
                    Row row = sheet.createRow(j + 1);
                    // 得到要插入的每一条记录
                    Map dataMap = dataList.get(j);
                    String name = dataMap.get("BankName").toString();
                    String address = dataMap.get("Addr").toString();
                    String phone = dataMap.get("Phone").toString();
                    for (int k = 0; k <= columnNumCount; k++) {
                    // 在一行内循环
                    Cell first = row.createCell(0);
                    first.setCellValue(name);
            
                    Cell second = row.createCell(1);
                    second.setCellValue(address);
            
                    Cell third = row.createCell(2);
                    third.setCellValue(phone);
                    }
                }
                // 创建文件输出流，准备输出电子表格：这个必须有，否则你在sheet上做的任何操作都不会有效
                out =  new FileOutputStream(finalXlsxPath);
                workBook.write(out);
            } catch (Exception e) {
                e.printStackTrace();
            } finally{
                try {
                    if(out != null){
                        out.flush();
                        out.close();
                    }
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
            System.out.println("数据导出成功");
        }
    
        /**
         * 判断Excel的版本,获取Workbook
         * @param in
         * @param filename
         * @return
         * @throws IOException
         */
        public static Workbook getWorkbok(File file) throws IOException{
            Workbook wb = null;
            FileInputStream in = new FileInputStream(file);
            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;
        }
    }

3:读取Excel中的数据，并写入list中

package com.li.controller;
    import java.io.File;
    import java.io.FileInputStream;
    import java.io.FileNotFoundException;
    import java.io.IOException;
    import java.io.InputStream;
    import java.util.ArrayList;
    import java.util.List;
    
    import jxl.Sheet;
    import jxl.Workbook;
    import jxl.read.biff.BiffException;
    public class ReadExcel {
        public static void main(String[] args) {
            ReadExcel obj = new ReadExcel();
            // 此处为我创建Excel路径：E:/zhanhj/studysrc/jxl下
            File file = new File("D:/readExcel.xls");
            List excelList = obj.readExcel(file);
            System.out.println("list中的数据打印出来");
            for (int i = 0; i < excelList.size(); i++) {
                List list = (List) excelList.get(i);
                for (int j = 0; j < list.size(); j++) {
                    System.out.print(list.get(j));
                }
                System.out.println();
            }
    
        }
        // 去读Excel的方法readExcel，该方法的入口参数为一个File对象
        public List readExcel(File file) {
            try {
                // 创建输入流，读取Excel
                InputStream is = new FileInputStream(file.getAbsolutePath());
                // jxl提供的Workbook类
                Workbook wb = Workbook.getWorkbook(is);
                // Excel的页签数量
                int sheet_size = wb.getNumberOfSheets();
                for (int index = 0; index < sheet_size; index++) {
                    List<List> outerList=new ArrayList<List>();
                    // 每个页签创建一个Sheet对象
                    Sheet sheet = wb.getSheet(index);
                    // sheet.getRows()返回该页的总行数
                    for (int i = 0; i < sheet.getRows(); i++) {
                        List innerList=new ArrayList();
                        // sheet.getColumns()返回该页的总列数
                        for (int j = 0; j < sheet.getColumns(); j++) {
                            String cellinfo = sheet.getCell(j, i).getContents();
                            if(cellinfo.isEmpty()){
                                continue;
                            }
                            innerList.add(cellinfo);
                            System.out.print(cellinfo);
                        }
                        outerList.add(i, innerList);
                        System.out.println();
                    }
                    return outerList;
                }
            } catch (FileNotFoundException e) {
                e.printStackTrace();
            } catch (BiffException e) {
                e.printStackTrace();
            } catch (IOException e) {
                e.printStackTrace();
            }
            return null;
        }
    }

4：在D盘下面创建readExcel.xls（有内容） 和writeExcel.xlsx即可

转自：页 [https://www.cnblogs.com/liyafei/p/8146136.html][https_www.cnblogs.com_liyafei_p_8146136.html] 的

[https_www.cnblogs.com_liyafei_p_8146136.html]: https://www.cnblogs.com/liyafei/p/8146136.html