POI操作excel

package com.jxtech.base.util;

/**
* jww
*/
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.Date;
import java.util.List;

import org.apache.log4j.Logger;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.DataFormat;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFFont;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

/**
*
* @author Administrator
*/
public class ExcelUtil {
private static final Logger logger=Logger.getLogger(ExcelUtil.class);

/**
* 给行设置值
* @param workBook
* @param sheet
* @param sheetRow
* @param rowid
* @param values
*/
public static void setSheetRowValue(XSSFWorkbook workBook,XSSFSheet sheet, XSSFRow sheetRow, int rowid, String... values) {
sheetRow = sheet.createRow(rowid);
CellStyle style = workBook.createCellStyle();
DataFormat format = workBook.createDataFormat();
style.setDataFormat(format.getFormat("@"));
style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
for (int i = 0; i < values.length; i++) {
Cell cell = sheetRow.createCell(i);
cell.setCellStyle(style);
cell.setCellType(Cell.CELL_TYPE_STRING);
cell.setCellValue(values[i]);
}
}

/**
* 给行设置值
*
* @param sheet
* @param rowid
* @param values
*/
public static void setSheetRowValue(XSSFSheet sheet, int rowid, String[] values) {
XSSFRow sheetRow = sheet.createRow(rowid);
for (int i = 0; i < values.length; i++) {
sheetRow.createCell(i).setCellValue(values[i]);
}
}

/**
* 设置列宽为自动扩展
*
* @param sheet
* @param rownum
*/
public static void setSheetColWidthAuto(XSSFSheet sheet, int rownum) {
for (int i = 0; i <= rownum; i++) {
sheet.autoSizeColumn(i);
}
}

/**
* 根据给出的字符串设置指定列宽
* @param sheet
* @param columnIndex 列索引
* @param content
*/
public static void setSheetWidthSpecify(XSSFSheet sheet,int columnIndex ,String content){
if (content == null || content.length() < 5 ) { //如果内容为空或者长度小于5则不设置，使用Excel默认列宽
return;
}
int maxWidth = 255 << 8;
int width = (content.length() << 9) > maxWidth ? maxWidth : (content.length() << 9);
if (sheet.getColumnWidth(columnIndex) > width) { //始终保持最大列宽
return ;
}
sheet.setColumnWidth(columnIndex, width);
}

/**
* 单元格
*
* @param sheet
* @param rs
* @param re
* @param cs
* @param ce
*/
public static void mergedRegion(XSSFSheet sheet, int rs, int re, int cs, int ce) {
sheet.addMergedRegion(new CellRangeAddress(rs, re, cs, ce));
}

/**
*
* @param sheet
* @param rs 起始行数
* @param re 终止行数
* @param colNum 制定列
*/
public static void mergedColRegion(XSSFSheet sheet, int rs, int re, int colNum) {
mergedRegion(sheet, rs, re, colNum, colNum);
}

/**
* 合并某一行单元格
*
* @param sheet
* @param startRowNum 起始行索引
* @param numList
* @param colNum 列索引
*/
public static void mergedColsRegion(XSSFSheet sheet, int startRowNum, List<Integer> numList, int colNum) {
int endRowNum = startRowNum;
for (Integer i : numList) {
startRowNum = endRowNum;
endRowNum = endRowNum + i;
mergedColRegion(sheet, startRowNum, endRowNum - 1, colNum);
}
}

/**
* 合并单元格
*
* @param sheet
* @param startRowNum 起始行索引
* @param numList
* @param colNum 列索引
*/
public static void addMergedRegion(XSSFSheet sheet, int startRow, List<Integer> endRowList, int colNum) {
int endRow = 0;
for (Integer n : endRowList) {
endRow = n;
sheet.addMergedRegion(new CellRangeAddress(startRow, endRow, colNum, colNum));
startRow = endRow + 1;
}
}

public static XSSFCellStyle getLinkCellStyle(XSSFWorkbook workBook) {
XSSFCellStyle linkStyle = workBook.createCellStyle();
XSSFFont cellFont = workBook.createFont();
cellFont.setUnderline((byte) 1);
cellFont.setColor(HSSFColor.BLUE.index);
linkStyle.setFont(cellFont);
return linkStyle;
}

/**
* 获取字符串类型数据
* @param cell
* @return
*/
public static String getStringCellValue(Cell cell) {
String strCell = "";
if(cell==null){
return null;
}
switch (cell.getCellType()) {
case HSSFCell.CELL_TYPE_STRING:
strCell = cell.getStringCellValue();
break;
case HSSFCell.CELL_TYPE_NUMERIC:
strCell = String.valueOf(cell.getNumericCellValue());
break;
case HSSFCell.CELL_TYPE_BOOLEAN:
strCell = String.valueOf(cell.getBooleanCellValue());
break;
case HSSFCell.CELL_TYPE_BLANK:
strCell = "";
break;
default:
strCell = "";
break;
}
if (strCell.equals("") || strCell == null) {
return "";
}
return strCell;
}

/**
* 获取单元格数据内容为日期类型的数据
*
* @param cell Excel单元格
* @return String 单元格数据内容
*/
public static String getDateCellValue(HSSFCell cell) {
String result = "";
try {
int cellType = cell.getCellType();
if (cellType == HSSFCell.CELL_TYPE_NUMERIC) {
Date date = cell.getDateCellValue();
Calendar calendar = Calendar.getInstance();
calendar.setTime(date);
result = (calendar.get(Calendar.YEAR) + 1900) + "-" + (calendar.get(Calendar.MONTH)+ 1) + "-" + calendar.get(Calendar.DATE);
} else if (cellType == HSSFCell.CELL_TYPE_STRING) {
String date = getStringCellValue(cell);
result = date.replaceAll("[年月]", "-").replace("日", "").trim();
} else if (cellType == HSSFCell.CELL_TYPE_BLANK) {
result = "";
}
} catch (Exception e) {
logger.error(e.getMessage());
}
return result;
}

/**
* 根据HSSFCell类型设置数据
*
* @param cell
* @return
*/
public static String getCellFormatValue(HSSFCell cell) {
String cellvalue = "";
if (cell != null) {
// 判断当前Cell的Type
switch (cell.getCellType()) {
// 如果当前Cell的Type为NUMERIC
case HSSFCell.CELL_TYPE_NUMERIC:
case HSSFCell.CELL_TYPE_FORMULA: {
// 判断当前的cell是否为Date
if (HSSFDateUtil.isCellDateFormatted(cell)) {
// 如果是Date类型则，转化为Data格式
// 方法1：这样子的data格式是带时分秒的：2011-10-12 0:00:00
// cellvalue = cell.getDateCellValue().toLocaleString();
// 方法2：这样子的data格式是不带带时分秒的：2011-10-12
Date date = cell.getDateCellValue();
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
cellvalue = sdf.format(date);

}
// 如果是纯数字
else {
// 取得当前Cell的数值
cellvalue = String.valueOf(cell.getNumericCellValue());
}
break;
}
// 如果当前Cell的Type为STRIN
case HSSFCell.CELL_TYPE_STRING:
// 取得当前的Cell字符串
cellvalue = cell.getRichStringCellValue().getString();
break;
// 默认的Cell值
default:
cellvalue = " ";
}
} else {
cellvalue = "";
}
return cellvalue;

}

/**
* @param args
*/
public static void main(String[] args) {
/*
* Workbook workBook = new XSSFWorkbook(); Sheet sheet = workBook.createSheet("设备面板"); Row row = sheet.createRow(0); row.createCell(0).setCellValue("C"); String[] values = { "A", "B", "C", "D" }; XSSFDataValidationConstraint constraint = new XSSFDataValidationConstraint(values); CellRangeAddressList regions = new CellRangeAddressList(0, 0, 0, 0); DataValidationHelper help = new
* XSSFDataValidationHelper((XSSFSheet) sheet); DataValidation validation = help.createValidation(constraint, regions); validation.createErrorBox("输入值有误", "请从下拉框中选择"); validation.setShowErrorBox(true); sheet.addValidationData(validation); row.createCell(1).setCellValue(3); row.createCell(1).setCellType(Cell.CELL_TYPE_NUMERIC); DataValidationConstraint constrain = new
* XSSFDataValidationConstraint(DataValidationConstraint.ValidationType.INTEGER, DataValidationConstraint.OperatorType.BETWEEN, "1", "10"); CellRangeAddressList region = new CellRangeAddressList(0, 0, 1, 1); DataValidation vali = help.createValidation(constrain, region); vali.createErrorBox("输入值类型或大小有误", "数值型，请输入1~10之间的数值"); validation.setShowErrorBox(true); sheet.addValidationData(vali);
* Sheet sheet1 = workBook.createSheet("基本信息"); try { workBook.write(new FileOutputStream("F:/zjc.xlsx")); } catch (FileNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); }
*/
}
}