lyggkj/gkaq/yjpt-java/trunk/java_src/com/jtgh/yjpt/common/PoiUtils.java

package com.jtgh.yjpt.common;

import java.io.File;
import java.io.FileOutputStream;
import java.math.BigDecimal;
import java.sql.Timestamp;
import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;

import org.apache.commons.lang.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFFooter;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.NumberToTextConverter;

public class PoiUtils {
	public static HSSFCell getCell(HSSFSheet sheet, int x, int y) {
		HSSFRow row = sheet.getRow(x - 1);
		if (row == null)
			return null;
		HSSFCell cell = row.getCell(y - 1);
		if (cell == null)
			return null;
		return cell;
	}

	/**
	 * 取单元格内的数字,非数字的单元格返回0
	 */
	public static double getNumericCellValue(HSSFCell cell) {
		if (cell == null)
			return 0;
		switch (cell.getCellType()) {
		case HSSFCell.CELL_TYPE_STRING: {
			try {
				return Double.parseDouble(StringUtils.trim(cell
						.getStringCellValue()));
			} catch (NumberFormatException pe) {
				return 0;
			}
		}
		case HSSFCell.CELL_TYPE_NUMERIC:
			return cell.getNumericCellValue();
		case HSSFCell.CELL_TYPE_FORMULA:
			return cell.getNumericCellValue();
		default:
			return 0;
		}
	}

	/**
	 * 取单元格内的日期,非日期的单元格返回null
	 */
	public static Date getDateCellValue(HSSFCell cell) {
		if (cell == null)
			return null;
		switch (cell.getCellType()) {
		case HSSFCell.CELL_TYPE_STRING: {
			try {
				DateFormat df = DateFormat.getDateInstance();
				return df.parse(cell.getStringCellValue());
			} catch (ParseException pe) {
				return null;
			}
		}
		case HSSFCell.CELL_TYPE_NUMERIC:
			if (HSSFDateUtil.isValidExcelDate(cell.getNumericCellValue()))
				return cell.getDateCellValue();
		case HSSFCell.CELL_TYPE_FORMULA:
			if (HSSFDateUtil.isValidExcelDate(cell.getNumericCellValue()))
				return cell.getDateCellValue();
		default:
			return null;
		}
	}

	/**
	 * 取单元格内的日期,非日期的单元格返回null
	 * 
	 * @throws ParseException
	 *             add by liuqi at 20110117
	 */
	public static Timestamp getTimestampFromStringCellValue(HSSFCell cell)
			throws ParseException {
		if (cell.getCellType() != HSSFCell.CELL_TYPE_STRING)
			return null;

		if (cell.getStringCellValue().equals("")
				|| cell.getStringCellValue() == null)
			return null;

		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		Date date = sdf.parse(cell.getStringCellValue());

		if (date == null)
			return null;
		else {
			Timestamp time = new Timestamp(date.getTime());
			return time;
		}
	}

	/**
	 * 取单元格内的字符串,非字符串的单元格返回""
	 */
	public static String getStringCellValue(HSSFCell cell) {
		if (cell == null)
			return "";
		switch (cell.getCellType()) {
		case HSSFCell.CELL_TYPE_BLANK:
			return "";
		case HSSFCell.CELL_TYPE_BOOLEAN:
			return cell.getBooleanCellValue() ? "TRUE" : "FALSE";
		case HSSFCell.CELL_TYPE_STRING:
			return cell.getStringCellValue();
		case HSSFCell.CELL_TYPE_NUMERIC:
			if (HSSFDateUtil.isCellDateFormatted(cell)) {
				SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
				return sdf.format(cell.getDateCellValue());
			}
			return NumberToTextConverter.toText(cell.getNumericCellValue());
		case HSSFCell.CELL_TYPE_FORMULA:
			return cell.getStringCellValue();
		default:
			return "";
		}
	}

