JAVA读取写入excle表兼容版

最新推荐文章于 2023-03-10 15:45:30 发布

图图小淘气_real

最新推荐文章于 2023-03-10 15:45:30 发布

阅读量174

点赞数 1

分类专栏：前端 JAVA

本文链接：https://blog.csdn.net/qq_41995919/article/details/103231419

版权

前端同时被 2 个专栏收录

74 篇文章 0 订阅

订阅专栏

JAVA

70 篇文章 1 订阅

订阅专栏

Maven依赖：

 <!-- https://mvnrepository.com/artifact/org.apache.poi/poi -->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>4.1.0</version>
        </dependency>
  <!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml -->
       <dependency>
            <groupId>org.apache.poi</groupId>
           <artifactId>poi-ooxml</artifactId>
           <version>4.1.0</version>
     </dependency>

模拟使用的实体类：Test.java：

package com.test.word;

import java.io.Serializable;
/**
 * 模拟的实体类
 * @author xieh
 *
 */
public class Test  implements Serializable{
	private  int id;
	private String name;
	public int getId() {
		return id;
	}
	public void setId(int id) {
		this.id = id;
	}
	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
	@Override
	public String toString() {
		return "Test [id=" + id + ", name=" + name + "]";
	}
}

读取与写入的excle工具类：

package com.test.word;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
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.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.CreationHelper;
import org.apache.poi.ss.usermodel.Font;
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.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFDataFormat;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

/**
 * excle工具类
 * 
 * @author xieh 2019/11/24 使用的jar包是：poi-4.1.0.jar 和 poi-ooxml-4.1.0.jar
 *
 */
public class ExcelUtil {
	
