Excel工具包 （POI包）

基本POI工具包写的Excel工具包

读就是读

写，是按模板文件进行写（模板文件预先设计好漂亮的格式，代码只写内容）

pom

<!-- excel start -->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>3.17</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-scratchpad</artifactId>
            <version>3.17</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.17</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml-schemas</artifactId>
            <version>3.17</version>
        </dependency>
        <dependency>
            <groupId>com.google.guava</groupId>
            <artifactId>guava</artifactId>
            <version>23.0</version>
        </dependency>

<!-- excel end -->

读取工具包

package com.marks.zweb.util.excel;

import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
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.ss.usermodel.WorkbookFactory;

import com.alibaba.fastjson.JSONArray;

/**
 * Excel工具类
 */
public class ExcelReadUtils {

	private Sheet currSheet;
	private Workbook workbook;
	private String filePath;

	private ExcelReadUtils(String file) {
		this.filePath = file;
	}

	public static ExcelReadUtils generate(String filePath) throws ExcelException {
		ExcelReadUtils util = new ExcelReadUtils(filePath);
		util.initWorkbook();

		util.setCurrSheet(null); // 设置第1个为当前sheet
		return util;
	}

	/**
	 * 设置当前sheet
	 * 
	 * @param sheetNo
	 * @throws ExcelException
	 */
	public void setCurrSheet(Integer sheetNo) throws ExcelException {

		if (sheetNo == null) {
			sheetNo = 0;
		}
		currSheet = workbook.getSheetAt(sheetNo);

	}