	public static HSSFCell setCellValue(HSSFSheet sheet, int x, int y,
			String strValue) {
		HSSFRow row = sheet.getRow(x - 1);
		if (row == null)
			row = sheet.createRow(x - 1);
		HSSFCell cell = row.getCell(y - 1);
		if (cell == null)
			cell = row.createCell(y - 1);
		// if (HSSFCell.CELL_TYPE_STRING != cell.getCellType())
		// cell.setCellType(HSSFCell.CELL_TYPE_STRING);

		// cell.setEncoding(HSSFCell.ENCODING_UTF_16);
		HSSFRichTextString fts = new HSSFRichTextString(strValue);
		cell.setCellValue(fts);
		return cell;
	}

	public static HSSFCell setCellValue(HSSFSheet sheet, int x, int y,
			boolean blValue) {
		HSSFRow row = sheet.getRow(x - 1);
		if (row == null)
			row = sheet.createRow(x - 1);
		HSSFCell cell = row.getCell(y - 1);
		if (cell == null)
			cell = row.createCell(y - 1);
		// cell.setCellType(HSSFCell.CELL_TYPE_BOOLEAN);
		cell.setCellValue(blValue);
		return cell;
	}

	public static HSSFCell setCellValue(HSSFSheet sheet, int x, int y,
			int nValue) {
		HSSFRow row = sheet.getRow(x - 1);
		if (row == null)
			row = sheet.createRow(x - 1);
		HSSFCell cell = row.getCell(y - 1);
		if (cell == null)
			cell = row.createCell(y - 1);
		// cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
		cell.setCellValue(nValue);
		return cell;
	}

	public static HSSFCell setCellValue(HSSFSheet sheet, int x, int y,
			double dValue) {
		HSSFRow row = sheet.getRow(x - 1);
		if (row == null)
			row = sheet.createRow(x - 1);
		HSSFCell cell = row.getCell(y - 1);
		if (cell == null)
			cell = row.createCell(y - 1);
		// cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
		cell.setCellValue(dValue);
		return cell;
	}

	public static HSSFCell setCellValue(HSSFSheet sheet, int x, int y,
			BigDecimal bValue) {
		HSSFRow row = sheet.getRow(x - 1);
		if (row == null)
			row = sheet.createRow(x - 1);
		HSSFCell cell = row.getCell(y - 1);
		if (cell == null)
			cell = row.createCell(y - 1);
		// cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
		if (bValue != null)
			cell.setCellValue(bValue.doubleValue());
		else
			cell.setCellValue(0);
		return cell;
	}

	public static HSSFCell setCellFormula(HSSFSheet sheet, int x, int y,
			String strFormula) {
		HSSFRow row = sheet.getRow(x - 1);
		if (row == null)
			row = sheet.createRow(x - 1);
		HSSFCell cell = row.getCell(y - 1);
		if (cell == null)
			cell = row.createCell(y - 1);
		// cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
		cell.setCellFormula(strFormula);
		return cell;
	}

	public static boolean setCellStyle(HSSFCell cell, HSSFWorkbook wb,
			HSSFFont font) {
		if (cell == null)
			return false;
		HSSFCellStyle cellStyle = wb.createCellStyle();
		cellStyle.setFont(font);
		cell.setCellStyle(cellStyle);
		return true;
	}

