转载
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
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.Font;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
/**
*
*/
public class ExcelExportUtil {
/**
* <p>
* 设置报表名称及样式
* </p>
*
* @param titleName
* @param sheet
* @param length
*/
public static void outputTitle(String titleName, Sheet sheet, int length) {
sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, length));
Row row = sheet.createRow(0);
// 行高
row.setHeightInPoints(40);
Cell cell = row.createCell(0);
cell.setCellValue(titleName);
Workbook workbook = sheet.getWorkbook();
// 居中对齐
CellStyle centerHr = workbook.createCellStyle();
centerHr.setVerticalAlignment(VerticalAlignment.CENTER);
centerHr.setAlignment(HorizontalAlignment.CENTER);
cell.setCellStyle(centerHr);
// 设置字体
Font f = workbook.createFont();
f.setBold(true);
f.setFontHeightInPoints((short) 16);
cell.getCellStyle().setFont(f);
}
/**
* <p>
* 设置表头信息
* </p>
*
* @param headersInfo
* @param sheet
*/
public static void outputHeaders(String[] headersInfo, Sheet sheet) {
Row row = sheet.createRow(1);
row.setHeightInPoints(32);
// 冻结行
sheet.createFreezePane(0, row.getRowNum() + 1);
Workbook workbook = sheet.getWorkbook();
// 中文行样式
CellStyle chinaRowFontStyle = workbook.createCellStyle();
chinaRowFontStyle = workbook.createCellStyle();
chinaRowFontStyle.setVerticalAlignment(VerticalAlignment.CENTER);
chinaRowFontStyle.setAlignment(HorizontalAlignment.CENTER);
chinaRowFontStyle.setVerticalAlignment(VerticalAlignment.CENTER);
chinaRowFontStyle.setAlignment(HorizontalAlignment.CENTER);
// 字体加粗
Font f1 = workbook.createFont();
f1.setBold(true);
f1.setFontHeightInPoints((short) 10);
chinaRowFontStyle.setFont(f1);
for (int i = 0; i < headersInfo.length; i++) {
sheet.setColumnWidth(i, 4000);
Cell cell = row.createCell(i);
cell.setCellValue(headersInfo[i]);
cell.setCellStyle(chinaRowFontStyle);
}
}
/**
* <p>
* 填充数据
* </p>
*
* @param headersInfo
* @param columnsInfo
* @param sheet
* @param rowIndex
*/
public static void outputColumns(String[] headersInfo, @SuppressWarnings("rawtypes") List columnsInfo, Sheet sheet,
int rowIndex) {
Row row;
@SuppressWarnings("unused")
int headerSize = headersInfo.length;
Workbook workbook = sheet.getWorkbook();
CellStyle defaultStyle = workbook.createCellStyle();
defaultStyle.setVerticalAlignment(VerticalAlignment.CENTER);
defaultStyle.setAlignment(HorizontalAlignment.CENTER);
Font f1 = workbook.createFont();
f1.setFontHeightInPoints((short) 10);
defaultStyle.setFont(f1);
for (int i = 0; i < columnsInfo.size(); i++) {
row = sheet.createRow(rowIndex + i);
Object obj = columnsInfo.get(i);
for (int j = 0; j < headersInfo.length; j++) {
Object value = getFieldValueByName(headersInfo[j], obj);
Cell cell = row.createCell(j);
if (value != null) {
if (value instanceof Date) {
// 日期类型
DateFormat format = new SimpleDateFormat("yyyy-MM-dd");
cell.setCellValue(format.format(value));
} else {
cell.setCellValue(value.toString());
}
} else {
cell.setCellValue("");
}
cell.setCellStyle(defaultStyle);
}
}
}
/**
* <p>
* 取值
* </p>
*
* @param fieldName
* @param obj
* @return
*/
private static Object getFieldValueByName(String fieldName, Object obj) {
String firstLetter = fieldName.substring(0, 1).toUpperCase();
String getter = "get" + firstLetter + fieldName.substring(1);
try {
Method method = obj.getClass().getMethod(getter, new Class[] {});
Object value = method.invoke(obj, new Object[] {});
return value;
} catch (NoSuchMethodException e) {
e.printStackTrace();
} catch (IllegalAccessException e) {
e.printStackTrace();
} catch (InvocationTargetException e) {
e.printStackTrace();
}
return null;
}
/**
* <p>
* 设置表头信息
* </p>
*
* @param headersInfo
* @param sheet
*/
public static void outputFirstHeaders(String[] headersInfo, Sheet sheet) {
Row row = sheet.createRow(0);
row.setHeightInPoints(32);
sheet.createFreezePane(0, row.getRowNum() + 1);
Workbook workbook = sheet.getWorkbook();
// 中文行样式
CellStyle chinaRowFontStyle = workbook.createCellStyle();
chinaRowFontStyle = workbook.createCellStyle();
chinaRowFontStyle.setVerticalAlignment(VerticalAlignment.CENTER);
chinaRowFontStyle.setAlignment(HorizontalAlignment.CENTER);
chinaRowFontStyle.setVerticalAlignment(VerticalAlignment.CENTER);
chinaRowFontStyle.setAlignment(HorizontalAlignment.CENTER);
// 字体加粗
Font f1 = workbook.createFont();
f1.setBold(true);
f1.setFontHeightInPoints((short) 10);
chinaRowFontStyle.setFont(f1);
for (int i = 0; i < headersInfo.length; i++) {
sheet.setColumnWidth(i, 4000);
Cell cell = row.createCell(i);
cell.setCellValue(headersInfo[i]);
cell.setCellStyle(chinaRowFontStyle);
}
}
/**
* <p>
* 设置表头信息
* </p>
*
* @param headersInfo
* @param sheet
*/
public static void outputSecondHeaders(String[] headersInfo, Sheet sheet) {
Row row = sheet.createRow(1);
row.setHeightInPoints(32);
// 冻结行
sheet.createFreezePane(0, row.getRowNum() + 2);
Workbook workbook = sheet.getWorkbook();
// 中文行样式
CellStyle chinaRowFontStyle = workbook.createCellStyle();
chinaRowFontStyle = workbook.createCellStyle();
chinaRowFontStyle.setVerticalAlignment(VerticalAlignment.CENTER);
chinaRowFontStyle.setAlignment(HorizontalAlignment.CENTER);
chinaRowFontStyle.setVerticalAlignment(VerticalAlignment.CENTER);
chinaRowFontStyle.setAlignment(HorizontalAlignment.CENTER);
// 字体加粗
Font f1 = workbook.createFont();
f1.setBold(true);
f1.setFontHeightInPoints((short) 10);
chinaRowFontStyle.setFont(f1);
for (int i = 0; i < headersInfo.length; i++) {
sheet.setColumnWidth(i, 4000);
Cell cell = row.createCell(i);
cell.setCellValue(headersInfo[i]);
cell.setCellStyle(chinaRowFontStyle);
}
}
}