PoiUtil类

//PoiUtil类

package com.cst.kit.poi.excel;

import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.DateUtil;
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.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import com.cst.kit.date.DateTimeKit;
import com.cst.kit.string.StringKit;

public class PoiUtil {

	static Workbook getWorkbook(InputStream in, boolean xls) {
		Workbook wb = null;
		try {
			if (xls) {
				wb = new HSSFWorkbook(in);
			} else {
				wb = new XSSFWorkbook(in);
			}
		} catch (Exception e) {
			throw new RuntimeException(e);
		}
		return wb;
	}
	
	static Workbook getWorkbook(String fileFullName) {
		return getWorkbook(new File(fileFullName));
	}
	
	static Workbook getWorkbook(File file) {
		String fname = file.getName();
		boolean xls;
		if (fname.endsWith(".xls")) {
			xls = true;
		} else if (fname.endsWith(".xlsx")) {
			xls = false;
		} else {
			throw new RuntimeException("文件格式错误，只能使用*.xls或*.xlsx格式文件");
		}
		try (FileInputStream excel = new FileInputStream(file);) {
			return getWorkbook(excel, xls);
		} catch (Exception e) {
			throw new RuntimeException(e);
		}
	}
	
	static String getCellString(Cell cell) {
		String content = "";
		if (cell == null) {
			return null;
		} else {
				CellType cft = cell.getCellType();
				switch (cft) {
				case BLANK:
					break;
				case BOOLEAN:
					content = StringKit.of(cell.getBooleanCellValue());
					break;
				case ERROR:
					content = StringKit.of(cell.getErrorCellValue());
					break;
				case FORMULA:
					try {
						content = StringKit.of(cell.getNumericCellValue());
					} catch (IllegalStateException e) {
						content = StringKit.of(cell.getStringCellValue());
					}
					break;
				case NUMERIC:
					if (DateUtil.isCellDateFormatted(cell)) {
						content = DateTimeKit.format(cell.getDateCellValue());
					} else {
						Double value = cell.getNumericCellValue();
				          // 去掉后面无用的零  如小数点后面全是零则去掉小数点
						content = StringKit.of(value);
					}
					break;
				case STRING:
			default:
				content = StringKit.of(cell.getStringCellValue());
			}
			return content;
		}
	}
	
	/**
	 * 在指定的行插入一行
	 * 
	 * @param sh
	 * @param row
	 * @param styleRow 要复制style的row
	 * @return
	 */
	static Row insertRow(Sheet sh, Row styleRow, int row) {
		int lastRow = sh.getLastRowNum();
		if (row <= lastRow) {
			sh.shiftRows(row, lastRow, 1, true, false); // 4.0.1 此处报错,待下版本检查
		}
		Row nrow = sh.createRow(row);
		copyRowStyle(styleRow, nrow);
		return nrow;
	}
	
	static void copyRowStyle(Row source, Row target) {
		CellStyle sourceRowStyle = source.getRowStyle();
		if (sourceRowStyle != null) {
			target.setRowStyle(sourceRowStyle);
		}
		for (int x = 0; x < source.getLastCellNum(); x++) {
			Cell sourceCellx = source.getCell(x);
			if (sourceCellx != null) {
				CellStyle sourceCellStylex = sourceCellx.getCellStyle();
				Cell targetCellx = target.getCell(x);
				if (targetCellx == null) {
					targetCellx = target.createCell(x);
				}
				if (sourceCellStylex != null) {
					targetCellx.setCellStyle(sourceCellStylex);
				}
			}
		}
		target.setHeight(source.getHeight());
		copyRowMerge(source, target);
	}
	
	/**
	 * 复制行的合并单元格
	 */
	static void copyRowMerge(Row source, Row target) {
		Sheet sh = source.getSheet();
		int tr = target.getRowNum();
		int sr = source.getRowNum();
		for (int x = 0, s = sh.getNumMergedRegions(); x < s; x++) {
			CellRangeAddress cra = sh.getMergedRegion(x);
			int fr = cra.getFirstRow();
			int lr = cra.getLastRow();
			if (tr >= fr && tr <= lr && fr < lr) {
				throw new RuntimeException("本方法不能有跨行的合并单元格[" + "firstRow:" + fr + ",lastRow:" + lr + "]");
			}
			if ((sr >= fr && sr <= lr)) {
				if (fr < lr) {
					throw new RuntimeException("本方法不能有跨行的合并单元格[" + "firstRow:" + fr + ",lastRow:" + lr + "]");
				}
				int fc = cra.getFirstColumn();
				int lc = cra.getLastColumn();
				sh.addMergedRegion(new CellRangeAddress(tr, tr, fc, lc));
			}
		}
	
	}
	
	static byte[] workbook2ByteArray(Workbook wb) {
		byte[] bs = null;
		try (ByteArrayOutputStream baos = new ByteArrayOutputStream();) {
			wb.write(baos);
			bs = baos.toByteArray();
			baos.flush();
	
		} catch (Exception e) {
			throw new RuntimeException(e);
		} finally {
			try {
				wb.close();
			} catch (IOException e) {
				throw new RuntimeException(e);
			}
		}
		return bs;
	}
	
	static List<List<List<String>>> workBook2List(Workbook wb) {
		int s = wb.getNumberOfSheets();
		List<List<List<String>>> elist = new ArrayList<>();
		for (int x = 0; x < s; x++) {
			elist.add(PoiUtil.sheet2List(wb.getSheetAt(x)));
		}
		try {
			wb.close();
		} catch (IOException e) {
			throw new RuntimeException(e);
		}
		return elist;
	}
	
	static List<List<String>> sheet2List(Sheet sheet) {
		int rows = sheet.getLastRowNum();
		List<List<String>> list = new ArrayList<>();
		for (int x = 0; x <= rows; x++) {
			Row row = sheet.getRow(x);
			if (row == null || row.getLastCellNum() == 0) {
				list.add(new ArrayList<String>());
			} else {
				int cols = row.getLastCellNum();
				List<String> slist = new ArrayList<String>(cols);
				list.add(slist);
				for (int y = 0; y < cols; y++) {
					Cell cell = row.getCell(y);
					slist.add(getCellString(cell));
				}
			}
		}
		return list;
	}

}