对Excel的相关操作

最新推荐文章于 2024-09-11 11:28:25 发布

qq_47386653

最新推荐文章于 2024-09-11 11:28:25 发布

阅读量177

点赞数

文章标签： excel windows

本文链接：https://blog.csdn.net/qq_47386653/article/details/131795278

版权

该文介绍了两种在Java中操作Excel的方法，包括使用ApachePOI库的方式一，这种方式每次写入都会生成FileInputStream，以及使用EasyExcel的更简洁方式二。此外，文章还详细展示了如何读取Excel内容以及如何将图片存入Excel。

摘要由CSDN通过智能技术生成

Excel相关操作

写入EXCEL

方式一：

此方式每往excel写入一次就会生成一个FileInputStream文件，不建议使用

import cn.hutool.system.SystemUtil;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.*;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

@Slf4j
public class WriteToExcelUtil {
	private static XSSFWorkbook workbook;
    private static XSSFSheet sheet;
    private static XSSFRow row;
    private static XSSFCell cell;
    private static FileInputStream file;

	static {
        if (SystemUtil.getOsInfo().isWindows()) {
            path = System.getProperty("user.dir") + "Excel/test.xlsx";
        } else {
            path = "E:\\newfunc\\test.xlsx";
        }
    }
    
	public static void WriteToExcel(Map<String, List<String>> map) {
        for (String key : map.keySet()) {
            WriteToExcel(map.get(key));
        }
    }

	 public static void WriteToExcel(List<String> content) {
        File fileA = new File(path);
        if (fileA.exists()) {
			//如果文件存在
            getSheet("WorkSheet", path);
            List<List<String>> contentList = getContent(content);//内容测试数据
            createContent(contentList);                   //设置内容
            writeToFile(path);         //写入文件
        } else {
            log.error("Dev.xlsx文件不存在，请自行创建!");
        }
    } 

	//获取已有sheet页
    public static void getSheet(String sheetName, String path) {
        try {
        	workbook = new XSSFWorkbook(path);
        } catch (IOException e) {
            e.printStackTrace();
        }
        sheet = workbook.getSheet(sheetName);
    }

	// 内容数据
    protected static List<List<String>> getContent(List<String> content) {
        List<List<String>> contentList = new ArrayList<>();
        contentList.add(content);
        return contentList;
    }

	//创建表内容
    public static void createContent(List<List<String>> contentList) {
    	int rows = sheet.getLastRowNum();
        //创建表内容，从第二行开始
        for (List<String> strings : contentList) {
            row = sheet.createRow(rows + 1);
            for (int j = 0; j < strings.size(); j++) {
                row.createCell(j).setCellValue(strings.get(j));
            }
        }
    }

	//写入文件
    public static void writeToFile(String filePath) {
    	//将文件保存到指定的位置
        try {
            file = new FileInputStream(filePath);
            workbook.write(new FileOutputStream(String.valueOf(file)));
            log.info("Excel写入成功！");
            workbook.close();
        } catch (Exception e) {
            log.error("Excel文件写入失败，失败原因:{}", e.getMessage());
        }
    }
}

方式二：

import com.alibaba.excel.annotation.ExcelProperty;
import lombok.Data;

import java.util.Date;

@Data
public class ExcelStudentDTO {

    @ExcelProperty("姓名")
    private String name;

    @ExcelProperty("生日")
    private Date birthday;

    @ExcelProperty("薪资")
    private Double salary;
}

import com.alibaba.excel.EasyExcel;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

public class ExcelWriteTest {

    public static void main(String[] args) {
        simpleWriteXlsx();
    }

	public static void simpleWriteXlsx() {
        String fileName = "E:\\test.xlsx"; //需要提前新建目录
        // 这里 需要指定写用哪个class去写，然后写到第一个sheet，名字为模板 然后文件流会自动关闭
        EasyExcel.write(fileName, ExcelStudentDTO.class).sheet("worksheet").doWrite(data());
    }

	//辅助方法
    public static List<ExcelStudentDTO> data(){
        List<ExcelStudentDTO> list = new ArrayList<>();
        //算上标题，做多可写65536行
        //超出：java.lang.IllegalArgumentException: Invalid row number (65536) outside allowable range (0..65535)
        for (int i = 0; i < 65535; i++) {
            ExcelStudentDTO data = new ExcelStudentDTO();
            data.setName("Helen" + i);
            data.setBirthday(new Date());
            data.setSalary(123456.1234);
            list.add(data);
        }
        return list;
    }
}

读取Excel

import cn.hutool.system.SystemUtil;
import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import lombok.extern.slf4j.Slf4j;
import java.io.File;
import java.util.Arrays;

@Slf4j
public class ReadExcelUtil {

	public static String path;
	
	static {
        if (SystemUtil.getOsInfo().isWindows()) {
            path = System.getProperty("user.dir") + "/Excel/test.xls";
        } else {
            path = "/Excel/test.xls";
        }
    }

