import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.lang.reflect.Field;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
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.usermodel.Font;
import org.apache.poi.ss.usermodel.IndexedColors;
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.ss.util.WorkbookUtil;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import com.pinganfu.common.log.LOG_TYPE;
import com.pinganfu.newcard.gm.util.servlet.InitSystemServlet;
import com.pinganfu.newcard.gm.web.report.model.CellColumn;
import com.pinganfu.newcard.gm.web.report.model.ExcelColumn;
import com.pinganfu.newcard.gm.web.report.model.ExcelTitle;
import com.thoughtworks.xstream.XStream;
/**
*
*
* @author MLeo
* @version $Id: ExcelPOI.java, v 0.1 2014年8月11日 下午4:56:32 MLeo Exp $
*/
public class ExcelPOI {
private Workbook wb;
private ExcelTitle title;
private List<ExcelColumn> columns;
private List<CellStyle> contentStyles;
private Logger LOG = LoggerFactory.getLogger(LOG_TYPE.PAFF_SERVICE.val);
public void loadXml(String xml) throws FileNotFoundException{
xml = InitSystemServlet.rootPath+"WEB-INF"+File.separator+ "reportexcelxml" +File.separator+xml;
// xml = InitSystemServlet.rootPath+File.separator+"WEB-INF"+File.separator+ "reportexcelxml" +File.separator+xml;
LOG.info("xml:"+xml);
XStream stream = new XStream();
stream.alias("title", ExcelTitle.class);
stream.alias("column", ExcelColumn.class);
stream.alias("cell", CellColumn.class);
title = (ExcelTitle)stream.fromXML(new FileInputStream(xml));
columns = title.getColumns();
}
public void loadExcel(String xml) throws FileNotFoundException{
LOG.info("POIExcel:Lood");
loadXml(xml);//加载XML文档
wb = createWorkbook();//创建Workbook
//添加sheet标签
Sheet sheet = createSheet(wb, title.getTitleName());//创建标签
//创建sheet样式
CellStyle sheetStyle = wb.createCellStyle();
sheetStyle(wb, sheetStyle,title);
//添加sheet标题
Row sheetRow = createRow(sheet, 1);//创建行
for (int i = 0; i < columns.size(); i++) {
Cell sheetCell = createCell(sheetRow, i+1);//创建单元格
addStyle(sheetCell, sheetStyle);//添加样式
if(0 == i){
addValue(sheetCell, title.getTitleName());//添加单元格内容
}
}
mergeCells(sheet, 1, 1, 1, columns.size());//合并表格
//添加title标题
Row titleRow = createRow(sheet, 2);//创建行
for (int i = 0; i < columns.size(); i++) {
ExcelColumn column = columns.get(i);
Cell titleCell = createCell(titleRow, i+1);//创建单元格
CellStyle titleStyle = wb.createCellStyle();
titleStyle(wb, titleStyle,column);
addStyle(titleCell, titleStyle);//添加样式
addValue(titleCell, column.getColumnName());//添加单元格内容
columnWidth(sheet, i+1, column.getWidth()); //设置列宽
}
//创建cell样式
contentStyles = new ArrayList<CellStyle>();
for (ExcelColumn column : columns) {
CellStyle contentStyle = wb.createCellStyle();
contentStyle(wb, contentStyle, column.getCell());
contentStyles.add(contentStyle);
}
LOG.info("POIExcel:LoodEnd");
}
public Workbook createExcel(List<?> list) throws NoSuchFieldException, SecurityException, IllegalArgumentException, IllegalAccessException, FileNotFoundException {
LOG.info("POIExcel:CreateExcelRow");
//添加内容信息
Sheet sheet = wb.getSheetAt(0);//获取当前的标签对象
int rowNum = wb.getSheetAt(0).getLastRowNum();
list = list == null ? new ArrayList<Object>():list;
for (Object obj : list) {
Row contentRow = createRow(sheet, ++rowNum);//创建行
Class<?> clazz = obj.getClass();
for (int i = 0; i < columns.size(); i++) {
ExcelColumn column = columns.get(i);
CellColumn cell = column.getCell();
Cell contentCell = createCell(contentRow, i+1);//创建单元格
addStyle(contentCell, contentStyles.get(i));//添加样式
Field field = clazz.getDeclaredField(column.getField());
field.setAccessible(true);
Object fieldValue = field.get(obj);
addValue(contentCell, fieldValue,cell.getPrecision());//添加单元格内容
}
}
LOG.info("POIExcel:CreateExcelRowEnd");
return wb;
}
/**
* 单元格赋值
* @param cell
* @param value
*/
public void addValue(Cell cell,Object value,Integer precision) {
if(null == value){
return;
}else if (value instanceof String) {
cell.setCellValue((String) value);
} else if (value instanceof Date) {
cell.setCellValue((Date) value);
} else if (value instanceof Number) {
if(null != precision && 0 != precision){
cell.setCellValue(((Number) value).doubleValue()/precision);
}else{
cell.setCellValue(((Number) value).doubleValue());
}
} else if (value instanceof Boolean) {
cell.setCellValue(((Boolean) value).booleanValue());
} else {
cell.setCellValue(value.toString());
}
}
/**
* 写入工作簿
*
* @param wb
* @param path
*/
public void writeWorkbook(Workbook wb, String pathBookName) {
FileOutputStream fileOut = null;
try {
fileOut = new FileOutputStream(pathBookName);
wb.write(fileOut);
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
fileOut.close();
fileOut.flush();
} catch (IOException e) {
e.printStackTrace();
}
}
}
public CellStyle sheetStyle(Workbook wb,CellStyle style,ExcelTitle title){
Font font = wb.createFont();
if(title.getFontBlod()){
font.setBoldweight(Font.BOLDWEIGHT_BOLD);// 粗体
}else{
font.setBoldweight(Font.BOLDWEIGHT_NORMAL);// 粗体
}
if(0 != title.getFontSize()){
font.setFontHeightInPoints(title.getFontSize());// 字体大小
}else{
font.setFontHeightInPoints((short)12);// 字体大小
}
if(null != title.getFontName() && !"".equals(title.getFontName())){
font.setFontName(title.getFontName());// 字体名字
}
if("center".equals(title.getAlign())){
style.setAlignment(CellStyle.ALIGN_CENTER);
}else if("left".equals(title.getAlign())){
style.setAlignment(CellStyle.ALIGN_LEFT);
}else if("right".equals(title.getAlign())){
style.setAlignment(CellStyle.ALIGN_RIGHT);
}
if("center".equals(title.getVertical())){
style.setAlignment(CellStyle.VERTICAL_CENTER);
}else if("top".equals(title.getVertical())){
style.setAlignment(CellStyle.VERTICAL_TOP);
}else if("bottom".equals(title.getVertical())){
style.setAlignment(CellStyle.VERTICAL_BOTTOM);
}
if(title.getBorder()){
styleFrame(style);//设置边框
}
style.setFont(font);
return style;
}
public CellStyle titleStyle(Workbook wb,CellStyle style,ExcelColumn column){
Font font = wb.createFont();
if(column.getFontBlod()){
font.setBoldweight(Font.BOLDWEIGHT_BOLD);// 粗体
}else{
font.setBoldweight(Font.BOLDWEIGHT_NORMAL);// 粗体
}
if(0 != column.getFontSize()){
font.setFontHeightInPoints(column.getFontSize());// 字体大小
}else{
font.setFontHeightInPoints((short)12);// 字体大小
}
if(null != column.getFontName() && !"".equals(column.getFontName())){
font.setFontName(column.getFontName());// 字体名字
}
if("center".equals(column.getAlign())){
style.setAlignment(CellStyle.ALIGN_CENTER);
}else if("left".equals(column.getAlign())){
style.setAlignment(CellStyle.ALIGN_LEFT);
}else if("right".equals(column.getAlign())){
style.setAlignment(CellStyle.ALIGN_RIGHT);
}
if("center".equals(column.getVertical())){
style.setAlignment(CellStyle.VERTICAL_CENTER);
}else if("top".equals(column.getVertical())){
style.setAlignment(CellStyle.VERTICAL_TOP);
}else if("bottom".equals(column.getVertical())){
style.setAlignment(CellStyle.VERTICAL_BOTTOM);
}
if(column.getBorder()){
styleFrame(style);//设置边框
}
style.setFont(font);
return style;
}
public CellStyle contentStyle(Workbook wb,CellStyle style,CellColumn cell){
Font font = wb.createFont();
if(cell.getFontBlod()){
font.setBoldweight(Font.BOLDWEIGHT_BOLD);// 粗体
}else{
font.setBoldweight(Font.BOLDWEIGHT_NORMAL);// 粗体
}
if(0 != cell.getFontSize()){
font.setFontHeightInPoints(cell.getFontSize());// 字体大小
}else{
font.setFontHeightInPoints((short)12);// 字体大小
}
if(null != cell.getFontName() && !"".equals(cell.getFontName())){
font.setFontName(cell.getFontName());// 字体名字
}
if("center".equals(cell.getAlign())){
style.setAlignment(CellStyle.ALIGN_CENTER);
}else if("left".equals(cell.getAlign())){
style.setAlignment(CellStyle.ALIGN_LEFT);
}else if("right".equals(cell.getAlign())){
style.setAlignment(CellStyle.ALIGN_RIGHT);
}
if("center".equals(cell.getVertical())){
style.setAlignment(CellStyle.VERTICAL_CENTER);
}else if("top".equals(cell.getVertical())){
style.setAlignment(CellStyle.VERTICAL_TOP);
}else if("bottom".equals(cell.getVertical())){
style.setAlignment(CellStyle.VERTICAL_BOTTOM);
}
if(null != cell.getFormat() && !"".equals(cell.getFormat())){
DataFormat format = wb.createDataFormat();
style.setDataFormat(format.getFormat(cell.getFormat()));
}
if(cell.getBorder()){
styleFrame(style);//设置边框
}
style.setFont(font);
return style;
}
/**
* 创建工作簿
*
* @return Workbook
*/
public Workbook createWorkbook() {
Workbook wb = new XSSFWorkbook();
return wb;
}
/**
* 创建工作表
*
* @param sheetNames
* @param wb
*/
public Sheet createSheet(Workbook wb, String sheetName) {
String safeName = WorkbookUtil.createSafeSheetName(sheetName); // 一个安全的方式来创建有效的名称,这个工具替换无效字符用空格('')
Sheet sheet = wb.createSheet(safeName);
return sheet;
}
/**
* 创建工作表
*
* @param sheetNames
* @param wb
*/
public List<Sheet> createSheets(Workbook wb, List<String> sheetNames) {
List<Sheet> sheets = new ArrayList<Sheet>();
for (String sheetName : sheetNames) {
// 一个安全的方式来创建有效的名称,这个工具替换无效字符用空格('')
String safeName = WorkbookUtil.createSafeSheetName(sheetName);
Sheet sheet = wb.createSheet(safeName);
sheets.add(sheet);
}
return sheets;
}
/**
* 创建行
*
* @param sheet
* @param rowIndex
* @return
*/
public Row createRow(Sheet sheet, int rowIndex) {
Row row = sheet.createRow(rowIndex);
return row;
}
/**
* 创建单元格
*
* @param row
* @param cellIndex
* @return Cell
*/
public Cell createCell(Row row, int cellIndex) {
Cell cell = row.createCell(cellIndex);
return cell;
}
/**
* 单元格赋值
*
* @param cell
* @param cellValue Double
* @return Cell
*/
public Cell addValue(Cell cell, Double cellValue) {
cell.setCellValue(cellValue);
return cell;
}
/**
* 单元格赋值
*
* @param cell
* @param cellValue Date
* @return Cell
*/
public Cell addValue(Cell cell, Date cellValue) {
cell.setCellValue(cellValue);
return cell;
}
/**
* 单元格赋值
*
* @param cell
* @param cellValue Boolean
* @return Cell
*/
public Cell addValue(Cell cell, Boolean cellValue) {
cell.setCellValue(cellValue);
return cell;
}
/**
* 单元格赋值
*
* @param cell
* @param cellValue String
* @return Cell
*/
public Cell addValue(Cell cell,String cellValue) {
cell.setCellValue(cellValue);
return cell;
}
/**
* 添加样试
*
* @param cell
* @param style
* @return
*/
public Cell addStyle(Cell cell, CellStyle style) {
cell.setCellStyle(style);
return cell;
}
/**
* 设置字体颜色
*
* @param wb
* @param style
* @param fontBlod 字体粗细
* @param fontColor 字体颜色
* @param fontHeight 字体大小
* @param fontName 字体名字
* @return
*/
public CellStyle styleFont(Workbook wb, CellStyle style, short fontBlod, short fontColor, short fontHeight, String fontName) {
Font font = wb.createFont();
font.setBoldweight(fontBlod);// 粗体
font.setColor(fontColor);// 字体颜色
font.setFontHeightInPoints(fontHeight);// 字体大小
font.setFontName(fontName);// 字体名字
style.setFont(font);
return style;
}
/**
* 设置字体颜色
*
* @param wb
* @param style
* @param fontBlod 字体粗细
* @param fontColor 字体颜色
* @param fontHeight 字体大小
* @return
*/
public CellStyle styleFont(Workbook wb, CellStyle style, short fontBlod, short fontColor, short fontHeight) {
Font font = wb.createFont();
font.setBoldweight(fontBlod);// 粗体
font.setColor(fontColor);// 字体颜色
font.setFontHeightInPoints(fontHeight);// 字体大小
style.setFont(font);
return style;
}
/**
* 设置背景色
*
* @param style
* @param foregroundCorlor
* @return CellStyle
*/
public CellStyle styleForegroundColor(CellStyle style,short foregroundCorlor) {
style.setFillForegroundColor(foregroundCorlor);
style.setFillPattern(CellStyle.SOLID_FOREGROUND);
return style;
}
/**
* 设置格式
*
* @param wb
* @param style
* @param dataFormat 格式
* @return CellStyle
*/
public CellStyle styleDataFormat(Workbook wb,CellStyle style,String dataFormat) {
DataFormat format = wb.createDataFormat();
style.setDataFormat(format.getFormat(dataFormat));
return style;
}
/**
* 对齐方式
*
* @param style
* @param halign
* @param valign
* @return CellStyle
*/
public CellStyle styleAlignment(CellStyle style, short halign,short valign) {
style.setAlignment(halign);
style.setVerticalAlignment(valign);
return style;
}
/**
* 单元格边框
* @param style
* @return CellStyle
*/
public CellStyle styleFrame(CellStyle style) {
style.setBorderBottom(CellStyle.BORDER_THIN);
style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
style.setBorderLeft(CellStyle.BORDER_THIN);
style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
style.setBorderRight(CellStyle.BORDER_THIN);
style.setRightBorderColor(IndexedColors.BLACK.getIndex());
style.setBorderTop(CellStyle.BORDER_THIN);
style.setTopBorderColor(IndexedColors.BLACK.getIndex());
return style;
}
/**
* 合并单元格
* @param sheet
* @param firstRow 开始行
* @param lastRow 最后行
* @param firstColumn 开始列
* @param lastColumn 最后列
*/
public void mergeCells(Sheet sheet,int firstRow,int lastRow,int firstColumn,int lastColumn) {
sheet.addMergedRegion(new CellRangeAddress(firstRow, lastRow, firstColumn, lastColumn));
}
/**
* 设置列宽度
* @param sheet
* @param columnIndex 列数
* @param columnWidth 列宽
*/
public void columnWidth(Sheet sheet,Integer columnIndex,Integer columnWidth) {
if(null != columnWidth && 0 != columnWidth){
sheet.setColumnWidth(columnIndex, columnWidth);
}else{
sheet.setColumnWidth(columnIndex, 500*columnWidth+1000);
}
}
}
转载于:https://my.oschina.net/mleo/blog/177524