相关依赖
<!-- Excel -->
<dependency>
<groupId>net.sourceforge.jexcelapi</groupId>
<artifactId>jxl</artifactId>
<version>2.6.12</version>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>3.1.1</version>
</dependency>
<!-- 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();
}
}
}
}
}