	/**
	 * 获取Workbook对象
	 * 
	 * @param filepath 文件全路径
	 */
	private Workbook initWorkbook() throws ExcelException {

		InputStream is = null;
		Workbook wb = null;
		if (StringUtils.isBlank(this.filePath)) {
			throw new ExcelException("文件路径不能为空");
		}

		String suffiex = getSuffiex(this.filePath);
		if (StringUtils.isBlank(suffiex)) {
			throw new ExcelException("文件后缀不能为空");
		}
		if (!"xls".equals(suffiex) && !"xlsx".equals(suffiex)) {
			throw new ExcelException("该文件非Excel文件");
		}

		try {
			is = new FileInputStream(this.filePath);
			wb = WorkbookFactory.create(is);

		} catch (Exception e) {
			throw new ExcelException("excel解析失败", e.getMessage());
		} finally {
			if (is != null) {
				try {
					is.close();
				} catch (IOException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
			if (wb != null) {
				try {
					wb.close();
				} catch (IOException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
		}

		this.workbook = wb;

		return this.workbook;
	}

	/**
	 * 获取后缀
	 * 
	 * @param filepath filepath 文件全路径
	 */
	private String getSuffiex(String filepath) {
		if (StringUtils.isBlank(filepath)) {
			return "";
		}
		int index = filepath.lastIndexOf(".");
		if (index == -1) {
			return "";
		}
		return filepath.substring(index + 1, filepath.length());
	}

	/**
	 * 读取sheet页数据
	 * 
	 * @param sheet
	 * @return
	 * @throws ExcelException
	 */
	private List<String[]> read(Sheet sheet) throws ExcelException {

		List<String[]> list = new ArrayList<String[]>();

		if (sheet == null) {
			return null;
		}

		// 得到excel的总记录条数
		int rowNos = sheet.getLastRowNum();
		for (int i = 0; i <= rowNos; i++) {
			// 遍历行
			String[] rowData = readRow(sheet, i);

			list.add(rowData);//
		}

		return list;
	}

	/**
	 * 读取Excel sheet页
	 * 
	 * @param filepath
	 * @param sheetNo
	 * @return
	 * @throws Exception
	 */
	public List<String[]> readSheet() throws Exception {
		return this.read(currSheet);
	}

	/**
	 * 读取Excel sheet页
	 * 
	 * @param filepath
	 * @param sheetNo
	 * @return
	 * @throws Exception
	 */
	public List<String[]> readSheet(Integer sheetNo) throws Exception {

		if (this.workbook == null) {
			return null;
		}

		if (sheetNo == null) {
			sheetNo = 0;
		}

		Sheet sheet = this.workbook.getSheetAt(sheetNo);
		if (sheet != null) {
			return read(sheet);
		}

		return null;
	}

	/**
	 * 读取指定行
	 * 
	 * @param filepath 文件
	 * @param sheetNo  sheet编号
	 * @param rowNo    行号
	 * @return
	 * @throws ExcelException
	 * @throws IOException
	 */
	public String[] readRow(Integer rowNo) throws ExcelException, IOException {

		Sheet sheet = this.currSheet;
		return readRow(sheet, rowNo);
	}

	/**
	 * 读取指定格
	 * 
	 * @param rowIndex
	 * @param colIndex
	 * @return
	 * @throws ExcelException
	 * @throws IOException
	 */
	public String readCell(Integer rowIndex, Integer colIndex) throws ExcelException, IOException {

		Sheet sheet = this.currSheet;
		Row row = sheet.getRow(rowIndex);

		Cell cell = row.getCell(colIndex);
		if (cell == null) {
			return null;
		}

		cell.setCellType(CellType.STRING);
		return cell.getStringCellValue();
	}

	/**
	 * 读取指定行
	 * 
	 * @param sheet
	 * @param rowNo
	 * @return
	 * @throws ExcelException
	 */
	public String[] readRow(Sheet sheet, Integer rowNo) throws ExcelException {

		if (sheet == null) {
			return null;
		}

		if (rowNo == null) {
			rowNo = 0;
		}

		Row row = sheet.getRow(rowNo);
		if (row == null) {
			return null;
		}

		int length = row.getLastCellNum();
		String[] rowData = new String[length];

		for (int i = 0; i < row.getLastCellNum(); i++) {

			String val = "";
			Cell cell = row.getCell(i);
			if (cell != null) {

				cell.setCellType(CellType.STRING);

				val = cell.getStringCellValue();
			}

			rowData[i] = val;
		}

		return rowData;
	}

	public void close() {
		try {
			this.workbook.close();
		} catch (IOException e) {
			e.printStackTrace();
		}
	}

	public void save() {
		try {
			OutputStream outputStream = new FileOutputStream(this.filePath);
			this.workbook.write(outputStream);
			this.workbook.close();
			outputStream.close();

		} catch (Exception e) {
			e.printStackTrace();
		}
	}

}

写工具包

package com.marks.zweb.util.excel;

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

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.ss.usermodel.WorkbookFactory;

/**
 * Excel工具类
 */
public class ExcelWriteUtils {

	private Sheet currSheet;
	private Workbook workbook;
	private String templateFile; // 模板地址
	private String targetFile; // 目标地址

	private InputStream is;

	private ExcelWriteUtils() throws ExcelException {

	}

	private ExcelWriteUtils(String templateFile, String targetFile) throws ExcelException {

		this.templateFile = templateFile;
		this.targetFile = targetFile;

		try {
			workbook = WorkbookFactory.create(new File(templateFile));
			currSheet = workbook.getSheetAt(0);
		} catch (Exception e) {
			throw new ExcelException("模板文件不存在");
		}
	}

	public static ExcelWriteUtils generate(String templateFile, String targetFile) throws ExcelException {

		ExcelWriteUtils util = new ExcelWriteUtils(templateFile, targetFile);
		return util;
	}

	/**
	 * 设置当前sheet
	 * 
	 * @param sheetNo
	 * @throws ExcelException
	 */
	public void setCurrSheet(Integer sheetNo) throws ExcelException {

		if (sheetNo == null) {
			sheetNo = 0;
		}
		currSheet = workbook.getSheetAt(sheetNo);

	}

	/**
	 * 写一行数据
	 * 
	 * @param sheet
	 * @param rowIndex
	 * @param textArr
	 * @return
	 */
	public Boolean writeRow(int rowIndex, String[] textArr) {

		Sheet sheet = this.currSheet;

		if (sheet == null || textArr == null || textArr.length == 0) {
			return false;
		}

		Row row = sheet.getRow(rowIndex);
		if (row == null) {
			return false;
		}

		for (int i = 0; i < textArr.length; i++) {
			Cell cell = row.getCell(i);
			if (cell != null) {
				cell.setCellValue(textArr[i]);
			}
		}
		return true;
	}

	/**
	 * 写一个数据
	 * 
	 * @param rowIndex
	 * @param colIndex
	 * @param text
	 * @return
	 */
	public Boolean writeCell(int rowIndex, int colIndex, String text) {
		Sheet sheet = this.currSheet;

		if (sheet == null) {
			return false;
		}

		Row row = sheet.getRow(rowIndex);
		if (row == null) {
			return false;
		}

		Cell cell = row.getCell(colIndex);
		if (cell == null) {
			return false;
		}

		cell.setCellValue(text);

		return true;
	}

	public void close() {
		try {
			this.workbook.close();
		} catch (IOException e) {
			e.printStackTrace();
		}
	}

	/**
	 * 写入文件
	 */
	public void save2File() {
		FileOutputStream fos = null;

		try {
			fos = new FileOutputStream(this.targetFile);
			workbook.write(fos);

		} catch (FileNotFoundException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		} finally {
			if (fos != null) {
				try {
					fos.close();
				} catch (IOException e) {
					e.printStackTrace();
				}
			}
		}
	}

	/**
	 * 写入文件(另存)
	 */
	public void saveAs(String targetFile) {
		this.targetFile = targetFile;
		this.save2File();
	}

}

 

测试代码

package com.marks.zweb.util.excel;

import java.util.Date;
import java.util.List;

import com.alibaba.fastjson.JSONArray;

public class Test {
	
	public static void main(String[] args) throws Exception {
		
		
		String filePath = "C:\\data\\excel\\excel.xlsx";
		ExcelReadUtils excel = ExcelReadUtils.generate(filePath);

		List<String[]> list;

		String json = "";
		String[] rowData1;

		list = excel.readSheet(0);
		json = JSONArray.toJSONString(list);
		System.out.println("第1页：" + json);

		rowData1 = excel.readRow(0);
		System.out.println("第1行：" + JSONArray.toJSONString(rowData1));

		rowData1 = excel.readRow(2);
		System.out.println("第3行：" + JSONArray.toJSONString(rowData1));

		System.out.println("1行2列：" + excel.readCell(0, 1));
		System.out.println("---------- sheet -----------");
		excel.setCurrSheet(1);

		list = excel.readSheet();

		json = JSONArray.toJSONString(list);
		System.out.println("第1页：" + json);

		rowData1 = excel.readRow(0);
		System.out.println("第1行：" + JSONArray.toJSONString(rowData1));

		rowData1 = excel.readRow(2);
		System.out.println("第3行：" + JSONArray.toJSONString(rowData1));

		System.out.println("1行2列：" + excel.readCell(0, 1));

		excel.close();

		System.out.println("---------- end read -----------");
		

		String filePath2 = "C:\\data\\excel\\excel-"+new Date().getTime()+".xlsx";

		ExcelWriteUtils utils = ExcelWriteUtils.generate(filePath, filePath2);

		String[] textArr = { "a", "b", "c" };

		utils.writeRow(9, textArr);

		utils.save2File();

		utils.close();

		System.out.println("---------- end write -----------");
	}

}