	public static boolean setCellStyle(HSSFCell cell, HSSFWorkbook wb,
			HSSFFont font, String strFormat) {
		if (cell == null)
			return false;
		HSSFCellStyle cellStyle = wb.createCellStyle();
		cellStyle.setFont(font);
		cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat(strFormat));
		cell.setCellStyle(cellStyle);
		return true;
	}

	public static boolean setCellStyle(HSSFCell cell, HSSFWorkbook wb,
			HSSFFont font, short sTop, short sBottom, short sLeft, short sRight) {
		if (cell == null)
			return false;
		HSSFCellStyle cellStyle = wb.createCellStyle();
		cellStyle.setFont(font);
		setBordersLine(cellStyle, sTop, sBottom, sLeft, sRight);
		cell.setCellStyle(cellStyle);
		return true;
	}

	public static boolean setCellStyle(HSSFCell cell, HSSFWorkbook wb,
			HSSFFont font, String strFormat, short sTop, short sBottom,
			short sLeft, short sRight) {
		if (cell == null)
			return false;

		HSSFCellStyle cellStyle = wb.createCellStyle();
		cellStyle.setFont(font);
		cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat(strFormat));
		setBordersLine(cellStyle, sTop, sBottom, sLeft, sRight);
		cell.setCellStyle(cellStyle);
		return true;
	}

	public static boolean setCellStyle(HSSFCell cell, HSSFWorkbook wb,
			short sTop, short sBottom, short sLeft, short sRight) {
		if (cell == null)
			return false;
		HSSFCellStyle cellStyle = wb.createCellStyle();
		setBordersLine(cellStyle, sTop, sBottom, sLeft, sRight);
		cell.setCellStyle(cellStyle);
		return true;
	}

	public static void setCellFormat(HSSFCellStyle style, String strFormat) {
		if (style == null)
			return;

		style.setDataFormat(HSSFDataFormat.getBuiltinFormat(strFormat));
	}

	public static void setBordersColor(HSSFCellStyle style, short sTop,
			short sBottom, short sLeft, short sRight) {
		if (style == null)
			return;

		style.setBottomBorderColor(sBottom);
		style.setLeftBorderColor(sLeft);
		style.setRightBorderColor(sRight);
		style.setTopBorderColor(sTop);
	}

	public static void setBordersLine(HSSFCellStyle style, short sTop,
			short sBottom, short sLeft, short sRight) {
		if (style == null)
			return;

		style.setBorderBottom(sBottom);
		style.setBorderLeft(sLeft);
		style.setBorderRight(sRight);
		style.setBorderTop(sTop);
	}

	public static void setCellFont(HSSFCellStyle style, HSSFFont font) {
		if (style == null)
			return;

		style.setFont(font);
	}

	public static void setCellAlign(HSSFCell cell, short halign, short valign) {
		HSSFCellStyle cellStyle = cell.getCellStyle();
		cellStyle.setAlignment(halign);
		cellStyle.setVerticalAlignment(valign);
		cell.setCellStyle(cellStyle);
	}

	public static void setCellBkGround(HSSFCell cell, short sBkGroud) {
		HSSFCellStyle cellStyle = cell.getCellStyle();
		cellStyle.setFillForegroundColor(sBkGroud);
		cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
	}

	public static void setCellBkGround(HSSFSheet sheet, int x, int y,
			short sBkGroud) {
		HSSFRow row = sheet.getRow(x - 1);
		if (row == null)
			row = sheet.createRow(x - 1);
		HSSFCell cell = row.getCell(y - 1);
		if (cell == null)
			cell = row.createCell(y - 1);
		HSSFCellStyle cellStyle = cell.getCellStyle();
		cellStyle.setFillForegroundColor(sBkGroud);
		cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
	}

	public static void enableWraptext(HSSFCell cell) {
		HSSFCellStyle cellStyle = cell.getCellStyle();
		cellStyle.setWrapText(true);
		cell.setCellStyle(cellStyle);
	}

	public static void disableWraptext(HSSFCell cell) {
		HSSFCellStyle cellStyle = cell.getCellStyle();
		cellStyle.setWrapText(false);
		cell.setCellStyle(cellStyle);
	}

	public static void copyRows(HSSFSheet from, int fromRowStart,
			int fromRowEnd, HSSFSheet to, int toRowStart) {
		int startIndex = fromRowStart - 1;
		int endIndex = fromRowEnd - 1;
		int toIndex = toRowStart - 1;
		for (int index = startIndex; index <= endIndex; index++) {

			HSSFRow fromRow = from.getRow(index);
			HSSFRow fromTo = to.createRow(toIndex);

			copyRow(fromRow, fromTo);
			int num = from.getNumMergedRegions();
			for (int mergeIndex = 0; mergeIndex < num; mergeIndex++) {
				CellRangeAddress r = from.getMergedRegion(mergeIndex);
				if (index == r.getFirstRow()) {
					int add = toIndex - r.getFirstRow();
					CellRangeAddress r2 = new CellRangeAddress(r.getFirstRow()
							+ add, r.getFirstColumn(), r.getLastRow() + add,
							r.getLastColumn());
					to.addMergedRegion(r2);
				}

			}
			toIndex++;
		}
	}

	public static void copyRow(HSSFRow from, HSSFRow to) {
		// set Row Height
		to.setHeight(from.getHeight());
		// set cell
		for (int i = 0; i < from.getLastCellNum() && i <= 255; i++) {
			HSSFCell cell = from.getCell(i);
			if (cell == null)
				continue;
			HSSFCell cell1 = to.createCell(i);
			cell1.setCellType(cell.getCellType());
			// cell1.setCellFormula(cell.getCellFormula());
			// cell1.setCellValue(cell.getStringCellValue());
			cell1.setCellStyle(cell.getCellStyle());
			if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC)
				cell1.setCellValue(cell.getNumericCellValue());
			else if (cell.getCellType() == HSSFCell.CELL_TYPE_STRING) {
				HSSFRichTextString str = cell.getRichStringCellValue();
				if (str.length() > 0) {
					cell1.setCellValue(str);
				}
			} else if (cell.getCellType() == HSSFCell.CELL_TYPE_FORMULA)
				cell1.setCellFormula(cell.getCellFormula());
		}
	}

	public static void createimage(byte[] image, String strPicUrl) {
		try {
			FileOutputStream out = new FileOutputStream(strPicUrl);
			out.write(image);
			out.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	public static void deletefile(String path) {
		try {
			File f = new File(path);
			if (f.exists())
				f.delete();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	public static void setRowHeight(HSSFSheet sheet, int nRow, float fHeight) {
		HSSFRow row = sheet.getRow(nRow - 1);
		if (row == null)
			row = sheet.createRow(nRow - 1);
		row.setHeightInPoints(fHeight);
	}

	public static void setFooter(HSSFSheet sheet, String strFontName,
			String strFooter) {
		String strSplit = "   ";
		HSSFFooter footer = sheet.getFooter();
		footer.setCenter(HSSFFooter.font(strFontName, "Normal")
				+ HSSFFooter.fontSize((short) 8) + strSplit + strFooter);
	}

	public static void setRegionBorders(HSSFWorkbook wb, HSSFSheet sheet,
			CellRangeAddress region) {
		if (region == null)
			return;
		for (int i = region.getFirstRow(); i <= region.getLastRow(); i++) {
			HSSFRow row = sheet.getRow(i);
			if (row == null)
				row = sheet.createRow(i);
			for (int j = region.getFirstColumn(); j <= region.getLastColumn(); j++) {
				HSSFCell cell = row.getCell(j);
				if (cell == null) {
					cell = row.createCell(j);
				}
				setCellStyle(cell, wb, HSSFCellStyle.BORDER_THIN,
						HSSFCellStyle.BORDER_THIN, HSSFCellStyle.BORDER_THIN,
						HSSFCellStyle.BORDER_THIN);
			}
		}
	}

	public static void main(String[] arg) {
		try {
			// FileInputStream fIn = new FileInputStream("d:\\test.xls");
			// HSSFWorkbook wb = new HSSFWorkbook(fIn);
			// HSSFSheet st = wb.getSheetAt(0);
			// HSSFCell cell = getCell(st, 4, 1);
			// System.out.println(cell.getCellFormula());
			// System.out.println(cell.getNumericCellValue());
			// HSSFRow row = st.createRow(18);
			// cell = row.createCell(8);
			// cell.setCellFormula("SUM(I1:I18)");
			// System.out.println(cell.getNumericCellValue());
			// cell.setCellValue("=SUM(I1:I18)");

			HSSFWorkbook wb = new HSSFWorkbook();
			for (int sheetNo = 1; sheetNo < 5; sheetNo++) {
				HSSFSheet st = wb.createSheet("TheSheet" + sheetNo);
				for (int rowNo = 0; rowNo < 10000; rowNo++) {
					HSSFRow row = st.createRow(rowNo);
					for (int colNo = 0; colNo < 10; colNo++) {
						HSSFCell cell = row.createCell(colNo);
						String cellValue = "Sheet:" + sheetNo + " Row:" + rowNo
								+ " Col:" + colNo;
						cell.setCellValue(cellValue);
						System.out.println(cellValue);
					}
				}
			}

			FileOutputStream fOut;
			fOut = new FileOutputStream("d:\\test.xls");
			// 将模板的内容写到输出文件上
			wb.write(fOut);
			fOut.flush();
			// 操作结束，关闭文件
			fOut.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

}