	/**
	 * 读取excle内容：注意此方法不兼容.xlsx文件
	 * @throws Exception
	 */
	@SuppressWarnings("resource")
	public static List<Map<String, String>> excelRead() throws Exception {
		//用流的方式先读取到你想要的excel的文件
        //FileInputStream fis=new FileInputStream(new File(System.getProperty("user.dir")+"/src/excel.xls"));
		FileInputStream fis = new FileInputStream(new File("D:/a.xls"));
		// 解析excel
		POIFSFileSystem pSystem = new POIFSFileSystem(fis);
		// 获取整个excel
		HSSFWorkbook hb = new HSSFWorkbook(pSystem);
		System.out.println(hb.getNumCellStyles());
		// 获取第一个表单sheet
		HSSFSheet sheet = hb.getSheetAt(0);
		// 获取第一行
		int firstrow = sheet.getFirstRowNum();
		// 获取最后一行
		int lastrow = sheet.getLastRowNum();
		// 存取最后结果
		List<Map<String, String>> result = new ArrayList<Map<String, String>>();
		// 循环行数依次获取列数
		for (int i = firstrow; i < lastrow + 1; i++) {
			// 获取哪一行i
			Row row = sheet.getRow(i);
			// 存放每行的键值对结果
			Map<String, String> tempMap = new HashMap<String, String>();
			if (row != null) {
				// 获取这一行的第一列
				int firstcell = row.getFirstCellNum();
				// 获取这一行的最后一列
				int lastcell = row.getLastCellNum();
				// 创建一个集合，用处将每一行的每一列数据都存入集合中
				//List<String> list = new ArrayList<String>();
				for (int j = firstcell; j < lastcell; j++) {
					// 获取第j列
					Cell cell = row.getCell(j);
					if (cell != null && !("").equals(cell.toString())) {
						//System.out.print(cell + "\t");
						//list.add(cell.toString());
						tempMap.put("c" + j, cell.toString().trim());
					}
				}
				// 存放每行的结果
				result.add(tempMap);
			}// row if end
			
		}// for end
		fis.close();
		System.out.println(result.toString());
		return result;
	}

	
	
	
	/**
	 * 此方法兼容.xls 和 .xlsx格式，建议使用
	 * @param path
	 * @return excle表中的内容
	 * @throws Exception
	 */
	@SuppressWarnings("resource")
	public static List<Map<String, String>> excelXRead(String path) throws Exception {
		//用流的方式先读取到你想要的excel的文件
        //FileInputStream fis=new FileInputStream(new File(System.getProperty("user.dir")+"/src/excel.xls"));
		 File excel = new File(path);
		 String[] split = excel.getName().split("\\.");  //.是特殊字符，需要转义
		 Workbook wb = null;
		 FileInputStream  fis = null;
		 
         //根据文件后缀（xls/xlsx）进行判断
         if ( "xls".equals(split[1])){
        	 fis = new FileInputStream(excel);   //文件流对象 
             wb = new HSSFWorkbook(fis);
             
         }else if ("xlsx".equals(split[1])){
             wb = new XSSFWorkbook(excel);
             
         }else {
             System.out.println("文件类型错误!");
             return new ArrayList<>();
             
         }	
         //开始解析
         Sheet sheet = wb.getSheetAt(0);     //读取sheet 0
		// 获取第一行
		int firstrow = sheet.getFirstRowNum() + 1; //第一行是列名，所以不读
		// 获取最后一行
		int lastrow = sheet.getLastRowNum();
		// 存取最后结果
		List<Map<String, String>> result = new ArrayList<Map<String, String>>();
		// 循环行数依次获取列数
		for (int i = firstrow; i < lastrow + 1; i++) {
			// 获取哪一行i
			Row row = sheet.getRow(i);
			// 存放每行的键值对结果
			Map<String, String> tempMap = new HashMap<String, String>();
			if (row != null) {
				// 获取这一行的第一列
				int firstcell = row.getFirstCellNum();
				// 获取这一行的最后一列
				int lastcell = row.getLastCellNum();
				// 创建一个集合，用处将每一行的每一列数据都存入集合中
				//List<String> list = new ArrayList<String>();
				for (int j = firstcell; j < lastcell; j++) {
					// 获取第j列
					Cell cell = row.getCell(j);
					if (cell != null && !("").equals(cell.toString())) {
						//System.out.print(cell + "\t");
						//list.add(cell.toString());
						tempMap.put("c" + j, cell.toString().trim());
					}
				}
				// 存放每行的结果
				result.add(tempMap);
			}// row if end
			
		}// for end
		
		if(null != fis){
			fis.close();
		}
		System.out.println(result.toString());
		return result;
	}
	
	
	/**
	 * 创建和写入excle内容
	 * @param exportList 实体类list
	 * @param file 生成的文件路径
	 * @throws Exception
	 */
	public static void writeXls(List<Test> exportList, File file) throws Exception {
		 
		String[] options = { "ID", "内容", "名字" };
		XSSFWorkbook book = new XSSFWorkbook();
 
		CreationHelper createHelper = book.getCreationHelper();
 
		XSSFCellStyle style = book.createCellStyle();
		XSSFCellStyle dateStyle = book.createCellStyle();
		XSSFDataFormat format = book.createDataFormat();
		style.setWrapText(true);
		dateStyle.setWrapText(true);
 
		XSSFSheet sheet = book.createSheet("sheet");
 
		sheet.setColumnWidth(3, 13000);
		sheet.setDefaultColumnWidth(20);
 
		XSSFRow firstRow = sheet.createRow(0);
		XSSFCell[] firstCells = new XSSFCell[3];
 
		CellStyle styleBlue = book.createCellStyle(); // 样式对象
		// 设置单元格的背景颜色为淡蓝色
		styleBlue.setWrapText(true);// 指定当单元格内容显示不下时自动换行
 
		Font font = book.createFont();
		//font.setBoldweight(Font.BOLDWEIGHT_BOLD);
		font.setFontName("宋体");
		font.setFontHeight((short) 280);
		style.setFont(font);
		dateStyle.setFont(font);
		dateStyle.setDataFormat(format.getFormat("yyyy-mm-dd"));
		styleBlue.setFont(font);
 
		for (int j = 0; j < options.length; j++) {
			firstCells[j] = firstRow.createCell(j);
			firstCells[j].setCellStyle(styleBlue);
			firstCells[j].setCellValue(new XSSFRichTextString(options[j]));
		}
		getExport(sheet, style, createHelper, exportList, dateStyle);
		if (file.exists()) {
			file.delete();
		}
		file.createNewFile();
 
		OutputStream os = new FileOutputStream(file);
		book.write(os);
		os.close();
	}

	
	
	
	
	
	/**
	 * excle实体数据写入
	 * @param sheet
	 * @param style
	 * @param createHelper
	 * @param exportList
	 * @param dateStyle
	 */
	private static void getExport(XSSFSheet sheet, XSSFCellStyle style, CreationHelper createHelper, List<Test> exportList,
			XSSFCellStyle dateStyle) {
		// 遍历实例类的list集合
		for (int i = 0; i < exportList.size(); i++) {
			// 创建行
			XSSFRow row = sheet.createRow(i + 1);
			
			// 实体类
			Test export = exportList.get(i);
			
			// 第一列
			XSSFCell hotelId = row.createCell(0);
			hotelId.setCellStyle(style);
			
			// 第二列
			XSSFCell hotelName = row.createCell(1);
			hotelName.setCellStyle(dateStyle);
			
			// 第三列
			XSSFCell chargeCount = row.createCell(2);
			chargeCount.setCellStyle(style);
			
			// 设置值
			hotelId.setCellValue(export.getId());
			hotelName.setCellValue("测试");
			chargeCount.setCellValue(export.getName());
 
			// ta.append("写入excel开始,行数是" + (i + 1) + "\n");
		}// for end
 
	}

	
	
	
	
	/**
	 * 测试
	 * 
	 * @param args
	 */
	public static void main(String[] args) {
		try {
			ExcelUtil.excelRead();
			ExcelUtil.excelXRead("D:/柱子.xlsx");
			ExcelUtil.excelXRead("D:/a.xls");
			List<Test> show = new ArrayList<Test>();
			Test test1 = new Test();
			test1.setId(1);
			test1.setName("xieh");
			
			Test test2 = new Test();
			test2.setId(2);
			test2.setName("xieh");
			show.add(test1);
			show.add(test2);
			ExcelUtil.writeXls(show, new File("D:/bbb.xls"));
			ExcelUtil.writeXls(show, new File("D:/aaa.xlsx"));
			
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

}

效果：