结合网上给出的导出经验,在这里作一个小结。
关于.xls的导出:
1.得到list集合
Map<String, Object> condition = new HashMap<String, Object>();
List<Map<String,Object>> list = financeListService.findShowByAccountAll( condition);
2..设置标题和列名并输出list
// 生成提示信息,
response.setContentType("application/vnd.ms-excel");
String codedFileName = null;
OutputStream fOut = null;
HSSFSheet sheet = null;
HSSFWorkbook wb = null;
HSSFRow row0 = null;
String[] str = null;
try
{
SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMddHHmmss");
String currTime = sdf.format(new Date());
str=new String[]{"员工姓名","性别","生日","账号","密码","邮箱","状态"};
// 进行转码,使其支持中文文件名
codedFileName = java.net.URLEncoder.encode("员工明细_"+currTime, "UTF-8");
response.setHeader("content-disposition", "attachment;filename=" + codedFileName + ".xls");
// 产生工作簿对象
wb = new HSSFWorkbook();
//产生工作表对象
sheet = wb.createSheet("员工明细");
HSSFCellStyle cellStyle = wb.createCellStyle();
row0 = sheet.createRow((short)0);
row0.setHeight((short)500);
HSSFCell cell = row0.createCell(0);
cell.setCellValue("员工明细表");
cell.setCellStyle(setCellStyleHead(wb,1,0,0,cellStyle));
for(int k = 1;k <str.length;k++){
HSSFCell cells = row0.createCell(k);
cells.setCellStyle(setCellStyleHead(wb,1,0,0,cellStyle));
}
sheet.addMergedRegion(new CellRangeAddress(0,0,0,str.length-1));//指定合并区域 起始行号,终止行号, 起始列号,终止列号
HSSFRow row = sheet.createRow((short)1);
row.setHeight((short)1000);
for(int i=0;i<str.length;i++){
HSSFCell cells = row.createCell(i);
cells.setCellValue(str[i]);
cells.setCellStyle(setCellStyleHead(wb,2,0,0,cellStyle));
sheet.setColumnWidth(i,5000);
}
for(int j=0;j<list.size();j++){
Map<String,Object> tempMap=list.get(j);
HSSFRow row1 = sheet.createRow((short)(j+2));
row1.setHeight((short)500);
HSSFCell cell0 = row1.createCell(0);
if(tempMap.get("userame")!=null&&!"".equals(tempMap.get("userame"))){
cell0.setCellValue(tempMap.get("userame")+"");
}
cell0.setCellStyle(setCellStyleHead(wb,2,0,0,cellStyle));
HSSFCell cell1 = row1.createCell(1);
cell1.setCellValue(tempMap.get("sex")+"");
cell1.setCellStyle(setCellStyleHead(wb,2,0,0,cellStyle));
HSSFCell cell2 = row1.createCell(2);
cell2.setCellValue(tempMap.get("birthday")+"");
cell2.setCellStyle(setCellStyleHead(wb,2,0,0,cellStyle));
HSSFCell cell3 = row1.createCell(3);
cell3.setCellValue(tempMap.get("usercode")+"");
cell3.setCellStyle(setCellStyleHead(wb,2,0,0,cellStyle));
HSSFCell cell4 = row1.createCell(4);
cell4.setCellValue(tempMap.get("password")+"");
cell4.setCellStyle(setCellStyleHead(wb,2,0,0,cellStyle));
HSSFCell cell5 = row1.createCell(5);
cell5.setCellValue(tempMap.get("email")+"");
cell5.setCellStyle(setCellStyleHead(wb,2,0,0,cellStyle));
HSSFCell cell6 = row1.createCell(6);
cell6.setCellValue(tempMap.get("state")+"");
cell6.setCellStyle(setCellStyleHead(wb,2,0,0,cellStyle));
}
fOut = response.getOutputStream();
wb.write(fOut);
}
catch (Exception e)
{
e.printStackTrace();
}
finally
{
try
{
if(null!=fOut){
fOut.flush();
fOut.close();
}
}
catch (IOException e)
{
e.printStackTrace();
}
}
3.设置列样式
public HSSFCellStyle setCellStyleHead(HSSFWorkbook wb,int type,int statu,int flag,HSSFCellStyle cellStyle)
{
// HSSFCellStyle cellStyle = wb.createCellStyle();
// 创建单元格样式
if(flag == 1){
// 指定单元格居中对齐
cellStyle.setAlignment(HSSFCellStyle.ALIGN_LEFT);
// 指定单元格垂直居中对齐
cellStyle.setVerticalAlignment(HSSFCellStyle.ALIGN_LEFT);
}else{
// 指定单元格居中对齐
cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
// 指定单元格垂直居中对齐
cellStyle.setVerticalAlignment(HSSFCellStyle.ALIGN_CENTER);
}
//设置单元格边框和颜色
cellStyle.setBorderBottom((short)1);
cellStyle.setBorderLeft((short)1);
cellStyle.setBorderRight((short)1);
cellStyle.setBorderTop((short)1);
HSSFFont font = wb.createFont();
if(type == 1)
{
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
}
if(statu == 2)
{
font.setColor(HSSFColor.RED.index);
}
font.setFontName("宋体");
cellStyle.setFont(font);
//字体大小
font.setFontHeight((short) 300);
cellStyle.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
// 指定当单元格内容显示不下时自动换行
cellStyle.setWrapText(true);
return cellStyle;
}
关于.xlsx的导出:
1.得到list集合
Map<String, Object> condition = new HashMap<String, Object>();
List<Map<String,Object>> list = financeListService.findShowByAccountAll( condition);
2..设置标题和列名并输出list
// 生成提示信息,
response.setContentType("application/vnd.ms-excel");
String codedFileName = null;
OutputStream fOut = null;
XSSFSheet sheet = null;
XSSFWorkbook wb = null;
XSSFRow row0 = null;
String[] str = null;
try
{
SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMddHHmmss");
String currTime = sdf.format(new Date());
str=new String[]{"员工姓名","性别","生日","账号","密码","邮箱","状态"};
// 进行转码,使其支持中文文件名
codedFileName = java.net.URLEncoder.encode("员工明细_"+currTime, "UTF-8");
response.setHeader("content-disposition", "attachment;filename=" + codedFileName + ".xlsx");
// 产生工作簿对象
wb = new XSSFWorkbook();
//产生工作表对象
sheet = wb.createSheet("员工明细");
XSSFCellStyle cellStyle = wb.createCellStyle();
row0 = sheet.createRow((short)0);
row0.setHeight((short)500);
XSSFCell cell = row0.createCell(0);
cell.setCellValue("员工明细表");
cell.setCellStyle(setCellStyleHead(wb,1,0,0));
for(int k = 1;k <str.length;k++){
XSSFCell cells = row0.createCell(k);
cells.setCellStyle(setCellStyleHead(wb,1,0,0));
}
sheet.addMergedRegion(new CellRangeAddress(0,0,0,str.length-1));//指定合并区域 起始行号,终止行号, 起始列号,终止列号
XSSFRow row = sheet.createRow((short)1);
row.setHeight((short)1000);
for(int i=0;i<str.length;i++){
XSSFCell cells = row.createCell(i);
cells.setCellValue(str[i]);
cells.setCellStyle(setCellStyleHead(wb,2,0,0));
sheet.setColumnWidth(i,5000);
}
for(int j=0;j<list.size();j++){
Map<String,Object> tempMap=list.get(j);
XSSFRow row1 = sheet.createRow((short)(j+2));
row1.setHeight((short)500);
row1.createCell(0).setCellValue(tempMap.get("username")!=null&&!"".equals(tempMap.get("username"))?tempMap.get("username")+"":""+"");
row1.createCell(1).setCellValue(tempMap.get("sex")==1?"男"+"":"女"+"");
row1.createCell(2).setCellValue(tempMap.get("birthday")!=null&&!"".equals(tempMap.get("birthday"))?tempMap.get("birthday")+"":""+"");
row1.createCell(3).setCellValue(tempMap.get("usercode")!=null&&!"".equals(tempMap.get("usercode"))?tempMap.get("usercode")+"":""+"");
row1.createCell(4).setCellValue(tempMap.get("password")!=null&&!"".equals(tempMap.get("password"))?tempMap.get("password")+"":""+"");
row1.createCell(5).setCellValue(tempMap.get("email")!=null&&!"".equals(tempMap.get("email"))?tempMap.get("email")+"":""+"");
row1.createCell(6).setCellValue(tempMap.get("state")!=null&&!"".equals(tempMap.get("state"))?tempMap.get("state")+"":""+"");
}
fOut = response.getOutputStream();
wb.write(fOut);
}catch (Exception e)
{
e.printStackTrace();
}
finally
{
try
{
if(null!=fOut){
fOut.flush();
fOut.close();
}
}
catch (IOException e)
{
e.printStackTrace();
}
}
public XSSFCellStyle setCellStyleHead(XSSFWorkbook wb,int type,int statu,int flag)
{
XSSFCellStyle cellStyle = wb.createCellStyle();
// 创建单元格样式
if(flag == 1){
// 指定单元格居中对齐
cellStyle.setAlignment(XSSFCellStyle.ALIGN_LEFT);
// 指定单元格垂直居中对齐
cellStyle.setVerticalAlignment(XSSFCellStyle.ALIGN_LEFT);
}else{
// 指定单元格居中对齐
cellStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);
// 指定单元格垂直居中对齐
cellStyle.setVerticalAlignment(XSSFCellStyle.ALIGN_CENTER);
}
//设置单元格边框和颜色
cellStyle.setBorderBottom((short)1);
cellStyle.setBorderLeft((short)1);
cellStyle.setBorderRight((short)1);
cellStyle.setBorderTop((short)1);
XSSFFont font = wb.createFont();
if(type == 1)
{
font.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);
}
/*if(statu == 2)
{
font.setColor(XSSFColor.RED.index);
} */
font.setFontName("宋体");
cellStyle.setFont(font);
//字体大小
font.setFontHeight((short) 300);
//cellStyle.setFillForegroundColor(XSSFColor.GREY_25_PERCENT.index);
// 指定当单元格内容显示不下时自动换行
cellStyle.setWrapText(true);
return cellStyle;
}