代码中是用的HSSFWorkbook类,操作2003-2007excel,后缀为.xls
如过要生成2010的excel,后缀为.xlsx。可以用XSSFWorkbook
贴上代码
import java.io.IOException;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.util.ArrayList;
import java.util.HashSet;
import java.util.List;
import java.util.Map;
import java.util.Set;
import javax.servlet.http.HttpServletResponse;
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.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.slf4j.Logger;
import org.slf4j.LoggerFactory;
import ext.pdmdev.documentary.bean.Bean;
import ext.pdmdev.documentary.bean.TechnologyStatement;
/**
* 公司 凯锐远景
* @author 作者 slj:
* @version 创建时间:2018年11月22日 上午11:02:37
* 类说明
* 创建excel工具类
*/
public class CreateHSSFWorkbookUtil {
static Logger log=LoggerFactory.getLogger(CreateHSSFWorkbookUtil.class);
/**
* 创建HSSFWorkbook对象
* @param sheetName
* @param headList list表头字段名称的集合
* @param dataList set数据无序不重复
* @return
* @throws NoSuchMethodException
* @throws SecurityException
* @throws IllegalAccessException
* @throws IllegalArgumentException
* @throws InvocationTargetException
*/
public static HSSFWorkbook createExcel(String sheetName,List<String> headList,Set<?> dataList) throws NoSuchMethodException, SecurityException, IllegalAccessException, IllegalArgumentException, InvocationTargetException
{
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.createSheet(sheetName);
HSSFRow row = sheet.createRow(0);
HSSFCell cell = null;
//设置表头
HSSFCellStyle columnTopStyle =getColumnTopStyle(wb);
for(int k=0;k<headList.size();k++){
//设置列宽
if(k == 0){
sheet.setColumnWidth( k, 2048);
}else{
sheet.setColumnWidth(k, 10240);
}
cell=row.createCell(k);
cell.setCellStyle(columnTopStyle); //给表头设置样式
cell.setCellValue(headList.get(k));
}
int i=1;
for (Object tech:dataList)
{
//创建行
HSSFRow hssfRow = sheet.createRow(i);
//通过反射获取实体类字段集合
Field[] fields=tech.getClass().getDeclaredFields();
//通过反射获取实体类方法集合
Method[] methods=tech.getClass().getMethods();
//通过实体类字段个数创建列
for(int j=0;j<fields.length;j++){
//创建列
cell=hssfRow.createCell(j);
HSSFCellStyle columnStyle =getStyle(wb);
cell.setCellStyle(columnStyle);
cell.setCellValue(String.valueOf(i)); //设置默认值
Field filed=fields[j];
String filedName=filed.getName();
String filedGetMethodName="get"+filedName;
//动态调用实体类方法设置值
for(Method me:methods){
String methodName=me.getName();
if(methodName.toLowerCase().equals(filedGetMethodName.toLowerCase())){
String result=null;
//反射通过方法名获取方法
Method target=tech.getClass().getMethod(methodName);
//执行方法
result = (String) target.invoke(tech);
cell.setCellValue(result);
}
}
}
i++;
}
return wb;
}
/*
* 列头单元格样式
*/
public static HSSFCellStyle getColumnTopStyle(HSSFWorkbook workbook) {
// 设置字体
HSSFFont font = workbook.createFont();
//设置字体大小
font.setFontHeightInPoints((short)16);
//字体加粗
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
//设置字体名字
font.setFontName("Courier New");
//设置样式;
HSSFCellStyle style = workbook.createCellStyle();
//设置底边框;
style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
//设置底边框颜色;
style.setBottomBorderColor(HSSFColor.BLACK.index);
//设置左边框;
style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
//设置左边框颜色;
style.setLeftBorderColor(HSSFColor.BLACK.index);
//设置右边框;
style.setBorderRight(HSSFCellStyle.BORDER_THIN);
//设置右边框颜色;
style.setRightBorderColor(HSSFColor.BLACK.index);
//设置顶边框;
style.setBorderTop(HSSFCellStyle.BORDER_THIN);
//设置顶边框颜色;
style.setTopBorderColor(HSSFColor.BLACK.index);
//在样式用应用设置的字体;
style.setFont(font);
//设置自动换行;
//style.setWrapText(false);
//设置水平对齐的样式为居中对齐;
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
//设置垂直对齐的样式为居中对齐;
style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
return style;
}
/*
* 列数据信息单元格样式
*/
public static HSSFCellStyle getStyle(HSSFWorkbook workbook) {
// 设置字体
HSSFFont font = workbook.createFont();
//设置字体大小
//font.setFontHeightInPoints((short)10);
//字体加粗
//font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
//设置字体名字
font.setFontName("Courier New");
//设置样式;
HSSFCellStyle style = workbook.createCellStyle();
//设置底边框;
style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
//设置底边框颜色;
style.setBottomBorderColor(HSSFColor.BLACK.index);
//设置左边框;
style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
//设置左边框颜色;
style.setLeftBorderColor(HSSFColor.BLACK.index);
//设置右边框;
style.setBorderRight(HSSFCellStyle.BORDER_THIN);
//设置右边框颜色;
style.setRightBorderColor(HSSFColor.BLACK.index);
//设置顶边框;
style.setBorderTop(HSSFCellStyle.BORDER_THIN);
//设置顶边框颜色;
style.setTopBorderColor(HSSFColor.BLACK.index);
//在样式用应用设置的字体;
style.setFont(font);
//设置自动换行;
style.setWrapText(false);
//设置水平对齐的样式为居中对齐;
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
//设置垂直对齐的样式为居中对齐;
style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
return style;
}
/**
* 直接在页面生成excel(下载)
* @param args
* @throws IOException
*/
public static void downloadExcel(HSSFWorkbook wk,HttpServletResponse response) throws IOException{
//防止中文乱码
String headStr = "attachment; filename=\"" + new String( "技术状态情况统计表.xls".getBytes("gb2312"), "ISO8859-1" ) + "\"";
//response.setContentType("octets/stream");
response.setContentType("APPLICATION/OCTET-STREAM");
response.setHeader("Content-Disposition", headStr);
OutputStream out = response.getOutputStream();
//下载
wk.write(out);
out.flush();
out.close();
log.debug("-----------下载结束-----");
}
public static void main(String[] args) {
}
}