1、用HSSFWorkbook打开或者创建“Excel文件对象”
2、用HSSFWorkbook对象返回或者创建Sheet对象
3、用Sheet对象返回行对象，用行对象得到Cell对象
4、对Cell对象读写。
例子：
//创建HSSFWorkbook对象
HSSFWorkbook wb =
new
HSSFWorkbook();
//创建HSSFSheet对象
HSSFSheet sheet = wb.createSheet(
"sheet0"
);
//创建HSSFRow对象
HSSFRow row = sheet.createRow(
0
);
//创建HSSFCell对象
HSSFCell cell=row.createCell(
0
);
//设置单元格的值
cell.setCellValue(
"单元格中的中文"
);
//输出Excel文件
FileOutputStream output=
new
FileOutputStream(
"d:\\workbook.xls"
wkb.write(output);
output.flush();
package com.sesxh.hsmmp.common.util;
import java.io.File;
import java.io.FileInputStream;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.util.Date;
import java.util.List;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
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.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFDataFormat;
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;
import com.sesxh.common.exception.BusinessException;
/**
* @Description: Excel操作类
*/
public class ToExcelUtil {
/**
* @Description: 使用反射将List存储到Excel中，仅支持.xls（即excel2003之前的版本）
* @param list
* @param strings
* @return
* @throws Exception
* @return HSSFWorkbook
* @author sunmd
* @date 2017年1月4日 上午11:20:32
*/
public static HSSFWorkbook listToExcel(List<?> list, String... strings)
throws Exception {
if (list.size() <= 0) {
throw new BusinessException("数据不能为空");
}
HSSFWorkbook excel = new HSSFWorkbook();
HSSFSheet sheet = excel.createSheet();
/*
* 设置第一列需要展示的东西
*/
int cellLoc = 0; // 因为serialVersionUID的存在会打乱row.createCell的顺序，所以设置这个变量
HSSFRow row = sheet.createRow(0);
if (strings != null && strings.length > 0) {
for (int i = 0; i < strings.length; ++i) {
HSSFCell cell = row.createCell(i);
cell.setCellValue(strings[i]);
}
} else {
Object object = list.get(0);
Class<?> class1 = object.getClass();
Field[] fields = class1.getDeclaredFields();
for (int i = 0; i < fields.length; ++i) {
Field field = fields[i];
String fname = field.getName();
if (fname.equals("serialVersionUID")) {
continue;
}
HSSFCell cell = row.createCell(cellLoc++);
cell.setCellValue(fname);
}
}
/*
* 组装Excel表格
*/
for (int i = 0; i < list.size(); ++i) {
Object object = list.get(i);
Class<?> class1 = object.getClass();
Field[] fields = class1.getDeclaredFields();
HSSFRow row2 = sheet.createRow(i + 1);
cellLoc = 0;
for (int k = 0; k < fields.length; ++k) {
String fname = fields[k].getName();
if (fname.equals("serialVersionUID")) {
continue;
}
Method method = class1.getMethod("get" + change(fname));
Object result = method.invoke(object);
HSSFCell cell = row2.createCell(cellLoc++);
if (result instanceof Integer) {
cell.setCellValue((Integer) result);
} else if (result instanceof Date) {
cell.setCellValue((Date) result);
} else {
cell.setCellValue((String) result);
}
}
}
return excel;
}
/**
* @Description: 支持.xlsx（即excel2007后的文件）
* @param list
* @param strings
* @return
* @throws Exception
* @return XSSFWorkbook
*
*
*/
public static XSSFWorkbook listToExcelBetter(List<?> list,
String... strings) throws Exception {
if (list.size() <= 0) {
throw new BusinessException("数据不能为空");
}
XSSFWorkbook excel = new XSSFWorkbook();
XSSFSheet sheet = excel.createSheet();
//设置默认的行高和列宽
//sheet.setDefaultColumnWidth(40);
//sheet.setDefaultRowHeightInPoints(20);
/*
* 设置第一列需要展示的东西
*/
int cellLoc = 0; // 因为serialVersionUID的存在会打乱row.createCell的顺序，所以设置这个变量
XSSFRow row = sheet.createRow(0);
if (strings != null && strings.length > 0) {
for (int i = 0; i < strings.length; ++i) {
sheet.setColumnWidth(i, 20 * 256);
XSSFCell cell = row.createCell(i);
//设置样式
XSSFCellStyle xSSFCellStyle= excel.createCellStyle();
xSSFCellStyle.setAlignment(HorizontalAlignment.LEFT);//水平居左
xSSFCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中
// 生成一个字体
XSSFFont font = excel.createFont();
font.setFontHeightInPoints((short) 15);
font.setColor(HSSFColor.BLACK.index);
font.setBold(true);
font.setFontName("宋体");
// 把字体 应用到当前样式
xSSFCellStyle.setFont(font);
cell.setCellStyle(xSSFCellStyle);
cell.setCellValue(strings[i]);
}
} else {
Object object = list.get(0);
Class<?> class1 = object.getClass();
Field[] fields = class1.getDeclaredFields();
for (int i = 0; i < fields.length; ++i) {
Field field = fields[i];
String fname = field.getName();
if (fname.equals("serialVersionUID")) {
continue;
}
XSSFCell cell = row.createCell(cellLoc++);
//设置样式
XSSFCellStyle xSSFCellStyle= excel.createCellStyle();
xSSFCellStyle.setAlignment(HorizontalAlignment.LEFT);//水平居左
xSSFCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中
// 生成一个字体
XSSFFont font = excel.createFont();
font.setFontHeightInPoints((short) 15);
font.setColor(HSSFColor.BLACK.index);
font.setBold(true);
font.setFontName("宋体");
// 把字体 应用到当前样式
xSSFCellStyle.setFont(font);
cell.setCellValue(fname);
}
}
/*
* 组装Excel表格
*/
for (int i = 0; i < list.size(); ++i) {
Object object = list.get(i);
Class<?> class1 = object.getClass();
Field[] fields = class1.getDeclaredFields();
XSSFRow row2 = sheet.createRow(i + 1);
cellLoc = 0;
for (int k = 0; k < fields.length; ++k) {
String fname = fields[k].getName();
if (fname.equals("serialVersionUID")) {
continue;
}
Method method = class1.getMethod("get" + change(fname));
Object result = method.invoke(object);
XSSFCell cell = row2.createCell(cellLoc++);
XSSFCellStyle cellstyle = excel.createCellStyle();
//时间操作类
XSSFDataFormat format= excel.createDataFormat();
//设置样式
cellstyle.setAlignment(HorizontalAlignment.LEFT);//水平居左
cellstyle.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中
if (result instanceof Integer) {
cell.setCellStyle(cellstyle);
cell.setCellValue((Integer) result);
} else if (result instanceof Date) {
cellstyle.setDataFormat(format.getFormat("yyyy-MM-dd HH:mm:ss"));;
cell.setCellStyle(cellstyle);
cell.setCellValue((Date) result);
} else {
cell.setCellStyle(cellstyle);
cell.setCellValue((String) result);
}
}
}
return excel;
}
/**
* @Description: 根据模版生成Excel文件（.xls）
* @param list
* @param file
* @param startRow
* @return
* @throws Exception
* @return HSSFWorkbook
*
*
*/
@SuppressWarnings("resource")
public static XSSFWorkbook toExcelByTemplate(List<?> list, File templateFile,
int startRow, String time, String author) throws Exception {
FileInputStream fileInputStream = new FileInputStream(templateFile);
XSSFWorkbook xssfWorkbook = new XSSFWorkbook(fileInputStream);
XSSFCell cell = null;
xssfWorkbook = new ToExcelUtil().setupStyle(xssfWorkbook, time, author);
XSSFSheet sheet = xssfWorkbook.getSheetAt(0);
int cellLoc;
for (int i = 0; i < list.size(); ++i) {
Object object = list.get(i);
Class<?> class1 = object.getClass();
Field[] fields = class1.getDeclaredFields();
XSSFRow row2 = sheet.createRow(startRow++);
cellLoc = 0;
for (int k = 0; k < fields.length; ++k) {
String fname = fields[k].getName();
if (fname.equals("serialVersionUID")) {
continue;
}
Method method = class1.getMethod("get" + change(fname));
Object result = method.invoke(object);
cell = row2.createCell(cellLoc++);
if (result instanceof Integer) {
cell.setCellValue((Integer) result);
} else if (result instanceof Date) {
cell.setCellValue((Date) result);
} else {
cell.setCellValue((String) result);
}
}
}
return xssfWorkbook;
}
/**
* @Description: 将字符串的第一个字母转变成大写
* @param string
* @return
* @return String
*
*
*/
public static String change(String string) {
if (string == null || string.length() <= 0) {
return null;
} else {
char temp = Character.toUpperCase(string.charAt(0));
String string2 = temp + string.substring(1);
return string2;
}
}
/**
* @Description: 设置报表格式
* @param xssfWorkbook
* @param time
* @param author
* @return
* @return XSSFWorkbook
*
*
*/
public XSSFWorkbook setupStyle(XSSFWorkbook xssfWorkbook, String time, String author) {
XSSFSheet sheet = xssfWorkbook.getSheetAt(0);
XSSFRow row = sheet.createRow(3);
XSSFFont font = xssfWorkbook.createFont();
font.setBold(true);
XSSFCell cell = row.createCell(0);
cell.setCellValue(time);
XSSFCellStyle cellStyle = xssfWorkbook.createCellStyle();
cellStyle.setFont(font);
cellStyle.setAlignment(HorizontalAlignment.LEFT);
cell.setCellStyle(cellStyle);
cell = row.createCell(3);
cell.setCellValue(author);
XSSFCellStyle cellStyle2 = xssfWorkbook.createCellStyle();
cellStyle2.setFont(font);
cellStyle2.setAlignment(HorizontalAlignment.RIGHT);
cell.setCellStyle(cellStyle2);
CellRangeAddress cellRangeAddress = new CellRangeAddress(3, 3, 0, 2);
sheet.addMergedRegion(cellRangeAddress);
cellRangeAddress = new CellRangeAddress(3, 3, 3, 6);
sheet.addMergedRegion(cellRangeAddress);
return xssfWorkbook;
}
/**
* @Description:生成带样式的Excel
* @param title:标题
* @param titles:第一行中文列
* @param list:数据集合
* @param sheettitle:左下角sheet页的名字
* @param rowheight:默认行高
* @param columnwidth:默认列宽
* @return
*/
@SuppressWarnings("deprecation")
public static HSSFWorkbook getstyleExcel(String title,String[] titles,List<?> list,String sheettitle,int rowheight,int columnwidth,String[] zj,String[] jlr)throws Exception{
//创建HSSFWorkbook对象，即生成一个Eecel文档
HSSFWorkbook excel = new HSSFWorkbook();
//创建sheet页,可以创建多个sheet页
HSSFSheet sheet = excel.createSheet(sheettitle);
//设置默认的行高和列宽
sheet.setDefaultRowHeightInPoints(rowheight);
sheet.setDefaultColumnWidth(columnwidth);
//数据行开始的行数
int index=0;
//生成标题
if(!"".equals(title)){
//生成标题
getTitleRow(excel,sheet,title,titles.length);
//生成第一行
getRowOne(excel,sheet,titles,list,1);
index=2;
}else{
//生成第一行
getRowOne(excel,sheet,titles,list,0);
index=1;
}
//创建数据行
for (int i = 0; i < list.size(); ++i) {
Object object = list.get(i);
Class<?> class1 = object.getClass();
Field[] fields = class1.getDeclaredFields();
HSSFRow row2 = sheet.createRow(i + index);
//设置数据展示行的行高
row2.setHeightInPoints(20);
int cellLoc = 0;
for (int k = 0; k < fields.length; ++k) {
String fname = fields[k].getName();
if (fname.equals("serialVersionUID")) {
continue;
}
Method method = class1.getMethod("get" + change(fname));
Object result = method.invoke(object);
HSSFCell cell = row2.createCell(cellLoc++);
HSSFCellStyle cellstyle = excel.createCellStyle();
//设置样式
cellstyle.setAlignment(HorizontalAlignment.LEFT);//水平居左
cellstyle.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中
//设置边框
cellstyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框
cellstyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
cellstyle.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
cellstyle.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
cell.setCellStyle(cellstyle);
if (result instanceof Integer) {
cell.setCellValue((Integer) result);
} else if (result instanceof Date) {
cell.setCellValue((Date) result);
} else {
cell.setCellValue((String) result);
}
}
}
//创造总计行和净利润行
if(zj.length>0){
HSSFRow rowsum = sheet.createRow(list.size()+2);
//设置数据展示行的行高
rowsum.setHeightInPoints(20);
for(int s=0;s<zj.length;s++){
HSSFCell cell = rowsum.createCell(s);
HSSFCellStyle cellstyle = excel.createCellStyle();
//设置样式
cellstyle.setAlignment(HorizontalAlignment.LEFT);//水平居左
cellstyle.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中
// cellstyle.setFillBackgroundColor(HSSFColor.YELLOW.index);
// cellstyle.setFillForegroundColor(HSSFColor.YELLOW.index);
// cellstyle.setFillPattern(HSSFCellStyle.SPARSE_DOTS);
//设置边框
cellstyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框
cellstyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
cellstyle.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
cellstyle.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
cell.setCellStyle(cellstyle);
cell.setCellValue(zj[s]);
}
}
if(jlr.length>0){
HSSFRow rowsumjlr = sheet.createRow(list.size()+3);
//设置数据展示行的行高
rowsumjlr.setHeightInPoints(20);
for(int j=0;j<jlr.length;j++){
HSSFCell cell = rowsumjlr.createCell(j);
HSSFCellStyle cellstyle = excel.createCellStyle();
//设置样式
cellstyle.setAlignment(HorizontalAlignment.LEFT);//水平居左
cellstyle.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中
// cellstyle.setFillBackgroundColor(HSSFColor.YELLOW.index);
// cellstyle.setFillForegroundColor(HSSFColor.YELLOW.index);
// cellstyle.setFillPattern(HSSFCellStyle.SPARSE_DOTS);
//设置边框
cellstyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框
cellstyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
cellstyle.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
cellstyle.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
cell.setCellStyle(cellstyle);
cell.setCellValue(jlr[j]);
}
}
return excel;
}
/**
* @Description:标题行
* @param excel
* @param sheet
* @param title
* @param sum：合并的列数
* @return
*/
@SuppressWarnings("deprecation")
public static void getTitleRow(HSSFWorkbook excel,HSSFSheet sheet ,String title,int sum){
//标题行
HSSFRow row = sheet.createRow(0);
row.setHeightInPoints(40);
//单元格的样式
HSSFCellStyle _0CellStyle = excel.createCellStyle();
_0CellStyle.setAlignment(HorizontalAlignment.CENTER);//水平居中
_0CellStyle.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中
//设置背景色
_0CellStyle.setFillBackgroundColor(HSSFColor.LIGHT_BLUE.index);
_0CellStyle.setFillForegroundColor(HSSFColor.LIGHT_BLUE.index);
_0CellStyle.setFillPattern(HSSFCellStyle.SPARSE_DOTS);
//设置边框
_0CellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框
_0CellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
_0CellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
_0CellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
// 生成一个字体
HSSFFont font = excel.createFont();
font.setFontHeightInPoints((short) 20);
font.setColor(HSSFColor.BLACK.index);
font.setBold(true);
font.setFontName("宋体");
// 把字体 应用到当前样式
_0CellStyle.setFont(font);
//创建标题单元格,合并单元格
int startRowNo = 0;
int endRowNo = 0;
int startCellNo = 0;
int endCellNo = sum-1;
sheet.addMergedRegion(new CellRangeAddress(startRowNo, endRowNo, startCellNo, endCellNo));
HSSFCell cell = row.createCell(0);
cell.setCellValue(title);
cell.setCellStyle(_0CellStyle);
}
/**
* @Description:第一行 标题行
* @param sheet
* @param titles
* @param list
* @param sum
* @return
*/
@SuppressWarnings("deprecation")
public static void getRowOne(HSSFWorkbook excel,HSSFSheet sheet ,String[] titles,List<?> list,int sum){
//第一行
int cellLoc = 0; // 因为serialVersionUID的存在会打乱row.createCell的顺序，所以设置这个变量
HSSFRow row = sheet.createRow(sum);
//设置样式
HSSFCellStyle cellStyle = excel.createCellStyle();
cellStyle.setAlignment(HorizontalAlignment.LEFT);//水平居左
cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中
//设置背景色
cellStyle.setFillBackgroundColor(HSSFColor.ORANGE.index);
cellStyle.setFillForegroundColor(HSSFColor.ORANGE.index);
cellStyle.setFillPattern(HSSFCellStyle.SPARSE_DOTS);
//设置边框
cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框
cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
// 生成一个字体
HSSFFont font = excel.createFont();
font.setFontHeightInPoints((short) 15);
font.setColor(HSSFColor.BLACK.index);
font.setBold(true);
font.setFontName("宋体");
// 把字体 应用到当前样式
cellStyle.setFont(font);
if (titles != null && titles.length > 0) {
for (int i = 0; i < titles.length; ++i) {
HSSFCell cell = row.createCell(i);
//设置样式
cell.setCellStyle(cellStyle);
cell.setCellValue(titles[i]);
}
} else {
Object object = list.get(0);
Class<?> class1 = object.getClass();
Field[] fields = class1.getDeclaredFields();
for (int i = 0; i < fields.length; ++i) {
Field field = fields[i];
String fname = field.getName();
if (fname.equals("serialVersionUID")) {
continue;
}
HSSFCell cell = row.createCell(cellLoc++);
//设置样式
cell.setCellStyle(cellStyle);
cell.setCellValue(fname);
}
}
}
}
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