	public static void ToAnalysisExcel() {
        // Unable to recognize OLE stream  不支持xlsx格式  支持xls格式

        File Inputfile = new File(path);
        Workbook wb = null;
        try {
            wb = Workbook.getWorkbook(Inputfile);
            //3. 获取指定的sheet页码   通过指定的Sheet页的名字获取指定的Sheet页，也可以通过索引获取Sheet
            Sheet sheet = wb.getSheet("worksheet");
            String[] SheetNumes =  wb.getSheetNames();
            System.out.println(Arrays.toString(SheetNumes));
            //4. 获取指定的单元格的数据  通过getCell方法获取指定单元格对象，参数是column,row,索引从0开始

            //4.1 循环获取指定的行和列的单元格的值     外循环控制行，内循环控制列
            for (int i = 1; i < sheet.getRows(); i++) {
            	Cell VillageInfoName = sheet.getCell(2,i);
                String DQWD = sheet.getCell(3,i).getContents();
                double a = Double.parseDouble(DQWD);
                System.out.println(a);
            }
            wb.close();    //将工作簿的资源关闭
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

}

往Excel存入图片

import org.apache.poi.xssf.usermodel.XSSFClientAnchor;
import org.apache.poi.xssf.usermodel.XSSFDrawing;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import javax.imageio.ImageIO;
import java.awt.image.BufferedImage;
import java.io.*;
import java.util.HashMap;
import java.util.Map;

public class ExcelPictuer {

	public static void main(String[] args) throws IOException {
		Map<String, CellPoint> map = new HashMap<>();
		map.put(“xx\xz.jpg”,new CellPoint(0,0,2,10));
		addImageToExcel(new File(“xx\test.xlsx”),0,map);
		System.out.println(“ok”);
	}

	/**
	* 写入图片到Excel指定的位置
	*
	* @param patriarch 画图的顶级管理器，一个sheet只能获取一次，多次插入图片	请使用同一个patriarch对象
	* @param wb HSSFWorkbook对象
	* @param filepath 图片文件路径
	* @param cellPoint 自定义的对象，指定要插入图片的坐标(x, y)
	* @return cellPoint 自定义的对象，返回下一个要插入图片的坐标(x, y)
	* @throws IOException
	*/
	public static void whiteImg(XSSFDrawing patriarch, XSSFWorkbook wb, 		String filepath, CellPoint cellPoint) throws IOException {
		ByteArrayOutputStream byteArrayOut = new ByteArrayOutputStream();
		BufferedImage bufferImg = ImageIO.read(new File(filepath));
		ImageIO.write(bufferImg, filepath.substring(filepath.lastIndexOf(“.”) + 1), byteArrayOut);

		// 起点坐标
		int x1 = cellPoint.getRow1();
		int y1 = cellPoint.getCol1();
		// 终点坐标
		int x2 = cellPoint.getRow2();
		int y2 = cellPoint.getCol2();
		// anchor主要用于设置图片的属性
		XSSFClientAnchor anchor1 = new XSSFClientAnchor(0, 0, 0, 0, (short) x1, y1, (short) x2, y2);
		// 插入图片
		int index = wb.addPicture(byteArrayOut.toByteArray(), 		XSSFWorkbook.PICTURE_TYPE_PICT);
		patriarch.createPicture(anchor1, index);
	}

	public static class CellPoint {
		// 起点横坐标
		private int row1;
		// 起点纵坐标
		private int col1;
		// 终点横坐标
		private int row2;
		// 终点纵坐标
		private int col2;

		public CellPoint(int row1, int col1, int row2, int col2) {
			this.row1 = row1;
			this.col1 = col1;
			this.row2 = row2;
			this.col2 = col2;
		}
		public int getRow1() {
			return row1;
		}

		public void setRow1(int row1) {
			this.row1 = row1;
		}

		public int getCol1() {
			return col1;
		}

		public void setCol1(int col1) {
			this.col1 = col1;
		}

		public int getRow2() {
			return row2;
		}

		public void setRow2(int row2) {
			this.row2 = row2;
		}

		public int getCol2() {
			return col2;
		}

		public void setCol2(int col2) {
			this.col2 = col2;
		}
	}

	/**
	* * 插入图片到指定excel
	*
	* @param file 待插入excel文件
	* @param sheetIndex 待插入excel的Sheet序号(从0开始)
	* @param map key: 待插入图片路径 value: 图片在excel中的坐标
	* @return
	* @Date: 2020/11/28 10:09
	*/
	public static void addImageToExcel(File file, Integer sheetIndex, Map<String, CellPoint> map) {
		FileOutputStream fileOut = null;
		try {
			FileInputStream inputstream = new FileInputStream(file);
			XSSFWorkbook wb = new XSSFWorkbook(inputstream);
			XSSFSheet sheet1 = wb.getSheetAt(sheetIndex);
			// 画图的顶级管理器，一个sheet只能获取一个（一定要注意这点）
			XSSFDrawing patriarch = sheet1.createDrawingPatriarch();
			for (Map.Entry<String, CellPoint> entry : map.entrySet()) {
				String key = entry.getKey(); // 图片路径
				CellPoint point = entry.getValue(); // 图片插入坐标
			whiteImg(patriarch, wb, key, point);
			}
			fileOut = new FileOutputStream(file);
			// 写入excel文件
			wb.write(fileOut);
			} catch (Exception e) {
				e.printStackTrace();
			} finally {
				if (fileOut != null) {
					try {
						fileOut.close();
					} catch (IOException e) {
						e.printStackTrace();
					}
			}
		}
	}
}